Как вставить данные в SQL

  • 452 просмотров
  • 9 минута чтения
Хади Бахадори
  • 20 июля 2024 г.
0 Акции
0
0
0
0
0
Акции
0
0
0
0

Введение

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

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

Предпосылки

Для выполнения этого руководства вам понадобится компьютер с установленной системой управления реляционными базами данных (СУРБД), использующей SQL. Инструкции и примеры в этом руководстве были проверены в следующей среде:

  • Сервер под управлением Ubuntu 20.04 с пользователем без прав root, обладающим правами администратора, и брандмауэром, настроенным с помощью UFW.
  • MySQL установлен и защищен на сервере.

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

Подключение к MySQL и настройка образца базы данных

Если ваша система базы данных SQL работает на удаленном сервере, подключитесь к серверу по SSH с локальной машины:

ssh sammy@your_server_ip

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

mysql -u sammy -p

Создайте базу данных с именем insertDB:

CREATE DATABASE insertDB;

Если база данных была создана успешно, вы получите следующий вывод:

Query OK, 1 row affected (0.01 sec)

Чтобы выбрать базу данных insertDB, выполните следующий оператор USE:

USE insertDB;

Output
Database changed

Выбрав базу данных insertDB, создайте в ней таблицу. Например, предположим, что у вас есть фабрика, и вы хотите создать таблицу для хранения информации о своих сотрудниках. Эта таблица будет содержать следующие пять столбцов:

  • имя: Имя каждого сотрудника, выраженное с использованием типа данных varchar, длиной не более 30 символов.
  • позиция: В этом столбце хранится должность каждого сотрудника, которая также выражается с использованием типа данных varchar с максимальной длиной 30 символов.
  • отделение: Отдел, в котором работает каждый сотрудник, выраженный с использованием типа данных varchar, но с максимальной длиной только 20 символов.
  • почасовая оплата: Столбец для записи почасовой заработной платы каждого сотрудника использует десятичный тип данных. Значения в этом столбце могут содержать до четырёх знаков, два из которых находятся справа от десятичной точки. Таким образом, диапазон допустимых значений в этом столбце составляет от -99,99 до 99,99.
  • Дата начала: Дата приема на работу каждого сотрудника, выраженная типом данных «Дата». Значения этого типа должны соответствовать формату ГГГГ-ММ-ДД.

Создайте таблицу под названием factoryEmployees со следующими пятью столбцами:

CREATE TABLE factoryEmployees (
name varchar(30),
position varchar(30),
department varchar(20),
hourlyWage decimal(4,2),
startDate date
);

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

Введите данные вручную

Общий синтаксис вставки данных в 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');
Output
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');
Output
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');
Output
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);
Output
Query OK, 1 row affected (0.01 sec)

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

В этом примере перечисленные значения выровнены в том порядке, в котором столбцы определены в операторе CREATE TABLE для таблицы factoryEmployee:

INSERT INTO factoryEmployees
VALUES
('Marie', 'doodad welder', 'production', 27.88, '2018-03-29');

Output
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
);
Output
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';
Output
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0

Оператор SELECT в этой операции включает предложение WHERE, которое заставляет запрос возвращать только те строки из таблицы factoryEmployees, столбец name которых содержит значение Agnes. Поскольку в исходной таблице только одна строка, в таблицу showroomEmployees копируется только она.

Чтобы проверить это, выполните следующий запрос, чтобы вернуть каждую запись из таблицы сотрудников автосалона:

SELECT * FROM showroomEmployees;

Output
+-------+------------+------------+
| 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%';
Output
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0

Чтобы вернуть каждую запись из таблицы сотрудников автосалона, выполните этот запрос еще раз:

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)

Обратите внимание, что в столбце «Имя» есть две одинаковые строки с именем «Агнес». Каждый раз, когда вы выполняете оператор INSERT INTO с использованием SELECT, SQL обрабатывает результаты запроса как новый набор данных. Если вы не накладываете особые ограничения на таблицу или не создаете более точные запросы, ничто не препятствует загрузке вашей базы данных дублирующимися записями при добавлении данных подобным образом.

Автоматически вставлять информацию

При создании таблицы вы можете применить к столбцам определенные атрибуты, которые заставят СУБД автоматически заполнить их данными.

Для иллюстрации выполните следующий оператор, чтобы определить таблицу с именем «interns». Это создаст таблицу с именем «interns» и тремя столбцами. Первый столбец в этом примере, «internID», содержит данные типа int. Обратите внимание, что он также включает атрибут AUTO_INCREMENT. Этот атрибут заставляет SQL автоматически генерировать уникальное числовое значение для каждой новой строки, начиная с 1 по умолчанию и увеличивая его на единицу с каждой последующей записью.

Аналогично, второй столбец, section, содержит ключевое слово DEFAULT. Если удалить section из списка столбцов оператора INSERT INTO, СУБД автоматически подставит значение по умолчанию — в данном примере “production”:

CREATE TABLE interns (
internID int AUTO_INCREMENT PRIMARY KEY,
department varchar(20) DEFAULT 'production',
name varchar(30)
);

Чтобы продемонстрировать эти функции, загрузите данные в таблицу Interns, выполнив следующую инструкцию INSERT INTO. Эта операция задаёт только значения столбца Name:

INSERT INTO interns (name) VALUES ('Pierre'), ('Sheila'), ('Francois');

Output
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

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

SELECT * FROM interns;

Output
+----------+------------+----------+
| internID | department | name |
+----------+------------+----------+
| 1 | production | Pierre |
| 2 | production | Sheila |
| 3 | production | Francois |
+----------+------------+----------+
3 rows in set (0.00 sec)

Этот вывод показывает, что из-за определений столбцов предыдущий оператор INSERT INTO добавляет значения как во внутренний, так и во внешний разделы, даже если они не указаны.

Чтобы добавить в столбец раздела значение, отличное от значения по умолчанию, необходимо указать этот столбец в операторе INSERT INTO, например:

INSERT INTO interns (name, department)
VALUES
('Jacques', 'management'),
('Max', 'quality assurance'),
('Edith', 'management'),
('Daniel', DEFAULT);
Output
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

Обратите внимание, что последняя строка значений в этом примере содержит ключевое слово DEFAULT вместо строкового значения. Это приводит к тому, что база данных подставляет значение по умолчанию (‘production’):

SELECT * FROM interns;

Output
+----------+-------------------+----------+
| 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 и какие параметры для него доступны, обратитесь к документации вашей СУБД.

В этой статье:
,
Автор поста:
Следовать
Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Предыдущая статья
Следующая статья
Вам также может понравиться