Базы данных. Вводный курс

Сергей Кузнецов

Назад Содержание Вперёд

Лекция 21. Средства манипулирования данными

21.1. Введение

Базы данных, по крайней мере, в приложениях категории OLTP, являются высоко динамичными объектами. В таких приложениях на две операции выборки данных в среднем приходится одна операция обновления содержимого базы данных (добавления новых данных, удаления или модификации существующих данных). Поэтому для пользователей и разработчиков OLTP-приложений средства манипулирования данными по важности находятся на втором месте после средств выборки данных.

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

Лекция состоит из трех основных разделов. В разделе 21.2. Базовые средства манипулирования данными мы обсудим синтаксис и семантику операторов манипулирования данными, полагая, что они действуют над базовыми таблицами. В разделе 21.3. Представления, над которыми возможны операции обновления будет продемонстрировано, что в ряде случаев, специфицированных в стандарте языка SQL, операторы манипулирования данными можно применять к порождаемым таблицам и представлениям с однозначным отображением результатов действия этих операторов на соответствующие базовые таблицы. Раздел 21.4. Операции обновления баз данных и механизм триггеров посвящен механизму триггеров, которые, по существу, представляют собой «хранимые процедуры», автоматически вызываемые при возникновении соответствующих условий. Триггеры не обязательно связываются с действиями, производимыми при манипулировании данных, но, поскольку одно из основных функций этого механизма состоит в поддержании целостности баз данных, как правило, такая связь имеется. Поэтому мы включили обсуждение механизма триггеров в соответствии со стандартом SQL именно в данную лекцию.

21.2. Базовые средства манипулирования данными

К базовым средствам манипулирования данными языка SQL относятся «поисковые» варианты операторов UPDATE и DELETE. Эти варианты называются поисковыми, потому что при задании соответствующей операции задается логическое условие, налагаемое на строки адресуемой оператором таблицы, которые должны быть подвергнуты модификации или удалению. Кроме того, в такую категорию языковых средств входит оператор INSERT, позволяющий добавлять строки в существующие таблицы. Логично начать изложение именно с оператора INSERT, поскольку, для того чтобы можно было что-либо модифицировать в таблицах или удалять из таблиц, нужно, чтобы в таблицах содержались какие-то строки.

21.2.1. Оператор INSERT для вставки строк в существующие таблицы

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

