Л. Партыка, И. И. Попов системы управления базами данных

Здесь <образец> задает символьный шаблон для сравнения и заключается в кавычки. Шаблон может содержать символы-разделители. Допускается использование символов-разделителей, приведенных в табл. 4.2.

SELECT Фамилия, Имя, Отчество, Должность

FROM Кадровый_состав

WHERE Должность LIKE '%пр%'

Результат выполнения инструкции представлен на рис. 4.16.

Рис. 4.16. Применение шаблона при поиске. Выбраны профессоры и преподаватели

Применение образца для значения столбца Должность в данном случае позволило отобрать строки со значениями «Ст.преп.» и «Проф.».

Рассмотрим пример, уточняющий один из представленных выше:

SELECT Наименование, Семестр, Количество_часов

FROM Учебный_план INNER JOIN Дисциплины

ON Учебный_план.ID_Дисциплина =

Дисциплины.ID_Дисциплина

WHERE (Количество_часов > 60) AND (Семестр = 1)

Перенесем условие связывания в логическое выражение:

SELECT Наименование, Семестр, Количество_часов

FROM Учебный_план, Дисциплины

WHERE (Учебный план.ID_Дисциплина =

Дисциплины.ID_Дисциплина) AND (Количество часов > 60)

AND (Семестр = 1)

Рис. 4.17. Пример связывания таблиц. Дисциплины первого семестра

Результат выполнения обоих запросов одинаков (рис. 4.17).

Использование только условия связывания в разделе where аналогично связыванию ключевым словом inner в разделе FROM. Например, результаты следующих запросов одинаковы (рис. 4.18):

SELECT TOP 10 Наименование, Семестр, Количество часов FROM Учебный_план. Дисциплины WHERE (Учебный_план . ID_Дисциплина = Дисциплины.ID_Дисциплина)

SELECT TOP 10 Наименование, Семестр, Количество часов FROM Учебный_план INNER JOIN Дисциплины ON (Учебный_план.ID_Дисциплина = Дисциплины.ID_Дисциплина)

Рис. 4.18. Два запроса дают одинаковый результат

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

SELECT TOP 10 *

FROM Учебный_план (рис. 4.19);

SELECT TOP 10 *

Рис. 4.19. Первые 10 строк из таблицы Учебныйплан

FROM Дисциплины (рис. 4.20).

Рис. 4.20. Первые 10 строк из таблицы Дисциплины

Аналогом использования ключевых слов left outer join является указание в разделе where условия с помощью символов *=. Приведенные примеры возвращают одинаковые наборы строк:

SELECT Наименование, Семестр, Отчетность FROM Дисциплины LEFT OUTER JOIN Учебный_план ON Учебный_план.ID_Дисциплина = Дисциплины.ID_Дисциплина WHERE (Наименование LIKE '%информатик%')

SELECT Наименование, Семестр, Отчетность

FROM Дисциплины, Учебный план

WHERE (Учебный_план.ID_Дисциплина *=

Дисциплины.ID_Дисциплина)

AND (Наименование LIKE '%информатик%')

Аналогом использования ключевых слов right outer join является указание условия с помощью символов =*. Приведенные примеры возвращают одинаковый набор данных:

SELECT Отчетность, Семестр, Наименование

FROM Учебный_план RIGHT OUTER JOIN Дисциплины

ON Учебный_план.ID_Дисциплина =

Дисциплины.ID_Дисциплина

WHERE (Наименование LIKE '%информатик%')

SELECT Отчетность, Семестр, Наименование

FROM Учебный план, Дисциплины

WHERE (Учебный_план.ID_Дисциплина =*

Дисциплины.ID_Дисциплина)

AND (Наименование LIKE '%информатик%')

Следует отметить, что при использовании специальных ключевых слов inner | {LEFT | RIGHT | FULL } [OUTER ] данные представляются по-иному, чем при указании условия where. Скорость выполнения запроса в первом случае оказывается выше, поскольку организуется связывание данных, тогда как при использовании конструкции where происходит их фильтрация. При выполнении запросов на небольших наборах данных это не играет существенной роли, поэтому удобнее обращаться к конструкции where из-за наглядности и простоты синтаксиса этого варианта, но при построении сложных запросов, выполняющих обработку тысяч строк, все же лучше использовать конструкцию связывания.

