Операторы определения данных

Лекция 20. Операторы определения данных

Определение таблиц

Для создания и модификации объектов в языке SQL определена группа операторов DDL (Data Definition Language – язык описания данных). В группе DDL определены операторы для создания, удаления и изменения таблиц, представлений и индексов. Рассмотрим операторы для работы с таблицами.

Создание таблицы БД осуществляется оператором CREATE TABLE, имеющим следующий синтаксис:

CREATE TABLE  имя таблицы (

Поле_1:          тип_данных        ограничения,

Поле_2:          тип_данных        ограничения)

Главное в команде создания таблицы – определение имени таблицы и описание набора имен полей, которые указываются в соответствующем порядке. Кроме того, этой командой оговариваются типы данных и размеры полей таблицы.

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

Рекомендуемые материалы

CREATE TABLE  TOVAR

(ID INT NOT NULL  PRIMARY KEY,

Name          VARCHAR(50) NOT NULL,

Price MONEY NOT NULL,

Type  VARCHAR(25) NOT NULL,

Sort   VARCHAR(50),

City   VARCHAR(50))

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

PRIMARY KEY – определяет поле первичного ключа, в некоторых реализациях СУБД может использоваться другой способ определения первичного ключа:

CREATE TABLE  TOVAR

(ID INT NOT,

. . .

    City   VARCHAR(50),

PRIMARY KEY (ID));

Подобным образом можно определить сложный первичный ключ:

PRIMARY KEY (поле_1, …, поле_n)

Структура существующей таблицы может быть модифицирована, для модификации таблицы используется команда ALTER TABLE, которая позволяет: добавлять и удалять поля, переопределять первичные и внешние ключи и переопределять ограничения. Объявление начинается с команды ALTER  TABLE  Имя_таблицы, а далее следует объявление модификации. Внутри команды может быть только одна модификация. Возможные модификации:

ALTER COLUMN  Имя_поля  SET  DEFAULT  Выражение

ALTER COLUMN  Имя_поля  DROP  DEFAULT

ADD Имя_поля

DROP  COLUMN  Имя_поля

ADD CONSTRAINT Имя_ограничения

DROP CONSTRAINT  Имя_ограничения

Например, добавим к таблице товар новое поле – единица (Unit):

ALTER  TABLE  TOVAR

ADD  Unit VARCHAR(10)  NOT NULL

Если таблица не пуста, то добавляемый столбец не может быть определен с атрибутом NOT NULL. Этот атрибут означает, что для каждой строки данных соответствующий столбец должен содержать некоторое значение, поэтому добавление столбца с атрибутом NOT NULL приводит к появлению противоречия – уже существующие строки данных таблицы не будут иметь в новом столбце ненулевых значений.

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

Изменение поля (в частности, типа данных) может быть выполнено следующим образом:

ALTER  TABLE  TOVAR

ALTER COLUMN  City CHAR(30)  NOT NULL

Добавить внешний ключ (ключ связи) можно следующим способом:

ALTER  TABLE  TOVAR

    ADD CONSTRAINT  FK_Сategory

    FOREIGN KEY (ID_Сategory)

REFERENCES Сategory

Удаление таблицы осуществляется командой DROP TABLE, при этом используется простой и понятный синтаксис:

DROP TABLE   TOVAR

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

Определение индексов

Индексы представляют собой структуру, позволяющую выполнять ускоренный доступ к строкам таблицы на основе значений одного или более ее столбцов. Наличие индекса может существенно повысить скорость выполнения некоторых запросов и сократить время поиска необходимых данных за счет физического или логического их упорядочивания. Индекс – это набор ссылок, упорядоченных по определенному столбцу таблицы, который в данном случае будет называться индексированным столбцом. Хотя индекс и связан с конкретным столбцом (или столбцами) таблицы, все же он является самостоятельным объектом базы данных.

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

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

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

CREATE [ UNIQUE ] INDEX имя_индекса

  ON имя_таблицы (имя_столбца[ASC|DESC][,...n])

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

CREATE INDEX      New_index

  ON      TOVAR (Name)

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

DROP INDEX          New_index

Контрольные вопросы

1. Какие операторы определения данных вы знаете?

2. Какой синтаксис имеет оператор создания таблицы?

3. Что такое NOT NULL и NULL?

4. Какие проблемы могут быть при добавлении в таблицу поля с ограничением NOT NULL?

5. Как изменить имя поля таблицы?

6. Как удалить таблицу БД?

Информация в лекции "1.2 Организация управления процессом" поможет Вам.

7. Что такое индекс и в чем его назначение?

8. Как объявить индекс?

9. Как удалить индекс?

Задания для самостоятельной работы

Напишите запросы на создание таблиц и связей для БД «Библиотека»