SQL数据的插入(INSERT语句的使用方法)

database

目录

  • 一、什么是 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 语句

图 1 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;

DUALOracle 特有(安装时的必选项)的一种临时表 [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 语句

图 2 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 子句并不会产生任何效果)。

请参阅

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

(完)


  1. 有关日期型的介绍,请参考 表的创建 节。 ↩︎

  2. 但是使用默认值时列数无需完全一致。相关内容将会在随后的“插入默认值”中进行介绍。 ↩︎

  3. 插入多行的情况,请参考专栏“多行 INSERT”。 ↩︎

  4. 在书写没有参照表的 SELECT 语句时,写在 FROM 子句中的表。它并没有实际意义,也不保存任何数据,同时也不能作为 INSERTUPDATE 的对象。 ↩︎

  5. 不仅是 INSERTDELETEUPDATE 等更新语句也一样,SQL 语句执行失败时都不会对表中数据造成影响。 ↩︎

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

以上是 SQL数据的插入(INSERT语句的使用方法) 的全部内容, 来源链接: utcz.com/z/536099.html

回到顶部