Оператор SQL INSERT для вставки данных в таблицу БД
Назад<<< | Содержание | Вперёд>>> |
Оператор SQL INSERT предназначен для вставки значений столбцов в таблицу баз данных. В результате его применения в таблице базы данных появляется новая строка.
Оператор INSERT имеет следующий синтаксис:
Квадратные скобки [], в которые заключен элемент запроса (ИМЕНА СТОЛБЦОВ), означают, что этот элемент является необязательным.
Вставка значений в таблицу с указанием или без указания столбцов
Рассмотрим подробнее применение оператора INSERT с указанием имён столбцов и без указания, остановимся на случаях, когда указывать имена столбцов всё же требуется.
Будем работать с базой данных портала объявлений. В ней есть таблица ADS, содержащая данные о объявлениях, поданных за неделю.
Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке.
А скрипт для создания базы данных "Портал объявлений 1", её таблицы и заполения таблицы данных - в файле по этой ссылке.
Для использующих же MySQL приводим содержание оператора CREATE для создания таблицы:
Пример 1. Итак, есть база данных портала объявлений. Таблица ADS выглядит так:
Id | Category | Part | Units | Money |
1 | Транспорт | Автомашины | 110 | 17600 |
2 | Недвижимость | Квартиры | 89 | 18690 |
3 | Недвижимость | Дачи | 57 | 11970 |
4 | Транспорт | Мотоциклы | 131 | 20960 |
5 | Стройматериалы | Доски | 68 | 7140 |
6 | Электротехника | Телевизоры | 127 | 8255 |
7 | Электротехника | Холодильники | 137 | 8905 |
8 | Стройматериалы | Регипс | 112 | 11760 |
9 | Досуг | Книги | 96 | 6240 |
10 | Недвижимость | Дома | 47 | 9870 |
11 | Досуг | Музыка | 117 | 7605 |
12 | Досуг | Игры | 41 | 2665 |
Для вставки новой строки в эту таблицу на MySQL используем следующий запрос:
Или без указания имён столбцов:
MS SQL Server в ответ на такой запрос выдаст сообщение об ошибке, так как при создании таблицы было указано, что значения столбца Id являются идентификаторами и вставляются при добавлении новых строк автоматически с приращением 1. Поэтому на MS SQL Server нужно использовать следующий запрос (можете скопировать его и вставить в окно запросов):
В результате выполнения запроса в таблице появится новая строка:
13 | Недвижимость | Гаражи | 22 | 4620 |
Из примера видно, что для вставки числовых значений в таблицу значения нужно указывать без кавычек, а для вставки строковых значений - в одинарных кавычках.
В запросе на вставку данных можно список столбцов можно указать не в том порядке, который задан при создании таблицы, и тогда данные следует указывать также в изменённом порядке.
Пример 2. Таким будет запрос на MySQL, в котором порядок следования столбцов изменён:
В результате выполнения запроса в таблице появится такая же новая строка, как и в примере 1.
Если вы используете MS SQL Server, то в запросе не нужно указывать столбец Id и запрос с изменённым порядком следования столбцов будет таким:
Вставка значений по умолчанию (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 и его значение:
Все столбцы кроме Id получили значения по умолчанию. После выполнения этого запроса новая строка будет содержать следующие данные:
14 | Some Category | Some Part | NULL | NULL |
На MS SQL Server такой запрос недопустим.
Поскольку все столбцы могут иметь значения по умолчанию, можно использовать в запросе на вставку данных ключевое слово DEFAULT и не указывать имена столбцов.
Пример 4. Запрос на MySQL. Вставим новые значения, используя ключевое слово DEFAULT и не указывая имён столбцов:
Теперь все столбцы получили значения по умолчанию. После выполнения этого запроса новая строка будет содержать следующие данные:
100 | Some Category | Some Part | NULL | NULL |
Запрос на MS SQL Server (без указания столбца Id):
Вместо многократного использования слова DEFAULT можно использовать конструкцию DEFAULT VALUES, которая есть во многих диалектах SQL. Следует помнить, что в MySQL эта конструкция отсутствует.
Пример 5. Вставим новые значения, используя констукцию DEFAULT VALUES (запрос можно использовать и на MS SQL Server c предваряющей конструкцией USE adportal1):
После выполнения этого запроса новая строка будет содержать следующие данные:
след. номер | Some Category | Some Part | NULL | NULL |
Вставка значений с использованием оператора SET в MySQL
В MySQL дополнительно существует альтернативная конструкция для вставки значений в таблицу с использованием оператора SET. Она похожа на конструкцию оператора UPDATE и имеет следующий синтаксис:
В подобных запросах можно указывать имена не всех столбцов, при этом не указанные столбцы принимают значения по умолчанию.
Пример 6. База данных и таблица - те же, что и в предыдущих примерах.
Вставим в таблицу строку, при этом столбцы Units и Money примут значения по умолчанию:
В результате выполнения запроса в таблице появится новая строка:
13 | Недвижимость | Гаражи | NULL | NULL |
Использование механизма автоматического приращения при вставке данных
Механизм автоматического приращения предназначен для автоматического генерирования уникальных значений первичного ключа в таблице. При его использовании не требуется проверять все значения первичного ключа, чтобы определить максимальное значение. При создании новой записи можно указать в качестве значения первичного ключа 0 или NULL и в таблицу будет автоматически вставлено значение, увеличенное на единицу по сравнению с максимальным значением.
В разных диалектах SQL синтаксис автоматического приращения различается. В MySQL используется ключевое слово AUTO_INCREMENT. Таблица с использованием механизма AUTO_INCREMENT в MySQL создаётся так:
В SQL Server используется ключевое слово IDENTITY (N, M), где N - начальное значение столца, M - шаг приращения. Так, указав IDENTITY (1, 1) мы обеспечим начальное значение первичного ключа 1 и приращение на 1 значения при каждой вставке новой строки:
Вставка нескольких строк в таблицу
Применение рассмотренного выше механизма автоматического приращения позволяет вставлять в таблицу базы данных сразу несколько строк, не заботясь об уникальности значений первичного ключа.
Для этого применяется либо однострочный, либо многострочный оператор INSERT. Рассмотрим сначала синтаксис однострочного варианта.
Пример 7. Если используется механизм автоматического приращения значений первичного ключа, то на MySQL вставить новые строки в таблицу можно, применив несколько раз оператор INSERT и указав в качестве значений первичного ключа 0 или NULL:
Допустим, перед вставкой новых строк записи в таблице завершались строкой со значением первичного ключа 5. В результате выполнения запроса в таблице появятся новые строки:
6 | Электротехника | Телевизоры | 127 | 8255 |
7 | Электротехника | Холодильники | 137 | 8905 |
8 | Стройматериалы | Регипс | 112 | 11760 |
9 | Досуг | Книги | 96 | 6240 |
На MS SQL Server нет необходимости указывать значения столбца Id. Аналогичный запрос будет следующим:
В MySQL и SQL Server существует многострочный оператор INSERT. Его отличие от однострочного варианта в том, что для вставки нескольких строк он используется один раз, а после ключевого слова VALUES указывается не один, а несколько списков значений добавляемых строк.
Пример 8. Вставим строки с теми же значениями, что и в предыдущем примере, используя многострочный оператор INSERT.
Запрос на MySQL:
Запрос на MS SQL Server:
Результат применения - тот же, что и в предыдущем примере.
Примеры запросов к базе данных "Портал объявлений-1" есть также в уроках об операторах UPDATE, DELETE, HAVING и UNION.
Поделиться с друзьями
Назад<<< | Содержание | Вперёд>>> |