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

В соответствии со стандартом все команды SQL определены в подразделы:

• • Data definition language (DDL) — язык определения данных, содержит команды создания, изменения и удаления объектов базы данных;
• • Data manipulation language (DML) — язык манипулирования данными, содержит команды извлечения и изменения данных, хранящихся в таблицах;
• • Data control language (DCL) — язык управления данными, включает в себя команды предоставления пользователю привилегий доступа или их отмены. Команды языка также служат для контроля над распределением привилегий между пользователями. Эти действия относятся к функциям системного администратора базы данных, поэтому рассмотрение команд языка DCL выходит за рамки данного учебника.

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

Синтаксис команды:

CREATE TABLE имя_таблицы.

({описание_столбца | имя_вычисляемого_столбца AS выражение | ограничения_целостности_уровня_таблицы}.

[,

Именем таблицы является идентификатор длиной не более 128 символов.

Синтаксис описания столбца таблицы имеет вид:

имя_столбца тип_данных [ (размер)].

[{DEFAULT значение_по_умолчанию |.

IDENTITY [(значение, шаг)]}].

[ограничения_целостности_уровня_столбца].

DEFAULT позволяет задать значение, присваиваемое столбцу во вновь добавляемой записи; IDENTITY указывает, что создается столбец с поддержкой автоматической нумерации (столбец-счетчик). В таблице может быть определен только один столбец-счетчик. Параметр «значение» задает начальное значение счетчика, а параметр «шаг» — шаг приращения. Если эти параметры не заданы, то они имеют значение 1. IDENTITY может быть задано только для тех столбцов, которые имеют целочисленные или десятичные типы. Вставка значений в столбец IDENTITY запрещена.

Описание ограничений уровня столбца имеет следующий синтаксис:

[CONSTRAINT имя_ограничения].

{PRIMARY KEY | UNIQUE | NOT NULL |.

[FOREIGN KEY] REFERENCES имя_таблицы [(имя_столбца)].

[ON UPDATE {CASCADE | SET NULL}].

[ON DELETE {CASCADE | SET NULL}]}.

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

• • ограничение по первичному ключу PRIMARY KEY. Все значения первичного ключа таблицы должны быть уникальными и отличаться от значения NULL. В таблице может быть только один ключ. Если он является составным, то ограничения целостности по первичному ключу задаются на уровне таблицы;
• • ограничения уникальности значения столбца UNIQUE. Это означает, что в таблице не может быть двух записей, имеющих одно и то же значение в этом столбце;
• • ограничение NOT NULL, запрещающее хранить в столбце значение NULL;
• • ограничение по внешнему ключу FOREIGN KEY (ограничение ссылочной целостности). Для столбца, который является внешним ключом, указывается имя таблицы, с которой устанавливается связь, и имя столбца таблицы, по которому будет устанавливаться связь. Такая таблица является главной по отношению к создаваемой таблице. Для столбца главной таблицы, по значениям которого устанавливается связь, должно быть установлено ограничение PRIMARY KEY и UNIQUE.

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

Ограничения для внешних ключей определяют конкретные действия, выполняемые в случае изменения значения соответствующего ключа. Предложения ON UPDATE и ON DELETE для внешнего ключа определяют правила изменения связанных данных.

Предложение ON UPDATE CASCADE означает, что каждое значение внешнего ключа будет автоматически обновлено при обновлении значения в столбце главной таблицы. Предложение ON DELETE CASCADE означает, что в случае удаления строки из главной таблицы автоматически будут удалены все строки подчиненной таблицы, которые ссылаются на удаляемую строку главной таблицы.

Предложение ON UPDATE SET NULL означает, что в случае обновления столбца главной таблицы во всех ссылающихся строках подчиненной таблицы значениям внешнего ключа будут автоматически присвоены значения NULL. Аналогично, предложение ON DELETE SET NULL означает, что в случае удаления строки из главной таблицы всем соответствующим внешним ключам в подчиненной таблице будут автоматически присвоены значения NULL.

Пример Команда создания таблицы «Студент»:

CREATE TABLE [Студент] (.

[№ зачетной книжки] int PRIMARY KEY,.

[№ группы] smallint NOT NULL FOREIGN KEY REFERENCES Группа ([номер группы]),.

Фамилия char (15) NOT NULL,.

[Дата рождения] datetime NOT NULL,.

[Коммерческий] bit NOT NULL).

Таблица «Студент» является зависимой от таблицы «Группы», столбец «№ группы» является внешним ключом.

Изменение структуры таблицы выполняется командой ALTER TABLE. С помощью команды можно выполнять изменение свойств существующих столбцов, удалять их или добавлять в таблицу новые, управлять ограничениями целостности как на уровне столбцов, так и на уровне таблицы. Назначение многих параметров и ключевых слов аналогично назначению соответствующих параметров и ключевых слов команды CREATE TABLE.

Удаление таблицы выполняется при помощи команды DROP TABLE. Синтаксис команды:

DROP TABLE таблица Пример Запрос на удаление таблицы «Студент» имеет следующий вид: DROP TABLE Студент При удалении таблицы следует учитывать связи, установленные в базе данных между таблицами. Если на удаляемую таблицу с помощью ограничения целостности FOREIGN KEY ссылается другая таблица, то СУБД не разрешит ее удаление.

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

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

Раздел SELECT. Команда SELECT не изменяет данные в базе данных. Синтаксически она состоит из нескольких разделов, большинство из которых не являются обязательными:

SELECT [предикат] {* | таблица.* | [таблица.] поле 1 [AS.

псевдоним1] [, [таблица.] поле 2 [AS псевдоним2] [, …]]}.

FROM имена_таблиц [WHERE критерий_поиска].

[GROUP BY критерий_группировки].

[HAVING групповой_критерий].

[ORDER BY критерий_столбца].

В разделе SELECT указывается, данные каких полей (столбцов) выбрать, и, как видно из синтаксиса, это можно сделать несколькими способами. Например, * означает выбрать все поля, указанной в запросе таблицы, а таблица.поле1 означает выбрать значения поля из указанной таблицы.

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

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

Предикат DISTINCT используется для исключения строк, содержащих повторяющиеся значения в поле.

Предикат TOP п [PERCENT] указывает на то, что в результате выполнения команды SELECT возвращаются только первые п строк из набора результата. Если задано ключевое слово PERCENT, то будут возвращаться первые строки, составляющие п процентов от общего количества строк.

Раздел FROM. Раздел FROM содержит имена таблиц, из которых извлекаются данные, и в случае извлечения данных из одной таблицы имеет синтаксис:

FROM таблица [AS псевдоним].

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

Соединение таблиц — это набор результатов от операции соединения, выполненной над двумя или несколькими таблицами. При выполнении соединения имена таблиц перечисляются в разделе FROM запроса с указанием операции JOIN. Различают внутреннее и внешнее соединения.

Внутреннее соединение (INNER JOIN) является типом соединений, принятым по умолчанию. В этом случае синтаксис запроса на соединение будет иметь вид:

SELECT список_полей.

FROM таблица₁ INNER JOIN таблица₂.

ON таблица₁.поле₁ оператор таблица₂.поле₁.

Внутреннее соединение задает набор результатов, в который будут включены лишь те строки, которые соответствуют условию ON, а все несоответствующие строки будут отброшены.

Полные внешние соединения (FULL OUTER JOIN) задают набор результатов, состоящих как из строк, соответствующих условию ON, так и из строк, не соответствующих ему. Для строк, не соответствующих условию ON, значением колонки станет NULL. В этом случае синтаксис запроса на соединение будет иметь вид:

SELECT список_полей.

FROM таблица₁ FULL OUTER JOIN таблица₂.

ON таблица₁.поле₁ оператор таблица₂.поле₁.

Левые внешние соединения (LEFT OUTER JOIN) возвращают строки, в которых произошло соответствие условию поиска, плюс все строки из таблицы, заданной слева от ключевого слова JOIN В этом случае синтаксис запроса на соединение будет иметь вид:

SELECT список_полей.

FROM таблица₁ LEFT OUTER JOIN таблица₂.

ON таблица₁.поле₁ оператор таблица₂.поле₁.

Правые внешние соединения (RIGHT OUTER JOIN) возвращают строки, в которых произошло соответствие условию поиска, плюс все строки из таблицы, заданной справа от ключевого слова JOIN. В этом случае синтаксис запроса на соединение будет иметь вид:

SELECT список_полей.

FROM таблица₁ RIGHT OUTER JOIN таблица₂.

ON таблица₁.поле₁ оператор таблица₂.поле₁.

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

WHERE критерий_поиска | таблица1. поле {*= | =*} таблица2. поле Критерий поиска определяет логическое условие, при выполнении которого строка будет включена в результат. При составлении логического условия можно использовать операторы, идентификаторы, функции, константы и подзапросы.

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

GROUP BY критерий_группировки Аргумент критерий группировки определяет условие группировки. Как правило, здесь указывается имя одного или нескольких полей таблицы, по которым будет выполнена группировка.

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

Функция AVG () вычисляет среднее значение для набора данных и имеет следующий синтаксис:

AVG ([ALL | DISTINCT] выражение).

ALL — агрегирование выполняется для всех строк набора данных (по умолчанию); DISTINCT — агрегирование выполняется только для уникальных строк.

Функция COUNT () подсчитывает количество строк, которое должно быть выведено при выполнении запроса, и имеет следующий синтаксис:

COUNT ({[ALL / DISTINCT] выражение / *}).

«*» используется только в случае группировки данных с помощью раздела GROUP BY.

Функция SUM () выполняет суммирование всех значений в указанной колонке и имеет следующий синтаксис:

SUM ([ALL | DISTINCT] выражение) Функция MAX () возвращает максимальное значение в указанной колонке и имеет следующий синтаксис:

MAX ([ALL | DISTINCT] выражение) Функция MIN () возвращает минимальное значение в указанной колонке и имеет следующий синтаксис:

MIN ([ALL I DISTINCT] выражение).

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

Раздел ORDER BY применяется, чтобы задать порядок, в котором должны сортироваться строки набора результатов. Указать этот порядок можно с помощью ключевых слов ASC (по возрастанию значений; действует по умолчанию) или DESC (по убыванию).

Приведем несколько примеров.

Примеры.

Задача 1. Вывести сведения о студентах, родившихся зимой.

SELECT [№ группы], Фамилия, [Дата рождения].

FROM Студент.

WHERE MONTH ([Дата рождения]) IN (12,1,2).

Задача 2. Для каждой группы определить количество студентов.

SELECT [№ группы].

COUNT (Фамилия) AS [Количество студентов].

FROM Студент GROUP BY [№ группы].

Задача 3. Для каждой специальности определить количество коммерческих студентов в группе и итоговую оплату.

SELECT [Наименование специальности], а. [№ группы], COUNT ([№ зачетной книжки]) AS [Количество студентов].

FROM Студент a INNER JOIN Группа Ъ ON а. [№ группы] =Ь. [№ группы].

INNER JOIN Специальность с.

ON b. [№ специальности] =с. [№ специальности].

WHERE Коммерческий=1.

GROUP BY [Наименование специальности], а. [№ группы].

Добавление записей в таблицу. Добавление записей в таблицу выполняется командой INSERT INTO.

Синтаксис запроса на добавление одной записи:

INSERT INTO таблица [ (поле₁ [, поле₂ [, …]])].

VALUES (значение₁ [, значение₂ [, …]).

Примечание. Если создать в режиме SQL запрос на добавление с помощью инструкции INSERT INTO… VALUES, сохранить и закрыть его, а затем открыть снова, то предложение VALUES будет преобразовано в предложение SELECT. Это не повлияет на результат выполнения запроса.

Пример Задача — добавить строку в таблицу специальность.

INSERT INTO Специальность ([№ специальности], [Наименование специальности], [Стоимость обучения]).

VALUES (90 900, 'Информационная безопасность', 45 000).

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

INSERT INTO таблица_приемник.

SELECT список_полей.

FROM таблица_источник.

Удаление записей из таблицы. Удаление строк из таблицы выполняется командой DELETE. По этой команде исключаются только целые строки, а не отдельные значения полей. Синтаксис команды:

DELETE.

FROM имя_таблицы В результате выполнения этой команды таблица становится пустой.

Обычно из таблицы требуется удалить только некоторые указанные строки. Синтаксис такой команды:

DELETE.

FROM имя_таблицы.

WHERE критерий_поиска Пример Задача — удалить из таблицы данные студента по фамилии Иванов. DELETE FROM Студент WHERE Фамилия=¹ Иванов'.

Изменение записей в таблице. Изменение значений полей осуществляет команда UPDATE. Она позволяет изменить некоторые или все значения в одной или нескольких строках. Синтаксис команды:

UPDATE имя_таблицы.

SET поле 1 = новое_значение.

WHERE критерий_поиска Команда UPDATE позволяет указать имя таблицы, для которой выполняется операция обновления и предложение SET, определяющее изменения, которые необходимо выполнить для одного или нескольких столбцов таблицы.

Пример Задача — указать перевод студента в другую группу.

UPDATE Студент.

SET [№ группы] = 3501.

WHERE [№ группы] =3591 AND [№ зачетной книжки] =12 036.

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

Получить полные сведения о группах заданной специальности.

SELECT *.

FROM Группа.

WHERE [№ специальности] =.

(SELECT [№ специальности].

FROM Специальность.

WHERE [Наименование специальности] = 'Управление качеством').