relbase_book

– 71 –

ство называется доменом). Задать домен для того или иного поля можно с помощью ограничений на значение. Этот ограничитель указывается при создании таблицы. После типа поля или значения по умолчанию надо указать ключевое слово CHECK и логическое выражение в скобках

CREATE TABLE OFFISY

(

ID_OFC INTEGER NOT NULL,

CITY VARCHAR(15) NOT NULL,

REGION VARCHAR(10) NOT NULL, MNGR INTEGER,

TARGET MONEY,

SALES MONEY NOT NULL,

PRIMARY KEY (OFFICE), CONSTRAINT HASMGR

FOREIGN KEY (MGR) REFERENCES SLUZHASCHIE ON DELETE SET NULL, CHECK (TARGET >= 0.00) );

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

3.3.2. ИНСТРУКЦИЯ ALTER TABLE

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

–добавить в каждую строку таблицы CUSTOMERS имя и номер телефона служащего компании клиента, через которого поддерживается контакт;

–добавить в таблицу PRODUCTS столбец с указанием минимального количества товара, чтобы иметь возможность предупреждения о том, что запас какого-либо товара стал меньше допустимого предела;

–сделать столбец REGION в таблице OFFISY внешним ключом для вновь созданной таблицы REGIONS, первичным ключом которой является название региона;

–удалить определение внешнего ключа для столбца CUST таблицы ZAKAZY, связывающего ее с таблицей CLIENTY, и заменить его определениями двух внешних ключей, связывающих столбец CUST с двумя вновь созданными таблицами

CUST_INFO и ACCOUNT_INFO.

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

–добавить новый столбец в уже существующую и заполненную таблицу;

–удалить столбец из существующей таблицы;

–изменить значение по умолчанию для какого-либо столбца;

–добавить или удалить первичный ключ таблицы;

–добавить или удалить внешний ключ таблицы;

–добавить или удалить условие уникальности;

–72 –

–добавить или удалить условие проверки для любого столбца или для таблицы в целом.

Однако оператором ALTER TABLE можно провести только одно из перечисленных изменений, например, за один раз можно добавить 1 столбец.

Добавление столбца

Чаще всего инструкция ALTER TABLE применяется для добавления столбца в существующую таблицу. Предложение с определением столбца в инструкции ALTER TABLE имеет точно такой же вид, что и в инструкции CREATE TABLE, и выполняет ту же самую функцию. Новое определение добавляется в конец определений столбцов таблицы, и в последующих запросах новый столбец будет крайним справа. СУБД обычно предполагает, что новый столбец во всех существующих строках содержит значения NULL. Поэтому нельзя объявлять новый столбец как NOT NULL.

Но если такое объявление все же необходимо, то необходимо определить этот столбец как NOT NULL WITH DEFAULT. При этом СУБД считает, что этот столбец содержит значение по умолчанию, и не будет автоматически добавлять значение NULL.

Пример. Добавить контактный телефон и имя служащего компании клиента в таблицу CLIENTY .

ALTER TABLE SLUZHASCHIE

ADD CONTACT_NAME VARCHAR(30)

ALTER TABLE SLUZHASCHIE ADD CONTACT_PHONE CHAR(10)

Пример. Добавить в таблицу TOVARY столбец с данными о минимальном допустимом количестве товара на складе.

ALTER TABLE TOVARY

ADD MIN_QTY INTEGER NOT NULL WITH DEFAULT 0

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

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

С помощью инструкции ALTER TABLE можно удалить из существующей таблицы один или несколько столбцов, если в них больше нет необходимости. Ниже приведен пример удаления столбца QUOTA из таблицы SLUZHASCHIE:

ALTER TABLE SLUZHASCHIE DROP QUOTA

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

– 73 –

Эти проблемы в стандарте SQL2 решены так же, как и в случае инструкций

DELETE и UPDATE, с помощью правил удаления RESTRICT и CASCADE.

