4.7.11Запросы на создание таблиц

Запрос на создание таблиц аналогичен запросу на добавление, за исключением того, что он создает новую таблицу и сразу же копирует в нее записи. Общий вид запроса:

SELECT <список полей> INTO<имя новой таблицы>

Примеры.

Из таблицы ЗАКАЗЫ сформировать новую таблицу АРХИВ_ЗАКАЗОВ, которая содержит заказы, сделанные до 1 января 2007 года:

SELECT ЗАКАЗЫ.* INTO АРХИВ_ЗАКАЗОВ

FROM ЗАКАЗЫ

WHERE [Дата заказа] <= #01/01/07#;

Из таблицы ПОСТАВКА сформировать новую таблицу АРХИВ, в которую отправить записи с датой поступления, совпавшей с запрашиваемой:

SELECT ПОСТАВКА.[Индекс_товара], ПОСТАВКА.[Код_поставщика], ПОСТАВКА.[Дата_поступления], ПОСТАВКА.[Количество]

INTO АРХИВ FROM ПОСТАВКА

WHERE ПОСТАВКА.[Дата_поступления]=[Введите дату поступления];

4.7.12Использование языка определения данных

Команды языка определения данных (Data Definition Language – DDL) представляют собой инструкции SQL, которые позволяют создавать и модифицировать элементы структуры базы данных. Например, используя DDL, можно создавать, удалять таблицы и изменять их структуру, создавать и удалять индексы.

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

CREATE TABLE <имя таблицы>

(<имя столбца> <тип данных> [NOT NULL]

[,<имя столбца> <тип данных> [NOT NULL]]…)

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

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

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

Стандарт SQL поддерживает следующие типы данных.

Строка данных

CHARACTER [(длина)] или CHAR [(длина)]

Значения данного типа имеют фиксированную длину, которая определяется параметром длина. Этот параметр может принимать значения от 1 до 255 символов. Если вводимая текстовая константа меньше заданной длины, то автоматически строка дополняется справа пробелами.

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

VARCHAR [(длина)]

Этот тип описывает текстовую строку, которая может иметь произвольную длину. Максимальная длина определяется конкретной реализацией языка SQL (например, в ORACLE – до 2000 символов). При вводе текстовой константы, длина которой меньше заданной, не происходит дополнение ее пробелами до заданного максимального значения.

Текстовые константы в выражениях SQL заключаются в одиночные кавычки.

Если длина строки не указана явно, она полагается равной одному символу во всех случаях.

Числовые типы данных.

Стандартными числовыми типами данных SQL являются:

INTEGER – используется для представления целых чисел в диапазоне от –231 до +231;

SMOLLINT – используется для представления чисел в диапазоне от –2¹⁵ до +2¹⁵;

DECIMAL (точность [, масштаб]) – десятичное число с фиксированной точкой, точность определяет количество значащих цифр в числе. Масштаб указывает максимальное число цифр справа от точки.

NUMERIC (точность[, масштаб]) – десятичное число с фиксированной точкой, такое же, как и DECIMAL;

FLOAT [(точность)] – число с плавающей точкой и указанной минимальной точностью;

REAL – число такое же, как при типе FLOAT, за исключением определения точности по умолчанию (в зависимости от конкретной реализации SQL);

DOUBLE PRECISION – число аналогично REAL, но точность в два раза выше точности REAL.

3. Дата и время.

Тип данных, предназначенный для представления даты и времени, является нестандартным. Каждая конкретная СУБД специфически определяет эти типы.

Константы типа даты записываются в зависимости от формата, принятого в операционной системе. Например, ’03.05.2006’, ‘12/09/2006’, ’09-nov-2006’, ’07-apr-06’.

При создании таблицы возможно задание ограничений.

1. Ограничение Not null может быть установлено для любого поля реляционной таблицы. При наличии ограничения Not null запрещается ввод значений null в это поле. Атрибут null устанавливается по умолчанию. Атрибут Not null означает, что в поле обязательно должна быть внесена информация.

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

3. Ограничение Unique означает, что все значения в поле уникальны. Однако в отличие от первичного ключа допускается наличие в поле пустых значений (если не установлено Not null). Ограничение записывается после определения типа поля.

4. Значение по умолчанию задается с помощью Default. Оно записывается после определения типа поля, например:

Create table имя таблицы

(… имя_поля тип_данных Default= значение по умолчанию).

Рассмотрим создание таблиц с использованием возможностей различных СУБД.

В табл. 3.3 перечислены типы данных, которые можно использовать при создании таблиц, используя Microsoft Jet DDL и предложение CREATE (СУБД Access).

Типы данных полей, доступных в Jet Таблица 3.3

Тип данных	SQL тип
Счетчик	COUNTER
Текстовый	TEXT
Memo	LONGTEXT
Денежный	CURRENCY
Дата/время	DATETIME
Числовой (одинарное с плавающей точкой)	SINGLE
Числовой (двойное с плавающей точкой)	DOUBLE
Числовой (целое)	INTEGER
Числовой (длинное целое)	LONG
Числовой (байт)	BYTE

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

Пример 1. Создание таблицы TABL1.

CREATE TABLE TABL1

( [FIL1] COUNTER, [FIL2] TEXT (10),

[FIL3] CURRENCY, [FIL4] DATETIME, [FIL5] BYTE, [FIL6] INTEGER, [FIL7] SINGLE, [FIL8] LONG,

[FIL9] DOUBLE,

CONSTRAINT PrimaryKey PRIMARY KEY ([FIL1]) )

В данном примере поле FIL1 объявлено ключевым, для данного поля создан индекс с именем PrimaryKey.

