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

ВВЕДЕНИЕ В SQL

Язык SQL используется для реализации всех функцио­нальных возможностей, необходимых для управления БД:

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

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

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

Таким образом, хотя SQL и не объявляется полноценным язы­ком программирования, он является достаточно полным и мощ­ным языком для управления взаимодействием с СУБД. SQL являет­ся подъязыком баз данных, предназначенным для управления базами данных. SQL на сегодняш­ний день является единственным стандартным языком для работы с реляционными базами данных.

Операторы SQL встраиваются в базовый язык, например PASCAL, FORTRAN или С, и дают возможность получать доступ к базам данных из прикладных программ.

Основные понятия и компоненты.

Инструкции и имена

SQL представлен множеством инструкций, каждая из которых пред­писывает СУБД выполнить определенное действие. Инструк­ция SQL начинается с команды — ключевого слова, описывающего действие, выполняемое инструкцией. Следом за командой указывается одно или не­сколько предложений. Предложение описывает данные, с которыми должна работать инструкция, или уточняет действие, выполняемое инструкцией. Предложения в инструкции делятся на обязательные и необязательные. Каждое предложение начинается с ключевого слова. Многие предложения в качестве параметров содержат имена таблиц или столбцов; некоторые из них могут содержать дополнительные ключевые слова, константы и выражения.

У каждого объекта в базе данных есть уникальное имя. Имена используются в инструкциях SQL и указывают, над каким объектом базы данных инструкция должна выполнить действие. В соответст­вии со стандартом ANSI/ISO имена в SQL могут содержать от 1 до 128 символов, начинаться с буквы и не должны включать пробелов или специальных символов пунктуации.

В инструкциях SQL могут использоваться как полные имена объектов, так и короткие. Полное имя таблицы (в отличие от корот­кого) содержит имя пользователя и короткое имя таблицы, разде­ленные точкой:

<Имя_пользователя>.<Имя_таблицы>

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

Полное имя столбца в свою очередь состоит из полного (или короткого) имени таблицы, которой принадлежит столбец, и корот­кого имени столбца, разделенных точкой:

<Имя_пользователя>.<Имя_таблицы>.<Имя_столбца> или <Имя_таблицы>.<Имя столбца>

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

Типы данных

Современные СУБД позволяют обрабатывать данные разнообразных типов, среди которых наиболее распространенными можно назвать следующие.

Целые числа (INT, SMALLINT). В столбцах, имеющих такой тип данных, обычно хранятся данные о количестве и возрасте со­трудников, идентификаторы.

Десятичные числа (NUMERIC, DECIMAL). В столбцах данного типа хранятся числа, имеющие дробную часть с фиксированным количеством знаков после запятой, например курсы валют и про­центы.

Числа с плавающей запятой (REAL, FLOAT). Числа с плаваю­щей запятой представляют больший диапазон действительных зна­чений, чем десятичные числа.

Строки символов постоянной длины (CHAR). В столбцах, имеющих этот тип данных, хранятся имена и фамилии, географиче­ские названия, адреса и т. п.

Строки символов переменной длины (VARCHAR). Столбцы этого типа позволяют хранить символьные строки, длина которых изменяется в заданном диапазоне.

Денежные величины (MONEY, SMALLMONEY). Наличие от­дельного типа данных для хранения денежных величин позволяет правильно форматировать их и снабжать признаком валюты перед выводом на экран.

Дата и время (DATETIME, SMALLDATETIME). Поддержка особого типа данных для значений дата/время. Как правило, с этим типом данных связа­ны особые операции и процедуры обработки.

Булевы величины (BIT). Столбцы такого типа данных позволя­ют хранить логические значения True (1) и False (0).

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

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

Встроенные функции

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

Встроенные функции, доступные при работе с SQL, можно ус­ловно разделить на следующие группы:

• математические функции;

• строковые функции;

• функции для работы с величинами типа дата-время;

• функции конфигурирования;

• системные функции;

• функции системы безопасности;

• функции управления метаданными;

• статистические функции.

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

 

Функция	Назначение
АВS(число)	Вычисляет абсолютную величину числа
ISNUMERIC(выражение)	Определяет, имеет ли выражение числовой тип данных
SIGN(число)	Определяет знак числа
RAND(целое число)	Вычисляет случайное число
ROUND(число, точность)	Выполняет округление числа с указанной точ­ностью
POWER (число, степень)	Возводит число в степень
SQRT(число)	Извлекает квадратный корень из числа
SIN (угол)	Вычисляет синус угла, указанного в радианах
COS(угол)	Вычисляет косинус угла, указанного в радианах
ЕХР(число)	Вычисляет экспоненту числа
LOG(число)	Вычисляет натуральный логарифм числа
LEN (строка)	Вычисляет длину строки в символах
LTRIM(строка)	Удаляет пробелы в начале строки
RTRIM(cтрокa)	Удаляет пробелы в конце строки
LEFT(строка, количество)	Возвращает указанное количество символов строки, начиная с самого левого символа
RIGHT(строка, количество)	Возвращает указанное количество символов строки, начиная с самого правого символа
LOWER (строка)	Приводит символы строки к нижнему регистру
UPPER (строка)	Приводит символы строки к верхнему регистру
STR (число)	Выполняет конвертирование числового значе­ния в символьный формат
SUBSTRING (строка, индекс, длина)	Возвращает для строки подстроку заданной длины, начиная с символа заданного индекса
GETDATE()	Возвращает текущую системную дату
ISDATE(строка)	Проверяет строку на соответствие одному из форматов даты и времени
DAY(дата)	Возвращает число указанной даты
MONTH (дата)	Возвращает месяц указанной даты
YEAR(дата)	Возвращает год указанной даты
DATEADD(тип, число, дата)	Прибавляет к дате указанное число единиц за­данного типа (год, месяц, день, час и т. п.)

