close

Вход

Забыли?

вход по аккаунту

?

1106.SQL Власова О В

код для вставкиСкачать
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Министерство образования и науки Российской Федерации
Ярославский государственный университет им. П. Г. Демидова
О. В. Власова
S Q L
Учебное пособие
Рекомендовано
Научно-методическим советом университета для студентов,
обучающихся по специальности
Прикладная математика и информатика
Ярославль 2011
1
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
УДК 51
ББК З973.223–0182я73+В1я73
В 58
Рекомендовано
Редакционно-издательским советом университета
в качестве учебного издания. План 2010/2011 учебного года
Рецензенты:
Коромыслов В. А., доктор физико-математических наук,
доцент, профессор Ярославского филиала МИИТ;
кафедра информационно-компьютерных технологий
Института информационных технологий МУБиНТ
В 58
Власова, О. В. SQL: учебное пособие / О. В. Власова ; Яросл.
гос. ун-т им. П. Г. Демидова. – Ярославль : ЯрГУ, 2011. – 136 с.
ISBN 978-5-8397-0806-8
Данное пособие по учебному курсу «Базы данных и экспертные системы» представляет собой основы использования языка
SQL на примере работы с MS SQL SERVER. Оно ориентировано в
первую очередь на практическое применения языка SQL: создание структуры реляционной базы данных, осуществление выборки из базы данных, модификации данных, создание представлений, индексов. В пособии содержатся теоретические сведения,
включая достаточно подробное описание синтаксиса операторов
SQL и основные сведения о создании представлений и индексов,
приведены методические указания по их использованию.
Пособие содержит большое количество примеров на использование операторов SQL, которые могут быть полезны на
этапе освоения материала, а также выступать в качестве вопросов для самопроверки.
Предназначено для студентов, обучающихся по специальности
010501.65 Прикладная математика и информатика (дисциплина
«Базы данных и экспортные системы», блок ОПД), очной формы
обучения.
УДК 51
ББК З973.223–0182я73+В1я73
ISBN 978-5-8397-0806-8
 Ярославский государственный
университет им. П. Г. Демидова, 2011
2
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Введение Основные идеи современных информационных технологий базируются на концепции, согласно которой данные должны быть
организованы в базы данных (БД) с целью адекватного отображения изменяющегося реального мира и удовлетворения информационных потребностей пользователей. Эти БД создаются и функционируют под управлением специальных программных комплексов,
называемых системами управления базами данных (СУБД).
Увеличение объема и структурной сложности хранимых данных, расширение круга пользователей информационных систем
привели к широкому распространению наиболее удобных и сравнительно простых для понимания реляционных БД. Для обеспечения одновременного доступа к данным множества пользователей, нередко расположенных достаточно далеко друг от друга и
от места хранения баз данных, созданы сетевые версии СУБД. В
них тем или иным путем решаются специфические проблемы параллельных процессов, целостности (правильности) и безопасности данных, а также санкционирования доступа.
Ясно, что совместная работа пользователей в сетях с помощью унифицированных средств общения с базами данных возможна только при наличии стандартного языка манипулирования
данными, обладающего средствами для реализации перечисленных выше возможностей. Таким языком стал SQL, разработанный в 1974 году фирмой IBM для экспериментальной реляционной СУБД System R.
Язык SQL является инструментом, предназначенным для выборки и обработки информации, содержащейся в компьютерной
базе данных. SQL – это сокращенное название структурированного языка запросов (Structured Query Language). По историческим причинам аббревиатура SQL читается обычно как «сиквел»,
но используется и альтернативное произношение «эскюэль». Как
следует из названия, SQL является языком программирования,
который применяется для организации взаимодействия пользователя с базой данных. На самом деле SQL работает только с базами данных одного определенного типа, называемых реляционными. На рис. 1.1 изображена схема работы SQL. Согласно этой
3
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
схеме, в вычислительной системе имеется база данных, в которой
хранится информация. Компьютерная программа, которая управляет базой данных, называется системой управления базой данных (СУБД). Если пользователю необходимо получить информацию из БД, он запрашивает её у СУБД c помощью команды SQL.
СУБД обрабатывает запрос, находит требуемые данные и посылает их пользователю. Процесс запрашивания данных и получения результата называется запросом к базе данных; отсюда и название «структурированный язык запросов».
SQL-запрос:
Select * from R1
Система
управления базой
данных (СУБД)
База
данных
Данные:
ID FIO
1. Иванов И. И.
Рис. 1.1. Применение SQL для доступа к базе данных
Однако название SQL не совсем соответствует действительности. Во-первых, сегодня SQL представляет собой нечто большее, чем просто инструмент создания запросов. Сейчас этот язык
используется для реализации всех функциональных возможностей, которые СУБД предоставляет пользователю. К ним относятся:
 организация данных. SQL дает пользователю возможность
изменять структуру представления данных, а также устанавливать отношения между элементами БД;
 выборка данных. SQL дает пользователю возможность изменять БД, т. е. добавлять в неё новые данные, а также удалять
или обновлять уже имеющиеся в ней данные;
 управление доступом. С помощью SQL можно ограничить
возможности пользователя по выборке и изменению данных и
защитить их от несанкционированного доступа;
 совместное использование данных. SQL координирует совместное использование данных пользователям, работающим параллельно, чтобы они не мешали друг другу;
4
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
 целостность данных. SQL позволяет обеспечить целостность БД, защищая её от разрушения из-за несогласованных изменений или отказа системы.
Таким образом, SQL является достаточно мощным языком,
обеспечивающим эффективное взаимодействие с СУБД.
Во-вторых, SQL – это не полноценный компьютерный язык
типа С++ или Java. В нем нет инструкций для проверки условий,
организации циклов. SQL является подъязыком БД, в который
входит около сорока инструкций, предназначенных для управления БД. Инструкции SQL встраиваются в базовый язык, например С++, и дают возможность получать доступ к БД. Кроме того,
из такого языка, как С++, инструкции SQL можно посылать в
СУБД, используя интерфейс вызова функций.
Наконец, SQL – это слабоструктурированный язык. Инструкции SQL напоминают английские предложения и содержат «слова-пустышки», не влияющие на смысл инструкции, но облегчающие её чтение. В SQL почти нет нелогичностей и к тому же
имеется ряд специальных правил, предотвращающих создание
инструкций, которые выглядят как абсолютно правильные, но не
имеют смысла.
SQL на сегодняшний день – единственный стандартный язык
для работы с реляционными БД. Сам по себе SQL не является ни
системой управления базами данных, ни отдельным программным продуктом. SQL – это неотъемлемая часть СУБД, с помощью которой осуществляется связь пользователя с ней.
Достоинства SQL
SQL – это легкий для понимания язык и в то же время универсальное программное средство управления данными. Успех
языку SQL принесли следующие его особенности:
1) независимость от конкретной СУБД;
2) межплатформенная переносимость;
3) наличие стандартов;
4) реляционная основа;
5) поддержка со стороны компаний IBM(СУБД DB2) и Microsoft (протокол OBDC и технология ADO);
6) возможность выполнения специальных интерактивных запросов;
5
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
7) поддержка архитектуры клиент/сервер;
8) возможность различного представления данных;
9) интеграция с языками высокого уровня;
10) расширяемость и поддержка объектно-ориентированных
технологий.
Недостатки SQL
1. Несоответствие реляционной модели данных.
Создатели реляционной модели данных Эдгар Кодд, Кристофер Дейт и их сторонники указывают на то, что SQL не является
истинно реляционным языком. В частности, они определяют следующие проблемы SQL: повторяющиеся строки; неопределённые
значения (NULL); явное указание порядка колонок слева направо; колонки без имени и дублирующиеся имена колонок; отсутствие поддержки свойства «=»; использование указателей; высокая избыточность.
2. Сложность.
Хотя SQL и задумывался как средство работы конечного
пользователя, в конце концов он стал настолько сложным, что
превратился в инструмент программиста.
3. Отступления от стандартов.
Несмотря на наличие международного стандарта SQL, многие компании, занимающиеся разработкой СУБД (например,
Oracle, Sybase, Microsoft, MySQL AB), вносят изменения в язык
SQL, применяемый в разрабатываемой СУБД, тем самым отступая от стандарта. Таким образом, появляются специфичные для
каждой конкретной СУБД диалекты языка SQL.
4. Сложность работы с иерархическими структурами.
Ранее диалекты SQL большинства СУБД не предлагали способа манипуляции древовидными структурами. Некоторые поставщики СУБД предлагали свои решения (например, Oracle использует выражение CONNECT BY). В настоящее время в ANSI
стандартизована рекурсивная конструкция WITH из диалекта
SQL DB2. В MS SQL Server рекурсивные запросы появились
лишь в версии MS SQL Server 2005. В версии MS SQL Server
2008 появился новый тип данных – hierarchyid, упрощающий манипуляцию древовидными структурами.
6
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Глава 1. Историческая справка 1.1. Стандарты Язык SQL был разработан в начале 1970-х годов в одной из
исследовательских лабораторий компании IBM для экспериментальной реляционной СУБД IBM System R.
Целью разработки было создание простого непроцедурного
языка, которым мог воспользоваться любой пользователь, даже не
имеющий навыков программирования. Собственно разработкой
языка запросов занимались Дональд Чэмбэрлин (Donald D. Chamberlin) и Рэй Бойс (Ray Boyce); Пэт Селинджер (Pat Selinger) занималась разработкой стоимостного оптимизатора (cost-based optimizer), Рэймонд Лори (Raymond Lorie) – компилятором запросов.
Стоит отметить, что SQL был не единственным языком подобного назначения. В Калифорнийском университете Беркли была
разработана некоммерческая СУБД Ingres (являвшаяся прародителем популярной сейчас некоммерческой СУБД PostgreSQL), которая являлась реляционной СУБД, но использовала свой собственный язык QUEL, который, однако, не выдержал конкуренции по
количеству поддерживающих его СУБД с языком SQL.
Первыми СУБД, поддерживающими новый язык, стали в
1979 году Oracle V2 для машин VAX от компании Relational
Software Inc. (впоследствии ставшей компанией Oracle) и
System/38 от IBM, основанная на System R.
Начиная с 1986 года комитеты ISO (International Organization
for Standardization) и ANSI (American National Standards Institute)
приступили к созданию ряда стандартов языка SQL, которые
впоследствии были приняты и получили следующие названия:
SQL86, SQL89, SQL92, SQL99, SQL2003 и SQL2008.
Стандарт SQL86 зафиксировал минимальный стандартный
синтаксис языка SQL.
Стандарт SQL99, при разработке именовавшийся как SQL3,
стандартизировал объектные расширения языка SQL и некоторые
процедурные расширения языка SQL. К моменту принятия этого
стандарта большинство коммерческих СУБД, таких как Oracle, уже
де-факто ввели использование объектных типов и наследования.
7
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
В стандарте SQL99 определено обязательное функциональное ядро (Core) и набор уровней расширенного соответствия. Функциональное ядро SQL99 включает в себя основной
уровень соответствия SQL92. Уровни расширенного соответствия
не являются обязательными для реализации в СУБД, претендующей на поддержку стандарта SQL99. СУБД может не поддерживать ни одного уровня расширенного соответствия или поддерживать любые из них. Каждый уровень описывает набор возможностей языка SQL, которые должны поддерживать реализации СУБД, претендующие на данный уровень соответствия.
Стандарт SQL99 содержит следующие уровни соответствия:
 функциональное ядро. Данный уровень является обязательным для любой реализации СУБД. Он включает в себя основной уровень соответствия SQL92, а также поддержку работы с
LOB-объектами (Large Object), вызов из SQL внешних программ,
написанных на других языках программирования, и простые типы данных, определяемые пользователем (UDT-типы, UserDefined Datatypes). Вводится поддержка LOB-объектов двух типов: бинарных BLOB-объектов (Binary Large Object) и символьных CLOB-объектов (Character Large Object). Внешние программы определяются как объекты схемы, так же, как и таблицы.
Следует отметить, что хотя использование внешних программ
входит в функциональное ядро, но поддержка вызова процедур и
функций SQL относится к расширенному уровню соответствия
"PSM-модули" (Persistent Stored Module). Определяемые пользователем типы данных могут быть простыми и структурированными. Второй случай относится к уровню соответствия "Базовая
поддержка объектов". Простой тип данных, определяемый пользователем – это существующий тип данных, для которого определено новое имя и возможно некоторое ограничение по количеству символов или цифр;
 поддержка работы с датой/временем. Этот уровень соответствия вводит типы данных DATETIME и INTERVAL, а для
типа DATETIME вводит поля TIMEZONE_HOUR и TIMEZONE_MINUTE, определяющие смещение для зонального времени
относительно универсального времени. В стандарте SQL92 пол8
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
ного уровня соответствия типы данных DATETIME и INTERVAL
уже были специфицированы;
 управление целостностью. Этот уровень соответствия вводит поддержку дополнительных возможностей ссылочной целостности: подзапросы в ограничениях целостности CHECK оператора
CREATE TABLE, триггеры. Большинство из этих возможностей
входило в стандарт SQL92;
 активные базы данных. На этом уровне соответствия определяется поддержка триггеров базы данных, хранимых в базе данных и выполняемых. Триггеры представляют собой фрагменты кода, выполняемые перед или после указанного изменения данных
(такого как вставка строки, удаление или изменение строки);
 OLAP. Этот уровень соответствия определяет средства описания более сложных запросов. Так, в оператор SELECT включена
фраза INTERSECT, позволяющая получать пересечения множеств,
выданных несколькими запросами. В оператор SELECT включена
фраза FULL OUTER JOIN, предназначенная для создания полных
внешних соединений таблиц. В операторах языка SQL, применяемых для манипулирования данными, определяется поддержка использования конструкторов значений строк и таблиц. Конструкторы значений строк состоят из одного или нескольких выражений
(например, (NULL, 1, 'Field1')). Конструкторы значений таблиц
представляют собой набор значений конструкторов строк, описывающий группу строк (например, VALUES (1, 'A'), (2, 'B'));
 PSM-модули. Стандарт для процедурных расширений. Этот
уровень соответствия полностью описан в документе SQL/PSM
стандарта SQL99. Так, язык SQL расширяется операторами управления CASE, IF, WHILE, REPEAT, LOOP и FOR. Вводится поддержка процедур и функций, создаваемых операторами CREATE
PROCEDURE и CREATE FUNCTION. В язык SQL введено использование переменных и применение обработчиков ошибок.
Перечень процедурных расширений для самых популярных
СУБД приведён в табл. 1.
 CLI-интерфейс. Этот уровень соответствия вводит поддержку интерфейса уровня вызова, определяющего вызов операторов SQL. В свое время на базе CLI-интерфейса был разработан
стандарт ODBC;
9
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Таблица 1
Процедурные расширения
Краткое
название
InterBase/Firebird PSQL
IBM DB2
SQL
PL (англ.)
СУБД
MS SQL Server/
MySQL
TransactSQL
SQL/PSM
Oracle
PL/SQL
PostgreSQL
PL/pgSQL
Расшифровка
Procedural SQL
SQL Procedural Language (расширяет
SQL/PSM); также в DB2 хранимые процедуры могут писаться на обычных языках программирования: Си, Java и т. д.
Transact-SQL
SQL/Persistent Stored Module (соответствует стандарту SQL:2003)
Procedural Language/SQL (основан на
языке Ada)
Procedural Language/PostgreSQL Structured
Query Language (очень похож на Oracle
PL/SQL)
 базовая поддержка объектов (Basic Object Support). Этот
уровень соответствия стандартизирует использование объектов,
вводя поддержку объектных типов данных, определяемых пользователем, применение типизированных таблиц (таблиц на базе
объектных типов), использование массивов и ссылочных типов
данных, а также переопределение внешних процедур;
 расширенная поддержка объектов (Enhanced Object
Support). Этот уровень соответствия включает все возможности,
предоставляемые уровнем базовой поддержки объектов, дополняя их поддержкой множественного наследования для типов данных, определяемых пользователем.
Представленные выше уровни расширенного соответствия
напрямую не связаны с документами, соответствующими разделам стандарта. Стандарт SQL99 содержит следующие основные
разделы:
 SQLFramework – описывает логические основы стандарта.
 SQLFoundation – определяет содержание каждого раздела
стандарта и описывает функциональное ядро стандарта (Core
SQL99).
10
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
 SQL/CLI – описывает интерфейс уровня вызова.
 SQL/PSM – определяет процедурные расширения языка
SQL.
 SQL/Bindings – определяет механизм взаимодействия языка
SQL с другими языками программирования.
 SQL/MM – описываются средства языка SQL, предназначенные для работы с мультимедийными данными.
 SQL/OLB – определяет связь SQL с объектными языками,
описывая 0-часть стандарта SQLJ для встраивания операторов
SQL в язык Java.
В конце 2003 года был принят и опубликован новый вариант
международного стандарта. Прежде всего претерпела некоторые
изменения общая организация стандарта. Стандарт SQL:2003 состоит из следующих частей:
9075-1, SQL/Framework;
9075-2, SQL/Foundation;
9075-3, SQL/CLI;
9075-4, SQL/PSM;
9075-9, SQL/MED;
9075-10, SQL/OLB;
9075-11, SQL/Schemata;
9075-13, SQL/JRT;
9075-14, SQL/XML.
Части 1–4 и 9–10 с необходимыми изменениями остались такими же, как и в SQL:1999. Часть 5 (SQL/Bindings) больше не
существует; соответствующие спецификации включены в часть 2.
Раздел части 2 SQL:1999, посвященный информационной схеме,
выделен в отдельную часть 11. Появились две новые части – 13 и
14. Часть 13 полностью называется “SQL Routines and Types
Using the Java Programming Language” (“Использование подпрограмм и типов SQL в языке программирования Java”). Появление
такой отдельной части стандарта оправдано повышенным вниманием к языку Java со стороны ведущих производителей SQLориентированных СУБД. Наконец, последняя часть SQL:2003 посвящена спецификациями языковых средств, позволяющих работать с XML-документами в среде SQL. Наиболее серьезные изменения языка SQL, специфицированные в части 2 стандарта
SQL:2003, касаются следующих аспектов: типы данных; подпрограммы, вызываемые из SQL; расширенные возможности оператора CREATE TABLE; новый объект схемы – генератор последовательностей; новые виды столбцов – идентифицирующие столб11
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
цы (identity column) и генерируемые столбцы (generated column);
новый оператор MERGE.
История версий стандарта представлена в табл. 2.
Таблица 2
Стандарты языка SQL
Год
Название
1986 SQL-86
Иное
название
SQL-87
Изменения языка
Первый вариант стандарта, принятый институтом ANSI и одобренный ISO в 1987
году
1989 SQL-89
FIPS 127-1 Немного доработанный вариант предыдущего стандарта
1992 SQL-92
SQL2,
Значительные изменения (ISO 9075); уроFIPS 127-2 вень Entry Level стандарта SQL-92 был принят как стандарт FIPS 127-2
1999 SQL:1999 SQL3
Добавлена поддержка регулярных выражений, рекурсивных запросов, поддержка
триггеров, базовые процедурные расширения, нескалярные типы данных и некоторые
объектно ориентированные возможности
2003 SQL:2003
Введены расширения для работы с XMLданными, оконные функции (применяемые
для работы с OLAP-базами данных), генераторы последовательностей и основанные на
них типы данных
2006 SQL:2006
Функциональность работы с XML-данными
значительно расширена. Появилась возможность совместно использовать в запросах
SQL и XQuery
2008 SQL:2008
Улучшены возможности оконных функций,
устранены некоторые неоднозначности
стандарта SQL:2003
1.2. Структура SQL В отличие от реляционной алгебры, где были представлены
только операции запросов к БД, SQL является полным языком, в
нем присутствуют не только операции запросов, но и операторы,
соответствующие Data Definition Language (DDL) – языку описа12
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
ния данных. Кроме того, язык содержит операторы, предназначенные для управления (администрирования) БД.
Операторы SQL.
SQL содержит примерно 40 операторов для выполнения различных действий внутри СУБД. Ниже приводится краткое описание категорий этих операторов.
Data Definition Language (DDL).
Data Definition Language содержит операторы, позволяющие
создавать, изменять и уничтожать базы данных и объекты внутри
них (таблицы, представления и др.).
Оператор
CREATE
DROP
ALTER
Описание
Применяется для добавления нового объекта к базе данных
Применяется для удаления объекта из базы данных
Применяется для изменения структуры имеющегося объекта
Data Manipulation Language (DML).
Data Manipulation Language содержит операторы, позволяющие выбирать, добавлять, удалять и модифицировать данные.
Оператор
SELECT
INSERT
DELETE
UPDATE
Описание
Применяется для выбора данных
Применяется для добавления строк к таблице
Применяется для удаления строк из таблицы
Применяется для изменения данных
Иногда оператор SELECT относят к отдельной категории, называемой Data Query Language (DQL).
Transaction Control Language (TCL).
Операторы Transaction Control Language применяются для управления изменениями, выполненными группой операторов DML.
Оператор
COMMIT
ROLLBACK
SET TRANSACTION
Описание
Применяется для завершения транзакции и сохранения изменений в БД
Применяется для отката транзакции и отмены изменений в БД
Применяется для установки параметров доступа к
данным в текущей транзакции
Data Control Language (DCL).
Операторы Data Control Language, иногда называемые операторами Access Control Language, применяются для осуществления
13
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
административных функций, присваивающих или отменяющих
право (привилегию) использовать базу данных, таблицы в базе
данных, а также выполнять те или иные операторы SQL.
Оператор
GRANT
REVOKE
Описание
Применяется для присвоения привилегии
Применяется для отмены привилегии
Cursor Control Language (CCL).
Операторы Cursor Control Language используются для определения курсора, подготовки SQL-предложений для выполнения,
а также для некоторых других операторов.
Оператор
DECLARE CURSOR
EXPLAIN
OPEN CURSOR
FETCH
CLOSE CURSOR
PREPARE
EXECUTE
DESCRIBE
Описание
Применяется для определения курсора для запроса
Применяется для описания плана запроса. Этот оператор представляет собой расширение SQL для
Microsoft SQL Server 7.0. Он не обязан выполняться
в других СУБД. Например, в случае Oracle следует
использовать оператор EXPLAIN PLAN
Применяется для открытия курсора при получении
результатов запроса
Применяется для получения строки из результатов
запроса
Применяется для закрытия курсора
Применяется для подготовки оператора SQL для выполнения
Применяется для выполнения оператора SQL
Применяется для описания подготовленного запроса
Каждый оператор SQL начинается с глагола, представляющего собой ключевое слово, определяющее, что именно делает этот
оператор (SELECT, INSERT, DELETE...). В операторе содержатся
также предложения, содержащие сведения о том, над какими
данными производятся операции. Каждое предложение начинается с ключевого слова, такого как FROM, WHERE и др. Структура предложения зависит от его типа – ряд предложений содержит имена таблиц, или полей, некоторые могут содержать дополнительные ключевые слова, константы или выражения.
В синтаксических конструкциях используются следующие
обозначения: квадратные скобки ([]) – означают, что конструкции, заключенные в эти скобки, являются необязательными (т. е.
14
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
могут быть опущены); фигурные скобки ({}) – означают, что
конструкции, заключенные в эти скобки, должны рассматриваться как целые синтаксические единицы, т. е. они позволяют уточнить порядок разбора синтаксических конструкций, заменяя
обычные скобки, используемые в синтаксисе SQL; многоточие
(...) – указывает на то, что непосредственно предшествующая ему
синтаксическая единица факультативно может повторяться один
или более раз; прямая черта (|) – означает наличие выбора из двух
или более возможностей; точка с запятой (;) – завершающий элемент предложений SQL; запятая (,) – используется для разделения элементов списков.
Контрольные вопросы 1. На какое представление данных ориентирован язык SQL?
2. Назовите отличительные черты реализации от стандарта языка.
3. Какой раздел стандарта SQL99 определяет процедурные расширения языка SQL?
4. Какой раздел стандарта SQL99 описывает средства языка SQL,
предназначенные для работы с мультимедийными данными?
5. В чем отличие языка SQL от реляционной алгебры?
15
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Глава 2. Пример БД «Ресторан» Во всех примерах, приводимых в данной работе, будет рассматриваться база данных, описывающая следующую предметную область.
2.1. Описание Кладовая ресторана периодически пополняется продуктами
(таблица Продукты). Каждый продукт имеет следующие характеристики: уникальный номер, название, основные пищевые вещества (белки, жиры и углеводы даны в граммах), минеральные вещества (калий, кальций, натрий) и витамины (B2, PP, C) (в миллиграммах из расчета на 1кг продукта).
В таблице Блюда представлены уникальные номера блюд, их
названия, коды видов, основной продукт (столбец Основа), масса
порции в граммах и приведенная стоимость в рублях приготовления одной порции (столбец Труд).
В таблице Рецепты приведена технология приготовления
блюд. Их выделение в отдельную таблицу произведено потому,
что одно и то же блюдо может иметь несколько разных рецептов.
Таблица Состав связывает между собой таблицы Блюда и
Продукты, оговаривая, какая масса (в граммах) того или иного
продукта (столбец Вес) должна входить в состав одной порции
блюда.
Шеф-повар ежедневно получает от администратора сведения
о количестве (в килограммах) имеющихся продуктов и их текущей стоимости (таблица Наличие). Используя эти сведения, он
определяет по таблице Состав перечень тех блюд, которые можно
приготовить из этих продуктов, а также калорийность и стоимость таких блюд. При этом стоимость блюда складывается из
стоимости и массы продуктов, необходимых для приготовления
одной его порции, а также из трудозатрат на ее приготовление
(таблица Блюда). Калорийность же определяется по массе и калорийности каждого из продуктов блюда. (Для получения значения калорийности продукта исходят из того, что при окислении
1 г углеводов или белков в организме освобождается в среднем
4.1 ккал, а при окислении 1 г жиров – 9.3 ккал.)
16
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
На кажды
Н
ый деньь шеф-поовар сосставляет меню. В этом меню
(табли
ица Мен
ню) пред
длагаетсяя по несколько альтерна
а
ативных
х блюд
каждоого видаа. Заказы
ы клиенттов фиксируютсяя в табли
ице Закаазы.
2.2. Д
Диаграм
мма 17
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
2.3. Данные в таблицах Таблица Продукты
ID_п
родукта
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Продукт
Говядина
Судак
Масло
Майонез
Яйца
Сметана
Молоко
Творог
Морковь
Лук
Помидоры
Зелень
Рис
Мука
Яблоки
Сахар
Кофе
Белки Жиры
189
190
60
31
127
26
28
167
13
17
6
9
70
106
4
NULL
127
124
80
825
670
115
300
32
90
1
NULL
NULL
NULL
6
13
NULL
NULL
36
Углеводы
К
Са
Na
B2
PP
C
NULL
0
90
26
7
28
47
13
70
95
42
20
773
732
113
998
9
3150
1870
230
480
1530
950
1460
1120
2000
1750
290
340
540
1760
2480
30
9710
90
270
220
280
550
850
1210
1640
510
310
140
275
240
240
160
20
180
600
NULL
740
NULL
710
320
1500
1410
210
180
400
75
260
120
260
10
180
1,5
1,1
0,1
NULL
4,4
1
1,3
2,7
0,7
0,2
0,4
1,2
0,4
1,2
0,3
NULL
0,3
28
10
1
NULL
1,9
1
1
4
9,9
2
5,3
4
16
22
3
NULL
1,8
0
30
NULL
NULL
NULL
2
10
5
50
100
250
380
NULL
NULL
NULL
130
NULL
Таблица Блюда
ID_блюда
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Блюдо
Салат летний
Салат мясной
Салат витаминный
Салат рыбный
Паштет из рыбы
Мясо с гарниром
Сметана
Творог
Суп харчо
Суп-пюре из рыбы
Уха из судака
Суп молочный
Баструма
Бефстроганов
Судак по-польски
Драчена
Морковь с рисом
Сырники
Омлет с луком
Вид
1
1
1
1
1
1
1
1
2
2
2
2
3
3
3
3
3
3
3
Основа
Овощи
Мясо
Овощи
Рыба
Рыба
Мясо
Молоко
Молоко
Мясо
Рыба
Рыба
Молоко
Мясо
Мясо
Рыба
Яйца
Овощи
Молоко
Яйца
18
Вес
200
200
200
200
120
250
140
140
500
500
500
500
300
210
160
180
260
220
200
Труд
3
4
4
4
5
3
1
2
5
6
5
3
5
6
5
4
3
4
5
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
20
21
22
23
24
25
26
27
28
29
30
31
32
33
Каша рисовая
Пудинг рисовый
Вареники ленивые
Помидоры с луком
Суфле из творога
Рулет с яблоками
Яблоки печеные
Суфле яблочное
Крем творожный
"Утро"
Компот
Молочный напиток
Кофе черный
Кофе на молоке
3
3
3
3
3
4
4
4
4
5
5
5
5
5
Крупа
Крупа
Молоко
Овощи
Молоко
Фрукты
Фрукты
Фрукты
Молоко
Фрукты
Фрукты
Молоко
Кофе
Кофе
210
160
220
260
280
200
160
220
160
200
200
200
200
200
4
6
4
4
6
5
3
6
4
5
2
2
1
2
Таблица Состав
Блюдо
1
1
1
1
2
2
2
2
2
2
3
3
3
3
3
3
4
4
4
4
4
4
5
5
5
5
6
6
6
6
Продукт
4
11
12
15
1
4
5
9
11
12
6
10
11
12
15
16
2
4
5
9
11
12
2
3
9
12
1
4
11
12
Вес
15
100
5
80
65
20
20
40
35
20
50
15
55
20
55
5
50
40
20
35
50
5
80
25
40
5
80
30
150
10
Блюдо
9
9
9
9
9
9
10
10
10
10
10
11
11
11
11
11
12
12
12
12
13
13
13
13
13
14
14
14
14
14
Продукт
1
3
10
11
12
13
2
3
7
12
14
2
3
9
10
12
3
7
13
16
1
3
10
11
12
1
3
6
7
10
19
Вес
80
15
30
25
15
35
70
20
250
5
15
100
5
20
20
2
5
350
35
5
180
5
40
100
20
90
5
20
50
10
Блюдо
20
21
21
21
21
21
22
22
22
22
23
23
23
24
24
24
24
24
24
24
25
25
25
25
25
26
26
26
27
27
Продукт
16
3
5
6
13
16
5
6
8
16
3
10
11
3
5
6
7
8
14
16
3
8
14
15
16
3
15
16
3
5
Вес
10
20
20
30
70
15
8
30
140
15
20
65
250
10
40
30
100
80
10
20
20
20
30
120
35
2
150
20
2
80
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
7
7
8
8
8
15
16
16
16
16
16
17
17
17
17
17
17
18
18
6
16
6
8
16
12
3
5
6
7
14
3
7
9
12
13
14
5
6
125
15
50
75
15
5
5
120
15
35
9
20
50
150
10
25
5
10
30
14
14
15
15
15
15
15
18
18
18
19
19
19
19
20
20
20
20
20
12
14
2
3
5
9
10
8
14
16
3
5
7
10
3
7
13
14
15
5
3
100
20
20
20
10
140
15
15
15
120
45
20
5
75
50
20
75
27
27
27
28
28
28
28
28
29
29
29
30
30
31
31
31
32
33
33
Таблица Наличие
Продукт
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Количество
108
0
73
39
61
88
214
92
0
77
46
13
54
91
117
98
37
7
15
16
3
5
6
8
16
9
15
16
15
16
7
15
16
17
7
16
150
50
35
10
20
20
100
15
200
150
15
70
10
150
150
25
8
75
25
Таблица Меню
Блюдо
1
2
2
4
9
10
12
14
15
15
16
25
27
30
33
…
Цена
429,84
0,00
274,61
97,46
111,83
206,60
83,80
82,80
0,00
46,30
51,70
34,96
51,17
43,77
189,92
96,14
166,50
20
Вид
1
1
1
1
2
2
2
3
3
3
3
4
4
5
5
Дата
2011-01-02
2011-01-02
2011-01-03
2011-01-03
2011-01-02
2011-01-03
2011-01-02
2011-01-02
2011-01-02
2011-01-03
2011-01-03
2011-01-03
2011-01-02
2011-01-03
2011-01-02
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Таблица Заказ
ID_
заказ
1
1
1
2
2
3
3
3
4
5
…
КоличеБлюдо ство_
порций
1
10
14
4
15
6
27
2
33
2
2
3
15
2
16
1
25
1
4
3
Таблица Справочник_вид_блюда
ID_вид
1
2
3
4
5
Дата
2011-01-02
2011-01-02
2011-01-02
2011-01-02
2011-01-02
2011-01-03
2011-01-03
2011-01-03
2011-01-03
2011-01-03
Вид
Закуска
Суп
Горячее
Десерт
Напиток
Контрольные вопросы 1. Перечислите типы связей представленных в диаграмме «Ресторан».
2. Между сущностями «Блюда» и «Продукты» поддерживается связь
«многие ко многим». Как эта связь представлена в диаграмме «Ресторан».
3. Для каждой таблицы укажите первичный и внешние ключи.
4. Что показывает столбец «Allow Nulls» при описании полей таблицы?
Глава 3. Выборка данных Все запросы на получение практически любого количества
данных из одной или нескольких таблиц выполняются с помощью единственного оператора SELECT. В общем случае результатом выполнения оператора SELECT является другая таблица. К
этой новой (рабочей) таблице может быть снова применена операция SELECT и т. д., т. е. такие операции могут быть вложены
друг в друга. Представляет исторический интерес тот факт, что
именно возможность включения одного предложения SELECT
внутрь другого послужила мотивировкой использования прилагательного "структурированный" в названии языка SQL.
3.1. Оператор SELECT. Синтаксис Оператор SELECT может использоваться как:
21
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
 самостоятельная команда на получение и вывод строк таблицы, сформированной из столбцов и строк одной или нескольких таблиц (представлений);
 элемент WHERE- или HAVING-условия (сокращенный вариант предложения, называемый "вложенный запрос");
 фраза выбора в командах CREAT VIEW, DECLARE
