9.2.5 Встроенные функции

В стандарт SQL2 вошли наиболее полезные функции из различных реализации SQL. Эти функции перечислены в таблце.

табл. 9.2 Встроенные функции SQL.

Функция	Возвращается
ВIT LENGTH(строка)	количество битов в строке
САSТ(значение AS тип данных)	значение, преобразованное тип данных (например, дата преобразованная в строку)
CHAR_LENGTH(строка)	длина строки символов
CONVERT(строка USING функция)	строка, преобразованная в соответствии с указанной функцией
CURRENT_DATE	текущая дата
CURRENT_TIME(точность)	текущее время с указанной точностью
CURRENT_IMESTAMP (точность)	текущие дата и время с указанной точностью
EXTRACT(часть FROM значение)	указанная часть (DAY, HOUR и т.д.) из значения типа DATETIME
LOWER(строка)	строка, преобразованная к нижнему регистру
OCTETLENGTH(строка)	число байтов в строке символов
POSITION(первая строка IN вторая строка)	позиция, с которой начинается вхождение первой строки во вторую строку
SUBSTRING(строка FROM n FOR длина)	часть строки, начинающаяся с n-го символа и имеющая указанную длину
TRANSLATE(строка USING функция)	строка, преобразованная с помощью указанной функции
TRIM(BOTH символ FROM строка)	строка, в которой удалены первые и последние указанные символы
TRIM(LEADING символ FROM строка)	строка, в которой удалены первые указанные символы
TRIM(TRAILING символ FROM строка)	строка, в которой удалены последние указанные символы
UPPER(строка)	строка, преобразованная к верхнему регистру

9.3 Запросы на чтение данных. Оператор SELECT

Оператор SELECT применяется для построения выборок данных и имеет следующий синтаксис:

SELECT [ALL | DISTINCT] возвращаемый_столбец, … | *

FROM спецификатор_таблицы, …

WHERE условие_поиска

GROUP BY имя_столбца, …

HAVING условие_поиска

ORDER BY спецификатор_сортировки, …

9.3.1 Предложение SELECT

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

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

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

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

9.3.2 Предложение FROM

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

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

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

Иногда требуется получить содержимое всех столбцов таблицы. На практике такая ситуация может возникнуть, когда вы впервые сталкиваетесь с новой базой данных и необходимо быстро получить представление о ее структуре и хранимых в ней данных. С учетом этого в SQL разрешается использовать вместо списка .возвращаемых столбцов символ звездочки (*), который означает, что требуется прочитать все столбцы:

Показать все данные, содержащиеся в таблице Students.

SELECT *

FROM Students

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

9.3.3 Отбор строк (предложение WHERE)

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

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

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

Если условие поиска имеет значение FALSE или NULL, то строка исключается из результатов запроса.

9.3.4 Условия поиска

В SQL используется множество условий поиска, позволяющих эффективно и естественным образом создавать различные типы запросов. Ниже рассматриваются пять основных условий поиска (в стандарте ANSI/ISO они называются предикатами).

Сравнение (=, <>, <, <=, >, >=). Наиболее распространенным условием поиска в SQL является сравнение. При сравнении SQL вычисляет и сравнивает значения двух выражений для каждой строки данных. Выражения могут быть как очень простыми, например содержать одно имя столбца или константу, так и более сложными - арифметическими выражениями.

Ниже приведен синтаксис оператора сравнения.

Выражение1 = | <> | < | <= | > | >= Выражение2

Проверка на принадлежность диапазону значений (BETWEEN). Другой формой условия поиска является проверка на принадлежности диапазону значений (ключевое слово BETWEEN). При этом проверяется, находится ли значение данных между двумя определенными значениями.

Проверяемое_выражение [NOT] BETWEEN нижнее_выражение AND верхнее_выражение

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

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

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

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

проверяемое_выражение [NOT] IN (константа, …)

Например: вывести список фамилий студентов, которые учатся в группах с кодами 1, 3, 5 и 10.

SELECT StName

FROM Students

WHERE GrNo IN (1, 3, 5, 10)

Проверка на соответствие шаблону (LIKE). Для чтения строк, в которых содержимое некоторого текстового столбца совпадает с заданным текстом, можно использовать простое сравнение, однако иногда необходимо осуществить сравнение на основе фрагмента строки.

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

