Определение структур базы данных (DDL)

Язык определения данных (DDL) является частью SQL, дающей пользователю возможность создавать различные объекты базы данных и переопределять их структуру, например, создавать или удалять таблицы.

Рассмотрим следующие команды DDL: CREATE TABLE, ALTER TABLE, DROP TABLE.

Чтобы создавать таблицы, используется оператор CREATE TABLE.

Синтаксис оператора для создания таблиц:

TABLE имя_таблицы

(ПОЛЕ1 ТИП ДАННЫХ [NOT NULL],

ПОЛЕ2 ТИП ДАННЫХ [NOT NULL],

ПОЛЕЗ ТИП ДАННЫХ [NOT NULL],

ПОЛЕ4 ТИП ДАННЫХ [NOT NULL],

ПОЛЕ5 ТИП ДАННЫХ [NOT NULL])

 

Ключ таблице назначается при ее создании с помощью опции PRIMARY KEY одному или нескольким полям и является по своей сути ограничивающим условием:

 

CREATE TABLE имя_таблицы PRIMARY KEY

(ПОЛЕ1 ТИП ДАННЫХ [NOT NULL], …)

 

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

 

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

(ПОЛЕ1 ТИП ДАННЫХ [NOT NULL],

ПОЛЕ2 ТИП ДАННЫХ [NOT NULL], …KEY (ПОЛЕ1, ПОЛЕ2))

 

Внешний ключ создается с помощью опции FOREIGN KEY. Внешний ключ создается после определения всех столбцов таблицы следующим образом:

TABLE имя_таблицы_1

(ПОЛЕ1_1 ТИП ДАННЫХ [NOT NULL],

ПОЛЕ1_2 ТИП ДАННЫХ [NOT NULL],

…,

ПОЛЕ2_1 ТИП ДАННЫХ [NOT NULL],

CONSTRAINT ПОЛЕ2_1_FK FOREIGN KEY (ПОЛЕ2_1)

REFERENCES имя_таблицы_2 (ПОЛЕ2_1))

 

Столбец ПОЛЕ2_1 здесь назначается внешним ключом таблицы имя_таблицы_1. Этот внешний ключ ссылается на столбец ПОЛЕ2_1 таблицы имя_таблицы_2.

 

Модификация таблицы

 

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

Стандартный синтаксис команды ALTER TABLE следующий:

 

ALTER TABLE имя_таблицы [MODIFY] [COLUMN имя_столбца]

[ТИП ДАННЫХ|NULL NOT NULL] [RESTRICT|CASCADE]

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

[ADD] [COLUMN] определение столбца

 

а) модификация элементов таблицы. Атрибуты столбца задают правила представления данных в столбце. С помощью команды ALTER TABLE можно менять атрибуты столбца. Под атрибутами здесь понимается следующее: тип данных в столбце; длина, точность или масштаб данных в столбце; разрешение или запрет иметь в столбце значение NULL.

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

добавьте столбец, задав ему атрибут NULL (это значит, что в столбце не обязательно должны присутствовать данные);

введите данные в каждую строку нового столбца таблицы;

убедившись, что столбец содержит значение в каждой из строк таблицы, можно изменить атрибут столбца на NOT NULL;

б) изменение столбцов. При изменении столбцов таблиц нужно учитывать целый ряд моментов. Общие правила следующие:

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

ширину столбца можно уменьшить только до наибольшей длины имеющихся в этом столбце значений;

для столбцов с числовыми данными ширину всегда можно увеличить;

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

для числовых данных можно увеличивать или уменьшать число десятичных знаков;

тип данных в столбце обычно можно изменить.

В некоторых реализациях использование определенных опций оператора ALTER TABLE может быть запрещено. Например, вам могут не позволить удалять столбцы из таблиц. Вместо этого вам нужно будет удалить таблицу и создать новую с нужным числом столбцов. Могут возникнуть проблемы с удалением столбцов из таблицы, зависящей от столбца из другой таблицы, или с удалением столбца, на который ссылается другая таблица. По этому поводу внимательно просмотрите документацию, предлагаемую той реализацией SQL, с которой вы работаете;

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

 

