Создание, модификация и удаление таблиц и представлений

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

CREATE TABLE

Как мы уже знаем из прошлой лекции, создание таблиц осуществляется запросом CREATE TABLE, который можно выполнить с помощью утилиты IBConsole. Полный синтаксис запроса такой:

CREATE TABLE Имя_таблицы [EXTERNAL [FILE] Имя_файла] 
   (<описание_столбца_1> [, …, <описание_столбца_n>] | 
    <ограничение_таблицы> …);

Имя_таблицы - уникальный внутри базы данных идентификатор (имя) таблицы. Является обязательным. Нельзя допускать, чтобы такой же идентификатор был у других таблиц, представлений или процедур текущей базы данных.

EXTERNAL [FILE] Имя_файла - необязательное определение того, что создается внешняя по отношению к базе данных таблица. "Имя_файла" при этом указывает адрес и имя файла создаваемой таблицы. Файла с таким именем не должно существовать на момент создания таблицы. В результате применения этого оператора будет создана внешняя по отношению к базе данных таблица, в текстовом формате ASCII. Обычно поля в таких файлах разделяются символом табуляции, а в конце записи ставится символ перевода строки. При этом созданная во внешнем файле таблица будет доступна в списке таблиц базы данных в утилите IBConsole. В основном, такие таблицы могут использоваться для обмена данными между разными БД, для сбора и обработки статистических данных, для сложной сортировки и т.п. Пример создания таблицы во внешнем файле (не забывайте, что служба InterBase должна быть запущена, утилита IBConsole загружена, и база данных First открыта):

CREATE TABLE VneshTable EXTERNAL FILE 'C:\DataBases\VneshFile.tbl'(
  ID INTEGER,
  NAME VARCHAR(30))

<описание_столбца> - Описание столбца таблицы, которое может иметь как простой, так и достаточно сложный формат. Описание столбца имеет свой синтаксис:

<описание_столбца> = имя_столбца {<тип_данных> | COMPUTED [BY]  <выражение> | <домен>}
   [DEFAULT {<литерал> | NULL | USER}]
   [NOT NULL] [<ограничение_столбца>]
   [COLLATE collation]

Давайте по порядку разберемся со всеми этими определениями. Как вы, вероятно, знаете, в синтаксисе различных языков программирования в фигурные скобки принято помещать список возможных параметров, из которого нужно выбрать. То есть, мы должны выбрать либо <тип_данных>, либо COMPUTED [BY], либо заранее созданный домен. О типах данных и их описаниях, равно как и о доменах, мы говорили на прошлой лекции. Теперь рассмотрим создание вычисляемого столбца.

Вычисляемые столбцы

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

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

CREATE TABLE Sdelki (
   ID INTEGER,
   TOVAR VARCHAR(20) CHARACTER SET WIN1251 COLLATE PXW_CYRL,
   ED_IZM VARCHAR(7) CHARACTER SET WIN1251,
   STOIMOST DOUBLE PRECISION,
   KOLVO SMALLINT,
   SUMMA COMPUTED BY (STOIMOST * KOLVO))

Значения по умолчанию

Значения столбцов по умолчанию задаются необязательным параметром DEFAULT:

[DEFAULT {<литерал> | NULL | USER}]

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

<литерал> - заданный по умолчанию символ или текст, целое или вещественное число, дата и (или) время, в зависимости от типа столбца. Применяется соответственно с текстовыми, числовыми полями и полями с датами. Значение даты указывается в кавычках. В примере ниже мы создаем логическое поле, которое должно содержать один из двух символов: Y (истина) или N (ложь). По умолчанию, в поле должен помещаться символ N. Кроме того, мы создаем числовое поле, которое по умолчанию "обнуляем", а также поле дат, которое по умолчанию будет содержать дату 1 Января 2010 года. Вот как можно этого добиться:

CREATE TABLE MyDefault(
   Bool_col CHAR(1) DEFAULT 'N',
   Int_col INTEGER DEFAULT 0,
   Date_col DATE DEFAULT '01.01.2010' )

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

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

CREATE TABLE UsersTable(
   ID INTEGER,
   ZAPIS VARCHAR(50),
   LOG_USERS VARCHAR(10) DEFAULT USER)

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

