Каждый современный человек владеет ноутбуком, но как самому убрать возникающие...
Команда TRUNCATE имеет следующий синтаксис:
TRUNCATE TABLE имя_таблицы;
Пример 1
Если нужно полностью очистить таблицу tovar, то следует выполнить команду:
TRUNCATE TABLE tovar;
Команда TRUNCATE позволяет очистить таблицу сразу и полностью и не позволяет удалять отдельные строки, удовлетворяющие каким-либо условиям.
Команда DELETE удаляет из таблицы записи, удовлетворяющие некоторому условию. Она выполняет удаление двух видов:
- Удаление из одной таблицы;
- Каскадное удаление из нескольких таблиц.
Удаление из одной таблицы
DELETE FROM имя_таблицы
;
- Если указана инструкция LOW_PRIORITY, то удаление не произойдет, пока все пользователи не завершат чтение таблицы.
- Инструкция QUICK запрещает объединять индексы при выполнении удаления. Это несколько ускоряет процесс удаления.
- Инструкция ORDER BY позволяет упорядочить записи перед удалением. Сама по себе эта опция не имеет смысла. Ее полезно использовать в сочетании с инструкцией LIMIT.
- Инструкция LIMIT задает количество удаляемых записей.
Каскадное удаление
При каскадном удалении удаляется не только основная запись, но и записи из других таблиц, содержащие внешний ключ основной записи. В MySQL существует два вида таблиц:
- InnoDB, которые поддерживают механизм связи по внешним ключам.
- MyISAM, которые не поддерживают механизма связи по внешним ключам. В этом случае все связи хранятся исключительно в памяти администратора базы данных и программистов.
Для таблиц InnoDB при построении внешнего ключа всегда описывается реакция СУБД на удаление связанных записей. Реакция может быть трех видов:
- RESTRICT – ограничение;
- CASCADE – каскадное удаление;
- SET NULL – установить значение NULL вместо значений удаленного внешнего ключа.
Пример 4
Пусть имеется таблица tovar, содержащая внешний ключ - categ со ссылкой на таблицу category.
Таким образом, каждый товар относится к определенной категории:
Необходимо удалить категорию «Лакокрасочные» из таблицы category и каскадно удалить все связанные с ней записи из таблицы tovar. Если во время создания внешнего ключа categ была указана реакция CASCADE при удалении, то для решения задачи достаточно выполнить команду:
DELETE FROM category WHERE category.categ_name=”лакокрасочные”;
Все товары, относящиеся к категории «лакокрасочные» автоматически удалятся из связанной таблицы tovar.
Однако, если пользователь работает с таблицами MyIsam, то СУБД не знает ни о каких внешних ключах и каскадного удаления не произойдет. В этом случае пригодятся специальные формы команды DELETE, предназначенные для каскадного удаления:
DELETE имя_таблицы [ ,имя_таблицы...]
DELETE
FROM имя_таблицы, [имя_таблицы...] USING таблица_ссылка
Пример 5
Задачу из примера 4 можно решить двумя эквивалентными командами:
DELETE tovar, category FROM tovar join category on category.itcateg=tovar.categ where category.name_categ=”Лакокрасочные”;
DELETE from tovar, category using tovar join category on category.itcateg=tovar.categ where category.name_categ=”Лакокрасочные”;
Замечание 1
На первый взгляд кажется, что команды
TRUNCATE имя_таблицы;
DELETE FROM имя_таблицы;
совершенно эквивалентны. Действительно, они обе полностью очищают таблицу от всех записей. Однако, разница все же есть.
Если используется TRUNCATE, то удаляются сразу все записи и при повторном заполнении таблицы поля с автоинкрементом получат значения начиная с 1. Если используется DELETE, то удаление происходит по одной записи и при повторном заполнении таблицы поля с автоинкрементом получат значение на 1 больше последнего удаленного значения (то есть автоинкрементирование продолжится).
Удаление записей
Для удаления записей из таблицы применяется оператор DELETE:
DELETE FROM имяТаблицы WHERE условие;
Данный оператор удаляет из указанной таблицы записи (а не отдельные значения столбцов), которые удовлетворяют указанному условию. Условие - это логическое выражение, различные конструкции которого были рассмотрены в предыдущих лабораторных занятиях.
Следующий запрос удаляет записи из таблицы Customer, в которой значение столбца LName равно "Иванов":
DELETE FROM Customer
WHERE LName = "Иванов"
Если таблица содержатся сведения о нескольких клиентах с фамилией Иванов, то все они будут удалены.
В операторе WHERE может находиться подзапрос на выборку данных (оператор SELECT). Подзапросы в операторе DELETE работают точно так же, как и в операторе SELECT. Следующий запрос удаляет всех клиентов из города Москва, при этом уникальный идентификатор города возвращается с помощью подзапроса.
DELETE FROM Customer
WHERE IdCity IN (SELECT IdCity FROM City WHERE CityName = "Москва" )
Transact-SQL расширяет стандартный SQL, позволяя использовать в инструкции DELETE еще одно предложение FROM. Это расширение, в котором задается соединение, может быть использовано вместо вложенного запроса в предложении WHERE для указания удаляемых строк. Оно позволяет задавать данные из второго FROM и удалять соответствующие строки из таблицы в первом предложении FROM. В частности предыдущий запрос может быть переписан следующим образом
DELETE FROM Customer
FROM Customer k INNER JOIN
Операция удаления записей из таблицы является опасной в том смысле, что связана с риском необратимых потерь данных в случае семантических (но не синтаксических) ошибок при формулировке SQL-выражения. Чтобы избежать неприятностей, перед удалением записей рекомендуется сначала выполнить соответствующий запрос на выборку, чтобы просмотреть, какие записи будут удалены. Так, например, перед выполнением рассмотренного ранее запроса на удаление не помешает выполнить соответствующий запрос на выборку.
SELECT *
FROM Customer k INNER JOIN
City c ON k.IdCity = c.IdCity AND c.CityName = "Москва"
Для удаления всех записей из таблицы достаточно использовать оператор DELETE без ключевого слова WHERE. При этом сама таблица со всеми определенными в ней столбцами сохраняется и готова для вставки новых записей. Например, следующий запрос удаляет записи обо всех товарах.
DELETE FROM Product
Задание для самостоятельной работы: Сформулируйте на языке SQL запрос на удаление всех заказов, не имеющих в составе ни одного товара (т. е. все пустые заказы).
Инструкция UPDATE используется для модифицирования строк таблицы. Эта инструкция имеет следующую общую форму:
Строки таблицы tab_name выбираются для изменения в соответствии с условием в предложении WHERE. Значения столбцов каждой модифицируемой строки изменяются с помощью предложения SET инструкции UPDATE, которое соответствующему столбцу присваивает выражение (обычно) или константу. Если предложение WHERE отсутствует, то инструкция UPDATE модифицирует все строки таблицы. С помощью инструкции UPDATE данные можно модифицировать только в одной таблице.
В примере ниже инструкция UPDATE изменяет всего лишь одну строку таблицы Works_on, поскольку комбинация столбцов EmpId и ProjectNumber является первичным ключом этой таблицы и, следственно, она однозначна. В данном примере изменяется должность сотрудника, значение которого было ранее неизвестно или имело значение NULL:
В примере ниже значения строкам таблицы присваиваются посредством выражения. Запрос пересчитывает бюджеты всех проектов с долларов на евро:
USE SampleDb; UPDATE Project SET Budget = Budget * 0.9;
В данном примере изменяются все строки таблицы Project, поскольку в запросе отсутствует предложение WHERE.
В примере ниже в предложении WHERE инструкции UPDATE используется вложенный запрос. Поскольку применяется оператор IN, то этот запрос может возвратить более одной строки:
Согласно этому запросу, для сотрудницы Вершининой Натальи во всех ее проектах в столбце ее должности присваивается значение NULL. Запрос в этом примере можно также выполнить посредством предложения FROM инструкции UPDATE. В предложении FROM указываются имена таблиц, которые обрабатываются инструкцией UPDATE. Все эти таблицы должны быть в дальнейшем соединены. Применение предложения FROM показано в примере ниже. Логически, этот пример идентичен предыдущему:
В примере ниже показано использование выражения CASE в инструкции UPDATE. (Подробное рассмотрение этого выражения описывалось ранее.) В данном примере нужно увеличить бюджет всех проектов на определенное число процентов (20, 10 или 5), в зависимости от исходной суммы бюджета: чем меньше бюджет, тем больше должно быть его процентное увеличение:
USE SampleDb; UPDATE Project SET Budget = CASE WHEN Budget > 0 AND Budget 100000 AND Budget
Инструкция DELETE
Инструкция DELETE удаляет строки из таблицы. Подобно инструкции INSERT, эта инструкция также имеет две различные формы:
Удаляются все строки, которые удовлетворяют условие в предложении WHERE. Явно перечислять столбцы в инструкции DELETE не то чтобы нет необходимости, а даже не разрешается, поскольку эта инструкция оперирует строками, а не столбцами. Использование первой формы инструкции DELETE показано в примере ниже, в котором происходит удаление из таблицы Works_on всех сотрудников с должностью "Менеджер":
Предложение WHERE инструкции DELETE может содержать вложенный запрос, как это показано в примере ниже:
Поскольку сотрудница Вершинина уволилась, из базы данных удаляются все записи, связанные с ней. Запрос из этого примера можно также выполнить с помощью предложения FROM, как это показано ниже. В данном случае семантика этого предложения такая же, как и предложения FROM в инструкции UPDATE.
Использование предложения WHERE в инструкции DELETE не является обязательным. Если это предложение отсутствует, то из таблицы удаляются все строки:
USE SampleDb; -- Удаление всех строк таблицы DELETE FROM Works_on;
Инструкции DELETE и DROP TABLE существенно отличаются друг от друга. Инструкция DELETE удаляет (частично или полностью) содержимое таблицы, тогда как инструкция DROP TABLE удаляет как содержимое, так и схему таблицы. Таким образом, после удаления всех строк посредством инструкции DELETE таблица продолжает существовать в базе данных, а после выполнения инструкции DROP TABLE таблица больше не существует.
Другие инструкции и предложения Transact-SQL для модификации таблиц
Сервер SQL Server поддерживает следующие дополнительные инструкции и предложения для модификации таблиц:
инструкцию TRUNCATE TABLE;
инструкцию MERGE;
предложение OUTPUT.
Эти инструкции и предложение рассматриваются в последующих подразделах.
Инструкция TRUNCATE TABLE
Инструкция TRUNCATE TABLE является более быстрой версией инструкции DELETE без предложения WHERE. Эта инструкция удаляет все строки таблицы более быстро, чем инструкция DELETE, поскольку она удаляет содержимое постранично, тогда как инструкция DELETE делает это построчно. Инструкция TRUNCATE TABLE является расширением Transact-SQL стандарта SQL. Еще одним важным отличием этой инструкции является то, что она сбрасывает индекс столбца, для которого указано свойство автоинкремента IDENTITY.
Инструкция TRUNCATE TABLE имеет следующий синтаксис:
TRUNCATE TABLE table_name
Инструкция MERGE
Инструкция MERGE объединяет последовательность инструкций INSERT, UPDATE и DELETE в одну элементарную инструкцию, в зависимости от существования записи (строки). Иными словами, можно синхронизировать две разные таблицы, чтобы модифицировать содержимое таблицы назначения в зависимости от различий, обнаруженных в таблице-источнике.
Основной областью применения для инструкции MERGE является среда хранилищ данных, где таблицы необходимо периодически обновлять, чтобы отражать новые данные, прибывающие с систем оперативной обработки транзакций OLTP (On-Line Transaction Processing) . Эти данные могут содержать изменения существующих строк таблиц и/или новый строки, которые нужно вставить в таблицы. Если строка в новых данных соответствует записи, которая уже имеется в таблице, выполняется инструкция UPDATE или DELETE. В противном случае выполняется инструкция INSERT.
Альтернативно, вместо инструкции MERGE можно использовать последовательность инструкций INSERT, UPDATE и DELETE, в которых для каждой строки решается, какую операцию выполнять: вставку, удаление или обновление. Но этот подход имеет значительный недостаток, связанный с производительностью: в нем требуется выполнять несколько проходов по данным, а данные обрабатываются по принципу "запись за записью".
Предложение OUTPUT
По умолчанию единым видимым результатом выполнения инструкции INSERT, UPDATE или DELETE является только сообщение о количестве модифицированных строк, например "3 rows DELETED" (удалены 3 строки) и система не сохраняет информацию о модифицированных данных. Если такой видимый результат не удовлетворяет вашим требованиям, то можно использовать предложение OUTPUT , которое выводит модифицированные, вставленные или удаленные строки.
Предложение OUTPUT также применимо с инструкцией MERGE, для которой оно выводит все модифицированные строки в виде таблицы.
Результаты выполненных операций соответствующих инструкций предложение OUTPUT выводит в таблицах inserted и deleted. Кроме этого, чтобы заполнить таблицы, в предложении OUTPUT требуется использовать выражение INTO. Поэтому для сохранения результата используется табличная переменная.
В этой статье мы разберём, пожалуй, одни из самых важных SQL-запросов . Это запросы на добавление и удаление записей из таблицы базы данных . Поскольку, ОЧЕНЬ часто приходится добавлять новые записи в таблицу , причём делать это в автоматическом режиме, то данный материал обязателен к изучению.
Для начала SQL-запрос на добавление новой записи в таблицу :
INSERT INTO users (login, pass) values("TestUser", "123456")
При добавлении записи вначале идёт команда "INSERT INTO ", затем название таблицы, в которую мы вставляем запись. Далее идёт в круглых скобках названия полей, которые мы хотим заполнить. А затем в круглых скобках после слова "values " начинаем перечислять значения тех полей, которые мы выбрали. После выполнения этого запроса в нашей таблице появится новая запись.
Иногда требуется обновить запись в таблице , для этого существует следующий SQL-запрос :
UPDATE users SET login = "TestUser2", pass="1234560" WHERE login="TestUser"
Данный запрос является более сложным, так как он имеет конструкцию "WHERE ", но о ней чуть ниже. Вначале идёт команда "UPDATE ", затем имя таблицы, а после "SET " мы описываем значения всех полей, которые мы хотим изменить. Было бы всё просто, но встаёт вопрос: "А какую именно запись следует обновлять? ". Для этого существует "WHERE ". В данном случае мы обновляем запись, поле "login " у которой имеет значение "TestUser ". Обратите внимание, что если таких записей будет несколько, то обновятся абсолютно все ! Это очень важно понимать, иначе Вы рискуете потерять свою таблицу.
Давайте немного ещё поговорим о "WHERE ". Помимо простых проверок на равенство существуют так же и неравенства, а также логические операции: AND и OR .
UPDATE users SET login = "TestUser2", pass="1234560" WHERE id < 15 AND login="TestUser"
Данный SQL-запрос обновит те записи, id которых меньше 15 И поле "login " имеет значение "TestUser ". Надеюсь, Вы разобрались с конструкцией "WHERE ", потому что это очень важно. Именно "WHERE " используется при выборке записей из таблиц , а это самая частоиспользуемая задача при работе с базами данных.
И, напоследок, простой SQL-запрос на удаление записей из таблицы :
DELETE FROM users WHERE login="TestUser2"
После команды "DELETE FROM " идёт имя таблицы, в которой требуется удалить записи. Дальше описываем конструкцию "WHERE". Если запись будет соответствовать описанным условиям, то она будет удалена. Опять же обратите внимание, в зависимости от количества записей, удовлетворяющих условию после "WHERE ", может удалиться любое их количество.
DELETE - это DM-операция удаления записей из таблицы. Критерий отбора записей для удаления определяется выражением WHERE . В случае, если критерий отбора не определён, выполняется удаление всех записей. Синтаксис:
DELETE FROM ;
Более быстро операцию удаление всех строк из таблицы можно в Transact-SQL также выполнить с помощью команды:
TRUNCATE TABLE
Transact-SQL (T-SQL) - процедурное расширение языка SQL, используемое для программирования на стороне сервера в Microsoft SQL Server и Sybase ASE.
Примеры работы оператора
Пример 1. Требуется удалить из таблицы Laptop все портативные компьютеры с размером экрана менее 15 дюймов.
DELETE FROM Laptop WHERE screen
Все блокноты можно удалить с помощью оператора:
DELETE FROM Laptop;
Transact-SQL расширяет синтаксис оператора DELETE, вводя дополнительное предложение FROM:
При помощи этого предложения можно выполнять соединения таблиц, что логически заменяет использование подзапросов в предложении WHERE для идентификации удаляемых строк. Рассмотрим пример:
Пример 2. Пусть требуется удалить те модели ноутбуков из таблицы Product, для которых нет соответствующих строк в таблице Notebook. Используя стандартный синтаксис, задача решается так:
DELETE FROM Notebook WHERE type = "nb" AND model NOT IN (SELECT model FROM NB);
Предикат type = "Nb" необходим здесь, чтобы не были удалены также модели принтеров, сканеров и персональных компьютеров.
Эту же задачу можно решить с помощью дополнительного предложения FROM следующим образом:
DELETE FROM Notebook FROM Product pr LEFT JOIN NB ON pr.model = NB.model WHERE type = "nb" AND NB.model IS NULL;
Здесь применяется внешнее соединение, в результате чего столбец NB.model для моделей ноутбуков, отсутствующих в таблице NB, будет содержать NULL-значение, что и используется для идентификации подлежащих удалению строк.
