ID: 4671

Название работы: Информационное обеспечение систем управления. Построение запросов при работе с базой данных

Категория: Книга

Предметная область: Информатика, кибернетика и программирование

Описание: Тестовая база данных Перед изучением языка SQL необходимо рассмотреть тестовую базу данных на которой будут отрабатываться все запросы. Наша тестовая база данных полностью соответствует рассмотренной в учебном пособии по нормализации данных и создан...

Язык: Русский

Дата добавления: 2012-11-24

Размер файла: 710.5 KB

Работу скачали: 12 чел.

Тестовая база данных

Перед изучением языка SQL необходимо рассмотреть тестовую базу данных на которой будут отрабатываться все запросы. Наша тестовая база данных полностью соответствует рассмотренной в учебном пособии по нормализации данных и создании базы данных. Напоминаю,  что эта база данных описывает сферу воздушных перевозок. Все пояснения к модели даны в пособии по нормализации данных и создании базы данных

Для начала приведем логическую модель БД, где каждый информационный объект отображается реляционной таблицей, а связи между таблицами соответствуют связям между информационными объектами. Модель представлена в виде диаграммы

Рисунок 1. Логическая модель

Последний этап моделирования БД - переход к физическому уровню модели, на котором модель также представлена в виде диаграммы

Рисунок 2. Физическая модель

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

Вид объекта	Название на логическом уровне	Название на физическом уровне
Таблица	Авиамаршрут	AIRLINE
Поле	Номер маршрута	AL_NUM
Поле	Код Типа Самолета	AL_PL_CODE
Поле	Код Авиакомпании	AL_AC_CODE
Поле	Откуда	AL_AP_FROM
Поле	Куда	AL_AP_TO
Таблица	Аэропорт	AIRPORT
Поле	Код аэропорта	AP_CODE
Поле	Название аэропорта	AP_NAME
Поле	Код Города	AP_CT_CODE
Таблица	Авиакомпания	AIRCOMPANY
Поле	Код Авиакомпании	AC_CODE
Поле	Название Авиакомпании	AC_NAME
Таблица	Борт	BOARD
Поле	Борт номер	BRD_NUM
Поле	Код Типа Самолета	BRD_PL_CODE
Поле	Код Авиакомпании	BRG_AC_CODE
Таблица	Город	CITY
Поле	Код Города	CT_CODE
Поле	Название Города	CT_NAME
Таблица	Член Экипажа	EQUIPAGE
Поле	Код Члена Экипажа	EQ_CODE
Поле	Дата вылета	EQ_FL_DATE
Поле	Номер маршрута	EQ_FL_NUM
Поле	Код Личности	EQ_PR_CODE
Поле	Код роли	EQ_RNK_CODE
Таблица	Рейс	FLIGHT
Поле	Дата вылета	FL_DATE
Поле	Номер маршрута	FL_NUM
Поле	Борт номер	FL_BRD_NUM
Таблица	Личность	PERSON
Поле	Код Личности	PR_CODE
Поле	ФИО	PR_NAME
Таблица	Тип Самолета	PLANE
Поле	Код Типа Самолета	PL_CODE
Таблица	Роль члена экипажа	RANK
Поле	Код роли	RNK_CODE
Поле	Роль	RNK_NAME
Таблица	Тип Салона	SALON
Поле	Код Типа Салона	SL_TYPE
Поле	Название Типа Салона	SL_NAME
Таблица	Салон в Самолете	SALON_IN_PLANE
Поле	Код Типа Салона	SP_SL_TYPE
Поле	Код Типа Самолета	SP_PL_CODE
Поле	Количество мест	SP_COUNT
Таблица	Расписание	TIMETABLE
Поле	Код аэропорта	TBL_AP_CODE
Поле	Номер маршрута	TBL_AL_NUM
Поле	Время Прилета	TBL_DOWN_TIME
Поле	Время Вылета	TBL_START_TIME
Поле	Номер в Маршруте	TBL_NUMBER
Таблица	Тариф	TARIFF
Поле	Код Тарифа	TR_CODE
Поле	Код Типа Салона	TR_SL_TYPE
Поле	Номер маршрута	TR_AL_NUM
Поле	Цена билета	TR_COST
Поле	Аэропорт Откуда	TR_AP_FROM
Поле	Аэропорт Куда	TR_AP_TO
Таблица	Билет	TICKET
Поле	Номер билета	TC_NUM
Поле	Код Личности	TC_PR_CODE
Поле	Код Тарифа	TC_TR_CODE
Поле	Дата вылета	TC_FL_DATE
Поле	Номер маршрута	TC_FL_NUM

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

Выборка данных. Команда SELECT

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

Синтаксис оператора SELECT

Рассмотрим упрощенный синтаксис этого оператора.

SELECT [ALL | DISTINCT] <список выбора>

 FROM <ссылка на таблицу> [, <ссылка на таблицу>]...

 [WHERE <условия поиска>]

 [GROUP BY <список столбцов> [HAVING <условия поиска>]]

 [ORDER BY <список упорядочения>];

Разберем предложения оператора.

SELECT [ALL | DISTINCT]

<список выбора> — список выбираемых столбцов, констант, функций или выражений, разделенных запятыми.

Необязательное ключевое слово ALL означает, что выбираются все строки, соответствующие условию поиска. Это значение по умолчанию, и обычно в операторе не указывается. Ключевое слово DISTINCT указывает, что в выходной набор данных попадут лишь отличающиеся строки, дубликаты будут отбрасываться.

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

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

<имя таблицы>.<имя столбца>

или

<псевдоним таблицы>.<имя столбца>

Уточненные имена столбцов обязательно должны присутствовать в операторах SELECT, где присутствует несколько таблиц, это позволит избежать двусмысленности при обращении к столбцам с одинаковым именем из разных таблиц. Такая ситуация может возникнуть при выполнении соединений таблиц.

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

SELECT     CITY.CT_NAME  AS "Город", AIRPORT.AP_NAME AS "Аэропорт"

FROM AIRPORT INNER     JOIN  CITY  

    ON  AIRPORT.AP_CT_CODE = CITY.CT_CODE

WHERE  CITY.CT_NAME = 'Москва'

Русские тексты здесь заключены в кавычки, потому что содержат символы, недопустимые в именах, а например CT_NAME, являясь правильным именем в SQL, может обойтись без кавычек. Строковые константы должны заключатся в одинарные кавычки.

ВНИМАНИЕ!

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

Чуть позже мы рассмотрим некоторые варианты.

FROM <ссылка на таблицу> задает список имен таблиц, в которых осуществляется поиск. Здесь же можно указывать соединения (JOIN) таблиц, представления (см. в конце этой главы).

Необязательное предложение WHERE <условия поиска> определяет условия поиска строк в таблицах. На самом деле это предложение не является таким уж необязательным. Если таблица в базе данных содержит тысячи строк, то, во-первых, вам вряд ли понадобится за один раз такое количество записей, поскольку просмотреть их вы просто физически не сможете, а во-вторых, выборка больших объемов данных на сервере приводит к увеличению сетевого трафика и перегрузке сети.

Необязательное предложение GROUP BY <имя столбца> группирует найденные строки в соответствии со значением указанного столбца.

Необязательное предложение HAVING <условия поиска> определяет дополнительные условия поиска для использования в GROUP BY.

Необязательное предложение ORDER BY <список имен> позволяет упорядочить найденные строки, указывая список имен столбцов (здесь опять же можно использовать уточненные имена в виде <имя таблицы>.<имя столбца>), а также направление сортировки для каждого столбца — ключевые слова ASCENDING (по возрастанию) или DESCENDING (по убыванию).

Для разъяснения  содержания запроса в любом месте может быть размещен комментарий - произвольный текст ограниченный парой символов, спереди / *, сзади */ (так же как в языке С).

Синтаксис:

/ * т е к с т . . .*/

Текст комментария может занимать несколько строк.

Многие СУБД допускают удобный способ комментирования при помощи пары символов  ‘--’, текст от этой пары до конца строки будет считаться комментарием.

Простые варианты поиска данных

Рассмотрим вначале наиболее простые варианты использования оператора. Мы будем отыскивать данные каждый раз только в одной из таблиц базы данных.

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

В самом простом варианте оператора SELECT можно указать вместо списка отображаемых столбцов символ *, например:

SELECT * FROM PERSON

Этот оператор возвращает все столбцы всех строк таблицы PERSON

Листинг 1. Выбор всех столбцов всех строк таблицы людей

PR_CODE	PR_NAME	PR_NAME2	PR_NAME3	PR_BIRTHDAY
1	Зеленина	Надежда	Александровна	11.12.1965
2	Бычкова	Марина	Валентиновна	01.04.1957
3	Мельчакова	Оксана	Владимировна	06.07.1978
4	Бычков	Максим	Владимирович	08.09.1970
5	Попутько	Павел	Павлович	05.05.1959
6	Лебедев	Александр	Анатольевич	03.04.1977
7	Варанкина	Елена	Алевтиновна	01.02.1952
8	Лядов	Олег	Владимирович	07.06.1964
9	Вашкарина	Лариса	Вилльевна	05.11.1980
10	Верхоланцева	Анна	Николаевна	06.10.1991
11	Алексеев	Юлий	Альбертович	19.09.1974

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

SELECT PR_NAME, PR_NAME2, PR_NAME3, PR_BIRTHDAY

FROM PERSON

Мы получим те же записи. Каждая будет содержать фамилию, имя, отчество и дату рождения человека.

Заголовки нам с вами, как проектировщикам таблиц нашей базы данных, понятны. Однако другим людям следует долго объяснять, что это такое. Лучше сразу сформировать заголовки на русском языке. Выполните:

SELECT PR_NAME2 AS "Имя",

PR_NAME3 AS "Отчество",

PR_NAME  AS "Фамилия",

PR_BIRTHDAY AS "Дата рождения"

FROM PERSON

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

Листинг 2. Добавление осмысленных заголовков

Имя	Отчество	Фамилия	Дата рождения
Надежда	Александровна	Зеленина	11.12.1965
Марина	Валентиновна	Бычкова	01.04.1957
Оксана	Владимировна	Мельчакова	06.07.1978
Максим	Владимирович	Бычков	08.09.1970
Павел	Павлович	Попутько	05.05.1959
Александр	Анатольевич	Лебедев	03.04.1977
Елена	Алевтиновна	Варанкина	01.02.1952
Олег	Владимирович	Лядов	07.06.1964
Лариса	Вилльевна	Вашкарина	05.11.1980
Анна	Николаевна	Верхоланцева	06.10.1991
Юлий	Альбертович	Алексеев	19.09.1974

ЗАМЕЧАНИЕ

Обратите, пожалуйста, внимание на вид, в котором представляются операторы SELECT в этой главе. Вместо того чтобы даже короткие операторы размещать на одной строке,  старайтесь отдельные группы переносить на новые строки. SQL позволяют для ввода любого оператора использовать произвольное количество cтрок.

В списке выбора могут присутствовать не только имена столбцов, но и константы или литералы (строки). Выполните следующий оператор:

SELECT PR_NAME2   AS "Имя",

PR_NAME3   AS "Отчество",

'Фамилия:'   AS "Текст",

PR_NAME   AS "Фамилия",

PR_BIRTHDAY AS "Дата рождения"

FROM PERSON

В результате выполнения запроса один столбец будет во всех строках содержать один и тот же текст — "Фамилия:". Ему мы и присвоили заголовок Текст

Листинг 3. Добавление в результат выбора текстовой строки "Фамилия:"

Имя	Отчество	Текст	Фамилия	Дата рождения
Надежда	Александровна	Фамилия:	Зеленина	11.12.1965
Марина	Валентиновна	Фамилия:	Бычкова	01.04.1957
Оксана	Владимировна	Фамилия:	Мельчакова	06.07.1978
Максим	Владимирович	Фамилия:	Бычков	08.09.1970
Павел	Павлович	Фамилия:	Попутько	05.05.1959
Александр	Анатольевич	Фамилия:	Лебедев	03.04.1977
Елена	Алевтиновна	Фамилия:	Варанкина	01.02.1952
Олег	Владимирович	Фамилия:	Лядов	07.06.1964
Лариса	Вилльевна	Фамилия:	Вашкарина	05.11.1980
Анна	Николаевна	Фамилия:	Верхоланцева	06.10.1991
Юлий	Альбертович	Фамилия:	Алексеев	19.09.1974

Как это будет использоваться, покажем позднее, при исследовании ключевого слова UNION

