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

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

Само представление описывается путем указания идентификатора представления и запроса, который должен быть выполнен для его получения. Оператор создания представления имеет формат вида:

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

[(<имя поля> [,<имя поля> ]... )] AS <инструкция SELECT>

Если имена столбцов в представлении не указываются, то будут использоваться имена столбцов из запроса, описываемого соответствующим оператором SELECT. Оператор удаления представления имеет формат вида: DROP VIEW <имя представления>.

Использование представлений для однопользовательской БД имеет целью лишь упрощение структуры запросов в БД. Однако для многопользовательской сетевой СУБД представления играют ключевую роль в определении доступа к данным и защиты информации. Использование представлений дает следующие преимущества:

1. Независимость от данных. С помощью представлений можно создать согласованную, неизменную картину структуры БД, которая будет оставаться стабильной даже в случае изменения (незначительного) исходных таблиц;

2. Актуальность. Представление всегда содержит только «свежие» данные;

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

4. Снижение сложности. Использование представлений позволяет упростить структуру запросов;

5. Возможность индивидуальной настройки. Каждый пользователь может работать только с теми данными, которые ему действительно нужны, и к тому же в определенной форме.

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

Для удобства работы с представлениями в язык SQL введено понятие курсора. Курсор представляет собой своеобразный указатель, используемый для перемещения по наборам записей при их обработке. Описание и использование курсора в языке SQL выполняется следующим образом. В описательной части программы выполняют связывание переменной типа курсор (CURSOR) с оператором SQL (обычно с оператором SELECT). В выполняемой части программы производится открытие курсора (OPEN <имя курсора>), перемещение курсора по записям (FETCH <имя курсора>...), сопровождаемое соответствующей обработкой, и, наконец, закрытие курсора (CLOSE <имя курсора>).

Создание доменов

Оператор создания домена имеет формат вида:

CREATE DOMAIN <имя домена> [AS] тип данных

[DEFAULT <значение по умолчанию>]

[CHECK (<условия целостности>)]

SQL поддерживает пять скалярных типов данных: символьный (строковый), битовый, численный, даты/время и интервал. Символьный тип данных позволяет определить строки фиксированной (CHAR(n) или CHARACTER(n)) и переменной длинны (VARCHAR(n) или CHAR VARYING(n) или CHARACTER VARYING(n)), где n означает максимальное количество символов. Спецификация VARYING позволяет сохранять в базе только вводимые символы, иначе введенная строка дополняется пробелами до n символов. Численный тип данных позволяет задавать целые числа разного размера (SMALLINT, INT или INTEGER) и вещественные числа разной точности (FLOAT [precision], REAL, DOUBLE PRECISION, NUMERIC(precision, scale), DECIMAL(precision, scale) или DEC (precision, scale)), где precision определяет полное число десятичных цифр, а scale – число цифр после десятичной точки. Битовые типы данных позволяют хранить последовательности двоичных цифр фиксированной (BIT(n)) и переменной длинны (BIT VARYING(n)). Для хранения данных типа даты/время введены три типа данных: DATE, TIME и TIMESTAMP. Типы DATE и TIME позволяют хранить соответственно дату и время по отдельности, а TIMESTAMP – вместе. Тип данных INTERVAL определен для хранения интервалов времени. При определении доменов типы данных задаются после оператора AS, например: Name AS VARCHAR(20).

Условие проверки CHECK позволяет задать ограничители целостности на значения, которые может принимать домен. Например,

CREATE DOMAIN sex_type AS CHAR CHECK ( VALUE IN (‘M’,’F’) );

Значения в операторе IN могут также выбираться и из некоторой таблицы, например IN (SELECT s_type FROM STypes).

Изменить определение домена можно с помощью оператора ALTER DOMAIN. Удалить созданный домен можно с помощью оператора DROP DOMAIN, имеющего следующий формат записи:

DROP DOMAIN <имя домена> [RESTRICT | CASCADE]

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

Создание таблиц

Оператор создания таблицы имеет формат вида:

CREATE TABLE <имя таблицы> (<имя поля> <тип данных> [NOT NULL] [,<имя поля> <тип данных> [NOT NULL]] ... )

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

CREATE TABLE Товары( Код CHAR(5) NOT NULL, Тип CHAR(8), Наименование VARCHAR(20) NOT NULL, Цена DECIMAL(8,2) );

Оператор изменения структуры таблицы имеет формат вида:

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

( {ADD, MODIFY, DROP} <имя поля> [<тип данных>] [NOT NULL]

[,{ADD, MODIFY, DROP} <имя поля> [<тип данных>] [NOT NULL]]...)

Изменение структуры таблицы может состоять в добавлении (ADD), изменении (MODIFY) или удалении (DROP) одного или нескольких столбцов таблицы. Правила записи оператора ALTER TABLE такие же, как и оператора CREATE TABLE, разве что при удалении столбца указывать тип данных не требуется. Для примера добавим одно поле:

ALTER TABLE Товары(ADD Категория VARCHAR(20));

Оператор удаления таблицы имеет формат вида: DROP TABLE <имя таблицы>.

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

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

{<имя поля> <тип данных> [NOT NULL] [UNIQUE] [DEFAULT <значение по умолчанию>] [CHECK (<условия целостности>)] [, …]}

[PRIMARY KEY (<список полей>), ]

{[UNIQUE (<список полей>), ] [, …]}

{[FOREIGN KEY (<список внешних полей>)] REFERENCES <имя базовой таблицы> [<список ключевых полей базовой таблицы>] MATCH {PARTIAL | FULL} [ON UPDATE <действие>] [ON DELETE < действие >] [, …]} {[CHECK (<условия целостности>)] [, …]}.

PRIMARY KEY определяет первичный ключ таблицы. UNIQUE – позволяет определить альтернативные (вторичные) ключи таблицы. Операторы FOREIGN KEY и REFERENCES используются для задания связей между таблицами. Можно дополнительно определить каскадное удаление и каскадное обновление записей. Для выполнения ссылочной целостности SQL определяет четыре вида действий: CASCADE, SET NULL, SET DEFAULT, NO ACTION (используется по умолчанию). Последний оператор CHECK служит для задания дополнительных условий для значений полей таблицы. Вышеперечисленные ограничители целостности могут еще дополнительно предваряться фразой CONSTRAINT <имя ограничителя целостности>, что позволит впоследствии отменить это ограничение в операторе ALTER TABLE. Например,

CREATE TABLE Заказы (КодЗаказа INTEGER PRIMARY KEY, КодКлиента INTEGER, ДатаЗаказа DATE, ПримечанияЗаказа VARCHAR(255), CONSTRAINT ВнКлЗаказыКодКлиента FOREIGN KEY (КодКлиента) REFERENCES Клиенты ON UPDATE CASCADE ON DELETE CASCADE);

Расширенный вариант оператора изменения таблицы имеет формат вида:

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

[ADD [COLUMN] <имя поля> <тип данных> [NOT NULL] [UNIQUE]

[DEFAULT <значение по умолчанию>] [CHECK (<условия целостности>)]]

[DROP [COLUMN] <имя поля> [RESTRICT | CASCADE]]

[ADD CONSTRAINT <имя ограничителя> PRIMARY KEY | UNIQUE | FOREIGN KEY | CHECK]

[DROP CONSTRAINT <имя ограничителя> [RESTRICT | CASCADE]]

[ALTER [COLUMN] SET DEFAULT <значение>]

[ALTER [COLUMN] DROP DEFAULT]

Создание индексов

Оператор создания индекса имеет формат вида:

CREATE [UNIQUE] INDEX <имя индекса> ON <имя таблицы> (<имя поля> [ASC | DESC] [,<имя поля> [ ASC | DESC ]... ) [WITH {PRIMARY | DISALLOW NULL | IGNORE NULL}]

Оператор позволяет создать индекс для одного или нескольких столбцов заданной таблицы. Для одной таблицы можно создать несколько индексов. Индексы можно создавать только для таблиц базы данных, но не для представлений. Задав необязательную опцию UNIQUE, можно обеспечить уникальность значений во всех указанных в операторе полях. По существу, создание индекса с указанием признака UNIQUE означает определение потенциального ключа в созданной ранее таблице. С помощью зарезервированного слова PRIMARY можно объявить индексированные поля первичным ключом. В этом случае опцию UNIQUE можно опустить, поскольку уникальность все равно будет обеспечена. При создании индекса можно задать порядок сортировки значений в столбцах – в порядке возрастания ASC (по умолчанию), или в порядке убывания DESC. Для разных полей можно задавать различный порядок сортировки. Например,

CREATE INDEX AscName ON Клиенты(Фамилия, Имя, Отчество);

С помощью оператора WITH можно запретить значения NULL в индексированных полях новых записей (DISALLOW NULL) или запретить включение в индекс записей, имеющих значения NULL в индексированных полях (IGNORE NULL).

Оператор удаления индекса имеет формат вида: DROP INDEX <имя индекса>.