Язык SQL — Команды модификации данных INSERT, DELETE, UPDATE

Рассмотрим команды, которые управляют значениями, представленными в таблице. Значения могут быть помещены и удалены тремя командами языка манипулирования данными DML:

• INSERT – команда добавления;
• DELETE – команда удаления;
• UPDATE – команда обновления.

1. Добавление записей в таблицу

Для добавления записей используется команда INSERT, имеющая следующий формат:

INSERT INTO <Имя таблицы> [(<Список полей>)] VALUES (<Список выражений>)

Команда INSERT служит для добавления записей в конец существующей таблицы, используя выражения, перечисленные после слова VALUES.

В предложении VALUES указываются выражения, порождающие значения полей новой записи таблицы. Выражение может включать имена полей таблицы, вызовы функций, определенных в данной СУБД, константы, знак операций сцепления строк или знаки операций. Типы значений выражений должны соответствовать типам полей таблицы. Строки и даты должны заключаться в одинарные кавычки.

Необязательная часть команды <Список полей> может быть опущена. Тогда подразумеваются все столбцы таблицы, причем в порядке, установленном при создании таблицы.

Так, например, чтобы ввести строку в таблицу Поставщик нового поставщика, можно использовать следующую команду:

INSERT INTO Поставщик

VALUES (33,'Алферов','Моск.обл.',65)

На рис. 4 представлена таблица Поставщик после вставки новой записи.

Рис. 4. Таблица Поставщик после вставки новой строки

Таблица (в нашем случае — Поставщик), должна быть предварительно определена, а каждое значение в предложении значений, должно совпадать с типом данных столбца, в который оно вставляется.

Если порядок значений не соответствует тому порядку полей, который был установлен при создании таблицы, то после имени таблицы в скобках приводится список полей в соответствии со списком значений. Это позволяет вставлять имена в любом порядке.

Например, следующая команда дополняет таблицу Поставщик новой записью (рис. 5):

INSERT INTO Поставщик (Регион, Название, Код_постав, Рейтинг)

VALUES ('Владимир.обл.', 'Егоров', 2001, 76)

Рис. 5. Таблица Поставщик после вставки строки (‘Владимир.обл.’, ‘Егоров’, 2001, 76)

Какие поля должны быть заданы при вводе данных? Это определяется тем, как описаны эти поля при создании соответствующей таблицы. Отметим, что если поле имеет признак обязательный (NOT NULL) при описании таблицы, то команда INSERT должна обязательно содержать данные для ввода в это поле каждой строки.

Поэтому если в таблице все поля обязательные, то каждая вводимая строка должна содержать полный перечень вводимых значений, а указание имен полей в этом случае необязательно. В противном случае, если имеется хотя бы одно необязательное поле и вы не вводите в него значений, задание списка имен полей — обязательно.

Если в списке полей отсутствует какое-либо поле таблицы, то ему будет присвоено значение NULL или значение по умолчанию (DEFAULT), если эти ограничения определены при создании таблицы.

Предположим, что еще не известно значение поля Регион для Меньшова. Тогда новую строку можно вставить в таблицу Поставщик одной из следующих команд:

INSERT INTO Поставщик (Код_постав, Название, Рейтинг) VALUES (33,'Меньшов', 74)

или

INSERT INTO Поставщик VALUES (33,'Мньшов', NULL, 74)

Эти команды будут выполнены, т.к. поле Регион допускает использование значения NULL (приложение 3). Результат выполнения команды представлен на рис. 6.

Рис. 6. Таблица Поставщик после вставки строки со значением NULL

Таким же образом можно присваивать значения полям, для которых при создании таблицы установлено значение по умолчанию (DEFAULT).

Например, команда

INSERT INTO Поставщик (Код_постав, Название, Регион) VALUES (21, 'Мальцев В.Б.', 'Иванов.обл.')

добавит в таблицу Поставщик нового поставщика Мальцева из Ивановской области, имеющего код равный 21 и рейтинг равный 100 (рис. 7).

Рис. 7. Таблица Поставщик после вставки строки со значениями (21, ‘Мальцев В.Б.’, ‘Иванов.обл.’)

Обратите внимание, что столбец Рейтинг в списке полей команды INSERT отсутствует. Это значит, что для этого поля автоматически установлено значение по умолчанию и оно равно 100 (приложение 3).

Следует отметить, что начиная с SQL Server 2008 с помощью одной команды INSERT можно вставлять несколько записей. Например, команда

INSERT INTO Поставщик (Код_постав, Название, Регион) VALUES (2002, 'Аникина', 'Владимир.обл.'),(2003, 'Сидоров', 'Иванов.обл.')

