1 Часть II. Лекция 4. © Куркурин Николай Дмитриевич. (906)

2 Команды SQL разделяются на следующие группы: Команды языка определения данных DDL (Data Definition Language). Эти команды можно использовать для создания, изменения и удаления различных объектов базы данных. Команды языка управления данными DCL (Data Control Language). С помощью этих команд можно управлять доступом пользователей к базе данных и использовать конкретные данные (таблицы, представления и т.д.). Команды языка манипулирования данными DML (Data Manipulation Language). Эти команды позволяют пользователю перемещать данные в базу данных и из нее. Команды языка управления транзакщями (Тгапsасtiоп Сопtrol Language). Эти команды позволяют определить исход транзакции.

Команды языка определения данных (DDL). Используются для создания, изменения и удаления различных объектов базы данных. Команды CREATE (создание), ALTER (модификация) и DROP (удаление) имеет большинство типов объектов баз данных (таблиц, представлений, процедур, триггеров, табличных областей, пользователей и др.). То есть, существует множество команд DDL: CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE TRIGGER, CREATE USER, CREATE ROLE и т.д.

4 CREATE TABLE предназначена для описания структуры таблицы. Эта команда создает пустую таблицу (без строк). Синтаксис команды:

5 А) Определение ограничений целостности таблицы. Чтобы предохранить поле от разрешения в нем пустых (NULL) указателей для столбца задается ограничение NOT NULL. CREATE TABLE Agent ( snum integer NOT NULL, sname char (15) NOT NULL, city char (15), commis decimal(8,2))

6 DEFAULT – установить значение по умолчанию. CREATE TABLE Agent ( snum integer NOT NULL, sname char (15) NOT NULL, city char (15) DEFAULT Астрахань, commis decimal(8,2))

7 Если помещается ограничение столбца UNIQUE в поле при создании таблицы, база данных отклонит любую попытку ввода в это поле для одной из строк, значения, которое уже представлено в другой строке. Это ограничение может применяться только к полям, которые были объявлены, как непустые (NOT NULL ) ! CREATE TABLE Agent ( snum integer NOT NULL UNIQUE, sname char (15) NOT NULL UNIQUE, city char (15), commis decimal(8,2) )

8 Если база данных определяет, что каждый заказчик назначается одному и только одному агенту, то каждая комбинация номера заказчика (cnum) и номера агента (snum) в таблице Заказчиков должна быть уникальной. CREATE TABLE Zakazchik ( cnum integer NOT NULL, cname char (30) NOT NULL, city char (15), rating integer, snum integer NOT NULL, UNIQUE (cnum, snum))

9 Первичный Ключ (PRIMARE KEY) может ограничивать таблицы или их столбцы. Это ограничение работает так же как и ограничение UNIQUE. Но следует учитывать различие между первичными ключами и уникальностью столбцов в способе их использования с внешними ключами. Первичные ключи не могут позволять значений NULL. Это означает что, подобно полям в ограничении UNIQUE, любое поле, используемое в ограничении PRIMARY KEY, должно уже быть объявлено NOT NULL. CREATE TABLE Agent ( snum integer NOT NULL PRIMARY KEY, sname char(30) NOT NULL UNIQUE, city char(15), commis decimal(8,2))

10 Лучше всего помещать ограничение PRIMARY KEY в поле (или в поля), которое будет образовывать уникальный идентификатор строки, и сохранить ограничение UNIQUE для полей которые должны быть уникальными логически (такие, как номера телефона или поле sname), а не для идентификации строк. Ограничение PRIMARY KEY может также быть применено для многочисленных полей, составляющих уникальную комбинацию значений: CREATE TABLE Agent2 (firstname char (10) NOT NULL, lastname char (10) NOT NULL, city char (15), PRIMARY KEY ( firstname, lastname ))

11 Можно устанавливать любое число ограничений для данных, вводимых в таблицы, чтобы, например, ограничить диапазон вводимых данных. Опция CHECK обеспечивает ограничение, которое позволяет установить условие (предикат), которому должно удовлетворять значение, вводимое в таблицу, прежде чем оно будет принято. Любая попытка модифицировать или вставить значение поля, которое могло бы сделать условие (предикат) неверным будет отклонена. CREATE TABLE Agent ( snum integer NOT NULL PRIMARY KEY, sname char(15) NOT NULL UNIQUE, city char(15), commis decimal(8,2) CHECK ( commis < ))

