Главная | Обратная связь | Поможем написать вашу работу!
МегаЛекции

Внешние объединения.




 

Стандарт SQL2 расширил понятие условного объединения. В стандарте SQL1, при объединении отношений использовались только условия, задаваемые в части WHERE оператора SELECT, и в этом случае в результирующее отношение попадпли только сцепленные по заданным условиям кортежи исходных отношений, для которых эти условия были определены и истинны. Однако в действительности часто необходимо объединять таблицы таким образом, что бы в результат попали все строки из первой таблицы, а вместо тех строк второй таблицы, для которых не выполнено условие соединения в результат попадали бы неопределенные значения. Или наоборот, включаются все стороки из правой (второй) таблицы, а из неудовлетворяющие части строк из первой таблицы дополняются неопределенными значениями. Такие объединения были названы внешними в противоположность объединениям, определенным стандартом SQL1, которые стали называться внутренними.

 


В общем случае синтаксическая диаграмма части FROM в стандарте SQL2 выглядит следующим образом:

Выражение естественного объединения:

 

|¾ Таблица 1 NATURAL ¾¾¾¾¾¾¾¾¾¾¾¾ JOIN Таблица 2 ¾¾®·

INNER¾¾¾¾¾¾¾

FULL ¾¾¾¾¾¾¾

LEFT ¾ OUTER

RIGHT¾

 

 
 

Выражение перекрестного объединения

Фактически это полное декартово произведение таблицы1 и таблицы 2.

 

 

Выражение запроса на объединение

 

 

|¾ Табл. 1 ¾¾¾¾¾¾¾¾¾¾¾¾ JOIN Табл. 2 ¾ ON условие ¾¾¾®·

¾INNER¾¾¾¾¾¾¾ USING (список столбцов)

¾ FULL ¾¾¾¾¾¾¾

¾ LEFT ¾OUTER

RIGHT

 


Выражение объединения

Эта операция соответствует операции теоретико множественного объединения в алгебре, поэтому для нее естественым ограничением является наличие эквивалентных схем.

 

В этих диаграммах INNER – означает внутреннее объединение, LEFT – левое объединение, т.е. в результат входят все строки Табл. 1, а неопределенные значения для табл.2 дополняются значениями NULL, RIGHT – наоборот, а FULL – полное внешние объединение и левое и правое. Ключевое слово OUTER означает внешнее, но если заданы ключевые слова FULL, LEFT, RIGHT, то объединение всегда считается внешним.

Рассмотрим примеры выполнения внешних объединений на примере БД «Библиотека»

BOOKS(ISBN,TITL,AUTOR,COAUTOR,YEARIZD,PAGES)

READER(NUM_READER, NAME_READER, ADRES, HOOM_PHONE, WORK_PHONE, BIRTH_DAY)

EXEMPLARE(INV,ISBN,YES_NO,NUM_READER,DATE_IN,DATE_OUT)

 

Определим перечень книг у каждого читателя, если у читателя нет книг, то номер экзекмпляра книги равен NULL. Для выполнения этого поиска нам надо выполнить левое внешнее объединение, т.е. мы берем все строки из таблицы READER и соединяем со строками из таблицы EXEMPLARE, если во второй таблице нет строки с соотвествующим номером читательского билета, то в строке результирующего отношения атрибут EXEMPLARE.INV будет иметь неопределенное значение NULL:

 

SELECT READER.NAME_READER, EXEMPLARE.INV

FROM READER RIGHT JOIN EXEMPLARE ON READER.NUM_READER=EXEMPLARE.NUM_READER

Операция внешнего объединения может использоваться для формирования источников в предложении FROM, поэтому допустимым будет например следующий текст запроса:

 

 

SELECT *

FROM (BOOKS LEFT JOIN EXEMPLARE)

LEFT JOIN

(READER NATURAL JOIN EXEMPLARE)

USING (ISBN)

При этом для книг, ни один экземпляр которых не находится на руках у читателей значения номера читательского билета и дат взятия и возврата книги будут неопределенными.

Перекрестное объединение соответствует операции расширенного декартова произведения.

 

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

 