Параметр NOT NULL

Параметр NOT NULL указывает, что столбец не должен быть пустым. В случае если вы попытаетесь сохранить запись с таким столбцом, не введя в него какого-нибудь значения, произойдет ошибка, и InterBase не позволит сохранить такую запись. Если вы описываете поле, которое будет ключевым, указание такого параметра является обязательным. Пример:

CREATE TABLE Key_col(
  ID INTEGER NOT NULL)

Ограничения столбцов

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

CHECK (<условие_поиска>);

  где <условие_поиска> =

  <значение> <оператор> {<значение1> | <выбор_одного>}
  | <значение> [NOT] BEETWEN <значение1> AND <значение2>
  | <значение> [NOT] LIKE <маска> [ESCAPE <символ>]
  | <значение> [NOT] IN (<значение1> [, <значение2> ...]) 
  | <список_выбора>
  | <значение> IS [NOT] NULL
  | <значение> {[NOT] {= | < | >} | >= | <=} 
    {ALL | SOME | ANY} {список_выбора}
  | EXISTS (<выражение_выбора>)
  | SINGULAR (<выражение_выбора>)
  | <значение> [NOT] CONSTAINING <значение1>
  | <значение> [NOT] STARTING [WITH] <строка>
  | NOT <условие_поиска>
  | <условие_поиска> OR <условие_поиска>
  | <условие_поиска> AND <условие_поиска>

Напомним, что символом "|" в описании синтаксиса языков программирования принято разделять альтернативные значения. То есть, "|" смело можно заменить на "или". А в квадратные скобки заключаются необязательные параметры.

Диапазон возможностей оператора CHECK весьма широк - теоретически в нем можно указать почти любое условие поиска. В ограничениях можно сравнивать вводимое значение с другим указанным значением, со значением другого столбца или даже другого столбца другой таблицы. Кроме того, в качестве сравниваемого значения можно использовать выборку оператором SELECT из другой таблицы. Практически все параметры отвечают стандартам языка SQL и могут встречаться в операторах SELECT. Приведем несколько примеров:

CREATE TABLE Check_table(
   /*Столбец должен содержать только положительные числа или ноль:*/
   Col_1 INT CHECK (Col_1 >= 0),
   
   /*Столбец должен содержать значение в диапазоне от 10 до 50:*/
   Col_2 INT CHECK (Col_2 BETWEEN 10 AND 50),

   /*Столбец должен оканчиваться символами "руб."*/
   Col_3 VARCHAR(20) CHECK (Col_3 LIKE '% руб.'),

   /*Столбец должен содержать либо "муж", либо "жен".*/
   Col_4 VARCHAR(3) CHECK(Col_4 IN ('муж','жен')),

   /*Столбец не может быть пустым.*/
   Col_5 VARCHAR(5) CHECK (Col_5 IS NOT NULL),

   /*Столбец 6 не может иметь такое же значение, как столбец 2*/
   Col_6 INT CHECK (NOT Col_6 = Col_2),

   /* Значение столбца 7 должно совпадать с одним или несколькими из */
   /* значений столбца DLINNOE таблицы Table_cel*/
   Col_7 INT CHECK (EXISTS(SELECT DLINNOE FROM Table_cel
      WHERE Table_cel.DLINNOE = Check_table.Col_7)),

   /* Значение столбца 8 должно совпадать лишь с одним из */
   /* значений столбца DLINNOE таблицы Table_cel*/
   Col_8 INT CHECK (SINGULAR(SELECT DLINNOE FROM Table_cel
      WHERE Table_cel.DLINNOE = Check_table.Col_8)),

   /*Столбец обязательно должен содержать подстроку "мир".*/
   /*Например, "Мы за мир!", */
/*"мировая экономика", "эмират"*/
   Col_9 VARCHAR(30) CHECK ( Col_9 CONTAINING 'мир'),

   /*Столбец обязательно должен начинаться с подстроки "мир".*/
   /*Например, "мировая экономика", "мираж", */
  /*"мир в объективе"*/
   Col_10 VARCHAR(30) CHECK ( Col_10 STARTING WITH 'мир')
)

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

Ограничения CHECK в доменах