Пример 2. Создание таблицы TABL2.

CREATE TABLE TABL2

( [FIL1] INTEGER, [FIL2] TEXT (10) NOT NULL,

[FIL3] CURRENCY, [FIL4] LONGTEXT,

CONSTRAINT PrimaryKey PRIMARY

KEY ([FIL1], [FIL2]) )

В данном примере поля FIL1, FIL2 объявлены ключами и поле FIL2 объявлено также обязательным.

Пример 3. Создание таблицы TABL2 (структура таблицы совпадает с таблицей из примера 2). В данной таблице поле FIL1 объявлено внешним ключом. Между таблицами TABL1 и TABL2 устанавливается связь «один-ко-многим» по полю FIL1.

CREATE TABLE TABL2

([FIL1] INTEGER, [FIL2] TEXT (10) NOT NULL, [FIL3] CURRENCY, [FIL4] LONGTEXT,

CONSTRAINT PrimaryKey PRIMARY KEY ([FIL1],[FIL2]),

CONSTRAINT ForeignKey FOREIGN KEY ([FIL1])

REFERENCES TABL1 ([FIL1]))

Примеры создания таблиц с использованием языка SQL, встроенного в Delphi.

Пример 1. Создание таблицы STUDENT.

CREATE TABLE STUDENT

(Nom_z Character (10), Gr Character (10), Fam Varchar (30) Not null)

Пример 2. Создание таблицы STUDENT с ключевым полем Nom_z

CREATE TABLE STUDENT

(Nom_z Character (10) Not null primary key, Gr Varchar (10), Fam Varchar (30) not null)

Пример 3. Создания таблицы STUDEN с двумя ключевыми полями

CREATE TABLE STUDENT

(Nom_z Character (10) Not null, Gr Varchar (10) Not null, Fam Varchar (30), primary key (Nom_z, Gr))

Пример 4. Создание таблиц и связывание их между собой. Таблицы:

Firma (Nomer, Naim, Adres), Tovar (Kod, Nazv, Cena),

Postavka (Nomer_p, Nomer, Kod, Kol).

Первая и третья таблицы связаны по полю Nomer, вторая и третья таблицы связаны по полю Kod.

Create table Firma

(Nomer Integer Not null primary key, Naim Varchar (150), Adres Varchar (50))

Create table Tovar

(Kod Integer Not null primary key, Nazv Varchar (50), Cena decimal (8,2))

Create table Postavka

(Nomer_p Integer Not null primary key, Nomer Integer, Kod Integer, Kol Decimal (8,2),

foreign key Firma_Postavka (Nomer) references Firma (Nomer), foreign key Tovar_Postavka (Kod) references Tovar (Kod))

Удаление таблиц. Удалять элементы базы данных можно с помощью предложения DROP. Оператор удаления таблиц имеет следующий вид:

DROP TABLE <имя таблицы>

Например, удаление таблицы TABL2 можно осуществить следующим образом:

DROP TABLE TABL2

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

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

MODIFY | ADD | DROP <имя поля> [<тип данных>]

Пример 1. Добавление поля.

ALTER TABLE Student

ADD (Email Character (25))

Пример 2. Изменение типа поля.

ALTER TABLE Student

MODIFY (Email Varchar (25))

Пример 3. Удаление столбца.

ALTER TABLE Student

DROP (Email)

Создание индексов. Помимо создания индексов в процессе формирования таблицы (с помощью предложения CONSTRAINT), можно также создавать индексы уже после того, как таблица сформирована (с помощью предложения CREATE INDEX). Это приносит эффект в тех случаях, когда таблица уже существует (в то время как конструкция CONSTRAINT применяется для формирования индексов только в момент создания таблицы). Оператор создания индекса имеет следующий вид:

CREATE [UNIQUE] INDEX <имя индекса>

ON <имя таблицы>

(<имя столбца> [ASC | DESC]

[, <имя столбца> [ASC | DESC]…)

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

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

При создании индекса можно задать порядок автоматической сортировки значений в столбцах – в порядке возрастания ASC (по умолчанию), или в порядке убывания DESC. Для разных столбцов можно задавать различный порядок сортировки.

Пример 1: создание индекса FIL2_Index в таблице TABL1 по полю FIL2.

CREATE INDEX FIL2_Index

ON TABL1 ([FIL2])

Пример 2: создание уникального индекса FIL1_Index (имеет разные значения, в том числе и NULL) по полю FIL1 в таблице TABL1.

CREATE UNIQUE INDEX FIL1_Index

ON TABL1 ([FIL1])

Пример 3: создание уникального индекса FIL3_Index, не имеющего значения NULL , по полю FIL3 в таблице TABL1.

CREATE UNIQUE INDEX FIL3_Index

ON TABL1 ([FIL3])

WITH DISALLOW NULL

Пример 4: добавление первичного ключа в таблицу TABL1 и построение для него индекса PrimaryKey. Ключом объявляется поле FIL1.

CREATE UNIQUE INDEX PrimaryKey

ON TABL1 ([FIL1])

WITH PRIMARY

Пример 5: создание в таблице TABL1 индекса для двух полей, одно из которых сортируется по возрастанию (FIL2), а второе поле сортируется по убыванию (FIL3).

CREATE INDEX FIL2_FIL3_Index

ON TABL1 ([FIL2],[FIL3] DESC)

Удаление индекса. Оператор удаления индекса DROP INDEX имеет следующий вид:

DROP INDEX <имя индекса> ON <имя таблицы>

Пример: удаление индекса FIL3_Index из таблицы TABL1.

DROP INDEX FIL3_Index ON TABL1