Базы Данных - Сибилев, 2007
.pdf211
7.2.13Предложение ORDER BY
7.2.14Предложение ORDER BY используется для сортировки выходной таблицы запроса. Оно всегда обрабатывается последним.
Столбцы выходной таблицы, по значениям которых должна быть выполнена сортировка, могут быть указаны либо именами, либо порядко-
выми номерами в целевом списке. Последний способ ссылки необходим,
если в целевом списке присутствуют выражения, для которых не указаны псевдонимы. Строки выводятся в порядке сортировки значений указанных столбцов. При выполнении сортировки левый столбец имеет высший при-
оритет по отношению к правому, т.е. строки с одинаковыми значениями левого столбца будут собраны в одну группу, упорядоченную по значени-
ям правого столбца. По умолчанию используется параметр сортировки
ASC (по возрастанию).
7.2.15 Операторы UNION, INTERSECT и EXCEPT
Эти операторы реализуют соответственно операции объединения,
пересечения и разности РА [5, с. 73]. Они используют в качестве аргумен-
тов операторы SELECT без концевого ограничителя ‘;’ и предложения
ORDER BY в соответствии со следующим синтаксисом:
запрос_А { UNION | INTERSECT | EXCEPT }
[ ALL ] [ CORRESPONDING [ BY (имя_столбца.,..) ]
]
запрос_В ;
Если не указан параметр CORRESPONDING, то запросы-операнды должны иметь эквивалентные целевые списки, то есть количество столб-
цов должно быть одинаковым, и столбцы с одинаковыми порядковыми номерами должны быть сравнимы. Совпадения имён соответственных столбцов не требуется.
Параметр CORRESPONDING используется, если операнды содер-
жат одноимённые сравнимые столбцы. Их порядковые номера могут не совпадать, и каждый из операндов может содержать и другие столбцы. В
212
результат операции в любом случае войдут только одноимённые сравни-
мые столбцы. Таким образом, CORRESPONDING выполняет SQL-
проекцию операндов на эти столбцы перед выполнением соответствующей бинарной операции.
Если не указан параметр BY, то в результат операции войдут все од-
ноимённые столбцы операндов в том порядке, в каком они расположены в первом. В противном случае результат будет содержать только перечис-
ленные в списке столбцы в порядке следования имён.
Если параметр ALL не указан, то результат любой из рассматривае-
мых операций не будет содержать дубликатов строк, т.е. будет множест-
вом в точном смысле. Использование ALL гарантирует сохранение дубли-
катов в результате, если они имеются в операндах.
Если m – число дубликатов некоторой строки S в таблице A, и n –
число дубликатов той же строки в таблице B, то количество дубликатов в результате операции определяется следующими правилами:
Операция |
Число дубликатов |
|
|
TABLE А UNION ALL TABLE В25 |
m + n |
|
|
TABLE А INTERSECT ALL TABLE В |
MIN (m, n) |
|
|
TABLE А EXCEPT ALL TABLE В |
если m > n, то m – n, иначе 0 |
|
|
Операции UNION, INTERSECT и EXCEPT (без параметра ALL)
обладают всеми свойствами реляционных операций объединения, пересе-
чения и разности. Их можно использовать для построения табличных вы-
ражений. Порядок выполнения операций в выражениях указывается с по-
мощью круглых скобок.
При записи сложных выражений следует обратить особое внимание на порядок выполнения операций с параметром ALL. В этой части стан-
дарт содержит неоднозначности. Например, неизвестно, как система долж-
на интерпретировать выражение
213
TABLE А UNION ALL TABLE В UNION TABLE C;
Если так:
(TABLE А UNION ALL TABLE В) UNION TABLE C;
то результат не будет содержать дубликатов строк, а если так:
TABLE А UNION ALL (TABLE В UNION TABLE C);
то в результат войдут все экземпляры строк, входящих в А. Если такие же строки есть в объединении B и C (не содержащем дубликатов!), то и они будут представлены.
Если результат табличного выражения, содержащего операторы
UNION, INTERSECT и EXCEPT, должен быть упорядочен, то предложе-
ние ORDER BY следует указать непосредственно перед концевым ограни-
чителем ‘;’. Будет отсортирована таблица, произведённая выражением, а
не последним оператором SELECT.
Пример 1. С помощью операции объединения можно реализовать внешнее соединение. Вернёмся к примеру 2 из п. 7.2.9. Следующее выра-
жение эквивалентно левому внешнему соединению:
SELECT Snam, Jnam
FROM S, SPJ, J
WHERE S.Snum = SPJ.Snum
AND J.Jnum = SPJ.Jnum
UNION
SELECT Snam, NULL
FROM S
WHERE NOT EXISTS (
SELECT *
FROM SPJ
WHERE S.Snum = SPJ.Snum
);
Первый оператор SELECT произведёт следующую таблицу:
25 TABLE А – разрешённая стандартом форма записи оператора SELECT * FROM A;
|
214 |
|
|
Snam |
Jnam |
Иван |
дисплей |
Иван |
дисплей |
Иван |
процессор |
Николай |
мышь |
Николай |
принтер |
Григорий |
джойстик |
Григорий |
винчестер |
Петр |
процессор |
|
|
Константин |
джойстик |
Иван |
винчестер |
Иван |
винчестер |
Сергей |
клавиатура |
Сергей |
процессор |
Сергей |
клавиатура |
Владимир |
процессор |
Владимир |
клавиатура |
Владимир |
клавиатура |
Владимир |
клавиатура |
|
|
Владимир |
клавиатура |
Второй оператор создаст таблицу, состоящую также из двух столб-
цов. Первый столбец будет содержать значения имён поставщиков, не вы-
полнивших ни одной поставки, а второй (безымянный) будет заполнен зна-
чениями NULL во всех строках.26
Snam
Егор NULL
Результат объединения содержит в единственном экземпляре каж-
дую строку первой таблицы и единственную строку второй:
|
|
Владимир |
клавиатура |
Владимир |
процессор |
Григорий |
винчестер |
Григорий |
джойстик |
Егор |
NULL |
Иван |
винчестер |
Иван |
процессор |
Константин |
джойстик |
Николай |
мышь |
Николай |
принтер |
Петр |
процессор |
Иван |
дисплей |
Сергей |
Клавиатура |
26 Эта таблица не может содержать дубликатов строк. Почему?
215
Сергей Процессор
Пример 2. Получить номера поставщиков, выполняющих поставки для изделий, производимых в Томске и Яе. Результат упорядочить по име-
нам.
SELECT S.Snum, Snam
FROM S JOIN (J JOIN SPJ) USING (Snum)
WHERE J.Ci = 'Томск'
INTERSECT
SELECT S.Snum, Snam
FROM S JOIN (J JOIN SPJ) USING (Snum)
WHERE J.Ci = 'Яя'
ORDER BY 2;
S1 Иван
S8 Владимир
Попытайтесь сформулировать этот запрос без оператора
INTERSECT.
Этим мы завершим обзор оператора SELECT. В заключение сделаем два замечания.
Во-первых, мы обсудили здесь возможности оператора очень бегло.
Для того чтобы получить о нём более полное представление, необходимо решить задачи, предлагаемые в приложении Б. Для каждого запроса поста-
райтесь записать несколько различных формул.
Во-вторых, реальные СУБД поддерживают не все определения стан-
дарта. Например, диалект SQL MS Access не допускает использования подзапросов в предложении FROM, поддерживает единственный вид встроенной операции соединения – соединение по условию – и только ти-
пы INNER, LEFT и RIGHT, не поддерживает операции INTERSECT и EXCEPT, использует определение предиката LIKE, отличное от стан-
дартного, и имеет ещё ряд особенностей. Это следует иметь в виду, прове-
ряя правильность своих формулировок в конкретной операционной среде.
216
7.2.16 Операторы обновления данных
Оператор SELECT, как мы видели, может использовать в качестве источника данных любую совокупность именованных таблиц. Существует единственное ограничение: ID, издавший оператор, должен иметь приви-
легии на просмотр всех таблиц источника. В отличие от этого операторы обновления данных всегда используют в качестве приёмника данных одну
именованную таблицу – базовую или представление. Если приёмником яв-
ляется представление, то оно должно быть обновляемым (см. п. 7.4.3). В
любом случае реально обновляются только базовые таблицы.
Порядок исполнения операторов обновления следующий. Приняв оператор, система считывает из ФБД в свой рабочий буфер обновляемую базовую таблицу. Все изменения данных выполняются в этой копии. Об-
новлённая таблица переносится из рабочего буфера в ФБД, только если транзакция, содержащая оператор обновления, завершилась успешно. В
противном случае рабочий буфер очищается, и никаких изменений в ФБД не происходит.
Действие операторов ограничено не только привилегиями пользова-
теля, но и правилами целостности данных, объявленными в определениях объектов схемы. Исполняющая система автоматически проверяет все ог-
раничения, которые могут быть нарушены вследствие предложенных из-
менений данных. Если хотя бы одно из них нарушается, операция обнов-
ления будет отвергнута. Если правилами целостности в связи с предло-
женными изменениями предусмотрены каскадные обновления других объ-
ектов, они выполняются автоматически. Порядок выполнения этих не за-
данных явно обновлений такой же.
Рассмотрим теперь синтаксис операторов обновления.
Оператор INSERT в общем случае добавляет в указанную таблицу группу строк. Группа может быть либо результатом запроса, либо явно за-
217
данным списком строк – конструктором значений таблицы.27 Результат за-
проса или строки конструктора могут содержать значения части столбцов обновляемой таблицы. В этом случае имена этих столбцов должны быть указаны списком имён столбцов.
Вот синтаксическая диаграмма оператора, соответствующая опреде-
лению SQL2:
INSERT INTO имя_таблицы [ (имя_столбца.,..) ]
запрос
| конструктор_значений_таблицы
| { DEFAULT VALUES };
Здесь имя_столбца – имя столбца обновляемой таблицы. Список имён можно не указывать, если добавляемые строки содержат значения для всех столбцов таблицы. Столбцам, не вошедшим в список, в новых строках будут присвоены значения по умолчанию, если они определены,
либо NULL-значения. Если для какого-либо столбца это невозможно (на-
пример, столбец определён как NOT NULL, а значение по умолчанию не задано), операция будет прервана.
запрос – любой оператор SELECT, производящий таблицу с числом столбцов, равным числу имён в списке или (если он не задан) числу столбцов приёмника данных. Столбцы производной таблицы запроса должны совпадать по типу с соответствующими по порядку следования столбцами из списка имён или из схемы обновляемой таблицы.
Пример 1. Пусть в схеме определена временная таблица TQt, в
которую приложение в процессе исполнения загружает данные об общих объёмах поставок деталей. Таблица содержит два столбца: Pnum – номер детали и Sumqt – суммарный объём поставок. Следующий оператор запол-
нит эту таблицу строками, содержащими обработанные данные из таблицы
SPJ:
27 SQL1 разрешал таким способом добавлять только одну строку.
218
INSERT INTO TQt
SELECT Pnum, SUM(Qt)
FROM SPJ
GROUP BY Pnum;
Вводимые строки могут быть заданы явно с помощью следующей конструкции:
конструктор_значений_таблицы ::=
VALUES конструктор_значений_строки.,..
конструктор_значений_строки ::=
элемент_конструктора
| (элемент_конструктора.,..)
| подзапрос_строки элемент_конструктора ::=
выражение_для вычисления_значения
| NULL | DEFAULT
Простейшим выражением является литерал. Однако можно исполь-
зовать и скалярные подзапросы, и переменные, передаваемые приложе-
ниями или системные.
Пример 2. Добавить в таблицу S строку (‘S12’, ‘Алексей’, NULL, ‘Тайга’).
INSERT INTO S
VALUES (‘S12’, ‘Алексей’, NULL, ‘Тайга’);
Исполняя этот оператор, система проверит, существует ли значение
‘S12’ в текущем множестве значений столбца Snum и допустимо ли значе-
ние NULL для столбца St.
Точно такие же изменения в таблицу внесёт оператор
INSERT INTO S (Snam, Ci, Snum)
VALUES (‘Алексей’, ‘Тайга’, ‘S12’);
219
Обратите внимание на то, что порядок следования имён в списке не совпадает с порядком столбцов в схеме таблицы, но порядок следования значений в конструкторе значений строки совпадает с порядком следова-
ния имён в списке.
Пример 3. Внести в таблицу J строки:
('J11’, ‘принтер’, ‘Тайга’), ('J12’, ‘плоттер’, ‘Яя’),
('J13’, ‘сканер’, NULL)
INSERT INTO J
VALUES ('J12’, ‘плоттер’, ‘Яя’), ('J11’, ‘принтер’, ‘Тайга’), ('J13’, ‘сканер’, NULL);
Оператор UPDATE изменяет значения одного или нескольких столбцов в подмножестве строк базовой таблицы. Вот его синтаксическая диаграмма:
UPDATE имя_таблицы
SET {имя_столбца =
выражение_для_вычисления_значения
| NULL
| DEFAULT }}.,..
[ WHERE предикат ];
Здесь выражение может содержать литералы, имена переменных,
ссылки на любые столбцы обновляемой таблицы, скалярные подзапросы.
Тип выражения должен соответствовать типу обновляемого столбца. В ка-
честве альтернативы выражению можно явно указывать значение NULL
или «значение по умолчанию» DEFAULT, заданное при определении столбца (см. п. 7.3.5).
Если оператор не содержит предложения WHERE, то указанные в предложении SET значения присваиваются столбцам во всех строках таб-
220
лицы. Предложение WHERE ограничивает действие оператора подмноже-
ством строк, на которых предикат принимает значение TRUE.
Пример 4. Изменить единицу измерения веса деталей с грамма на
килограмм.
UPDATE P
SET We = We/1000;
Будут изменены значения столбца We во всех строках таблицы P.
Пример 5. Изменить название производимого в Яе изделия
‘дисплей’ на ‘«Электрон»’.
UPDATE J
SET Jnam = ‘«Электрон»’
WHERE Jnam = ‘дисплей’ AND Ci = ‘Яя’;
Пример 5. Увеличить вдвое статусы тех поставщиков, чьи суммар-
ные объёмы поставок детали P1 превосходят 2000.
UPDATE S
SET St = St*2
WHERE 2000 < SELECT SUM(Qt)
FROM SPJ
WHERE SPJ.Pnum = ‘P1’
AND SPJ.Snum = S.Snum;
Оператор DELETE удаляет подмножество строк из базовой табли-
цы.
DELETE FROM имя_таблицы
[ WHERE предикат ];
Предложение WHERE определяет подмножество удаляемых строк.
Если оно отсутствует, то удаляются все строки таблицы. Определение таблицы в схеме сохраняется, т.е. в базе данных остаётся пустая табли-
ца.
Пример 7. Очистить таблицу TQt.