SELECT ПР, SUM(К_во), COUNT(К_во)
FROM Поставки
WHERE ПР = 10;
то было бы получено сообщение об ошибке. Это связано с тем, что SQL-функция создает единственное значение из множества значений столбца-аргумента, а для “свободного” столбца должно быть выдано все множество его значений. Без специального указания (оно задается фразой GROUP BY) SQL не будет выяснять, одинаковы значения этого множества (как в данном примере, где ПР=10) или различны (как было бы при отсутствии WHERE фразы). Поэтому подобный запрос отвергается системой. Правда, никто не запрещает дать запрос
SELECT 'Кол-во лука =', SUM(К_во), COUNT(К_во)
FROM Поставки
WHERE ПР = 10;
Результат:
'Кол-во лука ='
SUM(К_во)
COUNT(К_во)
Кол-во лука =
220
2
Отметим также, что в столбце-аргументе перед применением любой функции, кроме COUNT(*), исключаются все неопределенные значения. Если оказывается, что аргумент – пустое множество, функция COUNT принимает значение 0, а остальные – NULL. Например, для получения суммы цен, средней цены, количества поставляемых продуктов и количества разных цен продуктов, проданных коопторгом УРОЖАЙ (ПС=5), а также для получения количества продуктов, которые могут поставляться этим коопторгом, можно дать запрос
SELECT SUM(Цена), AVG(Цена), COUNT(Цена),
COUNT(DISTINCT Цена), COUNT(*)
FROM Поставки
WHERE ПС = 5;
и получить
SUM(Цена)
AVG(Цена)
COUNT(Цена)
COUNT(DISTINCT Цена)
COUNT (*)
6. 2
1. 24
5
4
7
В другом примере, где надо узнать “Сколько поставлено моркови и сколько поставщиков ее поставляют? ”:
SELECT SUM(К_во), COUNT(К_во)
FROM Поставки
WHER ПР = 2;
будет получен ответ:
SUM(К_во)
COUNT (К_во)
-0
0
Наконец, попробуем получить сумму массы поставленного лука с его средней ценой (“Сапоги с яичницей”):
Результат:
SELECT (SUM(К_во) +AVG(Цена))
FROM Поставки
WHERE ПР = 10;
SUM(К_во)+AVG(Цена)
220. 6
Фраза GROUP BY
Мы показали, как можно вычислить массу определенного продукта, поставляемого поставщиками. Предположим, что теперь требуется вычислить общую массу каждого из продуктов, поставляемых в настоящее время поставщиками. Это можно легко сделать с помощью предложения
SELECT ПР, SUM(К_во)
FROM Поставки
GROUP BY ПР;
Результат показан на рис. 2. 5, а.
а)
б)
в)
г)
ПР
ПР
9
0
0
11
150
150
12
30
30
15
370
70
1
370
370
3
250
250
5
170
70
6
220
140
8
150
150
7
200
200
2
0
0
4
100
100
13
190
190
14
70
70
16
250
250
17
50
50
10
220
220
Рисунок 2. 4
Фраза 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