Раздел ORDER BY

Раздел предназначен для упорядочения набора данных, возвращаемого после выполнения запроса. Рассмотрим пример упорядочения данных таблицы Дисциплины ПО столбцу Наименование в алфавитном порядке:

SELECT TOP 10 * FROM Дисциплины ORDER BY Наименование

Результат сортировки представлен на рис. 4.21.

Рис. 4.21. Пример сортировки записей. Первые 10 дисциплин расположены в алфавитном порядке

Полный синтаксис раздела order by следующий:

ORDER BY {<условие_сортировки> [ ASC | DESC ] } [, ...,n]

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

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

Раздел order by разрешает использование ключевых слов ASC и DESC, с помощью которых можно явно указать, каким образом следует упорядочить строки. При указании ключевого слова ASC данные будут отсортированы по возрастанию. Если необходимо отсортировать данные по убыванию, указывается ключевое слово DESC. По умолчанию используется сортировка по возрастанию.

Данные можно отсортировать по нескольким столбцам. Для этого необходимо ввести имена столбцов через запятую по порядку сортировки. Сначала данные сортируются по столбцу, имя которого было указано первым в разделе order by. Затем, если имеется множество строк с одинаковыми значениями в первом столбце, выполняется дополнительная сортировка этих строк по второму столбцу (внутри группы с одинаковым значением в первом столбце) и т. д.

Приведем пример сортировки по двум столбцам:

SELECT TOP 2 0 Наименование, Семестр, Количество_часов

FROM Учебный план, Дисциплины

WHERE (Учебный план.ID_Дисциплина =

Дисциплины.ID_Дисциплина)

ORDER BY Семестр, Количество_часов DESC

Будет возвращен следующий набор строк (рис. 4.22). Добавим в раздел select столбец Отчетность и получим пример сортировки по трем столбцам:

SELECT TOP 20 Наименование, Семестр, Количество_часов,

Отчетность

FROM Учебный план, Дисциплины

WHERE (Учебный_план.ID_Дисциплина =

Дисциплины.ID_Дисциплина)

ORDER BY Семестр, Отчетность, Количество часов

Будет возвращен набор строк, приведенный на рис 4.23.

Рис. 4.22. Сортировка по двум столбцам — сначала по семестрам, а затем в порядке убывания количества часов

Рис. 4.23. Сортировка по трем столбцам — семестрам, количеству часов и формам отчетности

Раздел GROUP BY

Раздел group by позволяет выполнять группировку строк таблиц по определенным критериям. Для каждой группы можно выполнить специальные функции агрегирования, которые применяются ко всем строкам в группе. Одним из примеров использования раздела group by является суммирование однотипных значений.

Синтаксис раздела group by следующий:

GROUP BY [ALL] <условие_группировки> [,...,n]

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

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

Функции агрегирования позволяют выполнять статистическую обработку данных, подсчитывая количество, сумму, среднее значение и другие величины для всего набора данных. Во многих функциях агрегирования допускается использование ключевых слов all и distinct. Ключевое слово all выполняет агрегирование всех строк исходного набора данных. При указании ключевого слова distinct будет выполняться агрегирование только уникальных строк. Все повторяющиеся строки будут проигнорированы. По умолчанию выполняется агрегирование всех строк, т. е. используется ключевое слово all. Далее приведены описания некоторых функций агрегирования.

AVG(). Эта функция вычисляет среднее значение для указан­ного столбца. Функция имеет следующий синтаксис:

AVG([ALL | DISTINCT] <выражение>)

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

SELECT AVG (Количество_часов) FROM Учебный_план

Результат запроса:

41

(1   row(s)   affected)

SELECT Семестр, AVG(Количество_часов) FROM Учебный_план GROUP BY Семестр

Результат:

Семестр

        2      54

46

39

37

34

44

(9 row(s) affected)

