如何在 SQL 中插入数据

介绍

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

在本指南中，我们将介绍如何使用 SQL 的INSERT INTO语法通过这些方法中的每一种将数据添加到表中。

先决条件

为了遵循本指南，您需要一台运行某种类型的使用 SQL 的关系数据库管理系统 (RDBMS) 的计算机。本指南中的说明和示例已使用以下环境进行验证：

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

您还需要一个可以练习插入数据的数据库和表。如果您没有这些，您可以阅读以下连接到 MySQL 和设置示例数据库部分，详细了解如何创建本指南将在整个示例中使用的数据库和表。

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

如果您的 SQL 数据库系统在远程服务器上运行，请从本地计算机通过 SSH 连接到您的服务器：

  • ssh sammy@your_server_ip

然后打开 MySQL 服务器提示，替换sammy为您的 MySQL 用户帐户的名称：

  • mysql -u sammy -p

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

  • CREATE DATABASE insertDB;

如果数据库创建成功，您将收到如下输出：

Output
Query OK, 1 row affected (0.01 sec)

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

  • USE insertDB;

Output
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 插入数据。

手动插入数据

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

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

在此示例中，列出的值与factoryEmployee表CREATE TABLE语句中定义列的顺序一致：

  • 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查询中列出的列均以表名factoryEmployees和句点开头。当您在引用这样的列时指定表名时，它被称为完全限定的列引用。在这种特殊情况下，这不是必需的。事实上，以下示例INSERT INTO语句将产生与前一个相同的结果：

  • INSERT INTO showroomEmployees

  • SELECT

  • name,

  • hourlyWage,

  • startDate

  • FROM factoryEmployees

  • WHERE name = 'Agnes';

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

SELECT此操作中的语句包含一个WHERE子句，该子句使查询仅返回列包含值的factoryEmployees表中的行。因为源表中只有一个这样的行，所以只有该行会被复制到表中。nameAgnesshowroomEmployees

要确认这一点，请运行以下查询以返回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

再次运行此查询以返回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 都会将查询结果视为一组新数据。除非您对表施加某些限制或开发更细粒度的查询，否则在添加这样的数据时，没有什么可以阻止数据库加载重复记录。

自动插入数据

创建表时，您可以将某些属性应用于列，这将导致 RDBMS 自动用数据填充它们。

为了说明这一点，请运行以下语句来定义一个名为 的表interns。这将创建一个名为interns具有三列的表。本示例中的第一列internID，保存该int类型的数据。但是请注意，它也包括该AUTO_INCREMENT属性。此属性将导致 SQL 为每个新行自动生成一个唯一的数值1，默认情况下从开始，然后随着每个后续记录递增 1。

同样，第二列department包含DEFAULT关键字。'production'如果您department从INSERT INTO语句的列列表中省略，这将导致 RDBMS 自动插入默认值（在本例中）：

  • CREATE TABLE interns (

  • internID int AUTO_INCREMENT PRIMARY KEY,

  • department varchar(20) DEFAULT 'production',

  • name varchar(30)

  • );

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

以下是一些流行的开源数据库有关该主题的官方文档：

要演示这些功能，请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语句将值添加到两者中internID，department即使它们没有被指定。

要将默认值以外的值添加到department列中，您需要在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 数据库都使用自己独特的语言实现，因此您应该查阅 DBMS 的官方文档，以INSERT INTO获取有关它如何处理语句以及可用选项的更完整描述。

如果您想了解有关使用 SQL 的更多信息，我们鼓励您查看本系列中关于如何使用 SQL的其他教程。

觉得文章有用？

点个广告表达一下你的爱意吧 ！😁