SQL 如何插入、删除和更新数据,SQL数据的更新处理
目录- 一、数据的插入(INSERT 语句的使用方法)
- 1.1 什么是 INSERT
- 1.2 INSERT 语句的基本语法
- 1.3 列清单的省略
- 1.4 插入 NULL
- 1.5 插入默认值
- 1.6 从其他表中复制数据
- 二、数据的删除(DELETE 语句的使用方法)
- 2.1 DROP TABLE 语句和 DELETE 语句
- 2.2 DELETE 语句的基本语法
- 2.3 指定删除对象的 DELETE 语句(搜索型 DELETE)
- 三、数据的更新(UPDATE 语句的使用方法)
- 3.1 UPDATE 语句的基本语法
- 3.2 指定条件的 UPDATE 语句(搜索型 UPDATE)
- 3.3 使用 NULL 进行更新
- 3.4 多列更新
本文将会给大家介绍 DBMS 中用来更新表中数据的方法。SQL 数据的更新处理大体可以分为插入(INSERT
)、删除(DELETE
)和更新(UPDATE
)三类。本文将会对这三类更新方法进行详细介绍。
一、数据的插入(INSERT 语句的使用方法)
本节重点
-
使用
INSERT
语句可以向表中插入数据(行)。原则上,INSERT
语句每次执行一行数据的插入。 -
将列名和值用逗号隔开,分别括在
()
内,这种形式称为清单。 -
对表中所有列进行
INSERT
操作时可以省略表名后的列清单。 -
插入
NULL
时需要在VALUES
子句的值清单中写入NULL
。 -
可以为表中的列设定默认值(初始值),默认值可以通过在
CREATE TABLE
语句中为列设置DEFAULT
约束来设定。 -
插入默认值可以通过两种方式实现,即在
INSERT
语句的VALUES
子句中指定DEFAULT
关键字(显式方法),或省略列清单(隐式方法)。 -
使用
INSERT…SELECT
可以从其他表中复制数据。
1.1 什么是 INSERT
SQL 如何对表进行创建、更新和删除操作 给大家介绍了用来创建表的 CREATE TABLE
语句。通过 CREATE TABLE
语句创建出来的表,可以被认为是一个空空如也的箱子。
只有把数据装入到这个箱子后,它才能称为数据库。用来装入数据的 SQL 就是 INSERT
(插入)(图 1)。
本节将会和大家一起学习 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
表中插入数据。
1.2 INSERT 语句的基本语法
SQL 如何对表进行创建、更新和删除操作 中讲到向 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')
当然,表名后面的列清单和 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)。其语法请参见代码清单 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 特有(安装时的必选项)的一种临时表。因此“SELECT * FROM DUAL
”部分也只是临时性的,并没有实际意义。在书写没有参照表的
SELECT
语句时,写在FROM
子句中的表。它并没有实际意义,也不保存任何数据,同时也不能作为INSERT
和UPDATE
的对象。
1.3 列清单的省略
对表进行全列 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');
1.4 插入 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
语句插入的数据无法正常插入到表中,但之前已经插入的数据并不会被破坏 [4]。
1.5 插入默认值
我们还可以向表中插入默认值(初始值)。可以通过在创建表的 CREATE TABLE
语句中设置 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)。代码清单 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
)。
1.6 从其他表中复制数据
要插入数据,除了使用 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)。
-
多种多样的 SELECT 语句
该
INSERT
语句中的SELECT
语句,也可以使用WHERE
子句或者GROUP BY
子句等。目前为止学到的各种
SELECT
语句也都可以使用 [5]。对在关联表之间存取数据来说,这是非常方便的功能。接下来我们尝试一下使用包含
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
子句并不会产生任何效果)。
二、数据的删除(DELETE 语句的使用方法)
本节重点
如果想将整个表全部删除,可以使用
DROP TABLE
语句,如果只想删除表中全部数据,需使用DELETE
语句。如果想删除部分数据行,只需在
WHERE
子句中书写对象数据的条件即可。通过WHERE
子句指定删除对象的DELETE
语句称为搜索型DELETE
语句。
2.1 DROP TABLE 语句和 DELETE 语句
上一节我们学习了插入数据的方法,本节我们来学习如何删除数据。删除数据的方法大体可以分为以下两种。
① DROP TABLE
语句可以将表完全删除
② DELETE
语句会留下表(容器),而删除表中的全部数据
① 中的 DROP TABLE
语句我们已经在 SQL 如何对表进行创建、更新和删除操作 中学过了,此处再简单回顾一下。
DROP TABLE
语句会完全删除整张表,因此删除之后再想插入数据,就必须使用 CREATE TABLE
语句重新创建一张表。
反之,② 中的 DELETE
语句在删除数据(行)的同时会保留数据表,因此可以通过 INSERT
语句再次向表中插入数据。
本节所要介绍的删除数据,指的就是只删除数据的 DELETE
语句。
此外,我们在 SQL 如何对表进行创建、更新和删除操作 中也提到过,不管使用哪种方法,删除数据时都要慎重,一旦误删,想要恢复数据就会变得十分困难。
2.2 DELETE 语句的基本语法
DELETE
语句的基本语法如下所示,十分简单。
语法 2 保留数据表,仅删除全部数据行的 DELETE 语句
DELETE FROM <表名>;
执行使用该基本语法的 DELETE
语句,就可以删除指定的表中的全部数据行了。因此,想要删除 Product
表中全部数据行,就可以参照代码清单 13 来书写 DELETE
语句。
代码清单 13 清空 Product 表
DELETE FROM Product;
如果语句中忘了写 FROM
,而是写成了“DELETE <表名>
”,或者写了多余的列名,都会出错,无法正常执行,请大家特别注意。
前者无法正常执行的原因是删除对象不是表,而是表中的数据行(记录)。这样想的话就很容易理解了吧 [6]。
后者错误的原因也是如此。因为 DELETE
语句的对象是行而不是列,所以 DELETE
语句无法只删除部分列的数据。
因此,在 DELETE
语句中指定列名是错误的。当然,使用星号的写法(DELETE * FROM Product;
)也是不对的,同样会出错。
法则 4
DELETE
语句的删除对象并不是表或者列,而是记录(行)。
2.3 指定删除对象的 DELETE 语句(搜索型 DELETE)
想要删除部分数据行时,可以像 SELECT
语句那样使用 WHERE
子句指定删除条件。这种指定了删除对象的 DELETE
语句称为搜索型 DELETE
[7]。
搜索型 DELETE
的语法如下所示。
语法 3 删除部分数据行的搜索型 DELETE
DELETE FROM <表名>
WHERE <条件>;
下面让我们以 Product
(商品)表为例,来具体研究一下如何进行数据删除(表 1)。
表 1 Product 表
product_id(商品编号) | product_name(商品名称) | product_type(商品种类) | sale_price(销售单价) | purchase_price(进货单价) | regist_date(登记日期) |
---|---|---|---|---|---|
0001 | T 恤衫 | 衣服 | 1000 | 500 | 2009-09-20 |
0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11 |
0003 | 运动 T 恤 | 衣服 | 4000 | 2800 | |
0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-09-20 |
0005 | 高压锅 | 厨房用具 | 6800 | 5000 | 2009-01-15 |
0006 | 叉子 | 厨房用具 | 500 | 2009-09-20 | |
0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2008-04-28 |
0008 | 圆珠笔 | 办公用品 | 100 | 2009-11-11 |
假设我们要删除销售单价(sale_price
)大于等于 4000
元的数据(代码清单 14)。上述表中满足该条件的是“运动 T 恤”和“高压锅”。
代码清单 14 删除销售单价(sale_price)大于等于 4000 元的数据
DELETE FROM Product
WHERE sale_price >= 4000;
WHERE
子句的书写方式与此前介绍的 SELECT
语句完全一样。
通过使用 SELECT
语句确认,表中的数据被删除了 2 行,只剩下 6 行。
-- 确认删除后的结果
SELECT * FROM Product;
执行结果:
product_id | product_name | product_type | sale_price | purchase_price | regist_date
-----------+--------------+--------------+------------+----------------+-----------
0001 | T恤衫 | 衣服 | 1000 | 500 | 2009-09-20
0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11
0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-09-20
0006 | 叉子 | 厨房用具 | 500 | | 2009-09-20
0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2008-04-28
0008 | 圆珠笔 | 办公用品 | 100 | | 2009-11-11
法则 5
可以通过
WHERE
子句指定对象条件来删除部分数据。
与 SELECT
语句不同的是,DELETE
语句中不能使用 GROUP BY
、 HAVING
和 ORDER BY
三类子句,而只能使用 WHERE
子句。
原因很简单,GROUP BY
和 HAVING
是从表中选取数据时用来改变抽取数据形式的,而 ORDER BY
是用来指定取得结果显示顺序的。
因此,在删除表中数据时它们都起不到什么作用。
专栏
删除和舍弃
标准 SQL 中用来从表中删除数据的只有
DELETE
语句。但是,很多数据库产品中还存在另外一种被称为TRUNCATE
的语句。这些产品主要包括 Oracle、SQL Server、PostgreSQL、MySQL 和 DB2。
TRUNCATE
是舍弃的意思,具体的使用方法如下所示。语法 A 只能删除表中全部数据的 TRUNCATE 语句
TRUNCATE <表名>;
与
DELETE
不同的是,TRUNCATE
只能删除表中的全部数据,而不能通过WHERE
子句指定条件来删除部分数据。也正是因为它不能具体地控制删除对象,所以其处理速度比
DELETE
要快得多。实际上,
DELETE
语句在 DML 语句中也属于处理时间比较长的,因此需要删除全部数据行时,使用TRUNCATE
可以缩短执行时间。但是,产品不同需要注意的地方也不尽相同。
例如在 Oracle 中,把
TRUNCATE
定义为 DDL,而不是 DML(因此,Oracle 中的TRUNCATE
不能使用ROLLBACK
。执行TRUNCATE
的同时会默认执行COMMIT
操作。)。使用
TRUNCATE
时,请大家仔细阅读使用手册,多加注意。便利的工具往往还是会存在一些不足之处的。
三、数据的更新(UPDATE 语句的使用方法)
本节重点
-
使用
UPDATE
语句可以更改(更新)表中的数据。 -
更新部分数据行时可以使用
WHERE
来指定更新对象的条件。通过WHERE
子句指定更新对象的UPDATE
语句称为搜索型UPDATE
语句。 -
UPDATE
语句可以将列的值更新为NULL
。 -
同时更新多列时,可以在
UPDATE
语句的SET
子句中,使用逗号分隔更新对象的多个列。
3.1 UPDATE 语句的基本语法
使用 INSERT
语句向表中插入数据之后,有时却想要再更改数据,例如“将商品销售单价登记错了”等的时候。
这时并不需要把数据删除之后再重新插入,使用 UPDATE
语句就可以改变表中的数据了。
和 INSERT
语句、DELETE
语句一样,UPDATE
语句也属于 DML 语句。通过执行该语句,可以改变表中的数据。其基本语法如下所示。
语法 4 改变表中数据的 UPDATE 语句
UPDATE <表名>
SET <列名> = <表达式>;
将更新对象的列和更新后的值都记述在 SET
子句中。
我们还是以 Product
(商品)表为例,由于之前我们删除了“销售单价大于等于 4000
元”的 2 行数据,现在该表中只剩下了 6 行数据了(表 2)。
表 2 Product 表
product_id(商品编号) | product_name(商品名称) | product_type(商品种类) | sale_price(销售单价) | purchase_price(进货单价) | regist_date(登记日期) |
---|---|---|---|---|---|
0001 | T 恤衫 | 衣服 | 1000 | 500 | 2009-09-20 |
0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11 |
0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-09-20 |
0006 | 叉子 | 厨房用具 | 500 | 2009-09-20 | |
0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2008-04-28 |
0008 | 圆珠笔 | 办公用品 | 100 | 2009-11-11 |
接下来,让我们尝试把 regist_date
列(登记日期)的所有数据统一更新为“2009-10-10”。具体的 SQL 语句请参见代码清单 15。
代码清单 15 将登记日期全部更新为“2009-10-10”
UPDATE Product
SET regist_date = '2009-10-10';
表中的数据有何变化呢?我们通过 SELECT
语句来确认一下吧。
-- 确认更新内容
SELECT * FROM Product ORDER BY product_id;
执行结果:
此时,连登记日期原本为 NULL
的数据行(运动 T 恤)的值也更新为 2009-10-10
了。
3.2 指定条件的 UPDATE 语句(搜索型 UPDATE)
接下来,让我们看一看指定更新对象的情况。
更新数据时也可以像 DELETE
语句那样使用 WHERE
子句,这种指定更新对象的 UPDATE
语句称为搜索型 UPDATE
语句。
该语句的语法如下所示(与 DELETE
语句十分相似)。
语法 5 更新部分数据行的搜索型 UPDATE
UPDATE <表名>
SET <列名> = <表达式>
WHERE <条件>;
例如,将商品种类(product_type
)为厨房用具的记录的销售单价(sale_price
)更新为原来的 10 倍,请参见代码清单 16。
代码清单 16 将商品种类为厨房用具的记录的销售单价更新为原来的 10 倍
UPDATE Product
SET sale_price = sale_price * 10
WHERE product_type = '厨房用具';
我们可以使用如下 SELECT
语句来确认更新后的内容。
-- 确认更新内容
SELECT * FROM Product ORDER BY product_id;
执行结果:
该语句通过 WHERE
子句中的“product_type = '厨房用具'
”条件,将更新对象限定为 3 行。
然后通过 SET
子句中的表达式 sale_price * 10
,将原来的单价扩大了 10 倍。
SET
子句中赋值表达式的右边不仅可以是单纯的值,还可以是包含列的表达式。
3.3 使用 NULL 进行更新
使用 UPDATE
也可以将列更新为 NULL
(该更新俗称为 NULL
清空)。
此时只需要将赋值表达式右边的值直接写为 NULL
即可。
例如,我们可以将商品编号(product_id
)为 0008
的数据(圆珠笔)的登记日期(regist_date
)更新为 NULL
(代码清单 17)。
代码清单 17 将商品编号为 0008 的数据(圆珠笔)的登记日期更新为 NULL
UPDATE Product
SET regist_date = NULL
WHERE product_id = '0008';
-- 确认更新内容
SELECT * FROM Product ORDER BY product_id;
执行结果:
和 INSERT
语句一样,UPDATE
语句也可以将 NULL
作为一个值来使用。
但是,只有未设置 NOT NULL
约束和主键约束的列才可以清空为 NULL
。
如果将设置了上述约束的列更新为 NULL
,就会出错,这点与 INSERT
语句相同。
法则 6
使用
UPDATE
语句可以将值清空为NULL
(但只限于未设置NOT NULL
约束的列)。
3.4 多列更新
UPDATE
语句的 SET
子句支持同时将多个列作为更新对象。
例如我们刚刚将销售单价(sale_price
)更新为原来的 10 倍,如果想同时将进货单价(purchase_price
)更新为原来的一半,该怎么做呢?
最容易想到的解决办法可能就是像代码清单 18 那样,执行两条 UPDATE
语句。
代码清单 18 能够正确执行的繁琐的 UPDATE 语句
-- 一条UPDATE语句只更新一列
UPDATE Product
SET sale_price = sale_price * 10
WHERE product_type = '厨房用具';
UPDATE Product
SET purchase_price = purchase_price / 2
WHERE product_type = '厨房用具';
虽然这样也能够正确地更新数据,但执行两次 UPDATE
语句不但有些浪费,而且增加了 SQL 语句的书写量。
其实,我们可以将其合并为一条 UPDATE
语句来处理。合并的方法有两种,请参见代码清单 19 和代码清单 20。
方法 ①:代码清单 19 将代码清单 18 的处理合并为一条 UPDATE 语句
-- 使用逗号对列进行分隔排列
UPDATE Product
SET sale_price = sale_price * 10,
purchase_price = purchase_price / 2
WHERE product_type = '厨房用具';
方法 ②:代码清单 20 将代码清单 18 的处理合并为一条 UPDATE 语句
-- 将列用()括起来的清单形式
UPDATE Product
SET (sale_price, purchase_price) = (sale_price * 10, purchase_price / 2)
WHERE product_type = '厨房用具';
执行上述两种 UPDATE
语句,都可以得到相同的结果:只有厨房用具的销售单价(sale_price
)和进货单价(purchase_price
)被更新了。
-- 确认更新内容
SELECT * FROM Product ORDER BY product_id;
执行结果:
当然,SET
子句中的列不仅可以是两列,还可以是三列或者更多。
需要注意的是第一种方法——使用逗号将列进行分隔排列(代码清单 19),这一方法在所有的 DBMS 中都可以使用。
但是第二种方法——将列清单化(代码清单 20),这一方法在某些 DBMS 中是无法使用的 [8]。因此,实际应用中通常都会使用第一种方法。
原文链接:https://www.developerastrid.com/sql/sql-insert-delete-update/
(完)
有关日期型的介绍,请参考 SQL 如何对表进行创建、更新和删除操作。 ↩︎
但是使用默认值时列数无需完全一致。相关内容将会在随后的“插入默认值”中进行介绍。 ↩︎
插入多行的情况,请参考专栏“多行
INSERT
”。 ↩︎不仅是
INSERT
,DELETE
和UPDATE
等更新语句也一样,SQL 语句执行失败时都不会对表中数据造成影响。 ↩︎但即使指定了
ORDER BY
子句也没有任何意义,因为无法保证表内部记录的排列顺序。 ↩︎与
INSERT
语句相同,数据的更新也是以记录为基本单位进行的。下一节将要学习的UPDATE
语句也是如此。 ↩︎虽然“搜索型
DELETE
”是正式用语,但实际上这种说法并不常用,而是简单地称为DELETE
语句。 ↩︎可以在 PostgreSQL 和 DB2 中使用。 ↩︎