#49 Язык SQL. Назначение и основные операторы языка SQL

№49 Язык SQL. Назначение и основные операторы языка SQL

Язык SQL, его структура, стандарты, история развития. Подмножество языка DML: операторы SELECT, INSERT, UPDATE, DELETE.
Доступ к данным осуществляется в виде запросов, которые формулируются на стандартном языке запросов. Сегодня для большинства СУБД таким языком является SQL.
Появление и развития этого языка как средства описания доступа к базе данных связано с созданием теории реляционных баз данных. Прообраз языка SQL возник в 1970 году в рамках научно-исследовательского проекта System/R (IBM). Ныне SQL —это стандарт интерфейса с реляционными СУБД.
SQL не является языком программирования в традиционном представлении. На нем пишутся не программы, а запросы к базе данных. Поэтому SQL —декларативный или непроцедурный язык. Это означает, что с его помощью можно сформулировать, что необходимо получить, но нельзя указать, как это следует сделать.
Первый международный стандарт языка SQL был принят в 1989 г. (SQL/89 или SQL1), в 1992 г. был принят стандарт языка SQL (SQL/92 или SQL2). В 1999 г. появился стандарт SQL3. В SQL3 введены новые типы данных, при этом предоставляется возможность задания сложных структурированных типов данных, которые в большей степени соответствуют объектной ориентации. Появились стандарты на события и триггеры, которые раньше не затрагивались в стандартах.
Язык SQL делится на подмножества.
) Язык определения данных (DDL - Data Definition Language) предоставляет пользователям средства указания типа данных и их структуры, а также средства задания ограничений для информации, хранимой в базе данных.
Операторы –CREATE, ALTER, DROP.
) Язык манипулирования данными (DML - Data Manipulation Language) позволяет вставлять, обновлять и извлекать информацию из базы данных.
Операторы –SELECT, INSERT, DELETE, UPDATE.
) Язык управления данными (DCL - Data Control Language) состоит из управляющих операторов.
Операторы –GRANT, REVOKE.
) Язык управления транзакциями.
Операторы –COMMIT, ROLLBACK, SAVEPOINT.

Запрос на языке SQL состоит из одного или нескольких операторов, следующих один за другим и разделенных точкой с запятой.
Каждый столбец в любой таблице хранит данные определенных типов. Различают базовые типы данных —строки символов фиксированной длины - CHAR(n), целые и вещественные числа - NUMERIC(n,m), DEC(n,m), INTEGER, SMALLINT, FLOAT(n), REAL, DOUBLE PRECISION , и дополнительные типы данных —строки символов переменной длины - VARCHAR(n), BIT VARYING(n), денежные единицы, дату и время –DATE, TIMESTAMP, INTERVAL, логические данные –BOOLEAN (два значения —"ИСТИНА" и "ЛОЖЬ").
Подмножество языка DML: операторы SELECT, INSERT, UPDATE, DELETE.
Язык обработки данных DML позволяет добавлять (insert), изменять (update), удалять (delete) и выбирать (select) информацию в базе данных, т.е. предназначен для работы с информационным содержанием базы данных. Операторы языка DML представляют собой SQL-команды, позволяющие изменять и дополнять хранящуюся в базе данных информацию.

. Подмножество языка DDL: операторы CREATE, ALTER, DROP. Представления, их значение; обновляемые представления.
Язык определения данных DDL представляет собой подмножество команд языка SQL, предназначенное для создания и определения объектов базы данных. Определения объектов, созданные с помощью команд DDL, сохраняются в словаре базы данных.
Язык определения данных DDL обеспечивает:
- Создание объектов базы данных (CREATE)
- Удаление объектов базы данных (DROP)
- Изменение свойств объектов базы данных (ALTER)
Современные базы данных содержат различные типы объектов:
таблицы, индексы, представления, роли, синонимы, последовательности, кластеры, триггеры, процедуры, функции, пакеты, пользователи, профили и т.д.
Все эти объекты создаются, изменяются и удаляются с помощью операторов DDL.
Представления, их значение
Представление –объект базы данных, позволяющий получить определенную пользователем выборку данных из одной или нескольких таблиц. В отличие от таблицы, представление не содержит никаких данных, а лишь запрос на языке SQL, дающий возможность прочитать из базы данных необходимую информацию и представить ее в табличной форме.
Пользователь может не знать, работает он с представлением или с настоящей таблицей. Подобно таблицам, к представлениям можно применять операторы insert, update, delete и select.
Важно понимать принципы использования представлений. Их применение может оказаться необходимым в силу следующих причин:
) Представления обеспечивают дополнительный уровень безопасности базы данных. Например, можно создать общую таблицу со сведениями обо всех сотрудниках компании, но разрешить менеджерам компании получать информацию только об их подчиненных.
) Представления позволяют скрыть от пользователей сложность структуры хранимых данных.
) Представления позволяют использовать разумные названия отдельных столбцов.
) Представления обеспечивают гибкость при изменении формата одной или нескольких входящих в них таблиц.
Обновляемые представления
Если к представлению можно применить операторы обновления, то представление является обновляемым (updateble), иначе оно является читаемым (read-only).
Приведем критерии того, является ли представление обновляемым в SQL:
- оно базируется на одной таблице;
- оно должно включать первичный ключ таблицы;
- оно не должно включать полей, полученных в результате применения функций агрегирования;
- оно не может содержать спецификации DISTINCT;
- оно не должно использовать GROUP BY или HAVING;
- оно не должно использовать подзапросы;
- оно может быть определено на другом представлении, но это представление должно быть обновляемым;
- оно не может содержать константы, строки или выражения в списке выбираемых выходных полей;
- для INSERT оно должно включать поля из таблицы, которые имеют ограничения NOT NULL.

