100.数据的插入

之前学的都是查询，所使用的 SQL 语句都是 SELECT 语句。SELECT 语句并不会更改表中数据，也就是说，SELECT 语句是读取专用的指令。

接下来学习DBMS 中用来更新表中数据的方法。数据的更新处理大体可以分为插入（INSERT ）、删除（DELETE ）和更新（UPDATE ）三类。

此外，还会给大家介绍数据库中用来管理数据更新的重要概念——事务。

‍

‍

什么是insert

之前在查询基础的时候，使用了用来创建表的 CREATE TABLE 语句。通过 CREATE TABLE 语句创建出来的表，可以被认为是一个空空如也的箱子。我们需要将数据装入到这个箱子，用来装入数据的 SQL 就是 INSERT（插入）。

我们新创建一个名为 ProductIns 的表，用来学习insert。

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

‍

该表和Product表几乎相同，除了DEFAULT 0 的约束：这个约束的含义将会在随后进行介绍

‍

‍

‍

insert语句的基本语法

insert语句的基本语法：

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

‍

‍

‍

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

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

‍

使用的 INSERT 语句：

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

‍

将列名和值用逗号隔开，分别括在（）内，这种形式称为清单 。刚刚的 INSERT 语句包含如下两个清单：

• 列清单 →(product_id, product_name, product_type, sale_price, purchase_price, regist_date)
• 值清单 →('0001', 'T恤衫', '衣服', 1000, 500,'2009-09-20')

‍

注意：

• 列清单和值清单的列数必须保持一致
• 字符型和日期型的数据要用单引号括起来

‍

多行insert

原则上，执行一次 insert 语句会插入一行数据。其实很多 RDBMS 都支持一次插入多行数据，这样的功能称为多行 INSERT （multi row INSERT）。例如：

-- 通常的INSERT，插入3条数据需要3条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，一条insert语句插入3条数据
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 。

‍

‍

‍

‍

列清单的省略

对表进行全列 INSERT 时，可以省略表名后的列清单。这时 VALUES 子句的值会按照创建表的时候，从上到下的顺序赋给每一列。

-- 包含列清单
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：

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

‍

‍

注意：如果向设置了 NOT NULL 约束的列中插入 NULL 时，会出错，导致数据插入失败。

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

不仅是 INSERT ，DELETE 和 UPDATE 等更新语句也一样，SQL 语句执行失败时都不会对表中数据造成影响。

‍

‍

‍

‍

插入默认值

我们还可以向表中插入默认值 （初始值）。可以通过在创建表的 CREATE TABLE 语句中设置 DEFAULT 约束 来设定默认值，例如本文开头创建的ProductIns表，就为sale_price设置了默认值0：

 sale_price      INTEGER      DEFAULT 0,

‍

像这样，我们可以通过“DEFAULT < 默认值 > ”的形式来设定默认值。

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

‍

通过显式方法插入默认值

在 VALUES 子句中指定 DEFAULT 关键字：

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

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

‍

‍

‍

通过隐式方法插入默认值

插入默认值时也可以不使用 DEFAULT 关键字，只要在列清单和 VALUES 中省略设定了默认值的列就可以了。这样也可以给 sale_price 赋上默认值 0 。

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

那么在实际使用中哪种方法更好呢？建议使用显式的方式，因为这样一目了然地知道哪列使用了默认值，可读性更高。

说到省略列名，还有一点要注意：如果省略了没有设定默认值的列，该列的值就会被设定为 NULL 。因此，如果省略的是设置了 NOT NULL 约束的列，INSERT 语句就会出错。

‍

‍

‍

从其他表中复制数据

要插入数据，除了使用 VALUES 子句指定具体的数据之外，还可以从其他表中复制数据。

要学习该方法，我们首先得创建一张表：

-- 用来插入数据的商品复制表
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 （商品）表完全一样，除了表名。然后我们就可以开始复制了：

-- 将商品表中的数据复制到商品复制表中
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 语句可以在需要进行数据备份时使用

‍

除了全表复制，还可以使用 WHERE 子句或者 GROUP BY 子句等。目前为止学到的各种 SELECT 语句也都可以使用 。

但即使指定了 ORDER BY 子句也没有任何意义，因为无法保证表内部记录的排列顺序。

例如，我们想要将Product 表的销售单价合计值，以及进货单价合计值存储到一个新的表：

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


-- 插入数据
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 * FROM ProductType;

--结果：
+--------------+----------------+--------------------+
| product_type | sum_sale_price | sum_purchase_price |
+--------------+----------------+--------------------+
| 办公用品     |            600 |                320 |
| 厨房用具     |          11180 |               8590 |
| 衣服         |           5000 |               3300 |
+--------------+----------------+--------------------+
3 rows in set (0.00 sec)

‍

‍

（完）