В вывод может попадать не только столбец но и результат каких либо действий над столбцами, для примера, вместо имени, отчества и фамилии мы можем вывести  один столбец содержащий результат объединения имени, отчества и фамилии из таблицы  PERSON добавив между ними пробелы (соединение нескольких строк осуществляем при помощи операции  конкатенации – ||):

SELECT PR_NAME2  || ' ' || PR_NAME3 || ' ' || PR_NAME AS "ФИО",

PR_BIRTHDAY AS "Дата рождения"

FROM PERSON

В результате выполнения запроса получим

Листинг 4. Список людей с использованием соединения имени, отчества и фамилии при помощи операции  конкатенации

ФИО	Дата рождения
Надежда Александровна Зеленина	11.12.1965
Марина Валентиновна Бычкова	01.04.1957
Оксана Владимировна Мельчакова	06.07.1978
Максим Владимирович Бычков	08.09.1970
Павел Павлович Попутько	05.05.1959
Александр Анатольевич Лебедев	03.04.1977
Елена Алевтиновна Варанкина	01.02.1952
Олег Владимирович Лядов	07.06.1964
Лариса Вилльевна Вашкарина	05.11.1980
Анна Николаевна Верхоланцева	06.10.1991
Юлий Альбертович Алексеев	19.09.1974

Упорядочение результата запроса. Предложение ORDER BY

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

Чтобы явно задать нужный нам порядок в оператор следует ввести предложение ORDER BY. В предложении перечисляются имена столбцов таблицы или порядковые номера столбцов, указанных в списке выбора. По умолчанию сортировка выполняется в возрастающем порядке, как если бы вы задали ключевое слово ASCENDING, однако для различных столбцов в одном и том же предложении вы можете указать и убывающий порядок, задав ключевое слово DESCENDING. Для ключевого слова ASCENDING допустимо сокращение ASC, для DESCENDING — DESC.

Предложение ORDER BY имеет следующий синтаксис:

ORDER BY {<имя столбца> | <номер столбца>} [{ASC[ENDING] | DESC[ENDING]]}

Помимо имен столбцов в этом предложении мы можем указать и порядковые номера столбцов в списке выбора. Столбцы в списке нумеруются, начиная с единицы. Чтобы можно было использовать номера столбцов, вы должны явно перечислить столбцы в списке выбора. Вариант * в этом случае недопустим.

Что интересно — столбцы, по которым выполняется сортировка данных, вовсе не обязательно должны быть включены в состав выбираемых столбцов, хотя и не совсем понятно, кому это может понадобиться. Такую сортировку допускают делать далеко не все реляционные базы данных.

Рассмотрим примеры с нашей таблицей людей. Введите и выполните:

SELECT PR_NAME2 AS "Имя",

PR_NAME3 AS "Отчество",

'Фамилия:' AS "Текст",

PR_NAME AS "Фамилия",

PR_BIRTHDAY AS "Дата рождения"

FROM PERSON

ORDER PR_NAME, PR_NAME2;

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

В нашем примере мы получили список, отсортированный по фамилиям в возрастающем порядке. Если несколько человек имеют одну и ту же фамилию, то строки еще упорядочиваются и по именам. Как и должно быть. Если нужно, мы можем добавить еще один уровень сортировки — по отчествам.

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

Зададим упорядочение не в виде имен столбцов, а указанием их номеров. Напомню, что столбцы в списке выбора нумеруются начиная с единицы, а в самом списке выбора нельзя указывать символ *.

SELECT PR_NAME2 AS "Имя",

PR_NAME3 AS "Отчество",

'Фамилия:' AS "Текст",

PR_NAME AS "Фамилия",

PR_BIRTHDAY AS "Дата рождения"

FROM PERSON

ORDER 3, 2;

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

SELECT PR_NAME2 AS "Имя",

PR_NAME3 AS "Отчество",

'Фамилия:' AS "Текст",

PR_NAME AS "Фамилия",

PR_BIRTHDAY AS "Дата рождения"

FROM PERSON

ORDER BY  PR_NAME, 2;

Это работает точно так же, как и в обоих предыдущих случаях.

Теперь проверим, действительно ли работает вариант различного направления сортировки в разных столбцах в одном предложении. Изменим оператор, добавив упорядочение по столбцу PR_NAME3 (имя человека) в убывающем порядке:

SELECT PR_NAME2 AS "Имя",

PR_NAME3 AS "Отчество",

'Фамилия:' AS "Текст",

PR_NAME AS "Фамилия",

PR_BIRTHDAY AS "Дата рождения"

FROM PERSON

ORDER BY  PR_NAME, PR_NAME2  DESC;

Все работает замечательно Фамилии сортируются в возрастающем порядке, а имена у однофамильцев — в убывающем.

Интересно, как поведут себя пустые значения (NULL) в результате сортировки? Стандарт допускает помещение всех таких строк либо в самое начало списка, либо в самый конец сортируемого набора данных. Посмотрим, как решается этот вопрос в наших системах управления базами данных.

SELECT PR_NAME2 AS "Имя",

PR_NAME3 AS "Отчество",

'Фамилия:' AS "Текст",

PR_NAME AS "Фамилия",

PR_BIRTHDAY AS "Дата рождения"

FROM PERSON

ORDER BY PR_BIRTHDAY, PR_NAME

Получим следующий список, упорядоченный по датам рождения.

Листинг 5. Список личностей упорядоченный по дате рождения

Имя	Отчество	Фамилия	Дата рождения
Татьяна	Валерьевна	Веремчук	23.08.1948
Татьяна	Николаевна	Некрасова	04.12.1949
Елена	Алевтиновна	Варанкина	01.02.1952
Татьяна	Владимировна	Хемлих	25.10.1953
Татьяна	Сергеевна	Арсентьева	10.09.1954
Татьяна	Анатольевна	Баландина	11.01.1955
Кирил	Владимирович	Бешляга	07.01.1956
Марина	Валентиновна	Бычкова	01.04.1957
Людмила	Александровна	Блинова	28.08.1957
Елена	Александровна	Беляева	30.04.1958
Павел	Павлович	Попутько	05.05.1959
Татьяна	Викторовна	Владысик	19.10.1960
Светлана	Александровна	Ветошкина	23.12.1960
Татьяна	Александровна	Звездина	01.10.1961
Татьяна	Фёдоровна	Леушина	04.05.1963
Олег	Владимирович	Лядов	07.06.1964
Оксана	Васильевна	Власова	11.02.1965
Надежда	Александровна	Зеленина	11.12.1965
Татьяна	Евгеньевна	Беспалова	21.12.1965
Татьяна	Александровна	Соснина	05.12.1968
Татьяна	Александровна	Мазеина	06.04.1969
Татьяна	Геннадьевна	Зырянова	22.01.1970
Максим	Владимирович	Бычков	08.09.1970
Юлий	Альбертович	Алексеев	19.09.1974
Татьяна	Сергеевна	Бартош	02.12.1974
Александр	Анатольевич	Лебедев	03.04.1977
Татьяна	Владимировна	Алтынцева	03.09.1977
Оксана	Владимировна	Мельчакова	06.07.1978
Татьяна	Михайловна	Мосина	30.06.1979
Татьяна	Юрьевна	Шарапова	14.04.1980
Лариса	Вилльевна	Вашкарина	05.11.1980
Анна	Николаевна	Верхоланцева	06.10.1991
Наталья	Владимировна	Алавердян
Назгуль	Асановна	Алиева
Руслан	Рафаилович	Амерзянов
Ксения	Аркадьевна	Антонова

Видно, что все пустые значения помещаются в самый конец списка. Этот результат мы получим при использовании Firebird версии 1.5, в Firebird 2.0 все получается с точностью до наоборот — пустые значения находятся в начале списка.

Использование ключевого слова DISTINCT

Выполните следующий оператор:

SELECT PR_NAME2 AS "Имя"

FROM PERSON

Вы получите список из имен, среди которых есть много повторяющихся.

ЗАМЕЧАНИЕ

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

Чтобы убрать ненужные нам повторы, необходимо в оператор ввести ключевое слово DISTINCT (заодно упорядочим вывод по имени):

SELECT  DISTINCT PR_NAME2 AS "Имя"

FROM PERSON

ORDER BY PR_NAME2;

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

Листинг 6. Список всех имен (без повторов)

Имя		Имя		Имя
Алевтина		Екатерина		Николай
Александр		Елена		Нина
Алексей		Илья		Оксана
Альбина		Ирина		Олег
Амина		Кирил		Ольга
Анастасия		Константин		Павел
Ангелина		Ксения		Петр
Анна		Лариса		Руслан
Антонина		Лиана		Светлана
Валентина		Лилия		Семен
Валерий		Нина		Сергей
Василий		Любовь		Станислав
Вера		Людмила		Тарас
Виктория		Максим		Татьяна
Владимир		Марианна		Эдуард
Вячеслав		Марина		Эльвира
Галина		Мария		Эмма
Гульнара		Михаил		Юлиана
Дмитрий		Надежда		Юлий
Евгений		Назгуль		Юлия
Евгения		Наталья		Явар

Предложение WHERE

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

Следует отдавать себе отчет, что выражение в предложении WHERE является логическим выражением, возвращающим истинностное значение TRUE, FALSE или UNKNOWN. В выборку будут попадать только те записи таблицы, для которых это выражение дает истинный результат (TRUE). Соответственно, к выражению применимы все законы исчисления высказываний. На практике чаще всего используется закон де Моргана, про который далее мы скажем несколько слов.

Надо еще помнить, что операции сравнения, в которых принимают участие пустые значения (NULL), никогда не дают истинного значения Для таких столбцов дополнительно следует также использовать проверку типа IS NULL или IS NOT NULL.

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

Синтаксис предложения WHERE

Начальное определение синтаксиса предложения WHERE до неприличия простое:

WHERE <условия поиска>

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

<условия поиска> ::= <значение> <оператор> {<значение> | (<выбор одного>)}

 | <значение> [NOT] BETWEEN <значение> AND <значение>

 | <значение> [NOT] LIKE <значение>

 | <значение> [NOT] IN (<значение> [, <значение>] ... | <список выбора>)

 | <значение> IS [NOT] NULL

 | <значение> <оператор> {ALL | SOME | ANY} (<выбор списка значений одного столбца>)

 | EXISTS (<список выбора>)

 | SINGULAR (<список выбора>)

 | <значение> [NOT] CONTAINING <значение>

 | <значение> [NOT] STARTING [WITH] <значение>

 | <значение> <оператор> {ALL | SOME | ANY} (<выбор списка значений одного столбца>)

 | EXISTS (<список выбора>)

 | SINGULAR (<список выбора>)

 | (<условия поиска>)

 | NOT <условия поиска>

 | <условия поиска> OR <условия поиска>

 | <условия поиска> AND <условия поиска>

Здесь <оператор> — один из допустимых операторов сравнения: =, <, >, <=, >=, !<, !> <>, !=. Восклицательный знак используется в этих операторах как отрицание. Кроме того, в этом качестве также используется и символ ^. К перечню операторов сравнения следует также добавить и следующие: ^=, ^>, ^<.

<значение> ::= {

 <имя столбца>

 | <константа> | <выражение> | <функция>

 | NULL }

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

Не спеша, просмотрим по порядку возможные варианты выбора нужных нам строк.

Использование операторов сравнения

Рассмотрим использование операторов сравнения в конструкции:

<значение> <оператор> {<значение> | (<выбор одного>)}

Операторы сравнения применимы к любым типам данных, кроме BLOB.

Выполним следующий оператор выборки всех данных из таблицы PERSON, SELECT  *  FROM  PERSON

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

Найдем всех Татьян, данные на которых хранятся в базе данных:

SELECT  *  FROM  PERSON

WHERE PR_NAME2 = 'Татьяна'

Листинг 7.  Список всех Татьян.

PR_CODE	PR_NAME	PR_NAME2	PR_NAME3	PR_BIRTHDAY
13	Беспалова	Татьяна	Евгеньевна	21.12.1965
77	Соснина	Татьяна	Александровна	05.12.1968
93	Хемлих	Татьяна	Владимировна	25.10.1953
104	Шарапова	Татьяна	Юрьевна	14.04.1980
118	Алтынцева	Татьяна	Владимировна	03.09.1977
121	Арсентьева	Татьяна	Сергеевна	10.09.1954
124	Баландина	Татьяна	Анатольевна	11.01.1955
126	Бартош	Татьяна	Сергеевна	02.12.1974
143	Веремчук	Татьяна	Валерьевна	23.08.1948
146	Владысик	Татьяна	Викторовна	19.10.1960
173	Звездина	Татьяна	Александровна	01.10.1961
175	Зырянова	Татьяна	Геннадьевна	22.01.1970
216	Леушина	Татьяна	Фёдоровна	04.05.1963
223	Мазеина	Татьяна	Александровна	06.04.1969
230	Мосина	Татьяна	Михайловна	30.06.1979
236	Некрасова	Татьяна	Николаевна	04.12.1949

