Учебное пособие 800353
.pdfР1 для какого-либо изделия в количестве, большем среднего объема поставок детали Р1 для этого изделия.
Объединение
25.Постройте упорядоченный список всех городов, в которых размещаются, по крайней мере, один поставщик, деталь или изделие.
26.Приведите результат следующего предложения
SELECT:
SELECT Р.ЦВЕТ
FROM Р
UNION
SELECT P.ЦВЕТ
FROM Р;
8.4 Операции обновления
Введение
В двух последних частях весьма подробно было рассмотрено предложение выборки данных SELECT языка SQL. Обратим теперь наше внимание на предложения обновления данных UPDATE (обновить), DELETE (удалить) и INSERT (вставить).
Как и предложение SELECT, три предложения обновления данных оперируют не только базовыми таблицами, но и представлениями. Однако не все представления являются обновляемыми. Если пользователь попытается выполнять операцию обновления над необновляемым представлением, система просто отвергнет эту операцию с соответствующим сообщением для пользователя. Предположим, следовательно, для целей данной части, что все таблицы, которые будут обновляться, являются базовыми таблицами.
310
В следующих трех разделах подробно обсуждаются три операции обновления. Синтаксис этих операций следует тому же общему образцу, который был уже показан для операции SELECT. Для удобства в начале соответствующих разделов приводится в общих чертах синтаксис обсуждаемых предложений языка SQL.
Предложение update
Предложение UPDATE имеет следующий общий формат:
UPDATE таблица
SET поле = выражение
[, поле = выражение] … [WHERE предикат];
Все записи в «таблице», которые удовлетворяют «предикату», обновляются в соответствии с присваиваниями «поле = выражение» во фразе SET (установить).
Обновление единственной записи
Изменить цвет детали Р2 на желтый, увеличить ее вес на 5 и установить значение города «неизвестен» (NULL).
UPDATE Р
SET ЦВЕТ = ’Желтый’, ВЕС = ВЕС + 5, ГОРОД = NULL WHERE НОМЕР_ДЕТАЛИ = ’Р2’;
Для каждой записи, которая должна быть обновлена (т. е. для каждой записи, которая удовлетворяет предикату WHERE,. или для всех записей, если фраза WHERE опущена), ссылки во фразе SET на поля этой записи обозначают значения этих полей перед тем, как будет выполнено какое-либо присваивание в этой фразе SET.
311
Обновление множества записей
Удвоить состояние всех поставщиков, находящихся в Лондоне.
UPDATE S
SET СОСТОЯНИЕ = 2*СОСТОЯНИЕ WHERE ГОРОД = ’Лондон’;
Обновление с подзапросом
Установить объем поставок равным нулю для всех поставщиков из Лондона.
UPDATE SP
SET КОЛИЧЕСТВО = 0 WHERE ’Лондон’ =
(SELECT ГОРОД FROM S
WHERE S.НОМЕР_ПОСТАВЩИКА = SP.НОМЕР_ПОСТАВЩИК
А);
Обновление нескольких таблиц
Изменить номер поставщика S2 на S9.
UPDATE S
SET НОМЕР_ПОСТАВЩИКА = ’S9’ WHERE НОМЕР_ПОСТАВЩИКА = ’S2’;
UPDATE SP
SET НОМЕР_ПОСТАВЩИКА = ’S9’ WHERE НОМЕР_ПОСТАВЩИКА = ’S2’;
Невозможно обновить более одной таблицы в единственном запросе. Иными словами, в предложении UPDATE должна специфицироваться в точности одна таблица. Поэтому в данном примере мы сталкиваемся со следующей проблемой целостности (точнее, с проблемой
целостности по ссылкам): база данных становится противоречивой после выполнения первого предложения
312
UPDATE — она включает теперь некоторые поставки, для которых не имеется соответствующей записи о поставщике, и остается в таком состоянии до тех пор, пока не будет выполнено второе предложение UPDATE. Изменение порядка предложений UPDATE, конечно, не решает эту проблему. Поэтому важно обеспечить выполнение обоих эти предложений, а не только одного.
Предложение delete
Предложение DELETE имеет следующий общий формат:
DELETE
FROM таблица [WHERE предикат];
Удаляются все записи в «таблице», которые удовлетворяют «предикату».
Удаление единственной записи
Удалить поставщика S1.
DELETE
FROM S
WHERE НОМЕР_ПОСТАВЩИКА = ’S1’;
И снова, если таблица SP в настоящее время содержит какие-либо поставки для поставщика S1, это удаление нарушит непротиворечивость базы данных.
Удаление множества записей
Удалить всех поставщиков из Лондона.
DELETE
FROM S
WHERE ГОРОД = ’Лондон’;
313
Удаление множества записей
Удалить все поставки.
DELETE
FROM SP;
SP — все еще известная таблица, но она теперь пуста. Удалить все записи — это не уничтожить таблицу (операция
DROP).
Удаление с подзапросом
Удалить все поставки для поставщиков из Лондона.
DELETE
FROM SP
WHERE ’Лондон’ =
(SELECT ГОРОД FROM S
WHERE S.НОМЕР_ПОСТАВЩИКА = SP.НОМЕР_ПОСТАВЩИКА);
Предложение insert
Предложение INSERT имеет следующий общий формат:
INSERT
INTO таблица [(поле [, поле] …)] VALUES (константа [, константа] …);
или:
INSERT
INTO таблица [(поле [, поле] …)] подзапрос;
В первом формате в «таблицу» вставляется строка, имеющая заданные значения для указанных полей, причем 1-я константа в списке констант соответствует 1-му полю в списке полей. Во втором формате вычисляется «подзапрос»; копия результата, представляющего собой, вообще говоря, множество строк, вставляется в «таблицу». При этом 1-й
314
столбец этого результата соответствует 1-му полю в списке полей. В обоих случаях отсутствие списка полей эквивалентно спецификации списка всех полей в таблице.
Вставка единственной записи
Добавить в таблицу Р деталь Р7 (город 'Атенс', вес — 2, название и цвет в настоящее время неизвестны).
INSERT
INTO Р (НОМЕР_ДЕТАЛИ, ГОРОД, ВЕС)
VALUES (’Р7’, ’Атенс’, 2);
Создается новая запись для детали с заданным номером, городом и весом, с неопределенными значениями для названия и цвета. Эти два последних поля не должны быть, конечно, определены как NOT NULL в предложении CREATE TABLE для таблицы Р. Порядок слева — направо, в котором поля указаны в предложении INSERT, не обязательно должен совпадать с порядком слева — направо, в котором поля были специфицированы в предложении CREATE (или ALTER).
Вставка единственной записи с опущенными именами полей
Добавить деталь Р8 в таблицу Р, при этом: название — ’Звездочка’, цвет — ’Розовый’, вес — 14, город — ’Ницца’.
INSERT
INTO Р
VALUES (’Р8’, ’Звездочка’, ’Розовый’, 14, ’Ницца’);
Отсутствие списка полей эквивалентно спецификации списка всех полей в таблице в порядке слева — направо, как они были определены в предложении CREATE (или ALTER). Как и «SELECT * », такая краткая нотация может быть удобной для интерактивного SQL. Она потенциально опасна, однако, во встроенном SQL, т. е. в предложениях SQL, используемых в прикладной программе, в связи с тем, что предполагаемый список полей может изменяться, если для
315
программы заново осуществляется связывание, а определение таблицы было в этом промежутке времени изменено.
Вставка единственной записи
Вставить новую поставку с номером поставщика S20, номером детали Р20 и количеством 1000.
INSERT
INTO SP (НОМЕР_ПОСТАВЩИКА, НОМЕР_ДЕТАЛИ, КОЛИЧЕСТВО)
VALUES (’S20’, ’Р20’, 1000);
Подобно операциям UPDATE и DELETE операция INSERT при отсутствии соответствующего управления также может порождать проблему целостности по ссылкам (см. часть
2).
Вставка множества записей
Для каждой поставляемой детали получить ее номер и общий объем поставок, сохранить результат в базе данных.
CREATE TABLE ВРЕМЕННАЯ
(НОМЕР_ДЕТАЛИ CHAR (6), ОБЪЕМ_ПОСТАВКИ INTEGER);
INSERT
INTO ВРЕМЕННАЯ (НОМЕР_ДЕТАЛИ, ОБЪЕМ_ПОСТАВКИ)
SELECT НОМЕР_ДЕТАЛИ, SUM (КОЛИЧЕСТВО)
FROM SP
GROUP BY НОМЕР_ДЕТАЛИ;
Здесь предложение SELECT выполняется точно так же, как обычно, но результат не возвращается пользователю, а копируется в таблицу ВРЕМЕННАЯ. Теперь с этой копией пользователь может делать все, что он пожелает — делать дальнейшие запросы, печатать и даже обновлять ее. Никакая из этих операций не будет оказывать какого-либо влияния на первоначальные данные. В конечном счете, таблицу
316
ВРЕМЕННАЯ можно будет уничтожить, когда она больше не будет нужна:
DROP TABLE ВРЕМЕННАЯ;
Предыдущий пример очень хорошо показывает, почему свойство замкнутости реляционных систем, является таким важным. Приведенная полная процедура работает именно в связи с тем, что результатом предложения SELECT является другая таблица. Она не работала бы, если бы результат был чем-либо иным, кроме таблицы.
Между прочим, целевая таблица вовсе не обязательно должна быть первоначально пустой для вставки множества записей, хотя в приведенном примере это так. Если таблица не пуста, новые записи просто добавляются к тем, которые уже имеются.
Одно из важных применений INSERT … SELECT — построение так называемого внешнего соединения. Обычное (естественное) соединение двух таблиц не включает в результате строк какой-либо из двух таблиц, для которых нет соответствующих строк в другой таблице. Например, обычное соединение таблиц S и Р по городам не включает какой-либо строки для поставщика S5 или для детали Р3, поскольку в Атенсе не хранится никакая деталь и нет поставщиков, находящихся в Риме. Следовательно, в некотором смысле можно считать, что при обычном соединении теряется информация для таких несоответствующих строк. Однако иногда может потребоваться способность сохранять эту информацию. Рассмотрим следующий пример.
Использование insert ... select для построения внешнего соединения
Для каждого поставщика получить его номер, фамилию, состояние и город вместе с номерами всех поставляемых им деталей. Если данный поставщик не поставляет вообще никаких деталей, то выдать информацию
317
для этого поставщика, оставляя в результате пробелы вместо номера детали.
CREATE TABLE ВНЕШ_СОЕДИНЕНИЕ (НОМЕР_ПОСТАВЩИКА CHAR (5),
ФАМИЛИЯ CHAR (20),
СОСТОЯНИЕ SMALLINT,
ГОРОД CHAR (15),
НОМЕР_ДЕТАЛИ CHAR (6);
INSERT
INTO ВНЕШ_СОЕДИНЕНИЕ
SELECT S.*, SP.НОМЕР_ДЕТАЛИ FROM S, SP
WHERE S.НОМЕР_ПОСТАВЩИКА = SP.НОМЕР_ПОСТАВЩИКА;
INSERT
INTO ВНЕШ_СОЕДИНЕНИЕ
SELECT S.*, ’bb’ FROM S
WHERE NOT EXISTS (SELECT * FROM SP WHERE
SP.НОМЕР_ПОСТАВЩИКА=
S.
НОМЕР_ПОСТАВЩИКА);
Пояснение. Первые двенадцать строк приведенного результата соответствуют первому из двух INSERT … SELECT и представляют собой обычное естественное соединение таблиц S и SP по номерам поставщиков, за исключением того, что не включен столбец КОЛИЧЕСТВО. Последняя строка результата соответствует второму INSERT … SELECT и сохраняет информацию для поставщика S5, который не поставляет никаких деталей. Полный результат представляет собой внешнее соединение таблиц S и SP по номерам поставщиков, в котором опущен столбец КОЛИЧЕСТВО. В противоположность этому обычное соединение называется
318
иногда внутренним соединением.
Заметим, что нужны два отдельных INSERT … SELECT, поскольку подзапрос не может содержать UNION.
Упражнения
Как обычно, все следующие упражнения основаны на базе данных поставщиков-деталей-изделий:
S (НОМЕР_ПОСТАВЩИКА, ФАМИЛИЯ, СОСТОЯНИЕ, ГОРОД)
Р (НОМЕР_ДЕТАЛИ, НАЗВАНИЕ, ЦВЕТ, ВЕС, ГОРОД)
J (НОМЕР_ИЗДЕЛИЯ, НАЗВАНИЕ, ГОРОД)
SPJ (НОМЕР_ПОСТАВЩИКА, НОМЕР_ДЕТАЛИ, НОМЕР_ИЗДЕЛИЯ,
КОЛИЧЕСТВО)
Запишите подходящее предложение INSERT, DELETE или UPDATE для каждой из следующих задач.
1.Измените цвет всех красных деталей на оранжевый.
2.Удалите все изделия, для которых нет поставок
деталей.
3.Увеличьте размер поставки на 10 процентов для всех поставок тех поставщиков, которые поставляют какуюлибо красную деталь.
4.Удалите все изделия из Рима и все соответствующие
поставки.
5.Вставьте в таблицу S нового поставщика S10. Его фамилия и город — ’Уайт’ и ’Нью-Йорк’ соответственно, а состояние еще неизвестно.
6.Постройте таблицу, содержащую список номеров деталей, которые поставляются либо каким-нибудь поставщиком из Лондона, либо для какого-либо изделия в Лондоне.
319