7.4. Управление данными

 

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

 

7.4.1. Извлечение данных – команда SELECT

 

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

Чаще всего используется упрощенный вариант команды SELECT, имеющий следующий синтаксис:

 

SELECT <Список_выбора>

[ INTO <Новая_таблица> ]

FROM <Исходная_таблица>

[ WHERE <Условие_отбора> ]

[ GROUPBY <Ключи_группировки> ]

[ HAVING <Условие_отбора> ]

[ ORDERBY <Ключи_сортировки> [ ASC | DESC ] ]

 

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

 

SELECT

INTO

FROM

WHERE

GROUP BY

HAVING

UNION

ORDER BY

COMPUTE

FOR

OPTION

 

7.4.1.1. Раздел SELECT

 

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

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

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

SELECT [ ALL | DISTINCT ]

[ TOP n [ PERCENT ] [ WITH TIES ] ]

<Список_выбора>

Рассмотрим назначение параметров.

 

Ключевые слова ALL | DISTINCT

 

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

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

 

                                            

а                                                                б

 

Рис. 7.4. Действие ключевых слов а - ALL и б – DISTINCT

 

Рассмотрим результат использования ключевых слов ALL и DISTINCT на примере выборки столбцов Семестр и Отчетность из таблицы «Учебный_план» базы данных «Сессия» (рис. 7.4). Сначала выполним запрос с указанием ключевого слова ALL:

 

SELECTALL Семестр, Отчетность

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

Фрагмент результата представлен на рис. 7.4 а.

 

Теперь заменим ключевое слово ALL на DISTINCT:

 

SELECTDISTINCTALLСеместр, Отчетность

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

 

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

 

Ключевоеслово TOP n [PERCENT] [WITH TIES]

 

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

Продемонстрируем использование ключевого слова ТОР:

 

SELECTTOP 5 * FROMСтуденты

 

В этом примере из таблицы Студенты базы данных «Сессия» было выбрано 5 первых строк:

 

 

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

 

SELECTTOP 10 PERCENT * FROMСтуденты

 

Всего в таблице было 115 строк, следовательно, 10% будет составлять 11,5 строк. В результате будут выданы 12 строк:

 

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

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

 

SELECT TOP 10 PERCENT * FROM Студенты ORDER BY Номер_Группы

 

В результате выполнения такого запроса будут выданы следующие 12 строк:

 

 

При указании вместе с предложением ORDERBY ключевого слова WITHTIES в результат будут включены еще и строки, совпадающие по значению колонки сортировки с последними выведенными строками запроса SELECTTOPn [PERCENT].

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

 

SELECT TOP 10 PERCENT WITH TIES *

FROM Студенты

 

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

 

Предложение <Список_выбора>

Синтаксис предложения <Список_выбора>следующий:

<Список_выбора> ::=

{ *

| { <Имя_таблицы> | <Псевдоним_таблицы> }.*

| { <Имя_столбца> | <Выражение> }

[ [ AS ] <Псевдоним_столбца>]

| <Псевдоним_столбца> = <Выражение>

} [ ,...,n ]

 

Символ «*» означает включение в результат всех столбцов, имеющихся в списке таблиц раздела FROM.

Если в результат не нужно включать все столбцы всех таблиц, то можно явно указать имя объекта, из которого необходимо выбрать все столбцы (<Имя_таблицы>.* или <Псевдоним_таблицы>.*).

Отдельный столбец таблицы в результат выборки включается явным указанием имени столбца (параметр <Имя_столбца>). Столбец должен принадлежать одной из таблиц, указанных в разделе FROM. Если столбец с указанным именем имеется более чем в одном источнике данных, перечисленных в разделе FROM, то необходимо явно указать имя источника данных, к которому принадлежит столбец в формате <Имя_таблицы>.<Имя_столбца>.  В противном случае будет выдано сообщение об ошибке.

