Домены (области значений) в SQL



Описание данных на основе SQL

 

Единственной структурой представления данных (как прикладных, так и системных) в реляционной базе данных (БД) является двумерная таблица.

Любая таблица может рассматриваться как одна из форм представления теоретико-множественного понятия отношение (relation), отсюда название модели данных - «реляционная».

 В реляционной модели данных таблица обладает следующими основными свойствами:

1. идентифицуруется уникальным именем;

2. имеет конечное (как правило, постоянное) ненулевое количество столбцов; 3. имеет конечное (возможно, нулевое) число строк;

4. столбцы таблицы идентифицируются своими уникальными именами и номерами;

 5. содержимое всех ячеек столбца принадлежит одному типу данных (т.е. столбцы однородны), содержимым ячейки столбца не может быть таблица;

6. строки таблицы не имеют какой-либо упорядоченности и идентифицируются только своим содержимым (т.е. понятие «номер строки» не определено);

7. в общем случае ячейки таблицы могут оставаться «пустыми» (т.е. не содержать какого-либо значения), такое их состояние обозначается как NULL.

Необходимо иметь в виду, что видимая пользователям СУБД логическая организация данных (в нашем случае с помощью реляционной модели) может очень слабо коррелироваться с их физической организацией в памяти ЭВМ.

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

1. требования уникальности содержимого каждой ячейки какого-либо столбца и/или совокупности ячеек в строке, относящихся к нескольким столбцам;

2. запрета для какого-либо столбца (столбцов) иметь «пустые» (NULL) ячейки.

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

Ключом таблицы называется столбец или комбинация столбцов, содержимое ячеек которого(ых) используется для прямого доступа («быстрого» определения местоположения) к строкам таблицы.

Различают ключи первичный (он может быть только единственным для каждой таблицы) и вторичные.

Первичный ключ уникален и однозначно идентифицирует строку таблицы. Столбец строки, определенный в качестве первичного ключа, не может содержать «пустое» (NULL) значение в какой-либо своей ячейке.

 Вторичный ключ определяет местоположение, в общем случае, не одной строки таблицы, а нескольких «подобных» (в любом случае ускоряя доступ к ним, хотя не в такой степени как ключ первичный). Ключи используются внутренними механизмами СУБД для оптимизации затрат на доступ к строкам таблиц (путем, например, их физического упорядочения по значениям ключей или построения двоичного дерева поиска).

 Основными операциями над таблицами являются следующие.

1. Проекция - построение новой таблицы из исходной путем включения в нее избранных столбцов исходной таблицы.

2. Ограничение - построение новой таблицы из исходной путем включения в нее тех строк исходной таблицы, которые отвечают некоторому критерию в виде логического условия (ограничения).

 3. Объединение - построение новой таблицы из 2-ух или более исходных путем включения в нее всех строк исходных таблиц (при условии, конечно, что они подобны).

4. Декартово произведение - построение новой таблицы из 2-ух или более исходных путем включения в нее строк, образованных всеми возможными вариантами конкатенации (слияния) строк исходных таблиц. Количество строк новой таблицы определяется как произведение количеств строк всех исходных таблиц.

Пречисленные выше 4 операции создают базис, на основе которого может быть построено большинство (но не все) практически полезных запросов на извлечение информации из реляционной БД.

Кроме перечисленных выше в языке SQL реализованы операции модификации содержимого строк таблицы и пополнения таблицы новыми строками (что теоретически может рассматриваться как операция объединения), а также операции управления таблицами.

Рассмотренные выше операции над таблицами реляционной БД обладая функциональной полнотой, будучи реализованы на практике в своем «чистом» каноническом виде, как правило, крайне неэкономичны (в первую очередь это относится к комбинации операций ограничения и декартового произведения). Разработчики реальных реляционных СУБД прибегают ко всевозможным приемам и «ухищрениям» для минизации вычислительных затрат (в первую очередь, машинного времени) при выполнении этих операций.

Общим способом, нашедшим отражение в языке SQL, повышения эффективности выполнения запросов в реляционных СУБД являются импользование ключей индексов.

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

