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

оказывается, что аргумент – пустое множество, функция 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 |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 |З |3 |1 |Завт|З |Заку|3 |Сала|З |Овощ|200.|4 |

| | | | |рак | |ска | |т | |и | | |

| | | | | | | | |вита| | | | |

| | | | | | | | |минн| | | | |

| | | | | | | | |ый | | | | |

|1 |З |6 |1 |Завт|З |Заку|6 |Мясо|З |Мясо|250.|3 |

| | | | |рак | |ска | |с | | | | |

| | | | | | | | |гарн| | | | |

| | | | | | | | |иром| | | | |

|1 |Г |19 |1 |Завт|Г |Горя|19 |Омле|Г |Яйца|200.|5 |

| | | | |рак | |чее | |т с | | | | |

| | | | | | | | |луко| | | | |

| | | | | | | | |м | | | | |

|. . | | | | | | | | | | | | |

|. | | | | | | | | | | | | |

|3 |Г |16 |3 |Ужин|Г |Горя|16 |Драч|Г |Яйца|180.|4 |

| | | | | | |чее | |ена | | | | |

|3 |Н |30 |3 |Ужин|Н |Напи|30 |Комп|Н |Фрук|200.|2 |

| | | | | | |ток | |от | |ты | | |

|3 |Н |31 |3 |Ужин|Н |Напи|31 |Моло|Н |Моло|200.|2 |

| | | | | | |ток | |чный| |ко | | |

| | | | | | | | |напи| | | | |

| | | | | | | | |ток | | | | |

Естественное соединение таблиц

Легко заметить, что в эквисоединение таблиц вошли дубликаты столбцов,

по которым проводилось соединение (Т, В и БЛ). Для исключения этих

дубликатов можно создать естественное соединение тех же таблиц:

SELECT Т, В, БЛ, Трапеза, Вид, Блюдо, Основа, Выход, Труд

FROM Меню, Трапезы, Вид_блюд, Блюда

WHERE Меню.Т = Трапезы.Т

AND Меню.В = Вид_блюд.В

AND Меню.БЛ = Блюда.БЛ;

Реализация естественного соединения таблиц имеет вид

|Т |В |БЛ |Трапез|Вид |Блюдо |Основа|Выход |Труд |

| | | |а | | | | | |

|1 |З |3 |Завтра|Закуск|Салат |Овощи |200. |4 |

| | | |к |а |витами| | | |

| | | | | |нный | | | |

|1 |З |6 |Завтра|Закуск|Мясо с|Мясо |250. |3 |

| | | |к |а |гарнир| | | |

| | | | | |ом | | | |

|1 |Г |19 |Завтра|Горяче|Омлет |Яйца |200. |5 |

| | | |к |е |с | | | |

| | | | | |луком | | | |

|… | | | | | | | | |

|3 |Г |16 |Ужин |Горяче|Драчен|Яйца |180. |4 |

| | | | |е |а | | | |

|3 |Н |30 |Ужин |Напито|Компот|Фрукты|200. |2 |

| | | | |к | | | | |

|3 |Н |31 |Ужин |Напито|Молочн|Молоко|200. |2 |

| | | | |к |ый | | | |

| | | | | |напито| | | |

| | | | | |к | | | |

Композиция таблиц

Для исключения всех столбцов, по которым проводится соединение таблиц,

надо создать композицию

SELECT Трапеза, Вид, Блюдо, Основа, Выход, Труд

FROM Меню, Трапезы, Вид_блюд, Блюда

WHERE Меню.Т = Трапезы.Т

AND Меню.В = Вид_блюд.В

AND Меню.БЛ = Блюда.БЛ;

имеющую вид

|Трапеза |Блюдо |Вид |Основа |Выход |Труд |

|Завтрак |Салат витаминный |Закуска |Овощи |200. |4 |

|Завтрак |Мясо с гарниром |Закуска |Мясо |250. |3 |

|Завтрак |Омлет с луком |Горячее |Яйца |200. |5 |

|. . . | | | | | |

|Ужин |Драчена |Горячее |Яйца |180. |4 |

|Ужин |Компот |Напиток |Фрукты |200. |2 |

|Ужин |Молочный напиток |Напиток |Молоко |200. |2 |

Тета-соединение таблиц

В базе данных ПАНСИОН трудно подобрать несложный пример,

иллюстрирующий тета-соединение таблиц. Поэтому сконструируем такой

надуманный запрос:

SELECT Вид_блюд.*, Трапезы.*

FROM Вид_блюд, Трапезы

WHERE Вид Трапеза;

позволяющий выбрать из полученного декартова произведения таблиц Вид_блюд и

Трапезы лишь те строки, в которых значение трапезы «меньше» (по алфавиту)

значения вида блюда:

|В |Вид |Т |Трапеза |

|З |Закуска |1 |Завтрак |

|С |Суп |1 |Завтрак |

|С |Суп |2 |Обед |

|Н |Напиток |1 |Завтрак |

Соединение таблиц с дополнительным условием

