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

Порядок сортировки 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, который, как говорилось выше, подтверждает и завершает предыдущую транзакцию.

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

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

• Представление состоит только из одной таблицы.
• Столбцы представления содержат все столбцы таблицы, определенные с параметром NOT NULL.
• В представлении не используются агрегатные функции, параметры DISTINCT и HAVING, хранимые процедуры и пользовательские функции.

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

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

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

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

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

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

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