4671

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

Книга

Информатика, кибернетика и программирование

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

Русский

2012-11-24

710.5 KB

10 чел.

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

Перед изучением языка 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

Константин

Игоревич

Баталов

Бизнес

8800

Ольга

Юрьевна

Боброва

Бизнес

8800

Елена

Павловна

Бельтюкова

Бизнес

8800

Светлана

Леонидовна

Верушкина

Бизнес

8800

Александр

Александрович

Чудинов

Бизнес

8800

Сергей

Владимирович

Богатырев

Бизнес

8800

Владимир

Николаевич

Карпов

Бизнес

8800

Татьяна

Викторовна

Владысик

Бизнес

8800

Наталья

Васильевна

Вожакова

Бизнес

8800

Владимир

Юрьевич

Войтович

Бизнес

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

Имя

Отчество

Фамилия

Тип

Роль/Место

Александр

Александрович

Чудинов

Пассажир

Владимир

Николаевич

Карпов

Пассажир

Владимир

Юрьевич

Войтович

Пассажир

Елена

Павловна

Бельтюкова

Пассажир

Ирина

Валентиновна

Щекина

Пассажир

4A

Константин

Игоревич

Баталов

Пассажир

Лариса

Вилльевна

Вашкарина

Член экипажа

Борт проводник

Максим

Владимирович

Бычков

Член экипажа

Командир

Наталья

Васильевна

Вожакова

Пассажир

Олег

Владимирович

Лядов

Член экипажа

Пилот

Ольга

Юрьевна

Боброва

Пассажир

Светлана

Александровна

Ветошкина

Член экипажа

Борт проводник

Светлана

Леонидовна

Верушкина

Пассажир

Сергей

Владимирович

Богатырев

Пассажир

Татьяна

Викторовна

Владысик

Пассажир

Елена

Александровна

Воронюк

Пассажир

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 всегда происходит «монопольно», то есть