- •Методические указания к лабораторной работе №4 «Базы данных. Язык запросов sql»
- •Введение
- •Выборка данных
- •Сортировка данных
- •Фильтрация данных
- •Использование функций
- •Агрегирующие функции
- •Использование подзапросов
- •Объединение таблиц
- •Тестовые данные
- •Варианты заданий
- •Вариант 1 «Хранилище документов»
- •Вариант 2 «Музыкальный архив»
- •Вариант 3 «Библиотека»
- •Вариант 4 «Почтовый сервер»
- •Вариант 5 «Страховая компания»
- •Вариант 6 «Сервисный центр»
- •Список литературы
Агрегирующие функции
Агрегирующие функции позволяют выбрать из исходной выборки итоговые данные — суммарные и средние значения, количество значений. Если в запросе используется хотя бы одна агрегирующая функция, необходимо использовать агрегирующие значения во всех выражениях секции SELECT (либо поля без агрегирующий функций должны быть группирующими).
Список агрегирующих функций и их назначения описаны в таблице 2.
Таблица 2. Агрегирующие функции
Имя функции |
Назначение |
SUM |
Сумма значений столбца |
MIN |
Минимальное значение столбца |
MAX |
Максимальное значение столбца |
COUNT |
Число строк в выборке |
AVG |
Среднее значение столбца |
Следующий запрос подсчитывает сумму детальных строк всех заказов:
SELECT
SUM(OrderItem.OrderItemPrice)
FROM
OrderItems;
Для того чтобы в одном запросе использовать агрегирующие функции одновременно с обычными выражениями для столбцов, необходимо использовать группировку с помощью секции GROUP BY. Обобщено синтаксис данной секции можно представить следующим образом:
GROUP BY
поле1 [, поле2 ...]
Следующий запрос подсчитывает сумму детальных строк в разрезе конкретных заказов:
SELECT
OrderItems.OrderID,
SUM(OrderItems.OrderItemPrice)
FROM
OrderItems
GROUP BY
OrderItems.OrderID;
Использование подзапросов
Подзапрос — это оператор SELECT, вложенный в другую операцию выборки или обработки данных. Подзапрос оформляется в полном соответствии с синтаксисом обычных запросов, но при этом всегда должен возвращать выборку ровно из одного столбца. Подзапрос можно использовать в качестве выражения для столбца в секции SELECT запроса, либо в качестве ограничения на выбираемые данные в секции WHERE запроса. Внутри подзапроса допускается обращение к полям из внешнего по отношению к нему запроса. Это означает, что подзапрос должен вычислиться для каждой строки выборки внешнего запроса.
Следующий пример иллюстрирует, как можно выбрать имена всех заказчиков, которые заказывали заданный продукт с помощью вложенных подзапросов:
SELECT *
FROM [Customers]
WHERE [Customers].[CustomerID] IN
(SELECT [Orders].[CustomerID]
FROM [Orders]
WHERE [Orders].[OrderID] IN
(SELECT [OrderItems].[OrderID]
FROM [OrderItems]
WHERE [OrderItems].[ProductID] IN
(SELECT [Products].[ProductID]
FROM [Products]
WHERE [Products].[ProductName] = 'Цейлонский чай')));
Следующий пример иллюстрирует, как можно вычислить для каждого заказчика количество поступивших от него заказов:
SELECT
[Customers].[CustomerSurname],
[Customers].[CustomerName],
(SELECT
COUNT(*)
FROM
[Orders]
WHERE
[Orders].[CustomerID] = [Customers].[CustomerID]) AS [OrderCount]
FROM
[Customers];
При использовании подзапросов необходимо помнить о том, что каждый дополнительный уровень вложенности подзапросов чреват увеличением времени выполнения запроса. Поэтому необходимо стремиться сводить использование подзапросов к минимуму, а для некоторых задач находить более приемлемое по производительности решение с использованием объединения таблиц.