SQL 数据的插入（INSERT 语句的使用方法）-编程学习网

短信预约信息系统项目管理师报名、考试、查分时间动态提醒

SQL 数据的插入（INSERT 语句的使用方法）

一、什么是 INSERT
二、INSERT 语句的基本语法
三、列清单的省略
四、插入 NULL
五、插入默认值
六、从其他表中复制数据
请参阅

学习重点

使用 INSERT 语句可以向表中插入数据（行）。原则上，INSERT 语句每次执行一行数据的插入。

将列名和值用逗号隔开，分别括在 () 内，这种形式称为清单。

对表中所有列进行 INSERT 操作时可以省略表名后的列清单。

插入 NULL 时需要在 VALUES 子句的值清单中写入 NULL。

可以为表中的列设定默认值（初始值），默认值可以通过在 CREATE TABLE 语句中为列设置 DEFAULT 约束来设定。

插入默认值可以通过两种方式实现，即在 INSERT 语句的 VALUES 子句中指定 DEFAULT 关键字（显式方法），或省略列清单（隐式方法）。

使用 INSERT…SELECT 可以从其他表中复制数据。

一、什么是 `INSERT`

表的创建中给大家介绍了用来创建表的 CREATE TABLE 语句。通过 CREATE TABLE 语句创建出来的表，可以被认为是一个空空如也的箱子。只有把数据装入到这个箱子后，它才能称为数据库。用来装入数据的 SQL 就是 INSERT（插入）（图 1）。

KEYWORD

INSERT 语句

本节将会和大家一起学习 INSERT 语句。

要学习 INSERT 语句，我们得首先创建一个名为 ProductIns 的表。请大家执行代码清单 1 中的 CREATE TABLE 语句。该表除了为 sale_price 列（销售单价）设置了 DEFAULT 0 的约束之外，其余内容与之前使用的 Product（商品）表完全相同。DEFAULT 0 的含义将会在随后进行介绍，大家暂时可以忽略。

代码清单 1　创建 ProductIns 表的 CREATE TABLE 语句

CREATE TABLE ProductIns
(product_id      CHAR(4)      NOT NULL,
 product_name    VARCHAR(100) NOT NULL,
 product_type    VARCHAR(32)  NOT NULL,
 sale_price      INTEGER      DEFAULT 0,
 purchase_price  INTEGER      ,
 regist_date     DATE         ,
 PRIMARY KEY (product_id));

如前所述，这里仅仅是创建出了一个表，并没有插入数据。接下来，我们就向 ProductIns 表中插入数据。

二、`INSERT` 语句的基本语法

表的删除和更新中讲到向 CREATE TABLE 语句创建出的 Product 表中插入数据的 SQL 语句时，曾介绍过 INSERT 语句的使用示例，但当时的目的只是为学习 SELECT 语句准备所需的数据，并没有详细介绍其语法。下面就让我们来介绍一下 INSERT 语句的语法结构。

INSERT 语句的基本语法如下所示。

语法 1　INSERT 语句

INSERT INTO <表名> (列1, 列2, 列3, ……) VALUES (值1, 值2, 值3, ……);

例如，我们要向 ProductIns 表中插入一行数据，各列的值如下所示。

`product_id` （商品编号）	`product_name` （商品名称）	`product_type` （商品种类）	`sale_price` （销售单价）	`purchase_price` （进货单价）	`regist_date` （登记日期）
0001	T 恤衫	衣服	1000	500	2009-09-20

此时使用的 INSERT 语句可参见代码清单 2。

代码清单 2　向表中插入一行数据

