Тема №22

22 ЯЗЫК SQL

22.1 Назначение, достоинства языка SQL

Язык SQL (Structured Query Language) — структурированный язык запросов. Его история насчитывает уже более 30 лет.

Первый, достаточно полный функционально, но не полностью синтаксически и семантически определенный вариант языка SQL был разработан и частично реализован в рамках проекта компании IBM по созданию экспериментальной реляционной СУБД SystemR в 1974—1979 гг.

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

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

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

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

Характеризуя язык SQL в целом, можно выделить его следующие черты:

• высокоуровневую структуру, напоминающую английский язык;

• независимость от конкретных СУБД;

• межплатформенную переносимость;

• наличие развивающихся стандартов;

• возможность выполнения интерактивных запросов извлечения данных и модификации их структуры;

• обеспечение программного доступа к базам данных;

• поддержку архитектуры клиент/сервер;

• возможность изменять и расширять структуру базы данных даже в то время, когда пользователи обращаются к ее содержимому (динамическое определение данных);

• расширяемость и поддержку объектно-ориентированных технологий;

• возможность доступа к данным в среде Интернет.

22.2 Роль языка SQL в СУБД

Сам по себе SQL не является ни системой управления базами данных, ни отдельным программным продуктом. SQL — это неотъемлемая часть СУБД, инструмент с помощью которого осуществляется связь пользователя с ней. На рисунке 22.1 изображена структурная схема типичной СУБД, компоненты которой соединяются в единое целое с помощью SQL (своего рода «клея»). Ядро базы данных является сердцевиной СУБД. Оно отвечает за физическое структурирование данных и запись их на диск, а также за физическое чтение данных с диска. Кроме того, ядро принимает SQL-запросы от других компонентов СУБД (таких как генератор форм, генератор отчетов или модуль запросов), от пользовательских приложений, а также от других вычислительных систем.

Рисунок 22.1 – Структурная схема типичной SQL-ориентированной СУБД

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

SQL выполняет множество функций:

• язык интерактивных запросов. Пользователи вводят команды SQL в интерактивном режиме для выборки данных и отображения их на экране, а также для внесения изменений в базу данных;

• язык программирования баз данных. Чтобы получить доступ к базе данных, в прикладные программы вставляются команды SQL;

• язык администрирования баз данных. Администратор базы данных может использовать SQL для определения структуры базы данных и управления доступом к данным;

• язык создания приложений клиент/сервер. В прикладных программах SQL используется как средство организации связи по локальной сети с сервером баз данных, в которой хранятся совместно используемые данные и др.

22.2 Основные понятия языка SQL

22.2.1 Структура команды SQL

Каждая команда SQL начинается с действия — ключевого слова или группы слов, описывающих выполняемую операцию.

Например, INSERT (добавить), DELETE (удалить), COMMIT (завершить), CREATE TABLE (создать таблицу).

Примечание. В языках программирования ключевое слово — название, зарезервированное для определенных целей, например, названий команд, имен функций и т.п.

После действия может следовать одно или несколько предложений. Предложение описывает данные, с которыми работает команда, или содержит уточняющую информацию о действии, выполняемом командой. Каждое предложение начинается с ключевого слова, такого как, например, WHERE (где), FROM (откуда), INTO (куда). Многие предложения содержат имена таблиц и полей базы данных, некоторые — константы и выражения.

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

Пример команды SQL

22.2.2 Типы данных

Такие традиционные типы данных, как числа и строки символов постоянной длины, были определены в стандарте SQL-1. Более поздние стандарты предусматривают дополнительно и другие типы данных, например, строку символов переменной длины, дату, время, абстрактные типы и др.

Ограничимся указанием типов данных, предусмотренных в стандарте SQL-1 (таблица 22.1).

Таблица 22.1 – Типы данных языка SQL-1

Тип данных | Описание
СНАН(длина) СНАКАСТЕR (длина) | Строка символов постоянной длины
INTEGER INT | Целое число
SMALLINT | Малое целое число
NUMERIC (точность, степень) DECIMAL (точность, степень) DEC (точность, степень) | Число с фиксированной запятой
FLOAT (точность) | Число с плавающей запятой

22.2.3 Выражения

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

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

21 -375.18 62.3

константы с плавающей запятой

1.5Е7 -3.14Е9 2.5Е-6 0.783Е24

Строковые константы должны быть заключены в одинарные кавычки. Примеры таких констант:

'Минск' 'New York' 'Иванов И. И.'

Отсутствующее значение (NULL). SQL поддерживает обработку отсутствующих данных с помощью понятия «отсутствующее значение».

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

• COUNT — количество значений в столбце таблицы;

• SUM — сумма значений в столбце;

• AVG — среднее арифметическое значений в столбце;

• МАХ — максимальное значение в столбце;