В алгебре рассматривалась реляционная операция "Объединение", позволяющая получить отношение, состоящее из всех строк, входящих в одно или оба объединяемых отношения. Но при этом исходные отношения или их объединяемые проекции должны быть совместимыми по объединению. Для SQL это означает, что две таблицы можно объединять тогда и только тогда, когда:

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

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

Например, выдать названия продуктов, в которых нет жиров, либо входящих в состав блюда с кодом БЛ = 1:

 

Результат:

Продукт

 

SELECT Продукт

FROM Продукты

WHERE Жиры = 0

UNION

SELECT Продукт

FROM Соста

WHERE БЛ = 1

 

Майонез
Лук
Помидоры
Зелень
Яблоки
Сахар

 

Из этого простого примера видно, что избыточные дубликаты всегда исключаются из результата UNION. Поэтому, хотя в рассматриваемом примере Помидоры, Зелень и Яблоки выбираются обеими из двух составляющих предложения SELECT, в окончательном результате они появляются только один раз.

Предложением с UNION можно объединить любое число таблиц (проекций таблиц). Так, к предыдущему запросу можно добавить (перед точкой с запятой) конструкцию

 

 

UNION

SELECT Продукт

FROM Продукты

WHERE Ca < 250

 

позволяющую добавить к списку продуктов Масло, Рис, Мука и Кофе. Однако тот же результат можно получить простым изменением фразы WHERE первой части исходного запроса

 

WHERE Жиры = 0 OR Ca < 250

 

 

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

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

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

 

DELETE

FROM Поставщики

WHERE ПС = 7;

 

Если таблица Поставки содержит в момент выполнения этого предложения какие-либо поставки для поставщика с ПС = 7, то такое удаление нарушит непротиворечивость базы данных. К сожалению нет операции удаления, одновременно воздействующей на несколько таблиц. Однако в некоторых СУБД реализованы механизмы поддержания целостности (см.п.2.5 в литературе [2]), позволяющие отменить некорректное удаление или каскадировать удаление на несколько таблиц.

 

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

 

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

 
 

DELETE

FROM Поставки;

 

Поставки - все еще известная таблица, но в ней теперь нет строк. Для уничтожения таблицы надо выполнить операцию DROP TABLE Поставки (см.п.5.2).

 

 

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

 

DELETE FROM Блюда

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

 

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

 

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

 

DELETE

FROM Поставки

WHERE ПС IN

(SELECT ПС

FROM Поставщики

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

 

 

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

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

 

Синтаксическая диаграмма операции INSERT представлена на рис.

 
 

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

 

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

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

 

INSERT

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

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

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

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

 

INSERT

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

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

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

 

INSERT

INTO Блюда

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

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

В предыдущих примерах проводилась модификация стержневой сущности, т.е. таблицы с первичным ключом БЛ (см.п.2.4 в лите-ратуре [2]). Почти все СУБД имеют механизмы для предотвращения ввода не уникального первичного ключа, например, ввода "Шашлыка" под номером, меньшим 34. А как быть с ассоциациями или другими таблицами, содержащими внешние ключи?

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

 

INSERT

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

VALUES (1, 10, 15);

 

Подобно операции DELETE операция INSERT может нарушить непротиворечивость базы данных. Если не принять специальных мер, то СУБД не проверяет, имеется ли в таблице Блюда блюдо с первичным ключом БЛ = 1 и в таблице Продукты - продукт с первичным ключом ПР = 10. Отсутствие любого из этих значений породит противоречие: в базе появится ссылка на несуществующую запись. Проблемы, возникающие при использовании внешних ключей, подробно рассмотрены в литературе [2], а здесь отме-тим, что все "приличные" СУБД имеют механизмы для предотв-ращения ввода записей со значениями внешних ключей, отсутст-вующих среди значений соответствующих первичных ключей.

 
 

 

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

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

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

 

UPDATE Блюда

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

 
 

WHERE БЛ = 5;

 

Поделиться:





Читайте также:





Воспользуйтесь поиском по сайту:



©2015 - 2024 megalektsii.ru Все авторские права принадлежат авторам лекционных материалов. Обратная связь с нами...