Организация Web-доступа к базам данных с использованием SQL-запросов
| |
|Со| | |
|ст| | |
|а | | |
|WH| | |
|ER| | |
|E | | |
|БЛ| | |
|= | | |
|1 | | |
| | |Лук |
| | |Помидоры |
| | |Зелень |
| | |Яблоки |
| | |Сахар |
Из этого простого примера видно, что избыточные дубликаты всегда
исключаются из результата UNION. Поэтому, хотя в рассматриваемом примере
Помидоры, Зелень и Яблоки выбираются обеими из двух составляющих
предложения SELECT, в окончательном результате они появляются только один
раз.
Предложением с UNION можно объединить любое число таблиц (проекций
таблиц). Так, к предыдущему запросу можно добавить (перед точкой с запятой)
конструкцию
UNION
SELECT Продукт
FROM Продукты
WHERE Ca < 250
позволяющую добавить к списку продуктов Масло, Рис, Мука и Кофе. Однако тот
же результат можно получить простым изменением фразы WHERE первой части
исходного запроса
WHERE Жиры = 0 OR Ca < 250
Реализация операций реляционной алгебры предложением SELECT
С помощью предложения SELECT можно реализовать любую операцию
реляционной алгебры.
Селекция (горизонтальное подмножество) таблицы создается из тех ее
строк, которые удовлетворяют заданным условиям. Пример:
SELECT *
FROM Блюда
WHER Основа = 'Молоко'
AND Выход 200;
Проекция (вертикальное подмножество) таблицы создается из указанных ее
столбцов (в заданном порядке) с последующим исключением избыточных
дубликатов строк. Пример:
SELECT DISTINCT Блюдо, Выход, Основа
FROM Блюда;
Объединение двух таблиц содержит те строки, которые есть либо в
первой, либо во второй, либо в обеих таблицах. Пример:
SELECT Блюдо, Основа, Выход
FROM Блюда
WHER Основа = 'Овощи'
UNION
SELECT Блюдо, Основа, Выход
FROM Блюда
WHER В = 'Г';
Пересечение двух таблиц содержит только те строки, которые есть и в
первой, и во второй. Пример:
SELECT БЛ
FROM Состав
WHERE БЛ IN
( SELECT БЛ
FROM Меню);
Разность двух таблиц содержит только те строки, которые есть в первой,
но отсутствуют во второй. Пример:
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;
Рисунок 2.7
Такой результат, нестрого говоря, строился следующим образом.
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.
Особенности и
| |  скачать работу |
Организация Web-доступа к базам данных с использованием SQL-запросов |