Указанные выше ограничения на значения столбцов справедливы и для доменов, с небольшим изменением. Поскольку мы заранее не знаем, какой столбец (столбцы) какой таблицы (таблиц) будут использовать описание этого домена, вместо имени столбца указывается ключевое слово VALUE. VALUE является заменой для любого имени поля, константы, значения переменной или результата выражения, которые могут быть подставлены в синтаксисе SQL для сохранения данных в столбце.

Пример:

CREATE DOMAIN Poloj_Cel
AS INT CHECK(VALUE >= 0)

Порядок сортировки COLLATE

Эту тему мы рассматривали в прошлой лекции и знаем, что данный параметр применяется с текстовыми столбцами и определяет способ, по которому будут сортироваться и сравниваться текстовые данные при выводе их оператором SELECT. Для кодировки WIN1251 это может быть сортировка WIN1251 или PXW_CYRL.

Удаление таблиц

Нередко возникает необходимость удалить из базы данных созданную ранее таблицу. Делается это оператором DROP. Пример:

DROP TABLE ARRAY_TABLE

Этот же оператор используется для удаления доменов, представлений, триггеров и т.д.

Модификация таблицы

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

Изменить структуру таблицы можно оператором ALTER, который может иметь дополнительные параметры ADD (добавить столбец) или DROP (удалить столбец). Примеры:

/* Добавляем столбец */
ALTER TABLE TABLE_CEL ADD New_String VARCHAR(30)

или 

/* Удаляем столбец */
ALTER TABLE TABLE_CEL DROP Korotkoe

Примечание: после выполнения операторов ALTER в утилите IBConsole, транзакция может считаться незавершенной, и попытка ввести новую команду или закрыть окно Interactive SQL вызовет ошибку. В этом случае нужно просто ввести и выполнить команду завершения транзакции COMMIT, после которой можно вводить новые запросы. О транзакциях мы будем говорить позднее.

Иногда бывает необходимо не удалить столбец, а только изменить его. Например, вместо VARCHAR(30) указать VARCHAR(50). При этом нужно сохранить данные, которые хранились в старом столбце. Сделать это одним оператором невозможно, придется изменять столбец в несколько этапов. Вначале создается новый временный столбец, повторяющий все атрибуты изменяемого, и в него копируются все данные из старого столбца. Копирование данных осуществляется оператором UPDATE … SET:

/* Добавляем новый временный столбец: */
ALTER TABLE TABLE_CEL ADD Temp_String VARCHAR(30);
/* Копируем в него данные из столбца New_String: */
UPDATE TABLE_CEL SET Temp_String = New_String

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

/* Удаляем старый столбец: */
ALTER TABLE TABLE_CEL DROP New_String;
/* Добавляем новый, с другими параметрами: */
ALTER TABLE TABLE_CEL ADD New_String VARCHAR(50)

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

/* Копируем данные: */
UPDATE TABLE_CEL SET  New_String = Temp_String;
/* Удаляем временный столбец: */
ALTER TABLE TABLE_CEL DROP Temp_String

Представления

Представление (VIEW) - это виртуальная таблица, созданная SQL -запросом для выборки данных из одной или нескольких таблиц БД, или даже из других представлений. Такая таблица не содержит данных, а лишь ссылается на другие таблицы или представления. Для пользователя представление ничем не отличается от обычной таблицы. Для работы с представлением можно использовать обычные наборы данных: TTable или TQuery. Само представление является SQL -запросом, хранящемся на сервере и выполняющимся всякий раз, когда происходит обращение к нему. Во время запроса к представлению, сервер InterBase оптимизирует и компилирует этот запрос, что значительно сокращает время его выполнения. Представление, в отличие от таблиц, не может иметь ключей или индексов. При упорядочивании записей используются ключи и индексы таблиц, которые лежат в основе представления.

Представления обычно применяют для изоляции реально хранимых данных от пользователя, что увеличивает безопасность базы данных. Представления удобны, когда например, программист или администратор БД принимает решение разделить одну таблицу на две. При этом описание представления также изменяется, но для пользователя это по-прежнему одна таблица, так что изменять клиентское приложение не придется. Разработчик также получает возможность изменять представление, дополняя его новыми возможностями. Еще представления помогут, если данному пользователю нежелательно предоставлять доступ ко всем полям таблицы (таблиц). Ему можно сделать доступ к представлению, в котором использовать нужные столбцы как с возможностью их редактирования, так и "Только для чтения".