имя_столбца [NOT] LIKE шаблон [ESCAPE символ_пропуска]

Подстановочные знаки. Подстановочный знак % совпадает с любой последовательностью из нуля или более символов. Например, следующий запрос выведет информацию о студентах, чья фамилия начинается с "Иван":

SELECT *

FROM StudentsWHERE StName LIKE 'Иван%'

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

SELECT *

FROM Students WHERE StName LIKE 'Натал_я'

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

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

В стандарте ANSI/ISO определен способ проверки наличия в строках литералов, использующихся в качестве подстановочных знаков. Для этом применяются символы пропуска. Когда в шаблоне встречается такой символ то символ, следующий непосредственно за ним, считается не подстановочным знаком, а литералом. (Происходит пропуск символа.) Непосредственно за символом пропуска может следовать либо один из двух подстановочных знаков, либо сам символ пропуска, поскольку он тоже приобретает в шаблоне особое значение.

Символ пропуска определяется в виде строки, состоящей из одного символа, и предложения ESCAPE. Ниже приведен пример использования знака доллара ($) в качестве символа пропуска:

SELECT *

FROM DataTable WHERE Text LIKE '%менее 50$% %' ESCAPE $

Проверка на равенство значению NULL (IS NULL). Значения NULL обеспечивают возможность применения трехзначной логики в условиях поиска. Для любой заданной строки результат применения условия поиска может быть TRUE, FALSE или NULL (в случае, когда в одном из столбцов содержится значение NULL). Иногда бывает необходимо явно проверять значения столбцов на равенство NULL и непосредственно обрабатывать их. Для этого в SQL имеется специальная проверка на равенство значению NULL (IS NULL).

IS [NOT] NULL имя_ столбца

Составные условия поиска (AND, OR и NOT). Простые условия поиска, описанные в предыдущих параграфах, после применения к некоторой строке возвращают значения TRUE, FALSE или NULL. С помощью правил логики эти простые условия можно объединять в более сложные. Условия поиска, объединенные с помощью ключевых слов AND, OR и NOT, сами могут быть составными.

WHERE [NOT] условие_поска [AND | OR] [NOT] условие_поска …

9.3.5 Сортировка результатов запроса (предложение ORDER BY).

Строки результатов запроса, как и строки таблицы базы данных, не имеют определенного порядка. Включив в оператор SELECT предложение ORDER BY, можно отсортировать результаты запроса. Это предложение состоит из ключевых слов ORDER BY, за которыми следует список имен столбцов, разделенных запятыми.

ORDER BY имя_столбца [ASC | DESC], …

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

Например: вывести список фамилий студентов учащихся в группе с кодом 1 в обратном алфавитном порядке.

SELECT StName

FROM Students ORDER BY DESC StName

9.4 Многотабличные запросы на чтение (объединения).

На практике многие запросы считывают данные сразу из нескольких таблиц базы данных.

9.4.1 Запросы с использованием отношения предок/потомок.

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

Например: вывести список фамилий студентов с названием группы, в которой он учится

SELECT StName, GrName

FROM Students, Groups

WHERE Students.GrNo = Groups.GrNo

9.4.2 Прочие объединения таблиц по равенству

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

Объединение таблиц по неравенству. Термин "объединение" применяется к любому запросу, который объединяет данные из двух таблиц базы данных путем сравнения значений в двух столбцах этих таблиц. Самыми распространенными являются объединения, созданные на основе равенства связанных столбцов (объединения по равенству). Кроме того, SQL позволяет объединять таблицы с помощью других операций сравнения. Например, получить все коды дисциплин по которым студентом с кодом 1 была получена оценка большая, чем по дисциплине с кодом 1.

SELECT Marks1.SubjNo

FROM Marks AS Marks1, Marks AS Marks2

WHERE Marks1.Mark > Marks2.Mark

AND Marks2.SubjNo = 1 AND Marks1.StNo = 1

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

Полные имена столбцов. В учебной базе данных имеется несколько случаев, когда две таблицы содержат столбцы с одинаковыми именами. Например, столбцы с именем GrNo имеются в таблицах Groups и Students.

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