ALTER TABLE имя_таблицыCONSTRAINT имя_таблицы_PK

PRIMARY KEY (имя_поля1, имя_поля2)

 

г) внешние ключи можно назначить таблице следующим образом:

 

ALTER TABLE имя_таблицыCONSTRAINT ID_FK FOREIGN REY (имя_поля)

REFERENCES имя_таблицы (имя_поля)

 

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

 

DROP TABLE имя_таблицы [RESTRICT|CASCADE]

 

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

 

Задание на лабораторную работу

 

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

Создание базы данных. TradeCompany

а) создание базы данных в графическом режиме.

При этом способе выполняются следующие действия:

запустите программу MS SQL Server 2005 посредством основного меню: Пуск-Все программы- MS SQL Server 2005-SQL Server Management Studio;

в появившемся диалоговом окне предлагается ввести имя сервера, оно установлено по умолчанию, щелкните на Connect;

в следующем окне Object Explorer на строке Databases из контекстного меню выберите New Database;

появится окно, в котором надо ввести имя создаваемой базы данных и ОК; в нижней части этого окна в таблице располагается информация о файлах базы данных (разверните окно, чтобы видеть все колонки таблицы); здесь кнопка Add предназначена для случая, если база данных располагается не в одном файле;

закройте окно; убедитесь, что ваша база появилась в списке Database;

удалите базу данных (команда Delete из контекстного меню);

б) создание базы данных посредством команды Transact-SQL.

В окне Object Explorer щелкните на кнопку New Query. В правой части экрана появится окно редактора запросов. Введите в него команду на создание базы данных. Если команда выполнилась успешно, в нижней части окна должно появиться сообщение: Command (s) completed successfully.

В отличие от графического режима, чтобы увидеть в списке баз данных только что созданную базу, необходимо на строке Database из контекстного меню выбрать Refresh.

Создание и модификация таблиц базы данных TradeCompany

Таблицы тоже можно создавать несколькими способами:

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

откройте папку своей базы данных; отметьте строку Tables и в контекстном меню выберите New Table;

в появившемся окне введите наименования столбцов таблицы, выберите тип данных, установите (или уберите) флажок Allows Null; внизу окна установите свойства столбцов;

закройте текущее окно, подтвердите сохранение изменений, введите имя таблицы;

б) нажмите в окне Object Explorer кнопку New Query, откроется окно редактора команд, вводя в это окно команды создания таблицы (создайте таким способом таблицы PRODUCTS_TBL, ORDERS_TBL), нажимая кнопку Execute (выполнение команды), создайте еще одну таблицу;

в) чтобы изменить структуру таблицы, выбираем из контекстного меню Modify Table;

г) для быстрого изучения команд SQL, выделите таблицу базы данных, выберите в контекстном меню Script Table As/ … /New Query Editor Window (вместо многоточия выберите требуемую команду), появится окно редактора команд, в котором можно просмотреть заготовки изучаемых скриптов на создание, удаление, обновление и т.д.

Просмотрите диаграмму связей созданной базы данных (раздел Diagrams)

Требования к отчету

 

Отчет по работе выполняется на бумажном носителе и должен содержать:

описание вариантов создания базы данных;

обоснование выбора типов данных для полей таблиц;

определение первичных и внешних ключей таблиц;

команды создания структур таблиц.

 

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

 

Что означает аббревиатура SQL?

Перечислите типы команд SQL.

Какие способы создания базы данных вы знаете, в чем их отличия?

Сколько файлов используется для базы данных?

Для чего предназначен журнал транзакций?

Объясните создание резервной копии и восстановление базы данных.

Объясните способы удаления базы данных.

Дайте определение реляционной базы данных.

В каких объектах хранится исходная информация базы данных?

Что называется полем таблицы, записью таблицы?

Какая команда определяет структуру таблицы?

Дайте определение первичного ключа таблицы.

Объясните назначение внешних ключей.

С помощью какой команды можно модифицировать структуру таблицы?