CURSOR или INSERT;
 средство присвоения глобальным переменным значений из
строк сформированной таблицы (INTO-фраза).
Оператор SELECT (выбрать) имеет следующий формат:
SELECT [[ALL] | DISTINCT] [ТОР n [PERCENT]] [WITH TIES] {* | элемент_SELECT [, элемент_SELECT] ...}
FROM
таблица [псевдоним] [, таблица [псевдоним]] ...
[WHERE условие_отбора_строк]
[GROUP BY [таблица.]столбец [, [таблица.]столбец] ...
[HAVING условие_отбора_групп]]
[ORDER BY
{[таблица.]столбец | номер_элемента_SELECT} [[ASC] |
DESC]
[, {[таблицаю]столбец | номер_элемента_SELECT } [ [ASC] | DESC] ] ...] ];
Этот оператор можно прочитать следующим образом:
SELECT (выбрать) – данные из указанных столбцов и (если
необходимо) выполнить перед выводом их преобразование в соответствии с указанными выражениями и (или) функциями;
FROM (из) – перечисленных таблиц, в которых расположены
эти столбцы;
WHERE (где) – строки из указанных таблиц должны удовлетворять указанному перечню условий отбора строк;
GROUP BY (группируя по) – указанному перечню столбцов с
тем, чтобы получить для каждой группы единственное агрегированное значение, используя во фразе SELECT SQL-функции SUM
(сумма), COUNT (количество), MIN (минимальное значение), MAX
(максимальное значение) или AVG (среднее значение);
HAVING (имея) – в результате лишь те группы, которые
удовлетворяют указанному перечню условий отбора групп;
ORDER BY (упорядочить) – результаты выбора данных по
указанному перечню столбцов. При этом упорядочение можно
22
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
производить в порядке возрастания – ASC (ASCending) (по умолчанию) или убывания DESC (DESCending).
Рассмотрим каждую строку этого оператора подробно.
Раздел SELECT.
В разделе SELECT указывается список столбцов, которые
будут включены в результат выборки. Структура раздела SELECT следующая:
SELECT [[ALL] | DISTINCT] [ТОР n [PERCENT]] [WITH
TIES] {* | элемент_SELECT [, элемент_SELECT]...}
Параметры раздела обозначают следующее:
ALL – указывает, что в результат выборки должны быть
включены все строки, возвращаемые запросом, т. е. выборка может содержать повторяющиеся строки (используется по умолчанию).
DISTINCT – позволяет исключить из выборки повторяющиеся строки.
ТОР n [PERCENT] [WITH TIES] – ограничивает количество строк в выборке. Параметр n задает максимальное количество
строк, при указании параметра PERCENT количество строк задается в процентах от общего числа строк, возвращаемых запросом.
* – означает включение в результат выборки всех столбцов
всех таблиц, участвующих в запросе и указанных в разделе
FROM. При этом порядок вывода полей соответствует порядку, в
котором эти поля определялись при создании таблиц;
элемент_SELECT – список столбцов, которые включены в
результат выборки.
Структура этой конструкции следующая:
элемент_SELECT = {[таблица.]* | [таблица.]столбец [AS
псевдоним] | (выражение) [AS псевдоним] | константа [AS псевдоним] | переменная [AS псевдоним] | SQL_функция [AS псевдоним]}
термин таблица – используется для обобщения понятий: базовая таблица, представление;
выражение – подразумевает выражение, на основе которого
будет формироваться содержимое столбца.
AS псевдоним – определение псевдонима для столбца.
23
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Раздел FROM.
Раздел FROM содержит список таблиц, из которых будет
производиться выборка данных.
Структура раздела FROM следующая:
FROM таблица [псевдоним] [, таблица [псевдоним]] ...
псевдоним – позволяет присвоить таблице имя, под которым
на неё можно будет ссылаться в запросе, служит для упрощения
вида запроса при работе с длинными именами таблиц и для именования временных таблиц (полученных в результате выполнения оператора SELECT).
Также в разделе FROM можно указать метод связывания
таблиц между собой (фраза JOIN будет рассмотрена в п. 3.4.).
Следует обратить внимание на тот факт, что если даже ни
один из столбцов таблицы не включен в результат запроса, но
используется в других разделах запроса, то имя этой таблицы
также должно быть указано в разделе FROM.
3.2. Примеры запросов с использованием единственной таблицы 3.2.1. Выборка без использования фразы WHERE
3.2.1.1. Простейшие примеры
Пример 1.
Для каждого продукта, используемого в ресторане, указать
количество белков, жиров и углеводов.
SELECT
FROM
Продукт, Белки, Жиры, Углеводы
Продукты;
дает результат
Результат
Продукт
Говядина
Судак
Масло
Майонез
Яйца
…
Кофе
Белки
189
190
60
31
127
Жиры
124
80
825
670
115
Углеводы
NULL
NULL
90
26
7
127
36
9
При необходимости получения полной информации о продуктах, можно было бы дать запрос
24
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
SELECT ID_продукта, Продукт, Белки, Жиры, Углеводы, К, Са, Na, В2,
РР, С
FROM
Продукты;
или использовать его более короткую нотацию:
SELECT
FROM
*
Продукты;
Пример 2.
Выдать основу всех блюд:
SELECT
Основа FROM Блюда;
дает результат, показанный на
рис. 3.1 а.
Результат
Основа
Овощи
Мясо
Овощи
Рыба
Рыба
Мясо
Молоко
…
Кофе
а
Результат
Основа
Кофе
Крупа
Молок
Мясо
Овощи
Рыба
Фрукты
Яйца
б
Рис. 3.1 Простейшие запросы
3.2.1.2. Исключение дубликатов (DISTINCT)
В предыдущем примере был выдан правильный, но не совсем
удачный перечень основных продуктов: из него не были исключены дубликаты. Для исключения дубликатов необходимо дополнить запрос ключевым словом DISTINCT (различный, различные):
SELECT DISTINCT Основа
FROM
Блюда;
Результат приведен на рис. 3.1 б.
3.2.1.3. Выборка вычисляемых значений
Пример 3.
Нужно получить значение калорийности всех продуктов. В
этом запросе необходимо учесть, что при окислении 1 г углеводов или белков в организме освобождается в среднем 4.1 ккал, а
при окислении 1 г жиров – 9.3 ккал:
SELECT
FROM
Продукт, ((Белки+Углеводы)*4.1+Жиры*9.3) as ккал
Продукты;
Выражение ((Белки+Углеводы)*4.1+Жиры*9.3) не является
полем базовой таблицы, а следовательно, не имеет имени. Ис25
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
пользование псевдонима позволяет именовать результирующий
столбец (рис. 3.2 а).
Фраза SELECT может включать не только выражения, но и
отдельные числовые или текстовые константы. Следует отметить,
что текстовые константы должны заключаться в апострофы (')
(рис. 3.2 б).
SELECT
FROM
Продукт, 'Калорий =', ((Белки+Углеводы)*4.1+Жиры *9.3)
Продукты;
Результат
Продукт
Говядина
Судак
Масло
Майонез
Яйца
Сметана
Кофе
ккал
NULL
NULL
8287,5
6464,7
1618,9
3011,4
892,4
Результат
Продукт
Говядина
Судак
Масло
Майонез
Яйца
Сметана
Кофе
Калорий =
Калорий =
Калорий =
Калорий =
Калорий =
Калорий =
Калорий =
а
NULL
NULL
8287,5
6464,7
1618,9
3011,4
892,4
б
Рис. 3.2. Примеры запросов с вычисляемыми полями
3.2.1.4. NULL-значения в выражениях
В некоторых ячейках результата стоит NULL. Почему?
Как правило, применение NULL-значения в выражении приводит к результату, равному NULL. Например, SELECT
(5+NULL) вернет NULL, а не 5. Как и в случае простых выражений, при передаче большинству функций NULL-значений результатом будет NULL. Исключение составляют функции, специально предназначенные для работы с неопределенными значениями.
ISNULL (<проверяемое поле>,< значение, если проверяемое
поле равно NULL>) преобразует NULL- Результат
значение к значению, отличному от NULL. То- Продукт
Говядина
1928,1
гда запрос будет выглядеть так
SELECT
Продукт,
(ISNULL(Белки,0)+ISNULL(Углеводы,0))*4.1
+ISNULL(Жиры,0) *9.3
FROM
Продукты;
26
Судак
Масло
Майонез
Яйца
Сметана
Кофе
1523
8287,5
6464,7
1618,9
3011,4
892,4
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
3.2.2. Выборка c использованием фразы WHERE
Раздел WHERE предназначен для ограничения количества
строк, включаемых в результат выборки. Будут включены только
те строки, которые удовлетворяют условию отбора строк.
WHERE условие_отбора_строк,
где условие_отбора_строк – выражение логического типа (TRUE,
FALSE). В условии можно использовать операторы сравнения =
(равно), <> (не равно), < (меньше), <= (меньше или равно), >
(больше), >= (больше или равно), которые могут предваряться
оператором NOT, создавая, например, отношения "не меньше" и
"не больше";
условие_отбора_строк – предназначено для объединения множества логических условий, каждое из которых возвращает выражение логического типа. Объединение выполняется с помощью
операторов AND или OR.
3.2.2.1. Использование операторов сравнения
Пример 4.
Получить перечень продуктов, содержащих менее 50 г углеводов
SELECT
FROM
WHERE
Продукт, Белки, Жиры, Углеводы, K, Ca, Na, B2, PP, C
Продукты
(Углеводы < 50);
и получить:
Результат
Продукт
Майонез
Яйца
Сметана
Молоко
Творог
Помидоры
Зелень
Кофе
Белки
31
127
26
28
167
6
9
127
Жиры
670
115
300
32
90
NULL
NULL
36
Углеводы
26
7
28
47
13
42
20
9
К
480
1530
950
1460
1120
290
340
9710
Са
280
550
850
1210
1640
140
275
180
Na
NULL
710
320
1500
1410
400
75
180
B2
NULL
4,4
1
1,3
2,7
0,4
1,2
0,3
PP
NULL
1,9
1
1
4
5,3
4
1,8
C
NULL
NULL
2
10
5
250
380
NULL
Пример 5.
Получить перечень продуктов, практически не содержащих
углеводов и натрия.
SELECT
FROM
WHERE
Продукт, Белки, Жиры, Углеводы, K, Ca, Na, B2, PP, C
Продукты
(Углеводы = 0) AND (Na = 0);
27
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Результат
Продукт Белки
Жиры
Углеводы К
Са
Na
B2
PP
C
результат запроса пуст.
3.2.2.2. Сравнение с NULL
Заметим, что отсутствующие и пустые значения – это две
большие разницы. Целое, значение которого отсутствует, это не
то же самое, что целое, значение которого равно нулю. Неопределенная строка – это не то же самое, что строка нулевой длины
или строка, содержащая одни пробелы. Эта разница важна, так
как сравнения между пустыми и неопределенными значениями
всегда будут неудачны. Фактически, NULL-значения даже не
равны друг другу в таких сравнениях.
Возможность неопределенных значений в реляционных базах
данных означает, что для любого сравнения возможны три результата: Истина (True), Ложь (False) или Неизвестно (Unknown).
Это требует использования трехзначной логики.
AND
True
False
Unknown
NOT
True
True
False
Unknown
True
False
False
False
False
False
False
True
Unknown
Unknown
False
Unknown
OR
True
False
Unknown
True
True
True
True
False
True
False
Unknown
Unknown
True
Unknown
Unknown
Unknown
Unknown
Для выявления равенства значения некоторого столбца неопределенному применяют специальные стандартные предикаты:
<Столбец> IS NULL
и
< Столбец > IS NOT NULL.
Пример 6.
Вывести все продукты, не содержащие углеводов
SELECT
FROM
WHERE
Продукт
Продукты
(Углеводы IS NULL) OR (Углеводы = 0);
Результат
Продукт
Говядина
Судак
3.2.2.3. Использование BETWEEN
С помощью BETWEEN … AND … (находится в интервале
от ... до ...) можно отобрать строки, в которых значения какоголибо столбца находятся в заданном диапазоне.
28
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Пример 7.
Выдать перечень продуктов, в которых значение содержания белка находится в диапазоне
от 10 до 50:
SELECT
FROM
WHERE
Продукт, Белки
Продукты
Белки BETWEEN 10 AND 50;
Можно задать и NOT BETWEEN (не принадлежит диапазону между), например:
SELECT
FROM
WHERE
AND 50)
Продукт, Белки, Жиры
Продукты
(Белки NOT BETWEEN 10
AND (Жиры > 100);
Результат
Продукт
Говядина
Масло
Яйца
Результат
Продукт
Майонез
Сметана
Молоко
Морковь
Лук
Белки
189
60
127
Белки
31
26
28
13
17
Жиры
124
825
115
BETWEEN удобен при работе с
данными, задаваемыми интервалами, начало и конец которых
расположены в разных столбцах.
3.2.2.4. Использование IN (NOT IN)
Задает поиск выражения, включенного или исключенного из
списка. Выражение поиска может быть константой или именем
столбца, а списком может быть набор констант или, что чаще, вложенный запрос. Список значений необходимо заключать в скобки.
Пример 8.
Выдать сведения о блюдах на основе яиц, крупы и овощей.
SELECT
FROM
WHERE
Результат
ID_блюда
1
3
16
17
19
20
21
23
*
Блюда
Основа IN (’Яйца’, ’Крупа’, ’Овощи’);
Блюдо
Салат летний
Салат витаминный
Драчена
Морковь с рисом
Омлет с луком
Каша рисовая
Пудинг рисовый
Помидоры с луком
Вид
1
1
3
3
3
3
3
3
29
Основа
Овощи
Овощи
Яйца
Овощи
Яйца
Крупа
Крупа
Овощи
Вес
200
200
180
260
200
210
160
260
Труд
3
4
4
3
5
4
6
4
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Рассмотренная форма IN является в действительности просто
краткой записью последовательности отдельных сравнений, соединенных операторами OR. Предыдущее предложение эквивалентно такому:
SELECT
FROM
WHERE
*
Блюда
Основа=’Яйца’ OR Основа=’Крупа’ OR Основа=’Овощи’;
3.2.2.5. Использование LIKE
LIKE определяет, совпадает ли указанная символьная строка
с заданным шаблоном. Шаблон может включать обычные символы и символы-шаблоны. Во время сравнения с шаблоном необходимо, чтобы его обычные символы в точности совпадали с
символами, указанными в строке. Использование символовшаблонов с оператором LIKE предоставляет больше возможностей, чем использование операторов сравнения строк = и < >.
Синтаксис:
выражение [NOT] LIKE строка_шаблон [ESCAPE esc_символ]
Символы строки_шаблона интерпретируются следующим образом:
 символ _ (подчеркивание) – заменяет любой одиночный символ,
 символ % (процент) – заменяет любую последовательность из
N символов (где N может быть нулем),
 все другие символы означают просто сами себя.
 [] – любой одиночный символ внутри диапазона([a-f]) или набора [abcdf].
 [^]- любой одиночный символ, не принадлежащий диапазону
([^a-f]) или набору [^abcdf].
Результат
Блюдо
Салат летний
Салат мясной
Салат витаминный
Салат рыбный
Пример 9.
Выдать перечень салатов.
SELECT
FROM
WHERE
Блюдо
Блюда
Блюдо LIKE 'Салат%';
В приведенном примере SELECT будет осуществлять выборку записей из таблицы Блюда, для которых значение в столбце Блюдо начинается сочетанием 'Салат' и содержит любую последовательность из нуля или более символов, следующих за со30
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
четанием 'Салат'. Если бы среди блюд были "Луковый салат",
"Фруктовый салат" и т. п., то они не были бы найдены. Для их
отыскания надо изменить фразу WHERE:
WHERE Блюдо LIKE '%салат%'
(при отсутствии различий между малыми и большими буквами
(такую настройку допускают некоторые СУБД)).
А что делать, если необходимо искать знак процента или
знак подчеркивания в строке? В LIKE предикате, вы можете определить любой одиночный символ как символ ESCAPE. Символ
ESCAPE используется сразу перед процентом (подчеркиванием)
в предикате и означает, что процент (подчеркивание) будет интерпретироваться как обычный символ, а не как служебный. Например, мы могли бы найти все блюда, где присутствует символ
подчеркивания, следующим образом:
SELECT
FROM
WHERE
*
Блюда
Блюдо LIKE '%/_%' ESCAPE '/';
Данное предложение WHERE определяет '/' как символ
ESCAPE. Символ ESCAPE должен быть одиночным символом и
применяться только к одиночному символу сразу после него.
В примере выше символ % начала и символ % окончания обрабатываются как служебные символы; только подчеркивание
предоставлено само себе.
3.2.2.6. Выборка с упорядочением
Синтаксис:
ORDER BY {[таблица.]столбец | номер_элемента_SELECT} [[ASC] |
DESC]
[, {[таблица.]столбец | номер_элемента_SELECT } [ [ASC] |
DESC] ] ...]
Простейший вариант этой фразы – упорядочение строк результата по значению одного из столбцов с указанием порядка
(ASC возрастание (ASCending) (по умолчанию) или убывания
DESC (DESCending)) сортировки.
Пример 10.
Выдать перечень продуктов и содержание в них основных
веществ в порядке убывания содержания белка.
31
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Результат
Продукт
Судак
Говядина
Творог
Яйца
…
Яблоки
SELECT Продукт, Белки,
Жиры, Углеводы
FROM
Продукты
ORDER
BY Белки DESC;
Белки
190
189
167
127
Жиры
80
124
90
115
Углеводы
NULL
NULL
13
7
4
NULL 113
При включении в список ORDER BY нескольких столбцов
СУБД сортирует строки результата по значениям первого столбца списка, пока не появится несколько строк с одинаковыми значениями данных в этом столбце. Такие строки сортируются по
значениям следующего столбца из списка ORDER BY и т. д.
Например, выдать содержимое таблицы Блюда, отсортировав
ее строки по видам блюд и основе:
Результат
ID_блюда
7
8
2
6
1
3
4
5
12
9
…
Блюдо
Сметана
Творог
Салат мясной
Мясо с гарниром
Салат летний
Салат витаминный
Салат рыбный
Паштет из рыбы
Суп молочный
Суп харчо
SELECT
FROM
ORDER
*
Блюда
BY Вид, Основа;
Вид
1
1
1
1
1
1
1
1
2
2
Основа
Молоко
Молоко
Мясо
Мясо
Овощи
Овощи
Рыба
Рыба
Молоко
Мясо
Вес
140
140
200
250
200
200
200
120
500
500
Труд
1
2
4
3
3
4
4
5
3
5
Кроме того, в список ORDER BY можно включать не только
имя столбца, а его порядковую позицию в перечне SELECT. Благодаря этому, возможно упорядочение результатов на основе вычисляемых столбцов, не имеющих имен.
Например, запрос
SELECT
FROM
ORDER
Продукт, ((Белки + Углеводы)*4.1+Жиры*9.3)
Продукты
BY 2;
32
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Пример 11.
Найти продукт, содержащий наименьшее число калорий
SELECT TOP 1
Продукт,
(ISNULL(Белки,0)+ISNULL(Углеводы,0))*4.1+ISNULL(Жиры,0) *9.3
Результат
FROM
Продукты
Продукт
ORDER
BY 2 DESC;
Масло
8287,5
Результат упорядочен по убыванию цены, и с помощью
TOP 1 в выборку включена только первая строка.
3.2.3. Использование агрегатных функций
для подведения итогов
В SQL существует ряд специальных агрегатных (статических) функций. Каждая из этих функций оперирует совокупностью значений столбца некоторой таблицы и создает единственное значение, определяемое так:
 COUNT(столбец) – возвращает количество строк с непустым значением (не NULL) в заданном столбце,
 COUNT(*) – возвращает общее количество строк в выборке, включая строки со значением NULL,
 SUM (столбец) – возвращает сумму всех значений в пределах группы в заданном столбце, применима только к столбцам с
числовыми значениями,
 AVG (столбец) – возвращает среднее арифметическое для
указанного столбца в пределах строк, принадлежащих одной
группе, применима только к столбцам с числовым типом данных,
 MAX(столбец) – возвращает наибольшее значение в указанном столбце в пределах группы,
 MIN (столбец) – возвращает наименьшее значение в указанном столбце в пределах группы.
Следует отметить, что здесь столбец – это столбец виртуальной таблицы, в которой могут содержаться не только данные из
столбца базовой таблицы, но и данные, полученные путем функционального преобразования и (или) связывания символами
арифметических операций значений из одного или нескольких
столбцов. При этом выражение, определяющее столбец такой
таблицы, может быть сколь угодно сложным, но не должно содержать агрегатные функции (вложенность агрегатных функ33
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
ций не допускается). Однако из агрегатных функций можно составлять любые выражения.
Аргументу всех функций, кроме COUNT(*), может предшествовать ключевое слово DISTINCT (различный), указывающее,
что избыточные дублирующие значения должны быть исключены перед тем, как будет применяться функция.
Агрегатные функции могут быть использованы в качестве
выражений только в следующих случаях:
 список выбора инструкции SELECT (вложенный или внешний запрос);
 предложение HAVING.