Например, попробуем выбрать данные из столбца ID_Дисциплина, который имеется в таблицах «Дисциплина» и «Учебный_план»:

SELECTID_Дисциплина, Наименование, Семестр

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

 

В ответ будет выдано сообщение об ошибке, указывающее на некорректное использование имени  ‘ID_Дисциплина'.

 

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

SELECT Дисциплина.ID_Дисциплина, Наименование, Семестр

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

 

Столбцам, возвращаемым как результат выполнения запроса, могут быть присвоены псевдонимы. Псевдонимы позволяют изменить имя исходного столбца или поименовать столбец, содержимое которого получено как результат вычисления выражения. Имя псевдонима указывается с помощью параметра [AS] <Псевдоним_столбца>. Ключевое слова AS необязательно при задании псевдонима. В общем случае сервер не требует уникальности имен столбцов результата выборки, поэтому разные столбцы могут иметь одинаковые имена или псевдонимы.

Столбцы в результате выборки могут быть не только копией столбца одной из исходных таблиц, но и формироваться на основе вычисления выражения. Такой столбец в списке выбора задается с помощью конструкции <Выражение> [[AS] <Псевдоним_столбца>]. Выражение при этом может содержать константы, имена столбцов, функции, а также их комбинации. Дополнительно столбцу, формируемому на основе вычисления выражения, можно присвоить псевдоним, указав его с помощью параметра [AS] <Псевдоним_столбца>. По умолчанию вычисляемый столбец не имеет имени.

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

Рассмотрим следующий пример. Пусть для таблицы «Студенты» необходимо построить запрос, представляющий фамилию, имя и отчество в одной колонке. Используя операцию конкатенации (сложения) символьных строк и значение ФИО в качестве псевдонима столбца, построим запрос:

 

SELECTTOP 10 Фамилия + ' ' + Имя + ' ' + Отчество as ФИО, Номер_Группы

FROM Студенты

 

Результат запроса имеет следующий вид:

 

 

7.4.1.2. Раздел FROM

 

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

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

FROM { <Источник_данных> } [,...,n]

 

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

<Источник_данных> ::= <имя_таблицы> [ [AS] <псевдоним_таблцы>]|

                                               <связка_таблиц>

 

С помощью параметра <имя_таблицы> указывается имя обычной таблицы. Параметр <псевдоним_таблицы> используется для присвоения таблице псевдонима, под которым на нее нужно будет ссылаться в запросе. Часто псевдонимы таблиц применяют, чтобы ссылку на нужную таблицу сделать более удобной и короткой. Например, если в запросе часто упоминается имя таблицы «Учебный_план», то можно воспользоваться псевдонимом, например, tpl. Указание ключевого слова AS не является при этом обязательным.

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

 

<связка_таблиц> ::= <левая_таблица> <тип_связывания> <правая_таблица>

ON <условие_связывания>

 

Конструкция <тип_связывания> описывает тип связывания двух таблиц. Исходная таблица указывается слева от конструкции <тип_связывания> (<левая_таблица>), а справа указывается зависимая таблица (<правая_таблица>).

Общий синтаксис конструкции <тип_связывания> следующий:

 

<тип_связывания> ::= [INNER | {{LEFT | RIGHT| FULL } [OUTER] } ] JOIN

 

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

Конструкция ON<условие_связывания> задает логическое условие связывания двух таблиц. Допустимы операторы сравнения (например, =, <, >, <=, >=, !-, <>). Чаще всего используется оператор равенства, например:

 

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

 

В этом примере устанавливается связь между таблицами «Учебный_план» и «Дисциплина» по столбцу ID_Дисциплина, имеющемуся в каждой из таблиц.

 

Ключевое слово INNER

 

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

