Инструкция языка sql create table используется для

Доброго времени суток, друзья!

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

Для более полного погружения в SQL рекомендую изучить эти руководства по MySQL и PostgreSQL от Метанита. Они хороши тем, что просты в изучении и позволяют быстро начать работу с названными СУБД.

Официальная документация по MySQL.

Официальная документация по PostreSQL (на русском языке).

Свежий туториал по SQL от Codecamp.

Свежая шпаргалка по SQL в формате PDF.

При обнаружении ошибок, опечаток и неточностей, не стесняйтесь писать мне в личку.

Содержание

• Что такое SQL?
• Почему SQL?
• Процесс SQL
• Команды SQL
• Что такое таблица?
• Что такое поле?
• Что такое запись или строка?
• Что такое колонка?
• Что такое NULL?
• Ограничения
• Целостность данных
• Нормализация БД
• Синтаксис SQL
• Типы данных
• Операторы
• Выражения
• Создание БД
• Удаление БД
• Выбор БД
• Создание таблицы
• Удаление таблицы
• Добавление колонок
• Выборка полей
• Предложение WHERE
• Операторы AND и OR
• Обновление полей
• Удаление записей
• Предложения LIKE и REGEX
• Предложение TOP/LIMIT/ROWNUM
• Предложения ORDER BY и GROUP BY
• Ключевое слово DISTINCT
• Соединения
• Предложение UNION
• Предложение UNION ALL
• Синонимы
• Индексы
• Обновление таблицы
• Очистка таблицы
• Представления
• HAVING
• Транзакции
• Временные таблицы
• Клонирование таблицы
• Подзапросы
• Последовательности

Что такое SQL?

SQL — это язык структурированных запросов (Structured Query Language), позволяющий хранить, манипулировать и извлекать данные из реляционных баз данных (далее — РБД, БД).

↥ Наверх

Почему SQL?

SQL позволяет:

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

↥ Наверх

Процесс SQL

При выполнении любой SQL-команды в любой RDBMS (Relational Database Management System — система управления РБД, СУБД, например, PostgreSQL, MySQL, MSSQL, SQLite и др.) система определяет наилучший способ выполнения запроса, а движок SQL определяет способ интерпретации задачи.

В данном процессе участвует несколького компонентов:

• диспетчер запросов (Query Dispatcher)
• движок оптимизации (Optimization Engines)
• классический движок запросов (Classic Query Engine)
• движок запросов SQL (SQL Query Engine) и т.д.

Классический движок обрабатывает все не-SQL-запросы, а движок SQL-запросов не обрабатывает логические файлы.

↥ Наверх

Команды SQL

Стандартными командами для взаимодействия с РБД являются CREATE, SELECT, INSERT, UPDATE, DELETE и DROP. Эти команды могут быть классифицированы следующим образом:

• DDL — язык определения данных (Data Definition Language)

• DML — язык изменения данных (Data Manipulation Language)

• DCL — язык управления данными (Data Control Language)

Обратите внимание: использование верхнего регистра в названиях команд SQL — это всего лишь соглашение, большинство СУБД нечувствительны к регистру. Тем не менее, форма записи инструкций, когда названия команд пишутся большими буквами, а названия таблиц, колонок и др. — маленькими, позволяет быстро определять назначение производимой с данными операции.

↥ Наверх

Что такое таблица?

Данные в СУБД хранятся в объектах БД, называемых таблицами (tables). Таблица, как правило, представляет собой коллекцию связанных между собой данных и состоит из определенного количества колонок и строк.

Таблица — это самая распространенная и простая форма хранения данных в РБД. Вот пример таблицы с пользователями (users):

↥ Наверх

Что такое поле?

Каждая таблица состоит из небольших частей — полей (fields). Полями в таблице users являются userId, userName, age, city и status. Поле — это колонка таблицы, предназначенная для хранения определенной информации о каждой записи в таблице.

Обратите внимание: вместо userId и userName можно было бы использовать id и name, соответственно. Но при работе с несколькими объектами, содержащими свойство id, бывает сложно понять, какому объекту принадлежит идентификатор, особенно, если вы, как и я, часто прибегаете к деструктуризации. Что касается слова name, то оно часто оказывается зарезервизованным, т.е. уже используется в среде, в которой выполняется код, поэтому я стараюсь его не использовать.

↥ Наверх

Что такое запись или строка?

Запись или строка (record/row) — это любое единичное вхождение (entry), существующее в таблице. В таблице users 5 записей. Проще говоря, запись — это горизонтальное вхождение в таблице.

↥ Наверх

Что такое колонка?

Колонка (column) — это вертикальное вхождение в таблице, содержащее всю информацию, связанную с определенным полем. В таблице users одной из колонок является city, которая содержит названия городов, в которых проживают пользователи.

↥ Наверх

Что такое нулевое значение?

Нулевое значение (NULL) — это значение поля, которое является пустым, т.е. нулевое значение — это значение поля, не имеющего значения. Важно понимать, что нулевое значение отличается от значения 0 и от значения поля, содержащего пробелы (`). Поле с нулевым значением - это такое поля, которое осталось пустым при создании записи. Также, следует учитывать, что в некоторых СУБД пустая строка (») — этоNULL`, а в некоторых — это разные значения.

↥ Наверх

Ограничения

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

Ограничения могут устанавливаться как на уровне колонки, так и на уровне таблицы.

Среди наиболее распространенных ограничений можно назвать следующие:

• NOT NULL — колонка не может иметь нулевое значение
• DEFAULT — значение колонки по умолчанию
• UNIQUE — все значения колонки должны быть уникальными
• PRIMARY KEY — первичный или основной ключ, уникальный идентификатор записи в текущей таблице
• FOREIGN KEY — внешний ключ, уникальный идентификатор записи в другой таблице (таблице, связанной с текущей)
• CHECK — все значения в колонке должны удовлетворять определенному условию
• INDEX — быстрая запись и извлечение данных

Любое ограничение может быть удалено с помощью команды ALTER TABLE и DROP CONSTRAINT + название ограничения. Некоторые реализации предоставляют сокращения для удаления ограничений и возможность отключать ограничения вместо их удаления.

↥ Наверх

Целостность данных

В каждой СУБД существуют следующие категории целостности данных:

• целостность объекта (Entity Integrity) — в таблице не должно быть дубликатов (двух и более строк с одинаковыми значениями)
• целостность домена (Domain Integrity) — фильтрация значений по типу, формату или диапазону
• целостность ссылок (Referential integrity) — строки, используемые другими записями (строки, на которые в других записях имеются ссылки), не могут быть удалены
• целостность, определенная пользователем (User-Defined Integrity) — дополнительные правила

↥ Наверх

Нормализация БД

Нормализация — это процесс эффективной организации данных в БД. Существует две главных причины, обуславливающих необходимость нормализации:

• предотвращение записи в БД лишних данных, например, хранения одинаковых данных в разных таблицах
• обеспечение «оправданной» связи между данными

Нормализация предполагает соблюдение нескольких форм. Форма — это формат структурирования БД. Существует три главных формы: первая, вторая и, соответственно, третья. Я не буду вдаваться в подробности об этих формах, при желании, вы без труда найдете необходимую информацию.

↥ Наверх

Синтаксис SQL

Синтаксис — это уникальный набор правил и рекомендаций. Все инструкции SQL должны начинаться с ключевого слова, такого как SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE, USE, SHOW и т.п. и заканчиваться точкой с запятой (;) (точка с запятой не входит в синтаксис SQL, но ее наличия, как правило, требуют консольные клиенты СУБД для обозначения окончания ввода команды). SQL не чувствителен к регистру, т.е. SELECT, select и SeLeCt являются идентичными инструкицями. Исключением из этого правила является MySQL, где учитывается регистр в названии таблицы.

Примеры синтаксиса

-- выборка
SELECT col1, col2, ...colN
FROM tableName;

SELECT DISTINCT col1, col2, ...colN
FROM tableName;

SELECT col1, col2, ...colN
FROM tableName
WHERE condition;

SELECT col1, col2, ...colN
FROM tableName
WHERE condition1 AND|OR condition2;

SELECT col2, col2, ...colN
FROM tableName
WHERE colName IN (val1, val2, ...valN);

SELECT col1, col2, ...colN
FROM tableName
WHERE colName BETWEEN val1 AND val2;

SELECT col1, col2, ...colN
FROM tableName
WHERE colName LIKE pattern;

SELECT col1, col2, ...colN
FROM tableName
WHERE condition
ORDER BY colName [ASC|DESC];

SELECT SUM(colName)
FROM tableName
WHERE condition
GROUP BY colName;

SELECT COUNT(colName)
FROM tableName
WHERE condition;

SELECT SUM(colName)
FROM tableName
WHERE condition
GROUP BY colName
HAVING (function condition);

-- создание таблицы
CREATE TABLE tableName (
  col1 datatype,
  col2 datatype,
  ...
  colN datatype,
  PRIMARY KEY (одна или более колонка)
);

-- удаление таблицы
DROP TABLE tableName;

-- создание индекса
CREATE UNIQUE INDEX indexName
ON tableName (col1, col2, ...colN);

-- удаление индекса
ALTER TABLE tableName
DROP INDEX indexName;

-- получение описания структуры таблицы
DESC tableName;

-- очистка таблицы
TRUNCATE TABLE tableName;

-- добавление/удаление/модификация колонок
ALTER TABLE tableName ADD|DROP|MODIFY colName [datatype];

-- переименование таблицы
ALTER TABLE tableName RENAME TO newTableName;

-- вставка значений
INSERT INTO tableName (col1, col2, ...colN)
VALUES (val1, val2, ...valN)

-- обновление записей
UPDATE tableName
SET col1 = val1, col2 = val2, ...colN = valN
[WHERE condition];

-- удаление записей
DELETE FROM tableName
WHERE condition;

-- создание БД
CREATE DATABASE [IF NOT EXISTS] dbName;

-- удаление БД
DROP DATABASE [IF EXISTS] dbName;

-- выбор БД
USE dbName;

-- завершения транзакции
COMMIT;

-- отмена изменений
ROLLBACK;

↥ Наверх

Типы данных

Каждая колонка, переменная и выражение в SQL имеют определенный тип данных (data type). Основные категории типов данных:

Точные числовые

Приблизительные числовые

Дата и время

Строковые символьные

Строковые символьные (юникод)

Бинарные

Смешанные

↥ Наверх

Операторы

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

В дальнейших примерах мы будем исходить из предположения, что переменная a имеет значение 10, а b — 20.

Арифметические

Операторы сравнения

Логические операторы

↥ Наверх

Выражения

Выражение (expression) — это комбинация значений, операторов и функций для оценки (вычисления) значения. Выражения похожи на формулы, написанные на языке запросов. Они могут использоваться для извлечения из БД определенного набора данных.

Базовый синтаксис выражения выглядит так:

SELECT col1, col2, ...colN
FROM tableName
WHERE [condition|expression];

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

Логические

Логические выражения извлекают данные на основе совпадения с единичным значением.

SELECT col1, col2, ...colN
FROM tableName
WHERE выражение для поиска совпадения с единичным значением;

Предположим, что в таблице users имеются следующие записи:

Выполняем поиск активных пользователей:

SELECT * FROM users WHERE status = active;

Результат:

Числовые

Используются для выполнения арифметических операций в запросе.

SELECT numericalExpression as operationName
[FROM tableName
WHERE condition];

Простой пример использования числового выражения:

SELECT (10 + 5) AS addition;

Результат:

Существует несколько встроенных функций, таких как count(), sum(), avg(), min(), max() и др. для выполнения так называемых агрегирующих вычислений данных таблицы или колонки.

SELECT COUNT(*) AS records FROM users;

Результат:

• AVG — вычисляет среднее значение
• SUM — вычисляет сумму значений
• MIN — вычисляет наименьшее значение
• MAX — вычисляет наибольшее значение
• COUNT — вычисляет количество записей в таблице

Также существует несколько встроенных функций для работы со строками:

• CONCAT — объединение строк
• LENGTH — возвращает количество символов в строке
• TRIM — удаляет пробелы в начале и конце строки
• SUBSTRING — извлекает подстроку из строки
• REPLACE — заменяет подстроку в строке
• LOWER — переводит символы строки в нижний регистр
• UPPER — переводит символы строки в верхний регистр и т.д.

с числами:

• ROUND — округляет число
• TRUNCATE — обрезает дробное число до указанного количества знаков после запятой
• CEILING — возвращает наименьшее целое число, которое больше или равно текущему значению
• FLOOR — возвращает наибольшее целое число, которое меньше или равно текущему значению
• POWER — возводит число в указанную степень
• SQRT — возвращает квадратный корень числа
• RAND — генерирует случайное число с плавающей точкой в диапазоне от 0 до 1

Выражения для работы с датами

Эти выражения, как правило, возвращают текущую дату и время.

SELECT CURRENT_TIMESTAMP;

Результат:

CURRENT_TIMESTAMP — это и выражение, и функция (CURRENT_TIMESTAMP()). Другая функция для получения текущей даты и времени — NOW().

Другие функции для получения текущей даты и времени:

• CURDATE/CURRENT_DATE — возвращает текущую дату
• CURTIME/CURRENT_TIME — возвращает текущее время и т.д.

Функции для разбора даты и времени:

• DAYOFMONTH(date) — возвращает день месяца в виде числа
• DAYOFWEEK(date) — возвращает день недели в виде числа
• DAYOFYEAR(date) — возвращает номер дня в году
• MONTH(date) — возвращает месяц
• YEAR(date) — возвращает год
• LAST_DAY(date) — возвращает последний день месяца в виде даты
• HOUR(time) — возвращает час
• MINUTE(time) — возвращает минуты
• SECOND(time) — возвращает секунды и др.

Функции для манипулирования датами:

• DATE_ADD(date, interval) — выполняет сложение даты и определенного временного интервала
• DATE_SUB(date, interval) — выполняет вычитание из даты определенного временного интервала
• DATEDIFF(date1, date2) — возвращает разницу в днях между двумя датами
• TO_DAYS(date) — возвращает количество дней с 0-го дня года
• TIME_TO_SEC(time) — возвращает количество секунд с полуночи и др.

Для форматирования даты и времени используются функции DATE_FORMAT(date, format) и TIME_FORMAT(date, format), соответственно.

↥ Наверх

Создание БД

Для создания БД используется инструкция CREATE DATABASE.

CREATE DATABASE dbName;
-- или
CREATE DATABASE IF NOT EXISTS dbName;

Условие IF NOT EXISTS позволяет избежать получения ошибки при попытке создания БД, которая уже существует.

Название БД должно быть уникальным в пределах СУБД.

Создаем БД testDB:

CREATE DATABASE testDB;

Получаем список БД:

SHOW DATABASES;

Результат:

↥ Наверх

Удаление БД

Для удаления БД используется инструкция DROP DATABASE.

DROP DATABASE dbName;
-- или
DROP DATABASE IF EXISTS dbName;

Условие IF EXISTS позволяет избежать получения ошибки при попытке удаления несуществующей БД.

Удаляем testDB:

DROP DATABASE testDB;

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

Проверяем, что БД удалена:

SHOW DATABASES;

Для получения списка таблиц используется инструкция SHOW TABLES.

Результат:

↥ Наверх

Выбор БД

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

USE dbName;

Предположим, что мы не удаляли testDB. Тогда мы можем выбрать ее так:

USE testDB;

↥ Наверх

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

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

CREATE TABLE tableName (
  col1 datatype,
  col2 datatype,
  ...
  colN datatype,
  PRIMARY KEY (хотя бы одна колонка)
);

Для создания таблицы путем копирования другой таблицы используется сочетание CREATE TABLE и SELECT.

Пример создания таблицы users, где первичным ключом являются идентификаторы пользователей, а поля для имени и возраста пользователя не могут быть нулевыми:

CREATE TABLE users (
  userId INT,
  userName VARCHAR(20)  NOT NULL,
  age INT           NOT NULL,
  city VARCHAR(20),
  status VARCHAR(8),
  PRIMARY KEY (id)
);

Проверяем, что таблица была создана:

DESC users;

Результат:

↥ Наверх

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

Для удаления таблицы используется инструкция DROP TABLE.

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

Удаляем таблицу users:

DROP TABLE users;

Теперь, если мы попытаемся получить описание users, то получим ошибку:

DESC users;
-- ERROR 1146 (42S02): Table 'testDB.users' doesn't exist

↥ Наверх

Добавление колонок

Для добавления в таблицу колонок используется инструкция INSERT INTO.

INSERT INTO tableName (col1, col2, ...colN)
VALUES (val1, val2, ...valN);

Названия колонок можно не указывать, однако, в этом случае значения должны перечисляться в правильном порядке.

INSERT INTO tableName VALUES (val1, val2, ...valN);

Во избежание ошибок, рекомендуется всегда перечислять названия колонок.

Предположим, что мы не удаляли таблицу users. Заполним ее пользователями:

INSERT INTO users (userId, userName, age, city, status)
VALUES (1, 'Igor', 25, 'Moscow', 'active');

INSERT INTO users (userId, userName, age, city, status)
VALUES (2, 'Vika', 26, 'Ekaterinburg', 'inactive');

INSERT INTO users (userId, userName, age, city, status)
VALUES (3, 'Elena', 27, 'Ekaterinburg', 'active');

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

INSERT INTO users (userId, userName, age, city, status)
VALUES
(1, 'Igor', 25, 'Moscow', 'active'),
(2, 'Vika', 26, 'Ekaterinburg', 'inactive'),
(3, 'Elena', 27, 'Ekaterinburg', 'active');

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

INSERT INTO users
VALUES (4, 'Oleg', 28, 'Moscow', 'inactive');

Результат:

Заполнение таблицы с помощью другой таблицы

INSERT INTO tableName [(col1, col2, ...colN)]
  SELECT col1, col2, ...colN
  FROM anotherTable
  [WHERE condition];

↥ Наверх

Выборка полей

Для выборки полей из таблицы используется инструкция SELECT. Она возвращает данные в виде результирующей таблицы (результирующего набора, result-set).

SELECT col1, col2, ...colN
FROM tableName;

Для выборки всех полей используется такой синтаксис:

SELECT * FROM tableName;

Произведем выборку полей userId, userName и age из таблицы users:

SELECT userId, userName, age FROM users;

Результат:

↥ Наверх

Предложение WHERE

Предложение WHERE используется для фильтрации возвращаемых данных. Оно используется совместно с SELECT, UPDATE, DELETE и другими инструкциями.

SELECT col1, col2, ...col2
FROM tableName
WHERE condition;

Условие (condition), которому должны удовлетворять возвращаемые записи, определяется с помощью операторов сравнения или логических операторов типа >, <, =, NOT, LIKE и т.д.

Сделаем выборку полей userId, userName и age активных пользователей:

SELECT userId, userName, age
FROM users
WHERE status = 'active';

Результат:

Сделаем выборку полей userId, age и city пользователя с именем Vika.

SELECT userId, age, city
FROM users
WHERE userName = 'Vika';

Результат:

Обратите внимание: строки в предложении WHERE должны быть обернуты в одинарные кавычки (''), а числа, напротив, указываются как есть.

↥ Наверх

Операторы AND и OR

Конъюнктивный оператор AND и дизъюнктивный оператор OR используются для соединения нескольких условий при фильтрации данных.

AND

SELECT col1, col2, ...colN
FROM tableName
WHERE condition1 AND condition2 ...AND conditionN;

Возвращаемые записи должны удовлетворять всем указанным условиям.

Сделаем выборку полей userId, userName и age активных пользователей старше 26 лет:

SELECT userId, userName, age
FROM users
WHERE status = active AND age > 26;

Результат:

OR

SELECT col1, col2, ...colN
FROM tableName
WHERE condition1 OR condition2 ...OR conditionN;

Возвращаемые записи должны удовлетворять хотя бы одному условию.

Сделаем выборку тех же полей неактивных пользователей или пользователей, младше 27 лет:

SELECT userId, userName, age
FROM users
WHERE status = inactive OR age < 27;

Результат:

↥ Наверх

Обновление полей

Для обновления полей используется инструкция UPDATE ... SET. Эта инструкция, обычно, используется в сочетании с предложением WHERE.

UPDATE tableName
SET col1 = val1, col2 = val2, ...colN = valN
[WHERE condition];

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

UPDATE users
SET age = 30
WHERE username = 'Igor';

Если в данном случае опустить WHERE, то будет обновлен возраст всех пользователей.

↥ Наверх

Удаление записей

Для удаления записей используется инструкция DELETE. Эта инструкция также, как правило, используется в сочетании с предложением WHERE.

DELETE FROM tableName
[WHERE condition];

Удалим неактивных пользователей:

DELETE FROM users
WHERE status = 'inactive';

Если в данном случае опустить WHERE, то из таблицы users будут удалены все записи.

↥ Наверх

Предложения LIKE и REGEX

LIKE

Предложение LIKE используется для сравнения значений с помощью операторов с подстановочными знаками. Существует два вида таких операторов:

• проценты (%)
• нижнее подчеркивание (_)

% означает 0, 1 или более символов. _ означает точно 1 символ.

SELECT col1, col2, ...colN FROM tableName
WHERE col LIKE 'xxx%'
-- или
WHERE col LIKE '%xxx%'
-- или
WHERE col LIKE '%xxx'
-- или
WHERE col LIKE 'xxx_'
-- и т.д.

Примеры:

Сделаем выборку неактивных пользователей:

SELECT * FROM users
WHERE status LIKE 'in%';

Результат:

Сделаем выборку пользователей 30 лет и старше:

SELECT * FROM users
WHERE age LIKE '3_';

Результат:

REGEX

Предложение REGEX позволяет определять регулярное выражение, которому должна соответствовать запись.

SELECT col1, col2, ...colN FROM tableName
WHERE colName REGEXP регулярное выражение;

В регулярное выражении могут использоваться следующие специальные символы:

• ^ — начало строки
• $ — конец строки
• . — любой символ
• [символы] — любой из указанных в скобках символов
• [начало-конец] — любой символ из диапазона
• | — разделяет шаблоны

Сделаем выборку пользователей с именами Igor и Vika:

SELECT * FROM users
WHERE userName REGEXP 'Igor|Vika';

Результат:

↥ Наверх

Предложение TOP/LIMIT/ROWNUM

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

SELECT TOP number|percent col1, col2, ...colN
FROM tableName
[WHERE condition];

Сделаем выборку первых трех пользователей:

SELECT TOP 3 * FROM users;

Результат:

В mysql:

SELECT * FROM users
LIMIT 3, [offset];

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

SELECT * FROM users
LIMIT 2, 2;

В oracle:

SELECT * FROM users
WHERE ROWNUM <= 3;

↥ Наверх

Предложения ORDER BY и GROUP BY

ORDER BY

Предложение ORDER BY используется для сортировки данных по возрастанию (ASC) или убыванию (DESC). Многие СУБД по умолчанию выполняют сортировку по возрастанию.

SELECT col1, col2, ...colN
FROM tableName
[WHERE condition]
[ORDER BY col1, col2, ...colN] [ASC | DESC];

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

Сделаем выборку пользователей, отсортировав их по городу и возрасту:

SELECT * FROM users
ORDER BY city, age;

Результат:

Теперь выполним сортировку по убыванию:

SELECT * FROM users
ORDER BY city, age DESC;

Определим собственный порядок сортировки по убыванию:

SELECT * FROM users
ORDER BY (CASE city
  WHEN 'Ekaterinburg' THEN 1
  WHEN 'Moscow' THEN 2
ELSE 100 END) ASC, city DESC;

GROUP BY

Предложение GROUP BY используется совместно с инструкцией SELECT для группировки записей. Оно указывается после WHERE и перед ORDER BY.

SELECT col1, col2, ...colN
FROM tableName
WHERE condition
GROUP BY col1, col2, ...colN
ORDER BY col1, col2, ...colN;

Сгруппируем активных пользователей по городам:

SELECT city, COUNT(city) AS amount FROM users
WHERE status = active
GROUP BY city
ORDER BY city;

Результат:

↥ Наверх

Ключевое слово DISTINCT

Ключевое слово DISTINCT используется совместно с инструкцией SELECT для возврата только уникальных записей (без дубликатов).

SELECT DISTINCT col1, col2, ...colN
FROM tableName
[WHERE condition];

Сделаем выборку городов проживания пользователей:

SELECT DISTINCT city
FROM users;

Результат:

↥ Наверх

Соединения

Соединения (joins) используются для комбинации записей двух и более таблиц.

Предположим, что кроме users, у нас имеется таблица orders с заказами пользователей следующего содержания:

Сделаем выборку полей userId, userName, age и amount из наших таблиц посредством их соединения:

SELECT userId, userName, age, amount
FROM users, orders
WHERE users.userId = orders.userId;

Результат:

При соединении таблиц могут использоваться такие операторы, как =, <, >, <>, <=, >=, !=, BETWEEN, LIKE и NOT, однако наиболее распространенным является =.

Существуют разные типы объединений:

• INNER JOIN — возвращает записи, имеющиеся в обеих таблицах
• LEFT JOIN — возвращает записи из левой таблицы, даже если такие записи отсутствуют в правой таблице
• RIGHT JOIN — возвращает записи из правой таблицы, даже если такие записи отсутствуют в левой таблице
• FULL JOIN — возвращает все записи объединяемых таблиц
• CROSS JOIN — возвращает все возможные комбинации строк обеих таблиц
• SELF JOIN — используется для объединения таблицы с самой собой

↥ Наверх

Предложение UNION

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

В случае с UNION, каждая инструкция SELECT должна иметь:

• одинаковый набор колонок для выборки
• одинаковое количество выражений
• одинаковые типы данных колонок и
• одинаковый порядок колонок

Однако, они могут быть разной длины.

SELECT col1, col2, ...colN
FROM table1
[WHERE condition]

UNION

SELECT col1, col2, ...colN
FROM table2
[WHERE condition];

Объединим наши таблицы users и orders:

  SELECT userId, userName, amount, date
  FROM users
  LEFT JOIN orders
  ON users.useId = orders.userId
UNION
  SELECT userId, userName, amount, date
  FROM users
  RIGHT JOIN orders
  ON users.userId = orders.userId;

Результат:

↥ Наверх

Предложение UNION ALL

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

SELECT col1, col2, ...colN
FROM table1
[WHERE condition]

UNION ALL

SELECT col1, col2, ...colN
FROM table2
[WHERE condition];

Существует еще два предложения, похожих на UNION:

• INTERSECT — используется для комбинации результатов двух и более SELECT, но возвращаются только строки из первого SELECT, совпадающие со строками из второго SELECT
• EXCEPT|MINUS — возвращаются только строки из первого SELECT, отсутствующие во втором SELECT

↥ Наверх

Синонимы

Синонимы (aliases) позволяют временно изменять названия таблиц и колонок. «Временно» означает, что новое название используется только в текущем запросе, в БД название остается прежним.

Синтаксис синонима таблицы:

SELECT col1, col2, ...colN
FROM tableName AS aliasName
[WHERE condition];

Синтаксис синонима колонки:

SELECT colName AS aliasName
FROM tableName
[WHERE condition];

Пример использования синонимов таблиц:

SELECT U.userId, U.userName, U.age, O.amount
FROM users AS U, orders AS O
WHERE U.userId = O.userId;

Результат:

Пример использования синонимов колонок:

SELECT userId AS user_id, userName AS user_name, age AS user_age
FROM users
WHERE status = active;

Результат:

↥ Наверх

Индексы

Создание индексов

Индексы — это специальные поисковые таблицы (lookup tables), которые используются движком БД в целях более быстрого извлечения данных. Проще говоря, индекс — это указатель или ссылка на данные в таблице.

Индексы ускоряют работу инструкции SELECT и предложения WHERE, но замедляют работу инструкций UPDATE и INSERT. Индексы могут создаваться и удаляться, не оказывая никакого влияния на данные.

Для создания индекса используется инструкция CREATE INDEX, позволяющая определять название индекса, индексируемые колонки и порядок индексации (по возрастанию или по убыванию).

К индексам можно применять ограничение UNIQUE для того, чтобы обеспечить их уникальность.

Синтаксис создания индекса:

CREATE INDEX indexName ON tableName;

Синтаксис создания индекса для одной колонки:

CREATE INDEX indexName
ON tableName (colName);

Синтакис создания уникальных индексов (такие индексы используются не только для повышения производительности, но и для обеспечения согласованности данных):

CREATE UNIQUE INDEX indexName
ON tableName (colName);

Синтаксис создания индексов для нескольких колонок (композиционный индекс):

CREATE INDEX indexName
ON tableName (col1, col2, ...colN);

Решение о создании индексов для одной или нескольких колонок следует принимать на основе того, какие колонки будут часто использоваться в запросе WHERE в качестве условия для сортировки строк.

Для ограничений PRIMARY KEY и UNIQUE автоматически создаются неявные индексы.

Удаление индексов

Для удаления индексов используется инструкция DROP INDEX:

DROP INDEX indexName;

Несмотря на то, что индексы предназначены для повышения производительности БД, существуют ситуации, в которых их использования лучше избегать.

К таким ситуациям относится следующее:

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

↥ Наверх

Обновление таблицы

Команда ALTER TABLE используется для добавления, удаления и модификации колонок существующей таблицы. Также эта команда используется для добавления и удаления ограничений.

Синтаксис:

-- добавление новой колонки
ALTER TABLE tableName ADD colName datatype;

-- удаление колонки
ALTER TABLE tableName DROP COLUMN colName;

-- изменение типа данных колонки
ALTER TABLE tableName MODIFY COLUMN colName newDatatype;

-- добавление ограничения `NOT NULL`
ALTER TABLE tableName MODIFY colName datatype NOT NULL;

-- добавление ограничения `UNIQUE`
ALTER TABLE tableName
ADD CONSTRAINT myUniqueConstraint UNIQUE (col1, col2, ...colN);

-- добавление ограничения `CHECK`
ALTER TABLE tableName
ADD CONSTRAINT myUniqueConstraint CHECK (condition);

-- добавление первичного ключа
ALTER TABLE tableName
ADD CONSTRAINT myPrimaryKey PRIMARY KEY (col1, col2, ...colN);

-- удаление ограничения
ALTER TABLE tableName
DROP CONSTRAINT myUniqueContsraint;

-- mysql
ALTER TABLE tableName
DROP INDEX myUniqueContsraint;

-- удаление первичного ключа
ALTER TABLE tableName
DROP CONSTRAINT myPrimaryKey;

-- mysql
ALTER TABLE tableName
DROP PRIMARY KEY;

Добавляем в таблицу users новую колонку — пол пользователя:

ALTER TABLE users ADD sex char(1);

Удаляем эту колонку:

ALTER TABLE users DROP sex;

↥ Наверх

Очистка таблицы

Команда TRUNCATE TABLE используется для очистки таблицы. Ее отличие от DROP TABLE состоит в том, что сохраняется структура таблицы (DROP TABLE полностью удаляет таблицу и все ее данные).

TRUNCATE TABLE tableName;

Очищаем таблицу users:

TRUNCATE TABLE users;

Проверяем, что users пустая:

SELECT * FROM users;
-- Empty set (0.00 sec)

↥ Наверх

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

Представление (view) — это не что иное, как инструкция, записанная в БД под определенным названием. Другими словами, представление — это композиция таблицы в форме предварительно определенного запроса.

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

Представления — это виртутальные таблицы, позволяющие делать следующее:

• структурировать данные способом, который пользователи находят наиболее естественным или интуитивно понятным
• ограничивать доступ к данным таким образом, что пользователь может просматривать и (иногда) модифицировать только то, что ему нужно и ничего более
• объединять данные из нескольких таблиц для формирования отчетов

Создание представления

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

CREATE VIEW viewName AS
SELECT col1, col2, ...colN
FROM tableName
[WHERE condition];

Создаем представление для имен и возраста пользователей:

CREATE VIEW usersView AS
SELECT userName, age
FROM users;

Получаем данные с помощью представления:

SELECT * FROM usersView;

Результат:

WITH CHECK OPTION

WITH CHECK OPTION — это настройка инструкции CREATE VIEW. Она позволяет обеспечить соответствие всех UPDATE и INSERT условию, определенном в представлении.

Если условие не удовлетворяется, выбрасывается исключение.

CREATE VIEW usersView AS
SELECT userName, age
FROM users
WHERE age IS NOT NULL
WITH CHECK OPTION;

Обновление представления

Представление может быть обновлено при соблюдении следующих условий:

• SELECT не содержит ключевого слова DISTINCT
• SELECT не содержит агрегирующих функций
• SELECT не содержит функций установки значений
• SELECT не содержит операций установки значений
• SELECT не содержит предложения ORDER BY
• FROM не содержит больше одной таблицы
• WHERE не содержит подзапросы
• запрос не содержит GROUP BY или HAVING
• вычисляемые колонки не обновляются
• все ненулевые колонки из базовой таблицы включены в представление в том же порядке, в каком они указаны в запросе INSERT

Пример обновления возраста пользователя с именем Igor в представлении:

UPDATE usersView
SET age = 31
WHERE userName = 'Igor';

Обратите внимание: обновление строки в представлении приводит к ее обновлению в базовой таблице.

В представление могут добавляться новые строки с помощью команды INSERT. При выполнении этой команды должны соблюдаться те же правила, что и при выполнении команды UPDATE.

С помощью команды DELETE можно удалять строки из представления.

Удаляем из представления пользователя, возраст которого составляет 26 лет:

DELETE FROM usersView
WHERE age = 26;

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

Удаление представления

Для удаления представления используется инструкция DROP VIEW:

DROP VIEW viewName;

Удаляем представление usersView:

DROP VIEW usersView;

↥ Наверх

HAVING

Предложение HAVING используется для фильтрации результатов группировки. WHERE используется для применения условий к колонкам, а HAVING — к группам, созданным с помощью GROUP BY.

HAVING должно указываться после GROUP BY, но перед ORDER BY (при наличии).

SELECT col1, col2, ...colN
FROM table1, table2, ...tableN
[WHERE condition]
GROUP BY col1, col2, ...colN
HAVING condition
ORDER BY col1, col2, ...colN;

↥ Наверх

Транзакции

Транзакция — это единица работы или операции, выполняемой над БД. Это последовательность операций, выполняемых в логическом порядке. Эти операции могут запускаться как пользователем, так и какой-либо программой, функционирующей в БД.

Транзакция — это применение одного или более изменения к БД. Например, при создании/обновлении/удалении записи мы выполняем транзакцию. Важно контролировать выполнение таких операций в целях обеспечения согласованности данных и обработки возможных ошибок.

На практике, запросы, как правило, не отправляются в БД по одному, они группируются и выполняются как часть транзакции.

Свойства транзакции

Транзакции имеют 4 стандартных свойства (ACID):

• атомарность (atomicity) — все операции транзакции должны быть успешно завершены. В противном случае, транзакция прерывается, а все изменения отменяются (происходит откат к предыдущему состоянию)
• согласованность (consistency) — состояние должно изменться в полном соответствии с операциями транзакции
• изоляция или автономность (isolation) — транзакции не зависят друг от друга и не оказывают друг на друга никакого влияния
• долговечность (durability) — результат звершенной транзакции должен сохраняться при поломке системы

Управление транзакцией

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

• BEGIN|START TRANSACTION — запуск транзакции
• COMMIT — сохранение изменений
• ROLLBACK — отмена изменений
• SAVEPOINT — контрольная точка для отмены изменений
• SET TRANSACTION — установка характеристик текущей транзакции

Команды для управления транзакцией могут использоваться только совместно с такими запросами как INSERT, UPDATE и DELETE. Они не могут использоваться во время создания и удаления таблиц, поскольку эти операции автоматически отправляются в БД.

Удаляем пользователя, возраст которого составляет 26 лет, и отправляем изменения в БД:

BEGIN TRANSACTION
DELETE FROM users
WHERE age = 26;
COMMIT;

Удаляем пользователя с именем Oleg и отменяем эту операцию:

BEGIN
  DELETE FROM users
  WHERE username = 'Oleg';
ROLLBACK;

Контрольные точки создаются с помощью такого синтаксиса:

SAVEPOINT savepointName;

Возврат к контрольной точке выполняется так:

ROLLBACK TO savepointName;

Выполняем три запроса на удаление данных из users, создавая контрольные точки перед каждый удалением:

START TRANSACTION
SAVEPOINT sp1;
DELETE FROM users
WHERE age = 26;

SAVEPOINT sp2;
DELETE FROM users
WHERE userName = 'Oleg';

SAVEPOINT sp3;
DELETE FROM users
WHERE status = 'inactive';

Отменяем два последних удаления, возвращаясь к контрльной точке sp2, созданной после первого удаления:

ROLLBACK TO sp2;

Делаем выборку пользователей:

SELECT * FROM users;

Результат:

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

Для удаление контрольной точки используется команда RELEASE SAVEPOINT. Естественно, после удаления контрольной точки, к ней нельзя будет вернуться с помощью ROLLBACK TO.

Команда SET TRANSACTION используется для инициализации транзакции, т.е. начала ее выполнения. При этом, можно определять некоторые характеристики транзакции. Например, так можно определить уровень доступа транзакции (доступна только для чтения или для записи тоже):

SET TRANSACTION [READ WRITE | READ ONLY];

↥ Наверх

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

Некоторые СУБД поддерживают так называемые временные таблицы (temporary tables). Такие таблицы позволяют хранить и обрабатывать промежуточные результаты с помощью таких же запросов, как и при работе с обычными таблицами.

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

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

Временная таблица удаляется точно также, как и обычная таблица, с помощью инструкции DROP TABLE.

↥ Наверх

Клонирование таблицы

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

В mysql, например, это можно сделать так:

• вызываем команду SHOW CREATE TABLE для получения инструкции, выполненной при создании таблицы, включая индексы и прочее
• меняем название таблицы и выполняем запрос. Получаем точную копию таблицы
• опционально: если требуется содержимое копируемой таблицы, можно также использовать инструкции INSERT INTO или SELECT

↥ Наверх

Подзапросы

Подзапрос — это внутренний (вложенный) запрос другого запроса, встроенный (вставленный) с помощью WHERE или других инструкций.

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

Подзапросы могут использоваться в инструкциях SELECT, INSERT, UPDATE и DELETE, а также с операторами =, <, >, >=, <=, IN, BETWEEN и т.д.

Правила использования подзапросов:

• они должны быть обернуты в круглые скобки
• подзапрос должен содержать только одну колонку для выборки, если основной запрос не содержит несколько таких колонок, которые сравниваются в подзапросе
• в подзапросе нельзя использовать команду ORDER BY, это можно сделать в основном запросе. В подзапросе для замены ORDER BY можно использовать GROUP BY
• подзапросы, возвращающие несколько значений, могут использоваться только с операторами, которые работают с наборами значений, такими как IN
• список SELECT не может содержать ссылки на значения, которые оцениваются (вычисляются) как BLOB, ARRAY, CLOB или NCLOB
• подзапрос не может быть сразу передан в функцию для установки значений
• команду BETWEEN нельзя использовать совместно с подзапросом. Тем не менее, в самомподзапросе указанную команду использовать можно

Подзапросы, обычно, используются в инструкции SELECT.

SELECT col1, col2, ...colN
FROM table1, table2, ...tableN
WHERE colName operator
  (SELECT col1, col2, ...colN
  FROM table1, table2, tableN
  [WHERE condition]);

Пример:

SELECT * FROM users
WHERE userId IN (
  SELECT userId FROM users
  WHERE status = 'active'
);

Результат:

Подзапросы могут использоваться в инструкции INSERT. Эта инструкция добавляет в таблицу данные, возвращаемые подзапросом. При этом, данные, возвращаемые подзапросом, могут быть модифицированы любыми способами.

INSERT INTO tableName col1, col2, ...colN
SELECT col1, col2, ...colN
FROM table1, table2, ...tableN
[WHERE operator [value]];

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

UPDATE tableName
SET col = newVal
[WHERE operator [value]
  (
    SELECT colName
    FROM tableName
    [WHERE condition]
  )
];

Данные, возвращаемые подзапросом, могут использоваться и для удаления записей.

DELETE FROM tableName
[WHERE operator [value]
  (
    SELECT colName
    FROM tableName
    [WHERE condition]
  )
];

↥ Наверх

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

Последовательность — это набор целых чисел (1, 2, 3 и т.д.), генерируемых автоматически. Последовательности часто используются в БД, поскольку многие приложения нуждаются в уникальных значениях, используемых для идентификации строк.

Приведенные ниже примеры рассчитаны на mysql.

Простейшим способом определения последовательности является использование AUTO_INCREMENT при создании таблицы:

CREATE TABLE tableName (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id),
  -- другие строки
);

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

