Электронная библиотека Либрус” ( )ftemk.mpei.ac.ru/ed/sql/sql.pdf ·...

Электронная библиотека “Либрус” ( http://librus.ru ) Научно-техническая библиотека электронных книг. Первоначально задуманная как хранилище компьютерной литературы, в настоящий момент библиотека содержит книжные издания по различным областям знания (медицинские науки, техника, гуманитарные науки, домашнее хозяйство, учебная литература и т.д.). Серьезность научно-технических e-book'ов разбавляет раздел развлекательной литературы (эротика, комиксы, задачи и головоломки). Основной целью проекта является ознакомление читателей с многообразием книгопечатной продукции и помощь в выборе действительно стоящей книги для приобретения у законных издателей, их представителей или в соответствующих организациях торговли. Для покупки через Internet мы рекомендуем воспользоваться услугами интернет-магазина “Озон”. ВНИМАНИЕ! Данный файл представлен исключительно в ознакомительных целях! После ознакомления с данной книгой Вы обязаны удалить ее с Вашего компьютера. В случае несоблюдения данного обязательства, Вы нарушите закон "Об авторском праве и смежных правах". Все авторские права сохраняются за правообладателем. По его требованию доступ к данному электронному документу будет перекрыт. Однако, таким образом, тысячи потенциальных покупателей так и не узнают о, возможно, нужной и полезной книге. Авторам и издательствам Если Вы заинтересованы в рекламе и продвижении Ваших книг на бескрайних сетевых просторах мы с удовольствием включим их в наш каталог.

http://librus.ru

http://www.ozon.ru/?partner=librus

Администратор

librus

И.Ф. АстаховаА.П. ТолстобровВ.М. Мельников

В ПРИМЕРАХ И ЗАДАЧАХ

УДК 004.655.3(075.8)

ББК 32.973.26-018.1я73

А91

Р е ц е н з е н т ы :

доцент кафедры АСИТ Московскогогосударственного университета Н.Д. Васюкова;

Воронежское научно-производственное предприятие «РЕЛЭКС»;

кафедра информатики и МПМ Воронежскогогосударственного педагогического университета;

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

технологической академии В.В. Сысоев;

доктор физико-математических наук, профессор, декан факультета ,компьютерных наук Воронежского государственного университета Э.К. Алгазинов

Астахова И.Ф.

А91 SQL в примерах и задачах; Учеб. пособие / И.Ф. Астахова, А.П. Тол-

стобров,В.М. Мельников.— Мн.: Новое знание, 2002. — 176 с.

ISBN 985-475-004-3.

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

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

УДК 004.655.3(075.8)ББК 32.973.26-018.1я73

ISBN 985-475-004-3

Астахова И.Ф., Толстобров А.П.,Мельников В.М., 2001Оформление. ООО «Новое знание», 2002

Оглавление

Введение 8

1. Основные понятия и определения 101.1. Основные понятия реляционных баз данных 101.2. Отличие SQL от процедурных языков программирования ... 121.3. Интерактивный и встроенный SQL 131.4. Составные части SQL 131.5. Типы данных SQL 14

1.5.1. Тип данных «строка символов» 141.5.2. Числовые типы данных 151.5.3. Дата и время 161.5.4. Неопределенные или пропущенные данные

(NULL) 171.6. Используемые термины и обозначения 181.7. Учебная база данных 18

2. Выборка данных (оператор SELECT) 232.1. Простейшие SELECT-запросы 232.2. Операторы IN, BETWEEN, LIKE, is NULL 282.3. Преобразование вывода и встроенные функции 31

2.3.1. Числовые, символьные и строковые константы ... 312.3.2. Арифметические операции для преобразования

числовых данных 322.3.3. Операция конкатенации строк 332.3.4. Функции преобразования символов в строке..... 342.3.5. Строковые функции 342.3.6. Функции работы с числами 372.3.7. Функции преобразования значений 39

2.4. Агрегирование и групповые функции 432.5. Пустые значения (NULL) в агрегирующих функциях 46

2.5.1. Влияние NULL-значений в функции COUNT 462.5.2. Влияние NULL-значений в функции AVG 47


2.6. Результат действия трехзначных условных операторов ... 472.7. Упорядочение выходных полей (ORDER BY) 492.8. Вложенные подзапросы 512.9. Формирование связанных подзапросов . 53

2.10. Связанные подзапросы в HAVING 542.11. Использование оператора EXISTS 56

2.12. Операторы сравнения с множествомзначений IN, ANY, All 58

2.13. Особенности применения операторов ANY, ALL, EXISTSпри обработке пустых значений (NULL) 60

2.14. Использование COUNT вместо EXISTS 622.15. Оператор объединения UNION 632.16. Устранение дублирования в UNION 642.17. Использование UNION с ORDER BY 662.18. Внешнее объединение 672.19. Соединение таблиц с использованием оператора JOIN ... 69

2.19.1. Операции соединения таблиц посредствомссылочной целостности 70

2.19.2. Внешнее соединение таблиц 722.19.3. Использование псевдонимов при соединении

таблиц 74

3. Манипулирование данными 783.1. Команды манипулирования данными 783.2. Использование подзапросов в INSERT 81

3.2.1. Использование подзапросов, основанныхна таблицах внешних запросов 82

3.2.2. Использование подзапросов с DELETE 833.2.3. Использование подзапросов с UPDATE 85

4. Создание объектов базы данных 874.1. Создание таблиц базы данных 874.2. Использование индексации для быстрого доступа

88к данным4.3. Изменение существующей таблицы


4.4. Удаление таблицы 904.5. Офаничения на множество допустимых значений данных ...91

4.5.1. Ограничение NOT NULL 924.5.2. Уникальность как ограничение на столбец 934.5.3. Уникальность как ограничение таблицы 944.5.4. Присвоение имен ограничениям 954.5.5. Ограничение первичных ключей 964.5.6. Составные первичные ключи 964.5.7. Проверка значений полей 974.5.8. Проверка ограничивающих условий

с использованием составных полей 984.5.9. Установка значений по умолчанию. 99

4.6. Поддержка целостности данных 1014.6.1. Внешние и родительские ключи 1014.6.2. Составные внешние ключи 1024.6.3. Смысл внешнего и родительского ключей 1024.6.4. Ограничение FOREIGN KEY (внешнего ключа)... 1034.6.5. Внешний ключ как ограничение таблицы 1034.6.6. Внешний ключ как ограничение столбца 1054.6.7. Поддержание ссылочной целостности

и ограничения значений родительского ключа ... 1074.6.8. Использование первичного ключа в качестве

уникального внешнего ключа 1074.6.9. Ограничения значений внешнего ключа 108

4.6.10. Действие ограничений внешнегои родительского ключей при использованиикоманд модификации 108

5. Представления (VIEW) 1125.1. Представления — именованные запросы 1125.2. Представления таблиц 1135.3. Представления столбцов 1135.4. Модифицирование представлений 1145.5. Маскирующие представления 114

5.5.1. Представления, маскирующие столбцы 1145.5.2. Операции модификации в представлениях,

маскирующих столбцы 115


5.5.3. Представления, маскирующие строки 1155.5.4. Операции модификации в представлениях,

маскирующих строки 1165.5.5. Операции модификации в представлениях,

маскирующих строки и столбцы 1175.6. Агрегированные представления 1195.7. Представления, основанные на нескольких таблицах ... 1195.8. Представления и подзапросы 1205.9. Ограничения применения оператора SELECT для

создания представлений 1215.10. Удаление представлений 1215.11. Изменение значений в представлениях 1225.12. Примеры обновляемых и необновляемых

представлений 1235.13. Представления, базирующиеся на других

представлениях 125

6. Определение прав доступа пользователейк данным 128

6.1. Пользователи и привилегии 1286.2. Стандартные привилегии 1296.3. Команда GRANT 1306.4. Использование аргументов ALL и PUBLIC 1316.5. Отмена привилегий 1316.6. Использование представлений для фильтрации

привилегий 1326.6.1. Ограничение привилегии SELECT

для определенных столбцов 1336.6.2. Ограничение привилегий для определенных

строк 1336.6.3. Предоставление доступа только

к извлеченным данным 1346.6.4. Использование представлений в качестве

альтернативы ограничениям 1346.7. Другие типы привилегий. 1356.8. Типичные привилегии системы 136


6.9. Создание и удаление пользователей....6.10. Создание синонимов (SYNONYM)6.11. Синонимы общего пользования (PUBLIC)6.12. Удаление синонимов

136138139139

7. Управление транзакциями 141

Приложение 1. Ответы к упражнениям

Приложение 2. Задачи по проектированию БД

Предметный указатель

143

159

171

ВведениеВ настоящее время информационные системы, применяющие

базы данных, представляют собой одну из важнейших областей совре-менных компьютерных технологий. С этой сферой связана большаячасть современного рынка программных продуктов. Среди общих тен-денций в развитии таких систем выделяются процессы интеграциии стандартизации, затрагивающие структуры данных и способы их об-работки и интерпретации, системное и прикладное программное обес-печение, средства взаимодействия компонентов баз данных и многоедругое. Современные системы управления базами данных (СУБД) ос-нованы на реляционной модели представления данных — в большойстепени благодаря простоте и четкости ее концептуальных понятий истрогого математического обоснования.

Неотъемлемая и важнейшая часть любой системы, применяющейбазы данных, — языковые средства, обеспечивающие возможностьдоступа и действий над данными, определения их структур, способовиспользования и интерпретации. Язык SQL появился в 1970-е годыкак одно из таких средств. Его прототип был разработан фирмой IBMи известен под названием SEQUEL (Structured English Query Language).SQL вобрал в себя достоинства реляционной модели, в частности дос-тоинства лежащего в ее основе математического аппарата реляционнойалгебры и реляционного исчисления, используя при этом сравнитель-но небольшое число операторов и относительно простой синтаксис.

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

Говоря о стандарте языка SQL, следует заметить, что большинствоего коммерческих реализаций имеют некоторые, большие или меньшие,отличия от стандарта. Это, конечно, ухудшает совместимость систем,использующих различные «диалекты» SQL. Но, с другой стороны, по-лезные расширения реализаций языка обеспечивают его развитие и современем включаются в новые редакции стандарта. Учитывая место,

занимаемое SQL в современных информационных технологиях, егознание необходимо любому специалисту, работающему в этой области.

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

В приложении 1 пособия содержатся ответы на большинство приве-денных в нем упражнений. Примеры и задачи упражнений протести-рованы с использованием СУБД Oracle и отечественной СУБД ЛИНТЕР.ЛИНТЕР представляет собой полномасштабный кросс-платформен-ный SQL-сервер, соответствующий основным мировым стандартам,предъявляемым к системам такого класса. Для некоммерческого ис-пользования учебным заведениям он предоставляется бесплатно. Бо-лее подробную информацию о системе можно получить на сайте ком-пании РЕЛЭКС по адресу www.relex.ru.

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

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

Основные понятияи определения

1.1. Основные понятия реляционных баз данных

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

Рис. 1. Пример таблицы-отношения реляционной базы данных

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

1.1. Основные понятия реляционных баз данных 11

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

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

• Ключ, или первичный ключ отношения — это уникальныйидентификатор строк (кортежей), то есть такой атрибут (на-бор атрибутов), для которого в любой момент времени в от-ношении не существует строк с одинаковыми значениямиэтого атрибута (набора атрибутов). На приведенном рисункетаблицы ячейка с именем ключевого атрибута имеет ниж-нюю границу в виде двойной черты.

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

ми свойствами:• в отношениях не должно быть кортежей-дубликатов,• кортежи отношений не упорядочены,• атрибуты отношений также не упорядочены.

Из этих свойств отношения вытекают важные следствия.• Уникальность кортежей определяет, что в отношении всегда

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

12 1. Основные понятия и определения

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

• Неупорядоченность атрибутов определяет, что единствен-ным способом их адресации в запросах является использова-ние наименования атрибута.Относительно свойства реляционного отношения, касаю-

щегося отсутствия кортежей-дубликатов, следует сделать важ-ное замечание. В этом пункте SQL не полностью соответствуетреляционной модели. А именно: в отношениях, являющихсярезультатами запросов, SQL допускает наличие одинаковыхстрок. Для их устранения в запросе используется ключевое сло-во DISTINCT (см. ниже).

Информация в реляционных базах данных, как правило,хранится не в одной таблице-отношении, а в нескольких. Присоздании нескольких таблиц взаимосвязанной информации по-является возможность выполнения более сложных операцийс данными, то есть более сложной их обработки. Для работы сосвязанными данными из нескольких таблиц важным являетсяпонятие так называемых внешних ключей.

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

1.2. Отличие SQL от процедурных языковпрограммирования

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

1.4. Составные части SQL 13

Это означает следующее: в качестве входной информациидля формулируемого на языке SQL запроса к базе данных ис-пользуется множество кортежей-записей одной или несколькихтаблиц-отношений. В результате выполнения запроса также об-разуется множество кортежей результирующей таблицы-отно-шения. Другими словами, в SQL результатом любой операциинад отношениями также является отношение. Запрос SQL зада-ет не процедуру, то есть последовательность действий, необхо-димых для получения результата, а условия, которым должныудовлетворять кортежи результирующего отношения, сформу-лированные в терминах входного (или входных) отношения.

1.3. Интерактивный и встроенный SQL

Существуют и используются две формы языка SQL: интер-активный SQL и встроенный SQL.

Интерактивный SQL используется для задания SQL-запросовпользователем и получения результата в интерактивном режиме.

Встроенный SQL состоит из команд SQL, встроенных внутрьпрограмм, обычно написанных на каком-то другом языке (Пас-каль, С, C++ и др. ). Это делает программы, использующие та-кие языки, более мощными, гибкими и эффективными, обес-печивая их применение для работы с данными, хранящимисяв реляционных базах. При этом, однако, требуются дополни-тельные средства интерфейса SQL с языком, в который онвстраивается.

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

1.4. Составные части SQL

И интерактивный, и встроенный SQL подразделяются наследующие составные части.

Язык определения данных — DDL (Data Definition Language) —дает возможность создания, изменения и удаления различныхобъектов базы данных (таблиц, индексов, пользователей, при-вилегий и т.д.).

1. Основные понятия и опредепения14

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

Язык обработки Даннь^^х — DML (Data Manipulation Language) -предоставляет возможность выборки информации из базы дан-ных и ее преобразования.

Тем не менее это не два различных языка, а компонентыединого SQL.

1.5. Типы данных SQL

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

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

1.5.1. Тип данных «строка символов»

Стандарт поддерживает только один тип представления тек-ста — CHARACTER (CHAR). Этот тип данных представляет собойсимвольные строки фиксированной длины. Его синтаксис име-ет вид:

CHARACTER [(длина)] или

CHAR [(длына)].

Текстовые значения поля таблицы, определенного как типCHAR, имеют фиксмрованную длину, которая определяется пара-метром длина. Этот параметр может принимать значения от 1 до255, то есть строка может содержать до 255 символов. Если вовводимой в поле текстовой константе фактическое число симво-лов меньше числа, определенного параметром длмна, то этаконстанта автоматически дополняется справа пробелами до за-данного числа символов.

1.5. Типы данных SQL 15

Некоторые реализации языка SQL поддерживают в качестветипа данных строки переменной длины. Этот тип может обозна-чаться ключевыми словами VARCHAR ( j , CHARACTER VARYINGили CHAR VARYING (j . Он описывает текстовую строку, котораяможет иметь произвольную длину до определенного конкретнойреализацией SQL максимума (в Oracle — до 2000 символов).В отличие от типа CHAR в этом случае при вводе текстовой кон-станты, фактическая длина которой мены^е заданной, не про-изводится ее дополнение пробелами до заданного максимально-го значения.

Константы, имеющие тип CHARACTER и VARCHAR, в выра-жениях SQL заключаются в одиночные кавычки, например,'текст'.

Следующие предложения эквивалентны:

VARCHAR [(длмня)], CHAR VARYING [(длмнд)],

CHARACTER VARYING [(длмна)].

Если длина строки не указана явно, она полагается равнойодному символу во всех случаях.

По сравнению с типом CHAR тип данных VARCHAR позволяетболее экономно использоватъ память, выделяемую для хранениятекстовых значений, и оказывается более удобным при выполне-нии операций, связанных со сравнением текстовых констант.

1.5.2. Числовые типы данных

Стандартными числовыми типами данных SQL являются:• INTEGER -- используется для представления целых чисел

в диапазоне от —231 до +231.• SMOLLINT -- используется для представления целых чисел

в меньшем, чем для INTEGER, диапазоне, а именно — от-215 до +215.

• DECIMAL (точность[,масштаб]) — десятичное число с фик-сированной точкой, точность определяет количество знача-щих цифр в числе. Масштаб указывает максимальное числоцифр справа от точкм.

• NUmeRIC (точность[,масштаб|) — десятичное число с фик-сированной точкой, такое же, как и DECIMAL.


• FLOAT [(точность)] — число с плавающей точкой и указан-ной минимальной точностью.

• REAL — число такое же, как при типе FLOAT, за исключениемопределения точности по умолчанию (в зависимости от кон-кретной реализации SQL).

• DOUBLE PRECISION — число аналогично REAL, но точностьв два раза выше точности REAL.

СУБД Oracle использует дополнительно тип данных NUMBERдля представления всех числовых данных, целых, с фиксиро-ванной или плавающей точкой. Его синтаксис:

NUMBER [(точность[,.масшта6])].

Если значение параметра точность не указано явно, онополагается равным 38. Значение параметра масштаб по умолча-нию предполагается равным 0. Значение параметра точностьможет изменяться от 1 до 38; значение параметра масштаб мо-жет изменяться от —84 до 128. Использование отрицательныхзначений масштаба означает сдвиг десятичной точки в сторонустарших разрядов. Например, определение NUMBER (7,—3) оз-начает округление до тысяч.

Типы DECIMAL (иногда обозначаемый DEC) и NUMERICполностью эквивалентны типу NUMBER.

Синтаксис: DECIMAL [(точнос/иь[,л«асштао])],

DEC [(точностъ[,масштаб])],

NUMERIC [(точность[,масштаб\)].

1.5.3. Дата и время

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

В СУБД Oracle имеется тип DATE, используемый для хране-ния даты и времени. Поддерживаются даты, начиная от 1 января4712 года до н.э. и до 31 декабря 4712 года. По умолчанию при

1.5. Типы данных SQL 17

определении даты без уточнения времени принимается время по-луночи.

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

Константы типа DATE записываются в зависимости от форма-та, принятого в операционной системе. Например, '03.05.1999',или '12/06/1989', или '03-nov-1999', или 'ОЗ-арг-99'.

1.5.4. Неопределенные или пропущенные данные (NOLL)

Для обозначения отсутствующих, пропущенных или не-известных значений атрибута в SQL используется ключевоеслово NULL. Довольно часто можно встретить словосочетание«атрибут имеет значение NULL». Строго говоря, NULL не явля-ется значением в обычном понимании, а используется имен-но для обозначения того факта, что действительное значениеатрибута на самом деле пропущено или неизвестно. Это при-водит к ряду особенностей, что следует учитывать при ис-пользовании значений атрибутов, которые могут находитьсяв состоянии NULL.

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

• Условные операторы от булевой двузначной логики TRUE/FALSEрасширяются до трехзначной логики TRUE/FALSE/UNKNOWN.

• Все операторы, за исключением оператора конкатенациистрок «||», возвращают пустое значение (NULL), если значениелюбого из операндов отсутствует (имеет «значение NULL»).

• Для проверки на пустое значение следует использовать опе-раторы is NULL и is NOT NULL (использование с этой цельюоператора сравнения «=» является ошибкой).

• Функции преобразования типов, имеющие NULL в качествеаргумента, возвращают пустое значение (NULL).


1.6. Используемые термины и обозначения

Ключевые слова — это используемые в выражениях SQL сло-ва, имеющие специальное назначение (например, конкретныекоманды SQL). Ключевые слова нельзя использовать для другихцелей, к примеру, в качестве имен объектов базы данных.В книге они выделяются шрифтом: КЛЮЧЕВОЕ слово.

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

Объекты базы данных, имеющие имена (таблицы, атрибу-ты и др.), в книге также выделяются особым образом: ТАБЛ^ЦА1, АТРИБУТ_2.

В описании синтаксиса команд SQL:

• оператор определения «::=» разделяет определяемый эле-мент (слева от оператора) и собственно его определение(справа от оператора);

• квадратные скобки «[ ]» указывают необязательный элементсинтаксической конструкции;

• многоточие «... » определяет, что выражение, предшествую-щее ему, может повторяться любое число раз;

• фигурные скобки «{ }» объединяют последовательность эле-ментов в логическую группу, один из элементов которой дол-жен быть обязательно использован;

• вертикальная черта «|» указывает, что часть определения,следующая за этим символом, является одним из возможныхвариантов;

• в угловые скобки «< >» заключаются элементы, объясняемыепо мере того, как они вводятся.

1.7. Учебная база данных

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

STUDENT (Студент)

STUDENT ID

1

3

61012

26532

654

27655

SURNAME

Иванов

Петров

Сидоров

Кузнецов

Зайцева

Павлов

Котов

Лукин

Петров

Белкин

NAME

Иван

Петр

Вадим

Борис

Ольга

Андрей

Павел

Артем

Антон

Вадим

STIPEND

150200

150

0250

0

150

200200

250

KURS

1

3

4

223

534

5

CITY

Орел

Курск

Москва

Брянск

Липецк

Воронеж

Белгород

Воронеж

NULL

Воронеж

BIRTHDAY

3/12/1982

1/12/1980

7/06/1979

8/12/1981

1/05/1981

5/11/1979

NULL

1/12/1981

5/08/1981

7/01/1980

UNIV ID

1010

22

10101014

1022

10

1.7. Учебная база д

анных

STUDENT_ID — числовой код, идентифицирующий студента,SURNAME — фамилия студента,NAME — имя студента,STIPEND — стипендия, которую получает студент,KURS — курс, на котором учится студент,CITY — город, в котором живет студент,BIRTHDAY — дата рождения студента,UNIV_ID— числовой код, идентифицирующий университет, в котором учится студент.


LECTURER (Преподаватель)

LECTURER ID

24

46

74

108

276

328

SURNAME

Колесников

Никонов

Лагутин

Струков

Николаев

Сорокин

NAME

Борис

Иван

Павел

Николай

Виктор

Андрей

CITY

Воронеж

Воронеж

Москва

Москва

Воронеж

Орел

UNIV ID

10

10

22

22

10

10

LECTURER_ID — ЧИСЛОВОЙ КОД, ИДСНТифиЦИруЮЩИЙ ПрСПО-

давателя,SURNAME — фамилия преподавателя,NAME — имя преподавателя,CITY — город, в котором живет преподаватель,UNIV_ID — идентификатор университета, в котором рабо-

тает преподаватель.

SUBJECT (Предмет обучения)

SUBJ ID

10

22

43

56

94

73

SUBJ NAME

Информатика

Физика

Математика

История

Английский

Физкультура

HOUR

56

34

56

34

56

34

SEMESTER

1

1

2

4

3

5

SUBJ_ID — идентификатор предмета обучения,SUBJ_NAME — наименование предмета обучения,HOUR — количество часов, отводимых на изучение предмета,SEMESTER — семестр, в котором изучается данный предмет.

1.7. Учебная база данных 21

UNIVERSITY (Университеты)

UNIV_ID

22

10

11

32

14

15

18

UN IV NAME

МГУ

ВГУ

НГУ

РГУ

БГУ

ТГУ

ВГМА

RATING

606

296

345

416

326

368

327

CITY

Москва

Воронеж

Новосибирск

Ростов

Белгород

Томск

Воронеж

UNIV_ID — идентификатор университета,UNIV_NAME — название университета,RATING — рейтинг университета,CITY — город, в котором расположен университет.

EXAM_MARKS (Экзаменационные оценки)

EXAM_ID

145

34

75

238

639

43

STUDENT ID

12

32

55

12

55

6

SUBJ ID

10

10

10

22

22

22

MARK

5

4

5

3

NULL

4

EXAM_DATE

12/01/2000

23/01/2000

05/01/2000

17/06/1999

22/06/1999

18/01/2000

EXAM_ID — идентификатор экзамена,STUDENT_ID — идентификатор студента,SUBJ_ID — идентификатор предмета обучения,MARK — экзаменационная оценка,EXAM DATE — дата экзамена.


SUBJ LECT (Учебные дисциплины преподавателей)

LECTURER _ID

24

46

74

108

276

328

SUBJJCD

24

46

74

108

276

328

LECTURER_ID — идентификатор преподавателя,SUBJ ID — идентификатор предмета обучения.

Вопросы1. Какие поля приведенных таблиц являются первичными ключами?2. Какие данные хранятся в столбце 2 таблицы «Предмет обучения»?3. Как по-другому называется строка? Столбец?4. Почему нельзя запросить для просмотра первые пять строк?

2 Выборка данных(оператор SELECT)

2.1. Простейшие SELECT-запросы

Оператор SELECT (выбрать) языка SQL является самым важ-ным и самым часто используемым оператором. Он предназначендля выборки информации из таблиц базы данных. Упрощенныйсинтаксис оператора SELECT выглядит следующим образом.

SELECT [DISTINCT] <список атрибутов>FROM <список таблиц>

[WHERE <условие выборки>]

[ORDER BY <список атрибутов>]

[GROUP BY <список атрибутов>]

[HAVING <условие>][UNION <выражение с оператором SELECT>J;

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

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

Обязательным ключевым словом в предложении-запросеSELECT является слово FROM (из). За ключевым словом FROMуказывается список разделенных запятыми имен таблиц, из ко-торых извлекается информация.

Например,SELECT NAME,SURNAME

FROM STUDENT;

Любой SQL-запрос должен заканчиваться символом «;» (точ-ка с запятой).

24 2. Выборка данных (оператор SELECT)

Приведенный запрос осуществляет выборку всех значенийПОЛеЙ NAME И SURNAME ИЗ Таблицы STUDENT.

Его результатом является таблица следующего вида:

NAME

Иван

Петр

Вадим

Борис

Ольга

Андрей

Павел

Артем

Антон

Вадим

SURNAME

Иванов

Петров

Сидоров

Кузнецов

Зайцева

Павлов

Котов

Лукин

Петров

Белкин

Порядок следования столбцов в этой таблице соответствуетпорядку полей NAME и SURNAME, указанному в запросе, а не ихпорядку во входной таблице STUDENT.

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

SELECT *

FROM STUDENT;

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

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

2.1. Простейшие SELECT-запросы 25

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

SELECT CITY FROM STUDENT;

Его результатом будет таблица:

CITY

Орел

Курск

Москва

Брянск

Липецк

Воронеж

Белгород

Воронеж

NULL

Воронеж

Видно, что в таблице встречаются одинаковые строки (вы-делены жирным шрифтом).

Для исключения из результата SELECT-запроса повторяющих-ся записей используется ключевое слово DISTINCT (отличный).Если запрос SELECT извлекает множество полей, то DISTINCT ис-ключает дубликаты строк, в которых значения всех выбранныхполей идентичны.

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

SELECT DISTINCT CITY

FROM STUDENT;

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


CITY

Орел

Курск

Москва

Брянск

Липецк

Воронеж

Белгород

NULL Ч

Ключевое слово ALL (все), в отличие от DISTINCT, оказыва-ет противоположное действие, то есть при его использованииповторяющиеся строки включаются в состав выходных данных.Режим, задаваемый ключевым словом ALL, действует по умол-чанию, поэтому в реальных запросах для этих целей оно прак-тически не используется.

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

ПримерНаписать запрос, выполняющий выборку имен (NAME) всех

студентов с фамилией (SURNAME) Петров, сведения о которыхнаходятся в таблице STUDENT. •

SELECT SURNAME, NAME

FROM STUDENT

WHERE SURNAME = 'Петров';

2.1. Простейшие SELECT-запросы 27

Результатом этого запроса будет таблица:

SURNAME

Петров

Петров

NAME

Петр

Антон

В задаваемых в предложении WHERE условиях могут использо-ваться операции сравнения, определяемые операторами = (рав-но), > (больше), < (меньше), >= (больше или равно), <— (мень-ше или равно), <> (не равно), а также логические операторыAND, OR И NOT.

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

SELECT NAME, SURNAME

FROM STUDENT

WHERE KURS = 3 AND STIPEND > 0;

Результат выполнения этого запроса имеет вид:

SURNAME

Петров

Лукин

NAME

Петр

Артем

Упражнения

1. Напишите запрос для вывода идентификатора (номера) предметаобучения, его наименования, семестра, в котором он читается,и количества отводимых на этот предмет часов для всех строк таб-лицы SUBJECT.

2. Напишите запрос, позволяющий вывести все строки таблицыEXAM_MARKS, в которых предмет обучения имеет номер (SUBJ_ID),равный 12.

3. Напишите запрос, выбирающий все данные из таблицы STUDENT,расположив столбцы таблицы в следующем порядке: KURS, SURNAME,NAME, STIPEND.


4. Напишите запрос SELECT, который выводит наименование пред-мета обучения (SUB JJSIAME) и количество часов (HOUR) для каждогопредмета (SUBJECT) в 4-м семестре (SEMESTER).

5. Напишите запрос, позволяющий получить из таблицы EXAM_MARKSзначения столбца MARK (экзаменационная оценка) для всех студен-тов, исключив из списка повторение одинаковых строк.

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

7. Напишите запрос, выбирающий данные о фамилии, имени и но-мере курса для студентов, получающих стипендию больше 140.

8. Напишите запрос, выполняющий выборку из таблицы SUBJECTназваний всех предметов обучения, на которые отводится более 30часов.

9. Напишите запрос, который выполняет вывод списка университе-тов, рейтинг которых превышает 300 баллов.

10. Напишите запрос к таблице STUDENT для вывода списка фамилий(SURNAME), имен (NAME)- и номера курса (KURS) всех студентов состипендией, большей или равной 100, и живущих в Воронеже.

11. Какие данные будут получены в результате выполнения запроса?

SELECT *

FROM STUDENT

WHERE (STIPEND < 100 OR

NOT (BIRTHDAY >= '10/03/1980'

AND STODENT_ID > 1003));

12. Какие данные будут получены в результате выполнения запроса?

SELECT *

FROM STUDENT

WHERE NOT ((BIRTHDAY = '10/03/1980' OR STIPEND > 100)

AND STUDENT_ID >= 1003);

2.2. Операторы IN, BETWEEN, LIKE, is NULL

При задании логического условия в предложении WHERE мо-гут быть использованы операторы IN, BETWEEN, LIKE, is NULL.

Операторы IN (равен любому из списка) и NOT IN (не ра-вен ни одному из списка) используются для сравнения прове-ряемого значения поля с заданным списком. Этот список зна-чений указывается в скобках справа от оператора IN.

2.2. Операторы IN, BETWEEN, LIKE, is NULL 29

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

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

ПримерыПолучить из таблицы EXAM_MARKS сведения о студентах,

имеющих экзаменационные оценки только 4 и 5.SELECT *

FROM EXAM_MARKS

WHERE MARK IN (4, 5 );

Получить сведения о студентах, не имеющих ни одной экза-менационной оценки, равной 4 и 5.

SELECT *

FROM EXAM_MARKS

WHERE MARK NOT IN (4, 5 );

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

Например, запрос на вывод записей о предметах, на изуче-ние которых отводится количество часов, находящееся в пре-делах между 30 и 40, имеет вид:

SELECT *FROM SUBJECTWHERE HOUR BETWEEN 30 AND 40;

Граничные значения, в данном случае значения 30 и 40,входят во множество значений, с которыми производится срав-нение. Оператор BETWEEN может использоваться как для число-вых, так и для символьных типов полей.

Оператор LIKE применим только к символьным полям ти-па CHAR или VARCHAR (см. раздел 1.5 «Типы данных SQL»).


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

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

• символ подчеркивания «_», указанный в шаблоне, определя-ет возможность наличия в указанном месте одного любогосимвола;

• символ «%» допускает присутствие в указанном месте прове-ряемой строки последовательности любых символов про-извольной длины.

ПримерНаписать запрос, выбирающий из таблицы STUDENT сведе-

ния о студентах, фамилии которых начинаются на букву «Р».

SELECT *

FROM STUDENT

WHERE SURNAME LIKE 'P%';

В случае необходимости включения в образец самих симво-лов «_» и «%» применяют так называемые escape-символы. Еслиescape-символ предшествует знаку «_» и «%», то эти знаки будутвосприниматься буквально. Например, можно задать образецпоиска с помощью следующего выражения:

LIKE '_\_P' ESCAPE 'V.

В этом выражении символ 'V с помощью ключевого словаESCAPE объявляется escape-символом. Первый символ «_» в задан-ном шаблоне поиска '_\_Р' будет соответствовать, как и ранее,любому символу в проверяемой строке. Однако второй символ«_», следующий после символа 'V, объявленного escape-симво-лом, уже будет интерпретироваться буквально как обычныйсимвол, так же как и символ 'Р' в заданном шаблоне.

Обращаем внимание на то, что рассмотренные выше опера-торы сравнения «=, <, >, <=, >=, <>» и операторы IN,BETWEEN и LIKE ни в коем случае нельзя использовать для про-

2.3. Преобразование вывода и встроенные функции 31

верки содержимого поля на наличие в нем пустого значенияNULL (см. раздел 1.5 «Типы данных SQL»). Для этих целей пред-назначены специальные операторы is NULL (является пустым)и IS NOT NULL (является не пустым).


\. Напишите запрос на вывод находящихся в таблице EXAM_MARKSномеров предметов обучения, экзамены по которым сдавались ме-жду 10 и 20 января 1999 года.

2. Напишите запрос, выбирающий данные обо всех предметах обуче-ния, экзамены по которым сданы студентами, имеющими иденти-фикаторы 12 и 32.

3. Напишите запрос на вывод названий предметов обучения, начина-ющихся на букву «И».

4. Напишите запрос, выбирающий сведения о студентах, у которыхимена начинаются на буквы «И» или «С».

5. Напишите запрос для выбора из таблицы EXAM_MARKS записей,в которых отсутствуют значения оценок (поле MARK).

6. Напишите запрос на вывод из таблицы EXAM_MARKS записей, име-ющих в поле MARK значения оценок.

2.3. Преобразование вывода и встроенные функции

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

2.3.1. Числовые, символьные и строковые константы

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


Если в запросе вместо спецификации столбца SQL обнару-живает число, то оно интерпретируется как числовая константа.

Символьные константы должны указываться в одинарных

кавычках. Если одинарная кавычка должна выводиться какчасть строковой константы, то ее нужно предварить другой оди-

нарной кавычкой.Например, результатом выполнения запроса

SELECT 'ФаМИЛИЯ', SURNAME, 'Имя', NAME, 100

FROM STUDENT;

является таблица следующего вида:

Фамилия

Фамилия

Фамилия

Фамилия

Фамилия

Фамилия

Фамилия

Фамилия

Фамилия

Фамилия

SURNAME

Иванов

Петров

Сидоров

Кузнецов

Зайцева

Павлов

Котов

Лукин

Петров

Белкин

Имя

Имя

Имя

Имя

Имя

Имя

Имя

Имя

Имя

Имя

NAME

Иван

Петр

Вадим

Борис

Ольга

Андрей

Павел

Артем

Антон

Вадим

100

100

100

100

100

100

100

100

100

100

2.3.2. Арифметические операции для преобразованиячисловых данных

• Унарный (одиночный) оператор «—» (знак минус) изменяетзнак числового значения, перед которым он указан, на про-тивоположный.


• Бинарные операторы «+», «—», «*» и «/» предоставляют воз-можность выполнения арифметических операций сложения,вычитания, умножения и деления.Например, результат запроса

SELECT SURNAME, NAME, STIPEND, -(STIPEND*KURS)/2

FROM STUDENT


выглядит следующим образом:

SURNAME

Сидоров

Петров

NAME

Вадим

Антон

STIPEND

150

200

KURS

4

4

-300

-400

2.3.3. Операция конкатенации строк

Операция конкатенации «||» позволяет соединять («склеи-вать») значения двух или более столбцов символьного типа илисимвольных констант в одну строку.

Эта операция имеет синтаксис

<значимое символьное выражение > {||} <значимое символьное выра-жение>.

Например:

SELECT SURNAME ||'_'|| NAME, STIPEND

FROM STUDENT


Результат запроса будет выглядеть следующим образом:

Сидоров_Вадим

Петров_Антон

STIPEND

150

200

2 Зак 444


2.3.4. Функции преобразования символов в строке

• LOWER — перевод в строчные символы (нижний регистр)LOWER (<строка>)

• UPPER — перевод в прописные символы (верхний регистр)

UPPER (<строка>)

• INITCAP — перевод первой буквы каждого слова строкив прописную (заглавную)

INITCAP (<строка>)

Например:

SELECT LOWER (SURNAME), UPPER (NAME)

FROM STUDENT


Результат запроса будет выглядеть следующим образом:

- SURNAME

сидоров

петров

NAME

ВАДИМ

АНТОН

2.3.5. Строковые функции

• LPAD — дополнение строки слева

LPAD (<строка>,<длина>[,<подстрока>])

• <строка> дополняется слева заданной в <подстроке> по-следовательностью символов до указанной <длины> (воз-можно, с повторением последовательности);

• если <подстрока> не указана, то по умолчанию <строка>дополняется пробелами;

• если <длина> меньше длины <строки>, то исходная<строка> усекается слева до заданной <длины>.

• RPAD — дополнение строки справа

RPAD (<строка>,<длина>[,<подстрока>])


• <строка> дополняется справа заданной в <подстроке> по-следовательностью символов до указанной <длины> (воз-можно, с повторением последовательности);

• если <подстрока> не указана, то по умолчанию <строка>дополняется пробелами;

• если <длина> меньше длины <строки>, то исходная <строка>усекается справа до заданной <длины >.

• LTRIM — удаление левых граничных символов

LTRIM (<строка>[,<подстрока>])

• из <строки> удаляются слева символы, указанные в <под-строке>;

• если < подстрока > не указана, по умолчанию удаляютсяпробелы;

• в <строку> справа добавляется столько пробелов, сколькосимволов слева было удалено, то есть длина <строки> ос-тается неизменной.

• RTRIM — удаление правых граничных символов

RTRIM (<строка>[,<подстрока>])

• из <строки> удаляются справа символы, указанные в <под-строке>;

• если <подстрока> не указана, по умолчанию удаляютсяпробелы;

• в <строку> слева добавляется столько пробелов, сколькосимволов справа было удалено, то есть длина <строки> ос-тается неизменной.

Функции LTRIM и RTRIM рекомендуется использовать при на-писании условных выражений, в которых сравниваются текстовыестроки. Дело в том, что наличие начальных или конечных пробе-лов в сравниваемых операндах может исказить результат сравнения.

Например, константы ' ААА' и 'ААА ' не равны друг другу.• SUBSTR — выделение подстроки

SUBSTR (<строка>,<начало>[,<количество>])

• из <строки> выбирается заданное <количество> символов,начиная с указанной параметром <начало> позициив строке;


• если <количество> не задано, символы выбираются с <на-чала> и до конца <строки>;

• возвращается подстрока, содержащая число символов, за-данное параметром <количество>, либо число символов отпозиции, заданной параметром <начало> до конца строки;

• если указанное <начало> превосходит длину <строки>, товозвращается строка, состоящая из пробелов. Длина этойстроки будет равна заданному <количеству> или исходнойдлине <строки> (при не заданном <количестве>).

• INSTR — поиск подстрокиINSTR (<строка>,<подстрока>[,<начало поиска>

[,<номер вхождения>]])• <начало поиска> задает начальную позицию в строке для

поиска <подстроки>. Если не задано, то по умолчаниюпринимается значение 1;

• <номер вхождения> задает порядковый номер искомойподстроки. Если не задан, то по умолчанию принимаетсязначение 1;

• значимые выражения в <начале поиска> или в <номеревхождения > должны иметь беззнаковый целый тип илиприводиться к этому типу;

• тип возвращаемого значения — INT;функция возвращает позицию найденной подстроки.

• LENGTH — определение длины строки

ыагстн(<строка>)

• длина <строки>, тип возвращаемого значения — ЮТ;• функция возвращает NULL, если <строка> имеет NULL-зна-

чение.

Примеры запросов, использующих строковые функцииРезультат запроса

SELECT LPAD (SURNAME, 10, '@'), RPAD (NAME, 10, '$')

FROM STUDENT


будет выглядеть следующим образом:


@@@@Петров

@@@@Павлов

@@@@@Дукин

Петр$$$$$$

Андрей$$$$

Артем$$$$$

А запрос

SELECT SUBSTR(NAME, 1, 1) Ц ' . ' || SURNAME, CITY, LENGTH (CITY,)

FROM STUDENT

WHERE KURS IN(2, 3, 4)AND STIPEND > 0;

выдаст результат:

П. Петров

С. Сидоров

О. Зайцева

А. Лукин

А. Петров

CITY

Курск

Москва

Липецк

Воронеж

NULL

5

6

6

7

NULL

2.3.6. Функции работы с числами

• ABS — абсолютное значение

ABS (<значимое числовое выражение>)

• FLOOR — урезает значение числа с плавающей точкой до наи-большего целого, не превосходящего заданное число

FLOOR (<значимое числовое выражение>)

• CEIL — самое малое целое, равное или большее заданного числа

CEIL (<значимое числовое выражение>)


• Функция округления — ROUNDROUND (<значимое числовое выражение>,<точность>)

аргумент <точность> задает точность округления (см. примерниже)

• Функция усечения — TRUNCTRUNC (оначимое числовое выражение>,<точность>)

• Тригонометрические функции — cos, SIN, TAN

cos (<значимое числовое выражение>)

SIN (<значимое числовое выражение>)

TAN (<значимое числовое выражение>)

« Гиперболические функции — COSH, SINH, TANH

COSH (<значимое числовое выражение>)

SINH (<значимое числовое выражение>)

TANH (<значимое числовое выражение >)

• Экспоненциальная функция — ЕХР

ЕХР (<значимое числовое выражение>)

• Логарифмические функции — LN, LOG

ш (<значимое числовое выражение>)

LOG (<значимое числовое выражение>)

• Функция возведения в степень — POWER

POWER (<значимое числовое выражение>,<экспонента>)

• Определение знака числа — SIGN

SIGN (<значимое числовое выражение>)

• Вычисление квадратного корня — SQRT

SQRT (<значимое числовое выражение>)

ПримерЗапрос

SELECT UNIVJffiME, RATING, ROUND (RATING, -1), TRDNC (RATING,

FROM UNIVERSITY;

вернет результат:


UN IV NAME

МГУ

ВГУ

НГУ

РГУ

БГУ

ТГУ

ВГМА

RATING

606

296

345

416

326

368

327

610

300

350

420

330

370

330

600

290

340

410

320

360

320

2.3.7. Функции преобразования значений

• Преобразование в символьную строку — TO_CHAR

TO_CHAR (<значимое выражение>[,<символьный формат>])

• <значимое выражение > — числовое значение или значениетипа дата-время;

• для числовых значений <символьный формат> должениметь синтаксис [S]9[9][,9[9]], где S — представление зна-ка числа (при отсутствии предполагается без отображениязнака), 9 — представление цифр-знаков числового значе-ния (для каждого знакоместа). Символьный формат опре-деляет вид отображения чисел. По умолчанию для число-вых значений используется формат '999999.99';

• для значений типа дата-время <символьный формат>имеет вид (то есть вид отображения значений даты и вре-мени):

— в части даты

'DD-Mon-YY''DD-Mon-YYYY''MM/DD/YY''MM/DD/YYYY1

'DD.MM.YY''DD.MM.YYYY'


— в части времени

'НН24'ИН24.Ш''HH24:MI:SS''HH24:MI:SS.FF'

ще: НН24 — часы в диапазоне от 0 до 24R/FI — минутыSS — секундыFF — тики (сотые доли секунды)

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

Возвращаемое значение — символьное представление Зна-чимого выражения> в соответствии с заданным ^символьнымформатом> преобразования.

• Преобразование из символьного значения в числовое —TO_NUMBER

TO_NUMBER (<значимое символьное выражение >)

При этом <значимое символьное выражение> должно зада-вать символьное значение числового типа.

• Преобразование символьной строки в дату — TO_DATE

TCMDATE (<значимое символьное выражение >[,<символьный формат>])

• <значимое символьное выражение> должно задавать сим-вольное значение типа дата-время;

• <символьный формат> должен описывать представлениезначения типа дата-время в <значимом символьном выра-жении>. Допустимые форматы (в том числе и формат поумолчанию) приведены выше.

Возвращаемое значение — <значимое символьное выраже-ние> во внутреннем представлении. Тип возвращаемого значе-ния — DATE.

Над значениями типа DATE разрешены следующие операции:

• бинарная операция сложения;

• бинарная операция вычитания.


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

Например:• при добавлении к дате '22.05.1998' пяти лет получится дата

'22.05.2003';• при добавлении к этой же дате девяти месяцев получится да-

та '22.02.1998';• при добавлении 10 дней получим '01.06.1998'.

При сложении двух полных дат, например, '22.05.1998'и '01.12.2000', результат непредсказуем.

ПримерЗапрос

SELECT SURNAME,NAME,BIRTHDAY,

TO_CHAR (ВIRTHDAY, 'DD-MON-YYYY'),

TO_CHAR (BIRTHDAY, 'DD.MM.YY')

FROM STUDENT;

вернет результат:

SURNAME

Иванов

Петров

Сидоров

Кузнецов

Зайцева

Павлов

Котов

Лукин

Петров

Белкин

NAME

Иван

Петр

Вадим

Борис

Ольга

Андрей

Павел

Артем

Антон

Вадим

BIRTHDAY

3/12/1982

1/12/1980

7/06/1979

8/12/1981

1/05/1981

5/11/1979

NULL

1/12/1981

5/08/1981

7/01/1980

З-дек-1982

1 -дек- 1980

7-июн-1979

8-дек-1981

1-МЗЙ-1981

5-ноя-1979

NULL

1 -дек- 1981

5-авг-1981

7-янв-1980

3.12.82

1.12.80

7.06.79

8.12.81

1.05.81

5.11.79

NULL

1.12.81

5.08.81

7.01.80

42 2. Выборкаданных (оператор SELECT)

Функция CAST является средством явного преобразования дан-ных из одного типа в другой. Синтаксис этой команды имеет вид

сАзКзначимое выражение> AS <тип данных>

• <значимое выражение> должно иметь числовой или сим-вольный тип языка SQL (возможно, с указанием длины, точ-ности и масштаба) или быть NULL-значением;

• любое числовое выражение может быть явно преобразованов любой другой числовой тип;

• символьное выражение может быть преобразовано в любойчисловой тип. При этом в результате символьного выраже-ния отсекаются начальные и конечные пробелы, а остальныесимволы преобразуются в числовое значение по правиламязыка SQL;

• если явно заданная длина символьного типа недостаточнаи преобразованное значение не размещается в нем, то ре-зультативное значение усекается справа;

• возможно явное преобразование символьного типа в сим-вольный с другой длиной. Если длина результата большедлины аргумента, то значение дополняется пробелами; еслименьше, то усекается;

• NULL-значение преобразуется в NULL-значение соответству-ющего типа;

• числовое выражение может быть преобразовано в символь-ный тип.

ПримерSELECT CAST STUDENT_JD AS CHAR(10)

FROM STUDENT;


l. Составьте запрос для таблицы STUDENT таким образом, чтобы вы-ходная таблица содержала один столбец, содержащий последова-тельность разделенных символом «;» (точка с запятой) значенийвсех столбцов этой таблицы, и при этом текстовые значения дол-жны отображаться прописными символами (верхний регистр),то есть быть представленными в следующем виде: 10;КУЗНЕ-ЦОВ;БОРИС;0;БРЯНСК;8/12/1981;10.

2.4. Агрегирование и групповые функции 43

2. Составьте запрос для таблицы STUDENT таким образом, чтобы вы-ходная таблица содержала всего один столбец в следующем виде:Б.КУЗНЕЦОВ;местожительства-БРЯНСК;родился-8.12.81.

3. Составьте запрос для таблицы STUDENT таким образом, чтобы вы-ходная таблица содержала всего один столбец в следующем виде:б.кузнецов;место жительства-брянск;родился:8-дек-1981.

4. Составьте запрос для таблицы STUDENT таким образом, чтобы вы-ходная таблица содержала всего один столбец в следующем виде:Борис Кузнецов родился в 1981 году.

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

6. То же, что и в задаче 4, но только для студентов 1, 2 и 4-го курсови таким образом, чтобы фамилии и имена были выведены пропис-ными буквами.

7. Составьте запрос для таблицы UNIVERSITY таким образом, чтобывыходная таблица содержала всего один столбец в следующем ви-де: Код-10;ВГУ-г. ВОРОНЕЖ;Рейтинг=296.

8. То же, что и в задаче 7, но значения рейтинга требуется округлитьдо первого знака (например, значение 382 округляется до 400).

2.4. Агрегирование и групповые функции

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

• COUNT определяет количество строк или значений поля, вы-бранных посредством запроса и не являющихся NULL-значе-ниями;

• SUM вычисляет арифметическую сумму всех выбранных зна-чений данного поля;

• AVG вычисляет среднее значение для всех выбранных значе-ний данного поля;

• МАХ вычисляет наибольшее из всех выбранных значенийданного поля;

• MIN вычисляет наименьшее из всех выбранных значенийданного поля.


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

Функция AVG предназначена для подсчета среднего значе-ния поля на множестве записей таблицы.

Например, для определения среднего значения поля MARK(оценки) по всем записям таблицы EXAM_MARKS можно исполь-зовать запрос с функцией AVG следующего вида:

SELKCT AVERAGE (MARK)

PROM EXAM_MARKS;

Для подсчета общего количества строк в таблице следует ис-пользовать функцию COUNT со звездочкой.

SELECT COUNT(*)

FROM EXAM_MARKS;

Аргументы DISTINCT и ALL позволяют, соответственно,исключать и включать дубликаты обрабатываемых функциейCOUNT значений, при этом необходимо учитывать, что при ис-пользовании опции ALL значения NULL все равно не войдутв число подсчитываемых значений.

SELECT COUNT(DISTINCT SUBJ_IDj

FROM SUBJECT;

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

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

SELECT STUDENT_ID, MAX(MARKj

FROM EXAM_MARKS

GROUP BY STUDENT_ID;

Выбираемые из таблицы EXAM_MARKS записи группируют-ся по значениям поля STUDENT__ID, указанного в предложенииGROUP BY, и для каждой группы находится максимальное зна-

2.4. Агрегирование и групповые функции 45

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

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

SELECT STUDENT_ID, SUBJ_ID, MAX (MARKj

FROM EXAM_MARKS

GROUP BY STUDENT_ID, SUBJ_ID;

В этом случае строки вначале группируются по значениямпервого столбца, а внутри этих групп — в подгруппы по значе-ниям второго столбца. Таким образом, GROUP BY не только уста-навливает столбцы, по которым осуществляется группирова-ние, но и указывает порядок разбиения столбцов на группы.

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

При необходимости часть сформированных с помощьюGROUP BY групп может быть исключена с помощью предложе-ния HAVING.

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

SELECT SUBJ_NAME, MAX(HOURj

FROM SUBJECT

GROUP BY SUBJ_NAME

HAVING MAX (HOURj >= 72;

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

Упражнения1. Напишите запрос для подсчета количества студентов, сдававших

экзамен по предмету обучения с идентификатором, равным 20.


2. Напишите запрос, который позволяет подсчитать в таблицеEXAM_MARKS количество различных предметов обучения.

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

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

5. Напишите запрос, выполняющий вывод фамилии первого в алфа-витном порядке (по фамилии) студента, фамилия которого начи-нается на букву «И».

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

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

8. Напишите запрос для получения среднего балла для каждого курсапо каждому предмету.

9. Напишите запрос для получения среднего балла для каждого сту-дента.

10. Напишите запрос для получения среднего балла для каждого экза-мена.

11. Напишите запрос для определения количества студентов, сдавав-ших каждый экзамен.

12. Напишите запрос для определения количества изучаемых предме-тов на каждом курсе.

2.5. Пустые значения (NULL) в агрегирующихфункциях

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

2.5.1. Влияние NULL-значений в функции COUNT

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

2.6. Результат действия трехзначных условных операторов 47

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

Если бы механизм NULL не был доступен, то неприменимыеи отсутствующие значения пришлось бы исключать с помощьюКОНСТРУКЦИИ WHERE.

Поведение функции COUNT(*) не зависит от пустых значе-ний. Она возвратит общее количество строк в таблице.

2.5.2. Влияние NULL-значений в функции AVG

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

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

2.6. Результат действия трехзначныхусловных операторов

Условные операторы при отсутствии пустых значений воз-вращают либо TRUE (истина), либо FALSE (ложь). Если жев столбце присутствуют пустые значения, то может быть возвра-щено и третье значение: UNKNOWN (неизвестно). В этой схеме,например, условие WHERE А = 2, где А — имя столбца, значениякоторого могут быть неизвестны, при А = 2 будет соответство-вать TRUE, при А = 4 в результате будет получено значениеFALSE, а при отсутствующем значении А (NULL-значение) ре-зультат будет UNKNOWN. Пустые значения оказывают влияние наиспользование логических операторов NOT, AND и OR.


Оператор NOTОбычный унарный оператор NOT обращает оценку TRUE

в FALSE и наоборот. Однако NOT NULL по прежнему будет возвра-щать пустое значение NULL. При этом следует отличать случай NOTNULL от условия IS NOT NULL, которое является противополож-ностью IS NULL, отделяя известные значения от неизвестных.

Оператор AND• Если результат двух условий, объединенных оператором

AND, известен, то применяются правила булевой логики, тоесть при обоих утверждениях TRUE составное утверждениетакже будет TRUE. Если же хотя бы одно из двух утвержде-ний будет FALSE, то составное утверждение будет FALSE.

• Если результат одного из утверждений неизвестен, а другойоценивается как TRUE, то состояние неизвестного утвержде-ния является определяющим, и, следовательно, итоговыйрезультат также неизвестен.

• Если результат одного из утверждений неизвестен, а другойоценивается как FALSE, итоговый результат будет FALSE.

• Если результат обоих утверждений неизвестен, то результаттакже остается неизвестным.

Оператор OR• Если результат двух условий, объединенных оператором OR,

известен, то применяются правила булевой логики, а имен-но: если хотя бы одно из двух утверждений соответствуетTRUE, то и составное утверждение будет TRUE, если оба ут-верждения оцениваются как FALSE, то составное утвержде-ние будет FALSE.

• Если результат одного из утверждений неизвестен, а другойоценивается как TRUE, итоговый результат будет TRUE.

• Если результат одного из утверждений неизвестен, а другойоценивается как FALSE, то состояние неизвестного утвер-ждения имеет определяющее значение. Следовательно, ито-говый результат также неизвестен.

• Если результат обоих утверждений неизвестен, то результаттакже остается неизвестным.

2.7. Упорядочение выходных полей (ORDER BY) 49

ПримечаниеОтсутствующие (NULL) значения целесообразно исполь-

зовать в столбцах, предназначенных для агрегирования, что-бы извлечь преимущества из способа обработки пустых значе-ний в функциях COUNT и AVERAGE. Практически во всехостальных случаях пустых значений следует избегать, так какпри их наличии существенно усложняется корректное постро-ение условий отбора, приводя иногда к непредсказуемым ре-зультатам выборки. Для индикации же отсутствующих, неп-рименимых или по какой-то причине неизвестных данныхможно использовать значения по умолчанию, устанавливае-мые заранее (например, с помощью команды CREATE TABLE(раздел 4.1)).

2.7. Упорядочение выходных полей (ORDER BY)

Как уже отмечалось, записи в таблицах реляционной базыданных не упорядочены. Однако данные, выводимые в резуль-тате выполнения запроса, могут быть упорядочены. Для этогоиспользуется оператор ORDER BY, который позволяет упорядо-чивать выводимые записи в соответствии со значениями одногоили нескольких выбранных столбцов. При этом можно задатьвозрастающую (ASC) или убывающую (DESC) последователь-ность сортировки для каждого из столбцов. По умолчанию при-нята возрастающая последовательность сортировки.

Запрос, позволяющий выбрать все данные из таблицы пред-метов обучения SUBJECT с упорядочением по наименованиямпредметов, выглядит следующим образом:

SELECT *

FROM SUBJECT

ORDER BY SUBJ_NAME;

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

SELECT *

FROM SUBJECT

ORDER BY SUBJ NAME DESC;


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

SELECT *

FROM SUBJECT

ORDER BY SEMESTER, SUBJ_NAME;

Предложение ORDER BY может использоваться с GROUP BY дляупорядочения групп записей. При этом оператор ORDER BY в за-просе всегда должен быть последним.

SELECT SUBJ_NAME, SEMESTER, MAX(HOUR)

FROM SUBJECT

GROUP BY SEMESTER, SUBJ_NAME

ORDER BY SEMESTER;

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

SELECT SUBJ_ID, SEMESTER

FROM SUBJECT

ORDER BY 2 DESC;

В этом запросе выводимые записи будут упорядочены поПОЛЮ SEMESTR.

Если в поле, которое используется для упорядочения, су-ществуют NULL-значения, то все они размещаются в конце илипредшествуют всем остальным значениям этого поля.


1. Предположим, что стипендия всем студентам увеличена на 20%.Напишите запрос к таблице STUDENT, выполняющий вывод номе-ра студента, фамилию студента и величину увеличенной стипен-дии. Выходные данные упорядочить: а) по значению последнегостолбца (величине стипендии); б) в алфавитном порядке фамилийстудентов.

2.8. Вложенные подзапросы 51

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

3. Напишите запрос, выполняющий вывод списка предметов обуче-ния в порядке а) убывания семестров и б) возрастания отводимыхна предмет часов. Поле семестра в выходных данных должно бытьпервым, за ним должны следовать имя предмета обучения и иден-тификатор предмета.

4. Напишите запрос, который выполняет вывод суммы баллов всехстудентов для каждой даты сдачи экзаменов и представляет резуль-таты в порядке убывания этих сумм.

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

2.8. Вложенные подзапросы

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

SELECT *

FROM EXAM_MARKS

WHERE STUDENT_ID =

(SELECT STUDENT_ID

FROM STUDENT SURNAME = 'Петров');

Как работает запрос SQL со связанным подзапросом?• Выбирается строка из таблицы, имя которой указано во

внешнем запросе.• Выполняется подзапрос и полученное значение применяется

для анализа этой строки в условии предложения WHEREвнешнего запроса.

• По результату оценки этого условия принимается решениео включении или не включении строки в состав выходныхданных.


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

прос корректен только в том случае, если в результате выпол-нения указанного в скобках подзапроса возвращается единст-венное значение. Если в результате выполнения подзапроса бу-дет возвращено несколько значений, то этот подзапрос будетошибочным. В данном примере это произойдет, если в табли-це STUDENT будет несколько записей со значениями поляSURNAME = 'Петров'.

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

Оператор IN также широко применяется в подзапросах. Онзадает список значений, с которыми сравниваются другие зна-чения для определения истинности задаваемого этим операто-ром предиката.

Данные обо всех оценках (таблица EXAM_MARKS) студентовиз Воронежа можно выбрать с помощью следующего запроса:

SELECT *

FROM EXAM_MARKS

WHERE STUDENT_ID IN

(SELECT STUDENT_ID

FROM STUDENT

WHERE CITY = 'Воронеж');

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

SELECT COONT(DISTINCT SUBJ_IDj, MARK

FROM EXAM_MARKS

GROUP BY MARK

HAVING MARK >

2.9. Формирование связанных подзапросов 53

(SELECT AVG(MARK)

FROM EXAM_MARKS

WHERE STUDENT_ID = 301);

2.9. Формирование связанных подзапросов

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

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

SELECT *

FROM SUBJECT SU

WHERE '20/01/1999' IN

(SELECT EXAM_DATE

PROM EXAM_MARKS EX

WHERE SU.SUBJ_ID = EX.SUBJ_ID);

В некоторых СУБД для выполнения этого запроса можетпотребоваться преобразование значения даты в символьныйтип. В приведенном запросе su и ЕХ являются псевдонимами(алиасами), то есть специально вводимыми именами, которыемогут быть использованы в данном запросе вместо настоящихимен. В приведенном примере они используются вместо иментаблиц SUBJECT и EXAM_MARKS.

Эту же задачу можно решить с помощью операции соедине-ния таблиц:

SELECT DISTINCT SU.SUBJ_ID, SUBJJJAME, HOUR, SEMESTER

PROM SUBJECT FIRST,EXAM_MARKS SECOND

WHERE FIRST.SUBJ_ID = SECOND.SUBJ_ID

AND SECOND.EXAM_DATE = '20/01/1999';

В этом выражении алиасами таблиц являются имена FIRSTИ SECOND.


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

SELECT DISTINCT STUDENT_ID,SURNAME, STIPEND

FROM STUDENT El

WHERE STIPEND >

(SELECT AVG(STIPEND)

FROM STUDENT E2

WHERE El.KURS = E2.KURS);

Тот же результат можно получить с помощью следующегозапроса:

SELECT DISTINCT STUDENT_ID, SURNAME,STIPEND

FROM STUDENT El,

(SELECT KURS, AVG (STIPENDj AS AVG_STIPEND

FROM STUDENT E2

GROUP BY E2.KURS) E3

WHERE El.STIPEND > AVG_STIPEND AND El.KURS=E3.KURS;

Обратите внимание — второй запрос будет выполнен гораздобыстрее. Дело в том, что в первом варианте запроса агрегирую-щая функция AVG выполняется над таблицей, указанной в под-запросе, для каждой строки внешнего запроса. В другом вари-анте вторая таблица (алиас Е2) обрабатывается агрегирующейфункцией один раз, в результате чего формируется вспомогатель-ная таблица (в запросе она имеет алиас ЕЗ), со строками которойзатем соединяются строки первой таблицы (алиас Е1). Следуетиметь в виду, что реальное время выполнения запроса в большойстепени зависит от оптимизатора запросов конкретной СУБД.

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

В разделе 2.4 указывалось, что предложение GROUP BY позво-ляет группировать выводимые SELECT-запросом записи по зна-чению некоторого поля. Использование предложения HAVING

2.10. Связанные подзапросы в HAVING 55

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

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

SELECT EXAM_DATE, SUM(MARK)

FROM EXAM_MARKS A

GROUP BY EXAM_DATE

HAVING 10 <

(SELECT COUNT(MARK)

FROM EXAM_MARKS В

WHERE A.EXAM_DATE = В.EXAM_DATE);

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


1. Напишите запрос с подзапросом для получения данных обо всехоценках студента с фамилией «Иванов». Предположим, что егоперсональный номер неизвестен. Всегда ли такой запрос будеткорректным?

2. Напишите запрос, выбирающий данные об именах всех студентов,имеющих по предмету с идентификатором 101 балл выше общегосреднего балла.

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

4. Напишите запрос, выполняющий вывод количества предметов, покоторым экзаменовался каждый студент, сдававший более 20 пред-метов.

5. Напишите команду SELECT, использующую связанные подзапро-сы и выполняющую вывод имен и идентификаторов студентов,


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

6. Напишите запрос, который позволяет вывести имена и идентифи-каторы всех студентов, для которых точно известно, что они проживают в городе, где нет ни одного университета.

7. Напишите два запроса, которые позволяют вывести имена и идеитификаторы всех студентов, для которых точно известно, что онпроживают не в том городе, где расположен их университет. Одизапрос с использованием соединения, а другой — с использованием связанного подзапроса.

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

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

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

SELECT DISTINCT STUDENT_ID

FROM EXAM_MARKS A

WHERE EXISTS

(SELECT *

FROM EXAM_MARKS В

WHERE MARK < 3

AND B.STUDENT_ID = A.STUDENT_ID);

При использовании связанных подзапросов предложениеEXISTS анализирует каждую строку таблицы, на которую имеет-ся ссылка во внешнем запросе. Главный запрос получает стро-ки-кандидаты на проверку условия. Для каждой строки-канди-дата выполняется подзапрос. Как только подзапрос находит

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

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

Например, требуется получить идентификаторы предме-тов обучения, экзамены по которым сдавались не одним, а не-сколькими студентами:

SELECT DISTINCT SUBJ_ID

FROM EXAM_MARKS A

WHERE EXISTS

(SELECT *

FROM EXAM_MARKS В

WHERE A.SUBJ__ID = B.SUBJ_ID

AND A.STUDENT_ID < > В.STUDENT_ID);

Часто EXISTS применяется с оператором NOT (по-русскиNOT EXISTS интерпретируется, как «не существует»). Еслипредыдущий запрос сформулировать следующим образом —найти идентификаторы предметов обучения, которые сдавалисьодним, и только одним студентом (другими словами, для кото-рых не существует другого сдававшего студента), то достаточнопросто поставить NOT перед EXISTS.

Следует иметь в виду, что в подзапросе, указываемом в опе-раторе EXISTS, нельзя использовать агрегирующие функции.

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

I предмета.

SELECT *

FROM STUDENT FIRST

WHERE EXISTS

(SELECT SUBJ_ID

FROM EXAM_MARKS SECOND

GROUP BY SUBJ_ID

HAVING COUNT (SUBJ_IDj > 1

WHERE FIRST.STUDENT ID = SECOND.STUDENT ID);



1. Напишите запрос с EXISTS, позволяющий вывести данные обовсех студентах, обучающихся в вузах, которые имеют рейтинг вы-ше 300

2. Напишите предыдущий запрос, используя соединения.3. Напишите запрос с EXISTS, выбирающий сведения обо всех сту-

дентах, для которых в том же городе, где живет студент, существу-ют университеты, в которых он не учится.

4. Напишите запрос, выбирающий из таблицы SUBJECT данные о на-званиях предметов обучения, экзамены по которым сданы более чемодним студентом.

2.12. Операторы сравнения с множествомзначений IN, ANY, All

Операторы сравнения с множеством значений имеют сле-дующий смысл.

IN

NOT IN

= ANY

> ANY, > = ANY

< ANY, < = ANY

= ALL

> ALL, > = ALL

Равно любому из значений, полученных во вну-треннем запросе.

Не равно ни одному из значений, полученныхво внутреннем запросе.

То же, что и IN, Соответствует логическомуоператору OR.

Больше, чем (либо больше или равно) любое по-лученное число. Эквивалентно > или > = длясамого меньшего полученного числа.

Меньше, чем (либо меньше или равно) любоеполученное число. Эквивалент < или < = длясамого большего полученного числа.

Равно всем полученным значениям. Эквива-лентно логическому оператору AND

Больше, чем (либо больше или равно) все полу-ченные числа. Эквивалент > или > = для самогобольшего полученного числа.

2.12. Операторы сравнения с множеством значений IN, ANY, All 59

< ALL, < = ALL Меньше, чем (либо меньше или равно) все полу-ченные числа. Эквивалентно < или < = самогоменьшего полученного числа.

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

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

расположен университет, в котором они учатся.

SELECT *

FROM STUDENT S

WHERE CITY = ANY

(SELECT CITY

FROM UNIVERSITY U

WHERE U.UNIV_ID = S.UNIV_ID);

Другой вариант этого запроса:

SELECT *

FROM STUDENT S

WHERE CITY IN

(SELECT CITY

FROM UNIVERSITY U

WHERE U.UNIV_ID = S . UNIV_ID);

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

SELECT DISTINCT STUDENT_ID

FROM EXAM_MARKS

WHERE MARK > ANY

(SELECT MARK

FROM EXAM_MARKS

WHERE EXAM DATE = '06/10/1999');


Оператор ALL, как правило, эффективно используется с нера-венствами, а не с равенствами, поскольку значение равно всем,которое должно получиться в этом случае в результате выполненияподзапроса, может иметь место, только если все результаты иден-тичны. Такая ситуация практически не может быть реализована,так как если подзапрос генерирует множество различных значе-ний, то никакое одно значение не может быть равно сразу всемзначениям в обычном смысле. В SQL выражение < > ALL реаль-но означает не равно ни одному из результатов подзапроса.

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

SELECT *

FROM UNIVERSITY

WHERE RATING > ALL

(SELECT RATING

FROM UNIVERSITY

WHERE CITY = 'Воронеж') ;

В этом запросе вместо ALL можно использовать ANY (про-анализируйте, как в этом случае изменится смысл приведенно-го запроса):

SELECT *

FROM UNIVERSITY

WHERE NOT RATING > ANY

(SELECT RATING

FROM UNIVERSITY

WHERE CITY = 'Воронеж');

2.13. Особенности применения операторов ANY, ALL,EXISTS при обработке пустых значений (NULL)

Необходимо иметь в виду, что при обработке NULL-значе-ний следует учитывать различие реакции на них операторовEXISTS, ANY И ALL.

2.13. Особенности применения операторов ANY, ALL, EXISTS 61

Когда правильный подзапрос не генерирует никаких выход-ных данных, оператор ALL автоматически принимает значениеистина, а оператор ANY — значение ложь.

ЗапросSELECT *

FROM UNIVERSITYWHERE RATING > ANY

(SELECT RATINGFROM UNIVERSITYWHERE CITY = 'New York');

не генерирует выходных данных (подразумевается, что в базе нетданных об университетах города New York), в то время как запрос

SKLECT *FROM UNIVERSITYWHERE RATING > ALL

(SELECT RATINGFROM UNIVERSITYWHERE CITY = 'New York');

полностью воспроизведет таблицу UNIVERSITY.Использование NULL-значений создает определенные про-

блемы для рассматриваемых операторов. Когда в SQL сравнива-ются два значения, одно из которых NULL-значение, результатпринимает значение UNKNOWN (неизвестно). Предикат UNKNOWN,так же, как и FALSE-предикат, создает ситуацию, когда строкане включается в состав выходных данных, но результат при этомбудет различен для разных типов запросов, в зависимости от ис-пользования в них ALL или ANY вместо EXISTS. Рассмотримв качестве примера две реализации запроса: найти все данные обуниверситетах, рейтинг которых меньше рейтинга любого уни-верситета в Москве.

1) SELECT *

FROM UNIVERSITY

WHERE RATING < ANY

(SELECT RATING


' PROM UNIVERSITY

WHERE CITY = 'Москва');

2) SELECT *

FROM UNIVERSITY A

WHERE NOT EXISTS

(SELECT *

FROM UNIVERSITY В

WHERE A.RATING >= B.RATING

AND B.CITY = 'Москва');

При отсутствии в таблицах NULL оба эти запроса ведут себясовершенно одинаково. Пусть теперь в таблице UNIVERSITYесть строка с NULL-значениями в столбце RATING. В версии зап-роса с ANY в основном запросе, когда выбирается поле RATINGс NULL, предикат принимает значение UNKNOWN и строка невключается в состав выходных данных. Во втором же вариантезапроса, когда NOT EXISTS выбирает эту строку в основном запро-се, NULL-значение используется в предикате подзапроса, при-сваивая ему значение UNKNOWN. Поэтому в результате выполне-ния подзапроса не будет получено ни одного значения, и подза-прос примет значение ложь. Это в свою очередь сделает NOTEXISTS истинным, и, следовательно, строка с NULL-значениемв поле RATING попадет в выходные данные. По смыслу запросатакой результат является неправильным, так как на самом делерейтинг университета, описываемого данной строкой, можетбыть и больше рейтинга какого-либо московского университета(он просто неизвестен). Указанная проблема связана с тем, чтозначение EXISTS всегда принимает значения истина или ложь,и никогда — UNKNOWN . Это является доводом для использова-ния в таких случаях оператора ANY вместо EXISTS.

2.14. Использование COUNT вместо EXISTS

При отсутствии NULL-значений оператор EXISTS может бытьиспользован вместо ANY и ALL. Также вместо EXISTS и NOTEXISTS могут быть использованы те же самые подзапросы, но

2.15. Оператор объединения ONION 63

с использованием COUNT(*) в предложении SELECT. Например,запрос

SELECT *

FROM UNIVERSITY A

WHERE NOT EXISTS

(SELECT *

FROM UNIVERSITY В

WHERE A.RATING > = B.RATING


может быть представлен и в следующем виде:

SELECT *

FROM UNIVERSITY A

WHERE 1 >

(SELECT COUNT(*)

FROM UNIVERSITY В

WHERE A.RATING > = B.RATING



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

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

3. Напишите запрос, выбирающий из таблицы EXAM_MARKS данныео названиях предметов обучения, для которых значение получен-ных на экзамене оценок (поле MARK) превышает любое значениеоценки для предмета, имеющего идентификатор, равный 105.

4. Напишите этот же запрос с использованием МАХ.

2.15. Оператор объединения UNION

Оператор UNION используется для объединения выходныхданных двух или более SQL-запросов в единое множество строк


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

SELECT 'Студент ', SURNAME, ST0DENT_ID

FROM STUDENT

WHERE CITY = 'Москва'

UNION

SELECT 'Преподаватель', SURNAME, LECTURER_ID

FROM LECTURER

WHERE CITY = 'Москва';

Обратите внимание на то, что символом «;» (точка с запя-той) оканчивается только последний запрос. Отсутствие этогосимвола в конце SELECT-запроса означает, что следующий заним запрос так же, как и он сам, является частью общего запро-са с UNION.

Использование оператора UNION возможно только приобъединении запросов, соответствующие столбцы которых со-вместимы по объединению, то есть соответствующие числовыеполя должны иметь полностью совпадающие тип и размер,символьные поля должны иметь точно совпадающее количест-во символов. Если NULL-значения запрещены для столбца хотябы одного любого подзапроса объединения, то они должныбыть запрещены и для всех соответствующих столбцов в другихподзапросах объединения.

2.16. Устранение дублирования в UNION

В отличие от обычных запросов UNION автоматически исклю-чает из выходных данных дубликаты строк, например, в запросе

SELECT CITY

FROM STUDENT

UNION

SELECT CITY

FROM LECTURER;

совпадающие наименования городов будут исключены.

2.16. Устранение дублирования в UNION 65

Если все же необходимо в каждом запросе вывести все стро-ки независимо от того, имеются ли такие же строки в других объ-единяемых запросах, то следует использовать во множественномзапросе конструкцию с оператором UNION ALL. Так, в запросе

SELECT CITY

FROM STUDENT

UNION ALL

SELECT CITY

FROM LECTURER;

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

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

SELECT 'МЭКСОЦ', A.STUDENT_ID, SURNAME, MARK, EXAM_DATE

FROM STUDENT A, EXAM_MARKS В

WHERE (A.STUDENT_ID = B.STUDENT_ID

AND B.MARK =

(SELECT MAX(MARK)

FROM EXAM_MARKS С

WHERE C.EXAM_DATE = B.EXAM_DATE))

UNION ALL

SELECT 'МИНОЦ1, A.STUDENT_ID, SURNAME, MARK, EXAM_DATE

FROM STUDENT A, EXAM_MARKS В

WHERE (A.STUDENT_ID = B.STUDENT_ID \

AND B.MARK =

(SELECT MIN(MARK)

FROM EXAM_MARKS С

WHERE C.EXAM_DATE = В.EXAM_DATE));

Для отличия строк, выводимых первой и второй частями за-проса, в них вставлены текстовые константы 'Макс оц' и 'Мин оц'.

ЗЗак 444


В приведенном запросе агрегирующие функции использу-ются в подзапросах. Это является нерациональным с точки зре-ния времени, затрачиваемого на выполнение запроса (см. раз-дел 2.9). Более эффективна форма запроса, возвращающего ана-логичный результат:

SEIECT 'МаКСОЦ', A.STUDENT_ID, SURNAME, E.MARK, E.EXAM_DATE

FROM STUDENT A,

(SELECT B.STUDENT_ID, B.MARK, B.EXAM_DATE

FROM EXAM_MARKS B,

(SELECT MAX(MARK) AS MAX_MARK, C.EXAM_DATE

FROM EXAM__MARKS С

GROUP BY C.EXAM_DATE) D

WHERE В.EXAM_DATE=D.EXAM_DATE

AND B.MARK=MAX_MARK) E

WHERE A.STUDENT_ID=E.STUDENT_ID

UNION ALL

SEMCT 'МИНОЦ', A. STUDENT__ID, SURNAME, E .MARK, E .EXAM_DATE

FROM STUDENT A,


FROM EXAM_MARKS B,

(SELECT MIN(MARK) AS MIN_MARK, C.EXAM_DATE

FROM EXAM_MARKS С


WHERE B.EXAM_DATE=D.EXAM_DATE

AND B.MARK=MIN_MARK) E


2.17. Использование UNION с ORDER BY

Предложение ORDER BY применяется для упорядочения вы-ходных данных объединения запросов так же, как и для отдель-ных запросов. Последний пример, при необходимости упорядо-

2.18. Внешнее объединение 67

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

SELECT 'МЭКСОЦ', A.STUDENT_ID, SURNAME, E.MARK, E.EXAM_DATE

FROM STUDENT A,


FROM EXAM_MARKS B,

(SELECT MAX (MARK) AS MAX_MARK, C.EXAM_DATE

FROM EXAM_MARKS С


WHERE B.EXAM_DATE=D.EXAM_DATE

AND B.MARK=MAX_MARK) E


UNION ALL

SELECT 'МИНОЦ', A.STUDENT_ID, SURNAME, E.MARK, E.EXAM_DATE

FROM STUDENT A,


FROM EXAM_MARKS B,

(SELECT MIN(MARK) AS MIN_MARK, C.EXAM_DATE

FROM EXAM_MARKS С


WHERE В.EXAM_DATE=D.EXAM_DATE

AND B.MARK=MIN_MARK) E


ORDER BY SURNAME,E.EXAM_DATE;

2.18. Внешнее объединение

Часто бывает полезна операция объединения двух запросов,в которой второй запрос выбирает строки, исключенные пер-вым. Такая операция называется внешним объединением.

Рассмотрим пример. Пусть в таблице STUDENT имеются запи-си о студентах, в которых не указан идентификатор университета.Требуется составить список студентов с указанием наименова-


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

Приведенный выше запрос может быть записан иначе, с ис-пользованием ключевого слова JOIN.

SELECT STUDENT.SURNAME, UNIVERSITY.UNIV_NAME,

STUDENT.CITY

FROM STUDENT INNER JOIN UNIVERSITY

ON STUDENT.CITY = UNIVERSITY.CITY;

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

Рассмотренный выше случай полного соединения (декарто-ва произведения) таблиц с использованием ключевого словаJOIN будет выглядеть следующим образом:

SELECT * FROM STUDENT JOIN UNIVERSITY;

что эквивалентно

SELECT * FROM STUDENT, UNIVERSITY ;

Заметим, что в СУБД Oracle задаваемый стандартом языкаSQL оператор JOIN не поддерживается.

т

2.19.1. Операции соединения таблиц посредством ссылочнойцелостности

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

2.19. Соединение таблиц с использованием оператора JOIN 74

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

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

SELECT SURNAME, MARK, SUBJ_ID

FROM STUDENT,EXAM_MARKS

WHERE STUDENT.STUDENT_ID = EXAM_MARKS.STUDENT_ID;

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

SELECT SURNAME, MARK

FROM STUDENT JOIN EXAM_MARKS

ON STUDENT.STUDENT_ID = EXAM_MARKS.STUDENT_ID;

Хотя выше речь шла о соединении двух таблиц, можно сфор-мировать запросы путем соединения более чем двух таблиц.

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

SELECT SUBJ_NAME, SURNAME, MARK

FROM STUDENT,SUBJECT,EXAM_MARKS

WHERE STUDENT.STUDENT_ID = EXAM_MARKS.STUDENT_ID

AND SUBJECT.SUBJ_ID = EXAM_MARKS.SUBJ_ID

AND EXAM_MARKS.MARK = 2;

To же самое с использованием оператора JOIN:

SELECT SUBJ_NAME, SURNAME, MARK

FROM STUDENT JOIN SUBJECT JOIN EXAM_MARKS

ON STUDENT.STUDENT_ID = EXAM_MARKS.STUDENT_ID

AND SUBJECT.SUBJ_ID = EXAM_MARKS.SUBJ_ID

AND EXAM MARKS.MARK = 2;


2.19.2. Внешнее соединение таблиц

Как отмечалось ранее, при использовании внутреннего(INNER) соединения таблиц соединяются только те их строки,в которых совпадают значения полей, задаваемые в запросепредложением WHERE. Однако во многих случаях это может при-вести к нежелательной потере информации. Рассмотрим еще разприведенный выше пример запроса на выборку списка фамилийстудентов с полученными ими оценками и идентификаторамипредметов. При использовании, как это было сделано в рассмат-риваемом примере, внутреннего соединения в результат запросане попадут студенты, которые еще не сдавали экзамены, и кото-рые, следовательно, отсутствуют в таблице EXAMJMARKS. Если женеобходимо иметь записи об этих студентах в выдаваемом запро-сом списке, то можно присоединить сведения о студентах, несдававших экзамен, путем использования оператора UNION с со-ответствующим запросом. Например, следующим образом:

SELECT SURNAME, CAST MARK AS CHARdV CAST SUBJ_ID AS CHAR (10)


WHERE STUDENT.STUDENT_ID = EXAM_MARKS.STUDENT_ID

UNION

SELECT SURNAME, CAST NULL AS CHAR(1), CAST NULL AS CHAR(10)

FROM STUDENT

WHERE NOT EXIST

(SELECT *

FROM EXAM_MARKS

WHERE STUDENT.STUDENT_ID = EXAM_MARKS.STUDENT_ID);

(здесь функция преобразования типов CAST используется для обес-печения совместимости типов полей объединяемых запросов).

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


FROM STUDENT LEFT OUTER JOIN EXAM_MARKS

ON STUDENT.STUDENT ID = EXAM MARKS.STUDENT ID;


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

Следует заметить, что нотация запросов с внешним соеди-нением в СУБД Oracle отличается от приведенной нотации, за-даваемой стандартом языка SQL. В нотации, используемойв Oracle, этот же запрос будет иметь вид:



WHERE STUDENT.STUDENT_ID = EXAM_MARKS.STUDENT_ID(+);

Знак (+) ставится у той таблицы, которая дополняется за-писями с NULL-значениями, чтобы при соединении таблицв выходное отношение попали и те записи другой таблицы, длякоторых в таблице со знаком (+) не находится строк с соответ-ствующими значениями атрибутов, используемых для соеди-нения. То есть для левого внешнего соединения (по нотациистандарта SQL) в запросе Oracle-SQL указатель (+) ставитсяу правой таблицы.

Приведенный выше запрос может быть реализован и с при-менением правого внешнего соединения. Он будет иметь следую-щий вид:


FROM EXAM_MARKS RIGHT OUTER JOIN STUDENT

ON EXAM_MARKS.STUDENT_ID = STUDENT.STUDENT_ID;

Здесь таблица STUDENT, за счет записей которой осущес-твляется расширение выводимой таблицы, указана справа отоператора JOIN.

В нотации Oracle этот запрос будет выглядеть следующимобразом:



WHERE EXAM_MARKS.STUDENT_ID(+) = STUDENT.STUDENT_ID;

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


Иногда возникает необходимость включения в результат за-проса записей из обеих (правой и левой) соединяемых таблиц,для которых не удовлетворяется условие соединения. Такое со-единение называется полным внешним соединением и осуществля-ется указанием в запросе ключевых слов FULL OUTER JOIN илиUNION JOIN.


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

2. Напишите запрос, который выполняет выборку значений фами-лии всех студентов с указанием для студентов, сдававших экзаме-ны, идентификаторов сданных ими предметов обучения.

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

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

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

6. Напишите запрос, который выполняет вывод списка университе-тов с рейтингом, превышающим 300, вместе со значением макси-мального размера стипендии, получаемой студентами в этих уни-верситетах.

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

2.19.3. Использование псевдонимов при соединении таблиц

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


наковые имена. При соединении таблицы с ее же копией вводятпсевдонимы (алиасы) таблицы. Запрос для поиска фамилийстудентов, имеющих одинаковые имена, выглядит следующимобразом:

SELECT FIRST.SURNAME, SECOND.SURNAME

FROM STUDENT FIRST, STUDENT SECOND

WHERE FIRST.NAME = SECOND.NAME

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

{,, SELECT FIRST. SURNAME, SECOND. SURNAME<jfl FROM STUDENT FIRST, STUDENT SECONDЧ;! WHERE FIRST.NAME = SECOND.NAME

AND FIRST.SURNAME < SECOND.SURNAME


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

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

3. Написать запрос, который позволяет получить данные о названияхуниверситетов и городов, в которых они расположены, с рейтин-гом, равным или превышающим рейтинг ВГУ.

3 Манипулированиеданными

3.1. Команды манипулирования данными

В SQL для выполнения операций ввода данных в таблицу,их изменения и удаления предназначены три команды языкаманипулирования данными (DML). Это команды INSERT (вста-вить), UPDATE (обновить), DELETE (удалить).

Команда INSERT осуществляет вставку в таблицу новой •строки. В простейшем случае она имеет вид:

INSERT INTO <имя таблицы> VALUES (<значение>, <значение>,);

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

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

INSERT INTO STUDENT

VALUES (101,'Иванов','Александр', 200, 3,'Москва1,

'6/10/1979', 15);

Чтобы такая команда могла быть выполнена, таблица с ука-занным в ней именем (STUDENT) должна быть предварительноопределена (создана) командой CREATE TABLE. Если в ка-кое-либо поле необходимо вставить NULL-значение, то оно вво-дится как обычное значение:

INSERT INTO STUDENT

VALUES (101, 'Иванов', NULL, 200, 3, 'Москва','6/10/1979', 15);

В случаях, когда необходимо ввести значения полей в по-рядке, отличном от порядка столбцов, заданного командойCREATE TABLE, или требуется ввести значения не во все столб-цы, следует использовать следующую форму команды INSERT:

3.1. Команды манипулирования данными 79

INSERT INTO STUDENT (STUDENT_ID, CITY, SURNAME, NAME)

VALUES (101, 'Москва', 'Иванов', 'Саша');

Столбцам, наименования которых не указаны в приведен-ном в скобках списке, автоматически присваивается значениепо умолчанию, если оно назначено при описании таблицы (ко-манда CREATE TABLE), либо значение NULL.

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

INSERT INTO STUDENT1

SELECT *

FROM STUDENT


При этом таблица STUDENTI должна быть предварительносоздана командой CREATE TABLE (раздел 4.1) и иметь структу-ру, идентичную таблице STUDENT.

Удаление строк из таблицы осуществляется с помощью ко-манды DELETE.

Следующее выражение удаляет все строки таблицы EXAM_MARKSI .

DELETE FROM EXAM_MARKS1;

В результате таблица становится пустой (после этого онаможет быть удалена командой DROP TABLE).

Для удаления из таблицы сразу нескольких строк, удовлетво-ряющих некоторому условию, можно воспользоваться предло-жением WHERE, например:

DELETE FROM EXAM_MARKS1

WHERE STUDENT_ID = 103;

Можно удалить группу строк:

DELETE FROM STUDENTI


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

80 3. Манипулирование данными

дения о которых находятся в таблице UNIVERSITYI, изменитьрейтинг на значение 200, можно использовать конструкцию:

UPDATE UNIVERSITYI

SET RATING = 200;

Для указания конкретных строк таблицы, значения полейкоторых должны быть изменены, в команде UPDATE можно ис-пользовать предикат, указываемый в предложении WHERE.

UPDATE UNIVERSITYI

SET RATING = 200


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

Команда UPDATE позволяет изменять не только один, нои множество столбцов. Для указания конкретных столбцов,значения которых должны быть модифицированы, использует-ся предложение SET.

Например, наименование предмета обучения 'Математика'(для него SUBJ_ID = 43) должно быть заменено на название'Высшая математика', при этом идентификационный номер не-обходимо сохранить, но в соответствующие поля строки табли-цы ввести новые данные об этом предмете обучения. Запрос бу-дет выглядеть следующим образом:

UPDATE SUBJECT1

SET SUBJ_NAME = Высшая математика', HOUR = 36, SEMESTER = 1

WHERE SUBJ_ID = 43;

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

UPDATE UNIVERSITYI

SET RATING = RATING*2;

Например, для увеличения в таблице STUDENTi значенияполя STIPEND в два раза для студентов из Москвы можно ис-пользовать запрос

3.2. Использование подзапросов в INSERT

UPDATE STUDENT 1

SET STIPEND = STIPEND*2


Предложение SET не является предикатом, поэтому в немможно указать значение NULL следующим образом:

UPDATE UNIVERSITYl

SET RATING = NULL



1. Напишите команду, которая вводит в таблицу SUBJECT строку длянового предмета обучения со следующими значениями полей:SEMESTER = 4; SUBJ_NAME = 'Алгебра'; HOUR = 72; SUBJ_ID =201.

2. Введите запись для нового студента, которого зовут Орлов Нико-лай, обучающегося на первом курсе ВГУ, живущего в Воронеже,сведения о дате рождения и размере стипендии неизвестны.

3. Напишите команду, удаляющую из таблицы EXAM_MARKS записиобо всех оценках студента, идентификатор которого равен 100.

4. Напишите команду, которая увеличивает на 5 значение рейтингавсех имеющихся в базе данных университетов, расположенныхв Санкт-Петербурге.

5. Измените в таблице значение города, в котором проживает студентИванов, на «Воронеж».

3.2. Использование подзапросов в INSERT

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

4 Зак. 444


Например, пусть таблица STUDENTI имеет структуру, пол-ностью совпадающую со структурой таблицы STUDENT. Запрос,позволяющий заполнить таблицу STUDENTI записями обо всехстудентах из Москвы из таблицы STUDENT, выглядит следую-щим образом:

INSERT INTO STUDENTI

•SELECT *

FROM STUDENT


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

INSERT INTO STUDENTI

SELECT *

FROM STUDENT

WHERE UNIV_ID IN

(SELECT UNIV_ID

FROM UNIVERSITY

WHERE CITY = 'Москва');

3.2.1. Использование подзапросов, основанных на таблицахвнешних запросов

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

INSERT INTO SSTUD

SELKCT *

FROM STUDENT A

WHERE CITY IN

(SELECT CITY

FROM UNIVERSITY В

WHERE A.UNIV ID = B.UNIV ID);

3.2. Использование подзапросов в INSERT 83

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

INSERT INTO EXAM

SELECT EXAM_ID, STUDENT_ID, SUBJ_ID, MARK, EXAMJ5ATE

FROM EXAM_MARKS A

WHERE MARK =

(SELECT MAX(MARK;

FROM EXAM_MARKS В

WHERE A.EXAM_DATE = В.EXAM_DATE);

3.2.2. Использование подзапросов с DELETE

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

DELETE

FROM STUDENT

WHERE UNIV_ID IN

(SELECT UNIV_ID

FROM UNIVERSITY

WHERE CITY = 'Нью-Васюки');

В предикате предложения FROM (подзапроса) нельзя ссы-латься на таблицу, из которой осуществляется удаление. Однакоможно ссылаться на текущую строку из таблицы, являющуюсякандидатом на удаление, то есть на строку, которая в настоящеевремя проверяется в основном предикате.

DELETE

FROM STUDENT

WHERE EXISTS

(SELECT *

FROM UNIVERSITY

1,1


WHERE RATING = 401

AND STUDENT.UNIV_ID = UNIVERSITY. UNIV_ID);

Часть AND предиката внутреннего запроса ссылается на таб-лицу STUDENT. Команда удаляет данные о студентах, которыеучатся в университетах, имеющих рейтинг, равный 401. Сущес-твуют и другие способы решения этой задачи.

DELETE

FROM STUDENT

WHERE 401 IN

(SELECT RATING

FROM UNIVERSITY

WHERE STUDENT. UNIV_ID = UNIVERSITY. UNIV__ID);

Пусть нужно найти наименьшее значение оценки, полу-ченной в каждый день сдачи экзаменов, и удалить из таблицысведения о студенте, который получил эту оценку. Запрос бу-дет иметь вид:

DELETE

FROM STUDENT

WHERE STUDENT_ID IN

(SELECT STUDENT_ID

FROM EXAM_MARKS A

WHERE MARK=

(SELECT MIN(MARK)

FROM EXAM_MARKS В

WHERE A.EXAM_DATE = В.EXAMJDATE));

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

Если в какой-то день сдавался только один экзамен (то естьполучена только одна минимальная оценка) и по какой-либопричине запись, в которой находится эта оценка, требуется ос-тавить, то решение будет иметь вид:

DELETE

FROM STUDENT

3.2. Использование подзапросов в INSERT 85

WHERE STUDENT_ID IN

(SELECT STUDENT_ID

FROM EXAM_MARKS A

WHERE MARK =

(SELECT MIN(MARK)

FROM EXAM_MARKS В

WHERE A.EXAM_DATE = B.EXAM_DATE

AND 1 <

(SELECT COONT(SUBJ_ID)

FROM EXAM_MARKS В

WHERE A.EXAM_DATE = В.EXAM_DATE)));

3.2.3. Использование подзапросов с UPDATEС помощью команды UPDATE можно применять подзапро--

сы в любой форме, приемлемой для команды DELETE.Например, используя связанные подзапросы, можно увели-

чить значение размера стипендии на 20 в записях студентов,сдавших экзамены на 4 и 5.

UPDATE STUDENT1

SET STIPEND = STIPEND + 20

WHERE 4 <=

(SELECT MIN(MARK)

FROM EXAM_MARKS

WHERE EXAM_MARKS.STUDENT_ID = STUDENT1.STUDENT_ID);

Другой запрос: «Уменьшить величину стипендии на 20 всемстудентам, получившим на экзамене минимальную оценку».

UPDATE STUDENT1

SET STIPEND = STIPEND - 20

WHERE STUDENT_ID IN

(SELECT STUDENT_ID

FROM EXAM_MARKS A

WHERE MARK =


(SELECT MIN(MARK)

FROM EXAM_MARKS В

WHERE A.EXAM DATE = B.EXAM DATE));


1. Пусть существует таблица с именем STUDENTI, определения столб-цов которой полностью совпадают с определениями столбцов таб-лицы STUDENT. Вставить в эту таблицу сведения о студентах, ус-пешно сдавших экзамены более чем по пяти предметам обучения.

2. Напишите команду, удаляющую из таблицы SUBJECTI сведенияо предметах обучения, по которым студентами не получено ни од-ной оценки.

3. Напишите запрос, увеличивающий данные о величине стипендиина 20% всем студентам, у которых общая сумма баллов превышаетзначение 50.

4 Создание объектовбазы данных

4.1. Создание таблиц базы данных

Создание объектов базы данных осуществляется с помо-щью операторов языка определения данных (DDL).

Таблицы базы данных создаются с помощью командыCREATE TABLE. Эта команда создает пустую таблицу, то естьтаблицу, не имеющую строк. Значения в эту таблицу вводятсяс помощью команды INSERT. Команда CREATE TABLE определя-ет имя таблицы и множество поименованных столбцов в ука-занном порядке. Для каждого столбца должен быть определентип и размер. Каждая создаваемая таблица должна иметь, покрайней мере, один столбец. Синтаксис команды CREATE TABLEимеет следующий вид:

CREATE TABLE <ИМЯ ТабЛИЦЫ>

(<имя столбцаХтип данных>[(<размер>)]);

Используемые в SQL типы данных как минимум поддержи-вают стандарты ANSI (American National Standards Institute — Аме-риканский национальный институт стандартов) (см. раздел 1.5«Типы данных SQL»):

CHAR (CHARACTER),

INT (INTEGER),

SMALLINT,

DEC (DECIMAL),

NUMERIC,

FLOAT,

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

88 4. Создание объектов базы данных

может находиться в поле, изменяется от нуля (если в поле со-держится NULL-значение) до заданного в CREATE TABLE макси-мального значения.

Следующий пример показывает команду, которая позволя-ет создать таблицу STUDENT.

CREATE TABLE STUDENT1

(STUDENT__ID INTEGER,

SURNAME VARCHAR(60;,

NAME VARCHAR (60),

STIPEND DOUBLE,

KURS INTEGER,

CITY VARCHAR(60>,

BIRTHDAY DATE,

UNIV_ID INTEGER);

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

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

При использовании индексации следует, однако, иметьв виду, что управление индексом существенно замедляет времявыполнения операций, связанных с обновлением данных (та-

4.3. Изменение существующей таблицы 89

ких, как INSERT и DELETE), так как эти операции требуют пере-стройки индексов.

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

Синтаксис команды создания индекса имеет следующий вид:CREATE INDEX <имя индекса> ON <имя табпицы> (<имя столбца>

[,<имя столбца>]);

При этом таблица должна быть уже создана и содержатьстолбцы, имена которых указаны в команде создания индекса.Имя индекса, определенное в команде, должно быть уникаль-ным в базе данных. Будучи однажды созданным, индекс явля-ется невидимым для пользователя, все операции с ним осущес-твляет СУБД.

ПримерЕсли таблица EXAM_MARKS часто используется для поиска

оценки конкретного студента по значению поля STUDENT_ID,то следует создать индекс по этому полю.

CREATE INDEX STUDENT_ID_1 ON EXAM_MARKS (STUDENT_ID);

Для удаления индекса (при этом обязательно требуетсязнать его имя) используется команда DROP INDEX, имеющаяследующий синтаксис:

DROP INDEX <имя индекса>;

Удаление индекса не изменяет содержимого поля или по-лей, индекс которых удаляется.

4.3. Изменение существующей таблицы

Для модификации структуры и параметров существующейтаблицы используется команда ALTER TABLE. Синтаксис ко-


манды ALTER TABLE для добавления столбцов в таблицу име-ет вид

ALTER TABLE <ИМЯ ТабЛИЦЫ> ADD (<ИМЯ СТОЛбца> <ТИП ДАННЫХ>

<размер>);

По этой команде для существующих в таблице строк добав-ляется новый столбец, в который заносится NULL-значение.Этот столбец становится последним в таблице. Можно добав-лять несколько столбцов, в этом случае их определения в ко-манде ALTER TABLE разделяются запятой.

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

ALTER TABLE <ИМЯ ТЭбЛИЦЫ> MODIFY <ИМЯ СТОЛбЦЭ> <ТИП ДАННЫХ>

<размер/точность >;

Следует иметь в виду, что модификация характеристикстолбца может осуществляться не в любом случае, а с учетомследующих ограничений:

• изменение типа данных возможно только в том случае, еслистолбец пуст;

• для незаполненного столбца можно изменять размер/точ-ность. Для заполненного столбца размер/точность можноувеличить, но нельзя понизить;

• ограничение NOT NULL может быть установлено, если ни од-но значение в столбце не содержит NULL. Опцию NOT NULLвсегда можно отменить;

• разрешается изменять значения, установленные по умолчанию.

4.4. Удаление таблицы

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

DROP TABLE <имя таблицы>;

4.5. Ограничения на множество допустимых значений данных 91


1. Напишите команду CREATE TABLE для создания таблицы LECTURER.2. Напишите команду CREATE TABLE для создания таблицы SUBJECT.3. Напишите команду CREATE TABLE для создания таблицы UNIVER-

SITY.4. Напишите команду CREATE TABLE для создания таблицы EXAM_MARKS .5. Напишите команду CREATE TABLE для создания таблицы SUB J_LECT.6. Напишите команду, которая позволит быстро выбрать данные

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

осуществить поиск оценок, сгруппированных по датам.

4.5. Ограничения на множество допустимыхзначений данных

До сих пор рассматривалось только следующее ограниче-ние — значения, вводимые в таблицу, должны иметь типы дан-ных и размеры, совместимые с типами/размером данных столб-цов, в которые эти значения вводятся (как определено в командеCREATE TABLE или ALTER TABLE). Описание таблицы может бытьдополнено более сложными ограничениями, накладываемымина значения, которые могут быть вставлены в столбец или груп-пу столбцов. Ограничения (CONSTRAINTS) являются частью оп-ределения таблицы.

При создании (изменении) таблицы могут быть определеныограничения на вводимые значения. В этом случае SQL будетотвергать любое из них при несоответствии заданным критериям.Ограничения могут быть статическими, ограничивающими зна-чения или диапазон значений, вставляемых в столбец (CHECK,NOT NULL). Они могут иметь связь со всеми значениями столб-ца, ограничивая новые строки значениями, которые не содер-жатся в столбцах или их наборах (уникальные значения, пер-вичные ключи). Ограничения могут также определяться связьюсо значениями, находящимися в другой таблице, допуская, на-пример, вставку в столбец только тех значений, которые в дан-ным момент содержатся также в другом столбце другой или этой

,же таблицы (внешний ключ). Эти ограничения носят динами-ческий характер.


Существует два основных типа ограничений — ограниченияна столбцы и ограничения на таблицу. Ограничения на столбцы(COLUMN CONSTRAINTS) применимы только к отдельным столб-цам, а ограничения на таблицу (TABLE CONSTRAINTS) примени-мы к группам, состоящим из одного или более столбцов. Огра-ничения на столбец добавляются в конце определения столбцапосле указания типа данных и перед окончанием описаниястолбца (запятой). Ограничения на таблицу размещаются в кон-це определения таблицы, после определения последнего столбца.Команда CREATE TABLE имеет следующий синтаксис, расши-ренный включением ограничений:

CREATE TABLE <ИМЯ таблицы >

(<имя столбца > <тип данных> Ограничения на столбец>,

<имя столбца> <тип данных> Ограничения на столбец>,

Ограничения на таблицу> (<имя столбца>[,<имя столбца>]));

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

4.5.1. Ограничение NOT NULL

Чтобы запретить возможность использования в поле NULL-зна-чений, можно при создании таблицы командой CREATE TABLEуказать для соответствующего столбца ключевое слово NOT NULL.Это ограничение применимо только к столбцам таблицы. Какуже говорилось выше, NULL — это специальный маркер, обозна-чающий тот факт, что поле пусто. Но он полезен не всегда. Пер-вичные ключи, например, в принципе не должны содержатьNULL-значений (быть пустыми), поскольку это нарушило бы тре-бование уникальности первичного ключа (более строго — функ-циональную зависимость атрибутов таблицы от первичного клю-ча). Во многих других случаях также необходимо, чтобы поляобязательно содержали определенные значения. Если ключевоеслово NOT NULL размещается непосредственно после типа данных(включая размер) столбца, то любые попытки оставить значение


поля пустым (ввести в поле NULL-значение) будут отвергнутысистемой.

Например, для того, чтобы в определении таблицы STUDENTзапретить использование NULL-значений для столбцов STUDENT_ID,SURNAME и NAME, можно записать следующее:

CREATE TABLE STUDENT

(STUDENT_ID INTEGER NOT NULL,

SURNAME CHAR (25) NOT NULL,

NAME CHAR (10) NOT NULL,

STIPEND INTEGER,

KURS INTEGER,

CITY CHAR (15),

BIRTHDAY DATE,

UNIV_ID INTEGER);

Важно помнить: если для столбца указано NOT NULL, то прииспользовании команды INSERT обязательно должно быть ука-зано конкретное значение, вводимое в это поле. При отсутст-вии ограничения NOT NULL в столбце значение может отсутство-вать, если только не указано значение столбца по умолчанию(DEFAULT). Если при создании таблицы ограничение NOT NULLне было указано, то его можно указать позже, используя коман-ду ALTER TABLE. Однако для того, чтобы для вновь вводимогос помощью команды ALTER TABLE столбца можно было задатьограничение NOT NULL, таблица, в которую добавляется стол-бец, должна быть пустой.

4.5.2. Уникальность как ограничение на столбец

Иногда требуется, чтобы все значения, введенные в стол-бец, отличались друг от друга. Например, этого требуют первич-ные ключи. Если при создании таблицы для столбца указываетсяограничение UNIQUE, то база данных отвергает любую попыткуввести в это поле какой-либо строки значение, уже содержащеесяв том же поле другой строки. Это ограничение применимо толь-ко к тем полям, которые были объявлены NOT NULL. Можно


предложить следующее определение таблицы STUDENT, исполь-зующее ограничение UNIQUE:


(STUDENT_ID INTEGER NOT NULL UNIQUE,



STIPEND INTEGER,

KURS INTEGER,

CITY CHAR (15),

BIRTHDAY DATE,

UNIV_ID INTEGER);

Объявляя поле STUDENT_ID уникальным, можно быть уве-ренным, что в таблице не появится записей для двух студентовс одинаковыми идентификаторами. Столбцы, отличные от пер-вичного ключа, для которых требуется поддержать уникаль-ность значений, называются возможными ключами или уни-кальными ключами (CANDIDATE KEYS ИЛИ UNIQUE KEYS).

4.5.3. Уникальность как ограничение таблицы

Можно сделать уникальными группу полей, указав UNIQUEв качестве ограничений таблицы. При объединении полейв группу важен порядок, в котором они указываются. Ограни-чение на таблицу UNIQUE является полезным, если требуетсяподдерживать уникальность группы полей. Например, еслив нашей базе данных не допускается, чтобы студент сдавалв один день больше одного экзамена, то можно в таблице объя-вить уникальной комбинацию значений полей STUDENT_IDи EXAM_DATE. Для этого следует создать таблицу EXAM_MARKSследующим способом:

CREATE TABLE EXAM_MARKS

(EXAM_ID INTEGER NOT NULL,

STUDENT_ID INTEGER NOT NULL,

SUBJ_ID INTEGER NOT NULL,

MARK CHAR (1),


EXAM_DATE DATE NOT NULL,

UNIQUE (STUDENT_ID, EXAM_DATE));

Обратите внимание, что оба поля в ограничении таблицыUNIQUE все еще используют ограничение столбца — NOT NULL.Если бы использовалось ограничение столбца UNIQUE для поляSTUDENT_ID, то такое ограничение таблицы было бы необяза-тельным.

-Если значение поля STUDENT_ID должно быть различнымдля каждой строки в таблице EXAM_MARKS, это можно сделать,объявив UNIQUE как ограничение самого поля STUDENT_ID.В этом случае не будет и двух строк с идентичной комбинациейзначений полей STUDENT_ID, EXAM_DATE. Следовательно, указа-ние UNIQUE как ограничение таблицы наиболее полезно исполь-зовать в случаях, когда не требуется уникальность индивидуаль-ных полей, как это имеет место на самом деле в рассматриваемомпримере.

4,5.4. Присвоение имен ограничениям

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

Для присвоения имени ограничению используется несколькоизмененный синтаксис команд CREATE TABLE и ALTER TABLE.

Приведенный выше пример запроса изменяется следующимобразом:

CREATE TABLE EXAM_MARKS

(EXAM_ID INTEGER NOT NULL,

STUDENT_ID INTEGER NOT NULL,


MARK CHAR (1),

EXAM_DATE DATE NOT NULL,

CONSTRAINT STUD_SUBJ_CONSTR

UNIQUE (STUDENT ID, EXAM DATE);


В этом запросе STUD__SUBJ_CONSTR — это имя, присвоен-ное указанному ограничению таблицы.

4.5.5. Ограничение первичных ключей

Первичные ключи таблицы — это специальные случаи ком-бинирования ограничений UNIQUE и NOT NULL. Первичныеключи имеют следующие особенности:

• таблица может содержать только один первичный ключ;• внешние ключи по умолчанию ссылаются на первичный

ключ таблицы;• первичный ключ является идентификатором строк таблицы

(строки, однако, могут идентифицироваться и другими спо-собами).Улучшенный вариант создания таблицы STUDENTI с объяв-

ленным первичным ключом имеет теперь следующий вид:


(STUDENT_ID INTKGER PRIMARY KEY,



STIPEND INTEGER,

KURS INTEGER,

CITY CHAR (15),

BIRTHDAY DATE,

UNIV_ID INTEGER);

4.5.6. Составные первичные ключи

Ограничение PRIMARY KEY может также быть применено длянескольких полей, составляющих уникальную комбинацию зна-чений — составной первичный ключ. Рассмотрим таблицуEXAM_MARKS. Очевидно, что ни к полю идентификатора студента(STUDENT_ID), ни к полю идентификатора предмета обучения(EXAM_ID) по отдельности нельзя предъявить требование уни-кальности. Однако для того, чтобы в таблице не могли появиться


разные записи для одинаковых комбинаций значений полейSTUDENT_ID и EXAM_ID (конкретный студент на конкретномэкзамене не может получить более одной оценки), имеет смыслобъявить уникальной комбинацию этих полей. Для этого мы мо-жем применить ограничение таблицы PRIMARY KEY объявив па-ру EXAM_ID И STUDENT_ID ПерВИЧНЫМ КЛЮЧОМ Таблицы.

CREATE TABLE NEW_EXAM_MARKS

(STUDENT_ID INTEGER NOT NULL,


MARK INTEGER,

DATA DATE,

CONSTRAINT EX_PR_KEY PRIMARY KEY (EXAM ю, STUDENT ID));

4.5.7. Проверка значений полей

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

Рассмотрим таблицу STUDENT. Значение столбца STIPENDв этой таблице выражается десятичным числом. Наложим назначения этого столбца ограничение — величина размера сти-пендии должна быть меньше 200.

Соответствующий запрос имеет следующий вид:


(STUDENT_ID INTEGER PRIMARY KEY,

* SURNAME CHAR (25) NOT NULL,


STIPEND INTEGER CHECK (STIPEND < 200),

KURS INTEGER,

CITY CHAR (15),

BIRTHDAY DATE,

UNIV_ID INTEGER);

5 Зак 444 i


4.5.8. Проверка ограничивающих условий с использованиемсоставных полей

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

Предположим, что ограничение на размер стипендии (меньше200) должно распространяться только на студентов, живущихв Воронеже. Это можно указать в запросе со следующим таблич-ным ограничением CHECK:



SURNAME CHAR(25) NOT NULL,


STIPEND INTEGER,

KURS INTEGER,

CITY CHAR (15),

BIRTHDAY DATE,

UNIV_ID INTEGER UNIQUE,

CHECK (STIPEND < 200 AND CITY = 'Воронеж'));

или в несколько другой записи:



SURNAME CHAR(25) NOT NULL,


STIPEND INTEGER,

KURS INTEGER,

CITY CHAR (15),

BIRTHDAY DATE,

UNIV_ID INTEGER UNIQUE,

CONSTRAINT STUD_CHECK CHECK (STIPEND < 200

AND CITY = 'Воронеж'));


4.5.9. Установка значений по умолчанию

В SQL имеется возможность при вставке в таблицу строки,не указывая значений некоторого поля, определять значениеэтого поля по умолчанию. Наиболее часто используемым значе-нием по умолчанию является NULL. Это значение принимаетсяпо умолчанию для любого столбца, для которого не было уста-новлено ограничение NOT NULL.

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

ОЕРАОТлКзначение по умолчанию>.

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

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





STIPEND INTEGER CHECK (STIPEND < 200),

KURS INTEGER,

CITY CHAR (15) DEFAULT 'Воронеж',

BIRTHDAY DATE,

UNIV_ID INTEGER);

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


полей, поскольку NULL представляет собой признак того, чтофактическое значение поля неизвестно или неопределенно. Сле-довательно, строго говоря, сравнение с ним любого конкретно-го значения в рамках двузначной булевой логики является не-корректным, за исключением специальной операции сравненияis NULL, которая определяет, является ли содержимое поля ка-ким-либо значением или оно отсутствует. Действительно, ка-ким образом в рамках двузначной логики ответить на вопрос,истинно или ложно условие CITY = 'Воронеж', если текущеезначение поля CITY неизвестно (содержит NULL)?

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

Например, можно установить для столбца опцию NOT NULL,а для неопределенных значений числового типа установить зна-чение по умолчанию «равно нулю», или для полей типа CHARпробел, использование которых в операциях сравнения не вы-зывает никаких проблем.

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


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

2. Создайте таблицу предметов обучения SUBJECT так, чтобы количе-ство отводимых на предмет часов по умолчанию было равно 36, недопускались записи с отсутствующим количеством часов, полеSUBJ_ID являлось первичным ключом таблицы и значения семест-ров (поле SEMESTER) лежали в диапазоне от 1 до 12.

4.6. Поддержка целостности данных 101

3. Создайте таблицу EXAM_MARKS таким образом, чтобы значения по-ля EXAM__ID были больше значений поля SUBJ_ID, а значения поляSUBJ_ID были больше значений поля STUDENT__ID; пусть также бу-дут запрещены значения NULL в любом из этих трех полей.

4.6. Поддержка целостности данных

В таблицах рассматриваемой базы данных значения некото-рых полей связаны друг с другом. Так, поле STUDENT_ID в таб-лице STUDENT и поле STUDENT_ID в таблице EXAM_MARKS связа-ны тем, что описывают одни и те же объекты, то есть содержатидентификаторы студентов, информация о которых хранитсяв базе. Более того, значения идентификаторов студентов, кото-рые допустимы в таблице EXAM_MARKS, должны выбиратьсятолько из списка значений STUDENT_ID, фактически присутст-вующих в таблице STUDENT, то есть принадлежащих реально опи-санным в базе студентам. Аналогично, значения поля UNIV_IDтаблицы STUDENT должны соответствовать идентификаторамуниверситетов UNIV_ID, фактически присутствующим в табли-це UNIVERSITY, а значения поля SUBJ_ID таблицы EXAM_MARKSдолжны соответствовать идентификаторам предметов обуче-ния, фактически присутствующим в таблице SYBJECT.

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

4.6.1. Внешние и родительские ключи

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

Электронная библиотека Либрус” ( )ftemk.mpei.ac.ru/ed/sql/sql.pdf ·...

Documents