Введение в SQL - 3

SQL - язык определения данных (продолжение)


В этой статье мы продолжим изучать операции над объектами базы данных. Даваемый материал основан на предыдущих статьях цикла. Также полагаем, что читатель имеет теоретическую подготовку в области реляционной алгебры (например, см. статьи Alf'а в этом же разделе статей).

Для работы нам по-прежнему будут нужны Microsoft SQL Server (предпочтительно) или Microsoft Access.


Рабочая база данных


Продолжим работу по созданию базы данных, которую начали в предыдущей статье. Вспомним физическую модель базы данных нашей упрощённой библиотеки (см. рисунок 1).


Рисунок 1 - Физическая модель базы данных

Условные обозначения:
PK - первичный ключ;
FK - внешний ключ;
I - индекс;
U - уникальное значение;
полужирным шрифтом выделены обязательные поля (не могущие быть пустыми - содержать NULL);
на связях: чёрточка означает обязательное наличие записи, участвующей в связи;
кружок - необязательное наличие (допустимо отсутствие связанных записей).

Вспомним, что в прошлой статье мы начали создавать базу данных, и (если выполнены упражнения), имеем созданные полностью таблицы "Жанры" (Ganres) и "Отделы" (Sections), а также не полностью законченные таблицы "Авторы" (Authors) и "Читатели" (Readers). Приступим. (Не забудьте выполнить команду USE, чтобы сделать нашу базу данных текущей.)


Таблицы (продолжение)


Мы уже умеем создавать, изменять и удалять таблицы. Умеем добавлять, изменять и удалять поля таблиц. Знаем, как задаётся тип поля таблицы, как определить первичный ключ

Теперь познакомимся с уникальными значениями. В таблице "Читатели" (Readers) поле "Паспортные данные" (ReaderPassportData) должно быть уникальным. Как и
для первичного ключа, это означает, что в двух любых записях таблицы мы не должны найти одинаковых значений этого поля. Фактически, это поле является естественным первичным ключом, просто мы его не используем в качестве такового, так как оно текстовое. (Подробнее о причинах неиспользования мы поговорим ниже, когда будем разбирать связи.) Так как мы уже имеем первичный ключ, то могли бы и не заботится об уникальности поля "Паспортные данные" (или заботиться об этом в клиентском приложении, использующим базу данных), но для усиления защиты от некорректных (с точки зрения предметной области) данных мы добавим проверку на уникальность. Проверки уникальности полей прямо в базе данных незаменимы в крупных СУБД, поддерживающих параллельную работу нескольких пользователей с одной и той же таблицей. Для того, чтобы СУБД проверяла уникальность значений поля, в свойства поля можно добавить специальное определение.

Код: (sql)
ALTER TABLE Readers
DROP COLUMN ReaderPassportData

ALTER TABLE Readers
ADD ReaderPassportData VARCHAR(100) UNIQUE
(Access, SQL Server)

(Напомним, что инструкции лучше выполнять по одной). Ключевое слово UNIQUE определяет свойство уникальности поля. К такому полю по умолчанию применяется свойство NOT NULL. В самом деле, если допустить свойство NULL, СУБД не сможет позволить без нарушения уникальности более чем одной записи содержать значение NULL.
Часто возникают случаи, когда при создании записи некоторые поля можно заполнять заранее известным значениями. Например, в таблице "Задолженности" (Debts) есть поле "Дата начала задолженности" (DebtDate). Очевидно, что эта дата вносится в момент создания самой задолженности, поэтому мы можем установить для неё значение по умолчанию. Также по умолчанию мы можем устанавливать нормальную длительность задолженности (поле DebtPeriod), скажем, 2 недели - 14 дней. Значение по умолчанию определяется ключевым словом DEFAULT, которое задаётся как и любое другое свойство поля. Выражение, вычисляющее значение по умолчанию идёт сразу за ключевым словом. Так как таблица "Задолженности" (Debts) у нас отсутствует, то создадим её, пока не определяя связей и индексов.

Код: (sql)
CREATE TABLE Debts
(DebtID int identity(1,1) PRIMARY KEY,
ReaderID int NOT NULL,
BookID int NOT NULL,
DebtDate datetime NOT NULL DEFAULT GETDATE(),
DebtPeriod int NOT NULL DEFAULT 14,
DebtNotes text)
(SQL Server)

В Access мне не известно способа через JetSQL задать значение по умолчанию для поля. Однако это можно сделать в дизайнере таблицы или макросом VBA. В примере использована функция SQL Server GETDATE(), возвращающая текущую дату. В других СУБД подобная функция может называться иначе, скажем, в Access это Date(). На примере функции даты видно, что значениями по умолчанию могут быть не только константы, но и вычисляемые выражения.
Однако, использовать для вычисления значения по умолчанию поля самой записи в SQL Server запрещено.
В таблице можно задавать вычисляемые поля. Такие поля поддерживаются не всеми СУБД, но достаточно распространены. Вычисляемые поля являются виртуальными: они не хранят в базе данных своих значений, а определяют их в момент чтения записи. Вычисляемые поля удобно использовать в тех случаях, когда выгоднее получить из СУБД уже готовую таблицу значений, а не обрабатывать все полученные записи на стороне клиентского приложения.
Классическим примером вычисляемого поля является стоимость, получаемая умножением цены на количество товара. Определим в нашей таблице "Задолженности" вычисляемое поле "Срок возврата" (DebtDeadline), которое будем определять прибавлением длительности задолженности к дате начала задолженности. Для этого воспользуемся функцией SQL Server DATEADD().

Код: (sql)
ALTER TABLE Debts
ADD DebtDeadline AS DATEADD(DAY,DebtPeriod,DebtDate)
(SQL Server)

Обратите внимание, что тип вычисляемого поля не задаётся. Теперь, при смене даты создания задолженности и/или допустимой длительности задолженности мы автоматически получим дату крайнего срока возврата книги в поле "DebtDeadline". В вычисляемых полях можно использовать другие поля записи. Вычисляемые поля не могут быть внешними ключами, не могут иметь свойство NOT NULL, не могут иметь значения по умолчанию, не могут быть заполнены данными при операциях вставки или обновления данных, однако могут быть частью первичного ключа или уникального значения, могут быть индексированы.
Ещё одним объектом таблицы является проверка. Проверкой называется условие, при истинности которого разрешается операция над записью (создание, изменение, удаление). В проверках, как и в вычисляемых полях, можно использовать поля самой записи для определения условия. Проверку можно задать как свойство поля через ключевое слово CHECK и следующее за ним в скобках логическое выражение. Например, в таблице "Книги" (Books) есть два поля, нуждающиеся в проверке задаваемых данных. Значение поля "Год издания" (BookYear) не может быть больше текущего года, а значение поля "Цена" (Price) не может быть меньше 0,00. Создадим таблицу "Книги" (Books) пока без связей и индексов.

Код: (sql)
CREATE TABLE Books
(BookID int identity(1,1) PRIMARY KEY,
SectionID int NOT NULL,
BookName nvarchar(255) NOT NULL,
BookPublisher nvarchar(100) NOT NULL,
BookYear int CHECK(BookYear <= DATEPART(YEAR,GETDATE())),
BookPrice money NOT NULL CHECK(BookPrice >= 0),
BookCode nvarchar(20) UNIQUE,
BookAnnotation text,
BookNotes text)
(SQL Server)

Для JetSQL в Access свойство CHECK также не определено. В примере использована ещё одна функция SQL Server DATEPART(), возвращающая целое значение определённой части даты (в нашем случае год).


Замечания по вычислению выражений в SQL


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

* Над числами и числовыми переменными можно выполнять арифметические операции. Выражения строятся в инфиксной (обычной) нотации с высшим проритетом у операций умножения ("*"), деления ("/"), модуля числа ("%"), и более низким приоритетом у операций сложения ("+") и вычитания ("-").
Можно использовать круглые скобки для группировок. Например, (3.5 - 1.5) * 2 = 4.
Над числами и двоичными данными можно выполнять битовые операции: и ("&"), или ("|"), исключающее или ("^").
Унарные операции: арифметические "+" и "-", а также битовая инверсия не ("~").
* Над логическими значениями допустимы логические операции: и ("AND"), или ("OR"), не ("NOT"). У логических операций низший приоритет в выражениях.
* Имеются логико-множественные операторы, возвращающие логическое значения сравнения скалярного выражения с элементами множества: между ("BETWEEN"), например, Year BETWEEN 1920 AND 1930 или обратное условие Year NOT BETWEEN 1853 AND 1855 - оператор применим для любых перечислимых типов данных. все ("ALL"), некоторый ("SOME" или "ANY"), существует ("EXISTS"), в ("IN") - с ними мы познакомимся подробно, когда будем изучать запросы в языке манипулирования данными, однако оператор IN можно использовать в CHECK свойствах полей. Например, CHECK(TableID IN (1, 3, 5, 7, 11)).
* При работе со строками можно использовать операции конкатенации ("+") и сравнения по шаблону ("LIKE"). При сравнении по шаблону применяются два символа-маски: "_" - одиночный произвольный символ и "%" - произвольная подстрока.
Например, выражение StrField LIKE '_АРАН' будет истинным как для "ВАРАН", так и для "ТАРАН", но ложным для "БУРАН", а StrField LIKE 'БА%АН' истинно как для "БАКЛАН", так и для "БАРАБАН", но ложно для "ТАРАН".
* Значения даты обычно задаются строковыми константами, которые затем преобразуются к типу даты. (В ряде случаев в SQL Server такие преобразования выполняются автоматически, но не всегда). Воспринимаемый СУБД формат даты, определённой в строке, часто зависит от настроек локализации (под конкретную страну).
Универсальными можно считать американский формат ММ-DD-YYYY или специальный формат, где дата записывается одним числом, YYYYMMDD.
* Операторы точного сравнения: больше (">"), меньше ("<"), больше или равно (">="), меньше или равно ("<="), равно ("="), не равно ("<>") применимы к числовым, днежным, строковым типам, к типам даты.
* Для преобразования типов в стандарте SQL определена функция CAST(). Формат записи следующий:

CAST(<выражение> AS <тип>)

Например, CAST('02-28-2004' AS datetime) - преобразует строку в дату, CAST(180 AS varchar(10)) - преобразует число в строку. В качестве выражения может быть исползовано что угодно: константа, формула, переменная.



Таблицы (окончание)


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

Вспомним, что первичным ключом называется такое поле (или набор полей) таблицы, значения которого однозначно определяют запись в таблице. Такое поле содержит только уникальные значения. Внешним ключом называется такое поле (или набор полей) таблицы, значения которого полностью совпадают с некоторыми значениями первичного ключа какой-либо другой таблицы. Такое соответствие значений ключевых полей образует связь. Участвующие в связи таблицы называются: главной - содержащая поле первичного ключа, подчинённой - содержащая поле внешнего ключа.
Связи бывают двух видов: в общем случае "один ко многим" ("1:М"), когда одной записи главной таблицы соответствует несколько записей подчинённой таблицы (в нескольких записях подчинённой таблицы в поле внешнего ключа содержится одинаковое значение, равное значению поля первичного ключа одной из записей главной таблицы); частным случаем является связь "один к одному" ("1:1"), причём подавляющее большинство СУБД такую связь в явном виде не поддерживает (её можно получить, используя свойство уникальности для поля внешнего ключа).
Ещё известен тип связи "многие ко многим" ("М:М"), однако в СУБД он не поддерживается (лишь в крупных системах с мощными средствами автоматизации проектирования баз данных, бывает, встречаются связи такого типа, но в любом случае на уровне физического представления данных они заменяются на связи "1:М" через вспомогательные таблицы.

Например, в нашей учебной базе данных (см. рисунок 1) есть три связи типа "М:М": между читателями (Readers) и книгами (Books), между книгами и произведениями (Works) и между произведениями и авторами (Authors).
Для приведения этих связей к виду "1:М" определены вспомогательные таблицы: задолженности (Debts), сборники (Collections) и авторства (Athorships).
Следует отметить, что вспомогательные таблицы не всегда являются "лишними". Примером тому служит таблица задолженностей (Debts), хранящая помимо фактов связей таблиц читателей (Readers) и книг (Books) дополнительные сведения.

В качестве ключей лучше использовать маленькие по размеру значения полей (например, числа, короткие (в несколько символов) строки). Это экономит память, упрощает индексацию и поиск, отслеживание уникальности (самое главное). Кроме того, небольшие значения проще копировать при определении внешних ключей.

Первоначально (в ранних СУБД) связи между таблицами явно (как особые объекты базы) не устанавливались. Используя механизм ключей пользователь сам вычислял связанные записи. В ходе эксплуатации таких баз в результате ошибок пользователей возникали нарушения целостности и непротиворечивости - важнейших требований, отличающих базу данных от хаоса. При модификации базы данных для поддержания её целостности пользователь должен был самостоятельно выполнять ряд действий:

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


Естественно, возникло желание (которое в современных многопользовательских СУБД с параллельной обработкой запросов является необходимым требованием) автоматизировать эти действия.
Объект "связь" как раз и является тем сигналом для СУБД, который указывает на необходимость вышеописанных проверок при изменении данных в базе.

В SQL через особое свойство поля, обозначаемое ключевым словом REFERENCES можно создать связь между данным полем, являющимся внешним ключом, и полем другой таблицы, являющимся первичным ключом. Например, для таблицы "Задолженности" (Debts) создадим связь с таблицей читатели.

Код: (sql)
ALTER TABLE Debts
DROP COLUMN ReaderID

ALTER TABLE Debts
ADD ReaderID INT REFERENCES Readers(ReaderID) ON DELETE CASCADE
(SQL Server)

В примере также использовано определение ON DELETE CASCADE, задающее особое поведение СУБД при работе со связью. Без такого определения всякая попытка удалить запись из главной таблицы при наличии связанных записей в подчинённой таблице будет блокирована.
Указанное определение позволит удалить все подчинённые записи вместе с главной. В самом деле, если мы удаляем из базы данных читателя, зачем нам хранить связанные с ним задолженности? Они будут удалены автоматически. Удаление производится каскадно, т.е. если бы подчинённая таблица "Задолженности" (Debts) являлась бы главной для третьей таблицы, то и в третьй таблице удалились бы все связанные с удаляемыми задолженностями записи (если на той связи было бы установлено такое же определение).
Для закрепления материала создадим таблицу "Произведения" (Works) с нужными связями.

Код: (sql)
CREATE TABLE Works
(WorkID AUTOINCREMENT PRIMARY KEY,
GanreID INTEGER REFERENCES Ganres(GanreID),
WorkName VARCHAR(255) NOT NULL,
WorkCreationDate VARCHAR(50),
WorkSize INTEGER);

Код: (sql)
CREATE TABLE Works
(WorkID int identity(1,1) PRIMARY KEY,
GanreID int REFERENCES Ganres(GanreID),
WorkName nvarchar(255) NOT NULL,
WorkCreationDate nvarchar(50),
WorkSize int CHECK(WorkSize >= 1))
(Access)

(SQL Server)

И в заключении определения таблиц познакомимся с ограничениями. Ограничения - это объекты, ассоциированные с таблицей, задающие поведение СУБД при работе с записями таблицы. Фактически, со всеми ограничениями мы уже познакомились: первичный и внешний ключи, уникальные значения, значения по умолчанию и проверки - это всё ограничения.

Их можно (и даже рекомендуется) записывать отдельно в конструкции CONSTRAINT. Описываются они наравне с полями при задании таблицы. Также к ним применимы операции ADD и DROP. Ещё одним свойством ограничений является то, что они могут быть применены к нескольким полям сразу. Описание поименованных ограничений отдельно от полей даёт ряд преимуществ при модификации таблиц. Например, для удаления внешнего ключа или уникального значения не нужно удалять поле со всеми данными в нём, достаточно удалить лишь ограничение.

Каждое ограничение имеет собственное имя. Если ограничение задано не явно, а как свойство поля (как мы делали до сих пор), то ему автоматически назначается имя. Способ узнать имя такого ограничения зависит от СУБД. Так как имён имеющихся у нас ограничений мы не знаем, удалим таблицу "Задолженности" (Debts) целиком и пересоздаим её заново, описывая ограничения отдельно.

Код: (sql)
DROP TABLE Debts

CREATE TABLE Debts
(DebtID AUTOINCREMENT NOT NULL,
ReaderID INT NOT NULL,
BookID INT NOT NULL,
DebtDate DATETIME NOT NULL,
DebtPeriod INT NOT NULL,
DebtNotes MEMO,
CONSTRAINT Debts_PK PRIMARY KEY (DebtID),
CONSTRAINT Debts_Readers_FK1 FOREIGN KEY (ReaderID)
REFERENCES Readers,
CONSTRAINT Debts_Books_FK1 FOREIGN KEY (BookID)
REFERENCES Books);

Код: (sql)
DROP TABLE Debts

CREATE TABLE Debts
(DebtID int identity(1,1) NOT NULL,
ReaderID int NOT NULL,
BookID int NOT NULL,
DebtDate datetime NOT NULL CONSTRAINT DebtDate_Default
DEFAULT GETDATE(),
DebtPeriod int NOT NULL CONSTRAINT DebtPeriod_Default
DEFAULT 14,
DebtDeadline AS DATEADD(DAY,DebtPeriod,DebtDate),
DebtNotes text,
CONSTRAINT Debts_PK PRIMARY KEY (DebtID),
CONSTRAINT Debts_Readers_FK1 FOREIGN KEY (ReaderID)
REFERENCES Readers(ReaderID) ON DELETE CASCADE,
CONSTRAINT Debts_Books_FK1 FOREIGN KEY (BookID)
REFERENCES Books(BookID) ON DELETE CASCADE)
(Access)

(SQL Server)

В примере для Access мы опустили все свойства, которые Access не поддерживает. Для ограничения внешнего ключа ключевым словосочетанием является FOREIGN KEY. Как видно из примера, ограничения можно задавать двумя способами: либо сразу за описанием поля (как определены значения по умолчанию), так и отдельно (как заданы ключи).
Следует заметить, что значения по умолчаню отдельно заданы быть не могут, так как привязаны к одному конкретному полю (в отличие от проверок, уникальных значений и ключей). Теперь у нас таблица "Задолженности" (Debts) определена почти полностью - осталось лишь задать индекс.

Приведём примеры изменения ограничений в уже созданных таблицах. Например, в созданную выше таблицу "Книги" (Books) добавим внешний ключ.

Код: (sql)
ALTER TABLE Books
ADD CONSTRAINT Books_Sections_FK1
FOREIGN KEY (SectionID)
REFERENCES Sections(SectionID)
(Access, SQL Server)

Ещё добавим проверку на заполнение кода книги.

Код: (sql)
ALTER TABLE Books
ADD CONSTRAINT BookCode_Check1
CHECK (BookCode <> '' AND LEN(BookCode) >= 8)
(SQL Server)

В этом примере использована функция LEN(), возвращающая длину строки, хранящейся в поле.
Завершая тему, приведём пример удаления ограничения. Удалим, значение по умолчанию длительности задолженности из таблицы "Задолженности" (Debts).

Код: (sql)
ALTER TABLE Debts
DROP CONSTRAINT DebtPeriod_Default
(SQL Server)


Задания для закрепления


Задание одно: полностью сформировать нашу учебную базу данных (за исключением индексов). Для тех, кто использует Access, естественно, значения по умолчанию, вычисляемые поля и проверки с помощью встроенного SQL создать не удастся.


Заключение статьи


Не успели мы в этой статье рассмотреть индексы, как было обещано. Сделаем это в следующей. К настоящему моменту в нашем багаже знаний уже имеются все необходимые операции для создания набора связанных таблиц в базе данных, а также операции изменения и удаления таблиц и их объектов.
Кроме индексов в следующей статье мы начнём изучать язык манипулирования данными, так как вводить понятия представлений, хранимых процедур, триггеров без языка запросов смысла не имеет. Мы не будем сильно углубляться в возможности применения языка запросов - эта тема будет раскрыта в отдельном цикле статей.