Основные возможности языка структурированных запросов (SQL)

Сразу заметим, что в этой небольшой книге мы не ставим задачу дать описание SQL, а всего лишь приведем обзор основных команд и на нескольких примерах покажем возможности SQL-запросов. Что касается точного описания инструкций SQL, то это описание не совсем совпадает для различных СУБД. Наиболее известные серверы реляционных баз данных (Oracle, MS SQL Server, Cybase, Informix и некоторые другие) поддерживают ту или иную версию или уровень стандарта ANSI / ISO SQL (ANSI – Американский национальный институт стандартов, ISO – Международная организация стандартизации), но для повышения эффективности каждый коммерческий SQL-сервер применяет некоторые расширения стандарта. Приведенные ниже примеры проверены в рамках СУБД Access. Кстати, справочная система Access достаточно полно разъясняет инструкции SQL. Ясное и полное описание технологии клиент/сервер, включающее также классификацию и описание команд SQL, применительно к СУБД Oracle 7 приведено в [14].

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

Можно выделить несколько классов команд SQL: определения данных, манипулирования данными, управления транзакциями, управления данными (доступом к ним). Последние два класса инструкций применяются в запросах к SQL-серверам, а первые два класса – и в запросах к локальным СУБД.

Команды языка определения данных (DDL – Data Definition Language) – это команды SQL, которые можно использовать для создания (CREATE), изменения (ALTER) и удаления (DROP) различных объектов базы данных (таблиц, процедур и др.). В качестве примера рассмотрим команду, с помощью которой можно создать таблицу Объекты [КодОб, ИмяОб]:

CREATE TABLE Объекты (КодОб LONG CONSTRAINT obj PRIMARY KEY, ИмяОб TEXT(40) )

В этой инструкции CREATE TABLE указывается имя создаваемой таблицы (Объекты) и затем в скобках, через запятую описываются поля записей. Поле КодОб имеет тип LONG (см. раздел 3), а после слова CONSTRAINT (ограничение) указывается имя индекса (obj) для этого поля и параметр PRIMARY KEY – ведь поле КодОб должно быть первичным ключом. Описание второго поля таблицы включает его имя (ИмяОб), тип (TEXT) и длину в байтах (40). Пример показывает, что при хорошем знании синтаксиса языка SQL структуру любой таблицы базы данных можно создать не только с помощью имеющихся в Access (но не в каждой СУБД) средств визуального конструирования, но и с помощью команды SQL.

К числу команд языка манипулирования данными (DML) относятся инструкции SELECT (отбор данных), INSERT (вставка записей в таблицу), UPDATE (обновление записей), DELETE (удаление записей). Приведем пример инструкции SELECT.

SELECT Затраты.[Код затр], Объекты.Объект, Затраты.Дата, Затраты.Стоимость

FROM Затраты, Организации, Работы, Объекты

WHERE Объекты.[Код об] = Затраты.[Код об]

AND Затраты.Дата >= #7/01/2009# AND Затраты.Дата < #1/1/2010#

ORDER BY Затраты.[Код затр]

В этом примере создается виртуальная таблица, состав полей которой определен во фразе SELECT, до фразы FROM: поле Код затр из таблицы Затраты, поле Объект из таблицы Объекты и т. д. Имя таблицы и имя поля указываются через точку. Имя поля Код затр заключено в квадратные скобки, так как содержит пробел. В некоторых СУБД для ограничения имен с пробелами применяются апострофы. Поэтому удобнее при конструировании таблиц вместо пробелов в именах полей использовать подчерк.

Во фразе FROM указываются имена исходных таблиц.