ALTER TABLE tableName DROP id;
ALTER TABLE tableName
ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
ADD PRIMARY KEY (id);

По умолчанию значения, генерируемые с помощью AUTO_INCREMENT, начинаются с 1. Для того, чтобы установить другое начальное значение достаточно указать, например, AUTO_INCREMENT = 100 — в этом случае нумерация строк начнется со 100.

↥ Наверх

Перед изучением данной темы рекомендуется ознакомиться с темой:

• Microsoft SQL Server Management Studio 2018. Пример создания простейшего запроса

---

Содержание

• 1. Оператор CREATE TABLE. Создание таблицы. Общая форма
• 2. Примеры создания таблиц
  • 2.1. Пример создания простейшей таблицы учета товаров в магазине
  • 2.2. Пример создания таблицы учета телефонов абонентов
  • 2.3. Пример создания таблицы учета заработной платы и отчислений в организации
• Связанные темы

---

Поиск на других ресурсах:

1. Оператор CREATE TABLE. Создание таблицы. Общая форма

Таблица является основным элементом любой реляционной базы данных. Вся необходимая информация, которая должна храниться в базе данных, размещается в таблицах. Таблицы могут быть связаны между собой. Количество таблиц в базе данных не ограничено и зависит от сложности решаемой задачи.

Для создания таблицы в языке SQL используется оператор CREATE TABLE. В простейшем случае общая форма оператора CREATE TABLE следующая