В приведенном ниже примере выполняется выборка данных из таблиц «Дисциплины» и «Учебный_план» с помощью запроса SELECT. Таблицы связаны по ключевому полю ID_Дисциплина, имеющемуся в каждой из них. Для каждой строки таблицы «Учебный_план» ищется строка с совпадающим значением поля ID_Дисциплина в таблице «Дисциплины». Все строки таблицы «Учебный_план», для которых нет строк с соответствующим значением поля ID_Дисциплина, игнорируются и не включаются в конечный результат. Аналогично, не включаются в результат все строки таблицы «Дисциплины», для которых нет соответствующей строки в таблице «Учебный_план» (что, однако, невозможно для данного примера, так как столбец ID_Дисциплина таблицы «Учебный_план» связан внешним ключом со столбцом ID_Дисциплина таблицы «Дисциплины»).

 

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

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

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

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

 

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

 

 

Ключевое слово LEFT [OUTER]

 

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

 

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

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

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

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

 

Будет возвращен следующий набор строк:

 

 

Как видно, по сравнению с использованием ключевого слова INNER, в результат запроса добавлена строка из таблицы «Дисциплины», которая удовлетворяет сформулированному условию отбора, но для которой не существует соответствующей строки в таблице «Учебный_план». В столбцах Семестр и Отчетность (относящихся к таблице «Учебный_план») для этих строк установлено значение NULL.

 

Ключевое слово RIGHT [OUTER]

 

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

 

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

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

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

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

 

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

 

 

Ключевое слово FULL [OUTER]

 

При использовании ключевого слова FULL в результат будут включены все строки как правой, так и левой таблицы. Применение ключевого слова FULL [OUTER] можно рассматривать как одновременное применение ключевых слов LEFT [OUTER] и RIGHT[OUTER].

 

7.4.1.3. Раздел WHERE

 

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

В общем случае логическое выражение содержит имена столбцов таблиц, с которыми работает запрос. Для каждой строки, возвращенной запросом, вычисляется логическое выражение путем подстановки вместо имен столбцов конкретных значений из соответствующей строки. Если при вычислении выражения возвращается значение TRUE, то есть выражение истинно, то строка будет включена в конечный результат. В противном случае строка в результат не включается. При необходимости можно указать более одного логического выражения, объединив их с помощью логических операторов OR и AND.

Рассмотрим синтаксис раздела WHERE.

 

WHERE <условие_отбора>

| <имя_столбца> {= | *= | =*} <имя_столбца>

 

В конструкции <условие_отбора> можно определить любое логическое условие, при выполнении которого строка будет включена в результат. Хотя и было сказано, что обычно логическое условие содержит имена столбцов, оно может быть и произвольным, в том числе и совсем не связанным с данными. Например, в следующей команде условие WHERE никогда не выполнится и ни одна строка не будет возвращена:

 

SELECT * FROMДисциплиныWHERE 3=5

 

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

 

SELECT Фамилия, Имя, Отчество, Номер_Группы, Год_поступления

FROM Студенты

WHERE Год_поступления < 2000

 

В результате будет возвращен список всех студентов, поступивших на факультет ранее 2000 года:

 

 

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

 

Оператор BETWEEN

 

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

 

<выражение> [NOT] BETWEEN <начало_диапазона> AND <конец_диапазона>

 

<Выражение> задает проверяемую величину, а аргументы <начало_диапазона> и <конец_диапазона> определяют возможные границы ее изменения. Использование оператора NOT совместно с оператором BETWEEN позволяет задать диапазон, вне которого может изменяться проверяемая величина.

При выполнении оператор BETWEEN преобразуется в конструкцию из двух операций сравнения:

 

(<выражение>>=<начало_диапазона>)AND(<выражение><=<конец_диапазона>)

 

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

 

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

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

Дисциплины ON

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

WHERE Количество_часов BETWEEN 50 AND 100

 

В результате выполнения инструкции получим список дисциплин учебного плана с количеством часов от 50 до 100:

 

 

Оператор IN

 

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

 

<выражение> [NOT] IN (<выражение1>,…,<выражениеN>)

 