В случае применения правила RESTRICT инструкция ALTER TABLE завершится выдачей сообщения об ошибке и столбец не будет удален. Во втором случае внешние ключи, связанные с удаляемым столбцом будут удалены. Однако правило CASCADE может вызвать целую «лавину» изменений, поэтому применять его следует с осторожностью. Лучше указывать правило RESTRICT, а связанные внешние ключи обрабатывать с помощью дополнительных инструкций типа ALTER.

Изменение первичных и вторичных ключей

Инструкция ALTER TABLE чаще всего применяется для изменения или добавления определений первичных и вторичных ключей таблицы. Предложения, добавляющие определения первичного и внешнего ключей, являются точно такими же, как в инструкции CREATE TABLE, и выполняет те же функции.

Пример. Сделать столбец REGION таблицы OFFISY внешним ключом для вновь созданной таблицы REGIONS, первичным ключом которой является название региона.

ALTER TABLE OFFISY

ADD CONSTRAINT IN REGION

FOREIGN KEY (REGION) REFERENCES REGIONS

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

Пример. Изменить первичный ключ таблицы OFFISY.

ALTER TABLE SLUZHASCHIE

DROP CONSTRAINT WORKSIN

FOREIGN KEY (REP_OFFICE) REFERENCES OFFISY

ALTER TABLE OFFISY

DROP PRIMARY KEY (OFFICE)

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

3.4. СОЗДАНИЕ ПРЕДСТАВЛЕНИЙ (VIEW)

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

Представления используются по нескольким причинам:

– они позволяют сделать так, что разные пользователи базы данных будут видеть ее по-разному;

– 75 –

требованиям. Представление создается в текущей базе данных и хранится как отдельный объект. Наилучший способ создания представления – создать запрос SELECT и, проверив его, добавить недостающую часть CREATE VIEW.

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

3.4.1. ОБЩИЙ ФОРМАТ ОПЕРАТОРА CREATE VIEW

Создание просмотра базы данных осуществляется оператором

CREATE VIEW Имя_Представления

AS <Оператор SELECT>;

– Имя_Представления − имя просмотра после его создания можно использовать как имя физической таблицы.

– Список столбцов − определяет состав столбцов просмотра. Если список не задан, то в просмотр отбираются все столбцы таблиц, указанных в операторе SE LECT.

– WITH CHECK OPTION – для редактируемого просмотра запрещает добавление записей, не удовлетворяющих условиям отбора, заданным в операторе SELECT.

На Рис. 3.2. изображено представление, определенное в соответствии с запросом

CREATE VIEW SLUZHASCHIE_OFFISY AS

SELECT С.FAMILY, С.NAME, О.CITY, О.REGION, С.QUOTA, С.SALES FROM SLUZHASCHIE С, OFFISY О

WHERE С.ID_OFC = O.ID_OFC

3.4.2. ГОРИЗОНТАЛЬНОЕ ПРЕДСТАВЛЕНИЕ

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

CREATE VIEW SAL_DEPT AS SELECT *

FROM EMPLOYEE

WHERE DEPARTMENT = «отдел продаж»

3.4.3. ВЕРТИКАЛЬНОЕ ПРЕДСТАВЛЕНИЕ

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

– 76 –

бавке должна быть закрыта. Для него можно создать следующее вертикальное представление:

CREATE VIEW TABLE AS

SELECT T_NUM, NAME, POSITION, DEPRT FROM EMPLOYEE

Таблица SLUZHASCHIE

Таблица OFFISY

Рис. 3.2. Типичное представление с двумя исходными таблицами

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

3.4.4. УДАЛЕНИЕ ПРЕДСТАВЛЕНИЯ

Удалить просмотр можно следующим оператором:

DROP VIEW <ИмяПросмотра>;

– 77 –

Например,

DROP VIEW vStore;

3.4.5. НЕДОСТАТКИ ПРЕДСТАВЛЕНИЙ

Наряду с перечисленными выше преимуществами, представления обладают и двумя существенными недостатками.

–Производительность. Представление создает лишь видимость существования соответствующей таблицы, и СУБД приходится преобразовывать запрос к представлению в запрос к исходным таблицам. Если представление отображает многотабличный запрос, то простой запрос к представлению становится сложным объединением и на его выполнение может потребоваться много времени;

