介绍
Structured Query Language,更常被称为 SQL,在允许您将数据插入表方面提供了很大的灵活性,例如,您可以使用VALUES关键字指定单个数据行,使用SELECT查询复制现有表中的整个数据集,以及以自动将SQL插入数据的方式定义列。
在本指南中,我们将讨论如何使用 SQL 的INSERT INTO语法以使用这些方法将数据添加到表中。
前提条件
要遵循本指南,您需要运行某种类型的关系数据库管理系统(RDBMS)的计算机,该系统使用SQL。
- 运行 Ubuntu 20.04 的服务器,具有非 root 用户管理权限和与 UFW 配置的防火墙,如我们在 [Ubuntu 20.04 的初始服务器设置指南] 中所描述的。
<$>[注] 注:请注意,许多RDBMS使用自己的独特的SQL实现程序.虽然本教程中描述的命令将在大多数RDBMS上工作,但如果您在MySQL以外的系统上测试它们,精确的语法或输出可能会有所不同。
如果您没有这些数据,您可以阅读以下 连接到MySQL和设置示例数据库部分,详细了解如何创建数据库和表,本指南将在整个例子中使用。
连接到MySQL并设置样本数据库
如果您的 SQL 数据库系统在远程服务器上运行,则从本地计算机输入 SSH 到服务器:
1[environment local]
2ssh sammy@your_server_ip
然后打开MySQL服务器提示,用您的MySQL用户帐户的名称代替sammy:
1mysql -u sammy -p
创建一个名为insertDB的数据库:
1CREATE DATABASE insertDB;
如果数据库创建成功,您将收到这样的输出:
1[secondary_label Output]
2Query OK, 1 row affected (0.01 sec)
若要选择insertDB数据库,请运行以下USE语句:
1USE insertDB;
1[secondary_label Output]
2Database changed
选择insertDB数据库后,在其内创建一个表,例如,假设您拥有一个工厂,并希望创建一个表,以存储有关您的员工的一些信息。
*‘名称’:每个员工的名称,用最多30个字符 *‘位置’的‘varchar’数据类型表示:此列将存储每个员工的职位名称,再次用最多30个字符 *‘部门’表示:每个员工工作所在的部门,用‘varchar’数据类型表示,但最多只有20个字符 *‘hourlyWage’:一个用于记录每个员工的小时工资的数据类型,它使用此列中的任何值最多限于四个字符的‘十字符’数据类型表示,其中右边的两个数字是十字符。
创建一个名为factoryEmployees的表,其中包含以下五个列:
1CREATE TABLE factoryEmployees (
2name varchar(30),
3position varchar(30),
4department varchar(20),
5hourlyWage decimal(4,2),
6startDate date
7);
有了这一点,您已经准备好跟随本指南的其余部分,并开始学习如何使用 SQL 插入数据。
手动输入数据
在 SQL 中插入数据的通用语法看起来如下:
1INSERT INTO table_name
2(column1, column2, . . . columnN)
3VALUES
4(value1, value2, . . . valueN);
如要说明情况,请运行以下INSERT INTO语句,将单行数据加载到factoryEmployees表中:
1INSERT INTO factoryEmployees
2(name, position, department, hourlyWage, startDate)
3VALUES
4('Agnes', 'thingamajig foreman', 'management', 26.50, '2017-05-01');
1[secondary_label Output]
2Query OK, 1 row affected (0.00 sec)
此语句始于INSERT INTO的关键字,随后是您要插入数据的表的名称。 接着是列表名称,列表将添加数据的列表,将其包裹在栏目中。
列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列列。
1INSERT INTO factoryEmployees
2(department, hourlyWage, startDate, name, position)
3VALUES
4('production', 15.59, '2018-04-28', 'Jim', 'widget tightener');
1[secondary_label Output]
2Query OK, 1 row affected (0.00 sec)
如果不正确对齐值,SQL 可能会将数据输入到错误的列中,此外,如果任何值与列的数据类型相冲突,则会导致错误,如本示例中所示:
1INSERT INTO factoryEmployees
2(name, hourlyWage, position, startDate, department)
3VALUES
4('Louise', 'doodad tester', 16.50, '2017-05-01', 'quality assurance');
1[secondary_label Output]
2ERROR 1366 (HY000): Incorrect decimal value: 'doodad tester' for column 'hourlyWage' at row 1
请注意,虽然您必须为您指定的每个列提供一个值,但在添加新数据行时,您不一定需要在表中指定每个列。
1INSERT INTO factoryEmployees
2(name, position, hourlyWage)
3VALUES
4('Harry', 'whatzit engineer', 26.50);
1[secondary_label Output]
2Query OK, 1 row affected (0.01 sec)
如果您打算输入表中的每个列的值行,则不需要包含列名,请记住,您输入的值仍然必须与列在表定义中定义的顺序一致。
在本示例中,列出的值与FactoryEmployee表的CREATE TABLE陈述中列列的顺序一致:
1INSERT INTO factoryEmployees
2VALUES
3('Marie', 'doodad welder', 'production', 27.88, '2018-03-29');
1[secondary_label Output]
2Query OK, 1 row affected (0.00 sec)
您还可以同时添加多个记录,如下,将每个行分隔成一个字符串:
1INSERT INTO factoryEmployees
2VALUES
3('Giles', 'gizmo inspector', 'quality assurance', 26.50, '2019-08-06'),
4('Daphne', 'gizmo presser', 'production', 32.45, '2017-11-12'),
5('Joan', 'whatzit analyst', 'quality assurance', 29.00, '2017-04-29');
1Query OK, 3 rows affected (0.00 sec)
2Records: 3 Duplicates: 0 Warnings: 0
使用SELECT声明复制数据
您可以从一个表中复制多个数据行,然后使用选择查询将其插入到另一个表中。
这种类型的操作的语法看起来像这样:
1INSERT INTO table_A (col_A1, col_A2, col_A3)
2SELECT col_B1, col_B2, col_B3
3FROM table_B;
相反,这个示例语法跟随列表中的VALUES关键字,然后是SELECT语句,而这个示例语法中的SELECT语句只包含FROM语句,但任何有效的查询都可以工作。
为了说明,请运行下面的创建表操作来创建一个名为展示室员工的新表. 请注意,此表的列具有相同的名称和数据类型,如前一节使用的工厂员工表中的三个列:
1CREATE TABLE showroomEmployees (
2name varchar(30),
3hourlyWage decimal(4,2),
4startDate date
5);
1[secondary_label Output]
2Query OK, 0 rows affected (0.02 sec)
现在,您可以将此新表加载到之前创建的FactoryEmployees表中的一些数据,通过在INSERT INTO声明中包含一个SELECT查询。
如果选择查询返回与目标表的列相同的列数量,并且它们也有兼容的匹配数据类型,则可以将列表从输入语句中排除:
1INSERT INTO showroomEmployees
2SELECT
3factoryEmployees.name,
4factoryEmployees.hourlyWage,
5factoryEmployees.startDate
6FROM factoryEmployees
7WHERE name = 'Agnes';
1[secondary_label Output]
2Query OK, 1 row affected (0.01 sec)
3Records: 1 Duplicates: 0 Warnings: 0
<$>[注]
注:此操作的选择查询中列出的列,每个列都先有表名工厂员工和一个期限。当您在参考类似此类列时指定表名时,它被称为完全合格的列参考_。
1INSERT INTO showroomEmployees
2SELECT
3name,
4hourlyWage,
5startDate
6FROM factoryEmployees
7WHERE name = 'Agnes';
本节中的示例使用完全合格的列引用来提供清晰度,但这样做可能是一个很好的练习习惯,不仅可以帮助您的SQL更容易理解和解决问题,而且在某些指向多个表的操作中,如包含‘JOIN’条款的查询 <$>)中,需要完全合格的列引用。
此操作中的SELECT声明包含一个WHERE条款,导致查询只返回来自工厂员工表的行,其名称列包含值Agnes。
若要确认此情况,请运行以下查询,以返回showroomEmployees表中的每个记录:
1SELECT * FROM showroomEmployees;
1[secondary_label Output]
2+-------+------------+------------+
3| name | hourlyWage | startDate |
4+-------+------------+------------+
5| Agnes | 26.50 | 2017-05-01 |
6+-------+------------+------------+
71 row in set (0.00 sec)
例如,下列语句中的查询将返回工厂员工数据库中的每个记录,其中名称列中的值不是从J开始的:
1INSERT INTO showroomEmployees
2SELECT
3factoryEmployees.name,
4factoryEmployees.hourlyWage,
5factoryEmployees.startDate
6FROM factoryEmployees
7WHERE name NOT LIKE 'J%';
1[secondary_label Output]
2Query OK, 5 rows affected (0.01 sec)
3Records: 5 Duplicates: 0 Warnings: 0
再次运行此查询以返回showroomEmployees表中的每个记录:
1SELECT * FROM showroomEmployees;
1+--------+------------+------------+
2| name | hourlyWage | startDate |
3+--------+------------+------------+
4| Agnes | 26.50 | 2017-05-01 |
5| Agnes | 26.50 | 2017-05-01 |
6| Harry | 26.50 | NULL |
7| Marie | 27.88 | 2018-03-29 |
8| Giles | 26.50 | 2019-08-06 |
9| Daphne | 32.45 | 2017-11-12 |
10+--------+------------+------------+
116 rows in set (0.00 sec)
请注意,名列中有两个与Agnes相同的行。每次运行使用SELECT的INSERT INTO陈述时,SQL 将查询的结果视为新数据集。
自动输入数据
创建表时,可以将某些属性应用到列中,从而导致 RDBMS 自动填充数据。
为了说明,运行下面的陈述来定义一个名为interns的表。这将创建一个名为interns的表,该表有三个列。本示例中的第一个列internID包含int类型的数据。 但是,请注意,它还包含AUTO_INCREMENT属性。
类似地,第二列部门包含DEFAULT关键字,这会导致 RDBMS 在本示例中自动插入默认值生产 - 如果您从INSERT INTO语句的列表中排除部门:
1CREATE TABLE interns (
2internID int AUTO_INCREMENT PRIMARY KEY,
3department varchar(20) DEFAULT 'production',
4name varchar(30)
5);
<$>[注]
**注:AUTO_INCREMENT属性是MySQL特征,但许多RDBMS都有自己的整数增加方法。
以下是有关几个流行的开源数据库的主题的官方文档:
美元
要演示这些特征,请通过运行以下INSERT INTO语句来加载内部表中的一些数据,此操作只为名称列指定值:
1INSERT INTO interns (name) VALUES ('Pierre'), ('Sheila'), ('Francois');
1[secondary_label Output]
2Query OK, 3 rows affected (0.01 sec)
3Records: 3 Duplicates: 0 Warnings: 0
然后运行此查询以返回表中的每个记录:
1SELECT * FROM interns;
1[secondary_label Output]
2+----------+------------+----------+
3| internID | department | name |
4+----------+------------+----------+
5| 1 | production | Pierre |
6| 2 | production | Sheila |
7| 3 | production | Francois |
8+----------+------------+----------+
93 rows in set (0.00 sec)
此输出表明,由于列的定义,以前的INSERT INTO语句在internID和部门中添加了值,即使它们没有指定。
若要将默认值以外的值添加到部门列中,您需要在输入语句中指定该列,如下:
1INSERT INTO interns (name, department)
2VALUES
3('Jacques', 'management'),
4('Max', 'quality assurance'),
5('Edith', 'management'),
6('Daniel', DEFAULT);
1[secondary_label Output]
2Query OK, 4 rows affected (0.00 sec)
3Records: 4 Duplicates: 0 Warnings: 0
请注意,本示例中提供的最后一个值行包含DEFAULT代替字符串值的关键字,这会导致数据库插入默认值(生产):
1SELECT * FROM interns;
1[secondary_label Output]
2+----------+-------------------+----------+
3| internID | department | name |
4+----------+-------------------+----------+
5| 1 | production | Pierre |
6| 2 | production | Sheila |
7| 3 | production | Francois |
8| 4 | management | Jacques |
9| 5 | quality assurance | Max |
10| 6 | management | Edith |
11| 7 | production | Daniel |
12+----------+-------------------+----------+
137 rows in set (0.00 sec)
结论
通过阅读本指南,您了解了将数据插入表中的几种不同的方法,包括用VALUES关键字指定单个数据行,使用SELECT查询复制整个数据集,以及定义 SQL 将自动插入数据的列。
请记住,每个 SQL 数据库都使用其独特的语言实现,因此,您应该咨询您的 DBMS 的官方文档,以便更全面地描述它如何处理INSERT INTO声明以及可用的选项。
如果您想了解有关使用 SQL 的更多信息,我们鼓励您查看本系列中的其他教程在 如何使用 SQL。