Объекты базы данных. Язык определения данных

Объекты базы данных. Язык определения данных

Язык определения данных Определения таблиц Создание таблиц

Схема

Схема – это набор логических структур данных (объектов схемы) конкретного пользователя. Схема является собственностью пользователя базы данных и имеет то же самое имя, что и пользователь базы данных. Каждый пользователь имеет свою собственную схему. Объекты схемы создаются и управляются с помощью языка определения данных SQL. Объектами схемы являются, например, следующие логические структуры:

§ Таблицы

§ Последовательности

§ Представления

§ Синонимы

§ Индексы

§ Хранимые процедуры и т.д.

Когда пользователь создает объекты в базе данных, он создает их в определенной схеме (по умолчанию в своей). Схема содержит набор объектов конкретного пользователя, причем объекты другого пользователя в ней не содержатся. Если пользователь обращается к объектам собственной схемы, ему достаточно указать имя объекта. Однако при обращении к объектам другого пользователя этого недостаточно. Чтобы обращаться к объектам, содержащимся в другой схеме, необходимо

§ Указать имя схемы перед именем объекта, который в ней находится

§ Иметь привилегию на этот объект. Привилегию должен дать владелец объекта

Рассмотрим пример. Пользователь instructor создал таблицу sample и дал пользователю student привилегию на чтение этой таблицы. Пользователь student должен указать имя схемы при обращении к таблице sample, т.к. таблица sample находится в схеме пользователя instructor.

SQL> CONNECT student/tbd@student

Connected.

SQL> SELECT * FROM sample;

SELECT * FROM sample

*

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> SELECT * FROM instructor.sample;

FIRST NAME

--------- --------------------

1 One

2 Two

Язык определения данных

В общем случае операторы DDL обеспечивают высокий уровень доступа к данным, не затрагивая аспекты физического хранения данных, т.е. они оперируют… Ядро языка определения данных образуют три глагола SQL: · CREATE (создать) – позволяет определить и создать объект базы данных;

Определения таблиц

§ CREATE TABLE – используется для создания таблиц § ALTER TABLE – позволяет изменить структуру существующей таблицы § DROP TABLE – удаляет таблицу из базы данных

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

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

CREATE [GLOBAL TEMPORARY] TABLE [схема.]имя_таблицы

(имя_столбца1 тип_данных [DEFAULT выражение] [ограничение_столбца],

имя_столбца2 тип_данных [DEFAULT выражение] [ограничение_столбца],

...

[ограничение_таблицы])

[ON COMMIT {DELETE ROWS | PRESERVE ROWS}];

GLOBAL TEMPORARY – определяет таблицу, как временную

схема – имя пользователя - владельца таблицы. Если схема опущена, таблица создается в собственной схеме пользователя

имя_таблицы – имя создаваемой таблицы

имя_столбца – имя столбца создаваемой таблицы

тип_данных – тип данных и длина столбца

DEFAULT выражение – значение по умолчанию

ограничение_столбца – правило целостности на уровне столбца

ограничение_таблицы – правило целостности на уровне таблицы

ON COMMIT – используется только с временными таблицами и определяет действия, которые происходят при завершении транзакции

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

Временные таблицы

Предложение ON COMMIT определяет время жизни данных во временной таблице: · DELETE ROWS – очищает временную таблицу (удаляет все данные сеанса) после… · PRESERVE ROWS – оставляет данные для выполнения следующей транзакции и очищает таблицу только после завершения…

Создание таблицы из другой таблицы

CREATE TABLE [схема.]таблица [(определение_столбцов)] AS подзапрос; определение_столбцов – определяет имена, значения по умолчанию и правила… подзапрос – возвращает строки, которые должны быть добавлены в создаваемую таблицу.

Изменение структуры таблицы

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

ALTER TABLE [схема.]имя_таблицы

{[ADD (определение_столбца_или_ограничения)]] |

[MODIFY (определение_изменяемого столбца)]] |

[DROP определение_удаляемых_столбцов] |

[DROP определение_удаляемого ограничения] |

[{ENABLE | DISABLE} CONSTRAINT имя_ограничения [CASCADE]]}

Команда ALTER TABLE позволяет:

· Добавить в определение таблицы столбец или ограничение целостности

· Изменить определение существующего столбца

· Удалить один или несколько столбцов из определения таблицы

· Удалить ограничение целостности

· Кроме того, Oracle SQL позволяет разрешить или запретить проверку ограничения целостности, хотя эта операция не стандартизована

Команда ALTER TABLE так же, как и CREATE TABLE, является командой языка определения данных (DDL).

Добавление столбца или ограничения

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

определение_столбца_или_ограничения =

(имя_столбца1 тип_данных [DEFAULT выражение] [ограничение_столбца],

имя_столбца2 тип_данных [DEFAULT выражение] [ограничение_столбца],

...

[ограничение_таблицы])

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

SQL> ALTER TABLE project ADD (comments VARCHAR2(250));

Table altered.

SQL> DESC project

Name Null? Type

