Структура языка SQL

Этот стандарт оставляет  право выбора конкретного механизма создания и уничтожения каталогов за разработчиком СУБД, однако регламентирует механизм создания и удаления схем. Оператор определения схемы имеет следующий формат (упрощенно):

CREATE. SCHEMA [nаmе | AUTHORIZATION  Creator-Identifier]

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

CREATE SCHEMA SqlTests AUTHORIZATION Smith;

В стандарте ISO также указано, что  должна существовать возможность определить в рамках данного оператора диапазон средств, доступных пользователям создаваемой схемы. Однако конкретные способы определения подобных привилегий в разных СУБД различаются. Схема удаляется с помощью оператора DROP SCHEMA, который имеет следующий формат:

DROP SCHEMA Name [ RESTRICT | CASCADE]

Если указано ключевое слово RESTRICT (именно оно принимается по умолчанию), схема должна быть пустой, иначе выполнение операции будет отменено. Если указано ключевое слово CASCADE, при выполнении оператора будут автоматически удалены все связанные с удаляемой схемой объекты, причем в порядке, указанном выше. Если одна из этих операций удаления будет завершена неудачно, выполнение всего оператора DROP SCHEMA будет отменено. Общий эффект от выполнения оператора DROP SCHEMA с параметром CASCADE может затронуть значительную часть базы данных, поэтому подобные операторы должны вводиться с исключительной осторожностью.

В настоящее время операторы CREATE SCHEMA и DROP SCHEMA реализованы в очень немногих СУБД.

Создание таблиц (оператор CREATE TABLE).

После создания общей структуры  базы данных можно приступить к созданию таблиц, представляющих отношения, входящие в состав проекта базы данных. Для этой цели используется оператор CREATE TABLE, имеющий следующий общий формат:

CREATE TABLED TableName

