Операторы SQL

· Создание таблиц. Таблицы (их структура) создаются командой CREATE TABLE (выражение).

Пример 18 Создать таблицу для сохранения данных о продавцах:

CREATE TABLE Продавцы

(пном integer NOT NULL UNIQUE PRIMARY KEY,

пимя char (10),

город char (10) DEFAULT 'Москва',

комм decimal CHECK (комм < 1));

Пример 19 Создать таблицу для сохранения данных о заказчиках:

CREATE TABLE Заказчики (зном integer NOT NULL PRIMARY KEY, зимя char (10) NOT NULL, город char (10) рейтинг integer, пном integer NOT NULL, UNIQUE (зном, пном));

Команда CREATE TABLE определяет имя таблицы, набор имен столбцов, указанных в определенном порядке, типы данных, размеры и ограничения столбцов, ограничения таблицы. В SQL возможно задание следующих типов данных: integer, character, numeric, decimal, smallint, float, real, double, precision, long, varchar, date, time. Ограничение столбца вставляется в конец имени столбца после типа данных и перед запятой. Ограничение таблицы помещается в конец описания таблицы после последнего имени столбца, но перед заключительной круглой скобкой.

Основные типы ограничений:

- Исключение пустых или неопределенных (NULL) указателей введением команды NOT NULL.

- Уникальность данных UNIQUE. Это ограничение отклонит любую попытку ввода в это поле значения, которое уже представлено в другой строке. Это ограничение может применяться только к полям, которые были объявлены как непустые (NOT NULL).

- Первичный Ключ PRIMARY KEY. Первичные ключи не могут иметь значений NULL. Это означает, что, подобно полям в ограничении UNIQUE, любое поле, используемое в ограничении PRIMARY KEY, должно уже быть объявлено NOT NULL.

- Ограничения на значения полей CHECK необходимо, чтобы предотвратить ошибку неправильного ввода в поле.

- Установка значений по умолчанию DEFAULT.

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

· Удаление таблиц осуществляется (его владельцем) командой DROP Table <имя >.

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

· Создание представлений. Можно создавать представление (View) – таблицы, содержимое которых берется или выводится из других таблиц. Представление создается командой CREATE VIEW.

Пример 20 Создать представление, в котором будет храниться информация о продавцах из Москвы:

CREATE VIEW Москва1

AS SELECT пном, пимя

FROM Продавцы where город=’Москва’;

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

Имеются некоторые виды запросов, которые недопустимы в определениях модифицируемых представлений: одиночное представление должно основываться на одиночном запросе. Объединение (UNION), агрегатные функции, DISTINCT в определении, вычисляемые поля не разрешаются в работе с представлениями. Упорядочение (ORDER BY) никогда не используется в определении представлений.

· Удаление представлений осуществляется (его владельцем) командой DROP VIEW <имя >.

· Заполнение БД данными. Значения могут быть помещены в таблицу и удалены из нее командами языка DML INSERT (ВСТАВИТЬ), DELETE (УДАЛИТЬ).

Пример 21 Ввести новую строку в таблицу Продавцы, используя следующую команду:

INSERT INTO Продавцы VALUES (1009, 'Галкин', 'Тверь', 0.12);

· Исключение строк проводится командой DELETE. Она может удалять только введенные строки, а не отдельные значения полей, так что параметр поля является необязательным или недоступным.

Пример 22 У далить все содержание таблицы Продавцов:

DELETE FROM Продавцы;

· Изменение данных выполняется командой UPDATE.

Пример 23 После ухода продавца Строкова на пенсию, переназначить его номер новому продавцу Норову:

UPDATE Продавцы SET пимя = 'Норов', город = 'Орел', комм =.10 WHERE пном = 1001;

Эта команда передаст новому продавцу Норову, всех текущих заказчиков бывшего продавца Строкова и их заказы.

· Система разрешений. СУБД с помощью операторов SQL управляет системой разрешений доступа к данным (привилегий) или запрета. Администраторы баз данных сами создают пользователей и дают им привилегии. Однако пользователи, которые создают таблицы, сами имеют права на управление этими таблицами.

Привилегии – это права, которые определяют, может ли указанный пользователь выполнить данную команду.

При построении системы разрешений на базе языка SQL различают следующие приоритеты (по убыванию): роль, пользователь, группа, общий доступ (public).

Имеется несколько типов привилегий, соответствующих нескольким типам операций. Привилегии даются и отменяются двумя командами SQL: GRANT (ДОПУСК) и REVOKE (ОТМЕНА).

Каждый пользователь в среде SQL имеет специальное идентификационное имя (идентификатор – ID) или номер. ID разрешения – это имя пользователя, и SQL может использовать специальное ключевое слово USER, которое ссылается на идентификатор доступа, связанный с текущей командой. Команда интерпретируется и разрешается (или запрещается).

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

Привилегии относятся к командам SELECT, INSERT, UPDATE, DELETE, ALL (все), REFERENCES (определение внешнего ключа, который использует один или более столбцов этой таблицы как родительский ключ).

Пример 24 Пользователь Илья создал таблицу Заказчики и собирается позволить пользователю Петр выполнить запрос к ней:

GRANT SELECT ON Заказчики TO Петр;

Петр может выполнить запросы к таблице Заказчик и, но не может предоставить право SELECT другому пользователю, так как таблица принадлежит Илье.

Пример 25 Задать групповые привилегии GRANT SELECT ON Заказы TO PUBLIC;

Иногда создателю таблицы необходимо, чтобы другие пользователи могли получить привилегии в его таблице. Обычно это делается в системах, где один человек или более создает несколько (или все) базовые таблицы в БД, а затем передает ответственность за них тем, кто будет фактически с ними работать. SQL позволяет делать это с помощью предложения WITH GRANT OPTION.

Пример 26 Если Ольга хотела бы, чтобы Андрей имел право предоставлять привилегию SELECT в таблице Заказчики другим пользователям, она дала бы ему привилегию SELECT с использованием предложения WITH GRANT OPTION: GRANT SELECT ON Заказчики TO Андрей WITH GRANT OPTION; После выполнения этой команды Андрей получил право передавать привилегию SELECT третьим лицам.

Пример 27 Привилегии отменяются командой REVOKE

REVOKE INSERT, DELETE ON Заказчики FROM Степан;

Возможно задание (или отмена) привилегий с помощью рассмотренных ранее представлений.