Язык SQL

4.6.5.DML: Команды модификации данных.

К этой группе относятся операторы добавления, изменения и удаления записей.

Добавить новую запись в таблицу:

INSERT INTO <имя_таблицы> [ (<имя_столбца>,<имя_столбца>,...) ]
                                VALUES (<значение>,<значение>,..)

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

     INSERT INTO publishers VALUES (16,"Microsoft Press","http://www.microsoft.com");

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

     INSERT INTO publishers (publisher,pub_id) 
            VALUES ("Super Computer Publishing",17);

Модификация записей:

     UPDATE <имя_таблицы> SET <имя_столбца>=<значение>,...
         [WHERE <условие>]

Если задано ключевое слово WHERE и условие, то команда UPDATE применяется только к тем записям, для которых оно выполняется. Если условие не задано, UPDATE применяется ко всем записям. Пример:

     UPDATE publishers SET url="http://www.superpub.com" WHERE pub_id=17;

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

операции сравнения: > , < , >= , <= , = , <> , != . В SQL эти операции могут применяться не только к числовым значениям, но и к строкам ( "<" означает раньше, а ">" позже в алфавитном порядке) и датам ( "<" раньше и ">" позже в хронологическом порядке).
оперции проверки поля на значение NULL: IS NULL, IS NOT NULL
операции проверки на вхождение в диапазон: BETWEEN и NOT BETWEEN.
операции проверки на вхождение в список: IN и NOT IN
операции проверки на вхождение подстроки: LIKE и NOT LIKE
отдельные операции соединяются связями AND, OR, NOT и группируются с помощью скобок.

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

     UPDATE publishers SET url="url not defined" WHERE url IS NULL;

Эта команда находит в таблице publishers все неопределенные значения столбца url и заменяет их строкой "url not defined".

Удаление записей

    DELETE FROM <имя_таблицы> [ WHERE <условие> ]

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

    DELETE FROM publishers WHERE publisher = "Super Computer Publishing";

Эта команда удаляет запись об издательстве Super Computer Publishing.

4.6.6.DML: Выборка данных.

Для извлечения записей из таблиц в SQL определен оператор SELECT. С помощью этой команды осуществляется не только операция реляционной алгебры "выборка" (горизонтальное подмножество), но и предварительное соединение (join) двух и более таблиц. Это наиболее сложное и мощное средство SQL, полный синтаксис оператора SELECT имеет вид:

      SELECT [ALL | DISTINCT] <список_выбора>
           FROM <имя_таблицы>, ...
           [ WHERE <условие> ]
           [ GROUP BY <имя_столбца>,... ]
              [ HAVING <условие> ]
           [ORDER BY <имя_столбца> [ASC | DESC],... ]

Порядок предложений в операторе SELECT должен строго соблюдаться (например, GROUP BY должно всегда предшествовать ORDER BY), иначе это приведет к появлению ошибок.

Мы начнем рассмотрение SELECT с наиболее простых его форм. Все примеры, приведенные ниже, касающиеся базы данных publications, можно выполнить самостоятельно, зайдя на эту страничку, поэтому результаты запросов здесь не приводятся.

Этот оператор всегда начинается с ключевого слова SELECT. В кострукции <список_выбора> определяется столбец или столбцы, включаемые в результат. Он может состоять из имен одного или нескольких столбцов, или из одного символа * (звездочка), определяющего все столбцы. Элементы списка разделяются запятыми.

Пример: получить список всех авторов

        SELECT author FROM authors;

получить список всех полей таблицы authors:

        SELECT * FROM authors;

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

        SELECT title FROM titles WHERE yearpub > 1996;

Допустим теперь, что нам надо найти все публикации за интервал 1995 - 1997 гг. Это условие можно записать в виде:

        SELECT title FROM titles WHERE yearpub>=1995 AND yearpub<=1997;

Другой вариант этой команды можно получить с использованием логической операции проверки на вхождение в интервал:

        SELECT title FROM titles WHERE yearpub BETWEEN 1995 AND 1997;

При использовании конструкции NOT BETWEEN находятся все строки, не входящие в указанный диапазон.

Еще один вариант этой команды можно построить с помощью логической операции проверки на вхождение в список:

SELECT title FROM titles WHERE yearpub IN (1995,1996,1997);

Здесь мы задали в явном виде список интересующих нас значений. Конструкция NOT IN позволяет найти строки, не удовлетворяющие условиям, перечисленным в списке.

