DB基础第十五篇：数据的插入(INSERT)

java大本营 2021-07-07

2907

什么是 INSERT

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

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

代码清单4-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 语句的基本语法

语法4-1　 INSERT 语句
INSERT INTO < 表名 > ( 列 1 ,  列 2 ,  列 3 ,  …… )  VALUES ( 值 1 ,  值 2 ,  值 3 ,  …… );

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

代码清单4-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 （登记日期）列也是如此。

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

列清单→ ( product _ id ,  product _ name ,  product _ type ,
sale _ price ,  purchase _ price ,  regist _ date )
值清单→ (' 0001 ', ' T 恤衫 ', ' 衣服 ',  1000 ,  500 ,' 2009 - 09 - 20 ')

当然，表名后面的列清单和 VALUES 子句中的值清单的列数必须保持一致。

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

多行 INSERT

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

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

代码清单4- 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。

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特有（安装时的必选项）的一种临时表。因此“ SELECT *FROM DUAL ”部分也只是临时性的，并没有实际意义。

列清单的省略

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

代码清单4-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-4 中的 INSERT 语句。

代码清单4-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 语句插入的数据无法正常插入到表中，但之前已经插入的数据并不会被破坏。

插入默认值

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

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

代码清单4-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 关键字。

代码清单4-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 。

通过隐式方法插入默认值

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

这样也可以给 sale _ price 赋上默认值 0 。

那么在实际使用中哪种方法更好呢？建议大家使用显式的方法。

因为这样可以一目了然地知道 sale _ price 列使用了默认值，SQL 语句的含义也更加容易理解。

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

代码清单4-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');

法则：

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

从其他表中复制数据

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

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

代码清单4-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 表中吧。代码清单 4-10 中的语句可以将查询的结果直接插入到表中。

代码清单4-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语句可以在需要进行数据备份时使用。

多种多样的 SELECT 语句

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

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

代码清单4-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 ）计算出的销售单价合计值以及进货单价合计值的表。下面就让我们使用代码清单 4-12 中的 INSERT … SELECT 语句，从 Product 表中选取出数据插入到这张表中吧。

代码清单4-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 行数据。

法则：

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

总结：

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

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

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

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

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

● 插入默认值可以通过两种方式实现，即在 INSERT 语句的 VALUES 子句

中指定 DEFAULT 关键字（显式方法），或省略列清单（隐式方法）。

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

<END>

数据库

文章转载自java大本营，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

DB基础第十五篇：数据的插入(INSERT)

评论