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

Текст из файла (страница 46)

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

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

Определение таблицы

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

CREATE TABLE [{GLOBAL|LOCAL}|TEMPORARY] имя таблицы

({определение столбца|[ограничение таблицы]}.,..

[ON COMMIT{DELETE|PRESERVE} ROWS]);

Базовые таблицы бывают постоянные или временные. Если со­здается временная таблица, то используется параметр TEMPORARY. В этом случае при определении таблицы должны быть указаны до­полнительные параметры GLOBAL или LOCAL.

Предложение ON COMMIT может использоваться только для вре­менных таблиц. Оно определяет, что происходит со строками времен­ных таблиц по завершении транзакции. По умолчанию подразумева­ется ON COMMIT DELETE ROWS.

При описании таблицы должен быть определен хотя бы один стол­бец и могут быть определены ограничения для таблицы. Столбец оп­ределяется следующим образом:

определение столбца ::=

имя столбца|{имя домена

|тип данных [размер]}

[ограничение столбца...]

[DEFAULT значение по умолчанию]

[COLLATE имя сравнения]

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

В стандарте SQL-92 принято семь типов данных, но большин­ство из них имеют подтипы с собственными именами, используе­мыми при задании типов данных для столбцов или доменов. Имен­но каждый подтип обычно и считается типом данных, однако для работы самой СУБД нет необходимости различать типы данных в пределах категорий.

Стандарт предусматривает следующие категории данных:

• CHARACTER STRING (CHARACTER, CHARACTER VARYING);

• NATIONAL CHARACTER (NATIONAL CHARACTER, NATIONAL CHARACTER VARYING);

• BIT STRING (BIT, BIT VARYING);

