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

Версия 21:15, 2 марта 2007

Информация в реляционной базе данных хранится в таблицах и в связях между таблицами. В архитектуре клиент-сервер, клиент посылает на сервер запрос и получает от него ответ -- результат выполнения запроса. Наибольшее распространение получил язык запросов 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 принимает на вход два параметра: генератор и шаг приращения и возвращает новое значение генератора.

Вставка записей

Теперь, все готово для того, чтобы занести первую запись в нашу таблицу. Выполним для этого SQL команду INSERT:

 INSERT INTO lsn_team (name, founded) VALUES ('Real Madrid', 1902)

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

 SELECT * FROM lsn_team

Результат:

 id      name           founded
 ======================================
 1       Real Madrid    1902

Аналогичным образом добавим еще несколько записей:

 INSERT INTO lsn_team (name, founded) VALUES ('Barcelona', 1901);
 INSERT INTO lsn_team (name, founded) VALUES ('Chelsea', 1905);
 INSERT INTO lsn_team (name, founded) VALUES ('Milan', 1899);

Проверим, что у нас получилось:

 SELECT * FROM lsn_team

 id      name           founded
 ======================================
 1       Real Madrid    1902
 2       Barcelona      1901
 3       Chelsea        1905
 4       Milan          1899

Создаем вторую таблицу

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

• Домашняя команда;
• Гостевая команда;
• Дата игры;
• Количество голов, забитых домашней командой;
• Количество голов, забитых гостевой командой.

Напишем DDL команду:

 CREATE TABLE lsn_result (
   homekey     lsn_dintkey,
   awaykey     lsn_dintkey,
   gamedate    DATE DEFAULT 'CURRENT_DATE' NOT NULL,
   homescore   SMALLINT,
   awayscore   SMALLINT,

   PRIMARY KEY (homekey, awaykey, gamedate),
   CHECK (homescore BETWEEN 0 AND 20),
   CHECK (awayscore BETWEEN 0 AND 20)
 )

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

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

• CURRENT_DATE -- текущая дата;
• CURRENT_TIME -- текущее время;
• CURRENT_TIMESTAMP, NOW -- текущая дата и время.

Создание внешнего ключа

Создавая внешний ключ (FOREIGN KEY) мы говорим серверу, что некоторое поле в некоторой таблице будет содержать значение первичного ключа записи из другой или этой же таблицы. FOREIGN KEY является ограничением, подобным уже известным нам ограничениям PRIMARY KEY и CHECK. Но, как добавить ограничение, если таблица уже существует? Практически для каждого объекта метаданных язык DDL предоставляет нам три команды: CREATE для создания объекта, ALTER для изменения существующего объекта и DROP для удаления объекта. В данном случае, мы воспользуемся командой ALTER TABLE, которая позволяет вносить правки в уже существующую таблицу:

 ALTER TABLE lsn_result ADD CONSTRAINT lsn_fk_result_homekey
   FOREIGN KEY (homekey) REFERENCES lsn_team (id)
   ON UPDATE CASCADE
   ON DELETE NO ACTION

Разберем данную команду:

1. ALTER TABLE lsn_result -- Мы указываем серверу, что следует внести изменения в таблицу lsn_result;
2. ADD CONSTRAINT lsn_fk_result_homekey -- В указанную таблицу добавляем новое ограничение с именем lsn_fk_result_homekey. Мы не говорили об этом ранее, но каждое ограничение может иметь свое имя. В дальнейшем, по имени можно вносить изменения в ограничение или даже вовсе удалить его из базы. Наверное, вы уже догадались, что для этих целей существуют команды: ALTER TABLE ALTER CONSTRAINT ... и ALTER TABLE DROP CONSTRAINT ... Пусть вас не смущает такое длинное имя, присвоенное нами ограничению. Его можно расшифровать следующим образом: lsn_ -- префикс нашей подсистемы, fk_ -- префикс типа объекта (FOREIGN KEY), result -- имя таблицы, homekey -- имя поля, на которое наложено ограничение.
3. FOREIGN KEY (homekey) -- Ограничение типа внешний ключ на поле homekey;
4. REFERENCES lsn_team (id) -- Которое содержит ссылку (значение первичного ключа записи) на таблицу lsn_team, на поле id;
5. ON UPDATE CASCADE -- Если в таблице со списком футбольных команд изменится значение первичного ключа некоторой записи, то автоматически изменения произойдут во всех, ссылающихся на эту команду, записях таблицы lsn_result;
6. ON DELETE NO ACTION -- Сервер запретит нам удалять футбольную команду для которой в базу данных занесен хотя бы один результат игры. Разумеется, мы могли бы написать правило ON DELETE CASCADE, но в этом случае при удалении команды удалились бы и все результаты игр с ее участием.

Аналогично создадим внешний ключ и для второй ссылки:

 ALTER TABLE lsn_result ADD CONSTRAINT lsn_fk_result_awaykey
   FOREIGN KEY (awaykey) REFERENCES lsn_team (id)
   ON UPDATE CASCADE
   ON DELETE NO ACTION

Использование исключений

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

 CREATE EXCEPTION lsn_einvalidgamedate 'Некорректная дата игры'

Теперь напишем триггер:

 CREATE TRIGGER lsn_biu_result FOR lsn_result
   BEFORE INSERT OR UPDATE
   POSITION 100
 AS 
   DECLARE VARIABLE D1 DATE;
   DECLARE VARIABLE D2 DATE;
 BEGIN
   SELECT founded FROM lsn_team WHERE id = NEW.homekey INTO :D1;
   SELECT founded FROM lsn_team WHERE id = NEW.awaykey INTO :D2;

   IF (NEW.gamedate < :D1 OR NEW.gamedate < :D2) THEN
   BEGIN
     EXCEPTION lsn_einvalidgamedate;
   END
 END

Заносим данные

На этом создание таблицы с результатами футбольных матчей можно считать законченным. Время приступать к вводу информации.

Реал-Барселона 2:0

 INSERT INTO lsn_result VALUES (1, 2, '01.05.2006', 2, 0)

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