Теперь рассмотрим использование условий “>”,”<”

Найдем всех рожденных после  1.01.1970:

SELECT PR_NAME2  AS "Имя",

PR_NAME3  AS "Отчество",

PR_NAME    AS "Фамилия",

PR_BIRTHDAY AS "Дата рождения"

FROM PERSON

WHERE   PR_BIRTHDAY > '1.01.1970'

ORDER BY   PR_BIRTHDAY, PR_NAME

Листинг 8. Список всех рожденных после 1.01.1970

Имя	Отчество	Фамилия	Дата рождения
Татьяна	Геннадьевна	Зырянова	22.01.1970
Максим	Владимирович	Бычков	08.09.1970
Юлий	Альбертович	Алексеев	19.09.1974
Татьяна	Сергеевна	Бартош	02.12.1974
Александр	Анатольевич	Лебедев	03.04.1977
Татьяна	Владимировна	Алтынцева	03.09.1977
Оксана	Владимировна	Мельчакова	06.07.1978
Татьяна	Михайловна	Мосина	30.06.1979
Татьяна	Юрьевна	Шарапова	14.04.1980
Лариса	Вилльевна	Вашкарина	05.11.1980
Анна	Николаевна	Верхоланцева	06.10.1991

Объединим оба предыдущих условия, т.е. найдем всех Татьян рожденных после даты “1.01.1970”:

SELECT PR_NAME2  AS "Имя",

PR_NAME3  AS "Отчество",

PR_NAME    AS "Фамилия",

PR_BIRTHDAY AS "Дата рождения"

FROM PERSON

WHERE (PR_NAME2 = ' Татьяна')  AND   (PR_BIRTHDAY > '1.01.1970')

ORDER BY PR_BIRTHDAY, PR_NAME

Листинг 9. Список Татьян рожденных после 1.01.1970

Имя	Отчество	Фамилия	Дата рождения
Татьяна	Геннадьевна	Зырянова	22.01.1970
Татьяна	Сергеевна	Бартош	02.12.1974
Татьяна	Владимировна	Алтынцева	03.09.1977
Татьяна	Михайловна	Мосина	30.06.1979
Татьяна	Юрьевна	Шарапова	14.04.1980

Использование варианта BETWEEN

Близок к операторам сравнения вариант BETWEEN, который, выглядит следующим образом:

<значение> [NOT] BETWEEN <значение> AND <значение>

Это применимо как к числовым, так и к строковым столбцам, дате, времени — ко всем, за исключением BLOB.

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

Еще раз обратимся к полному списку личностей. Выберем из этого списка только тех, чья дата рождения находится в диапазоне от 1.01.1960 до  31.12.1969:

SELECT PR_NAME2 AS "Имя",

PR_NAME3  AS "Отчество",

PR_NAME    AS "Фамилия",

PR_BIRTHDAY AS "Дата рождения"

FROM   PERSON

WHERE  PR_BIRTHDAY BETWEEN '1.01.1960' AND '31.12.1969'

ORDER BY PR_BIRTHDAY, PR_NAME

Листинг 10. Список рожденных в диапазоне от 1.01.1960 до  31.12.1969

Имя	Отчество	Фамилия	Дата рождения
Татьяна	Викторовна	Владысик	19.10.1960
Светлана	Александровна	Ветошкина	23.12.1960
Татьяна	Александровна	Звездина	01.10.1961
Татьяна	Фёдоровна	Леушина	04.05.1963
Олег	Владимирович	Лядов	07.06.1964
Оксана	Васильевна	Власова	11.02.1965
Надежда	Александровна	Зеленина	11.12.1965
Татьяна	Евгеньевна	Беспалова	21.12.1965
Татьяна	Александровна	Соснина	05.12.1968
Татьяна	Александровна	Мазеина	06.04.1969

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

SELECT PR_NAME2  AS "Имя",

PR_NAME3  AS "Отчество",

PR_NAME    AS "Фамилия",

PR_BIRTHDAY AS "Дата рождения"

FROM PERSON

WHERE  PR_BIRTHDAY  >= '1.01.1960' AND  PR_BIRTHDAY  <= '31.12.1969'

ORDER BY PR_BIRTHDAY, PR_NAME

Результат будет такой же, но вариант запроса с использованием BЕTWEEN намного более выразительный.

ЗАМЕЧАНИЕ

Логическое выражение в предложении WHERE нашего примера содержит только операторы конъюнкции (логическое И). По этой причине я здесь не использую скобок. Вообще же очень рекомендую всегда использовать скобки для явного задания порядка выполнения любых операторов, в особенности логических, потому что не часто на лету, когда в поте лица своего пишешь гениальную программу (при обычном отставании от утвержденного графика), вспомнишь порядок выполнения этих операций. Я вот только помню, что конъюнкция выполняется прежде дизъюнкции. Скобки совершенно необходимы, когда у вас довольно сложное логическое выражение. Иначе выяснение причин, почему неверно осуществляется выборка данных, может затянуться на длительное время.

Есть еще один неприятный момент для любителей языка Delphi, который раньше назывался Объектный Паскаль. Там при использовании операций сравнения и логических операций порядок выполнения действий отличается от того, что принято в языке SQL. В этом языке и сами операции сравнения следует заключать в скобки, чтобы избежать ошибок трансляции.

Проверка на присутствие в списке значений (вариант IN)

В варианте IN вы можете задать список, среди элементов которого должно (или не должно) находиться значение указанного столбца:

<значение> [NOT] IN ({<значение> [, <значение>] ... | <список выбора>})

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

Найдем из нашего списка всех Юль, Юлиев и Юлиан:

SELECT PR_NAME2  AS "Имя",

PR_NAME3  AS "Отчество",

PR_NAME    AS "Фамилия",

PR_BIRTHDAY AS "Дата рождения"

FROM PERSON 

WHERE PR_NAME2  IN ('Юлий','Юлия','Юлиана')

 

Листинг 11. Список Юль, Юлиев и Юлиан

Имя	Отчество	Фамилия	Дата рождения
Юлий	Альбертович	Алексеев	19.09.1974
Юлия	Геннадьевна	Проскурякова	29.09.1974
Юлия	Игоревна	Шатрова	03.04.1958
Юлия	Владимировна	Янышева
Юлия	Дмитриевна	Гаевская	07.01.1959
Юлиана	Валерьевна	Коршунова	02.03.1950
Юлия	Валерьевна	Кудинова	29.05.1981
Юлия	Владимировна	Лаврова	08.10.1949
Юлия	Анатольевна	Луковникова	06.12.1979
Юлия	Владимировна	Мясникова	01.09.1961
Юлия	Витальевна	Оборина	02.10.1984

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

SELECT PR_NAME2  AS "Имя",

PR_NAME3  AS "Отчество",

PR_NAME    AS "Фамилия",

PR_BIRTHDAY AS "Дата рождения"

FROM PERSON

WHERE PR_NAME2 = 'Юлий'  OR

PR_NAME2 = 'Юлия'  OR

PR_NAME2 = 'Юлиана'

Проверка на пустое значение

Если столбцы ваших таблиц, входящих в условие поиска могут иметь пустое значение, то вам следует проявлять бдительность, задавая проверку их значений на NULL. Всегда следует перед выполнением "нормальных" проверок уточнить, не является ли значение пустым. Мы можем использовать два варианта:

<значение> IS NULL

и

<значение> IS NOT NULL

Еще раз повторю, это действительно может стать источником больших ошибок — перед обычной, нормальной, проверкой проверяйте значения на NULL.

Поиск в строковых столбцах

Для строковых значений используются варианты LIKE, CONTAINING и STARTING WITH. Напомню синтаксис:

...

 | <значение> [NOT] LIKE <значение>

 | <значение> [NOT] CONTAINING <значение>

 | <значение> [NOT] STARTING [WITH] <значение>

...

Вариант LIKE

В варианте LIKE строковое значение должно содержать указанные символы.

В этом варианте можно использовать шаблонные символы: процент (%) означает любое, в том числе и нулевое количество любых символов, знак подчеркивания (_) означает ровно один любой символ. LIKE является чувствительным к регистру, т. е. различает строчные и прописные буквы. На самом деле это неприятное ограничение можно очень легко и безболезненно обойти, применяя для имени столбца, используемого в выражении, функцию UPPER, которая возвращает все буквенные данные в верхнем регистре в любом, допустимом для набора используемых символов, алфавите.

Выберем строки из таблицы PERSON,  введя условие, по которому фамилия должна заканчиваться на "ОВ". Для этого перед буквами "ОВ" используем шаблонный символ %.

SELECT PR_NAME2  AS "Имя",

PR_NAME3  AS "Отчество",

PR_NAME    AS "Фамилия"

FROM PERSON

WHERE  PR_NAME  LIKE  '%ов'

Листинг 12. Выборка людей в варианте LIKE '%в'

Имя	Отчество	Фамилия
Максим	Владимирович	Бычков
Олег	Владимирович	Лядов
Евгений	Андреевнич	Болотов
Олег	Сергеевич	Феофилактов
Явар	Гейдар	Джафаров
Александр	Михайлович	Жданов
Алексей	Викторович	Лобанов
Сергей	Анатольевич	Лубов
Алексей	Олегович	Львов
Станислав	Витальевич	Приданников
Сергей	Анатольевич	Ременников
Эдуард	Владимирович	Седов
Олег	Николаевич	Фролов
Александр	Александрович	Чудинов
Николай	Викторович	Шатров
Руслан	Рафаилович	Амерзянов
Константин	Игоревич	Баталов
Сергей	Валерьевич	Бутаков
Василий	Александрович	Глазов
Дмитрий	Федорович	Глумов
Михаил	Валерьевич	Ермаков
Максим	Владимирович	Захаров
Илья	Борисович	Карманов
Владимир	Николаевич	Карпов
Вячеслав	Валерьевич	Красногоров
Станислав	Евгеньевич	Кузнецов
Олег	Александрович	Куликов
Максим	Николаевич	Кучеров
Владимир	Николаевич	Лобанов
Дмитрий	Алексеевич	Назаров
Дмитрий	Аресентьевич	Наумов
Эдуард	Булатович	Нигаметзянов
Алексей	Викторович	Спешков
Петр	Сергеевич	Теплоухов

Расширим условие поиска, выполним:

SELECT PR_NAME2  AS "Имя",

PR_NAME3  AS "Отчество",

PR_NAME    AS "Фамилия"

FROM PERSON

WHERE  UPPER(PR_NAME)  LIKE  '%ов%'

Мы добавили еще знак процента в условие: LIKE '%ов%'. То есть не только до, но и после символов "ов" может располагаться любое, в том числе и нулевое, количество любых символов. Теперь мы также получаем и женские фамилии.

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

SELECT PR_NAME2  AS "Имя",

PR_NAME3  AS "Отчество",

PR_NAME    AS "Фамилия"

FROM PERSON

WHERE  UPPER(PR_NAME)  LIKE '%ов%'

Теперь покажем как воспользоваться знаком подчеркивания (_) . Найдем всех людей у которых  “ов”  начинается со второго символа:

SELECT PR_NAME2  AS "Имя",

PR_NAME3  AS "Отчество",

PR_NAME    AS "Фамилия"

FROM PERSON

WHERE  UPPER(PR_NAME)  LIKE ‘_ов%'

В результате получим

Листинг 13. Список людей, у которых в фамилии после первого символа следует 'ов' (выборка в варианте LIKE '_ов%')

Имя	Отчество	Фамилия
Вера	Валерьевна	Новикова
Марина	Рафаиловна	Новикова
Ольга	Викторовна	Новосёлова

Использование логических операций в условиях поиска

Вот последние четыре строки в описании синтаксиса предложения WHERE:

...

 | (<условия поиска>)

 | NOT <условия поиска>

 | <условия поиска> OR <условия поиска>

 | <условия поиска> AND <условия поиска>

Это означает, что любую правильную часть условия можно заключать в круглые скобки и что можно строить сколь угодно сложные логические условия, используя операции отрицания (NOT), дизъюнкции (OR) и конъюнкции (AND).

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

В математической логике существует два замечательных закона де Моргана1, которые позволяют выполнить преобразование отрицания конъюнкции и отрицания дизъюнкции. Законы простые:

NOT (A AND B) = NOT (A) OR NOT (B)

