Краткий курс SQL

Информация в реляционной базе данных хранится в таблицах и в связях между таблицами. В архитектуре клиент-сервер, клиент посылает на сервер запрос и получает от него ответ -- результат выполнения запроса. Наибольшее распространение получил язык запросов SQL (акроним от Structured Query Language). Пример SQL команды, которая извлекает список контактов из таблицы GD_CONTACT:

SELECT * FROM gd_contact

В свою очередь, язык SQL подразделяется на два подмножества: DDL (Data Definition Language) и DML (Data Manipulation Language). DDL определяет набор команд, с помощью которых в базе данных создаются структурные объекты, т.н. метаданные, -- таблицы, домены, внешние ключи, индексы, хранимые процедуры и т.п. На языке DML пишутся запросы на извлечение данных из базы, а так же на вставку, изменение или удаление записей из таблиц.

Типы данных

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

• INTEGER и SMALLINT -- целочисленные;
• SINGLE PRECISION и DOUBLE PRECISION -- числа с плавающей точкой;
• NUMERIC и DECIMAL -- числа с фиксированной точкой;
• DATE, TIME, TIMESTAMP -- дата, время и дата с временем;
• CHAR, VARCHAR -- строки;
• BLOB -- двоичный объект.

Создание домена

Изучая предметную область разработчик базы данных часто сталкивается с тем, что встроенный тип слишком "широк" для хранения аттрибута рассматриваемой сущности. Например, если необходимо хранить в базе данных возраст человека, как целое число прожитых лет, то, очевидно, что типы данных INTEGER и SMALLINT предоставляют нам слишком широкие диапазоны: от минус двух до плюс двух миллиардов с копейками в первом случае и от минус 32768 до плюс 32767 -- во втором. Конечно, можно понадеяться, что оператор никогда не будет ошибаться и всегда будет вводить в базу корректные величины, но реалии жизни таковы, что если где-то может произойти ошибка -- она, увы, обязательно случится. К счастью, сервер предоставляет нам возможность создать свой тип данных, наложив на него необходимые ограничения. Тип данных в SQL называется доменом и для его создания служит команда CREATE DOMAIN:

 CREATE DOMAIN dage 
   AS INTEGER 
   DEFAULT 0
   CHECK(VALUE >= 0 AND VALUE <= 120) 

Рассмотрим приведенную выше команду. Мы попросили сервер создать домен CREATE DOMAIN с именем dage на основе целочисленного типа AS INTEGER, причем, если пользователь не укажет возраст, то будет использовано значение по умолчанию 0 -- DEFAULT 0, и значение поля должно находиться в пределах от 0 до 120 -- CHECK(VALUE >= 0 AND VALUE <= 120). Мы могли бы указать, что поле будет обязательно для заполнения -- NOT NULL, но в этом нет необходимости, так как NULL значение в любом случае не пройдет проверку CHECK.

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

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

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

• Название;
• Год основания.

Сперва создадим три домена:

 CREATE DOMAIN lsn_dintkey
   AS INTEGER
   NOT NULL

 CREATE DOMAIN lsn_dname
   AS VARCHAR(60)
   CHARACTER SET WIN1251
   NOT NULL
   COLLATE PXW_CYRL

 CREATE DOMAIN lsn_dfounded
   AS INTEGER
   CHECK(VALUE >= 1800 AND VALUE <=2007)

Мы будем использовать префикс lsn_ для всех объектов, создаваемых в рамках нашего курса, для того, чтобы не забывать, что это всего лишь урок -- lesson. Префиксы позволяют не запутаться в именах объектов на больших базах данных. Второй префикс -- d -- указывает на тип объекта метаданных -- домен. Обратите внимание, как при создании домена lsn_dname мы указали кодовую таблицу WIN1251 (Кирилические символы) и сличение (COLLATION PXW_CYRL). Последнее определяет порядок символов в алфавите конкретного языка и используется при сортировке информации.

Теперь, напишем DDL команду создания таблицы, которую назовем lsn_team:

 CREATE TABLE lsn_team (
   id      lsn_dintkey,
   name    lsn_dname UNIQUE,
   founded lsn_dfounded,

   PRIMARY KEY(id)
 )

Выше, мы говорили только о двух аттрибутах, а в примере видим еще и третье поле -- id. В чем его предназначение?

Первичный ключ записи

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

Первичный ключ становится таковым после наложения ограничения PRIMARY KEY:

 PRIMARY KEY (id)

Разумеется, что в таблице могут быть и другие поля, уникальные по природе задачи. В этом случае они могут выступать наравне с первичным ключем таблицы при организации связей. Такие поля называются кандидатами на первичный ключ. На них накладывается ограничение UNIQUE. В нашем примере таким полем является наименование футбольной команды, которое мы определили как:

name   lsn_dname UNIQUE

Автоматическое заполнение первичного ключа

Создадим генератор lsn_gunique:

 CREATE GENERATOR lsn_gunique

Вновь созданный генератор имеет значение 0, которое можно изменить с помощью команды:

 SET GENERATOR lsn_gunique TO <Новое значение>

Определить значение генератора можно с помощью SQL запроса:

 SELECT GEN_ID(lsn_gunique, 0) FROM rdb$database

Из какой таблицы выполнять SELECT в данном случае не принципиально, но таблица RDB$DATABASE удобна тем, что всегда содержит одну и только одну запись.

Но, как значение генератора попадет в поле ID? В этом нам поможет такой объект метаданных, как триггер.

Создание триггера

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

• BEFORE INSERT -- перед вставкой записи;
• AFTER INSERT -- после вставки записи;
• BEFORE UPDATE -- перед изменением записи;
• AFTER UPDATE -- после изменения записи;
• BEFORE DELETE -- перед удалением записи;
• AFTER DELETE -- после удаления записи.

Триггер может быть создан для конкретного события или для нескольких событий. В нашем примере необходимо создать триггер на событие BEFORE INSERT для таблицы lsn_team:

 CREATE TRIGGER lsn_bi_team FOR lsn_team
   BEFORE INSERT
   POSITION 0
 AS BEGIN
   NEW.id = GEN_ID(lsn_gunqiue, 1);
 END

Позиция триггера (POSITION) определят порядок выполнения однотипных триггеров, если их несколько. Триггера с меньшим значением будут выполняться раньше. В теле триггера мы можем обратиться к значениям полей записи через метапаременные NEW и OLD. Причем, в триггерах на вставку записи доступна только переменная NEW. В триггерах на удаление -- только OLD. И, наконец, в триггерах на изменение записи можно обращаться как к старой, так и к новой версии записи. В триггерах, вызываемых перед осуществлением операции, можно менять значение полей записи, а в пост триггерах -- только читать.

Как нетрудно догадаться, функция GEN_ID принимает на вход два параметра: генератор и шаг приращения и возвращает новое значение генератора.