CREATE TABLE Table_Name (
    Field_Name_1 Type_1,
    Field_Name_2 Type_2,
    ...
    Field_Name_N Type_N
)

здесь

• Table_Name – имя таблицы базы данных. Если в базе данных есть таблица с таким именем, то возникнет ошибка;
• Field_Name_1, Field_Name_2, Field_Name_N – имена полей (столбцов) таблицы базы данных. Имя каждого поля должно быть уникальным. В разных   таблицах имена полей могут совпадать;
• Type_1, Type_2, Type_N – соответственно типы полей Field_Name_1, Field_Name_2, Field_Name_N такие как INTEGER, DECIMAL, DATE и другие.

На поля Field_Name_1, Field_Name_2, Field_Name_N могут накладываться ограничения. Каждое ограничение указывается после имени поля. В этом случае общая форма оператора CREATE TABLE выглядит примерно следующим образом:

CREATE TABLE Table_Name (
    Field_Name_1 Type_1 Attribute_1,
    Field_Name_2 Type_2 Attribute_2,
    ...
    Field_Name_N Type_N Attribute_N
)

здесь

• Attribute_1, Attribute_2, Attribute_N – ограничения, накладываемые на поля Field_Name_1, Field_Name_2, Field_Name_N. Ограничения задаются одним из возможных слов: NULL, NOT NULL, UNIQUE, CHECK, PRIMARY KEY, FOREIGN KEY и других.