NOT (A OR B) = NOT (A) AND NOT (B)

То есть отрицание конъюнкции двух высказываний (в нашем случае — условий, которые так же, как и высказывания, возвращают истинностное значение: "истина" или "ложь") равно дизъюнкции отрицаний этих высказываний. Отрицание дизъюнкции равно конъюнкции отрицаний. Если вы это хорошо запомните, то у вас не будет головной боли при выполнении преобразований условий.

ЗАМЕЧАНИЕ

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

Рассмотрим пример. Чуть раньше мы отображали список тех, чья дата рождения находится в диапазоне от 1.01.1960 до  31.12.1969. Мы использовали следующий оператор:

SELECT PR_NAME2  AS "Имя",

PR_NAME3  AS "Отчество",

PR_NAME  AS "Фамилия",

PR_BIRTHDAY AS "Дата рождения"

FROM PERSON

WHERE  PR_BIRTHDAY  >= '1.01.1960' AND  PR_BIRTHDAY  <= '31.12.1969'

Если же нам нужен список людей с датами рождения, не входящими в этот диапазон (то есть находящимися в точности за пределами этого диапазона), то мы должны выполнить отрицание выражения:

PR_BIRTHDAY  >= '1.01.1960’ AND  PR_BIRTHDAY  <= ‘31.12.1969’

В соответствии с законом де Моргана мы получаем оператор:

SELECT PR_NAME2  AS "Имя",

PR_NAME3  AS "Отчество",

PR_NAME  AS "Фамилия",

PR_BIRTHDAY AS "Дата рождения"

FROM PERSON

WHERE  (PR_BIRTHDAY < '1.01.1960' OR  PR_BIRTHDAY  > '31.12.1969')

Здесь нам нужно выполнить отрицание нашего выражения, задающего условия для окладов сотрудников. Отрицанием для операции сравнения >= будет < (или !>= или ^>=), для операции <= отрицанием будет > (!<=, ^ <=). Конъюнкцию мы меняем на дизъюнкцию.

Если кому-то вдруг по непонятной причине не очень нравится использование фактов из математической логики, то в данном случае легко можно использовать вариант BETWEEN, что мы и делали раньше:

PR_BIRTHDAY BETWEEN '1.01.1960’ AND ‘31.12.1969’

для задания первоначального диапазона и

PR_BIRTHDAY NOT  BETWEEN '1.01.1960’ AND ‘31.12.1969’

для дат рождения за пределами этого диапазона. Применение закона де Моргана в этом случае выполнит сам сервер базы данных.

Порядок выполнения логических операций

Сам порядок выполнения логических операций на самом деле очень прост:

1. Выполняются действия в скобках.

2. Арифметические операции умножения и деления.

3. Арифметические операции сложения и вычитания.

4. Отрицание (NOT).

5. Конъюнкция (AND).

6. Дизъюнкция (OR).

Операции с одинаковым приоритетом выполняются слева направо.

Преобразование данных при выборке

В ряде случаев при выборке данных из базы с ними необходимо произвести некоторые преобразования. Функция CAST обеспечивает преобразование данных к явно указанному типу:

CAST (<val> AS <datatype>), где <val> - преобразуемое выражение, <datatype> - явно указываемый тип данных.

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

В некоторых случаях преобразование типов является обязательным, например при использовании объединения запросов – UNION (смотри соответствующий раздел).

Обобщение Данных с помощью Агрегатных Функций

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

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

Агрегатные функции используются подобно именам полей в предложении SELECT запроса, но с одним исключением, они берут имена поля как аргументы. Только числовые поля могут использоваться с SUM и AVG. COUNT, MAX, и MIN, могут использоваться и числовые или символьные поля. Когда они используются с символьными полями, MAX и MIN будут транслировать их в эквивалент ASCII, который должен сообщать, что MIN будет означать первое, а MAX последнее значение в алфавитном порядке. Выполним, например, следующий оператор, который отыскивает человека с "максимальной" фамилией, т. е. с фамилией, являющейся последней в упорядоченном по алфавиту списке фамилий:

SELECT  MAX( PR_NAME)  AS "Фамилия"   FROM PERSON

Мы получим фамилию "Янышева"

Аналогичным образом работает и функция MIN, в смысле с точностью до наоборот. Можете это также проверить.

Применим MIN к числовому полю, найдем минимальный тариф на билеты:

SELECT MIN(TR_COST) FROM  TARIFF

Получим минимальную стоимость  билета.

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

Использование DISTINCT с COUNT

Функция COUNT несколько отличается от всех. Она считает число значений в данном столбце, или число строк в таблице. Когда она считает значения столбца, она используется с DISTINCT чтобы производить счет чисел различных значений в данном поле. Мы могли бы использовать ее, например, чтобы сосчитать количество всех имен в таблице PERSON.

SELECT     COUNT ( DISTINCT PR_NAME2) FROM PERSON

В результате получаем :

COUNT

63

если бы мы не использовали DISTINCT а ввели:

SELECT     COUNT (PR_NAME2) FROM PERSON

мы бы получили количество всех имен с учетом повторов

COUNT

63

COUNT примененная к значениям столбца к не может подсчитать значения NULL, если мы пожелаем подсчитать количество дней рождения  в таблице PERSON то получим количество значений только с ненулевыми значениями. Введем 

SELECT     COUNT ( PR_BIRTHDAY) FROM PERSON

В результате получаем :

COUNT

104

т.е. количество людей у которых  известна дата рождения.

Теперь применим COUNT для подсчета числа строк, в этом случае DISTINCT не используется. Найдем количество всех Татьян

SELECT  COUNT(*)  FROM PERSON

WHERE PR_NAME2 = 'Татьяна'

получаем

COUNT

16

Предложение GROUP BY

Предложение GROUP BY позволяет вам определять подмножество значений в особом поле в терминах другого поля, и применять функцию агрегата к подмножеству. Это дает вам возможность объединять поля и  агрегатные функции в едином предложении SELECT. Группировка является обязательной, если в операторе SELECT вы используете и агрегатные функции, и обычные столбцы. При этом группировка должна выполняться по всем неагрегатным столбцам.

Существует два основных правила группировки.

•  Каждый столбец, включенный в неагрегатный список оператора SELECT, должен появиться в предложении GROUP BY.
•  Оператор SELECT может содержать только одно предложение GROUP BY.

Это важнейшие правила группировки. Мы должны их помнить и использовать в любом нашем операторе, выполняющем группировку. Иначе получим ошибку, борьба с которой может занять слишком много нашего драгоценного времени

Предложение GROUP BY располагается после предложения WHERE и перед предложением ORDER BY.

Например, предположим что вы хотите найти сколько каких имен содержится в нашем списке, при этом упорядочивая вывод по найденному количеству (по направлению от большего к меньшему), если количество будет повторяться, отсортируем список по имени:

SELECT     COUNT (*),  PR_NAME2

FROM PERSON

GROUP BY PR_NAME2

ORDER BY 1  DESC, PR_NAME2

Получим следующий список, упорядоченный по результатам расчета функции COUNT

Листинг 14.Количество каждого имени в списке людей

COUNT	PR_NAME2
23	Елена
20	Наталья
16	Татьяна
15	Светлана
12	Марина
11	Ирина
11	Оксана
11	Ольга
10	Екатерина
9	Юлия
7	Сергей
6	Александр
6	Анна
5	Алексей
5	Дмитрий
5	Надежда
5	Олег
4	Вера
4	Людмила
4	Мария
3	Владимир
3	Ксения
3	Лариса
3	Максим
2	Анастасия
2	Вячеслав
2	Евгений
2	Илья
2	Любовь
2	Михаил
2	Семен
2	Станислав
2	Эдуард
1	Алевтина
1	Алмаз
1	Альбина
1	Амина
1	Ангелина
1	Антонина
1	Валентина
1	Валерий
1	Василий
1	Виктория
1	Галина
1	Гульнара
1	Евгения
1	Кирил
1	Константин
1	Лиана
1	Лилия
1	Марианна
1	Назгуль
1	Николай
1	Нина
1	Павел
1	Петр
1	Руслан
1	Тарас
1	Эльвира
1	Эмма
1	Юлиана
1	Юлий
1	Явар

Предложение HAVING

Предположим, что в предыдущем примере, вы хотели бы увидеть только те имена, которые повторяются более двух раз. Вы не сможете использовать агрегатную функцию в предложении WHERE  потому что агрегатные функции находятся после того как предложении WHERE уже отработало.

Предложение HAVING определяет критерии используемые чтобы удалять определенные группы из вывода, точно также как предложение WHERE делает это для индивидуальных строк. Для GROUP BY ключевое слово HAVING играет ту же роль, что и WHERE для ORDER BY. Другими словами, WHERE задает условия для значений из выбранных столбцов, а HAVING задает условия для групп, создаваемых с помощью GROUP BY.

Ключевое слово HAVING в операторе SELECT должно следовать за выражением ключевого слова GROUP BY и тоже предшествовать ключевому слову ORDER BY, если последнее используется. Синтаксис:

[GROUP BY <список столбцов> [HAVING <условия поиска>]]

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

При выполнении такого оператора SELECT делается все то же самое, что и при обычном SELECT — выбираются указанные столбцы строк, соответствующие условию в предложении WHERE, вычисляются значения агрегатных функций. После этого выполняется группировка по столбцам, перечисленным в предложении GROUP BY. Затем осуществляется дополнительная "фильтрация" строк на основании условия в предложении HAVING.

Найдем сколько каких имен содержится в нашем списке показывая только те имена, которые повторяются более двух раз:

SELECT     COUNT (*) AS  "Количество" ,  PR_NAME2

FROM PERSON

GROUP BY PR_NAME2

HAVING COUNT (*)  > 2

ORDER BY 1  DESC , PR_NAME2

Листинг 15. Количество каждого имени в списке людей при условии что это количество превышает 2.

Количество	PR_NAME2
23	Елена
20	Наталья
16	Татьяна
15	Светлана
12	Марина
11	Ирина
11	Оксана
11	Ольга
10	Екатерина
9	Юлия
7	Сергей
6	Александр
6	Анна
5	Алексей
5	Дмитрий
5	Надежда
5	Олег
4	Вера
4	Людмила
4	Мария
3	Владимир
3	Ксения
3	Лариса
3	Максим

Мы получили все те данные, которые нам были нужны.

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

1. Отбираются те строки, которые соответствуют условию поиска в предложении WHERE, если таковое имеется. В нашем примере это предложение отсутствует.

2. Выбранные строки объединяются в группы на основании значения имени (PR_NAME2).

3. Для каждой группы рассчитываются значения агрегатных функций. При этом для каждой группы создается одна результирующая строка.

4. Полученные в результате строки упорядочиваются в соответствии с предложением ORDER BY (в нашем случае — по количеству строк в группе).

Соединение таблиц

Вы помните, что в процессе нормализации таблиц вам часто приходилось разделять таблицы на несколько взаимосвязанных таблиц, чтобы устранить избыточность данных. Тогда же я успокоил вас, сказав, что таблицы снова можно будет объединить с помощью операции соединения. Это действительно так.

Соединение таблиц в операторе SELECT является одним из наиболее мощных и элегантных средств реляционных баз данных.

Существует небольшое количество вариантов соединений, JOIN. Соединения задаются в списке имен таблиц:

FROM <ссылка на таблицу> [, <ссылка на таблицу>]...

Здесь не очень удачно названная мною конструкция <ссылка на таблицу> имеет приблизительно такой синтаксис:

<ссылка на таблицу> ::= { <соединяемая таблица> |

 <имя таблицы> |

 <имя представления> |

 <имя процедуры> [(<значение> [,<значение>] ...)]}

[<псевдоним>]

Мы в этом предложении пока только использовали вариант указания имени одной-единственной таблицы. Сейчас начнем рассматривать соединяемые таблицы.

<соединяемая таблица> ::= <ссылка на таблицу> <тип соединения>

 <ссылка на таблицу> ON <условия поиска> | (<ссылка на таблицу>)

<тип соединения> ::= [INNER] JOIN

 | {LEFT | RIGHT | FULL} OUTER JOIN

Есть внешние (OUTER) левые, правые и полные соединения, а есть внутренние (INNER) соединения. Если вид соединения не указывается, то по умолчанию предполагается внутреннее соединение.

Рассмотрим на примерах использование всех видов соединений

Внутренние соединения (связывание по равенству) 

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

В случае полного внешнего соединения выбираются все соответствующие условию в предложении WHERE строки как левой, так и правой таблиц. Затем между ними устанавливается соответствие, заданное в предложении ON.

