Создание простой таблицы

Таблицы могут храниться в базе данных несколькими способами. Простейший из них это heap таблица. Таблица может хранить строки различной длины в случайном порядке. Может быть какая-либо корреляция между порядком записи и порядком в котором данные хранятся но на это нельзя рассчитывать. Более сложные структуры таблицы, такие как представленные ниже, могут влиять на порядок и группировку строк или использовать случайное распределение

Использование сложных структур таблицы не вляет на SQL. Любой SQL запрос выполненные к таблицам созданным с этими опциями вернёт абсолютно одинаковый результат такой же как будто таблица являются обычными heap таблицами. Но если программист понимает как они работаеют то можно существенно увеличить производительность.

Создание таблицы с указанием столбцов

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

 

CREATE TABLE [schema.]tablename [ORGANIZATION HEAP]

(columnname datatype [DEFAULT expression]

[,columnname datatype [DEFAULT expression]);

 

Как минимум необходимо указать имя таблицы (по умолчанию таблицы создаются в схеме текущего аккаунта, если вы не указываете другую) и один столбец с типом данных. Практически никто не указывает явно ORGANIZATION HEAP, так как это значение по умолчанию и стандарт SQL. Ключевое слово DEFAULT в определении столбца позволяет указать выражение которое будет использоваться при выполнении команды INSERT если не указано значение для этого столбца.

Рассмотрим пример

 

CREATE TABLE SCOTT.EMP

(EMPNO NUMBER(4),

ENAME VARCHAR2(10),

HIREDATE DATE DEFAULT TRUNC(SYSDATE),

SAL NUMBER(7,2),

COMM NUMBER(7,2) DEFAULT 0.03);

 

Создаем таблицу EMP в схеме SCOTT. Этот запрос выполнится если подключен аккаунт SCOTT (и тогда указание схемы необязательно) или другой пользователь у которого есть доступ на создание таблиц в схеме SCOTT. Столбцы

EMPNO число длиной 4 цифры без дробной части. Если будет указана дробная часть при выполнении команды INSERT значение будет округлено к ближайшему целому.

ENAME строка с максимальной длиной в 10 символов

HIREDATE возможно использовать значение любой даты с указанием времени, если время не указано используется значение полночь

SAL предназначен для хранения зарплаты, принимает число длиной 7 цифр. Цифры после семи будут обрезаны а значение округлено

COMM имеет значение по умолчанию 0.03, которое будет использоваться если явно не указанть значение при выполнении команды INSERT

После создания таблицы можно записывать данные и выполнять команду SELECT

Обратите внимание что столбцы не указанные при команде INSERT использовали значения указанные в DEFAULT. Если бы не были указаны значения по умолчанию в секции DEFAULT использовалось бы значение NULL. Также обратите внимание что значение SAL было округлено.

 

TIP

The DEFAULT clause can be useful, but it is of limited functionality. You cannot use a subquery to generate the default value: you can only specify literal values or functions.

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

Вместо создания таблиц с нуля и затем вставки строк (как мы только что рассмотрели) таблицы можно создавать используя другие таблицы – с помозью подзапросов. Этот метод позволяет определить таблицу и записать данные используя один запрос. Любой запрос может быть использован как источник для описания столбцов и как набор данных (строк). Синтаксис команды

 

CREATE TABLE [schema.]tablename AS subquery;

 

Любой запрос возвращает двумерный набор строк; результат хранится как новая таблица. Просто пример создания таблицы используя подзапрос

 

create table employees_copy as select * from employees;

 

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

Рассмотрим более сложный пример

 

create table emp_dept as select

last_name ename,department_name dname,round(sysdate — hire_date) service

from employees natural join departments order by dname,ename;

 

Строки в новой таблице это результат объекдинения двух таблиц-источников, и два столбца изменяют название. Новый столбец SERVICE будет заполнен результатом арифметической операции и получено количество дней с момента приёма на работу. Строки буду вставлены в определённом порядке. Этот порядок не будет управляться последующими DML командами, но предполагая что в схеме HR данные по умолчанию новая таблица будет выглядеть следующим образом

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

 

create table no_emps as select * from employees where 1=2;

Изменение таблицы после создания

Доступно много изменений которые возможны после создания таблицы. Изменения которые затрагивают физическую структуру ложатся на плечи администратора БД, но большинство изменения являются логическими и могут быть применены SQL разработчиками. Раасмотрим примеры (большинтсво из них говорят сами за себя)

 

• Добавление столбца
  alter table emp add (job_id number);

 

• Изменение столбца
  alter table emp modify (commission_pct number(4,2) default 0.05);

 

• Удаление столбца
  alter table emp drop column commission_pct;

 

• Маркировка столбца как неиспользуемого
  alter table emp set unused column job_id;

 

• Переименование столбца
  alter table emp rename column hire_date to recruited;

 

• Включение режима только-чтения для таблицы
  alter table emp read only;

Все изменения этих DDL команд содержат встроенный COMMIT. Эти изменения нельзя отменить и они не смогут отработать если имеется активная транзакция к таблице. Также эти операции происходят практически мгновенно за исключением удаления столбца. Удаление столбца может затребовать длительное время так как поскольку удаляется столбец, каждая строка должна быть изменена. Команда SET UNUSED делает столбец недоступным для SQL команд, и часто является более лучшей альтернативой в сочетании с командой

ALTER TABLE tablename DROP UNUSED COLUMNS;

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

После перевода таблицы в режим только-чтения все DML команды не выполнятся. Но таблица может быть удалена, так как удаление всего лишь удалит строки из словаря данных.

Удаление и сокращение таблицы (drop и truncate)

Команда TRUNCATE TABLE удаляет вссе строки из таблицы и оставляет определение таблицы. Команда DROP TABLE удаляет также и определение таблицы (саму таблицу вместе с данными). Синтаксис команды

 

DROP TABLE [schema.]tablename ;

 

Если схема не указана удалится таблица с этим именем в схеме текущего аккаунта.

SQL не выдает предупреждений перед выполнением команды DROP и как и любая команда DDL содержит встроенный COMMIT. То есть удаление таблицы нельзя отменить. При выполнении определенных условий, удаление может быть отменено используя определенные методики.

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