p>Фраза GROUP BY (группировать по) инициирует перекомпоновку указанной во FROM таблицы по группам, каждая из которых имеет одинаковые значения в столбце, указанном в GROUP BY. В рассматриваемом примере строки таблицы Поставки группируются так, что в одной группе содержатся все строки для продукта с ПР = 1, в другой – для продукта с ПР = 2 и т. д. (см. рис. 2. 5, б). Далее к каждой группе применяется фраза SELECT. Каждое выражение в этой фразе должно принимать единственное значение для группы, т. е. оно может быть либо значением столбца, указанного в GROUP BY, либо арифметическим выражением, включающим это значение, либо константой, либо одной из SQL-функций, которая оперирует всеми значениями столбца в группе и сводит эти значения к единственному значению (например, к сумме). Отметим, что фраза GROUP BY не предполагает ORDER BY. Чтобы гарантировать упорядочение по ПР результата рассматриваемого примера (рис. 2. 5, в) следует дать запрос
SELECT ПР, SUM(К_во) FROM Поставки GROUP BY ПР ORDER BY ПР;
Наконец, отметим, что строки таблицы можно группировать по любой комбинации ее столбцов. Так, по запросу
SELECT Т, БЛ, COUNT(БЛ) FROM Заказ GROUP BY Т, БЛ;
можно узнать коды и количество порций блюд, заказанных отдыхающими пансионата (32 человека) на каждую из трапез следующего дня: Т
БЛ COUNT(БЛ) 1 3 18 1 6 14 1 19 17 1 21 15 … Использование фразы HAVING
Фраза HAVING (рис. 2. 3) играет такую же роль для групп, что и фраза WHERE для строк: она используется для исключения групп, точно так же, как WHERE используется для исключения строк. Эта фраза включается в предложение лишь при наличии фразы GROUP BY, а выражение в HAVING должно принимать единственное значение для группы. Например, выдать коды продуктов, поставляемых более чем двумя поставщиками:
SELECT FROM Поставки GROUP BY ПС HAVING COUNT(*) 2; Результат: ПР 9 11 12
2. 2. 3. Использование запросов с использованием нескольких таблицы. О средствах одновременной работы с множеством таблиц Затрагивая вопросы проектирования баз данных, мы выяснили, что базы данных – это множество взаимосвязанных сущностей или отношений (таблиц) в терминологии реляционных СУБД. При проектировании стремятся создавать таблицы, в каждой из которых содержалась бы информация об одном и только об одном типе сущностей. Это облегчает модификацию базы данных и поддержание ее целостности. Но такой подход тяжело усваивается начинающими проектантами, которые пытаются привязать проект к будущим приложениям и так организовать таблицы, чтобы в каждой из них хранилось все необходимое для реализации возможных запросов. Типичен вопрос: как же получить сведения о том, где купить продукты для приготовления того или иного блюда и определить его калорийность и стоимость, если нужные данные “рассыпаны” по семи различным таблицам? Не лучше ли иметь одну большую таблицу, содержащую все сведения базы данных ПАНСИОН ? Даже при отсутствии средств одновременного доступа ко многим таблицам нежелателен проект, в котором информация о многих типах сущностей перемешана в одной таблице. SQL же обладает великолепным механизмом для одновременной или последовательной обработки данных из нескольких взаимосвязанных таблиц. В нем реализованы возможности “соединять” или “объединять” несколько таблиц и так называемые “вложенные подзапросы”. Например, чтобы получить перечень поставщиков продуктов, необходимых для приготовления Сырников, возможен запрос
SELECT Продукт, Цена, Название, Статус FROM Продукты, Состав, Блюда, Поставки, Поставщики WHERE Продукты. ПР = Состав. ПР AND Состав. БЛ = Блюда. БЛ AND Поставки. ПР = Состав. ПР AND Поставки. ПС = Поставщики. ПС AND Блюдо = 'Сырники' AND Цена IS NOT NULL; Продукт Цена Название Статус Яйца 1. 8 ПОРТОС Кооператив Яйца 2. КОРЮШКА Кооператив Сметана 3. 6 ПОРТОС Кооператив Сметана 2. 2 ОГУРЕЧИК Ферма Творог 1. ОГУРЕЧИК Ферма Мука 0. 5 УРОЖАЙ Коопторг Сахар 0. 94 ТУЛЬСКИЙ Универсам Сахар 1. УРОЖАЙ Коопторг
Он получен следующим образом: СУБД последовательно формирует строки декартова произведения таблиц, перечисленных во фразе FROM, проверяет, удовлетворяют ли данные сформированной строки условиям фразы WHERE, и если удовлетворяют, то включает в ответ на запрос те ее поля, которые перечислены во фразе SELECT. Следует подчеркнуть, что в SELECT и WHERE (во избежание двусмысленности) ссылки на все (*) или отдельные столбцы могут (а иногда и должны) уточняться именем соответствующей таблицы, например, Поставки. ПС, Поставщики. ПС, Меню. *, Состав. БЛ, Блюда. * и т. п. Очевидно, что с помощью соединения несложно сформировать запрос на обработку данных из нескольких таблиц. Кроме того, в такой запрос можно включить любые части предложения SELECT, рассмотренные в главе 2 (выражения с использованием функций, группирование с отбором указанных групп и упорядочением полученного результата). Следовательно, соединения позволяют обрабатывать множество взаимосвязанных таблиц как единую таблицу, в которой перемешана информация о многих типах сущностей. Поэтому начинающий проектант базы данных может спокойно создавать маленькие нормализованные таблицы, так как он всегда может получить из них любую “большую” таблицу. Кроме механизма соединений в SQL есть механизм вложенных подзапросов, позволяющий объединить несколько простых запросов в едином предложении SELECT. Иными словами, вложенный подзапрос – это уже знакомый нам подзапрос (с небольшими огра-ничениями), который вложен в WHERE фразу другого вложенного подзапроса или WHERE фразу основного запроса. Для иллюстрации вложенного подзапроса вернемся к предыдущему примеру и попробуем получить перечень тех поставщиков продуктов для Сырников, которые поставляют нужные продукты за минимальную цену.
SELECT Продукт, Цена, Название, Статус FROM Продукты, Состав, Блюда, Поставки, Поставщики WHERE Продукты. ПР = Состав. ПР AND Состав. БЛ = Блюда. БЛ AND Поставки. ПР = Состав. ПР AND Поставки. ПС = Поставщики. ПС AND Блюдо = 'Сырники' AND Цена = ( SELECT MIN(Цена) FROM Поставки X WHERE X. ПР = Поставки. ПР ); Результат запроса имеет вид Продукт Цена Название Статус Яйца 1. 8 ПОРТОС Кооператив Сахар 0. 94 ТУЛЬСКИЙ Универсам Мука 0. 5 УРОЖАЙ Коопторг Сметана 2. 2 ОГУРЕЧИК Ферма Творог 1. ОГУРЕЧИК Ферма
Здесь с помощью подзапроса, размещенного в трех последних строках запроса, описывается процесс определения минимальной цены каждого продукта для Сырников и поиск поставщика, предлагающего этот продукт за такую цену.
Запросы, использующие соединения Декартово произведение таблиц
Так как декартово произведение n таблиц – это таблица, содержащая все возможные строки r, такие, что r является сцеплением какой-либо строки из первой таблицы, строки из второй таблицы, … и строки из n-й таблицы (а мы уже научились выделять с помощью SELECT любое подмножество реляционной таблицы), то осталось лишь выяснить, можно ли с помощью SELECT получить декартово произведение. Для получения декартова произведения нескольких таблиц надо указать во фразе FROM перечень перемножаемых таблиц, а во фразе SELECT – все их столбцы. Так, для получения декартова произведения Вид_блюд и Трапезы надо выдать запрос
SELECT Вид_блюд. *, Трапезы. * FROM Вид_блюд, Трапезы; Получим таблицу, содержащую 5 х 3 = 15 строк: В Вид Т Трапеза З Закуска 1 Завтрак З Закуска 2 Обед З Закуска 3 Ужин С Суп 1 Завтрак С Суп 2 Обед С Суп 3 Ужин Г Горячее 1 Завтрак Г Горячее 2 Обед Г Горячее 3 Ужин Д Десерт 1 Завтрак Д Десерт 2 Обед Д Десерт 3 Ужин Н Напиток 1 Завтрак Н Напиток 2 Обед Н Напиток 3 Ужин
В другом примере, где перемножаются таблицы Меню, Трапезы, Вид_блюд, Блюда:
SELECT Меню. *, Трапезы. *, Вид_блюд. *, Блюда. * FROM Меню, Трапезы, Вид_блюд, Блюда;
образуется таблица (рис 2. 6), содержащая 21 х 3 х 5 х 33 = 10395 строк.
Эквисоединение таблиц
Если из декартова произведения убрать ненужные строки и столбцы, то можно получить актуальные таблицы, соответствующие любому из соединений.
Меню Трапезы Вид_блюд Блюда Т В БЛ Т Трапеза В Вид БЛ Блюдо В Основа Выход Труд 1 З 3 1 Завтрак З Закуска 1 Салат летний З Овощи 200. 3 1 З 3 1 Завтрак З Закуска 2 Салат мясной З Мясо 200. 4 1 З 3 1 Завтрак З Закуска 3 Салат витаминный З Овощи 200. 4 * ... . 1 З 3 1 Завтрак З Закуска 12 Суп молочный С Молоко 500. 3 1 З 3 1 Завтрак З Закуска 13 Бастурма Г Мясо 300. 5 ... . 1 З 3 1 Завтрак З Закуска 32 Кофе черный Н Кофе 100. 1 1 З 3 1 Завтрак З Закуска 33 Кофе на молоке Н Кофе 200. 2 1 З 6 1 Завтрак З Закуска 1 Салат летний З Овощи 200. 3 1 З 6 1 Завтрак З Закуска 2 Салат мясной З Мясо 200. 4 1 З 6 1 Завтрак З Закуска 3 Салат витаминный З Овощи 200. 4 1 З 6 1 Завтрак З Закуска 4 Салат рыбный З Рыба 200. 4 1 З 6 1 Завтрак З Закуска 5 Паштет из рыбы З Рыба 120. 5 1 З 6 1 Завтрак З Закуска 6 Мясо с гарниром З Мясо 250. 3 * ... . Рисунок 2. 5
Страницы: 1, 2, 3, 4, 5, 6, 7
|