<Выражение> указывает проверяемую величину, а аргументы <выражение1>,…, <выражениеN> задают перечислением через запятую набор значений, которые может принимать проверяемая величина. Ключевое слово NOT выполняет логическое отрицание.

Рассмотрим пример применения оператора IN.

 

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

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

Дисциплины ON

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

WHERE Наименование IN ('Английский язык', 'Физическая культура')

 

В результате выполнения инструкции получим строки учебного плана для дисциплин «Английский язык» и «Физическая культура»:

 

 

Оператор LIKE

 

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

 

<Символьное_выражение> [NOT] LIKE <образец>

 

<Образец> задает символьный шаблон для сравнения и заключается в кавычки. Шаблон может содержать символы-разделители. Допускается использование следующих символов-разделителей (табл. 7.2):

 

Таблица 7.2.

 

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

 

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

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

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

 

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

 

 

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

 

Связывание таблиц

 

Раздел WHERE может быть использован для связывания таблиц. В этом случае условие связывания должно присоединяться к логическому выражению с помощью логической операции AND (логическое умножение).

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

 

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

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

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

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

 

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

 

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

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

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

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

 

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

 

 

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

 

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

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

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

 

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

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

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

 

 

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

 

SELECT TOP 10 *

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

 

 

SELECT TOP 10 *

FROM Дисциплины

 

 

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

 

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

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

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

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

 

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

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

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

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

 

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

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

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

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

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

 

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

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

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

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

 

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

 

7.4.1.4. Раздел ORDERBY

 

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

 

SELECT TOP 10 *

FROM Дисциплины

ORDER BY Наименование

 

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

 

 

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

 

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

 

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

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

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

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

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

 

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

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

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

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

 

Будет возвращен следующий набор строк:

 

 

Добавим в раздел SELECT столбец Отчетность и получим пример сортировки по трем столбцам:

 

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

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

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

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

 

Будет возвращен следующий набор строк:

 

 

7.4.1.5. Раздел GROUPBY

 

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

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

 

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

 

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

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

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

 

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

 

SELECT Семестр, SUM(Количество_часов) AS 'Нагрузка'

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

GROUPBY Семестр

 

В результате выполнения запроса будет получен следующий результат:

 

 

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

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

 

SELECTСеместр, COUNT(*) AS 'Экзамены'

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

WHERE Отчетность = 'э'

GROUPBY Семестр

 

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

 

 

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

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

 

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

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

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

GROUPBY Семестр

 

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

 

 

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

Добавим в раздел GROUPBY ключевое слово ALL:

 

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

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

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

GROUPBYALL Семестр

 

Будет получен следующий результат:

 

 

7.4.1.6. Раздел COMPUTE

 

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

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

 

COMPUTE <Функция_агрегирования>(<столбец_агрегирования>)}[,..., n]

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

 

Аргумент <столбец_агрегирования>должен содержать имя агрегируемого столбца. Этот столбец должен быть включен в результат выборки. Ключевое слово BY указывает, что результат вычисления следует сгруппировать. Следуемый за этим ключевым словом аргумент <столбец_группировки> содержит имя столбца, по которому будет производиться группировка. Результат необходимо предварительно отсортировать по этому столбцу, то есть столбец должен быть указан в разделе ORDERBY. Приведем простой пример применения раздела COMPUTE для вычисления количества дисциплин, читаемых в семестре, и общей суммы часов:

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

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

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

         (Семестр = 2)

COMPUTESUM(Количество_часов), COUNT(Семестр)

 

Будет получен следующий результат:

 

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

---------------------------- ----------- --------------------

Английский язык                   2           110

Физическая культура               2           130

Философия                         2           32

Экономическая теория              2           34

История мировых цивилизаций       2           20

Математический анализ             2           56

Основы алгебры и геометрии        2           52