12 Можно использовать ограничение CHECK, чтобы защитить от ввода в поле определенных значений, и, таким образом, предотвратить ошибку. CREATE TABLE Agent (snum integer NOT NULL UNIQUE, sname char(15) NOT NULL UNIQUE, city char(15) CHECK (city IN (Астрахань, Волгоград, Элиста, Ростов-на-Дону, Ставрополь, Краснодар, Махачкала, Владикавказ)), commis decimal CHECK (commis < ))

13 Можно использовать CHECK в качестве табличного ограничения. Это полезно в тех случаях когда необходимо включить более одного поля строки в условие. CREATE TABLE Agent ( snum integer NOT NULL UNIQUE, sname char (15) NOT NULL UNIQUE, city char(15), commis decimal(8,2), CHECK (commis < AND city = Махачкала))

14 Б) Ограничения по ссылкам. Синтаксис ограничения таблицы FOREIGN KEY: FOREIGN KEY (column list1) REFERENCES name_table (column list2) Первый список столбцов это список из одного или более столбцов таблицы, которые отделены запятыми и будут созданы или изменены этой командой. name_table это таблица содержащая родительский ключ. Она может быть таблицей, которая создается или изменяется текущей командой. Второй список столбцов это список столбцов, которые будут составлять родительский ключ. Списки двух столбцов должны быть совместимы (!).

15 Совместимость: иметь одинаковое число столбцов в данной последовательности, первый, второй, третий, и т.д., столбцы списка столбцов внешнего ключа, должны иметь одинаковые типы данных и размеры, что и первый, второй, третий, и т.д., столбцы списка столбцов родительского ключа. столбцы в списках обоих столбцов не должны иметь одинаковых имен. CREATE TABLE Zakazchik ( cnum integer NOT NULL PRIMARY KEY, cname char(30), city char(15), snum integer, FOREIGN KEY (snum) REFERENCES Agent ( snum ))

16 Используя ограничение FOREIGN KEY таблицы или столбца, можно не указывать список столбцов родительского ключа, если родительский ключ имеет ограничение PRIMARY KEY. В случае ключей со многими полями, порядок столбцов во внешних и первичных ключах должен совпадать, и, в любом случае, применим принцип совместимости между двумя ключами. CREATE TABLE Zakazchik ( cnum integer NOT NULL PRIMARY KEY, cname char(30), city char(15), snum integer REFERENCES Agent)