Наиболее полно преимущества ключевого слова IN проявляются во вложенных запросах, также называемых подзапросами. Предположим, нам нужно найти все издания, выпущенные компанией "Oracle Press". Наименования издательских компаний содержатся в таблице publishers, названия книг в таблице titles. Ключевое слово NOT IN позволяет объединить обе таблицы (без получения общего отношения) и извлечь при этом нужную информацию:


  SELECT title FROM titles WHERE pub_id IN
     (SELECT pub_id FROM publishers WHERE publisher='Oracle Press');

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

Некоторые задачи нельзя решить с использованием только операторов сравнения. Например, мы хоти найти web-site издательтва "Wiley", но не знаем его точного наименования. Для решения этой задачи предназначено ключевое слово LIKE, его синтаксис имеет вид:

   WHERE <имя_столбца> LIKE <образец> [ ESCAPE <ключевой_символ> ]

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

% (знак процента) - заменяет любое количество символов
_ (подчеркивание) - заменяет одиночный символ.

Попробуем найти искомый web-site:

SELECT publiser, url FROM publishers WHERE publisher LIKE '%Wiley%';

В соотвествии с шаблоном СУБД найдет все строки включающие в себя подстроку "Wiley". Другой пример: найти все книги, название которых начинается со слова "SQL":

       SELECT title FROM titles WHERE title LIKE 'SQL%';

В том случае, когда надо найти значение, которое само содержит один из символов шаблона, используют ключевое слово ESCAPE и <ключевой_символ>. Литерал, следующий в шаблоне после ключевого символа, рассматривается как обычный символ, все последующие символы имеют обычное значение. Например, нам надо найти ссылку на web-страницу, о которой известно, что в ее url содержится подстрока "my_works":

      SELECT site, url FROM wwwsites WHERE url LIKE '%my@_works%' ESCAPE '@';

В заключение заметим, что при выполнении оператора SELECT результирующее отношение может иметь несколько записей с одинаковыми значениями всех полей. Чтобы исключить повторяющиеся записи из выборки используется ключевое слово DISTINCT. Ключевое слово ALL указывает, что в результат необходимо включать все строки.

4.6.7.DML: Выборка из нескольких таблиц.

Очень часто возникает ситуация, когда выборку данных надо производить из отношения, которое является результатом слияния (join) двух других отношений. Например, нам нужно получить из базы данных publications информацию о всех печатных изданиях в виде следующей таблицы:

------------------------------------------------
|название_книги | год_выпуска | издательство   |
------------------------------------------------
|               |             |                |
|               |             |                |

Для этого СУБД предварительно должна выполнить слияние таблиц titles и publishers, а только затем произвести выборку из полученного отношения.

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

    SELECT titles.title,titles.yearpub,publishers.publisher
        FROM titles,publishers
        WHERE titles.pub_id=publishers.pub_id;

А вот пример, где одновременно задаются условия и слияния, и выборки (результат предыдущего запроса ограничивается изданиями после 1996 года):

    SELECT titles.title,titles.yearpub,publishers.publisher
        FROM titles,publishers
        WHERE titles.pub_id=publishers.pub_id AND
              titles.yearpub>1996;

Следует обратить внимание на то, что когда в разных таблицах присутствуют одноименные поля, то для устранения неоднозначности перед именем поля указывается имя таблицы и знак "." (точка). (Хорошее правило: имя таблицы указывать всегда!)

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


 SELECT authors.author,titles.title,titles.yearpub,publishers.publisher
   FROM titles,publishers,titleauthors
   WHERE titleauthors.au_id=authors.au_id AND
         titleauthors.title_id=titles.title_id AND
         titles.pub_id=publishers.pub_id AND
         titles.yearpub > 1996;

4.6.8.DML: Вычисления внутри SELECT.

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

     SELECT title, yearpub-1992 FROM titles WHERE yearpub > 1992;

В арифметических вражения допускаются операции сложения (+), вычитания (-), деления (/), умножения (*), а также различные функции (COS, SIN, ABS - абсолютное значение и т.д.). Также в запрос можно добавить строковую константу:

     SELECT 'the title of the book is', title, yearpub-1992 
            FROM titles WHERE yearpub > 1992;

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

AVG(<имя поля>) - среднее по всем значениям данного поля
COUNT(<имя поля>) или COUNT (*) - число записей
MAX(<имя поля>) - максимальное из всех значений данного поля
MIN(<имя поля>) - минимальное из всех значений данного поля
SUM(<имя поля>) - сумма всех значений данного поля

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


           SELECT MIN(yearpub) FROM titles;

