Основные операторы языка SQL. Интерактивный SQL

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

12.1. Общее представление об основных операторах языка SQL

Как уже отмечалось в лекции 11, все операторы языка SQL разделяются на три составные части: DDL – язык определения данных, DCL – язык управления данными, DML – язык обработки данных.

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

Операторы разграничения доступа пользователей к объектам базы данных (DCL).

GRANT – создание в системе безопасности записи, разрешающей пользователю работать с данными или выполнять определенные операции SQL.

DENY - создание в системе безопасности записи, запрещающей доступ для определенной учетной записи.

Операторы определения данных (язык DDL).

Соответствующие операторы предназначены для создания, удаления, изменения основных объектов модели данных реляционных СУБД: таблиц, представлений, индексов.

CREATE TABLE <имя> - создание новой таблицы в базе данных.

DROP TABLE <имя> - удаление таблицы из базы данных.

ALTER TABLE <имя> - изменение структуры существующей таблицы или ограничений целостности, задаваемых для данной таблицы.

При выполнении аналогичных операций с представлениями или индексами в указанных операторах вместо служебного слова TABLE записывается слово VIEW (представление) или слово INDEX (индекс)

Операторы манипулирования данными (язык DML).

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

SELECT – выборка строк, удовлетворяющих заданным условиям. Оператор реализует, в частности, такие операции реляционной алгебры как "селекция" и "проекция".

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

INSERT – вставка новых строк в таблицу.

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

12.2 Интерактивный режим работы с SQL (интерактивный SQL)

Соответствующий режим предусматривает непосредственную работу пользователя с базой данных по следующему алгоритму: используя прикладную программу (клиентское приложение) или стандартную утилиту, входящую в СУБД, пользователь:

Текст запроса поступает в СУБД, которая:

Схема взаимодействия пользователя и СУБД с использованием интерактивного SQL приводится на рис 12.1.

12.3. Использование языка SQL для выбора информации из таблицы

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

SELECT [ALL/DISTINCT] <список атрибутов>/*
FROM <список таблиц>
[WHERE <условие выборки>]
[ORDER BY <список атрибутов>]
[GROUP BY <список атрибутов>]
[HAVING <условие>]
[UNION<выражение с оператором SELECT>]

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

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

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

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

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

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

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

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

Рассмотрим реализацию запросов для конкретного примера, представленного в )

Выдать список всех студентов.

SELECT * 
FROM student

или

SELECT id_st, surname 
FROM student

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

Выдать список оценок, которые получил студент с кодом "1".

SELECT id_st, mark 
FROM mark_st
WHERE id_st = 1

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

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

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

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

Оператор 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

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

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

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

_ (символ подчеркивания) – замещает любой одиночный символ;

% (знак процента) – замещает последовательность любого числа символов.

SELECT id_st, surname
FROM student
WHERE surname LIKE 'А%'

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

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

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, предназначенная для указания того столбца, по значениям которого будет производиться группировка. Так, например, мы можем вычислить средний балл по всем экзаменам для каждого студента. Для этого достаточно выполнить следующий запрос:

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

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

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

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

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

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

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

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

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

12.4. Использование SQL для выбора информации из нескольких таблиц

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

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

Еще раз вернемся к примеру из .).

В этом примере тоже присутствуют связанные таблицы. Рассмотрим таблицы student, mark_st и exam_st.

Таблица mark_st связана с таблицей exam_st по полю id_ex.

Таблица mark_st связана с таблицей student по полю id_st.

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

SELECT student.surname, mark_st.id_ex, mark_st.mark
FROM student, mark_st
WHERE  student.id_st = mark_st.id_st

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

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

SELECT E.surname, M.id_ex, M.mark
FROM student E, mark_st M
WHERE E.id_st = M. id_st

12.5. Использование SQL для вставки, редактирования и удаления данных в таблицах

Для добавления данных в таблицу в стандарте SQL предусмотрена команда INSERT. Рассмотрим ряд примеров запросов.

INSERT INTO mark_st
VALUES (1, 2, 5)

Данный запрос вставляет в таблицу mark_st строку, содержащую значения, перечисленные в списке VALUES. Если не нужно указывать значение какого-то поля, можно присвоить ему NULL:

INSERT INTO mark 
VALUES (1, 2, NULL)

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

INSERT INTO mark_st (id_st, id_ex)
VALUES (1, 2)

Для удаления данных из таблицы существует команда DELETE:

DELETE  
FROM student

Этот запрос удаляет все данные из таблицы student.

Можно ограничить диапазон удаляемой информации следующим образом:

DELETE  
FROM student
WHERE surname > 'И'

Для обновления данных используется команда UPDATE.

UPDATE 	mark_st 
SET mark = '5'
WHERE id_st = 100 AND id_ex = 10

При помощи этого запроса изменится на "5" оценка у студента с кодом 100 по экзамену с кодом 10.

12.5. Язык SQL и операции реляционной алгебры

Язык SQL является средством выражения мощного математического аппарата теории множеств и реляционной алгебры. В данном разделе рассматривается связь операторов языка SQL с операциями реляционной алгебры и теории множеств.

Операция объединения

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

SELECT *
FROM A
UNION
SELECT *
FROM B

Операция разности

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

SELECT *
FROM A
EXCEPT
SELECT *
FROM B

Операция проекции

SELECT Fieldi1, ..., Fieldin
FROM A

Операция выборки (селекции)

SELECT *
FROM A
WHERE (<condition>)

Операция пересечения

SELECT *
FROM A
INTERSECT
SELECT *
FROM B

Операция соединения, эквисоединения

Если $$\Theta$$ – операция "=", то это эквисоединение.

Операция естественного соединения

Пусть есть отношения A(X1, ..., Xn, A1, ..., Am) и B(X1, ..., Xn, B1, ..., Br).

SELECT A.X1, ..., A.Xn, A.A1, ..., A.Am, B.B1, ..., B.Br
FROM A, B
WHERE (A.X1 = B.X1) AND ... AND (A.Xn = B.Xn)

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

Более подробно материалы лекции рассматриваются в [-].