3.2.3.1. Агрегатные функции без использования
фразы GROUP BY
Если не используется фраза GROUP BY, то в перечень элементов_SELECT можно включать лишь агрегатные функции или
выражения, содержащие такие функции. Другими словами, нельзя иметь в списке столбцы, не являющиеся аргументами агрегатных функций.
Пример 12.
Выдать данные о блюде (Блюдо=10), указать количество ингредиентов и их суммарный вес:
SELECT
FROM
WHERE
COUNT(Продукт) as Количество, SUM(Вес) as Вес
Состав
Результат
Количество
Блюдо = 10;
5
Вес
360
При выполнении запроса
SELECT
FROM
WHERE
Блюдо, COUNT(Продукт), SUM(Вес)
Состав
Блюдо = 10;
будет получено сообщение об ошибке:
Column 'Состав.Блюдо' is invalid in the select list because it is
not contained in either an aggregate function or the GROUP BY
clause.
Это связано с тем, что агрегатная функция создает единственное значение из множества значений столбца-аргумента, а для
"свободного" столбца должно быть выдано все множество его
значений. Без специального указания (оно задается фразой
GROUP BY) SQL не будет выяснять, одинаковы значения этого
34
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
множества (как в данном примере, где Блюдо = 10) или различны
(как было бы при отсутствии WHERE фразы). Поэтому подобный
запрос отвергается системой.
Правда, никто не запрещает дать запрос
SELECT
FROM
WHERE
'Блюдо 10', COUNT(Продукт), SUM(Вес)
Состав
Блюдо = 10;
Отметим также, что в столбце-аргументе перед применением
любой функции, кроме COUNT(*), исключаются все неопределенные значения. Если оказывается, что аргумент – пустое множество,
функция COUNT принимает значение 0, а остальные – NULL.
Например, для получения общего количества заказов, количества различных блюд и общего количества порций на указанную дату можно написать запрос
SELECT ’01.02.2011’ as Дата,
COUNT( DISTINCT ID_заказ) as Кол_заказов,
COUNT( DISTINCT блюдо) as Кол_блюд,
SUM(Количество_порций) as Кол_порций
FROM
Заказы
WHERE Дата=’10.02.2011’
и получить:
Результат
Дата
01.02.2011
Кол заказов
2
Кол блюд
5
Кол порций
24
Если в запросе указать дату, когда ресторан не работал
SELECT ’01.01.2011’ as Дата,
COUNT( DISTINCT ID_заказ) as Кол_заказов,
COUNT( DISTINCT блюдо) as Кол_блюд,
SUM(Количество_порций) as Кол_порций
FROM
Заказы
WHERE Дата=’01.01.2011’
будет получен
ответ:
Результат
Дата
01.01.2011
Кол_заказов
0
Кол_блюд
0
Кол_порций
NULL
3.2.3.2. Фраза GROUP BY
Мы показали, как можно вычислить статистику по заказам на
определенную дату.
35
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Пример 13.
Теперь требуется вычислить эту статистику за каждый отработанный день. Это можно легко сделать с помощью запроса
SELECT Дата,
COUNT( DISTINCT ID_заказ) as Кол_заказов,
COUNT( DISTINCT блюдо) as Кол_блюд,
SUM(Количество_порций) as Кол_порций
FROM
Заказы
GROUP
BY Дата;
Результат
Дата
2011-01-02
2011-01-03
…
Кол_заказов
2
3
Кол_блюд
5
5
Кол_порций
24
10
Фраза GROUP BY (группировать по) инициирует перекомпоновку указанной во FROM таблицы по группам, каждая из которых имеет одинаковые значения в столбце, указанном в
GROUP BY. В рассматриваемом примере строки таблицы Заказы
группируются так, что в одной группе содержатся все строки для
заказов с Дата=’ 2011-01-02’, в другой – для заказов с Дата=’
2011-01-03’ и т. д. Далее к каждой группе применяется фраза
SELECT. Каждое выражение в этой фразе должно принимать
единственное значение для группы, т. е. оно может быть либо
значением столбца, указанного в GROUP BY, либо арифметическим выражением, включающим это значение, либо константой,
либо одной из агрегатных функций, которая оперирует всеми
значениями столбца в группе и сводит эти значения к единственному значению (например, к сумме).
Если в запросе используются фразы WHERE и GROUP BY,
то строки, не удовлетворяющие фразе WHERE, исключаются до
выполнения группирования.
SELECT Дата,
COUNT( DISTINCT ID_заказ) as Кол_заказов,
COUNT( DISTINCT Блюдо) as Кол_блюд,
SUM(Количество_порций) as Кол_порций
FROM
Заказы
WHERE Дата BETWEEN ’01.01.2011’ and ’01.31.2011’
GROUP
BY Дата;
36
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Отметим, что фраза GROUP BY не предполагает ORDER BY.
Чтобы гарантировать упорядочение по дате результата рассматриваемого примера, следует дать запрос
SELECT Дата,
COUNT( DISTINCT ID_заказ) as Кол_заказов,
COUNT( DISTINCT Блюдо) as Кол_блюд,
SUM(Количество_порций) as Кол_порций
FROM
Заказы
WHERE Дата BETWEEN ’01.01.2011’ and ’31.01.2011’
GROUP
BY Дата
ORDER BY Дата;
Наконец, отметим, что строки таблицы можно группировать
по любой комбинации ее столбцов.
Так, по запросу
SELECT Дата, Блюдо,
SUM(Количество_порций) as Кол_порций
FROM
Заказы
WHERE Дата BETWEEN ’01.01.2011’ and ’01.31.2011’
GROUP
BY Дата, Блюдо
ORDER BY Дата;
можно на каждый день января, узнать какие блюда и в каком количестве порций были приготовлены:
Результат
Дата
2011-01-02
2011-01-02
2011-01-02
2011-01-02
2011-01-02
2011-01-03
2011-01-03
2011-01-03
2011-01-03
2011-01-03
Блюдо
1
14
15
27
33
2
4
15
16
25
Кол_порций
10
4
6
2
2
3
3
2
1
1
Синтаксис фразы GROUP BY
GROUP BY [ALL] [ CUBE | ROLLUP] {[таблица.]столбец [, [таблица.]столбец] …}
позволяет указать следующие параметры
ALL – означает включение в результат выборки всех групп,
независимо от того, соответствуют ли связанные с ним данные
существующим в разделе WHERE условиям выборки. В строках,
37
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
не соответствующих условию выборки, во всех столбцах, кроме
столбцов, по которым осуществляется группировка, будут выведены значения NULL.
Пример 14.
Результат
SELECT
FROM
WHERE
GROUP
Основа, COUNT(*) as Количество
Блюда
Основа = 'Овощи'
BY ALL Основа
Основа
Кофе
Крупа
Молоко
Мясо
Овощи
Рыба
Фрукты
Яйца
Количество
0
0
0
0
4
0
0
0
ROLLUP ( )
Формирует статистические строки
простого предложения GROUP BY и строки подытогов или строки со статистическими вычислениями высокого уровня, а также строки общего итога.
Количество возвращаемых группирований равно количеству
выражений в списке элементов GROPU BY плюс один.
Например, рассмотрим следующую инструкцию.
SELECT a, b, c, SUM( <expression> )
FROM
T
GROUP BY
ROLLUP(a, b, c)
Для каждого уникального сочетания значений (a, b, c), (a, b) и
(a) формируется одна строка с подытогом. Вычисляется также
строка общего итога.
Столбцы свертываются справа налево. Последовательность
расположения столбцов влияет на Результат
выходное группирование ROLLUP и
Кол_порДата
Блюдо
ций
может отразиться на количестве
10
строк в результирующем наборе. 2011-01-02 1
2011-01-02 14
4
Рассмотрим пример для нашей БД.
SELECT Дата, Блюдо,
SUM(Количество_порций) as
Кол_порций
FROM Заказы
WHERE Дата BETWEEN
’01.01.2011’ and ’01.31.2011’
GROUP
BY ROLLUP(Дата, Блюдо);
CUBE ( )
Формирует
статистические
строки
простого
предложения
38
2011-01-02
2011-01-02
2011-01-02
2011-01-02
2011-01-03
2011-01-03
2011-01-03
2011-01-03
2011-01-03
2011-01-03
NULL
15
27
33
NULL
2
4
15
16
25
NULL
NULL
6
2
2
24
3
3
2
1
1
10
34
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
GROUP BY, строки со статистическими вычислениями высокого
уровня конструкции ROLLUP и строки с результатами перекрестных вычислений.
Выходные данные CUBE являются группированием для всех
перестановок выражений в списке элементов GROPU BY.
Количество формируемых группирований равно (2n), где n –
количество выражений в списке элементов GROPU BY. Например, рассмотрим следующую инструкцию.
SELECT a, b, c, SUM(<expression>)
FROM
T
GROUP BY CUBE(a, b, c)
Формируется одна строка для каждого уникального сочетания значений (a, b, c), (a, b), (a, c), (b, c), (a), (b) и (c) с подытогом
для каждой строки и строкой
Результат
общего итога.
Дата
Блюдо Кол_порций
Выходные данные CUBE 2011-01-02
15
6
15
2
не зависят от порядка столб- 2011-01-03
NULL
15
8
цов.
16
1
Рассмотрим пример для 2011-01-03
NULL
16
1
нашей БД.
2011-01-03
25
1
SELECT Дата, Блюдо,
SUM(Количество_порций) as
Кол_порций
FROM
Заказы
WHERE Блюдо >= 15
GROUP BY CUBE(Дата, Блюдо);
NULL
2011-01-02
NULL
2011-01-02
NULL
NULL
2011-01-02
2011-01-03
25
27
27
33
33
NULL
NULL
NULL
1
2
2
2
2
14
10
4
3.2.3.3. Раздел HAVING
Предложение HAVING обычно используется c предложением GROUP BY. Предложение HAVING подобно предложению
WHERE, но применимо только к целым группам (то есть к строкам в результирующем наборе, представляющим собой группы),
тогда как предложение WHERE применимо к отдельным строкам. В запросе могут содержаться оба предложения: WHERE и
HAVING. В этом случае: предложение WHERE применяется сначала к отдельным строкам таблиц или возвращающих табличное
значение объектов в области схем. Группируются только строки,
39
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
которые удовлетворяют условиям в предложении WHERE; затем
предложение HAVING применяется к строкам в результирующем
наборе. Только строки, которые удовлетворяют условиям
HAVING, появляются в результирующем запросе. Можно применить предложение HAVING только к тем столбцам, которые
появляются в предложении GROUP BY или статистической
функции.
Когда GROUP BY не используется, предложение HAVING
работает так же, как и предложение WHERE.
Синтаксис:
[HAVING условие_отбора_групп]
Пример 15.
Вывести статистику заказов по конкретному блюду\
SELECT
Дата,
Блюдо, SUM(Количество_порций) AS Количество_порций
FROM
Заказы
Результат
GROUP BY Дата, Блюдо
Блюдо
Количество_порций
HAVING Блюдо = 15;
15
6
15
2
Эквивалентный ему запрос, но без HAVING
SELECT Дата, Блюдо, SUM(Количество_порций) AS Количество_порций
FROM
Заказы
WHERE Блюдо = 15
GROUP BY Дата, Блюдо;
будет работать быстрее, так как будут заранее (до группировки и
вычисления функции) отброшены ненужные строки.
Пример 16.
Вывести названия блюд, у которых количество заказанных
порций в день превышает 5:
SELECT
FROM
GROUP
HAVING
Дата, Блюдо, SUM(Количество_порций) AS Кол_порций
Заказы
Результат
BY Дата, Блюдо
Дата
Блюдо Кол_порций
SUM(Кол_порций) > 5;
2011-01-02 1
10
2011-01-02
15
Для данного запроса эквивалентного с предложением WHERE не существует.
40
6
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
3.3. Примеры запросов с использованием нескольких таблиц Типичен вопрос: как же получить сведения о том, какие продукты необходимы для приготовления того или иного блюда, какова его калорийность и стоимость, если нужные данные "рассыпаны" по нескольким различным таблицам? Не лучше ли
иметь одну большую таблицу, содержащую все сведения базы
данных "Ресторан"?
База данных – это множество взаимосвязанных сущностей
или отношений (таблиц) в терминологии реляционных СУБД.
При проектировании стремятся создавать таблицы, в каждой из
которых содержалась бы информация об одном и только об одном типе сущностей. Это облегчает модификацию базы данных и
поддержание ее целостности. Даже при отсутствии средств одновременного доступа ко многим таблицам нежелателен проект, в
котором информация о многих типах сущностей перемешана в
одной таблице. SQL же обладает великолепным механизмом для
одновременной или последовательной обработки данных из нескольких взаимосвязанных таблиц. В нем реализованы возможности "соединять" или "объединять" несколько таблиц и так называемые "вложенные подзапросы".
3.3.1. Соединения "с условием WHERE"
Вообще, соединения – это подмножества декартова произведения. Так как декартово произведение n таблиц – это таблица,
содержащая все возможные строки r, такие, что r является сцеплением какой-либо строки из первой таблицы, строки из второй
таблицы ... и строки из n-й таблицы (а мы уже научились выделять с помощью SELECT любое подмножество реляционной таблицы), то осталось лишь выяснить, можно ли с помощью
SELECT получить декартово произведение. Для получения декартова произведения нескольких таблиц, надо указать в предложении FROM перечень перемножаемых таблиц, а в предложении
SELECT – все их столбцы.
Пример 17.
Предположим нам необходимо получить состав продуктов
для каждого блюда.
41
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Информация для этого запроса хранится в таблицах Блюда,
Состав и Продукты. Для получения декартова произведения таблиц Блюда (n-строк), Состав (m-строк) и Продукты (k-строк) надо
написать запрос
SELECT
FROM
Блюда.*, Состав.*, Продукты.*
Блюда, Состав, Продукты;
Получим таблицу, содержащую (n × m × k) строк:
ID_блюда
Блюдо
Вид Основа
Вес Труд Блюдо Продукт Вес
1
1
1
1
…
Салат летний
Салат летний
Салат летний
Салат летний
1
1
1
1
200
200
200
200
ID_продукта
1
1
1
1
Продукт
Говядина
Говядина
Говядина
Говядина
Белки
189
189
189
189
Овощи
Овощи
Овощи
Овощи
Жиры
124
124
124
124
Углеводы
NULL
NULL
NULL
NULL
3
3
3
3
1
1
1
1
К
3150
3150
3150
3150
Са
90
90
90
90
4
11
12
15
Na
600
600
600
600
B2
1,5
1,5
1,5
1,5
15
100
5
80
PP
28
28
28
28
C
0
0
0
0
Результат далек от нужного, так как содержит лишние строки. Если из декартова произведения убрать ненужные строки и
столбцы, то можно получить актуальные таблицы, соответствующие операции соединения.
Очевидно, что отбор актуальных строк обеспечивается вводом в запрос фразы WHERE, в которой устанавливается соответствие между кодами блюд в таблицах Блюда и Состав (Блюда.ID_Блюда = Состав.Блюдо) и кодами продуктов в таблицах
Состав и Продукты (Состав.Продукт = Продукты.ID_Продукта).
Такой скорректированный запрос имеет вид:
SELECT
FROM
WHERE
Блюда.Блюдо, Продукты.Продукт, Состав.Вес
Блюда, Состав, Продукты
Блюда.ID_Блюда = Состав.Блюдо
and Состав.Продукт = Продукты.ID_Продукта;
Запрос позволяет получить эквисоединение таблиц (соединение при равенстве значений столбцов) Блюда, Состав и Продук42
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
ты. В строке SELECT указаны лишь те столбцы, которые необходимы в результате:
Результат
Блюдо
Салат летний
Салат летний
Салат летний
Салат летний
Салат мясной
Салат мясной
Салат мясной
Салат мясной
Салат мясной
Салат мясной
Салат витаминный
Салат витаминный
Салат витаминный
Салат витаминный
…
Продукт
Майонез
Помидоры
Зелень
Яблоки
Говядина
Майонез
Яйца
Морковь
Помидоры
Зелень
Сметана
Лук
Помидоры
Зелень
Вес
15
100
5
80
65
20
20
40
35
20
50
15
55
20
Рассмотренный вид соединения является внутренним, так как
исключает не совпадающие по значению строки. То есть если в
БД занесено блюдо, но для него ещё не указан состав, то информация о таком блюде не появится в результирующей таблице, а
иногда такая информация необходима.
Решить эту проблему помогают внешние соединения. Для
получения внешнего соединения применяют один из трех подходов: 1) использование подзапроса с предикатом EXISTS; 2) объединение двух запросов с помощью UNION; 3) применение специальной конструкции JOIN в предложении FROM.
Все они будут рассмотрены далее.
Следует отметить, что в предложении SELECT доступны все
поля соединённых таблиц, даже если они не используются в запросе. Каждое имя поля уточняется с помощью точечной нотации, при которой имя поля следует через точку за именем таблицы. Такое уточнение необходимо, когда одно имя поля в запросе
встречается более одного раза. (Конечно, эти поля будут находиться в разных таблицах, ведь невозможно создать два поля с
одинаковыми именами в одной таблице.) Если не идентифицировать однозначно эти поля, то получим синтаксическую ошибку о
43
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
неопределённости имён. Уточнение имён необходимо производить вне зависимости от того, ссылается ли запрос на оба поля
или нет – каждая ссылка должна быть уточнена.
Когда поле в запросе встречается всего один раз, уточнение
имён становится необязательным. Поэтому мы могли получить
такой же результат, воспользовавшись следующим кодом:
SELECT
FROM
WHERE
Блюда.Блюдо, Продукты.Продукт, Состав.Вес
Блюда, Состав, Продукты
ID_Блюда = Состав.Блюдо
and Состав.Продукт = ID_Продукта;
Тем не менее указывать полные имена полей – это хорошая
практика, поскольку иначе, взглянув на запрос, вы не всегда
сможете сказать, к какой таблице относится каждое поле.
В некотором смысле такой запрос будет самодокументированным – выполняемые им действия очевидны, поэтому его легче описать в документации.
Псевдонимы таблиц
Другой способ задания имён полей – это использование
псевдонимов таблиц. Псевдоним – это альтернативное имя, присвоенное таблице в запросе. Как правило, псевдоним короче реального имени таблицы. Вот как наше соединение записывается с
помощью псевдонимов:
SELECT
FROM
WHERE
b.Блюдо, p.Продукт, s.Вес
Блюда b, Состав s, Продукты p
b.ID_Блюда = s.Блюдо and s.Продукт = p.ID_Продукта;
Здесь таблице Блюда присвоен псевдоним b, таблице Состав – псевдоним s, а таблице Продукты – псевдоним p. Вы можете использовать в качестве псевдонима любые имена, псевдонимы создаются лишь на время выполнения запроса. Многие
программисты стараются использовать однобуквенные псевдонимы по мере возможности, поскольку так уменьшается объём
кода и повышается его читаемость. Единственная тонкость состоит в том, что как только вы определили псевдоним для таблицы, то в текущем запросе уже не можете использовать реальное
имя таблицы, можно указывать только её псевдоним. Псевдонимы действуют только на протяжении одного запроса.
44
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
3.3.2. Соединение таблиц с дополнительными условиями
При формировании соединения создается рабочая таблица, к
которой применимы все операции, рассмотренные ранее: отбор
нужных строк соединения (WHERE фраза), упорядочение получаемого результата (ORDER BY фраза) и агрегатирование данных (SQL-функции и GROUP BY фраза).
Пример 18.
Выручка на каждый день (только продукты без учета трудозатрат).
SELECT z.Дата,
SUM( ( s.Вес*n.Цена/1000 )*z.Количество_порций
) as Выручка
FROM
Заказы
z, Состав s, Наличие n
WHERE z.Блюдо=s.Блюдо and
s.Продукт=n.Продукт
GROUP BY
z.Дата;
Результат
Дата
2011-01-02
2011-01-03
Выручка
556,6954
203,155
3.3.2.1. Соединение таблицы со своей копией
В ряде приложений возникает необходимость одновременной
обработки данных какой-либо таблицы и одной или нескольких
ее копий, создаваемых на время выполнения запроса.
Например, при формировании Блюд (таблица БЛЮДА) возможен повторный ввод данных о каком-либо блюде с присвоением ему второго идентификатора. Для выявления таких ошибок
можно соединить таблицу Блюда с ее временной копией, установив в WHERE условие на равенство значений всех одноименных
столбцов этих таблиц, кроме столбцов с ID_Блюда (для последних надо установить условие неравенства значений).
Временную копию таблицы можно сформировать, указав имя
псевдонима за именем таблицы во фразе FROM.
Пример 19.
Найти ошибки при формировании таблицы Блюда (повтор
блюд).
SELECT К1.Блюдо,
К1.Основа, К1.ID_Блюда as ID , К2.ID_Блюда as Дубликат
FROM
Блюда К1, Блюда К2
WHERE К1.Блюдо = К2.Блюдо AND К1.Основа = К2. Основа
AND К1.ID_Блюда<> К2.ID_Блюда;
45
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Пример 20.
Нарастающий итог количества заказанных порций для блюда
с номером 15.
SELECT z1.Дата , z1.Блюдо, SUM(z.Количество_порций) as Итог
FROM
Заказ z, Заказ z1
WHERE
z.Блюдо=z1.Блюдо and z.Дата<=z1.Дата and z1.Блюдо=15
GROUP BY z1.Дата, z1.Блюдо
Результат
Дата
2011-01-02
2011-01-03
Блюдо
15
15
Итог
6
8
3.4. Соединения нескольких таблиц, используя JOIN В этом случае способ соединения таблиц указывается в
предложении FROM с помощью специального ключевого слова
вместе с ключевым словом JOIN. JOIN соединяет строки двух
таблиц, основываясь на правиле, которое называется условием
соединения, – оно сравнивает значения в строках обеих таблиц и
определяет, какие из них должны быть в результате соединения.
Существует три основных типа соединения:
 внутреннее соединение, задаваемое с помощью ключевых
слов INNER JOIN.
Синтаксис:
FROM таблица_А [ INNER ] JOIN таблица_B ON условие_соединения
 внешнее соединение, которое может принимать три формы:
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
Синтаксис:
FROM таблица_А { LEFT | RIGHT | FULL } [ OUTER ] JOIN
таблица_B ON условие_соединения
 перекрёстное соединение, задаваемое ключевыми словами
