Язык SQL. Оператор Select

Фактическим стандартом доступа к реляционным данным стал язык SQL (Structured Query Language). Язык SQL представляет собой смесь операторов реляционной алгебры и выражений реляционного исчисления, использует синтаксис, близкий к фразам английского языка, и расширен дополнительными возможностями, отсутствующими в реляционной алгебре и реляционном исчислении.

Все СУБД, претендующие на название «реляционные», реализуют тот или иной диалект SQL. Многие нереляционные системы также имеют в настоящее время средства доступа к реляционным данным. Целью стандартизации является переносимость приложений между различными СУБД.

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

Язык SQL оперирует терминами, несколько отличающимися от терминов реляционной теории. Например, вместо «отношений» используются «таблицы», вместо «кортежей» — «строки», вместо «атрибутов» — «колонки» или «столбцы».

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

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

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

Можно выделить следующие группы:

• операторы DDL (Data Definition Language) — операторы определения объектов базы данных (их мы рассматривать не будем);
• операторы защиты и управления данными (их мы рассматривать не будем);
• операторы DML (Data Manipulation Language) — операторы манипулирования данными:
— SELECT — отобрать строки из таблиц;
— INSERT — добавить строки в таблицу;
— UPDATE — изменить строки в таблице;
— DELETE — удалить строки в таблице.

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

Наиболее важными для пользователя являются операторы манипулирования данными (DML), а наиболее часто встречаемым оператором среди них — оператор Select.

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

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

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

Будем называть отношения совместимыми по типу, если они имеют идентичные заголовки, а именно:

• отношения имеют одно и то же множество имен атрибутов, т. е. для любого атрибута в одном отношении найдется атрибут с таким же наименованием в другом отношении;
• атрибуты с одинаковыми именами определены на одних и тех же доменах.

Некоторые отношения не являются совместимыми по типу, но становятся

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

В MS Access запросы можно строить и на языке SQL. В этом языке оператор Select является фактически самым важным для пользователя и самым сложным оператором SQL. Он предназначен для выборки данных из таблиц, т. е. он, собственно, и реализует одно их основных назначений базы данных — предоставлять информацию пользователю.

Оператор Select всегда выполняется над некоторыми таблицами, входящими в базу данных. Здесь необходимо отметить, что на самом деле в базах данных могут быть не только постоянно хранимые таблицы, но и временные таблицы, и так называемые представления. Представления — это просто хранящиеся в базе данных запросы, т. е. Select-выражения. С точки зрения пользователей представления — это таблица, которая не хранится постоянно в базе данных, а «возникает» в момент обращения к ней. С точки зрения оператора Select и постоянно хранимые таблицы, и временные таблицы и представления выглядят совершенно одинаково.

Результатом выполнения оператора Select всегда является таблица. Таким образом, по результатам действий оператор Select похож на операторы реляционной алгебры. Любой оператор реляционной алгебры может быть выражен подходящим образом, сформулированным оператором Select. Необходимо обратить внимание и на то, что выполнение всех запросов в MS Access происходит в SQL.

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

