Как вставлять данные в базы 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 с локального компьютера:
ssh 8host@your_server_ip
Затем откройте командную строку MySQL, заменив 8host именем вашей учетной записи пользователя MySQL.
mysql -u 8host -p
Создайте базу данных по имени insertDB:
CREATE DATABASE insertDB;
Если база данных была создана успешно, вы получите такой вывод:
Query OK, 1 row affected (0.01 sec)
Чтобы выбрать базу данных insertDB, выполните следующую команду USE:
USE insertDB;
Вы увидите вывод:
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 и его параметров вам следует обратиться к официальной документации вашей СУБД.
Читайте также: Как работать с ограничениями в SQL
Tags: MySQL, SQL, Ubuntu 20.04