CROSS JOIN.
Синтаксис:
FROM таблица_А CROSS JOIN таблица_B ON условие_соединения
46
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Чтобы визуально представить себе работу A
B
JOIN, мы будем использовать две таблицы с 102
101
именами R1 и R2, показанные на рис. 3.3.
104
102
106
104
Не задумывайтесь пока о том, что пред106
ставляют собой таблицы R1 и R2. В них может 107
108
быть всё что угодно. Смысл этого примера в
том, чтобы сакцентировать ваше внимание на Рис. 3.3. Таблицы
значениях соединяемых полей.
R1 и R2
3.4.1. Внутреннее соединение
Во внутреннем соединении возвращаются только те строки, которые соответствуют условию, указанному после ключевого слова ON. Это наиболее распространённый вид соединений.
В следующем примере, как и в большинстве случаев, условие ON
указывает два поля, которые должны иметь совпадающие значения. Здесь, если значение (поля A) в строке первой таблицы (R1)
равно значению (поля В) в строке второй таблицы (R2), условие
выполняется и строки соединяются:
SELECT
FROM
А, В
R1 INNER JOIN R2
A
102
104
106
107
ON
А=В;
Результат
А
102
104
106
B
101
102
104
106
108
В
102
104
106
Рис. 3.4. R1 INNER JOIN R2
Рис. 3.4. иллюстрирует принцип работы внутреннего соединения.
Как вы можете видеть, строка из R1 соединяется со строкой
из R2, если их значения одинаковы. Поэтому в результате возвращаются значения 102, 104 и 106. Значение 107 из таблицы R1
не имеет соответствий в таблице R2 и поэтому не включается в
результат. Аналогично, значения 101 и 108 из таблицы R2 не
встречаются в таблице R1, поэтому и они тоже не включаются в
набор.
47
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Данная форма эквивалентна следующему запросу с условием
WHERE
SELECT
А, В FROM
R1, R2
WHERE А = В;
3.4.2. Внешнее соединение
Внешние соединения отличаются от внутренних тем, что могут возвращать строки, соответствующие условию, и некоторые
из тех, что не соответствуют ему.
3.4.2.1. Левое внешнее соединение
В левом внешнем соединении результатом являются все
строки левой таблицы, вне зависимости от того, имеют ли они
подходящую пару в правой таблице. Но какая же из них левая, а
какая правая? Оказывается, это таблицы, указанные слева и справа от ключевых слов OUTER JOIN. Например, в следующей команде, R1 – левая таблица, а R2 – правая, и задано левое внешнее
объединение.
SELECT
FROM
А, В
R1 LEFT OUTER JOIN R2
ON
А=В;
Рис. 3.5 демонстрирует результаты такого соединения.
A
102
104
106
107
B
101
102
104
106
108
Результат
А
В
102
102
104
104
106
106
107
NULL
Рис. 3.5. R1 LEFT OUTER JOIN R2
Заметьте, что все значения из таблицы R1 включены в результат, так как она является левой таблицей. Значение 107, не
встречающееся в таблице R2, тоже включено в результат, однако
в строке вместе с ним нет значения из R2, оно пустое (NULL).
3.4.2.2. Правое внешнее соединение
В правом внешнем соединении результатом являются все
строки правой таблицы, вне зависимости от того, имеют ли
они подходящее соответствие в левой таблице. Другими словами, правое соединение работает так же, как и левое, с тем лишь
отличием, что возвращаются все строки из правой таблицы.
48
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
SELECT
FROM
A, B
R1 RIGHT OUTER JOIN R2 ON A=B;
В приведённом примере R1 по-прежнему является левой таблицей, а R2 – правой, поскольку именно в таком порядке они указаны по отношению к ключевым словам OUTER JOIN. Поэтому
результат соединения содержит все строки таблицы R2 и строки
таблицы R1, соответствующие условию, если такие имеются
(см. рис. 3.6.).
A
102
104
106
107
B
101
102
104
106
108
Результат
А
NULL
102
104
106
NULL
В
101
102
104
106
108
Рис. 3.6. R1 RIGHT OUTER JOIN R2
Правое внешнее соединение противоположно левому. На одинаковых таблицах – левой R1 и правой R2– результаты правого и
левого внешних объединений сильно различаются. На этот раз возвращаются все значения таблицы R2. Значения 101 и 108, не встречающиеся в R1, тоже включены в результат – в виде пустого значения в соответствующем поле. Опять-таки строка включается в
результат, несмотря на отсутствие значений в таблице R1.
3.4.2.3. Полное внешнее соединение
В полном внешнем соединении результатом являются
строки обеих таблицы, вне зависимости от того, имеют ли они
соответствия в другой таблице. Другими словами, оно работает
так же, как левое и правое, но на этот раз возвращаются значения
обеих таблиц. Рассмотрим пример:
SELECT
FROM
А, В
R1 FULL OUTER JOIN R2 ON А=В;
Опять же R1 – левая таблица, а R2 – правая, хотя на этот раз
это не так важно. Полное внешнее coединение возвратит строки
из всех таблиц, включая и те, что соответствуют условию, если
такие найдутся (см. рис. 3.7).
Полное внешнее соединение – это комбинация левого и правого соединения. К сожалению, по крайней мере в одной из сис49
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
тем баз данных они вызовут ошибку. В MySQL, которая не поддерживает FULL OUTER JOIN, вопреки стандарту SQL, результатом будет синтаксическая ошибка: SQL Error: You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near
'OUTER JOIN entries ON …'
A
102
104
106
107
Результат
А
В
NULL
101
102
102
104
104
106
106
107
NULL
NULL
108
B
101
102
104
106
108
Рис. 3.7. R1 FULL OUTER JOIN R2
Различие между внутренними и внешними соединениями.
Результаты внешнего соединения всегда включают в себя результаты соответствующего внутреннего соединения, а также
не соответствующие условию строки из левой, правой или обеих
таблиц – в зависимости от типа соединения.
Различие между левым и правым внешним соединением
лишь в том, что в первом возвращаются все строки левой таблицы с соответствиями из правой, а во втором наоборот – все строки правой таблицы с соответствиями из левой.
В то же время полное внешнее объединение всегда включает
результаты как левого, так и правого объединения.
3.4.2.4. Перекрёстное соединение
В перекрёстном соединении каждая строка из одной таблицы соединяется с каждой строкой из другой таблицы. Отличительной чертой перекрёстного соединения является отсутствие
условия ON, как вы можете заметить из следующего запроса:
SELECT A, B FROM
R1 CROSS JOIN R2
Как можно видеть, перекрестное соединение эквивалентно
декартову произведению. Перекрёстные соединения могут быть
полезны, но используются чрезвычайно редко. Они применяются
для генерации табличной структуры, содержащей все возможные
50
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
комбинации двух наборов значений (в нашем примере, значений
полей двух таблиц; это полезно при генерации тестовых данных
или поиске недостающих значений).
A
B
102
101
104
102
106
104
107
106
108
Результат
А
В
102
101
102
102
102
104
102
106
102
108
104
101
104
102
104
104
104
106
104
108
Продолжение
106
101
106
102
106
104
106
106
106
108
107
101
107
102
107
104
107
106
107
108
Рис. 3.8. R1 CROSS JOIN R2
3.4.3. Реальные примеры соединений
Внутреннее соединение.
Пример 21.
Получить информацию о стоимости и количестве каждого
продукта на складе
Результат
Продукт
Говядина
Судак
Масло
Майонез
Яйца
Сметана
Молоко
Творог
Морковь
Лук
Помидоры
Зелень
Рис
Мука
Яблоки
Сахар
Кофе
Количество
108
0
73
39
61
88
214
92
0
77
46
13
54
91
117
98
37
Цена
429,84
0,00
274,61
97,46
111,83
206,60
83,80
82,80
0,00
46,30
51,70
34,96
51,17
43,77
189,92
96,14
166,50
51
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
SELECT Продукты.Продукт, Наличие.Количество, Наличие.Цена
FROM Продукты JOIN Наличие
ON Продукты.ID_Продукта = Наличие.Продукт;
Таблица Продукты соединяется с таблицей Наличие с помощью ключевого слова JOIN (INNER по умолчанию). После ON задаётся условие соединения, которое указывает, каким образом
строки таблиц должны соответствовать друг другу, чтобы принять
участие в соединении.
Между таблицами Продукты и Наличие существует связь
«один к одному». Это означает, что для каждой строки из таблицы Продукты в таблице Наличие будет найдено не более одной
соответствующей строки (может не быть ни одной). В результат
запроса попадут только те строки из Таблицы продукты, для которых соответствие найдено (продукт есть на складе).
Пример 22.
Более сложный запрос: Рассчитать стоимость блюда.
SELECT b.Блюдо, SUM(s.Вес*n.Цена/1000)+b.Труд as Стоимость
FROM
(Блюда b JOIN Состав s ON b.ID_блюда =s.Блюдо )
JOIN Наличие n ON s.Продукт=n.Продукт
GROUP BY b.Блюдо, b.Труд;
Для того чтобы определить состав каждого блюда, таблица
Блюда соединяется с таблицей Состав. Между таблицами Блюда
и Состав существует связь «один ко многим», что означает, что в
состав каждого блюда может входить несколько продуктов. В результате внутреннего соединения получаем промежуточную таблицу. Для расчета стоимости полученную промежуточную таблицу соединяем с таблицей Наличие
для определения цены продукта. Результат
Блюдо
Стоимость
Группировка по полю Блюдо позво- Кофе черный
2,332
ляет каждое блюдо рассматривать Сметана
28,2671
как независимый набор строк, к ко- Компот
16,2558
12,0205
торому применяется формула под- Кофе на молоке
счета стоимости. Группировка по Молочный напи- 45,4615
ток
полю Труд позволяет учесть трудо- Творог
19,9821
затраты на изготовление одной Морковь с рисом 14,5298
порции блюда.
…
52
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Левое внешнее соединение.
Запрос с левым внешним соединением идентичен уже рассмотренному запросу с внутренним соединением (Пример 21), за
исключением того, что используются ключевые слова LEFT
OUTER JOIN.
SELECT p.Продукт, n.Количество, n.Цена
FROM Продукты p LEFT JOIN Наличие n ON p.ID_Продукта =
n.Продукт;
Единственное разли- Результат
Количество Цена
чие между левым внешним Продукт
108
429,84
объединением и внутрен- Говядина
Судак
0
0,00
ним в наличии лишней Масло
73
274,61
строки для продукта "мас- Майонез
39
97,46
61
111,83
ло растительное" в резуль- Яйца
88
206,60
тате. Этого продукта нет Сметана
Молоко
214
83,80
на складе (т. е. не выпол- Творог
92
82,80
няется условие внутренне- Морковь
0
0,00
77
46,30
го соединения). Строка Лук
46
51,70
включается потому, что Помидоры
13
34,96
использовано именно ле- Зелень
Рис
54
51,17
вое внешнее объединение, Мука
91
43,77
при котором все строки ле- Яблоки
117
189,92
98
96,14
вой таблицы Продукты Сахар
Кофе
37
166,50
должны попасть в резульМасло растительное NULL
NULL
тат.
Если мы хотим узнать, какие продукты отсутствуют на складе, необходимо дополнить запрос условием:
SELECT p.Продукт,
ISNULL(CAST(n.Количество as VARCHAR), ’НЕТ’) as на_складе
FROM Продукты p LEFT JOIN Наличие n ON p.ID_Продукта =
n.Продукт
WHERE n.Продукт IS NULL;
Результат
Продукт
на_складе
Масло растительное НЕТ
53
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Правое внешнее объединение.
Следующее правое внешнее соединение даёт в точности такие же результаты, как и левое:
SELECT p.Продукт,
ISNULL(CAST(n.Количество as VARCHAR),’НЕТ’) as на_складе
FROM Наличие n RIGHT JOIN Продукты p ON p.ID_Продукта =
n.Продукт
Результат
WHERE n.Продукт IS NULL
Продукт
на_складе
Масло растительное НЕТ
Но как такое может быть?
В запросе поменяны местами таблицы! В запросе с правым
соединением написано
FROM Наличие n RIGHT JOIN Продукты p
А в запросе с левым соединением было:
FROM Продукты p LEFT JOIN Наличие n
Из этого примера можно вынести важный урок: левое и правое внешние соединения абсолютно эквивалентны, смысл лишь в
том, какая из таблиц является внешней – все строки этой таблицы будут включены в результат. Из-за этого многие программисты избегают правых внешних объединений и преобразуют их в
левые, меняя таблицы местами; при этом таблица, из которой
должны быть взяты все строки, всегда является левой. Левые
внешние соединения для многих людей кажутся более понятными, чем правые.
Что если не менять таблицы местами в рассмотренном соединении? Предположим, запрос выглядит так:
SELECT p.Продукт, n.Количество, n.Стоимость
FROM Продукты p RIGHT JOIN Наличие n ON p.ID_Продукта =
n.Продукт
На этот раз, как и в исходном левом объединении, таблица
Продукты – левая, а таблица Наличие – правая.
Результаты запроса оказываются идентичными результатам
внутреннего соединения.
Как такое может быть? Нет ли ошибки? Нет, и причина лежит в содержимом таблиц. Вспомните: правое внешнее объединение возвращает все строки правой таблицы и соответствующие
им строки левой таблицы, если такие найдутся. Таблица Наличие – правая, и в данном случае каждая запись имеет свой
ID_Продукта в таблице Продукты (иначе бы при добавлении
54
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
строк в таблицу Наличие мы бы имели ошибку, связанную с
внешним ключом). Возвращаются все записи, и несоответствующих строк нет.
Результат
Продукт
Говядина
Судак
Масло
Майонез
Яйца
Сметана
Молоко
Творог
Морковь
Лук
Помидоры
Зелень
Рис
Мука
Яблоки
Сахар
Кофе
Масло растительное
Количество
108
0
73
39
61
88
214
92
0
77
46
13
54
91
117
98
37
NULL
Цена
429,84
0,00
274,61
97,46
111,83
206,60
83,80
82,80
0,00
46,30
51,70
34,96
51,17
43,77
189,92
96,14
166,50
NULL
Поэтому в порядке вещей, что правое внешнее соединение в
этом случае даст такие же результаты, как и внутреннее, ведь оно
следовало правилу: вернуть все строки внешней таблицы и соответствия, если таковые найдутся. В данном случае они не нашлись.
Результат
Блюдо
Бефстроганов
Кофе на молоке
Паштет из рыбы
Салат летний
Салат мясной
Судак по-польски
Суп молочный
Суп харчо
Суфле яблочное
Дата
2011-01-02
2011-01-02
2011-01-02
2011-01-02
2011-01-02
2011-01-02
2011-01-02
2011-01-02
2011-01-02
4
2
NULL
10
NULL
6
NULL
NULL
2
55
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Чтобы на самом деле увидеть правые внешние соединения в
действии, рассмотрим следующий запрос.
Пример 23.
SELECT b.Блюдо, m.Дата, SUM( z.Количество_порций)
FROM
( Заказы z RIGHT JOIN Меню m ON m.Блюдо = z. Блюдо
and m. Дата = z. Дата)
JOIN Блюда b ON m.Блюдо = b.ID_ Блюдо
WHERE m.Дата = '2011-01-02'
GROUP BY
b.Блюдо, m.Дата;
Данный запрос позволяет (благодаря правому внешнему соединению) вывести на экран все блюда из меню на 02.01.2011,
даже если их никто не заказывал.
Контрольные вопросы 1. Что является результатом выполнения оператора SELECT?
2. Перечислите основные предложения оператора SELECT, какие
из них являются обязательными.
3. Как устранить повторяющиеся строки в результате запроса?
4. Как NULL-значения влияют на результат вычисления?
5. Как выполнить сравнение с NULL-значением?
6. Назовите правила использования предикатов IN, LIKE,
BETWEEN?
7. Перечислите основные правила использования агрегатных
функций.
8. В чем отличие использования предложения WHERE от предложения HAVING?
9. Как выполнить запрос, если данные расположены в нескольких
таблицах?
10. В чем разница между внутренним и внешним соединениями?
56
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Глава 4. Подзапросы и производные таблицы С помощью SQL вы можете вкладывать запросы внутрь друг
друга. В этом случае говорят о запросе с подзапросом.
4.1. Производная таблица Синтаксис SQL позволяет в предложении FROM, кроме базовых таблиц, использовать любое выражение, генерирующее
табличную структуру. Рассмотрим пример.
Пример 24.
Выдать меню на 02-01-2011.
SELECT a.Блюдо, cast(a. Стоимость as numeric(6,2)) as [Цена за порцию]
FROM
(SELECT b.Блюдо, b.ID_блюда,
SUM(s.Вес*n.Цена/1000)+b.Труд as Стоимость
FROM (Блюда b JOIN Состав s ON b.ID_блюда = s.Блюдо )
JOIN Наличие n ON s.Продукт = n.Продукт
GROUP BY b.Блюдо, b.ID_блюда, b.Труд ) a
JOIN Меню m ON m.Блюдо = a.ID_блюда
WHERE Дата = '2011-01-02'
ORDER BY Вид;
Результат
Блюдо
Салат летний
Салат мясной
Паштет из рыбы
Суп харчо
Суп молочный
Бефстроганов
Судак по-польски
Суфле яблочное
Кофе на молоке
Цена за
порцию
25.00
38.63
12.04
48.50
35.97
55.15
13.37
40.93
12.02
Производная таблица – это весь запрос SELECT, помещённый в скобках (скобки являются частью синтаксиса и предназначены для выделения запроса). В примере производная таблица
имеет псевдоним а.
57
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Производная таблица – это общий случай подзапроса – запроса, который вложен в другой запрос или зависит от него.
Производные таблицы чрезвычайно полезны в SQL.
В запросе используется функция преобразования типа –
cast(a. Стоимость as numeric(6,2))
её синтаксис:
CAST ( выражение AS тип_данных [ (длина) ])
4.2. Вложенные подзапросы Вложенный подзапрос – это подзапрос, заключенный в круглые скобки и вложенный в WHERE (HAVING) фразу предложения SELECT или других предложений, использующих WHERE
фразу. Вложенный подзапрос создан для того, чтобы при отборе
строк таблицы, сформированной основным запросом, можно было использовать данные из других таблиц (например, при отборе
блюд для меню использовать данные о наличии продуктов в кладовой ресторана).
Существуют простые и соотнесенные вложенные подзапросы.
Они включаются в WHERE (HAVING) фразу с помощью
предикатов IN, EXISTS или одного из условий сравнения ( = | <> |
< | <= | > | >= ). Простые вложенные подзапросы обрабатываются
системой "снизу вверх". Первым обрабатывается вложенный подзапрос самого нижнего уровня. Множество значений, полученное
в результате его выполнения, используется при реализации подзапроса более высокого уровня и т. д.
Запросы с соотнесенными вложенными подзапросами обрабатываются системой в обратном порядке. Сначала выбирается
первая строка рабочей таблицы, сформированной основным запросом, и из нее выбираются значения тех столбцов, которые используются во вложенном подзапросе (вложенных подзапросах).
Если эти значения удовлетворяют условиям вложенного подзапроса, то выбранная строка включается в результат. Затем выбирается вторая строка и т. д., пока в результат не будут включены
все строки, удовлетворяющие вложенному подзапросу (последовательности вложенных подзапросов).
Следует отметить, что SQL обладает большой избыточностью в том смысле, что он часто предоставляет несколько раз58
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
личных способов формулировки одного и того же запроса. Поэтому во многих примерах мы будем использовать уже знакомые
формулировки запросов. Кроме того, разные формулировки одного и того же запроса требуют для своего выполнения различных ресурсов памяти и могут значительно отличаться по времени
реализации в разных СУБД.
4.2.1 Простые вложенные подзапросы
Обычно, внутренний запрос генерирует значение, которое
проверяется в предикате внешнего запроса, определяющего, верно оно или нет.
Пример 25.
Мы знаем название блюда: Сырники, но не знаем значение
ID и хотим извлечь все Продукты из таблицы Продукты, которые
участвуют в его приготовлении. Можно сделать это, выполнив
операцию соединения, но существует и иной способ:
SELECT *
FROM Соcтав
WHERE Блюдо = ( SELECT ID_блюда
FROM Блюда
WHERE Блюдо = 'Сырники');
Результат
Блюдо
18
18
18
18
18
Продукт
5
6
8
14
16
Вес
10
30
140
15
15
Чтобы оценить внешний (основной)
запрос, SQL сначала должен оценить
внутренний запрос (или подзапрос) внутри предложения
WHERE. Единственной найденной строкой естественно будет
ID_блюда = 18. Однако SQL не просто выдает это значение, а
помещает его в предикат основного запроса вместо самого подзапроса, так чтобы предикат принял вид
WHERE Блюдо = 18
Основной запрос затем выполняется как обычно. Конечно
же, подзапрос должен выбрать один и только один столбец, а тип
данных этого столбца должен совпадать с тем значением, с которым он будет сравниваться в предикате. При использовании подзапросов в предикатах, основанных на операторах сравнения ( =,
59
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
<>, < и т. д.) вы должны убедиться, что использовали подзапрос,
который будет выдавать одну и только одну строку вывода (в некоторых случаях можно использовать DISTINCT, чтобы вынудить подзапрос генерировать одиночное значение).
Если заменить условие WHERE Блюдо=’Сырники’ на условие WHERE Основа = 'Молоко’, мы получим в результате подзапроса несколько строк. Это сделает условие основного запроса
невозможным для оценки, и команда выдаст ошибку. Если вы
используете подзапрос, который не выводит никаких значений
вообще, команда будет выполнена; но основной запрос не выведет никаких значений. Подзапросы, которые не производят никакого вывода (или нулевой вывод), вынуждают рассматривать
предикат не как верный, не как неверный, а как неизвестный. Однако неизвестный предикат имеет тот же самый эффект, что и неверный: никакие строки не выбираются основным запросом.
Синтаксис использования простого подзапроса:
< столбец | выражение > < оператор > < подзапрос >
Варианты < подзапрос > < оператор > < столбец | выражение
> или, < подзапрос > < оператор > < подзапрос > недопустимы.
Если не удается построить подзапрос, возвращающий единственное значение необходимо использовать предикат IN.
Пример 26.
SELECT *
FROM Соcтав
WHERE Блюдо IN
( SELECT ID_блюда
FROM Блюда
WHERE Основа = 'Молоко');
При обработке полного запроса система выполняет прежде
всего вложенный подзапрос. Этот подзапрос выдает множество
номеров блюд, основой которых является молоко, а именно множество (7, 8, 12, 18, 22, 24, 28, 31). Поэтому первоначальный запрос эквивалентен такому простому запросу:
SELECT *
FROM Соcтав
WHERE Блюдо IN (7, 8, 12, 18, 22, 24, 28, 31);
Приведем пример запроса с несколькими уровнями вложенности.
60
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Пример 27.
SELECT
FROM
WHERE
p.*, Количество, Цена
Продукты p LEFT JOIN Наличие n
ON p. ID_Продукта = n.Продукт
p.ID_Продукта IN (SELECT Продукт
FROM Соcтав
WHERE Блюдо IN ( SELECT ID_блюда
FROM Блюда
WHERE Основа = ’Молоко’)
);
230 220 740
0,1
1
1530 550 710
4,4
1,9
950 850 320
1
1
1460 1210 1500 1,3
1
1120 1640 1410 2,7
4
540 240 260
0,4
16
1760 240 120
1,2
22
2480 160 260
0,3
3
30
20
10 NULL NULL
Цена
Кол-во
C
PP
B2
Na
90
7
28
47
13
773
732
113
998
Са
825
115
300
32
90
6
13
NULL
NULL
К
Углеводы
Белки
60
Масло
127
Яйца
Сметана 26
28
Молоко
167
Творог
70
Рис
106
Мука
4
Яблоки
NULL
Сахар
Жиры
3
5
6
7
8
13
14
15
16
Продукт
ID_Продукта
Результат
NULL 73 274,61
NULL 61 111,83
2
88 206,60
10
214 83,80
5
92 82,80
NULL 54 51,17
NULL 91 43,77
NULL 117 189,92
130
98 96,14
Этот запрос выдает информацию о продуктах, их цене и наличии на складе, необходимых для приготовления блюд на основе молока.
В любой ситуации, где вы можете использовать реляционный
оператор сравнения (=), вы можете использовать IN. В отличие от
реляционных операторов, IN не может заставить команду потерпеть неудачу, если больше чем одно значение выбрано подзапросом. Заметим, что использование IN соответствует операции пересечения реляционной алгебры, а NOT IN разности.
4.2.2. Использование одной и той же таблицы во внешнем
и вложенном подзапросе
Пример 28.
Выдать номера Блюд, в состав которых входят продукты, необходимые для приготовления сырников.
61
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
SELECT DISTINCT Блюдо
FROM Соcтав
WHERE Продукт IN ( SELECT Продукт
FROM Состав
WHERE Блюдо = 18);
Отметим, что ссылка на Состав во вложенном подзапросе означает не то же самое, что ссылка на Состав во внешнем запросе.
В действительности, два имени Состав обозначают различные
значения. Чтобы этот факт стал явным, полезно использовать
псевдонимы, например, С1 и С2:
SELECT DISTINCT Блюдо
FROM Соcтав С1
WHERE С1. Продукт IN ( SELECT Продукт
FROM Состав С2
WHERE С2.Блюдо = 18);
Здесь С1 и С2 – произвольные псевдонимы таблицы Состав, определяемые во фразе FROM и используемые как явные уточнители во фразах SELECT и WHERE. Напомним, что псевдонимы определены лишь в пределах одного запроса.
4.2.3. Использование агрегатных функций в подзапросах
Любой запрос, использующий одиночную функцию агрегата
без предложения GROUP BY, будет выбирать одиночное значение, а значит, может быть использован для сравнения в основном
предикате.
Пример 29.
Необходимо найти самые дешевые продукты:
SELECT p.Продукт, Цена
FROM Продукты p JOIN Наличие n ON
p.ID_продукта = n.Продукт
WHERE Цена = (SELECT MIN (Цена) FROM Наличие);
Результат
Продукт Цена
Зелень
34,96
Подзапрос ищет минимальную цену в таблице Наличие, а основной запрос выбирает продукты с найденной ценой.
Следует заметить, что если агрегатные функции используются с предложением GROUP BY, то подзапрос может вернуть многочисленные значения. Они, следовательно, не допустимы в под62
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
запросах такого характера. Даже если GROUP BY и HAVING используются таким способом, что только одна группа выводится с
помощью подзапроса, команда будет отклонена в принципе.
Если не удается построить подзапрос, возвращающий единственное значение, вы должны использовать предикаты IN, ANY
или ALL. Но следует заметить, что предикаты BETWEEN, LIKE
и IS NULL не могут использоваться с подзапросами.
Следует отметить: все подзапросы в качестве результата
должны возвращать одиночный столбец или одиночное выражение (может быть основано на нескольких столбцах). Это обязательное требование, поскольку результат запроса сравнивается
с одиночным значением. Исключением из этого правила является
ситуация, когда подзапросы используются с оператором EXISTS,
который мы будем рассматривать ниже.
4.2.4. Подзапросы в предложении HAVING
Подзапросы могут использоваться внутри предложения
HAVING.
Результат
Пример 30.
Блюдо
Цена
Найти самые дешевые блюда.
Кофе черный 2,33
SELECT b.Блюдо,
cast(SUM (s.Вес*n.Цена/1000)+b.Труд as numeric(6,2)) as Стоимость
FROM (Блюда b JOIN Состав s ON b.ID_блюда =s.Блюдо )
JOIN Наличие n ON s.Продукт = n.Продукт
GROUP BY b.Блюдо, b.Труд
HAVING (SUM (s.Вес*n.Цена/1000)+b.Труд) = (SELECT
MIN(стоимость)
FROM (SELECT b.Блюдо, SUM (s.Вес*n.Цена/1000)+b.Труд as Стоимость
FROM (Блюда b JOIN Состав s ON b.ID_блюда = s.Блюдо )
JOIN Наличие n ON s.Продукт = n.Продукт
GROUP BY b.Блюдо, b.Труд ) a
);
Этот запрос ищет для каждого блюда стоимость (псевдоним a),
затем из этих данных выбирает минимум (подается на вход HAVING). Этот минимум используется для отбора блюд для вывода
результата основного запроса.
63
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
4.3. Соотнесенные подзапросы Соотнесенным называется подзапрос, который ссылается на
данные внешнего запроса. Соотнесенный подзапрос – один из
большого количества тонких понятий в SQL из-за сложности в
его оценке. Если вы сумеете овладеть им, вы найдете, что он
очень мощный, потому что может выполнять сложные функции с
помощью очень лаконичных указаний. Соотнесенный подзапрос
выполняется неоднократно, по одному разу для каждой строки
таблицы основного запроса.
Пример 31.
Рассчитать стоимость блюд на основе молока.
SELECT b.Блюдо, ( SELECT SUM (s.Вес*n.Цена/1000)
FROM Состав s JOIN Наличие n ON s.Продукт = n.Продукт
WHERE s.Блюдо = b.ID_Блюда) + b.Труд as Стоимость
FROM Блюда b
WHERE Основа = 'Молоко';
Результат
Блюдо
Сметана
Творог
Суп молочный
Сырники
Вареники ленивые
Суфле из творога
Крем творожный
Молочный напиток
Стоимость
28,2671
19,9821
35,9746
25,0069
24,1267
36,7818
22,8368
45,4615
Такой подзапрос отличается от рассмотренного выше тем,
что вложенный подзапрос не может быть обработан прежде, чем
будет обрабатываться внешний подзапрос. Это связано с тем, что
вложенный подзапрос зависит от значения b.ID_Блюда, а оно изменяется по мере того, как система проверяет различные строки
таблицы Блюда. Следовательно, с концептуальной точки зрения
обработка осуществляется следующим образом:
1. Система проверяет первую строку таблицы Блюда. Предположим, что это строка удовлетворяет условию Основа =
'Молоко'.
64
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
2. Теперь система может передать b.ID_Блюда в подзапрос и
вычислить стоимость блюда.
3. Далее система будет повторять обработку такого рода для
следующего блюда и т. д. до тех пор, пока не будут рассмотрены
все строки таблицы Блюда.
Рассмотрим пример использования одной и той же таблицы
во внешнем подзапросе и соотнесенном вложенном подзапросе.
Пример 32.
Выдать номера всех продуктов, которые используются только в одном блюде.
SELECT С1.Продукт
FROM
Состав С1
WHERE С1.Продукт NOT IN
(SELECT С2.Продукт
FROM
Состав С2
WHERE С2.Блюдо <> С1.Блюдо);
Данный запрос возвращает пустую таблицу, так как нет таких
продуктов в БД.
Действие этого запроса можно пояснить следующим образом: "Поочередно для каждой строки таблицы Состав, скажем С1,
выделить значение номера продукта (С1.Продукт) и номер блюда
(С1.Блюдо), в котором этот продукт используется. А затем проверить, не встречается ли этот продукт в списке продуктов (С2),
необходимых для приготовления других блюд (<>С1.Блюдо). Если не встречается, то выводим его".
Отметим, что в этой формулировке должен быть использован
по крайней мере один псевдоним – либо С1, либо С2.
Данный запрос может быть реализован и по-другому.
SELECT Продукт
FROM
Состав
GROUP BY
Продукт
HAVING COUNT (DISTINCT Блюдо) = 1;
Для проверки правильности выводов данных запросов можно
выполнить команду:
SELECT Продукт , COUNT (DISTINCT Блюдо)
FROM
Состав
GROUP BY Продукт;
65
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
И получить результат:
Результат
Продукт
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
5
5
19
4
11
10
11
6
7
8
8
13
5
8
9
17
2
4.4. Использование оператора EXISTS Квантор EXISTS (существует) – понятие, заимствованное из
формальной логики. В языке SQL предикат с квантором существования представляется выражением
… EXISTS (SELECT * FROM ...).
Такое выражение считается истинным только тогда, когда результат вычисления "SELECT * FROM ..." является непустым
множеством (вернул хотя бы одну строку). Рассмотрим примеры.
Пример 33.
Выдать названия Блюд, представленных в Результат
Блюдо
меню на данное число.
SELECT
FROM
WHERE
Блюдо
Блюда
EXISTS
(SELECT * FROM Меню m
WHERE m.Блюдо = Блюда.ID_Блюда
and Дата = '2011-01-02');
66
Салат летний
Салат мясной
Паштет из рыбы
Суп харчо
Суп молочный
Бефстроганов
Судак по-польски
Суфле яблочное
Кофе на молоке
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Система последовательно выбирает строки таблицы Блюда,
выделяет из них значения столбцов Блюдо и ID_Блюда, а затем
проверяет, является ли истинным условие существования, т. е.
существует ли в таблице Меню(m) хотя бы одна строка со значением Дата = '2011-01-02' и значением m.Блюдо, равным значению Блюда.ID_Блюда. Если условие выполняется, то полученное
значение столбца Блюдо включается в результат. Соответственно,
запрос
SELECT
Блюдо
FROM
Блюда
WHERE NOT EXISTS (SELECT *
FROM Меню m
WHERE m.Блюдо = Блюда.ID_Блюда );
выдаст названия блюд, которые ни разу не включались в состав
меню. Данные запросы также являются соотнесенными.
Хотя этот рассмотренный пример только показывает иной
способ формулировки запроса для задачи, решаемой и другими
путями (с помощью оператора IN или соединения), EXISTS представляет собой одну из наиболее важных возможностей SQL.
Фактически любой запрос, который выражается через IN, может
быть альтернативным образом сформулирован также с помощью
EXISTS. Однако обратное высказывание несправедливо.
4.5. Использование операторов ANY и ALL Операторы ANY и ALL напоминают EXISTS, который воспринимает подзапросы как аргументы; однако они отличаются от
EXISTS тем, что используются совместно с реляционными операторами. В этом отношении они напоминают оператор IN, когда
тот используется с подзапросами; они берут все значения, выведенные подзапросом, и обрабатывают их как модуль. Однако, в
отличие от IN, они могут использоваться только с подзапросами.
Оператор ANY.
Оператор ANY означает, что предикат истинен, если хотя бы
для одного значения из подзапроса предикат сравнения истинен.
Пример 34.
Имеется иной способ нахождения названий Блюда представленных в меню на данное число.
67
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
SELECT Блюдо
FROM
Блюда
WHERE ID_Блюда = ANY (SELECT Блюдо
FROM Меню
WHERE Дата = '2011-01-02');
Оператор ANY берет все значения, выведенные подзапросом
(для этого случая – это все значения Блюд в таблице Меню на указанную дату), и оценивает их как верные, если любой (ANY) из них
равняется значению Блюда текущей строки внешнего запроса.
Следует отметить, что подзапрос должен выбирать значения
такого же типа, как и те, которые сравниваются в основном предикате. В этом его отличие от EXISTS, который просто определяет, производит ли подзапрос результаты или нет, и фактически не
использует эти результаты.
Если при работе оператора ANY используется равенство, то
этот запрос может быть всегда заменен запросом с IN
SELECT Блюдо
FROM
Блюда
WHERE ID_Блюда IN (SELECT Блюдо
FROM Меню
WHERE Дата = '2011-01-02');
или EXISTS.
SELECT Блюдо
FROM
Блюда b
WHERE EXISTS (SELECT Блюдо
FROM Меню m
WHERE Дата = '2011-01-02' and m.Блюдо = b. ID_Блюда);
Однако оператор ANY может использовать и другие реляционные операторы, кроме равенства, и таким образом делать сравнения, которые являются выше возможностей IN. Например, мы
сделали заказ, но не рассчитали с деньгами, и нам требуется замена некоторых блюд на более дешевые блюда.
Пример 35.
Список блюд с ценой, меньшей, чем любое блюдо из заказа
номер 2.
SELECT b.Блюдо,
cast(SUM (s.Вес * n.Цена/1000)+b.Труд as numeric(6,2)) as Стоимость
FROM
(Блюда b JOIN Состав s ON b.ID_Блюда = s.Блюдо)
JOIN Наличие n ON s.Продукт = n.Продукт
68
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
WHERE b.ID_Блюда NOT IN (select Блюдо from Заказы where ID_Заказ = 2)
GROUP BY b.Блюдо,b.ID_Блюда, b.Труд
HAVING (SUM (s.Вес * n.Цена/1000)+b.Труд) < ANY
(SELECT SUM (s.Вес*n.Цена/1000)+b.Труд as Стоимоть
FROM ((Блюда b JOIN Состав s ON b.ID_Блюда = s.Блюдо)
JOIN Наличие n ON s.Продукт = n.Продукт)
JOIN Заказы z ON s.Блюдо = z.Блюдо
WHERE ID_Заказ = 2
GROUP BY b.Блюдо, b.Труд);
Подзапрос определяет стоимость блюд заказа. Эти данные
используются для отбора в основном запросе.
Оператор ALL.
С помощью ALL предикат является верным, если каждое значение, выбранное подзапросом, удовлетворяет условию в предикате внешнего запроса. Если мы хотим найти самый дорогой (дешевый Цена <=) продукт, то можем это сделать следующим образом.
Пример 36.
SELECT
WHERE
Продукт FROM
Наличие
Цена >= ALL (SELECT DISTINCT Цена FROM Наличие);
Т. е. мы найдем те продукты, которые имеют цену выше или
равную всем остальным (максимальную). Как и в случае с ANY,
мы можем использовать EXISTS для производства альтернативной формулировки такого же запроса:
SELECT Продукт
FROM
Наличие n
WHERE NOT EXISTS (SELECT * FROM Наличие n1
WHERE n1.Цена > n.Цена);
ALL чаще используется с неравенствами, чем с равенствами,
так как значение может быть "равным для всех" результатом подзапроса, только если все результаты фактически идентичны. Рассмотрим следующий запрос:
Пример 37.
SELECT
FROM
WHERE
Продукт
Наличие
Цена = ALL (SELECT DISTINCT Цена FROM Наличие);
Эта команда допустима, но мы не получим никакого вывода.
Только в единственном случае вывод будет выдан этим запросом – если все цены на продукты окажутся одинаковыми.
В SQL выражение – < > ALL – в действительности соответствует "не равен любому" результату подзапроса. Другими сло69
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
вами, предикат верен, если данное значение не найдено среди результатов подзапроса.
Следует отметить, что вариант с EXISTS не абсолютно идентичен вариантам с ANY(ALL) из-за различия в том, как обрабатываются NULL-значения. Всякий раз, когда допустимый подзапрос
не в состоянии сделать вывод, ALL автоматически верен, а ANY
автоматически неправилен. Большинство пользователей, однако,
находят ANY и ALL более удобными в использовании, чем
EXISTS, который требует соотнесенных подзапросов. Кроме того,
в зависимости от реализации, ANY и ALL могут, по крайней мере
в теории, быть более эффективными, чем EXISTS. Подзапросы
ANY или ALL могут выполняться один раз и иметь вывод, используемый, чтобы определять предикат для каждой строки основного запроса. EXISTS, с другой стороны, берет соотнесенный
подзапрос, который требует, чтобы весь подзапрос повторно выполнялся для каждой строки основного запроса. Основная причина для формулировки EXISTS как альтернативы ANY и ALL в
том, что ANY и ALL могут быть несколько неоднозначны из-за
способа использования этого термина в английском языке.
Правильное понимание ANY и ALL
В SQL сказать, что значение больше (или меньше), чем любое (ANY) из набора значений, – то же самое, что сказать, что
оно больше (или меньше), чем любое одно отдельное из этих значений. И наоборот, сказать, что значение не равно всему (ALL)
набору значений, – то же, что сказать, что нет такого значения в
наборе, которому оно равно.
4.6. Объединение запросов UNION Операцию соединения можно представить как присоединение строки одной таблицы к строке другой таблицы – горизонтальную конкатенацию. Операция объединения же действует как
вертикальная конкатенация – второй набор результатов добавляется в конец первого набора.
Основные правила объединения результирующих наборов
двух запросов с помощью операции UNION:  количество и порядок столбцов должны быть одинаковыми во всех запросах;
 тип данных должен быть совместимым.
