Дейт К. Д27 Руководство по реляционной субд db2/ Пер с англ и предисл. М. Р. Когаловского
| Вид материала | Руководство |
- Рич Р. К. Политология. Методы исследования: Пер с англ. / Предисл. А. К. Соколова, 6313.17kb.
- Рич Р. К. Политология. Методы исследования: Пер с англ. / Предисл. А. К. Соколова, 6313.29kb.
- Сорокин П. А. С 65 Человек. Цивилизация. Общество / Общ ред., сост и предисл., 11452.51kb.
- The guilford press, 6075kb.
- The guilford press, 6075.4kb.
- Курс лекций "Базы данных и субд" Ульянов В. С. Лекция. Целостная часть реляционной, 213.79kb.
- Дэвид Дайчес, 1633.42kb.
- Mathematics and the search for knowledge morris kline, 498.28kb.
- Лекция №1: Стандарты языка sql, 1420.56kb.
- Курс лекций "Базы данных и субд" Ульянов В. С. Лекция. Манипулирование реляционными, 276.31kb.
ГЛАВА 5
МАНИПУЛИРОВАНИЕ ДАННЫМИ II:
ДАЛЬНЕЙШИЕ ОПЕРАЦИИ ВЫБОРКИ ДАННЫХ
5.1. ВВЕДЕНИЕ
В этой главе мы завершаем обсуждение предложения SELECT языка SQL. План этой главы следующий:
— В разделе 5.2 вводится понятие подзапроса (Subquery) или вложенного предложения SELECT. Представляет исторический интерес тот факт, что именно возможность вкладывать одно предложение SELECT внутрь другого первоначально послужила мотивировкой использования прилагательного «структуризованный» в названии языка «структуризованный язык запросов» (Structured Query Language—SQL). Однако более поздние дополнения к языку привели к тому, что сами по себе вложенные предложения SELECT стали значительно менее важными.
— В разделе 5.3 рассматривается квантор существования EXISTS (существует), который вместе с соединением расценивается, по мнению автора, как одна из наиболее важных и фундаментальных, хотя, может быть, и не самых легких для использования, возможностей полного языка SQL.
— В разделе 5.4 обсуждаются стандартные функции COUNT (число значений), SUM (сумма), AVG (среднее) и т. п. В нем описывается, в частности, использование в связи с этими функциями фраз GROUP BY (группировать по) и HAVING (имея).
— В разделе 5.5 обсуждается оператор UNION (объединение).
— Для того чтобы попытаться связать воедино ряд идей, введенных в данной и предыдущей главах, в разделе 5.6 представлен пример весьма сложного предложения SELECT и принципиально показано, каким образом такое предложение могло бы обрабатываться системой DB2.
Эта глава, как нетрудно видеть, довольно большая, и у читателя может возникнуть желание опустить при первом чтении наиболее сложные ее части. Однако при первом просмотре главы необходимо прочесть по крайней мере первый параграф каждого раздела. Одна из причин большого размера данной главы заключается в большой избыточности языка SQL в том смысле, что он часто предоставляет несколько различных способов формулировки одного и того же запроса. Поскольку мы пытаемся дать достаточно исчерпывающее представление этого языка, материалы данной главы по необходимости также в некоторой степени избыточны.
Необходимо сделать еще одно заключительное вводное замечание, которое может быть не совсем понятным пока не будет прочитана вся глава. Несмотря на то, что наша задача состоит в исчерпывающем обсуждении языка, в данную главу намеренно не включено какое-либо детальное описание вариантов ANY (любой) и ALL (все) операторов сравнения (>ANY,=ALL и т. д.), Читателя, которому необходимо такое детальное описание, отсылаем к фирменному руководству по системе. Причина исключения указанных операторов из этой книги состоит в том, что они совершенно излишни. Нет такого запроса, сформулированного с их использованием, который нельзя было бы в равной степени хорошо, а на самом деле лучше, сформулировать, используя конструкцию EXISTS (существует). Более того, они запутывают и, по мнению автора, порождают потенциальную опасность ошибок. Например, корректное предложение:
SELECT S. НОМЕР_ПОСТАВЩИКА
FROM S
WHERE S. ГОРОД = ANY(SELECT P. ГОРОД FROM P);
вовсе не осуществляет выборки номеров поставщиков, находящихся в городах, не совпадающих с любыми городами, где хранятся детали. Вместо этого оно производит выборку номеров поставщиков таких, что город, в котором размещен каждый из них, не совпадает с каким-нибудь городом, где хранятся детали. Эквивалентная формулировка с помощью EXISTS дает ясную корректную интерпретацию:
SELECT S. НОМЕР_ПОСТАВЩИКА
FROM S
WHERE EXISTS (SELECT P. ГОРОД
FROM P
WHERE P. ГОРОД = S. ГОРОД);
(«выдать номера поставщиков таких, что существует некоторый город хранения деталей, который отличается от города, где находится данный поставщик»). Естественная интуитивная интерпретация =ANY как «не совпадает с любыми» и некорректна и весьма обманчива. Подобная критика относится ко всем операторам, использующим ANY и ALL.
5.2. ПОДЗАПРОСЫ
В этом разделе обсуждаются подзапросы или вложенные предложения SELECT. Говоря нестрого, подзапрос представляет собой выражение SELECT-FROM-WHERE, которое вложено в другое такое предложение 13'. Обычно подзапросы используются для представления множества значений, исследование которых должно осуществляться в каком-либо предикате IN, как иллюстрируется в следующем примере.
5.2.1. ПРОСТОЙ ПОДЗАПРОС
Выдать фамилии поставщиков, которые поставляют деталь Р2.
SELECT ФАМИЛИЯ
FROM S
WHERE НОМЕР_ПОСТАВЩИКА IN
(SELECT НОМЕР_ПОСТАВЩИКА
FROM SP
WHERE НОМЕР_ДЕТАЛИ = 'P2');
Результат:
ФАМИЛИЯ
Смит
Джонс
Блейк
Кларк
Пояснение. При обработке полного запроса система обрабатывает прежде всего вложенный подзапрос. Этот подзапрос возвращает множество номеров поставщиков, которые поставляют деталь P2, а именно множество ('S1', 'S2', 'S3', 'S4'). Поэтому первоначальный запрос эквивалентен следующему простому запросу:
SELECT ФАМИЛИЯ FROM S
WHERE НОМЕР_ПОСТАВЩИКА IN ('S1'.'S2','S3','S4');
и, следовательно, получаем приведенный ранее результат.
Неявное уточнение фамилии в этом примере требует дополнительного обсуждения. Заметим, в частности, что «НОМЕР-ПОСТАВЩИКА» слева от IN неявным образом уточняется именем таблицы S, в то время как «НОМЕР-ПОСТАВЩИКА» в подзапросе неявно уточняется именем таблицы SP. Справедливо следующее общее правило: предполагается, что неуточненное имя поля должно уточняться именем таблицы (или псевдонимом таблицы — см. примеры 5.2.3–5.2.5 ниже), указанным в той фразе FROM, которая является непосредственной частью того же самого запроса или подзапроса. В случае поля НОМЕР-ПОСТАВЩИКА слева от IN этой фразой является «FROM S», а в случае поля НОМЕР-ПОСТАВЩИКА в подзапросе—это фраза «FROM SP». Для большей ясности повторим первоначальный запрос с явно указанными предполагаемыми уточнениями:
SELECT S. ФАМИЛИЯ
FROM S
WHERE S. НОМЕР_ПОСТАВЩИКА IN
(SELECT SP. НОМЕР_ПОСТАВЩИКА
FROM SP
WHERE SP. НОМЕР_ДЕТАЛИ = 'P2');
Неявные уточнения всегда можно отвергнуть путем задания явных уточнений. Это демонстрируется ниже в примерах 5.2.3–5.2.5.
Прежде чем перейти к нашему следующему примеру подзапроса, необходимо отметить еще один важный момент. Первоначальная задача — «Выдать фамилии поставщиков, которые поставляют деталь P2» — может быть эквивалентным образом выражена как запрос с использованием соединения:
SELECT S. ФАМИЛИЯ
FROM S, SP
WHERE S. НОМЕР_ПОСТАВЩИКА = SP. НОМЕР_ПОСТАВЩИКА
AND SP. НОМЕР_ДЕТАЛИ = 'P2';
Пояснение. Соединение S и SP по номерам поставщиков представляет собой таблицу из 12 строк (по одной строке для каждой строки SP), каждая из которых состоит из соответствующей строки SP, дополненной значениями ФАМИЛИЯ, СОСТОЯНИЕ и ГОРОД для поставщика, указываемого значением НОМЕР-ПОСТАВЩИКА в этой строке. Из этих 12 строк только четыре относятся к детали P2. Окончательный результат получается, таким образом, выделением значения ФАМИЛИЯ из этих четырех строк.
Обе формулировки первоначального запроса, одна из которых использует подзапрос, а другая — соединение, в равной степени корректны. Вопрос о том, какой из этих формулировок отдать предпочтение,— исключительно дело вкуса данного пользователя.
5.2.2. ПОДЗАПРОС С НЕСКОЛЬКИМИ УРОВНЯМИ ВЛОЖЕННОСТИ
Выдать фамилии поставщиков, которые поставляют по крайней мере одну красную деталь.
SELECT ФАМИЛИЯ
FROM S
WHERE НОМЕР_ПОСТАВЩИКА IN
(SELECT НОМЕР_ПОСТАВЩИКА
FROM SP
WHERE НОМЕР_ДЕТАЛИ IN
(SELECT НОМЕР_ДЕТАЛИ
FROM P
WHERE ЦВЕТ = 'Красный'));
Результат:
ФАМИЛИЯ
Смит
Джонс
Кларк
Пояснение. Результатом самого внутреннего подзапроса является множество ('Р1, 'Р4', 'Р6'). Подзапрос следующего уровня в свою очередь дает в результате множество ('S1', 'S2', 'S4'). Последний, самый внешний SELECT, вычисляет приведенный выше окончательный результат. Вообще допускается любая глубина вложенности подзапросов.
Для того чтобы убедиться в Вашем понимании этого примера, попытайтесь выполнить следующие упражнения:
а) Перепишите данный запрос так, чтобы все уточнения имен были указаны явным образом.
б) Напишите эквивалентную формулировку этого же запроса с использованием соединения.
5.2.3. КОРРЕЛИРОВАННЫЙ ПОДЗАПРОС
Выдать фамилии поставщиков, которые поставляют деталь P2. Этот пример уже рассматривался в п. 5.2.1. Однако для иллюстрации проблемы, рассматриваемой в данном разделе, приведем иное решение этой задачи.
SELECT ФАМИЛИЯ
FROM S
WHERE 'P2' IN
(SELECT НОМЕР_ДЕТАЛИ
FROM SP
WHERE НОМЕР_ПОСТАВЩИКА =
S. НОМЕР_ПОСТАВЩИКА);
Пояснение. В последней строке приведенного запроса неуточненная ссылка на НОМЕР-ПОСТАВЩИКА уточняется неявным образом именем таблицы SP. Другая ссылка явно уточняется именем таблицы S. Этот пример отличается от предыдущих тем, что внутренний подзапрос не может быть обработан раз навсегда прежде, чем будет обрабатываться внешний запрос, поскольку этот внутренний подзапрос зависит от переменной, а именно от S.HOMEP_ПОСТАВЩИКА, значение которой изменяется по мере того, как система проверяет различные строки таблицы S. Следовательно, с концептуальной точки зрения обработка осуществляется следующим образом.
а) Система проверяет первую строку таблицы S. Предположим, что это строка поставщика «S1». Тогда переменная S.НОМЕР_ПОСТАВЩИКА в данный момент имеет значение 'S1', и система обрабатывает внутренний запрос
(SELECT НОМЕР_ДЕТАЛИ
FROM SP
WHERE НОМЕР_ПОСТАВЩИКА ='S1'),
получая в результате множество ('Р1, 'P2', 'РЗ', 'Р4', 'Р5', 'Р6'). Теперь она может завершить обработку для S1. Выборка значения ФАМИЛИЯ для S1, а именно Смит, будет произведена тогда и только тогда, когда 'Р2' принадлежит этому множеству, что, очевидно, справедливо.
б) Далее система будет повторять обработку такого рода для следующего поставщика и т. д. до тех пор, пока не будут рассмотрены все строки таблицы S.
Такой подзапрос, как в этом примере, называется коррелированным. Коррелированный подзапрос — это такой подзапрос, результат которого зависит от некоторой переменной. Эта переменная принимает свое значение в некотором внешнем запросе. Обработка такого подзапроса, следовательно, должна повторяться для каждого значения переменной в запросе, а не выполняться раз навсегда. Далее будет приведен другой пример коррелированного подзапроса (см. п. 5.2.5). Несколько других примеров приведено в разделах 5.3 и 5.4.
Для того чтобы сделать более ясной связь коррелированных подзапросов с внешними запросами, некоторые пользователи любят вводить псевдонимы (если требуется освежить в памяти вопрос относительно псевдонимов, см. пример 4.3.6 из главы 4). Например:
SELECT SX.ФАМИЛИЯ
FROM S SX
WHERE 'P2' IN
(SELECT НОМЕР_ДЕТАЛИ
FROM SP
WHERE НОМЕР_ПОСТАВЩИКА=
SX.НОМЕР_ПОСТАВЩИКА);
В этом примере псевдонимом является имя SX, введенное во фразе FROM как альтернативное имя таблицы S и используемое далее в качестве явного уточнителя во фразе WHERE подзапроса, а также во фразе SELECT внешнего запроса. Действие приведенного выше полного предложения можно теперь описать более понятно и более точно следующим образом:
— SX — это переменная, областью определения которой является множество записей таблицы S, т. е. переменная, представляющая в любой заданный момент времени некоторую запись таблицы S.
— Поочередно для каждого возможного значения SX выполнить следующее:
+ вычислить подзапрос и получить множество номеров деталей, например Р;
+ добавить к результирующему множеству значение SX.ФАМИЛИЯ, если и только если P2 принадлежит множеству Р.
В предыдущем варианте этого запроса символ «S» в действительности выполнял две различные функции. Он обозначал, конечно, саму базовую таблицу, а также переменную, которая определена на множестве записей этой базовой таблицы. Как уже указывалось, многие считают более ясным использование двух различных символов для того, чтобы различать эти две различные функции.
Введение псевдонима никогда не является ошибкой, а иногда оно необходимо (см. ниже пример 5.2.5).
5.2.4. СЛУЧАЙ ИСПОЛЬЗОВАНИЯ ОДНОЙ И ТОЙ ЖЕ ТАБЛИЦЫ В ПОДЗАПРОСЕ И ВНЕШНЕМ ЗАПРОСЕ
Выдать номера поставщиков, которые поставляют по крайней мере одну деталь, поставляемую поставщиком S2.
SELECT DISTINCT НОМЕР_ПОСТАВЩИКА
FROM SP
WHERE НОМЕР_ДЕТАЛИ IN
(SELECT НОМЕР_ДЕТАЛИ
FROM SP
WHERE НОМЕР_ПОСТАВЩИКА = 'S2');
Результат:
-
НОМЕР_ПОСТАВЩИКА
S1
S2
S3
S4
Отметим здесь, что ссылка на SP в подзапросе означает не то же самое, что ссылка на SP во внешнем запросе. В действительности, два имени SP обозначают различные переменные. Чтобы этот факт стал явным, можно использовать псевдонимы:
SELECT DISTINCT SPX. НОМЕР_ПОСТАВЩИКА
FROM SP SPX
WHERE SPX. НОМЕР_ДЕТАЛИ IN
(SELECT SPY. НОМЕР_ДЕТАЛИ
FROM SP SPY
WHERE SPY. НОМЕР_ПОСТАВЩИКА ='S2');
Эквивалентный запрос с использованием соединения имеет вид;
SELECT DISTINCT SPX. НОМЕР_ПОСТАВЩИКА
FROM SP SPX, SP SPY
WHERE SPX. НОМЕР_ДЕТАЛИ = SPY. НОМЕР_ДЕТАЛИ
AND SPY. НОМЕР_ ПОСТАВЩИКА = 'S2';
5.2.5. СЛУЧАЙ, КОГДА В КОРРЕЛИРОВАННОМ И ВНЕШНЕМ ЗАПРОСЕ ИСПОЛЬЗУЕТСЯ ОДНА И ТА ЖЕ ТАБЛИЦА
Выдать номера всех деталей, поставляемых более чем одним поставщиком. (Другое решение этой задачи дается позднее в примере 5.4.9):
SELECT DISTINCT SPX. НОМЕР_ДЕТАЛИ
FROM SP SPX
WHERE SPX. НОМЕР_ДЕТАЛИ IN
(SELECT SPY. НОМЕР_ДЕТАЛИ
FROM SP SPY
WHERE SPY. НОМЕР_ПОСТАВЩИКА
= SPX. НОМЕР_ПОСТАВЩИКА);
Результат:
-
НОМЕР_ДЕТАЛИ
P1
P2
P4
P5
Действие этого запроса можно пояснить следующим образом. «Поочередно для каждой строки таблицы SP, скажем SPX, выделить значение НОМЕР_ДЕТАЛИ, если и только если это значение входит в некоторую строку, скажем SPY, таблицы SP, значение столбца НОМЕР_ПОСТАВЩИКА в которой не является его значением в строке SPX». Заметим, что в этой формулировке должен быть использован по крайней мере один псевдоним — либо SPX, либо SPY, но не они оба, может быть заменен просто на SP.
5.2.6. ПОДЗАПРОС С ОПЕРАТОРОМ СРАВНЕНИЯ, ОТЛИЧНЫМ ОТ IN
Выдать номера поставщиков, находящихся в том же городе, что и поставщик S1.
SELECT НОМЕР_ПОСТАВЩИКА
FROM S
WHERE ГОРОД =
(SELECT ГОРОД
FROM S
WHERE НОМЕР_ПОСТАВЩИКА = 'S1');
Результат:
-
НОМЕР_ПОСТАВЩИКА
S1
S4
Иногда пользователь может знать, что заданный подзапрос должен возвратить в точности одно значение, как в рассматриваемом примере. В таком случае можно использовать вместо обычного IN более простой оператор сравнения (например, =, > и т. д.). Однако, если подзапрос возвращает более одного значения и не используется оператор IN, будет возникать ошибка. Ошибка не возникнет, если подзапрос не возвратит вообще ни одного значения. При этом сравнение интерпретируется в точности так, как если бы подзапрос возвратил неопределенное значение. Иными словами, если х — переменная, то сравнение
х простой—оператор—сравнения (подзапрос),
где «подзапрос» возвращает пустое множество, имеет значение истинности не истина или ложь, а неизвестно (см. главу 4, пример 4.2.10).
Нужно отметить, что сравнение в предыдущем примере должно быть записано именно так, как показано — подзапрос должен следовать за оператором сравнения. Иначе говоря, следующая запись запроса некорректна:
SELECT НОМЕР_ПОСТАВЩИКА
FROM S
WHERE (SELECT ГОРОД
FROM S
WHERE НОМЕР_ПОСТАВЩИКА = 'S1') = ГОРОД;
Более того, подзапрос не может включать фраз GROUP BY или HAVING, если он используется с простым оператором сравнения, например с =, > и т. д.