Кириллов В.В., Громов Г.Ю. - Введение в реляционные базы данных. Литература для вузов isbn 9785941577705 в книге рассматриваются основные понятия баз данных и систем управления ими
 Скачать 11.62 Mb.
|
|
Глава 6 Запросы с использованием нескольких таблиц 6.1. О средствах одновременной работы с множеством таблиц Затрагивая во второй главе вопросы проектирования баз данных, мы выясни- ли, что базы данных — это множество взаимосвязанных сущностей или от- ношений (таблиц) в терминологии реляционных СУБД. При проектировании стремятся создавать таблицы, в каждой из которых содержалась бы инфор- мация об одном и только об одном типе сущностей. Это облегчает модифи- кацию базы данных и поддержание ее целостности. Но такой подход тяжело усваивается начинающими проектировщиками, которые пытаются привязать проект к будущим приложениям и так организовать таблицы, чтобы в каждой из них хранилось все необходимое для реализации возможных запросов. Ти- пичен вопрос: как же получить сведения о том, где купить продукты для при- готовления того или иного блюда и определить его калорийность и стои- мость, если нужные данные "рассыпаны" по семи различным таблицам? Не лучше ли иметь одну большую таблицу, содержащую все сведения базы данных "COOK"? Даже при отсутствии средств одновременного доступа ко многим таблицам нежелателен проект, в котором информация о многих типах сущностей пере- мешана в одной таблице. SQL же обладает великолепным механизмом для одновременной или последовательной обработки данных из нескольких взаимосвязанных таблиц. В нем реализованы возможности "соединять" или "объединять" несколько таблиц и так называемые "вложенные подзапросы". Например, чтобы получить перечень поставщиков продуктов, необходимых для приготовления сырников, возможен запрос SELECT Продукты.Продукт, Поставки.Цена, Поставщики.Название, Поставщики.Статус Часть II. Язык SQL. Извлечение данных 126 FROM Продукты, Состав, Блюда, Поставки, Поставщики WHERE Продукты.Код_продукта = Состав.Код_продукта AND Состав.Код_блюда = Блюда.Код_блюда AND Поставки.Код_продукта = Состав.Код_продукта AND Поставки.Код_поставщика = Поставщики.Код_поставщика AND Блюда.Блюдо = 'Сырники' AND Поставки.Цена IS NOT NULL; дающий следующий результат: ПРОДУКТ ЦЕНА НАЗВАНИЕ СТАТУС -------- ------- -------- ---------- Яйца 1,80 ПОРТОС кооператив Яйца 2,00 КОРЮШКА кооператив Сметана 3,60 ПОРТОС кооператив Сметана 2,20 ОГУРЕЧИК ферма Творог 1,00 ОГУРЕЧИК ферма Мука 0,50 УРОЖАЙ коопторг Сахар 0,94 ТУЛЬСКИЙ универсам Сахар 1,00 УРОЖАЙ коопторг Он получен следующим образом: СУБД последовательно формирует строки декартова произведения таблиц (см. разд. 3.3.2), перечисленных во фразе FROM , проверяет, удовлетворяют ли данные сформированной строки условиям фразы WHERE , и если удовлетворяют, то включает в ответ на запрос те ее поля, которые перечислены во фразе SELECT Очевидно, что с помощью соединения несложно сформировать запрос на об- работку данных из нескольких таблиц. Кроме того, в такой запрос можно включить любые части предложения SELECT , рассмотренные в главе 5 (выра- жения с использованием функций, группирование с отбором указанных групп и упорядочением полученного результата). Следовательно, соединения позволяют обрабатывать множество взаимосвязанных таблиц как единую таблицу, в которой перемешана информация о многих типах сущностей. По- этому начинающий проектировщик базы данных может спокойно создавать маленькие нормализованные таблицы, так как он всегда может получить из них любую "большую" таблицу. Кроме механизма соединений в SQL есть механизм вложенных подзапросов, позволяющий объединить несколько простых запросов в едином предложе- нии SELECT . Иными словами, вложенный подзапрос — это уже знакомый нам подзапрос (с небольшими ограничениями), который вложен в WHERE -фразу другого вложенного подзапроса или WHERE -фразу основного запроса. Глава 6. Запросы с использованием нескольких таблиц 127 Для иллюстрации вложенного подзапроса вернемся к предыдущему примеру и попробуем получить перечень тех поставщиков продуктов для сырников, которые поставляют нужные продукты за минимальную цену. SELECT Продукты.Продукт, Поставки.Цена, Поставщики.Название, Поставщики.Статус FROM Продукты, Состав, Блюда, Поставки, Поставщики WHERE Продукты.Код_продукта = Состав.Код_продукта AND Состав.Код_блюда = Блюда.Код_блюда AND Поставки.Код_продукта = Состав.Код_продукта AND Поставки.Код_поставщика = Поставщики.Код_поставщика AND Блюда.Блюдо = 'Сырники' AND Поставки.Цена = (SELECT MIN(Цена) FROM Поставки X WHERE X.Код_продукта = Поставки.Код_продукта ); Результат запроса имеет вид: ПРОДУКТ ЦЕНА НАЗВАНИЕ СТАТУС -------- ------- -------- ---------- Яйца 1,80 ПОРТОС кооператив Сметана 2,20 ОГУРЕЧИК ферма Творог 1,00 ОГУРЕЧИК ферма Мука 0,50 УРОЖАЙ коопторг Сахар 0,94 ТУЛЬСКИЙ универсам Здесь с помощью подзапроса, размещенного в трех последних строках запро- са, описывается процесс определения минимальной цены каждого продукта для сырников и поиск поставщика, предлагающего этот продукт за такую цену. Механизм реализации подзапросов будет подробно описан в разд. 6.3. Там же будет рассмотрено, как и для чего вводится псевдоним X для имени таблицы Поставки В заключение следует подчеркнуть, что во всех фразах, в которых упомина- ются имена столбцов, (например, SELECT , WHERE , …) во избежание двусмыс- ленности ссылки на эти столбцы или символ ( * ) могут (а иногда и должны) уточняться именем соответствующей таблицы (квалификатором), например, Поставки.Код_поставщика , Блюда.* , Поставщики.Код_поставщика , Меню.* , Состав.Код_блюда и т. п. Это особенно необходимо делать в тех случаях, ко- гда в таблицах, описанных во фразе FROM , существуют столбцы с одинаковы- ми именами (например, Код_блюда в Блюдах , Состав и Рецепты , Код_продукта в Состав , Продукты и Поставки ). Часть II. Язык SQL. Извлечение данных 128 6.1.1. Использование фразы JOIN Рассмотренный ранее способ, создания соединений с помощью фразы WHERE — является "старым" способом соединения. Начиная с SQL:1992 и Oracle 9i лучшим способом является использование фразы JOIN , которая позволяет перенести условия соединения таблиц внутрь фразы FROM , оставив во фразе WHERE лишь условия для отбора строк. Такое разделение условий упрощает понимание текста запроса. Синтаксис FROM с использованием JOIN имеет вид: FROM таблица [AS псевдоним] {CROSS JOIN | { [NATURAL] [тип_соединения] JOIN соединяемая_таблица [AS псевдоним] { ON условие_соединения1 [{AND|OR} условие_соединения2 [. . .] ] | USING (столбец1 [, . . .]) } } [. . .] Здесь FROM таблица — первая таблица или представление в соединении. Ключевое слово NATURAL служит для указания того, что соединение таблиц должно проводиться по всем их столбцам с идентичными именами. Это слово позволяет исключить из запроса условия соединения, обычно оговариваемые фразами ON или USING . Запрос не будет выполнен, если этот вид соединения будет проводиться в таблицах, не содержащих столбцов с одинаковыми име- нами. С помощью фразы [тип_соединения] JOIN соединяемая_таблица указыва- ется тип соединения и вторая (и все последующие) таблицы в соединении. Для всех этих таблиц можно определить псевдонимы. Могут использоваться следующие типы соединений. CROSS JOIN — осуществляет полное перекрестное соединение двух таблиц. Каждая строка первой таблицы соединяется со всеми строками второй таб- лицы, что создает результирующий набор огромных размеров — декартово произведение таблиц. Этот же результат будет получен и в случае других типов соединения, если вы забыли прописать условия соединения. [INNER] JOIN — осуществляет соединение двух таблиц, где каждая строка первой таблицы соединяется только с теми строками второй таблицы, ко- торые удовлетворяют условию соединения. Ключевое слово INNER (внут- ренний) можно опускать — этот тип предполагается по умолчанию. LEFT [OUTER] JOIN — указывает, что строки будут возвращаться из табли- цы, находящейся слева от ключевого слова JOIN . Если строка, возвращаемая из левой таблицы, не имеет соответствующей строки в правой таблице, строка все равно извлекается. В этом случае в столбцах для значений Глава 6. Запросы с использованием нескольких таблиц 129 из правой таблицы будут установлены значения NULL . Рекомендуется везде, где это возможно, использовать левосторонние внешние соединения ( LEFT OUTER ), чтобы не смешивать левосторонние и правосторонние соединения. Здесь и далее ключевое слово OUTER (внешний) можно опускать — этот тип для LEFT , RIGHT и FULL JOIN предполагается по умолчанию. RIGHT [OUTER] JOIN — схожа с LEFT OUTER , но строки будут возвращать- ся из таблицы, находящейся справа от ключевого слова JOIN FULL [OUTER] JOIN — указывает, что возвращаться будут все строки из обеих таблиц независимо от того, совпадают ли строки в таблицах. Всем столбцам, для которых нет соответствующих значений в соединенной таблице, присваиваются значения NULL UNION JOIN — указывает, что возвращаться будут все столбцы в обеих таблицах и все строки каждого столбца. Всем столбцам, для которых нет соответствующих значений в соединенной таблице, присваиваются значе- ния NULL ON условие_соединения имеет следующий синтаксис: FROM имя_таблицы1 JOIN имя_таблицы2 ON имя_таблицы1.столбец1 = имя_таблицы2.столбец2 [ {AND| OR} имя_таблицы1.столбец3 = имя_таблицы2.столбец4] [. . .] JOIN имя_таблицы3 ON имя_таблицы1.столбец5 = имя_таблицы3.столбец6 [ {AND| OR} имя_таблицы1.столбец7 = имя_таблицы3.столбец8] [. . .] [JOIN. . .] Если столбцы, по которым производится соединение таблиц, имеют совпа- дающие имена, то вместо условия с ON можно написать USING (столбец1 [, . . .] ) При использовании JOIN , первый запрос, рассмотренный в разд. 6.1, может быть записан так: SELECT Продукты.Продукт, Поставки.Цена, Поставщики.Название, Поставщики.Статус FROM Продукты JOIN Состав ON Продукты.Код_продукта = Состав.Код_продукта JOIN Поставки ON Поставки.Код_продукта = Состав.Код_продукта JOIN Блюда ON Состав.Код_блюда = Блюда.Код_блюда Часть II. Язык SQL. Извлечение данных 130 JOIN Поставщики ON Поставки.Код_поставщика = Поставщики.Код_поставщика WHERE Блюдо = 'Сырники' AND Цена IS NOT NULL; или так: SELECT Продукты.Продукт, Поставки.Цена, Поставщики.Название, Поставщики.Статус FROM Продукты JOIN Состав USING (Код_продукта) JOIN Поставки USING (Код_продукта) JOIN Блюда USING (Код_блюда) JOIN Поставщики USING (Код_поставщика) WHERE Блюдо = 'Сырники' AND Цена IS NOT NULL; или вот так: SELECT Продукты.Продукт, Поставки.Цена, Поставщики.Название, Поставщики.Статус FROM Продукты NATURAL JOIN Состав NATURAL JOIN Поставки NATURAL JOIN Блюда NATURAL JOIN Поставщики WHERE Блюдо = 'Сырники' AND Цена IS NOT NULL; 6.2. Запросы, использующие соединения В данном разделе будут рассмотрены примеры реализации реляционных операций с помощью описанных ранее средств языка SQL. 6.2.1. Декартово произведение таблиц В разд. 3.3.2 показано, что соединения — это подмножества декартова про- изведения. Так как декартово произведение n таблиц — это таблица, содер- жащая все возможные строки r, такие, что r является сцеплением какой-либо строки из первой таблицы, строки из второй таблицы, ... и строки из n-й таб- лицы. Так как мы уже научились выделять с помощью SELECT любое под- Глава 6. Запросы с использованием нескольких таблиц 131 множество реляционной таблицы, то осталось лишь выяснить, можно ли с помощью SELECT получить декартово произведение. Для получения декартова произведения нескольких таблиц можно: 1. Указать во фразе FROM перечень перемножаемых таблиц, а во фразе SELECT все их столбцы. 2. Воспользоваться фразой CROSS JOIN Так как количество строк декартова произведения равно произведению коли- чества строк соединяемых таблиц, то "перемножим" маленькие таблицы Ви- ды_блюд (табл. 3.2) и Трапезы (табл. 3.7): SELECT Виды_блюд.*, Трапезы.* FROM Виды_блюд, Трапезы; и получим таблицу, содержащую 5 3 = 15 строк: КОД_ВИДА ВИД КОД_ТРАПЕЗЫ ТРАПЕЗА -------- ------- ----------- ------- 1 Закуска 1 Завтрак 2 Суп 1 Завтрак 3 Горячее 1 Завтрак 4 Десерт 1 Завтрак 5 Напиток 1 Завтрак 1 Закуска 2 Обед 2 Суп 2 Обед 3 Горячее 2 Обед 4 Десерт 2 Обед 5 Напиток 2 Обед 1 Закуска 3 Ужин 2 Суп 3 Ужин 3 Горячее 3 Ужин 4 Десерт 3 Ужин 5 Напиток 3 Ужин В рекомендуемой нотации с использованием JOIN аналогичный запрос име- ет вид: SELECT Виды_блюд.*, Трапезы.* FROM Виды_блюд CROSS JOIN Трапезы; В другом примере, где перемножаются таблицы Меню , Трапезы , Виды_блюд , Блюда : SELECT Меню.*, Трапезы.*, Виды_блюд.*, Блюда.* FROM Меню, Трапезы, Виды_блюд, Блюда; Часть II. Язык SQL. Извлечение данных 132 образуется таблица, содержащая 21 3 5 33 = 10 395 строк. Далее приведе- на выборка из 39 первых строк этой таблицы. В ней для уменьшения ширины изъят столбец ДАТА таблицы МЕНЮ , содержащий одинаковые значения 15.05.1989 МЕНЮ ТРАПЕЗЫ ВИДЫ_БЛЮД БЛЮДА СТРОКА КОД_ТРАПЕЗЫ КОД_БЛЮДА КОД_ТРАПЕЗЫ ТРАПЕЗА КОД_ВИДА ВИД КОД_БЛЮДА БЛЮДО КОД_ВИДА ОСНОВА ВЫХОД ТРУД 1 1 3 1 Завтрак 1 Закуска 1 Салат летний 1 Овощи 200,0 3 1 1 3 1 Завтрак 1 Закуска 2 Салат мясной 1 Мясо 200,0 4 1 1 3 1 Завтрак 1 Закуска 3 Салат витаминный 1 Овощи 200,0 4 * 1 1 3 1 Завтрак 1 Закуска 12 Суп молочный 2 Молоко 500,0 3 1 1 3 1 Завтрак 1 Закуска 13 Бастурма 3 Мясо 300,0 5 1 1 3 1 Завтрак 1 Закуска 32 Кофе черный 5 Кофе 100,0 1 1 1 3 1 Завтрак 1 Закуска 33 Кофе на молоке 5 Кофе 200,0 2 2 1 6 1 Завтрак 1 Закуска 1 Салат летний 1 Овощи 200,0 3 2 1 6 1 Завтрак 1 Закуска 2 Салат мясной 1 Мясо 200,0 4 2 1 6 1 Завтрак 1 Закуска 3 Салат витаминный 1 Овощи 200,0 4 2 1 6 1 Завтрак 1 Закуска 4 Салат рыбный 1 Рыба 200,0 4 2 1 6 1 Завтрак 1 Закуска 5 Паштет из рыбы 1 Рыба 120,0 5 2 1 6 1 Завтрак 1 Закуска 6 Мясо с гарниром 1 Мясо 250,0 3 * При анализе этих строк мы нашли только две актуальных (отмечены симво- лом "*"), в которых совпадают номера блюд таблиц Меню и Блюда . В осталь- Глава 6. Запросы с использованием нескольких таблиц 133 ных полная чепуха: к закускам отнесены супы и напитки, на завтрак предла- гается незапланированный суп и т. д. 6.2.2. Эквисоединение таблиц Если из декартова произведения убрать ненужные строки и столбцы, то можно получить актуальные таблицы, соответствующие любому из соединений. Очевидно, что отбор актуальных строк обеспечивается вводом в запрос WHERE фразы, в которой устанавливается соответствие между: кодами трапез в таблицах Меню и Трапезы ( Меню.Код_трапезы = Трапезы.Код_трапезы ); кодами видов блюд в таблицах Меню и Виды_блюд ( Меню.Вид_блюда = Виды_блюд.Вид_блюда ), кодами блюд в таблицах Меню и Блюда ( Меню.Код_блюда = Блюда.Код_блюда ). Такой скорректированный запрос, содержащий все столбцы, SELECT Меню.*, Трапезы.*, Виды_блюд.*, Блюда.* FROM Меню, Трапезы, Виды_блюд, Блюда WHERE Меню.Код_блюда = Блюда.Код_блюда AND Блюда.Код_вида = Виды_блюд.Код_вида AND Меню.Код_трапезы = Трапезы.Код_трапезы; позволит получить эквисоединение таблиц Меню , Трапезы , Виды_блюд и Блюда , содержащее всего 21 строку (столько же, сколько в таблице Меню ): СТРОКА КОД_ТРАПЕЗЫ КОД_БЛЮДА КОД_ТРАПЕЗЫ ТРАПЕЗА КОД_ВИДА ВИД КОД_БЛЮДА БЛЮДО КОД_ВИДА ОСНОВА ВЫХОД ТРУД 1 1 3 1 Завтрак 1 Закуска 3 Салат вита- минный 1 Овощи 200,0 4 2 1 6 1 Завтрак 1 Закуска 6 Мясо с гар- ниром 1 Мясо 250,0 3 3 1 19 1 Завтрак 3 Горячее 19 Омлет с луком 3 Яйца 200,0 5 Часть II. Язык SQL. Извлечение данных 134 19 3 16 3 Ужин 3 Закуска 16 Драчена 3 Яйца 180,0 4 20 3 30 3 Ужин 5 Закуска 30 Компот 5 Фрукты 200,0 2 21 3 31 3 Ужин 5 Закуска 31 Молочный напиток 5 Молоко 200,0 2 Аналогичный результат может быть получен с помощью запросов, исполь- зующих рекомендуемую нотацию с фразой JOIN : SELECT Меню.*, Трапезы.*, Виды_блюд.*, Блюда.* FROM Меню JOIN Блюда ON Меню.Код_блюда = Блюда.Код_блюда JOIN Виды_блюд ON Блюда.Код_вида = Виды_блюд.Код_вида JOIN Трапезы ON Меню.Код_трапезы = Трапезы.Код_трапезы; 6.2.3. Естественное соединение таблиц Легко заметить, что в эквисоединение таблиц вошли дубликаты столбцов, по которым проводилось соединение ( Код_трапезы , Код_вида и Код_блюда ). Для исключения этих дубликатов можно создать естественное соединение тех же таблиц: SELECT Строка, Код_трапезы, Код_блюда, Трапеза, Код_вида, Вид, Блюдо, Основа, Выход, Труд FROM Меню JOIN Блюда ON Меню.Код_блюда = Блюда.Код_блюда JOIN Виды_блюд ON Блюда.Код_вида = Виды_блюд.Код_вида JOIN Трапезы ON Меню.Код_трапезы = Трапезы.Код_трапезы; Реализация естественного соединения таблиц имеет вид СТРОКА КОД_ТРАПЕЗЫ КОД_БЛЮДА ТРАПЕЗА КОД_ВИДА ВИД БЛЮДО ОСНОВА ВЫХОД ТРУД 1 1 3 Завтрак 1 Закуска Салат витаминный Овощи 200,0 4 2 1 6 Завтрак 1 Закуска Мясо с гарниром Мясо 250,0 3 3 1 19 Завтрак 3 Горячее Омлет с луком Яйца 200,0 5 |