добавит в таблицу Поставщик две записи (рис. 8).

Рис. 8. Таблица Поставщик после вставки двух новых строк

Заметим, что при вставке строки в таблицу проверяются все ограничения, наложенные на данную таблицу. Это могут быть ограничения первичного ключа или уникального индекса, проверочные ограничения типа CHECK, ограничения ссылочной целостности. В случае нарушения какого-либо ограничения вставка строки будет отклонена.

2. Удаление записей из таблицы

Записи из таблицы удаляются командой удаления — DELETE. Эта команда позволяет удалить одну или несколько строк из таблицы в соответствии с условиями, которые задаются для удаляемых строк. Формат команды выглядит следующим образом:

DELETE FROM <Имя таблицы или представления> [WHERE <Условие>]

Команда удаления может удалять только введенные строки, а не индивидуальные значения полей.

Если условие не указано, то будут удалены все записи без предупреждения и без запроса на подтверждение!

Однако это не означает, что удаляется вся таблица. Структура таблицы остается. Например, чтобы удалить все содержание таблицы Поставщик, вы можете использовать следующую команду:

DELETE FROM Поставщик

Теперь, когда таблица пуста, ее можно окончательно удалить командой DROP TABLE.

Обычно, требуется удалять только некоторые определенные строки из таблицы. Чтобы определить какие строки будут удалены, используется условие предложения WHERE. Например, удалить из таблицы Поставщик Меньшова, имеющего код равный 35, можно командой

DELETE FROM Поставщик WHERE Код_постав = 35

В данном примере в условии использовалось поле Код_постав вместо поля Название потому, что это поле является первичным ключом, что гарантирует удаление только одной строки.

Можно также использовать DELETE с условием, которое бы выбирало группу строк, как показано в этом примере:

DELETE FROM Поставщик WHERE Регион = 'Иванов.обл'

В результате выполнения этой команды будут удалены все поставщики из Ивановской области (рис. 9).

Рис. 9. Таблица Поставщик после удаления поставщиков из Ивановской области

3. Изменение значений полей таблицы

В SQL для изменения существующих данных в таблице используется команда UPDATE, которая имеет следующий синтаксис:

UPDATE <Имя таблицы>

SET Столбец1 = Выражение1 [, Столбец2 = Выражение2,…] [WHERE <Условие>]

Команда изменяет в указанной таблице значения указанных полей тех записей, которые удовлетворяют заданному условию отбора (WHERE <условие>). Если условие не указано, изменения применяются ко всем записям таблицы. Например, изменить рейтинги всех поставщиков на 200 можно с помощью команды

UPDATE Поставщик

Рейтинг = 200

Конечно, редко требуется указывать все строки таблицы для изменения единственного значения. Поэтому команда UPDATE как правило содержит условие. Вот как, например можно выполнить изменение для поставщика с кодом равным 120:

UPDATE Поставщик

SET Рейтинг = 98

WHERE Код_постав = 120

С помощью одной команды могут быть заданы значения для любого количества столбцов.

Предположим, что поставщик Петров (код поставщика 100) ушел на пенсию, и мы хотим переназначить его код новому поставщику Ягудину из Московской области:

UPDATE Поставщик

SET Название = 'Ягудин', Регион = 'Московск.обл.'

WHERE Код_постав = 100

Результат запроса представлен на рис. 10.

Рис. 10. Таблица Поставщик после выполнения команды UPDATE

В предложении SET команды UPDATE допускается использовать выражения.

Предположим, что надо уменьшить в два раза рейтинг всем поставщикам во Владимирской области:

UPDATE Поставщик

SET Рейтинг = Рейтинг * 0.5

WHERE Регион = 'Владимир.обл.'

Результат запроса представлен на рис. 11.

Рис. 11. Таблица Поставщик после выполнения команды UPDATE

Предложение SET — это не предикат, поэтому оно может вводить неопределенные значения NULL. Так что, если требуется установить все рейтинги поставщиков в Московской области в NULL, то можно воспользоваться следующей командой:

UPDATE Поставщик

SET Рейтинг = NULL

WHERE Рейтинг = 'Моск.обл.'

4. Использование подзапросов с командами модификации

Подзапросы могут использоваться и с командами языка манипулирования данными (DML).

Важный принцип, который надо соблюдать при работе с командами модификации, состоит в том, что нельзя в предложении FROM любого подзапроса, указывать таблицу, к которой ссылаетесь с помощью основной команды. Это относится ко всем трем командам модификации.

Использование подзапросов с командой INSERT

Команда INSERT использует данные, возвращаемые подзапросом, для помещения их в другую таблицу.

