Глава 12. Операторы модификации данных

Язык манипуляции данными (DML – Data Manipulation Language), помимо оператора SELECT, осуществляющего извлечение информации из базы данных (смотри главу 11), включает операторы, изменяющие состояние данных. Этими операторами являются:

INSERT	Добавление записей (строк) в таблицу базы данных
UPDATE	Обновление данных в столбце таблицы базы данных
DELETE	Удаление записей из таблицы базы данных

Оператор INSERT

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

Синтаксис оператора:

INSERT INTO
имя таблицы>[(
имя столбца>,...)]
{VALUES (
значение столбца>,…)}
|
выражение запроса>
| {DEFAULT VALUES};

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

CREATE TABLE Поставщик (
[Код_поставщика] LONG NOT NULL ,
[ФИО_поставщика] CHAR(30) NOT NULL ,
[Адрес] VARCHAR(50) NOT NULL )
Примечание
Язык определения схемы (DDL – Data Definition Language), который, в частности, включает оператор создания таблицы – CREATE TABLE, выходит за рамки данной книги. Необходимые объекты схемы мо-гут быть построены в Access с помощью визуальных средств конструирования, рассмотренных ранее достаточно подробно. Тем не менее, вот комментарий к приведенному выше оператору:
- В скобках после имени таблицы указывается список столбцов, перечисленных через запятую.
- Элемент списка содержит имя столбца, тип данных и ограничение, накладываемое на значения в данном столбце.
- LONG – длинное целое.
- CHAR – символьный тип постоянной длины. В скобках указан размер в символах.
- VARCHAR – символьный тип переменной длины. В скобках указан максимальный размер в символах.
- NOT NULL – ограничение, означающее, что запись в этом столбце обязательно должна иметь значение. В противном случае сохранить/изменить запись будет невозможно. Альтернативой является ог-раничение NULL (принимается по умолчанию).

Пусть требуется добавить в эту таблицу поставщика Фролова Олега Евгеньевича с кодом 100, проживающего в городе Ростове-на-Дону. Это можно сделать следующим оператором:

INSERT INTO Поставщик VALUES (100, 'Фролов Олег Евгеньевич', '344000, Рос-тов-на-Дону');

Если задать список столбцов, то можно изменить «естественный» порядок их следования:

INSERT INTO Поставщик(ФИО_поставщика, Адрес, Код_поставщика) VALUES ('Фролов Олег Евгеньевич', '344000, Ростов-на-Дону', 100);

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

CREATE TABLE Поставщик_D (
[Код_поставщика] LONG NULL ,
[ФИО_поставщика] CHAR(30) NULL ,,
[Адрес] VARCHAR(50) NOT NULL DEFAULT ‘344000, Ростов-на-Дону’ )
Внимание
Синтаксис оператора CREATE TABLE в Access не поддерживает предложения DEFAULT для определения значения по умолчанию для столбца, однако, значение по умолчанию можно задать в конструкторе таблиц.

Отметим, что здесь для всех столбцов определены значения по умолчанию (для первых двух – NULL, а для последнего столбца – адрес – ‘344000, Ростов-на-Дону’). Теперь мы могли бы написать:

INSERT INTO Поставщик_D(ФИО_поставщика, Код_поставщика) VALUES ('Фролов Олег Евгеньевич', 100);

В этом случае отсутствующее значение столбца при вставке строки будет заменено значением по умолчанию – ‘344000, Ростов-на-Дону’. Заметим, что если для столбца в операторе CREATE TABLE не указано значение по умолчанию и не указано ограничение NOT NULL, запрещающее использование NULL в данном столбце таблицы, то подразумевается значение по умолчанию NULL.

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

Рассмотрим теперь случай использования подзапроса. Пусть нам требуется вставить в таблицу Поставщик_D все строки из таблицы По-ставщик, относящиеся к поставщикам с идентификационными номерами (Код_поставщика) в диапазоне от 10 до 15. Поскольку необходимые нам значения уже имеются в некоторой таблице, то формирование вставляемых строк вручную, во-первых, является неэффективным, а, во-вторых, может допускать ошибки ввода. Использование подзапроса решает эти проблемы:

INSERT INTO Поставщик_D
SELECT *
FROM Поставщик
WHERE Код_поставщика BETWEEN 10 AND 15;

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