COUNT({[ALL | DISTINCT] <выражение>] | *})

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

Пример использования функции count:

SELECT   COUNT(*)   AS   'Всего   сотрудников¹, COUNT(Телефон)   AS   'С  домашним  телефоном' FROM  Кадровый_состав

Этот запрос подсчитывает общее количество строк в таблице, а также количество ненулевых значение в столбце Телефон.

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

Всего   сотрудников   С  домашним  телефоном

14           10

(1   row(s)   affected) Warning:   Null  value  eliminated  from  aggregate

Пример использования функции Count () при выполнении группировки:

SELECT   Должность,    COUNT(*) FROM  Кадровый_состав GROUP   BY  Должность

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

Должность

Ассистент            3              .               ^г

Доцент  4

Зав.каф.                2

Проф.    3

Ст.преп.                2

(5 row(s) affected)

MAX([ALL | DISTINCT] <выражение>)

Пример использования функции:

SELECT MAX(Количество_часов), MАX(Количество_часов/2) FROM Учебный_план

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

1   140  7 0

(1   row(s)   affected)

min(). Функция возвращает минимальное значение в указанном диапазоне. Синтаксис функции следующий:

MIN([ALL | DISTINCT] <выражение>)

Пример использования функции:

SELECT MIN(Количество_часов) FROM Учебный_план

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

1   12

Синтаксис функции следующий:

SUM([ALL | DISTINCT] <выражение>)

В качестве примера просто суммируем значения в столбце

Количество_часов:

SELECT SUМ(Количество_часов), COUNT(*),

SUM(Количество_часов)/COUNT(*), AVG(Количество_часов)

FROM Учебный_план

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

3 694        89          41            41

(1 row(s) affected)

Теперь вновь обратимся к разделу select и приведем пример группировки значений таблицы Учебный_план. Произведем группировку строк по семестрам (столбец Семестр) и подсчитаем общую нагрузку в часах за каждый семестр:

SELECT Семестр, SUM(Количество_часов) AS 'Нагрузка' FROM [Учебный_план] GROUP BY Семестр

В результате выполнения запроса будет получен результат (рис. 4.24, а).

Рис. 4.24. Группировка по семестрам: а — подсчет общей нагрузки в часах; б — подсчет количества экзаменов

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

SUM была получена сумма значений столбца Количество_часов. Функции агрегирования работают со всеми строками группы, возвращая единственное значение для всех этих строк.

Рассмотрим теперь запрос, подсчитывающий количество экзаменов в каждом семестре:

SELECT Семестр, COUNT(*) AS 'Экзамены' FROM [Учебный план] WHERE Отчетность = 'э' GROUP BY Семестр

Результат выполнения запроса приведен на рис. 4.24, б.

Предложение группировки может содержать ключевое слово all. Назначение этого слова следующее. Нередко при выполнении группировки используется раздел where, т. е. группировка должна выполняться не над всеми строками, а лишь над определенной частью строк. Результатом такого подхода может явиться то, что одна или более групп не будет содержать ни одной строки. Если группа не содержит ни одной строки, то по умолчанию эта группа не включается в результат выборки. Однако в некоторых ситуациях все же требуется, чтобы были выведены все группы, в том числе и не содержащие ни одной строки. Для этого и необходимо указывать в разделе group by ключевое слово all. В этом случае будет выводиться список всех групп, но для групп, не содержащих строк, не будут выполняться функции агрегирования.

Рассмотрим это на примере. Для начала выполним группировку без использования ключевого слова all, но с вертикальной фильтрацией (с помощью раздела where) — в таблице Учебный_план подсчитаем для каждого семестра количество дисциплин с нагрузкой более 60 часов:

SELECT Семестр, COUNT(*) AS 'Количество часов > 60'

FROM [Учебный_план]

WHERE Количество_часов > 60

GROUP BY Семестр

Результат запроса приводится на рис. 4.25, а. В полученной таблице отсутствуют данные для 5, 6 и 7-го семестров. Это означает, что дисциплин, удовлетворяющих поставленному условию, в семестрах нет.

Рис. 4.25. Действие ключевого слова all: