Create Table

Наряду с описанием оператора создания таблицы "CREATE TABLE" на странице представлена следующая информация :

• Первичный ключ - PRIMARY KEY
• Удаление таблицы - DROP TABLE
• Добавление данных - INSERT
• Изменение данных - UPDATE
• Удаление данных - DELETE
• Очистка таблицы - TRUNCATE TABLE
• Временные таблицы - TEMPORARY TABLE

Создание таблицы "Create Table" относится к области создания объектов DDL (Data Definition Language - язык определения данных).

Синтаксис create table sql

create table [schema_name.]table_name (
  {columns_definitions}
)
[опции_таблицы];

- schema_name
Определение схемы (базы данных), в которой создается таблица. При создании таблицы нужны быть либо владельцем (owner) схемы (базы данных), либо иметь соответствующие права на создание/обновление/удаление таблицы.

- table_name
Определение наименования таблицы. Правила для допустимых имен таблицы приведены в документации к каждой СУБД. Чаще всего это касается размера наименования и используемых символов. Если указанная таблица уже существует, то возникает ошибка выполнения команды.

columns_definitions
В операторе create table прежде всего определяются столбцы таблицы - наименование и тип данных. При описании столбцов может быть наложено ограничение на значения, определено значение по умолчанию.

- опции_таблицы
Дополнительные параметры таблицы. В СУБД MySQL можно дополнительно в опциях таблицы определить порядок сортировки и кодировку таблицы. В СУБД PostgreSQL можно указать необходимость использования серверного идентификатора OIDS.

Пример create table

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

create table beeline.users (
  id        int NOT NULL,
  name      varchar(255) NOT NULL,
  sex       char(1) NOT NULL,
  address   varchar(255) NULL,
  city      varchar(255) NULL,
  birthday  date NULL
);

Особенности postgres create table

В операторе create table СУБД PostgreSQL можно использовать в наименованиях символы как в верхнем, так и в нижнем регистре. В этом случае наименование следует заключать в двойные кавычки. Наименование схемы также следует указывать в двойных кавычках.

-- создание таблицы в СУБД PostgreSQL
create table "beeline".users (
  "Id"      int NOT NULL,
  "Name"    varchar(255) NOT NULL,
  ...
)
WITH (
  OIDS=FALSE
);

В примере в качестве опции таблицы определяется необходимость использования серверного идентификатора OIDS.

Особенности mysql create table

В операторе create table СУБД MySQL можно дополнительно указать тип таблиц ENGINE, кодировку CHARSET, сортировку COLLATE, комментарий к таблице COMMENT.

-- создание таблицы в СУБД MySQL
create table beeline.users (
  idi     int NOT NULL,
  name    varchar(255) NOT NULL,
  ...
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8
COLLATE=utf8_bin
COMMENT = "Таблица пользователей";

Наибольшее распространение в MySQL получили следующие типы ENGINE :

• InnoDB - таблицы с поддержкой транзакций и блокировкой строк;
• MyISAM - таблицы, обеспечивающие переносимость в бинарном виде;
• HEAP - таблица с хранением данных в памяти.

Первичный ключ, PRIMARY KEY

Первичный ключ - это ограничение, позволяющее однозначно идентифицировать каждую запись в таблице SQL. Если PRIMARY KEY включает одно поле таблицы, то его называют простым ключом. В случае включения в PRIMARY KEY нескольких полей, его называют составным ключом. Поля таблицы, используемые в первичных ключах, не могут позволять нулевые значения и должны быть обьявлены NOT NULL.

PRIMARY KEY MySQL, PostgreSQL

Первичный ключ в СУБД MySQL не имеет наименования. Пример создания таблицы с PRIMARY KEY:

-- создание таблицы в СУБД MySQL, PostgreSQL
create table beeline.users (
  id        int NOT NULL,
  name      varchar(255) NOT NULL,
  sex       char(1) NOT NULL,
  address   varchar(255) NULL,
  city      varchar(255) NULL,
  birthday  date NULL,
  PRIMARY KEY (city, id)
);

При создании таблицы СУБД PostgreSQL автоматически сгенерирует наименование первичного ключа.

Простой первичный ключ - Oracle, MS SQL, PostgreSQL

Синтаксис CREATE TABLE с простым PRIMARY KEY допускает определение первичного ключа вместе с описанием поля таблицы. Пример создания таблицы с простым PRIMARY KEY:

-- создание таблицы с простым PRIMARY KEY в СУБД Oracle, MS SQL, PostgreSQL.
create table beeline.users (
  id        int [ NOT NULL ] PRIMARY KEY,
  name      varchar(255) NOT NULL,
  ...
);

При создании таблицы СУБД автоматически сгенерирует наименование первичного ключа.

Составной первичный ключ - Oracle, MS SQL, PostgreSQL

Синтаксис CREATE TABLE с составным PRIMARY KEY обуславливает необходимость использования оператора CONSTRAINT. Пример создания таблицы с составным PRIMARY KEY:

-- создание таблицы с простым PRIMARY KEY в СУБД Oracle, MS SQL, PostgreSQL.
create table beeline.users (
  id        int NOT NULL,
  name      varchar(255) NOT NULL,
  ... ,
  CONSTRAINT pkUserName PRIMARY KEY (name, id)
);

CONSTRAINT описания первичного ключа включает наименование PRIMARY KEY и список полей. При описании первичного ключа с помощью CONSTRAINT можно использовать одно или несколько полей.

Удаление таблицы, DROP TABLE

Удаление таблиц осуществляется с помощью оператора DROP TABLE. Нельзя удалять таблицу, на которую ссылаются внешние ключи; сначала следует удалить ссылающееся ограничение FOREIGN KEY или ссылающуюся таблицу.

Синтаксис удаления таблицы имеет следующий вид :

DROP TABLE [IF EXISTS] [schema_name.]table_name [ CASCADE | RESTRICT ]

- IF EXISTS
При попытке удаления несущестующей таблицы ошибки не возникнет исключение. Используется в MySQL, PostgreSQL.

- CASCADE
Автоматически удалять объекты, зависящие от данной таблицы (например представление View). Используется в Oracle, PostgreSQL.

- RESTRICT
Не удалять таблицу, если имеются зависящие от данной таблицы объекты. Используется в PostgreSQL.

Особенности DROP TABLE СУБД MS SQL

СУБД MS SQL позволяет одним оператором drop table удалять сразу несколько таблиц, разделенных символом запятой. CASCADE и RESTRICT в синтаксисе не предусмотрены.

Особенности DROP TABLE СУБД Oracle

СУБД Oracle при выполнении drop table не сразу освобождает пространство, занимаемое таблицей. Сервер БД переименовывает таблицу и помещает ее в корзину, где таблица позже может быть восстановлена с помощью инструкции FLASHBACK TABLE.

Если требуется немедленно освободить занимаемое таблицей пространство, то в инструкцию drop table необходимо включить ключевое слово "PURGE". Откат назад инструкции "drop table" с предложением "PURGE" невозможен, и таблицу невозможно будет восстановить.

Для безвозратного удаления таблицы в можно использовать следующий синтаксис drop table oracle :

DROP TABLE [schema_name.]table_name [ PURGE ]

Добавление и изменение данных - INSERT, UPDATE

Данные в таблицу вводятся и обновляются с помощью DML команд insert и update.

Добавление записи в таблицу, INSERT INTO

Оператор insert into позволяет добавить строки в таблицу. Значения можно вставлять либо перечислением с использованием оператора insert into values, либо c использованием оператора insert into select.

Примеры использования "insert into ... values" и "insert into ... select".

-- добавление записи с использованием перечислений значений
insert into [schema_name.]table_name ([<Имя столбца>, ... ]) 
    values (<Значение>,...)
-- добавление записи с использованием оператора select
insert into [schema_name.]table_name
    select <имя столбца1>,...
        from <название таблицы>
           [where <condition>]

Оператор insert into select может включать ключевое слово where с условиями для фильтрации данных. Следует отметить, что СУБД не обращает внимания на названия колонок, которые содержатся в операторе "insert into select"; для нее важно только порядок типов значений.

Копирование данных из одной таблицы в другую, SELECT ... INTO

Иногда при работе с БД возникает необходимость в создании копий одной или нескольких таблиц. Чтобы сделать полную копию таблицы предусмотрен отдельный оператор select into.

Пример использования оператора select into.

select * into [schema_name.]table_name_new from [schema_name.]table_name_old

В отличие от конструкции insert into select, когда данные добавляются в существующую таблицу, конструкция select into ... from ... копирует данные в новую таблицу. Можно сказать, что первая конструкция импортирует данные, а вторая - экспортирует. При использовании конструкции "select into ... from ..." необходимо учитывать следующее :

• в операторе select можно использовать любые предложения, такие как GROUP BY и HAVING;
• для добавления данных из нескольких таблиц можно использовать объединение;
• данные можно добавить только в одну таблицу.

Обновление данных, UPDATE TABLE

Синтаксис оператора обновления данных таблицы update table имеет следующий вид :

update table_name
  set column1 = value1, column2 = value2,...
    [where some_column1 = some_value1 [and | or]
           some_column2 = some_value2 ...];

table_name - наименование таблицы. При необходимости в наименовании таблицы можно указать и наименование схемы БД : [schema_name.]table_name .

Одним SQL оператором update table set можно обновить одну или несколько строк таблицы. Для обновления нескольких полей записи необходимо после ключевого слова "set" указать поля и их новые значения в формате "ключ=значение", разделенных запятой. В качестве значения поля можно использовать подзапрос "select ...".

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

Фунции удаления данных, DELETE SQL

DML-операция delete удаляет записи из таблицы. Критерий отбора записей для удаления определяется выражением where. В случае, если критерий отбора не определён, выполняется удаление всех записей.

Особенности использования оператора delete :

• Операция delete может вызывать срабатывание подключенного к таблице триггера;
• Для обеспечения ссылочной целостности при наличии на таблице внешних ключей все дочерние к удаляемым записи в подчинённых таблицах также должны быть удалены;
• Выполнение операции Delete должно быть подтверждено COMMIT'ом, либо опровергнуто ROLLBACK'ом.

Синтаксис команды удаления данных sql delete where :

delete from [schema_name.]table_name
   [ where <Условие отбора записей> ];

В "условиях отбора записей" может быть использован подзапрос SELECT. Пример sql delete данных с использованием подзапроса :

delete from goods
   where owner_id in (select id from managers where region like 'Моск%');

Удаление всех записей из таблицы при наличии внешних ключей и использовании механизма транзакций может занять продолжительное время. Для быстрой и полной очистки таблицы может быть использована операция truncate.

Фунции очистки таблицы, TRUNCATE TABLE

DDL-операция truncate table sql выполняет удаление всех строк в таблице. Логически truncate повторяет операцию DELETE без условия WHERE. Но имеются существенные отличия.

Синтаксис команды удаления truncate :

TRUNCATE TABLE [schema_name.]table_name;

Основные отличия операторов truncate и delete, которые могут присутствовать в различных реализациях СУБД :

• Операция truncate не регистрирует в журнале событий удаление строк, вследствие чего не может активировать триггеры.
• Операция truncate для некоторых СУБД (например, Oracle) сопровождается неявной операцией COMMIT, не позволяющей удаленные в таблице записи восстановить операцией ROLLBACK. Но в MS SQL операция truncate может участвовать в транзакциях.
• Операция truncate блокирует всю таблицу, delete — каждую строку.
• Операция delete возвращает осмысленное значение (число удаленных строк), а truncate обычно возвращает 0.
• Операция truncate в некоторых СУБД (например, MySQL или MS SQL), сбрасывает значение счетчиков для полей с AUTOINCREMENT / IDENTITY.
• Операция truncate в некоторых СУБД (например, MySQL или MS SQL) запрещена для таблиц, содержащих внешние ключи других таблиц.

Вообще говоря, реализация оператора truncate может зависеть от выбора конкретной СУБД. Поэтому в каждом случае необходимо изучать документацию выбранной системы.

Особенности truncate PostgreSQL

Ключевое слово TABLE в синтаксисе очистки таблицы truncate postgresql является необязательным. Следующая команда удаляет все записи из таблицы authors базы данных PostgreSQL.

TRUNCATE authors;

Особенности truncate Oracle

При использовании truncate table, можно столкнуться с такой ошибкой:

Эта ошибка появляется, если на таблицу, которую необходимо очистить, ссылаются другие таблицы, т.е. используется foreign key. Эта ошибка появляется даже в том случае, если в таблицах нет записей.

Чтобы выполнить очистку такой таблицы перед выполнением TRUNCATE TABLE надо отключить все foreign keys, после чего ключи снова включить.

-- отключение foreign key
alter table table_name -- таблица, в которой определен ключ 
  disable constraint constraint_name; -- foreign key;
  
-- включение foreign key
alter table table_name -- таблица, в которой определен ключ 
  enable constraint constraint_name; -- foreign key;

Чтобы найти все foreign keys в Oracle, можно выполнить следующий запрос :

select constraint_name, table_name
  from user_constraints 
 where r_constraint_name = (
  select constraint_name
    from user_constraints
   where constraint_type = 'P' and 
         table_name = upper('наименование таблицы')
  )

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

Временные таблицы temporary table используются для хранения данных, которые относятся к одной сессии или одной транзакции. То есть, данные во временной таблицы доступны только в той сессии, в которой таблица была создана и данные в таблицу добавлены.

Oracle temporary table

Применение temporary table в Oracle существенно отличается от применения временных таблиц в других СУБД. В Oracle временная таблица - это тот же DDL обьект со всеми ограничениями.

-- синтаксис создания temporary table в Oracle
CREATE GLOBAL TEMPORARY TABLE [SCHEMA_NAME].TABLE_NAME
(
 . . .
 ON COMMIT [DELETE | RESERVE] ROWS;

ОN COMMIT DELETE ROWS используется во временных таблицах, данные которой существуют в пределах одной транзакции. Oracle удаляет все строки временной таблицы после завершения транзакции, то есть после выполнения команды COMMIT. Данная инструкция подразумевается по умолчанию, то есть при создании временной таблицы - она всегда будет ОN COMMIT DELETE ROWS.

ОN COMMIT RESERVE ROWS используется во временных таблицах, данные которой существуют в пределах одной сессии. Если попробовать прочитать временную таблицу из другой сессий, то таблица будет пустой. СУБД Oracle удаляет все строки из временной таблицы после завершения сессии.

Пример создания temporary table с использованием подзапроса.

CREATE GLOBAL TEMPORARY TABLE CITIES_MOSCOW
  ON COMMIT RESERVE ROWS    
    AS SELECT * FROM CITIES WHERE REGION like '%MOSCOW%'

MS SQL temporary table

Временные таблицы в СУБД MS SQL хранятся в базе данных tempdb и автоматически удаляются, когда необходимость в них отпадает. Можно создавать два вида временных таблиц: локальные и глобальные. Они отличаются друг от друга именами, видимостью и доступностью. Имена локальных временных таблиц начинаются с одного символа '#'; они видны только текущему соединению пользователя и удаляются, когда пользователь отключается от SQL Server. Имена глобальных таблиц начинаются с двух символов '##'; они видны любому пользователю и удаляются, когда все пользователи, которые на них ссылаются, отключаются от SQL Server.

Если во время сессии создается локальная временная таблица #users, с ней можно будет работать только в данной сессии. Таблица будет удалена при завершении сессии. Если создать глобальную временную таблицу ##users, с ней сможет работать любой пользователь базы данных. Если никто из пользователей не будут работать с этой таблицей, она будет удалена после завершения сеанса. Если один из пользователей обратится к созданной таблице, то SQL Server удалит ее, когда произойдет отключение и другие сессии перестанут активно к ней обращаться.

-- синтаксис создания temporary table в MS SQL
create table #t ( 
  id    int, 
  val1  varchar(10), 
  val2  datetime default getdate()
);

MySQL temporary table

В MySQL для создания temporary table необходимо в оператор create table вставить ключевое слово TEMPORARY.

-- синтаксис создания temporary table в MySQL
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] (
 . . .
);

Временная таблица удаляется автоматически по завершению соединения. Имя temporary table действительно только в течение соединения. Это означает, что в двух разных соединениях могут использоваться временные таблицы с одинаковыми именами без конфликта друг с другом или с постоянной таблицей с тем же именем. Постоянная одноименная таблица будет скрыта, пока не будет удалена ее "тёзка" (временная таблица). Но к постоянной таблице можно обращаться с дополнительным использованием наименования схемы (базы данных).

Ключевые слова IF NOT EXISTS необходимо использовать для того, чтобы не возникала ошибка, если указанная таблица уже существует. Следует учитывать, что при этом не проверяется идентичность структур этих таблиц.

Postgres temporary table

В Postgres для создания temporary table необходимо в оператор create table вставить ключевое слово TEMPORARY или TEMP. Синтаксис аналогичен MySQL

-- синтаксис создания temporary table в PostgresSQL
CREATE [TEMPORARY | TEMP] TABLE [IF NOT EXISTS] (
 . . .
);