INSERT INTO table_name
     { [ (column_commalist) ] query_expression
     | DEFAULT VALUES

На вид синтаксические правила кажутся очень простыми, пока не вспомнишь, что обозначает синтаксическая категория query_expression (см. подраздел 17.2.1. «Общие синтаксические правила построения скалярных выражений» лекции 17). Даже если ограничиться простейшей составляющей этой конструкции (simple_table), то мы имеем следующие возможности:

simple_table ::= query_specification
     | table_value_constructor
     | TABLE table_name

Вставка всех строк указанной таблицы

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

Чтобы привести пример этого варианта операции INSERT (пример 21.1), предположим, что в базе данных EMP-DEPT-PRO имеется еще одна промежуточная таблица EMP_TEMP, в которой временно хранятся данные о служащих, проходящих испытательный срок. Пусть эта таблица имеет следующий заголовок:

EMP_TEMP:

EMP_NO : EMP_NO

EMP_NAME : VARCHAR

EMP_BDATE : DATE

В таблице EMP_TEMP хранятся не полные сведения о служащих, а именно те, которые требуются на время испытательного срока. Если выполнить операцию

INSERT INTO EMP (EMP_NO, EMP_NAME, EMP_BDATE) TABLE EMP_TEMP;

то в основной таблице EMP появятся строки, соответствующие служащим, проходившим испытательный срок. При этом в столбцах EMP_NO, EMP_NAME, EMP_BDATE этих строк будут содержаться данные, взятые из таблицы EMP_TEMP, а в столбцах EMP_SAL, DEPT_NO, PRO_NO будут находиться значения, определенные для данных столбцов по умолчанию. Конечно, поскольку столбец EMP_NO является первичным ключом таблицы EMP (по всей видимости, и таблицы EMP_TEMP), операция вставки будет успешно выполнена только в том случае, когда ограничение первичного ключа таблицы EMP не будет нарушено (конечно же, требуется выполнение и всех других ограничений целостности, определенных для таблицы EMP).

Вставка явно заданного набора строк

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

table_value_constructor ::= 
    VALUES row_value_constructor_comma_list
row_value_constructor ::= row_value_constructor_element
    | [ ROW ] (row_value_constructor_element_comma_list)
    | row_subquery
row_value_constructor_element ::= value_expression 
    | NULL | DEFAULT

Самый простой пример использования этого варианта оператора вставки состоит в занесении в таблицу EMP явно задаваемых данных о новом служащем (пример 21.2):

INSERT INTO EMP
   ROW (2445, 'Brown', '1985-04-08', 16500.00, 630, 772);

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

INSERT INTO EMP
   ROW ( 2445, DEFAULT, NULL, DEFAULT, NULL, NULL);

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

Если обладать полной информацией об определении таблицы EMP, то формулировку операции примера 21.2.1 можно переписать короче следующим эквивалентным образом (пример 21.2.2):

INSERT INTO EMP (EMP_NO) 2445;

Вспомним теперь, что одной из разновидностей value_expression_primary является scalar_subquery (см. раздел 17.2 «Скалярные выражения» лекции 17). Это означает, что в список элементов конструктора строки могут входить скалярные запросы, т. е. запросы, результат выполнения которых состоит из единственной строки, включающей единственный столбец. Поэтому допустима, например, такая операция вставки (пример 21.3):

INSERT INTO EMP VALUES
           ROW (2445, (SELECT EMP_NAME
                        FROM EMP
                        WHERE EMP_NO = 2555),
                   '1985-04-08',
                   SELECT EMP_SAL
                        FROM EMP
                        WHERE EMP_NO = 2555),
                   NULL, NULL ),
           ROW (2446, (SELECT EMP_NAME
                        FROM EMP
                        WHERE EMP_NO = 2556),
                   '1978-05-09',
                   (SELECT EMP_SAL
                        FROM EMP
                        WHERE EMP_NO = 2556),
                   NULL, NULL );

После выполнения этой операции в таблице EMP появятся две новые строки для служащих с уникальными идентификаторами 2445 и 2446, причем первому из них будет присвоено имя и размер заработной платы служащего с уникальным идентификатором 2555, а второму – аналогичные данные о служащем с уникальным идентификатором 2556.

Вставка строк результата запроса

Наконец, обсудим вариант оператора вставки, когда набор вставляемых строк определяется через спецификацию запроса. Предположим, например, что требуется сохранить в отдельной таблице DEPT_SUMMARY сведения о числе служащих каждого отдела, их максимальной, минимальной и суммарной заработной плате. Пусть таблица DEPT_SUMMARY уже создана и имеет следующийзаголовок¹⁵²⁾:

DEPT_SUMMARY:

DEPT_NO : DEPT_NO

DEPT_EMP_NO : INTEGER

DEPT_MAX_SAL : SALARY

DEPT_MIN_SAL : SALARY

DEPT_TOTAL_SAL : SALARY

Тогда заполнить таблицу можно с помощью следующей операции вставки (пример 21.4):

INSERT INTO DEPT_SUMMARY
   (SELECT DEPT_NO, COUNT(*), MAX (EMP_SAL),
        MIN (EMP_SAL), SUM (EMP_SAL)
     FROM EMP
     GROUP BY DEPT_NO);

21.2.2. Оператор UPDATE для модификации существующих строк в существующих таблицах

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

UPDATE table_name SET update_assignment_commalist
    WHERE conditional_expression
update_assignment ::= column_name =
    { value_expression | DEFAULT | NULL }

Семантика оператора модификации существующих строк определяется следующим образом:

1. для всех строк таблицы с именем table_name вычисляется булевское выражение conditional_expression. Строки, для которых значением этого булевского выражения является true, считаются подлежащими модификации (обозначим множество таких строк через Tm);
2. каждая строка s (s Tm) подвергается модификации таким образом, что значение каждого столбца этой строки, указанного в списке update_assignment_commalist, заменяется значением, указанным в правой части соответствующего элемента спискамодификации¹⁵³⁾. Значения столбцов строки s, не указанные в списке модификации, остаются неизменными.