Groups.GrNo Students.GrNo

В операторе SELECT вместо простых имен столбцов всегда можно использовать полные имена. Таблица, заданная в полном имени столбца, должна, конечно, соответствовать одной из таблиц, заданных в предложении FROM.

Чтение всех столбцов. Как уже говорилось ранее, оператор SELECT * используется для чтения всех столбцов таблицы, указанной в предложении FROM. В многотабличном запросе звездочка означает выбор всех столбцов из всех таблиц, указанных в предложении FROM.

Самообъединения. Некоторые многотабличные запросы используют отношения, существующие внутри одной из таблиц. Предположим, например, что требуется вывести список имен всех преподавателей и их руководителей. Каждому преподавателю соответствует одна строка в таблице Teachers, а столбец ChiefNo содержит идентификатор преподавателя, являющегося руководителем. Столбцу ChiefNo следовало бы быть внешним ключом для таблицы, в которой хранятся данные о руководителях. И он им, фактически, является - это внешний ключ для самой таблицы Teachers.

Для объединения таблицы с самой собой в SQL применяется именно такой подход: использование "виртуальной копии". Вместо того чтобы на самом деле сделать копию таблицы, SQL позволяет вам сослаться на нее, используя другое имя, которое называется псевдонимом таблицы.

Например: вывести список всех преподавателей и их руководителей.

SELECT Teachers. TName, Chiefs.TName

FROM Teachers, Teachers Chiefs

WHERE Teachers. ChiefNo = Chiefs.TNo

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

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

Литература:

1. Джеймс Р. Грофф, Пол Н. Вайнберг. SQL: полное руководство: пер.с англ. -К.: Издательская группа BHV, 2000.-608с. Стр. 31-39,69-166.

ЛЕКЦИЯ 10. Язык SQL (продолжение)

• 10.1 Объединения и стандарт SQL2
• 10.2 Итоговые запросы на чтение. Агрегатные функции
• 10.3 Запросы с группировкой (предложение GROUP BY)
• 10.4 Вложенные запросы

10.1 Объединения и стандарт SQL2

В стандарте SQL2 был определен совершенно новый метод поддержки внешних объединений, который не опирался ни на одну популярную СУБД. В спецификации стандарта SQL2 поддержка внешних объединений осуществлялась в предложении FROM с тщательно разработанным синтаксисом, позволявшим пользователю точно определить, как исходные таблицы должны быть объединены в запросе. Расширенное предложение FROM поддерживает также операцию UNION над таблицами и допускает сложные комбинации запросов на объединение операторов SELECT и объединений таблиц.

10.1.1 Внутренние объединения в стандарте SQL2

Две объединяемые таблицы соединяются явно посредством операции JOIN, а условие поиска, описывающее объединение, находится теперь в предложении ON внутри предложения FROM В условии поиска, следующем за ключевым словом ON, могут быть заданы любые критерии сравнения строк двух объединяемых таблиц.

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

SELECT StName, GrName

FROM Students INNER JOIN Groups

ON Students.GrNo = Groups.GrNo