17 CREATE TABLE Agent ( snum integer NOT NULL PRIMARY KEY, sname char(10) NOT NULL, city char(10), commis decimal(8,2)) CREATE TABLE Zakazchik ( cnum integer NOT NULL PRIMARY KEY, cname char(10) NOT NULL, city char(10), rating integer, snum integer, FOREIGN KEY (snum) REFERENCES Agent, UNIQUE (cnum, snum)) CREATE TABLE Zakaz ( onum integer NOT NULL PRIMARY KEY, summa decimal(9,2), odate date NOT NULL, cnum integer NOT NULL, snum integer NOT NULL, FOREIGN KEY (cnum, snum) REFERENCES Zakazchik, FOREIGN KEY (snum) REFERENCES Agent

18 Действие ограничений. Если необходимо изменить или удалить текущее ссылочное значение родительского ключа, то для этого имеются три возможности: 1. Можно ограничить или запретить изменения, обозначив, что изменения в родительском ключе ограничены. 2. Можно сделать изменение в родительском ключе и тем самым сделать изменения во внешнем ключе автоматическим, - это называется - каскадным изменением. 3. Можно сделать изменение в родительском ключе, и установить внешний ключ в NULL автоматически (полагая, что NULL разрешен во внешнем ключе), - это называется пустым изменением внешнего ключа.

19 Действие ограничений. Если необходимо изменить или удалить текущее ссылочное значение родительского ключа, то для этого имеются три возможности: 1. Можно ограничить или запретить изменения, обозначив, что изменения в родительском ключе ограничены. 2. Можно сделать изменение в родительском ключе и тем самым сделать изменения во внешнем ключе автоматическим, - это называется - каскадным изменением. 3. Можно сделать изменение в родительском ключе, и установить внешний ключ в NULL автоматически (полагая, что NULL разрешен во внешнем ключе), - это называется пустым изменением внешнего ключа. Три эффекта: 1. Ограниченные (RESTRICTED) изменения. 2. Каскадируемые (CASCADES) изменения. 3. Пустые (NULL) изменения.

20 Пусть имеются причины изменить поле snum таблицы Agent Необходимо изменить номер агента, при этом сохранить все его заказчики. Если этот агент покидает компанию, он удаляется из базы данных, но мы можем не удалять его заказчиков. Его заказчики назначаются другому агенту. Чтобы сделать это вы должны указать UPDATE с каскадируемым эффектом, и DELETE с Ограниченным эффектом.

21 CREATE TABLE Zakazchik ( cnum integer NOT NULL PRIMARY KEY, cname char(30) NOT NULL, city char(15), rating integer, snum integer REFERENCES Agent, UPDATE OF Agent CASCADES, DELETE OF Agent RESTRICTED) 1. Меняется номер агента snum в таблице Zakazchik. Автоматически изменится поле snum в Zakaz. 2. Удаляется агент из таблицы Agent.

22 Третий эффект - Пустые (NULL) изменения. Агент оставляет компанию, его текущие Заказы не передаются другому агенту. С другой стороны, мы хотим отменить все Заказы автоматически для заказчиков, чьи счета мы удаляем. Изменив номера агента или заказчика, можно переназначить заказчиков.

23 CREATE TABLE Zakaz ( onum integer NOT NULL PRIMARY KEY, summa decimal(9,2), odate date NOT NULL, cnum integer NOT NULL REFERENCES Zakazchik, snum integer REFERENCES Agent, UPDATE OF Zakazchik CASCADES, DELETE OF Zakazchik CASCADES, UPDATE OF Agent CASCADES, DELETE OF Agent NULLS) В команде DELETE с эффектом Пустого изменения в таблице Agent ограничение NOT NULL должно быть удалено из поля snum !!!

24 Представление. Типы таблиц, с которыми вы имели дело до сих пор, назывались - базовыми таблицами. Это - таблицы, которые содержат данные. Однако имеется другой вид таблиц - представления. Представления - это таблицы, чье содержание выбирается или получается из других таблиц. ПРЕДСТАВЛЕНИЕ (VIEW) - ОБЪЕКТ ДАННЫХ, КОТОРЫЙ не содержит никаких данных его владельца. Представления подобны окнам, через которые вы просматриваете информацию, которая фактически хранится в базовой таблице.

25 Команда CREATE VIEW Вы создаете представление командой CREATE VIEW. Она состоит из слов CREATE VIEW (СОЗДАТЬ ПРЕДСТАВЛЕНИЕ), имени представления, которое нужно создать, слова AS (КАК), и далее запроса Пример: CREATE VIEW Astrakhan AS SELECT * FROM Agent WHERE city = Астрахань

26 Команда CREATE VIEW Вы создаете представление командой CREATE VIEW. Она состоит из слов CREATE VIEW (СОЗДАТЬ ПРЕДСТАВЛЕНИЕ), имени представления, которое нужно создать, слова AS (КАК), и далее запроса Пример: CREATE VIEW Astrakhan AS SELECT * FROM Agent WHERE city = Астрахань Имея представление, называемое Astrakhan, Вы можете использовать это представление точно так же, как и любую другую таблицу. Она может быть запрошена, модифицирована, вставлена в..., удалена из..., и соединена с другими таблицами и представлениями.

27 Представления значительно расширяют управление вашими данными. Это превосходный способ дать публичный доступ к некоторой, но не всей информации в таблице. Если вы хотите, чтобы Ваш агент был показан в таблице Агентов, но при этом не были показаны комиссии других Агентов, Вы можете создать представление с использованием следующего оператора: CREATE VIEW SalesPeople AS SELECT snum, sname, city FROM Agent

Команды управления данными (DCL). С помощью них можно управлять доступом пользователей к базе данных. Операторы управления данными включают: - применяемые для предоставления и отмены полномочий команды GRANT и REVOKE; - команду SET ROLE, которая разрешает или запрещает роли для текущего сеанса.

29 Синтаксис команды GRANT: system_priv системная привилегия. role роль: набор соответствующих полномочий, которые администратор может коллективно предоставлять пользователям и другим ролям. user пользователь. PUBLIC привилегия передается всем пользователям. WITH ADMIN OPTION если предоставлены системные полномочия или роли, то параметр позволяет пользователю передать полномочие или роль другим пользователям или ролям.

30 Команда SQL GRANT используется для предоставления пользователю роли или полномочия. Эту привилегию имеет только администратор базы данных. В системах клиент/сервер доступ к базе данных могут получить только пользователи, зарегистрированные в системе.

31 Привилегии: DBA разрешает пользователю выполнять действия администратора базы данных. Имея привилегию DBA, пользователь может выполнять команду SELECT для любой таблицы и представления, создавать объекты для других пользователей, предоставлять другим пользователям различные привилегии, выполнять полный экспорт/импорт базы данных. RESOURCE разрешает пользователю создавать объекты базы данных, включая таблицы и индексы. CONNECT позволяет пользователю подключаться к базе данных и работать с объектами, к которым он имеет привилегии по доступу. Пользователь может создавать представления, синонимы и межтабличные связи. Например: GRANT DBA TO SYSADM IDENTIFIED BY SYSTEM

32 Можно использовать предложение GRANT для спецификации предоставляемого полномочия, роли или разделяемого запятыми списка полномочий либо ролей. Для указания целевого пользователя или роли применяется предложение ТО. Например: GRANT CREATE TABLE, CREATE VIEW Ivanov, Petrov GRANT place1, update1, delete1 ТО role77 GRANT SELECT ON Zakazchik to Sidorov В первом примере системные полномочия CREATE TABLE и CREATE VIEW предоставляются пользователям Ivanov и Petrov. Второй оператор предоставляет роли place1, update1 и delete1 другой роли -- role77. Третий оператор предоставляет пользователю Sidorov полномочия SELECT на таблицу Zakazchik.

33 Если необходимо предоставить кому-то все полномочия на конкретный объект, используется ключевое слово ALL: GRANT ALL ON Zakazchik ТО Rodionov Можно предоставлять системные полномочия или роли с параметром ADMIN, что позволяет пользователю передать полномочие или роль другим пользователям или ролям: GRANT CREATE TABLE ТО Medvedev WITH ADMIN OPTION После этого Medvedev может предоставлять полномочия CREATE TABLE другим пользователям и ролям: GRANT CREATE TABLE ТО Krasnov

34 Если пользователю предоставляются полномочия на объект, то параметр GRANT позволяет передать эту возможность предоставления полномочий другим пользователями и ролям: GRANT SELECT, INSERT, UPDATE, DELETE ON Zakazchik ТО Dudkin WITH GRANT OPTION Если таблица должна быть доступна всем пользователям, указывается вместо имени пользователя ключевое слово PUBLIC.

35 Отмена привилегий осуществляется с помощью команды REVOKE, синтаксис которой аналогичен синтаксису команды GRANT. Можно отменить общие привилегии пользователя или же привилегии на указанную таблицу. Например: REVOKE CREATE TABLE FROM Ivanov REVOKE place1 FROM role77 REVOKE SELECT ON Zakazchik FROM Sidirov С помощью команды REVOKE можно задать отменяемое полномочие или роль. Предложение FROM позволяет указать пользователя или роль, для которых отменяются полномочия. Ключевое слово ALL позволяет отменить все объектные полномочия: REVOKE ALL ON Zakazchik FROM Sidorov Команда SQL SET ROLE разрешает или запрещает роли в текущем сеансе. С помощью ролей администратор может значительно упростить управление полномочиями.

36