------------------- -------- -----------------

ID NOT NULL NUMBER(7)

NAME NOT NULL VARCHAR2(20)

COST NUMBER(10)

COMMENTS VARCHAR2(250)

SQL> DESC t1

Name Null? Type

-------------- -------- -----------------

FIRST NUMBER

SECOND NUMBER

SQL> DESC t2

Name Null? Type

-------------- -------- -----------------

FIRST NUMBER

NAME VARCHAR2(20)

SQL> SELECT constraint_name, constraint_type FROM user_constraints

2 WHERE table_name IN ('T1', 'T2');

no rows selected

SQL> ALTER TABLE t1 ADD CONSTRAINT t1_pk PRIMARY KEY (first);

Table altered.

SQL> ALTER TABLE t1 ADD CONSTRAINT t1_second_ck CHECK (second>1000);

Table altered.

SQL> ALTER TABLE t2 ADD CONSTRAINT t2_first_fk FOREIGN KEY (first)

2 REFERENCES t1(first);

Table altered.

SQL> SELECT constraint_name, constraint_type FROM user_constraints

2 WHERE table_name IN ('T1', 'T2');

CONSTRAINT_NAME C

------------------------------ -

T1_PK P

T1_SECOND_CK C

T2_FIRST_FK R

Изменение столбца

определение_изменяемого_столбца = (имя_столбца1 [тип_данных] [DEFAULT выражение], имя_столбца2 [тип_данных] [DEFAULT выражение],

Удаление столбца

определение_удаляемых_столбцов = {COLUMN имя_колонки | (список_столбцов)} [CASCADE CONSTRAINS] При удалении колонки удаляются все данные столбца, все индексы, построенные для этого столбца и все ограничения на…

Удаление ограничения

определение_удаляемого_ограничения = {PRIMARY KEY | UNIQUE (список_столбцов) | CONSTRAINT имя_ограничения}… PRIMARY KEY – удаляет ограничение первичного ключа

Разрешение и запрет ограничений

ALTER TABLE [схема.]имя_таблицы {ENABLE | DISABLE} CONSTRAINT имя_ограничения [CASCADE] При разрешении ограничений UNIQUE и PRIMARY KEY автоматически создаются… SQL> SELECT constraint_name, constraint_type, status

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

DROP TABLE [схема.]таблица [CASCADE CONSTRAINTS]; § Из таблицы удаляются все данные § Удаляются все индексы и представления, связанные с таблицей

SQL> SELECT constraint_name, constraint_type, table_name, r_constraint_name

2 FROM user_constraints WHERE table_name IN ('T1', 'T2');

CONSTRAINT_NAME C TABLE_NAME R_CONSTRAINT_NAME

-------------------- - --------------- --------------------

T1_SECOND_CK C T1

T1_PK P T1

T2_FIRST_FK R T2 T1_PK

SQL> DROP TABLE t1;

DROP TABLE t1

*

ERROR at line 1:

ORA-02449: unique/primary keys in table referenced by foreign keys

SQL> DROP TABLE t1 CASCADE CONSTRAINTS;

Table dropped.

SQL> DESC t1

ERROR:

ORA-04043: object t1 does not exist

SQL> DESC t2

Name Null? Type

----------------------------------------- -------- ------------------------

FIRST NUMBER

NAME VARCHAR2(20)

SQL> SELECT constraint_name, constraint_type, table_name, r_constraint_name

2 FROM user_constraints WHERE table_name IN ('T1', 'T2');

No rows selected

Дополнительные команды Oracle SQL

Переименование объектов базы данных

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

RENAME старое_имя TO новое_имя;

Усечение таблицы

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

TRUNCATE TABLE таблица;

Команду TRUNCATE можно применить и к временной таблице. В этом случае будут усечены все строки временной таблицы, вставленные данным сеансом.

Словарь данных

§ USER_ - объекты, принадлежащие пользователю, т.е. те, которые пользователь создал сам § ALL_ - объекты, к которым пользователь имеет доступ, т.е. собственные… § DBA_ - все объекты базы данных (только для пользователей с привилегией DBA)

Просмотр объектов пользователя

SQL> SELECT object_name, object_type FROM user_objects; OBJECT_NAME OBJECT_TYPE ------------------------------ -------------------------

Просмотр ограничений

SQL> SELECT constraint_name, constraint_type, 2 search_condition, r_constraint_name 3 FROM user_constraints;

Просмотр столбцов, на которые распространяются ограничения

SQL> SELECT constraint_name, column_name 2 FROM user_cons_columns; CONSTRAINT_NAME COLUMN_NAME

Литература

1. Вильям Дж. Пэйдж Использование Oracle8/8i: пер. с англ. – М.: Издательский дом "Вильямс", 1999.

2. Мартин Грабер. Справочное руководство по SQL: пер. с англ. – М.: Издательство «Лори», 1997.

3. Джудит С.Боуман и др. Практическое руководство по SQL/ 3-е издание: пер. с англ. – К.: Диалектика, 1997.