Индексы неявным образом (скрытно от пользователя) автоматически создаются для всех ключей таблицы.

В настоящее время наибольшее распространение получили реляционные SQL СУБД двух групп:

1. мощные крупные коммерческие СУБД, ориентированные на хранение огромных объемов информации (от гигабайт);

2. мобильные компактные свободно распространяемые (в том числе и в исходных кодах) СУБД, использование которых оправдано и для БД объемом всего лишь в десятки килобайт.

Наиболее известными СУБД первой группы являются: • Sybase SQLserver фирмы Sybase, Inc.; • Oracle фирмы Oracle Corporation; • Ingres фирмы Computer Associates International; • Informix фирмы Informix Corporation.

 К наиболее популярным СУБД второй группы относятся: • PostgreSQL организации PostgreSQL; • microSQL фирмы Hughes Technologies Pty. Ltd.; • mySQL фирмы T.C.X DataKonsult AB.

Типы данных

Типы данных, используемые в языке SQL для хранения информации в столбцах таблиц БД, весьма разнообразны. К сожалению, производители конкретных реляционных СУБД считают своим долгом «улучшить» множество типов данных, регламентируемых стандартом, реализуя свои собственные версии и расширения.

 

• INT[(len)] - целое число длиной 4 байта, представляемое при выводе максимально len цифрами;

• SMALLINT[(len)] - целое число длиной 2 байта, представляемое при выводе максимально len цифрами;

• FLOAT[(len,dec)] - действительное число, представляемое при выводе максимально len символами с dec цифрами после десятичной точки;

• CHAR(size) - строка символов фиксированной длины размером size символов;

 • VARCHAR(size) - строка символов переменной длины максимальным размером до size символов;

• BLOB (Binary Large OBject) - массив произвольных (двоичных) байтов (максимальный размер зависит от реализации, обычно это 65535 байт); этот тип данных может использоваться, например, для хранения изображений;

• DATE - астрономическая дата;

• TIME - астрономическое время. Символьные константы (типа CHAR и VARCHAR) записываются как последовательности символов, заключенные в одиночные апострофы, например «brass» (латунь). Десятичные константы (типа FLOAT) могут записываться в «научной» нотации как последовательности следующих компонент:

• знак числа;

 • десятичное число с точкой;

• символ «е»;

• знак («+» или «-») показателя степени;

 • целое число, играющее роль показателя степени числа 10.

 

Тип данных BLOB поддерживается непосредственно не всеми СУБД, однако каждая из них предлагает его аналог (например, BINARY или IMAGE). Рекомендация. Разрабатывая мобильное приложение (рассчитанное на работу в среде различных СУБД), старайтесь без необходимости избегать использования необязательных возможностей в описании типов данных.

Манипулирование таблицами

Для создания, изменения и удаления таблиц в SQL БД используются операторы CREATE TABLE, ALTER TABLE и DROP TABLE.

Создание таблицы

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

 CREATE TABLE имя_табл (с_спецификация, ...);

 где с_спецификация имеет разнообразный синтаксис.

 Здесь же рассматриваются наиболее часто используемые ее формы.

1. Описание столбца таблицы имя_столбца тип_данных [NULL]

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

2. Описание столбца таблицы

 имя_столбца тип_данных NOT NULL [DEFAULT по_умолч] [PRIMARY KEY]

 где конструкция NOT NULL запрещает иметь в таблице пустые ячейки в данном столбце. Конструкция PRIMARY KEY указывает, что содержимое столбца будет играть роль первичного ключа для создаваемой таблицы. Конструкция DEFAULT по_умолч переопределяет имеющееся для столбцов каждого типа данных значение «по умолчанию» (например, 0 для числовых типов), используемое при добавлении в таблицу оператором INSERT INTO строк, не содержащих значений в этом столбце.

3. Описание первичного ключа PRIMARY KEY имя_ключа (имя_столбца, ...) Эта спецификация позволяет задать первичный ключ для таблицы в виде композиции содержимого нескольких столбцов.