• MIN — минимальное значение в столбце.

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

• арифметические: + (сложение), - (вычитание), * (умножение), / (деление);

• отношения: = (равно), > (больше), < (меньше), >= (больше или равно), <= (меньше или равно), <> (не равно);

• логические: AND (логическое «И»), OR (логическое «ИЛИ»), NOT (логическое отрицание);

• специальные:

• IN — определяет множество, которому может принадлежать значение;

• BETWEEN — задает границы, в которые должно попадать значение;

• LIKE — применяется для поиска по шаблону. В шаблоне используются специальные символы: % (процент), заменяющий любую последовательность символов, и _ (подчеркивание), заменяющее один любой символ;

• IS NULL — используется для поиска NULL-значений. Для указания порядка действий в выражениях можно применять круглые скобки.

Пример выражения:

Цена * (Остаток + Приход - Расход)

22.3 Функциональные возможности языка SQL

22.3.1 Характеристика команд языка SQL

Язык SQL, соответствующий последним стандартам SQL:1999, SQL:2003, представляет собой очень богатый и сложный язык, все возможности которого трудно сразу осознать и тем более понять, поэтому приходится разбивать язык на уровни. В одной из классификаций, предусмотренных стандартом SQL, этот язык разбивается на «базовый» (entry), «промежуточный» (intermediate) и «полный» (full) уровни. Базовый уровень содержит около сорока команд, которые по функциональному на значению можно сгруппировать в категории. В таблице 22.2 приводится подмножество команд базового уровня языка SQL по категориям.

Таблица 22.2 – Команды языка SQL

Команда | Назначение
Команды определения данных
CREATE TABLE | Создает структуру таблицы
DROP TABLE | Удаляет таблицу
ALTER TABLE | Изменяет описание структуры таблицы
CREATE VIEW | Создает представление
Команда извлечения данных
SELECT | Извлекает данные из базы данных
Команды внесения изменений в базу данных
INSERT | Добавляет новые записи в таблицу
DELETE | Удаляет записи из таблицы
UPDATE | Обновляет данные таблицы
Команды управления транзакциями
COMMIT | Сообщает об успешном окончании транзакции
ROLLBACK | Сообщает о неуспешном окончании транзакции
Команды управления доступом
GRANT | Предоставляет пользователю определенные права доступа
REVOKE | Отменяет определенные права доступа
Команды встроенного SQL
DECLARE, OPEN, FETCH, CLOSE | Реализуют обращения к базе данных из прикладных программ

Формирование запросов на языке SQL осуществляется в строгом соответствии с форматом команды, который определяет правила ее формирования.

Для описания форматов команд языка SQL (иногда усеченных) будем использовать следующие обозначения:

• < > (угловые скобки) — то, что в них указано, определяет пользователь;

• [ ] (квадратные скобки) — выделяют те части команды, которые могут отсутствовать;

• { } (фигурные скобки) — объединяют последовательность элементов в логическую группу;

• ... (многоточие) — указывает на допустимость повторения элемента или группы элементов один или несколько раз;

• | (вертикальная черта) — означает альтернативный выбор;

• ( ) (круглые скобки) — заключают аргументы команды;

• (пробелы) — используются для разделения элементов команды.

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

Рисунок 22.2 – Реляционная модель предметной области БАНК

22.3.2 Команды определения данных

Команда создания структуры таблицы CREATE TABLE имеет следующий формат:

CREATE TABLE <имя таблицы>

({<имя поля> <тип данных>[<размер>]

[<ограничения на поле>]

[<значение поля по умолчанию>] },...

[,<ограничения на таблицу>])

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

• NOT NULL — поле, для которого указано это ограничение, не допускает значений NULL;

• UNIQUE — поле, для которого указано это ограничение, не допускает повторяющихся значений;

• PRIMARY KEY — поле, для которого это ограничение задано, должно выступать в роли первичного ключа.

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

Пусть требуется создать в базе данных структуру таблицы КЛИЕНТ, состоящую из полей «Номер клиента», «Ф.И.О. клиента», «Социальное положение» и «Адрес клиента». Поле «Номер клиента» является числовым, первичным ключом таблицы; поле «Ф.И.О. клиента» — символьным с размером 20, обязательным для заполнения; поле «Социальное положение» — символьным с размером 15; поле «Адрес клиента» — символьным с размером 50. Решить эту задачу можно с помощью следующей команды:

CREATE TABLE КЛИЕНТ (НК INT PRIMARY KEY, ФИО CHAR(20) NOT NULL, СОЦ_П CHAR(15), АДР_К CHAR(20)

Команда удаления таблицы DROP TABLE имеет следующий формат:

DROP TABLE <имя таблицы>

Например, по команде

DROP TABLE КЛИЕНТ

таблица КЛИЕНТ удаляется из базы данных.

Команда внесения изменений в структуру таблицы ALTER TABLE имеет следующий формат:

ALTER TABLE <имя таблицы>

({<имя поля> <тип данных>[<размер>]

[<ограничения на поле>]

[<значение поля по умолчанию>]},...)

Например, по команде

ALTER TABLE КЛИЕНТ (НФ INT)

в структуру таблицы КЛИЕНТ добавляется новое поле НФ («Номер филиала»), имеющее тип «целое число».

Представления (views) — это таблицы, содержимое которых извлекается из других таблиц базы данных. Представления подобны окнам, через которые просматривается информация, хранимая в базе данных. Представление можно использовать, как и любую другую таблицу: формулировать к нему запросы, выполнять обновление, вставку, удаление данных и соединение с другими таблицами и представлениями.

Команда создания представлений CREATE VIEW имеет следующий формат:

CREATE VIEW <имя представления> AS <запрос>

Например, по команде

CREATE VIEW СЛУЖАЩИЕ AS SELECT * FROM КЛИЕНТ WHERE

СОЦ_П='служащий'

создается представление СЛУЖАЩИЕ, содержащее записи о клиентах-служащих, выбираемых по команде SELECT.

22.3.3 Команда извлечения данных

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

SELECT [ALL | DISTINCT | DISTINCTROW |

[TOP n [PERCENT]]]

<список полей> | *

FROM {<имя таблицы}> [,...]

[WHERE <условие>]

[ORDER BY (<имя поля> [ASC | DESC]},..]

[GROUP BY {<имя поля>} [,...]

[HAVING <условие>]]

Команда SELECT должна обязательно содержать ключевые слова SELECT и FROM. Остальные ключевые слова являются необязательными.

Можно выводить все значения полей (ALL) или только неповторяющиеся (DISTINCT). DISTINCTROW позволяет не отображать полностью повторяющиеся записи. Если не указаны эти ключевые слова, то выводятся все значения полей.

ТОР п возвращает п записей, a TOP n PERCENT — п процентов записей, находящихся в начале диапазона, выбранного по условию.

В списке полей перечисляются поля, которые необходимо включить в результирующую таблицу. Звездочка (*) означает, что в результирующую таблицу включаются все поля таблицы.

Ключевое слово ORDER BY задает сортировку записей по возрастанию (ASC) или убыванию (DESC). Если не указан порядок сортировки, то осуществляется сортировка по возрастанию.

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

Команда SELECT дает возможность проведения вычислений над полями базы данных, итоговых вычислений с использованием функций агрегирования, таких как SUM, AVG, COUNT, MAX, MIN. Она позволяет также создавать вложенные и многотабличные запросы.

Примеры команды SELECT

1. По команде

SELECT * FROM КЛИЕНТ

из таблицы КЛИЕНТ извлекаются все поля.

2. По команде

SELECT * FROM КЛИЕНТ WHERE ФИО_К = 'Сидоров С. С OR ФИО_К - 'Петров П. П.'

из таблицы КЛИЕНТ извлекаются все записи, у которых в поле ФИО К хранятся значения Сидоров С. С. либо Петров П. П.

3. По команде

SELECT ФИО_К, АДРК FROM КЛИЕНТ ORDER BY ФИО_К

из таблицы КЛИЕНТ выбираются поля ФИО_К и АДР_К и их значения выдаются в алфавитном порядке значений поля ФИО_К.

4. По команде

SELECT НФ, COUNT(HC)

FROM СЧЕТ

GROUP BY НФ

выдается количество счетов, обрабатываемых каждым филиалом.

5. По команде

SELECT HK, НС

FROM КЛИЕНТ-СЧЕТ

WHERE HK= (SELECT HK

FROM КЛИЕНТ

WHERE ФИО_К="Сидоров С. С)

выводятся номера счетов клиента Сидорова С. С. Для их определения отыскивается номер клиента вложенной командой SELECT.

6. По команде

SELECT ФИЛИАЛ.НФ, ФИЛИАЛ.АДР Ф, СЧЕТ.НС

FROM ФИЛИАЛ, СЧЕТ

WHERE ФИЛИАЛ.НФ=СЧЕТ.НФ

выводятся на основании двух таблиц сведения о номерах филиалов, их адресе и счетах, обрабатываемых филиалами.

22.3.4 Команды внесения изменений в базу данных

Команда добавления новых записей в таблицу имеет следующий формат:

INSERT INTO <имя таблицы> [{<имя поля>},...]

VALUES({<значение>},...)

Например, по команде

INSERT INTO КЛИЕНТ VALUES (80101, 'Стром И. И.', 'рабочий', 'Сухая, 6")

в конец таблицы КЛИЕНТ добавляется новая запись, в первое поле которой заносится значение 80101, во второе — значение Стром И. И., в третье — значение рабочий, в четвертое поле — значение Сухая, 6.

Команда удаления записей из таблицы DELETE имеет следующий формат:

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

В предложении WHERE можно использовать различные операторы, в том числе специальные. Например, по команде

DELETE FROM СЧЕТ WHERE НФ=10

из таблицы СЧЕТ удаляются все записи о счетах филиала № 10.

Команда обновления данных UPDATE имеет следующий формат:

UPDATE <имя таблицы>

SЕТ{<имя поля>=<выражение>},... [WHERE <условие>]

Например, по команде

UPDATE КЛИЕНТ SET ФИО_К = 'Кравцова И. И.' WHERE HK=80201

в таблице КЛИЕНТ для тех записей, у которых в поле НК хранится 80201, изменяется значение поля ФИО_К на Кравцова И. И.

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

К категории Управление транзакциями относятся команды, позволяющие обеспечивать целостность данных в базе. В реляционной базе данных фиксируются два требования целостности: условие «целостности таблиц» и условие «ссылочной целостности»..

Приведем пример обеспечения требования целостности таблиц. Пусть в таблице КЛИЕНТ хранятся следующие сведения о клиентах филиала банка: номер филиала, Ф.И.О., дата рождения. Клиенты могут иметь одинаковые Ф.И.О. и дату рождения. Чтобы не нарушить условия целостности таблиц, в таблицу КЛИЕНТ добавляется уникальное поле «Номер клиента».

Проиллюстрируем на примере обеспечение требования ссылочной целостности. В поле «Номер филиала» таблицы КЛИЕНТ хранятся сведения о принадлежности клиентов к определенному филиалу банка. Здесь поле «Номер филиала» является внешним ключом для ссылки на таблицу ФИЛИАЛ. Для обеспечения ссылочной целостности каждому значению поля «Номер филиала» таблицы КЛИЕНТ должно соответствовать конкретное значение номера филиала в таблице ФИЛИАЛ.

Для обеспечения целостности данных в языке SQL используются средства обработки транзакций.

SQL-транзакция — это несколько последовательных команд SQL, которые должны выполняться как единое целое.

Транзакция либо успешно выполняется, и тогда СУБД фиксирует произведенные изменения базы данных на внешнем носителе; либо отменяется, и тогда все произведенные изменения СУБД ликвидирует. Таким образом, принцип транзакции — «либо все, либо ничего». Примером транзакции может служить перевод денег клиентом банка из одного филиала в другой. Необходимо как единое целое осуществить два действия: списать деньги со счета в одном филиале и занести деньги на счет в другом филиале.

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

Команды COMMIT и ROLLBACK используются в основном в программном режиме, хотя возможно их использование и в интерактивном режиме.

22.3.6 Команды управления доступом

К категории Управление доступом относятся команды для осуществления административных функций, присваивающих или отменяющих право (привилегию) использовать таблицы базы данных определенным образом. Каждый пользователь базы данных имеет определенные права по отношению к объектам базы. Права — это те действия с объектом, которые может выполнять пользователь. Права могут меняться с течением времени: старые могут отменяться, новые — добавляться. Предусмотрены следующие права:

• SELECT — право читать таблицу;

• INSERT — право добавлять данные в таблицу;

• UPDATE — право изменять данные таблицы;

• DELETE — право удалять данные из таблицы;

• REFERENCES — право определять первичный ключ.

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

Команда предоставления прав доступа GRANT имеет следующий формат:

GRANT ALL [PRIVILEGES] | {SELECT | INSERT | DELETE | UPDATE [<список полей>] | REFERENCES [<список полей >]},...ON <имя таблицы> ТО <список пользователей>

Например, по команде

GRANT SELECT, UPDATE ON КЛИЕНТ ТО USER1, USER2

пользователям USER1 и USER2 назначаются права чтения и обновления (редактирования) таблицы КЛИЕНТ.

Команда отмены прав доступа REVOKE имеет следующий формат:

REVOKE ALL [PRIVILEGES] | {SELECT | INSERT | DELETE | UPDATE [<список полей>] | REFERENCES [<список полей>]},...ON <имя таблицы>ТО <список пользователей>

Например, по команде

REVOKE UPDATE ON КЛИЕНТ ТО USER1

отменяется право обновления таблицы КЛИЕНТ пользователю USER1.

22.3.7 Команды встроенного SQL

К категории Встроенный SQL относятся команды, предназначенные для реализации обращения к базе данных из прикладных программ, написанных на определенном языке программирования. Для пересылки данных из базы данных в прикладную программу используется временная таблица, называемая SQL-курсором. Команды DECLARE, OPEN, FETCH, CLOSE позволяют управлять SQL-курсором.