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

·        ALL - включает все строки, соответствующие указанным далее условиям отбора;

·        DISTINCT (ключевое слово из ANSI SQL-92) - исключает строки с повторяющимися данными на основе только данных результирующего набора записей;

Необязательный параметр TOP n [PERCENT] ограничивает количество записей в результирующей таблице первыми n или n% набора.

Оператор FROM определяет имена таблиц, из которых должны выбираться данные.

WHERE определяет условие для отбора записей и реализует реляционную операцию выборки. Условие задается текстовым оператором типа LIKE для текстовых полей или числовыми операторами типа >, <, =, < >, >=, BETWEEN для числовых полей. Если WHERE не использован, то запрос возвратит все записи, удовлетворяющие критерию SELECT.

Модификатор ORDER BY определяет порядок сортировки записей в созданной таблице. Ключевыми словами ASC и DESC можно определить сортировку по возрастанию или убыванию соответственно.

Пример инструкции запроса на выборку:

Результатом выполнения запроса будет новая таблица, содержащая два столбца Имя_Д и Вес и множество строк, удовлетворяющих условию Вес>500, отсортированных по убыванию веса.

2.4 Статистические функции

Статистические или агрегатные функции (итоговые в реляционной алгебре) используются тогда, когда необходимо определить статистические данные (сумму, среднее, минимальное, максимальное и т.п.) группы записей с общим значением атрибута. Для этого используется инструкция GROUP BY:

SELECT статистическая функция (имя поля) AS заголовок поля [, список полей]

FROM имена таблиц

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

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

[HAVING условие для результата]

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

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

Ключевое слово AS определяет заголовок столбца результирующего набора записей. GROUP BY определяет столбец, по значениям которого записи объединяются в группы, к которым применяется статистическая функция и возвращает одно значение. HAVING позволяет ввести одно или несколько условий, налагаемых на значение результирующего столбца, полученного в результате группировки и применения статистической функции.

Примеры команд SQL, применяющих статистические функции:

Общее количество деталей можно получить следующим образом:

SUM(Поставки.Кол) FROM Поставки;

В предложении SELECT можно указывать несколько скалярных выражений:

SELECT MIN(Кол), MAX(Кол), SUM(Кол), AVG(Кол) FROM Поставки;

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

Количество кортежей в отношении можно посчитать следующим образом:

SELECT COUNT (*) AS Кол_кортежей FROM Поставки;

В результате получим таблицу из одной строки с заголовком “Кол_кортежей”.

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

SELECT Пк.ПN, SUM(Пк.Кол)Поставки AS ПкBY Пк.ПN;

(Здесь показан пример использования псевдонимов, которые вводятся в предложении FROM при помощи оператора AS (его можно и пропустить). Использование псевдонимов упрощает запись команд. Они также используются и для организации некоторых запросов.)

В предложении SELECT необходимо указывать атрибут, по которому производится группировка и нельзя указывать имена атрибутов, не входящих в предложение GROUP BY (но можно указывать несколько статистических функций).

На создаваемые оператором GROUP BY результаты можно накладывать ограничения оператором HAVING, например:

SELECT Пк.ПN, SUM (Пк.Кол)Поставки AS ПкBY Пк.ПNCOUNT(*)>2;

Предложение HAVING COUNT(*)>2 выделяет только те группы, в которых количество кортежей больше 2 (поставщики выполнили более 2 поставок).

2.5 Создание соединений

Реляционная операция произведения двух отношений TIMES реализуется в SQL, если указать имена этих отношений в предложении FROM:

* FROM Проекты, Поставки;

Если дополнить эту команду предложением WHERE, сравнивающим значения атрибутов этих отношений, то будет реализована реляционная операция соединения JOIN: Например, соединение отношений Проекты и Поставки:

* Проекты, ПоставкиПроекты.ПрN=Поставки.ПрN;

Подобным образом можно соединить произвольное число отношений:

SELECT DISTINCT П.Имя_П, Д.Имя_Д, Пр.Имя_Пр, Пк.КолПоставщики П, Детали Д, Проекты Пр, Поставки Пк

WHERE Д.ДN=Пк.ДN

AND П.ПN=Пк.ПN

AND Пр.ПрN=Пк.ПрN

AND Пк.Кол>500;

Такое соединение выполняется с конца. Сначала из таблицы Поставки удаляются строки со значениями поля Кол менее или равным 500. Затем соединяются кортежи отношения Поставки с теми кортежами отношения Проекты, у которых совпадают значения атрибута ПрN. После этого кортежи созданного представления соединяются с кортежами отношения Поставщик, у которых совпадают значения атрибута ПN. Далее, полученные кортежи соединяются с кортежами отношения Детали, у которых совпадают значения атрибута ДN.

Для организации соединений между таблицами и их объединения может быть использован и оператор JOIN…ON, который указывает на подключаемую таблицу и связь между полями:

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

FROM имя таблицы {INNER/LEFT/RIGHT} JOIN связанная таблица

ON условие связи

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

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

В приведенной инструкции показано, как оператор JOIN окружен именами двух связываемых таблиц, причем вместо правого имени может использоваться повторно конструкция JOIN … ON, называемая вложенной: [имя таблицы {INNER/LEFT/RIGHT} JOIN связанная таблица ON условие связи]. В этом случае первая таблица соединяется с соединением второй и третьей таблиц. Инструкция SQL может содержать набор нескольких вложенных конструкций JOIN … ON. Их число обычно равно общему количеству таблиц, включенных в запрос, минус один. Перед оператором JOIN должен быть указан тип соединения:

·        INNER - соединяет записи из двух таблиц, если связующие поля этих таблиц содержат одинаковые значения;

·        LEFT (RIGHT) - соединяет записи исходных таблиц, причем левое внешнее соединение включает все записи из первой (левой) таблицы и присоединяет к ним записи из второй таблицы, если связующие поля содержат одинаковые значения. Правое внешнее соединение включает все записи из второй (правой) таблицы и присоединяет к ним записи из первой таблицы, если связующие поля содержат одинаковые значения.

Конструкция ON условие связи позволяет описать два поля и связь между ними (одно поле в таблице связанная таблица, второе - в таблице имя таблицы). В выражении условие связи присутствует оператор сравнения значений полей, который возвращает значения True или False. Если значение выражения True, то объединенная запись включается в результирующий набор.

Пример инструкции на соединение отношений Проекты и Поставки базы данных Проекты-Поставщики-Детали по значениям полей ПрN, присутствующим в обоих отношениях:

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 П