Значения NULL

При заполнении таблиц базы данных отдельные элементы в них мо­гут отсутствовать. Например, при заполнении таблицы «Студенты» или «Кадровый_состав» может быть не задан для некоторых строк номер телефона, тем не менее, строка должна быть введена в табли­цу и должна участвовать в запросах на выдачу информации.

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

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

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

Первичный ключ таблицы

Всякая таблица обычно содержит один или несколько столбцов, значение или совокупность значений которых уникально идентифи­цируют каждую строку в таблице. Этот столбец (или столбцы) назы­вается первичным ключом (Primary Key, PK) таблицы.

Если в первичный ключ входит более одного столбца, значения в пределах одного столбца могут дублироваться, но любая совокуп­ность значений всех столбцов первичного ключа при этом должна быть уникальна. Например, в таблице «Дисциплины» один столбец (ID_Дисциплина) определен как первичный ключ (рис. 21), а для таблицы «Сводная ведомость» задан составной первичный ключ - в него входят значения столбцов ID_Студент и ID_Дисциплина.

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

Рис. 21. Первичный ключ таблицы «Сводная_ведомость»

 

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

 

Рис. 22. Первичный ключ таблицы «Учебный_план»

 

Если по столбцу строится первичный ключ, столбцу должен быть приписан атрибут PRIMARY KEY (ограничение целостности на уровне столбца), например, описание столбца ID_План для таб­лицы «Учебный_план» (см. рис. 22) может выглядеть так: ID_Дисциплина INTEGER NOT NULL PRIMARY KEY

Первичный ключ может быть также построен с помощью от­дельного предложения PRIMARY KEY (ограничение целостности на уровне таблицы) - путем включения имени (имен) ключевого столбца (столбцов) в качестве параметров. Например, первичный ключ для таблицы «Сводная_ведомость» (рис. 1) может быть задан следующим образом:

PRIMARY KEY (ID_Дисциплина, ID_Студент)

Внешний ключ таблицы

Внешний ключ строится в дочерней (зависимой) таблице для соеди­нения родительской (главной) и дочерних таблиц БД.

Это ограничение целостности предназначено для организации ссылочной целостности данных. Внешний ключ связывается с по­тенциальным первичным ключом в другой таблице. Внешний ключ при этом может ссылаться либо на столбец (или столбцы) с ограни­чением целостности PRIMARY KEY, либо на столбец (столбцы) с ограничением целостности UNIQUE.

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

Столбцы внешнего ключа (в отличие от столбцов первичного ключа) могут содержать значения типа NULL, однако при этом проверка на ограничение FOREIGN KEY будет пропускаться. За­дать внешний ключ можно как при создании, так и при изменении таблиц.

Синтаксис определения внешнего ключа следующий:

FOREIGN KEY (<список столбцов внешнего ключа>)

REFERENCES <имя родительской таблицы>

[[<список столбцов родительской таблицы>]

[ON DELETE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}]

[ON UPDATE {NO ACTION | CASCADE | SET DEFAULT| SET NULL}]]

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

Имя родительской таблицы определяет таблицу, в которой опи­сан первичный ключ (или столбец с атрибутом UNIQUE). На этот ключ (столбец) должен ссылаться внешний ключ дочерней таблицы для обеспечения ссылочной целостности.

Список столбцов родительской таблицы, определяющий ссылоч­ную целостность, необязателен при ссылке на первичный ключ ро­дительской таблицы. При ссылке в родительской таблице на стол­бец с атрибутом UNIQUE этот список лучше привести.

Параметры ON DELETE, ON UPDATE задают способы измене­ния подчиненных записей дочерней таблицы при удалении (ON DELETE) или изменении (ON UPDATE) поля связи в записи роди­тельской таблицы. Перечислим эти способы:

- NO ACTION - запрещает удаление/изменение родительской записи при наличии подчиненных записей в дочерней таблице;

- CASCADE - при удалении записи родительской таблицы (ис­пользуется совместно с ON DELETE) происходит удаление всех подчиненных записей в дочерней таблице; при измененииполя связи в записи родительской таблицы (используется совместно с ON UPDATE) происходит изменение на то же значение поля внешнего ключа у всех подчиненных записей в дочерней таблице;

- SET DEFAULT - в поле внешнего ключа записей дочерней таблицы заносится значение этого поля по умолчанию, ука­занное при определении поля (параметр DEFAULT);

- SET NULL - в поле внешнего ключа записей дочерней таб­лицы заносится значение NULL.

Установим связь между таблицами «Студенты», «Учебный_план» и «Сводная_ведомость»:

ALTER TABLE Сводная ведомость

ADD FOREIGN KEY (ID_План)

REFERENCES Учебный_план

 

ALTER TABLE Сводная ведомость

ADD FOREIGN KEY (ID_Студент)

REFERENCES Студенты

 

Хотя в рассмотренном примере имена столбцов первичного и внешнего ключей в обеих таблицах совпадают, это не является обя­зательным. Первичный ключ может быть определен для столбца с одним именем, в то время как столбец, на который наложено огра­ничение FOREIGN KEY, может иметь совершенно другое имя. Од­нако лучше давать таким столбцам идентичные названия, чтобы по­казать связь между ними (рис. 23).

Рис. 23. Связь внешнего и первичного ключей