DDL – Data Definition Language (язык описания данных)

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

Табельный номер	ФИО	Дата рождения	E-mail	Должность	Отдел
1000	Иванов И.И.	19.02.1955	[email protected]	Директор	Администрация
1001	Петров П.П.	03.12.1983	[email protected]	Программист	ИТ
1002	Сидоров С.С.	07.06.1976	[email protected]	Бухгалтер	Бухгалтерия
1003	Андреев А.А.	17.04.1982	[email protected]	Старший программист	ИТ

В данном случае столбцы таблицы имеют следующие наименования: Табельный номер, ФИО, Дата рождения, E-mail, Должность, Отдел.

Каждый из этих столбцов можно охарактеризовать по типу содержащемся в нем данных:

· Табельный номер – целое число

· ФИО – строка

· Дата рождения – дата

· E-mail – строка

· Должность – строка

· Отдел – строка

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

Для начала будет достаточно запомнить только следующие основные типы данных используемые в MS SQL:

Значение	Обозначение в MS SQL	Описание
Строка переменной длины	varchar(N) и nvarchar(N)	При помощи числа N, мы можем указать максимально возможную длину строки для соответствующего столбца. Например, если мы хотим сказать, что значение столбца «ФИО» может содержать максимум 30 символов, то необходимо задать ей тип nvarchar(30). Отличие varchar от nvarchar заключается в том, что varchar позволяет хранить строки в формате ASCII, где один символ занимает 1 байт, а nvarchar хранит строки в формате Unicode, где каждый символ занимает 2 байта. Тип varchar стоит использовать только в том случае, если вы на 100% уверены, что в данном поле не потребуется хранить Unicode символы. Например, varchar можно использовать для хранения адресов электронной почты, т.к. они обычно содержат только ASCII символы.
Строка фиксированной длины	char(N) и nchar(N)	От строки переменной длины данный тип отличается тем, что если длина строка меньше N символов, то она всегда дополняется справа до длины N пробелами и сохраняется в БД в таком виде, т.е. в базе данных она занимает ровно N символов (где один символ занимает 1 байт для char и 2 байта для типа nchar). На моей практике данный тип очень редко находит применение, а если и используется, то он используется в основном в формате char(1), т.е. когда поле определяется одним символом.
Целое число	int	Данный тип позволяет нам использовать в столбце только целые числа, как положительные, так и отрицательные. Для справки (сейчас это не так актуально для нас) – диапазон чисел который позволяет тип int от -2 147 483 648 до 2 147 483 647. Обычно это основной тип, который используется для задания идентификаторов.
Вещественное или действительное число	float	Если говорить простым языком, то это числа, в которых может присутствовать десятичная точка (запятая).
Дата	date	Если в столбце необходимо хранить только Дату, которая состоит из трех составляющих: Числа, Месяца и Года. Например, 15.02.2014 (15 февраля 2014 года). Данный тип можно использовать для столбца «Дата приема», «Дата рождения» и т.п., т.е. в тех случаях, когда нам важно зафиксировать только дату, или, когда составляющая времени нам не важна и ее можно отбросить или если она не известна.
Время	time	Данный тип можно использовать, если в столбце необходимо хранить только данные о времени, т.е. Часы, Минуты, Секунды и Миллисекунды. Например, 17:38:31.3231603 Например, ежедневное «Время отправления рейса».
Дата и время	datetime	Данный тип позволяет одновременно сохранить и Дату, и Время. Например, 15.02.2014 17:38:31.323 Для примера это может быть дата и время какого-нибудь события.
Флаг	bit	Данный тип удобно применять для хранения значений вида «Да»/«Нет», где «Да» будет сохраняться как 1, а «Нет» будет сохраняться как 0.

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

Для выполнения примеров создадим тестовую базу под названием Test.

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

CREATE DATABASE Test

Удалить базу данных можно командой (стоит быть очень осторожным с данной командой):

DROP DATABASE Test

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

USE Test

Или же выберите базу данных Test в выпадающем списке в области меню SSMS. При работе мною чаще используется именно этот способ переключения между базами.

Теперь в нашей БД мы можем создать таблицу используя описания в том виде как они есть, используя пробелы и символы кириллицы:

CREATE TABLE [Сотрудники](

[Табельный номер] int,

[ФИО] nvarchar(30),

[Дата рождения] date,

[E-mail] nvarchar(30),

[Должность] nvarchar(30),

[Отдел] nvarchar(30)

)

В данном случае нам придется заключать имена в квадратные скобки […].

Но в базе данных для большего удобства все наименования объектов лучше задавать на латинице и не использовать в именах пробелы. В MS SQL обычно в данном случае каждое слово начинается с прописной буквы, например, для поля «Табельный номер», мы могли бы задать имя PersonnelNumber. Так же в имени можно использовать цифры, например, PhoneNumber1.