• EXACT NUMERIC (NUMERIC, DECIMAL, INTEGER, SMOLLINT;

• APPROXIMATE NUMERIC (FLOAT, REAL, DOUBLE PRESION);

• DATETIME (DATE, TIME, TIMESTAMP);

• INTERVAL.

Если вместо типа данных указывается имя домена, то, естествен­но, что этот домен должен быть предварительно определен.

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

Возможны следующие ограничения:

• NOT NULL;

• PRIMARY KEY;

• UNIQUE;

• CHECK;

• FOREIGN KEY/REFERENCES.

Если для столбца задано ограничение NOT NULL, то либо долж­но быть определено значение по умолчанию, либо каждый оператор INSERT или UPDATE не должен приводить к появлению NULL.

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

Ограничение PRIMARY KEY служит для задания первичного ключа. Как известно, ключ может быть простым, состоящим из одно­го поля, и составным, включающим несколько полей. В первом слу­чае ограничение может быть задано или непосредственно при описа­нии поля (см. пример описания таблицы «Spr_mat» ниже), или как ограничивающее условие в конце описания таблицы (см. пример опи­сания таблицы «Spr_post» ниже). Если ключ составной, то может быть использован только второй способ задания ограничения, причем поля, входящие в состав ключа поля, указываются в скобках и разделяются запятыми (см. пример описания таблицы «Post» ниже). В таблице мо­жет быть задано только одно ограничение PRIMARY KEY

Ограничение UNIQUE также может относиться как к отдельному полю, так и к совокупности полей. Оно похоже на ограничение PRIMARY KEY, но в отличие от него, во-первых, может задаваться для нескольких разных полей или совокупностей полей и, во-вто­рых, для них разрешено значение NUL.

Внешний ключ (FOREIGN KEY) - это столбец в дочерней табли­це, ссылающийся на ключ родительской таблицы. Использование вне­шних ключей является основным механизмом поддержания ссылоч­ной целостности внутри реляционной базы данных. При описании внешнего ключа указывается также ссылка на соответствующие поля в родительской таблице (REFERENCES) (см. пример описания таб­лицы «Spr_post» ниже). В родительской таблице для столбца или со­вокупности столбцов, на которые идет ссылка, должно быть задано ограничение на уникальность. Если внешний ключ составной, то при описании таблиц должны быть выполнены следующие условия: для каждого из полей, на которые идет ссылка, должно быть задано огра­ничение NOT NULL; число столбцов в ссылке на таблицу и столбцы должно соответствовать числу столбцов во внешнем ключе; i-й стол­бец во внешнем ключе соответствует i-му столбцу в ссылке на табли­цу и столбцы, тип данных и длина поля каждого столбца ссылки дол­жны соответствовать типу данных соответствующего целевого стол­бца и иметь равную длину.

При задании ограничения CHECK в скобках указывается преди­кат, который использует значения столбцов в выражении для вычис­ления значения. Предикат может принимать значения TRUE, FALSE или UNKNOWN. Ограничение считается нарушенным, когда преди­кат принимает значение FALSE.

Стандарт SQL-92 позволяет определять ограничения так, чтобы они не проверялись до завершения текущей транзакции.

Ниже приведен пример описания трех связанных таблиц: «Spr_mat» — справочник материалов, «Spr_post» — справочник постав­щиков и «Post» - таблица, содержащая информацию о поставках. При включении в базу данных информации о конкретной поставке долж­но проверяться наличие вводимых значений кода материала (kod_mat) и кода поставщика (kod_post) в соответствующих справочниках.

CREATE TABLE spr_mat

(kod_mat CHAR(3) NOT NULL PRIMARY KEY,

naim_mat CHAR (30) NOT NULL UNIQUE);

CREATE TABLE spr_post

(kod_post CHAR(5) NOT NULL,

naim_post CHAR (30) NOT NULL,

PRIMARY KEY (kod_post),

UNIQUE (naim_post));

CREATE TABLE post

(kod_post CHAR(5) NOT NULL, kod_mat CHAR(3) NOT NULL,

data_post DATE NOT NULL, kolv NUMERIC NOT NULL,

PRIMARY KEY (kodjpost, kodjnat, datajpost),

FORIGN KEY (kodjnat) REFERENCES sprjnat (kodjnat),

FORIGN KEY (kod_post) REFERENCES spr_post (kod_post));

Структуру существующей таблицы можно изменить с помощью оператора

ALTER TABLE имя таблицы

{ADD [COLUMN] определение столбца}

| {ALTER [COLUMN] <имя столбца> <изменяющее действие>}

| {DROP [COLUMN] имя столбца RESTRICT | CASCADE}

| {ADD определение ограничения для таблицы}

| {DROP CONSTAINT имя ограничения RESTRICT | CASCADE};

изменяющее действие::=

{SET DEFAULT значение по умолчанию} | {DROP DEFAULT}

Как видно, с помощью оператора ALTER TABLE можно добавить новый столбец в таблицу, изменить определение существующего стол­бца, добавив/удалив значение по умолчанию, добавить/удалить огра­ничения целостности, а также удалить столбец из таблицы. При уда­лении столбца, если будет использован параметр RESTRICT, то в слу­чае наличия каких-либо ссылок на этот столбец (в представлениях, ограничениях, условиях) оператор будет отвергнут. Если же будет ис­пользован параметр CASCADE, то все объекты, имеющие ссылки на этот столбец, будут уничтожены.

Оператор ALTER TABLE присутствует не во всех СУБД. Часто реализация этого оператора не полностью соответствует стандарту.

Определение домена

Домен позволяет определить альтернативный тип данных. Домен создается оператором CREATE DOMAIN:

CREATE DOMAIN имя домена [AS] тип данных

[DEFAULT значение по умолчанию]

[определение ограничения...]

[COLLATE имя сравнения];

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

7.8.2. Запросы

Оператор SELECT

Общая характеристика оператора. Для отбора информации из базы данных служит оператор SELECT. Синтаксис оператора выгля­дит следующим образом:

SELECT [DISTINCT]

{{функция агрегирования.. | выражение для вычисления значения

[AS имя столбца]}.,}

| {спецификатор.*}

|*

FROM {{имя таблицы [А8][имя корреляции].[(имя столбца.,..)]}

|{подзапрос [АS][имя корреляции.[имя столбца.,..]}

|соединенная таблица}.,..

[WHERE предикат ]

[GROUP BY {{[ имя таблицы| имя корреляции]}.] имя столбца}.,..}]

[HAVING предикат]

[UNION | INTERSECT | EXCEPT}[ALL]

[CORRESPONDING [BY (имя столбца.,..)]]

оператор select | TABLE имя таблицы | конструктор значений таб­лицы]

[ORDER ВY{{столбец-результат [ASC| DESC]}.,..}

|{{положительное 4hoio[ASC| DESC]}.,..}]};

Оператор состоит из предложений SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, которые должны быть записаны в команде именно в той последовательности, в которой они перечисле­ны в синтаксической формуле.

