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

В теории практики баз данных всегда различают 2 понятия: язык манипулирования данными (ЯМД) и язык описания данных (ЯОД). К ЯМД относятся рассмотренные выше команды SQL, а к ЯОД относятся команды создания таблиц и связей между ними. Речь идёт не только об описании состава и формата полей, но и о значении по умолчанию и ограничении на множество допустимых значений. Всё это определяется не на уровне конкретных записей, а как свойство определяемых таблиц данных и совокупностей таблиц.

Создание таблиц может быть выполнено командой языка ЯМД на основе данных из уже существующих таблиц. Это типичная форма хранения промежуточных данных. Формат это команды имеет следующий вид:

SELECT список_полей INTO новая_таблица FROM источник;

Здесь список_полей – это имена полей исходных таблиц, из которых будет состоять новая таблица; новая_таблица – имя создаваемой таблицы; источник – таблица или подзапрос.

Пример:

Создать таблицу средний балл с данными о средних баллах студентов.

SELECT snum, avg INTO новая_таблица FROM источник;

средбалл (snum, avg)

SELECT osnum, AVG (ocen) FROM оценка

GROUP BY osnum;

В этом запросе, в отличие от запроса INSERT INTO таблицу Средний балл не нужно предварительно создавать.

Другим способом создания таблиц является использование команд ЯОД. В MS Access эти команды определены в режиме SQL, но не имеют аналогов в конструкторе запросов. Это уменьшает гибкость использования SQL, т.к. требует предварительного определения всех временно создаваемых таблиц. Формат этой команды имеет следующий вид:

CREATE TABLE имя_таблицы (имя_поля тип_данных [(размер)], … имя_поля тип_данных [(размер)]);

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

Типы данных для различных программных средств значительно отличаются, однако в целях совместимости со стандартом всегда поддерживают стандартные ANSI типы данных. К ним относятся:

1. char (размер) – символьные данные (размер по умолчанию равен 1);
2. long – целое (4 байта);
3. real – с плавающей точкой (4 байта);
4. numeric, float - с плавающей точкой (8 байта);

Пример:

Создать таблицу о среднем балле студента.

CREATE TABLE Средбалл (snum char (3), avg real]);

Структуру таблицы существующей можно изменить по команде ALTER TABLE. Эта команда не является частью стандарта ANSI, но присутствует практически во всех реализациях SQL. Формат команды имеет следующий вид:

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

ADD (column) имя_поля тип_данных [размер];

Пример:

Добавить в таблицу Средний балл фамилию студента.

ALTER TABLE Средбалл

ADD sname char (30);

Для удаления столбца используется следующая команда:

ALTER TABLE имя_таблицы DROP [COLUMN] имя_поля;

Пример:

ALTER TABLE Средбалл DROP sname;

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

DROP TABLE имя_таблицы;

Пример:

Удалить таблицу Средний балл:

DROP TABLE Средбалл;

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

Когда создаётся таблица можно определить ограничения назначения, которые вводятся в поля, и SQL будет отвергать любое значение, не удовлетворяющее условию. Ограничение может быть задано в команде CREATE TABLE для конкретного столбца или группы столбцов. Следует отметить, что данное средство не работает в MS Access. Команда CREATE TABLE с такими ограничениями отвергается в принципе. Формат команды CREATE TABLE с возможными ограничениями имеет вид:

CREATE TABLE имя_таблицы (имя_поля тип_данных [(размер)] [ограничение]…, имя_таблицы (имя_поля тип_данных [(размер)] [ограничение])

Типы ограничений к команде:

1. NOT NULL – значение поля или полей должно быть обязательно задано
2. CHECK (условие) – должно быть выполнено условие назначения полей. Если ограничение применяется к значению столбца, то должно использоваться только имя столбца. Если ограничение применяется к таблице, то может использоваться значение всех полей добавляемой записи.
3. UNIQUE – значение поля или полей в различных таблицах не должны повторяться.
4. PRIMARY KEY – поле или группа полей объявляются первичным ключом. Эти поля не могут содержать NULL-значений. Значение ключа должно быть уникальным, и таблица должна иметь только один такой ключ.

Пример:

Создать таблицу наиболее успевающих студентов, специализирующихся на кафедре.

CREATE TABLE КАФ_СТУД_ОТЛ (snum char (3) UNIQUE, sname char (30), ball float CHECK (ball >= 4), NOT NULL (snum,sname));

Таблица оценка может быть задана со следующими параметрами:

CREATE TABLE Оценка (opnum char (3) NOT NULL, osnum char (3) NOT NULL, odate char (8) NOT NULL, ocen int CHECK (ocen IN NULL OR ocen >= 2 AND ocen =< 5), PRIMARY KEY (opnum, osnum, odate);

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

Например:

ball float CHECK (ball >= 4) DEFAULT = 4;

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