Свяжем две таблицы, покажем все аэропорты с указанием города, где аэропорт расположен:

SELECT  *  FROM AIRPORT

INNER    JOIN  CITY   ON  AIRPORT.AP_CT_CODE = CITY.CT_CODE

Листинг 16. Результат внутреннего объединения таблиц AIROPORT и CITY - список аэропортов с указанием города расположения

AP_CODE	AP_NAME	AP_CT_CODE	CT_CODE	CT_NAME
1	Домодедово	1	1	Москва
2	Внуково	1	1	Москва
3	Шереметьево	1	1	Москва
4	Быково	1	1	Москва
5	Пулково	2	2	С. Петербург
6	Савино	3	3	Пермь
7	Кольцово	4	4	Екатеренбург

Данные извлекаются из таблиц AIRPORT и CITY, поскольку требуемые данные размещаются частично в одной, а частично в другой таблице. Условие соединения задается после ключевого слова ON. "Главной" таблицей здесь, в левом соединении, является таблица AIRPORT. К каждой выбранной строке этой таблицы присоединяются данные из таблицы CITY, которые удовлетворяют условию в предложении ON. В нашем случае требуется равенство значения столбца AP_CT_CODE из таблицы AIRPORT, который является внешним ключом, значению столбца CT_CODE из таблицы CITY.

Перепишем запрос, для более осмысленного вывода:

SELECT  CITY.CT_NAME    AS "Город",

AIRPORT.AP_NAME  AS "Аэропорт"

FROM AIRPORT INNER     JOIN  CITY  

ON  AIRPORT.AP_CT_CODE = CITY.CT_CODE

ORDER BY   CITY.CT_NAME, AIRPORT.AP_NAME

При создании достаточно сложных операторов бывает утомительным набирать перед именами столбцов полные имена таблиц, особенно если эти имена достаточно длинные. По этой причине существует возможность задавать псевдонимы (или алиасы, alias) для имен таблиц. Сравните следующий оператор:

SELECT  C.CT_NAME AS    "Город",

A.AP_NAME  AS    "Аэропорт"

FROM AIRPORT  A    INNER     JOIN  CITY  C

ON  A.AP_CT_CODE = C.CT_CODE

ORDER BY   C.CT_NAME, A.AP_NAME

В предложении FROM мы для таблицы AIRPORT задали псевдоним A, а для таблицы CITY — псевдоним C. Эти псевдонимы мы используем в списке выбора и в условии соединения.

Можно также использовать предложение WHERE. Например, мы можем получить название всех аэропортов находящихся в городе «Москва»

SELECT C.CT_NAME  AS    "Город",

A.AP_NAME AS    "Аэропорт"

FROM  AIRPORT  A    INNER     JOIN  CITY  C

ON  A.AP_CT_CODE = C.CT_CODE

WHERE C.CT_NAME  = 'Москва'

ORDER BY    A.AP_NAME

Город	Аэропорт
Москва	Быково
Москва	Внуково
Москва	Домодедово
Москва	Шереметьево

Внешние соединения

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

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

Таблицу в соединении будем называть левой, если она стоит перед  ключевым словом JOIN (слева), и правой, если она стоит после от него (справа).

Левое внешнее соединение

LEFT (OUTER) - тип соединения "левое (внешнее)". Левое соединение таблиц включает в себя все строки из левой таблицы и те строки из правой таблицы, для которых выполняется условие соединения. Для строк из левой таблицы, для которых не найдено соответствия в правой, в столбцы, извлекаемые из правой таблицы, заносятся значения NULL.

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

SELECT C.CT_NAME  AS    "Город",

A.AP_NAME  AS    "Аэропорт"

FROM AIRPORT  A    LEFT OUTER    JOIN CITY  C

   ON  A.AP_CT_CODE = C.CT_CODE

ORDER BY   C.CT_NAME, A.AP_NAME

Получаем:

Листинг 17. Результат левого внешнего объединения таблиц AIROPORT и CITY - список всех аэропортов с указанием города в том случае, если аэропорт располагается в городе.

Город	Аэропорт
Екатиренбург	Кольцово
Москва	Быково
Москва	Внуково
Москва	Домодедово
Москва	Шереметьево
Пермь	Савино
С. Петербург	Пулково
NULL	Аэропорт Н-ской ВЧ

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

NULL	Аэропорт Н-ской ВЧ

т.е. мы видим аэропорт, для которого код города не указан (этот аэропорт не находится ни в одном из известных городов).

Продемонстрированное  левое внешнее соединение позволяет получить список аэропортов находящихся вне города, для этого необходимо отобрать только те строки, где поля из таблицы CITY будут содержать пустые значения (NULL).  Отбираем эти строки при помощи предложения WHERE.

SELECT  C.CT_NAME AS    "Город",

A.AP_NAME  AS    "Аэропорт"

FROM AIRPORT  A     LEFT OUTER    JOIN CITY  C

ON  A.AP_CT_CODE = C.CT_CODE

WHERE  C.CT_CODE  IS  NULL

Получаем только одну строку

Город	Аэропорт
NULL	Аэропорт Н-ской ВЧ

Правое внешнее соединение

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

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

Для иллюстрации этой зеркальности выполните оператор:

SELECT  C.CT_NAME  AS    "Город",

A.AP_NAME AS    "Аэропорт"

FROM AIRPORT  A RIGHT  OUTER    JOIN CITY  C

ON  A.AP_CT_CODE = C.CT_CODE

ORDER BY   C.CT_NAME, A.AP_NAME

Теперь мы получим список из всех городов, если в городе имеется аэропорт, он будет представлен в соответствующем столбце, если аэропорта нет – столбец будет  содержать пустые значения (NULL):

Листинг 18. Результат правого внешнего объединения таблиц AIROPORT и CITY - список всех городов с указанием аэропортов в том случае, если в этом городе есть аэропорты.

Город	Аэропорт
Екатиренбург	Кольцово
Москва	Быково
Москва	Внуково
Москва	Домодедово
Москва	Шереметьево
Пермь	Савино
С. Петербург	Пулково
Чайковский	NULL

В получившейся список попала  строка

Чайковский

NULL

мы получили город Чайковский в котором в настоящее уже нет аэропорта.

Полное внешнее соединение

FULL (OUTER) - тип соединения "полное (внешнее)". Это комбинация левого и правого соединений. В полное соединение включаются все строки из обеих таблиц. Для совпадающих строк поля заполняются реальными значениями, для несовпадающих строк поля заполняются в соответствии с правилами левого и правого соединений. На практике этот тип соединения требуется чрезвычайно редко. Поэтому примеров мы приводить не будем.

Более сложные примеры соединений

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

Рассмотрим пример двойного внешнего соединения, т. е. тот случай, когда к первой таблице присоединяется не одна, а уже две таблицы.

Чтобы увидеть фамилии с именами и отчествами всех членов экипажа с указанием их роли, номера авиалинии и даты вылета нужно выполнить два соединения таблицы EQUIPAGE с таблицей PERSON и с таблицей RANK.

Введите и выполните оператор:

SELECT E.EQ_FL_DATE AS "Дата вылета",

E.EQ_FL_NUM AS "Авиалиния",

P.PR_NAME2  || ' ' || P.PR_NAME3 || ' ' || P.PR_NAME AS "ФИО",

R.RNK_NAME AS "Роль"

FROM EQUIPAGE E  INNER JOIN  PERSON  P 

ON  E.EQ_PR_CODE = P.PR_CODE INNER JOIN RANK R

ON   E.EQ_RNK_CODE = R.RNK_CODE

WHERE E.EQ_FL_DATE BETWEEN  '1.02.08' AND '29.02.08'

ORDER BY  E.EQ_FL_DATE,  E.EQ_FL_NUM, 3

Вначале отбираются все члены экипажа из таблицы EQUIPAGE (здесь она становится главной) на основании условий предложения WHERE (все рейсы за февраль 2008). В выбранные строки добавляются полные имена из таблицы PERSON. Условием соединения является равенство кода в таблице PERSON коду человека в таблице EQUIPAGE. После чего к этим строкам добавляются названия роли из таблицы RANK. Здесь условием соединения является равенство кода, первичного ключа таблицы RANK коду роли в начальной, "главной", таблице EQUIPAGE. Результат сортируем по дате вылета, номеру авиалинии и фамилиям именам и отчествам (так как поле ФИО вычисляется, в списке сортировки указываем номер поля).

В результате выполнения двойного соединения мы получим

Листинг 19. Двойное внешнее соединение – список всех членов экипажа с указанием их роли, номера авиалинии и даты вылета

Дата вылета	Авиалиния	ФИО	Роль
13.02.2008	901	Анна Валерьевна Грунтович	Борт проводник
13.02.2008	901	Евгений Андреевнич Болотов	Командир
13.02.2008	901	Людмила Александровна Блинова	Борт проводник
13.02.2008	901	Марина Владимировна Волченко	Борт проводник
13.02.2008	901	Наталья Николаевна Васюкова	Борт проводник
13.02.2008	901	Павел Павлович Попутько	Пилот
14.02.2008	901	Анна Евгеньевна Елизарова	Борт проводник
14.02.2008	901	Вера Раисовна Дударева	Борт проводник
14.02.2008	901	Екатерина Викторовна Еремеева	Борт проводник
14.02.2008	901	Елена Ильинична Жукова	Борт проводник
14.02.2008	901	Семен Валерьевич Дудин	Командир
14.02.2008	901	Юлий Альбертович Алексеев	Пилот
15.02.2008	901	Анна Валерьевна Грунтович	Борт проводник
15.02.2008	901	Евгений Андреевнич Болотов	Командир
15.02.2008	901	Елена Анатольевна Мичурина	Борт проводник
15.02.2008	901	Марина Владимировна Волченко	Борт проводник
15.02.2008	901	Сергей Иванович Ридель	Пилот
15.02.2008	901	Татьяна Александровна Соснина	Борт проводник

Необходимо отметить, что порядок указания соединяемых таблиц не имеет значения. Здесь важно задать первую, "главную", таблицу сразу после ключевого слова FROM, все остальные соединяемые таблицы "подключаются" к строкам, выбранным именно из этой таблицы, и ее строки выбираются на основании условия в предложении WHERE.

Для иллюстрации этого изменим предыдущий оператор SELECT, поменяв местами соединяемые таблицы. Выполните оператор:

SELECT E.EQ_FL_DATE AS "Дата вылета",

E.EQ_FL_NUM AS "Авиалиния",

P.PR_NAME2  || ' ' || P.PR_NAME3 || ' ' || P.PR_NAME AS "ФИО",

R.RNK_NAME AS "Роль"

FROM PERSON  P INNER JOIN  EQUIPAGE  E  

ON  E.EQ_PR_CODE = P.PR_CODE INNER JOIN RANK R

ON   E.EQ_RNK_CODE = R.RNK_CODE

WHERE E.EQ_FL_DATE BETWEEN  '1.02.08' AND '29.02.08'

ORDER BY  E.EQ_FL_DATE,  E.EQ_FL_NUM, 3

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

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

SELECT T.TR_AL_NUM AS "Номер Авиалинии",

      ATO.AP_NAME AS "Аэропорт прилета",

      S.SL_NAME AS "Салон",

      T.TR_COST AS "Стоимость"

FROM  TARIFF T

  INNER JOIN AIRPORT AFROM ON (T.TR_AP_FROM = AFROM.AP_CODE)

  INNER JOIN AIRPORT ATO ON (T.TR_AP_TO = ATO.AP_CODE)

  INNER JOIN SALON S ON (T.TR_SL_TYPE = S.SL_TYPE)

  INNER JOIN CITY C  ON (AFROM.AP_CT_CODE = C.CT_CODE)

WHERE C.CT_NAME = 'Пермь'

Таким образом, в запросе будут задействованы следующие таблицы:  TARIFF, AIRPORT,  SALON,  CITY. Обратите внимание, что таблица AIRPORT используется в запросе два раза – один раз мы используем соединение используя поле TR_AP_TO таблицы TARIFF,  таблица в этом соединении обозначается псевдонимом ATO и означает аэропорт прилета, второй раз в соединении используется поле TR_AP_FROM, в этом случае таблица AIRPORT будет обозначаться псевдонимом AFROM и означает аэропорт вылета.

