Материал: Реляционная алгебра. Основы SQL

SELECT DISTINCT Пр.Имя_Пр, Пр.Гор, Пк.ДN, Пк.Кол(Проекты Пр INNER JOIN Поставки Пк)

ON Пр.ПрN=Пк.ПрN;

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

SELECT F.ПN, S.ПN, F.ДNПоставки AS F, Поставки AS SF.ДN=S.ДN;

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

Добавив условие выборки

AND F.ПN <> S.ПN

убираем строки, содержащие одинаковые номера поставщиков. Модификатор DISTINCT удалит повторяющиеся кортежи. Если бы в полях F.ПN и S.ПN были данные числового типа, то условие выборки AND F.ПN < S.ПN позволило бы получить ответ без последующих операций.

Выбрав проекцию F.ДN, получим список деталей, поставляемых несколькими поставщиками.

2.6 Вложенные запросы

Вложенные запросы - это применение одного запроса к результирующему набору записей другого. Для этой цели создается запрос SELECT, в котором для формирования условия предложения WHERE используется еще один запрос SELECT. Такие конструкции могут существенно повысить производительность работы базы данных.

Синтаксис записи вложенных запросов:

SELECT список полей

FROM список таблиц

WHERE [имя таблицы.] имя поля

                   IN (SELECT оператор выборки

                            [GROUP BY условие группировки]

                            [HAVING условие отбора]

[ORDER BY столбцы сортировки]);

Пример: Найти номера поставщиков, поставляющих хотя бы одну черную деталь.

SELECT Пк.ПN FROM Поставки Пк

WHERE Пк.ДN IN

                   (SELECT Д.ДN FROM Детали Д

                            WHERE Д.Цвет='Черный');

Механизм действия вложенного запроса такой. Внутренний запрос вложен во внешний запрос, который последовательно сравнивает значения своего атрибута (Пк.ДN) с возвращенным внутренним запросом множеством этих же атрибутов (Д.ДN). Если рассматриваемый атрибут внешнего запроса есть в указанном множестве, то будет возвращен выбранный в предложении SELECT атрибут (Пк.ПN).

Чтобы в нашем примере найти имена поставщиков, надо добавить еще один вложенный запрос:

SELECT П.Имя_П FROM Поставщики П

WHERE П.ПN IN

                   (SELECT Пк.ПN FROM Поставки Пк

                   WHERE Пк.ДN IN

                            (SELECT Д.ДN FROM Детали Д

                            WHERE Д.Цв='Черный'));

Такой же результат можно получить при помощи операции соединения:

SELECT DISTINCT П.Имя_П

FROM Поставщики П, Поставки Пк, Детали Д

WHERE П.ПN=Пк.ПN

                   AND Пк.ДN=Д.ДN

                   AND Д.Цв='Черный';

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

SELECT * FROM Поставщики П

WHERE П.ПN NOT IN

                   (SELECT Пк.ПN FROM Поставки Пк);

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

2.7 Запрос на объединение

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

SELECT оператор выборки

UNION

SELECT оператор выборки

                   [GROUP BY условие группировки]

                   [HAVING итоговое условие]

[UNION

SELECT оператор выборки

                   [GROUP BY условие группировки]

                   [HAVING итоговое условие]]

[UNION

…]

[ORDER BY столбцы сортировки];

При использовании операторов UNION необходимо задавать одинаковый набор имен полей в списке отбираемых полей, причем, их последовательность должна быть одинакова в каждом предложении UNION SELECT. Модификатор ORDER BY может быть использован только один раз в инструкции за последним оператором UNION SELECT. При необходимости в каждый оператор SELECT и UNION SELECT можно вложить операторы GROUP BY и HAVING. Оператор UNION удаляет из результирующей таблицы все строки-дубликаты. Чтобы это не происходило, используют оператор UNION ALL.

Синтаксис оператора UNION позволяет собирать в одном поле объединенной таблицы значения из различных доменов:

SELECT Имя_П AS Наименование

         FROM Поставщики

         WHERE Гор=’Минск’

UNION SELECT Имя_Пр AS Наименование

         FROM Проекты

         WHERE Гор=’Минск’;

Результатом запроса будет таблица из одного столбца, названного «Наименование», содержащая поставщиков, находящихся в Минске, и проектов, выполняемых в Минске.

2.8 Запросы, выполняющие реляционные операции вычитания, пересечения и деления

В некоторых модификациях языка SQL, например, используемой в СУБД Oracle, реляционная операция пересечения отношений выполняется оператором INTERSECT, который возвращает только те строки, которые присутствуют в обеих таблицах. Вычитание отношений реализуется оператором EXCEPT, который возвращает строки первой таблицы за исключением тех, которые присутствуют и во второй таблице. Как и в случае оператора UNION, обрабатываемые таблицы должны иметь одинаковый набор и последовательность имен полей. В СУБД Access эти операции могут быть реализованы при помощи оператора EXISTS.

Оператор EXISTS в предложении WHERE выполняет проверку на существование данных, которые удовлетворяют критериям соответствующего вложенного запроса, и возвращает булево значение «истина» или «ложь».

Пример. Найти имена поставщиков, которые поставляют деталь Д1:

SELECT DISTINCT П.Имя_П FROM Поставщики AS ПEXISTS

(SELECT * FROM Поставки AS Пк

                   WHERE Пк.ПN=П.ПN

                            AND Пк.ДN='Д1');

Для каждого кортежа отношения Поставщики, которое обрабатывается во внешнем запросе, для вложенного запроса будет возвращено значение “истина”, если существует хотя бы один кортеж в отношении Поставки с тем же значением П№, который рассматривает внешний запрос. Чтобы это определить, выполняется соединение по Пк.П№=П.П№. Во вложенном запросе используется предложение SELECT *, но можно указать имя любого атрибута, т.к. этот вложенный запрос возвращает не данные, а значение истинности.