Рассмотрение существующих полей атрибутов в языке SQL не является предметом изучения данной темы.

  ⇑

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

2.1. Пример создания простейшей таблицы учета товаров в магазине

Условие задачи.

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

Название товара	Стоимость закупки, грн.	Количество, штук	Дата получения	Примечание
…	…	…	…	…

1. Таблица обязана содержать первичный ключ и обеспечивать уникальность записей.

Решение.

1. Для обеспечения уникальности записей в таблице нужно создать дополнительное поле, которое будет являться счетчиком (автоинкрементом). Название поля – ID_Product. Это поле есть первичным ключом. Также это поле имеет ограничение NOT NULL (непустое поле).
2. Следующим шагом решения есть назначение имен полям таблицы. В связи с тем, что не все системы управления базами данных (СУБД) поддерживают символы кириллицы, имена полей таблицы будут задаваться латинскими символами. В нашем случае формируются имена и типы данных, сформированные в следующей таблице:

Название поля в условии задачи	Название поля на языке SQL	Тип поля	Объяснение
ID_Product	ID_Product	INTEGER	Первичный ключ, счетчик, NOT NULL
Название товара	[Name]	VARCHAR(100)	Строковый тип переменной длины максимум до 100 символов
Стоимость закупки	Price	DECIMAL(15, 2)	Точность равна 15 знакам, масштаб равен 2 знакам после запятой
Количество штук	[Count]	INTEGER	Целое число
Дата получения	[Date]	DATE
Примечание	Note	VARCHAR(200)

1. Написание кода на языке SQL с учетом особенностей предыдущих шагов. Текст программы создания таблицы на языке SQL следующий

CREATE TABLE [Product] (
    [ID_Product] Integer Not Null Primary Key,
    [Name] VarChar(100) ,
    [Price] Decimal(15, 2),
    [Count] Integer,
    [Date] Date,
    [Note] VarChar(200)
)

В Microsoft SQL Server допускается задавать имена полей без их обрамления в квадратные скобки []. То есть, предыдущая команда может быть переписана следующим образом

CREATE TABLE Product (
    ID_Product Integer Not Null Primary Key,
    Name VarChar(100) ,
    Price Decimal(15, 2),
    Count Integer,
    Date Date,
    Note VarChar(200)
)

В приведенном выше коде для поля ID_Product задаются ограничения Not Null и Primary Key. В результате запуска программы на SQL будет создана следующая таблица

ID_Product	Name	Price	Count	Date	Note
…	…	…	…	…	…

На рисунке 1 отображаются поля созданной таблицы в Microsoft SQL Server Management Studio.

Рисунок 1. Этапы создания таблицы Product в Microsoft SQL Server Management Studio 18: 1 – создание файла; 2 – набор SQL-запроса; 3 – запуск запроса на выполнение; 4 – результирующая таблица

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

CREATE TABLE Product (
    Name VarChar(100) ,
    Price Decimal(15, 2),
    Count Integer,
    Date Date,
    Note VarChar(200)
)

  ⇑

2.2. Пример создания таблицы учета телефонов абонентов

Условие задачи.

Используя средства языка SQL (T-SQL) создать таблицу учета телефонов абонентов.

Name	Address	Phone Number 1	Phone Number 2	Phone Number 3
Ivanov I.I.	New York	123456	067-1234567	–
Johnson J.	Kiev	789012	033-7777778	102
Petrenko P.P.	Warshaw	044-2521412	–	–
…	…	…	…	…

Обеспечить уникальность и корректное сохранение записей таблицы.

Решение.

