Операции добавления, обновления и удаления данных

Язык обработки данных (DML) включает три операции обновления: INSERT (вставка), UPDATE (изменение) и DELETE (удаление). Рассмотрим синтаксис этих операторов в несколько упрощенном виде по сравнению со стандартом SQL.

Вставка строк производится с помощью оператора INSERT, формат которого приведен ниже:

INSERT INTO ctable name> [(column_list) ]

{VALUES (value_list) I <query>)

где <table name> – имя базовой таблицы или обновляемого представления (представления будут обсуждаться далее в этой главе), column_list – необязательный параметр, позволяющий указать обновляемые столбцы; если он не указан, то порядок столбцов берется таким же, как в определении таблицы.

Форма записи с ключевым словом VALUES позволяет добавить в таблицу строку с заданными значениями атрибутов. Например, добавим в таблицу Students (табл. 7.2) дополнительную запись (атрибуты StudID и Group – числовые; FIO – строковый, значение в соответствии с требованиями синтаксиса SQL взято в одинарные кавычки):

INSERT INTO Students

VALUES (', 'Ивановский И.И.', 386)

Аналогичный результат может быть получен выполнением следующего выражения (разница в том, что порядок значений изменен и надо явно указать перечень столбцов):

INSERT INTO Students (FIO, Group, StudID)

VALUES ('Ивановский И.И.', 386, 130)

Таблица 7.2

Таблица Students

StudID

FIO

Group

123

Иванов И.И.

382

124

Петров П.П.

382

125

Сидоров С.С.

383

Значения отдельных атрибутов при добавлении записи могут быть опущены. В этом случае столбец получит или значение по умолчанию, если оно для него было определено (явно или через определение домена), или значение NULL. Если такой столбец находится в середине списка, пропуск значения явно указывается, если в конце – можно ничего не указывать:

INSERT INTO Students (StudID, Group, FIO)

VALUES (', , 'Васильев В.В.')

Сравните с

INSERT INTO Students (StudID, FIO, Group)

VALUES (','Васильев B.B.')

С помощью оператора INSERT также можно добавить в таблицу набор строк, полученных в результате выполнения запроса <query>. В этом случае вместо ключевого слова VALUES и перечисления значений должен стоять оператор SELECT, который будет подробно рассмотрен в параграфе 7.5. Ниже приведен пример, добавляющий в таблицу Students записи из таблицы St1 о студентах из группы с номером 387. Здесь предполагается, что заголовки у этих таблиц одинаковые и добавление новых значений не приведет к нарушению ограничения первичного ключа (значения StudID добавляемых записей отличны от имеющихся в Students значений этого поля):

INSERT INTO Students (StudID, FIO, Group)

SELECT * FROM Stl

WHERE Group=387

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

UPDATE <table name>

SET column_l = value_l [, column_2 = value_2 ...]

[WHERE <predicate>]

Здесь <table name> – название обновляемой таблицы; column_l – название первого из обновляемых столбцов, value_l – присваиваемое ему значение (константа или результат вычислений). Обновляемых столбцов может быть несколько.

Выражение <predicate> обозначает логическое условие. Если необязательная секция WHERE пропущена, обновляются все записи таблицы. Если эта секция присутствует, то обновляется только те записи, для которых <predicate> будет истинным. Составное условие формируется с помощью логических связок AND (логическое "и"), OR (логическое "или"), NOT (отрицание). Если необходимо обновить только одну конкретную запись, это можно сделать, указав в условии значение первичного ключа. Например:

UPDATE Students

SET FIO = 'Петровский П.П.', Group = 384 WHERE StudID = 130

В данном примере изменены фамилия и номер группы студента, идущего в БД под идентификатором 130. Рассмотрим другой пример. Пусть в таблице Т1 в поле Price хранится информация о цене товара. Следующее выражение увеличивает все цены на 10%:

UPDATE T1 SET Price=Price*l. 1

Удаление строк из таблицы производится оператором DELETE: DELETE FROM <table name>

[WHERE <predicate>]

Здесь ctable name> – название таблицы, из которой удаляются данные; <predicate> – логическое условие. Если секция WHERE присутствует, удаляются только те записи, для которых <predicate> будет истинным. Например, удалим из таблицы Students всех Ивановых И. И. из группы 382:

DELETE FROM Students

WHERE Group = 382 AND FIO = 'Иванов И.И.' Выполнение приведенного ниже выражения, удалит все данные из таблицы, но, в отличие от оператора DROP TABLE, сама таблица удалена не будет:

DELETE FROM Students

Нужно учитывать, что определенные для БД ограничения целостности (например, ограничение внешнего ключа) могут не позволить обновить или удалить значения некоторых записей.

Оператор MERGE позволяет изменить, добавить или удалить записи в таблице, на основании проверки условий относительно совпадения записей изменяемой таблицы и какой-то другой. Это может понадобиться, например, при синхронизации данных, хранящихся в разных таблицах. MERGE появился в SQL позже описанных выше INSERT, UPDATE

и DELETE, в частности его поддержка в СУБД SQL Server реализована только начиная с версии SQL Server 2008.

Рассмотрим несколько упрощенный синтаксис MERGE из диалекта Transact-SQL:

MERGE [INTO] <target_table> [[AS] table_alias]

USING <table_source>

ON <merge_search_condition>

[WHEN MATCHED [AND <search_condition> ]

THEN <merge_matched> ] [ ...n ]

[WHEN NOT MATCHED [BY TARGET]

[ AND <search_condition> ]

THEN <merge_not_matched> ]

[WHEN NOT MATCHED BY SOURCE

[ AND <search_condition> ]

THEN <merge_matched> ] [ ...n ];

Здесь [INTO] <target_table> определяет "целевую" таблицу или представление, данные из которой сравниваются с "исходной" таблицей, представлением или выражением, обозначенным как <table_source>. По результатам сравнения изменения происходят в целевой таблице. Условия, выполнение которых проверяется в отношении записей исходной и целевой таблиц, определяются выражением ON <merge_search_ condition:". Целевой таблице может быть задан псевдоним (более короткое или понятное имя, далее используемое в выражении), указанный в формате как table_alias.

Действия, выполняемые при соответствии значений записей условиям, определяет необязательный раздел [WHEN MATCHED [AND <search_ conditions]. Одно выражение MERGE может иметь не больше двух разделов WHEN MATCHED. Если их два, в первом обязательно должны быть дополнительные условия: AND <search_condition>. Тогда второй WHEN MATCHED применяется только в тех случаях, если не применяется первый. Если имеются два WHEN MATCHED, один должен указывать действие UPDATE (изменить записи), а другой – действие DELETE (удалить).

Наличие раздела [when not matched [by target] [and <search_condition>] говорит, что если в целевой таблице нет строки из "исходной" таблицы (выражения) и такая строка соответствует дополнительным условиям <search_condition>, то она должна быть вставлена (INSERT) в таблицу target_table так, как определяется в THEN <merge_not_matched>. Инструкция MERGE может иметь только один раздел WHEN NOT MATCHED BY TARGET.

[WHEN NOT MATCHED BY SOURCE [AND <search_condition>] THEN <merge_matched>] определяет действия (UPDATE или DELETE) над строками, которые присутствуют в целевой таблице, но отсутствуют в исходной. Как и в случае с WHEN MATCHED, в одном выражении MERGE может иметь не больше двух разделов WHEN NOT MATCHED BY SOURCE, один из которых определяет изменение данных, другой – удаление.

Рассмотрим пример использования данного оператора. Пусть имеется две таблицы – Sub j и NewSub j (табл. 7.3 и 7.4). В первой таблице пере

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

Таблица 7.3

Таблица Subj

Subjld

SubjTitle

Comment

3_BD

Базы данных

3 курс

4_OS

Операционные системы

4 курс

Таблица 7.4

Таблица NewSubj

Subjld

SubjTitle

OldSubjTitle

2_Inf

Информатика

3_BD

Основы баз данных

Базы данных

Пусть требуется согласовать содержимое таблиц таким образом, что если имеющемуся в таблице Subj курсу в таблице NewSubj дано новое название, то мы обновляем только название, а если в NewSubj появляется новый (по значению Subj Id) курс, мы его добавляем в Subj. Это можно сделать следующим выражением:

MERGE INTO Subj USING NewSubj ON Subj.SubjId = NewSubj.SubjId WHEN MATCHED AND

Subj.SubjTitle <> NewSubj.SubjTitle THEN UPDATE SET Subj.SubjTitle = NewSubj.SubjTitle WHEN NOT MATCHED BY TARGET THEN INSERT (SubjId, SubjTitle)

VALUES (NewSubj.SubjId, NewSubj.SubjTitle);

После выполнения данного выражения в таблице Subj будут значения, представленные в табл. 7.5. Нужно отметить, что аналогичный результат можно получить, последовательно выполнив запросы на обновление (UPDATE) и добавление (INSERT) требуемых данных. Однако с точки зрения производительности это будет менее эффективным решением.

Таблица 7.5

Таблица Subj после преобразований

Subjld

SubjTitle

Comment

2_Inf

Информатика

NULL

3_BD

Основы баз данных

3 курс

4_OS

Операционные системы

4 курс

 
< Пред   СОДЕРЖАНИЕ     След >