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

Особенности и синтаксис предложений модификации

Модификация данных может выполняться с помощью предложений 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 фразе,

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

модифицируемой таблицы.

В значениях, находящихся в правых частях равенств фразы SET, следует

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

(псевдонима).

Предложение INSERT

Вставка единственной записи в таблицу

Добавить в таблицу Блюда блюдо:

Шашлык (БЛ – 34, Блюдо – Шашлык, В – Г, Основа – Мясо, Выход – 150)

при неизвестной пока трудоемкости приготовления этого блюда.

INSERT

INTO Блюда (БЛ, Блюдо, В, Основа, Выход)

VALUES (34, 'Шашлык', 'Г', 'Мясо', 150);

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

значением в столбце Труд.

Порядок полей в INSERT не обязательно должен совпадать с порядком

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

такая версия предыдущего предложения:

INSERT

INTO Блюда (Основа, В, Блюдо, БЛ, Выход)

VALUES ('Мясо', 'Г', 'Шашлык', 34, 150);

При известной трудоемкости приготовления шашлыка (например, 5 коп)

сведения о нем можно ввести с помощью укороченного предложения:

INSERT

INTO Блюда

VALUES (34, 'Шашлык', 'Г', 'Мясо', 150, 5);

в котором должен соблюдаться строгий порядок перечисления вводимых

значений, так как, не имея перечня загружаемых столб-цов, СУБД может

использовать лишь перечень, который определен при создании модифицируемой

таблицы.

В предыдущих примерах проводилась модификация стержневой сущности,

т.е. таблицы с первичным ключом БЛ. Почти все СУБД имеют механизмы для

предотвращения ввода не уникального первичного ключа, например, ввода

«Шашлыка» под номером, меньшим 34. А как быть с ассоциациями или другими

таблицами, содержащими внешние ключи?

Пусть, например, потребовалось добавить в рецепт блюда Салат летний

(БЛ = 1) немного (15 г) лука (ПР = 10), и мы воспользовались предложением

INSERT

INTO Состав (БЛ, ПР, Вес)

VALUES (1, 10, 15);

Подобно операции DELETE операция INSERT может нарушить

непротиворечивость базы данных. Если не принять специальных мер, то СУБД не

проверяет, имеется ли в таблице Блюда блюдо с первичным ключом БЛ = 1 и в

таблице Продукты – продукт с первичным ключом ПР = 10. Отсутствие любого из

этих значений породит противоречие: в базе появится ссылка на

несуществующую запись. Проблемы, возникающие при использовании внешних

ключей, подробно рассмотрены в литературе, а здесь отме-тим, что все

«приличные» СУБД имеют механизмы для предотв-ращения ввода записей со

значениями внешних ключей, отсутст-вующих среди значений соответствующих

первичных ключей.

Вставка множества записей

Создать временную таблицу К_меню, содержащую калорийность и стоимость

всех блюд, которые можно приготовить из имеющихся продуктов. (Эта таблица

будет использоваться шеф-поваром для составления меню на следующий день.)

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

воспользоваться предложением CREATE TABLE

CREATE TABLE К_меню

( Вид CHAR (10),

Блюдо CHAR (60),

Калор_блюда INTEGER,

Стоим_блюда REAL);

а для ее загрузки данными – предложение INSERT с вложенным подзапросами:

INSERT

INTO К_меню

SELECT Вид, Блюдо,