4. Описание вторичного ключа KEY имя_ключа (имя_столбца, ...)

 

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

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

ALTER TABLE имя_табл м_специкация [,м_спецификация ...]

где м_спецификация имеет различные формы.

Ниже рассматриваюся наиболее часто используемые.

1. Добавление нового столбца ADD COLUMN с_спецификация

 где с_спецификация - описание добавляемого столбца в том виде, как оно используется для создания таблицы оператором CREATE TABLE.

2. Удаление первичного ключа для таблицы DROP PRIMARY KEY

 3. Изменение/удаление значения «по умолчанию» ALTER COLUMN имя_столбца SET по_умолч или ALTER COLUMN имя_столбца DROP DEFAULT

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

Удаление одной или сразу нескольких таблиц из БД реализуется оператором DROP TABLE, имеющим следующий простой синтаксис DROP TABLE имя_табл, ...

Подчеркнем, что оператор DROP TABLE удаляет не только все содержимое таблицы, но и само описание таблицы из БД. Если требуется удалить только содержимое таблицы, то необходимо использовать оператор DELETE FROM.

Домены (области значений) в SQL

Понятие домена
Домен или область допустимых значений столбца определяет пользовательский тип данных и позволяет дополнительно указать:
a) Значение по умолчанию.
b) Ограничения на значения.
c) Правила проверки ограничений.
d) Время проверки ограничений.
e) Правила сравнения (для символьного типа).

Создание домена
CREATE DOMAIN <имя домена> [AS] <тип данных> [(<размер>)] [DEFAULT <значение по умолчанию>] [[<имя ограничения>] <проверка ограничения>]
[[NOT] DEFERRABLE]
[INITIALLY IMMEDIATE|DEFERRED]]
[COLLATE <имя сопоставления>]

Пример:

CREATE DOMAIN DComm AS Decimal CHECK (Comm > 0)

3) Изменение домена
ALTER DOMAIN <имя домена>
{SET DEFAULT <значение по умолчанию> |
DROP DEFAULT|ADD <имя ограничения> |
DROP CONSTRAINT <имя ограничения>}

Пример:

ALTER DOMAIN DSNUM ADD Range_chech CHEK (SNum BETWEEN 1000 AND 10000)

Удаление домена
DROP DOMAIN <имя домена> CASCADE|RESTRICT

Ключевые слова
a) [NOT] DEFERRABLE
Устанавливает [не] отсроченную проверку ограничения на значения столбца.
По умолчанию действует NOT.
Его обычно указывают для столбцов с PK, UNIQUE, многих ограничений для столбца.
Отсроченную проверку чаще указывают для утверждений.

b) INITIALLY IMMEDIATE|DEFERRED
Используется вместе с DEFERRABLE и устанавливает режим проверки ограничения столбца.
IMMEDIATE (немедленный) – после каждого оператора обновления.
DEFERRED (отсроченный) – по окончанию транзакции, т.е. после оператора COMMIT.

c) CASCADE и RESTRICT
CASCADE – при удалении домена тип данных домена, значение по умолчанию и ограничения столбца передаются в виде соответствующих типов данных, значений по умолчанию и ограничений на соответствующие столбцы таблиц.
RESTRICT – запрещает удалять используемый домен.

d) CHECK
Ограничение CHECK на значение столбца может включать проверки для других столбцов, если они включены в подзапрос в разделе WHERE.
Следовательно, <предикат> может стать ложным, если не выполняется условие не проверяемого, а кого-то другого столбца и будет ошибка.

6) Особенности значения по умолчанию
При определении начального значения домена могут быть указаны:
— Константа: число, строка, дата.
— Переменная: Current_user, Current_connection, …
— Предварительно определенный литерал даты.
— NULL.

 

Индексы

Индекс для таблицы создается с помощью инструкции CREATE INDEX. Эта инструкция имеет следующий синтаксис:

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name

ON table_name (column1 [ASC | DESC] ,...)

   [ INCLUDE ( column_name [ ,... ] ) ]

       

