Организация Web-доступа к базам данных с использованием SQL-запросов
значение 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 |1 |9 |-0- |-0- |1 |370 |9 |0 |
|11 |150 |3 |9 |-0- |-0- |2 |0 |11 |150 |
|12 |30 |5 |9 |-0- |-0- |3 |250 |12 |30 |
|15 |370 |1 |11 |1.50 |50 |4 |100 |15 |70 |
|1 |370 |5 |11 |-0- |-0- |5 |170 |1 |370 |
|3 |250 |6 |11 |-0- |-0- |6 |220 |3 |250 |
|5 |170 |8 |11 |1.00 |100 |7 |200 |5 |70 |
|6 |220 |1 |12 |3.00 |10 |8 |150 |6 |140 |
|8 |150 |3 |12 |2.50 |20 |9 |0 |8 |150 |
|7 |200 |6 |12 |-0- |-0- |10 |220 |7 |200 |
|2 |0 |1 |15 |2.00 |170 |11 |150 |2 |0 |
|4 |100 |3 |15 |1.50 |200 |12 |30 |4 |100 |
|13 |190 |2 |1 |3.60 |300 |13 |190 |13 |190 |
|14 |70 |7 |1 |4.20 |70 |14 |70 |14 |70 |
|16 |250 |2 |3 |-0- |-0- |15 |370 |16 |250 |
|17 |50 |7 |3 |4.00 |250 |16 |250 |17 |50 |
|10 |220 |. . .| | | |17 |50 |10 |220 |
Рисунок 2.5
Фраза 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.6
Очевидно, что отбор актуальных строк обеспечивается вводом в запрос WHERE
фразы, в которой устанавливается соответствие между:
кодами трапез (Т) в таблицах Меню и Трапезы (Меню.Т = Трапезы.Т),
кодами видов блюд (В) в таблицах Меню и Вид_блюд (Меню.В = Вид_блюд.В),
номерами блюд (БЛ) в таблицах Меню и Блюда (Меню.БЛ = Блюда.БЛ).
Такой скорректированный запрос
SELECT Меню.*, Трапезы.*, Вид_блюд.*, Блюда.*
FROM Меню, Трапезы, Вид_блюд, Блюда
WHERE Меню.Т = Трапезы.Т
AND Меню.В = Вид_блюд.В
AND Меню.БЛ = Блюда.БЛ;
позволит получить эквисоединение таблиц Меню, Трапезы, Вид_блюд и Блюда:
Страницы: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
|