Язык SQL

Обучающее руководство по PostgreSQL
Предыдущий	Глава 2. SQL	Следующий

Язык SQL

Как и большинство современных реляционных языков, 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 A₁, ⃛, A_k мы разделяем отношение на группы так, что два кортежа будут в одной группе, если у них соответствуют все атрибуты A₁, ⃛, A_k.

Пример 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

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

Вот список некоторых типов данных, которые поддерживает 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 Index, Drop View

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

   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

В этом разделе мы опишем в общих чертах как 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 год].

Предыдущий	Начало	Следующий
Операции в реляционной модели данных	В начало главы	Архитектура