70
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Синтаксис:
<оператор_SELECT >
UNION [ ALL ]
< оператор_SELECT >
[ UNION [ ALL ] <оператор_SELECT > … ]
Где:
<оператор_SELECT > – это запрос, возвращающий данные
для объединения с данными из другого запроса. Определения
столбцов, которые являются частью операции UNION, могут не
совпадать, однако должны быть совместимыми посредством неявного преобразования.
UNION – указывает на то, что несколько результирующих
наборов следует объединить и возвратить в виде единого результирующего набора. Дубликаты строк удаляются.
UNION ALL – объединяет в результирующий набор все
строки. Это относится и к дублирующимся строкам.
Пример 38.
Например, выдать названия продуктов, в которых нет жиров,
Результат
либо входящих в состав блюда с кодом БЛ = 1:
SELECT
FROM
WHERE
UNION
ID Продукта
10
11
12
15
16
Результат
ID Продукта
4
11
12
15
Продукт
Продукты
Жиры IS NULL
SELECT Продукт
FROM
Состав
WHERE Блюдо = 1;
Результат объединения
Результат
ID Продукта
4
10
11
12
15
16
Из этого простого примера видно, что избыточные дубликаты всегда исключаются из результата UNION. Поэтому хотя в
рассматриваемом примере Помидоры (11), Зелень (12) и Яблоки
71
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
(15) выбираются обеими частями из двух составляющих предложения SELECT, в окончательном результате они появляются
только один раз.
Предложением с UNION можно объединить любое число запросов. Так, к предыдущему запросу можно добавить (перед точкой с запятой) конструкцию
UNION
SELECT
FROM
WHERE
Продукт
Продукты
Ca < 250;
позволяющую добавить к списку продуктов Масло, Рис, Мука и
Кофе. Однако тот же результат можно получить простым изменением фразы WHERE первой части исходного запроса
WHERE Жиры = 0 OR Ca < 250;
4.6.1. UNION и устранение дубликатов
UNION будет автоматически исключать дубликаты строк из
вывода. Это нечто несвойственное для SQL, так как одиночные
запросы обычно содержат DISTINCT, чтобы устранять дубликаты. Например, запрос
Пример 39.
SELECT Блюдо, Количество_порций
FROM Заказы
WHERE Дата = '2011-01-02'
UNION
SELECT Блюдо, Количество_порций
FROM Заказы
WHERE Дата = '2011-01-03';
может иметь повторные комбинации значений (Блюдо, Количество_порций), так как некоторые блюда в разные дни могли заказываться в одинаковом количестве. Однако если мы используем UNION в построении этого запроса, повторные комбинации
будут устранены, что в дальнейшем, при вычислении выручки,
приведет к ошибке. Для решения проблемы устранения повторяющихся строк необходимо использовать UNION ALL.
SELECT Блюдо, Количество_порций
FROM
Заказы
WHERE Дата = '2011-01-02'
UNION ALL
72
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
SELECT Блюдо, Количество_порций
FROM
Заказы
WHERE Дата = '2011-01-03';
4.6.2. Использование строк и выражений с UNION
Иногда можно вставлять константы и выражения в предложении SELECT, используемые с UNION. Константы и выражения,
которые будут использоваться, должны быть совместимы по типу.
Эта свойство полезно, например, чтобы устанавливать комментарии, указывающие на то, какой запрос вывел данную строку.
Пример 40.
Необходимо сделать отчет о том, какие блюда наиболее и
наименее популярны у клиентов (из тех блюд, которые заказывались). Мы можем объединить два запроса, вставив туда текст,
чтобы различать вывод для каждого из них.
SELECT 'Наиболее популярное блюдо' , b.Блюдо,
SUM(z.Количество_порций)
FROM
Блюда b JOIN Заказы z ON b.ID_ Блюдо = z. Блюдо
GROUP BY b. Блюдо
HAVING SUM(z.Количество_порций) = (SELECT MAX (кол)
FROM (SELECT SUM(Количество_порций) as кол
FROM Заказы
GROUP BY Блюдо) m)
UNION ALL
SELECT 'Наименее популярное блюдо', b.Блюдо,
SUM(z.Количество_порций)
FROM Блюда b JOIN Заказы z ON b.ID_ Блюдо = z. Блюдо
GROUP BY b. Блюдо
HAVING SUM(z.Количество_порций) = (SELECT MIN (кол)
FROM ( SELECT SUM(Количество_порций) as кол
FROM Заказы
GROUP BY Блюдо)
m);
Результат
Блюдо
Наиболее популярное блюдо Салат летний
10
Наименее популярное блюдо Драчена
1
Наименее популярное блюдо Рулет с яблоками 1
4.6.3. Использование UNION с ORDER BY
При построении запроса с UNION часто необходимо, чтобы
данные выводились в каком-то особом порядке. Для этого необ73
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
ходимо использовать ORDER BY (один раз и в конце последнего
предложения SELECT), чтобы упорядочить вывод из объединения, точно так же, как это делается в индивидуальных запросах.
Давайте пересмотрим наш последний пример, чтобы упорядочить
строки по названиям блюд.
Пример 41.
SELECT 'Наиболее популярное блюдо' , b.Блюдо,
SUM(z.Количество_порций)
FROM Блюда b JOIN Заказы z ON b.ID_ Блюдо = z. Блюдо
GROUP BY b. Блюдо
HAVING SUM(z.Количество_порций) = (SELECT MAX (кол)
FROM (SELECT SUM(Количество_порций) as кол
FROM Заказы
GROUP BY Блюдо) m)
UNION ALL
SELECT 'Наименее популярное блюдо', b.Блюдо,
SUM(z.Количество_порций)
FROM Блюда b JOIN Заказы z ON b.ID_ Блюдо = z. Блюдо
GROUP BY b. Блюдо
HAVING SUM(z.Количество_порций) = (SELECT MIN (кол)
FROM ( SELECT SUM(Количество_порций) as кол
FROM Заказы
GROUP BY Блюдо)
m)
ORDER BY b.Блюдо;
4.6.4. Реализация внешнего полного соединения
через запросы с UNION
Если ваша система баз данных не поддерживает синтаксис
FULL OUTER JOIN, те же результаты можно получить с помощью более сложного запроса, используя UNION.
Схема такого запроса имеет вид:
SELECT <поля для вывода>
FROM
таблица_А LEFT OUTER JOIN таблица_В
ON <условие
связи>
UNION
SELECT <поля для вывода>
FROM таблица_А RIGHT OUTER JOIN таблица_В ON <условие связи>;
Как вы можете видеть, левое и правое внешние соединения
просто объединяются друг с другом с помощью оператора UNION.
74
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Следует отметить, что предложение GROUP BY с оператором GROUPING SETS может дать такой же результирующий набор, как и несколько простых предложений GROUP BY, объединенных с помощью UNION ALL.
Синтаксис:
GROUP BY GROUPING SETS (<набор группирования> [, ... n ])
Следующие инструкции эквивалентны.
SELECT Блюдо, ID_Заказ, SUM(Количество_порций)
FROM
Заказы
GROUP BY GROUPING SETS (Блюдо,ID_Заказ);
SELECT
Блюдо, NULL as ID_Заказ, SUM(Количество_порций)
FROM
Заказы
GROUP BY Блюдо
UNION ALL
SELECT NULL as Блюдо, ID_Заказ, SUM(Количество_порций)
FROM
Заказы
GROUP BY ID_Заказ;
И дают одинаковый результат:
Результат
Блюдо
1
2
4
14
15
16
25
27
33
NULL
NULL
NULL
NULL
NULL
ID_Заказа
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
1
2
3
4
5
10
3
3
4
8
1
1
2
2
20
4
6
1
3
Контрольные вопросы 1. Перечислите основные правила использования подзапросов.
2. В каких ситуациях можно использовать предикаты EXSITS,
ANY, ALL?
3. Как с помощью подзапроса реализовать внешнее соединение?
4. В чем разница между UNION и UNION ALL?
75
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Глава 5. Функции ранжирования Очень часто возникает вопрос: "Как получить последнюю
добавленную в таблицу строку?". Ответом на вопрос будет "никак", если в таблице не предусмотрен столбец, содержащий дату
вставки строки, или не используется последовательная нумерация строк, реализуемая во многих СУБД с помощью столбца с
автоинрементируемым значением. Тогда можно выбрать строку с
максимальным значением даты или счетчика.
Реляционная модель исходит из того факта, что строки в таблице не имеют порядка, являющегося прямым следствием теоретико-множественного подхода. Вопрос о последней строке имеет
смысл только в аспекте выдачи результата выполнения запроса,
при этом предполагается некоторая сортировка, задающаяся с
помощью предложения ORDER BY в операторе SELECT. Если
никакая сортировка не задана, то полагаться на то, что порядок
вывода строк, полученных при выполнении запроса сегодня, останется таким же и завтра, нельзя, т. к. этот порядок зависит от
плана, который выбирает оптимизатор запросов для их выполнения. А план может меняться, и зависит это от многих причин, которые мы здесь опустим.
Теоретически каждая строка запроса обрабатывается независимо от других строк. Однако на практике часто требуется при
обработке строки соотносить ее с предыдущими или последующими строками (например, для получения нарастающих итогов),
выделять группы строк, обрабатываемые независимо от других и
т. д. В ответ на потребности практики в ряде СУБД в языке SQL
появились соответствующие конструкции, в частности, функции
ранжирования и оконные (аналитические) функции, которые деюре были зафиксированы в стандарте SQL:2003. В SQL Server
ранжирующие функции появились в версии 2005.
5.1. Функция ROW_NUMBER Функция ROW_NUMBER, нумерует строки, возвращаемые
запросом. С ее помощью можно выполнить более сложное упорядочивание строк в отчете, чем то, которое дает предложение
ORDER BY в рамках Стандарта SQL-92.
76
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Используя функцию ROW_NUMBER можно:
задать нумерацию, которая будет отличаться от порядка сортировки строк результирующего набора;
создать "несквозную" нумерацию, т. е. выделить группы из
общего множества строк и пронумеровать их отдельно для каждой группы;
использовать одновременно несколько способов нумерации,
поскольку фактически нумерация не зависит от сортировки строк
запроса.
Покажем возможности функции ROW_NUMBER на простых
примерах.
Пример 42.
Пронумеровать все блюда из таблицы Блюда в алфавитном
порядке. Выполнить сортировку по {Основа, Блюдо}.
SELECT row_number() over(ORDER BY Блюдо) as Номер, Блюдо, Основа
FROM Блюда
WHERE Вид < 2
ORDER BY Основа, Блюдо;
Предложение OVER, с которым используется функция
ROW_NUMBER, задает порядок нумерации строк. При этом используется дополнительное предложение ORDER BY, которое не
имеет отношения к порядку вывода строк запроса. Если вы посмотрите на результат, то заметите, что порядок строк в результирующем наборе и порядок нумерации не совпадают:
Результат
Номер
7
8
1
5
3
4
2
6
Блюдо
Сметана
Творог
Мясо с гарниром
Салат мясной
Салат витаминный
Салат летний
Паштет из рыбы
Салат рыбный
Основа
Молоко
Молоко
Мясо
Мясо
Овощи
Овощи
Рыба
Рыба
А если требуется пронумеровать блюда для каждой основы
отдельно? Для этого нам потребуется еще одна конструкция в
предложении OVER – PARTITION BY.
77
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Конструкция PARTITION BY задает группы строк, для которых выполняется независимая нумерация. Группа определяется
равенством значений в списке столбцов, перечисленных в этой
конструкции, у строк, составляющих группу.
Пример 43.
Пронумеровать блюда в рамках каждой основы отдельно.
SELECT row_number() over(partition BY Основа ORDER BY Блюдо) as
Номер, Блюдо, Основа
FROM Блюда
WHERE Вид < 2
ORDER BY Основа, Блюдо;
PARTITION BY Основа означает, что блюда с одной основой
образуют группу, для которой и выполняется независимая нумерация. В результате получим:
Результат
Номер
1
2
1
2
1
2
1
2
Блюдо
Сметана
Творог
Мясо с гарниром
Салат мясной
Салат витаминный
Салат летний
Паштет из рыбы
Салат рыбный
Основа
Молоко
Молоко
Мясо
Мясо
Овощи
Овощи
Рыба
Рыба
Отсутствие конструкции PARTITION BY, как это было в
первом примере, означает, что все строки результирующего набора образуют одну единственную группу.
5.2. Функции RANK() и DENSE_RANK() Эти функции, как и функция ROW_NUMBER(), тоже нумеруют строки, но делают это несколько отличным способом. Это
отличие проявляется в том, что строки, которые имеют одинаковые значения в столбцах, по которым выполняется упорядочивание, получают одинаковые номера (ранги).
78
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Пример 44.
SELECT rank() over(ORDER BY
Основа ) as Номер, Блюдо,
Основа
FROM Блюда
WHERE Вид < 2
ORDER BY Основа, Блюдо;
SELECT dense_rank()
over(ORDER BY Основа ) as
Номер, Блюдо, Основа
FROM Блюда
WHERE Вид< 2
ORDER BY Основа, Блюдо;
Результат
Номер
1
1
3
3
5
5
7
7
Блюдо
Сметана
Творог
Мясо с гарниром
Салат мясной
Салат витаминный
Салат летний
Паштет из рыбы
Салат рыбный
Основа
Молоко
Молоко
Мясо
Мясо
Овощи
Овощи
Рыба
Рыба
Результат
Номер
1
1
2
2
3
3
4
4
Блюдо
Сметана
Творог
Мясо с гарниром
Салат мясной
Салат витаминный
Салат летний
Паштет из рыбы
Салат рыбный
Основа
Молоко
Молоко
Мясо
Мясо
Овощи
Овощи
Рыба
Рыба
В первом случае мы получаем неплотную нумерацию (номер
группы – порядковый номер строки, с которого начинается группа), во втором – плотную.
Как и для функции ROW_NUMBER, в предложении OVER
может использоваться конструкция PARTITION BY, разбивающая весь набор строк, возвращаемых запросом, на группы, к которым затем применяется соответствующая функция.
Запрос
SELECT rank() over(partition
BY Основа ORDER BY Вес)
as Номер, Блюдо, Основа
FROM Блюда
WHERE Вид < 2
ORDER BY Основа, Блюдо;
Результат
Номер
1
1
2
1
1
1
1
2
позволяет в каждой группе, определяемой основой, ранжировать блюда
по весу в порядке его возрастания.
79
Блюдо
Сметана
Творог
Мясо с гарниром
Салат мясной
Салат витаминный
Салат летний
Паштет из рыбы
Салат рыбный
Основа
Молоко
Молоко
Мясо
Мясо
Овощи
Овощи
Рыба
Рыба
Вес
140
140
250
200
200
200
120
200
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Творог и сметана имеют одинаковый номер в группе, так как
их вес совпадает. А вот как можно выбрать самые легкие блюда в
каждой категории:
SELECT *
FROM
(SELECT
rank()
over(partition BY Основа
ORDER BY Вес) as Номер,
Блюдо, Основа
FROM Блюда
WHERE Вид< 2) a
WHERE Номер = 1;
Результат
Номер
1
1
1
1
1
1
Блюдо
Сметана
Творог
Салат мясной
Салат витаминный
Салат летний
Паштет из рыбы
Основа
Молоко
Молоко
Мясо
Овощи
Овощи
Рыба
Вес
140
140
200
200
200
120
Запрос можно было бы написать короче, если бы функцию
RANK можно было бы применять в предложении WHERE, т. к.
само значение ранга нам не требуется. Однако это запрещено (как
и для других ранжирующих функций), по крайней мере в SQL
Server.
Наконец, рассмотрим еще один пример.
Пример 45.
Найти основы блюд, которые в таблице Блюда представлены
более чем тремя наименованиями.
Эта задача имеет традиционное решение через агрегатные
функции:
SELECT Основа
FROM
Блюда
GROUP BY Основа
HAVING count(*)>3;
Однако эту задачу можно решить и с помощью функции
RANK. Идея состоит в следующем: ранжировать блюда каждой
основу по уникальному ключу и выбрать только те основы, блюда которых достигают ранга 4 и более:
SELECT DISTINCT Основа
FROM
(SELECT rank() over(partition BY Основа ORDER BY
ID_Блюда) as
Номер, Блюдо, Основа FROM Блюда ) a
WHERE Номер > 3;
80
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
И в одном и в другом случае, естественно, мы получим один
и тот же результат:
Результат
основа
Молоко
Мясо
Овощи
Рыба
Фрукты
Еще раз заметим, что упорядочивание в последнем случае
должно быть выполнено по уникальной комбинации столбцов,
т. к. в противном случае блюд может быть больше трех, а ранг
меньше (например, 1, 2, 2,...). В нашем случае данное условие выполнено, т. к. упорядочивание выполняется по столбцу ID_блюда,
который является первичным ключом в таблице Блюда.
Контрольные вопросы 1. Как пронумеровать строки запроса?
2. Как пронумеровать строки в каждой отдельной группе запроса?
81
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Глава 6. Использование PIVOT и UNPIVOT Рассмотрим такой запрос
SELECT
FROM
s.ID_Вид;
s.Вид, Основа
Блюда b JOIN Справочник_вид_блюда s ON b.Вид =
результатом которого является
Результат
Вид
Закуска
Закуска
Закуска
Закуска
основа
Овощи
Мясо
Овощи
Рыба
продолжение
Закуска
Молоко
Закуска
Молоко
Суп
Мясо
Суп
Рыба
Суп
Рыба
продолжение
Горячее
Мясо
Горячее
Рыба
Горячее
Яйца
Горячее
Овощи
Горячее
Молоко
продолжение
Горячее Крупа
Горячее Молоко
Горячее Овощи
Горячее Молоко
Десерт Фрукты
…
Пусть теперь нам требуется получить таблицу со следующими заголовками:
Вид
Овощи Мясо Рыба Молоко Яйца Крупа Фрукты Кофе
Горячее
Десерт
Закуска
Напиток
Суп
Заголовками строк здесь являются уникальные названия видов блюд, которые берутся из столбца Вид вышеприведенного
запроса, а заголовками столбцов – уникальные основы блюд (соответственно из столбца Основа). А что должно быть в середине?
Ответ очевиден – некоторый агрегат, например, функция count
(Основа), которая подсчитает для каждого вида отдельно число
блюд с основой овощи, мясо и т. д., которые и заполнят соответствующие ячейки этой таблицы.
Это простейший вариант сводной таблицы, который имеет
всего два уровня иерархии по столбцам и строкам.
82
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Сводные таблицы появились в версии MS Excel 5.0 и представляют собой двумерную визуализацию многомерных структур
данных, применяемых в технологии OLAP для построения хранилищ данных. Следует сказать, что сводная таблица не является
реляционной, поскольку имеет не только заголовки столбцов, но
и заголовки строк, при этом и те и другие формируются из данных, находящихся в столбцах обычных реляционных таблиц. Последнее, кстати, означает, что число строк и столбцов заранее неизвестно, т. к. они формируются динамически при выполнении
запроса к реляционным данным. Кроме того, заголовки могут
иметь многоуровневые подзаголовки, что и позволяет выполнять
операции свертки (переход на более высокий уровень иерархии)
и детализации (переход на более низкий уровень иерархии).
Можно сказать, что pivot-таблица в SQL – это одноуровневая сводная таблица.
Оператор PIVOT не является стандартным, поэтому мы будем использовать в примерах его реализацию в языке TransactSQL (SQL Server 2005/2008).
SELECT Вид,
--столбец (столбцы), значения из которого формируют заголовки строк
[Овощи], [Мясо], [Рыба], [Молоко], [Яйца], [Крупа], [Фрукты], [Кофе]
--значения, формирующие заголовки столбцов
FROM
(SELECT s.Вид, Основа
FROM
Блюда b JOIN Справочник_вид_блюда s
ON b.Вид = s.ID_Вид) a
--здесь может быть таблица или подзапрос
PIVOT
--формирование pivot-таблицы
(COUNT(Основа)
-- агрегатная функция, формирующая содержимое сводной таблицы
FOR Основа
-- указывается столбец, уникальные значения в котором будут являться -заголовками столбцов
IN ([Овощи], [Мясо], [Рыба], [Молоко], [Яйца], [Крупа], [Фрукты], [Кофе])
--указываются конкретные значения в столбце Основа,
-- которые следует использовать в качестве заголовков
) pvt ;
-- алиас для сводной таблицы
83
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Результат
Вид
Горячее
Десерт
Закуска
Напиток
Суп
Овощи
2
0
2
0
0
Мясо
2
0
2
0
1
Рыба
1
0
2
0
2
Молоко
3
1
2
1
1
Яйца
2
0
0
0
0
Крупа
2
0
0
0
0
Фрукты
0
3
0
2
0
Кофе
0
0
0
2
0
В отличие от сводных таблиц, в операторе PIVOT требуется
явно перечислить столбцы для вывода. Это серьезное ограничение, т. к. для этого нужно знать характер данных, а значит, и
применять в приложениях этот оператор мы сможем, как правило, только к справочникам (вернее, к данным, которые берутся из
справочников).
Синтаксис:
SELECT <несведенный столбец>,
[первый сведенный столбец] AS <имя столбца>,
[второй сведенный столбец] AS <имя столбца>,
...
[последний сведенный столбец] AS <имя столбца>,
FROM
(< запрос SELECT, извлекающий эти данные>)
AS <псевдоним исходного запроса>
PIVOT
(
<статистическая функция>(<статистически обработанный столбец>)
FOR
[<столбец, содержащий значения, которые станут именами столбцов>]
IN ( [первый сведенный столбец], [второй сведенный столбец],
... [последний сведенный столбец])
)AS <псевдоним сведенной таблицы>
<необязательное предложение ORDER BY>;
Приведенную таблицу можно заполнить и стандартным способом с использованием оператора CASE:
SELECT s.Вид,
SUM(CASE Основа WHEN 'Овощи' THEN 1 ELSE 0 END) Овощи
, SUM(CASE Основа WHEN 'Мясо' THEN 1 ELSE 0 END) Мясо
, SUM(CASE Основа WHEN 'Рыба' THEN 1 ELSE 0 END) Рыба
,SUM(CASE Основа WHEN 'Молоко' THEN 1 ELSE 0 END) Молоко
,SUM(CASE Основа WHEN 'Яйца' THEN 1 ELSE 0 END) Яйца
,SUM(CASE Основа WHEN 'Крупа' THEN 1 ELSE 0 END) Крупа
84
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
,SUM(CASE Основа WHEN 'Фрукты' THEN 1 ELSE 0 END) Фрукты
,SUM(CASE Основа WHEN 'Кофе' THEN 1 ELSE 0 END) Кофе
FROM Блюда b JOIN Справочник_вид_блюда s ON b. Вид = s.ID_ Вид
GROUP BY s. Вид;
Где опреатор Case имеет следующий синтаксис:
CASE
input_expression
WHEN when_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END
input_expression –представляет собой любое допустимое выражение.
WHEN when_expression – простое выражение, с которым сравнивается аргумент input_expression. Типы данных аргумента
input_expression и каждого из выражений when_expression должны быть одинаковыми или неявно приводимыми друг к другу.
THEN result_expression – выражение, возвращаемое, если сравнение выражений input_expression и when_expression дает в результате TRUE или выражение Аргумент result expression представляет собой любое допустимое выражение.
ELSE else_result_expression –выражение, возвращаемое, если ни
одна из операций сравнения не дает в результате TRUE. Если
этот аргумент опущен и ни одна из операций сравнения не дает в
результате TRUE, функция CASE возвращает NULL. Аргумент
else_result_expression представляет собой любое допустимое выражение. Типы данных аргумента else_result_expression и любого
из аргументов result_expression должны быть одинаковыми или
неявно приводимыми друг к другу.
Как следует из названия оператора, UNPIVOT выполняет обратную по отношению к PIVOT операцию, т. е. представляет
данные, записанные в строке таблицы, в одном столбце.
Пусть мы получили следующий результат, ограничив вывод
Вид = ‘Горячее’
Вид
Овощи Мясо Рыба Молоко Яйца Крупа Фрукты Кофе
Горячее 2
2
1
3
2
2
0
0
Развернуть эту таблицу в столбец мы можем следующим
запросом
SELECT Основа,
-- заголовок столбца, который будет содержать заголовки строк
-- исходной таблицы
85
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Горячее
-- заголовок столбца, который будет содержать значения из строки
-- исходной таблицы
From ( SELECT Вид, [Овощи], [Мясо], [Рыба]
, [Молоко], [Яйца], [Крупа], [Фрукты], [Кофе]
FROM
(
-- pivot-запрос из предыдущего примера
SELECT
s.Вид, Основа
FROM
Блюда b JOIN Справочник_вид_блюда s
ON b.Вид = s.ID_Вид) a
PIVOT
(COUNT(Основа)
FOR Основа
IN ([Овощи], [Мясо], [Рыба], [Молоко], [Яйца], [Крупа], [Фрукты], [Кофе])
) pvt -- конец pivot-запроса
Результат
) pvt1
Основа
Горячее
UNPIVOT
Овощи
2
(Горячее
Мясо
2
-- заголовок столбца, который будет содержать
Рыба
1
-- значения из столбцов исходной таблицы,
Молоко
3
-- перечисленных ниже
Яйца
2
FOR Основа
2
IN ([Овощи], [Мясо], [Рыба], [Молоко], [Яйца], Крупа
Фрукты
0
[Крупа], [Фрукты], [Кофе])
Кофе
0
) unpvt;
Контрольные вопросы 1. Почему операторы PIVOT и UNPIVOT не являются реляционными?
2. Приведите пример задачи, в которой эти операторы помогут
сделать выборку данных.
86
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Глава 7. Операторы манипулирования данными Операторы манипулирования данными (DML – Data Manipulation Language) предназначены для изменения данных в существующих таблицах БД. В этот раздел языка SQL входят три оператора:
INSERT – добавление новых данных,
DELETE – удаление данных и
UPDATE – изменение существующих данных.
Все операторы манипулирования данными позволяют изменить данные только в одной таблице.
7.1. Оператор INSERT для добавления одной записи Синтаксис:
INSERT INTO <имя_таблицы> [(<список столбцов>) ]
VALUES (<список значений>)
Задание списка столбцов необязательно тогда, когда мы вводим строку с указанием значений всех столбцов.
Пример 46.
Добавим новое блюдо в таблицу Блюда.
INSERT
VALUES
INTO
Блюда (ID_Блюда, Блюдо, Вид, Основа, Вес, Труд)
(34, 'Шашлык', 3, 'Мясо', 150 , 4);
Так как мы заполняем все поля и данные передаем в соответствии со списком полей таблицы, то мы можем не задавать список
столбцов, ограничиться только заданием перечня значений, в этом
случае оператор ввода будет выглядеть следующим образом:
INSERT
VALUES
INTO
Блюда
(34, 'Шашлык', 3, 'Мясо', 150, 4);
Результаты работы обоих операторов одинаковые.
Какие столбцы должны быть заданы при вводе данных? Это
определяется тем, как описаны эти столбцы при создании соответствующей таблицы. Если столбец имеет признак обязательный (NOT NULL) при создании таблицы, то оператор INSERT
должен обязательно содержать данные для ввода в каждую строку данного столбца. Поэтому если в таблице все столбцы обязательные, то каждая вводимая строка должна содержать полный
перечень вводимых значений, а указание имен столбцов в этом
87
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
случае необязательно. Если имеется хотя бы один необязательный столбец и вы не вводите в него значение, задание списка
имен столбцов обязательно.
INSERT INTO Продукты (ID_Продукта, Продукт, Белки)
VALUES (18, 'Горох', 180 );
Все остальные столбцы таблицы Продукты получат значение
NULL или значение по умолчанию, которое было задано при создании таблицы.
Многие коммерческие продукты допускают использование автоинкрементируемых столбцов в таблицах, то есть столбцов, значение которых формируется автоматически при добавлении новых
записей. Такие столбцы широко используются в качестве первичных ключей таблицы, так как они автоматически обеспечивают
уникальность за счет того, что генерируемые значения не повторяются. Типичным примером столбца такого типа является последовательный счетчик, который при вставке строки генерирует значение на единицу большее предыдущего значения (значения, полученного при вставке предыдущей строки). При вставке значения в
поле такого типа произойдет ошибка. Поэтому в этом случае поступают аналогично значениям по умолчанию – формируют список
полей для ввода, в котором автоинкрементное поле не указывается.
Вставка NULL-значений может выполнятся командой:
INSERT INTO Продукты (Продукт, Белки, Жиры)
VALUES ( 'Горох', 180, NULL);
Так как значение NULL – это специальный маркер, а не просто символьное значение, он не включается в одиночные кавычки. Поле ID_Продукта в данном случае не указывается, так как
оно автоинкрементное.
7.2. Оператор INSERT для добавления набора записей Синтаксис:
INSERT INTO <имя_таблицы> [(<список столбцов>)]
SELECT …
Оператор ввода данных позволяет ввести сразу множество
строк, если их можно выбрать из некоторой другой таблицы.
Допустим, мы хотим в меню на ‘03-01-2011’ добавить все горячие блюда из ассортимента ресторана.
88
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Пример 47.
INSERT INTO Меню
SELECT b. ID_Блюда, b.Вид, '2011-01-03'
FROM
Блюда b JOIN Справочник_вид_блюда c ON b.Вид=c.ID_Вид
WHERE c.Вид = 'Горячее';
Следует отметить, что количество полей и их тип в результате запроса должны полностью совпадать с описанием таблицы.
Запросы при добавлении могут быть любой сложности.
Пример 48.
INSERT INTO Продукты (Продукт, Белки, Жиры)
SELECT 'Горох', 180, NULL
UNION
SELECT 'Фасоль', 185, NULL;
Следует отметить, что в SQL Server 2008 реализована вставка
нескольких строк с помощью VALUES.
С учетом этой возможности последний запрос можно переписать в виде:
Пример 49.
INSERT INTO Продукты ( Продукт, Белки, Жиры)
VALUES ( 'Горох', 180, NULL), ( 'Фасоль', 185, NULL);
7.3. Оператор DELETE Оператор удаления данных позволяет удалить одну или несколько строк из таблицы в соответствии с условиями, которые
задаются для удаляемых строк, при этом удаляется вся строка
(строки), а не индивидуальные значения полей.
Синтаксис:
DELETE FROM
<имя_таблицы>
[WHERE <условия_отбора>]
Если условия отбора не задаются, то из таблицы удаляются все
строки, однако это не означает, что удаляется вся таблица. Исходная таблица остается, но она остается пустой, незаполненной.
Например, если нам надо очистить таблицу Меню, то мы можем удалить все строки командой
DELETE FROM Меню;
Условия отбора в части WHERE имеют тот же вид, что и условия фильтрации в операторе SELECT. Эти условия определяют, ка89
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
кие строки из исходного отношения будут удалены. Например,
удалить из таблицы наличия все закончившиеся продукты
DELETE
WHERE
FROM
Наличие
Количество = 0;
В предложении WHERE может находиться встроенный запрос. Например, если нам надо исключить из меню на сегодняшний день блюда, для приготовления которых необходимы продукты, имеющиеся на складе в ограниченном количестве (< 1 кг),
то надо выполнить запрос.
Пример 50.
DELETE FROM Меню
WHERE Дата = GETDATE() and Блюдо IN (SELECT Блюдо
FROM Состав c LEFT JOIN Наличие n ON c.Продукт =
n.Продукт
WHERE количество < 1 OR количество IS NULL);
Однако при выполнении операции DELETE, включающей
сложный подзапрос, в подзапросе нельзя упоминать таблицу, из
которой удаляются строки.
Функция GETDATE() возвращает текущую дату и время.
Все операции манипулирования данными связаны с понятием
целостности базы данных, которое будет рассматриваться далее,
поэтому они не всегда выполнимы, даже если написаны синтаксически правильно. Например, нельзя удалить продукт из таблицы
Продукты, если он используется для приготовления какого-либо
блюда, так как на него ссылаются строки из таблицы Состав.
7.4. Оператор UPDATE Операция обновления данных UPDATE требуется тогда, когда происходят изменения во внешнем мире и их надо адекватно
отразить в БД, так как надо всегда помнить, что БД отражает некоторую предметную область. Например, изменились закупочные
цены на продукты питания.
Синтаксис:
UPDATE< имя_таблицы>
SET <имя_столбца> = <новое_значение>[,< имя_столбца> = <новое_значение> …]
[ FROM <список_таблиц>]
[WHERE <условие_отбора>]
90
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Часть WHERE является необязательной, так же как и в операторе DELETE. Она позволяет отобрать строки, к которым будет
применена операция модификации. Если условие отбора не задается, то операция модификации будет применена ко всем строкам
таблицы.
Часть FROM позволяет задать имена таблиц, которые будут
использоваться при формировании условия отбора.
Например, при удорожании говядины на 5 процентов необходимо выполнить следующую команду обновления.
Пример 51.
UPDATE Наличие
SET Цена = Цена * 1.5
WHERE Продукт = (SELECT ID_Продукта
FROM Продукты
WHERE Продукт = 'Говядина');
или
UPDATE Наличие
SET Цена = Цена * 1.5
FROM Продукты
WHERE Наличие.Продукт = Продукты.ID_Продукта
and Продукты.Продукт = 'Говядина';
В первом случае используется вложенный подзапрос, который возвращает номер продукта, во втором случае происходит
соединение таблиц для нахождения нужного номера продукта.
В каких случаях требуется провести изменение в нескольких
строках? Это не такая уж редкая задача.
Пример 52.
UPDATE Наличие
SET Цена = Цена * 1.5;
Этот запрос поднимет цены на все продукты на 5%.
Если необходимо изменить значения в нескольких столбцах
таблицы, то надо выполнить запрос подобный этому.
Пример 53.
UPDATE Наличие
SET Количество = Количество + 100, Цена = Цена * 1.5
FROM Продукты
WHERE Наличие.Продукт = Продукты. ID_Продукта
and Продукты.Продукт = 'Говядина';
Пример изменения значения на неопределенное:
SET Белки = NULL
91
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Контрольные вопросы 1. Как добавить в таблицу одновременно несколько строк?
2. Укажите причины по которым операция добавления может завершится с ошибкой?
3. Как очистить всю таблицу?
4. Какой оператор позволяет изменить существующие данные?
5. Можно ли одной командой изменить(добавить, удалить) данные сразу в нескольких таблицах?
6. Как можно использовать оператор SELECT в командах манипулирования данными?
Глава 8. Представление Обычные таблицы, те, что мы рассматривали ранее, относятся
к базовым, т. е. содержащим данные и постоянно находящимся на
устройстве хранения информации. Представления, или просмотры
(VIEW), представляют собой временные, производные (иначе –
виртуальные) таблицы и являются объектами базы данных, информация в которых не хранится постоянно, как в базовых таблицах, а
формируется динамически при обращении к ним. Представление
не может существовать само по себе, а определяется только в терминах одной или нескольких таблиц (базовых или виртуальных).
Применение представлений позволяет разработчику базы данных
обеспечить каждому пользователю или группе пользователей наиболее подходящие способы работы с данными, что решает проблему простоты их использования и безопасности. Содержимое представлений выбирается из других таблиц с помощью выполнения
запроса, причем при изменении значений в таблицах данные в
представлении автоматически меняются. Представление – это
фактически тот же запрос, который выполняется всякий раз при
участии в какой-либо команде. Результат выполнения этого запроса
в каждый момент времени становится содержанием представления. У пользователя создается впечатление, что он работает с настоящей, реально существующей таблицей.
В отличие от других объектов базы данных, представление
не занимает дисковой памяти за исключением памяти, необходимой для хранения определения самого представления.
92
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Синтаксис:
CREATE
VIEW
<имя_представления>
[(<имя_столбца>
[,<имя_столбца> ...])]
[WITH ENCRYPTION]
AS SELECT …
[WITH CHECK OPTION]
По умолчанию имена столбцов в представлении соответствуют именам столбцов в исходных таблицах. Явное указание
имени столбца требуется для вычисляемых столбцов или при соединении нескольких таблиц, имеющих столбцы с одинаковыми
именами. Имена столбцов перечисляются через запятую, в соответствии с порядком их следования в представлении.
Параметр WITH ENCRYPTION предписывает СУБД шифровать SQL-код запроса, что гарантирует невозможность его
несанкционированного просмотра и использования. Параметр
WITH CHECK OPTION предписывает СУБД исполнять проверку изменений, производимых через представление, на соответствие критериям, определенным в операторе SELECT. Это
означает, что не допускается выполнение изменений, которые
приведут к исчезновению строки из представления. Такое случается, если для представления установлен горизонтальный
фильтр и изменение данных приводит к несоответствию строки
установленным фильтрам. Использование аргумента WITH
CHECK OPTION гарантирует, что сделанные изменения будут
отображены в представлении. Если пользователь пытается выполнить изменения, приводящие к исключению строки из представления, при заданном аргументе WITH CHECK OPTION
СУБД выдаст сообщение об ошибке и все изменения будут отклонены.
Пример 54.
Показать в представлении стоимость всех блюд.
Создание представления:
CREATE VIEW Цены (Название, Цена)
AS
SELECT b.Блюдо, SUM (s.Вес*n.Цена/1000)+b.Труд as Цена
FROM (Блюда b JOIN Состав s ON b.ID_блюда =s.Блюдо )
JOIN Наличие n ON s.Продукт = n.Продукт
GROUP BY b.Блюдо, b.Труд;
93
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Представление можно использовать в команде так же, как и
любую другую таблицу. К представлению можно строить запрос,
модифицировать его (если оно отвечает определенным требованиям), соединять с другими таблицами. Содержание представления
не фиксировано и обновляется каждый раз, когда на него ссылаются в команде.
Пример 55 (использование представлений).
Найти самые дешевые блюда.
SELECT *
FROM Цены
WHERE Цена = ( SELECT MIN(Цена) From Цены );
Представления значительно расширяют возможности
управления данными. В частности, это прекрасный способ разрешить доступ к информации в таблице, скрыв часть данных.
Представление может изменяться командами модификации
DML, но фактически модификация воздействует не на само представление, а на базовую таблицу.
Не все представления в SQL могут быть модифицированы.
Модифицируемое представление определяется следующими
критериями:
 основывается только на одной базовой таблице;
 содержит первичный ключ этой таблицы;
 не содержит DISTINCT в своем определении;
 не использует GROUP BY или HAVING в своем определении;
 по возможности не применяет в своем определении подзапросы;
 не использует константы или выражения значений среди