На заметку
В некоторых СУБД более предпочтительным может быть следующий формат наименований «PHONE_NUMBER», например, такой формат часто используется в БД ORACLE. Естественно при задании имя поля желательно чтобы оно не совпадало с ключевыми словами используемые в СУБД.

 

По этой причине можете забыть о синтаксисе с квадратными скобками и удалить таблицу [Сотрудники]:

DROP TABLE [Сотрудники]

Например, таблицу с сотрудниками можно назвать «Employees», а ее полям можно задать следующие наименования:

· ID – Табельный номер (Идентификатор сотрудника)

· Name – ФИО

· Birthday – Дата рождения

· Email – E-mail

· Position – Должность

· Department – Отдел

Очень часто для наименования поля идентификатора используется слово ID.

Теперь создадим нашу таблицу:

CREATE TABLE Employees(

ID int,

Name nvarchar(30),

Birthday date,

Email nvarchar(30),

Position nvarchar(30),

Department nvarchar(30)

)

Для того, чтобы задать обязательные для заполнения столбцы, можно использовать опцию NOT NULL.

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

-- обновление поля ID

ALTER TABLE Employees ALTER COLUMN ID int NOT NULL

 

-- обновление поля Name

ALTER TABLE Employees ALTER COLUMN Name nvarchar(30) NOT NULL

 

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

Чтобы не быть голословным, приведу несколько примеров тех же команд для СУБД ORACLE:

-- создание таблицы

CREATE TABLE Employees(

ID int, -- в ORACLE тип int - это эквивалент(обертка) для number(38)

Name nvarchar2(30), -- nvarchar2 в ORACLE эквивалентен nvarchar в MS SQL

Birthday date,

Email nvarchar2(30),

Position nvarchar2(30),

Department nvarchar2(30)

);

 

-- обновление полей ID и Name (здесь вместо ALTER COLUMN используется MODIFY(…))

ALTER TABLE Employees MODIFY(ID int NOT NULL,Name nvarchar2(30) NOT NULL);

 

-- добавление PK (в данном случае конструкция выглядит как и в MS SQL, она будет показана ниже)

ALTER TABLE Employees ADD CONSTRAINT PK_Employees PRIMARY KEY(ID);

Для ORACLE есть отличия в плане реализации типа varchar2, его кодировка зависит настроек БД и текст может сохраняться, например, в кодировке UTF-8. Помимо этого длину поля в ORACLE можно задать как в байтах, так и в символах, для этого используются дополнительные опции BYTE и CHAR, которые указываются после длины поля, например:

NAME varchar2(30 BYTE) -- вместимость поля будет равна 30 байтам

NAME varchar2(30 CHAR) -- вместимость поля будет равна 30 символов

Какая опция будет использоваться по умолчанию BYTE или CHAR, в случае простого указания в ORACLE типа varchar2(30), зависит от настроек БД, так же она иногда может задаваться в настройках IDE. В общем порой можно легко запутаться, поэтому в случае ORACLE, если используется тип varchar2 (а это здесь порой оправдано, например, при использовании кодировки UTF-8) я предпочитаю явно прописывать CHAR (т.к. обычно длину строки удобнее считать именно в символах).

 

Но в данном случае если в таблице уже есть какие-нибудь данные, то для успешного выполнения команд необходимо, чтобы во всех строках таблицы поля ID и Name были обязательно заполнены. Продемонстрируем это на примере, вставим в таблицу данные в поля ID, Position и Department, это можно сделать следующим скриптом:

INSERT Employees(ID,Position,Department) VALUES

(1000,N'Директор',N'Администрация'),

(1001,N'Программист',N'ИТ'),

(1002,N'Бухгалтер',N'Бухгалтерия'),

(1003,N'Старший программист',N'ИТ')

В данном случае, команда INSERT также выдаст ошибку, т.к. при вставке мы не указали значения обязательного поля Name.
В случае, если бы у нас в первоначальной таблице уже имелись эти данные, то команда «ALTER TABLE Employees ALTER COLUMN ID int NOT NULL» выполнилась бы успешно, а команда «ALTER TABLE Employees ALTER COLUMN Name int NOT NULL» выдала сообщение об ошибке, что в поле Name имеются NULL (не указанные) значения.

Добавим значения для полю Name и снова зальем данные:

INSERT Employees(ID,Position,Department,Name) VALUES

(1000,N'Директор',N'Администрация',N'Иванов И.И.'),

(1001,N'Программист',N'ИТ',N'Петров П.П.'),

(1002,N'Бухгалтер',N'Бухгалтерия',N'Сидоров С.С.'),

(1003,N'Старший программист',N'ИТ',N'Андреев А.А.')