Во фразе WHERE задаются условия отбора записей. Так, из таблицы Затраты отбираются только те записи, в которых дата больше (позже) или равна 01.07.2009 и меньше (раньше) 01.01.2010 (даты в текстах запросов задаются в формате #мм/дд/гг#). Для того чтобы к записи из таблицы Затраты «привязать» через код объекта соответствующие записи из таблицы Объекты, во фразе WHERE заданы еще условия равенства этих кодов. Все условия заданы через И (AND), поскольку в этом примере требуется выполнение и первого, и второго, и третьего условий. В некоторых запросах условия задаются через ИЛИ (OR) – тогда для отбора записи требуется выполнение или первого, или второго условия.

Во фразе ORDER BY указано поле, по которому надо упорядочить (отсортировать) записи в виртуальной таблице результатов. Эта таблица не хранится в базе данных, а создается только в оперативном порядке, например, когда запрос является источником данных для формирования отчета.

На рис. 9.6 показан результат запроса, содержащего рассмотренную инструкцию SELECT.

Рис. 9.6. Результат запроса, содержащего инструкцию SELECT

Приведем также примеры инструкции DELETE:

DELETE * FROM Объекты – удаление всех записей из таблицы Объекты,

DELETE * FROM Организации WHERE Город IN (‘Париж’, ’Ницца’, ’Чикаго’) – удаление из таблицы Организации записей, соответствующих тем организациям, которые расположены в городах, входящих в указанное множество. Если в таблице таких записей не окажется, то ничего удалено не будет.

К числу команд управления транзакциями относятся следующие:

COMMIT – сохранить (зафиксировать) внесенные текущей транзакцией изменения,

ROLLBACK – отменить изменения, вносимые текущей транзакцией,

SAVEPOINT – назначить промежуточную точку сохранения.

Промежуточные точки позволяют разбить выполняемую транзакцией работу на фрагменты:

INSERT…

DELETE…

SAVEPOINT one

UPDATE…

DELETE…

SAVEPOINT two

INSERT…

UPDATE…

Используя точки сохранения, можно отменить одни части транзакции и оставить другие:

ROLLBACK TO SAVEPOINT two.

Эта команда отменит действие всех SQL-инструкций после точки сохранения two.

Команды языка управления данными (DCL) используются для управления доступом пользователей к базе данных. Эти команды, как и инструкции управления транзакциями, применяются при работе с SQL-серверами. Например, в СУБД Oracle 7 [14] введено понятие ролей. Каждая роль имеет имя и определенный набор полномочий: право создавать или удалять таблицы, изменять определенные таблицы и т. п. Если пользователь знает пароль некоторой роли, например, роли с именем role7, то он может установить эту роль в текущем сеансе и тем самым получить полномочия этой роли:

SET ROLE role7 IDENTIFIED BY x77y – устанавливается роль role7 с паролем x77y.

Кроме обширного набора команд языка SQL, серверы реляционных баз данных предоставляют средства для создания процедур и триггеров базы данных (триггер – особый тип процедур – см. далее). Так, Oracle 7 предлагает для SQL процедурные языковые расширения, называемые PL/SQL. Сам SQL – непроцедурный язык: мы не видим деталей обработки, конкретных процедур, в которые сервер транслирует наш запрос. Отсюда и простота использования SQL. Процедурные языки, такие как Си, Паскаль, Бейсик, требуют детальной записи действий. Поэтому их использовать сложнее, но они обладают большей гибкостью и более широкими возможностями. PL/SQL сочетает возможности SQL с возможностями процедурного языка. Как и в программе на процедурных языках (раздел 10), в процедуре на PL/SQL можно описать переменные и константы, использовать инструкции проверки условий, организовывать циклы и т. д. В виде процедур и их пакетов можно запрограммировать сложные алгоритмы обработки данных, что иногда невозможно или неэффективно сделать с помощью одних только команд SQL. Особым видом процедур являются триггеры базы данных. Триггер – это процедура, связываемая с таблицей. Когда дается команда SQL, соответствующая условиям триггера, СУБД запускает триггер на выполнение. Поэтому триггеры используют для того, чтобы СУБД реагировала на различные ситуации, например, на ввод в таблицу определенных данных. Таким образом, в рамках технологии «клиент/сервер» на сервере хранятся не только данные, но и многочисленные процедуры их обработки.

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

Контрольные вопросы и задания к главе 9

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

2. Структуры данных – таблица, запись, поле.

3. Назначение ключа, индекса.

4. Реляционная база данных.

5. Понятие информационно-логической модели предметной области.

6. Назначение СУБД.

7. Нормализация отношений; первая, вторая и третья нормальные формы.

8. Обеспечение целостности данных в базе данных, понятия ссылочной целостности и целостности домена.

9. Схема обобщенной технологии работы с СУБД.

10. Работа с СУБД Access – создание новой базы данных, создание структуры таблиц, ввод данных в таблицы.

11. Создание и применение форм, запросов, отчетов.

12. Основные понятия структурированного языка запросов (SQL), классы команд SQL.

13. Команды языка определения данных.

14. Команды языка манипулирования данными.

15. Команды управления транзакциями.

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

17. Процедуры и триггеры базы данных.