Чтобы получить имена поставщиков, которые не поставляют деталь Д1, можно использовать отрицание предложения EXISTS:

SELECT DISTINCT П.Имя_П FROM Поставщики AS ПNOT EXISTS

(SELECT * FROM Поставки AS ПК

                   WHERE ПК.ПN=П.ПN

                            AND ПК.ДN='Д1');

Пример решения задачи двумя способами - с оператором EXISTS и без него: Найти номера деталей, поставляемых поставщиком из города, название которого начинается на букву М.

I. SELECT DISTINCT Пк.ДN FROM Поставки AS Пк WHERE Пк.ПN IN (SELECT П.ПN FROM Поставщики AS П WHERE П.Гор LIKE 'М*');. SELECT DISTINCT Пк.Д№ FROM Поставки Пк WHERE EXISTS (SELECT * FROM Поставщики П WHERE П.П№ = Пк.П№ AND Гор LIKE ′М*′);

Если надо указать имя детали, т.е. получить сведения из 3-й таблицы, то команда SQL выглядит так:

SELECT DISTINCT Д.Имя_Д FROM Детали AS ДEXISTS

(SELECT DISTINCT Пк.ДN FROM Поставки AS Пк

                   WHERE EXISTS

                   (SELECT * FROM Поставщики AS П

                            WHERE П.ПN=Пк.ПN

                                      AND Гор LIKE 'М*')

                                      AND Д.ДN=П.ДN);

Покажем, как при помощи оператора EXISTS можно реализовать операции пересечения, разности и деления.

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

SELECT DISTINCT Д.Гор FROM Детали AS ДEXISTS

(SELECT * FROM Поставщики П

                   WHERE Д.Гор=П.Гор);

Разностью между этими таблицами по полю Гор является множество городов, в которых есть детали, но нет поставщиков:

SELECT DISTINCT Д.Гор FROM Детали ДNOT EXISTS

(SELECT * FROM Поставщики П

                   WHERE Д.Гор=П.Гор);

Операция пересечения может быть реализована и без оператора EXISTS:

SELECT DISTINCT Д.Гор FROM Детали Д(SELECT COUNT (*) FROM Поставщики П

                   WHERE Д.Гор = П.Гор) >0;

(если количество найденных строк больше 0, значит они есть и оператор EXISTS принял бы значение “истина”).

При таком запросе производительность ниже, чем при использовании EXISTS, т.к. EXISTS возвращает результат, как только ему встретится хотя бы одна строка, удовлетворяющая условию, а COUNT (*) подсчитывает строки, просматривая таблицу до конца.

Пример реализации реляционной операции деления DIVIDED BY: Получить номера поставщиков, поставляющих все детали

SELECT DISTINCT Пк.ПNПоставки AS ПкNOT EXISTS

(SELECT Д.ДN FROM Детали AS Д

                   WHERE NOT EXISTS

                   (SELECT Пк1.ДN FROM Поставки AS Пк1

                            WHERE Пк1.ПN=Пк.ПN

                                      AND Пк1.ДN=Д.ДN));

Внешний запрос исследует каждый кортеж отношения Поставки и возвращает из него ПN, если вложенный запрос возвращает значение «Ложь». Вложенный запрос создает множество всех возможных значений ДN из отношения Детали. Из этого множества удаляются те значения ДN, которые состоят в паре с ПN в кортеже с тем же значением ПN, что и кортеж, рассматриваемый во внешнем запросе. Если рассматриваемый ПN комбинируется со всеми возможными значениями ДN, то результатом будет пустое множество и вложенный запрос возвратит значение «Ложь», т.е., не существует значений ДN, которые не состоят в паре с этим конкретным ПN.

Чтобы получить имена поставщиков, надо вложить этот запрос в другой:

SELECT П.Имя_ППоставщики AS ПП.ПN IN

(SELECT DISTINCT Пк.ПNПоставки AS ПкNOT EXISTS

(SELECT Д.ДN FROM Детали AS Д

                   WHERE NOT EXISTS

                   (SELECT Пк1.ДN FROM Поставки AS Пк1

                            WHERE Пк1.ПN=Пк.ПN

                                      AND Пк1.ДN=Д.ДN)));

2.9 Запросы на изменение

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

Синтаксис запроса на добавление записей:

INSERT INTO таблица-получатель

SELECT список полей

FROM таблица-источник;

Если в инструкции на добавление отсутствует предложение WHERE, то в таблицу-получатель будут добавлены все записи таблицы-источника.

Синтаксис запроса на удаление записей:

DELETE FROM имя таблицы [WHERE условие удаления];

В этой инструкции предложение WHERE также не обязательное. При его отсутствии из указанной таблицы будут удалены все записи.

Синтаксис запроса на создание таблицы:

SELECT список полей

INTO новая таблица

FROM исходная таблица

[WHERE условие выбора];

Полную копию исходной таблицы можно создать, указав вместо списка полей символ * и не используя предложение WHERE.

Запрос на обновление используется для присвоения новых значений отдельным столбцам при помощи операторов UPDATE и SET:

 имя таблицы

SET имя_поля_1=значение [,имя_поля_2=значение[,…]]

[WHERE условие обновления];

2.10 Перекрестные запросы

Перекрестные запросы позволяют создавать различные итоговые запросы, использующие статистические функции SQL. Когда данные группируются с помощью перекрестного запроса, можно выбирать значения из заданных полей или выражений как заголовки столбцов. Это позволяет просматривать данные в более компактной форме, чем при работе с запросом на выборку. <JavaScript:hhobj_3.Click()> Для организации перекрестных запросов используются операторы Jet-SQL TRANSFORM и PIVOT: