Волк В.К. Базы данных. Практикум по проектированию, программированию и администрированию баз данных, включающий примеры и практические задания для самостоятельного выполнения
 Скачать 3.17 Mb.
|
|
Раздел SELECT представляет реляционно-алгебраическую операцию про- екции отношения, указанного в разделе FROM, на список атрибутов, указанных в разделе SELECT. В этом разделе допускается использование вычисляемых по- лей — атрибутов, отсутствующих в базовых таблицах и вычисляемых в процес- се выполнения запроса. Имена вычисляемых атрибутов указываются после ключевого слова AS, при наличии пробелов в имени оно должно заключаться в прямые скобки. Следует заметить, что SQL-модель данных отличается от классической реляционной модели тем, что допускает наличие повторяющихся кортежей в результирующих отношениях. Если, например, в списке атрибутов раздела 14 / 19 91 SELECT отсутствуют возможные ключи, не исключена вероятность появления в результирующем отношении кортежей-дубликатов, и это не будет нарушени- ем требований SQL-модели данных. Для исключения дублирующих кортежей в разделе SELECT следует указать параметр DISTINCT, как это сделано в двух по- следних примерах. Раздел WHERE реализует реляционно-алгебраическую операцию ограни- чения. Параметром этого раздела является так называемое условие ограниче- ния — любое корректное логическое выражение, которое будет вычисляться для каждого кортежа отношения, указанного в разделе FROM: в результирую- щем отношении останутся только те кортежи, для которых это выражение при- мет значение «истина». В качестве операндов логических выражений могут ис- пользоваться константы и имена любых атрибутов отношений, указанных в разделе FROM, а также составленные из имен атрибутов логические выражения, использующие операторы AND, OR и NOT. В логических выражениях могут использоваться стандартные предикаты сравнения (=, <, <=, >, >=, <>), предикаты between, IS NULL, LIKE (для сравнения строковых данных), а также предикаты IN, ALL и EXIST, которые будут рассмот- рены позднее при обсуждении примеров использования подчиненных (вложен- ных) запросов. При формировании логических выражений условий ограничения следует учитывать еще одну специфическую особенность SQL-модели, допускающей неопределенные (NULL) значения атрибутов в кортежах отношений. В этих условиях вычисление условия ограничения производится не в булевой, а в трехзначной (тернарной) логике со значениями true, false и unknown в соответ- ствии с таблицей истинности (табл. 3.1). Таблица 3.1 Таблица истинности в трехзначной логике true OR unknown = true true AND unknown = unknown unknown OR unknown = unknown unknown AND unknown = unknown false OR unknown = unknown false AND unknown = false NOT unknown = unknown Для сравнения данных дата-временных типов допускается использовать стандартный набор скалярных предикатов сравнения (=, <, <=, >, >=, <>) и пре- диката between, так как внутренним представлением данных этого типа являет- ся число: для даты — количество дней, прошедших с некоторой начальной да- ты до указанной даты, для времени — количество временных единиц (напри- мер, сотых долей секунды), прошедших с начала суток до заданного времени. По этой же причине допускается применять весь набор арифметических операций к данным дата-временных типов: например, можно вычислить новую 15 / 19 92 дату путем сложения даты с числом или вычислить длину временного интерва- ла путем вычитания двух дат. Дата-временные константы помещаются внутри пары символов #, стро- ковые константы заключаются в кавычки (одинарные или двойные). Для обработки строковых данных может быть использован стандартный набор встроенных функций, обеспечивающих слияние и расщепление строк, выделение подстрок в строках, вычисление длины строки и т. д. 6.3.2. SQL-запросы с соединением (JOIN) таблиц Листинг 3.4 представляет более сложные SQL-запросы, в которых выбор- ка производится из виртуальных таблиц, получаемых в результате соединения нескольких реальных таблиц базы данных путем применения к ним реляцион- но-алгебраической операции JOIN в ее различных модификациях. а) SELECT Категория,Товар,Поставщик, ОптоваяЦена, Количество,ЕдиницаИзмерения, ОптоваяЦена*Количество AS [Стоимость складского запаса] FROM (Поставщики INNER JOIN (Категории INNER JOIN Склад ON Категории.Код_Категории = Склад.Код_Категории) ON Поставщики.Код_Поставщика = Склад.Код_Поставщика) INNER JOIN ЕдиницыИзмерения ON ЕдиницыИзмерения.Код_Ед = Склад.Код_ЕдИзм WHERE Количество>0 ORDER BY Категория ASC, ОптоваяЦена*Количество DESC; б) SELECT Города.Город, Страны.Страна, Регионы.Регион FROM Регионы, Страны, Города; в) SELECT Города.Город, Страны.Страна, Регионы.Регион FROM Регионы, Страны, Города WHERE Города.Код_Страны=Страны.Код_Страны AND Страны.Код_Региона=Регионы.Код_Региона; г) SELECT Города.Город, Страны.Страна FROM Регионы INNER JOIN (Страны INNER JOIN Города ON Страны.Код_Страны = Города.Код_Страны) ON Регионы.Код_Региона = Страны.Код_Региона; д) SELECT Города.Город, Страны.Страна FROM Страны LEFT JOIN Города ON Страны.Код_Страны = Города.Код_Страны; е) SELECT Города.Город, Страны.Страна FROM Страны RIGHT JOIN Города ON Страны.КодСтраны = Города.КодСтраны; Листинг 3.4 Примеры SQL-запросов с соединением таблиц Комментарии к примерам, приведенным в листинге 3.4: а) раздел FROM реализует реляционно-алгебраическую операцию внут- реннего соединения (INNER JOIN) четырех взаимосвязанных таблиц, в качестве 16 / 19 93 условий соединения которых используются равенства значений первичных ключей главных таблиц и внешних ключей соответствующих подчиненных таблиц: – операция внутреннего соединения производит конкатенацию (сцепле- ние) только тех кортежей таблиц, в которых обнаруживается такое равенство; – результат выборки представляется в отсортированном виде: кортежи упорядочены по двум критериям — по категории товара (в алфавитном порядке по возрастанию) и по суммарной стоимости складского запаса товара (в поряд- ке убывания); б) в разделе FROM явно не указан тип соединения таблиц, их имена про- сто разделены запятой — синтаксически это обозначает выполнение реляцион- но-алгебраической операции расширенного декартова произведения, произво- дящей виртуальную таблицу, в которой каждая строка таблицы Страны сцепле- на со всеми строками таблицы Города: – пример иллюстрирует лишь синтаксические возможности языка, ре- зультат операции не имеет никакого смысла и явно противоречит не только се- мантике предметной области, но и естественным географическим представле- ниям о расположении регионов, стран и городов; – мощность результирующей таблицы будет равной произведению мощ- ностей всех трех базовых таблиц и составит 30 000 строк для примера из учеб- ной базы данных, в таблицах которой представлены 10 регионов, 30 стран и 100 городов; в) в этом примере сделана попытка устранения семантического недостат- ка предыдущего SQL-запроса: – вначале состав кортежей таблицы получен декартовым перемножением трех базовых таблиц; – затем состав кортежей ограничивается условием равенства первичных ключей главных таблиц и соответствующих внешних ключей подчиненных таблиц; – формально эта попытка вполне успешна, так как результат запроса дает правильный перечень из 100 городов с указанием регионов и стран, в которых действительно расположены эти города; – однако вряд ли следует считать правильным метод получения этого ре- зультата — вначале производится таблица мощностью 30 000 строк, а затем из нее удаляются лишние 29 900 строк; г) результат выполнения этого запроса настолько же правилен, как и предыдущего, однако здесь предлагается совсем другой способ его достижения: в разделе FROM явно указана операция внутреннего соединения трех таблиц, что предписывает транслятору подобрать эффективный процедурный план вы- полнения этой операции, использующий, например, индексные структуры дан- ных вместо полного перебора кортежей таблиц по методу вложенных циклов; д) в отличие от внутреннего соединения (INNER JOIN), левое соединение (LEFT JOIN) производит таблицу, содержащую все строки левой базовой табли- цы, в том числе и те, для которых в правой таблице отсутствуют соответству- 17 / 19 94 ющие строки. При этом недостающие поля результирующей таблицы получат неопределенные NULL-значения. В рассматриваемом примере в результирую- щей таблице будут представлены все страны, в том числе и те, в которых нет городов (разумеется, не в географическом смысле); е) операция RIGHT JOIN произведет таблицу, в которой будут представле- ны все города, в том числе и те, которые оказались не связанными ни с одной из стран. 6.3.3. SQL-запросы с объединением (UNION) таблиц В отличие от оператора JOIN, сцепляющего строки таблиц и производя- щего таблицу «суммарной» арности, оператор UNION выполняет операцию объ- единения таблиц, в результате которой формируется таблица «суммарной» мощности. Естественным ограничением на выполнение этой операции является тре- бование совместимости объединяемых таблиц, в основе которого лежат базо- вые ограничения реляционной модели данных, требующие, в частности, иден- тичности схем всех кортежей отношения и наличия среди атрибутов хотя бы одного уникального ключа. Как уже отмечалось, SQL-модель данных отличается от классической ре- ляционной модели и поддерживает только минимальное требование совмести- мости: все объединяемые оператором UNION таблицы должны иметь одина- ковую арность. Остальные ограничения в конкретных реализациях языка либо игнорируются (как, например, несовпадение типов данных в соответствующих столбцах объединяемых таблиц), либо принимаются решения «по умолчанию» (например, имена столбцов объединенной таблицы наследуются от имен столб- цов первой из объединяемых). Листинг 3.5 иллюстрирует использования оператора UNION для формиро- вания объединенной таблицы, включающей перечень всех контрагентов — как клиентов, так и поставщиков товаров. Дополнительный (вычисляемый) столбец таблицы содержит наименование статуса контрагента. SELECT Код_Клиента AS Код, Клиент AS Контрагент, Город, АдресГлавногоОфиса AS Адрес, Телефон, «Клиент» AS [Статус] FROM Города INNER JOIN Клиенты ON Города.Код_Города = Клиенты.КодГорода UNION SELECT Код_Поставщика, Поставщик, Город, Адрес,Телефон, «Поставщик» FROM Города INNER JOIN Поставщики ON Города.Код_Города = Поставщики.КодГорода; Листинг 3.5 Пример использования оператораUNION 18 / 19 95 6.3.4. Модифицирующие SQL-запросы Оператор INSERT вставляет строки в существующую таблицу, при этом структура и значения столбцов вставляемых строк должны соответствовать схеме таблицы. Оператор DELETE удаляет из таблицы строки, соответствующие условию ограничения WHERE. Оператор UPDATE изменяет значения указанных столбцов таблицы в строках, соответствующих ограничению WHERE. а) INSERT INTO Поставщики (Поставщик, Адрес, Телефон, КодГорода) VALUES («Horns and Hoofs, Limited», «666666», «+7 777777», 106); б) INSERT INTO Поставщики (Поставщик, Адрес, Телефон, КодГорода) SELECT Клиент,АдресГлавногоОфиса, Телефон, КодГорода FROM Клиенты WHERE Клиенты.КодГорода=20; в) SELECT Заказы.Код_Заказа, ДатаИсполнения, Товар, Заказано.Количество,Клиент, Сотрудник INTO Продажи_2018 FROM Сотрудники INNER JOIN (Клиенты INNER JOIN (Склад INNER JOIN (Заказы INNER JOIN Заказано ON Заказы.Код_Заказа = Заказано.Код_Заказа) ON Склад.КодТовара = Заказано.Код_Товара) ON Клиенты.Код_Клиента = Заказы.Код_Клиента) ON Сотрудники.Код_Сотрудника = Заказы.Код_Сотрудника WHERE Year(ДатаИсполнения)=2018; г) UPDATE Склад INNER JOIN (Заказы INNER JOIN Заказано ON Заказы.Код_Заказа = Заказано.Код_Заказа) ON Склад.КодТовара = Заказано.Код_Товара SET Склад.Количество = Склад.Количество — Заказано.Количество WHERE Заказы.ДатаИсполнения =Date(); д) DELETE * FROM Поставщики WHERE КодГорода=106; Листинг 3.6 Примеры использования операторов INSERT, DELETE и UPDATE Синтаксические правила использования этих операторов интуитивно по- нятны и иллюстрируются приведенными ниже примерами (листинг 3.6): а) в таблицу Поставщики вставляется одна строка, значения полей кото- рой заданы соответствующими константами; б) все клиенты, находящиеся в городе с кодом 20, становятся поставщи- ками (по-прежнему оставаясь клиентами); в) этот пример иллюстрирует еще один способ вставки строк в таблицу без использования оператора INSERT: инструкция SELECT … INTO создает в базе данных новую таблицу Продажи_2018, в которую помещается результат вы- борки информации о товарах, включенных в заказы 2018 г.; 19 / 19 96 г) во всех строках таблицы Склад, связанных со строками таблицы Зака- зано, которые, в свою очередь, связаны со строками таблицы Заказы, датиро- ванными «сегодняшним» числом, изменяется значение поля Количество путем его уменьшения на количество проданных товаров (по завершении торгового дня корректируется складской запас товаров с учетом объемов проданных то- варов); д) из таблицы Поставщики удаляются все строки, представляющие по- ставщиков из города № 106: – этот пример иллюстрирует простейшую ситуацию, когда условие вы- борки удаляемых из таблицы строк ссылается только на поля этой таблицы; – в более сложных случаях (например, для удаления поставщиков, по- ставки товаров которых прекращены) потребуются ссылки на другие таблицы, связанные с модифицируемой таблицей; – SQL дает несколько альтернативных способов решения такой задачи, один из них связан с использованием подчиненных запросов, рассматриваемых в п. 6.3.6. 6.3.5. Хранимые представления Представлением (view) называется именованный логический объект, представляющий собой SQL-запрос, хранимый в базе данных в виде исходного SQL-кода или в некотором прекомпилированном формате. При выполнении представления формируется виртуальная таблица, схема и состав кортежей ко- торой определены структурой оператора CREATE VIEW, а также текущим состоя- нием используемых в представлении базовых таблиц, в котором они находи- лись в момент выполнения представления. Ссылки на имена представлений и имена их полей могут использоваться в операторе SELECT точно так же, как для реальных таблиц базы данных, что де- лает хранимые представления полезным и эффективным инструментом при разработке пользовательских запросов. Листинг 3.7 содержит пример использования хранимых представлений. Операторами а) и б) создаются два хранимых представления: Представители_Клиентов (соединением пяти базовых таблиц: Клиент, Пред- ставитель, Город, Страна и Регион) и, аналогично, представление Представители_Клиентов. Результатом автономного выполнения каждого из этих представлений будет список представителей (соответственно клиентов или поставщиков), для каждого из которых будет указано имя представителя, город, страна и регион его нахождения, а также наименование его работодателя (клиента или постав- щика). Далее оператором в) производится выборка строк из виртуальной табли- цы, полученной путем соединения двух представлений (также виртуальных таблиц), в результате формируется список городов, в которых находятся и представители клиентов, и представители поставщиков, с указанием имен таких представителей. 1 / 19 97 а) CREATE VIEW Представители_Клиентов (Клиент, Представитель, Город, Страна, Регион) AS SELECT Представитель, Клиент, Город, Страна, Регион FROM (Регионы INNER JOIN (Страны INNER JOIN Города ON Страны.Код_Страны = Города.КодСтраны) ON Регионы.Код_Региона = Страны.КодРегиона) INNER JOIN (Клиенты INNER JOIN Представители ON Клиенты.Код_Клиента = Представители.Код_Клиента) ON Города.Код_Города = Представители.Код_Города; б) CREATE VIEW Представители_Поставщиков (Поставщик, Представитель, Город, Страна, Регион) AS SELECT Представитель, Поставщик, Город, Страна, Регион FROM (Регионы INNER JOIN (Страны INNER JOIN Города ON Страны.Код_Страны = Города.КодСтраны) ON Регионы.Код_Региона = Страны.КодРегиона) INNER JOIN (Поставщики INNER JOIN Представители ON Поставщики.Код_Поставщика = Представители.Код_Поставщика) ON Города.Код_Города = Представители.Код_Города; в) SELECT Представители_Клиентов.Представитель, Представители_Поставщиков.Представитель, Представители_Клиентов.Город FROM Представители_Клиентов INNER JOIN Представители_Поставщиков ON Представители_Клиентов.Город = Представители_Поставщиков.Город ORDER BY Представители_Клиентов.Город; Листинг 3.7 Пример использования представлений в SQL-запросах 6.3.6. Подчиненные SQL-запросы Подчиненный запрос или, более кратко, подзапрос — это SQL-запрос, вложенный в другой SQL-запрос и компилируемый совместно с основным за- просом. Синтаксически подзапросом будет считаться любой корректный опера- тор SELECT, заключенный в круглые скобки. Подзапросы могут находиться в лю- бых разделах основного запроса, допускающих использование выражений, в том числе они могут быть вложены в предикаты условий выборки раздела WHERE. Если подзапрос возвращает скалярное значение (унарную таблицу мощ- ностью в одну строку), его допускается использовать в качестве операнда в простых предикатах сравнения (=, <, <=, >, >=, <>), предикатах between или LIKE в зависимости от типа данных возвращаемого подзапросом значения. Такое ис- пользование подзапроса иллюстрирует листинг 3.8 — результирующий SQL-запрос 2 / 19 98 возвращает список имен торговых представителей, находящихся в том же горо- де, что и сотрудник по имени «Новиков». SELECT Представитель, Город FROM Города INNER JOIN Представители ON Города.Код_Города = Представители.Код_Города WHERE Город = ( SELECT Город FROM (Города INNER JOIN Филиалы ON Города.Код_Города = Филиалы.Код_Города) INNER JOIN Сотрудники ON Филиалы.Код_Филиала = Сотрудники.Код_Филиала WHERE Сотрудник Like «Новиков»); Листинг 3.8 Пример использования подчиненного запроса в простом предикате сравнения раздела WHERE Если подзапрос возвращает множество скалярных значений (унарную таблицу из множества строк), в предикатах условий выборки раздела WHERE основного запроса могут использоваться ключевые слова ALL, ANY|SOME, IN, EXISTS и NOT EXISTS. Предикат EXISTS (подзапрос) получит значение «истина», если подзапрос возвращает непустое множество строк. Предикат выражение IN (подзапрос) получит значение «истина», если вычисляемое значение выражения входит во множество значений, возвращае- мое подзапросом. а) SELECT Сотрудник FROM Сотрудники WHERE NOT EXISTS (SELECT Код_Заказа FROM Заказы WHERE Заказы.Код_Сотрудника = Сотрудники.Код_Сотрудника AND YEAR(Заказы.ДатаРазмещения)=2017); б) SELECT Сотрудник FROM Сотрудники WHERE Код_Сотрудника NOT IN (SELECT Код_Сотрудника FROM Заказы WHERE YEAR(Заказы.ДатаРазмещения)=2017); в) DELETE FROM Поставщики WHERE Код_Поставщика IN ( SELECT Код_Поставщика FROM Склад WHERE ПоставкиПрекращены=True); Листинг 3.9 Примеры использования подчиненных запросов в предикатах сравнения EXISTS и IN 3 / 19 99 Примеры а) и б) в листинге 3.9 представляют два синтаксически различных, но семантически эквивалентных SQL-запроса, производящих выборку из таблицы Сотрудники имен всех сотрудников, не оформивших ни одного заказа в 2017 г. Первый из двух этих примеров иллюстрирует еще одну важную особен- ность использования подчиненных запросов, связанную с известным в про- граммировании понятием «области видимости переменных». Автономное вы- полнение подзапроса из примера а) было бы невозможным, так как в этом слу- чае считался бы неопределенным параметр Сотрудники.Код_Сотрудника, од- нако в приведенном примере такая конструкция вполне корректна, так как в подзапросе доступны имена всех столбцов всех таблиц, используемые в разде- лах FROM всех внешних запросов более высоких уровней. Пример в) демонстрирует запрос на удаление поставщиков, прекратив- ших поставку хотя бы одного из своих товаров. а) SELECT Поставщик FROM Поставщики WHERE Код_Поставщика=ANY( SELECT Код_Поставщика FROM Склад WHERE Количество>0 AND ПоставкиПрекращены=True); б) SELECT Поставщик FROM Поставщики WHERE Код_Поставщика=ALL( SELECT Код_Поставщика FROM Склад WHERE Количество<=МинимальныйЗапас); в) SELECT Сотрудник FROM Сотрудники WHERE Код_Сотрудника=SOME( SELECT DISTINCT Код_Сотрудника FROM Заказы WHERE ДатаИсполнения > DateAdd(«m»,1,ДатаРазмещения)); г) DELETE FROM Поставщики WHERE Код_Поставщика = ALL ( SELECT Код_Поставщика FROM Склад WHERE ПоставкиПрекращены=True); Листинг 3.10 Примеры использования подчиненных запросов в предикатах сравнения ANY|SOME и ALL Предикат выражение ANY|SOME (подзапрос) получит значение «истина», если вычисляемое значение выражения совпадает хотя бы с одним значением из множества значений, возвращаемых подзапросом, а предикат выражение ALL (подзапрос) — если вычисляемое значение выражения совпадает со всеми возвращаемыми подзапросом значениями. Листинг 3.10 содержит примеры использования этих предикатов. Запрос а) формирует список поставщиков, прекративших поставку хотя бы одного из своих товаров, имеющихся на складе. 4 / 19 100 Запрос б) формирует список поставщиков, складские запасы всех товаров которых не превышают установленного минимального запаса. Запрос в) формирует список сотрудников, хотя бы один заказ которых исполнялся более одного месяца. Запрос г) удаляет поставщиков, прекративших поставку всех своих товаров. 6.3.7. SQL-средства статистической обработки данных Рассмотренные выше примеры использования DML-операторов демон- стрируют возможности построчной обработки табличных данных: соединение строк таблиц, выборка отдельных строк по заданным критериям и их последу- ющая обработка. Язык SQL также содержит средства статистической обработки данных, обеспечивающие возможности группировки строк таблиц по различным крите- риям, выборки групп строк (а не отдельных строк, как это делает раздел WHERE оператора SELECT) и вычисления статистических характеристик сформирован- ных групп строк по различным столбцам и их комбинациям. Для формирования групп строк используются разделы GROUP BY и HAVING оператора SELECT, а для вычисления статистических характеристик сформированных групп — агрегатные функции (set-functions). GROUP BY Раздел GROUP BY <список параметров группировки> производит группи- ровку строк таблицы, сформированной разделами SELECT, FROM и WHERE опе- ратора SELECT: в одну группу попадают те строки сформированной таблицы, для которых все значения параметров из заданного списка одинаковы. В качестве параметров группировки допускается использовать любые имена столбцов таблиц или представлений, присутствующих в разделе FROM, а также любые (нестатистические) выражения, составленные из констант и имен этих столбцов. При наличии в операторе SELECT раздела GROUP BY раздел SELECT этого оператора не может включать имен столбцов или выражений, отсутствующих в списке параметров группировки (за исключением агрегатных функций, фор- мирующих значения вычисляемых столбцов результирующей таблицы). Наличие раздела GROUP BY в операторе SELECT имеет смысл лишь в том случае, если группировка строк производится с целью последующей «фильтра- ции» групп разделом HAVING и/или вычисления статистических характеристик сформированных групп с помощью агрегатных функций. Мощность (количе- ство строк) результирующей таблицы, формируемой оператором SELECT с раз- делом GROUP BY, равна количеству сформированных групп. HAVING Раздел HAVING <условие выборки групп> имеет смысл только при нали- чии в операторе SELECT раздела GROUP BY: он вычисляет условие выборки для каждой группы и сохраняет в результирующей таблице только те группы, для которых это условие принимает значение «истина». 5 / 19 101 Условие выборки групп — это логическое выражение, операндами которо- го могут быть параметры группировки из списка параметров раздела GROUP BY и/или агрегатные функции, вычисляющие значения статистических характе- ристик групп, сформированных в разделе GROUP BY. Агрегатные функции Стандартом SQL-89 определен набор из пяти агрегатных функций, каж- дая из которых вычисляет значение определенной числовой характеристики для каждой группы строк, сформированных разделом GROUP BY: – COUNT(*|p) — вычисляет количество строк в группе; – MAX(p) — вычисляет максимальное значение параметра p; – MIN(p) — вычисляет минимальное значение параметра p; – SUM(p) — вычисляет суммарное значение параметра p; – AVG(p) — вычисляет среднее арифметическое значение параметра p. В качестве параметра p допускается использовать любое корректное вы- ражение числового типа, составленное из констант и имен столбцов таблиц, указанных в разделе FROM. Исключение составляет функция COUNT, допуска- ющая использование параметра любого типа, — при подсчете количества строк функция не будет учитывать строки, в которых этот параметр имеет неопреде- ленное значение (NULL). Функция COUNT допускает также использование сим- вола «*» в качестве своего параметра — в этом случае она будет учитывать все строки групп. При отсутствии раздела GROUP BY в операторе SELECT все строки табли- цы, сформированной этим оператором, будут считаться ее единственной груп- пой и агрегатная функция (при ее наличии) вычислит соответствующую харак- теристику для всей таблицы. Агрегатные функции могут использоваться в разделах SELECT и/или HAVING оператора SELECT, а также в подчиненных запросах, как это показано в примерах листинга 3.11. Два семантически эквивалентных, но синтаксически различных запроса а) и б)рассчитывают количество заказов, оформленных каждым из сотрудников в 2017 г. В каждом из этих запросов сначала формируется исходная таблица пу- тем соединения таблиц Сотрудники и Заказы. В запросе а) из исходной таблицы производится выборка строк (WHERE), соответствующих заказам 2017 г., затем строки полученной выборки группи- руются по полю Сотрудник и для каждой из полученных групп функция COUNT(*) вычисляет количество строк. В запросе б) выборка строк не производится, а исходная таблица сразу группируется по двум параметрам — имени сотрудника и году исполнения за- каза, после чего производится выборка групп (HAVING), соответствующих зака- зам 2017 г., и вычисляется количество строк для каждой из этих групп. 6 / 19 102 а) SELECT Сотрудник, COUNT(*) AS [Количество заказов] FROM Сотрудники INNER JOIN Заказы ON Сотрудники.Код_Сотрудника = Заказы.Код_Сотрудника WHERE YEAR(ДатаИсполнения)=2017 GROUP BY Сотрудник; б) SELECT Сотрудник,YEAR(ДатаИсполнения), COUNT(*) AS [Количество заказов] FROM Сотрудники INNER JOIN Заказы ON Сотрудники.Код_Сотрудника = Заказы.Код_Сотрудника GROUP BY Сотрудник,YEAR(ДатаИсполнения) HAVING YEAR(ДатаИсполнения)=2017; в) SELECT Категория, SUM(ОптоваяЦена*Количество) AS [Стоимость складского запаса] FROM Категории AS К INNER JOIN Склад AS С ON К.Код_Категории = С.Код_Категории GROUP BY Категория HAVING AVG(Количество)>50; г) SELECT Город FROM Города WHERE (SELECT COUNT(*) FROM Клиенты WHERE Города.Код_Города=Клиенты.КодГорода) + (SELECT COUNT(*) FROM Поставщики WHERE Города.Код_Города=Поставщики.КодГорода) >3; Листинг 3.11 Примеры групповой обработки данных Запрос в) рассчитывает суммарную стоимость складского запаса товаров каждой из категорий, для которых среднее количество складского запаса пре- вышает 50 единиц. В этом примере используются две агрегатные функции: SUM() в разделе SELECT и AVG() в разделе HAVING — обе эти функции произво- дят обработку одних и тех же групп строк, сформированных в соответствии с условием выборки групп, заданным в разделе GROUP BY. В запросе г) формируется список городов, в которых суммарное количе- ство клиентов и поставщиков превышает 5. Агрегатная функция COUNT(*) ис- пользована в двух подчиненных запросах, не содержащих раздела GROUP BY и вложенных в раздел WHERE основного запроса. Каждый из подчиненных запро- сов вычисляется для каждой строки таблицы Города и возвращает скалярное значение — количество строк в таблице Клиенты (для первого подчиненного запроса) и количество строк в таблице Поставщики (для второго подчиненного запроса), соответствующих значению поля Код_Города в соответствующей строке таблицы Города. Заметим, что автономное выполнение каждого из этих подчиненных за- просов было бы невозможным, так как в условиях выборки строк этих запросов используется ссылка на столбец Код_Города таблицы Города, доступной в ос- новном запросе. 7 / 19 103 6.4. Стандарты и диалекты языка SQL 6.4.1. История стандартизации языка SQL Язык SQL (под названием SEQUEL — Structured English QUEry Language) был разработан корпорацией IBM в середине 1970-х гг. в рамках проекта экспериментальной реляционной СУБД System R [30]. Название («язык запросов») только частично отражает суть этого языка, который уже тогда являлся полноценным языком реляционных баз данных, со- держащим не только операторы формулирования запросов выборки и модифи- кации данных, но также и средства определения схемы БД и ограничений це- лостности, триггеров и хранимых представлений; поддержку структур физиче- ского уровня, обеспечивающих эффективное выполнение запросов, средств управления транзакциями и разграничения доступа пользователей к таблицам базы данных и отдельным их полям. К началу 1980-х гг. уже существовали различные коммерческие версии этого языка, существенно отличающиеся от языка SQL System R, так как пол- ная реализация всех идей System R была для того времени слишком сложной. В 1983 г. Международная организация по стандартизации (ISO) и Американ- ский национальный институт стандартов (ANSI) приступили к разработке стан- дарта языка SQL. Первый этап стандартизации языка SQL завершился к 1989 г., когда был принят международный стандартSQL89, в котором многие аспекты языка не были детально прописаны — их предполагалось определять в реализации. До- стижением SQL89 являлась стандартизация синтаксиса и семантики операторов выборки и манипулирования данными (SELECT, INSERT, DELETE, UPDATE) и средств ограничения целостности БД: первичного и внешних ключей (PRIMARY KEY и FOREIGN KEY), проверяемых (CHECK CONSTRAINTS) ограничений целост- ности. В 1992 г. был введен существенно более полный стандарт языка SQL, по- лучивший название SQL92 и охватывающий практически все необходимые для реализации аспекты: – манипулирование схемой БД; – полноценное управление изолированностью транзакций; – каскадное удаление и изменение данных в связанных отношениях; – динамический SQL и встроенный SQL для использования в семи раз- личных языках программирования; – использование временных таблиц; – использование подчиненных запросов в проверяемых ограничениях; – расширенный набор типов данных и средства преобразования типов. В последующих версиях были расширены возможности стандарта SQL92 и добавлены некоторые объектно-ориентированные возможности. SQL99: – использование UDT-типов (User-Defined Datatypes), определяемых пользователем с помощью SQL-оператора CREATE TYPE; 8 / 19 104 – использование определяемых пользователем нескалярных типов дан- ных (массивов из скалярных элементов допустимых SQL-типов) и объектных типов данных, в том числе с поддержкой множественного наследования; – работа с бинарными и символьными LOB-объектами (Large Object); – поддержка конструкторов типов данных и значений строк таблиц; – поддержка дополнительных возможностей ссылочной целостности, например использование подчиненных запросов в ограничениях целостности CHECK оператора CREATE TABLE; – поддержка рекурсивных запросов и средств описания сложных запро- сов, востребованных в системах аналитической обработки данных (OLAP), например использование в операторе SELECT операции INTERSECT для формиро- вания пересечения множеств, выданных несколькими SQL-запросами, и опера- ции FULL OUTER JOIN для создания полных внешних соединений таблиц, содер- жащих все строки из соединяемых таблиц, с NULL-значениями в несовпадаю- щих столбцах; – использование PSM-модулей (Persistent Stored — постоянно хранимые), поддерживающих процедурные расширения языка: переменные, операторы управления CASE, IF, WHILE, REPEAT, LOOP и FOR, процедуры и функции, созда- ваемые операторами CREATE PROCEDURE и CREATE FUNCTION; – вызов из SQL внешних программ, написанных на других языках про- граммирования; при этом внешняя программа может создаваться так же, как и внутренние объекты базы данных — SQL-операторами CREATE PROCEDURE или CREATE FUNCTION с обязательным указанием параметров EXTERNAL и LANGUAGE. SQL2003 В стандарте 2003 г. специфицирован ряд новых свойств языка: – обновлен состав используемых типов данных: • специфицирован новый тип данных, значениями которого являются XML-документы; для XML-типа определен набор операций, обеспечивающих доступ и преобразования значений типа XML; • расширены возможности использования нескалярных типов данных: во-первых, введен новый конструктор типов мультимножеств MULTISET; во-вторых, в качестве элементов любого нескалярного типа допуска- ется использование любого допустимого в SQL, в том числе и нескалярного, типа данных (кроме самого конструируемого нескалярного типа), тем самым полностью снято ограничение «плоских таблиц», исторически присущее реля- ционным (в том числе и SQL-ориентированным) базам данных; – введено понятие табличной функции (Table Value Function), возвраща- ющей значение типа мультимножества, элементы которого — строки таблицы; к результату, возвращаемому табличной функцией, можно адресовать SQL-запросы точно так же, как и к таблице или представлению, хранимым в ба- зе данных; 9 / 19 105 – появились три новых возможности определения автоматически запол- няемых столбцов таблиц: • использование объектов базы данных нового типа — генераторов по- следовательностей (sequence generators), производящих последовательности изменяемых во времени уникальных автоинкрементных числовых данных; для работы с генераторами последовательностей предусмотрены операторы CREATE SEQUENCE, ALTER SEQUENCE, DROP SEQUENCE и функция NEXT VALUE FOR <имя генератора>; • использование типа IDENTITY для определения столбцов с уникальны- ми автоинкрементными целочисленными значениями; • использование генерируемых столбцов (generated columns), значения которых при вставке строк таблицы формируются в результате вычисления за- данного выражения, в котором допустимо использование констант и ссылок на основные (не генерируемые) столбцы этой таблицы. В стандарте языка SQL2006/2008 значительно расширены средства работы с XML-данными, появилась возможность совместного использования в запросах SQL и XQuery, а также устранены неоднозначности стандарта SQL2003. SQL2011 Основным достижением стандарта 2011 г. является улучшение и развитие средств работы с временными (temporal)базами данных, в которых хранится информация, актуальная для определенных временных периодов. В битемпоральных базах данных могут быть определены два периода: период valid time (или application time), в течение которого некоторый факт действителен в реальном мире, и период transaction time (или system time), в те- чение которого был известен факт, хранящийся в базе данных. В многотемпоральных базах данных может быть определено более двух временных интервалов. SQL2011 содержит языковые средства определения и манипулирования временными интервалами: – для определения именованного временного интервала используются два стандартных столбца таблиц; – для application time-таблиц: • определение периода valid time (PERIOD FOR); • обновление и удаление строк с автоматическим расщеплением вре- менного интервала; • определение временных первичных ключей (temporal primary keys) с контролем перекрытия интервалов (WITHOUT OVERLAPS); • определения временных ограничений ссылочной целостности (temporal referential integrity); • использование новых предикатов для обработки временных интерва- лов: CONTAINS, OVERLAPS, EQUALS, PRECEDES, SUCCEEDS, IMMEDIATELY PRECEDES и IMMEDIATELY SUCCEEDS; 10 / 19 106 – для system time-таблиц: • определение таблиц с использованием PERIOD FOR SYSTEM_TIME и модификатора WITH SYSTEM VERSIONING; • автоматическое сохранение интервалов system time; • использование языковых конструкций AS OF SYSTEM TIME и VERSIONS BETWEEN SYSTEM TIME ... AND ... для упорядоченных во времени (time-sliced и sequenced) запросов. 6.4.2. Диалекты языка SQL Язык SQL в его исходном виде являлся информационно-логическим языком декларативного типа, однако более поздние версии этого языка предусматривают возможность его процедурных и частично объектно-ориентированных расшире- ний, что делает современный SQL полноценным языком программирования, на котором реализуются серверные компоненты пользовательских приложений. Коммерческие СУБД реализуют собственные диалекты SQL, базирую- щиеся на различных его стандартах и, как правило, расширяющие возможности стандартного языка. В таблице 3.2 приведены фирменные наименования неко- торых из широко распространенных диалектов SQL. Таблица 3.2 Диалекты языка SQL СУБД Диалект языка SQL IBM DB2 SQL PL (SQL Procedural Language) MySQL SQL/PSM (SQL/Persistent Stored Module) InterBase/Firebird PSQL (Procedural SQL) Oracle PL/SQL (Procedural Language/SQL) Postgres, PostgreSQL PL/pgSQL (Procedural Language/PostgreSQL) Microsoft SQL-Server Transact-SQL Microsoft Jet/Access Microsoft Jet SQL Правила использования диалектов языка SQL размещены на официаль- ных ресурсах разработчиков соответствующих СУБД. Синтаксические конструкции языков программирования формулируются с использованием специальной нотации — так называемых форм Бэкуса — Наура (BNF). Стандарт BNF и пример BNF-формулы одного из SQL-операто- ров приведены в приложении А. Microsoft Jet SQL Основная причина рассмотрения в учебном пособии особенностей имен- но этого диалекта языка SQL — его относительная простота, что немаловажно на начальном этапе освоения такого специфического языка программирования, как SQL. По этой же причине практикум по SQL-программированию, предла- гаемый в следующей главе учебного пособия, ориентирован именно на исполь- зование Microsoft Jet SQL и поддерживающей его СУБД MS ACCESS при вы- полнении практических заданий. Microsoft Jet SQL разработан на основе стандарта SQL-89, однако в этом диалекте реализованы не все средства стандартного языка, имеются также до- 11 / 19 107 полнительные возможности, не поддерживаемые стандартным языком, напри- мер Microsoft Jet SQL позволяет использовать более мощные выражения, раз- решает группировку и сортировку по выражениям, допускает реализацию пере- крестных запросов и т. д. Ниже приведена краткая сводка основных различий между этими языками. 1. Оператор Between...And Оператор имеет следующий синтаксис: выражение [NOT] Between значение_1 And значение_2 В языке Microsoft Jet SQL значение_1 может превышать значение_2, а в SQL-89 значение_1 должно быть меньше или равно значение_2. 2. Подстановочные символы предиката LIKE (табл. 3.3) Таблица 3.3 Подстановочные символы предиката сравнения LIKE Заменяемые символы SQL-89 Microsoft Jet SQL Любой одиночный символ _ (подчеркивание) ? Любое количество символов % * 3. Различные наборы типов данных В таблице 3.4 перечислены типы данных SQL-89, эквивалентные им типы данных языка Microsoft Jet SQL и допустимые синонимы для их именования. Таблица 3.4 Сравнительная характеристика типов данных SQL-89 Microsoft Jet SQL Синонимы BIT, BIT VARYING BINARY VARBINARY Не поддерживается BIT BOOLEAN, LOGICAL, YES/NO Не поддерживается BYTE INTEGER1 Не поддерживается COUNTER AUTOINCREMENT Не поддерживается CURRENCY MONEY DATE, TIME, TIMESTAMP DATETIME DATE, TIME, TIMESTAMP Не поддерживается GUID DECIMAL Не поддерживается REAL SINGLE FLOAT4, IEEESINGLE, REAL DOUBLE PRECISION, FLOAT DOUBLE FLOAT, FLOAT8, NUMBER, NUMERIC SMALLINT SHORT INTEGER2, SMALLINT INTEGER LONG INT, INTEGER, INTEGER4 INTERVAL Не поддерживается Не поддерживается LONGBINARY GENERAL, OLEOBJECT Не поддерживается LONGTEXT LONGCHAR, MEMO, NOTE CHARACTER, CHARACTER VARYING TEXT ALPHANUMERIC, CHAR, CHARACTER, STRING, VARCHAR Дополнительные возможности языка Microsoft Jet SQL 4. Оператор TRANSFORM Оператор TRANSFORM предназначен для создания так называемых пере- крестных запросов, результат выполнения которых представляется пользова- 12 / 19 108 телю в стиле электронной таблицы — в более компактной форме по сравнению со стандартным запросом выборки данных. Ниже приведены формат этого оператора и описания его аргументов. TRANSFORM агрегатная_функция SELECT … PIVOT поле[ IN (значение_1[, значение_2[, ...] ]) ] Аргументы оператора: – инструкция TRANSFORM — должна быть записана первой; – агрегатная_функция — одна из агрегатных функций из числа поддер- живаемых СУБД; – инструкция SELECT может содержать: • список полей, имена которых образуют заголовки строк перекрестной таблицы, записываемые в ее левом столбце; • раздел GROUP BY, задающий параметры группировки по строкам; • раздел WHERE, задающий условия выборки строк; • подчиненные запросы в предложении WHERE; – поле — имя столбца или выражение, которое содержит заголовки столбцов для результирующего набора; – значение_1, значение_2 и т. д. — фиксированные значения, используемые при создании заголовков столбцов (верхняя строка результирующей перекрест- ной таблицы). Листинг 3.12 содержит пример перекрестного запроса, представляющего информацию о суммах выручки, полученных от реализации товаров, в коорди- натах СОТРУДНИК — ТОВАР, а рисунок 3.1 — результат выполнения этого за- проса. TRANSFORM Sum(Заказано.Количество* БазоваяЦенаРеализации*(1-Скидка)) AS [Сумма выручки] SELECT Товар FROM Склад INNER JOIN (Сотрудники INNER JOIN (Заказы INNER JOIN Заказано ON Заказы.Код_Заказа = Заказано.Код_Заказа) ON Сотрудники.Код_Сотрудника = Заказы.Код_Сотрудника) ON Склад.КодТовара = Заказано.Код_Товара GROUP BY Товар PIVOT Сотрудник; Листинг 3.12 Пример перекрестного запроса 5. Агрегатные функции StDev и StDevP Дополнительно к пяти стандартным агрегатным функциям язык Microsoft Jet SQL включает функции StDev(выражение)иStDevP(выражение), возвраща- 13 / 19 109 ющие соответственно смещенное и несмещенное значения среднеквадратично- го отклонения, вычисляемого по набору значений, содержащихся в выражении. Рис. 3.1 Результат выполнения перекрестного запроса, представленного на листинге 3.12 Аргумент выражение может быть именем столбца, содержащего обраба- тываемые данные числового типа, или выражением, операндами которого мо- гут быть имена столбцов, числовые константы или (нестатистические) функ- ции, возвращающие числовые значения. Если результат запроса содержит меньше двух строк (или не содержит строк для функции StDevP), эти функции возвращают значение Null (что озна- чает невозможность вычисления среднеквадратичного отклонения). 6. Запросы с параметрами Запрос с параметрами помогает автоматизировать процесс изменения условий отбора запроса. При выполнении такого запроса значения параметров запрашиваются у пользователя и после ввода значений подставляются вместо имен параметров в текст запроса. Раздел параметров SQL-запроса записывается перед разделом SELECT в соответствии со следующей BNF-формулой (приложение А): PARAMETERS ИмяПараметра ТипДанных [,ИмяПараметра ТипДанных [, ...]]; ИмяПараметра — текст, который будет отображаться в окне диалога при выполнении запроса. При наличии пробелов или знаков препинания в имени параметра его следует заключить в квадратные скобки. Имена параметров до- пускается использовать в качестве переменных в разделах WHERE или HAVING запроса. ТипДанных — один из базовых типов данных Microsoft Jet SQL или его синоним (табл. 4.2). 14 / 19 110 В листинге 3.13 рассчитывается сумма выручки от заказов, исполненных сотрудниками филиалов, при этом у пользователя запрашиваются значения пара- метров: имя сотрудника и годы исполнения заказов. PARAMETERS [Сотр] Text (16), [Год_ОТ] Long, [Год_ДО] Long; SELECT Филиал, Город, Сотрудник, SUM(Количество*БазоваяЦенаРеализации*(1-Скидка)) AS [Сумма выручки] FROM Города INNER JOIN (Филиалы INNER JOIN (Сотрудники INNER JOIN (Заказы INNER JOIN Заказано ON Заказы.Код_Заказа = Заказано.Код_Заказа) ON Сотрудники.Код_Сотрудника = Заказы.Код_Сотрудника) ON Филиалы.Код_Филиала = Сотрудники.Код_Филиала) ON Города.Код_Города = Филиалы.Код_Города WHERE Сотрудник LIKE [Сотр] AND YEAR(ДатаРазмещения) BETWEEN [Год_ОТ] AND [Год_ДО] GROUP BY Филиал, Город, Сотрудник; Листинг 3.13 Пример использования параметризованного SQL-запроса 15 / 19 |