. Подмножество языка DCL: операторы GRANT, REVOKE. Системные привилегии, привилегии на объекты, роли.
Предоставление пользователям необходимых полномочий и лишение полномочий осуществляется с помощью операторов DCL: GRANT и REVOKE.
Оператор GRANT используется для открытия другой схеме доступа к привилегии, а оператор REVOKE - для запрещения доступа, разрешенного оператором GRANT. Оба оператора могут использоваться как для объектных, так и для системных привилегий.
Для объектных привилегий синтаксис оператора GRANT таков:
GRANT привилегия ON объект TO обладатель_привилегий [WITH GRANT OPTION];
где привилегия - это нужная привилегия,
объект - это объект, к которому разрешается доступ, а
обладатель_привилегий - пользователь, получающий привилегию.
Для системных привилегий синтаксис оператора GRANT таков:
GRANT привилегия TO обладатель_привилегий [WITH ADMIN OPTION];
где привилегия - это предоставляемая системная привилегия, обладатель_привилегий - пользователь, получающий привилегию.
Для объектных привилегий синтаксис оператора REVOKE таков:
REVOKE привилегия ON объект FROM обладатель_привилегий [CASCADE CONSTRAINTS];
где привилегия - это отменяемая привилегия,
объект - это объект, на который предоставлена привилегия,
обладатель_привилегий - пользователь, получающий эту привилегию.
Для системных привилегий синтаксис оператора REVOKE таков:
REVOKE привилегия FROM обладатель_привилегий;
где привилегия - это отменяемая системная привилегия,
обладатель_привилегий - пользователь, который более не будет ее иметь.
Примеры операторов GRANT, REVOKE
) GRANT select ON customers TO Adrian;
) GRANT select, insert ON orders TO Adrian, Diana;
) GRANT ALL ON customers TO Adrian;
) GRANT select ON orders TO PUBLIC;
)   REVOKE insert ON orders FROM Adrian;
Роли
Для организаций, в которых работает множество пользователей, управление привилегиями является достаточно сложной задачей. Для ее упрощения можно использовать средство, называемое ролями. Роль (role) является совокупностью привилегии, как объектных, так и системных.
Примеры ролей:
) CREATE ROLE spaceadmin IDENTIFIED BY password
GRANT create session, alter session, restricted session, alter database,
create rollback segment, alter rollback segment, drop rollback segment,
create tablespace, alter tablespace, drop     TO spaceadmin;
) CREATE ROLE backupadmin IDENTIFIED BY password
GRANT create session, alter session, restricted session, manage tablespace,       backup any table     TO backupadmin;
Объектные и системные привилегии
До сих пор предполагалось, что каждый пользователь базы данных может обращаться к объектам всех других пользователей базы данных и к информации, содержащейся в этих объектах. В действительности дело обстоит далеко не так. Сервер баз данных предоставляет администратору полный и систематический контроль над тем, что именно каждый отдельный пользователь может читать, модифицировать, стирать или изменять. В сочетании с использованием представлений данных можно полностью контролировать доступ к информации всех пользователей.
Права доступа используются для того, чтобы позволить одному пользователю работать с данными другого пользователя. После получения необходимых
полномочий обладатель прав доступа может работать с объектами, принадлежащими другому пользователю.
Существуют привилегии двух различных видов: объектные и системные.
Объектная привилегия (object privilege) разрешает выполнение определенной операции над конкретным объектом (например, над таблицей - SELECT, DELETE, INSERT, UPDATE, REFERENCES).
Системная привилегия (system privilege) разрешает выполнение операций над целым классом объектов.