Базовый синтаксис соответствующей команды должен быть следующим:

INSERT INTO <Имя таблицы> [(<Список полей>)]

<команда SELECT>

Эта команда используется для ввода в заданную таблицу новых строк, отобранных из другой таблицы с помощью команды SELECT.

Рассмотрим пример использования этой команды. Пусть таблица Владимир имеет структуру, полностью совпадающую со структурой таблицы Поставщик. Запрос, позволяющий заполнить таблицу Владимир записями из таблицы Поставщик обо всех поставщиках из Владимирской области, выглядит следующим образом:

INSERT INTO Владимир

SELECT *

FROM Поставщик

WHERE Регион = 'Владимир.обл.'

Эта команда выбирает все строки из таблицы Поставщик со значениями Регион = ‘Владимир.обл.’ и помещает в таблицу Владимир. Чтобы это работало, таблица Владимир должна отвечать следующим условиям:

• она должна уже быть создана;
• она должна иметь четыре столбца, которые совпадают с таблицей Поставщик в терминах типа данных; то есть первый, второй, и так далее, столбцы каждой таблицы, должны иметь одинаковый тип данных.

Использование в подзапросе символа «*» является в данном случае оправданным, так как порядок следования столбцов является одинаковым для обеих таблиц. Если бы это было не так, следовало бы применить список столбцов либо в команде INSERT, либо в подзапросе, либо в обоих местах, который приводил бы в соответствие порядок следования столбцов.

Данные таблицы Владимир представлены на рис. 12.

Рис. 12. Данные таблицы Владимир

Предположим, например, необходимо сформировать новую таблицу с именем Итоги_дня, которая будет следить за общим количеством сумм поступлений за каждый день. Заполнить таблицу Итоги_дня надо информацией представленной в таблице Приход. Для этого воспользуемся следующей командой:

INSERT INTO Итоги_дня (Дата, Сумма)

SELECT Дата, Sum(Цена * Количество)

FROM Документ, Приход

WHERE Документ.Ном_док = Приход.Ном_док

GROUP BY Дата

Данные таблицы Владимир представлены на рис. 13.

Рис. 13. Данные таблицы Итоги_дня

Использование подзапросов с командой DELETE

Использование подзапроса в предложение WHERE команды DELETE дает возможность задавать некоторые довольно сложные условия, чтобы установить, какие строки будут удаляться.

Как отмечалось выше при работе с командами модификации нельзя ссылаться к таблице, из которой вы будете удалять строки в предложении FROM подзапроса. Однако, в условии подзапроса можно сослаться на текущую строку этой таблицы.

Пример, удалить все поступления товара за 25 октября 2020 года от поставщика Гусь П.А.

DELETE FROM Приход

WHERE EXISTS ( SELECT * FROM Документ d, Поставщик p WHERE Дата = ’25.11.2020’ AND Название = ‘Гусь П.А.’ AND Приход. Ном_док = d. Ном_док AND d. Код_постав = p.Код_постав)

Обратите внимание, что в предложении WHERE внутреннего запроса имеется ссылка (Приход.Ном_док) к таблице Приход. Это означает, что весь подзапрос будет выполняться отдельно для каждой строки таблицы Приход, т.е. подзапрос является коррелированным.

На рис. 14, а приведены данные таблицы Приход до удаления, а на рис. 14, б – после удаления.

Рис. 14. Результат выполнения команды DELETE

Transact-SQL расширяет синтаксис команды DELETE, вводя дополнительное предложение FROM.

При помощи этого предложения можно выполнять соединения таблиц, что логически заменяет использование подзапросов в предложении WHERE для идентификации удаляемых строк. Предыдущий пример можно записать с помощью дополнительного предложения FROM следующим образом:

DELETE FROM Приход

FROM Документ d, Поставщик p

WHERE Дата = ’25.11.2020’AND Название = ‘Гусь П.А.’AND Приход. Ном_док = d. Ном_док AND d. Код_постав = p.Код_постав

Использование подзапросов с командой UPDATE

Команда UPDATE использует подзапросы в предложении WHERE .

Рассмотрим пример, поясняющий использование команды UPDATE с подзапросом: увеличить рейтинг поставщика на 100, если он не менее 2 раз поставлял товар:

UPDATE Поставщик

SET Рейтинг = Рейтинг + 100

WHERE 2 < = ( SELECT COUNT (Ном_док) FROM Документ d WHERE Поставщик.Код_постав = d.Код_постав)

Данные таблицы Поставщик после выполнения команды UPDATE представлены на рис. 15.

Рис. 15. Данные таблицы Поставщик после выполнения команды UPDATE