Так же опцию NOT NULL можно использовать непосредственно при создании новой таблицы, т.е. в контексте команды CREATE TABLE.

Сначала удалим таблицу при помощи команды:

DROP TABLE Employees

Теперь создадим таблицу с обязательными для заполнения столбцами ID и Name:

CREATE TABLE Employees(

ID int NOT NULL,

Name nvarchar(30) NOT NULL,

Birthday date,

Email nvarchar(30),

Position nvarchar(30),

Department nvarchar(30)

)

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

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

ALTER TABLE Employees ALTER COLUMN Name nvarchar(30) NULL

Или просто:

ALTER TABLE Employees ALTER COLUMN Name nvarchar(30)

Так же данной командой мы можем изменить тип поля на другой совместимый тип, или же изменить его длину. Для примера давайте расширим поле Name до 50 символов:

ALTER TABLE Employees ALTER COLUMN Name nvarchar(50)

 

Первичный ключ

При создании таблицы желательно, чтобы она имела уникальный столбец или же совокупность столбцов, которая уникальна для каждой ее строки – по данному уникальному значению можно однозначно идентифицировать запись. Такое значение называется первичным ключом таблицы. Для нашей таблицы Employees таким уникальным значением может быть столбец ID (который содержит «Табельный номер сотрудника» — пускай в нашем случае данное значение уникально для каждого сотрудника и не может повторяться).

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

ALTER TABLE Employees ADD CONSTRAINT PK_Employees PRIMARY KEY(ID)

Где «PK_Employees» это имя ограничения, отвечающего за первичный ключ. Обычно для наименования первичного ключа используется префикс «PK_» после которого идет имя таблицы.

Если первичный ключ состоит из нескольких полей, то эти поля необходимо перечислить в скобках через запятую:

ALTER TABLE имя_таблицы ADD CONSTRAINT имя_ограничения PRIMARY KEY(поле1,поле2,…)

Стоит отметить, что в MS SQL все поля, которые входят в первичный ключ, должны иметь характеристику NOT NULL.

Так же первичный ключ можно определить непосредственно при создании таблицы, т.е. в контексте команды CREATE TABLE. Удалим таблицу:

DROP TABLE Employees

А затем создадим ее, используя следующий синтаксис:

CREATE TABLE Employees(

ID int NOT NULL,

Name nvarchar(30) NOT NULL,

Birthday date,

Email nvarchar(30),

Position nvarchar(30),

Department nvarchar(30),

CONSTRAINT PK_Employees PRIMARY KEY(ID) -- описываем PK после всех полей, как ограничение

)

После создания зальем в таблицу данные:

INSERT Employees(ID,Position,Department,Name) VALUES

(1000,N'Директор',N'Администрация',N'Иванов И.И.'),

(1001,N'Программист',N'ИТ',N'Петров П.П.'),

(1002,N'Бухгалтер',N'Бухгалтерия',N'Сидоров С.С.'),

(1003,N'Старший программист',N'ИТ',N'Андреев А.А.')

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

CREATE TABLE Employees(

ID int NOT NULL CONSTRAINT PK_Employees PRIMARY KEY, -- указываем как характеристику поля

Name nvarchar(30) NOT NULL,

Birthday date,

Email nvarchar(30),

Position nvarchar(30),

Department nvarchar(30)

)

На самом деле имя ограничения можно и не задавать, в этом случае ему будет присвоено системное имя (наподобие «PK__Employee__3214EC278DA42077»):

CREATE TABLE Employees(

ID int NOT NULL,

Name nvarchar(30) NOT NULL,

Birthday date,

Email nvarchar(30),

Position nvarchar(30),

Department nvarchar(30),

PRIMARY KEY(ID)

)

Или:

CREATE TABLE Employees(

ID int NOT NULL PRIMARY KEY,

Name nvarchar(30) NOT NULL,

Birthday date,

Email nvarchar(30),

Position nvarchar(30),

Department nvarchar(30)

)

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

ALTER TABLE Employees DROP CONSTRAINT PK_Employees

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

Подытожим

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

· CREATE TABLE имя_таблицы (перечисление полей и их типов, ограничений) – служит для создания новой таблицы в текущей БД;

· DROP TABLE имя_таблицы – служит для удаления таблицы из текущей БД;

· ALTER TABLE имя_таблицы ALTER COLUMN имя_столбца … – служит для обновления типа столбца или для изменения его настроек (например для задания характеристики NULL или NOT NULL);

· ALTER TABLE имя_таблицы ADD CONSTRAINT имя_ограничения PRIMARY KEY (поле1, поле2,…) – добавление первичного ключа к уже существующей таблице;

· ALTER TABLE имя_таблицы DROP CONSTRAINT имя_ограничения – удаление ограничения из таблицы.