При формировании соединения создается рабочая таблица, к которой

применимы все операции: отбор нужных строк соединения (WHERE фраза),

упорядочение получаемого результата (ORDER BY фраза) и агрегатирование

данных (SQL-функции и GROUP BY фраза).

Например, для получения перечня блюд, предлагаемых в меню на завтрак,

можно сформировать запрос на основе композиции:

SELECT Вид, Блюдо, Основа, Выход, 'Номер –', БЛ

FROM Меню, Трапезы, Вид_блюд, Блюда

WHERE Меню.Т = Трапезы.Т

AND Меню.В = Вид_блюд.В

AND Меню.БЛ = Блюда.БЛ

AND Трапеза = ’Завтрак’;

|Вид |Блюдо |Основа |Выхо|'Номе|БЛ |

| | | |д |р –' | |

|Закуск|Салат |Овощи |200.|Номер|3 |

|а |витаминны| | |- | |

| |й | | | | |

|Закуск|Мясо с |Мясо |250.|Номер|6 |

|а |гарниром | | |- | |

|Горяче|Омлет с |Яйца |200.|Номер|19 |

|е |луком | | |- | |

|Горяче|Пудинг |Крупа |160.|Номер|21 |

|е |рисовый | | |- | |

|Напито|Молочный |Молоко |200.|Номер|31 |

|к |напиток | | |- | |

|Напито|Кофе |Кофе |100.|Номер|32 |

|к |черный | | |- | |

Соединение таблицы со своей копией

В ряде приложений возникает необходимость одновременной обработки

данных какой-либо таблицы и одной или нескольких ее копий, создаваемых на

время выполнения запроса.

Например, при создании списков студентов (таблица Студенты) возможен

повторный ввод данных о каком-либо студенте с присвоением ему второго

номера зачетной книжки. Для выявления таких ошибок можно соединить таблицу

Студенты с ее временной копией, установив в WHERE фразе равенство значений

всех одноименных столбцов этих таблиц кроме столбцов с номером зачетной

книжки (для последних надо установить условие неравенства значений).

Временную копию таблицы можно сформировать, указав имя псевдонима за

именем таблицы во фразе FROM. Так, с помощью фразы

FROM Блюда X, Блюда Y, Блюда Z

будут сформированы три копии таблицы Блюда с именами X, Y и Z.

В качестве примера соединения таблицы с ней самой сформируем запрос на

вывод таких пар блюд таблицы Блюда, в которых совпадает основа, а название

первого блюда пары меньше (по алфавиту), чем номер второго блюда пары. Для

этого можно создать запрос с одной копией таблицы Блюда (Копия):

SELECT Блюдо, Копия.Блюдо, Основа

FROM Блюда, Блюда Копия

WHERE Основа = Копия.Основа

AND Блюдо < Копия.Блюдо;

или двумя ее копиями (Первая и Вторая):

SELECT Первая.Блюдо, Вторая.Блюдо, Основа

FROM Блюда Первая, Блюда Вторая

WHERE Первая.Основа = Вторая.Основа

AND Первая.Блюдо < Вторая.Блюдо;

Получим результат вида

|Первая.Блюдо |Вторая.Блюдо |Основа |

|Морковь с рисом |Помидоры с луком |Овощи |

|Морковь с рисом |Салат летний |Овощи |

|Морковь с рисом |Салат витаминный |Овощи |

|Помидоры с луком |Салат витаминный |Овощи |

|Помидоры с луком |Салат летний |Овощи |

|Салат витаминный |Салат летний |Овощи |

|Бастурма |Бефстроганов |Мясо |

|Бастурма |Мясо с гарниром |Мясо |

|Бефстроганов |Мясо с гарниром |Мясо |

Вложенные подзапросы

Виды вложенных подзапросов

Вложенный подзапрос – это подзапрос, заключенный в круглые скобки и

вложенный в WHERE (HAVING) фразу предложения SELECT или других предложений,

использующих WHERE фразу. Вложенный подзапрос может содержать в своей WHERE

(HAVING) фразе другой вложенный подзапрос и т.д. Нетрудно догадаться, что

вложенный подзапрос создан для того, чтобы при отборе строк таблицы,

сформированной основным запросом, можно было использовать данные из других

таблиц (например, при отборе блюд для меню использовать данные о наличии

продуктов в кладовой пансионата).

Существуют простые и коррелированные вложенные подзапросы. Они

включаются в WHERE (HAVING) фразу с помощью условий IN, EXISTS или одного

из условий сравнения ( = | < | < | X.ПС ); | |

| |17 |

Действие этого запроса можно пояснить следующим образом: «Поочередно

для каждой строки таблицы Поставки, скажем X, выделить значение номера

продукта (ПР), если и только если это значение не входит в некоторую

строку, скажем, Y, той же таблицы, а значение столбца номер поставщика (ПС)

в строке Y не равно его значению в строке X».

Отметим, что в этой формулировке должен быть использован по крайней

мере один псевдоним – либо X, либо Y.

Запросы, использующие EXISTS