Основы программирования           2           26

 

                                              sum

                                              ==========

                                              460

 

                                  cnt

                                  ==========

                                  8

 

(9 row(s) affected)

 

Рассмотрим пример группировки при использовании раздела COMPUTE (составление списков групп и вычисление количества студентов в группе):

 

SELECT Фамилия, Имя, Отчество, Номер_Группы

FROM Студенты

ORDERBY Номер_Группы

COMPUTECOUNT(Номер_Группы) BY Номер_Группы

 

Будет получен следующий результат:

 

Фамилия             Имя          Отчество      Номер_Группы

------------- --------------- ----------------- ------------

Агапов              Иван         Иванович            2002\1

Агуреев             Дмитрий      Александрович       2002\1

Гогешвили           Серго        Тамазович           2002\1

Григорьева          Мария        Александровна       2002\1

Желтов              Олег         Игоревич            2002\1

Жуков               Виктор       Владимирович        2002\1

Жучков              Сергей       Сергеевич           2002\1

Захаркин            Николай      Владимирович        2002\1

Иванов              Олег         Геннадиевич         2002\1

Кадаков             Антон        Дмитриевич          2002\1

Леонтьев            Богдан       Вадимович           2002\1

Миняйло             Евгений      Николаевич          2002\1

Нечаева             Ольга        Николаевна          2002\1

Парфенова           Светлана     Витальевна          2002\1

Потапкин            Александр    Александрович       2002\1

Соловьева           Полина       Сергеевна           2002\1

Федянин             Александр    Алексеевич          2002\1

 

                                                     cnt

                                                     =======

                                                     17

 

 

Фамилия             Имя         Отчество        Номер_Группы

------------- -------------- ---------------- ---------------

Алексеев            Иван         Александрович       2002\2

Белова              Ирина        Владимировна        2002\2

Бородкина           Анна         Михайловна          2002\2

Братченко           Елена        Анатольевна         2002\2

Волков              Иван         Александрович       2002\2

Гончаров            Иван         Андреевич           2002\2

Калинин             Андрей       Евгеньевич          2002\2

Кондрашкина         Ольга        Игоревна            2002\2

Ларина              Евгения      Валерьевна          2002\2

Малкова             Дарья        Дмитриевна          2002\2

Поспелов            Игорь        Григорьевич         2002\2

Тюрина              Юлия         Александровна       2002\2

Филоненко           Петр         Алексеевич          2002\2

Юртанов             Сергей       Михайлович          2002\2

 

                                                     cnt

                                                     ========

                                                     14

 

(33 row(s) affected)

 

7.4.1.7. РазделUNION

 

Раздел UNION служит для объединения результатов выборки, возвращаемых двумя и более запросами.

Рассмотрим синтаксис раздела UNION:

 

<Спецификация_Запроса_1>

UNION [ALL]

<Спецификация_Запроса_2>

…

[UNION [ALL]]

<Спецификация_Запроса_ n>

 

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

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

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

ни один из результатов не может быть отсортирован с помощью раздела ORDERBY (однако общий результат может быть отсортирован, как будет показано ниже)

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

Продемонстрируем применение раздела UNION. Рассмотрим таблицы «Кадровый_Состав» и «Студенты» и попробуем построить, например, общий список и учащихся, и преподавателей, номер телефона которых начинается на 120.

Сначала построим запрос для таблицы «Кадровый_Состав»:

 

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

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

WHERE Телефон LIKE '120%'

 

Результат действия запроса следующий:

 

 

Затем построим запрос для таблицы «Студенты»:

 

SELECT Фамилия, Имя, Отчество, Телефон

FROM Студенты

WHERE Телефон LIKE '120%'

 

В результате выполнения запроса получим следующую выборку:

 

 

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

 

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

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

WHERE Телефон LIKE '120%'

UNION

SELECT Фамилия, Имя, Отчество, Новый_столбец = 'Студент', Телефон

FROM Студенты

