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

Версия 20:26, 1 марта 2007

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

SELECT * FROM gd_contact

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

Типы данных

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

• 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). Последнее определяет порядок символов в алфавите конкретного языка и используется при сортировке информации.

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

 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