 |
Создание таблиц при помощи команды SELECT
|
|
|
|
Ч.2
материалы к лекциям
Ростов-на-Дону
2009 г.
Автор: Заставной Д.А., преподаватель кафедры информатики и информационных таможенных технологий Ростовского филиала РТА, к.т.н.
Рецензенты
Букатов А.А. – заместитель директора ЮГИНФО ЮФУ, к.т.н., доц.
Крицкий С.П. – заведующий кафедрой информатики и информационных таможенных технологий, к.т.н., доц.
Ответственный за выпуск
Крицкий С.П. – заведующий кафедрой информатики и информационных таможенных технологий, к.т.н., доц.
Утверждено на заседании кафедры информатики и ИТТ 21 октября 2008 года (протокол №3)
Данное издание адресовано студентам четвертого курса Ростовского филиала Российской таможенной академии, специальность 080115 («Таможенное дело»), специализация «Информационные таможенные технологии». В нем содержится описание синтаксиса команд языка баз данных Oracle SQL, относящихся к созданию таблиц, представлений, индексов, и некоторых других объектов схемы, и примеры, иллюстрирующие их использование и практическое применение. Представленный в пособии материал полностью соответствует разделу программы учебной дисциплины «Системы управления базами данных».
Издание также может быть использовано в качестве справочного материала при выполнении практической работы с системой Oracle SQL Server и для самостоятельного углубленного изучения курса.
© Российская таможенная академия,
Ростовский филиал, 2009
© Заставной Д.А.
Оглавление
Оглавление. 3
Введение. 4
Раздел 1. Объекты базы данных. 5
Раздел 2. Создание таблиц. 6
2.1. Общие сведения. 6
2.2. Типы значений. 7
2.3. Значение по умолчанию.. 14
2.4. Ограничения. 15
2.5. Создание таблиц при помощи команды SELECT. 24
|
|
|
2.6. Изменение определения таблицы.. 26
2.7. Удаление таблиц. 31
Раздел 3. Последовательности. 34
Раздел 4. Синонимы.. 36
Раздел 5. Представления. 37
Раздел 6. Индексы.. 44
Раздел 7. Системный каталог. 52
Заключение. 57
Библиографический список. 58
Указатели. 59
Введение
В данном издании описаны команды языка Oracle SQL для создания таблиц, представлений, индексов, синонимов и последовательностей, и соответствующие команды для изменения и удаления этих объектов. Кроме того, в этой части приведены основные сведения, необходимые для работы с системным каталогом.
При изложении материала приняты следующие лексические соглашения. Стандартные и зарезервированные слова языка SQL записываются прописными буквами, а имена таблиц, полей и других объектов – малыми; данное соглашение, однако, не является требованием синтаксиса языка, и используется в методических целях. Вводимые новые понятия (а также текст комментариев к запросам) выделяются курсивом.
При описании SQL-команд автор счел возможным не проводить полный синтаксис, а вводить новые варианты по мере изложения материала, отсылая читателя при необходимости к соответствующим разделам системной документации и источникам в приводимой литературе.
Раздел 1. Объекты базы данных
Объектами базы данных Oracle называются специальные структуры, используемые системой баз данных для представления пользовательской и системной информации. К рассматриваемым в данном пособии объектам базы данных относятся следующие:
1. таблицы (TABLE);
2. представления (VIEW);
3. индексы (INDEX);
4. последовательности (SEQUENCE);
5. синонимы (SYNONYM);
6. ограничения (CONSTRAINTS).
Для создания, изменения и удаления (эти действия так же обобщено называются управлением объектами) объектов базы данных, называемых далее сокращенно объектами, используются соответственно тройки команд CREATE, ALTER и DROP языка SQL; например CREATE TABLE, ALTER INDEX, DROP VIEW. Эти команды языка SQL получили историческое название команды языка определения данных (Data Definition Language). Для некоторых объектов схемы существуют так же команды CREATE OR REPLACE, с помощью которых можно предварительно удалить существующий одноименный объект и создать новый с тем же именем.
|
|
|
В языке SQL Oracle существуют так же команды типа ALTER SYSTEM, ALTER SESSION, используемые для управления системой.
Сведения о существующих объектах базы данных можно получить из системного каталога.
Объекты называются так же объектами схемы базы данных, поскольку ассоциированы с учетными записями конкретных пользователей, то есть со схемой. Название схемы совпадает с именем учетной записи. Обычно объекты базы данных создаются в собственной схеме, однако, при наличии соответствующих прав доступа, создавать объекты в схемах других учетных записей или обращаться к объектам других схем. В этом случае необходимо указывать имя объекта, квалифицированное именем схемы, например: SYSTEM.OBJ, где SYSTEM – имя схемы, OBJ – имя объекта (таблицы).
Раздел 2. Создание таблиц
Общие сведения
Для создания таблицы используется команда CREATE TABLE. Эта команда является одной из наиболее сложных по количеству вариантов использования и существующих опций; в данном пособии приводятся наиболее распространенные.
При создании таблицы указывается:
· имя таблицы и схема, в которой таблица создается (опционально);
· определения полей таблицы;
· определения ограничений;
· дополнительные опции, например, описывающие физическое размещение таблицы, и т.д.
Определение поля таблицы включает:
· имя поля, которое должно быть уникальным в данной таблице и соответствовать общим правилам (см. Часть 1 Пособия);
· тип значения поля;
· значение по умолчанию (опционально);
· список ограничений, накладываемых на это поле.
Типы значений
Используемые в системе Oracle типы значений можно разделить на типы собственно этой системы, и типы, используемые для совместимости со стандартами ANSI (American National Standards Institute) языка SQL. Документация по Oracle традиционно рекомендует избегать использования типов ANSI, однако их использование может быть полезным при разработке переносимых на другие системы приложения. Кроме того, ANSI-типы являются более распространенными. Впрочем, эти рекомендации не являются принципиальными, и далее обе категории типов рассматриваются равноправно.
|
|
|
Как уже упоминалось в Части 1 Пособия, все типы можно разделить на числовые, символьные (строковые), типы для представления значений дат и времени, и специальные типы. Ниже в Таблице 1 приведены наиболее распространенные типы с примерами использования этих типов.
Таблица 1. Типы значений Oracle
| Численные типы | ||
| NUMBER | число в формате целого и вещественного с указанием общей длины значения в количестве значащих символов всего числа и дробной части | NUMBER NUMBER(10) NUMBER(10,2) длина до 22 байт |
| FLOAT | число в формате вещественного с указанием общей длины значения в количестве значащих символов | FLOAT FLOAT(10) длина до 22 байт |
| BINARY_FLOAT | вещественное число длиной 32 разряда | BINARY_FLOAT длина 4 байта (5) |
| BINARY_DOUBLE | вещественное число длиной 64 разряда | BINARY_DOUBLE длина 8 байт (9) |
| INTEGER INT SMALLINT | ANSY-типы для хранения целочисленных значений (хранятся как значения NUMBER) | длина 22 байта |
| REAL DECIMAL | ANSY-типы для хранения вещественных значений (хранятся как значения FLOAT) | |
| Символьные | ||
| CHAR CHARACTER | символьные данные фиксированной длины | CHAR CHAR(20) |
| NCHAR NCHARACTER | символьные данные фиксированной длины с использованием символом национальных алфавитов | |
| VARCHAR2 VARCHAR | символьные данные произвольной длины | |
| NVARCHAR2 | символьные данные произвольной длины с использованием символом национальных алфавитов | |
| Типы для представления значений дат и времени | ||
| DATE | значение даты | |
| TIMESTAMP | значение даты и времени | |
| Специальные типы | ||
| BLOB | бинарные данные очень большого размера | |
| CLOB NCLOB | текстовые данные очень большого размера | |
| ROWID | представляет адрес записи таблицы | |
Замечания.
Тип NUMBER(p,s) является основным типом для представления чисел. Необязательные значения p и s означают соответственно количество знаков всего и после десятичной запятой. Например, тип NUMBER(10,3) может использоваться для хранения вещественных чисел с семью знаками перед запятой и тремя после. Для хранения целых чисел можно использовать описание NUMBER(10, 0), в этом случае при присваивании полю вещественного значения дробная часть будет отбрасываться.
|
|
|
Для хранения вещественных чисел можно использовать тип FLOAT(p), который эквивалентен типу NUMBER(p).
Типы INTEGER, INT, SMALLINT, REAL, DECIMAL, используемые для совместимости со стандартами ANSI, представляются типом NUMBER; длина реально хранимого поля может существенно отличаться от ожидаемой. Например, длина поля типа SMALLINT будет иметь не 2 байта, как это бывает в языках программирования типа C/C++ или в системе Microsoft SQLServer, а 22 байта.
Рассмотрим пример создания иллюстративной таблицы, поля которой имеют различные виды численных типов.
CREATE TABLE TypeTest
(p1 NUMBER,
p2 NUMBER(5),
p3 NUMBER(5,3),
p4 NUMBER(5,0),
p5 FLOAT,
p6 FLOAT(5),
p7 INTEGER,
p8 BINARY_FLOAT,
p9 BINARY_DOUBLE);
Ниже представлена структура этой таблицы согласно данным системного каталога, полученные при помощи команды DESCR. Обратите внимание на длины и разрядность полей, а так же тот факт, что тип INTEGER из категории ANSI-типов представляется как тип NUMBER.
| Table | Column | Data Type | Length | Precision | Scale |
| TYPETEST | P1 | Number | - | - | - |
| P2 | Number | - | |||
| P3 | Number | - | |||
| P4 | Number | - | |||
| P5 | Float | - | |||
| P6 | Float | - | |||
| P7 | Number | - | - | ||
| P8 | Binary_Float | - | - | ||
| P9 | Binary_Double | - | - |
Добавив следующие две записи в эту таблицу, можно посмотреть на различия представления значений в зависимости от типа и значений разрадности и точности.
INSERT INTO TypeTest
VALUES (1, 1, 1, 1, 1, 1, 1, 1, 1)
INSERT INTO TypeTest
VALUES (1, 12345.67, 45.67, 12345.67, 12345.67, 12345.67, 12345.67, 12345.67, 12345.67)
SELECT p1 "n", p2 "n(5)", p3 "n(5,3)",
p4 "n(5,0)", p5 "float", p6 "float(5)",
p7 "int", p8 "BINARY_FLOAT"
FROM TypeTest
| n | n(5) | n(5,3) | n(5,0) | float | float(5) | int | BINARY_FLOAT |
| 1.0E+000 | |||||||
| 45.67 | 12345.67 | 1.23456699E+004 |
Тип CHAR предназначен для хранения последовательности символов, представленных однобайтовыми символами. Количество символов не должно превышать 2000; если количество символов опущено, длина по умолчанию – один символ. Тип NCHAR предназначен для хранения символов, использующих двухбайтовое представление. Длина NCHAR-поля не может превышать 2000 байт, что достаточно для хранения 1000 символов.
Следует иметь в виду, что все значения конкретного поля типа CHAR имеют фиксированную длину, одинаковую для всех строк таблицы вне зависимости от реальной длины хранимых строк. Для хранения строк переменой длины рекомендуется использовать типы VARCHAR2 (VARCHAR) и NVARCHAR2. Значения этих типов действительно представляются в базе данных в виде строк переменной длины. Максимальная длина строк – до 4000 байт.
|
|
|
Определение следующей таблицы иллюстрирует употребление символьных типов; ниже приведены определение таблицы и описание ее структуры, возвращаемое командой DESCR.
CREATE TABLE CharTable
(p1 CHAR,
p2 CHAR (10),
p3 NCHAR,
p4 CHAR(10),
p5 VARCHAR (1000))
| Table | Column | Data Type | Length | Precision | Scale |
| CHARTABLE | P1 | Char | - | - | |
| P2 | Char | - | - | ||
| P3 | Nchar | - | - | ||
| P4 | Nchar | - | - | ||
| P5 | Varchar2 | - | - |
“Длинные ” типы BLOB (Binary Long Object), CLOB (Character Large Object) и NCLOB используются для хранения в БД данных очень большого объема. Тип BLOB предназначен для хранения двоичной информации (например, растровых изображений), типы CLOB и NCLOB – длинного текста. Значения этих типов нельзя сохранять и извлекать из базы данных напрямую при помощи команд SQL, а так же использовать в выражениях, в частности, в операции сравнении, но можно удалять и проверять при помощи предиката IS NULL.
Наконец, рассмотрим тип ROWID, который предназначен для представления логического адреса записей таблиц. Это поле автоматически создается для каждой таблицы, и ее можно использовать в запросах, например:
SELECT ROWID FROM TypeTest
| ROWID |
| AAAE4vAAEAAAFIUAAA |
| AAAE4vAAEAAAFIUAAB |
Эти значения – адреса строк можно использовать для обращения к строкам, например:
SELECT * FROM TypeTest
WHERE ROWID = 'AAAE4vAAEAAAFIUAAA'
| P1 | P2 | P3 | P4 | P5 | P6 | P7 | P8 | P9 |
| 1.0E+000 | 1.0E+000 |
Впрочем, подобные запросы и в целом использование типа ROWID ограничены в употреблении.
Значение по умолчанию
При определении поля таблицы можно указать значение по умолчанию, используя ключевое слово DEFAULT и выражение для этого значения; в выражении можно использовать функции, но нельзя использовать другие поля этой записи и подзапросы. Отметим, что при отсутствии ключевого слова DEFAULT значением по умолчанию считается значение NULL. В командах INSERT, если значение поля добавляемой записи отсутствует, значение поля устанавливается в соответствии со значением вычисленного выражения; при необходимости в команде INSERT можно явно указать другое значение.
Поле, для которого определено значение по умолчанию, может быть в команде INSERT инициализировано любым другим значением.
CREATE TABLE DefaultTest
(name CHAR(20) DEFAULT 'Неизвестно',
datereg TIMESTAMP DEFAULT current_date,
num NUMBER(10) DEFAULT SIN(0.5))
INSERT INTO DefaultTest
VALUES ('Ana', DEFAULT, DEFAULT)
INSERT INTO DefaultTest (num) VALUES (2)
SELECT * FROM DefaultTest
| NAME | DATEREG | NUM |
| Ana | 01-JUL-09 10.43.30.000000 AM | |
| Неизвестно | 01-JUL-09 10.43.33.000000 AM |
Слово DEFAULT в INSERT и UPDATE-командах ссылается на значение поля, устанавливаемое по умолчанию, например:
UPDATE DefaultTest SET num = default
Ограничения
На поля таблицы могут быть наложены ограничения – специальные требования, или условия, которые ограничивают множество значений, которые могут быть присвоены полям таблицы. Эти условия обычно используются для того, чтобы запретить помещать в базу данных значения, которые являются логически неправильными или противоречивыми. Например, поле “Возраст”, объявленное как целочисленное, не может содержать отрицательные значения, или, например, значение менее 18, и т.д. В целом, ограничения отражают семантические свойства данных предметной области, и выявляются при ее анализе. Использовать ограничения не обязательно, более того, их определение в схеме базы данных потенциально снижает скорость работы системы, однако их использование эффективно предотвращает накапливание в БД неправильных данных и откровенного “мусора” и повышает качество и эксплуатационные свойства системы.
Проверка выполнения ограничения производится при выполнении SQL-сервером команд обновления данных (INSERT, UPDATE и DELETE), а так же при изменении структуры таблицы. Если ограничение при конкретном обновлении не выполняется, генерируется ошибка.
Объявление ограничения в команде CREATE TABLE может помещаться непосредственно после определения поля (эти ограничения так же называются ограничениями на уровне полей, или in-line-ограничениями), или отдельным описанием (в этом случае их называют ограничениями на уровне таблицы, или out-line-ограничениями). Для одного поля может быть определено несколько in-line-ограничений. Ограничение на уровне таблицы обычно используют для накладывания ограничения на несколько полей, а ограничение на уровне поля затрагивает, естественно, только одно это поле.
Полное определение ограничения имеет следующий синтаксис:
CONSTRAINT имя_ограничения описание_ограничения
Здесь имя_ограничения - имя, которое присваивается данному ограничению (должно быть уникальными среди всех использованных имен ограничений в данной схеме); явное присваивание имени ограничению полезно для дальнейших действий с этим ограничением. Для in-line-ограничений служебное слово CONSTRAINT и имя ограничения необязательны и могут быть пропущены, при этом имя для ограничения создается автоматически.
Ограничения в SQL Oracle бывают следующих видов:
1. Ограничение NOT NULL.
2. Ограничение первичного ключа PRIMARY KEY.
3. Ограничение доменной целостности CHECK.
4. Ограничение уникальности UNIQUE.
5. Ограничение референциальной целостности FOREIGN KEY.
Если на поле наложено ограничение NOT NULL, это означает, что в данном поле любой записи не может храниться значение NULL. Данное ограничение – единственное, которое может быть наложено на поля “длинных” типов BLOB и CLOB.
Это ограничение полезно, если в поле планируется хранить информацию, которая является обязательной и не может быть опущена или вставлена позднее.
Ограничение уникальности UNIQUE используется для того, чтобы в данном поле значения во всех записях были различными, или, иными словами, в таблице не должно существовать две записи с одинаковыми значениями этого поля. Это ограничение может быть наложено на одно поле или на группу полей; однако нельзя на одни и те же поля накладывать ограничение UNIQUE дважды.
Следует иметь в виду, что, если на группу полей наложено ограничение уникальности, из этого не следует, что каждой поле из набора само так же является уникальным. Например, если на пару полей “Имя” и “Фамилия” наложено это ограничение, конечно, сами по себе имена и фамилии, которые можно хранить в записях, могут повторяться. Кроме того, могут встречаться и пустые значения.
Частными случаем ограничения уникальности является ограничение первичного ключа PRIMARY KEY, которое требует дополнительно отсутствие пустых значений в группе полей. В дополнении к этому, ограничение PRIMARY KEY может встречаться только один раз среди ограничений на таблицу; впрочем, эквивалентная комбинация NOT NULL UNIQUE может быть наложена на любые другие поля.
Термин “первичный ключ” восходит к терминологии теории реляционных баз данных, согласно которой было принято разделять все возможные ключи на первичный и прочие, называемые вторичными, альтернативными и т.д. По существу, ключ таблицы – это группа полей с наложенным на них ограничением уникальности.
Ограничение доменной целостности (CHECK) позволяет контролировать присваиваемое значение данному полю при помощи логического выражения, которое проверяется при присваивании значения и, в случае если значение равно FALSE, ограничение считается нарушенным и возбуждается ошибка. В этом выражении можно использовать различные операции, содержащие присваиваемое значение поля и значения других полей данной записи, если ограничение объявлено как out-of-line. Подзапросы, однако, использовать нельзя, что означает, что при проверке нельзя анализировать данные из других строк этой же таблицы или других таблиц.
Наконец, последним из рассматриваемых ограничений является ограничение внешнего ключа, или ограничение референциальной целостности. Данное ограничение требует, чтобы в качестве значений поля или группы полей можно было использовать только значения полей какой-либо таблицы. Таблицу, на которую ссылается ограничение, называют часто master-таблицей, или родительской, а таблицу, в которой это ограничение определено – slave-таблицей, или подчиненной. Описание ограничений FOREIGN KEY, соответственно, содержит имя родительской таблицы и список ее полей, причем в родительской таблице на эту группу полей должно существовать наложенное ограничение UNIQUE или PRIMARY KEY.
Наконец, пре определении ограничения референциальной целостности можно определить поведение системы при удалении или изменении полей, на которые ссылается вешний ключ. Действительно, что должно происходить, если удалить запись в master-таблице, на которую ссылаются записи в подчиненной таблице? Данный вопрос регламентируется следующим образом. По умолчанию изменения полей референциального ключа и удаление содержащей его записи запрещено; однако существует две дополнительных опции – SET NULL для принудительного присваивания полям внешнего ключа значений NULL при удалении “родительской” записи, и опция CASCADE для удаления “подчиненных” записей. Перенос изменений от “родительских” записей к “подчиненным” называется каскадным обновлением.
Теперь рассмотрим пример определения схемы некоторой учебной базы данных, иллюстрирующей практическое применение ограничений. Используемая схема типична для многочисленных приложений баз данных, например, для Internet-магазинов. Этот пример так же иллюстрирует проектирование баз данных, используемая здесь методология рекомендуется для применения в аналогичных сферах применения.
База данных состоит из четырех таблиц: таблица "Клиент" содержит данные о зарегистрированных клиентах, таблица "Товар" – описание и характеристики продаваемого товара, Заказ" – оформленные заказы пользователей, и, наконец, "СоставЗак" – единицы товаров и их количество в конкретном заказе.
???Вставить маленький кусок про ER-диаграммы и диаграмму для этой схемы???
1 CREATE TABLE "Клиент"
2 (
3 "Имя" VARCHAR(50) NOT NULL,
4 "Фамилия" VARCHAR(50) NOT NULL,
5 CONSTRAINT "Клиент_Ф_С"
6 UNIQUE ("Имя", "Фамилия"),
7 "Пол" NCHAR
8 CHECK ("Пол" IN ('М','Ж','м','ж')),
9 "Дом Адрес" VARCHAR(100),
10 "Дата Рождения" DATE,
11 "Дом Телефон" CHAR(20),
12 "Моб Телефон" CHAR(20),
13 CONSTRAINT "Клиент_ТТ_С"
14 CHECK ("Дом Телефон" IS NOT NULL
15 OR "Моб Телефон" IS NOT NULL),
16 "Код Клиента" CHAR(20) NOT NULL UNIQUE
17)
18 CREATE TABLE "Товар"
19 (
20 "Код товара" CHAR(20) NOT NULL UNIQUE,
21 "Название" VARCHAR(50) NOT NULL,
22 "Полное название" VARCHAR(200) NOT NULL,
23 "Цена" NUMBER NOT NULL
24 CHECK ("Цена" > 0),
25 "Мера изм" VARCHAR(20)
26 CHECK ("Мера изм"
27 IN ('шт', 'кг', 'литр')),
28 "Наличие" INT CHECK ("Наличие" > 0),
29 "Описание" CLOB,
30 "Фото" BLOB
31)
32 CREATE TABLE "Заказ"
33 (
34 "Код заказа" CHAR(40) PRIMARY KEY,
35 "Дата регистр" DATE DEFAULT current_date
36 NOT NULL,
37 "Клиент" CHAR(20) NOT NULL
38 REFERENCES "Клиент" ("Код Клиента"),
39 CONSTRAINT "Заказ_ДК_U"
40 UNIQUE ("Дата регистр", "Клиент"),
41 "Дата выполн" DATE
42 DEFAULT current_date + 3 NOT NULL,
43 CONSTRAINT "Заказ_Д_C"
44 CHECK("Дата выполн" > "Дата регистр")
45)
46 CREATE TABLE "СоставЗак"
47 (
48 "Код заказа" CHAR(40) NOT NULL,
49 CONSTRAINT "СоставЗак_К_F" FOREIGN KEY
50 ("Код заказа") REFERENCES "Заказ",
51 "Код товара" CHAR(20) NOT NULL,
52 CONSTRAINT "СоставЗак_КК_U"
53 UNIQUE ("Код заказа", "Код товара"),
54 "Количество" INT CHECK ("Количество">0),
55 CONSTRAINT "ТоварВЗаказе_К_C"
56 FOREIGN KEY ("Код товара")
57 REFERENCES "Товар"("Код товара")
58)
Комментарии.
Строки 3 и 4: наложены два In-line-ограничений NOT NULL, запрещающие создавать клиентов без имени и фамилии.
Строки 5 и 6: out-of-line-ограничение уникальности, которое запрещает повторно создавать клиентов с теми же именами и фамилиями. Определение ограничения содержит явно задаваемое имя ограничения, которое рекомендуется создавать по каким-либо понятным правилам.
Строка 8: ограничение доменной целостности, которое требует, чтобы в данном поле можно было хранить только эти значения пола клиента. Поверка реализована при помощи предиката IN.
Строки 9-12: никакие ограничения не наложены, поскольку эти данные о клиенте необязательны и могут отсутствовать, и их уникальность так же не предполагается.
Строки 13-15. Здесь CHECK-ограничение используется для контроля, что бы хоть один из телефонов был известен (одно из полей не должно хранить пустое значение).
Строка 16. На поле "Код Клиента" наложено одновременно ограничение уникальности и NOT NULL. Предполагается, что в этом поле хранится некоторый системный ключ (такие значения называются суррогатными), однако это поле не объявлено как первичный ключ, чтобы не привлекать к нему излишнего внимания.
Строка 24. Цена не может быть отрицательной.
Строки 26 и 27. Ограничение определяет значения - допустимые единицы измерения товара.
Строки 29, 30. Эти поля предполагается использовать для хранения развернутого описания товара и картинки с его изображением; эти данные не является в данном случае обязательным. Все прочие поля этой таблицы должны быть заполнены.
Строка 34. Наложено ограничение первичного ключа.
Строка 35. Здесь в качестве значения по умолчанию используется текущее время, возвращаемое системной функцией CURRENT_DATE, что удобно для практических целей.
Строка 38. Пример использования ограничения внешнего ключа, в определении которого указывается имя таблицы и список ее полей. С содержательной точки зрения это ограничение запрещает создавать заказы для незарегистрированных пользователей.
Строки 39 и 40. Данное ограничение имеет важный с прикладной точки зрения и не вполне очевидный (с точки зрения анализа SQL-кода) смысл, - оно запрещает создавать для каждого клиента более одного заказа с одинаковыми значениями дат (то есть одновременно). Читателю в качестве упражнения рекомендуется изменить выражение для этого ограничения, чтобы пользователю было запрещено создавать повторные заказы в течение одного дня.
Строки 41, 42. Предполагается, что заказ будет выполнен в течение трех дней, но, в любом случае, дата завершения не может быть ранее даты оформления заказа.
Строки 49, 50. Out-of-line-ограничение внешнего ключа, в котором необходимо после ключевого слова FOREIGN KEY указать список полей, к котором это ограничение относится, а затем, после слова REFERENCES, имя родительской таблицы и список ее полей. В этом примере, однако, список полей в родительской таблице опущен, поскольку по умолчанию используется ее первичный ключ (он был явно определен).
Сроки 42, 53. Это ограничение, как читатель уже догадался, запрещает помещать в заказ один и тот же товар более одного раза (следует указывать количество единиц товара в заказе).
Из примера видно, что основное назначение ограничений внешнего ключа – запрещение хранить “бесхозные” записи подчиненных таблиц. Без ограничения в строках 41-42 вполне возможна ситуация, когда некая запись о заказе из одноименной таблицы была бы удалена, а связанные с ней записи в таблице "СоставЗак" остались бы как следствие человеческой ошибки. Это привело бы к известным проблемам при, например, формировании отчетности на основании записей из таблицы "СоставЗак".
Другой любопытной тонкостью является использование ограничения CHECK на строке 23. Как уже говорилось, оно определяет список допустимых единиц измерения. Альтернативными способом реализации данного требования бизнес-логики является создание отдельной таблицы, в каждой строке которое хранится одна из единиц; такие таблицы исторически получили название справочников. Этот способ является более громоздким, но потенциально более гибким, поскольку упрощает, в данном примере, добавление при необходимости новых единиц, и более приемлемым, если количество значений большое. В целом, применение тех или других средств зависит от конкретных целей и ограничений при проектирования БД.
Создание таблиц при помощи команды SELECT
Таблицы, создание которых рассматривалось выше, после исполнения соответствующих команд CREATE TABLE не содержат, естественно, строк с данными. Другим вариантом использования этой команды является создание таблиц из данных, взятых из других таблиц. Для этого необходимо при определении таблицы указать SELECT-запрос, который будет вычислен, и его значение – множество строк – образует строки новой таблицы. При этом структура новой таблицы – имена полей и их типы – будет создана в соответствии со структурой выборки запроса. Определения полей в таких командах CREATE TABLE AS SELECT, могут быть опущены, однако, если эти определения присутствуют, они должны соответствовать структуре фактической выборки SELECT-запроса. Ограничения на данные исходных таблиц в новую таблицу не переносятся.
Ниже приведен пример создания таблицы при помощи запроса:
CREATE TABLE "Все заказы(таб)" AS
SELECT "Имя", "Фамилия", "Клиент",
"Дата регистр", "Название", "Количество", "Мера изм", "Дата выполн"
FROM "Клиент", "Товар", "Заказ", "СоставЗак"
WHERE "Клиент"."Код Клиента" = "Заказ"."Клиент"
AND "Заказ"."Код заказа"="СоставЗак"."Код заказа"
AND "Товар"."Код товара"="СоставЗак"."Код товара"
Следует иметь в виду, что данные таблицы "Все заказы(таб)" после ее создания никакой связи с данными исходных таблиц не поддерживают, и могут быть изменены любым способом.
Можно так же создавать временные таблицы (CREATE TEMPORARY TABLE), которые, как следует из названия, не хранятся в базе данных постоянно, а существует ограниченное время. Структура и данные временных таблиц так же формируются как значение запроса, извлекающего данные из других таблиц. Ниже приведен пример создания временной таблицы:
CREATE GLOBAL TEMPORARY TABLE "Все заказы(вр)"
ON COMMIT PRESERVE ROWS
AS SELECT "Имя", "Фамилия", "Клиент",
"Дата регистр", "Название", "Количество", "Мера изм", "Дата выполн"
FROM "Клиент", "Товар", "Заказ", "СоставЗак"
WHERE "Клиент"."Код Клиента"="Заказ"."Клиент"
AND "Заказ"."Код заказа"="СоставЗак"."Код заказа"
AND "Товар"."Код товара"="СоставЗак"."Код товара"
Эта таблица по структуре и данным совершенно аналогично рассмотренной выше «постоянной» таблице "Все заказы(таб)", однако эта таблица будет существовать до окончания текущей сессии. Дальнейшие подробности работы с временными таблицами можно найти в системной документации.
|
|
|