Квантор EXISTS (существует) – понятие, заимствованное из формальной

логики. В языке SQL предикат с квантором существования представляется

выражением EXISTS (SELECT * FROM …).

Такое выражение считается истинным только тогда, когда результат

вычисления «SELECT * FROM …» является непустым множеством, т.е. когда

существует какая-либо запись в таблице, указанной во фразе FROM подзапроса,

которая удовлетворяет условию WHERE подзапроса. (Практически этот подзапрос

всегда будет коррелированным множеством.)

Рассмотрим примеры. Выдать названия поставщиков, поставляющих продукт

с номером 11.

| |Результат: |

| SELECT Название |Название |

|FROM Поставщики | |

|WHERE EXISTS | |

|( SELECT * | |

|FROM Поставки | |

|WHERE ПС = Поставщики.ПС | |

|AND ПР = 11 ); | |

| |СЫТНЫЙ |

| |УРОЖАЙ |

| |КОРЮШКА |

| |ЛЕТО |

Система последовательно выбирает строки таблицы Поставщики, выделяет

из них значения столбцов Название и ПС, а затем проверяет, является ли

истинным условие существования, т.е. су-ществует ли в таблице Поставки хотя

бы одна строка со значением ПР=11 и значением ПС, равным значению ПС,

выбранному из таблицы Поставщики. Если условие выполняется, то полученное

значение столбца Название включается в результат.

Предположим, что первые значения полей Название и ПС равны,

соответственно, 'СЫТНЫЙ' и 1. Так как в таблице Поставки есть строка с

ПР=11 и ПС=1, то значение 'СЫТНЫЙ' должно быть включено в результат.

Хотя этот первый пример только показывает иной способ формулировки

запроса для задачи, решаемой и другими путями (с помощью оператора IN или

соединения), EXISTS представляет собой одну из наиболее важных возможностей

SQL. Фактически любой запрос, который выражается через IN, может быть

альтернативным образом сформулирован также с помощью EXISTS. Однако

обратное высказывание несправедливо.

Выдать название и статус поставщиков, не поставляющих продукт с номером 11.

| |Результат: |

| SELECT Название, Статус |Название |Статус |

|FROM Поставщики | | |

|WHERE NOT EXISTS | | |

|( SELECT * | | |

|FROM Поставки | | |

|WHERE ПС = Поставщики.ПС | | |

|AND ПР = 11 ); | | |

| |ПОРТОС |кооператив|

| |ШУШАРЫ |совхоз |

| |ТУЛЬСКИЙ |универсам |

| |ОГУРЕЧИК |ферма |

Функции в подзапросе

Теперь, после знакомства с различными формулировками вложенных

подзапросов и псевдонимами легче понять текст и алгоритм реализации запроса

на получение тех поставщиков продуктов для Сырников, которые поставляют эти

продукты за минимальную цену:

SELECT Продукт, Цена, Название, Статус

FROM Продукты, Состав, Блюда, Поставки, Поставщики

WHERE Продукты.ПР = Состав.ПР

AND Состав.БЛ = Блюда.БЛ

AND Поставки.ПР = Состав.ПР

AND Поставки.ПС = Поставщики.ПС

AND Блюдо = 'Сырники'

AND Цена = ( SELECT MIN(Цена)

FROM Поставки X

WHERE X.ПР = Поставки.ПР );

Естественно, что это коррелированный подзапрос: здесь сначала

определяется минимальная цена продукта, входящего в состав Сырников, и

только затем выясняется его поставщик.

На этом примере мы закончим знакомство с вложенными подзапросами,

предложив попробовать свои силы в составлении ряда запросов, с помощью

механизма таких подзапросов:

1. Выдать названия всех мясных блюд.

2. Выдать количество всех блюд, в состав которых входят помидоры.

3. Выдать блюда, продукты для которых поставляются агрофирмой ЛЕТО.

Объединение (UNION)

Для SQL это означает, что две таблицы можно объединять тогда и только

тогда, когда:

1. они имеют одинаковое число столбцов, например, m;

2. для всех i (i = 1, 2, …, m) i-й столбец первой таблицы и i-й столбец

второй таблицы имеют в точности одинаковый тип данных.

Например, выдать названия продуктов, в которых нет жиров, либо входящих в

состав блюда с кодом БЛ = 1:

|Результат: |Продукт |

| | |Майонез |

|SE| | |

|LE| | |

|CT| | |

|Пр| | |

|од| | |

|ук| | |

|т | | |

|FR| | |

|OM| | |

|Пр| | |

|од| | |

|ук| | |

|ты| | |

| | | |

|WH| | |

|ER| | |

|E | | |

|Жи| | |

|ры| | |

|= | | |

|0 | | |

|UN| | |

|IO| | |

|N | | |

|SE| | |

|LE| | |

|CT| | |

|Пр| | |

|од| | |

|ук| | |

|т | | |

|FR| | |

|OM| | |

|Со| | |

|ст| | |

|а | | |

Страницы: 1, 2, 3, 4, 5, 6