Для обеспечения уникальности записей нужно создать дополнительное поле – счетчик. Это поле будет увеличивать свое значение на 1 при каждом добавлении новой записи. Если запись будет удаляться, текущее максимальное значение счетчика не будет уменьшаться. Таким образом, все числовые значения этого поля будут различаться между собой (будут уникальными). В нашем случае добавляется поле ID_Subscriber. Это поле не допускает нулевые значения (NULL).

Для полей Name и [Phone Number 1] целесообразно задать ограничение (атрибут) NOT NULL. Это означает, что в эти поля обязательно нужно ввести значение. Это логично, поскольку абонент в базе данных должен иметь как минимум имя и хотя бы один номер телефона.

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

Название поля	Тип данных	Объяснение
ID_Subscriber	INTEGER	Первичный ключ, счетчик, NOT NULL
Name	VARCHAR(50)	Фамилия и имя абонента
Address	VARCHAR(100)	Адрес
[Phone Number 1]	VARCHAR(20)	NOT NULL
[Phone Number 2]	VARCHAR(20)
[Phone Number 3]	VARCHAR(20)

Учитывая вышесказанное, команда CREATE TABLE на языке Transact-SQL (T-SQL) будет выглядеть следующим образом

Create Table Subscriber (
    ID_Subscriber Int Not Null Primary Key,
    [Name] VarChar(50) Not Null,
    [Address] VarChar(100),
    [Phone Number 1] VarChar(20) Not Null,
    [Phone Number 2] VarChar(20),
    [Phone Number 3] VarChar(20)
)

В запросе имена полей

[Phone Number 1]
[Phone Number 2]
[Phone Number 3]

обязательно должны быть в квадратных скобках [], поскольку имена состоят из нескольких слов (между словами есть символ пробела).

На рисунке 2 показаны этапы создания таблицы в системе Microsoft SQL Server Management Studio.

Рисунок 2. Окно Microsoft SQL Server Management Studio. Этапы формирования запроса: 1 — создание файла «SQL Query 2.sql»; 2 — набор SQL-запроса; 3 — выполнение; 4 — результирующая таблица

  ⇑

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

Условие задачи

Используя язык SQL сделать таблицу Account, в которой ведется учет начисленной заработной платы в некой организации. Образец таблицы следующий

Name	Position	Accrued salary	Date of employment	Gender
Johnson J.	Manager	3200.00	01.02.2128	M
Petrova M.P.	Clerk	2857.35	02.03.2125	F
Williams J.	Secretary	3525.77	01.08.2127	F
Wilson K.	Recruiter	1200.63	22.07.2125	F
…	…	…	…	…

Таблицу реализовать так, чтобы обеспечивалась уникальность записей.

Решение.

Чтобы обеспечить уникальность записей, создается дополнительное поле-счетчик ID_Account типа Int. Это поле целесообразно выбрать первичным ключом, если нужно будет использовать данные этой таблицы в других связанных таблицах.

После модификации поля таблицы будут иметь следующие свойства.

Название поля (атрибут)	Тип данных	Дополнительные объяснения
ID_Account	Int	Автоинкремент (счетчик), первичный ключ (Primary Key), ненулевое поле (Not Null), обеспечивает уникальность записей
[Name]	VARCHAR(50)	Фамилия и имя, не нулевое поле (Not Null)
Position	VARCHAR(100)	Должность, не нулевое поле (Not Null)
Salary	DECIMAL	Тип, предназначенный для сохранения денежных величин
[Employment Date]	DATE	Дата, не нулевое поле (Not Null)
Gender	CHAR(1)	Стать, не нулевое поле (Not Null)

В модифицированной таблице поле Salary допускает нулевые (Null) значения. Таким случаем может быть, например, когда человек принят на работу, но заработная плата ему еще не начислена. В данной ситуации временно устанавливается Null-значение. Все остальные поля обязательны для заполнения.

Запрос на язык SQL, создающий вышеприведенную таблицу имеет вид

/* Create the Account table */
Create Table Account (
    ID_Account Int Not Null Primary Key,
    [Name] VarChar(50) Not Null,
    [Position] VarChar(100) Not Null,
    Salary Decimal Null,
    [Employment Date] Date Not Null,
    Gender Char(1)
)

Результат выполнения SQL-запроса показан на рисунке 3

Рисунок 3. Результат выполнения запроса на языке Transact-SQL (T-SQL)

  ⇑

---

Связанные темы

• Microsoft SQL Server Management Studio 18. Пример создания простейшего запроса
• Модификация таблиц. Оператор ALTER TABLE. Примеры

  ⇑

---

In the SQL database for creating a table, we use a command called CREATE TABLE.

SQL CREATE TABLE Statement

A Table is a combination of rows and columns. For creating a table we have to define the structure of a table by adding names to columns and providing data type and size of data to be stored in columns.

Syntax:

CREATE table table_name

(

Column1 datatype (size),

column2 datatype (size),

.

.

columnN datatype(size)

);

Here table_name is name of the table, column is the name of column

SQL CREATE TABLE Example

Let us create a table to store data of Customers, so the table name is Customer, Columns are Name, Country, age, phone, and so on. 

CREATE TABLE Customer(
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(50),
    LastName VARCHAR(50),
    Country VARCHAR(50),
    Age int(2),
  Phone int(10)
);

Output:

Insert Data  into Table

To add data to the table, we use INSERT INTO, the syntax is as shown below:

Syntax:

//Below query adds data in specific column, (like Column1=Value1)//

Insert into Table_name(Column1, Column2, Column3)

Values (Value1, value2, value3);

//Below query adds data in table in sequence of column name(Value1 will be 

added in Column1 and so on)//

Insert into Table_name

Values (Value1, value2, value3);

//Adding multiple data in the table in one go//

Insert into Table_name

Values (Value01, value02, value03),

(Value11, value12, value13),

(Value21, value22, value23),

(ValueN1, valueN2, valueN3)

Example Query

This query will add data in the table named Subject 

-- Insert some sample data into the Customers table
INSERT INTO Customer (CustomerID, CustomerName, LastName, Country, Age, Phone)
VALUES (1, 'Shubham', 'Thakur', 'India','23','xxxxxxxxxx'),
       (2, 'Aman ', 'Chopra', 'Australia','21','xxxxxxxxxx'),
       (3, 'Naveen', 'Tulasi', 'Sri lanka','24','xxxxxxxxxx'),
       (4, 'Aditya', 'Arpan', 'Austria','21','xxxxxxxxxx'),
       (5, 'Nishant. Salchichas S.A.', 'Jain', 'Spain','22','xxxxxxxxxx');

Output:

Create a Table Using Another Table

We can also use CREATE TABLE to create a copy of an existing table. In the new table, it gets the exact column definition all columns or specific columns can be selected.

If an existing table was used to create a new table, by default the new table would be populated with the existing values ​​from the old table.

Syntax:

CREATE TABLE new_table_name AS

    SELECT column1, column2,…

    FROM existing_table_name

    WHERE ….;

Query:

CREATE TABLE SubTable AS
SELECT CustomerID, CustomerName
FROM customer;

Output:

• We can use * instead of column name to copy whole table to another table.

Note : In this query another table will be created with all the columns instead of just two columns.

Syntax :

CREATE TABLE new_table_name AS

    SELECT *

    FROM existing_table_name

    WHERE ….;

Query :

CREATE TABLE customer_copy AS SELECT * FROM customer;

Output :

• You can also use LIMIT to insert specific number of records from old table. In below example it will populate new table with first three records.

Syntax :

CREATE TABLE <new_table_name> AS SELECT * FROM <old_table_name> LIMIT <numeric_value>;

Query :

CREATE TABLE customer_copy AS SELECT * FROM customer LIMIT 3;

Output :

This article is contributed by Saylli Walve. If you like GeeksforGeeks and would like to contribute, you can also write an article using write.geeksforgeeks.org or mail your article to review-team@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks. Please write comments if you find anything incorrect, or if you want to share more information about the topic discussed above.

Last Updated :
30 Aug, 2023

Like Article

Save Article

Creating database tables in SQL is one of the most common tasks a developer or DBA does in a database. Learn how to create tables, what the syntax is, and see some examples in this article.

This guide applies to Oracle, SQL Server, MySQL, and PostgreSQL.

What Is The Create Table Command Used For?

The SQL CREATE TABLE command is used to create a database table.

It can be used to create different types of database tables, such as temporary tables. However, in this article, I’ll only cover the regular database table.

The syntax for the SQL create table statement is:

CREATE [schema_name.]table_name (
  column_name data_type [NULL | NOT NULL] [inline_constraint] [DEFAULT default_value],
  ...
  out_of_line_constraints
);

The parameters or values mentioned in this syntax are:

schema_name

This is the schema that the table will be created under. It needs to be followed by a period and then the table name. It’s optional.

table_name

This is the name of the table that you want to create.

It’s a good idea not to include spaces in the table name, to make development easier. I also recommend using singular table names (read more on singular vs plural here).

column_name

This is the name of each column that can be stored in the database table. It can also be up to 30 characters.

data_type

The data type that the column relates to. Many data types require brackets after the data type to specify the number of characters or digits. You can find a full list of data types on Oracle’s website.

NULL|NOT NULL

This is used to specify if the column values are allowed to be NULL or not.

• NULL means the column can be set to NULL. If you don’t specify the value when inserting, or specify the value of NULL, the statement won’t cause an error and the data will be inserted.
• NOT NULL means the column cannot be set to NULL. If you don’t specify the value when inserting, or specify the value of NULL, the statement will cause an error.

inline_constraint

You can define a constraint here on this column. This is what is called an “inline constraint”, because you define it on the same line as the column.

