Базы данных
и язык SQL
Форум
Тесты онлайн

Оператор SQL INSERT для вставки данных в таблицу БД

Навигация по уроку Связанные темы
Назад<<<СодержаниеВперёд>>>

Оператор SQL INSERT предназначен для вставки значений столбцов в таблицу баз данных. В результате его применения в таблице базы данных появляется новая строка.

Оператор INSERT имеет следующий синтаксис:

INSERT INTO ИМЯ_ТАБЛИЦЫ [(ИМЕНА СТОЛБЦОВ)] VALUES (ВСТАВЛЯЕМЫЕ_ЗНАЧЕНИЯ)

Квадратные скобки [], в которые заключен элемент запроса (ИМЕНА СТОЛБЦОВ), означают, что этот элемент является необязательным.

Вставка значений в таблицу с указанием или без указания столбцов

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

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

Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке.

А скрипт для создания базы данных "Портал объявлений 1", её таблицы и заполения таблицы данных - в файле по этой ссылке.

Для использующих же MySQL приводим содержание оператора CREATE для создания таблицы:

CREATE TABLE ADS ( Id INT(11) NOT NULL DEFAULT '100', Category varchar(25) DEFAULT 'Some Category', Part varchar(25) DEFAULT 'Some Part', Units INT(5) DEFAULT NULL, Money INT(10) DEFAULT NULL, PRIMARY KEY (Id) )

Пример 1. Итак, есть база данных портала объявлений. Таблица ADS выглядит так:

IdCategoryPartUnitsMoney
1ТранспортАвтомашины11017600
2НедвижимостьКвартиры8918690
3НедвижимостьДачи5711970
4ТранспортМотоциклы13120960
5СтройматериалыДоски687140
6ЭлектротехникаТелевизоры1278255
7ЭлектротехникаХолодильники1378905
8СтройматериалыРегипс11211760
9ДосугКниги966240
10НедвижимостьДома479870
11ДосугМузыка1177605
12ДосугИгры412665

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

INSERT INTO ADS (Id, Category, Part, Units, Money) VALUES (13, 'Недвижимость', 'Гаражи', 22, 4620)

Или без указания имён столбцов:

INSERT INTO ADS VALUES (13, 'Недвижимость', 'Гаражи', 22, 4620)

MS SQL Server в ответ на такой запрос выдаст сообщение об ошибке, так как при создании таблицы было указано, что значения столбца Id являются идентификаторами и вставляются при добавлении новых строк автоматически с приращением 1. Поэтому на MS SQL Server нужно использовать следующий запрос (можете скопировать его и вставить в окно запросов):

USE adportal1; INSERT INTO ADS (Category, Part, Units, Money) VALUES ('Недвижимость', 'Гаражи', 22, 4620);

В результате выполнения запроса в таблице появится новая строка:

13НедвижимостьГаражи224620

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

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

Пример 2. Таким будет запрос на MySQL, в котором порядок следования столбцов изменён:

INSERT INTO ADS (Category, Id, Money, Part, Units) VALUES ('Недвижимость', 13, 4620, 'Гаражи', 22)

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

Если вы используете MS SQL Server, то в запросе не нужно указывать столбец Id и запрос с изменённым порядком следования столбцов будет таким:

USE adportal1; INSERT INTO ADS (Category, Money, Part, Units) VALUES ('Недвижимость', 4620, 'Гаражи', 22)

Вставка значений по умолчанию (DEFAULT) и неопределённых значений (NULL)

Заметим, что при создании таблицы было предусмотрено, что значения столбцов могут иметь значения по умолчанию (DEFAULT). На MySQL для столбца Id можно предусмотреть значение 100. На MS SQL Server со строгим автоматическим приращением идентификатора это не допускается. В остальном же всё одинаково: для стобцов Category и Part значения по умолчанию - соответственно Some Category и Some Part, для столбцов Units и Money - значения NULL. Если в запросе на вставку данных на MySQL некоторые столбцы отсутствуют, то в них будут вставлены значения по умолчанию. На MS SQL в качестве значений нужно указать DEFAULT.

Пример 3. База данных и таблица - те же.

Запрос на MySQL. Вставим новые значения, указывая лишь столбец Id и его значение:

INSERT INTO ADS (Id) VALUES (14)

Все столбцы кроме Id получили значения по умолчанию. После выполнения этого запроса новая строка будет содержать следующие данные:

14Some CategorySome PartNULLNULL

На MS SQL Server такой запрос недопустим.

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

Пример 4. Запрос на MySQL. Вставим новые значения, используя ключевое слово DEFAULT и не указывая имён столбцов:

INSERT INTO ADS VALUES (DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)

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

100Some CategorySome PartNULLNULL

Запрос на MS SQL Server (без указания столбца Id):

USE adportal1; INSERT INTO ADS VALUES (DEFAULT, DEFAULT, DEFAULT, DEFAULT)

Вместо многократного использования слова DEFAULT можно использовать конструкцию DEFAULT VALUES, которая есть во многих диалектах SQL. Следует помнить, что в MySQL эта конструкция отсутствует.

