Многие запросы к БД не нуждаются в той степени детализации, которую обеспечивают SQL-запросы, рассмотренные в предыдущих примерах. Так, во всех запросах перечисленных далее, требуется узнать всего одно или несколько значений, которые подытоживают информацию, содержащуюся в БД:
На языке SQL запросы такого типа можно создавать с помощью агрегатных функций и предложений GROUP BY и HAVING, используемых в операторе SELECT.
Для подведения итогов по информации, содержащейся в БД, в SQL предусмотрены агрегатные функции. Агрегатная функция принимает в качестве аргумента какой-либо столбец данных целиком, а возвращает одно значение, которое определенным образом подытоживает этот столбец.
Например, агрегатная функция AVG() принимает в качестве аргумента столбец чисел и вычисляет их среднее значение.
Чтобы вычислить среднедушевой доход жителя Зеленограда, нужен такой запрос:
SELECT ‘СРЕДНЕДУШЕВОЙ ДОХОДА, AVG(SUMD) FROM PERSON
В SQL имеется шесть агрегатных функций, которые позволяют получать различные виды итоговой информации (рис. 3.16):
SUM() вычисляет сумму всех значений, содержащихся в столбце;
AVG() вычисляет среднее среди значений, содержащихся в столбце;
COUNT(*) подсчитывает количество строк в таблице результатов запроса.
Аргументом агрегатной функции может быть простое имя столбца, как в предыдущем примере, или выражение, как в следующем запросе, задающем вычисление среднедушевого налога:
SELECT AVG(SUMD*0.13)
Рис. 3.16.
При выполнении этого запроса создается временный столбец, содержащий значения (SUMD*0.13) для каждой строки таблицы PERSON, а затем вычисляется среднее значение временного столбца.
Сумму доходов у всех жителей Зеленограда можно вычислить с помощью агрегатной функции SUM:
SELECT SUM(SUMD) FROM PERSON
Агрегатная функция может быть использована и для вычисления итогов по таблице результатов, полученной соединением нескольких исходных таблиц. Например, можно вычислить общую сумму дохода, которая получена жителями от источника с названием «Стипендия»:
SELECT SUM(MONEY)
FROM PROFIT, HAVE_D
WHERE PROFIT.ID=HAVE_D.ID
AND PROFIT.SOURCE^Стипендия’
Агрегатные функции MIN() и MAX() позволяют найти соответственно наименьшее и наибольшее значения в таблице. При этом столбец может содержать числовые или строковые значения либо значения даты или времени.
Например, можно определить:
(а) наименьший общий доход, полученный жителями, и наибольший налог, подлежащий уплате:
SELECT MIN(SUMD), MAX(SUMD*0.13)
(б) даты рождения самого старого и самого молодого жителя:
SELECT MIN(RDATE), MAX(RDATE)
(в) фамилии, имена и отчества самого первого и самого последнего жителей в списке, упорядоченном по алфавиту:
SELECT MIN(FIO), MAX(FIO)
Применяя эти агрегатные функции, нужно помнить, что числовые данные сравниваются по арифметическим правилам, сравнение дат происходит последовательно (более ранние значения дат считаются меньшими, чем более поздние), сравнение интервалов времени выполняется на основании их продолжительности.
При использовании функции MIN() и МАХ() со строковыми данными результат сравнения двух строк зависит от используемой таблицы кодировки символов.
Агрегатная функция COUNT() подсчитывает количество значений в столбце любого типа:
(а) сколько квартир в 1-м микрорайоне?
SELECT COUNT(ADR)
WHERE ADR LIKE *%, 1_
(б) сколько жителей имеют источники дохода?
SELECT C0UNT(DISTINCT NOM)
(в) сколько источников дохода используются жителями?
SELECT COUNT(DISTINCT ID)
Ключевое слово «DISTINCT» указывает, что подсчитываются неповторяющиеся значения в столбце.
Специальная агрегатная функция COUNT(*) подсчитывает строки в таблице результатов, а не значения данных:
(а) сколько квартир во 2-м микрорайоне?
WHERE ADR LIKE "%, 2_-%’
(б) сколько источников дохода у Иванова Ивана Ивановича?
FROM PERSON, HAVE_D
WHERE FIO = "Иванов Иван Иванович"
AND PERSON.NOM = HAVE_D.NOM
(в) сколько жителей проживает в квартире по определенному адресу?
SELECT COUNT(*) FROM PERSON WHERE ADR = "Зеленоград, 1001-45’
Один из способов понять, как выполняются итоговые запросы с агрегатными функциями, - это представить выполнение запроса разбитым на две части. Сначала определяется, как бы запрос работал без агрегатных функций, возвращая несколько строк результатов. Затем применяются агрегатные функции к результатам запроса, возвращая одну итоговую строку.
Например, рассмотрим следующий сложный запрос: найти среднедушевой общий доход, сумму общих доходов жителей, а также среднюю доходность источника в процентах от общего дохода жителя. Ответ дает оператор
SELECT AVG(SUMD), SUM(SUMD), (100*AVG(MONEY/SUMD))
FROM PERSON, PROFIT, HAVE_D WHERE PERSON.NOM = HAVE_D.NOM AND HAVE_D.ID = PROFIT.ID
Без агрегатных функций запрос выглядел бы так:
SELECT SUMD, SUMD, M0NEY/SUMD FROM PERSON, PROFIT, HAVE_D WHERE PERSON.NOM = HAVE_D.NOM AND HAVE_D.ID = PROFIT.ID
и возвращал бы одну строку результатов для каждого жителя и конкретного источника дохода. Агрегатные функции используют столбцы таблицы результатов этого запроса для получения однострочной таблицы с итоговыми результатами.
В строке возвращаемых столбцов вместо имени любого столбца можно указать агрегатную функцию. Например, она может входить в выражение, в котором суммируются или вычитаются значения двух агрегатных функций:
SELECT МАХ(SUMD)-MIN(SUMD)
Однако агрегатная функция не может быть аргументом для другой агрегатной функции, т.е. запрещены вложенные агрегатные функции.
Кроме того, в списке возвращаемых столбцов нельзя одновременно использовать агрегатные функции и обычные имена столбцов, поскольку в этом нет смысла, например:
SELECT FIO, SUM(SUMD)
Здесь первый элемент списка указывает, чтобы СУБД создала таблицу, которая будет состоять из нескольких строк и содержать по одной строке для каждого жителя. Второй элемент списка просит СУБД получить одно результирующее значение, являющееся суммой значений столбца SUMD. Эти два указания противоречат друг другу, что приводит к ошибке.
Сказанное не относится к случаям обработки подзапросов и запросов с группировкой.
Важно! Если параметр функции имеет тип Строка и в нем указывается имя поля, которое содержит пробелы, то такое имя поля должно быть заключено в квадратные скобки.
Например: "[Количество Оборот]".
1. Сумма (Total) - рассчитывает сумму значений выражений, переданных ей в качестве аргумента для всех детальных записей. В качестве параметра можно передавать Массив. В этом случае функция будет применена к содержимому массива.
Пример
:
Сумма(Продажи.СуммаОборот)
2. Количество (Count) - рассчитывает количество значений отличных от значения NULL. В качестве параметра можно передавать Массив. В этом случае функция будет применена к содержимому массива.
Синтаксис
:
Количество([Различные] Параметр)
В указания получения различных значений следует перед параметром метода Количество указать Различные (Distinct).
Пример
:
Количество(Продажи.Контрагент)
Количество(Различные Продажи.Контрагент)
3. Максимум (Maximum)
- получает максимальное значение. В качестве параметра можно передавать Массив. В этом случае функция будет применена к содержимому массива.
Пример
:
Максимум(Остатки.Количество)
4. Минимум (Minimum) - получает минимальное значение. В качестве параметра можно передавать Массив. В этом случае функция будет применена к содержимому массива.
Пример
:
Минимум(Остатки.Количество)
5. Среднее (Average) - получает среднее значение для значений, отличных от NULL. В качестве параметра можно передавать Массив. В этом случае функция будет применена к содержимому массива.
Пример
:
Среднее(Остатки.Количество)
6. Массив (Array) - формирует массив, содержащий для каждой детальной записи значение параметра.
Синтаксис
:
Массив([Различные] Выражение)
В качестве параметра можно использовать таблицу значений. При этом результатом работы функции будет массив, содержащий значения первой колонки таблицы значений, переданной в качестве параметра. Если выражение содержит функцию Массив, то считается, что данное выражение является агрегатным. Если указано ключевое слово Различные, то получаемый массив не будет содержать дублирующихся значений.
Пример
:
Массив(Контрагент)
7. ТаблицаЗначений (ValueTable) - формирует таблицу значений, содержащую столько колонок, сколько параметров у функции. Детальные записи получаются из наборов данных, которые нужны для получения всех полей, участвующих в выражениях параметров функции.
Синтаксис
:
ТаблицаЗначений([Различные] Выражение1 [КАК ИмяКолонки1][, Выражение2 [КАК ИмяКолонки2],...])
Если параметрами функции выступают поля–остатки, то в результирующую таблицу значений попадут значения для записей по уникальным комбинациям измерений из других периодов. При этом значения получаются только для полей-остатков, измерений, счетов, полей периодов и их реквизитов. Значениями остальных полей в записях из других периодов считаются равными NULL. Если выражение содержит функцию ТаблицаЗначений, то считается, что данное выражение является агрегатным. Если указано ключевое слово Различные, то в получаемой таблице значений не будет строк, содержащих одинаковые данные. После каждого параметра может располагаться необязательное ключевое слово КАК и имя, которое будет назначено колонке таблицы значений.
Пример
:
ТаблицаЗначений(Различные Номенклатура, ХарактеристикаНоменклатуры КАК Характеристика)
8. Свернуть (GroupBy) - предназначена для удаления дубликатов из массива.
Синтаксис
:
Свернуть(Выражение, НомераКолонок)
Параметры :
9. ПолучитьЧасть (GetPart) - получает таблицу значений, содержащую определенные колонки из исходной таблицы значений.
Синтаксис
:
ПолучитьЧасть(Выражение, НомераКолонок)
Параметры :
Пример
:
ПолучитьЧасть(Свернуть(ТаблицаЗначений(НомерТелефона, Адрес) ,"НомерТелефона"),"НомерТелефона");
10. Упорядочить (Order) - предназначена для упорядочивания элементов массива и таблицы значений.
Синтаксис
:
Упорядочить(Выражение, НомераКолонок)
Параметры :
Пример
:
Упорядочить(ТаблицаЗначений(НомерТелефона, Адрес, ДатаЗвонка),"ДатаЗвонка Убыв");
11. СоединитьСтроки (JoinStrings) - предназначена для объединения строк в одну строку.
Синтаксис
:
СоединитьСтроки (Значение, РазделительЭлементов, РазделителиКолонок)
Параметры :
12. ГрупповаяОбработка (GroupProcessing) - возвращает объект ДанныеГрупповойОбработкиКомпоновкиДанных. В объект в свойство Данные помещается в виде таблицы значений значения группировок для каждого выражения, указанного в параметре функции Выражения. В случае использования иерархической группировки каждый уровень иерархии обрабатывается отдельно. Значения для иерархических записей также помещаются в данные. В свойство ТекущийЭлемент объекта помещается строка таблицы значений, для которой в настоящий момент вычисляется функция.
Синтаксис
:
ГрупповаяОбработка(Выражения, ВыраженияИерархии, ИмяГруппировки)
Параметры :
Синтаксис
:
Каждый(Выражение)
Параметр :
14. Любой (Any) - если хоть одна запись имеет значение Истина, то результат Истина, иначе Ложь
Синтаксис
:
Любой(Выражение)
Параметр :
15. СтандартноеОтклонениеГенеральнойСовокупности (Stddev_Pop) - вычисляет стандартное отклонение совокупности. Вычисляется по формуле: SQRT(ДисперсияГенеральнойСовокупности(X)).
Синтаксис
:
СтандартноеОтклонениеГенеральнойСовокупности(Выражение)
Параметр :
Пример
:
| X | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
| Y | 7 | 1 | 2 | 5 | 7 | 34 | 32 | 43 | 87 |
16. СтандартноеОтклонениеВыборки (Stddev_Samp) - вычисляет совокупное типовое стандартное отклонение. Вычисляется по формуле: SQRT(ДисперсияВыборки(X)).
Синтаксис
:
СтандартноеОтклонениеВыборки(Выражение)
Параметр :
Пример
:
| X | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
| Y | 7 | 1 | 2 | 5 | 7 | 34 | 32 | 43 | 87 |
17. ДисперсияВыборки (Var_Samp) - вычисляет типовое различие ряда чисел без учета значений NULL в этом наборе. Вычисляется по формуле: (Сумма(X^2) - Сумма(X)^2 / Количество(X)) / (Количество(X) - 1). Если Количество(X) = 1, то возвращается значение NULL.
Синтаксис
:
ДисперсияВыборки(Выражение)
Параметр :
19. КовариацияГенеральнойСовокупности (Covar_Pop) - вычисляет ковариацию ряда числовых пар. Вычисляется по формуле: (Сумма(Y * X) - Сумма(X) * Сумма(Y) / n) / n, где n число пар (Y, X) в которых ни Y ни X не являются NULL.
Синтаксис
:
КовариацияГенеральнойСовокупности(Y, X)
Параметры :
| X | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
| Y | 7 | 1 | 2 | 5 | 7 | 34 | 32 | 43 | 87 |
20. КовариацияВыборки (Covar_Samp) - вычисляет типовое различие ряда чисел без учета значений NULL в этом наборе. Вычисляется по формуле: (Сумма(Y * X) - Сумма(Y) * Сумма(X) / n) / (n-1), где n число пар (Y, X) в которых ни Y ни X не являются NULL.
Синтаксис
:
КовариацияВыборки(Y, X)
Параметры :
| X | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
| Y | 7 | 1 | 2 | 5 | 7 | 34 | 32 | 43 | 87 |
21. Корреляция (Corr) - вычисляет коэффициент корреляции ряда числовых пар. Вычисляется по формуле: КовариацияГенеральнойСовокупности(Y, X) / (СтандартноеОтклонениеГенеральнойСовокупности(Y) * СтандартноеОтклонениеГенеральнойСовокупности(X)). Не учитываются пары, в которых Y или X равны NULL.
Синтаксис
:
Корреляция(Y, X)
Параметры :
| X | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
| Y | 7 | 1 | 2 | 5 | 7 | 34 | 32 | 43 | 87 |
22. РегрессияНаклон (Regr_Slope) - вычисляет наклон линии. Вычисляется по формуле: КовариацияГенеральнойСовокупности(Y, X) / ДисперсияГенеральнойСовокупности(X). Вычисляется без учета пар, содержащих NULL.
Синтаксис
:
РегрессияНаклон(Y, X)
Параметры :
| X | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
| Y | 7 | 1 | 2 | 5 | 7 | 34 | 32 | 43 | 87 |
23. РегрессияОтрезок (Regr_Intercept) - вычисляет Y-точку пересечения линии регресса. Вычисляется по формуле: Среднее(Y) - РегрессияНаклон(Y, X) * Среднее(X). Вычисляется без учета пар, содержащих NULL.
Синтаксис
:
РегрессияОтрезок(Y, X)
Параметры :
25. РегрессияR2 (Regr_R2) - вычисляет коэффициент детерминации. Вычисляется без учета пар, содержащих NULL.
Синтаксис
:
РегрессияR2(Y, X)
Параметры :
| X | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
| Y | 7 | 1 | 2 | 5 | 7 | 34 | 32 | 43 | 87 |
26. РегрессияСреднееX (Regr_AvgX) - вычисляет среднее число X после исключения X и Y пар, где или X или Y являются пустыми. Среднее(X) вычисляется без учета пар, содержащих NULL.
Синтаксис
:
РегрессияСреднееX(Y, X)
Параметры :
| X | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
| Y | 7 | 1 | 2 | 5 | 7 | 34 | 32 | 43 | 87 |
27. РегрессияСреднееY (Regr_AvgY) - вычисляет среднее число Y после исключения X и Y пар, где или X или Y являются пустыми. Среднее(Y) вычисляется без учета пар, содержащих NULL.
Синтаксис
:
РегрессияСреднееY(Y, X)
Параметры :
| X | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
| Y | 7 | 1 | 2 | 5 | 7 | 34 | 32 | 43 | 87 |
28. РегрессияSXX (Regr_SXX) - вычисляется по формуле: РегрессияКоличество(Y, X) * ДисперсияГенеральнойСовокупности(X). Вычисляется без учета пар, содержащих NULL.
Синтаксис
:
РегрессияSXX(Y, X)
Параметры :
Пример
:
ВЫБРАТЬ РегрессияSYY(Y, X) ИЗ Таблица
Результат: 6445.55556
30. РегрессияSXY (Regr_SXY) - вычисляется по формуле: РегрессияКоличество(Y, X) * КовариацияГенеральнойСовокупности(Y, X). Вычисляется без учета пар, содержащих NULL.
Синтаксис
:
РегрессияSXY (Y, X)
Параметры :
| X | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
| Y | 7 | 1 | 2 | 5 | 7 | 34 | 32 | 43 | 87 |
31. МестоВПорядке (Rank)
Синтаксис
:
МестоВПорядке(Порядок, ПорядокИеррахии, ИмяГруппировки)
Параметры :
Пример
:
МестоВПорядке("[Количество Оборот]")
32. КлассификацияABC (ClassificationABC)
Синтаксис
:
КлассификацияABC(Значение, КоличествоГрупп, ПроцентыДляГрупп, ИмяГруппировки)
Параметры :
Пример
:
КлассификацияABC("Сумма(ВаловаяПрибыль)", 3, "60, 90")
Введение
SQL (Structured Query Language) - Структурированный Язык Запросов - стандартный язык запросов по работе с реляционными БД.
Первый международный стандарт языка SQL был принят в 1989 г. (далее мы будем называть его SQL/89 или SQL1). Иногда стандпрт SQL1 также называют стандартом ANSI/ISO и подавляющее большинство доступных на рынке СУБД поддерживают этот стандарт полностью.
В конце 1992 г. был принят новый международный стандарт языка SQL (который в дальнейшем будем называть SQL/92 или SQL2). И он не лишен недостатков, но в то же время является существенно более точным и полным, чем SQL/89. В настоящий момент большинство производителей СУБД вносят изменения в свои продукты так, чтобы они в большей степени удовлетворяли стандарту SQL2.
Последний стандарт по языку SQL был выпущен в 1996 г. Он назван SQL3.
SQL нельзя в полной мере отнести к традиционным языкам программирования: он не содержит традиционные операторы управления ходом выполнения программы, операторы описания типов и многое другое, он содержит только набор стандартных операторов доступа к данным, хранящимся в базе данных. Операторы SQL встраиваются в базовый язык программирования, которым может быть любой стандартный язык типа C++, PL, COBOL и т.д. Кроме того, операторы SQL могут выполняться непосредственно в интерактивном режиме.
1. Структура SQL.
SQL содержит следующие разделы:
1. Операторы определения данных DDL (Data definition language).
| Оператор | Смысл | Действие |
| CREATE TABLE | Создать таблицу | Создает новую таблицу в БД |
| DROP TABLE | Удалить таблицу | Удаляет таблицу из БД |
| ALTER TABLE | Изменить таблицу | Изменяет структуру существующей таблицы |
| CREATE VIEW | Создать представление | Создает виртуальную таблицу, т.е. таблицу, которая на самом деле не существует, но моделируется с использованием этого оператора. |
| ALTER VIEW | Изменить представление | Изменяет структуру или содержание виртуальной таблицы |
| DROP VIEW | Удалить представление | Удаляет описание виртуальной таблицы. Саму таблицу удалять не надо,т.к. она на самом деле и не существует. |
| CREATE INDEX | Создать индекс | Создает специальную физическую структуру, называемую индексом, которая обеспечивает ускорение доступа к данным |
| DROP INDEX | Удалить индекс | Удаляет созданную структуру |
| CREATE SYNONYM | Создать синоним | |
| DROP SYNONYM | Удалить синоним | |
2. Операторы манипулирования данными Data Manipulation Language (DML)
3. Язык запросов Data Query Language (DQL)
4. Средства управления транзакциями (DCL)
5. Средства администрирования данных (DDL)
Программный SQL
2. Типы данных
В языке SQL/89 поддерживаются следующие типы данных: CHARACTER, NUMERIC, DECIMAL, INTEGER, SMALLINT, FLOAT, REAL, DOUBLE PRECISION. Эти типы данных классифицируются на типы строк символов, точных чисел и приблизительных чисел.
В стандарте SQL92 добавлены следующие типы данных:
VARCHAR(n) - строки символов переменной длины
NCHAR(N) – строки локализованных символов постоянной длины
NCHAR VARYING(n) - строки локализованных символов переменной длины
BIT(n) - строка битов постоянной длины
BIT VARYING(n) - строка битов переменной длины
DATE календарная дата
TIMESTAMP(точность) дата и время
INTERVAL временной интервал
3. Оператор выбора SELECT
Select - единственный оператор поиска, который заменяет все операции реляционной алгебры.
Синтаксическая диаграмма опертора SELECT изображена на рис.1
Здесь ключевое слово ALL означает, что в результирующий набор строк включаются все строки удовлетворяющие условим запроса. Ключевое слово DISTINCT означает, что в результирующий набор включаются только различные строки, т.е. дубликаты строк результата не включаются в набор. Если не стоит никакое ключевое слово, то такая ситуация интерпретируется как наличие ключевого слова ALL.
Символ * означает, что в результирующий набор включаются все столбцы из исходных таблиц запроса.
В части FROM задается перечень исходных отношений (таблиц) запроса.
В части WHERE задаются условия отбора срок результата или условия соединиения кортежей исходных таблиц.
В частиGROUP BY задается список полей группировки.
В части HAVING задаются предикаты-условия, накладываемые на каждую группу.
В части ORDER BY задается список полей упорядочения результата.
В выражении условий для части WHERE могут быть использованы следующие предикаты:
· предикат сравнения с образцом LIKE и NOT LIKE
· предикат EXIST и NOT EXIST.
· предикаты сравнения{ =, <>, >,<,>=,<=,}. Синтаксическая диаграмма предикатов сравнения представлена на рис.2
Предикат IN истинен тогда, когда значение атрибута, заданного в проверяемом выражении для текущего кортежа совпадает хотя бы с одним из множества значений, полученных в результате выполнения соответствующего подзапроса или содержащихся в списке значений. И обратно, предикат NOT IN – истинен только тогда, когда значение заданного атрибута в текущем кортеже не совпадает ни с одним из множества значений, определенных встроенным подзапросом или заданным списком значений.
Шаблон может содержать символы _ подчеркивания для обозначения любого одиночного символа;
% символ процента - для обозначения любой произвольной последовательности символов.
Предикат LIKE истинен тогда, когда значение атрибута, заданного именем столбца в текущем кортеже включает в себя заданный <шаблон>.
Предитак NOT LIKE – истинен тогда, когда значение атрибута в текущем кортеже не включает в себя заданный <шаблон>.
· предикат NULL - неизвестно, неопределено
Синтаксическая диаграмма предиката представлена на рис. 7.
Отложив на время знакомство с группировкой, рассмотрим детально первые три строки оператора SELECT:
SELECT - ключевое слово, которое сообщает СУБД, что эта команда - запрос. Все запросы начинаются этим словом с последующим пробелом. За ним может следовать способ выборки - с удалением дубликатов (DISTINCT ), или без удаления (ALL , подразумевается по умолчанию). Затем следует список перечисленных через запятую столбцов, которые выбираются запросом из таблиц, или символ ‘* ’ для выбора всей строки. Любые столбцы, не перечисленные здесь, не будут включены в результирующий набор данных. Это, конечно, не значит, что они будут удалены или их информация будет стерта из таблиц, потому что запрос не воздействует на информацию в таблицах - он только показывает данные.
FROM - ключевое слово, которое должно быть представлено в каждом запросе. После ключевого слова FROM следует один или несколько пробелов и далее список исходных таблиц, которые используются в запросе. Имена таблиц отделяются друг от друга запятыми. Таблицам можно присвоить имена-псевдонимы, что бывает полезно для осуществления операции соединения таблицы с самой собой или для доступа из вложенного подзапроса к текущей записи внешнего запроса (вложенные подзапросы здесь не рассматриваются). Псевдоним – это временное имя таблицы, которое используется только в данном запросе и далее не применяется. Псевдоним отделяется от основного имени таблицы по крайней мере одним пробелом. Синтаксическая диаграмма части FROM представлена на рис. 9.
· WHERE - ключевое слово, за которым следует предикат-условие, который определяет те записи, которые попадут в результаирующий набор данных запроса.
Рассмотрим отношения базу данных, которая моделирует сдачу сессии в некотором учебном заведении. Пусть она состоит из трех отношений , , . Будем считать, что они представлены таблицами R1, R2 и R3 соответственно.
R1=(ФИО, Дисциплина, Оценка )
R2=(ФИО, Группа)
R3=(Группа, Дисциплина)
Приведем несколько примеров использования оператора SELECT.
· Список всех групп (без повторений), где должны пройти экзамены
SELECT DISTINCT Группы
FROM R3
· Список студентов, которые сдали экзамен по БД на «отлично»
SELECT ФИО
FROM R1
WHERE Дисциплина
= "БД" AND Оценка
= 5
· Список всех студентов, которым надо что-либо сдавать, вместе с названием дисциплины.
SELECT ФИО, Дисциплина
FROM R2, R3
WHERE R1.Группа
= R2.Группа
Здесь часть WHERE задает условия соединения отношений R1 и R2. При отсутствии условий соединения в части WHERE результат будет эквивалентен расширенному декартову произведению и, в этом случае, каждому студенту были бы приписаны все дисциплины из отношения R2, а не те, которые должна сдавать его группа.
· Список разгильдяев, имеющих несколько двоек
SELECT ФИО
FROM R1 a, R1 b
WHERE a.ФИО = b.ФИО
AND
a.Дисциплина
<> b.Дисциплина
AND
a.Оценка <= 2
AND b.Оценка.<= 2
Здесь мы использовали псевдонимы для именования отношения R1 a и b, так как для записи условий поиска нам необходимо работать сразу с двумя экземплярами данного отношения.
Из этих примеров хорошо видно, что логика работы оператора выбора (декартово произведение-селекция-проекция) не совпадает с порядком описания в нем данных (сначала список полей для проекции, потом список таблиц для декартова произведения, потом условие соединения). Дело в том, что SQL изначально разрабатывался для применения конечным пользователем, и его стремились приблизить к языку естественному, а не к языку алгоритмическому. Конечно, в качестве естественного выбран английский, как международный язык, широко используемый в вычислительной технике и программировании. По этой причине SQL на первых порах вызывает путаницу и раздражение у начинающих его изучать профессиональных программистов, которые привыкли разговаривать с машиной именно на алгоритмических языках.
Наличие неопределенных Null значений повышает гибкость обработки информации, хранящейся в БД. В наших примерах мы можем предположить ситуацию, когда студент пришел на экзамент, но не сдавал его по некоторой причине, в этом случае оценка по некоторой дисциплине для данного студента имеет неопределенное значение. В данной ситуации можно поставить вопрос: «Найти студентов, пришедших на экзамен, но не сдававших его с указанием названия дисциплины». Оператор Select будет выглядеть следующим образом:
SELECT ФИО, Дисциплина
WHERE Оценка IS NULL
Сразу хочу оговориться, что все примеры, приведенные ранее условны. Почему? Разве они не будут работать в реальных базах данных? Разве они неправильны? Здесь все правильно кроме имен атрибутов или столбцов таблицы. В большинстве СУБД (Систем управления базами данных) не разрешается именовать столбцы на национальных языках, это объекты базы данных и объекты языка и требуется, чтобы они именовались по правилам именования идентификаторов в данном языке. Чаще всего именем атрибута может быть последовательность букв латинского алфавита и цифр, начинающаяся с буквы, не содержащая некоторых специальных символов (например пробелов, точек, запятых, знаков процента % и других специальных символов) и имеющая некоторые ограничения по длине. В разных СУБД эти ограничения разные, например в MS SQL Server 2000 – длина имени атрибута может достигать 128 символов. Длинные имена атрибутов неудобны для написания запроса, но очень короткие однобуквенные имена не позволяют сохранить семантику смысл столбца таблицы, поэтому выбирают некоторый компромис и именуют недлинно, но удобно, так чтобы не надо было заглядывать в полное описание базы данных при написании каждого запроса. Кроме того, имена атрибутов, так же как и имена других объектов не должны совпарать с ключевыми словами языка SQL – т.е. теми словами, которые входят в операторы языка.
Поэтому с точки зрения корректности мы должны бы были схему базы данных «Сессия» представить в виде
R1=(St_name,Discipline, Mark )
R2=(St_name,N_group)
R3=(N_group,Discipline)
И соотвествующим образом изменить все запросы.
Применение агрегатных функций и вложенных запросов в операторе выбора
Запросы могут вычислять обобщенное групповое значение полей точно также как и значение одного поля. Это делается с помощью агрегатных функций. Агрегатные функции производят одиночное значение для всей группы таблицы. Список этих функций:
Агрегатные функции используются подобно именам полей в операторе SELECT, но с одним исключением: они берут имя поля как аргумент. С функциями SUM и AVG могут использоваться только числовые поля. С функциями COUNT, MAX, и MIN могут использоваться как числовые так и символьные поля. При использовании с символьными полями, MAX и MIN будут транслировать их в эквивалент ASCII, и обрабатывать в алфавитном порядке. Некоторые СУБД позволяют использовать вложенные агрегаты, но это является отклонением от стандарта ANSI со всеми вытекающими отсюда последствиями.
Обратившись снова к базе данных «Сессия» (таблицы R1, R2, R3), найдем количество успешно сданных экзаменов:
SELECT COUNT(*)
FROM R1
WHERE Mark > 2;
Это, конечно, отличается от выбора поля, поскольку всегда возвращается одиночное значение, независимо от того, сколько строк находится в таблице. Из-за этого агрегатные функции и поля не могут выбираться одновременно, если не будет использовано специальное предложение GROUP BY.
Предложение GROUP BY позволяет определять подмножество значений, которое далее называется группой, и применять функцию агрегата к этой группе. Группа образуется из всех строк, для которых значения полей группировки, заданные в предложении GROUP BY, имеют одинаковое значение. Это дает возможность объединять поля и агрегатные функции в едином предложении SELECT. Синтаксическая диаграмма применения агрегатных функций изображена на рис.10 Агрегатные функции могут применяться как в выражении вывода результатов строки SELECT , так и в выражении условии обработки сформированных групп HAVING . В этом случае каждая агрегатная функция вычисляется для каждой выделенной группы. Значения, полученные при вычислении агрегатных функций, могут быть использованы для вывода соответствующих результатов или для условия отбора групп.
При использовании агрегатных функций необходимо помнить, что в результирующем наборе могут присутствовать только значения полей группировки и возможно значения агрегатных функций. Не допустимо группировать по одним значениям, а выводить другие значения. Это будет синтаксической ошибкой.
Например, такой запрос всегда будет ошибочным:
Select A
Group By B
Действительно, давайте разберемся. Что же мы хотим найти? Мы пытаемся вывести некоторое значение столбца А из таблицы Т , и при этом выполняем группировку по другому столбцу, столбцу В. Выполняем группировку – это означает, собираем все строки с одинаковыми значениями столбца В в одну группу и дальше, а дальше непонятно, мы выводим значение столбца А, но ведь в одной группе может быть множество значений, разных значений столбца А. Так какое же значение мы выводим? Это непонятно ни нам, ни компьютеру. Именно поэтому он отказывается выполнять подобный запрос и заявляет, что у нас синтаксическая ошибка.
Sessia (N_zach,Discipline, Mark,Data_ex )
Пример 21 . Получить общее количество поставщиков (ключевое слово COUNT ):
SELECT COUNT(*) AS N
В результате получим таблицу с одним столбцом и одной строкой, содержащей количество строк из таблицы P:
Пример 23 . Для каждой детали получить суммарное поставляемое количество (ключевое слово GROUP BY …):
SUM(PD.VOLUME) AS SM
GROUP BY PD.DNUM;
Этот запрос будет выполняться следующим образом. Сначала строки исходной таблицы будут сгруппированы так, чтобы в каждую группу попали строки с одинаковыми значениями DNUM. Потом внутри каждой группы будет просуммировано поле VOLUME. От каждой группы в результатирующую таблицу будет включена одна строка:
Замечание . В списке отбираемых полей оператора SELECT, содержащего раздел GROUP BY можно включать только агрегатные функции и поля, которые входят в условие группировки . Следующий запрос выдаст синтаксическую ошибку:
SUM(PD.VOLUME) AS SM
GROUP BY PD.DNUM;
Причина ошибки в том, что в список отбираемых полей включено поле PNUM, которое не входит в раздел GROUP BY. И действительно, в каждую полученную группу строк может входить несколько строк с различными значениями поля PNUM. Из каждой группы строк будет сформировано по одной итоговой строке. При этом нет однозначного ответа на вопрос, какое значение выбрать для поля PNUM в итоговой строке.
Замечание . Некоторые диалекты SQL не считают это за ошибку. Запрос будет выполнен, но предсказать, какие значения будут внесены в поле PNUM в результатирующей таблице, невозможно.
Пример 24 . Получить номера деталей, суммарное поставляемое количество которых превосходит 400 (ключевое слово HAVING …):
Замечание . Условие, что суммарное поставляемое количество должно быть больше 400 не может быть сформулировано в разделе WHERE, т.к. в этом разделе нельзя использовать агрегатные функции. Условия, использующие агрегатные функции должны быть размещены в специальном разделе HAVING:
SUM(PD.VOLUME) AS SM
GROUP BY PD.DNUM
HAVING SUM(PD.VOLUME) > 400;
В результате получим следующую таблицу:
Замечание . В одном запросе могут встретиться как условия отбора строк в разделе WHERE, так и условия отбора групп в разделе HAVING. Условия отбора групп нельзя перенести из раздела HAVING в раздел WHERE. Аналогично и условия отбора строк нельзя перенести из раздела WHERE в раздел HAVING, за исключением условий, включающих поля из списка группировки GROUP BY.
Очень удобным средством, позволяющим формулировать запросы более понятным образом, является возможность использования подзапросов, вложенных в основной запрос.
Пример 25 . Получить список поставщиков, статус которых меньше максимального статуса в таблице поставщиков (сравнение с подзапросом):
WHERE P.STATYS <
(SELECT MAX(P.STATUS)
Замечание . Т.к. поле P.STATUS сравнивается с результатом подзапроса, то подзапрос должен быть сформулирован так, чтобы возвращать таблицу, состоящую ровно из одной строки и одной колонки .
Замечание
Выполнить один раз вложенный подзапрос и получить максимальное значение статуса.
Просканировать таблицу поставщиков P, каждый раз сравнивая значение статуса поставщика с результатом подзапроса, и отобрать только те строки, в которых статус меньше максимального.
Пример 26 . Использование предиката IN
(SELECT DISTINCT PD.PNUM
WHERE PD.DNUM = 2);
Замечание . В данном случае вложенный подзапрос может возвращать таблицу, содержащую несколько строк.
Замечание . Результат выполнения запроса будет эквивалентен результату следующей последовательности действий:
Выполнить один раз вложенный подзапрос и получить список номеров поставщиков, поставляющих деталь номер 2.
Просканировать таблицу поставщиков P, каждый раз проверяя, содержится ли номер поставщика в результате подзапроса.
Пример 27 . Использование предиката EXIST . Получить список поставщиков, поставляющих деталь номер 2:
PD.PNUM = P.PNUM AND
Замечание . Результат выполнения запроса будет эквивалентен результату следующей последовательности действий:
Просканировать таблицу поставщиков P, каждый раз выполняя подзапрос с новым значением номера поставщика, взятым из таблицы P.
В результат запроса включить только те строки из таблицы поставщиков, для которых вложенный подзапрос вернул непустое множество строк.
Замечание . В отличие от двух предыдущих примеров, вложенный подзапрос содержит параметр (внешнюю ссылку), передаваемый из основного запроса - номер поставщика P.PNUM. Такие подзапросы называются коррелируемыми (correlated ). Внешняя ссылка может принимать различные значения для каждой строки-кандидата, оцениваемого с помощью подзапроса, поэтому подзапрос должен выполняться заново для каждой строки, отбираемой в основном запросе. Такие подзапросы характерны для предиката EXIST, но могут быть использованы и в других подзапросах.
Замечание . Может показаться, что запросы, содержащие коррелируемые подзапросы будут выполняться медленнее, чем запросы с некоррелируемыми подзапросами. На самом деле это не так, т.к. то, как пользователь, сформулировал запрос, не определяет , как этот запрос будет выполняться. Язык SQL является непроцедурным, а декларативным. Это значит, что пользователь, формулирующий запрос, просто описывает, каким должен быть результат запроса , а как этот результат будет получен - за это отвечает сама СУБД.
Пример 28 . Использование предиката NOT EXIST . Получить список поставщиков, не поставляющих деталь номер 2:
PD.PNUM = P.PNUM AND
Замечание . Также как и в предыдущем примере, здесь используется коррелируемый подзапрос. Отличие в том, что в основном запросе будут отобраны те строки из таблицы поставщиков, для которых вложенный подзапрос не выдаст ни одной строки.
Пример 29 . Получить имена поставщиков, поставляющих все детали:
SELECT DISTINCT PNAME
PD.DNUM = D.DNUM AND
PD.PNUM = P.PNUM));
Замечание . Данный запрос содержит два вложенных подзапроса и реализует реляционную операцию деления отношений .
Самый внутренний подзапрос параметризован двумя параметрами (D.DNUM, P.PNUM) и имеет следующий смысл: отобрать все строки, содержащие данные о поставках поставщика с номером PNUM детали с номером DNUM. Отрицание NOT EXIST говорит о том, что данный поставщик не поставляет данную деталь. Внешний к нему подзапрос, сам являющийся вложенным и параметризованным параметром P.PNUM, имеет смысл: отобрать список деталей, которые не поставляются поставщиком PNUM. Отрицание NOT EXIST говорит о том, что для поставщика с номером PNUM не должно быть деталей, которые не поставлялись бы этим поставщиком. Это в точности означает, что во внешнем запросе отбираются только поставщики, поставляющие все детали.
