Команды модификации данных DML. Скрипты

Команды модификации данных относятся к языку DML (Язык Манипулирования Данными), который является подмножеством языка SQL. Значения могут быть помещены в таблицу, изменены или удалены следующими операторами:

INSERT (Вставить)
UPDATE (Изменить)
DELETE (Удалить)

В клиентском приложении мы имеем возможность пользоваться табличным компонентом Table, в котором эти действия можно выполнять с помощью методов, однако это удается не всегда. Например, мы вставим новую запись методом Append в таблицу, которая имеет автоинкрементное поле, заполняемое триггером автоматически. Затем мы введем все значения, кроме автоинкремента. Далее, при попытке выполнить метод Post, сохраняющий запись, мы, скорее всего, получим ошибку. Связано это с тем, что триггер BEFORE INSERT срабатывает после того, как табличный компонент выполнит метод Post. А поскольку ключевое поле имеет параметр NOT NULL, то InterBase не даст нам вставить запись с незаполненным ключевым полем. Зато вставка записи запросом INSERT осуществляется без проблем. Поэтому редактирование данных чаще всего перекладывают на компонент Query, свойству SQL которого присвоен нужный запрос. А значит, необходимо знать команды модификации, и уметь их применять.

INSERT

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

INSERT INTO <имя_таблицы> 
[(<список_полей>)] VALUES (<список_значений>);

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

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

INSERT INTO Tovar (Nazvanie, Stoimost) VALUES ('Соль', 3.00);

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

INSERT INTO Table_Cel(Dlinnoe, New_String) VALUES (10, NULL) ;

Помимо простой вставки, оператор можно использовать для добавления группы записей из другой таблицы. При этом ВМЕСТО параметра VALUES указывается встроенный оператор SELECT, делающий выборку записей из другой таблицы. Рассмотрим следующий пример.

Таблица Tovar, которую мы создавали в "Создание, модификация и удаление таблиц и представлений" , имеет три поля, все со значением NOT NULL. Поле ID заполняется генератором автоматически, поля Nazvanie и Stoimost нужно заполнять. Создадим и заполним временную таблицу TempTable:

/*Создаем таблицу*/
CREATE TABLE TempTable(
  TString VARCHAR(20) COLLATE PXW_CYRL,
  TDouble DOUBLE PRECISION);

/*Вводим новые записи*/
INSERT INTO TempTable VALUES ('Спички', 0.2);
INSERT INTO TempTable VALUES ('Конфеты', 10.5);
INSERT INTO TempTable VALUES ('Масло сливочное', 4.40);

/*Подтверждаем сделанные изменения*/
COMMIT;

Выполните этот код в IBConsole с помощью Interactive SQL. Убедитесь, что таблица TempTable действительно появилась в списке таблиц и имеет три записи. Теперь мы можем добавить все эти записи в таблицу Tovar одним оператором вставки:

INSERT INTO Tovar (Nazvanie, Stoimost) SELECT * FROM TempTable;
/*Подтверждаем сделанные изменения*/
COMMIT;

Как видите, мы указали лишь два поля для вставки, а из таблицы TempTable выбрали все поля (их тоже два). Поле ID заполнилось триггером автоматически. Самое главное, чтобы количество, тип и очередность столбцов, указанных для вставки, совпадали с полученными столбцами в выборке SELECT.

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

INSERT INTO Tovar (Nazvanie, Stoimost)
SELECT * 
FROM TempTable
WHERE TDouble < 10.00

UPDATE

Оператор UPDATE позволяет изменить значение существующей записи. Синтаксис оператора следующий:

UPDATE <имя_таблицы>
SET <имя_столбца = значение> [,<имя_столбца = значение>, …]
[WHERE <условия_поиска>]

Попробуем выполнить простейший пример обновления данных. Откройте утилиту IBConsole, войдите в базу данных First и откройте Interactive SQL. Введите следующий код:

UPDATE TempTable 
SET TDouble = 5.5;
COMMIT;

Теперь посмотрим, что у нас получилось, выполнив следующий запрос:

SELECT * FROM TempTable;

Рис. 21.1. Результат изменения значений

Как видно из рисунка, новое значение в поле TDouble получили все записи таблицы! Отсюда вывод: параметр WHERE в операторе UPDATE не является обязательным, однако если его исключить, то обновлению подвергнуться все записи таблицы. Если же нам необходимо изменить только одну запись, ее нужно найти с помощью WHERE:

UPDATE TempTable 
SET TDouble = 15.75
WHERE TString = 'Конфеты';
COMMIT;

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

UPDATE Tovar
SET Nazvanie = 'Крупа манная', Stoimost = 7.35
WHERE ID = 2;
COMMIT;

