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

Рефераты. Организация Web-доступа к базам данных с использованием SQL-запросов p> Даже при отсутствии средств одновременного доступа ко многим таблицам нежелателен проект, в котором информация о многих типах сущностей перемешана в одной таблице. SQL же обладает великолепным механизмом для одновременной или последовательной обработки данных из нескольких взаимосвязанных таблиц. В нем реализованы возможности «соединять» или
«объединять» несколько таблиц и так называемые «вложенные подзапросы».
Например, чтобы получить перечень поставщиков продуктов, необходимых для приготовления Сырников, возможен запрос

SELECT Продукт, Цена, Название, Статус
FROM Продукты, Состав, Блюда, Поставки, Поставщики
WHERE Продукты.ПР = Состав.ПР
AND Состав.БЛ = Блюда.БЛ
AND Поставки.ПР = Состав.ПР
AND Поставки.ПС = Поставщики.ПС
AND Блюдо = 'Сырники'
AND Цена IS NOT NULL;

Он получен следующим образом: СУБД последовательно формирует строки декартова произведения таблиц, перечисленных во фразе FROM, проверяет, удовлетворяют ли данные сформированной строки условиям фразы WHERE, и если удовлетворяют, то включает в ответ на запрос те ее поля, которые перечислены во фразе SELECT.

Следует подчеркнуть, что в SELECT и WHERE (во избежание двусмысленности) ссылки на все (*) или отдельные столбцы могут (а иногда и должны) уточняться именем соответствующей таблицы, например, Поставки.ПС,
Поставщики.ПС, Меню.*, Состав.БЛ, Блюда.* и т.п.

Очевидно, что с помощью соединения несложно сформировать запрос на обработку данных из нескольких таблиц. Кроме того, в такой запрос можно включить любые части предложения SELECT, рассмотренные в главе 2 (выражения с использованием функций, группирование с отбором указанных групп и упорядочением полученного результата). Следовательно, соединения позволяют обрабатывать множество взаимосвязанных таблиц как единую таблицу, в которой перемешана информация о многих типах сущностей. Поэтому начинающий проектант базы данных может спокойно создавать маленькие нормализованные таблицы, так как он всегда может получить из них любую «большую» таблицу.

Кроме механизма соединений в SQL есть механизм вложенных подзапросов, позволяющий объединить несколько простых запросов в едином предложении
SELECT. Иными словами, вложенный подзапрос – это уже знакомый нам подзапрос
(с небольшими огра-ничениями), который вложен в WHERE фразу другого вложенного подзапроса или WHERE фразу основного запроса.

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

FROM Поставки X

Здесь с помощью подзапроса, размещенного в трех последних строках запроса, описывается процесс определения минимальной цены каждого продукта для Сырников и поиск поставщика, предлагающего этот продукт за такую цену.

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

Декартово произведение таблиц

Так как декартово произведение 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 Вид Трапеза; позволяющий выбрать из полученного декартова произведения таблиц Вид_блюд и
Трапезы лишь те строки, в которых значение трапезы «меньше» (по алфавиту) значения вида блюда:
|В |Вид |Т |Трапеза |
|З |Закуска |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 или одного из условий сравнения ( = | < | < |

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