Представление создается следующим образом:

CREATE VIEW <Имя_представления> [(<Имя_столбца_представления>
	[, < Имя_столбца_представления > …])]
AS <Запрос_SELECT> [WITH CHECK OPTION]

Здесь <Имя_представления> является идентификатором представления, который не должен совпадать с идентификаторами других представлений, таблиц или хранимых процедур.

[(<Имя_столбца_представления> [, < Имя_столбца_представления > …])] - необязательный список имен столбцов создаваемого представления. Если этот список не указывать, имена столбцов будут такими же, как и имена столбцов таблицы (таблиц), указанных в запросе SELECT. Однако при использовании нескольких таблиц, могут возникнуть случаи дублирования имен столбцов, то есть две таблицы могут иметь столбцы с одинаковым именем. В этом случае указать список имен столбцов для представления необходимо, соответствующие столбцы будут переименованы в представлении. Имена столбцов в списке представления должны соответствовать количеству и порядку столбцов, указанных в операторе SELECT.

<Запрос_SELECT> представляет собой обычный SQL -запрос выборки данных из одной или нескольких таблиц или просмотров. Однако в запросе нельзя указывать условия упорядоченности, такие как ORDER BY.

Необязательный параметр [WITH CHECK OPTION] запрещает добавлять записи, значения столбцов которых не удовлетворяют условиям выборки запроса представления. Предположим, что в запросе SELECT представления имеется условие WHERE. Это условие делает выборку записей таблицы по полю целого типа, указывая, что значения поля должны быть в диапазоне от 0 до 100. Если в изменяемом представлении пользователь модифицирует запись, выйдя за рамки указанного диапазона, запись перестанет соответствовать условию WHERE. Параметр [WITH CHECK OPTION] гарантирует, что такие изменения будут невозможны. Пример создания представления:

CREATE VIEW View_Firma AS
SELECT FAMILIYA, IMYA
FROM Table_Firma

Данное представление создает виртуальную таблицу из двух столбцов FAMILIYA и IMYA, которые физически хранятся в таблице Table_Firma. В утилите IBConsole созданные представления можно увидеть в дереве серверов, в выбранной базе данных в разделе Views. Обратиться к этому представлению можно, как к обычной таблице, с помощью запроса SELECT, выполненного в окне запросов Interactive SQL:

SELECT * FROM View_Firma

В окне вывода результатов будут отображены столбцы представления.

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

CREATE TABLE TOVAR(
ID INTEGER NOT NULL,
NAZVANIE VARCHAR(20) NOT NULL COLLATE PXW_CYRL,
STOIMOST DOUBLE PRECISION NOT NULL);
COMMIT;

CREATE TABLE SKLAD(
ID INTEGER NOT NULL,
ID_TOVAR INTEGER NOT NULL,
KOLVO INTEGER NOT NULL);
COMMIT;

CREATE VIEW TOVARY20(NAZ, KOL, CENA) AS 
SELECT NAZVANIE, KOLVO, STOIMOST
FROM TOVAR, SKLAD
WHERE (SKLAD.ID_TOVAR = TOVAR.ID)
    AND (TOVAR.STOIMOST <= 20);

Данное представление создает три столбца: название товара, количество этого товара на складе и его стоимость. Причем выводятся только те товары, стоимость которых не превышает 20. Параметр [WITH CHECK OPTION] здесь не указывается, так как данное представление по определению является "только для чтения". После создания каждой таблицы указывается оператор COMMIT, который, как говорилось выше, подтверждает и завершает предыдущую транзакцию.

Изменяемые представления

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

Если представление удовлетворяет всем этим требованиям, к нему можно применять операторы INSERT, UPDATE и DELETE (то есть, редактировать).

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

Если в представлении указаны не все NOT NULL - столбцы таблицы, то нельзя добавлять новые записи, можно только редактировать или удалять имеющиеся.

Модификация представления

Представление, как и таблицу, можно удалить командой

DROP <Имя_представления>

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