В операторе UPDATE в качестве новых значений можно указывать и выражения, которые возвращают результат, соответствующий типу поля. Например, если произошло очередное подорожание, то можно изменить стоимость всех товаров одновременно, например:

UPDATE Tovar
SET Stoimost = Stoimost * 1.5;
COMMIT;

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

UPDATE TempTable 
SET TDouble = NULL
WHERE TString = 'Конфеты';
COMMIT;

DELETE

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

DELETE FROM <имя_таблицы>
[WHERE <условия_поиска>];

Как и в предыдущем случае, параметр WHERE не является обязательным, но если его не указывать, будут удалены ВСЕ ЗАПИСИ таблицы! Поэтому будьте осторожны с этим оператором:

/*Удаляем все записи временной таблицы*/
DELETE FROM TempTable;

Используя WHERE, можно удалить одну запись или группу записей, удовлетворяющих условиям поиска:

DELETE FROM Table_Cel
WHERE Dlinnoe < 10;

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

/*Удаляем старую запись*/
DELETE FROM Tovar
   WHERE ID = 3;
/*Добавляем новую запись с этим же номером*/
INSERT INTO Tovar(ID, Nazvanie, Stoimost) 
   VALUES(3, 'Соль экстра', 3.0);
/*Подтвердим изменения*/
COMMIT;

Значения для автоинкрементного поля генерируются с помощью триггера Tr_Tovar и генератора Gen_Tovar. Прежде чем изменить значение генератора, и присвоить его полю ID, триггер делает проверку на NULL. В приведенном выше примере в поле ID добавляется явное значение 3, то есть, триггер не сработает, значение генератора не изменится. Однако на практике, во избежание возможных ошибок, явно вмешиваться в нумерацию ключевого поля не рекомендуется.

Скрипты

Программист не всегда находится там, где эксплуатируют его программу. Нередко он работает в другом офисе, или даже в другом городе. В этом случае за работой БД следит администратор БД или даже просто "продвинутый" оператор. При необходимости внести изменения в базу данных, порой очень сложно объяснить непрограммисту по телефону, что ему следует делать. В этом случае помогут специальные файлы, которые принято называть скриптами. Это обычные текстовые файлы с расширением *.sql, которые можно переслать по e-mail администратору БД. Последнему останется только подгрузить нужный скрипт в утилиту IBConsole и дать команду на выполнение. Для примера создадим скриптовый файл, который создает в базе данных таблицу, индекс, генератор и триггер для реализации автоинкрементного ключевого поля. Затем файл содержит команды вставки в таблицу новых записей.

Откройте любой текстовый редактор, например, "Блокнот" или любой другой, который редактирует текст в кодировке Windows (старые редакторы используют DOS -кодировку, а менеджер файлов FAR позволяет переключаться между этими кодировками). Создайте файл MyScript.sql со следующим содержимым:

/*------------Начало файла------------*/

/*Создаем таблицу*/
CREATE TABLE Days(
   ID INTEGER NOT NULL PRIMARY KEY,
   DayOfWeek VARCHAR(11) COLLATE PXW_CYRL
);

/*Создаем индекс*/
CREATE ASC INDEX I_Days ON Days(DayOfWeek);

/*Создаем генератор*/
CREATE GENERATOR G_Days;

/* Создаем триггер для реализации автоинкрементного поля */
SET TERM ^;
CREATE TRIGGER T_Days FOR Days
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
   IF(New.ID is NULL) THEN
      New.ID = Gen_ID(G_Days, 1);
END^
COMMIT^
SET TERM ;^

/*Заполняем таблицу значениями*/
INSERT INTO Days(DayOfWeek) VALUES ('Понедельник');
INSERT INTO Days(DayOfWeek) VALUES ('Вторник');
INSERT INTO Days(DayOfWeek) VALUES ('Среда');
INSERT INTO Days(DayOfWeek) VALUES ('Четверг');
INSERT INTO Days(DayOfWeek) VALUES ('Пятница');
INSERT INTO Days(DayOfWeek) VALUES ('Суббота');
INSERT INTO Days(DayOfWeek) VALUES ('Воскресенье');

/*Подтверждаем изменения*/
COMMIT;

/*------------Конец файла------------*/

Предположим, мы переслали этот файл администратору БД. Теперь ему нужно сделать следующие шаги (выполните их поочередно):

1. Открыть утилиту IBConsole и войти в базу данных First.
2. Вызвать окно Interactive SQL.
3. Выбрать команду меню Query -> Load Script, найти и открыть наш файл MyScript.sql.
4. Нажать кнопку "Execute Query".

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