подсчитать количество книг в нашей базе данных:


           SELECT COUNT(*) FROM titles;

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


  SELECT COUNT(*) FROM titles WHERE title LIKE '%SQL%';

4.6.9.DML: Групировка данных.

Группировка данных в операторе SELECT осуществляется с помощью ключевого слова GROUP BY и ключевого слова HAVING, с помощью которого задаются условия разбиения записей на группы.

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


      SELECT publishers.publisher, count(titles.title)
          FROM titles,publishers
          WHERE titles.pub_id=publishers.pub_id
          GROUP BY publisher;

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


      SELECT publishers.publisher, count(titles.title)
         FROM titles,publishers
         WHERE titles.pub_id=publishers.pub_id
         GROUP BY publisher
           HAVING COUNT(*)>1;

Другой вариант использования HAVING - включить в результат только те издательтва, название которых оканчивается на подстроку "Press":


    SELECT publishers.publisher, count(titles.title)
         FROM titles,publishers
         WHERE titles.pub_id=publishers.pub_id
         GROUP BY publisher
            HAVING publisher LIKE '%Press';

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

4.6.10.DML: Cортировка данных.

Для сортировки данных, получаемых при помощи оператора SELECT служит ключевое слово ORDER BY. С его помощью можно сортировать результаты по любому столбцу или выражению, указанному в <списке_выбора>. Данные могут быть упорядочены как по возрастанию, так и по убыванию. Пример: сортировать список авторов по алфавиту:

SELECT author FROM authors ORDER BY author;

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


 SELECT authors.author,titles.title,titles.yearpub,publishers.publisher
       FROM authors,titles,publishers,titleauthors
       WHERE titleauthors.au_id=authors.au_id AND
             titleauthors.title_id=titles.title_id AND
              titles.pub_id=publishers.pub_id 
       ORDER BY authors.author ASC, titles.yearpub DESC;

Ключевое слово DESC задает здесь обратный порядок сортировки по полю yearpub, ключевое слов ASC (его можно опускать) - прямой порядок сортировки по полю author.

4.6.11.DML: Операция объединения.

В SQL предусмотрена возможность выполнения операции реляционной алгебры "ОБЪЕДИНЕНИЕ" (UNION) над отношениями, являющимися результатами оператора SELECT. Естественно, эти отношения должны быть определены по одной схеме.Пример: получить все Интеренет-ссылки, хранимые в базе данных publications. Эти ссылки хранятся в таблицах publishers и wwwsites. Для того, чтобы получить их в одной таблице, мы должны построить следующие запрос:


       SELECT publisher,url FROM publishers
       UNION
       SELECT site,url FROM wwwsites;

4.6.12.Использование представлений.

До сих пор мы говорили о таблицах, которые реально хранятся в базе данных. Это, так называемые, базовые таблицы (base tables). Существует другой вид таблиц, получивший название "представления" (иногда их называют"представляемые таблицы").

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

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

Представление определяется с помощью команды


      CREATE VIEW <имя_представления> [<имя_столбца>,...]
             AS <запрос>

При этом должны соблюдаться следующие ограничения:

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

Создадим представление, хранящее информацию об авторах, их книгах и издателях этих книг:


 CREATE VIEW books AS
 SELECT authors.author,titles.title,titles.yearpub,publishers.publisher
   FROM authors,titles,publishers,titleauthors
   WHERE titleauthors.au_id=authors.au_id AND
         titleauthors.title_id=titles.title_id AND
         titles.pub_id=publishers.pub_id

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


   SELECT titles FROM books WHERE author LIKE '%Date'

   SELECT author,count(title) FROM books GROUP BY author

(Права пользователей на доступ в представлениям назначаются также с помощью команд GRANT / REVOKE.)

Из приведенного выше примера достаточно ясен смысл использования представлений. Если запросы типа "выбрать все книги данного автора с указанием издательств" выполняются достаточно часто, то создание представляемой таблицы books значительно сократит накладные расходы на выполнение соединеия четырех базовых таблиц authors, titles, publishers и titleauthors. Кроме того, в представлении может быть представлена информация, явно не хранимая ни в одной из базовых таблиц. Например, один из столбцов представления может быть вычисляемым:


      CREATE VIEW amount (publisher, books_count) AS
      SELECT publishers.publisher, count(titles.title)
          FROM titles,publishers
          WHERE titles.pub_id=publishers.pub_id
          GROUP BY publisher;