–Ограничения на обновление. Когда пользователь пытается обновить строки представления, СУБД должна установить их соответствие строкам исходных таблиц, а также обновить последние. Это возможно только для простых представлений (созданных на основе одной таблицы); сложные представления обновлять нельзя, они доступны только для выборки.

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

3.5. СОЗДАНИЕ ИНДЕКСОВ

3.5.1. ОБЩИЙ ФОРМАТ ОПЕРАТОРА CREATE INDEX

Индекс может быть создан оператором:

CREATE [UNIQUE] [ASC[ENDING]|DESC[ENDING]]

INDEX ИмяИндекса ON ИмяТаблицы (столбец1 [, столбец2 ...]);

–UNIQUE − требует создания уникального индекса;

–ASC[ENDING] − указывает на необходимость сортировки значений индексных

полей по возрастанию (по умолчанию);

– DESC[ENDING] − указывает на необходимость сортировки значений индексных полей по убыванию;

–ИмяИндекса − имя создаваемого индекса;

–ИмяТаблицы − имя таблицы, для которой создается индекс;

–столбецN − имена столбцов, по которым создается индекс.

Ниже дан пример инструкции CREATE INDEX, которая создает индекс для таблицы ZAKAZY на основе столбцов MFR и PRODUCT и содержит требование уникальности для комбинации этих столбцов:

CREATE UNIQUE INDEX ORD_PROD_IDX ON ZAKAZY (MFR, PRODUCT);

3.5.2. НЕОБХОДИМОСТЬ СОЗДАНИЯ ИНДЕКСОВ

Индексы необходимо создавать в том случае, когда по столбцу или группе столбцов:

–78 –

–часто производится поиск в базе данных (столбец или группа часто перечисляются в предложении WHERE оператора SELECT);

–часто строятся объединения таблиц;

–часто производится сортировка (т. е. столбец или столбцы часто используются в предложении ORDER BY оператора SELECT.

Не рекомендуется строить индексы по столбцам или группам столбцов, которые:

–редко используются для поиска, объединения и сортировки результатов запросов;

–часто меняют значение, что приводит к необходимости часто обновлять индекс и способно существенно замедлить скорость работы с базой данных;

–содержат небольшое число вариантов значения.

3.5.3. УДАЛЕНИЕ ИНДЕКСА

Для удаления индекса, созданного оператором CREATE INDEX, используется оператор

DROP INDEX <имя_индекса>;

Нельзя удалить индекс, созданный в результате определения первичного и внешнего ключей. Для этой цели следует использовать оператор ALTER TABLE.

– 79 –

ГЛАВА 4. DML – ЯЗЫК МАНИПУЛИРОВАНИЯ ДАННЫМИ РЕЛЯЦИОННОЙ МОДЕЛИ

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

–SELECT – выборка данных, удовлетворяющих заданным условиям;

–INSERT – ввод новых данных;

–UPDATE – обновление существующих данных;

–DELETE – удаление данных.

Каждая из этих команд имеет множество вариантов, которые заслуживают отдельного рассмотрения.

4.1.ОПЕРАТОР ВЫБОРКИ SELECT

4.1.1. ОБЩИЙ ФОРМАТ ОПЕРАТОРА SELECT

Инструкция SELECT, используемая для построения SQL-запросов, является наиболее мощной из всех инструкций SQL, и реализует все операции реляционной алгебры.

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

SELECT [ALL | DISTINCT] (<Список полей>) FROM <Список таблиц>

[WHERE <Предикат – условие выборки или соединения>] [GROUP BY <Список полей результата>]

[HAVING <Предикат – условие для группы>] [ORDER BY < список_столбцов>]

Инструкция состоит из шести предложений:

–предложения SELECT, содержащего список столбцов, которые должны быть возвращены инструкцией;

–предложения FROM, перечисляющего список имен таблиц, содержащих элементы данных, извлекаемые запросом;