WHERE Телефон LIKE '120%'

 

После выполнения запроса получим следующую таблицу:

 

 

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

Упорядочим полученный список по алфавиту, добавив предложение ORDERBY:

 

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

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

WHERE Телефон LIKE '120%'

UNION

SELECT Фамилия, Имя, Отчество, Новый_столбец = 'Студент', Телефон

FROM Студенты

WHERE Телефон LIKE '120%'

ORDERBYФамилия

 

Получим следующий результат:

 

 

 

7.4.1.8. Раздел INTO. Использование команды SELECT...INTO

 

При указании этой конструкции результат выполнения запроса будет сохранен в новой таблице. Синтаксис раздела INTO следующий:

 

INTO <имя_новой_таблицы>

 

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

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

Синтаксис команды SELECT...INTO следующий:

 

SELECT {<имя_столбца> [[AS] <псевдоним_столбца>] [, ..., n] }

INTO <имя_новой_таблицы> FROM {<имя_исходной_таблицы> [,..., n]}

 

Приведенный вариант синтаксиса далеко не исчерпывает все возможности вставки данных с помощью команды SELECT...INTO. Допускаются практическивсе варианты синтаксиса запроса SELECT, то есть можно выполнять группировку, сортировку, объ­единение и т. д.

Рассмотрим назначение аргументов команды.

<имя_столбца> [[AS] <псевдоним_столбца>]. Аргумент <имя_столбца> задает имя столбца таблицы, который будет включен в результат. Указанный столбец должен принадлежать одной из таблиц, перечисленных в списке FROM {<имя_исходной_таблицы> [,..., n]}. Если столбцы, принадлежащие разным таблицам, имеют одинаковые имена, то для столбцов необходимо использовать псевдонимы. В противном случае произойдет попытка создать таблицу со столбцами, имеющими одинаковые имена, что приведет к ошибке, и выполнение запроса будет прервано. Указание псевдонимов также обязательно для столбцов, значения в которых формируются на основе вычисления выражений (по умолчанию такие столбцы не имеют никакого имени, что недопустимо для таблицы) и когда пользователь хо­чет задать столбцам в создаваемой таблице новые имена (отличные от ис­ходных). Имя псевдонима задается с помощью параметра <псевдоним_колонки>.

INTO <имя_новой_таблицы>. Аргумент <имя_новой_таблицы> содержит имя создаваемой таблицы. Это имя должно быть уникальным в пределах базы данных.

FROM {<имя_исходной_таблицы> [,..., n]}. В простейшем случае конструкция FROM содержит список исходных таблиц. В более сложных запросах с помощью этой конструкции определяются условия связывания двух и более таблиц.

С помощью команды SELECT...INTO, например, можно разделить таблицу «Студенты» на две, выделив в отдельную таблицу «Контакты» адреса и телефоны, а затем удалив эти столбцы из таблицы «Студенты»:

 

SELECTID_Студент, Адрес, Телефон

INTO Контакты

FROM Студенты

 

Будет создана новая таблица со следующей структурой:

 

 

Запрос для таблицы «Контакты»:

 

SELECT *

FROM Контакты

WHERE Телефон LIKE '120%'

 

Выдает следующий результат:

 

 

Построим внешний ключ для таблицы «Контакты», обеспечив связь с таблицей «Студенты»:

 

ALTER TABLE Контакта

ADD CONSTRAINT FK_Контакт

FOREIGN KEY (ID_Студент)

REFERENCES Студенты

 

Модифицируем запрос для таблицы «Контакты»:

 

SELECT *

FROM Студенты INNER JOIN

    КонтактыON

    Студенты.ID_Студент = Контакты.ID_Студент

WHERE Телефон LIKE '120%'

 

 

 

К оглавлению

Назад к разделу "7.3.3. Удаление таблиц – команда DROP TABLE"

Вперед к разделу "7.4.2. Добавление данных – команда INSERT"