{ (columName data Type  [NOT NULL] [UNIQUE]

[DEFAULT defaultoption]  [CHSCK (searchCondition}:] [, …] }

[PRIMARY KEY (ListOfColumns)  ]

{ [UNIQUE {listOfCoIumns) ]  [,. . .] }

{ [FOREIGN KEY (listOfFdreignKeyColumns)

REFERENCES ParentTabl eName [(listOfCandidateKeyColuims) ],

[MATCH {PARTIAL  | FULL}

[ON UPDATE. referentialAction]

[ON DELETE referential Action] } [, , .-.] }

{[CHECK (searchCondtitioa)]  [, . , , ] } )

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

В результате выполнения этого оператора  будет создана таблица, имя которой определяется параметром TableName, состоящая из одного или нескольких столбцов типа dataType. Для задания значения, применяемого по умолчанию при вставке данных в конкретный столбец, предусмотрена необязательная конструкция DEFAULT. В базе данных это значение применяется по умолчанию в тех случаях, если в операторе INSERT не задано значение для такого столбца. Кроме прочих значений, опция определения применяемого по умолчанию значения defaultoption может включать литералы. Конструкции КОТ NULL, UNIGUE и CHECK рассматривались в предыдущем разделе. Остальные конструкции известны под названием ограничений таблицы и могут быть дополнительно обозначены с помощью следующей конструкции:

CONSTRAINT ConstraintName

Эта конструкция позволяет в  дальнейшем удалить ограничение, указав его имя в операторе ALTER TABLE, как описано ниже. Конструкция PRIMARY KEY определяет один или несколько столбцов, которые образуют первичный ключ таблицы. Если эта конструкция предусмотрена в диалекте SQL, реализованном в конкретной базе данных, то она должна применяться при создании каждой таблицы. По умолчанию для всех столбцов, представляющих первичный ключ, предусмотрено применение ограничения NOT NULL. При создании таблицы разрешено использование только одной конструкции PRIMARY KEY. База данных отвергает все попытки выполнения операций INSERT или UPDATE, которые влекут за собой создание строки с повторяющимся значением в столбце (столбцах) PRIMARY KEY. Таким образом, в базе данных гарантируется уникальность значений первичного ключа. В конструкции FOREIGN KEY определяется внешний ключ (дочерней) таблицы и ее связь с другой (родительской) таблицей. Эта конструкция позволяет реализовать ограничения ссылочной целостности и состоит из следующих частей.

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

• Вспомогательная конструкция REFERENCES, указывающая на родительскую таблицу (т.е. таблицу, в которой определен  соответствующий потенциальный  ключ). Если список UstOfCandidateKsyCalumns опущен, предполагается, что определение внешнего ключа совпадает с определением первичного ключа родительской таблицы. В таком случае родительская таблица должна иметь в своем операторе CREATE TABLE конструкцию PRIMARY KEY.

• Необязательное правило обновления (ON UPDATE) для определения взаимосвязи между таблицами, которое указывает, какое действие (referentialAction) должно выполняться при обновлении в родительской таблице потенциального ключа, соответствующего внешнему ключу дочерней таблицы. В качестве параметра referentiaJAcCion можно указать CASCADE, SET NULL, SET DEFAULT ИЛИ NO ACTION. Если КОНСТРУКЦИЯ ON UPDATE опущена, то по умолчанию подразумевается, что никакие действия не выполняются, в соответствии со значением NO ACTION.

• Необязательное правило удаления (ON DELETE) для определения взаимосвязи  между таблицами, которое указывает, какое действие (referentialAction) должно выполняться  при удалении строки из родительской таблицы, которая содержит потенциальный  ключ, соответствующий внешнему ключу дочерней таблицы. Определение параметра referentzalAction совпадает с определением такого же параметра для правила ON UPDATE.

• По умолчанию ограничение ссылочной  целостности удовлетворяется, если любой компонент внешнего ключа  имеет значение NULL или в родительской таблице есть соответствующая строка. Опция MATCH позволяет ввести дополнительные ограничения, касающиеся применения значений NULL во внешнем ключе. Если задана опция MATCH FULL, то либо все компоненты внешнего ключа должны быть пусты (NULL), либо все должны иметь непустые значения. А если задана опция MATCH PARTIAL, то либо все компоненты внешнего ключа должны быть пусты (NULL), либо в родительской таблице должна существовать хотя бы одна строка, способная удовлетворить это ограничение, если все остальные значения NULL были подставлены правильно. Некоторые авторы утверждают, что в ограничениях ссылочной целостности следует применять только опцию MATCH FULL.

В операторе создания таблицы может  быть задано любое количество конструкций FOREIGN KEY. Конструкции CHECK и CONSTRAINT позволяют определять дополнительные ограничения. Если конструкция CHECK используется в качестве ограничения столбца, то она может ссылаться только на определяемый столбец. Ограничения фактически контролируются после применения каждого оператора SQL к таблице, на которой они заданы, но такая проверка может быть отложена до окончания той транзакции, в состав которой входит текущий оператор SQL.

Модификация определения таблицы (оператор ALTER TABLE).

В стандарте ISO предусмотрено применение оператора ALTER TABLE для изменения структуры таблицы после ее создания. Определение оператора ALTER TABLE состоит из шести опций, позволяющих выполнить следующие действия:

• ввести новый столбец в таблицу;

• удалить столбец из таблицы;

• ввести новое ограничение таблицы;

• удалить ограничение таблицы;

• задать для столбца значение, применяемое по умолчанию;

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

Ниже приведен основной формат этого оператора.

ALTER TABLE TableName

[ADD [COLUMN] columneName [RESTRICT | CASCADE]]

[DROP [COLUMN] columnName [RESTRICT | CASCADE]]

[ADD [CONSTRAINT [ConstreintName]] tableConstraintDefinition]

[DROP CONSTRAINT ConstraintName [RESTRICT | CASCADE]]

[ALTER [COLUMN] SET DEFULT  defaultOption]

[ALTER [COLUMN] DROP  DEFAULT]

Почти все параметры данного  оператора совпадают с параметрами  оператора CREATE TABLE, описанного в предыдущем разделе, В качестве параметра с  определением ограничения таблицы CaJbleConstraintDefinition может применяться одна из конструкций PRIMARY KKY, UNIQUE, FOREIGN KEY или CHECK. Конструкция ADD COLUMN аналогична конструкции определения столбца в операторе CREATE TABLE. В конструкции DROP COLUMN задается имя столбца, удаляемого из определения таблицы, и имеется необязательная опция, позволяющая указать, является ли действие операции DROP каскадным или нет, как показано ниже.

• RESTRICT, Операция DROP отвергается, если на данный столбец имеется ссылка в другом объекте базы данных (например, в определении представления). Это значение опции предусмотрено по умолчанию.

• CASCADE. Выполнение операции DROP продолжается в любом случав и ссылки на столбец  автоматически удаляются из любых  объектов базы данных, где они имеются. Эта операция выполняется каскадно, поэтому если столбец удаляется из объекта, содержащего ссылку, то в базе данных выполняется проверка того, имеются ли ссылки на этот столбец я каком-либо ином объекте, такие ссылки уничтожаются и в этом объекте, и т.д.

Удаление таблиц (оператор DROP TABLE).

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

DROP TABLE.TableWame [RESTRICT I CASCADE]

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

DROP TABLE PropertyForRent;

Однако следует отметить, что  эта команда удалит не только указанную  таблицу, но и все входящие в нее  строки данных. Если требуется удалить из таблицы лишь строки данных, сохранив в базе описание самой таблицы, то следует использовать оператор DELETE. Оператор DROP TABLE дополнительно позволяет указывать, следует ли операцию удаления выполнять каскадно.

• RESTRICT. Операция DROP отвергается, если в базе данных имеются другие объекты, существование которых зависит от того, существует ли в базе данных удаляемая таблица.

• CASCADE. Операция DROP продолжается, и  из базы данных автоматически удаляются  все зависимые объекты (и объекты, зависящие от этих объектов).

Общий эффект от выполнения оператора DROP TABLE с ключевым словом CASCADE может  распространяться на значительную часть  базы данных, поэтому подобные операторы  следует использовать с максимальной осторожностью. Чаще всего оператор DROP TABLE используется для исправления ошибок, допущенных при создании таблицы. Если таблица была создана с неправильной структурой, можно воспользоваться оператором DROP TABLE для ее удаления, после чего создать таблицу заново.

Создание индекса (оператор CREATE INDEX).

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

CREATE [UNIQUE] INDEX IndexName

ON TableName (columnName [ASC| DESC] [, ….])

Указанные в операторе столбцы составляют ключ индекса и должны быть перечислены в порядке уменьшения значимости. Индексы могут создаваться только для таблиц базы данных, но не для представлений. Если в операторе указано ключевое слово UNIQUE, уникальность значений ключа индекса будет автоматически поддерживаться СУБД. Требование уникальности значений обязательно для первичных ключей, а также, возможно, и для других столбцов таблицы (например, для альтернативных ключей). Хотя создание индексов осуществимо в любой момент, при построении индекса для уже заполненной данными таблицы могут возникнуть проблемы, связанные с дублированием данных в различных строках. Следовательно, имеет смысл создавать уникальные индексы (по крайней мере, для первичного ключа) непосредственно при создании таблицы. В результате система сразу же возьмет на себя контроль над уникальностью значений данных в соответствующих столбцах.

Для таблиц Staff и PropertyForRent должны быть созданы, по крайней мере, следующие  индексы:

CREATE UNIQUE INDEX ScaffNoInd ON Staff (staffNo);

CREATE UNIQUE INDEX PropertyNoInd ON PropertyForRent (propertyNo);

Для каждого из ключевых столбцов может быть указан порядок следования значений — по возрастанию (ASC) или по убыванию (DESC), причем значение АЗС используется по умолчанию. Например, для таблицы PropertyForRent можно создать следующий индекс:

CREATE INDEX Rentlnd ON PropertyForRent (city, rent);

При обработке этого оператора  будет создан файл под именем Rentlnd, содержащий данные вновь созданного индекса таблицы PropertyForRent. Строки в  этом файле будут расположены  в порядке возрастания значений столбца city, а внутри них — в  порядке возрастания значений столбца rent.

Удаление индекса (оператор DROP INDEX).

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

DROP INDEX JndexWame

С помощью приведенного ниже оператора  будет удален индекс, созданный в  предыдущем примере.

DROP INDEX Rentlnd;

 

2.2 Представления

 

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

С точки зрения пользователя базы данных представление выглядит как реальная таблица данных, содержащая набор поименованных столбцов и строк данных. Но в отличие от реальных таблиц представления не всегда существуют в базе как некоторый набор сохраняемых значений данных. В действительности доступные через представления строки и столбцы данных являются результатом выполнения запроса, заданного при определении представления. СУБД сохраняет определение представления в базе данных. Обнаружив ссылку на представление, СУБД применяет один из, двух следующих подходов для формирования представления. При первом подходе СУБД отыскивает определение представления, и преобразуют исходный запрос, лежащий в основе представления, в эквивалентный запрос к таблицам, использованным в определении представления, после чего модифицированный запрос выполняется. Этот процесс слияния запросов, называемый заменой представления (под этим подразумевается замена представления оператором SQL, который обращается к базовым таблицам). При втором подходе, который называется материализацией представления, готовое представление хранится в базе данных в виде временной таблицы, а его актуальность постоянно поддерживается по мере обновления всех таблиц, лежащих в его основе. Но вначале познакомимся с тем, как создаются и используются представления.