Смекни!
smekni.com

Организация Web-доступа к базам данных с использованием SQL-запросов (стр. 8 из 22)

Фраза 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 строк.