SELECT [ALL/DISTINCT] <список атрибутовi>/*

FROM <список таблиц>

[WHERE <условие выборки>]

[ORDER BY <список атрибутов>]

[GROUP BY <список атрибутов>]

[HAVING <условие>]

[UNION <выражение с оператором SELECT>]

В квадратных скобках указываются элементы, которые могут в запросе отсутствовать.

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

Символ * означает, что в таблицу-результат включаются все атрибуты исходной таблицы.

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

В предложении с ключевым словом WHERE задаются условия выборки строк таблицы. В таблицу-результат включаются только те строки, для которых условие, указанное в предложении where, принимает значение «истина».

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

В предложении с ключевым словом GROUP BY задается список атрибутов группировки (разъяснение этого и последующего ключевого слова будет представлено немного позднее).

В предложении having задаются условия, накладываемые на каждую группу (отбора групп).

Отдельно отметим, что ключевые слова FROM, where, order by используются аналогичным образом и в других операторах манипулирования данными языка SQL.

Рассмотрим реализацию запросов для конкретного примера (рис. 5.4-7).

Рис. 5.4-7. ER-схема БД

Здесь три таблицы: студент (код студента, фамилия), оценки (код студента, код экзамена, оценка) и экзамен (код экзамена, предмет, дата).

student (id_st, surname)

exam_st (id_ex, subject, date)

mark_st (id_ex, id_st, mark)

Пример 5.4-1. Выдать список всех студентов.

SELECT * FROM student

ИЛИ

SELECT id_st, surname FROM student

Заметим, что если добавить к данному запросу предложение ORDER BY surname, то список будет упорядочен по фамилии. По умолчанию подразумевается, что сортировка производится по возрастанию. Если необходимо упорядочение по убыванию, после имени атрибута добавляется слово DESC.

Пример 5.4-2. Выдать список оценок, которые получил студент с кодом «1».

SELECT id_st, mark FROM mark_st Where id_st = 1

Пример 5.4-3. Выдать список кодов студентов, которые получили на экзаменах хотя бы одну двойку или тройку.

В предложении where можно записывать выражение с использованием арифметических операторов сравнения (<, >, и т. д.) и логических операторов (AND, OR, NOT), как и в обычных языках программирования.

SELECT id_st, mark FROM mark_st WHERE (mark>= 2 ) AND ( mark<= 3 )

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

IN — вхождение в некоторое множество значений;

BETWEEN — вхождение в некоторый диапазон значений;

LIKE — проверка на совпадение с образцом;

IS NULL — проверка на неопределенное значение.

Оператор IN используется для проверки вхождения в некоторое множество значений. Так, запрос

SELECT id_st, mark FROM mark_st WHERE mark IN (2,3)

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

Того же результата можно добиться, используя оператор BETWEEN:

SELECT id_st, mark FROM mark_st WHERE mark BETWEEN 2 AND 3

Пример 5.4-4. Выдать список всех студентов, фамилии которых начинаются с буквы А.

В этом случае удобно использовать оператор LIKE.

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

? — замешает любой одиночный символ;
* — замешает последовательность любого числа символов.

SELECT id_st, surname FROM student WHERE surname LIKE "A?"

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

MIN — минимальное значение в столбце;

МАХ — максимальное значение в столбце;

SUM — сумма значений в столбце;

AVG — среднее значение в столбце;

COUNT — количество значений в столбце, отличных от NULL.

Пример 5.4-5. Рассчитать среднее среди всех баллов, полученных студентами на экзаменах.

SELECT AVG (mark) FROM mark_st

Естественно, можно использовать агрегатные функции совместно с предложением WHERE:

SELECT AVG (mark) FROM mark st WHERE id st = 100

Данный запрос вычислит средний балл студента с кодом 100 по результатам всех сданных им экзаменов.

SELECT AVG (mark) FROM mark_st WHERE id_ex = 10

Данный запрос вычислит средний балл студентов по результатам сдачи экзамена с кодом 10.

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

Пример 5.4-6. Вычислить средний балл по всем экзаменам для каждого студента.

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

SELECT id_st, AVG (mark) FROM mark_st GROUP BY id_st

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

Пример 5.4-7. Вычислить средний балл по оценкам, полученным на экзамене с кодом 100, для каждого студента.

SELECT id_st,AVG(mark) FROM mark_st WHERE id_ex=100 GROUP BY id_st

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

Для того чтобы форматировать вывод, существуют различные возможности SQL.

Пример 5.4-8. Допустимым является включение текста в запрос. Рассмотрим пример того, как это делается:

SELECT "Средний балл=", AVG(mark) FROM mark_st WHERE id_ex = 10

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

В реляционных БД существуют три основные операции извлечения данных: выборка, проекция и слияние. Выборка извлекает поднабор строк, проекция извлекает поднабор столбцов, слияние объединяет данные из двух таблиц (для объединения данных из более двух таблиц используется больше одной операции слияния).

SQL выполняет все эти операции через одно выражение SELECT:

SELECT "Средний балл=", AVG(mark) FROM mark_st WHERE id_ex =10

Необходимыми элементами являются слова SELECT и FROM. Операция проекции осуществляется путем указания списка столбцов.

Запишем выражение SELECT, предназначенное для извлечения перечня книг с именами авторов и соответствующим количеством экземпляров:

SELECT автор, кол FROM книги;

Для отображения всех столбцов и строк необходимо записать:

SELECT * FROM книги;

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

Порядок отображения данных можно изменить, используя слово ORDER BY с указанием имени столбца, на основе данных которого они будут отсортированы в алфавитном порядке (по умолчанию):

SELECT автор, название FROM книги ORDER BY название;

Если вы хотите, чтобы строки располагались в порядке убывания, необходимо добавить слово DESC:

SELECT автор, название FROM книги ORDER BY название DESC;

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

SELECT автор, название FROM книги WHERE кол > 10;

В данном случае выбираются только те книги, количество экземпляров которых превышает 10:

SELECT автор, название FROM книги WHERE автор='Пушкин';

Здесь выбираются книги одного автора. Текстовые значения необходимо помещать в одинарные кавычки.

Слово Like позволяет выбирать строки, в которых совпадают некоторые символы:

SELECT автор, название FROM книги WHERE автор Like "П*";

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

SELECT автор, название FROM книги WHERE автор Like "П*" and кол<3;

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

В слове where можно использовать те же виды критериев, которые были описаны выше.

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

SELECT автор, Sum(количество) AS кол FROM книги GROUP BY автор;

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

Слово WHERE в операторе SELECT используется для ограничения количества строк, включенных в результаты запроса. Слово же HAVING выполняет похожую функцию, но для групп, а не для отдельных строк. К примеру, если мы хотим получить список всех авторов (если условно считать одной группой одного автора, у которого есть разные книги), у которых максимальное количество экземпляров какой-либо книги больше либо равно 5, то можно использовать следующий оператор:

SELECT автор, МАХ(количество) AS максимум FROM Книга

GROUP BY автор HAVING МАХ(количество)>=5;

Слово HAVING должно быть после слова GROUP BY. Слово HAVING ограничивает результаты на основе встроенной функции МАХ, а слово WHERE — на значениях отдельной строки. Запомните, что почти всегда, когда вы включаете в запрос слово HAVING, вам нужно использовать слово GROUP BY. Из результата (рис. 5.4-8) видно, что строки соответствуют группам.

Рис. 5.4-8. Результат группировки

При объединении данных из нескольких таблиц реляционные БД используют совпадающие значения первичных и внешних ключей для связи строки одной таблицы со строкой в другой таблице.

Объединение данных из нескольких таблиц в один результирующий набор называется операцией слияния.

В операторе Select при связывании таблиц сначала указывается левая связываемая таблица, затем тип связывания и затем правая таблица. Далее после ключевого слова ON указывается условие связывания.

Условие связывания задает логическое условие связывания двух таблиц. Чаше всего используется оператор равенства, например:

ON ЧИТАТЕЛИ.КодЧитателя = ВЫДАЧА.КодЧитателя

Выдаются только те строки (записи), у которых в таблицах ЧИТАТЕЛИ и ВЫДАЧА совпадают коды читателей.

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

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

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

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

Пример 5.4-9. Построить запросы для БД БИБЛИОТЕКА.

1. Выбрать книги, которые не выданы читателям.

Запрос, построенный средствами QBE, представлен на рис. 5.4-9.

Запрос, сконструированный средствами Access на языке SQL, представлен на рис. 5.4-10. Здесь и далее приводится вид запроса на SQL в формате, принятом в Access.