Следующий пример потребует объединения шести таблиц. Получим список (фамилию, имя, отчество) тех кто имеет билеты из Перми в Москву на 11.01.2008, кроме фамилии имени отчества выведем вид салона, стоимость билета и занимаемое место. В запросе мы должны связать следующие таблицы: PERSON связываем с таблицей TICKET, TICKET – с TARIFF, TARIFF – с SALON, далее TARIFF дважды связываем а AIRPORT (первый раз связываем используя поле TR_AP_FROM, т.е. определяя аэропорт вылета, второй раз связываем используя поле TR_AP_TO, тем самым будет определен аэропорт прилета), AIRPORT дважды связывается с CITY (один раз для города, где размещен аэропорт вылета, второй раз связь будет определять город, где размещен аэропорт прилета). Так как таблицы AIRPORT и CITY используются два раза, для них используются различные синонимы.

Приводим вид запроса:

SELECT PR_NAME2 AS "Имя",

PR_NAME3 AS "Отчество",

PR_NAME AS "Фамилия",

S.SL_NAME AS "Салон",

TC.TC_SEAT AS "Место",

TR.TR_COST AS "Стоимость"

FROM PERSON  P

INNER  JOIN TICKET  TC ON TC.TC_PR_CODE = P.PR_CODE

INNER  JOIN TARIFF  TR ON TR.TR_CODE = TC.TC_TR_CODE

INNER JOIN SALON S ON TR.TR_SL_TYPE = S.SL_TYPE

INNER  JOIN AIRPORT AFROM  ON   AFROM.AP_CODE = TR.TR_AP_FROM

INNER  JOIN CITY   CFROM ON  CFROM.CT_CODE = AFROM.AP_CT_CODE

INNER  JOIN AIRPORT ATO  ON   ATO.AP_CODE = TR.TR_AP_TO

INNER  JOIN CITY   CTO ON  CTO.CT_CODE = ATO.AP_CT_CODE

WHERE CFROM.CT_NAME = 'Пермь'  AND  CTO.CT_NAME = 'Москва'

AND TC.TC_FL_DATE = '11.01.2008'

Получаем список:

Листинг 20. Список купивших билеты из Перми в Москву на 11.01.2008, объединение шести таблиц.

Имя	Отчество	Фамилия	Салон	Место	Стоимость
Ирина	Валентиновна	Щекина	Бизнес	4A	8800
Константин	Игоревич	Баталов	Бизнес	4Б	8800
Ольга	Юрьевна	Боброва	Бизнес	4Г	8800
Елена	Павловна	Бельтюкова	Бизнес	5А	8800
Светлана	Леонидовна	Верушкина	Бизнес	5В	8800
Александр	Александрович	Чудинов	Бизнес	5Г	8800
Сергей	Владимирович	Богатырев	Бизнес	6А	8800
Владимир	Николаевич	Карпов	Бизнес	3Б	8800
Татьяна	Викторовна	Владысик	Бизнес	3Г	8800
Наталья	Васильевна	Вожакова	Бизнес	2А	8800
Владимир	Юрьевич	Войтович	Бизнес	2Б	8800
Елена	Александровна	Воронюк	Эконом	20А	3500
Мария	Ивановна	Выгузова	Эконом	20Б	3500
Ольга	Николаевна	Вяткина	Эконом	20В	3500
Юлия	Дмитриевна	Гаевская	Эконом	20Г	3500
Василий	Александрович	Глазов	Эконом	20Д	3500
Дмитрий	Федорович	Глумов	Эконом	21А	3500

Последний пример демонстрирует использование соединения таблиц в сочетании с группировкой. Найдем суммарную стоимость проданных за 2008 год билетов для каждой из авиакомпаний. Для того чтобы найти то что нам требуется нужно просуммировать стоимость билета из таблицы TARIFF для каждого проданного (за указанный год) билета. В список вывода нужно поместить наименование авиакомпании и сумму стоимостей из таблицы TARIFF. Результат должен быть сгруппирован по наименованиям авиакомпаний. Для наглядности упорядочим выводимые строки по величине суммы.

Введем запрос:

SELECT AC.AC_NAME    AS  “Авиакомпания “,

SUM( TRF.TR_COST )  AS   “Стоимость”

FROM TARIFF  TRF

  INNER JOIN TICKET TC ON (TRF.TR_CODE = TC.TC_TR_CODE)

  INNER JOIN AIRLINE AL ON (TRF.TR_AL_NUM = AL.AL_NUM)

  INNER JOIN AIRCOMPANY AC ON (AL.AL_AC_CODE = AC.AC_CODE)

GROUP BY AC.AC_NAME

ORDER BY  2

В результате получим список:

Листинг 21. Результат объединения и группировки, суммарная стоимость проданных за 2008 год билетов для каждой из авиакомпаний.

Авиакомпания	Стоимость
S7 Airlines	42100
Lufthansa	57800
Сибавиатранс	59420
Ютэйр-экспресс	80300
Sky express	19690
Аэрофлот-РА	126800

Рефлексивное соединение, или самосоединение

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

Найдем всех однофамильцев, т.е. выведем фамилии (с именами и отчествами) и даты рождения из таблицы PERSON если существует соединение с этой же самой таблицей. Соединяем по фамилии, чтобы все строки не соединились сами с собой нужно указать, что первичные ключи (PR_CODE) для двух соединенных строк не совпадают.

SELECT DISTINCT

P2.PR_NAME  || ' ' || P2.PR_NAME2  || ' ' || P2.PR_NAME3 AS "ФИО", P2.PR_BIRTHDAY AS "Дата рождения"

FROM PERSON  P2   INNER JOIN PERSON P1 ON

(P2.PR_NAME = P1.PR_NAME) AND (P2.PR_CODE <> P1.PR_CODE)

ORDER BY 1

Если в исходной таблице есть больше чем два человек с одной фамилией мы получим дублирование строк, поэтому используем DISTINCT для исключения дубликатов. Результат упорядочиваем по вычисляемому полю ФИО, чтобы сразу увидеть группы однофамильцев.

Листинг 22. Результат самообъединения  таблицы  PERSON с самой собой - список всех однофамильцев.

ФИО	Дата рождения
Блинова Людмила Александровна	28.08.1957
Блинова Ольга Владимировна
Блинова Светлана Александровна	05.05.1980
Васильева Марианна Юрьевна
Васильева Наталья Васильевна	09.11.1980
Зеленина Екатерина Владимировна	16.12.1977
Зеленина Надежда Александровна	11.12.1965
Зеленина Наталья Александровна	23.09.1990
Зеленина Ольга Викторовна	30.04.1958
Култышева Лиана Валентиновна	22.01.1970
Култышева Наталья Сергеевна
Лобанов Алексей Викторович	18.11.1951
Лобанов Владимир Николаевич
Лядова Надежда Вячеславовна
Лядова Наталья Викторовна	12.01.1973
Некрасова Елена Павловна	08.09.1970
Некрасова Татьяна Николаевна	04.12.1949
Новикова Вера Валерьевна	09.03.1947
Новикова Марина Рафаиловна	30.11.1975
Рожкова Анастасия Сергеевна
Рожкова Оксана Юрьевна
Степанова Ирина Борисовна
Степанова Наталья Алексеевна	09.11.1969

Обратите внимание на то, что в данном случае мы обязаны для таблиц указывать псевдонимы, чтобы точно указывать в операторе, к какой именно таблице относится тот или иной столбец. В нашем случае используются псевдонимы P1, P2. Имея два псевдонима, таблицу можно рассматривать как две разные таблицы.

Использование подзапросов в операторах SQL 

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

При составлении подзапросов необходимо придерживаться следующих правил.

•  Подзапрос необходимо заключить в круглые скобки.
•  Ключевое слово ORDER BY использовать в подзапросе нельзя, хотя в главном запросе ORDER BY использоваться может. Вместо ORDER BY в подзапросе можно использовать GROUP BY.
•  Подзапрос, возвращающий несколько строк данных, можно использовать только в операторах, допускающих множество значений, например в IN.
•  Операцию BETWEEN по отношению к подзапросу использовать нельзя, но ее можно использовать в самом подзапросе

Выбор одного

"Выбор одного" — это оператор SELECT, который возвращает ровно одно значение одного столбца из любой таблицы нашей базы данных или ни одного значения. Использование этого вида подзапроса (который также называют вложенным запросом) может заменить внутреннее объединение.

Получим название всех аэропортов находящихся в городе «Москва» (такой запрос мы уже делали когда рассматривали внутреннее объединение, теперь используем подзапрос)

SELECT  AP_NAME AS    "Аэропорт"

FROM AIRPORT 

WHERE AP_CT_CODE =

(SELECT CT_CODE FROM CITY WHERE  CT_NAME  = 'Москва')

ORDER BY    AP_NAME

Результат будет точно таким же как и в случае объединения таблиц CITY и AIRPORT. Этот запрос выполняется так – сначала подзапрос находит код города «Москва», а затем внешний запрос отбирает все аэропорты у которых AP_CT_CODE равен найденному значению.

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

Использование в подзапросе агрегатных функций

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

Рассмотрим следующий пример. Пусть мы собираемся получить информацию по самым дешевым билетам – нам нужно получить номер маршрута, название аэропорта вылета и прилета, вид салона и саму стоимость билета. Выполнение этого запроса разбивается на два этапа. Сначала во внутреннем  запросе при помощи агрегатной функции MIN нужно найти минимальную стоимость билета из таблицы TARIFF, после этого получаем данные по билетам, стоимость которых  уже найдена во внутреннем запросе. Получаем следующий запрос:

SELECT T.TR_AL_NUM AS "Номер Авиалинии",

      AFROM.AP_NAME AS "Аэропорт вылета",

      ATO.AP_NAME AS "Аэропорт прилета",

      S.SL_NAME AS "Салон",

      T.TR_COST AS "Стоимость"

FROM  TARIFF T

   INNER JOIN AIRPORT AFROM ON (T.TR_AP_FROM = AFROM.AP_CODE)

   INNER JOIN AIRPORT ATO ON (T.TR_AP_TO = ATO.AP_CODE)

   INNER JOIN SALON S ON (T.TR_SL_TYPE = S.SL_TYPE)

WHERE T.TR_COST = (SELECT MIN(TR_COST) FROM TARIFF)

Связанные подзапросы 

Связанный подзапрос – это подзапрос, зависящий от информации, предоставляемой главным запросом.

В следующем примере в подзапросе определение связи между таблицами TARIFF во внутреннем подзапросе и AIRPORT использует псевдоним таблицы AFROM, определенный в главном запросе. Изменим условия предыдущего запроса – найдем номер авиалинии, аэропорт вылета, вид салона и стоимость самого дешевого билета для рейсов вылетающих из города «Пермь», т.е. от предыдущего запроса новый отличатся тем, что мы ищем только по тарифам связанным с аэропортами вылета находящимися в городе «Пермь». Для того чтобы это сделать нужно во внешний запрос добавить объединение с таблицей CITY  и наложить условие (предложение WHERE) по названию города, во внутреннем запросе нужно искать минимум не из всех возможных строк таблицы TARIFF, а только из тех, у которых код аэропорта вылета (TR_AP_FROM) совпадает с допустимыми аэропортами.

Получаем следующий запрос:

SELECT T.TR_AL_NUM AS "Номер Авиалинии",

      AFROM.AP_NAME AS "Аэропорт вылета",

      ATO.AP_NAME AS "Аэропорт прилета",

      S.SL_NAME AS "Салон",

      T.TR_COST AS "Стоимость"

FROM  TARIFF T

  INNER JOIN AIRPORT AFROM ON (T.TR_AP_FROM = AFROM.AP_CODE)

  INNER JOIN AIRPORT ATO ON (T.TR_AP_TO = ATO.AP_CODE)

  INNER JOIN SALON S ON (T.TR_SL_TYPE = S.SL_TYPE)

  INNER JOIN CITY C  ON (AFROM.AP_CT_CODE = C.CT_CODE)

WHERE (C.CT_NAME = 'Пермь')

  AND (T.TR_COST = (SELECT MIN(TR_COST) FROM TARIFF

                      WHERE TR_AP_FROM = AFROM.AP_CODE))

Другим примером может быть использование вложенного подзапроса с функцией COUNT. Найдем фамилию, имя, отчество членов экипажа совершивших в феврале 2008 года более одного полета, иными словами тех, для кого в таблице  EQUIPAGE содержится более одной записи датируемой февралем.  

SELECT PR_NAME2  AS "Имя",

PR_NAME3  AS "Отчество",

PR_NAME    AS "Фамилия"

FROM PERSON    

WHERE ( SELECT COUNT(*) FROM EQUIPAGE 

WHERE  EQ_FL_DATE BETWEEN '1.02.08' AND '29.02.08'

AND EQ_PR_CODE = PERSON.PR_CODE )  >  1

