SELECT БЛ
FROM Состав
WHERE БЛ NOT IN
( SELECT БЛ
FROM Меню);
Здесь опущено лишь достаточно нудное описание редко встречаемой операция деления, которая также может быть реализована предложением SELECT с коррелированными вложенными подзапросами.
Резюме
Знакомство с возможностями предложения SELECT показало, что с его помощью можно реализовать все реляционные операции. Кроме того, в предложении SELECT выполняются разнообразные вычисления, агрегирование данных, их упорядочение и ряд других операций, позволяющих описать в одном предложении ту работу, для выполнения которой потребовалось бы написать несколько страниц программы на алгоритмических языках Си, Паскаль или на внутренних языках ряда распространенных СУБД.
Например, пусть требуется получить калорийность и стоимость тех блюд, для которых:
· есть все составляющие их продукты;
· калорийность не превышает 400 ккал;
· стоимость не превышает 1.5 рубля, а результат надо упорядочить по возрастанию калорийности блюд в рамках их видов.
Для этого можно дать запрос, показанный на рис. 2.7, позволяющий получить искомый результат в виде таблицы
| Вид | Блюдо | ||||||||
| Горячее | Помидоры с луком | калорий - | 244.6 | 0.44 | руб | ||||
| Горячее | Бефстроганов | калорий - | 321.3 | 0.53 | руб | ||||
| Горячее | Драчена | калорий - | 333.9 | 0.33 | руб | ||||
| Горячее | Каша рисовая | калорий - | 339.2 | 0.27 | руб | ||||
| Горячее | Омлет с луком | калорий - | 354.9 | 0.36 | руб | ||||
| Десерт | Яблоки печеные | калорий - | 170.2 | 0.30 | руб | ||||
| Десерт | Крем творожный | калорий - | 394.3 | 0.27 | руб | ||||
| Закуска | Салат летний | калорий - | 155.5 | 0.32 | руб | ||||
| Закуска | Салат витаминный | калорий - | 217.4 | 0.37 | руб | ||||
| Закуска | Творог | калорий - | 330.0 | 0.22 | руб | ||||
| Закуска | Мясо с гарниром | калорий - | 378.7 | 0.62 | руб | ||||
| Напиток | Кофе черный | калорий - | 7.1 | 0.05 | руб | ||||
| Напиток | Компот | калорий - | 74.4 | 0.14 | руб | ||||
| Напиток | Кофе на молоке | калорий - | 154.8 | 0.11 | руб | ||||
| Напиток | Молочный напиток | калорий - | 264.9 | 0.34 | руб | ||||
| Суп | Суп молочный | калорий - | 396.6 | 0.22 | руб | ||||
SELECT Вид, Блюдо, 'калорий –',
(SUM(INT((Белки+Углев)*4.1+Жиры*9.3)*Вес/1000)),
(SUM(Стоимость/К_во*Вес/1000)+MIN(Труд/100)),’руб’
FROM Блюда, Вид_блюд, Состав, Продукты, Наличие
WHERE Блюда.БЛ = Состав.БЛ
AND Состав.ПР = Продукты.ПР
AND Состав.ПР = Наличие.ПР
AND Блюда.В = Вид_блюд.В
AND БЛ NOT IN
( SELECT БЛ
FROM Состав
WHERE ПР IN
( SELECT ПР
FROM Наличие
WHERE К_во = 0))
GROUP BY Вид, Блюдо
HAVING SUM(Стоимость/К_во*Вес/1000+MIN(Труд/100)) < 1.5
AND SUM(((Белки+Углев)*4.1+Жиры*9.3)*Вес/1000) < 400
ORDER BY Вид, 4;
Такой результат, нестрого говоря, строился следующим образом.
1. FROM. Эта фраза инициирует создание в рабочей памяти таблицы, являющейся декартовым произведением таблиц Блюда, Вид_блюд, Состав, Продукты и Наличие.
2. WHERE. Эта фраза нужна для преобразования полученного декартова произведения в естественное соединение и удаления из последнего строк с кодами блюд, не обеспеченных продуктами. Естественное соединение образуется путем вычеркивания строк, где не совпадают: код блюда из таблицы Блюда с кодом блюда из таблицы Состав, код продукта из таблицы Состав с кодом продукта из таблицы Продукты и т.д. Обеспеченность блюда всеми продуктами проверяется с помощью последовательности подзапросов. Внутренний подзапрос выдает перечень кодов продуктов, которых нет в кладовой пансионата. Следующий подзапрос выдает коды тех блюд, в состав которых должны входить «отсутствующие» продукты. И, наконец, из естественного соединения вычеркиваются строки с кодами полученных блюд (точнее оставляются строки «Где код блюда не принадлежит перечню кодов блюд, полученному в подзапросе».
3. SELECT. Из полученного соединения удаляются столбцы, не используемые в выражениях SELECT или других фразах. Если в списке SELECT есть выражения (константы), то для хранения их значений формируются дополнительные столбцы и инициируются операции по их заполнению. В рассматриваемом примере будут сохранены столбцы Вид, Блюдо, Белки, Углев, Жиры, Вес, Стоимость, К_во и созданы дополнительные столбцы для формирования и хранения значений стоимости и калорийности составляющих каждого блюда, а также для хранения текстовых констант 'калорий –' и 'руб'. Обратите внимание на прием, использованный при суммировании стоимостей продуктов, входящих в состав блюда, и стоимости его приготовления (Труд): можно ли заменить MIN на MAX или AVG?
4. GROUP BY. Отредактированное естественное соединение группируется по видам блюд и их названиям. Создаются группы горячих блюд, десертов и т.д., а внутри каждой группы создаются подгруппы строк со сведениями о продуктах, относящихся к конкретному блюду группы.
5. SELECT. Каждая подгруппа строк, полученная на предыдущем шаге, преобразуется в единственную строку для результата. В нее заносится вид блюда (общий для всех подгрупп группы), название блюда (общее для всех строк подгруппы), две текстовых константы ('калорий –' и 'руб') и две суммы. Последние формируются путем суммирования тех значений дополнительных столбцов, которые принадлежат подгруппе.
6. HAVING. Сформированные строки, не удовлетворяющие условиям фразы HAVING
SUM(Стоимость/К_во*Вес/1000+MIN(Труд/100)) < 1.5 и
SUM(((Белки+Углев)*4.1+Жиры*9.3)*Вес/1000) < 400
исключаются из результата предыдущего шага.
7. ORDER BY. Результат шага 6 упорядочивается в соответствии со списком фразы ORDER BY для получения окончательного результата. Сначала строки группируются по видам блюд (в алфавитном порядке), а затем – по значению элемента данных, указанного на четвертом месте фразы SELECT, т.е. по калорийности.
Конечно, рассмотренный запрос весьма сложен, но попробуйте написать на любом знакомом вам языке программу, реализующую те же действия, и оцените сложность ее написания и отладки.
2.2.4. Модификация данных в таблицах SQL.
Особенности и синтаксис предложений модификации
Модификация данных может выполняться с помощью предложений DELETE (удалить), INSERT (вставить) и UPDATE (обновить). Подобно предложению SELECT они могут оперировать как базовыми таблицами, так и представлениями. Однако по ряду причин не все представления являются обновляемыми. Пока зафиксируем этот факт, отложив описание представлений и особенностей их обновления до главы 5, но будем помнить, что термин «представление» относится только к обновляемым представлениям.
Предложение DELETE имеет формат
DELETE
FROM базовая таблица | представление
[WHERE фраза];
и позволяет удалить содержимое всех строк указанной таблицы (при отсутствии WHERE фразы) или тех ее строк, которые выделяются WHERE фразой.
Предложение INSERT имеет один из следующих форматов:
INSERT
INTO {базовая таблица | представление} [(столбец [,столбец] …)]
VALUES ({константа | переменная} [,{константа | переменная}] …);
или
INSERT
INTO {базовая таблица | представление} [(столбец [,столбец] …)]
подзапрос;
В первом формате в таблицу вставляется строка со значениями полей, указанными в перечне фразы VALUES (значения), причем i-е значение соответствует i-му столбцу в списке столбцов (столбцы, не указанные в списке, заполняются NULL-значениями). Если в списке VALUES фразы указаны все столбцы модифицируемой таблицы и порядок их перечисления соответствует порядку столбцов в описании таблицы, то список столбцов в фразе INTO можно опустить. Однако не советуем этого делать, так как при изменении описания таблицы (перестановка столбцов или изменение их числа) придется переписывать и INSERT предложение.
Во втором формате сначала выполняется подзапрос, т.е. по предложению SELECT в памяти формируется рабочая таблица, а потом строки рабочей таблицы загружаются в модифицируемую таблицу. При этом i-й столбец рабочей таблицы (i-й элемент списка SELECT) соответствует i-му столбцу в списке столбцов модифицируемой таблицы. Здесь также при выполнении указанных выше условий может быть опущен список столбцов фразы INTO.
Предложение UPDATE также имеет два формата. Первый из них:
UPDATE (базовая таблица | представление}
SET столбец = значение [, столбец = значение] …
[WHERE фраза]
где значение – это
столбец | выражение | константа | переменная
и может включать столбцы лишь из обновляемой таблицы, т.е. значение одного из столбцов модифицируемой таблицы может заменяться на значение ее другого столбца или выражения, содержащего значения нескольких ее столбцов, включая изменяемый.
При отсутствии WHERE фразы обновляются значения указанных столбцов во всех строках модифицируемой таблицы. WHERE фраза позволяет сократить число обновляемых строк, указывая условия их отбора.
Второй формат описывает предложение, позволяющее производить обновление значений модифицируемой таблицы по значениям столбцов из других таблиц. К сожалению в ряде СУБД эти форматы отличаются друг от друга и от стандарта. Для примера приведем один из таких форматов:
UPDATE {базовая таблица | представление}
SET столбец = значение [, столбец = значение] …
FROM {базовая таблица | представление} [псевдоним],
{базовая таблица | представление} [псевдоним]
[,{базовая таблица | представление} [псевдоним]] …
[WHERE фраза]
Здесь перечень таблиц фразы FROM содержит имя модифицируемой таблицы и тех таблиц, значения столбцов которых используются для обновления. При этом, естественно, таблицы должны быть связаны между собой в WHERE фразе, которая, кроме того, служит для указания условий отбора обновляемых строк модифицируемой таблицы.