Кириллов В.В., Громов Г.Ю. - Введение в реляционные базы данных. Литература для вузов isbn 9785941577705 в книге рассматриваются основные понятия баз данных и систем управления ими
 Скачать 11.62 Mb.
|
|
6.2.4. Композиция таблиц Для исключения всех столбцов, по которым проводится соединение таблиц, надо создать композицию SELECT Строка, Трапеза, Вид, Блюдо, Основа, Выход, Труд FROM Меню JOIN Блюда ON Меню.Код_блюда = Блюда.Код_блюда JOIN Виды_блюд ON Блюда.Код_вида = Виды_блюд.Код_вида JOIN Трапезы ON Меню.Код_трапезы = Трапезы.Код_трапезы; имеющую вид: СТРОКА ТРАПЕЗА ВИД БЛЮДО ОСНОВА ВЫХОД ТРУД 1 Завтрак Закуска Салат витаминный Овощи 200,0 4 2 Завтрак Закуска Мясо с гарниром Мясо 250,0 3 3 Завтрак Горячее Омлет с луком Яйца 200,0 5 19 Ужин Закуска Драчена Яйца 180,0 4 20 Ужин Закуска Компот Фрукты 200,0 2 21 Ужин Закуска Молочный напиток Молоко 200,0 2 6.2.5. Тета - соединение таблиц В базе данных "COOK" трудно подобрать несложный пример, иллюстрирующий тета-соединение таблиц. Поэтому сконструируем такой надуманный запрос: SELECT Виды_блюд.*, Трапезы.* FROM Виды_блюд, Трапезы WHERE Вид > Трапеза; Он позволяет выбрать из полученного в разд. 6.2.1 декартова произведения таблиц Виды_блюд и Трапезы лишь те строки, в которых значение трапезы "меньше" (по алфавиту) значения вида блюда: Часть II. Язык SQL. Извлечение данных 136 КОД_ВИДА ВИД КОД_ТРАПЕЗЫ ТРАПЕЗА -------- ------- ----------- ------- 1 Закуска 1 Завтрак 2 Суп 1 Завтрак 5 Напиток 1 Завтрак 2 Суп 2 Обед 6.2.6. Соединение таблицы со своей копией В ряде приложений возникает необходимость одновременной обработки данных какой-либо таблицы и одной или нескольких ее виртуальных копий, создаваемых на время выполнения запроса. Например, при вводе новых людей в таблицу Н_ЛЮДИ базы данных "UCHEBA" возможен повторный ввод данных о каком-либо человеке с при- своением ему второго идентификатора. Для выявления таких ошибок можно соединить таблицу Н_ЛЮДИ с ее временной копией, приравняв значения всех одноименных столбцов этих таблиц, кроме столбцов с идентификатором. Для последних надо использовать условие неравенства значений. Временную копию таблицы можно сформировать, указав имя псевдонима за именем таблицы во фразе FROM . Так, с помощью предложения SELECT копия.ид, н_люди.ид, н_люди.фамилия, н_люди.имя, н_люди.отчество, н_люди.дата_рождения FROM н_люди, н_люди копия WHERE н_люди.фамилия = копия.фамилия AND н_люди.имя = копия.имя AND н_люди.отчество = копия.отчество AND н_люди.дата_рождения = копия.дата_рождения AND н_люди.ид <> копия.ид ORDER BY н_люди.фамилия, н_люди.имя, н_люди.отчество; или его новой нотации SELECT копия.ид, н_люди.ид, н_люди.фамилия, н_люди.имя, н_люди.отчество, н_люди.дата_рождения FROM н_люди JOIN н_люди копия ON н_люди.фамилия= копия.фамилия AND н_люди.имя= копия.имя AND н_люди.отчество= копия.отчество AND н_люди.дата_рождения= копия.дата_рождения WHERE н_люди.ид <> копия.ид ORDER BY н_люди.фамилия, н_люди.имя, н_люди.отчество; Глава 6. Запросы с использованием нескольких таблиц 137 получим 224 строки с искомыми людьми. Рассмотрим выборку из 16 строк полученной таблицы: КОПИЯ.ИД ИД ФАМИЛИЯ ИМЯ ОТЧЕСТВО ДАТА_РОЖДЕНИЯ -------- ------ ----------- --------- ------------- ------------- 143878 145982 Марцуков Алексей Александрович 26.01.1987 145982 143878 Марцуков Алексей Александрович 26.01.1987 143880 146001 Мирошникова Катерина Федоровна 20.09.1987 143545 146001 Мирошникова Катерина Федоровна 20.09.1987 143545 143880 Мирошникова Катерина Федоровна 20.09.1987 146001 143880 Мирошникова Катерина Федоровна 20.09.1987 146001 143545 Мирошникова Катерина Федоровна 20.09.1987 143880 143545 Мирошникова Катерина Федоровна 20.09.1987 113191 142799 Михайлов Андрей Александрович 17.11.1962 142588 142800 Михайлов Андрей Александрович 10.04.1989 142800 142588 Михайлов Андрей Александрович 10.04.1989 142799 113191 Михайлов Андрей Александрович 17.11.1962 143881 146007 Михайлов Дмитрий Андреевич 06.02.1987 146007 143881 Михайлов Дмитрий Андреевич 06.02.1987 140476 143744 Михайлова Анастасия Владимировна 06.12.1987 143744 140476 Михайлова Анастасия Владимировна 06.12.1987 Отметим, что в ней для каждого совпадающего сочетания фамилии, имени, отчества и даты рождения выведено не менее двух строк, которые содержат одинаковые идентификаторы из Н_ЛЮДИ и КОПИЯ , но находящиеся в разных столбцах (формально это разные строки). Исключить фактические дубликаты строк можно, например, убрав из запроса столбец КОПИЯ.ИД и добавив в него ключевое слово DISTINCT : SELECT DISTINCT н_люди.ид, н_люди.фамилия, н_люди.имя, н_люди.отчество, н_люди.дата_рождения FROM н_люди JOIN н_люди копия ON н_люди.фамилия= копия.фамилия AND н_люди.имя= копия.имя AND н_люди.отчество= копия.отчество AND н_люди.дата_рождения= копия.дата_рождения WHERE н_люди.ид <> копия.ид ORDER BY н_люди.фамилия, н_люди.имя, н_люди.отчество; исключив из результата три "лишних" строки для Мирошниковой: 143878 Марцуков Алексей Александрович 26.01.1987 145982 Марцуков Алексей Александрович 26.01.1987 143545 Мирошникова Катерина Федоровна 20.09.1987 Часть II. Язык SQL. Извлечение данных 138 143880 Мирошникова Катерина Федоровна 20.09.1987 146001 Мирошникова Катерина Федоровна 20.09.1987 113191 Михайлов Андрей Александрович 17.11.1962 142799 Михайлов Андрей Александрович 17.11.1962 142800 Михайлов Андрей Александрович 10.04.1989 142588 Михайлов Андрей Александрович 10.04.1989 143881 Михайлов Дмитрий Андреевич 06.02.1987 146007 Михайлов Дмитрий Андреевич 06.02.1987 140476 Михайлова Анастасия Владимировна 06.12.1987 143744 Михайлова Анастасия Владимировна 06.12.1987 Тот же результат можно получить и без DISTINCT , использовав коррелиро- ванный вложенный подзапрос (см. разд. 6.3.6): SELECT ид, фамилия, имя, отчество, дата_рождения FROM н_люди WHERE EXISTS (SELECT * FROM н_люди копия WHERE н_люди.фамилия = копия.фамилия AND н_люди.имя = копия.имя AND н_люди.отчество = копия.отчество AND н_люди.дата_рождения = копия.дата_рождения AND н_люди.ид <> копия.ид) ORDER BY фамилия, имя, отчество; Так как во внешнем запросе используется только одна таблица Н_ЛЮДИ , то в нем не нужно использовать квалификаторы перед именами столбцов. 6.2.7. Внешние соединения До сих пор мы создавали соединения, в которых каждому связываемому зна- чению в одной из таблиц всегда находилось соответствующее значение в другой. Такие соединения принято называть внутренними соединениями. А как быть, если строка одной из связываемых таблиц не имеет пары в дру- гой. Например, если мы ввели в таблицу БЛЮДА несколько новых блюд (40 — Шашлык по-карски, 41 — Борщ, 42 — Щи русские, 43 — Рыба "Орли" и 44 — Икра из свеклы), но не успели (или забыли) ввести рецепты этих блюд, а сформировали запрос на вывод значений блюд с их рецептами: SELECT код_блюда, блюдо, основа, код_вида, SUBSTR(рецепт,1,20),вариант FROM блюда JOIN рецепты USING (код_блюда) Глава 6. Запросы с использованием нескольких таблиц 139 ORDER BY код_блюда, вариант; Результат выполнения этого запроса не содержит введенных нами блюд, так как в таблице РЕЦЕПТЫ нет строк с кодами блюд с 40 по 44. КОД_ БЛЮДА БЛЮДО ОСНОВА КОД_ВИДА SUBSTR(РЕЦЕПТ,1,20) ВАРИАНТ ----- ---------------- ------ -------- -------------------- ------- 21 Пудинг рисовый Крупа 3 Готовую рисовую расс 22 Вареники ленивые Молоко 3 В протертый творог п 23 Помидоры с луком Овощи 3 Спассеровать на масл 24 Суфле из творога Молоко 3 В протертый творог п 25 Рулет с яблоками Фрукты 4 Очистить яблоки, раз 26 Яблоки печеные Фрукты 4 Не прорезая насквозь 27 Суфле яблочное Фрукты 4 Запеченные яблоки с 28 Крем творожный Молоко 4 Яйца размешать с сах 29 «Утро» Фрукты 5 Очищенную и промытую 30 Компот Фрукты 5 Яблоки очистить от к 31 Молочный напиток Молоко 5 Яблоки натереть на т 32 Кофе черный Кофе 5 Вскипятить воду в ко 1 32 Кофе черный Кофе 5 Кофеварку или кастрю 2 33 Кофе на молоке Кофе 5 Сварить черный кофе, Для того чтобы они появились следует воспользоваться не операцией JOIN , соответствующей по умолчанию [INNER] JOIN , а, например, операцией LEFT [OUTER] JOIN : SELECT код_блюда, блюдо, основа, код_вида, SUBSTR(рецепт,1,20), вариант FROM блюда LEFT JOIN рецепты USING (код_блюда) ORDER BY код_блюда,вариант; КОД_ БЛЮДА БЛЮДО ОСНОВА КОД_ВИДА SUBSTR(РЕЦЕПТ,1,20) ВАРИАНТ ----- ---------------- ------ -------- -------------------- ------- 32 Кофе черный Кофе 5 Вскипятить воду в ко 1 32 Кофе черный Кофе 5 Кофеварку или кастрю 2 33 Кофе на молоке Кофе 5 Сварить черный кофе, 40 Шашлык по-карски Мясо 3 Часть II. Язык SQL. Извлечение данных 140 41 Рыба "Орли" Рыба 3 42 Щи русские Мясо 2 43 Борщ Мясо 4 44 Икра из свеклы Овощи 1 Нужно отметить, что в этих запросах для уменьшения длины выводимых строк использовалась функция SUBSTR (рецепт,1,20), которая, в данном слу- чае, вырезает из текста рецепта 20 символов, начиная с первого (1). (С описа- нием этой функции можно познакомиться в разд. 4.6.) Вот еще один пример внешнего соединения: SELECT блюда.блюдо, продукты.продукт, состав.вес FROM блюда FULL JOIN состав USING (КОД_БЛЮДА) FULL JOIN продукты USING (КОД_ПРОДУКТА); который при соединении таблиц БЛЮДА , СОСТАВ и ПРОДУКТЫ позволяет вывести те значения блюд и продуктов, которые введены в соответствующие табли- цы, но отсутствуют в таблице СОСТАВ : БЛЮДО ПРОДУКТ ВЕС ---------------- -------- ---- Уха из судака Судак 100 Уха из судака Морковь 20 Уха из судака Лук 20 Уха из судака Масло 5 Уха из судака Зелень 2 Яблоки печеные Яблоки 150 Яблоки печеные Сахар 20 Яблоки печеные Масло 2 Шашлык по-карски Борщ Щи русские Рыба "Орли" Икра из свеклы Кура Тыква Свекла Глава 6. Запросы с использованием нескольких таблиц 141 6.3. Вложенные подзапросы 6.3.1. Виды вложенных подзапросов Вложенный подзапрос (SUBQUERY ) — это предложение SELECT , заключен- ное в круглые скобки и вложенное в WHERE ( HAVING )-фразу другого предложе- ния SELECT или иных предложений, использующих WHERE -фразу. Вложенный подзапрос может содержать в своей WHERE ( HAVING )-фразе другой вложенный подзапрос и т. д. Нетрудно догадаться, что вложенный подзапрос создан для того, чтобы при отборе строк таблицы, сформированной основным запросом, можно было использовать данные из других таблиц (например, при отборе блюд для меню использовать данные о наличии продуктов в кладовой пан- сионата). Существуют простые и коррелированные вложенные подзапросы. Они вклю- чаются в WHERE ( HAVING )-фразу с помощью условий IN , EXISTS или одного из условий сравнения ( = | <> | < | <= | > | >= ). Простые вложенные под- запросы обрабатываются системой "снизу вверх". Первым обрабатывается вложенный подзапрос самого нижнего уровня. Множество значений, полу- ченное в результате его выполнения, используется при реализации подзапро- са более высокого уровня и т. д. Запросы с коррелированными вложенными подзапросами обрабатываются системой в обратном порядке. Сначала выбирается первая строка рабочей таблицы, сформированной основным запросом, и из нее выбираются значе- ния тех столбцов, которые используются во вложенном подзапросе (вложен- ных подзапросах). Если эти значения удовлетворяют условиям вложенного подзапроса, то выбранная строка включается в результат. Затем выбирается вторая строка и т. д., пока в результат не будут включены все строки, удовле- творяющие вложенному подзапросу (последовательности вложенных подза- просов). Следует отметить, что SQL обладает большой избыточностью в том смысле, что он часто предоставляет несколько различных способов формулировки одного и того же запроса. Поэтому во многих примерах данной главы будут использованы уже знакомые нам по предыдущей главе концептуальные фор- мулировки запросов. И несмотря на то, что часть из них успешнее реализует- ся с помощью соединений, здесь все же будут приведены их варианты с использованием вложенных подзапросов. Это связано с необходимостью детального знакомства с созданием и принципом выполнения вложенных под- запросов, так как существует немало задач (особенно на удаление и изменение Часть II. Язык SQL. Извлечение данных 142 данных), которые не могут быть реализованы другим способом. Кроме того, разные формулировки одного и того же запроса требуют для своего выпол- нения различные ресурсы памяти и могут значительно отличаться по времени реализации в разных СУБД. 6.3.2. Простые вложенные подзапросы Простые вложенные подзапросы используются для представления множества значений, исследование которых должно осуществляться в каком-либо пре- дикате IN , что иллюстрируется в следующем примере: выдать название и статус поставщиков продукта с номером 11, т. е. помидоров. SELECT Название, Статус FROM Поставщики WHERE Код_поставщика IN (SELECT Код_поставщика FROM Поставки WHERE Код_продукта = 11); Результат такого запроса имеет вид: НАЗВАНИЕ СТАТУС -------- ---------- СЫТНЫЙ рынок КОРЮШКА кооператив Как уже отмечалось в разд. 6.3.1, при обработке полного запроса система выполняет прежде всего вложенный подзапрос. Этот подзапрос выдает мно- жество номеров поставщиков, которые поставляют продукт с кодом, равным 11, а именно множество (1, 8). Поэтому первоначальный запрос эквивалентен такому простому запросу: SELECT Название, Статус FROM Поставщики WHERE Код_поставщика IN (1, 8); Подзапрос с несколькими уровнями вложенности можно проиллюстрировать на том же примере. Пусть требуется узнать не поставщиков продукта 11, как это делалось в предыдущих запросах, а поставщиков помидоров, являющих- ся продуктом с номером 11. Для этого можно дать запрос SELECT Название, Статус FROM Поставщики WHERE Код_поставщика IN (SELECT Код_поставщика Глава 6. Запросы с использованием нескольких таблиц 143 FROM Поставки WHERE Код_продукта IN (SELECT Код_продукта FROM Продукты WHERE Продукт = 'Помидоры')); В данном случае результатом самого внутреннего подзапроса является толь- ко одно значение (11). Как уже было показано ранее, подзапрос следующего уровня в свою очередь дает в результате множество (1, 8). Последний, самый внешний SELECT , вычисляет приведенный ранее окончательный результат. Вообще допускается любая глубина вложенности подзапросов. Тот же результат можно получить с помощью соединения SELECT Название, Статус FROM Поставщики JOIN Поставки USING (Код_Поставщика) JOIN Продукты USING (Код_Продукта) WHERE Продукт = 'Помидоры'; При выполнении этого компактного запроса система должна одновременно обрабатывать данные из трех таблиц, тогда как в предыдущем примере эти таблицы обрабатываются поочередно. Естественно, что для их реализации требуются различные ресурсы памяти и времени, однако этого невозможно ощутить при работе с ограниченным объемом данных в иллюстративной базе "COOK". 6.3.3. Использование одной и той же таблицы во внешнем и вложенном подзапросе Выдать номера поставщиков, которые поставляют хотя бы один продукт, по- ставляемый поставщиком 6. SELECT DISTINCT Код_поставщика FROM Поставки WHERE Код_продукта IN (SELECT Код_продукта FROM Поставки WHERE Код_поставщика = 6); Отметим, что ссылка на Поставки во вложенном подзапросе означает не то же самое, что ссылка на Поставки во внешнем запросе. В действительности, Часть II. Язык SQL. Извлечение данных 144 два имени Поставки описывают различные значения. Чтобы этот факт стал явным, полезно использовать псевдонимы, например, А и Б : SELECT DISTINCT А.Код_поставщика FROM Поставки А WHERE А.Код_продукта IN (SELECT Б.Код_продукта FROM Поставки Б WHERE Б.Код_поставщика = 6); Здесь А и Б — произвольные псевдонимы таблицы Поставки , определяемые во фразе FROM и используемые как явные уточнители во фразах SELECT и WHERE Напомним, что псевдонимы определены лишь в пределах одного запроса. 6.3.4. Вложенный подзапрос с оператором сравнения, отличным от IN Выдать номера поставщиков, находящихся в том же городе, что и поставщик с номером 6. SELECT А.Код_поставщика FROM Поставщики А WHERE А.Город = (SELECT Б.Город FROM Поставщики Б WHERE Б.Код_поставщика = 6 ); Результат: КОД_ПОСТАВЩИКА -------------- 1 4 6 В подобных запросах можно использовать и другие операторы сравнения ( <> , <= , < , >= или > ), однако, если вложенный подзапрос возвращает более одного значения и не используется оператор IN , будет возникать ошибка. 6.3.5. Коррелированные вложенные подзапросы Выдать название и статус поставщиков продукта с номером 11. SELECT Название, Статус Глава 6. Запросы с использованием нескольких таблиц 145 FROM Поставщики WHERE 11 IN (SELECT Код_продукта FROM Поставки WHERE Код_поставщика = Поставщики.Код_поставщика); Такой подзапрос отличается от рассмотренного в разд. 6.3.2 тем, что вло- женный подзапрос не может быть обработан прежде, чем будет обрабаты- ваться внешний запрос. Это связано с тем, что вложенный подзапрос зависит от значения Поставщики.Код_поставщика , а оно изменяется по мере того, как система проверяет различные строки таблицы Поставщики . Следовательно, с концептуальной точки зрения обработка осуществляется таким образом: 1. Система проверяет первую строку таблицы Поставщики . Предположим, что это строка поставщика с номером 1. Тогда значение Поставщики.Код_поставщика в данный момент имеет значение, равное 1, и система обрабатывает внут- ренний запрос (SELECT Код_продукта FROM Поставки WHERE Код_поставщика = 1); получая в результате множество (11, 12, 15). Теперь система может за- вершить обработку для поставщика с номером 1. Выборка значений Название и Статус для Код_поставщика=1 (СЫТНЫЙ и рынок) будет про- ведена тогда и только тогда, когда Код_продукта=11 будет принадлежать этому множеству, что, очевидно, справедливо. 2. Далее система будет повторять обработку такого рода для следующего поставщика и т. д. до тех пор, пока не будут рассмотрены все строки таб- лицы Поставщики Подобные подзапросы называются коррелированными, так как их результат зависит от значений, определенных во внешнем подзапросе. Обработка кор- релированного подзапроса, следовательно, должна повторяться для каждого значения, извлекаемого из внешнего подзапроса, а не выполняться раз и на- всегда. Рассмотрим пример использования одной и той же таблицы во внешнем под- запросе и коррелированном вложенном подзапросе. Выдать номера всех про- дуктов, поставляемых только одним поставщиком. SELECT DISTINCT А.Код_продукта FROM Поставки А WHERE А.Код_продукта NOT IN (SELECT Б.Код_продукта Часть II. Язык SQL. Извлечение данных 146 FROM Поставки Б WHERE Б.Код_поставщика <> А.Код_поставщика); Результат: КОД_ПРОДУКТА ------------ 3 7 8 14 17 Действие этого запроса можно пояснить следующим образом: "Поочередно для каждой строки таблицы Поставки , скажем А , выделить значение номера продукта ( Код_продукта ), если и только если это значение не входит в неко- торую строку, скажем, Б , той же таблицы, а значение столбца номер постав- щика ( Код_поставщика ) в строке Б не равно его значению в строке А ". Отметим, что в этой формулировке должен быть использован по крайней ме- ре один псевдоним — либо А , либо Б 6.3.6. Запросы, использующие EXISTS Квантор EXISTS (существует) — понятие, заимствованное из формальной ло- гики. В языке SQL предикат с квантором существования представляется вы- ражением EXISTS (SELECT * FROM ...) Такое выражение считается истинным только тогда, когда результат вычис- ления " SELECT * FROM ... " является непустым множеством, т. е. когда суще- ствует какая-либо запись в таблице, указанной во фразе FROM подзапроса, ко- торая удовлетворяет условию WHERE подзапроса. (Практически этот подзапрос всегда будет коррелированным множеством.) Рассмотрим примеры. Выдать названия поставщиков, поставляющих продукт с номером 11. SELECT Название FROM Поставщики WHERE EXISTS (SELECT * FROM Поставки WHERE Код_поставщика = Поставщики.Код_поставщика AND Код_продукта = 11); Глава 6. Запросы с использованием нескольких таблиц 147 НАЗВАНИЕ -------- СЫТНЫЙ КОРЮШКА Система последовательно выбирает строки таблицы Поставщики , выделяет из них значения столбцов Название и Код_поставщика . Затем она проверяет, яв- ляется ли истинным условие существования, т. е. существует ли в таблице Поставки хотя бы одна строка со значением Код_продукта=11 и значением Код_поставщика , равным значению Код_поставщика , выбранному из таблицы Поставщики . Если условие выполняется, то полученное значение столбца Название включается в результат. Предположим, что первые значения полей Название и Код_поставщика рав- ны, соответственно, 'СЫТНЫЙ' и 1 . Так как в таблице Поставки есть строка с Код_продукта=11 и Код_поставщика=1 , то значение 'СЫТНЫЙ' должно быть включено в результат. Хотя этот первый пример только показывает иной способ формулировки за- проса для задачи, решаемой и другими путями (с помощью оператора IN или соединения), EXISTS представляет собой одну из наиболее важных возмож- ностей SQL. Фактически любой запрос, который выражается через IN , может быть альтернативным образом сформулирован также с помощью EXISTS Однако обратное высказывание несправедливо. Выдать название и статус поставщиков, не поставляющих продукт с номе- ром 11: SELECT Название, Статус FROM Поставщики WHERE NOT EXISTS (SELECT * FROM Поставки WHERE Код_поставщика = Поставщики.Код_поставщика AND Код_продукта = 11); Результат: НАЗВАНИЕ СТАТУС -------- ---------- ПОРТОС кооператив ШУШАРЫ совхоз ТУЛЬСКИЙ универсам УРОЖАЙ коопторг ЛЕТО агрофирма Часть II. Язык SQL. Извлечение данных 148 ОГУРЕЧИК ферма Еще один пример запроса, использующего EXISTS , был рассмотрен в разд. 6.2.6. 6.3.7. Функции в подзапросе Теперь, после знакомства с различными формулировками вложенных подза- просов и псевдонимами легче понять текст и алгоритм реализации второго запроса разд. 6.1 на получение тех поставщиков продуктов для сырников, которые поставляют эти продукты за минимальную цену: SELECT Продукты.Продукт, Поставки.Цена, Поставщики.Название, Поставщики.Статус FROM Продукты, Состав, Блюда, Поставки, Поставщики WHERE Продукты.Код_продукта = Состав.Код_продукта AND Состав.Код_блюда = Блюда.Код_блюда AND Поставки.Код_продукта = Состав.Код_продукта AND Поставки.Код_поставщика = Поставщики.Код_поставщика AND Блюда.Блюдо = 'Сырники' AND Поставки.Цена = (SELECT MIN(Цена) FROM Поставки X WHERE X.Код_продукта = Поставки.Код_продукта ); Естественно, что это коррелированный подзапрос: здесь сначала определяет- ся минимальная цена продукта, входящего в состав сырников, и только затем выясняется его поставщик. На этом примере мы закончим знакомство с вложенными подзапросами, предложив попробовать свои силы в составлении ряда запросов, с помощью механизма таких подзапросов: 1. Выдать названия всех мясных блюд. 2. Выдать количество всех блюд, в состав которых входят помидоры. 3. Выдать блюда, продукты для которых поставляются агрофирмой ЛЕТО. Глава 6. Запросы с использованием нескольких таблиц 149 6.4. Фразы для работы с наборами: EXCEPT (MINUS), INTERSECT, UNION Существует класс операторов для работы с наборами данных: EXCEPT (ис- ключать), INTERSECT (пересекать) и UNION (объединять) — используемые для одновременного манипулирования результирующими наборами двух или более запросов (см. операции "разность", "пересечение" и "объединение" в разд. 3.3.2). Обобщенный синтаксис этих операторов можно представить так: <предложение SELECT_1> { EXCEPT | INTERSECT | UNION } [ALL | DISTINCT] <предложение SELECT_2> { EXCEPT | INTERSECT | UNION } [ALL | DISTINCT] Существует только одно важное правило, о котором следует помнить при использовании любого из этих операторов: порядок, количество и тип дан- ных столбцов должны быть идентичны во всех запросах. Типы данных не обязательно должны быть одинаковы, но они должны быть совместимы. Например, типы CHAR и VARCHAR являются совместимыми. По умолчанию результирующий набор использует размер наибольшего из совместимых типов, и в запросе, в котором объединяются три столбца типа CHAR — CHAR(3) , CHAR(10) и CHAR(12) , результаты будут в формате CHAR(12) , а в столбцы меньшего размера будут добавляться дополнительные пробелы. Ключевое слово ALL служит для указания, что результирующий набор дол- жен учитывать все дублирующие строки. Для исключения таких строк надо использовать ключевое слово DISTINCT Сортировать можно только окончательный результат, т. е. единственная фра- за ORDER BY может быть прописана лишь в последнем запросе. Оператор EXCEPT возвращает результирующий набор для запросов, вклю- чающий в себя все записи, полученные первым запросом, которые не обна- руживаются в результатах последующих запросов. (В Oracle существует эк- вивалент EXCEPT — оператор MINUS .) Оператор INTERSECT извлекает идентичные строки из результирующих набо- ров одного или нескольких запросов. В некотором отношении он очень на- поминает INNER JOIN Оператор UNION объединяет результирующие наборы нескольких запросов и создает из них один результирующий набор. Часть II. Язык SQL. Извлечение данных 150 В Oracle не используется ключевое слово DISTINCT , так как, если отсутствует ключевое слово ALL , то по умолчанию из результирующего набора исключа- ются дублирующие строки. В качестве примера использования таких операторов, мы здесь приведем только один, с помощью которого можно было получать внешние соедине- ния до появления в SQL фразы JOIN Выполним "старым" способом запросы из разд. 6.2.7. Сначала запрос, ис- пользующий фразу LEFT JOIN , который приходилось составлять из двух предложений SELECT , объединяемых оператором UNION : SELECT Блюда.Код_блюда, Блюдо, Основа, Код_вида, SUBSTR(рецепт,1,20), Вариант FROM Блюда, Рецепты WHERE Блюда.Код_блюда = Рецепты.Код_блюда UNION SELECT Блюда.Код_блюда, Блюдо, Основа, Код_вида, NULL, NULL FROM блюда WHERE NOT EXISTS (SELECT * FROM Рецепты WHERE Код_блюда = Блюда.Код_блюда) ORDER BY код_блюда, вариант; Второе предложение SELECT позволяет добавить в список блюд с рецептами те блюда, для которых рецепты не введены. Аналогичным образом формулировали запрос, который при соединении таб- лиц Блюда , Состав и Продукты позволяет вывести те значения блюд и продук- тов, которые введены в соответствующие таблицы, но отсутствуют в таблице Состав : SELECT Блюдо, Продукт, Вес FROM Блюда, Состав, Продукты WHERE Блюда.Код_блюда = Состав.Код_блюда AND Состав.Код_продукта = Продукты.Код_продукта UNION SELECT Блюдо, NULL, NULL FROM Блюда WHERE NOT EXISTS (SELECT * FROM Состав WHERE Код_блюда = Блюда.Код_блюда) UNION SELECT NULL, Продукт, NULL Глава 6. Запросы с использованием нескольких таблиц 151 FROM Продукты WHERE NOT EXISTS (SELECT * FROM Состав WHERE Код_продукта = Продукты.Код_продукта) ORDER BY 3; Здесь в список блюд, с входящими в них продуктами, добавляются блюда без списка продуктов и продукты, не входящие в состав блюд. Кроме того, здесь для сортировки использована допустимая в Oracle конст- рукция упорядочения по номеру столбца в перечне столбцов фразы SELECT 6.5. Заключение Краткое знакомство с возможностями предложения SELECT показало, что с его помощью можно реализовать все реляционные операции. Кроме того, в предложении SELECT выполняются разнообразные вычисления, агрегирова- ние данных, их упорядочение и ряд других операций, позволяющих описать в одном предложении ту работу, для выполнения которой потребовалось бы написать несколько страниц программы на алгоритмических языках Си, Пас- каль или на внутренних языках ряда распространенных СУБД. Пример 6.1. Получить перечень и вес (в килограммах) всех продуктов, кото- рые нужны для приготовления блюд, заказанных отдыхающими на 15.05.1989. Это можно сделать, соединив таблицы Выбор , Меню , Блюда , Состав и Продукты , а также агрегировав полученное соединение по продуктам, полу- чая сумму столбца Вес для каждого из них: SELECT Продукт, ROUND((SUM(Вес)/1000),3) FROM Продукты JOIN Состав USING (Код_продукта) JOIN Блюда USING (Код_блюда) JOIN Меню USING (Код_блюда) JOIN Выбор USING (Строка) GROUP BY Продукт; Результат такого запроса имеет вид: ПРОДУКТ ROUND((SUM(ВЕС)/1000),3) -------- ------------------------ Говядина 6,260 Зелень 1,170 Кофе 0,096 Часть II. Язык SQL. Извлечение данных 152 Лук 1,120 Майонез 1,665 Масло 1,234 Молоко 16,400 Мука 0,363 Помидоры 10,015 Рис 3,120 Сахар 2,635 Сметана 3,180 Творог 3,590 Яблоки 13,595 Яйца 4,570 Так как в таблице состав всех продуктов, входящих в блюдо, дан в грам- мах, то для получения суммарного веса продуктов, необходимых для при- готовления блюд на все трапезы 32 отдыхающих, в килограммах мы разде- лили эту сумму на 1000. Кроме того, мы воспользовались функцией ROUND (см. разд. 4.6 и 5.5.3) для округления результатов и сохранения в них трех цифр после запятой. Следовательно, для приготовления всех блюд на 15.05.1989 требуется чуть больше 6 кг говядины, 13 кг яблок, 16 литров молока, т. е. вполне приемле- мые количества. Пример 6.2. А сколько же продуктов в кладовой пансионата и какова сред- няя стоимость каждого из них? Если считать, что все поставленные до 15.05.1989 продукты еще не расходовались, то можно воспользоваться таким запросом SELECT (SELECT Продукт FROM Продукты WHERE Код_продукта = Поставки.Код_продукта) Продукт, SUM(К_во) Всего, ROUND(SUM(Цена*К_во)/SUM(К_во),2) Средняя FROM Поставки GROUP BY Код_продукта ORDER BY Продукт; в результате выполнения которого будет получена таблица: ПРОДУКТ ВСЕГО СРЕДНЯЯ -------- ---------- ---------- Говядина 370 3,71 Зелень 30 2,67 Глава 6. Запросы с использованием нескольких таблиц 153 Кофе 50 4,5 Лук 220 0,58 Майонез 100 2,52 Масло 250 4 Молоко 200 0,4 Мука 70 0,5 Помидоры 150 1,17 Рис 190 0,95 Сахар 250 0,95 Сметана 220 2,71 Творог 150 1 Яблоки 370 1,73 Яйца 170 1,88 Здесь мы впервые включили подзапрос в список фразы SELECT для получения названия продукта по его коду, взятому из таблицы Поставки Пример 6.3. Так как при составлении меню шеф-повар должен знать текущее количество и стоимость продуктов, то целесообразно иметь запрос, кото- рый бы учитывал не только поступление продуктов в кладовую, но и их еже- дневный расход. Этот запрос можно оформить в виде представления (напри- мер, Наличие ) с тем, чтобы у шеф-повара всегда существовала виртуальная таблица с необходимой ему информацией (о представлениях рассказано в разд. 4.3 и 7.3.4). CREATE OR REPLACE VIEW НАЛИЧИЕ (Код_продукта, К_во, Стоимость) AS SELECT Расход.Код_продукта, Склад.Всего - Расход.Итого К_во, Стоимость FROM (SELECT Код_продукта, ROUND(SUM(Вес)/1000,3) Итого FROM Продукты JOIN Состав USING (Код_продукта) JOIN Блюда USING (Код_блюда) JOIN Меню USING (Код_блюда) JOIN Выбор USING (Строка) GROUP BY Код_продукта) Расход, (SELECT Код_продукта, SUM(к_во) Всего, ROUND(SUM(цена*к_во)/SUM(к_во),2) Стоимость FROM поставки GROUP BY Код_продукта) Склад WHERE Расход.Код_продукта = Склад.Код_продукта; Часть II. Язык SQL. Извлечение данных 154 Во FROM -фразе этого представления используются не базовые таблицы, а две рабочие таблицы, создаваемые подзапросами. Первый из них, которому мы дали псевдоним ВСЕГО , это несколько модифицированный запрос из приме- ра 6.1. Второй подзапрос с псевдонимом СКЛАД — это модифицированный запрос из примера 6.2. Если теперь выполнить запрос SELECT * FROM Наличие; то СУБД произведет загрузку результирующих данных в виртуальную таб- лицу НАЛИЧИЕ и выдаст ее результат в виде: КОД_ПРОДУКТА К_ВО СТОИМОСТЬ ------------ ---------- ---------- 1 363,74 3,71 3 248,77 4 4 98,33 2,52 5 165,43 1,88 6 216,82 2,71 7 183,6 0,4 8 146,41 1 10 218,88 0,58 11 139,98 1,17 12 28,83 2,67 13 186,88 0,95 14 69,64 0,5 15 356,4 1,73 16 247,36 0,95 17 49,9 4,5 Пример 6.4. Если требуется получить калорийность и стоимость тех блюд, для которых: есть все составляющие их продукты; калорийность не превышает 400 ккал; стоимость не превышает 0.5 рубля; результат надо упорядочить по возрастанию калорийности блюд в рамках их видов, то можно дать запрос: SELECT Вид, Блюдо, ROUND(SUM(((Белки+Углев)*4.1+Жиры*9.3)*Вес/1000),1) Калорий, ROUND(SUM(Стоимость*Вес/1000)+MIN(Труд/100),2) Цена FROM Блюда Глава 6. Запросы с использованием нескольких таблиц 155 JOIN Состав USING (Код_блюда) JOIN Продукты USING (Код_продукта) JOIN Наличие USING (Код_продукта) JOIN Виды_блюд USING (Код_вида) WHERE Код_блюда NOT IN (SELECT Код_блюда FROM Состав WHERE Код_продукта NOT IN (SELECT Код_продукта FROM Наличие)) GROUP BY Вид, Блюдо HAVING SUM(Стоимость*Вес/1000)+MIN(Труд/100) < 0.5 AND SUM(((Белки+Углев)*4.1+Жиры*9.3)*Вес/1000) < 400 ORDER BY Вид, Калорий; Результат выполнения этого запроса имеет вид: ВИД БЛЮДО КАЛОРИЙ ЦЕНА ------- ---------------- ---------- ---------- Горячее Помидоры с луком 244,8 0,45 Горячее Драчена 334,1 0,34 Горячее Каша рисовая 339,3 0,28 Горячее Омлет с луком 355,0 0,37 Десерт Яблоки печеные 170,4 0,32 Десерт Крем творожный 394,4 0,29 Закуска Салат летний 155,6 0,34 Закуска Салат витаминный 217,5 0,40 Закуска Творог 330,1 0,24 Напиток Кофе черный 7,1 0,05 Напиток Компот 74,5 0,15 Напиток Кофе на молоке 154,8 0,11 Напиток Молочный напиток 265,0 0,36 Суп Суп молочный 396,6 0,23 Обратите внимание на прием, использованный при суммировании стоимо- стей продуктов, входящих в состав блюда, и стоимости его приготовления ( Труд ). В состав блюда может входить несколько продуктов, и для получения общей стоимости всех этих продуктов надо использовать агрегирующую функцию SUM(Стоимость*Вес/1000) с группировкой по столбцу БЛЮДО . Однако если к этой сумме добавить слагаемое Труд/100 , то будет выдано сообщение "ORA-00979: выражение не является выражением GROUP BY". Сделав же Труд/100 аргументом агрегирующей функции MIN , мы как бы "обманываем" Часть II. Язык SQL. Извлечение данных 156 СУБД, получая искомый результат. Равным образом можно было бы исполь- зовать вместо MIN функции MAX или AVG . Можно поступить и по-другому: не добавлять агрегирующую функцию MIN , а добавить во фразу GROUP BY стол- бец Труд SELECT Вид, Блюдо, ROUND(SUM(((Белки+Углев)*4.1+Жиры*9.3)*Вес/1000),1) Калорий, ROUND(SUM(Стоимость*Вес/1000)+Труд/100,2) Цена FROM Блюда JOIN Состав USING (Код_блюда) JOIN Продукты USING (Код_продукта) JOIN Наличие USING (Код_продукта) JOIN Виды_блюд USING (Код_вида) WHERE Код_блюда NOT IN (SELECT Код_блюда FROM Состав WHERE Код_продукта NOT IN (SELECT Код_продукта FROM Наличие)) GROUP BY Вид, Блюдо, Труд HAVING SUM(Стоимость*Вес/1000)+Труд/100 < 0.5 AND SUM(((Белки+Углев)*4.1+Жиры*9.3)*Вес/1000) < 400 ORDER BY Вид, Калорий; |