Самый внутренний оператор SELECT подсчитывает количество строк исходной таблицы EQUIPAGE попадающих на февраль, имеющих одно и то же значение кода человека. Предложение WHERE во внешнем запросе задает для выборки только те строки, где это количество больше единицы. Мы ссылаемся на коды людей во внутреннем операторе SELECT и на коды людей в операторе SELECT более высокого уровня.

В результате получаем результат:

Листинг 23. Список членов экипажа совершивших в феврале 2008 года более одного полета

Имя	Отчество	Фамилия
Имя	Отчество	Фамилия
Евгений	Андреевнич	Болотов
Марина	Владимировна	Волченко
Анна	Валерьевна	Грунтович

ЗАМЕЧАНИЕ

Точно такого же результата можно было добиться построив  запрос с использованием группировки по колонкам PR_NAME2 ,  PR_NAME3 , PR_NAME и применив HAVING  COUNT(*) >1

Следующий запрос проиллюстрирует другой пример использования функции COUNT в подзапросе (в этом случае использование подзапроса является единственным способом получения результата). Итак получим фамилию, имя, отчество пассажиров, которые приобрели более одного билета в течении 2008 года, также выведем номер авиалинии и дату вылета из билета. Внутренний подзапрос подсчитает количество билетов за 2008 год для каждой личности, которые  извлекаются внешним запросом. При этом внешний запрос выбирает только те строки, где количество найденное внутреннем запросом будет больше одного (иными словами у этой личности больше одного билета), а также производит внутреннее объединение таблиц PERSON и TICKET.  . Внутренний и внешний запрос связаны при помощи приравнивания кода личности из внешнего запроса и кода личности указанного в билете из внутреннего запроса. Для того чтобы данные на билеты приобретенные одним пассажиром выводились вместе, результат упорядочиваем по фамилии и имени.

Получаем запрос: 

SELECT P.PR_NAME2  AS "Имя",

P.PR_NAME3  AS "Отчество",

P.PR_NAME       AS "Фамилия",

T.TC_FL_NUM AS "Номер авиалинии",

T.TC_FL_DATE AS  "Дата вылета"

FROM PERSON P

INNER  JOIN TICKET  T ON  T.TC_PR_CODE = P.PR_CODE

WHERE T.TC_FL_DATE     BETWEEN '1.01.08'  AND '31.12.08'

AND (SELECT COUNT(*)  FROM TICKET

WHERE (TC_PR_CODE = P.PR_CODE)

AND (TC_FL_DATE BETWEEN '1.01.08'  AND '31.12.08')) > 1

ORDER  BY P.PR_NAME, P.PR_NAME2

Другой способ получить этот же результат – это воспользоваться вариантом IN. Как правило, связные подзапросы можно заменить на вариант IN, внутри которого находится подзапрос.

Для получения результата внутренний подзапрос должен найти коды всех пассажиров имеющих более одного билета в течении одного года, для этого нужно получить код личности (TC_TR_CODE) из таблицы TICKET  сгруппировав результат по этому коду. Для выделения тех  кодов, которые встречаются более одного раза, используем  HAVING  COUNT(*) >1. Внешний запрос выведет фамилии, имена, отчества, номера авиалинии и даты вылета для людей, коды которых были найдены во внутреннем запросе.

Запрос будет иметь вид: 

SELECT P.PR_NAME2  AS "Имя",

P.PR_NAME3  AS "Отчество",

P.PR_NAME    AS "Фамилия",

T.TC_FL_NUM AS "Номер авиалинии",

T.TC_FL_DATE AS  "Дата вылета"

FROM PERSON P

INNER  JOIN TICKET  T ON  T.TC_PR_CODE = P.PR_CODE

WHERE T.TC_FL_DATE  BETWEEN '1.01.08'  AND '31.12.08'

AND T.TC_PR_CODE IN  

(SELECT TC_PR_CODE FROM TICKET 

WHERE TC_FL_DATE BETWEEN '1.01.08'  AND '31.12.08'

GROUP  BY TC_PR_CODE HAVING COUNT(*) > 1)

ORDER  BY P.PR_NAME, P.PR_NAME2

В результате выполнения запроса получим:

Листинг 24. Список  пассажиров, которые приобрели более одного билета в течении 2008 года, также выводится номер авиалинии и дата вылета из билета. (с использованием GROUP BY и HAVING в подзапросе)

Имя	Отчество	Фамилия	Номер авиалинии	Дата вылета
Константин	Игоревич	Баталов	124	08.01.2008
Константин	Игоревич	Баталов	125	11.01.2008
Мария	Ивановна	Выгузова	124	08.01.2008
Мария	Ивановна	Выгузова	125	11.01.2008
Василий	Александрович	Глазов	124	08.01.2008
Василий	Александрович	Глазов	125	11.01.2008

Использование оператора EXISTS

EXISTS переводится как «существует» - это оператор, который возвращает логическое выражение (истина или ложь). Он берет подзапрос как аргумент и получает «истина», если тот производит любой вывод  или «ложь», если тот не делает этого. Иными словами  функция EXISTS возвращает значение «истина», если подзапрос  возвращает хотя бы одну строку и  «ложь», если подзапрос не возвращает ни одной строки. Функция EXISTS используется в условии WHERE.

Так как внутренний оператор SELECT содержит произвольное количество столбцов, вместо списка   столбцов обычно используют символ * (звездочка), что означает, что выбираются все столбцы.

В качестве примера выведем фамилию, имя, отчество членов экипажа летавших в феврале 2008 года (если в таблице EQUIPAGE  существует строка для выбранной личности с  атрибутом EQ_FL_DATE попадающим в февраль 2008 года).

SELECT PR_NAME2  AS "Имя",

PR_NAME3  AS "Отчество",

PR_NAME    AS "Фамилия"

FROM PERSON 

WHERE  EXISTS( SELECT * FROM EQUIPAGE E

   WHERE E.EQ_FL_DATE BETWEEN '1.02.08' AND '29.02.08'

AND E.EQ_PR_CODE = PERSON.PR_CODE )

Для каждой строки-кандидата внешнего запроса (представляющей личность, проверяемую в настоящее время), внутренний запрос находит строки, которые относятся к этой личности (совпадение E.EQ_PR_CODE = PERSON.PR_CODE) и проверяет попадание даты вылета (EQ_FL_DATE) в заданный интервал.

Альтернативным способом получения такого же результата будет использование связывания таблиц PERSON и EQUIPAGE и исключения дубликатов при помощи ключевого слова DISTINCT.

Использование варианта IN с подзапросами

Еще одним способом решить поставленную задачу является использование варианта IN. То есть во внутреннем запросе нужно из таблицы EQUIPAGE найти коды личностей (EQ_PR_CODE), даты вылета которых (EQ_FL_DATE) попадают в заданный интервал. Во внешнем запросе выводим фамилию, имя, отчество из таблицы PERSON коды которых, попадают в найденный перечень.

SELECT PR_NAME2  AS "Имя",

PR_NAME3  AS "Отчество",

PR_NAME    AS "Фамилия"

FROM PERSON

WHERE PR_CODE IN (SELECT EQ_PR_CODE FROM EQUIPAGE

                WHERE EQ_FL_DATE BETWEEN '1.02.08' AND '29.02.08' )

Использование   NOT EXISTS

Наиболее часто вместе  с EXISTS используется оператор отрицания NOT. Если в условии нужно, чтобы строк запроса не существовало, использование подзапроса зачастую является единственной возможностью.

Получим список членов экипажа, которые совершали полет в январе 2008 года, и при этом не разу не летали в феврале. Приведем первый вариант с использованием NOT EXISTS:

SELECT DISTINCT  P.PR_NAME2  AS "Имя",

P.PR_NAME3  AS "Отчество",

P.PR_NAME    AS "Фамилия"

FROM PERSON  P 

INNER JOIN   EQUIPAGE E1 ON E1.EQ_PR_CODE = P.PR_CODE

WHERE    (E1.EQ_FL_DATE BETWEEN '1.01.08' AND '31.01.08')

AND NOT EXISTS

( SELECT * FROM EQUIPAGE E2

 WHERE E2.EQ_FL_DATE BETWEEN '1.02.08' AND '29.02.08'

 AND E2.EQ_PR_CODE = P.PR_CODE )

Разберем полученный запрос – внешний запрос выводит фамилию, имя, отчество для всех членов экипажа которые совершали полет в январе 2008 года, ключевое слово DISTINCT нужно, чтобы даже те члены экипажа, которые летали неоднократно, выводились только один раз. Во внешнем запросе используются псевдонимы таблиц P для PERSON  и E1 для EQUIPAGE. В предложении WHERE соединяем два условия, первое ограничивает выводимый список членов экипажа теми, которые совершали полет в январе. Второе условие требует, чтобы текущая личность не совершала полет в течении февраля, т.е. чтобы за указанный период для этой личности не существовало строки в таблице EQUIPAGE. Во внешнем запросе для таблицы EQUIPAGE используется псевдоним Е2.

Приведем вариант этого запроса с использованием NOT IN.

SELECT DISTINCT  P.PR_NAME2  AS "Имя",

P.PR_NAME3  AS "Отчество",

P.PR_NAME    AS "Фамилия"

FROM PERSON  P 

INNER JOIN   EQUIPAGE E1 ON E1.EQ_PR_CODE = P.PR_CODE

WHERE    (E1.EQ_FL_DATE BETWEEN '1.01.08' AND '31.01.08')

AND  (NOT P.PR_CODE IN

( SELECT EQ_PR_CODE FROM EQUIPAGE

    WHERE EQ_FL_DATE BETWEEN '1.02.08' AND '29.02.08' ))

Здесь мы делаем следующее – во внутреннем подзапросе находим  коды всех личностей из таблицы EQUIPAGE, которые совершали полет в феврале, внешний запрос выводит фамилию, имя, отчество для тех кто летал в январе 2008 и при этом среди них нет тех, кто содержится в результате вывода подзапроса (т.е. те кто летал в феврале).

Объединение запросов

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

 Команда UNION 

Команда UNION используется для объединения результатов двух или более операторов SELECT с исключением повторяющихся строк. Другими словами, если строка попадает в вывод одного запроса, то второй раз она не выводится, даже если она возвращается вторым запросом. При использовании UNION в каждом из связываемых операторов SELECT должно быть выбрано одинаковое число столбцов, столбцы должны быть одинакового типа и следовать в том же порядке.

В составных запросах можно использовать ключевое слово ORDER BY. Однако ключевое слово ORDER BY в них можно использовать только для упорядочения результатов окончательного вывода обоих запросов. Поэтому в составном запросе допускается использовать только одно выражение с ключевым словом ORDER BY, хотя сам составной запрос может состоять из нескольких операторов SELECT На столбцы в выражении ключевого слова ORDER BY можно ссылаться как по псевдонимам, так и по их номерам в списке выбора

Допустим нам нужно найти всех прилетевших из Перми в Москву за определенное число. Человек может прилететь в качестве члена экипажа (если коды этой личности присутствует в таблице EQUIPAGE на заданное число), или в качестве пассажира (если у него есть билет на заданное число). Таким образом, требуется объединить два запроса, первый найдет фамилию, имя, отчество для всех членов экипажа прилетевших из Перми в Москву за 11.01.2008, а второй выведет фамилию, имя, отчество тех кто имеет билеты из Перми в Москву на это же число (такой запрос мы уже делали, когда исследовали соединения таблиц). Для того чтобы отличать пассажира от члена экипажа сделаем следующее, добавим в результат новое поле, где для первого запроса будет во всех строках содержаться один и тот же текст — «Член экипажа», второй запрос разместит в этом поле текст «Пассажир». Это дополнительное поле озаглавим как «Тип». Кроме этого добавим  еще одно поле, в которое будем выводить дополнительную информацию, для запроса по членам экипажа будем выводить роль для данного члена, а для пассажиров номер места указанный в билете. Соответственно это поле мы так и озаглавим «Роль/Место».

Обратим внимание, что вновь введенные поля в двух запросах не совпадают по типу. Для того чтобы можно было использовать команду UNION необходимо привести типы этих полей во втором запросе к тому же типу, что и в первом. Преобразование данных к требуемому типу обеспечивает функция CAST. Для поля «Тип» должно быть сделано преобразование к типу CHAR(12), а для поля «Роль/Место» к типу VARCHAR(20).

Получаем запрос:

SELECT     P.PR_NAME2     AS "Имя",

 P.PR_NAME3    AS "Отчество",

     P.PR_NAME      AS "Фамилия",

     'Член экипажа'  AS "Тип",

     R.RNK_NAME   AS "Роль/Место"