(В этих простых двухтабличных объединениях все содержимое предложения WHERE просто перешло в предложение ON, и предложение ON не добавляет ничего нового в язык SQL.

Стандарт SQL2 допускает еще один вариант запроса на простое внутреннее объединение таблиц Students и Groups. Так как связанные столбцы этих таблиц имеют одинаковые имена и сравниваются на предмет равенства (что делается довольно часто), то можно использовать альтернативную форму предложения ON, в которой задается список имен связанных столбцов:

SELECT StName, GrName

FROM Students INNER JOIN Groups USING (GrNo)

Ниже приведен синтаксис оператора JOIN:

1. естественное соединение.

FROM спецификация_таблиц,…,

таблица1

NATURAL {INNER|FULL[OUTER]|LEFT[OUTER]|RIGHT[OUTER]}JOIN

таблица2 …

2. соединение с использованием выражения.

FROM спецификация_таблицы,…,

таблица1

{INNER|[OUTER] FULL|[OUTER]LEFT|[OUTER]RIGHT} JOIN

таблица2

ON условие | USING(список_столбцов),…

3. объединение или декартово произведение.

FROM спецификация_таблицы,…,

таблица1 {UNION | CROSS JOIN} таблица2 ,…

Объединение двух таблиц, в котором связанные столбцы имеют идентичные имена, называется естественным объединением, так как обычно это действительно самый "естественный" способ объединения двух таблиц. Запрос на выборку пар фамилия студента/название группы, в которой он учиться, можно выразить как естественное объединение следующим образом:

SELECT StName, GrName

FROM Students NATURAL INNER JOIN Groups

10.1.2 Внешние объединения в стандарте SQL2

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

SELECT Chief.TName, SubOrdinate.TName

FROM Teachers AS Chief FULL OUTER JOIN Teachers AS SubOrdinate

ON Chief.TNo = SubOrdinate.TChiefNo

Результат такого запроса (данные из Приложения А) приведен на рис. 10.1.

Chief.TName	SubOrdinate.TName
NULL	Иванов
Иванов	Петров
Петров	Стрельцов
Петров	Сидоров
Сидоров	NULL
Стрельцов	NULL

рис. 10.1 Результатом такого запроса на внешнее объединение.

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

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

SELECT Chief.TName, SubOrdinate.TName

FROM Teachers AS Chief FULL JOIN Teachers AS SubOrdinate

ON Chief.TNo = SubOrdinate.TChiefNo

По слову FULL СУБД сама определяет, что запрашивается внешнее объединение.

Вполне естественно, что в стандарте SQL2 левое и правое внешние объединения обозначаются словами LEFT и RIGHT вместо слова FULL. Вот вариант того же запроса, определяющий левое внешнее объединение:

SELECT Chief.TName, SubOrdinate.TName

FROM Teachers AS Chief LEFT OUTER JOIN Teachers AS SubOrdinate

ON Chief.TNo = SubOrdinate.TChiefNo

В результате такого запроса (данные из Приложения А.) будет получено следующее отношение (рис. 10.2).

Chief.TName	SubOrdinate.TName
Иванов	Петров
Петров	Стрельцов
Петров	Сидоров
Сидоров	NULL
Стрельцов	NULL

рис. 10.2 Результатом такого запроса на внешнее объединение

10.1.3 Перекрестные объединения и запросы на объединение в SQL2

Расширенное предложение FROM в стандарте SQL2 поддерживает также два других способа соединения данных из двух таблиц - декартово произведение и запросы на объединение. Строго говоря, ни один из них не является операцией "объединения", но они поддерживаются в стандарте SQL2 с помощью тех же самых предложений, что и внутренние и внешние объединения. Вот запрос, создающий декартово произведение таблиц Students и Groups:

SELECT *

FROM Students CROSS JOIN Groups

10.1.4 Многотабличные объединения в стандарте SQL2

Одно из крупных преимуществ расширенного предложения FROM заключается в том, что оно дает единый стандарт для определения как внутренних и внешних объединений, так и произведений и запросов на объединение. Другим, даже еще более важным преимуществом этого предложения является то, что оно обеспечивает очень ясную и четкую спецификацию объединений трех и четырех таблиц, а также произведений и запросов на объединение. Для построения этих сложных объединений любые выражения описанные ранее, могут быть заключены в круглые скобки. Результирующее выражение, в свою очередь, можно использовать для создания других выражений объединения, как если бы оно было простой таблицей. Точно так же, как SQL позволяет с помощью круглых скобок комбинировать различные арифметические операции (+, -, * и /) и строить сложные выражения, стандарт SQL2 дает возможность создавать сложные выражения для объединений.

10.2 Итоговые запросы на чтение. Агрегатные функции

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

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

1. функция SUM() вычисляет сумму всех значений, содержащихся в столбце:

SUM(выражение | [DISTINCT] имя_столбца)

2. функция AVG() вычисляет среднее всех значений, содержащихся в столбце:

AVG(выражение | [DISTINCT] имя_столбца)

3. функция MIN() находит наименьшее среди всех значений, содержащихся в столбце:

MIN(выражение | имя_столбца)

4. функция МАХ() находит наибольшее среди всех значений, содержащихся в столбце:

MAX(выражение | имя_столбца)

5. функция COUNT() подсчитывает количество значений, содержащихся в столбце:

COUNT([DISTINCT] имя_столбца)

6. функция COUNT(*) подсчитывает количество строк в таблице результатов запроса:

COUNT(*)

10.2.1 Агрегатные функции и значения NULL

В стандарте ANSI/ISO также определены следующие точные правила обработки значений NULL в агрегатных функциях:

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

2. если все значения в столбце равны NULL, то функции SUM(), AVG(), MIN() и MAX () возвращают значение NULL; функция COUNT () возвращает ноль;

3. если в столбце нет значений (т.е. столбец пустой), то функции SUM() , AVG(), MIN() и МАХ() возвращают значение NULL; функция COUNT() возвращает ноль;

4. функция COUNT(*) подсчитывает количество строк и не зависит от наличия или отсутствия в столбце значений NULL; если строк в таблице нет, эта функция возвращает ноль.

10.3 Запросы с группировкой (предложение GROUP BY)

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

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

SELECT StName, AVG(Mark)

FROM Marks INNER JOIN Students USING(StNo)

GROUP BY StName

10.3.1 Несколько столбцов группировки

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

SELECT StName, Semester, AVG(Mark)

FROM Marks INNER JOIN Students USING(StNo)

GROUP BY StName, Semester

10.3.2 Ограничения на запросы с группировкой

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

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

1. константа;

2. агрегатная функция, возвращающая одно значение для всех строк, входящих в группу;

3. столбец группировки, который, по определению, имеет одно и то же значение во всех строках группы;

4. выражение, включающее в себя перечисленные выше элементы.

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

10.3.3 Значения NULL в столбцах группировки

В стандарте ANSI определено, что два значения NULL в предложении GROUP BY равны.

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

10.3.4 Условия поиска групп (предложение HAVING)

Точно так же, как предложение WHERE используется для отбора отдельных строк, участвующих в запросе, предложение HAVING можно применить для отбора групп строк. Его формат соответствует формату предложения WHERE. Предложение HAVING состоит из ключевого слова HAVING, за которым следует условие поиска. Таким образом, данное предложение определяет условие поиска для групп.

10.3.5 Ограничения на условия поиска групп

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

10.3.6 Предложение HAVING без GROUP BY

Предложение HAVING почти всегда используется в сочетании с предложением GROUP BY, однако синтаксис оператора SELECT не требует этого. Если предложение HAVING используется без предложения GROUP BY, SQL рассматривает полные результаты запроса как одну группу. Другими словами, агрегатные функции, содержащиеся в предложении HAVING, применяются к одной и только одной группе, и эта группа состоит из всех строк. На практике предложение HAVING очень редко используется без соответствующего предложения GROUP BY.

10.4 Вложенные запросы

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

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

Например, вывести список фамилий студентов, средний балл которых выше 4,5:

SELECT StName

FROM Students

WHERE (SELECT AVG(Mark)

FROM Marks

WHERE Marks.StNo = Students.StNo) > 4.5

Вложенный запрос всегда заключается в круглые скобки, но по-прежнему сохраняет знакомую структуру оператора SELECT, содержащего предложение FROM и необязательные предложения WHERE, GROUP BY и HAVING. Структура этих предложений во вложенном запросе идентична их структуре в оператора SELECT; во вложенном запросе эти предложения выполняют свои обычные функции. Однако между вложенным запросом и оператором SELECT имеется ряд отличий:

1. Таблица результатов вложенного запроса всегда состоит из одного столбца. Это означает, что в предложении SELECT вложенного запроса всегда указывается один возвращаемый столбец.

2. Во вложенный запрос не может входить предложение ORDER BY. Результаты вложенного запроса используются только внутри главного запроса и для пользователя остаются невидимыми, поэтому нет смысла их сортировать.

3. Вложенный запрос не может быть запросом на объединение нескольких различных операторов SELECT; допускается использование только одного оператора SELECT.

4. Имена столбцов во вложенном запросе могут являться ссылками на столбцы таблиц главного запроса. Это так называемые внешние ссылки (ссылка на Students.StNo в предыдущем примере). Внешние ссылки необязательно должны присутствовать во вложенном запросе.

10.4.1 Условия поиска во вложенном запросе

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

Сравнение с результатом вложенного запроса (=, <>, <, <=, >, >=). Сравнивает значение выражения с одним значением, возвращенным вложенным запросом. Эта проверка напоминает простое сравнение.

проверяемое_выражение  | = | <> | < | <= | > | >= | вложенный_запрос

Сравнение с результатом вложенного запроса является модифицированной формой простого сравнения. Значение выражения сравнивается со значением, возвращенным вложенным запросом, и если условие сравнения выполняется, то проверка возвращает значение TRUE. Эта проверка используется для сравнения значения из проверяемой строки с одним значением, возвращенным вложенным запросом, как показано первом примере.

Проверка на принадлежность результатам вложенного запроса (IN). Проверка на принадлежность результатам вложенного запроса (ключевое слово IN) является видоизмененной формой простой проверки на членство в множестве.

проверяемое_выражение [NOT] IN вложенный_запрос

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

SELECT *

FROM Students

WHERE GrNo IN (SELECT GrNo FROM Groups WHERE GrName LIKE `А%')

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

[NOT] EXISTS вложенный_запрос

Например, вывести фамилии студентов, которые в 1-м семестре сдали хотябы одну дисциплину:

SELECT StName

FROM Students

WHERE EXISTS (SELECT *

FROM Marks

WHERE Marks.StNo = Students.StNo AND Semester = 1)

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

проверяемое_выражение  | = | <> | < | <= | > | >= |  ANY | ALL вложенный_запрос

Проверка ANY. В проверке ANY используется один из шести операторов сравнения (=, <>, <, <=, >, >=) для того, чтобы сравнить одно проверяемое значение со столбцом данных, возвращенным вложенным запросом. Проверяемое значение поочередно сравнивается с каждым значением, содержащимся в столбце. Если любое из этих сравнений дает результат TRUE, то проверка ANY возвращает значение TRUE.

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

SELECT StName

FROM Students

WHERE 5 = ANY (SELECT Mark

FROM Marks

WHERE Marks.StNo = Students.StNo AND Semester = 1)

Если вложенный запрос в проверке ANY не создает ни одной строки результата или если результаты содержат значения NULL, то в различных СУБД проверка ANY может выполняться по-разному. В стандарте ANSI/ISO для языка SQL содержатся подробные правила, определяющие результаты проверки ANY, когда проверяемое значение сравнивается со столбцом результатов вложенного запроса:

1. если вложенный запрос возвращает пустой столбец результатов, то проверка ANY имеет значение FALSE (в результате выполнения вложенного запроса не получено ни одного значения, для которого выполнялось бы условие сравнения).

2. если операция сравнения имеет значение TRUE хотя бы для одного значения в столбце, то проверка ANY возвращает значение TRUE (имеется некоторое значение, полученное вложенным запросом, для которого условие сравнения выполняется).

3. если операция сравнения имеет значение FALSE для всех значений в столбце, то проверка ANY возвращает значение FALSE (можно утверждать, что ни для одного значения, возвращенного вложенным запросом, условие сравнения не выполняется);

4. если операция сравнения не имеет значение TRUE ни для одного значения в столбце, но в нем имеется одно или несколько значений NULL то проверка ANY возвращает результат NULL. (В этой ситуации невозможно но с определенностью утверждать, существует ли полученное вложенным запросом значение, для которого выполняется условие сравнения; может быть, существует, а может и нет - все зависит от "настоящих" значений неизвестных данных.)

Проверка ALL. В проверке ALL, как и в проверке ANY, используется один из шести операторов (=, <>, <, <=, >, >=) для сравнения одного проверяемого значения со столбцом данных, возвращенным вложенным запросом. Проверяемое значение поочередно сравнивается с каждым значением, содержащимся в столбце. Если все сравнения дают результат TRUE, то проверка ALL возвращает значение TRUE.

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

SELECT StName

FROM Students

WHERE 3 = ALL (SELECT Mark

FROM Marks

WHERE Marks.StNo = Students.StNo AND Semester = 1)

Если вложенный запрос в проверке ALL не возвращает ни одной строки или если результаты запроса содержат значения NULL, то в различных СУБД проверка ALL может выполняться по-разному. В стандарте ANSI/ISO для языка SQL содержатся подробные правила, определяющие результаты проверки ALL, когда проверяемое значение сравнивается со столбцом результатов вложенного запроса:

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

2. если операция сравнения дает результат TRUE для каждого значения в столбце, то проверка ALL возвращает значение TRUE. Условие сравнения выполняется для каждого значения, полученного вложенным запросом.

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

4. если операция сравнения не дает результат FALSE ни для одного значения в столбце, но для одного или нескольких значений дает результат NULL, то проверка ALL возвращает значение NULL. В этой ситуации нельзя с определенностью утверждать, для всех ли значений, полученных вложенным запросом, справедливо условие сравнения; может быть, для всех, а может и нет - все зависит от "настоящих" значений неизвестных данных.

10.4.2 Вложенные запросы и объединения

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

10.4.3 Уровни вложенности запросов

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

10.4.4 Вложенные запросы в предложении HAVING

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

Литература:

1. Джеймс Р. Грофф, Пол Н. Вайнберг. SQL: полное руководство: пер.с англ. -К.: Издательская группа BHV, 2000.-608с. Стр. 169-217.

ЛЕКЦИЯ 11. Язык SQL. (продолжение)

• 11.1 Внесение изменений в базу данных.
• 11.2 Удаление существующих данных (Оператор DELETE)
• 11.3 Обновление существующих данных (Оператор UPDATE)
• 11.4 Определение структуры данных в SQL
• 11.5 Понятие представления.
• 11.6 Представления в SQL.
• 11.7 Системный каталог (самостоятельное изучение)

11.1 Внесение изменений в базу данных.

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

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

11.1.1 Добавление новых данных (оператор INSERT).

Однострочный оператор INSERT, синтаксис которого описан ниже, добавляет в таблицу новую строку. В предложении INTO указывается таблица, в которую добавляется новая строка (целевая таблица), а в предложении VALUES содержатся значения данных для новой строки. Список столбцов определяет, какие значения в какой столбец заносятся.

INSERT INTO имя_таблицы (имя_столбца,…) VALUES (константа | NULL,…)

Ниже приведен пример оператора INSERT, который добавляет информацию о новой группе К-99-51 в учебную базу данных:

INSERT INTO Groups(GrNo, EnterYear, GrName)

VALUES(6, 1999, 'К-99-51')

Добавление значений NULL. При добавлении в таблицу новой строки всем столбцам, имена которых отсутствуют в списке столбцов оператора INSERT, автоматически присваивается значение NULL.

Добавление всех столбцов. Для удобства в SQL разрешается не включать список столбцов в оператор INSERT. Если список столбцов опущен, он генерируется автоматически и в нем слева направо перечисляются все столбцы таблицы. При выполнении оператора SELECT * генерируется такой же список столбцов. Пользуясь этой сокращенной формой записи, оператор INSERT из предыдущего примера можно переписать таким образом:

INSERT INTO Groups

VALUES(6, 1999, 'К-99-51')

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

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

INSERT INTO имя_таблицы (имя_столбца,…) запрос

11.2 Удаление существующих данных (Оператор DELETE)

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

Оператор DELETE, синтаксис которого изображен ниже, удаляет выбранные строки данных из одной таблицы.

DELETE FROM имя_таблицы [WHERE условие_поиска]

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

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

DELETE FROM Students

WHERE StNo = 3

Хотя предложение WHERE в операторе DELETE является необязательным, оно присутствует почти всегда. Если же оно отсутствует, то удаляются все строки целевой таблицы.

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

11.3 Обновление существующих данных (Оператор UPDATE)

Оператор UPDATE, обновляет значения одного или нескольких столбцов в выбранных строках одной таблицы.

UPDATE имя_таблицы

SET имя_столбца = выражение,...

[WHERE условие_поиска]

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

Например, Изменить фамилию студента Петрова с кодом 2 на фамилию Петренко:

UPDATE Students

SET StName = 'Петренко'

WHERE StNo = 2

Условия поиска, которые могут быть заданы в предложении WHERE оператора UPDATE, в точности соответствуют условиям поиска, доступным в операторах SELECT и DELETE.

Как и оператор DELETE, оператор UPDATE может одновременно обновить несколько строк, соответствующих условию поиска.

Предложение WHERE в операторе UPDATE является необязательным. Если оно опущено, то обновляются все строки целевой таблицы.

В операторе UPDATE, так же как и в операторе DELETE, вложенные запросы могут играть важную роль, поскольку они дают возможность отбирать строки для обновления, опираясь на информацию из других таблиц.

11.4 Определение структуры данных в SQL

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

11.4.1 Создание таблицы (оператор CREATE TABLE)

Оператор CREATE TABLE, синтаксис которого изображен ниже, определяет новую таблицу. Различные предложения оператора задают элементы определений таблицы. следует также помнить, что не все параметры стандарта SQL2 присутствуют во всех СУБД.

После выполнения оператора CREATE TABLE появляется новая таблица, которой присваивается имя, указанное в операторе. Имя таблицы должно быть идентификатором, допустимым в SQL, и не должно конфликтовать с именами существующих таблиц.

CREATE TABLE имя_таблицы(

определение столбцов

имя_столбца тип_данных [DEFAULT значение] [NOT NULL],…

определение первичного ключа

PRIMARY KEY(имя_столбца,…),

определение внешних ключей

FOREIGN KEY имя_ограничения (имя_столбца,…)

REFERENCE имя_таблицы

[ON DELETE CASCADE | SET NULL | SET DEFAULT | NO ACTION]

[ON UPDATE CASCADE | SET NULL | SET DEFAULT | NO ACTION],

условие уникальности данных

UNIQUE(имя_столбца,…),

условие проверки

CHECK(условие_поиска)

)

Определения столбцов. Столбцы новой таблицы задаются в операторе CREATE TABLE. Определения столбцов представляют собой заключенный в скобки список, элементы которого отделены друг от друга запятыми. Порядок следования определений столбцов в списке соответствует расположению столбцов в таблице. Каждое такое определение содержит следующую информацию:

1. Имя столбца, которое используется для ссылки на столбец в оператора SQL. Каждый столбец в таблице должен иметь уникальное имя, но' разных таблицах имена столбцов могут совпадать.

2. Тип данных столбца, показывающий, данные какого вида хранятся в столбце.

3. Указание на то, обязательно ли столбец должен содержать данные. Предложение NOT NULL предотвращает занесение в столбец значений NULL, в противном случае значения NULL допускаются.

4. Значение по умолчанию для столбца СУБД, которое заносится в столбец в том случае, если в операторе INSERT для таблицы не определено значение данного столбца.

Определения первичного и внешнего ключей. Кроме определений столбцов таблицы, в операторе CREATE TABLE указывается информация о первичном ключе таблицы и о ее связях с другими таблицами базы данных Эта информация содержится в предложениях PRIMARY KEY и FOREIGN KEY.

В предложении PRIMARY KEY задается столбец или столбцы, которые образуют первичный ключ таблицы. Этот столбец (или комбинация столбцов) служит в качестве уникального идентификатора строк таблицы. СУБД автоматически следит за тем, чтобы первичный ключ каждой строки таблицы имел уникальное значение. Кроме того, в определениях столбцов первичного ключа должно быть указано, что они не могут содержать значения NULL .

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

1. столбец или столбцы создаваемой таблицы, которые образуют внешний ключ;

2. таблица, связь с которой создает внешний ключ. Это таблица-предок;

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

4. как СУБД должна обращаться со значениями NULL в одном или нескольких столбцах внешнего ключа при связывании его со строками таблицы-предка;

5. необязательное правило удаления для данного отношения (CASCADE, SET NULL, SET DEFAULT или NO ACTION), которое определяет действие, предпринимаемое при удалении строки-предка;

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

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

Например, создать таблицу Students.

CREATE TABLE Students (

StNo INT NOT NULL,

GrNo INT NOT NULL,

StName CHAR(30) NOT NULL,

CityNo INT,

PRIMARY KEY (StNo),

FOREIGN KEY Students_Groups(GrNo)

REFERENCES Groups,

FOREIGN KEY Students_Cities(CityNo)

REFERENCES Cities,

CHECK(CHAR_LENGTH(StName)>10)

)

11.4.2 Удаление таблицы (оператор DROP TABLE)

Таблицы можно удалить из базы данных посредством оператора DROP TABLE.

DROP TABLE имя_таблицы CASCADE | RESTRICT

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