Главная | Обратная связь | Поможем написать вашу работу!

Применение агрегатных функций и вложенных запросов в операторе выбора

Введение

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)

DELETE	удалить строки	Удаляет строки из таблицы
INSERT	вставить строку	Вставляет строку в таблицу
UPDATE	обновить строку	Изменяет данные в строках таблицы

3. Язык запросов Data Query Language (DQL)

SELECT

Выбрать строки

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

4. Средства управления транзакциями (DCL)

COMMIT	Завершить транзакцию	Сохраняет все изменения в БД и делает невозможным откат транзакции и возвращение к старому состоянию БД.
ROLLBACK	откатить транзакцию	Отменяет все изменения, сделанные в базе данных и возвращает БД в исходное состояние
SAVEPOINT	Сохранить промежуточную точку выполнения транзакции	Дает возможность вернуться к изменениям, сделанным до точки сохранения, протгнорировав все изменения, сделанные после точки сохранения танзакции.

5. Средства администрирования данных (DDL)

ALTER DATABASE	Изменить БД
ALTER DBAREA	Изменить область хранения БД
ALTER PASSWORD	Изменить пароль
CREATE DATABASE	Создать БД
CREATE DBAREA	Создать область хранения
DROP DATABASE	Удалить БД
DROP DBAREA	Удалить область размещения базы данных
GRANT	Предоставить права доступа
REVOKE	Лишить прав доступа

Программный SQL

DECLARE CURSOR	Определяет курсор для запроса
OPEN	Открыть курсор
FETCH	Считать строку из множества строк, определенных курсором
CLOSE	Закрыть курсор
PREPARE	Подготовить оператор SQL к динамическому выполнению
EXECUTE	Выполнить динамический оператор

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

· предикат Between - принимает значение между а1 и а2 включительно.

· предикат Not Between - не принимает значение между а1 и а2

Синтаксическая диаграмма предиката Between представлена на рис.3

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

·

предикат IN - входит в множество / не входит в множество.

Предикат IN или NOT IN может также использоваться и для сравнения проверяемого выражения с подзапросом, в этом случае синтаксическая диаграмма изображена на рис. 5.

Предикат IN истинен тогда, когда значение атрибута, заданного в проверяемом выражении для текущего кортежа совпадает хотя бы с одним из множества значений, полученных в результате выполнения соответствующего подзапроса или содержащихся в списке значений. И обратно, предикат NOT IN – истинен только тогда, когда значение заданного атрибута в текущем кортеже не совпадает ни с одним из множества значений, определенных встроенным подзапросом или заданным списком значений.

·

предикат LIKE - включает (подобен)

Шаблон может содержать символы _ подчеркивания для обозначения любого одиночного символа;

% символ процента - для обозначения любой произвольной последовательности символов.

Предикат LIKE истинен тогда, когда значение атрибута, заданного именем столбца в текущем кортеже включает в себя заданный <шаблон>.

Предитак NOT LIKE – истинен тогда, когда значение атрибута в текущем кортеже не включает в себя заданный <шаблон>.

· предикат NULL - неизвестно, неопределено

Синтаксическая диаграмма предиката представлена на рис. 7.

Предикат NULL иститен тогда, когда значение атрибута, заданного именем столбца в текущем кортеже содержит неопределенное значение и обратно, предикат NOT NULL иститен тогда, когда значение атрибута в текущем кортеже содержит некоторое определенное значение.

· предикат EXISTS - существует что-то.

Синтаксическая диаграмма части WHERE в общем виде представлена на рис. 8.

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

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

SELECT — ключевое слово, которое сообщает СУБД, что эта команда — запрос. Все запросы начинаются этим словом с последующим пробелом. За ним может следовать способ выборки — с удалением дубликатов (DISTINCT), или без удаления (ALL, подразумевается по умолчанию). Затем следует список перечисленных через запятую столбцов, которые выбираются запросом из таблиц, или символ ‘ * ’ для выбора всей строки. Любые столбцы, не перечисленные здесь, не будут включены в результирующий набор данных. Это, конечно, не значит, что они будут удалены или их информация будет стерта из таблиц, потому что запрос не воздействует на информацию в таблицах — он только показывает данные.