Пример 5. Вставим новые значения, используя констукцию DEFAULT VALUES (запрос можно использовать и на MS SQL Server c предваряющей конструкцией USE adportal1):

INSERT INTO ADS DEFAULT VALUES

После выполнения этого запроса новая строка будет содержать следующие данные:

след. номерSome CategorySome PartNULLNULL

Вставка значений с использованием оператора SET в MySQL

В MySQL дополнительно существует альтернативная конструкция для вставки значений в таблицу с использованием оператора SET. Она похожа на конструкцию оператора UPDATE и имеет следующий синтаксис:

INSERT INTO ИМЯ_ТАБЛИЦЫ SET ИМЯ_СТОЛБЦА_1=ЗНАЧЕНИЕ, ИМЯ_СТОЛБЦА_2=ЗНАЧЕНИЕ, ..., ИМЯ_СТОЛБЦА_N=ЗНАЧЕНИЕ

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

Пример 6. База данных и таблица - те же, что и в предыдущих примерах.

Вставим в таблицу строку, при этом столбцы Units и Money примут значения по умолчанию:

INSERT INTO ADS SET Id=13, Category='Недвижимость', Part='Гаражи'

В результате выполнения запроса в таблице появится новая строка:

13НедвижимостьГаражиNULLNULL

Использование механизма автоматического приращения при вставке данных

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

В разных диалектах SQL синтаксис автоматического приращения различается. В MySQL используется ключевое слово AUTO_INCREMENT. Таблица с использованием механизма AUTO_INCREMENT в MySQL создаётся так:

CREATE TABLE ADS ( Id INT(11) NOT NULL AUTO_INCREMENT, Category varchar(25) DEFAULT 'Some Category', Part varchar(25) DEFAULT 'Some Part', Units INT(5) DEFAULT NULL, Money INT(10) DEFAULT NULL, PRIMARY KEY (Id) )

В SQL Server используется ключевое слово IDENTITY (N, M), где N - начальное значение столца, M - шаг приращения. Так, указав IDENTITY (1, 1) мы обеспечим начальное значение первичного ключа 1 и приращение на 1 значения при каждой вставке новой строки:

CREATE TABLE ADS ( Id int IDENTITY(1, 1) PRIMARY KEY, Category varchar(25) DEFAULT 'Some Category', Part varchar(25) DEFAULT 'Some Part', Units INT(5) DEFAULT NULL, Money INT(10) DEFAULT NULL )

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

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

Для этого применяется либо однострочный, либо многострочный оператор INSERT. Рассмотрим сначала синтаксис однострочного варианта.

Пример 7. Если используется механизм автоматического приращения значений первичного ключа, то на MySQL вставить новые строки в таблицу можно, применив несколько раз оператор INSERT и указав в качестве значений первичного ключа 0 или NULL:

INSERT INTO ADS VALUES (NULL, 'Электротехника', 'Телевизоры', 127, 8255); INSERT INTO ADS VALUES (NULL, 'Электротехника', 'Холодильники', 137, 8905); INSERT INTO ADS VALUES (NULL, 'Стройматериалы', 'Регипс', 112, 11760); INSERT INTO ADS VALUES (NULL, 'Досуг', 'Книги', 96, 6240);

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

6ЭлектротехникаТелевизоры1278255
7ЭлектротехникаХолодильники1378905
8СтройматериалыРегипс11211760
9ДосугКниги966240

На MS SQL Server нет необходимости указывать значения столбца Id. Аналогичный запрос будет следующим:

USE adportal1; INSERT INTO ADS VALUES ('Электротехника', 'Телевизоры', 127, 8255); INSERT INTO ADS VALUES ('Электротехника', 'Холодильники', 137, 8905); INSERT INTO ADS VALUES ('Стройматериалы', 'Регипс', 112, 11760); INSERT INTO ADS VALUES ('Досуг', 'Книги', 96, 6240);

В MySQL и SQL Server существует многострочный оператор INSERT. Его отличие от однострочного варианта в том, что для вставки нескольких строк он используется один раз, а после ключевого слова VALUES указывается не один, а несколько списков значений добавляемых строк.

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

Запрос на MySQL:

INSERT INTO ADS VALUES (NULL, 'Электротехника', 'Телевизоры', 127, 8255), (NULL, 'Электротехника', 'Холодильники', 137, 8905), (NULL, 'Стройматериалы', 'Регипс', 112, 11760), (NULL, 'Досуг', 'Книги', 96, 6240);

Запрос на MS SQL Server:

USE adportal1; INSERT INTO ADS VALUES ('Электротехника', 'Телевизоры', 127, 8255), ('Электротехника', 'Холодильники', 137, 8905), ('Стройматериалы', 'Регипс', 112, 11760), ('Досуг', 'Книги', 96, 6240);

Результат применения - тот же, что и в предыдущем примере.

Примеры запросов к базе данных "Портал объявлений-1" есть также в уроках об операторах UPDATE, DELETE, HAVING и UNION.

Поделиться с друзьями

Назад<<<СодержаниеВперёд>>>