SQL INSERT INTO: добавление строк в таблицу

Урок 8.1 · Время чтения: ~7 мин · Задачи для практики ↓

Оператор INSERT INTO в SQL добавляет в таблицу новые строки. В этом уроке вы изучите синтаксис INSERT, узнаете, почему всегда стоит указывать столбцы, как вставлять несколько строк сразу, как копировать данные с помощью INSERT INTO ... SELECT и как заполняются NULL, DEFAULT и автоинкрементные столбцы.

До сих пор каждый запрос в этом курсе читал данные с помощью SELECT. С этого модуля вы начинаете данные изменять. INSERT, UPDATE и DELETE образуют язык манипулирования данными (DML); INSERT - тот из них, что создаёт новые строки.

Учебные базы данных на SQLtest доступны только для чтения, поэтому там эти операторы вернут ошибку доступа. Примеры используют схему Sakila, чтобы показать синтаксис и правила, которые соблюдает реальная база данных.

Синтаксис INSERT

Со списком столбцов (рекомендуется)

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

Значения сопоставляются со столбцами по позиции: первое значение попадает в первый указанный столбец и так далее. Столбцы, которых нет в списке, получают значение по умолчанию или NULL.

Без списка столбцов

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

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

Вставка одной строки

INSERT INTO actor (first_name, last_name)
VALUES ('JOHNNY', 'DEPP');

Результат: новый актёр. actor_id заполняется автоматически (автоинкремент), а last_update получает текущее время из значения по умолчанию.

Правила для значений те же, что и в WHERE: текст и даты - в одинарных кавычках, числа - без кавычек.

Обязательные столбцы

Столбцу с ограничением NOT NULL без значения по умолчанию нужно передать значение. В Sakila таков столбец customer.create_date:

-- Ошибка: Field 'create_date' doesn't have a default value
INSERT INTO customer (store_id, first_name, last_name, email, address_id)
VALUES (1, 'ALICE', 'JOHNSON', 'alice.j@example.com', 5);

-- Верно: у обязательного столбца есть значение
INSERT INTO customer (store_id, first_name, last_name, email, address_id, create_date)
VALUES (1, 'ALICE', 'JOHNSON', 'alice.j@example.com', 5, NOW());

Результат: новый клиент магазина 1. customer_id генерируется автоматически, active получает значение по умолчанию 1.

Значения должны также удовлетворять внешним ключам: store_id = 1 и address_id = 5 должны существовать в таблицах store и address, иначе база данных отклонит строку.

Вставка нескольких строк

Несколько списков значений через запятую вставляют несколько строк одним оператором:

INSERT INTO actor (first_name, last_name)
VALUES ('TOM', 'HANKS'),
       ('MERYL', 'STREEP'),
       ('LEONARDO', 'DICAPRIO');

Результат: три новых актёра.

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

Значения NULL и DEFAULT

NULL записывает «нет значения» в столбец, который это допускает:

INSERT INTO customer (store_id, first_name, last_name, email, address_id, create_date)
VALUES (1, 'BOB', 'SMITH', NULL, 5, NOW());

Результат: клиент без адреса электронной почты.

Пропущенный столбец заполняется значением по умолчанию или NULL, если значения по умолчанию нет.

DEFAULT явно запрашивает значение по умолчанию - это удобно, когда перечислены все столбцы:

INSERT INTO actor (actor_id, first_name, last_name, last_update)
VALUES (DEFAULT, 'CATE', 'BLANCHETT', DEFAULT);

Результат: actor_id получает следующий номер автоинкремента, last_update - текущее время.

INSERT INTO ... SELECT

Вместо VALUES оператор INSERT может брать строки из запроса. Так данные копируют между таблицами, архивируют или загружают в таблицы отчётов:

INSERT INTO target_table (column1, column2, column3)
SELECT source_column1, source_column2, source_column3
FROM source_table
WHERE condition;

Список SELECT может сочетать столбцы, вычисления и константы. Например, если существует таблица customer_archive, в неё можно скопировать неактивных клиентов:

INSERT INTO customer_archive (customer_id, full_name, archived_at, status_note)
SELECT customer_id,
       CONCAT(first_name, ' ', last_name),
       CURRENT_DATE,
       'Auto-archived'
FROM customer
WHERE active = 0;

Результат: 15 строк, по одной на каждого неактивного клиента, с полным именем, сегодняшней датой и постоянной пометкой.

Количество и порядок столбцов SELECT должны совпадать со списком столбцов INSERT. Имена столбцов совпадать не обязаны - важны позиции.

Типичные ошибки INSERT

Ключевые выводы:

• INSERT INTO table (columns) VALUES (values) добавляет строку; значения сопоставляются со столбцами по позиции.
• Всегда указывайте столбцы: тогда оператор переживёт изменения таблицы.
• Обязательным столбцам NOT NULL без значения по умолчанию нужно передать значение; внешние ключи должны указывать на существующие строки.
• Один оператор может вставить много строк: VALUES (...), (...), (...).
• INSERT INTO ... SELECT вставляет результат запроса.
• Пропущенные столбцы получают значение по умолчанию или NULL; ключевое слово DEFAULT запрашивает значение по умолчанию явно.

Часто задаваемые вопросы

Как вставить несколько строк в SQL?

Перечислите несколько групп значений после VALUES через запятую: INSERT INTO actor (first_name, last_name) VALUES ('TOM', 'HANKS'), ('MERYL', 'STREEP');. Это работает в MySQL, MariaDB, PostgreSQL, SQL Server и SQLite.

Как скопировать строки из одной таблицы в другую?

Используйте INSERT INTO target (columns) SELECT columns FROM source WHERE condition;. SELECT должен возвращать столько же столбцов в том же порядке, что и список столбцов целевой таблицы.

Что происходит со столбцами, которые не указаны в INSERT?

Они получают значение по умолчанию. Автоинкрементный ключ получает следующий номер. Если значения по умолчанию нет, столбец получает NULL, а если он NOT NULL, оператор завершится ошибкой.