INT(SUM(((Белки+Углев)*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 Вид, Блюдо

ORDER BY Вид, 3;

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

результат не выводится на экран, а копируется в таблицу К_меню. Теперь с

этой копией можно работать как с обычной базовой таблицей (Блюда, Про-

дукты,…), т.е. выбирать из нее даннные на экран или принтер, обновлять в

ней данные и т.п. Никакая из этих операций не будет оказывать влияния на

исходные данные (например, изменение в ней названия блюда Салат летний на

Салат весенний не приведет к подобному изменению в таблице Блюда, где

сохранится старое название). Так как это может привести к противоречиям, то

подобные временные таблицы уничтожают после их использования. Поэтому

программа, обслуживающая шеф-повара, должна исполнять предложение DROP

TABLE К_меню после того, как будет закончено составление меню.

Использование INSERT…SELECT для построения внешнего соединения

Рассмотренное в естественное соединение двух таблиц не включает тех

строк какой-либо из них, для которых нет соответствующих строк в другой

таблице. Например, если в таблицу Блюда были занесены под номером 34

сведения о Шашлыке, а рецепт его приготовления не был занесен в таблицу

Рецепты, то при загрузке их естественного соединения в таблицу Временная:

CREATE TABLE Временная

( Вид CHAR (8),

Блюдо CHAR (60),

Рецепт CHAR (560));

INSERT

INTO Временная

SELECT Вид, Блюдо, Рецепт

FROM Блюда, Рецепты, Вид_блюд

WHERE Блюда.БЛ = Рецепты.БЛ

AND Блюда.В = Вид_блюд.В;

в ней не окажется строки с Шашлыком (в таблице Рецепты не обнаружен код 34,

и строка с этим кодом исключена из результата).

Следовательно, в некотором смысле можно считать, что при обычном

соединении теряется информация для таких несоответствующих строк. Однако

иногда (как и в приведенном примере) может потребоваться способность

сохранить эту информацию. В этом случае можно воспользоваться так

называемым внешним соединением:

INSERT

INTO Временная

SELECT Вид, Блюдо, Рецепт

FROM Блюда, Рецепты, Вид_блюд

WHERE Блюда.БЛ = Рецепты.БЛ

AND Блюда.В = Вид_блюд.В;

INSERT

INTO Временная

SELECT Вид, Блюдо, «???»

FROM Блюда, Вид_блюд

WHERE Блюда.В = Вид_блюд.В

AND БЛ NOT IN

( SELECT БЛ

FROM Рецепты);

В результате будет создана базовая таблица

|Вид |Блюдо |Рецепт |

|Закуска |Салат летний |Помидоры и яблоки нарезать… |

|Закуска |Салат мясной |Вареное охлажденное мясо, … |

|. . . | | |

|Напиток |Кофе черный |Кофеварку или кастрюлю спо… |

|Напиток |Кофе на молоке |Сварить черный кофе, как … |

|Горячее |Шашлык |??? |

где первые 33 строки соответствуют первому INSERT и представляют собой

проекцию естественного соединения таблиц Блюда и Рецепты по кодам блюд

(БЛ), включающую три столбца. Последняя строка результата соответствует

второму INSERT и сохраняет информацию о блюде Шашлык, рецепт котого пока не

введен в таблицу Рецепты.

Заметим, что для внешнего соединения нужны два отдельных

INSERT…SELECT. Однако тот же результат можно получить и одним

INSERT…SELECT, используя фразу UNION, объединяющую предложения SELECT из

двух INSERT:

INSERT

INTO Временная

SELECT Вид, Блюдо, Рецепт

FROM Блюда, Рецепты, Вид_блюд

WHERE Блюда.БЛ = Рецепты.БЛ

AND Блюда.В = Вид_блюд.В

UNION

SELECT Вид, Блюдо, «???»

FROM Блюда, Вид_блюд

WHERE Блюда.В = Вид_блюд.В

AND БЛ NOT IN

( SELECT БЛ

FROM Рецепты);

Предложение UPDATE

Обновление единственной записи

Изменить название блюда с кодом БЛ=5 на Форшмак, увеличить его выход

на 30 г и установить NULL-значение в столбец Труд.

UPDATE Блюда

SET Блюдо = 'Форшмак', Выход = (Выход+30), Труд = NULL

WHERE БЛ = 5;

Обновление множества записей

Утроить цену всех продуктов таблицы поставки (кроме цены кофе – ПР =

17).

UPDATE Поставки

SET Цена = Цена * 3

WHERE ПР <> 17;

Обновление с подзапросом

Установить равной нулю цену и К_во продуктов для поставщиков из

Паневежиса и Резекне.

UPDATE Поставки

SET Цена = 0, К_во = 0

WHERE ПС IN

(SELECT ПС

FROM Поставщики

WHERE Город IN ('Паневежис', 'Резекне'));

Обновление нескольких таблиц

Изменить номер продукта ПР = 13 на ПР = 20.

UPDATE Продукты UPDATE Состав

SET ПР = 20 SET ПР = 20

WHERE ПР = 13; WHERE ПР = 13;

UPDATE Поставки UPDATE Наличие

SET ПР = 20 SET ПР = 20

WHERE ПР = 13; WHERE ПР = 13;

К сожалению в единственным запросе невозможно обновить более одной

таблицы, а так как код продукта входит в четыре таблицы, то пришлось выдать

четыре сходных запроса. Это может привести к противоречию базы данных

(нарушению целостности по ссылкам), поскольку после выполнения первого

предложения таблицы Состав, Поставки и Наличие ссылаются на уже

несуществующий продукт. База становится непротиворечивой только после

выполнения четвертого запроса.

О конструировании предложений модификации

Для тех, кто достаточно хорошо понял предложение SELECT, несложно

овладеть конструированием предложений DELETE, INSERT и UPDATE. Но в

процессе такого конструирования следует учитывать, что:

1. Если в WHERE фразе предложений DELETE и UPDATE используется вложенный

подзапрос, то во фразе FROM этого подзапроса не должна упоминаться

таблица, из которой удаляются (в которой обновляются) строки.

Аналогично, в подзапросе предложения INSERT не должна упоминаться

таблица, в которую загружаются данные.

Так, SQL отвергнет предложение

INSERT

INTO Выбрано

SELECT (33), Т, БЛ

FROM Выбрано

WHERE СМ = 17;

позволяющее ввести информацию о том, что отдыхающий, сидящий на 33-м

месте, выбирает тот же набор блюд, что и отдыхающий, сидящий на 17-м

месте. Ввод придется осуществить через какую-либо промежуточную

таблицу, например, таблицу Выбор:

DELETE

FROM Выбор;

INSERT

INTO Выбор (СМ, Т, БЛ)

SELECT (33), Т, БЛ

FROM Выбрано

WHERE СМ = 17;

INSERT

INTO Выбрано

SELECT СМ, Т, БЛ

FROM Выбор;

2. Составляя предложения модификации данных, необходимо все время помнить

о сохранении непротиворечивости базы данных. Об этом упоминалось ранее

и подробно говорилось в литературе.

Предложение DELETE

Удаление единственной записи

Удалить поставщика с ПС = 7.

DELETE

FROM Поставщики

WHERE ПС = 7;

Если таблица Поставки содержит в момент выполнения этого предложения

какие-либо поставки для поставщика с ПС = 7, то такое удаление нарушит

непротиворечивость базы данных. К сожалению нет операции удаления,

одновременно воздействующей на несколько таблиц. Однако в некоторых СУБД

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

некорректное удаление или каскадировать удаление на несколько таблиц.

Удаление множества записей

Удалить все поставки.

DELETE

FROM Поставки;

Поставки – все еще известная таблица, но в ней теперь нет строк. Для

уничтожения таблицы надо выполнить операцию DROP TABLE Поставки.

Удалить все мясные блюда.

DELETE FROM Блюда

WHERE Основа = 'Мясо';

Удаление с вложенным подзапросом

Удалить все поставки для поставщика из Паневежиса.

DELETE

FROM Поставки

WHERE ПС IN

(SELECT ПС

FROM Поставщики

WHERE Город = 'Паневежис');