выбранных полей вывода;
 в просмотр должен быть включен каждый столбец таблицы, имеющий атрибут NOT NULL;
 оператор SELECT просмотра не использует агрегирующие (итоговые) функции, соединения таблиц, хранимые
процедуры и функции, определенные пользователем;
 основывается на одиночном запросе, поэтому объединение UNION не разрешено.
Если представление удовлетворяет этим условиям, к нему
могут применяться операторы INSERT, UPDATE, DELETE.
94
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Различия между модифицируемыми представлениями и представлениями, предназначенными только для чтения, не случайны. Цели, для которых их используют, различны. С модифицируемыми представлениями в основном обходятся точно
так же, как и с базовыми таблицами. Фактически пользователи
не могут даже осознать, является ли объект, который они запрашивают, базовой таблицей или представлением, т. е. прежде всего это средство защиты для сокрытия конфиденциальных
или не относящихся к потребностям данного пользователя частей таблицы. Представления в режиме <только для чтения>
позволяют получать и форматировать данные более рационально. Они создают целый арсенал сложных запросов, которые
можно выполнить и повторить снова, сохраняя полученную
информацию. Результаты этих запросов могут затем использоваться в других запросах, что позволит избежать сложных предикатов и снизить вероятность ошибочных действий.
Приведенное в примере представление является немодифицируемым.
Пример 56 (модифицируемого представления).
CREATE VIEW Список_блюд
AS
SELECT ID_Блюда, Блюдо, Вид, Труд FROM Блюда;
Команда модификации представления.
INSERT INTO Список_блюд VALUES (36, 'Рагу', 3, 20);
Преимущества и недостатки представлений
Механизм представления – мощное средство СУБД, позволяющее скрыть реальную структуру БД от некоторых пользователей за счет определения представлений.
Независимость от данных
С помощью представлений можно создать согласованную,
неизменную картину структуры базы данных, которая будет оставаться стабильной даже в случае изменения формата исходных таблиц (например, добавления или удаления столбцов, изменения связей, разделения таблиц, их реструктуризации или
переименования). Если в таблицу добавляются или из нее удаляются не используемые в представлении столбцы, то изменять
определение этого представления не потребуется. Если структура исходной таблицы переупорядочивается или таблица раз95
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
деляется, можно создать представление, позволяющее работать
с виртуальной таблицей прежнего формата. В случае разделения исходной таблицы, прежний формат может быть виртуально воссоздан с помощью представления, построенного на основе соединения вновь созданных таблиц, конечно, если это окажется возможным. Последнее условие можно обеспечить с помощью помещения во все вновь созданные таблицы первичного
ключа прежней таблицы.
Актуальность
Изменения данных в любой из таблиц базы данных, указанных в определяющем запросе, немедленно отображается на содержимом представления.
Повышение защищенности данных
Права доступа к данным могут быть предоставлены исключительно через ограниченный набор представлений, содержащих
только то подмножество данных, которое необходимо пользователю. Подобный подход позволяет существенно ужесточить контроль за доступом отдельных категорий пользователей к информации в базе данных.
Снижение стоимости
Представления позволяют упростить структуру запросов за
счет объединения данных из нескольких таблиц в единственную
виртуальную таблицу. В результате многотабличные запросы
сводятся к простым запросам к одному представлению.
Возможность настройки
Представления являются удобным средством настройки индивидуального образа базы данных. В результате одни и те же
таблицы могут быть предъявлены пользователям в совершенно
разном виде.
Однако использование представлений в среде SQL не лишено недостатков.
Ограниченные возможности обновления
В некоторых случаях представления не позволяют вносить
изменения в содержащиеся в них данные.
Структурные ограничения
Структура представления устанавливается в момент его
создания. Если определяющий запрос представлен в форме
96
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
SELECT * FROM …, то символ * ссылается на все столбцы,
существующие в исходной таблице на момент создания представления. Если впоследствии в исходную таблицу базы данных добавятся новые столбцы, то они не появятся в данном
представлении до тех пор, пока это представление не будет
удалено и вновь создано.
Снижение производительности
Использование представлений связано с определенным
снижением производительности. В одних случаях влияние этого фактора совершенно незначительно, тогда как в других оно
может послужить источником существенных проблем. Например, представление, определенное с помощью сложного многотабличного запроса, может потребовать значительных затрат
времени на обработку, поскольку при его разрешении потребуется выполнять соединение таблиц всякий раз, когда понадобится доступ к данному представлению. Выполнение разрешения представлений связано с использованием дополнительных
вычислительных ресурсов.
Контрольные вопросы 1. Как влияет изменение данных в таблицах, на данные в представлениях?
2. Какие виды представлений существуют?
3. Можно ли редактировать представления?
4. Можно ли использовать представления для сокрытия данных?
97
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Глава 9. Создание, изменение и удаление таблиц Эта глава вводит нас в область SQL, называемую DDL (Data
Definition Language – язык определения данных), где создаются
объекты данных SQL. Мы рассмотрим создание, изменение и
удаление таблиц и индексов. Индексы позволяют делать поиск в
БД более эффективным и иногда устанавливают ограничения на
вводимую в базу данных информацию.
9.1. Оператор CREATE TABLE В самом общем виде синтаксис команды создания таблицы
может быть представлен следующим образом:
CREATE TABLE <Имя таблицы>
(<Имя столбца> <Тип данных> <Ограничения на столбец>
[{, <Имя столбца> <Тип данных> <Ограничения на столбец> …}]
[<Ограничения на таблицу>]
)
Где:
<Имя таблицы> складывается из следующих элементов
[ database_name . [ schema_name ] . | schema_name . ] table_name
database_name – имя базы данных, в которой создается таблица. Если аргумент database_name не указан, по умолчанию
таблица создается в текущей базе данных.
schema_name – имя схемы, которой принадлежит новая таблица.
table_name – имя новой таблицы. Имена таблиц должны соответствовать правилам для идентификаторов. Аргумент table_name
может состоять не более чем из 128 символов, за исключением
имен локальных временных таблиц (имена с префиксом номера #),
длина которых не должна превышать 116 символов.
<Имя столбца> – имя столбца в таблице. Имена столбцов
должны соответствовать правилам для идентификаторов и быть
уникальными в данной таблице. Аргумент column_name может
98
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
содержать от 1 до 128 символов. При создании столбцов с типом
данных timestamp аргумент column_name может быть пропущен.
Если аргумент column_name не указан, столбцу типа timestamp
по умолчанию присваивается имя timestamp. Если имя столбца
состоит из нескольких слов (содержит пробелы), оно заключается
в квадратные скобки [ ].
<Тип данных> – указывает тип данных столбца. Тип данных
может быть одним из следующих.
 CHAR(n) – символьные строки постоянной длины в n символов (максимальная длина 8000 символов, не UNICODE). При
задании данного типа под каждое значение всегда отводится n
символов, и если реальное значение занимает менее чем n символов, то СУБД автоматически дополняет недостающие символы
пробелами.
 NUMERIC[(n,m)] (DECIMAL[(n,m)]) – точные числа, здесь
n – общее количество цифр в числе, m – количество цифр слева
от десятичной точки. Диапазон –1038–1 .. 1038–1
 BIGINT, INT, SMALLINT, TINYINT – целые числа длиной
8, 4, 2 и 1 байт соответственно. TINYINT – целое без знака.
 FLOAT[(n)](REAL) – числа большой точности, хранимые в
форме с плавающей точкой. Здесь n – число байтов, резервируемое под хранение одного числа. Диапазон чисел определяется
конкретной реализацией.
 VARCHAR(n) – строки символов переменной длины символов (максимальная длина 8000 символов, не UNICODE).
 TEXT(n) – символьные строки, место выделяется страницами размером 8 Кбайт (не UNICODE)
 NCHAR(N), NVARCHAR(n), NTEXT(n) – строки локализованных символов постоянной и переменной длины (4000 символов, UNICODE).
 BIT – один бит (допускает NULL-значение).
 BINARY(n), VARBINARY(n) – строки битов постоянной и
переменной длины (8000).
 DATE, TIME, DATETIME, SMALLDATETIME – календарная дата и время.
 TIMESTAMP(точность) – временная метка, уникальное
внутри БД значение.
99
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
 IMAGE – двоичные данные переменной длины.
Либо тип может быть создан с помощью инструкции
CREATE TYPE. Большинство коммерческих СУБД поддерживают еще дополнительные типы данных, которые не специфицированы в стандарте.
<Ограничения на столбец> – ограничения на данные, вводимые в данный столбец.
<Ограничения на таблицу> – ограничения, накладываемые
на несколько столбцов одновременно.
Пример:
Создание простейшей таблицы без ограничений:
CREATE ТABLE Справочник_вид_блюда(
ID_Вид
INT,
Вид
VARCHAR(20) );
Виды ограничений:
1. Исключение Null-значений
Для некоторых столбцов требуется наличие в каждой строке
таблицы конкретного и допустимого значения, отличного от опущенного значения или значения NULL. Для заданий ограничений
подобного типа стандарт SQL предусматривает использование
спецификации NOT NULL. Значение NULL – по умолчанию.
CREATE ТABLE Справочник_вид_блюда (
ID_Вид
INT NOT NULL,
Вид VARCHAR(20) );
2. Ограничение целостности сущности (PRIMARY KEY)
Таблица обычно имеет столбец или комбинацию столбцов,
значения которых уникально идентифицируют каждую строку в
таблице. Этот столбец (или столбцы) называется первичным ключом таблицы и нужен для обеспечения ее целостности. Если в первичный ключ входит более одного столбца, то значения в пределах
одного столбца могут дублироваться, но любая комбинация значений всех столбцов первичного ключа должна быть уникальна.
При создании первичного ключа SQL Server автоматически
создает уникальный индекс для столбцов, входящих в первичный
ключ. Индекс ускоряет доступ к данным этих столбцов при использовании первичного ключа в запросах. Таблица может иметь
только одно ограничение PRIMARY KEY, причем ни один из
100
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
включенных в первичный ключ столбцов не может принимать
значение NULL. При попытке использовать в качестве первичного ключа столбец (или группу столбцов), для которого ограничения первичного ключа не выполняются, первичный ключ создан не
будет, а система выдаст сообщение об ошибке.
Поскольку ограничение PRIMARY KEY гарантирует уникальность данных, оно часто определяется для столбцов-счетчиков.
Простой первичный ключ (ограничение на столбец)
CREATE ТABLE Справочник_вид_блюда (
ID_Вид
INT NOT NULL PRIMARY KEY,
Вид
VARCHAR(20) );
Составной первичный ключ (ограничение на таблицу)
CREATE ТABLE Состав (
Блюдо
INT NOT NULL ,
Продукт INT NOT NULL,
Вес INT,
PRIMARY KEY(Блюдо, Продукт) );
UNIQUE – это ограничение задает требование уникальности
значения поля столбца или группы полей столбцов, входящих в
уникальный ключ, по отношению к другим записям. Ограничение
UNIQUE для столбца таблицы похоже на первичный ключ: для
каждой строки данных в нем должны содержаться уникальные
значения. Установив для некоторого столбца ограничение первичного ключа, можно одновременно установить для другого
столбца ограничение UNIQUE. Отличие в ограничении первичного и уникального ключа заключается в том, что первичный ключ
служит как для упорядочения данных в таблице, так и для соединения связанных между собой таблиц. Кроме того, при использовании ограничения UNIQUE допускается существование значения NULL, но лишь единственный раз.
CREATE TABLE Сотрудник(
[Табельный номер] INT NOT NULL PRIMARY KEY,
[серия паспорта]
INT,
[номер паспорта]
INT,
[дата выдачи]
DATETIME,
ФИО
VARCHAR(25) NOT NULL,
101
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Адрес
VARCHAR(100),
UNIQUE ([серия паспорта], [номер паспорта]) );
Для ограничений PRIMARY KEY по умолчанию создается
кластеризованный индекс (CLUSTERED), а для ограничений
UNIQUE – некластеризованный (NONCLUSTERED).
В инструкции CREATE TABLE параметр CLUSTERED можно задать только для одного ограничения. Если для ограничения
UNIQUE указан параметр CLUSTERED и, кроме того, указано
ограничение PRIMARY KEY, то для PRIMARY KEY применяется по умолчанию значение NONCLUSTERED.
3. Ограничения целостности CHECK
Данное ограничение используется для проверки допустимости данных, вводимых в конкретный столбец таблицы, т. е. обеспечивает еще один уровень защиты данных.
Ограничения CHECK задают диапазон возможных значений
для столбца или столбцов. В основе CHECK лежит использование
логических выражений. Допускается применение нескольких ограничений CHECK к одному и тому же столбцу. В этом случае они
будут применимы в той последовательности, в которой происходило их создание. Возможно применение одного и того же ограничения к разным столбцам и использование в логических выражениях
значений других столбцов. Проверочные ограничения могут быть
реализованы и с помощью правил. Правило представляет собой самостоятельный объект базы данных, который связывается со
столбцом таблицы или пользовательским типом данных. Причем
одно и то же правило может быть одновременно связано с несколькими столбцами и пользовательскими типами данных, что является
неоспоримым преимуществом. Однако существенный недостаток
заключается в том, что с каждым столбцом или типом данных может быть связано только одно правило. Разрешается комбинирование ограничений целостности CHECK с правилами. В этом случае
выполняется проверка соответствия вводимого значения как ограничениям целостности, так и правилам.
Пример использования
Задание шаблона для ввода (ограничение на столбец)
Номер CHAR (6) CHECK(Номер LIKE '[A-Z][A-Z][A-Z][1-9][0-9][FM]')
102
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Ограничение на вводимые данные (значение только из списка) (ограничение на столбец)
Город VARCHAR(10)
'Иваново'))
CHECK
(Город
IN
('Ярославль',
'Москва',
Ограничение на группу полей (ограничение на таблицу)
CHECK (Комиссионные <0.5 OR Город = 'Ярославль')
4. Ограничение по умолчанию (DEFAULT)
Столбцу может быть присвоено значение по умолчанию. Оно
будет актуальным в том случае, если пользователь не введет в
столбец никакого иного значения. Отдельно необходимо отметить пользу от использования значений по умолчанию при добавлении нового столбца в таблицу. Если для добавляемого
столбца не разрешено хранение значений NULL и не определено
значение по умолчанию, то операция добавления столбца закончится неудачей.
Пример использования
Город VARCHAR(20) DEFAULT('Ярославль')
5. Ограничение ссылочной целостности (FOREIGN KEY)
Ограничение внешнего ключа – это основной механизм для
поддержания ссылочной целостности между таблицами реляционной БД. Столбец дочерней таблицы, определенный в качестве
внешнего ключа в параметре FOREIGN KEY, применяется для
ссылки на столбец родительской таблицы, являющийся в ней
первичным ключом. Имя родительской таблицы и столбцы ее первичного ключа указываются в предложении REFERENCES. Данные в столбцах, определенных в качестве внешнего ключа, могут
принимать только такие же значения, какие находятся в связанных с ним столбцах первичного ключа родительской таблицы.
Совпадение имен столбцов для связи дочерней и родительской
таблиц необязательно. Единственным требованием остается соответствие столбцов по типу и размеру данных.
На первичный ключ могут ссылаться не только столбцы других таблиц, но и столбцы, расположенные в той же таблице, что и
собственно первичный ключ; это позволяет создавать рекурсивные структуры.
Внешний ключ может быть связан не только с первичным
ключом другой таблицы. Он может быть определен и для столб103
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
цов с ограничением UNIQUE второй таблицы или любых других
столбцов, но таблицы должны находиться в одной базе данных.
Столбцы внешнего ключа могут содержать значение NULL,
однако проверка на ограничение FOREIGN KEY игнорируется.
Внешний ключ может быть проиндексирован, тогда сервер будет
быстрее отыскивать нужные данные. Внешний ключ определяется
как при создании, так и при изменении таблиц.
Ограничение ссылочной целостности задает требование, согласно которому для каждой записи в дочерней таблице должна
иметься запись в родительской таблице. При этом изменение значения столбца связи в записи родительской таблицы при наличии
дочерней записи блокируется, равно как и удаление родительской
записи (запрет каскадного изменения и удаления), что гарантируется параметрами ON DELETE NO ACTION и ON UPDATE NO
ACTION, принятыми по умолчанию. Для разрешения каскадного
воздействия следует использовать параметры ON DELETE
CASCADE и ON UPDATE CASCADE.
Синтаксис:
[ FOREIGN KEY ]
REFERENCES referenced_table_name [ (ref_column [ , ... ]) ]
[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET
DEFAULT}]
[ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET
DEFAULT } ]
где:
referenced_table_name – имя таблицы, на которую ссылается
ограничение FOREIGN KEY.
( ref_column [, ... ] ) – столбец или список столбцов из таблицы, на которую ссылается ограничение FOREIGN KEY. Можно
не указывать, если ссылка идет на первичный ключ.
ON DELETE { NO ACTION | CASCADE | SET NULL | SET
DEFAULT } – определяет операцию, которая производится над
строками создаваемой таблицы, если эти строки имеют ссылочную связь, а строка, на которую имеются ссылки, удаляется из
родительской таблицы.
ON UPDATE { NO ACTION | CASCADE | SET NULL | SET
DEFAULT } – указывает, какое действие совершается над стро104
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
ками в изменяемой таблице, когда у этих строк есть ссылочная
связь и строка родительской таблицы, на которую указывает
ссылка, обновляется.
Параметр по умолчанию – NO ACTION.
NO ACTION – ядро СУБД формирует ошибку, и выполняется
откат операции удаления (обновления) строки из родительской
таблицы.
CASCADE – если из (в) родительской таблицы удаляется
(обновляется) строка, соответствующие ей строки удаляются (обновляются) из (в) ссылающейся таблицы.
SET NULL – все значения, составляющие внешний ключ, при
удалении (обновлении) соответствующей строки родительской
таблицы устанавливаются в NULL. Для выполнения этого ограничения столбцы внешних ключей должны допускать существование значений NULL.
SET DEFAULT – все значения, составляющие внешний ключ,
при удалении (обновлении) соответствующей строки родительской таблицы устанавливаются в значение по умолчанию. Для
выполнения этого ограничения все столбцы внешних ключей
должны иметь определения по умолчанию. Если столбец допускает значение NULL и множество значений по умолчанию не задано явно, NULL становится неявным значением по умолчанию
для данного столбца.
Параметр ON DELETE CASCADE нельзя указывать, если в
таблице уже существует триггер ON DELETE.
Действие ON UPDATE CASCADE не может быть определено, если в изменяемой таблице уже существует триггер INSTEAD
OF ON UPDATE.
Пример использования.
Внешний ключ – ограничение на столбец.
CREATE ТABLE Состав (
Блюдо INT NOT NULL FOREIGN KEY REFERENCES Блюда(ID_Блюда),
Продукт INT NOT NULL REFERENCES Продукты,
Вес INT,
PRIMARY KEY (Блюдо, Продукт));
Внешний ключ – ограничение на таблицу.
CREATE ТABLE Заказы (
105
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
ID_Заказ INT NOT NULL,
Блюдо INT NOT NULL,
Количество_порций INT NOT NULL CHECK(Количество_порций >0),
Дата DATE NOT NULL,
PRIMARY KEY(ID_Заказ, Блюдо, Дата),
FOREIGN KEY (Блюдо, Дата) REFERENCES Меню (Блюдо, Дата) ) ;
Рекурсивная ссылка – внешний ключ ссылается на первичный
ключ той же таблицы.
CREATE TABLE Сотрудник(
[Табельный номер ]
INT NOT NULL PRIMARY KEY,
[серия паспорта]
INT,
[номер паспорта]
INT,
[дата выдачи]
DATETIME,
ФИО
VARCHAR(25) NOT NULL,
Адрес
VARCHAR(100),
Руководитель
INT NULL REFERENCES Сотрудник,
UNIQUE ([серия паспорта], [номер паспорта]) );
Для столбца Руководитель нельзя использовать ограничение NOT
NULL, так как ни одну запись нельзя будет добавить в таблицу.
6. Ограничение IDENTITY (автоинкрементное поле)
Указывает, что новый столбец является столбцом идентификаторов. При добавлении в таблицу новой строки ядро СУБД
формирует для этого столбца уникальное последовательное значение. Столбцы идентификаторов обычно используются с ограничением PRIMARY KEY для поддержания уникальности идентификаторов строк в таблице. Свойство IDENTITY может быть
присвоено столбцам типов tinyint, smallint, int, bigint, decimal(p,0)
или numeric(p,0). Для каждой таблицы можно создать только
один столбец идентификаторов. Значения по умолчанию не могут
использоваться в столбце идентификаторов. Необходимо указать
как начальное значение, так и приращение, или же не указывать
ничего. Если ни одно из значений не указано, значением по
умолчанию является (1,1).
Синтаксис:
[ IDENTITY [ ( seed, increment ) ] ]
seed – значение, используемое для самой первой строки, загружаемой в таблицу.
106
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
increment – значение приращения, добавляемое к значению
идентификатора предыдущей загруженной строки.
Пример:
[Табельный номер]
INT IDENTITY (1, 1) NOT NULL PRIMARY KEY
7. Вычисляемые поля
Синтаксис:
<имя_столбца> AS <выражение> [PERSISTED [NOT NULL] ]
Выражение, определяющее значение вычисляемого столбца.
Вычисляемый столбец представляет собой виртуальный столбец,
физически не хранящийся в таблице, если для него не установлен
признак PERSISTED. Значение столбца вычисляется на основе
выражения, использующего другие столбцы той же таблицы.
Например, определение вычисляемого столбца может быть
следующим:
Стоимость_товара AS Цена_за_штуку * Количество.
Выражение может быть именем невычисляемого столбца,
константой, функцией, переменной или любой их комбинацией,
соединенной одним или несколькими операторами. Выражение
не может быть вложенным запросом или содержать псевдонимы
типов данных.
Вычисляемые столбцы могут использоваться в списках выбора, предложениях WHERE, ORDER BY и в любых других местах, в которых могут использоваться обычные выражения, за исключением следующих случаев.
Вычисляемый столбец нельзя использовать ни в качестве определения ограничения DEFAULT или FOREIGN KEY, ни вместе
с определением ограничения NOT NULL. Однако вычисляемый
столбец может использоваться в качестве ключевого столбца индекса или части какого-либо ограничения PRIMARY KEY или
UNIQUE, если значение этого вычисляемого столбца определяется детерминистическим (предсказуемым) выражением и тип данных результата разрешен в столбцах индекса.
Например, если таблица содержит целочисленные столбцы a
и b, вычисляемый столбец a+b может быть включен в индекс, а
вычисляемый столбец a+DATEPART(dd, GETDATE()) не может, так как его значение может изменяться при последующих
вызовах.
107
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Вычисляемый столбец не может быть целевым столбцом инструкций INSERT или UPDATE.
СУБД автоматически определяет для вычисляемых столбцов
допустимость значений NULL на основе использованных выражений. Считается, что результат большинства выражений допускает значение NULL, даже если используются только столбцы,
для которых значение NULL запрещено, так как при возможном
переполнении или потере точности может формироваться значение NULL. Для выяснения возможности вычисляемого столбца
таблицы принимать значение NULL используйте функцию
COLUMNPROPERTY со свойством AllowsNull. Добиться того,
чтобы выражение не допускало значения NULL, можно, указав
ISNULL с константой check_expression, где константа представляет собой ненулевое значение, заменяющее любое значение
NULL. Для вычисляемых столбцов, основанных на выражениях,
содержащих определяемые пользователем типы среды CLR, требуется разрешение REFERENCES на тип.
PERSISTED указывает, что компонент SQL Server будет физически хранить вычисляемые значения в таблице и обновлять их
при изменении любого столбца, от которого зависит вычисляемый столбец. Указание PERSISTED для вычисляемого столбца
позволяет создать индекс по вычисляемому столбцу, который будет детерминистическим, но неточным.
При создании таблицы, кроме рассмотренных приемов, можно
указать необязательное ключевое слово CONSTRAINT, чтобы присвоить ограничению имя, уникальное в пределах базы данных.
CREATE ТABLE
Заказы(
ID_Заказ INT NOT NULL,
Блюдо INT NOT NULL,
Количество_порций INT NOT NULL CHECK(Количество_порций >0),
Дата DATE NOT NULL,
PRIMARY KEY(ID_Заказ, Блюдо, Дата),
CONSTRAINT FOREIGN_KEY
FOREIGN KEY (Блюдо, Дата) REFERENCES Меню(Блюдо, Дата) ) ;
Затем с поименованным ограничением можно работать как с
объектом БД. Если имя ограничения не задано, ядро СУДБ создаст его автоматически, выбрав имя по определенным в системе
правилам.
108
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Пример скрипта, построенного системой для создания таблицы Справочник_вид_блюда
CREATE TABLE [dbo].[Справочник_вид_блюда](
[ID_вид] [int] IDENTITY(1,1) NOT NULL,
[Вид] [varchar](20) NOT NULL,
CONSTRAINT
[PK_
Справочник_вид_блюда]
PRIMARY
KEY
CLUSTERED
(
[ID_вид] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS
= ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
9.2. Изменение таблицы после того, как она была создана (ALTER TABLE) В уже созданную таблицу изменения могут быть внесены с
помощью оператора ALTER TABLE, который имеет следующий
обобщенный формат:
ALTER TABLE <Имя таблицы>
ALTER COLUMN <Имя столбца> <Тип данных> [NOT NULL]
[UNIQUE]
[DEFAULT <Значение по умолчанию>] [CHECK (<Условие проверки на Допустимость>)]
| ADD [COLUMN] <Имя столбца> <Тип данных> [NOT NULL]
[UNIQUE]
[DEFAULT <Значение по умолчанию>] [CHECK (<Условие проверки на Допустимость>)]
| DROP [COLUMN] <Имя столбца> [RISTRICT | CASCADE]
| ADD [CONSTRAINT [(<Имя ограничения>)] <Ограничение>]
| DROP CONSTRAINT <Имя ограничения> [RISTRICT | CASCADE]
В данном формате предусмотрены возможности для выполнения ряда действий:
• изменить существующий столбец в существующей таблице – ALTER COLUMN;
• добавить новый столбец в существующую таблицу – ADD
COLUMN;
109
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
• удалить столбец из существующей таблицы – DROP
COLUMN;
• добавить в определение таблицы новое ограничение – ADD
CONSTRAINT;
• удалить из определения таблицы существующее ограничение – DROP CONSTRAINT.
Пример добавления столбца
CREATE TABLE doc_exa (column_a INT);
ALTER TABLE doc_exa ADD column_b VARCHAR(20) NULL;
Пример удаления столбца
CREATE TABLE doc_exb (column_a INT, column_b VARCHAR(20) NULL);
ALTER TABLE doc_exb DROP COLUMN column_b;
Пример изменения типа данных столбца (с INT на DECIMAL)
CREATE TABLE doc_exy (column_a INT );
INSERT INTO.doc_exy (column_a) VALUES (10);
ALTER TABLE doc_exy ALTER COLUMN column_a DECIMAL (5, 2);
Пример добавления нового столбца с ограничением UNIQUE.
CREATE TABLE doc_exc (column_a INT);
ALTER TABLE doc_exc ADD column_b VARCHAR(20) NULL
CONSTRAINT exb_unique UNIQUE;
Пример добавления непроверяемого ограничения CHECK к
существующему столбцу.
CREATE TABLE doc_exd ( column_a INT);
INSERT INTO doc_exd VALUES (-1);
ALTER TABLE doc_exd WITH NOCHECK
ADD CONSTRAINT exd_check CHECK (column_a > 1);
К существующему столбцу в таблице добавляется ограничение. Столбец имеет значение, нарушающее это ограничение. Поэтому во избежание проверки ограничения относительно существующих строк, а также, чтобы разрешить добавление ограничения, применяется WITH NOCHECK.
Пример добавления ограничения DEFAULT во второй столбец.
CREATE TABLE doc_exz ( column_a INT, column_b INT) ;
INSERT INTO doc_exz (column_a) VALUES ( 7 );
ALTER TABLE doc_exz ADD CONSTRAINT col_b_def DEFAULT 50 FOR
column_b;
110
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
9.3. Удаление таблицы (DROP TABLE) Синтаксис:
DROP TABLE <Имя таблицы> [,<Имя таблицы> … ];
Удаляет одно или больше определений таблиц и все данные,
индексы, триггеры, ограничения и разрешения для этих таблиц.
Любое представление или хранимая процедура, ссылающаяся на
удаленную таблицу, должна быть явно удалена с помощью инструкции DROP VIEW или DROP PROCEDURE.
Инструкцию DROP TABLE нельзя использовать для удаления таблицы, на которую ссылается ограничение FOREIGN KEY.
Сначала следует удалить ссылающееся ограничение FOREIGN
KEY или ссылающуюся таблицу. Если и ссылающаяся таблица, и
таблица, содержащая первичный ключ, удаляются с помощью
одной инструкции DROP TABLE, ссылающаяся таблица должна
быть первой в списке.
При удалении таблицы относящиеся к ней правила и значения по умолчанию теряют привязку, а любые связанные с таблицей ограничения или триггеры автоматически удаляются. Если
таблица будет создана заново, нужно будет заново привязать все
правила и значения по умолчанию, заново создать триггеры и добавить необходимые ограничения.
При удалении всех строк в таблице с помощью инструкции
DELETE tablename или TRUNCATE TABLE таблица продолжает
существовать, пока она не будет удалена.
Пример
DROP TABLE Состав;
9.4. Операторы создания и удаления индексов Поскольку базы данных предназначены для хранения больших объемов информации, эффективность их использования в
информационных системах во многом определяется скоростью
выборки данных. Для увеличения скорости выборки в БД обычно
используют специальную структуру, которая называется индексом. Стандарт языка SQL не предусматривает использование индексов. Но тем не менее разработчики СУБД охотно идут на
включение средств поддержки индексов в систему, несмотря на
то что наличие индекса увеличивает нагрузку на систему из-за
111
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
необходимости обновлять его при каждом изменении данных
таблицы, поскольку существенное повышение скорости запросов
окупает данные затраты.
Операторы создания и удаления индекса имеют следующий
формат.
Создать индекс:
CREATE [UNIQUE] [ CLUSTERED | NONCLUSTERED ] INDEX
<имя_индекса>
ON <Имя таблицы> (<Имя столбца> [ASC| DESC] [, <Имя столбца> [ASC|
DESC] …])
где
UNIQUE – создает уникальный индекс для таблицы или
представления. Уникальным является индекс, в котором не может быть двух строк с одним и тем же значением ключа индекса.
Кластеризованный индекс представления должен быть уникальным. Ядро СУБД не позволяет создать уникальный индекс по
столбцам, уже содержащим повторяющиеся значения. При попытке создания такого индекса выдается сообщение об ошибке.
Прежде чем создавать уникальный индекс по такому столбцу или
столбцам, необходимо удалить все повторяющиеся значения.
Столбцы, используемые в уникальном индексе, должны иметь
свойство NOT NULL, т. к. при создании индекса значения NULL
рассматриваются как повторяющиеся.
CLUSTERED – создает индекс, в котором логический порядок значений ключа определяет физический порядок соответствующих строк в таблице. На нижнем (конечном) уровне такого
индекса хранятся действительные строки данных таблицы. Для
таблицы или представления в каждый момент времени может
существовать только один кластеризованный индекс. Представление с уникальным кластеризованным индексом называется индексированным. Создание уникального кластеризованного индекса физически материализует представление. Уникальный кластеризованный индекс для представления должен быть создан до
того, как для этого же представления будут определены какиелибо другие индексы. Создавайте кластеризованные индексы до
создания любых некластеризованных. При создании кластеризованного индекса все существующие некластеризованные индексы
112
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
таблицы перестраиваются. Если аргумент CLUSTERED не указан, создается некластеризованный индекс.
NONCLUSTERED – создание индекса, задающего логическое упорядочение для таблицы. Логический порядок строк в некластеризованном индексе не влияет на их физический порядок.
Для каждой таблицы можно создать до 999 некластеризованных
индексов, независимо от того, каким образом они создаются: неявно с помощью ограничений PRIMARY KEY и UNIQUE или явно с помощью инструкции CREATE INDEX. Для индексированных представлений некластеризованные индексы могут создаваться только в случае, если уже определен уникальный кластеризованный индекс.
По умолчанию используется значение NONCLUSTERED.
Удалить индекс:
DROP INDEX <Имя индекса>
Если в операторе CREATE INDEX используется квалификатор UNIQUE, то уникальность значений индекса автоматически
поддерживается системой. Для каждого из ключевых столбцов
можно указать порядок следования значений: по возрастанию –
ASC (используется по умолчанию) и по убыванию – DESC.
9.5. Понятие домена Домен – это набор допустимых значений для одного или нескольких атрибутов. Если в таблице базы данных или в нескольких
таблицах присутствуют столбцы, обладающие одними и теми же
характеристиками, можно описать тип такого столбца и его поведение через домен, а затем поставить в соответствие каждому из
одинаковых столбцов имя домена. Домен определяет все потенциальные значения, которые могут быть присвоены атрибуту.
Стандарт SQL позволяет определить домен с помощью следующего оператора:
CREATE DOMAIN <имя_домена> [AS] <тип_данных> [ DEFAULT <значение>]
[ CHECK (<допустимые_значения>)]
Каждому создаваемому домену присваивается имя, тип данных, значение по умолчанию и набор допустимых значений. Сле113
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
дует отметить, что приведенный формат оператора является неполным. Теперь при создании таблицы можно указать вместо типа данных имя домена.
Удаление доменов из базы данных выполняется с помощью
оператора:
DROP DOMAIN <имя_домена> [ RESTRICT |CASCADE]
В случае указания ключевого слова CASCADE любые столбцы таблиц, созданные с использованием удаляемого домена, будут автоматически изменены и описаны как содержащие данные
того типа, который был указан в определении удаляемого домена.
Альтернативой доменам в среде SQL Server являются пользовательские типы данных.
Контрольные вопросы 1. Перечислите ограничения, которые можно накладывать на поля (группы полей) в таблице?
2. В чем существенное отличие PRIMARY KEY и UNIQUE?
3. Какое ограничение обеспечивает целостность по ссылке?
4. В чем отличие кластеризованного и некластеризованного индекса?
5. Может ли пользователь создать свой тип данных?
114
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Глава 10. Обобщенные табличные выражения (СТЕ) Многие проекты, над которыми трудятся разработчики,
включают в себя написание сложных операторов SQL, отличающихся от операторов SELECT/FROM/WHERE базовых типов. В
одном из таких случаев приходится внутри предложения FROM
писать запросы, использующие производные таблицы. Общепринятая практика позволяет разработчику взять набор строк и непосредственно в операторе SELECT объединить эти строки с другими таблицами, представлениями и пользовательскими функциями. Другая возможность состоит в использовании представления вместо производной таблицы. Оба этих варианта имеют
свои преимущества и недостатки.
При работе с SQL Server (начиная с версии 2005) существует
третья возможность – использование обобщенных табличных выражений (CTE). Выражения CTE помогают повысить удобочитаемость (и, таким образом, возможность обслуживания) кода, не
ухудшая производительности. Кроме этого, по сравнению с предыдущей версией SQL Server они значительно облегчают написание рекурсивного кода в T-SQL.
Синтаксис рекурсивного запроса (cтандарт SQL:1999):
WITH [ RECURSIVE ] <имя_запроса> [ ( <список столбцов> ) ]
AS (<запрос select> )
<запрос, использующий имя_запроса>;
10.1. Представления, производные таблицы и выражения CTE Выражения CTE могут оказаться полезными, когда запросам
необходимо делать выборку из набора данных, не представленного в виде таблицы в БД. Например, может понадобиться написать
запрос к набору агрегированных данных, в котором вычисляется
стоимость блюд, зависящая от состава продуктов и трудозатрат
на приготовление блюда. Агрегированные данные могут объединять таблицы Блюда, Состав, Наличие и вычислять сумму по каждому блюду. Может потребоваться выдать запрос к агрегиро115
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
ванному набору строк (найти самое дешевое блюдо). Одно из
решений заключается в том, чтобы сначала создать представление, которое собирает агрегированные данные, а затем написать
запрос к этому представлению. Другая возможность состоит в
выдаче запроса к агрегированным данным с помощью производной таблицы. Это реализуется перемещением оператора SQL в
предложение FROM и выдаче запроса к нему.
Представления обычно используются для разбиения больших
запросов, чтобы их можно было сформулировать в гораздо более
удобочитаемом виде. Затем к представлению можно направить
запрос с использованием других операторов SELECT в рамках
всей базы данных. Такая абстракция обеспечивает более удобный
доступ к набору строк, представленному представлением, и не
возникает необходимости в дублировании данных или хранении
их во временной таблице.
Представление может также повторно использоваться по всей
базе данных при условии, что разрешения допускают это. Например, создается представление, определяющее стоимость всех блюд:
CREATE VIEW
Цена (Блюдо, Стоимость)
AS
SELECT b.Блюдо,
cast(SUM (s.Вес*n.Цена/1000)+b.Труд as numeric(6,2)) as Стоимость
FROM (Блюда b JOIN Состав s ON b.ID_блюда = s.Блюдо )
JOIN Наличие n ON s.Продукт = n.Продукт
GROUP BY b.Блюдо, b.Труд;
после чего оно используется другим оператором для нахождения
самого дешевого блюда:
Результат
Блюдо
Цена
Кофе черный 2,33
SELECT Блюдо, Стоимость
FROM Цена
WHERE Стоимость = (SELECT MIN(Стоимость) FROM Цена);
Однако в тех ситуациях, когда требуется собрать данные для
однократного использования, представления, возможно, будут не
лучшим решением. Поскольку представление является объектом
БД, который существует и является доступным в рамках всей БД
всем пакетам (запросам, хранимым процедурам и т. д.), создание
представления, используемого только одним пакетом T-SQL, –
это явный перегиб.
116
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Другая возможность состоит в создании производной таблицы. Производную таблицу можно создать простым перемещением оператора SELECT в предложение FROM, заключенного в
круглые скобки. К ней можно обращать запросы или присоединяться к ней точно так же, как к таблице или представлению.
SELECT Блюдо, Стоимость
FROM (SELECT b.Блюдо,
cast(SUM (s.Вес*n.Цена/1000)+b.Труд as numeric(6,2)) as Стоимость
FROM (Блюда b JOIN Состав s ON b.ID_блюда = s.Блюдо )
JOIN Наличие n ON s.Продукт = n.Продукт
GROUP BY b.Блюдо, b.Труд
) Цена
WHERE Стоимость =
(SELECT MIN( SELECT b.Блюдо,
SUM (s.Вес*n.Цена/1000)+b.Труд as Стоимость
FROM (Блюда b JOIN Состав s ON b.ID_блюда = s.Блюдо )
JOIN Наличие n ON s.Продукт = n.Продукт
GROUP BY b.Блюдо, b.Труд)
FROM Цена);
Данный код обеспечивает тот же запрос, что и предыдущий,
но вместо представления в нем используются производные таблицы. Хотя производные таблицы доступны только в рамках оператора, в котором они существуют, в общем случае таблицы усложняют прочтение и вследствие этого – обслуживание запроса.
Эта проблема умножается, если производную таблицу (как в нашем примере) требуется использовать многократно в пределах
одного и того же пакета, поскольку для повторного использования производную таблицу потребуется копировать и вставлять.
В такой ситуации прекрасно подходит выражение CTE, поскольку оно обеспечивает большую удобочитаемость T-SQL (подобно представлению), но все-таки может использоваться более
одного раза в запросе, непосредственно следующем за ним в этом
же пакете. Безусловно, это невозможно за пределами указанной
сферы действия. Кроме этого, CTE является конструкцией уровня
языка – это означает, что SQL Server не создает внутренних временных или виртуальных таблиц. Запрос, лежащий в основе выражения CTE, можно вызывать каждый раз, когда на него появляется ссылка в следующем непосредственно за ним запросе.
117
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Следовательно, эту же ситуацию можно описать с помощью
выражения
WITH
Цена_СТЕ (Блюдо, Стоимость)
AS
(SELECT b.Блюдо, SUM (s.Вес*n.Цена/1000)+b.Труд as Стоимость
FROM (Блюда b JOIN Состав s ON b.ID_блюда = s.Блюдо )
JOIN Наличие n ON s.Продукт = n.Продукт
GROUP BY b.Блюдо, b.Труд)
SELECT Блюдо, Стоимость
FROM Цена_СТЕ
WHERE Стоимость = (SELECT MIN(Стоимость) FROM Цена_СТЕ);
Цена_CTE собирает агрегированные данные, и затем его использует запрос, непосредственно следующий за выражением
CTE. Применение выражения CTE в коде делает запрос удобочитаемым (подобно представлению), но не создает системный объект для хранения метаданных.
Выражение CTE начинается ключевым словом WITH. За
ключевым словом WITH следует имя выражения CTE, за которым идет необязательный список псевдонимов столбцов. Псевдонимы столбцов соответствуют столбцам, возвращаемым оператором SELECT, входящим в выражение CTE. За необязательными псевдонимами столбцов следует ключевое слово AS – оно является обязательным. А за ключевым словом AS следует выражение запроса, определяющее выражение CTE, заключенное в
круглые скобки.
Непосредственно за выражением CTE следует оператор
SELECT, ссылающийся на выражение CTE посредством псевдонимов столбцов.
Поскольку выражение CTE предназначено для того, чтобы на
него ссылался другой запрос, который затем может повторно обработать данные любым способом, запрос CTE не может содержать такие операторы, как ORDER и COMPUTE.
Выражение CTE не обязательно должно использоваться оператором SELECT; оно доступно для использования любому оператору, который ссылается на набор строк, генерируемый выражением CTE. Это означает, что за выражением CTE могут следовать операторы SELECT, INSERT, UPDATE или DELETE, использующие это выражение CTE.
118
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Более того, за выражением CTE может следовать еще одно
выражение CTE. Этот прием используется для построения выражения CTE на основе другого CTE, если в набор строк требуется
собрать промежуточные результаты. При создании выражения
CTE, строящегося на основе другого выражения CTE, определения выражений CTE разделяются запятой.
WITH
Цена (Блюдо, Стоимость) -- первое СТЕ
AS
(SELECT b.Блюдо, SUM (s.Вес*n.Цена/1000)+b.Труд as Стоимость
FROM (Блюда b JOIN Состав s ON b.ID_блюда = s.Блюдо )
JOIN Наличие n ON s.Продукт = n.Продукт
GROUP BY b.Блюдо, b.Труд),
Мин_Цена(Стоимость)
-- второе СТЕ
AS
(SELECT MIN(Стоимость) FROM Цена)
SELECT Блюдо, Цена .Стоимость
--запрос, использующий СТЕ
FROM
Цена JOIN Мин_Цена
ON Цена .Стоимость= Мин.Цена .Стоимость;
Этот запрос дает те же самые результаты, что и предыдущие
варианты.
10.2. Рекурсивные запросы Выражения CTE можно использовать для реализации рекурсивных алгоритмов. Рекурсивная логика удобна, когда требуется
написать алгоритм, вызывающий самого себя – это часто делается для прохода по вложенному набору данных. Написание рекурсивной логики может оказаться сложной задачей, особенно на таком языке, как T-SQL, однако это одна из тех специальных задач,
для решения которых были разработаны выражения CTE. Простейший рецепт создания рекурсивного выражения CTE выглядит следующим образом:
 создать запрос, возвращающий верхний уровень (это закрепленный элемент, начальная точка рекурсии);
 написать рекурсивный запрос (это рекурсивный элемент,
показывает, как перейти к следующему шагу рекурсии);
 выполнить операцию UNION для первого и рекурсивного
запросов;
119
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
 разместить UNION в СТЕ;
 связать CTE и рекурсивный запрос внутри CTE, чтобы
обеспечить пошаговое выполнение.
В результате рекурсивное выражение CTE будет выглядеть
приблизительно следующим образом.
WITH
myRecursiveCTE(col1, col2, ... coln)
AS
(
-- Anchor Member Query
UNION ALL
-- Recursive Member Query that references myRecursiveCTE
)
При написании пользовательской рекурсивной процедуры, не содержащей выражений CTE, необходимо включить явное завершающее предложение. Завершающее предложение отвечает за
обеспечение завершения (в конце концов) рекурсивного алгоритма и возвращение наверх по стеку рекурсивных вызовов. Без этого предложения ваш код может уйти в бесконечный цикл.
Выражения CTE обладают двумя свойствами, которые облегчают работу с завершающими предложениями. Первое состоит в
том, что если рекурсивный член возвращает пустые записи, возникает неявное завершающее предложение. В таком случае запрос рекурсивного члена не обращается рекурсивно к выражению CTE, а вместо этого возвращает наверх стек вызовов. Другая
особенность состоит в том, что имеется возможность явно установить уровень MAXRECURSION.
Уровень MAXRECURSION можно установить явно в пакете,
содержащем CTE, или посредством параметра на серверной стороне (по умолчанию в рамках сервера действует значение 100,
если только оно не было изменено). Этот параметр ограничивает
число вызовов выражения CTE, обращенных к нему самому. При
достижении предельного значения создается исключение. В синтаксисе для установки уровня MAXRECURSION необходимо использовать предложение OPTION в операторе SELECT, следующем за выражением CTE, как показано далее
SELECT * FROM CTE
OPTION (MAXRECURSION 7);
120
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Существуют некоторые другие правила, о которых следует
помнить при построении рекурсивных выражений CTE. В рекурсивное выражение CTE должен входить как закрепленный, так и
рекурсивный элемент. Оба элемента должны иметь одинаковое
число столбцов, и столбцы, принадлежащие обоим элементам,
должны иметь одинаковые типы данных. Рекурсивный элемент
может ссылаться на выражение CTE только один раз, и в элементах не разрешается использовать следующие предложения или
ключевые слова.
 SELECT DISTINCT
 GROUP BY
 HAVING
 TOP
 LEFT/RIGHT OUTER JOIN
Пример простой иерархии [Frederic BROUARD (оригинал:
Recursive Queries in SQL:1999 and SQL Server 2005) ].
Создадим таблицу, которая содержит типологию транспортных средств:
CREATE TABLE Tree
(ID INTEGER NOT NULL PRIMARY KEY,
ID_FATHER INTEGER FOREIGN KEY REFERENCES Tree (ID),
NAME VARCHAR(16));
Заполним её данными
INSERT INTO Tree
VALUES (1, NULL, 'ALL'), (2, 1, 'SEA'), (3, 1, 'EARTH'),
(4, 1, 'AIR'), (5, 2, 'SUBMARINE'),
(6, 2, 'BOAT'), (7, 3, 'CAR'),
(8, 3, 'TWO WHEELES'), (9, 3, 'TRUCK'),
(10, 4, 'ROCKET'), (11, 4, 'PLANE'),
(12, 8, 'MOTORCYCLE'), (13, 8, 'BYCYCLE');
Обычно иерархия схематизируется автоссылкой, которая
имеет место и здесь: внешний ключ ссылается на первичный
ключ той же таблицы. Имеющиеся данные можно трактовать
следующим образом:
ALL
|--SEA
| |--SUBMARINE
| |--BOAT
121
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
|--EARTH
| |--CAR
| |--TWO WHEELES
| | |--MOTORCYCLE
| | |--BYCYCLE
| |--TRUCK
|--AIR
|--ROCKET
|--PLANE
Построим запрос. Необходимо узнать, откуда пришел МОТОЦИКЛ (MOTORCYCLE). Другими словами, требуется найти
всех предков "МОТОЦИКЛА". Начать следует со строки данных,
которая содержат motorcycle:
SELECT NAME, ID_FATHER
FROM Tree
WHERE NAME = 'MOTORCYCLE';
Результат
NAME
ID FATHER
MOTORCYCLE 8
Мы должны иметь родительский ID, чтобы перейти к следующему шагу. Второй запрос, который делает этот следующий
шаг, должен быть написан подобно следующему:
SELECT NAME, ID_FATHER
FROM Tree;
Запросы отличаются только тем, что мы не задаем фильтр
WHERE для перехода к следующему шагу. Затем мы должны
объединить эти два запроса с помощью UNION ALL, что определит пошаговый метод:
SELECT NAME, ID_FATHER
FROM Tree
WHERE NAME = 'MOTORCYCLE'
UNION ALL
SELECT NAME, ID_FATHER
FROM Tree;
Теперь разместим все это в CTE:
WITH
tree_CTE (data, id)
AS (SELECT NAME, ID_FATHER
FROM Tree
WHERE NAME = 'MOTORCYCLE'
UNION ALL
SELECT NAME, ID_FATHER
FROM Tree
);
122
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Теперь мы вплотную подошли к рекурсии. Последний шаг
должен сделать цикл, чтобы организовать выполнение пошагового метода. Это делается при использовании имени CTE в качестве
таблицы внутри SQL-запроса CTE. В нашем случае мы должны
соединить второй запрос CTE с самим CTE, организовав цепочку
по tree_CTE.id = (второй запрос).ID. Это можно сделать следующим образом:
WITH
Результат
tree_CTE (data, id)
data
AS (SELECT NAME, ID_FATHER
MOTORCYCLE
TWO WHEELES
FROM Tree
EARTH
WHERE NAME = 'MOTORCYCLE'
ALL
UNION ALL
SELECT NAME, ID_FATHER
FROM Tree JOIN tree_CTE ON tree_CTE.id = Tree.ID
)
SELECT * FROM tree_CTE;
id
8
3
1
NULL
Что остановило рекурсивный процесс? Факт, что больше нет
звеньев цепочки, когда достигается значение id "NULL", что в
нашем примере означает случай достижения "ALL".
Отметим, что по неясным причинам MS SQL Server 2005 не
допускает ключевого слова RECURSIVE после слова WITH, которое вводит CTE.
Еще одна важная вещь, связанная со структурированными в
форме деревьев данными, – это визуализация данных в виде дерева ..., что означает наличие отступов в иерархии при извлечении данных. Возможно ли это? Да, конечно. Это может быть сделано вычислением пути внутри рекурсии и уровня вложенности. Вот пример такого запроса:
WITH tree_CTE (data, id, level, pathstr)
AS (SELECT NAME, ID, 0, cast(' ' as varchar(MAX))
FROM Tree
WHERE ID_FATHER IS NULL
UNION ALL
SELECT NAME, Tree.ID,
tree_CTE.level+4, tree_CTE .pathstr + ' ' +Tree.NAME
FROM Tree JOIN tree_CTE ON tree_CTE.id = Tree. ID_FATHER
)
SELECT SPACE(level) + data as data, id, level, pathstr
FROM
tree_CTE;
123
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Результат
data
ALL
SEA
EARTH
AIR
ROCKET
PLANE
CAR
TWO WHEELES
TRUCK
MOTORCYCLE
BYCYCLE
SUBMARINE
BOAT
id
1
2
3
4
10
11
7
8
9
12
13
5
6
level pathstr
0
4
SEA
4
EARTH
4
AIR
8
AIR ROCKET
8
AIR PLANE
8
EARTH CAR
8
EARTH TWO WHEELES
8
EARTH TRUCK
12
EARTH TWO WHEELES MOTORCYCLE
12
EARTH TWO WHEELES BYCYCLE
8
SEA SUBMARINE
8
SEA BOAT
Мы использовали новый тип данных в SQL 2005, который
называется VARCHAR (MAX), поскольку мы не знаем максимального количества символов, которое потребуется при конкатенации VARCHAR (16) в рекурсивном запросе, который может
оказаться очень глубоким.
10.3. Деревья без рекурсии В программировании рекурсию всегда можно избежать, если
использовать стек. В нашем случае так же можно обойтись без
рекурсии, поместив стек внутрь таблицы. Для этого добавим в
таблицу два новых столбца.
ALTER TABLE Tree
ADD RIGHT_BOUND INTEGER
ALTER TABLE Tree
ADD LEFT_BOUND INTEGER
Заполним эти новые столбцы следующими числами:
UPDATE Tree SET LEFT_BOUND = 1 , RIGHT_BOUND = 26 WHERE ID = 1
UPDATE Tree SET LEFT_BOUND = 2 , RIGHT_BOUND = 7 WHERE ID = 2
UPDATE Tree SET LEFT_BOUND = 8 , RIGHT_BOUND = 19 WHERE ID = 3
UPDATE Tree SET LEFT_BOUND = 20, RIGHT_BOUND = 25 WHERE ID =
4
UPDATE Tree SET LEFT_BOUND = 3 , RIGHT_BOUND = 4 WHERE ID = 5
UPDATE Tree SET LEFT_BOUND = 5 , RIGHT_BOUND = 6 WHERE ID = 6
UPDATE Tree SET LEFT_BOUND = 9 , RIGHT_BOUND = 10 WHERE ID = 7
UPDATE Tree SET LEFT_BOUND = 11, RIGHT_BOUND = 16 WHERE ID = 8
124
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
UPDATE Tree SET LEFT_BOUND = 17, RIGHT_BOUND = 18 WHERE ID = 9
UPDATE Tree SET LEFT_BOUND = 21, RIGHT_BOUND = 22
WHERE ID = 10
UPDATE Tree SET LEFT_BOUND = 23, RIGHT_BOUND = 24
WHERE ID = 11
UPDATE Tree SET LEFT_BOUND = 12, RIGHT_BOUND = 13
WHERE ID = 12
UPDATE Tree SET LEFT_BOUND = 14, RIGHT_BOUND = 15
WHERE ID = 13
Фактически мы реализовали стек, нумеруя строки данных.
Вот поясняющая картинка:
SUBMARINE
SEA
BOAT
CAR
MOTORCYCLE
ALL
EARTH
TWO WHEELES
BYCYCLE
TRUCK
ROCKET
AIR
PLANE
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Теперь, чтобы получить всех предков МОТОЦИКЛА, мы
только берем границы МОТОЦИКЛА (MOTORCYCLE) – слева 12,
а справа 13 – и помещаем их в предложение WHERE, выбирая данные, правая граница которых превышает 12, а левая меньше 13.
И вот запрос, дающий тот же самый результат, что и сложный иерархический рекурсивный запрос:
125
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
SELECT *
FROM Tree
WHERE RIGHT_BOUND > 12
AND LEFT_BOUND < 13;
Результат
ID
1
3
8
12
ID_FATHER
NULL
1
3
8
NAME
ALL
EARTH
TWO WHEELES
MOTORCYCLE
RIGHT_BOUND
26
19
16
13
LEFT_BOUND
1
8
11
12
Такое представление деревьев хорошо известно в литературе
по БД, особенно в трудах Джо Селко ("Деревья и иерархии" и т. д.).
10.4.Пример использования СТЕ для решения задачи Коммивояжера Проблема состоит в том, чтобы проехать на машине от Парижа до Тулузы, используя сеть автострад.
126
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Создадим таблицу и
занесем данные:
FROM_TOWN
PARIS
CREATE TABLE TUR
(FROM_TOWN
VARCHAR(32),
TO_TOWN VARCHAR(32),
MILES INTEGER);
PARIS
Возьмем в качестве
начала рекурсии «Париж»
и построим СТЕ.
PARIS
CLERMONTFERRAND
CLERMONTFERRAND
LYON
LYON
MONTPELLIER
MARSEILLE
TO_TOWN
NANTES
CLERMONTFERRAND
LYON
MILES
385
420
470
MONTPELLIER 335
TOULOUSE
375
MONTPELLIER
MARSEILLE
TOULOUSE
NICE
305
320
240
205
WITH
TUR_PARIS ([TO-TOWN])
AS
(
SELECT DISTINCT FROM_TOWN
FROM TUR
WHER FROM_TOWN= 'PARIS'
UNION ALL
SELECT TO_TOWN
FROM TUR INNER JOIN TUR_PARIS
ON TUR_PARIS.[TO-TOWN] =
TUR.FROM_TOWN
)
SELECT * FROM TUR_PARIS;
Результат
TO-TOWN
PARIS
NANTES
CLERMONT-FERRAND
LYON
MONTPELLIER
MARSEILLE
NICE
TOULOUSE
MONTPELLIER
TOULOUSE
TOULOUSE
Как видно из результата запроса, существует три способа добраться до Тулузы. Отфильтруем пункт назначения.
WITH
TUR_PARIS ([TO-TOWN])
AS
(
SELECT DISTINCT FROM_TOWN
FROM TUR
WHER FROM_TOWN= 'PARIS'
UNION ALL
SELECT TO_TOWN
FROM TUR INNER JOIN TUR_PARIS
ON TUR_PARIS.[TO-TOWN] = TUR.FROM_TOWN
)
SELECT * FROM TUR_PARIS
WHERE [TO-TOWN] = 'TOULOUSE' ;
127
Результат
TO-TOWN
TOULOUSE
TOULOUSE
TOULOUSE
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Мы можем уточнить этот запрос, подсчитав число шагов по
каждому направлению, расстояния по различным направлениям и
выведя списки городов, которые можно посетить, двигаясь по
этим направлениям:
WITH
TUR_PARIS([TO-TOWN], STEPS, DISTANSE, WAY)
AS
(
SELECT DISTINCT FROM_TOWN, 0, 0
,cast('PARIS' as VarChar(MAX))
FROM TUR
WHERE FROM_TOWN= 'PARIS'
UNION ALL
SELECT TO_TOWN, T_P.STEPS+1,
T_P. DISTANSE + T.MILES, T_P.WAY+ ' '+T.TO_TOWN
FROM TUR T INNER JOIN TUR_PARIS T_P
ON T_P.[TO-TOWN] = T.FROM_TOWN
)
SELECT * FROM TUR_PARIS
WHERE [TO-TOWN] = 'TOULOUSE';
Результат
TO-TOWN
STEPS DISTANSE WAY
TOULOUSE
3
1015
PARIS LYON MONTPELLIER TOULOUSE
TOULOUSE
2
795
PARIS CLERMONT-FERRAND TOULOUSE
PARIS CLERMONT-FERRAND MONTPELTOULOUSE
3
995
LIER TOULOUSE
Теперь мы сможем написать рекурсивный запрос решения
очень сложной задачи, названной задачей коммивояжера (одна из
действительных проблем исследования, на которых Edsger Wybe
Dijkstra нашел первый эффективный алгоритм и получил премию
Turing Award в 1972 году):
WITH
TUR_PARIS([TO-TOWN], STEPS, DISTANSE, WAY)
AS
(
SELECT DISTINCT FROM_TOWN, 0, 0
,cast('PARIS' as VarChar(MAX))
FROM TUR
WHERE FROM_TOWN= 'PARIS'
UNION ALL
128
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
SELECT TO_TOWN, T_P.STEPS+1,
T_P. DISTANSE + T.MILES, T_P.WAY+ ' '+T.TO_TOWN
FROM TUR T INNER JOIN TUR_PARIS T_P
ON T_P.[TO-TOWN] = T.FROM_TOWN
)
SELECT TOP 1 * FROM TUR_PARIS
WHERE [TO-TOWN] = 'TOULOUSE'
ORDER BY DISTANSE;
Результат
TO-TOWN
STEPS DISTANSE WAY
TOULOUSE 2
795
PARIS CLERMONT-FERRAND TOULOUSE
Следует заметить, что TOP n – нестандартная для SQL конструкция. Перепишем запрос без её использования:
WITH
TUR_PARIS([TO-TOWN], STEPS, DISTANSE, WAY)
AS
(
SELECT DISTINCT FROM_TOWN, 0, 0
,cast('PARIS' as VarChar(MAX))
FROM TUR
WHERE FROM_TOWN= 'PARIS'
UNION ALL
SELECT TO_TOWN, T_P.STEPS+1,
T_P. DISTANSE + T.MILES, T_P.WAY+ ' '+T.TO_TOWN
FROM TUR T INNER JOIN TUR_PARIS T_P
ON T_P.[TO-TOWN] = T.FROM_TOWN
),
MIN_DISTANSE( DISTANSE)
AS
(SELECT MIN( DISTANSE)
FROM TUR_PARIS
WHERE [TO-TOWN] = 'TOULOUSE'
)
SELECT TUR_PARIS.*
FROM
TUR_PARIS
JOIN
MIN_DISTANSE
MIN_DISTANSE.DISTANSE=TUR_PARIS.DISTANSE
WHERE [TO-TOWN] = 'TOULOUSE';
ON
В приведенной обработке сети имеется только одно ограничение – мы построили маршруты в одном направлении. Мы можем проехать из Парижа до Лиона, но нам не позволено совершить поездку из Лиона до Парижа. Для этого мы должны добавить обратные пути в таблицу.
129
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Это может быть сделано с помощью очень простого запроса:
INSERT INTO TUR
SELECT TO_TOWN, FROM_TOWN, MILES
FROM TUR;
Перепишем запрос, добавив условие: не проезжать через город, который уже встречался нам на ПУТИ (чтобы избежать зацикливания).
WITH
TUR_PARIS([TO-TOWN], STEPS, DISTANSE, WAY)
AS
(
SELECT DISTINCT FROM_TOWN, 0, 0
,cast('PARIS' as VarChar(MAX))
FROM TUR
WHERE FROM_TOWN= 'PARIS'
UNION ALL
SELECT TO_TOWN, T_P.STEPS+1,
T_P. DISTANSE + T.MILES, T_P.WAY+ ' '+T.TO_TOWN
FROM TUR T INNER JOIN TUR_PARIS T_P
ON T_P.[TO-TOWN] = T.FROM_TOWN
WHERE T_P.WAY NOT LIKE '%' + T.TO_TOWN + '%'
)
SELECT TUR_PARIS.*
FROM TUR_PARIS
WHERE [TO-TOWN] = 'TOULOUSE';
Результат
TO-TOWN
STEPS DISTANSE WAY
TOULOUSE 3
1015
PARIS LYON MONTPELLIER TOULOUSE
PARIS LYON MONTPELLIER CLERMONTTOULOUSE 4
1485
FERRAND TOULOUSE
TOULOUSE 2
795
PARIS CLERMONT-FERRAND TOULOUSE
PARIS CLERMONT-FERRAND MONTPELTOULOUSE 3
995
LIER TOULOUSE
Как видно, возник новый маршрут, самый длинный по расстоянию, но, возможно, самый красивый!
Контрольные вопросы 1. В каких случаях можно использовать обобщенные табличные выражения?
2. Как написать рекурсивный запрос?
3. Приведите пример схемы таблицы для хранения иерархических
данных?
4. В чем отличия CTE и VIEW?
130
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Приложение
Реализация реляционной алгебры средствами оператора SELECT (Реляционная полнота SQL) Для того чтобы показать, что язык SQL является реляционно полным,
нужно показать, что любой реляционный оператор может быть выражен
средствами SQL. На самом деле достаточно показать, что средствами SQL
можно выразить любой из примитивных реляционных операторов.
Оператор декартового произведения
Реляционная алгебра: (R  S),
Оператор SQL:
SELECT R.*, S.*
FROM R, S;
или
SELECT R.*, S.*
FROM R CROSS JOIN S;
Оператор выборки
Реляционная алгебра: (
Оператор SQL:
SELECT * FROM
R
;
WHERE
Оператор вычитания
Реляционная алгебра: (R  S) ,
Оператор SQL:
SELECT * FROM R
EXCEPT
SELECT * FROM S;
,
Оператор проекции
Реляционная алгебра: ( [X, Y,…, Z](R)),
Оператор SQL:
SELECT DISTINCT X, Y, …, Z
FROM R;
Оператор объединения
Реляционная алгебра: (R  S) ,
Оператор SQL:
SELECT * FROM R
UNION
SELECT * FROM S;
Некоторые СУБД не поддерживают данный синтаксис, поэтому оператор вычитания может быть реализован с помощью предиката NOT IN.
Пусть отношение R имеет первичный ключ PK1, а отношение S – PK2, тогда отрицание представляется:
SELECT * FROM R
WHERE PK1 NOT IN (SELECT PK2 FROM S);
131
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Оператор пересечения
Реляционная алгебра: (R  S)
Оператор SQL:
SELECT * FROM R
INTERSECT
SELECT * FROM S;
Некоторые СУБД не поддерживают данный синтаксис, поэтому оператор вычитания может быть реализован с помощью предиката IN. Пусть
отношение R имеет первичный ключ PK1, а отношение S – PK2, тогда отрицание представляется:
SELECT * FROM R
WHERE PK1 IN (SELECT PK2 FROM S)
Оператор соединения
Реляционная алгебра:
X Y RS
Оператор SQL:
SELECT *
FROM
R, S
WHERE
;
или
SELECT * FROM R JOIN S ON ;
Оператор деления
Реляционная алгебра: (R/S), где R (Х,Y) , S(Y)
Оператор SQL:
SELECT DISTINCT R.X
FROM
R
WHERE NOT EXISTS (SELECT * FROM S
WHERE NOT EXISTS (SELECT * FROM R R1
WHERE R1.X = R.X and R1.Y = S.Y)
);
Замечание. Оператор SQL, реализующий деление отношений, трудно
запомнить, поэтому дадим пример эквивалентного преобразования выражений, представляющих суть запроса.
Пусть отношение R содержит данные о продуктах, необходимых для
приготовления блюда, отношение S содержит список некоторых продуктов, которые есть в наличии. Атрибут X является номером блюда, атрибут
Y является номером продукта.
Разделить отношение R на отношение S означает в данном примере
"отобрать номера блюд, которые используют все продукты".
Преобразуем текст выражения:
"Отобрать номера блюд, которые используют все продукты" эквивалентно
132
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
"Отобрать те номера блюд из таблицы R, для которых не существует
отсутствующих продуктов в таблице S" эквивалентно
"Отобрать те номера блюд из таблицы R, для которых не существует
тех номеров продуктов из таблицы S, которые не используются этим блюдом" эквивалентно
"Отобрать те номера блюд из таблицы R, для которых не существует
тех номеров продуктов из таблицы S, для которых не существует записей
о блюдах в таблице R для этого продукта и этого блюда".
Последнее выражение дословно переводится на язык SQL. При переводе выражения на язык SQL нужно учесть, что во внутреннем подзапросе
таблица R должна быть переименована, для того чтобы отличать ее от экземпляра этой же таблицы, используемой во внешнем запросе.
Реляционный оператор переименования выражается при помощи
ключевого слова AS в списке отбираемых полей оператора SELECT.
Таким образом, язык SQL является реляционно полным.
133
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Рекомендуемая литература 1. Вьейра, Р. SQL Server 2000. Программирование / Р. Вьейра. –
Ч. 1. – М.: Бином. Лаборатория знаний, 2004. – 735 с.
2. Вьейра, Р. SQL Server 2000. Программирование / Р. Вьейра. –
Ч. 2. – М.: Бином. Лаборатория знаний, 2004. – 808 с.
3. Грабер, М. SQL: Справочное руководство / М. Грабер. – М.:
Лори, 2006. – 291 с.
4. Гроф, Дж. Р. SQL: полное руководство / Дж. Р. Гроф,
П. Н. Вайнберг. – К.: Издательская группа BHV, 1999. – 800 с.
5. Дейт, К. Дж. Введение в системы баз данных / К. Дж. Дейт. –
Киев; М.; СПб.: Вильямс, 2000. – 848 с.
6. Дибетт, П. Знакомство с Microsoft SQL Server 2005
/ П. Дибетт. – М.: Русская Редакция, 2005. – 288 с.
7. Кузнецов, С. Д. SQL. Язык реляционных баз данных
/ С. Д. Кузнецов. – М.: Майор, 2001. – 192 с.
8. Полякова, Л. Основы SQL / Л. Полякова. –М.: Интернет-университет информационных технологий – ИНТУИТ.ру, 2004. – 368 с.
9. Селко, Д. SQL для профессионалов / Д. Селко. – М.: Лори,
2004. – 464 с.
10. Тоу, Д. Настройка SQL / Д. Тоу. – СПб.: Питер, 2004. – 336 с.
Ресурсы Интернет 1. Библиотека MSDN // URL: http://msdn.microsoft.com/ru-ru/library
2. Форум по SQL // URL: http://www.sql.ru/
3. Практическое владение языком SQL // URL: http://www.sql-ex.ru/
4. Интерактивный учебник по SQL // URL: http://www.sqltutorial.ru/
5. Кузнецов, С. Д. Системы обработки информации – язык баз
данных SQL со средствами поддержания целостности / С. Д. Кузнецов // URL: http://www.citforum.ru/database/sql/index.shtml
6. Кузнецов, С. Д. Язык реляционных баз данных SQL и его стандарты / С. Д. Кузнецов // URL: ComputerWorld #4/97 http://www.citforum.ru/database/ articles/art_18.shtml
7. Кузнецов, С. Д. Введение в стандарты языка баз данных SQL
/ С. Д. Кузнецов // URL: http://www.citforum.ru/database/sqlbook/
index.shtml
134
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Содержание Введение .............................................................................................................. 3 Глава 1. Историческая справка ..................................................................... 7 1.1. Стандарты ......................................................................................... 7 1.2. Структура SQL ............................................................................... 12 Глава 2. Пример БД «Ресторан»................................................................... 16 2.1. Описание ......................................................................................... 16 2.2. Диаграмма ....................................................................................... 17 2.3. Данные в таблицах ......................................................................... 18 Глава 3. Выборка данных .............................................................................. 21 3.1. Оператор SELECT. Синтаксис ..................................................... 21 3.2. Примеры запросов с использованием единственной таблицы 24 3.3. Примеры запросов с использованием нескольких таблиц ........ 41 3.4. Соединения нескольких таблиц, используя JOIN ...................... 46 Глава 4. Подзапросы и производные таблицы ......................................... 57 4.1. Производная таблица ..................................................................... 57 4.2. Вложенные подзапросы................................................................. 58 4.3. Соотнесенные подзапросы ............................................................ 64 4.4. Использование оператора EXISTS ............................................... 66 4.5. Использование операторов ANY и ALL..................................... 67 4.6. Объединение запросов UNION..................................................... 70 Глава 5. Функции ранжирования ................................................................ 76 5.1. Функция ROW_NUMBER ............................................................. 76 5.2. Функции RANK() и DENSE_RANK() .......................................... 78 Глава 6. Использование PIVOT и UNPIVOT............................................. 82 Глава 7. Операторы манипулирования данными .................................... 87 7.1. Оператор INSERT для добавления одной записи ....................... 87 7.2. Оператор INSERT для добавления набора записей ................... 88 7.3. Оператор DELETE ......................................................................... 89 7.4. Оператор UPDATE......................................................................... 90 Глава 8. Представление ................................................................................. 92 Глава 9. Создание, изменение и удаление таблиц ................................... 98 9.1. Оператор CREATE TABLE ........................................................... 98 9.2. Изменение таблицы после того, как она была создана (ALTER
TABLE) ........................................................................................ 109 9.3. Удаление таблицы (DROP TABLE) ........................................... 111 9.4. Операторы создания и удаления индексов................................ 111 9.5. Понятие домена ............................................................................ 113 135
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Глава 10. Обобщенные табличные выражения (СТЕ) ......................... 115
10.1. Представления, производные таблицы
и выражения CTE ........................................................................ 115 10.2. Рекурсивные запросы ................................................................ 119 10.3. Деревья без рекурсии ................................................................. 124 10.4.Пример использования СТЕ для решения задачи
Коммивояжера ............................................................................. 126 Приложение. Реализация реляционной алгебры
средствами оператора SELECT
(Реляционная полнота SQL) ......................................................... 131 Рекомендуемая литература ......................................................................... 134 Учебное издание
Власова Ольга Владимировна
S Q L
Учебное пособие
Редактор, корректор М. В. Никулина
Верстка И. Н. Иванова
Подписано в печать 23.06.2011. Формат 6084 1/16.
Бум. офсетная. Гарнитура "Times New Roman".
Усл. печ. л. 7,90. Уч.-изд. л. 5,13.
Тираж 70 экз. Заказ
Оригинал-макет подготовлен
в редакционно-издательском отделе Ярославского
государственного университета им. П. Г. Демидова.
Отпечатано на ризографе.
Ярославский государственный университет им. П. Г. Демидова.
150000, Ярославль, ул. Советская, 14.
136
Документ
Категория
Без категории
Просмотров
48
Размер файла
1 040 Кб
Теги
sql, 1106, власов
1/--страниц
Пожаловаться на содержимое документа