Осваиваем инструкцию INSERT INTO SQL
Инструкция INSERT INTO SQL и INSERT INTRO SELECT используются для вставки новых строк в таблицу. Существует два способа использования инструкций:
- Только значения : Первый метод предусматривает указание только значений данных, которые нужно вставить без имен столбцов.
INSERT INTO имя_таблицы VALUES (значение1, значение2, значение3. ); имя_таблицы: имя таблицы. значение1, значение2. : значения первого столбца, второго столбца. для новой записи
- Имена столбцов и значения : При втором методе указываются имена столбцов и значения строк для вставки:
INSERT INTO имя_таблицы (столбец1, столбец2, столбец3. ) VALUES (значение1, значение2, значение3. ); имя_таблицы: имя таблицы. столбец1: имя первого столбцы, второго столбца . значение1, значение2. : значения первого столбца, второго столбца. для новой записи
Способ 1 ( вставка только значений ):
INSERT INTO Student VALUES ('5','HARSH','WEST BENGAL','8759770477','19');
После использования INSERT INTO SELECT таблица Student теперь будет выглядеть следующим образом:
| ROLL_NO | NAME | ADDRESS | PHONE | Age |
| 1 | Ram | Delhi | 9455123451 | 18 |
| 2 | RAMESH | GURGAON | 9562431543 | 18 |
| 3 | SUJIT | ROHTAK | 9156253131 | 20 |
| 4 | SURESH | Delhi | 9156768971 | 18 |
| 3 | SUJIT | ROHTAK | 9156253131 | 20 |
| 2 | RAMESH | GURGAON | 9562431543 | 18 |
| 5 | HARSH | WEST BENGAL | 8759770477 | 19 |
Способ 2 ( вставка значений только в указанные столбцы ):
INSERT INTO Student (ROLL_NO, NAME, Age) VALUES ('5','PRATIK','19');
Таблица Student теперь будет выглядеть следующим образом:
| ROLL_NO | NAME | ADDRESS | PHONE | Age |
| 1 | Ram | Delhi | 9455123451 | 18 |
| 2 | RAMESH | GURGAON | 9562431543 | 18 |
| 3 | SUJIT | ROHTAK | 9156253131 | 20 |
| 4 | SURESH | Delhi | 9156768971 | 18 |
| 3 | SUJIT | ROHTAK | 9156253131 | 20 |
| 2 | RAMESH | GURGAON | 9562431543 | 18 |
| 5 | PRATIK | null | null | 19 |
Обратите внимание, что для столбцов, значения для которых не указаны, задается null .
Использование SELECT в инструкции INSERT INTO
Можно использовать инструкцию MySQL INSERT SELECT для копирования строк из одной таблицы и их вставки в другую.
Использование этого оператора аналогично использованию INSERT INTO . Разница в том, что оператор SELECT применяется для выборки данных из другой таблицы. Ниже приведены различные способы использования INSERT INTO SELECT :
- Вставка всех столбцов таблицы : можно скопировать все данные таблицы и вставить их в другую таблицу.
INSERT INTO первая_таблица SELECT * FROM вторая_таблица; первая_таблица: имя первой таблицы. вторая_таблица: имя второй таблицы.
Мы использовали инструкцию SELECT для копирования данных из одной таблицы и инструкцию INSERT INTO для их вставки в другую.
- Вставка отдельных столбцов таблицы . Можно скопировать только те столбцы таблицы, которые необходимо вставить в другую таблицу.
INSERT INTO первая_таблица(имена_столбцов1) SELECT имена_столбцов2 FROM вторая_таблица; первая_таблица: имя первой таблицы. вторая_таблица: имя второй таблицы. имена_столбцов1: имена столбцов, разделенные запятой(,) для таблицы 1. имена_столбцов2: имена столбцов, разделенные запятой(,) для таблицы 2.
Мы использовали инструкцию SELECT для копирования данных только из выбранных столбцов второй таблицы и инструкцию INSERT INTO MySQL SELECT для их вставки в первую таблицу.
- Копирование определенных строк из таблицы . Можно скопировать определенные строки из таблицы для последующей вставки в другую таблицу с помощью условия WHERE с оператором SELECT . В этом случае нужно использовать соответствующее условие в WHERE .
INSERT INTO таблица1 SELECT * FROM таблица2 WHERE условие; таблица1: имя первой таблицы. таблица2: имя второй таблицы. условие: условие для выбора строк.
Таблица 2: LateralStudent
| ROLL_NO | NAME | ADDRESS | PHONE | Age |
| 7 | SOUVIK | DUMDUM | 9876543210 | 18 |
| 8 | NIRAJ | NOIDA | 9786543210 | 19 |
| 9 | SOMESH | ROHTAK | 9687543210 | 20 |
Способ 1 ( вставка всех строк и столбцов ):
INSERT INTO Student SELECT * FROM LateralStudent;
Этот запрос вставит все данные таблицы LateralStudent в таблицу Student . После применения INSERT INTO SQL SELECT таблица Student будет выглядеть следующим образом:
| ROLL_NO | NAME | ADDRESS | PHONE | Age |
| 1 | Ram | Delhi | 9455123451 | 18 |
| 2 | RAMESH | GURGAON | 9562431543 | 18 |
| 3 | SUJIT | ROHTAK | 9156253131 | 20 |
| 4 | SURESH | Delhi | 9156768971 | 18 |
| 3 | SUJIT | ROHTAK | 9156253131 | 20 |
| 2 | RAMESH | GURGAON | 9562431543 | 18 |
| 7 | SOUVIK | DUMDUM | 9876543210 | 18 |
| 8 | NIRAJ | NOIDA | 9786543210 | 19 |
| 9 | SOMESH | ROHTAK | 9687543210 | 20 |
Способ 2 ( вставка отдельных столбцов ):
INSERT INTO Student(ROLL_NO,NAME,Age) SELECT ROLL_NO, NAME, Age FROM LateralStudent;
Этот запрос вставит данные из столбцов ROLL_NO , NAME и Age таблицы LateralStudent в таблицу Student . Для остальных столбцов таблицы Student будет задано значение null . После применения SQL INSERT SELECT таблица будет выглядеть следующим образом:
| ROLL_NO | NAME | ADDRESS | PHONE | Age |
| 1 | Ram | Delhi | 9455123451 | 18 |
| 2 | RAMESH | GURGAON | 9562431543 | 18 |
| 3 | SUJIT | ROHTAK | 9156253131 | 20 |
| 4 | SURESH | Delhi | 9156768971 | 18 |
| 3 | SUJIT | ROHTAK | 9156253131 | 20 |
| 2 | RAMESH | GURGAON | 9562431543 | 18 |
| 7 | SOUVIK | Null | null | 18 |
| 8 | NIRAJ | Null | null | 19 |
| 9 | SOMESH | Null | null | 20 |
- Выбор определенных строк для вставки :
INSERT INTO Student SELECT * FROM LateralStudent WHERE Age = 18;
Этот запрос выберет только первую строку из таблицы LateralStudent для вставки в таблицу Student . После применения INSERT SELECT таблица будет выглядеть следующим образом:
| ROLL_NO | NAME | ADDRESS | PHONE | Age |
| 1 | Ram | Delhi | 9455123451 | 18 |
| 2 | RAMESH | GURGAON | 9562431543 | 18 |
| 3 | SUJIT | ROHTAK | 9156253131 | 20 |
| 4 | SURESH | Delhi | 9156768971 | 18 |
| 3 | SUJIT | ROHTAK | 9156253131 | 20 |
| 2 | RAMESH | GURGAON | 9562431543 | 18 |
| 7 | SOUVIK | DUMDUM | 9876543210 | 18 |
SQL-Урок 13. Добавление данных (INSERT INTO)
В предыдущих разделах мы рассматривали работу по получению данных из заранее созданных таблиц. Теперь пора разобрать, каким же образом мы можем создавать/удалять таблицы, добавлять новые записи и удалять старые.
Для этих целей в SQL есть такие операторы, как:
Начнём знакомство с данной группой операторов с оператора INSERT.
1. Добавление целых строк
Как видно из названия, оператор INSERT INTO используется для вставки (добавления) строк в таблицу базы данных.
Добавление можно осуществить несколькими путями:
Итак, чтобы добавить новую строку в таблицу, нам нужно указать название таблицы, перечислить названия колонок и указать значение для каждой колонки с помощью конструкции INSERT INTO название_таблицы (поле1, поле2. ) VALUES(значение1, значение2. ). Рассмотрим на примере.
INSERT INTO Sellers (ID, Address, City, Seller_name, Country) VALUES ('6', '1st Street', 'Los Angeles', 'Harry Monroe', 'USA') 
Можно также изменять порядок указания названий колонок, однако одновременно нужно изменять и порядок значений в параметре VALUES.
2. Добавление части строк
В предыдущем примере при использовании оператора INSERT мы явно указывали имена столбцов таблицы. Используя этот синтаксис, мы можем пропустить некоторые столбцы. Это означает, что вы вводите значение для одних столбцов и не предлагаете их для других. К примеру:
INSERT INTO Sellers (ID, City, Seller_name) VALUES ('6', 'Los Angeles', 'Harry Monroe') 
В данном примере мы не указали значения для двух столбцов Address и Country. Некоторые столбцы можно исключать из оператора ааа, если это позволяет делать определение таблицы. В этом случае должно соблюдаться одно из условий: этот столбец определен как допускающий значение null (отсутствие какого-либо значения) или в определении таблицы указанное значение по умолчанию. Это означает, что если не указано никакое значение, будет использовано значение по умолчанию. Если вы пропускаете столбец таблицы, не допускающей появления в своих строках значений NULL и не имеющего значения, определенного для использования по умолчанию, СУБД выдаст сообщение об ошибке, и эта строка не будет добавлена.
3. Добавление отобранных данных
В предыдущих примерах мы вставляем данные в таблице, прописывая их вручную в запросе. Однако оператор INSERT INTO позволяет автоматизировать этот процесс, если мы хотим вставлять данные из другой таблицы. Для этого в SQL существует такая кострукция как INSERT INTO. SELECT. Данная конструкция позволяет одновременно выбирать данные из одной таблицы и вставить их в другую. Допустим, мы имеем еще одну таблицу Sellers_EU с перечнем продавцов нашего товара в Европе и нам нужно их добавить в общую таблицу Sellers. Структура этих таблиц одинакова (то же количество колонок и те же их названия), однако другие данные. Для этого мы можем прописать следующий запрос:
INSERT INTO Sellers (ID, Address, City, Seller_name, Country) SELECT ID, Address, City, Seller_name, Country FROM Sellers_EU Следует обратить внимание, чтобы значения внутренних ключей не повторялись (поле ID), в противном случае произойдет ошибка. Оператор SELECT также может включать предложение WHERE для фильтрации данных. Также следует отметить, что СУБД не обращает внимания на названия колонок, содержащихся в операторе SELECT, для нее важен только порядок их расположения. Поэтому данные в первом указанном столбце, выбранные через SELECT, будут в любом случае заполнены в первый столбец таблицы Sellers, указанной после оператора INSERT INTO, независимо от названия поля.
4. Копирование данных из одной таблицы в другую
Часто при работе с базами данных возникает необходимость в создании копий любых таблиц с целью резервирования или модификации. Чтобы сделать полную копию таблицы в SQL, предусмотрен отдельный оператор SELECT INTO. Например, нам нужно создать копию таблицы Sellers, нужно будет прописать запрос следующим образом:
SELECT * INTO Sellers_new FROM Sellers 
В отличие от предыдущей конструкции INSERT INTO. SELECT. когда данные добавляются в существующую таблицу, конструкция SELECT. INTO. FROM. копирует данные в новую таблицу. Также можно сказать, что первая конструкция импортирует данные, а вторая – экспортирует.
При использовании конструкции SELECT. INTO. FROM. следует учитывать следующее:
Оператор 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 выглядит так:
| 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 используем следующий запрос:
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 | Недвижимость | Гаражи | 22 | 4620 |
Из примера видно, что для вставки числовых значений в таблицу значения нужно указывать без кавычек, а для вставки строковых значений — в одинарных кавычках.
В запросе на вставку данных можно список столбцов можно указать не в том порядке, который задан при создании таблицы, и тогда данные следует указывать также в изменённом порядке.
Пример 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 получили значения по умолчанию. После выполнения этого запроса новая строка будет содержать следующие данные:
| 14 | Some Category | Some Part | NULL | NULL |
На MS SQL Server такой запрос недопустим.
Поскольку все столбцы могут иметь значения по умолчанию, можно использовать в запросе на вставку данных ключевое слово DEFAULT и не указывать имена столбцов.
Пример 4. Запрос на MySQL. Вставим новые значения, используя ключевое слово DEFAULT и не указывая имён столбцов:
INSERT INTO ADS VALUES ( DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT )
Теперь все столбцы получили значения по умолчанию. После выполнения этого запроса новая строка будет содержать следующие данные:
| 100 | Some Category | Some Part | NULL | NULL |
Запрос на 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 Category | Some Part | NULL | NULL |
Вставка значений с использованием оператора SET в MySQL
В MySQL дополнительно существует альтернативная конструкция для вставки значений в таблицу с использованием оператора SET. Она похожа на конструкцию оператора UPDATE и имеет следующий синтаксис:
INSERT INTO ИМЯ_ТАБЛИЦЫ SET ИМЯ_СТОЛБЦА_1=ЗНАЧЕНИЕ, ИМЯ_СТОЛБЦА_2=ЗНАЧЕНИЕ, . ИМЯ_СТОЛБЦА_N=ЗНАЧЕНИЕ
В подобных запросах можно указывать имена не всех столбцов, при этом не указанные столбцы принимают значения по умолчанию.
Пример 6. База данных и таблица — те же, что и в предыдущих примерах.
Вставим в таблицу строку, при этом столбцы Units и Money примут значения по умолчанию:
INSERT INTO ADS SET Category=’Недвижимость’, Part=’Гаражи’
В результате выполнения запроса в таблице появится новая строка:
| 13 | Недвижимость | Гаражи | NULL | NULL |
Использование механизма автоматического приращения при вставке данных
Механизм автоматического приращения предназначен для автоматического генерирования уникальных значений первичного ключа в таблице. При его использовании не требуется проверять все значения первичного ключа, чтобы определить максимальное значение. При создании новой записи можно указать в качестве значения первичного ключа 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 | Электротехника | Телевизоры | 127 | 8255 |
| 7 | Электротехника | Холодильники | 137 | 8905 |
| 8 | Стройматериалы | Регипс | 112 | 11760 |
| 9 | Досуг | Книги | 96 | 6240 |
На 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.
Как вставлять данные в базы SQL
Structured Query Language, более известный как SQL, обеспечивает большую гибкость с точки зрения вставки данных в таблицы. Например, вы можете вставлять отдельные строки данных с помощью ключевого слова VALUES, копировать целые наборы данных из существующих таблиц с помощью запросов SELECT, а также определять столбцы таким образом, чтобы SQL автоматически вставлял в них данные.
В этом материале вы узнаете, как использовать синтаксис INSERT INTO для добавления данных в таблицы с помощью каждого из перечисленных выше средств.
Требования
Чтобы следовать этому руководству, вам понадобится любая машина с установленной системой управления реляционными базами данных (RDBMS), которая использует SQL. Инструкции и примеры в этом руководстве были выполнены на основе следующей среды:
- Сервер Ubuntu 20.04 + пользователь с правами администратора и брандмауэр UFW (настройка такого сервера описана в этом руководстве).
- Защищенная копия MySQL на сервере (инструкции вы найдете в мануале Установка MySQL в Ubuntu 20.04).
Примечание: Обратите внимание, что многие СУБД используют собственные уникальные реализации SQL. Команды, описанные в этом руководстве, будут работать правильно на большинстве СУБД, однако точный синтаксис или вывод могут отличаться, если вы тестируете их не в MySQL, а в другой системе.
Вам также понадобится база данных и таблица, на которых вы сможете попрактиковаться. Если у вас их нет, вы можете выполнить следующий раздел мануала.
Подключение к MySQL и создание тестовой базы данных
Если ваша БД SQL работает на удаленном сервере, подключитесь к серверу по SSH с локального компьютера:
Затем откройте командную строку MySQL, заменив 8host именем вашей учетной записи пользователя MySQL.
mysql -u 8host -p
Создайте базу данных по имени insertDB:
CREATE DATABASE insertDB;
Если база данных была создана успешно, вы получите такой вывод:
Query OK, 1 row affected (0.01 sec)
Чтобы выбрать базу данных insertDB, выполните следующую команду USE:
Вы увидите вывод:
Database changed
После выбора базы данных insertDB создайте в ней таблицу. Давайте для примера предположим, что у вас есть фабрика и вы хотите создать таблицу для хранения информации о ваших сотрудниках. Эта таблица будет включать в себя следующие пять столбцов:
- name: имя каждого сотрудника, выраженное при помощи типа данных varchar, не более 30 символов.
- position: в этом столбце будет храниться должность каждого сотрудника, опять же выраженная с помощью типа данных varchar с максимальным количеством символов 30.
- department: отдел, в котором работает каждый сотрудник, выраженный с помощью типа данных varchar, не более 20 символов.
- hourlyWage: столбец для записи почасовой заработной платы каждого сотрудника, в нем используется тип данных decimal, при этом любые значения в этом столбце ограничены до четырех цифр, причем две из этих цифр находятся справа от запятой. Таким образом, диапазон допустимых значений в этом столбце составляет от -99,99 до 99,99.
- startDate: дата приема на работу каждого сотрудника, выраженная с помощью типа данных date. Значения этого типа должны соответствовать формату YYYY-MM-DD.
Создайте таблицу factoryEmployees, которая будет включать эти пять столбцов:
CREATE TABLE factoryEmployees ( name varchar(30), position varchar(30), department varchar(20), hourlyWage decimal(4,2), startDate date );
Теперь можно приступать к выполнению остальной части руководства.
Вставка данных вручную
Общий синтаксис для вставки данных в SQL выглядит следующим образом:
INSERT INTO table_name (column1, column2, . . . columnN) VALUES (value1, value2, . . . valueN);
Чтобы протестировать это, запустите следующую команду INSERT INTO, она загрузит таблицу factoryEmployees с одной строкой данных:
INSERT INTO factoryEmployees (name, position, department, hourlyWage, startDate) VALUES ('Agnes', 'thingamajig foreman', 'management', 26.50, '2017-05-01');
Вывод выглядит так:
Query OK, 1 row affected (0.00 sec)
Этот оператор начинается с ключевых слов INSERT INTO, за которыми следует имя таблицы, в которую вы хотите вставить данные. За именем таблицы следует список столбцов, в которые оператор добавит данные, заключенные в круглые скобки. После списка столбцов идет ключевое слово VALUES, а затем набор значений, заключенных в круглые скобки и разделенных запятыми.
Порядок, в котором вы перечисляете столбцы, не имеет значения. Важно помнить следующее: порядок значений, которые вы предоставляете, обязательно должен совпадать с порядком столбцов. SQL всегда будет пытаться вставить первое заданное значение в первый столбец списка, второе значение — во второй столбец и так далее. Для иллюстрации введите следующий оператор INSERT. Он добавляет еще одну строку данных, но перечисляет данные в другом порядке:
INSERT INTO factoryEmployees (department, hourlyWage, startDate, name, position) VALUES ('production', 15.59, '2018-04-28', 'Jim', 'widget tightener');
Query OK, 1 row affected (0.00 sec)
Если вы неправильно разместите значения, SQL может ввести ваши данные в неправильные столбцы. Кроме того, если какое-либо из значений конфликтует с типом данных столбца, возникнет ошибка, как в этом примере:
INSERT INTO factoryEmployees (name, hourlyWage, position, startDate, department) VALUES ('Louise', 'doodad tester', 16.50, '2017-05-01', 'quality assurance');
Ошибка выглядит так:
ERROR 1366 (HY000): Incorrect decimal value: 'doodad tester' for column 'hourlyWage' at row 1
Имейте в виду: вы должны указать значение для каждого созданного вами столбца; однако ведь не все столбцы будут иметь свое значение в каждой строке. Если ни один из пропущенных столбцов не имеет ограничения, которое могло бы вызвать ошибку в этом случае (например, NOT NULL), MySQL добавит NULL вместо любых пропущенных значений:
INSERT INTO factoryEmployees (name, position, hourlyWage) VALUES ('Harry', 'whatzit engineer', 26.50);
Вы получите такой вывод:
Query OK, 1 row affected (0.01 sec)
Если строка, которую вы вводите, содержит значения для каждого столбца в таблице, вам вообще не нужно включать имена столбцов. Имейте в виду, что введенные вами значения должны соответствовать порядку, в котором столбцы были определены в самой таблице.
В этом примере перечисленные значения соответствуют порядку, в котором определены столбцы в таблице:
INSERT INTO factoryEmployees VALUES ('Marie', 'doodad welder', 'production', 27.88, '2018-03-29');
Query OK, 1 row affected (0.00 sec)
Вы также можете добавить сразу несколько записей, разделив все строки запятыми, например:
INSERT INTO factoryEmployees VALUES ('Giles', 'gizmo inspector', 'quality assurance', 26.50, '2019-08-06'), ('Daphne', 'gizmo presser', 'production', 32.45, '2017-11-12'), ('Joan', 'whatzit analyst', 'quality assurance', 29.00, '2017-04-29');
Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0
Копирование данных с помощью оператора SELECT
Чтобы не указывать данные построчно, вы можете скопировать несколько строк данных из одной таблицы и вставить их в другую с помощью запроса SELECT.
Синтаксис такой операции выглядит следующим образом:
INSERT INTO table_A (col_A1, col_A2, col_A3) SELECT col_B1, col_B2, col_B3 FROM table_B;
Вместо ключевого слова VALUES, которое обычно идет за списком столбцов, в этом примере за ним следует оператор SELECT. Оператор SELECT в этом примере включает только оператор FROM, но он может обработать любой допустимый запрос.
Выполните следующую операцию CREATE TABLE, чтобы создать новую таблицу showroomEmployees. Обратите внимание, что столбцы этой таблицы имеют те же имена и типы данных, что и в таблице factoryEmployees, которую мы создали в предыдущем разделе:
CREATE TABLE showroomEmployees ( name varchar(30), hourlyWage decimal(4,2), startDate date );
Query OK, 0 rows affected (0.02 sec)
Теперь вы можете загрузить в эту новую таблицу некоторые данные из таблицы factoryEmployees, которую создали ранее, включив запрос SELECT в INSERT INTO.
Если запрос SELECT возвращает то же количество столбцов в том же порядке, что и столбцы целевой таблицы, и они имеют совместимые (или совпадающие) типы данных, вы можете опустить список столбцов в операторе INSERT INTO:
INSERT INTO showroomEmployees SELECT factoryEmployees.name, factoryEmployees.hourlyWage, factoryEmployees.startDate FROM factoryEmployees WHERE name = 'Agnes';
Вывод выглядит так:
Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0
Примечание: Каждому из столбцов, перечисленных в запросе SELECT этой операции, предшествует имя таблицы factoryEmployees и точка. Когда вы указываете имя таблицы при ссылке на такой столбец, это называется fully qualified column reference. В данном конкретном случае это делать не обязательно. На самом деле следующий оператор INSERT INTO даст тот же результат, что и предыдущий:
INSERT INTO showroomEmployees SELECT name, hourlyWage, startDate FROM factoryEmployees WHERE name = 'Agnes';
В примерах данного раздела для ясности используются полные (fully qualified) ссылки на столбцы, что на практике может стать хорошей и полезной привычкой. Подобные ссылки не только помогут упростить понимание и устранение неполадок в SQL, но и пригодятся в некоторых операциях, которые ссылаются на более чем одну таблицу (это, например, запросы, включающие предложения JOIN).
Оператор SELECT в этой операции включает опцию WHERE, благодаря которой запрос возвращает только строки из таблицы factoryEmployees, чей столбец name содержит значение Agnes. Поскольку в исходной таблице есть только одна такая строка, только именно она будет скопирована в таблицу showroomEmployees.
Выполните следующий проверочный запрос, чтобы извлечь все записи в таблице showroomEmployees:
SELECT * FROM showroomEmployees;
Вы получите такой результат:
+-------+------------+------------+ | name | hourlyWage | startDate | +-------+------------+------------+ | Agnes | 26.50 | 2017-05-01 | +-------+------------+------------+ 1 row in set (0.00 sec)
Вы можете вставить несколько строк данных через любой запрос, который вернет более одной строки из исходной таблицы. Например, следующий запрос вернет каждую запись из БД factoryEmployees, в которой значение в столбце name не начинается с буквы J:
INSERT INTO showroomEmployees SELECT factoryEmployees.name, factoryEmployees.hourlyWage, factoryEmployees.startDate FROM factoryEmployees WHERE name NOT LIKE 'J%';
Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0
Запустите этот запрос еще раз, чтобы получить все записи в таблице showroomEmployees:
SELECT * FROM showroomEmployees;
Результат выглядит так:
+--------+------------+------------+ | name | hourlyWage | startDate | +--------+------------+------------+ | Agnes | 26.50 | 2017-05-01 | | Agnes | 26.50 | 2017-05-01 | | Harry | 26.50 | NULL | | Marie | 27.88 | 2018-03-29 | | Giles | 26.50 | 2019-08-06 | | Daphne | 32.45 | 2017-11-12 | +--------+------------+------------+ 6 rows in set (0.00 sec)
Обратите внимание на две одинаковые строки со значением Agnes. Каждый раз, когда вы запускаете INSERT INTO вместе с SELECT, SQL обрабатывает результаты запроса как новый набор данных. Если вы не наложите определенные ограничения на свою таблицу или не разработаете более детализированные запросы, при добавлении данных в таблицу они частично будут повторяться.
Автоматическая вставка данных
Создавая таблицу, вы можете применить к столбцам определенные атрибуты, которые заставят СУБД автоматически заполнить их данными.
Чтобы проиллюстрировать это, запустите следующую команду. Она создаст таблицу interns, которая включает в себя три столбца. Первый столбец, internID, содержит данные типа int. Однако обратите внимание, что он также включает атрибут AUTO_INCREMENT. С его помощью SQL будет автоматически генерировать уникальное числовое значение для каждой новой строки, начиная с 1 (по умолчанию) и увеличивая его на 1 с каждой последующей записью.
Второй столбец, department, включает ключевое слово DEFAULT. С его помощью СУРБД автоматически вставит значение по умолчанию — в этом примере мы используем ‘production’ — если вы не укажете department в списке столбцов оператора INSERT INTO:
CREATE TABLE interns ( internID int AUTO_INCREMENT PRIMARY KEY, department varchar(20) DEFAULT 'production', name varchar(30) );
Примечание: Атрибут AUTO_INCREMENT — это специфичная функция MySQL, многие другие СУБД имеют собственный метод увеличения целых чисел. Чтобы лучше понять, как ваша СУБД управляет автоматическим счетом, рекомендуем обратиться к ее официальной документации.
Здесь мы собрали для вас список документаций нескольких популярных БД с открытым исходным кодом:
- Документация MySQL по атрибуту AUTO_INCREMENT
- Документация PostgreSQL по последовательным типам данных
- Документация SQLite по Autoincrement
Чтобы продемонстрировать работу этих функций, мы заполним таблицу interns некоторыми данными, выполнив следующую команду INSERT INTO. Эта операция задает значения только для столбца name:
INSERT INTO interns (name) VALUES ('Pierre'), ('Sheila'), ('Francois');
Вы получите такой вывод:
Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0
Затем запустите этот запрос, чтобы вернуть каждую запись из таблицы:
SELECT * FROM interns;
Вы получите такой результат:
+----------+------------+----------+ | internID | department | name | +----------+------------+----------+ | 1 | production | Pierre | | 2 | production | Sheila | | 3 | production | Francois | +----------+------------+----------+ 3 rows in set (0.00 sec)
Результат показывает, что из-за определений столбцов предыдущая команда INSERT INTO добавила значения как в internID, так и в department, хотя мы не указывали их.
Чтобы добавить в столбец department нестандартное значение, вам нужно будет указать его в операторе INSERT INTO, например, так:
INSERT INTO interns (name, department) VALUES ('Jacques', 'management'), ('Max', 'quality assurance'), ('Edith', 'management'), ('Daniel', DEFAULT);
Результат выглядит так:
Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0
Обратите внимание, последняя строка значений в этом примере вместо строкового значения включает ключевое слово DEFAULT. Благодаря этому БД вставит значение по умолчанию (‘production’):
SELECT * FROM interns;
Результат выглядит так:
+----------+-------------------+----------+ | internID | department | name | +----------+-------------------+----------+ | 1 | production | Pierre | | 2 | production | Sheila | | 3 | production | Francois | | 4 | management | Jacques | | 5 | quality assurance | Max | | 6 | management | Edith | | 7 | production | Daniel | +----------+-------------------+----------+ 7 rows in set (0.00 sec)
Итоги
Прочитав это руководство, вы узнали о нескольких различных способах вставки данных в таблицу: это определение отдельных строк данных с помощью ключевого слова VALUES, копирование целых наборов данных с помощью запросов SELECT и определение столбцов, в которые SQL будет автоматически вставлять данные.
Описанные здесь команды будут работать в любой СУБД на основе SQL. Однако имейте в виду, что каждая база данных SQL использует свою собственную уникальную реализацию языка, поэтому для получения более полного описания оператора INSERT INTO и его параметров вам следует обратиться к официальной документации вашей СУБД.