Здесь использована еще одна, ранее не описанная, возможность SQL - присвоение новых имен столбцам представления. В приведенном примере число изданий, осуществленных каждым издатетлем, будет хранится в столбце с именем books_count. Заметим, что если мы хотим присвоить новые имена столбцам представления, нужно указывать имена для всех столбцов. Тип данных столбца представления и его нулевой статус всегда зависят от того, как он был определен в базовой таблице (таблицах).

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

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

Удаление представления производится с помощью оператора:


    DROP VIEW <имя_представления>

4.6.13.Другие возможности SQL.

Описываемые ниже возможности пока не стандартизованы, но представлены в той или иной мере практически во всех современных СУБД.

Хранимые процедуры. Практический опыт создания приложений обработки данных показывает, что ряд операций над данными, реализующих общую для всех пользователей логику и не связанных с пользовательским интерфейсом, целесообразно вынести на сервер. Однако, для написания процедур, реализующих эти операции стандартных возможностей SQL не достаточно, поскольку здесь необходимы операторы обработки ветвлений, циклов и т.д. Поэтому многие поставщики СУБД предлагают собственные процедурные расширения SQL (PL/SQL компании Oracle и т.д.). Эти расширения содержат логические операторы (IF ... THEN ... ELSE), операторы перехода по условию (SWITCH ... CASE ...), операторы циклов (FOR, WHILE, UNTIL) и операторы предачи управления в процедуры (CALL, RETURN). С помощью этих средств создаются функциональные модули, которые хранятся на сервере вместе с базой данных. Обычно такие модули называют хранимые процедуры. Они могут быть вызваны с передачей параметров любым пользователем, имеющим на то соотвествующие права. В некоторых системах хранимые процедуры могут быть реализованы и в виде внешних по отношению к СУБД модулей на языках общего назначения, таких как C или Pascal. Пример для СУБД PostgreSQL:
```
          CREATE FUNCTION <имя_функции> ([<тип_параметра1>,...<тип_параметра2>])
              RETURNS <возвращаемые_типы>
              AS [ <SQL_оператор> | <имя_объектного_модуля> ]
              LANGUAGE 'SQL' | 'C' | 'internal'
```
Вызов созданной функции осуществялется из оператора SELECT (также, как вызываются функции агрегирования). Более подробно о хранимых процедурах см. статью Э.Айзенберга Новый стандарт хранимых процедур в языке SQL, СУБД N 5-6, 1996 г.
Триггеры. Для каждой таблицы может быть назначена хранимая процедура без параметров, которая вызывается при выполнении оператора модификации этой таблицы (INSERT, UPDATE, DELETE). Такие хранимые процедуры получили название триггеров. Триггеры выполняются автоматически, независимо от того, что именно является причиной модификации данных - действия человека оператора или прикладной программы. "Усредненный" синтаксис оператора создания триггера:
```
           CREATE TRIGGER <имя_триггера>
                    ON <имя_таблицы>
                    FOR { INSERT | UPDATE | DELETE }
                         [, INSERT | UPDATE | DELETE ] ...
                    AS <SQL_оператор>
```
Ключевое слово ON задает имя таблицы, для которой определяется триггер, ключевое слово FOR указывает какая команда (команды) модификации данных активирует триггер. Операторы SQL после ключевого слова AS описывают действия, которые выполняет триггер и условия выполнения этих действий. Здесь может быть перечислено любое число операторов SQL, вызовов хранимых процедур и т.д. Использование триггеров очень удобно для выполнения операций контроля ограничений целостности (см. главу 4.3).
Мониторы событий. Ряд СУБД допускает создание таких хранимых процедур, которые непрерывно сканируют одну или несколько таблиц на предмет обнаружения тех или иных событий (например, среднее значение какого-либо столбца достигает заданного предела). В случае наступления события может быть инициирован запуск триггера, хранимой процедуры, внешнего модуля и т.п. Пример: пусть наша база данных является частью автоматизированной системы управления технологическим процессом. В поле одной из таблиц заносятся показания датчика температуры, установленного на резце токарного станка. Когда это значение превышает заданный предел, запускается внешняя программа, изменяющая параметры работы станка.

Введение в базы данных. (c) Зеленков Ю.А. (yz@yars.free.net) 1997 г. (c) Центр Интернет ЯрГУ