The difference between inline and out of line constraints is that you can’t define a constraint with multiple columns inline, and NOT NULL constraints can only be defined inline.

default_value

If you want to have a default value on this column when it is not specified during an INSERT statement, you can specify it here. An example of this would be a created_date column – it doesn’t need to have its value specified when you insert it, so the default could be SYSDATE.

out_of_line_constraints

Here, you can specify table constraints, which are called out of line constraints. They work in the same way as inline constraints, except you can’t specify NOT NULL constraints here. For example, you may want to add a primary key or foreign key here.

Now, there are a whole lot of parameters that are not mentioned here, because they are not relevant for most database users.

For the complete syntax for the CREATE TABLE statement, refer to the database manuals:

• Oracle
• SQL Server
• MySQL
• PostgreSQL

How Long Can A Table Name Be in SQL?

The length of a table name depends on the database you’re using:

• Oracle (before v12.2): 30 characters
• Oracle (after v12.2): 128 characters
• SQL Server: 128 characters
• MySQL: 64 characters
• PostgreSQL: 63 characters

Create Table Primary Key Syntax

You can specify a primary key on a table when you create in two ways:

1. Next to the column data type when you declare it on the same line (an inline constraint)
2. At the end of all column declarations (an out of line constraint)

The method you choose would be dependent on your team’s standards and whether or not you need to have two or more columns in your primary key (you can only specify one column in an inline constraint).

To declare a primary key inline using a CREATE TABLE statement:

CREATE table_name (
  column_name data_type PRIMARY KEY,
  ...
);

That’s it. You just add the words PRIMARY KEY after the data type. They are separate words – there’s no underscore or hyphen between them.

The out of line method of declaring a primary key in a Create Table command is a bit different.

CREATE table_name (
  column_name data_type,
  ...
  CONSTRAINT pk_tbl1 PRIMARY KEY (column_name)
);

You need to add it after all of your column definitions. You also need to start with the word CONSTRAINT.

Then, give the primary key a name. You only need to do this for out of line constraints. In this example, I’ve given it the name of “pk_tbl1”, to indicate that it is a primary key, and the “tbl1” would be the name of the table.

Inside the brackets after the word PRIMARY KEY, you specify the column names. If there is more than one, separate them by a comma.

Create Table Foreign Key Syntax

Like the primary key definition, you can declare a foreign key both inline and out of line in the SQL Create Table command.

Inline foreign keys are declared like this:

CREATE table_name (
  column_name data_type REFERENCES other_table_name(other_column_name),
  ...
);

You use the word REFERENCES, then the name of the table that the foreign key refers to, then within brackets you specify the column that the foreign key links to.

The other method of adding a foreign key using the CREATE TABLE command is the out of line method.

CREATE table_name (
  column_name data_type,
  ...
  CONSTRAINT fk_tbl1_tbl2 FOREIGN KEY (this_tables_column)
  REFERENCES other_table_name (other_column_name)
);

You need to start with the word CONSTRAINT, then the name of the foreign key. The name needs to be unique across the database, so I like to start with the term “fk”, then the two tables I am referring to.

Then, you specify the words FOREIGN KEY, then the name of the other table within brackets. Then you add the word REFERENCES, then within brackets, you specify the column name from the table you’re referring to (which is probably the primary key).

Is There A CREATE TABLE IF NOT EXISTS Command?

Some databases have CREATE TABLE IF NOT EXISTS, others don’t.

• Oracle: No, but there is a workaround.
• SQL Server: No, but there is a workaround.
• MySQL: Yes, there is.
• PostgreSQL: Yes, there is

As mentioned in this StackOverflow answer:

Normally, it doesn’t make a lot of sense to check whether a table exists or not because objects shouldn’t be created at runtime and the application should know what objects were created at install time. If this is part of the installation, you should know what objects exist at any point in the process so you shouldn’t need to check whether a table already exists.

Oracle CREATE TABLE IF NOT EXISTS Equivalent

To check if a table exists before creating it, you’ll need to write a PL/SQL block. There are a few ways you can check:

1. You can attempt to create a table, and catch the error that appears (ORA-00955: name is already in use by an existing object).
2. You can query the USER_TABLES view to find a count where the table name matches, and check if the value is > 0.
3. You can drop the table and then create it again.

I’ve written about finding a list of tables in Oracle here.

SQL Server CREATE TABLE IF NOT EXISTS Equivalent

To check if a table exists before creating it, you can enclose the CREATE TABLE statement inside an IF statement.

IF NOT EXISTS (
  SELECT *
  FROM sys.tables t
  INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
  WHERE s.name = 'my_schema_name'
  AND t.name = 'table_name'
)
CREATE TABLE table_name (
  column_name data_type
);

MySQL CREATE TABLE IF NOT EXISTS

To create a table if it does not exist in MySQL, you simply add IF NOT EXISTS to the CREATE TABLE statement:

CREATE TABLE IF NOT EXISTS table_name (
  column_name data_type
);

PostgreSQL CREATE TABLE IF NOT EXISTS

In PostgreSQL 9.1, this feature exists. You can simply add IF NOT EXISTS to the CREATE TABLE statement:

CREATE TABLE IF NOT EXISTS table_name (
  column_name data_type
);

For earlier versions, one workaround is to use a function. But if you are creating a function to create a table if it doesn’t exist, perhaps there’s a better approach to your problem you can take.

CREATE TABLE AS SELECT (CTAS)

SQL allows you to create a table based on a SELECT statement.

This is a very useful feature of the database.

It’s great for development – if you want to copy an existing table and use it for testing or development purposes without changing the real table. Or, if you want a similar table with the same structure.

The syntax for using this command is:

CREATE TABLE table_name AS (
SELECT select_query
);

It’s also referred to as CTAS.

You specify the table_name for your new table, as well as the SELECT query to base it from.

If you want all records to be copied to the new table, you could specify SELECT * FROM old_table.

You can use a WHERE clause to restrict the values to be copied across.

Alternatively, you can create your table like normal, without the AS SELECT, and use an INSERT INTO SELECT to populate the records into the table.

CREATE TABLE AS SELECT Without Copying Data

You can also use the CREATE TABLE AS SELECT to copy a table’s structure without any of the data.

It’s easier than trying to generate a create table script from an existing table.

To do this, you just adjust your WHERE clause in the SELECT query to ensure there are no rows returned.

How do you guarantee this?

By adding something that is clearly evaluated to FALSE.

A common example is WHERE 1=0. Or WHERE 1=2. Really, anything involving two numbers that are not equal will work.

So, the SELECT statement will return the column names and data types, but no data. When the table is created, it will have no data in it.

The syntax for this would be:

CREATE TABLE table_name AS (
SELECT * FROM old_table WHERE 1=0
);

Oracle CREATE TABLE Errors and Exceptions

These are some of the errors and exceptions that may appear when you’re creating a table. As a database developer, you’ll get these kinds of errors all the time!

Exception: ORA-00955: name is already used by an existing object

Reason: You’re attempting to create a table with a name that is already being used.

Resolution: Use a different name for your table, or drop the table with the existing name. Or, use a different schema for your table if that’s applicable.

Exception: ORA-02260: table can have only one primary key

Reason: You’re attempting to add a primary key to a table that already has one.

Resolution: Review your SQL CREATE TABLE statement and remove all but one PRIMARY KEY definition.

Exception: ORA-02267: column type incompatible with referenced column type

Reason: The column you’re referring to is not compatible with the column you’re defining it on. This could happen when defining a foreign key.

Resolution: Make sure the data types are the same for both columns. And, make sure you’re not referring to the same table for the foreign key (it should be a different table).

Exception: ORA-00904: : invalid identifier

Reason: There are many reasons for this error, but it’s usually a syntax error.

Resolution: Check that you have all of your commas and brackets in the right places in your statement.

Exception: ORA-00957: duplicate column name

Reason: You have two columns with the same name.

Resolution: Rename one of your columns to make sure it is not a duplicate. Also, check that your commas and brackets are all in the right places.

CREATE TABLE Examples

Alright, now it’s time to look at some examples of creating a table.

These CREATE TABLE examples cover all of the topics I’ve mentioned in this article. It usually helps to see examples with data and real names, rather than syntax.

Each of the examples demonstrates the SQL using syntax for Oracle, SQL Server, MySQL, and PostgreSQL.

Example 1 – Basic Table

This example uses a CREATE TABLE statement that creates a simple table with a couple of columns.

Oracle

CREATE TABLE example1 (
  table_id NUMBER(10),
  first_name VARCHAR2(50)
);

SQL Server

CREATE TABLE example1 (
  table_id INT,
  first_name VARCHAR(50)
);

MySQL

CREATE TABLE example1 (
  table_id INT(10),
  first_name VARCHAR(50)
);

Postgres

CREATE TABLE example1 (
  table_id INTEGER,
  first_name VARCHAR(50)
);

Example 2 – Large Table

This CREATE TABLE example includes many different columns and data types.

Oracle

CREATE TABLE example2 (
    table_id NUMBER(10),
    first_name VARCHAR2(50),
    last_name VARCHAR2(200),
    registration_date DATE,
    registration_category CHAR(1),
    upload_data BLOB
  );

SQL Server

CREATE TABLE example2 (
    table_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(200),
    registration_date DATE,
    registration_category CHAR(1),
    upload_data BLOB
  );

MySQL

CREATE TABLE example2 (
    table_id INT(10),
    first_name VARCHAR(50),
    last_name VARCHAR(200),
    registration_date DATE,
    registration_category CHAR(1),
    upload_data BLOB
  );