FROM PERSON P

INNER  JOIN   EQUIPAGE  EQ ON  EQ.EQ_PR_CODE = P.PR_CODE

INNER  JOIN RANK R  ON  R.RNK_CODE = EQ.EQ_RNK_CODE

INNER  JOIN AIRLINE AL ON    AL.AL_NUM = EQ.EQ_FL_NUM

INNER  JOIN AIRPORT AFROM  ON   AFROM.AP_CODE = AL.AL_AP_FROM

INNER  JOIN CITY   CFROM ON  CFROM.CT_CODE = AFROM.AP_CT_CODE

INNER  JOIN AIRPORT ATO  ON   ATO.AP_CODE = AL.AL_AP_TO

INNER  JOIN CITY   CTO ON  CTO.CT_CODE = ATO.AP_CT_CODE

WHERE CFROM.CT_NAME = 'Пермь'  AND  CTO.CT_NAME = 'Москва'

  AND EQ.EQ_FL_DATE = '11.01.2008'

UNION

SELECT    P.PR_NAME2, P.PR_NAME3, P.PR_NAME,

    CAST('Пассажир' AS  CHAR(12)),

    CAST(TC.TC_SEAT  AS  VARCHAR(20))

FROM PERSON  P

INNER  JOIN TICKET  TC ON TC.TC_PR_CODE = P.PR_CODE

INNER  JOIN TARIFF  TR ON TR.TR_CODE = TC.TC_TR_CODE

INNER  JOIN AIRPORT AFROM  ON   AFROM.AP_CODE = TR.TR_AP_FROM

INNER  JOIN CITY   CFROM ON  CFROM.CT_CODE = AFROM.AP_CT_CODE

INNER  JOIN AIRPORT ATO  ON   ATO.AP_CODE = TR.TR_AP_TO

INNER  JOIN CITY   CTO ON  CTO.CT_CODE = ATO.AP_CT_CODE

WHERE CFROM.CT_NAME = 'Пермь'  AND  CTO.CT_NAME = 'Москва'

 AND TC.TC_FL_DATE = '11.01.2008'

В результате выполнения запроса получаем список:

Листинг 25. Результат объединения запросов (команды UNION) – список всех прилетевших из Перми в Москву 11.01.2008

Имя	Отчество	Фамилия	Тип	Роль/Место
Александр	Александрович	Чудинов	Пассажир	5Г
Владимир	Николаевич	Карпов	Пассажир	3Б
Владимир	Юрьевич	Войтович	Пассажир	2Б
Елена	Павловна	Бельтюкова	Пассажир	5А
Ирина	Валентиновна	Щекина	Пассажир	4A
Константин	Игоревич	Баталов	Пассажир	4Б
Лариса	Вилльевна	Вашкарина	Член экипажа	Борт проводник
Максим	Владимирович	Бычков	Член экипажа	Командир
Наталья	Васильевна	Вожакова	Пассажир	2А
Олег	Владимирович	Лядов	Член экипажа	Пилот
Ольга	Юрьевна	Боброва	Пассажир	4Г
Светлана	Александровна	Ветошкина	Член экипажа	Борт проводник
Светлана	Леонидовна	Верушкина	Пассажир	5В
Сергей	Владимирович	Богатырев	Пассажир	6А
Татьяна	Викторовна	Владысик	Пассажир	3Г
Елена	Александровна	Воронюк	Пассажир	20А
Мария	Ивановна	Выгузова	Пассажир	20Б
Ольга	Николаевна	Вяткина	Пассажир	20В
Юлия	Дмитриевна	Гаевская	Пассажир	20Г
Василий	Александрович	Глазов	Пассажир	20Д
Дмитрий	Федорович	Глумов	Пассажир	21А

Добавление данных. Команда INSERT

Команда INSERT предназначена для добавления данных в базу. С ее помощью можно добавить в указанную таблицу или представление одну или сразу несколько строк.

Добавление отдельной строки

При добавлении отдельной строки в списке INTO перечисляются столбцы, в которые вводятся значения, а сами значения задаются в списке VALUES. Вместо списка столбцов можно указать символ "*"; в этом случае предполагается, что вводятся значения всех столбцов таблицы в специфицированном при создании таблицы порядке. Поскольку порядок столбцов может изменяться, то такая конструкция является потенциально опасной и лучше ее не применять.

Рассмотрим добавление строки в таблицу CITY.

INSERT INTO CITY (CT_CODE, CT_NAME) VALUES (11, 'Челябинск');

В данном случае вводимые значения задавались как константы, но вместо них могут стоять и любые допустимые в SQL выражения, возвращающие единственное значение, в том числе и конструкции SELECT. Покажем как добавить новый аэропорт для города 'Челябинск', при этом код города получим как результат запроса:

INSERT INTO AIRPORT (AP_CODE, AP_NAME, AP_CT_CODE)

VALUES (17, 'Чикменево',

(SELECT CT_CODE FROM CITY WHERE CT_NAME = 'Челябинск'))

Добавление группы строк

Одной командой INSERT можно добавить в таблицу и несколько строк. Перечень добавляемых строк в этом случае задается конструкцией SELECT.

В качестве примера рассмотрим добавление в таблицу EQUIPAGE нескольких строк – назначим на рейс авиалинии (AIRLINE) № 901 от '16.02.2008'  тех же самых бортпроводников, которые были назначены  '14.02.2008'. Для этого нам потребуется при помощи запроса  получить коды бортпроводников входивших в экипаж выполнявший 14 февраля 2008г.  рейс на линии № 901.

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

INSERT INTO EQUIPAGE 

(EQ_PR_CODE, EQ_FL_DATE, EQ_RNK_CODE,  EQ_FL_NUM)

SELECT  EQ_PR_CODE, '16.02.2008',  EQ_RNK_CODE,  '901'

FROM EQUIPAGE EQ INNER JOIN RANK R 

ON R.RNK_CODE = EQ.EQ_RNK_CODE

WHERE  R.RNK_NAME  = 'Бортпроводник'  

AND EQ.EQ_FL_NUM = '901' AND  EQ.EQ_FL_DATE = '14.02.2008'

В результате выполнения внутреннего SELECT находим коды личностей членов экипажа (EQ_PR_CODE) и код (EQ_RNK_CODE) соответствующий коду бортпроводника, поля «Дата вылета» (EQ_FL_DATE) и   «Номер маршрута» (EQ_FL_NUM) будут заданы как константы. Таким образом, команда INSERT вставит в таблицу EQUIPAGE столько строк, сколько возвращает внутренний запрос.

Если студент был внимателен, он должен был заметить что в списке столбцов добавляемых в таблицу  EQUIPAGE отсутствует первичный ключ – код члена экипажа (EQ_ CODE). В самом деле, мы не можем вставлять его для набора строк, так как для каждой строки он должен быть уникален. Мы можем выполнить приведенный запрос благодаря использованию триггеров, о чем будет рассказано в дальнейшем.

Обновление уже имеющихся данных. Команда UPDATE

Уже существующие в таблице данные можно изменить с помощью команды UPDATE. Команда UPDATE не добавляет новых записей в таблицу и не удаляет их, а только дает возможность изменить данные. С помощью одной такой команды можно изменить данные только одной таблицы, но одновременно можно менять данные нескольких столбцов. Одним таким оператором можно изменить и одну строку данных и целый набор строк.

Рассмотрим синтаксис подходящего оператора.

UPDATE имя_таблицы

SET столбец! = 'значение'

[, столбецЗ = 'значение']

[, столбецЗ = 'значение'] [WHERE условие];

Обратите внимание на использование ключевого слова SET' оно одно, а описаний столбцов — несколько. Описания столбцов разделяются запятыми. К этому моменту вы, должно быть, уже почувствовали логику SQL. В операторах SQL запятая обычно используется для разделения различного типа аргументов.

Изменим фамилию у одной из женщин из таблицы PERSON, запрос на изменение будет иметь вид:

UPDATE PERSON  SET  PR_NAME = 'Смирнова'

WHERE   PR_NAME ='Оборина' 

AND  PR_NAME2 = 'Юлия' 

AND  PR_NAME3 = 'Витальевна' 

AND PR_BIRTHDAY = '02.10.1984'

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

Удаление данных из таблиц. Команда DELETE 

Для удаления данных из таблиц используется команда DELETE. Команда DELETE предназначена не для того, чтобы удалять значения отдельных столбцов, а для того, чтобы удалять целые записи. Оператор DELETE следует применять с осторожностью — слишком уж безотказно он работает.

Чтобы удалить одну или несколько записей из таблицы, используйте следующий синтаксис оператора DELETE.

DELETE FROM имя_таблицы 

[WHERE условие];

Если ключевое слово WHERE в операторе DELETE опущено, будут удалены все строки таблицы. Поэтому практически всегда необходимо использовать ключевое слово WHERE в операторе DELETE.

В качестве примера удалим все сведенья о билетах с датой вылета ранее 1 января 2000 года:

DELETE FROM TICKET

WHERE  TC_FL_DATE < '01.01.2000'

При использовании команды DELETE бывает полезно использование вложенных запросов SELECT. Удалим из таблицы PERSON тех людей кто ни разу не летал в качестве пассажира или члена экипажа. Чтобы выполнить это, нужно найти коды личностей всех членов экипажа (из таблицы EQUIPAGE) и всех пассажиров с билетами (из таблицы TICKET). Получаем запрос на удаление:

DELETE FROM PERSON

WHERE NOT PR_CODE  IN

(SELECT EQ_PR_CODE FROM  EQUIPAGE)

AND NOT PR_CODE IN

(SELECT TC_PR_CODE  FROM   TICKET)

Здесь используются два вложенных запроса. Первый возвращает коды личностей членов экипажа из таблицы EQUIPAGE, а второй   коды личностей из  таблицы TICKET. В результате будут удалены все строки из таблицы PERSON коды личностей которых не попадают в результат вывода каждого из этих двух запросов.

Генераторы и их использование

При заполнении таблиц с суррогатным  первичным ключом нам приходится вручную проставлять его значение, например - 1, 2, 3..., 10, 20, 30, и т.п. Это в частности, не дает возможности добавить в таблицу несколько строк одной командой INSERT, при помощи конструкции SELECT, как это было показано в предшествующем разделе.

Для решения этой проблемы большинство SQL-серверов имеет специальные механизмы для создания уникальных идентификаторов. В Interbase и Firebird для этого существует механизм генераторов. Генераторы предназначены для получения последовательностей уникальных чисел. (1, 2, 3.. и т.д.). Например, в таблице аэропортов для их нумерации введен столбец AP_CODE, по которому построен первичный ключ. Столбец можно заполнять значениями генератора.

Нужно сразу заметить, что сами по себе генераторы не обеспечивают сохранение последовательности номеров в случае удаления записей - генератор всего лишь выдает числа по очереди увеличивая их на некоторую величину и обеспечивая уникальность выданных значений. То есть, генератор выглядит как переменная типа Integer , которая находится в памяти, и над которой можно выполнять операции Inc и Dec (прибавления и убавления).

Создание генераторов

Генератор - это специальный объект базы данных, который генерирует уникальные последовательные числа. Эти числа могут быть использованы в качестве идентификаторов (например, код аэропорта, код личности и т.п.). Для создания генератора необходимо использовать оператор:

CREATE GENERATOR  generatorname;

Как правило, генераторы, предназначаемые для заполнения поля будем называть по имени таблицы с прибавлением вначале строки «GEN_», а в конце «_ID». (Именно такое соглашение по именам использует IBExpert) Например, запрос на создание генератора для таблицы  AIRPORT (ключевого поля AP_CODE)  будет иметь вид

CREATE GENERATOR GEN_AIRPORT_ID

После создания генератора его значения можно получать при помощи функции

GEN_ID(generatorname, inc_value)

где inc_value - число, на которое необходимо прирастить значение а

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

Генераторы возвращают значения (и сохраняют свои значения на диске) вне контекста транзакции пользователя. Это означает, что если значение генератора было увеличено с 10 до 11 (инкремент 1), то даже при откате транзакции (ROLLBACK) значение генератора, выданное в этой транзакции, не вернется к предыдущему. Вместе с этим гарантируется, что каждому пользователю будет всегда возвращено уникальное значение генератора (вызов функции GEN_ID всегда происходит «монопольно», то есть непараллельно для любого числа одновременных вызовов, чтобы исключить возможность получения разными пользователями одного и того же значения).

 Использование генераторов

Работа с генератором возможна только при помощи встроенной функции GEN_ID, а значит работать с генераторами можно в запросах, процедурах и триггерах. Давайте создади