[WITH

[FILLFACTOR=n]

[[, ] PAD_INDEX = {ON | OFF}]

[[, ] DROP_EXISTING = {ON | OFF}]

[[, ] SORT_IN_TEMPDB = {ON | OFF}]

[[, ] IGNORE_DUP_KEY = {ON | OFF}]

[[, ] ALLOW_ROW_LOCKS = {ON | OFF}]

[[, ] ALLOW_PAGE_LOCKS = {ON | OFF}]

[[, ] STATISTICS_NORECOMPUTE = {ON | OFF}]

[[, ] ONLINE = {ON | OFF}]]

[ON file_group | "default"]

 

 

Параметр index_name задает имя создаваемого индекса. Индекс можно создать для одного или больше столбцов одной таблицы, обозначаемой параметром table_name. Столбец, для которого создается индекс, указывается параметром column1. Числовой суффикс этого параметра указывает на то, что индекс можно создать для нескольких столбцов таблицы. Компонент Database Engine также поддерживает создание индексов для представлений.

Можно проиндексировать любой столбец таблицы. Это означает, что столбцы, содержащие значения типа данных VARBINARY(max), BIGINT и SQL_VARIANT, также могут быть индексированы.

Индекс может быть простым или составным. Простой индекс создается по одному столбцу, а составной индекс - по нескольким столбцам. Для составного индекса существуют определенные ограничения, связанные с его размером и количеством столбцов. Индекс может иметь максимум 900 байтов и не более 16 столбцов.

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

Параметр CLUSTERED задает кластеризованный индекс, а параметр NONCLUSTERED (применяется по умолчанию) указывает, что индекс не изменяет порядок строк в таблице. Компонент Database Engine разрешает для таблицы максимум 249 некластеризованных индексов.

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

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

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

Параметр FILLFACTOR задает заполнение в процентах каждой страницы индекса во время его создания. Значение параметра FILLFACTOR можно установить в диапазоне от 1 до 100. При значении n=100 каждая страница индекса заполняется на 100%, т.е. существующая страница узла так же, как страница, не относящаяся к узлу, не будет иметь свободного места для вставки новых строк. Поэтому это значение рекомендуется применять только для статических таблиц. (Значение по умолчанию, n=0, означает, что страницы узлов индекса заполняются полностью, а каждая из промежуточных страниц содержит свободное место для одной записи.)

При значении параметра FILLFACTOR между 1 и 99 страницы узлов создаваемой структуры индекса будут содержать свободное место. Чем больше значение n, тем меньше свободного места в страницах узлов индекса. Например, при значении n=60 каждая страница узлов индекса будет иметь 40% свободного места для вставки строк индекса в дальнейшем. (Строки индекса вставляются посредством инструкции INSERT или UPDATE.) Таким образом, значение n=60 будет разумным для таблиц, данные которых подвергаются довольно частым изменениям. При значениях параметра FILLFACTOR между 1 и 99 промежуточные страницы индекса содержат свободное место для одной записи каждая.

После создания индекса в процессе его использования значение FILLFACTOR не поддерживается. Иными словами, оно только указывает объем зарезервированного места с имеющимися данными при задании процентного соотношения для свободного места. Для восстановления исходного значения параметра FILLFACTOR применяется инструкция ALTER INDEX.

Параметр PAD_INDEX тесно связан с параметром FILLFACTOR. Параметр FILLFACTOR в основном задает объем свободного пространства в процентах от общего объема страниц узлов индекса. А параметр PAD_INDEX указывает, что значение параметра FILLFACTOR применяется как к страницам индекса, так и к страницам данных в индексе.

Параметр DROP_EXISTING позволяет повысить производительность при воспроизведении кластеризованного индекса для таблицы, которая также имеет некластеризованный индекс. Более подробную информацию смотрите далее в разделе "Пересоздание индекса".

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

