如何使用SQL系列 之 如何在SQL中插入数据

简介

结构化查询语言，通常被称为SQL，在允许您将数据插入表中方面提供了极大的灵活性。例如，你可以使用VALUES关键字指定单独的行数据，使用SELECT查询从现有表中复制整组数据，以及以使SQL自动插入数据的方式定义列。

在本指南中,我们将复习如何使用SQL的INSERT INTO语法添加数据表与这些方法。

前期准备

为了学习本指南，你需要一台运行某种使用SQL的关系数据库管理系统(RDBMS)的计算机。

注意：请注意，许多RDBMS使用它们自己独特的SQL实现。虽然本教程中概述的命令适用于大多数RDBMS，但如果你在MySQL以外的系统上测试它们，确切的语法或输出可能会有所不同。

你还需要一个装载了一些示例数据的数据库和表，可以在其中练习使用相关命令。

连接到MySQL并设置一个示例数据库

如果SQL数据库系统运行在远程服务器上，请从本地设备SSH到服务器:

ssh sammy@your_server_ip

然后打开MySQL服务器提示符，将==sammy==替换为你的MySQL用户账户的名称:

mysql -u sammy -p

创建一个名为insertDB的数据库:

CREATE DATABASE insertDB;

如果数据库成功创建,您将收到这样的输出:

OutputQuery OK, 1 row affected (0.01 sec)

要选择insertDB数据库，运行以下USE语句:

USE insertDB;

OutputDatabase changed

选择insertDB数据库后，在其中创建一个表。举个例子，假设你有一家工厂，想创建一张表来存储员工的一些信息。这个表包含以下5个字段：

• name：每个员工的名字，表示使用的varchar数据类型，字符长度30
• position：这一列将存储每个员工的职位头衔，同样使用不超过30个字符的varchar数据类型表示
• department：每个员工的部门，表示使用的varchar数据类型，但最多只有20个字符
• hourlyWage：记录每个员工每小时工资的一列，它使用decimal数据类型，该列中的任何值的长度都限制为最多4位，其中2位在小数点的右侧。因此，这一列允许的取值范围是-99.99到99.99
• startDate：每个员工被雇佣的日期，使用date数据类型表示。该类型的值必须符合YYYY-MM-DD格式。

创建一个名为factoryEmployees的表，它包含以下5个字段：

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');

OutputQuery 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');

OutputQuery 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');

OutputERROR 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);

OutputQuery OK, 1 row affected (0.01 sec)

如果你计划输入一个包含表中每一列值的行，则根本不需要包含列名。请记住，您输入的值仍然必须与表定义中定义的列的顺序一致。

在这个例子中，列出的值与factoryEmployee表的CREATE TABLE语句中定义的列的顺序一致:

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

OutputQuery 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
);

OutputQuery OK, 0 rows affected (0.02 sec)

现在你可以通过在INSERT INTO语句中包含SELECT查询来从之前创建的factoryEmployees表中读取一些数据来加载这个新表。

如果SELECT查询返回的相同数量的列顺序相同目标表的列,和他们也有兼容匹配的数据类型,您可以省略字段列表的INSERT INTO声明:

INSERT INTO showroomEmployees
SELECT
factoryEmployees.name,
factoryEmployees.hourlyWage,
factoryEmployees.startDate
FROM factoryEmployees
WHERE name = 'Agnes';

OutputQuery OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

注意：此操作的SELECT查询中列出的列前都有factoryEmployees表名和一个句点。当你像这样指定一个表名来引用一个列时，它被称为完全限定列引用。在这种特殊情况下，这是不必要的。事实上，下面的INSERT INTO语句示例将产生与前一个相同的结果:

INSERT INTO showroomEmployees
SELECT
name,
hourlyWage,
startDate
FROM factoryEmployees
WHERE name = 'Agnes';

为了清晰起见，本节中的示例使用了完全限定的列引用，但这样做可能是一个好习惯。它们不仅可以帮助您的SQL更容易理解和故障排除，在引用多个表的某些操作中，例如包含JOIN子句的查询，完全限定列引用变得必要。

这个操作中的SELECT语句包含了一个WHERE子句，它导致查询只返回factoryEmployees表中的name列包含值Agnes的行。因为在源表中只有一行这样的记录，所以只有这一行会被复制到showroomEmployees表中。

为了确认这一点，运行以下查询来返回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%';

OutputQuery 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)

注意：在name列中有两行相同的Agnes。每次你运行使用SELECT的INSERT INTO语句时，SQL都会将查询结果视为一组新数据。除非你对表施加某些约束，或者开发更细粒度的查询，否则在添加这样的数据时，没有什么能防止数据库加载重复记录。

自动插入数据

在创建表时，可以对列应用某些属性，RDBMS会自动向列填充数据。

为了说明这一点，运行以下声明定义一个名为interns的表，包含3个字段。在这个例子中，第一列internID保存着int类型的数据。但是请注意，它还包含AUTO_INCREMENT属性。这个属性会让SQL自动为每条新记录生成一个唯一的数值，默认从1开始，然后每条记录加1。

类似地，第二列department包含DEFAULT关键字。如果你在 insert INTO语句的字段列表中省略了department， RDBMS会自动插入默认值——本例中是production:

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

注意:AUTO_INCREMENT属性是MySQL特有的特性，但是许多rdbms有自己的递增整数的方法。为了更好地理解你的RDBMS是如何管理自动递增的，你应该查阅它的官方文档。

以下是几个流行的开源数据库的官方文档:

• MySQL AUTO_INCREMENT属性文档

• PostgreSQL serial数据类型文档

• SQLite Autoincrement关键字文档

为了演示这些特性，用一些数据写入interns表通过运行以下INSERT INTO的语句。此操作仅指定name列的值:

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

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

Then run this query to return every record from the table:

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语句将值添加到internID和department中，即使没有指定它们。

要给department列添加一个默认值以外的值，你需要在INSERT INTO语句中指定该列，如下所示:

INSERT INTO interns (name, department)
VALUES
('Jacques', 'management'),
('Max', 'quality assurance'),
('Edith', 'management'),
('Daniel', DEFAULT);

OutputQuery 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数据库都使用自己独特的语言实现，因此你应该查阅相应DBMS的官方文档，以更完整地了解它如何处理INSERT INTO语句以及它有哪些可用选项。