Предложение SELECT. Оно определяет столбцы таблицы, полу­чаемой в результате выполнения запроса. Столбец результатной таб­лицы может быть задан именем столбца исходной таблицы. Если в запросе используется несколько таблиц и в них имеются поля, имею­щие одинаковые имена, то для указания такого поля используется кон­струкция <имя таблицы>.<имя поля>. Кроме того, в предложении SELECT могут использоваться любые допустимые выражения, кото­рые зададут формулу для определения вычисляемого поля. С помо­щью [AS <имя столбца>] можно задать имя столбца-результата. Эту конструкцию можно использовать не только тогда, когда определяются вычисляемые поля, но и во всех других случаях, когда нужно задать имя столбца-результата, отличающееся от имени столбца ис­ходной таблицы.

Результат выборки может в принципе содержать повторяющиеся строки. Чтобы избежать вывода повторяющихся строк в ответе, ис­пользуется параметр DISTINCT.

Запросы могут использовать функции агрегирования. Стандарт предусматривает использование следующих функций агрегирования:

• COUNT - подсчет;

• SUM - сумма;

• МАХ -максимум;

• MIN - минимум;

• AVG - среднее.

Чаще всего функции агрегирования используются совместно с предложением GROUP BY, но могут применяться и самостоятельно. В последнем случае результат относится не к какой-то группе, а ко всей выборке.

Существуют два типа функции COUNT. Первый тип использует символ «*». В этом случае функция подсчитывает количество строк в группе. Отдельные значения столбцов при этом не учитываются, и результат не будет зависеть от того, имеются ли в полях значения Null и указан ли параметр DISTINCT. Второй тип функции COUNT игно­рирует значения Null.

Если в ответ требуется включить все поля таблицы, то для этого можно использовать символ «*». Если запрос многотабличный, то следует применять конструкцию {спецификатор.*}

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

Начиная со стандарта SQL-92 в предложение FROM можно вклю­чать встроенный оператор JOIN, который служит для задания разно­образных условий соединения таблиц, участвующих в запросе.

Типы соединения и выполняемые ими действия приведены ниже.

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

Тип соединения	Выполняемое действие
Cross (перекрестное)	Прямое декартово произведение
Natural (естественное)	Соединение внешнего ключа со связанным с ним ключом (одноименные столбцы)
Inner (включающее или внутреннее)	Эквисоединение таблиц А и В (равные значения соответствующих столбцов)
Left (левое (внешнее))	Все строки таблицы А, а также значения из тех строк таблицы В, которые имеют совпадающие значения в поле связи
Right (правое (внешнее))	Все строки таблицы В, а также значения из тех строк таблицы А, которые имеют совпадающие значения в поле связи
Full (полное)	Объединяет левое и правое соединения
Union (соединение типа объединение)	Противоположно Inner

Таблица «А_сотрудники» содержит сведения о сотрудниках фир­мы X:

Таб_ном	ФИО
01	Диго
02	Афанасьев
03	Сидоров

Таблица «Б_разработки» содержит информацию о том, какие ПП предлагаются для распространения на фирме X и кто является авто­ром каждой разработки. Поскольку для сторонних разработчиков нет табельного номера (поле «А_сотрудники.таб_ном»), то в таблице «Б_разработки» фиксируются ФИО разработчиков.

ФИО	Продукт
Диго	П1
Диго	П2
Афанасьев	П3
Чистов	П4

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

Если в предложении FROM перечислено несколько таблиц, то все они неявно считаются соединяемыми. Если тип соединения явно не задан, то считается, что каждая строка первой таблицы соединяется с каждой строкой второй таблицы. Именно такое соединение и называ­ется перекрестным.

Результат перекрестного соединения для приведенных выше таб­лиц представлен ниже.

Таб_ном	А_сотрудники.фио	Б_разработки.фио	Продукт
01	Диго	Диго	П1
01	Диго	Диго	П2
01	Диго	Афанасьев	ПЗ
01	Диго	Чистов	П4
02	Афанасьев	Диго	П1
02	Афанасьев	Диго	П2
02	Афанасьев	Афанасьев	ПЗ
02	Афанасьев	Чистов	П4
03	Сидоров	Диго	Ш
03	Сидоров	Диго	П2
03	Сидоров	Афанасьев	ПЗ
03	Сидоров	Чистов	П4

Запрос на SQL может иметь следующий вид:

Характеристики

Список файлов книги