Файл: Базы данных - уч. пособие.pdf

106

Глава 5. Языки управления и манипулирования данными

Перекрестные запросы
В Jet SQL существует такой вид запросов, как перекрестный. В таком запро-

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

Для создания перекрестного запроса необходимо использовать следующую ин-

струкцию:

TRANSFORM статистическая_функция
инструкция_SELECT
PIVOT поле [IN (значение_1[, значение_2[,. . .]])],

где статистическая_функция — статистическая функция SQL, обрабатывающая ука-
занные данные; инструкция_SELECT — запрос на выборку; поле — поле или выра-
жение, которое содержит заголовки столбцов для результирующего набора; зна-
чение_1, значение_2 — фиксированные значения, используемые при создании заго-
ловков столбцов.

. . . . . . . . . . . . . . . . . . . . . .

Пример 5.11

. . . . . . . . . . . . . . . . . . . . .

Составим SQL-запрос, реализующий описанный выше пример.
TRANSFORM Avg(Успеваемость.Оценка)
SELECT Дисциплина.Наименование_дисциплины as [Предмет]
FROM Студент INNER JOIN
(Дисциплина INNER JOIN Успеваемость ON Дисциплина.Код_дисциплины =

Успеваемость.Код_дисциплины) ON Студент.Код_студента = Успеваемость.Код_
студента

GROUP BY Дисциплина.Наименование_дисциплины, Успеваемость.Код_дис-

циплины

PIVOT Студент.Номер_группы;

В результате выполнения такого перекрестного SQL-запроса формируется на-

бор данных, представленный на рисунке 5.14.

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

Таким образом, когда данные сгруппированы с помощью перекрестного запро-

са, можно выбирать значения из заданных столбцов или выражений и определять
как заголовки столбцов. Это позволяет просматривать данные в более компактной
форме, чем при работе с обычным запросом на выборку. Отметим, что перекрест-
ные запросы удобно использовать для формирования статистических отчетов.

5.1 Язык SQL

107

Рис. 5.14 – Результат выполнения перекрестного запроса

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

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

1) сравнение [ANY | ALL | SOME] (инструкция SQL);

2) выражение [NOT] IN (инструкция SQL);

3)

[NOT] EXISTS (инструкция SQL),

где сравнение — выражение и оператор сравнения, который сравнивает выраже-
ние с результатами подчиненного запроса; выражение — выражение, для которого
проводится поиск в результирующем наборе записей подчиненного запроса; ин-
струкция SQL — инструкция SELECT, заключенная в круглые скобки.

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

инструкции SELECT или в предложениях WHERE и HAVING. Инструкция SELECT
используется в подчиненном запросе для задания набора конкретных значений,
вычисляемых в выражениях предложений WHERE или HAVING.

Предикаты ANY или SOME, являющиеся синонимами, используются для от-

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

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

которые удовлетворяют сравнению со всеми записями, отобранными в подчинен-
ном запросе. Если в предыдущем примере предикат ANY заменить предикатом
ALL, результат запроса будет включать только тех студентов, у которых средний
балл больше 4. Это условие является значительно более жестким.

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

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

Предикат NOT IN используется для отбора в главном запросе только тех за-

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

Предикат EXISTS (с необязательным зарезервированным словом NOT) исполь-

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

108

Глава 5. Языки управления и манипулирования данными

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

на таблицы, перечисленные в предложении FROM, расположенном вне подчинен-
ного запроса.

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

ции UPDATE — увеличим сумму задолженности за обучение не для всех студентов,
а только для студента группы 423-1 Иванова Ивана Петровича.

. . . . . . . . . . . . . . . . . . . . . .

Пример 5.12

. . . . . . . . . . . . . . . . . . . . .

UPDATE Задолженность_за_обучение
SET Сумма_задолженности = Сумма_задолженности * 1.1
WHERE Код_студента =
(Select Код_студента FROM Студент WHERE ФИО_Студента = 'Иванов Иван

Петрович'AND Номер_группы = '423-1');

На рисунке 5.15 представлена таблица «Задолженность_ за_обучение» до и по-

сле выполнения запроса. (Значение поля «Код_студента» для Иванова Ивана Пет-
ровича равно 1.)

Рис. 5.15 – Результат выполнения запроса на обновление с подчиненным запросом

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

Некоторые подчиненные запросы можно использовать в перекрестных запро-

сах как предикаты (в предложении WHERE). Подчиненные запросы, используемые
для вывода результатов (в списке SELECT), нельзя использовать в перекрестных
запросах.

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

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

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

5.1 Язык SQL

109

Синтаксис команды:

[TABLE] запрос_1 UNION [ALL] [TABLE] запрос_2 [UNION[ALL] [TABLE]

запрос_n [. . .]],

где запрос_1–n — инструкция SELECT или имя сохраненной таблицы, перед кото-
рым стоит зарезервированное слово TABLE.

В одной операции UNION можно объединить в любом наборе результаты

нескольких запросов, таблиц и инструкций SELECT.

. . . . . . . . . . . . . . . . . . . . . .

Пример 5.13

. . . . . . . . . . . . . . . . . . . . .

Для выполнения следующего запроса создадим в БД и заполним таблицу «Аби-

туриент» (рис. 5.16).

Рис. 5.16 – Таблица «Абитуриент»

Здесь объединяются существующая таблица «Студент» и запрос на выборку

всех записей из таблицы «Абитуриент».

TABLE Студент
UNION ALL SELECT *
FROM Абитуриент;

Результат выполнения запроса представлен на рисунке 5.17.

Рис. 5.17 – Результат выполнения запроса на объединение

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

По умолчанию повторяющиеся записи не возвращаются при использовании

операции UNION, однако в нее можно добавить предикат ALL, чтобы гаранти-

110

Глава 5. Языки управления и манипулирования данными

ровать возврат всех записей. Кроме того, такие запросы выполняются несколько
быстрее.

Таблица и все запросы, включенные в операцию UNION, должны отбирать

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

В каждом аргументе «Запрос» допускается использование предложения GROUP

BY или HAVING для группировки возвращаемых данных. В конец последнего ар-
гумента «Запрос» можно включить предложение ORDER BY, чтобы отсортировать
возвращенные данные.

Удаление записей

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

Инструкция DELETE создает запрос на удаление записей из од-
ной или нескольких таблиц, перечисленных в предложении FROM
и удовлетворяющих предложению WHERE.
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

Синтаксис команды:

DELETE [Таблица.*]
FROM таблица
WHERE условие Отбора,

где Таблица — необязательное имя таблицы, из которой удаляются записи; таб-
лица — имя таблицы, из которой удаляются записи; условие Отбора — выражение,
определяющее удаляемые записи.

С помощью инструкции DELETE можно осуществлять удаление большого ко-

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

В следующем примере из таблицы «Абитуриент» будет удален абитуриент Ав-

деев Н. В.

. . . . . . . . . . . . . . . . . . . . . .

Пример 5.14

. . . . . . . . . . . . . . . . . . . . .

DELETE
FROM Абитуриент
WHERE ФИО_Абитуриента = 'Авдеев Н. В.'

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

Запрос на удаление удаляет записи целиком, а не только содержимое указанных

полей. Нельзя восстановить записи, удаленные с помощью запроса на удаление.
Чтобы узнать, какие записи будут удалены, необходимо посмотреть результаты
запроса на выборку, использующего те же самые условия отбора в предложении
Where, а затем выполнить запрос на удаление.