Postgres

CREATE TABLE example2 (
    table_id INTEGER,
    first_name VARCHAR(50),
    last_name VARCHAR(200),
    registration_date DATE,
    registration_category CHAR(1),
    upload_data BLOB
  );

Example 3 – Large Table with NOT NULL and DEFAULT

This CREATE TABLE example is similar to Example 2 but includes several NOT NULL constraints and some default values.

Oracle

CREATE TABLE example3 (
    table_id NUMBER(10),
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(200) NOT NULL,
    registration_date DATE DEFAULT SYSDATE,
    registration_category CHAR(1) DEFAULT 'B'
  );

SQL Server

CREATE TABLE example3 (
    table_id INT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(200) NOT NULL,
    registration_date DATE DEFAULT GETDATE(),
    registration_category CHAR(1) DEFAULT 'B'
  );

MySQL

CREATE TABLE example3 (
    table_id INT(10),
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(200) NOT NULL,
    registration_date DATE DEFAULT CURRENT_DATE,
    registration_category CHAR(1) DEFAULT 'B'
  );

Postgres

CREATE TABLE example3 (
    table_id INTEGER,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(200) NOT NULL,
    registration_date DATE DEFAULT CURRENT_DATE,
    registration_category CHAR(1) DEFAULT 'B'
  );

Note: Prior to MySQL v8.0.13, using a function or expression (such as CURRENT_DATE) as a column’s default value was not supported. From the MySQL documentation:

With one exception, the default value specified in a DEFAULT clause must be a literal constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that, for TIMESTAMP and DATETIME columns, you can specify CURRENT_TIMESTAMP as the default.

As of 8.0.13 this now works. You can set CURRENT_DATE as the default for a date column.

Example 4 – Inline Primary Key

This example of the CREATE TABLE statement uses a primary key that is defined inline.

The syntax is the same for all databases, only the data types of the columns are different.

Oracle

CREATE TABLE example4 (
    table_id NUMBER(10) PRIMARY KEY,
    first_name VARCHAR2(50)
  );

SQL Server

CREATE TABLE example4 (
    table_id INT PRIMARY KEY,
    first_name VARCHAR(50)
  );

MySQL

CREATE TABLE example4 (
    table_id INT(10) PRIMARY KEY,
    first_name VARCHAR(50)
  );

Postgres

CREATE TABLE example4 (
    table_id INTEGER PRIMARY KEY,
    first_name VARCHAR(50)
  );

Example 5 – Inline Primary Key and Foreign Key

This CREATE TABLE example uses an inline primary key and inline foreign key.

The syntax is the same for all databases, only the data types of the columns are different.

Oracle

CREATE TABLE example5 (
    table_id NUMBER(10) PRIMARY KEY,
    example_table_id NUMBER(10) REFERENCES example4(table_id),
    first_name VARCHAR2(50),
    last_name VARCHAR2(200)
  );

SQL Server

CREATE TABLE example5 (
    table_id INT PRIMARY KEY,
    example_table_id INT REFERENCES example4(table_id),
    first_name VARCHAR(50),
    last_name VARCHAR(200)
  );

MySQL

CREATE TABLE example5 (
    table_id INT(10) PRIMARY KEY,
    example_table_id INT(10) REFERENCES example4(table_id),
    first_name VARCHAR(50),
    last_name VARCHAR(200)
  );

Postgres

CREATE TABLE example5 (
    table_id INTEGER PRIMARY KEY,
    example_table_id INTEGER REFERENCES example4(table_id),
    first_name VARCHAR(50),
    last_name VARCHAR(200)
  );

Example 6 – Out of Line Primary Key and Foreign Key

This Oracle CREATE TABLE example declares a primary key and foreign key out of line (at the end of the column declarations).

The syntax is the same for all databases, only the data types of the columns are different.

Oracle

CREATE TABLE example6 (
    table_id NUMBER(10),
    example_table_id NUMBER(10),
    first_name VARCHAR2(50),
    last_name VARCHAR2(200),
    CONSTRAINT pk_ex6 PRIMARY KEY (table_id),
    CONSTRAINT fk_ex6_ex5 FOREIGN KEY (example_table_id) REFERENCES example5 (table_id)
  );

SQL Server

CREATE TABLE example6 (
    table_id INT,
    example_table_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(200),
    CONSTRAINT pk_ex6 PRIMARY KEY (table_id),
    CONSTRAINT fk_ex6_ex5 FOREIGN KEY (example_table_id) REFERENCES example5 (table_id)
  );

MySQL

CREATE TABLE example6 (
    table_id INT(10),
    example_table_id INT(10),
    first_name VARCHAR(50),
    last_name VARCHAR(200),
    CONSTRAINT pk_ex6 PRIMARY KEY (table_id),
    CONSTRAINT fk_ex6_ex5 FOREIGN KEY (example_table_id) REFERENCES example5 (table_id)
  );

Postgres

CREATE TABLE example6 (
    table_id INTEGER,
    example_table_id INTEGER,
    first_name VARCHAR(50),
    last_name VARCHAR(200),
    CONSTRAINT pk_ex6 PRIMARY KEY (table_id),
    CONSTRAINT fk_ex6_ex5 FOREIGN KEY (example_table_id) REFERENCES example5 (table_id)
  );

Example 7 – More Constraints

This example uses CREATE TABLE to declare a table with a primary key, foreign key, unique constraint, and check constraint.

Oracle

CREATE TABLE example7 (
    table_id NUMBER(10),
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(200) NOT NULL,
    registration_date DATE DEFAULT SYSDATE,
    registration_category CHAR(1) DEFAULT 'B',
    registration_number NUMBER(10),
    example_table_id NUMBER(10),
    CONSTRAINT pk_ex7 PRIMARY KEY (table_id),
    CONSTRAINT fk_ex7_ex5 FOREIGN KEY (example_table_id) REFERENCES example5 (table_id),
    CONSTRAINT ck_ex7_regcat CHECK (registration_category IN ('B', 'C', 'W', 'P')),
    CONSTRAINT uc_ex7_regno UNIQUE (registration_category, registration_number)
  );

SQL Server

CREATE TABLE example7 (
    table_id INT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(200) NOT NULL,
    registration_date DATE DEFAULT GETDATE(),
    registration_category CHAR(1) DEFAULT 'B',
    registration_number INT,
    example_table_id INT,
    CONSTRAINT pk_ex7 PRIMARY KEY (table_id),
    CONSTRAINT fk_ex7_ex5 FOREIGN KEY (example_table_id) REFERENCES example5 (table_id),
    CONSTRAINT ck_ex7_regcat CHECK (registration_category IN ('B', 'C', 'W', 'P')),
    CONSTRAINT uc_ex7_regno UNIQUE (registration_category, registration_number)
  );

MySQL

CREATE TABLE example7 (
    table_id INT(10),
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(200) NOT NULL,
    registration_date DATE DEFAULT CURRENT_DATE,
    registration_category CHAR(1) DEFAULT 'B',
    registration_number INT(10),
    example_table_id INT(10),
    CONSTRAINT pk_ex7 PRIMARY KEY (table_id),
    CONSTRAINT fk_ex7_ex5 FOREIGN KEY (example_table_id) REFERENCES example5 (table_id),
    CONSTRAINT ck_ex7_regcat CHECK (registration_category IN ('B', 'C', 'W', 'P')),
    CONSTRAINT uc_ex7_regno UNIQUE (registration_category, registration_number)
  );

Postgres

CREATE TABLE example7 (
    table_id INTEGER,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(200) NOT NULL,
    registration_date DATE DEFAULT CURRENT_DATE,
    registration_category CHAR(1) DEFAULT 'B',
    registration_number INTEGER,
    example_table_id INTEGER,
    CONSTRAINT pk_ex7 PRIMARY KEY (table_id),
    CONSTRAINT fk_ex7_ex5 FOREIGN KEY (example_table_id) REFERENCES example5 (table_id),
    CONSTRAINT ck_ex7_regcat CHECK (registration_category IN ('B', 'C', 'W', 'P')),
    CONSTRAINT uc_ex7_regno UNIQUE (registration_category, registration_number)
  );

Example 8 – Create Table as Select with All Columns

This example uses the Create Table as Select to create a table from another table, using all columns.

The syntax is the same for Oracle, SQL Server, MySQL, and PostgreSQL.

CREATE TABLE example8 AS (
  SELECT *
  FROM example7
);

Example 9 – Create Table as Select with Some Columns

This example uses the Create Table as Select to create a table from another table, using only some of the columns.

The syntax is the same for Oracle, SQL Server, MySQL, and PostgreSQL.

CREATE TABLE example9 AS (
  SELECT table_id, first_name, last_name
  FROM example7
);

Example 10 – Create Table as Select with No Data

This example uses the Create Table as Select to create a table from another table, but no data is added to the new table.

The syntax is the same for Oracle, SQL Server, MySQL, and PostgreSQL.

CREATE TABLE example10 AS (
  SELECT table_id, first_name, last_name
  FROM example7
  WHERE 1=0
);

Summary of Differences Between Vendors

This table shows a summary of differences in the CREATE TABLE statement between different databases.

Criteria	Oracle	SQL Server	MySQL	PostgreSQL
Max Table Name Length	128	128	64	63
Create Table If Not Exists?	No, but there is a workaround	No, but there is a workaround	Yes	Yes
Default Date to Function	Yes	Yes	Yes, from 8.0.13	Yes

Conclusion

So, there’s your guide to the SQL CREATE TABLE statement. If you have any questions on any of the parts of a CREATE TABLE statement, let me know in the comments below.