INSERT INTO Поставщик_D (Код_поставщика, ФИО_поставщика, Адрес)
SELECT *
FROM Поставщик
WHERE Код_поставщика BETWEEN 10 AND 15;

или

INSERT INTO Поставщик_D
SELECT Код_поставщика, ФИО_поставщика, Адрес
FROM Поставщик
WHERE Код_поставщика BETWEEN 10 AND 15;

или

INSERT INTO Поставщик_D(Код_поставщика, ФИО_поставщика, Адрес )
SELECT Код_поставщика, ФИО_поставщика, Адрес
FROM Поставщик
WHERE Код_поставщика BETWEEN 10 AND 15;

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

INSERT INTO Поставщик_D(Код_поставщика, ФИО_поставщика)
SELECT Код_поставщика, ФИО_поставщика
FROM Поставщик
WHERE Код_поставщика BETWEEN 10 AND 15;

В данном случае в столбец Адрес таблицы Поставщик_D будет под-ставлено значение по умолчанию 344000, Ростов-на-Дону’ для всех вставляемых строк.

Отметим, что при использовании подзапроса, содержащего предикат, будут вставлены только те строки, для которых значение предиката равно TRUE (не UNKNOWN!). Другими словами, если бы столбец Адрес в таблице Поставщик допускал бы NULL-значение, и это значение присутствовало бы в ряде строк, то эти строки не были бы вставлены в таблицу Поставщик_D.

Преодолеть ограничение на вставку одной строки в операторе INSERT при использовании конструктора строки в предложении VALUES позволяет искусственный прием использования подзапроса, формирующего строку с предложением UNION ALL. Так если нам требуется вставить несколько строк при помощи одного оператора INSERT, можно написать:

INSERT INTO Поставщик_D (Код_поставщика, ФИО_поставщика)
SELECT 16 AS Код_поставщика, 'Симонов Юрий Андреевич' AS ФИО_поставщика
UNION ALL
SELECT 17, 'Кудлатов Павел Сергеевич'
UNION ALL
SELECT 18, 'Полякова Елена Игоревна'

Здесь мы опять используем значение по умолчанию в качестве адреса. Использование UNION ALL предпочтительней, чем UNION даже тогда, когда отсутствие строк-дубликатов гарантировано, т.к. в этом случае не будет выполняться проверка для исключения дубликатов.

Внимание
В Access этот прием не работает, вызывая следующую ошибку:
Query input must contain at least one table or query.
(Запрос на входе должен содержать не более одной таблицы или запроса)

Вставка строк в таблицу, содержащую автоинкрементируемое поле

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

В качестве примера рассмотрим таблицу Поставщик_Inc со следую-щими столбцами (в скобках указан тип данных столбца в том виде, в котором он задается в конструкторе таблиц):

Код_поставщика (последовательный счетчик – первичный ключ),
ФИО_поставщика (текстовый) ,
Адрес (текстовый),

Таким образом, в нашем примере первая вставленная запись будет иметь в столбце Код_поставщика значение 1, вторая – 2 и т.д.

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

INSERT INTO Поставщик_Inc
VALUES (16, 'Симонов Юрий Андреевич', '344000 Ростов-на-Дону');
Примечание

Чтобы вставить строку с автоматическим определением значения счетчика, следует использовать список столбцов, не указывая при этом поле счетчика, точно так же, как мы поступали в случае использования значения по умолчанию, т.е.

INSERT INTO Поставщик_Inc(ФИО_поставщика, Адрес)
VALUES (‘Фролов Олег Евгеньевич’, ‘344000 Ростов-на-Дону’);

В результате выполнения этого оператора в таблицу Поставщик_Inc будет вставлена информация о поставщике Фролове Олеге Евгеньевиче из города Ростова-на-Дону. В поле Код_поставщика теперь находится значение, которое только случайно может оказаться равным 15. В большинстве случаев этого бывает достаточно, т.к. значение автоинкрементируемого поля, как правило, не несет никакой информации; главное, чтобы оно было уникальным.

Важно отметить, что если явно подставляемое значение (в нашем примере – 15) окажется максимальным в столбце Код_поставщика, то далее нумерация продолжится со значения 16. Наконец, рассмотрим пример вставки данных из таблицы Поставщик в таблицу Поставщик_Inc, сохранив значения в поле Код_поставщика:

INSERT INTO Поставщик_Inc(Код_поставщика,ФИО_поставщика, Адрес)
SELECT *
FROM Поставщик;

По поводу автоинкрементируемых столбцов следует еще сказать сле-дующее. Пусть последнее значение в поле Код_поставщика было равно 16, после чего строка с этим значением была удалена. Какое значение будет в этом столбце после вставки новой строки? Правильно, 17, т.к. последнее значение счетчика сохраняется, несмотря на удаление строки, его содержащей. Поэтому нумерация значений в результате удаления и добавления строк не будет последовательной. Это является еще одной из причин для вставки строки с заданным (пропущенным) значением в автоинкрементируемом столбце.

Оператор UPDATE

Оператор UPDATE изменяет имеющиеся данные в таблице. Команда имеет следующий синтаксис:

UPDATE
имя таблицы>
SET {
имя столбца> = {
выражение для вычисления значения столбца>
| NULL
| DEFAULT},...}
[ {WHERE
предикат>}];

С помощью одного оператора могут быть заданы новые значения для любого количества столбцов. Однако в одном и том же операторе UPDATE можно вносить изменения в каждый столбец указанной таблицы только один раз. При отсутствии предложения WHERE будут обновлены все строки таблицы. В противном случае, будут обновлены только те строки, для которых предикат в предложении WHERE дает значение ИСТИНА.

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

Внимание
Конструкция SET имя столбца> = DEFAULT не поддерживается в Access.

Ссылка на «выражение для вычисления значения столбца» может относиться к текущим значениям в изменяемой таблице. Например, мы можем уменьшить все розничные цены на 10 процентов с помощью следующего оператора:

UPDATE Товары
SET Цена_розн = Цена_розн*0.9

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

UPDATE Товары
SET Цена_розн = Цена_опт*1.2

Естественно, типы данных столбцов (в данном случае это, естест-венно, выполняется, т.к. оба поля имеют тип данных Денежный) долж-ны быть совместимы. Относительно приведения типов смотрите главу 11, где речь идет об операторе CAST.

Если требуется изменять данные в зависимости от содержимого некоторого столбца, можно воспользоваться функцией VBA IIF (глава 11) Если, скажем, нужно установить розничную цену на 10 процентов больше, чем оптовая на товары, оптовая стоимость которых превышает 1000 рублей, и на 20 процентов для остальных товаров, то можно написать такой запрос:

UPDATE Товары
SET Цена_розн = Цена_опт * IIF(Цена_опт > 1000, 1.1, 1.2)

Необходимо сказать несколько слов об автоинкрементируемых столбцах. Если, к примеру, столбец Код_поставщика в таблице Поставщик_Inc определен как последовательный счетчик, то следующий оператор

UPDATE Поставщик_Inc
SET Код_поставщика = 8
WHERE Код_поставщика = 4
не будет выполнен, т.к. автоикрементируемое поле не допускает обновления, и мы получим соответствующее сообщение об ошибке (Cannot update ‘Код_поставщика’; field not updateable). Чтобы выполнить все же обновление, можно поступить следующим образом. Сначала вставить нужную строку, так как это допускается (смотри выше оператор INSERT), после чего удалить старую строку:

INSERT INTO Поставщик_Inc(Код_поставщика, ФИО_поставщика, Адрес)
SELECT 8, ФИО_поставщика, Адрес
FROM Поставщик_Inc
WHERE Код_поставщика = 4
DELETE FROM Поставщик_Inc
WHERE Код_поставщика = 4

Разумеется, другой строки со значением Код_поставщика = 8 в таблице быть не должно.

Оператор DELETE

Оператор DELETE удаляет строки из временных или постоянных базовых таблиц, представлений или курсоров, причем в двух последних случаях действие оператора распространяется на те базовые таблицы, из которых извлекались данные в эти представления или курсоры. Оператор удаления имеет простой синтаксис:

DELETE FROM
имя таблицы >
[WHERE
предикат>];

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

Рассмотрим следующий пример. Требуется удалить из таблицы Продажи всю информацию о списанных товарах.

DELETE FROM Продажи
WHERE Прод_возвр = ‘Сп’

Все продажи можно удалить с помощью оператора

DELETE FROM Продажи

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

Эту задачу можно решить следующим запросом:

DELETE FROM Поставщик_Inc
WHERE Код_поставщика NOT IN (SELECT Код_поставщика
FROM Поставщик )