Приведем примеры операций модификации таблиц.

Пример 21.5. Перевести всех служащих, выполняющих проект с номером 772, в отдел 632 и повысить им заработную плату на 1000 руб.

UPDATE EMP SET DEPT_NO = 632, EMP_SAL = EMP_SAL + 1000.00
   WHERE PRO_NO = 772;

При выполнении данной операции на первом шаге в таблице EMP будут найдены все строки, относящиеся к служащим, которые участвуют в проекте с номером 772. На втором шаге во всех этих строках значение столбца DEPT_NO будет изменено на 632, а к значению столбца EMP_SAL будет прибавлено 1000.00.

Пример 21.6. Для всех служащих, работающих в отделах, заработная плата менеджеров которых превышает 30000 руб., установить размер заработной платы, на 1000 руб. превышающий средний размер заработной платы соответствующего отдела, а номера проектов, в которых участвуют эти служащие, сделать неопределенными.

UPDATE EMP SET EMP_SAL = (SELECT AVG (EMP1_SAL)
     FROM EMP EMP1
     WHERE EMP.DEPT_NO = EMP1.DEPT_NO)
         + 1000.00, PRO_NO = NULL
     WHERE (SELECT EMP1.EMP_SAL
          FROM EMP EMP1, DEPT
          WHERE EMP.DEPT_NO = DEPT.DEPT_NO
             AND DEPT_MNG = EMP1.EMP_NO AND) > 30000.00;

Конечно, если вам больше нравится другой стиль, то запрос, фигурирующий в разделе WHERE, можно переформулировать с использованием вложенного подзапроса (пример 21.6.1).

UPDATE EMP SET EMP_SAL = (SELECT AVG (EMP1_SAL)
     FROM EMP EMP1
     WHERE EMP.DEPT_NO = EMP1.DEPT_NO)
         + 1000.00, PRO_NO = NULL
     WHERE DEPT.NO IN (SELECT DEPT.DEPT_NO
          FROM EMP, DEPT
          WHERE DEPT_MNG = EMP_NO
          AND EMP_SAL > 30000.00);

Эти примеры позволяют понять, насколько богаты возможности оператора UPDATE. В разделе WHERE может содержаться любое условие, допускаемое в операторе выборки, а в элементах списка раздела SET может присутствовать любой вид value_expression, в том числе любой запрос, вырабатывающий одиночное значение (скалярный подзапрос).

21.2.3. Оператор DELETE для удаления строк в существующих таблицах

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

DELETE FROM table_name
     WHERE conditional_expression

В некотором смысле оператор DELETE является частным случаем оператора UPDATE (или, наоборот, действие оператора UPDATE представляет собой комбинацию действий операторов DELETE и INSERT).

Семантика оператора модификации существующих строк определяется следующим образом:

1. для всех строк таблицы с именем table_name вычисляется булевское выражение conditional_expression. Строки, для которых значением этого булевского выражения является true, считаются подлежащими удалению (обозначим множество таких строк через Td);
2. каждая строка s (s Td) удаляется из указанной таблицы.

С целью иллюстрации приведем два примера операции удаления строк.

Пример 21.7. Удалить из таблицы EMP все строки, относящиеся к служащим, которые участвуют в проекте с номером 772.

DELETE FROM EMP WHERE PRO_NO = 772;

Пример 21.8. Удалить из таблицы EMP все строки, относящиеся к служащим, размер заработной платы которых превышает размер заработной платы менеджеров их отделов.

DELETE FROM EMP WHERE EMP_SAL >
     (SELECT EMP1.EMP_SAL
          FROM EMP EMP1, DEPT
          WHERE EMP.DEPT_NO = DEPT.DEPT_NO
             AND DEPT.DEPT.MNG = EMP1.EMP_NO);

Как и в операторе UPDATE, в разделе WHERE оператора DELETE можно использовать любой вид булевского выражения, допустимого в операторе выборки. Поэтому возможности оператора удаления строк ограничены лишь фантазией пользователя.

---

152   Мы не будем приводить полное определение таблицы, включающее требуемые ограничения целостности.

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

Назад Содержание Вперёд