Как и большинство современных реляционных языков, SQL основан на исчислении кортежей. В результате, каждый запрос, сформулированный с помощью исчисления кортежей (или иначе говоря, реляционной алгебры), может быть также сформулирован с помощью SQL. Однако, он имеет способности, лежащие за пределами реляционной алгебры или исчисления. Вот список некоторых дополнительных свойств, предоставленных SQL, которые не являются частью реляционной алгебры или исчисления:
- Команды вставки, удаления или изменения данных.
- Арифметические возможности: в SQL возможно вызвать арифметические операции, так же как и сравнения, например A < B + 3. Заметим, что + или других арифметических операторов нет ни в реляционной алгебре ни в реляционном исчислении.
- Команды присвоения и печати: возможно напечатать отношение, созданное запросом и присвоить вычисленному отношению имя отношения.
- Итоговые функции: такие операции как average, sum, max, и т.д. могут применяться к столбцам отношения для получения единичной величины.
Наиболее часто используемая команда SQL - это оператор SELECT, используемый для получения данных. Синтаксис:
SELECT [ALL|DISTINCT]
{ * | expr_1
[AS c_alias_1] [, ...
[, expr_k [AS c_alias_k]]]}
FROM table_name_1
[t_alias_1]
[, ... [, table_name_n
[t_alias_n]]]
[WHERE condition]
[GROUP BY name_of_attr_i
[,... [, name_of_attr_j
]] [HAVING condition]]
[{UNION [ALL] | INTERSECT | EXCEPT} SELECT ...]
[ORDER BY name_of_attr_i
[ASC|DESC] [, ... [, name_of_attr_j [ASC|DESC]]]];
Сейчас на различных примерах, мы покажем сложные выражения оператора SELECT. Таблицы, используемые в примерах, определены в Базе данных поставщиков и деталей.
Вот несколько простых примеров использования оператора SELECT:
Пример 2-4. Простой ограничивающий запрос
Получить все кортежи из таблицы PART, где атрибут PRICE больше 10:
SELECT * FROM PART
WHERE PRICE > 10;
Получаемая таблица:
PNO | PNAME | PRICE
-----+---------+--------
3 | Bolt | 15
4 | Cam | 25
Использовав "*" в операторе SELECT, получаем все атрибуты из таблицы. Если мы хотим получить только атрибуты PNAME и PRICE из таблицы PART, то используем следующее выражение:
SELECT PNAME, PRICE
FROM PART
WHERE PRICE > 10;
В этом случае получим:
PNAME | PRICE
--------+--------
Bolt | 15
Cam | 25
Заметим, что
SQL SELECT соответствует "проекции" в реляционной алгебре, а не "выборке" (подробней смотри
Реляционная алгебра).
Ограничения в операторе WHERE могут также быть логически соединены с помощью ключевых слов OR, AND, и NOT:
SELECT PNAME, PRICE
FROM PART
WHERE PNAME = 'Bolt' AND
(PRICE = 0 OR PRICE < 15);
приведёт к результату:
PNAME | PRICE
--------+--------
Bolt | 15
Арифметические операции могут использоваться в списке объектов и операторе WHERE. Например, если нам надо знать сколько будут стоить две штуки одной детали, то используем следующий запрос:
SELECT PNAME, PRICE * 2 AS DOUBLE
FROM PART
WHERE PRICE * 2 < 50;
и мы получим:
PNAME | DOUBLE
--------+---------
Screw | 20
Nut | 16
Bolt | 30
Заметим, что слово DOUBLE после ключевого слова AS - это новый заголовок второго столбца. Эта техника может быть использована для любого элемента списка объектов, для того чтобы задать новый заголовок столбцу результата. Этот новый заголовок часто называют псевдонимом. Псевдонимы не могут просто использоваться в запросе.
Следующий пример показывает, как осуществлять соединения в SQL.
Для объединения трёх таблиц SUPPLIER, PART и SELLS по их общим атрибутам, мы формулируем следующее выражение:
SELECT S.SNAME, P.PNAME
FROM SUPPLIER S, PART P, SELLS SE
WHERE S.SNO = SE.SNO AND
P.PNO = SE.PNO;
и получаем следующую таблицу в качестве результата:
SNAME | PNAME
-------+-------
Smith | Screw
Smith | Nut
Jones | Cam
Adams | Screw
Adams | Bolt
Blake | Nut
Blake | Bolt
Blake | Cam
В операторе FROM мы вводим псевдоним имени для каждого отношения, так как в отношениях есть общие названия атрибутов (SNO и PNO). Теперь мы можем различить общие имена атрибутов, просто предварив имя атрибута псевдонимом с точкой. Соединение вычисляется тем же путём, как показано во внутреннем соединением. Во-первых, определяется декартово произведение SUPPLIER × PART × SELLS. Потом выбираются только те кортежи, которые удовлетворяют условиям, заданным в операторе WHERE (т.е. где общие именованные атрибуты равны).Наконец, убираются все колонки кроме S.SNAME и P.PNAME.
SQL снабжён итоговыми операторами (например AVG, COUNT, SUM, MIN, MAX), которые принимают название атрибута в качестве аргумента. Значение итогового оператора высчитывается из всех значений заданного атрибута(столбца) всей таблицы. Если в запросе указана группа, то вычисления выполняются только над значениями группы (смотри следующий раздел).
Пример 2-5. Итоги
Если мы хотим узнать среднюю стоимость всех деталей в таблице PART, то используем следующий запрос:
SELECT AVG(PRICE) AS AVG_PRICE
FROM PART;
Результат:
AVG_PRICE
-----------
14.5
Если мы хотим узнать количество деталей, хранящихся в таблице PART, то используем выражение:
SELECT COUNT(PNO)
FROM PART;
и получим:
COUNT
-------
4
SQL позволяет разбить кортежи таблицы на группы. После этого итоговые операторы, описанные выше, могут применяться к группам (т.е. значение итогового оператора вычисляется не из всех значений указанного столбца, а над всеми значениями группы. Таким образом, итоговый оператор вычисляет индивидуально для каждой группы.)
Разбиение кортежей на группы выполняется с помощью ключевых слов GROUP BY и следующим за ними списком атрибутов, которые определяют группы.Если мы имеем GROUP BY A1, ⃛, Ak мы разделяем отношение на группы так, что два кортежа будут в одной группе, если у них соответствуют все атрибуты A1, ⃛, Ak.
Пример 2-6. Итоги
Если мы хотим узнать сколько деталей продаёт каждый поставщик, то мы так сформулируем запрос:
SELECT S.SNO, S.SNAME, COUNT(SE.PNO)
FROM SUPPLIER S, SELLS SE
WHERE S.SNO = SE.SNO
GROUP BY S.SNO, S.SNAME;
и получим:
SNO | SNAME | COUNT
-----+-------+-------
1 | Smith | 2
2 | Jones | 1
3 | Adams | 2
4 | Blake | 3
Теперь давайте посмотрим что здесь происходит. Во-первых, соединяются таблицы SUPPLIER и SELLS:
S.SNO | S.SNAME | SE.PNO
-------+---------+--------
1 | Smith | 1
1 | Smith | 2
2 | Jones | 4
3 | Adams | 1
3 | Adams | 3
4 | Blake | 2
4 | Blake | 3
4 | Blake | 4
Затем, мы разбиваем кортежи на группы, помещая все кортежи вместе, у которых соответствуют оба атрибута S.SNO и S.SNAME:
S.SNO | S.SNAME | SE.PNO
-------+---------+--------
1 | Smith | 1
| 2
--------------------------
2 | Jones | 4
--------------------------
3 | Adams | 1
| 3
--------------------------
4 | Blake | 2
| 3
| 4
В нашем примере мы получили четыре группы и теперь мы можем применить итоговый оператор COUNT для каждой группы для получения итогового результата запроса, данного выше.
Заметим, что для получения результата запроса, использующего GROUP BY и итоговых операторов, атрибуты сгруппированных значений должны также быть в списке объектов. Все остальные атрибуты, которых нет в выражении GROUP BY, могут быть выбраны при использовании итоговых функций. С другой стороны ты можешь не использовать итоговые функции на атрибутах, имеющихся в выражении GROUP BY.
Оператор HAVING выполняет ту же работу что и оператор WHERE, но принимает к рассмотрению только те группы, которые удовлетворяют определению оператора HAVING. Выражения в операторе HAVING должны вызывать итоговые функции. Каждое выражение, использующее только простые атрибуты, принадлежат оператору WHERE. С другой стороны каждое выражение вызывающее итоговую функцию должно помещаться в оператор HAVING.
Пример 2-7. Having
Если нас интересуют поставщики, продающие более одной детали, используем запрос:
SELECT S.SNO, S.SNAME, COUNT(SE.PNO)
FROM SUPPLIER S, SELLS SE
WHERE S.SNO = SE.SNO
GROUP BY S.SNO, S.SNAME
HAVING COUNT(SE.PNO) > 1;
и получим:
SNO | SNAME | COUNT
-----+-------+-------
1 | Smith | 2
3 | Adams | 2
4 | Blake | 3
В операторах WHERE и HAVING используются подзапросы (вложенные выборки), которые разрешены в любом месте, где ожидается значение. В этом случае значение должно быть получено предварительно подсчитав подзапрос. Использование подзапросов увеличивает выражающую мощность SQL.
Пример 2-8. Вложенная выборка
Если мы хотим узнать все детали, имеющие цену больше чем деталь 'Screw', то используем запрос:
SELECT *
FROM PART
WHERE PRICE > (SELECT PRICE FROM PART
WHERE PNAME='Screw');
Результат:
PNO | PNAME | PRICE
-----+---------+--------
3 | Bolt | 15
4 | Cam | 25
Если мы посмотрим на запрос выше, то увидим ключевое слово SELECT два раза. Первый начинает запрос - мы будем называть его внешним запросом SELECT - и второй в операторе WHERE, который начинает вложенный запрос - мы будем называть его внутренним запросом SELECT. Для каждого кортежа внешнего SELECT внутренний SELECT необходимо вычислить. После каждого вычисления мы узнаём цену кортежа с названием 'Screw' и мы можем проверить выше ли цена из текущего кортежа.
Если мы хотим узнать поставщиков, которые ничего не продают (например, чтобы удалить этих поставщиков из базы данных) используем:
SELECT *
FROM SUPPLIER S
WHERE NOT EXISTS
(SELECT * FROM SELLS SE
WHERE SE.SNO = S.SNO);
В нашем примере результат будет пустым, потому что каждый поставщик продаёт хотя бы одну деталь. Заметим, что мы использовали S.SNO из внешнего SELECT внутри оператора WHERE в внутреннем SELECT. Как описывалось выше подзапрос вычисляется для каждого кортежа из внешнего запроса т.е. значение для S.SNO всегда берётся из текущего кортежа внешнего SELECT.
Эти операции вычисляют объединение, пересечение и теоретико-множественное вычитание кортежей из двух подзапросов.
Пример 2-9. Объединение, пересечение, исключение
Следующий запрос - пример для UNION(объединение):
SELECT S.SNO, S.SNAME, S.CITY
FROM SUPPLIER S
WHERE S.SNAME = 'Jones'
UNION
SELECT S.SNO, S.SNAME, S.CITY
FROM SUPPLIER S
WHERE S.SNAME = 'Adams';
даёт результат:
SNO | SNAME | CITY
-----+-------+--------
2 | Jones | Paris
3 | Adams | Vienna
Вот пример для INTERSECT(пересечение):
SELECT S.SNO, S.SNAME, S.CITY
FROM SUPPLIER S
WHERE S.SNO > 1
INTERSECT
SELECT S.SNO, S.SNAME, S.CITY
FROM SUPPLIER S
WHERE S.SNO > 2;
даёт результат:
SNO | SNAME | CITY
-----+-------+--------
2 | Jones | Paris
Возвращаются только те кортежи, которые есть в обоих частях запроса
и имеют $SNO=2$.
Наконец, пример для EXCEPT(исключение):
SELECT S.SNO, S.SNAME, S.CITY
FROM SUPPLIER S
WHERE S.SNO > 1
EXCEPT
SELECT S.SNO, S.SNAME, S.CITY
FROM SUPPLIER S
WHERE S.SNO > 3;
даёт результат:
SNO | SNAME | CITY
-----+-------+--------
2 | Jones | Paris
3 | Adams | Vienna
Существует набор команд, использующихся для определения данных, включенных в язык SQL.
Самая основная команда определения данных - это та, которая создаёт новое отношение (новую таблицу). Синтаксис команды CREATE TABLE:
CREATE TABLE table_name
(name_of_attr_1
type_of_attr_1
[, name_of_attr_2
type_of_attr_2
[, ...]]);
Пример 2-10. Создание таблицы
Для создания таблиц, определённых в Базе данных поставщиков и деталей, используются следующие SQL выражения:
CREATE TABLE SUPPLIER
(SNO INTEGER,
SNAME VARCHAR(20),
CITY VARCHAR(20));
CREATE TABLE PART
(PNO INTEGER,
PNAME VARCHAR(20),
PRICE DECIMAL(4 , 2));
CREATE TABLE SELLS
(SNO INTEGER,
PNO INTEGER);
Вот список некоторых типов данных, которые поддерживает SQL:
- INTEGER: знаковое полнословное двоичное целое (31 бит для представления данных).
- SMALLINT: знаковое полсловное двоичное целое (15 бит для представления данных).
- DECIMAL (p
[,q]): знаковое упакованное десятичное число с
p знаками представления данных, с возможным
q знаками справа от десятичной точки.
(15 ≥ p ≥
qq ≥ 0).
Если q опущено, то предполагается что оно равно 0.
- FLOAT: знаковое двусловное число с плавающей точкой.
- CHAR(n): символьная строка с постоянной длиной
n.
- VARCHAR(n): символьная строка с изменяемой длиной, максимальная длина
n.
Индексы используются для ускорения доступа к отношению. Если отношение R проиндексировано по атрибуту A, то мы можем получить все кортежи tимеющие t(A) = a за время приблизительно пропорциональное числу таких кортежей t, в отличие от времени, пропорциональному размеру R.
Для создания индекса в SQL используется команда CREATE INDEX. Синтаксис:
CREATE INDEX index_name
ON table_name ( name_of_attribute );
Пример 2-11. Создание индекса
Для создания индекса с именем I по атрибуту SNAME отношения SUPPLIER используем следующее выражение:
CREATE INDEX I
ON SUPPLIER (SNAME);
Созданный индекс обслуживается автоматически, т.е. при вставке ново кортежа в отношение SUPPLIER, индекс I будет перестроен. Заметим, что пользователь может ощутить изменения в при существовании индекса только по увеличению скорости.
Представление можно рассматривать как виртуальную таблицу, т.е. таблицу, которая в базе данных не существует физически, но для пользователя она как-бы там есть. По сравнению, если мы говорим о базовой таблице, то мы имеем в виду таблицу, физически хранящую каждую строку где-то на физическом носителе.
Представления не имеют своих собственных, физически самостоятельных, различимых хранящихся данных. Вместо этого, система хранит определение представления (т.е. правила о доступе к физически хранящимся базовым таблицам в порядке претворения их в представление) где-то в системных каталогах (смотри Системные каталоги). Обсуждение различных технологий реализации представлений смотри в SIM98.
Для определения представлений в SQL используется команда CREATE VIEW. Синтаксис:
CREATE VIEW view_name
AS select_stmt
где
select_stmt, допустимое выражение выборки, как определено в
Выборка. Заметим, что
select_stmt не выполняется при создании представления. Оно только сохраняется в
системных каталогах и выполняется всякий раз когда делается запрос представления.
Пусть дано следующее определение представления (мы опять используем таблицы из Базы данных поставщиков и деталей):
CREATE VIEW London_Suppliers
AS SELECT S.SNAME, P.PNAME
FROM SUPPLIER S, PART P, SELLS SE
WHERE S.SNO = SE.SNO AND
P.PNO = SE.PNO AND
S.CITY = 'London';
Теперь мы можем использовать это виртуальное отношение London_Suppliers как если бы оно было ещё одной базовой таблицей:
SELECT *
FROM London_Suppliers
WHERE P.PNAME = 'Screw';
которое возвращает следующую таблицу:
SNAME | PNAME
-------+-------
Smith | Screw
Для вычисления этого результата система базы данных в начале выполняет скрытый доступ к базовым таблицам SUPPLIER, SELLS и PART. Это делается с помощью выполнения заданных запросов в определении представления к этим базовым таблицам. После, это дополнительное определедение (заданное в запросе к представлению) можно использовать для получения результирующей таблицы.
Для уничтожения таблицы (включая все кортежи, хранящиеся в этой таблице) используется команда DROP TABLE:
DROP TABLE table_name;
Для уничтожения таблицы SUPPLIER используется следующее выражение:
DROP TABLE SUPPLIER;
Команда DROP INDEX используется для уничтожения индекса:
DROP INDEX index_name;
Наконец, для уничтожения заданного представления используется
команда DROP VIEW:
DROP VIEW view_name;
После создания таблицы (смотри Создание таблицы), её можно заполнять кортежами с помощью команды INSERT INTO. Синтаксис:
INSERT INTO table_name (name_of_attr_1
[, name_of_attr_2 [,...]])
VALUES (val_attr_1
[, val_attr_2 [, ...]]);
Чтобы вставить первый кортеж в отношение SUPPLIER (из База данных поставщиков и деталей) мы используем следующее выражение:
INSERT INTO SUPPLIER (SNO, SNAME, CITY)
VALUES (1, 'Smith', 'London');
Чтобы вставить первый кортеж в отношение SELLS используется:
INSERT INTO SELLS (SNO, PNO)
VALUES (1, 1);
Для изменения одного или более значений атрибутов кортежей в отношении используется команда UPDATE. Синтаксис:
UPDATE table_name
SET name_of_attr_1 = value_1
[, ... [, name_of_attr_k = value_k]]
WHERE condition;
Чтобы изменить значение атрибута PRICE детали 'Screw' в отношении PART используется:
UPDATE PART
SET PRICE = 15
WHERE PNAME = 'Screw';
Новое значение атрибута PRICE кортежа, чьё имя равно 'Screw' теперь стало 15.
Для удаления кортежа из отдельной таблицы используется команда DELETE FROM. Синтаксис:
DELETE FROM table_name
WHERE condition;
Чтобы удалить поставщика называемого 'Smith', из таблицы SUPPLIER используем следующее выражение:
DELETE FROM SUPPLIER
WHERE SNAME = 'Smith';
В каждой системе базы данных SQL определены системные каталоги, которые используются для хранения записей о таблицах, представлениях, индексах и т.д. К системным каталогам также можно строить запросы, как если бы они были нормальными отношениями. Например, один каталог используется для определения представлений. В этом каталоге хранятся запросы об определении представлений. Всякий раз когда делается запрос к представлению, система сначала берёт запрос определения представления из этого каталога и материализует представление перед тем, как обработать запрос пользователя (подробное описание смотри в SIM98). Более полную информацию о системных каталогах смотри у Дейта.
В этом разделе мы опишем в общих чертах как SQL может быть встроен в конечный язык (например в C). Есть две главных причины, по которым мы хотим пользоваться SQL из конечного языка:
- Существуют запросы, которые нельзя сформулировать на чистом SQL(т.е. рекурсивные запросы). Чтобы выполнить такие запросы, нам необходим конечный язык, обладающий большей мощностью выразительности, чем SQL.
- Просто нам необходим доступ к базе данных из другого приложения, которое написано на конечном языке (например, система бронирования билетов с графическим интерфейсом пользователя написана на C и информация об оставшихся билетах хранится в базе данных, которую можно получить с помощью встроенного SQL).
Программа, использующая встроенный SQL в конечном языке, состоит из выражений конечного языка и выражений встроенного SQL (ESQL). Каждое выражение ESQL начинается с ключевых слов EXEC SQL. Выражения ESQL преобразуются в выражения на конечном языке с помощью прекомпилятора (который обычно вставляет вызовы библиотечных процедур, которые выполняют различные команды SQL).
Если мы посмотрим на все примеры из Выборка мы поймём, что результатом запроса очень часто являются множество кортежей. Большинство конечных языков не предназначено для работы с множествами, поэтому нам нужен механизм доступа к каждому отдельному кортежу из множества кортежей, возвращаемого выражением SELECT. Этот механизм можно предоставить, определив курсор. После этого, мы можем использовать команду FETCH для получения кортежа и установления курсора на следующий кортеж.
Подробней про встраивание SQL смотри у [Date and Darwen, 1997 год], [Дейта, 1994 год], or [Ullman, 1988 год].
