Организация 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