Параметр IGNORE_DUP_KEY разрешает системе игнорировать попытку вставки повторяющихся значений в индексированные столбцы. Этот параметр следует применять только для того, чтобы избежать прекращения выполнения длительной транзакции, когда инструкция INSERT вставляет дубликат данных в индексированный столбец. Если этот параметр активирован, то при попытке инструкции INSERT вставить в таблицу строки, нарушающие однозначность индекса, система базы данных вместо аварийного завершения выполнения всей инструкции просто выдает предупреждение. При этом компонент Database Engine не вставляет строки с дубликатами значений ключа, а просто игнорирует их и добавляет правильные строки. Если же этот параметр не установлен, то выполнение всей инструкции будет аварийно завершено.

Когда параметр ALLOW_ROW_LOCKS активирован (имеет значение on), система применяет блокировку строк. Подобным образом, когда активирован параметр ALLOW_PAGE_LOCKS, система применяет блокировку страниц при параллельном доступе. Параметр STATISTICS_NORECOMPUTE определяет состояние автоматического перерасчета статистики указанного индекса.

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

Параметр ON создает указанный индекс или на файловой группе по умолчанию (значение default), или на указанной файловой группе (значение file_group).

В примере ниже показано создание некластеризованного индекса для столбца Id таблицы Employee:

Изменение индексов

ALTER INDEX. Эту инструкцию можно использовать для выполнения операций по обслуживанию индекса. Синтаксис инструкции ALTER INDEX очень сходен с синтаксисом инструкции CREATE INDEX. Иными словами, эта инструкция позволяет изменять значения параметров

ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS, IGNORE_DUP_KEY и STATISTICS_NORECOMPUTE, которые были описаны ранее при рассмотрении инструкции CREATE INDEX.

Кроме вышеперечисленных параметров, инструкция ALTER INDEX поддерживает три другие параметра:

параметр REBUILD, используемый для пересоздания индекса;

параметр REORGANIZE, используемый для реорганизации страниц узлов индекса;

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

 

Удаление индексов

Для удаления индексов в текущей базе данных применяется инструкция DROP INDEX.

Использование инструкции DROP INDEX для удаления индекса показано в примере ниже:

USE SampleDb;

 

DROP INDEX ix_empid ON Employee;

Инструкция DROP INDEX имеет дополнительный параметр MOVE TO, значение которого аналогично параметру ON инструкции CREATE INDEX. Иными словами, с помощью этого параметра можно указать, куда переместить строки данных, находящиеся в страницах узлов кластеризованного индекса. Данные перемещаются в новое место в виде кучи. Для нового места хранения данных можно указать или файловую группу по умолчанию, или именованную файловую группу.

Инструкцию DROP INDEX нельзя использовать для удаления индексов, которые создаются неявно системой для ограничений целостности, таких индексов, как PRIMARY KEY и UNIQUE. Чтобы удалить такие индексы, нужно удалить соответствующее ограничение.

Исключенияэ

Типы исключений

Может появиться три типа исключений.

1. Ошибки SQL - т. е. сообщения SQL, имеющие отрицательное значение SQLCODE.

2. Внутренние ошибки субд, которые имеют отношение к конкурирующему взаимодействию, данным, метаданным и условиям окружения.

3. Пользовательские исключения, которые вы объявляете как постоянные объекты базы данных и "вызываете" в коде, когда определяется специфическое условие.

Исключение - это просто сообщение, которое генерируется, когда появляется ошибка.

Создание исключения

Создание исключения является одним из самых простых элементов DDL. Синтаксис:

CREATE EXCEPTION имя-исключения <сообщение>;

Имя-исключения- обычный идентификатор до 31 символа длиной. Оно должно быть уникальным среди идентификаторов исключений, а в диалекте 3 может быть заключено в кавычки. Тогда имя будет чувствительным к регистру.

<сообщение> - заключенная в апострофы строка текста в наборе символов NONE. Из-за ограничения размера текст должен быть лаконичным.

 Например:

CREATE EXCEPTION NO_DOGS 'NO dogs allowed!'; COMMIT;

Оператор CREATE EXCEPTION должен быть подтвержден, как и любой другой оператор DDL.

 


Дата добавления: 2019-11-25; просмотров: 179; Мы поможем в написании вашей работы!

Поделиться с друзьями:






Мы поможем в написании ваших работ!