INSERT INTO ProductIns (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES ("0001", "T恤衫", "衣服", 1000, 500, "2009-09-20");

由于 product_id 列（商品编号）和 product_name 列（商品名称）是字符型，所以插入的数据需要像 "0001" 这样用单引号括起来。日期型的 regist_date（登记日期）列也是如此 ^[1]。

将列名和值用逗号隔开，分别括在()内，这种形式称为清单。代码清单 2 中的 INSERT 语句包含如下两个清单。

A：列清单→(product_id, product_name, product_type, sale_price, purchase_price, regist_date)

B：值清单→("0001", "T恤衫", "衣服", 1000, 500,"2009-09-20")

KEYWORD

清单

列清单

值清单

当然，表名后面的列清单和 VALUES 子句中的值清单的列数必须保持一致。如下所示，列数不一致时会出错，无法插入数据 ^[2]。

-- VALUES子句中的值清单缺少一列
INSERT INTO ProductIns (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES ("0001", "T恤衫", "衣服", 1000, 500);

此外，原则上，执行一次 INSERT 语句会插入一行数据 ^[3]。因此，插入多行时，通常需要循环执行相应次数的 INSERT 语句。

法则 1

原则上，执行一次 INSERT 语句会插入一行数据。

专栏

多行 INSERT

法则 1 中介绍了“执行一次 INSERT 语句会插入一行数据”的原则。虽然在大多数情况下该原则都是正确的，但它也仅仅是原则而已，其实很多 RDBMS 都支持一次插入多行数据，这样的功能称为多行 INSERT（multi row INSERT）。

KEYWORD

多行 INSERT

其语法请参见代码清单A，将多条 VALUES 子句通过逗号进行分隔排列。

代码清单 A　通常的 INSERT 和多行 INSERT
-- 通常的INSERT
INSERT INTO ProductIns VALUES ("0002", "打孔器", "办公用品", 500, 320, "2009-09-11");
INSERT INTO ProductIns VALUES ("0003", "运动T恤", "衣服", 4000, 2800, NULL);
INSERT INTO ProductIns VALUES ("0004", "菜刀", "厨房用具", 3000, 2800, "2009-09-20");

-- 多行INSERT （Oracle以外）
INSERT INTO ProductIns VALUES ("0002", "打孔器", "办公用品", 500, 320, "2009-09-11"),
("0003", "运动T恤", "衣服", 4000, 2800, NULL),
("0004", "菜刀", "厨房用具", 3000, 2800, "2009-09-20");
该语法很容易理解，并且减少了书写语句的数量，非常方便。但是，使用该语法时请注意以下几点。

首先，INSERT 语句的书写内容及插入的数据是否正确。若不正确会发生 INSERT 错误，但是由于是多行插入，和特定的单一行插入相比，想要找出到底是哪行哪个地方出错了，就变得十分困难。

其次，多行 INSERT 的语法并不适用于所有的 RDBMS。该语法适用于 DB2、SQL、SQL Server、PostgreSQL 和 MySQL，但不适用于 Oracle。

特定的 SQL

Oracle 使用如下语法来巧妙地完成多行 INSERT 操作。
-- Oracle中的多行INSERT
INSERT ALL INTO ProductIns VALUES ("0002", "打孔器", "办公用品", 500, 320, "2009-09-11")
          INTO ProductIns VALUES ("0003", "运动T恤", "衣服", 4000, 2800, NULL)
          INTO ProductIns VALUES ("0004", "菜刀", "厨房用具", 3000, 2800, "2009-09-20")
SELECT * FROM DUAL;
DUAL 是 Oracle 特有（安装时的必选项）的一种临时表 ^[4]。因此“SELECT * FROM DUAL”部分也只是临时性的，并没有实际意义。

三、列清单的省略

对表进行全列 INSERT 时，可以省略表名后的列清单。这时 VALUES 子句的值会默认按照从左到右的顺序赋给每一列。因此，代码清单 3 中的两个 INSERT 语句会插入同样的数据。

代码清单 3　省略列清单

-- 包含列清单
INSERT INTO ProductIns (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES ("0005", "高压锅", "厨房用具", 6800, 5000, "2009-01-15");

-- 省略列清单
INSERT INTO ProductIns VALUES ("0005", "高压锅", "厨房用具", 6800, 5000, "2009-01-15");

四、插入 `NULL`

INSERT 语句中想给某一列赋予 NULL 值时，可以直接在 VALUES 子句的值清单中写入 NULL。例如，要向 purchase_price 列（进货单价）中插入 NULL，就可以使用代码清单 4 中的 INSERT 语句。

代码清单 4　向 purchase_price 列中插入 NULL

INSERT INTO ProductIns (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES ("0006", "叉子", "厨房用具", 500, NULL, "2009-09-20");

但是，想要插入 NULL 的列一定不能设置 NOT NULL 约束。向设置了 NOT NULL 约束的列中插入 NULL 时，INSERT 语句会出错，导致数据插入失败。

插入失败指的是希望通过 INSERT 语句插入的数据无法正常插入到表中，但之前已经插入的数据并不会被破坏 ^[5]。

五、插入默认值

我们还可以向表中插入默认值（初始值）。可以通过在创建表的 CREATE TABLE 语句中设置 DEFAULT 约束来设定默认值。

KEYWORD

默认值
DEFAULT 约束

本文开头创建的 ProductIns 表的定义部分请参见代码清单 5。其中 DEFAULT 0 就是设置 DEFAULT 约束的部分。像这样，我们可以通过“DEFAULT <默认值>”的形式来设定默认值。

代码清单 5　创建 ProductIns 表的 CREATE TABLE 语句（节选）

CREATE TABLE ProductIns
(product_id     CHAR(4)  NOT NULL,
         （略）
 sale_price      INTEGER  DEFAULT 0, -- 销售单价的默认值设定为0;
         （略）
 PRIMARY KEY (product_id));

如果在创建表的同时设定了默认值，就可以在 INSERT 语句中自动为列赋值了。默认值的使用方法通常有显式和隐式两种。

通过显式方法插入默认值

在 VALUES 子句中指定 DEFAULT 关键字（代码清单 6）。

KEYWORD

DEFAULT 关键字

代码清单 6　通过显式方法设定默认值

INSERT INTO ProductIns (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES ("0007", "擦菜板", "厨房用具", DEFAULT, 790, "2009-04-28");

这样一来，RDBMS 就会在插入记录时自动把默认值赋给对应的列。

我们可以使用 SELECT 语句来确认通过 INSERT 语句插入的数据行。

-- 确认插入的数据行；
SELECT * FROM ProductIns WHERE product_id = "0007";

因为 sale_price 列（销售单价）的默认值是 0，所以 sale_price 列被赋予了值 0。

执行结果

product_id | product_name | product_type | sale_price | purchase_price | regist_date
-----------+--------------+--------------+------------+----------------+----------
0007      | 擦菜板       | 厨房用具     |          0 |            790  | 2008-04-28

通过隐式方法插入默认值

插入默认值时也可以不使用 DEFAULT 关键字，只要在列清单和 VALUES 中省略设定了默认值的列就可以了。我们可以像代码清单 7 那样，从 INSERT 语句中删除 sale_price 列（销售单价）。

代码清单 7　通过隐式方法设定默认值

通过隐式方法设定默认值

这样也可以给 sale_price 赋上默认值 0。

那么在实际使用中哪种方法更好呢？笔者建议大家使用显式的方法。因为这样可以一目了然地知道 sale_price 列使用了默认值，SQL 语句的含义也更加容易理解。

说到省略列名，还有一点要说明一下。如果省略了没有设定默认值的列，该列的值就会被设定为 NULL。因此，如果省略的是设置了 NOT NULL 约束的列，INSERT 语句就会出错（代码清单 8）。请大家一定要注意。

代码清单 8　未设定默认值的情况

-- 省略purchase_price列（无约束）：会赋予“NULL”
INSERT INTO ProductIns (product_id, product_name, product_type, sale_price, regist_date) VALUES ("0008", "圆珠笔", "办公用品", 100, "2009-11-11");

-- 省略product_name列（设置了NOT NULL约束）：错误！
INSERT INTO ProductIns (product_id, product_type, sale_price,purchase_price, regist_date) VALUES ("0009", "办公用品", 1000, 500, "2009-12-12");

法则 2

省略 INSERT 语句中的列名，就会自动设定为该列的默认值（没有默认值时会设定为 NULL）。

六、从其他表中复制数据

要插入数据，除了使用 VALUES 子句指定具体的数据之外，还可以从其他表中复制数据。下面我们就来学习如何从一张表中选取数据，复制到另外一张表中。

要学习该方法，我们首先得创建一张表（代码清单 9）。

代码清单 9　创建 ProductCopy 表的 CREATE TABLE 语句

-- 用来插入数据的商品复制表
CREATE TABLE ProductCopy
(product_id      CHAR(4)      NOT NULL,
 product_name    VARCHAR(100) NOT NULL,
 product_type    VARCHAR(32)  NOT NULL,
 sale_price      INTEGER      ,
 purchase_price  INTEGER      ,
 regist_date     DATE         ,
 PRIMARY KEY (product_id));

ProductCopy（商品复制）表的结构与之前使用的 Product（商品）表完全一样，只是更改了一下表名而已。

接下来，就让我们赶快尝试一下将 Product 表中的数据插入到 ProductCopy 表中吧。代码清单 10 中的语句可以将查询的结果直接插入到表中。

代码清单 10　INSERT ... SELECT 语句

-- 将商品表中的数据复制到商品复制表中
INSERT INTO ProductCopy (product_id, product_name, product_type, sale_price, purchase_price, regist_date)
SELECT product_id, product_name, product_type, sale_price, purchase_price, regist_date
  FROM Product;

执行该 INSERT … SELECT 语句时，如果原来 Product 表中有 8 行数据，那么 ProductCopy 表中也会插入完全相同的 8 行数据。当然，Product 表中的原有数据不会发生改变。因此，INSERT … SELECT 语句可以在需要进行数据备份时使用（图 2）。

KEYWORD

INSERT … SELECT 语句

多种多样的 SELECT 语句

该 INSERT 语句中的 SELECT 语句，也可以使用 WHERE 子句或者 GROUP BY 子句等。目前为止学到的各种 SELECT 语句也都可以使用 ^[6]。对在关联表之间存取数据来说，这是非常方便的功能。

接下来我们尝试一下使用包含 GROUP BY 子句的 SELECT 语句进行插入。代码清单 11 中的语句创建了一个用来插入数据的表。

代码清单 11　创建 ProductType 表的 CREATE TABLE 语句

-- 根据商品种类进行汇总的表；
CREATE TABLE ProductType
(product_type       VARCHAR(32)     NOT NULL,
 sum_sale_price     INTEGER         ,
 sum_purchase_price INTEGER         ,
 PRIMARY KEY (product_type));

该表是用来存储根据商品种类（product_type）计算出的销售单价合计值以及进货单价合计值的表。下面就让我们使用代码清单 12 中的 INSERT ... SELECT 语句，从 Product 表中选取出数据插入到这张表中吧。

代码清单 12　插入其他表中数据合计值的 INSERT ... SELECT 语句

INSERT INTO ProductType (product_type, sum_sale_price, sum_purchase_price)
SELECT product_type, SUM(sale_price), SUM(purchase_price)
  FROM Product
 GROUP BY product_type;

通过 SELECT 语句对插入结果进行确认，我们发现 ProductType 表中插入了以下 3 行数据。

-- 确认插入的数据行
SELECT * FROM ProductType;

执行结果

 product_type | sum_sale_price  | sum_purchase_price
--------------+-----------------+--------------------
 衣服         |            5000 |               3300
 办公用品     |             600 |                320
 厨房用具     |           11180 |               8590

法则 3

INSERT 语句的 SELECT 语句中，可以使用 WHERE 子句或者 GROUP BY 子句等任何 SQL 语法（但使用 ORDER BY 子句并不会产生任何效果）。

请参阅

数据的插入
数据的删除
数据的更新
事务

（完）

有关日期型的介绍，请参考表的创建节。 ↩︎
但是使用默认值时列数无需完全一致。相关内容将会在随后的“插入默认值”中进行介绍。 ↩︎
插入多行的情况，请参考专栏“多行 INSERT”。 ↩︎
在书写没有参照表的 SELECT 语句时，写在 FROM 子句中的表。它并没有实际意义，也不保存任何数据，同时也不能作为 INSERT 和 UPDATE 的对象。 ↩︎
不仅是 INSERT，DELETE 和 UPDATE 等更新语句也一样，SQL 语句执行失败时都不会对表中数据造成影响。 ↩︎
但即使指定了 ORDER BY 子句也没有任何意义，因为无法保证表内部记录的排列顺序。 ↩︎

文章详情