4671
Информационное обеспечение систем управления. Построение запросов при работе с базой данных
Книга
Информатика, кибернетика и программирование
Тестовая база данных Перед изучением языка SQL необходимо рассмотреть тестовую базу данных на которой будут отрабатываться все запросы. Наша тестовая база данных полностью соответствует рассмотренной в учебном пособии по нормализации данных и создан...
Русский
2012-11-24
710.5 KB
12 чел.
Перед изучением языка SQL необходимо рассмотреть тестовую базу данных на которой будут отрабатываться все запросы. Наша тестовая база данных полностью соответствует рассмотренной в учебном пособии по нормализации данных и создании базы данных. Напоминаю, что эта база данных описывает сферу воздушных перевозок. Все пояснения к модели даны в пособии по нормализации данных и создании базы данных
Для начала приведем логическую модель БД, где каждый информационный объект отображается реляционной таблицей, а связи между таблицами соответствуют связям между информационными объектами. Модель представлена в виде диаграммы
Рисунок 1. Логическая модель
Последний этап моделирования БД - переход к физическому уровню модели, на котором модель также представлена в виде диаграммы
Рисунок 2. Физическая модель
Приведем соответствие имен таблиц и полей на логическом и физическом уровне.
Вид объекта |
Название на логическом уровне |
Название на физическом уровне |
Таблица |
Авиамаршрут |
AIRLINE |
Поле |
Номер маршрута |
AL_NUM |
Поле |
Код Типа Самолета |
AL_PL_CODE |
Поле |
Код Авиакомпании |
AL_AC_CODE |
Поле |
Откуда |
AL_AP_FROM |
Поле |
Куда |
AL_AP_TO |
Таблица |
Аэропорт |
AIRPORT |
Поле |
Код аэропорта |
AP_CODE |
Поле |
Название аэропорта |
AP_NAME |
Поле |
Код Города |
AP_CT_CODE |
Таблица |
Авиакомпания |
AIRCOMPANY |
Поле |
Код Авиакомпании |
AC_CODE |
Поле |
Название Авиакомпании |
AC_NAME |
Таблица |
Борт |
BOARD |
Поле |
Борт номер |
BRD_NUM |
Поле |
Код Типа Самолета |
BRD_PL_CODE |
Поле |
Код Авиакомпании |
BRG_AC_CODE |
Таблица |
Город |
CITY |
Поле |
Код Города |
CT_CODE |
Поле |
Название Города |
CT_NAME |
Таблица |
Член Экипажа |
EQUIPAGE |
Поле |
Код Члена Экипажа |
EQ_CODE |
Поле |
Дата вылета |
EQ_FL_DATE |
Поле |
Номер маршрута |
EQ_FL_NUM |
Поле |
Код Личности |
EQ_PR_CODE |
Поле |
Код роли |
EQ_RNK_CODE |
Таблица |
Рейс |
FLIGHT |
Поле |
Дата вылета |
FL_DATE |
Поле |
Номер маршрута |
FL_NUM |
Поле |
Борт номер |
FL_BRD_NUM |
Таблица |
Личность |
PERSON |
Поле |
Код Личности |
PR_CODE |
Поле |
ФИО |
PR_NAME |
Таблица |
Тип Самолета |
PLANE |
Поле |
Код Типа Самолета |
PL_CODE |
Таблица |
Роль члена экипажа |
RANK |
Поле |
Код роли |
RNK_CODE |
Поле |
Роль |
RNK_NAME |
Таблица |
Тип Салона |
SALON |
Поле |
Код Типа Салона |
SL_TYPE |
Поле |
Название Типа Салона |
SL_NAME |
Таблица |
Салон в Самолете |
SALON_IN_PLANE |
Поле |
Код Типа Салона |
SP_SL_TYPE |
Поле |
Код Типа Самолета |
SP_PL_CODE |
Поле |
Количество мест |
SP_COUNT |
Таблица |
Расписание |
TIMETABLE |
Поле |
Код аэропорта |
TBL_AP_CODE |
Поле |
Номер маршрута |
TBL_AL_NUM |
Поле |
Время Прилета |
TBL_DOWN_TIME |
Поле |
Время Вылета |
TBL_START_TIME |
Поле |
Номер в Маршруте |
TBL_NUMBER |
Таблица |
Тариф |
TARIFF |
Поле |
Код Тарифа |
TR_CODE |
Поле |
Код Типа Салона |
TR_SL_TYPE |
Поле |
Номер маршрута |
TR_AL_NUM |
Поле |
Цена билета |
TR_COST |
Поле |
Аэропорт Откуда |
TR_AP_FROM |
Поле |
Аэропорт Куда |
TR_AP_TO |
Таблица |
Билет |
TICKET |
Поле |
Номер билета |
TC_NUM |
Поле |
Код Личности |
TC_PR_CODE |
Поле |
Код Тарифа |
TC_TR_CODE |
Поле |
Дата вылета |
TC_FL_DATE |
Поле |
Номер маршрута |
TC_FL_NUM |
В таблицы тестовой базы данных занесена информация в количестве достаточном для прослеживания основных закономерностей, конечно этих данных намного меньше чем может содержатся в реальной базе данных, посвященной воздушным перевозкам. Все примеры запросов приведенные ниже выполнялись именно на этой базе данных.
Для выборки данных из реляционной базы данных используется оператор SELECT самый мощный и самый сложный оператор в языке SQL. Его сложность заключается даже не столько в синтаксисе, хотя и он достаточно сложен, сколько в большом количестве вариантов его использования для получения различных данных из существующих таблиц базы данных. Разумеется, всех его возможностей мы рассматривать не будем. Детально исследуем лишь те его особенности, которые нам, скорее всего, пригодятся в нашей деятельности по созданию программ, работающих с базами данных.
Рассмотрим упрощенный синтаксис этого оператора.
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. В предложении перечисляются имена столбцов таблицы или порядковые номера столбцов, указанных в списке выбора. По умолчанию сортировка выполняется в возрастающем порядке, как если бы вы задали ключевое слово 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 все получается с точностью до наоборот пустые значения находятся в начале списка.
Выполните следующий оператор:
SELECT PR_NAME2 AS "Имя"
FROM PERSON
Вы получите список из имен, среди которых есть много повторяющихся.
ЗАМЕЧАНИЕ
Это, кстати, один из примеров нарушения принципов реляционной алгебры. Результатом выборки данных из таблицы базы данных (из отношения, в терминах реляционной алгебры) должно быть также отношение. Однако то, что мы получили, отношением не является, потому что содержит и одинаковые строки.
Чтобы убрать ненужные нам повторы, необходимо в оператор ввести ключевое слово DISTINCT (заодно упорядочим вывод по имени):
SELECT DISTINCT PR_NAME2 AS "Имя"
FROM PERSON
ORDER BY PR_NAME2;
Теперь мы получим список из 62 имен, среди которых не будет повторяющихся.
Листинг 6. Список всех имен (без повторов)
Имя |
Имя |
Имя |
||
Алевтина |
Екатерина |
Николай |
||
Александр |
Елена |
Нина |
||
Алексей |
Илья |
Оксана |
||
Альбина |
Ирина |
Олег |
||
Амина |
Кирил |
Ольга |
||
Анастасия |
Константин |
Павел |
||
Ангелина |
Ксения |
Петр |
||
Анна |
Лариса |
Руслан |
||
Антонина |
Лиана |
Светлана |
||
Валентина |
Лилия |
Семен |
||
Валерий |
Нина |
Сергей |
||
Василий |
Любовь |
Станислав |
||
Вера |
Людмила |
Тарас |
||
Виктория |
Максим |
Татьяна |
||
Владимир |
Марианна |
Эдуард |
||
Вячеслав |
Марина |
Эльвира |
||
Галина |
Мария |
Эмма |
||
Гульнара |
Михаил |
Юлиана |
||
Дмитрий |
Надежда |
Юлий |
||
Евгений |
Назгуль |
Юлия |
||
Евгения |
Наталья |
Явар |
Предложение WHERE позволяет задать условие, на основании которого строки таблицы будут попадать в результирующий набор данных. Строка помещается в выходной набор данных, если она удовлетворяет указанному, подчас довольно сложному, условию. При отсутствии этого предложения в выходной набор данных помещаются все строки исходной таблицы (таблиц).
Следует отдавать себе отчет, что выражение в предложении WHERE является логическим выражением, возвращающим истинностное значение TRUE, FALSE или UNKNOWN. В выборку будут попадать только те записи таблицы, для которых это выражение дает истинный результат (TRUE). Соответственно, к выражению применимы все законы исчисления высказываний. На практике чаще всего используется закон де Моргана, про который далее мы скажем несколько слов.
Надо еще помнить, что операции сравнения, в которых принимают участие пустые значения (NULL), никогда не дают истинного значения Для таких столбцов дополнительно следует также использовать проверку типа IS NULL или IS NOT NULL.
Вся мощь оператора SELECT в реляционных базах данных проявляется в первую очередь в предложении 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, который, выглядит следующим образом:
<значение> [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 вы можете задать список, среди элементов которого должно (или не должно) находиться значение указанного столбца:
<значение> [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 является чувствительным к регистру, т. е. различает строчные и прописные буквы. На самом деле это неприятное ограничение можно очень легко и безболезненно обойти, применяя для имени столбца, используемого в выражении, функцию 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 (описанное далее) не будет использовано.
Функция 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 позволяет вам определять подмножество значений в особом поле в терминах другого поля, и применять функцию агрегата к подмножеству. Это дает вам возможность объединять поля и агрегатные функции в едином предложении SELECT. Группировка является обязательной, если в операторе SELECT вы используете и агрегатные функции, и обычные столбцы. При этом группировка должна выполняться по всем неагрегатным столбцам.
Существует два основных правила группировки.
Это важнейшие правила группировки. Мы должны их помнить и использовать в любом нашем операторе, выполняющем группировку. Иначе получим ошибку, борьба с которой может занять слишком много нашего драгоценного времени
Предложение 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 |
Явар |
Предположим, что в предыдущем примере, вы хотели бы увидеть только те имена, которые повторяются более двух раз. Вы не сможете использовать агрегатную функцию в предложении WHERE потому что агрегатные функции находятся после того как предложении WHERE уже отработало.
Предложение HAVING определяет критерии используемые чтобы удалять определенные группы из вывода, точно также как предложение WHERE делает это для индивидуальных строк. Для GROUP BY ключевое слово HAVING играет ту же роль, что и WHERE для ORDER BY. Другими словами, WHERE задает условия для значений из выбранных столбцов, а HAVING задает условия для групп, создаваемых с помощью GROUP BY.
Ключевое слово HAVING в операторе SELECT должно следовать за выражением ключевого слова GROUP BY и тоже предшествовать ключевому слову ORDER BY, если последнее используется. Синтаксис:
[GROUP BY <список столбцов> [HAVING <условия поиска>]]
Основным требованием к составу предложения HAVING является то, что имена столбцов в этом предложении обязательно должны присутствовать в списке GROUP BY или быть параметрами агрегатной функции.
При выполнении такого оператора SELECT делается все то же самое, что и при обычном SELECT выбираются указанные столбцы строк, соответствующие условию в предложении WHERE, вычисляются значения агрегатных функций. После этого выполняется группировка по столбцам, перечисленным в предложении GROUP BY. Затем осуществляется дополнительная "фильтрация" строк на основании условия в предложении HAVING.
Найдем сколько каких имен содержится в нашем списке показывая только те имена, которые повторяются более двух раз:
SELECT COUNT (*) AS "Количество" , PR_NAME2
FROM PERSON
GROUP BY PR_NAME2
HAVING COUNT (*) > 2
ORDER BY 1 DESC , PR_NAME2
Листинг 15. Количество каждого имени в списке людей при условии что это количество превышает 2.
Количество |
PR_NAME2 |
23 |
Елена |
20 |
Наталья |
16 |
Татьяна |
15 |
Светлана |
12 |
Марина |
11 |
Ирина |
11 |
Оксана |
11 |
Ольга |
10 |
Екатерина |
9 |
Юлия |
7 |
Сергей |
6 |
Александр |
6 |
Анна |
5 |
Алексей |
5 |
Дмитрий |
5 |
Надежда |
5 |
Олег |
4 |
Вера |
4 |
Людмила |
4 |
Мария |
3 |
Владимир |
3 |
Ксения |
3 |
Лариса |
3 |
Максим |
Мы получили все те данные, которые нам были нужны.
В процессе обработки запроса система выполняет такие действия.
1. Отбираются те строки, которые соответствуют условию поиска в предложении WHERE, если таковое имеется. В нашем примере это предложение отсутствует.
2. Выбранные строки объединяются в группы на основании значения имени (PR_NAME2).
3. Для каждой группы рассчитываются значения агрегатных функций. При этом для каждой группы создается одна результирующая строка.
4. Полученные в результате строки упорядочиваются в соответствии с предложением ORDER BY (в нашем случае по количеству строк в группе).
Вы помните, что в процессе нормализации таблиц вам часто приходилось разделять таблицы на несколько взаимосвязанных таблиц, чтобы устранить избыточность данных. Тогда же я успокоил вас, сказав, что таблицы снова можно будет объединить с помощью операции соединения. Это действительно так.
Соединение таблиц в операторе SELECT является одним из наиболее мощных и элегантных средств реляционных баз данных.
Существует небольшое количество вариантов соединений, JOIN. Соединения задаются в списке имен таблиц:
FROM <ссылка на таблицу> [, <ссылка на таблицу>]...
Здесь не очень удачно названная мною конструкция <ссылка на таблицу> имеет приблизительно такой синтаксис:
<ссылка на таблицу> ::= { <соединяемая таблица> |
<имя таблицы> |
<имя представления> |
<имя процедуры> [(<значение> [,<значение>] ...)]}
[<псевдоним>]
Мы в этом предложении пока только использовали вариант указания имени одной-единственной таблицы. Сейчас начнем рассматривать соединяемые таблицы.
<соединяемая таблица> ::= <ссылка на таблицу> <тип соединения>
<ссылка на таблицу> ON <условия поиска> | (<ссылка на таблицу>)
<тип соединения> ::= [INNER] JOIN
| {LEFT | RIGHT | FULL} OUTER JOIN
Есть внешние (OUTER) левые, правые и полные соединения, а есть внутренние (INNER) соединения. Если вид соединения не указывается, то по умолчанию предполагается внутреннее соединение.
Рассмотрим на примерах использование всех видов соединений
Связывание по равенству используется чаще всего и является, пожалуй, самым полезным типом связывания таблиц. Связывание по равенству называют также внутренним (INNER JOIN). При связывании по равенству таблицы связываются по общим столбцам, которые в каждой из таблиц обычно являются ключевыми. Поскольку в запросе будет присутствовать более одной таблицы, мы должны использовать для столбцов уточнения, задавая перед именем столбца имя таблицы, псевдоним или алиас см. дальше. Имя таблицы отделятся от имени столбца точкой.
В случае полного внешнего соединения выбираются все соответствующие условию в предложении WHERE строки как левой, так и правой таблиц. Затем между ними устанавливается соответствие, заданное в предложении ON.
Свяжем две таблицы, покажем все аэропорты с указанием города, где аэропорт расположен:
SELECT * FROM AIRPORT
INNER JOIN CITY ON AIRPORT.AP_CT_CODE = CITY.CT_CODE
Листинг 16. Результат внутреннего объединения таблиц AIROPORT и CITY - список аэропортов с указанием города расположения
AP_CODE |
AP_NAME |
AP_CT_CODE |
CT_CODE |
CT_NAME |
1 |
Домодедово |
1 |
1 |
Москва |
2 |
Внуково |
1 |
1 |
Москва |
3 |
Шереметьево |
1 |
1 |
Москва |
4 |
Быково |
1 |
1 |
Москва |
5 |
Пулково |
2 |
2 |
С. Петербург |
6 |
Савино |
3 |
3 |
Пермь |
7 |
Кольцово |
4 |
4 |
Екатеренбург |
Данные извлекаются из таблиц AIRPORT и CITY, поскольку требуемые данные размещаются частично в одной, а частично в другой таблице. Условие соединения задается после ключевого слова ON. "Главной" таблицей здесь, в левом соединении, является таблица AIRPORT. К каждой выбранной строке этой таблицы присоединяются данные из таблицы CITY, которые удовлетворяют условию в предложении ON. В нашем случае требуется равенство значения столбца AP_CT_CODE из таблицы AIRPORT, который является внешним ключом, значению столбца CT_CODE из таблицы CITY.
Перепишем запрос, для более осмысленного вывода:
SELECT CITY.CT_NAME AS "Город",
AIRPORT.AP_NAME AS "Аэропорт"
FROM AIRPORT INNER JOIN CITY
ON AIRPORT.AP_CT_CODE = CITY.CT_CODE
ORDER BY CITY.CT_NAME, AIRPORT.AP_NAME
При создании достаточно сложных операторов бывает утомительным набирать перед именами столбцов полные имена таблиц, особенно если эти имена достаточно длинные. По этой причине существует возможность задавать псевдонимы (или алиасы, alias) для имен таблиц. Сравните следующий оператор:
SELECT C.CT_NAME AS "Город",
A.AP_NAME AS "Аэропорт"
FROM AIRPORT A INNER JOIN CITY C
ON A.AP_CT_CODE = C.CT_CODE
ORDER BY C.CT_NAME, A.AP_NAME
В предложении FROM мы для таблицы AIRPORT задали псевдоним A, а для таблицы CITY псевдоним C. Эти псевдонимы мы используем в списке выбора и в условии соединения.
Можно также использовать предложение WHERE. Например, мы можем получить название всех аэропортов находящихся в городе «Москва»
SELECT C.CT_NAME AS "Город",
A.AP_NAME AS "Аэропорт"
FROM AIRPORT A INNER JOIN CITY C
ON A.AP_CT_CODE = C.CT_CODE
WHERE C.CT_NAME = 'Москва'
ORDER BY A.AP_NAME
Город |
Аэропорт |
Москва |
Быково |
Москва |
Внуково |
Москва |
Домодедово |
Москва |
Шереметьево |
Внешнее связывание используется, когда вывод должен содержать все записи одной таблицы, даже если некоторые из ее записей не имеют соответствующих записей в другой таблице.
Пожалуй, чаще всего используются внешние соединения, которые действительно позволяют объединить разделенные в результате нормализации таблицы в единое целое.
Таблицу в соединении будем называть левой, если она стоит перед ключевым словом JOIN (слева), и правой, если она стоит после от него (справа).
LEFT (OUTER) - тип соединения "левое (внешнее)". Левое соединение таблиц включает в себя все строки из левой таблицы и те строки из правой таблицы, для которых выполняется условие соединения. Для строк из левой таблицы, для которых не найдено соответствия в правой, в столбцы, извлекаемые из правой таблицы, заносятся значения NULL.
Левое внешнее соединение чаще всего используется в наших операторах по причине его естественности. Итак найдем все аэропорты, даже если код города в котором он находится не указан.
SELECT C.CT_NAME AS "Город",
A.AP_NAME AS "Аэропорт"
FROM AIRPORT A LEFT OUTER JOIN CITY C
ON A.AP_CT_CODE = C.CT_CODE
ORDER BY C.CT_NAME, A.AP_NAME
Получаем:
Листинг 17. Результат левого внешнего объединения таблиц AIROPORT и CITY - список всех аэропортов с указанием города в том случае, если аэропорт располагается в городе.
Город |
Аэропорт |
Екатиренбург |
Кольцово |
Москва |
Быково |
Москва |
Внуково |
Москва |
Домодедово |
Москва |
Шереметьево |
Пермь |
Савино |
С. Петербург |
Пулково |
NULL |
Аэропорт Н-ской ВЧ |
Вначале отбираются строки первой, "главной", таблицы на основании условий, заданных в предложении WHERE. Затем к выбранным строкам добавляются данные из второй, присоединяемой, таблицы в соответствии с условиями соединения, заданными в предложении ON. Особенностью внешних соединений является то, что в выходной набор данных попадают и те строки, которые содержат пустые значения (NULL) в тех столбцах главной таблицы, которые присутствуют в условии соединения в предложении ON. В нашем случае в результат попала строка
NULL |
Аэропорт Н-ской ВЧ |
т.е. мы видим аэропорт, для которого код города не указан (этот аэропорт не находится ни в одном из известных городов).
Продемонстрированное левое внешнее соединение позволяет получить список аэропортов находящихся вне города, для этого необходимо отобрать только те строки, где поля из таблицы CITY будут содержать пустые значения (NULL). Отбираем эти строки при помощи предложения WHERE.
SELECT C.CT_NAME AS "Город",
A.AP_NAME AS "Аэропорт"
FROM AIRPORT A LEFT OUTER JOIN CITY C
ON A.AP_CT_CODE = C.CT_CODE
WHERE C.CT_CODE IS NULL
Получаем только одну строку
Город |
Аэропорт |
NULL |
Аэропорт Н-ской ВЧ |
RIGHT (OUTER) - тип соединения " правое (внешнее)". Правое соединение таблиц включает в себя все строки из правой таблицы и те строки из левой таблицы, для которых выполняется условие соединения. Для строк из правой таблицы, для которых не найдено соответствия в левой, в столбцы, извлекаемые из левой таблицы, заносятся значения NULL.
Правое внешнее соединение является зеркальным отражением левого внешнего соединения. При нем вначале отбираются все строки соединяемой таблицы (здесь она становится главной) на основании условий предложения WHERE, затем к ним добавляются строки второй таблицы, указанной сразу после ключевого слова FROM с учетом условий, заданных в предложении ON.
Для иллюстрации этой зеркальности выполните оператор:
SELECT C.CT_NAME AS "Город",
A.AP_NAME AS "Аэропорт"
FROM AIRPORT A RIGHT OUTER JOIN CITY C
ON A.AP_CT_CODE = C.CT_CODE
ORDER BY C.CT_NAME, A.AP_NAME
Теперь мы получим список из всех городов, если в городе имеется аэропорт, он будет представлен в соответствующем столбце, если аэропорта нет столбец будет содержать пустые значения (NULL):
Листинг 18. Результат правого внешнего объединения таблиц AIROPORT и CITY - список всех городов с указанием аэропортов в том случае, если в этом городе есть аэропорты.
Город |
Аэропорт |
Екатиренбург |
Кольцово |
Москва |
Быково |
Москва |
Внуково |
Москва |
Домодедово |
Москва |
Шереметьево |
Пермь |
Савино |
С. Петербург |
Пулково |
Чайковский |
NULL |
В получившейся список попала строка
Чайковский |
NULL |
мы получили город Чайковский в котором в настоящее уже нет аэропорта.
FULL (OUTER) - тип соединения "полное (внешнее)". Это комбинация левого и правого соединений. В полное соединение включаются все строки из обеих таблиц. Для совпадающих строк поля заполняются реальными значениями, для несовпадающих строк поля заполняются в соответствии с правилами левого и правого соединений. На практике этот тип соединения требуется чрезвычайно редко. Поэтому примеров мы приводить не будем.
Количество выполняемых соединений таблиц в одном операторе SELECT не имеет физических ограничений, однако рекомендуется использовать их не более 16.
Рассмотрим пример двойного внешнего соединения, т. е. тот случай, когда к первой таблице присоединяется не одна, а уже две таблицы.
Чтобы увидеть фамилии с именами и отчествами всех членов экипажа с указанием их роли, номера авиалинии и даты вылета нужно выполнить два соединения таблицы EQUIPAGE с таблицей PERSON и с таблицей RANK.
Введите и выполните оператор:
SELECT E.EQ_FL_DATE AS "Дата вылета",
E.EQ_FL_NUM AS "Авиалиния",
P.PR_NAME2 || ' ' || P.PR_NAME3 || ' ' || P.PR_NAME AS "ФИО",
R.RNK_NAME AS "Роль"
FROM EQUIPAGE E INNER JOIN PERSON P
ON E.EQ_PR_CODE = P.PR_CODE INNER JOIN RANK R
ON E.EQ_RNK_CODE = R.RNK_CODE
WHERE E.EQ_FL_DATE BETWEEN '1.02.08' AND '29.02.08'
ORDER BY E.EQ_FL_DATE, E.EQ_FL_NUM, 3
Вначале отбираются все члены экипажа из таблицы EQUIPAGE (здесь она становится главной) на основании условий предложения WHERE (все рейсы за февраль 2008). В выбранные строки добавляются полные имена из таблицы PERSON. Условием соединения является равенство кода в таблице PERSON коду человека в таблице EQUIPAGE. После чего к этим строкам добавляются названия роли из таблицы RANK. Здесь условием соединения является равенство кода, первичного ключа таблицы RANK коду роли в начальной, "главной", таблице EQUIPAGE. Результат сортируем по дате вылета, номеру авиалинии и фамилиям именам и отчествам (так как поле ФИО вычисляется, в списке сортировки указываем номер поля).
В результате выполнения двойного соединения мы получим
Листинг 19. Двойное внешнее соединение список всех членов экипажа с указанием их роли, номера авиалинии и даты вылета
Дата вылета |
Авиалиния |
ФИО |
Роль |
13.02.2008 |
901 |
Анна Валерьевна Грунтович |
Борт проводник |
13.02.2008 |
901 |
Евгений Андреевнич Болотов |
Командир |
13.02.2008 |
901 |
Людмила Александровна Блинова |
Борт проводник |
13.02.2008 |
901 |
Марина Владимировна Волченко |
Борт проводник |
13.02.2008 |
901 |
Наталья Николаевна Васюкова |
Борт проводник |
13.02.2008 |
901 |
Павел Павлович Попутько |
Пилот |
14.02.2008 |
901 |
Анна Евгеньевна Елизарова |
Борт проводник |
14.02.2008 |
901 |
Вера Раисовна Дударева |
Борт проводник |
14.02.2008 |
901 |
Екатерина Викторовна Еремеева |
Борт проводник |
14.02.2008 |
901 |
Елена Ильинична Жукова |
Борт проводник |
14.02.2008 |
901 |
Семен Валерьевич Дудин |
Командир |
14.02.2008 |
901 |
Юлий Альбертович Алексеев |
Пилот |
15.02.2008 |
901 |
Анна Валерьевна Грунтович |
Борт проводник |
15.02.2008 |
901 |
Евгений Андреевнич Болотов |
Командир |
15.02.2008 |
901 |
Елена Анатольевна Мичурина |
Борт проводник |
15.02.2008 |
901 |
Марина Владимировна Волченко |
Борт проводник |
15.02.2008 |
901 |
Сергей Иванович Ридель |
Пилот |
15.02.2008 |
901 |
Татьяна Александровна Соснина |
Борт проводник |
Необходимо отметить, что порядок указания соединяемых таблиц не имеет значения. Здесь важно задать первую, "главную", таблицу сразу после ключевого слова FROM, все остальные соединяемые таблицы "подключаются" к строкам, выбранным именно из этой таблицы, и ее строки выбираются на основании условия в предложении WHERE.
Для иллюстрации этого изменим предыдущий оператор SELECT, поменяв местами соединяемые таблицы. Выполните оператор:
SELECT E.EQ_FL_DATE AS "Дата вылета",
E.EQ_FL_NUM AS "Авиалиния",
P.PR_NAME2 || ' ' || P.PR_NAME3 || ' ' || P.PR_NAME AS "ФИО",
R.RNK_NAME AS "Роль"
FROM PERSON P INNER JOIN EQUIPAGE E
ON E.EQ_PR_CODE = P.PR_CODE INNER JOIN RANK R
ON E.EQ_RNK_CODE = R.RNK_CODE
WHERE E.EQ_FL_DATE BETWEEN '1.02.08' AND '29.02.08'
ORDER BY E.EQ_FL_DATE, E.EQ_FL_NUM, 3
В качестве главной таблицы мы задали таблицу PERSON, в результате получим точно такой же список, как и в предыдущем случае.
Приведем еще один более сложный пример, который требует соединения четырех таблиц. Выведем тариф для всех рейсов вылетающих из города «Пермь». Мы должны получить название аэропорта прилета, номер маршрута, вид салона и саму стоимость билета. Выполните оператор:
SELECT T.TR_AL_NUM AS "Номер Авиалинии",
ATO.AP_NAME AS "Аэропорт прилета",
S.SL_NAME AS "Салон",
T.TR_COST AS "Стоимость"
FROM TARIFF T
INNER JOIN AIRPORT AFROM ON (T.TR_AP_FROM = AFROM.AP_CODE)
INNER JOIN AIRPORT ATO ON (T.TR_AP_TO = ATO.AP_CODE)
INNER JOIN SALON S ON (T.TR_SL_TYPE = S.SL_TYPE)
INNER JOIN CITY C ON (AFROM.AP_CT_CODE = C.CT_CODE)
WHERE C.CT_NAME = 'Пермь'
Таким образом, в запросе будут задействованы следующие таблицы: TARIFF, AIRPORT, SALON, CITY. Обратите внимание, что таблица AIRPORT используется в запросе два раза один раз мы используем соединение используя поле TR_AP_TO таблицы TARIFF, таблица в этом соединении обозначается псевдонимом ATO и означает аэропорт прилета, второй раз в соединении используется поле TR_AP_FROM, в этом случае таблица AIRPORT будет обозначаться псевдонимом AFROM и означает аэропорт вылета.
Следующий пример потребует объединения шести таблиц. Получим список (фамилию, имя, отчество) тех кто имеет билеты из Перми в Москву на 11.01.2008, кроме фамилии имени отчества выведем вид салона, стоимость билета и занимаемое место. В запросе мы должны связать следующие таблицы: PERSON связываем с таблицей TICKET, TICKET с TARIFF, TARIFF с SALON, далее TARIFF дважды связываем а AIRPORT (первый раз связываем используя поле TR_AP_FROM, т.е. определяя аэропорт вылета, второй раз связываем используя поле TR_AP_TO, тем самым будет определен аэропорт прилета), AIRPORT дважды связывается с CITY (один раз для города, где размещен аэропорт вылета, второй раз связь будет определять город, где размещен аэропорт прилета). Так как таблицы AIRPORT и CITY используются два раза, для них используются различные синонимы.
Приводим вид запроса:
SELECT PR_NAME2 AS "Имя",
PR_NAME3 AS "Отчество",
PR_NAME AS "Фамилия",
S.SL_NAME AS "Салон",
TC.TC_SEAT AS "Место",
TR.TR_COST AS "Стоимость"
FROM PERSON P
INNER JOIN TICKET TC ON TC.TC_PR_CODE = P.PR_CODE
INNER JOIN TARIFF TR ON TR.TR_CODE = TC.TC_TR_CODE
INNER JOIN SALON S ON TR.TR_SL_TYPE = S.SL_TYPE
INNER JOIN AIRPORT AFROM ON AFROM.AP_CODE = TR.TR_AP_FROM
INNER JOIN CITY CFROM ON CFROM.CT_CODE = AFROM.AP_CT_CODE
INNER JOIN AIRPORT ATO ON ATO.AP_CODE = TR.TR_AP_TO
INNER JOIN CITY CTO ON CTO.CT_CODE = ATO.AP_CT_CODE
WHERE CFROM.CT_NAME = 'Пермь' AND CTO.CT_NAME = 'Москва'
AND TC.TC_FL_DATE = '11.01.2008'
Получаем список:
Листинг 20. Список купивших билеты из Перми в Москву на 11.01.2008, объединение шести таблиц.
Имя |
Отчество |
Фамилия |
Салон |
Место |
Стоимость |
Ирина |
Валентиновна |
Щекина |
Бизнес |
4A |
8800 |
Константин |
Игоревич |
Баталов |
Бизнес |
4Б |
8800 |
Ольга |
Юрьевна |
Боброва |
Бизнес |
4Г |
8800 |
Елена |
Павловна |
Бельтюкова |
Бизнес |
5А |
8800 |
Светлана |
Леонидовна |
Верушкина |
Бизнес |
5В |
8800 |
Александр |
Александрович |
Чудинов |
Бизнес |
5Г |
8800 |
Сергей |
Владимирович |
Богатырев |
Бизнес |
6А |
8800 |
Владимир |
Николаевич |
Карпов |
Бизнес |
3Б |
8800 |
Татьяна |
Викторовна |
Владысик |
Бизнес |
3Г |
8800 |
Наталья |
Васильевна |
Вожакова |
Бизнес |
2А |
8800 |
Владимир |
Юрьевич |
Войтович |
Бизнес |
2Б |
8800 |
Елена |
Александровна |
Воронюк |
Эконом |
20А |
3500 |
Мария |
Ивановна |
Выгузова |
Эконом |
20Б |
3500 |
Ольга |
Николаевна |
Вяткина |
Эконом |
20В |
3500 |
Юлия |
Дмитриевна |
Гаевская |
Эконом |
20Г |
3500 |
Василий |
Александрович |
Глазов |
Эконом |
20Д |
3500 |
Дмитрий |
Федорович |
Глумов |
Эконом |
21А |
3500 |
Последний пример демонстрирует использование соединения таблиц в сочетании с группировкой. Найдем суммарную стоимость проданных за 2008 год билетов для каждой из авиакомпаний. Для того чтобы найти то что нам требуется нужно просуммировать стоимость билета из таблицы TARIFF для каждого проданного (за указанный год) билета. В список вывода нужно поместить наименование авиакомпании и сумму стоимостей из таблицы TARIFF. Результат должен быть сгруппирован по наименованиям авиакомпаний. Для наглядности упорядочим выводимые строки по величине суммы.
Введем запрос:
SELECT AC.AC_NAME AS “Авиакомпания “,
SUM( TRF.TR_COST ) AS “Стоимость”
FROM TARIFF TRF
INNER JOIN TICKET TC ON (TRF.TR_CODE = TC.TC_TR_CODE)
INNER JOIN AIRLINE AL ON (TRF.TR_AL_NUM = AL.AL_NUM)
INNER JOIN AIRCOMPANY AC ON (AL.AL_AC_CODE = AC.AC_CODE)
GROUP BY AC.AC_NAME
ORDER BY 2
В результате получим список:
Листинг 21. Результат объединения и группировки, суммарная стоимость проданных за 2008 год билетов для каждой из авиакомпаний.
Авиакомпания |
Стоимость |
S7 Airlines |
42100 |
Lufthansa |
57800 |
Сибавиатранс |
59420 |
Ютэйр-экспресс |
80300 |
Sky express |
19690 |
Аэрофлот-РА |
126800 |
Соединяемые таблицы не обязательно должны отличаться от главной таблицы в операторе SELECT. Если таблица соединяется сама с собой, то такое соединение называется рефлексивным или самосоединением. Есть еще один термин для такого соединения реентерабельное. Рефлексивное связывание удобно использовать, когда все необходимые данные размещаются в одной таблице, но требуется каким-то образом сравнить одни записи таблицы с другими.
Найдем всех однофамильцев, т.е. выведем фамилии (с именами и отчествами) и даты рождения из таблицы PERSON если существует соединение с этой же самой таблицей. Соединяем по фамилии, чтобы все строки не соединились сами с собой нужно указать, что первичные ключи (PR_CODE) для двух соединенных строк не совпадают.
SELECT DISTINCT
P2.PR_NAME || ' ' || P2.PR_NAME2 || ' ' || P2.PR_NAME3 AS "ФИО", P2.PR_BIRTHDAY AS "Дата рождения"
FROM PERSON P2 INNER JOIN PERSON P1 ON
(P2.PR_NAME = P1.PR_NAME) AND (P2.PR_CODE <> P1.PR_CODE)
ORDER BY 1
Если в исходной таблице есть больше чем два человек с одной фамилией мы получим дублирование строк, поэтому используем DISTINCT для исключения дубликатов. Результат упорядочиваем по вычисляемому полю ФИО, чтобы сразу увидеть группы однофамильцев.
Листинг 22. Результат самообъединения таблицы PERSON с самой собой - список всех однофамильцев.
ФИО |
Дата рождения |
Блинова Людмила Александровна |
28.08.1957 |
Блинова Ольга Владимировна |
|
Блинова Светлана Александровна |
05.05.1980 |
Васильева Марианна Юрьевна |
|
Васильева Наталья Васильевна |
09.11.1980 |
Зеленина Екатерина Владимировна |
16.12.1977 |
Зеленина Надежда Александровна |
11.12.1965 |
Зеленина Наталья Александровна |
23.09.1990 |
Зеленина Ольга Викторовна |
30.04.1958 |
Култышева Лиана Валентиновна |
22.01.1970 |
Култышева Наталья Сергеевна |
|
Лобанов Алексей Викторович |
18.11.1951 |
Лобанов Владимир Николаевич |
|
Лядова Надежда Вячеславовна |
|
Лядова Наталья Викторовна |
12.01.1973 |
Некрасова Елена Павловна |
08.09.1970 |
Некрасова Татьяна Николаевна |
04.12.1949 |
Новикова Вера Валерьевна |
09.03.1947 |
Новикова Марина Рафаиловна |
30.11.1975 |
Рожкова Анастасия Сергеевна |
|
Рожкова Оксана Юрьевна |
|
Степанова Ирина Борисовна |
|
Степанова Наталья Алексеевна |
09.11.1969 |
Обратите внимание на то, что в данном случае мы обязаны для таблиц указывать псевдонимы, чтобы точно указывать в операторе, к какой именно таблице относится тот или иной столбец. В нашем случае используются псевдонимы P1, P2. Имея два псевдонима, таблицу можно рассматривать как две разные таблицы.
Подзапрос это запрос, содержащийся в выражении ключевого слова WHERE другого запроса с целью дополнительных ограничений на выводимые данные. Подзапросы называют также вложенными запросами. Подзапрос в содержащем его запросе используют для наложения условий на выводимые данные.
При составлении подзапросов необходимо придерживаться следующих правил.
"Выбор одного" это оператор 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 используется в условии 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. То есть во внутреннем запросе нужно из таблицы 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' )
Наиболее часто вместе с 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 используется для объединения результатов двух или более операторов SELECT с исключением повторяющихся строк. Другими словами, если строка попадает в вывод одного запроса, то второй раз она не выводится, даже если она возвращается вторым запросом. При использовании UNION в каждом из связываемых операторов SELECT должно быть выбрано одинаковое число столбцов, столбцы должны быть одинакового типа и следовать в том же порядке.
В составных запросах можно использовать ключевое слово ORDER BY. Однако ключевое слово ORDER BY в них можно использовать только для упорядочения результатов окончательного вывода обоих запросов. Поэтому в составном запросе допускается использовать только одно выражение с ключевым словом ORDER BY, хотя сам составной запрос может состоять из нескольких операторов SELECT На столбцы в выражении ключевого слова ORDER BY можно ссылаться как по псевдонимам, так и по их номерам в списке выбора
Допустим нам нужно найти всех прилетевших из Перми в Москву за определенное число. Человек может прилететь в качестве члена экипажа (если коды этой личности присутствует в таблице EQUIPAGE на заданное число), или в качестве пассажира (если у него есть билет на заданное число). Таким образом, требуется объединить два запроса, первый найдет фамилию, имя, отчество для всех членов экипажа прилетевших из Перми в Москву за 11.01.2008, а второй выведет фамилию, имя, отчество тех кто имеет билеты из Перми в Москву на это же число (такой запрос мы уже делали, когда исследовали соединения таблиц). Для того чтобы отличать пассажира от члена экипажа сделаем следующее, добавим в результат новое поле, где для первого запроса будет во всех строках содержаться один и тот же текст «Член экипажа», второй запрос разместит в этом поле текст «Пассажир». Это дополнительное поле озаглавим как «Тип». Кроме этого добавим еще одно поле, в которое будем выводить дополнительную информацию, для запроса по членам экипажа будем выводить роль для данного члена, а для пассажиров номер места указанный в билете. Соответственно это поле мы так и озаглавим «Роль/Место».
Обратим внимание, что вновь введенные поля в двух запросах не совпадают по типу. Для того чтобы можно было использовать команду UNION необходимо привести типы этих полей во втором запросе к тому же типу, что и в первом. Преобразование данных к требуемому типу обеспечивает функция CAST. Для поля «Тип» должно быть сделано преобразование к типу CHAR(12), а для поля «Роль/Место» к типу VARCHAR(20).
Получаем запрос:
SELECT P.PR_NAME2 AS "Имя",
P.PR_NAME3 AS "Отчество",
P.PR_NAME AS "Фамилия",
'Член экипажа' AS "Тип",
R.RNK_NAME AS "Роль/Место"
FROM PERSON P
INNER JOIN EQUIPAGE EQ ON EQ.EQ_PR_CODE = P.PR_CODE
INNER JOIN RANK R ON R.RNK_CODE = EQ.EQ_RNK_CODE
INNER JOIN AIRLINE AL ON AL.AL_NUM = EQ.EQ_FL_NUM
INNER JOIN AIRPORT AFROM ON AFROM.AP_CODE = AL.AL_AP_FROM
INNER JOIN CITY CFROM ON CFROM.CT_CODE = AFROM.AP_CT_CODE
INNER JOIN AIRPORT ATO ON ATO.AP_CODE = AL.AL_AP_TO
INNER JOIN CITY CTO ON CTO.CT_CODE = ATO.AP_CT_CODE
WHERE CFROM.CT_NAME = 'Пермь' AND CTO.CT_NAME = 'Москва'
AND EQ.EQ_FL_DATE = '11.01.2008'
UNION
SELECT P.PR_NAME2, P.PR_NAME3, P.PR_NAME,
CAST('Пассажир' AS CHAR(12)),
CAST(TC.TC_SEAT AS VARCHAR(20))
FROM PERSON P
INNER JOIN TICKET TC ON TC.TC_PR_CODE = P.PR_CODE
INNER JOIN TARIFF TR ON TR.TR_CODE = TC.TC_TR_CODE
INNER JOIN AIRPORT AFROM ON AFROM.AP_CODE = TR.TR_AP_FROM
INNER JOIN CITY CFROM ON CFROM.CT_CODE = AFROM.AP_CT_CODE
INNER JOIN AIRPORT ATO ON ATO.AP_CODE = TR.TR_AP_TO
INNER JOIN CITY CTO ON CTO.CT_CODE = ATO.AP_CT_CODE
WHERE CFROM.CT_NAME = 'Пермь' AND CTO.CT_NAME = 'Москва'
AND TC.TC_FL_DATE = '11.01.2008'
В результате выполнения запроса получаем список:
Листинг 25. Результат объединения запросов (команды UNION) список всех прилетевших из Перми в Москву 11.01.2008
Имя |
Отчество |
Фамилия |
Тип |
Роль/Место |
Александр |
Александрович |
Чудинов |
Пассажир |
5Г |
Владимир |
Николаевич |
Карпов |
Пассажир |
3Б |
Владимир |
Юрьевич |
Войтович |
Пассажир |
2Б |
Елена |
Павловна |
Бельтюкова |
Пассажир |
5А |
Ирина |
Валентиновна |
Щекина |
Пассажир |
4A |
Константин |
Игоревич |
Баталов |
Пассажир |
4Б |
Лариса |
Вилльевна |
Вашкарина |
Член экипажа |
Борт проводник |
Максим |
Владимирович |
Бычков |
Член экипажа |
Командир |
Наталья |
Васильевна |
Вожакова |
Пассажир |
2А |
Олег |
Владимирович |
Лядов |
Член экипажа |
Пилот |
Ольга |
Юрьевна |
Боброва |
Пассажир |
4Г |
Светлана |
Александровна |
Ветошкина |
Член экипажа |
Борт проводник |
Светлана |
Леонидовна |
Верушкина |
Пассажир |
5В |
Сергей |
Владимирович |
Богатырев |
Пассажир |
6А |
Татьяна |
Викторовна |
Владысик |
Пассажир |
3Г |
Елена |
Александровна |
Воронюк |
Пассажир |
20А |
Мария |
Ивановна |
Выгузова |
Пассажир |
20Б |
Ольга |
Николаевна |
Вяткина |
Пассажир |
20В |
Юлия |
Дмитриевна |
Гаевская |
Пассажир |
20Г |
Василий |
Александрович |
Глазов |
Пассажир |
20Д |
Дмитрий |
Федорович |
Глумов |
Пассажир |
21А |
Команда INSERT предназначена для добавления данных в базу. С ее помощью можно добавить в указанную таблицу или представление одну или сразу несколько строк.
При добавлении отдельной строки в списке 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 имя_таблицы
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 FROM имя_таблицы
[WHERE условие];
Если ключевое слово WHERE в операторе DELETE опущено, будут удалены все строки таблицы. Поэтому практически всегда необходимо использовать ключевое слово WHERE в операторе DELETE.
В качестве примера удалим все сведенья о билетах с датой вылета ранее 1 января 2000 года:
DELETE FROM TICKET
WHERE TC_FL_DATE < '01.01.2000'
При использовании команды DELETE бывает полезно использование вложенных запросов SELECT. Удалим из таблицы PERSON тех людей кто ни разу не летал в качестве пассажира или члена экипажа. Чтобы выполнить это, нужно найти коды личностей всех членов экипажа (из таблицы EQUIPAGE) и всех пассажиров с билетами (из таблицы TICKET). Получаем запрос на удаление:
DELETE FROM PERSON
WHERE NOT PR_CODE IN
(SELECT EQ_PR_CODE FROM EQUIPAGE)
AND NOT PR_CODE IN
(SELECT TC_PR_CODE FROM TICKET)
Здесь используются два вложенных запроса. Первый возвращает коды личностей членов экипажа из таблицы EQUIPAGE, а второй коды личностей из таблицы TICKET. В результате будут удалены все строки из таблицы PERSON коды личностей которых не попадают в результат вывода каждого из этих двух запросов.
При заполнении таблиц с суррогатным первичным ключом нам приходится вручную проставлять его значение, например - 1, 2, 3..., 10, 20, 30, и т.п. Это в частности, не дает возможности добавить в таблицу несколько строк одной командой INSERT, при помощи конструкции SELECT, как это было показано в предшествующем разделе.
Для решения этой проблемы большинство SQL-серверов имеет специальные механизмы для создания уникальных идентификаторов. В Interbase и Firebird для этого существует механизм генераторов. Генераторы предназначены для получения последовательностей уникальных чисел. (1, 2, 3.. и т.д.). Например, в таблице аэропортов для их нумерации введен столбец AP_CODE, по которому построен первичный ключ. Столбец можно заполнять значениями генератора.
Нужно сразу заметить, что сами по себе генераторы не обеспечивают сохранение последовательности номеров в случае удаления записей - генератор всего лишь выдает числа по очереди увеличивая их на некоторую величину и обеспечивая уникальность выданных значений. То есть, генератор выглядит как переменная типа Integer , которая находится в памяти, и над которой можно выполнять операции Inc и Dec (прибавления и убавления).
Генератор - это специальный объект базы данных, который генерирует уникальные последовательные числа. Эти числа могут быть использованы в качестве идентификаторов (например, код аэропорта, код личности и т.п.). Для создания генератора необходимо использовать оператор:
CREATE GENERATOR generatorname;
Как правило, генераторы, предназначаемые для заполнения поля будем называть по имени таблицы с прибавлением вначале строки «GEN_», а в конце «_ID». (Именно такое соглашение по именам использует IBExpert) Например, запрос на создание генератора для таблицы AIRPORT (ключевого поля AP_CODE) будет иметь вид
CREATE GENERATOR GEN_AIRPORT_ID
После создания генератора его значения можно получать при помощи функции
GEN_ID(generatorname, inc_value)
где inc_value - число, на которое необходимо прирастить значение а
Таким образом можно заранее узнать какое число выдаст генератор при следующем обращении, так для генератора предназначенного для выдачи первичного ключа таблицы.
Генераторы возвращают значения (и сохраняют свои значения на диске) вне контекста транзакции пользователя. Это означает, что если значение генератора было увеличено с 10 до 11 (инкремент 1), то даже при откате транзакции (ROLLBACK) значение генератора, выданное в этой транзакции, не вернется к предыдущему. Вместе с этим гарантируется, что каждому пользователю будет всегда возвращено уникальное значение генератора (вызов функции GEN_ID всегда происходит «монопольно», то есть непараллельно для любого числа одновременных вызовов, чтобы исключить возможность получения разными пользователями одного и того же значения).
Работа с генератором возможна только при помощи встроенной функции GEN_ID, а значит работать с генераторами можно в запросах, процедурах и триггерах. Давайте создади