FROM — ключевое слово, которое должно быть представлено в каждом запросе. После ключевого слова FROM следует один или несколько пробелов и далее список исходных таблиц, которые используются в запросе. Имена таблиц отделяются друг от друга запятыми. Таблицам можно присвоить имена-псевдонимы, что бывает полезно для осуществления операции соединения таблицы с самой собой или для доступа из вложенного подзапроса к текущей записи внешнего запроса (вложенные подзапросы здесь не рассматриваются). Псевдоним – это временное имя таблицы, которое используется только в данном запросе и далее не применяется. Псевдоним отделяется от основного имени таблицы по крайней мере одним пробелом. Синтаксическая диаграмма части FROM представлена на рис. 9.

Все последующие части оператора SELECT являются необязательными.

· 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 ФИО, Дисциплина

FROM R1

WHERE Оценка IS NULL

Сразу хочу оговориться, что все примеры, приведенные ранее условны. Почему? Разве они не будут работать в реальных базах данных? Разве они неправильны? Здесь все правильно кроме имен атрибутов или столбцов таблицы. В большинстве СУБД (Систем управления базами данных) не разрешается именовать столбцы на национальных языках, это объекты базы данных и объекты языка и требуется, чтобы они именовались по правилам именования идентификаторов в данном языке. Чаще всего именем атрибута может быть последовательность букв латинского алфавита и цифр, начинающаяся с буквы, не содержащая некоторых специальных символов (например пробелов, точек, запятых, знаков процента % и других специальных символов) и имеющая некоторые ограничения по длине. В разных СУБД эти ограничения разные, например в MS SQL Server 2000 – длина имени атрибута может достигать 128 символов. Длинные имена атрибутов неудобны для написания запроса, но очень короткие однобуквенные имена не позволяют сохранить семантику смысл столбца таблицы, поэтому выбирают некоторый компромис и именуют недлинно, но удобно, так чтобы не надо было заглядывать в полное описание базы данных при написании каждого запроса. Кроме того, имена атрибутов, так же как и имена других объектов не должны совпарать с ключевыми словами языка SQL – т.е. теми словами, которые входят в операторы языка.

Поэтому с точки зрения корректности мы должны бы были схему базы данных «Сессия» представить в виде

R1=(St_name,Discipline, Mark)

R2=(St_name,N_group)

R3=(N_group,Discipline)

И соотвествующим образом изменить все запросы.

Применение агрегатных функций и вложенных запросов в операторе выбора

Запросы могут вычислять обобщенное групповое значение полей точно также как и значение одного поля. Это делается с помощью агрегатных функций. Агрегатные функции производят одиночное значение для всей группы таблицы. Список этих функций:

Функция	Результат
	COUNT	Количество строк или непустых значений полей, которые выбрал запрос
	SUM	Сумма всех выбранных значений данного поля
	AVG	Среднеарифметическое значение всех выбранных значений данного поля
	MIN	Наименьшее из всех выбранных значений данного поля
	MAX	Наибольшее из всех выбранных значений данного поля

Агрегатные функции используются подобно именам полей в операторе 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

From T

Group By B

Действительно, давайте разберемся. Что же мы хотим найти? Мы пытаемся вывести некоторое значение столбца А из таблицы Т, и при этом выполняем группировку по другому столбцу, столбцу В. Выполняем группировку – это означает, собираем все строки с одинаковыми значениями столбца В в одну группу и дальше, а дальше непонятно, мы выводим значение столбца А, но ведь в одной группе может быть множество значений, разных значений столбца А. Так какое же значение мы выводим? Это непонятно ни нам, ни компьютеру. Именно поэтому он отказывается выполнять подобный запрос и заявляет, что у нас синтаксическая ошибка.

Вернемся к нашей базе данных «Сессия», но дополним ее еще несколькими атрибутами. Во-первых, среди студентов могут быть однофамильцы, поэтому для идентификации студента будем применять намер студенческой зачетной книжки, который всегда однозначно идентифицирует студента. И во-вторых, предположим, что студент может делать несколько попыток сдачи экзамена по одной и той же дисциплине, а для этого введем в отношение R1 дату очередной попытки сдачи экзамена. И, наконец, третье дополнение, будем считать, что у нас в Вузе учится множество групп по разным специальностям, тогда схема нашей базы данных будет следующей

Sessia (N_zach,Discipline, Mark,Data_ex)

12 3 4 5 6 7 Следующая ⇒