12.1 更新表中的数据 - 《精通SQL——结构化查询语言详解》 - 免费试读 - bo...

昵称310935 2009-09-17

展开全文

上一章介绍了如何向表中插入数据，本章将主要讨论通过UPDATE 和DELETE语句更新和删除表中的数据。

12.1 更新表中的数据

在SQL中，可以使用UPDATE语句来修改表中的现有数据，也称为更新数据库表中的数据。

12.1.1 UPDATE语句的基本语法

使用UPDATE语句，一次可以改变数据库表中单行上的值，也可以改变表中选定的一些行上的多列数据，当然也可以更新所有行的数据。语法如下。

UPDATE table_name

SET column1=value1,

column2=value2,

……

WHERE search_condition

说明：UPDATE语句实际上主要包括三部分：要更新的表名、列名和它们的新值以及选择更新行的搜索条件。

UPDATE语句中的WHERE子句确定表（table_name）中要修改的数据行。SET子句提供要修改的列值的清单。简单来讲，UPDATE语句执行时，一次一行地处理整个表，更新那些搜索为True（即WHERE子句为True）的行上的SET子句列出的列值。UPDATE语句把那些搜索条件为False或NULL的行中的数据保持不变。

由UPDATE的语法可见，SET子句包括了列赋值表达式的清单。表列名在赋值清单中作为赋值目标只能出现一次。并且表达式必须要产生一个与要赋值的列的数据类型相兼容的值。

注意	一定要注意不要忽略WHERE子句，如果没有指明WHERE子句，则数据库表中所有行的记录都将被更新。

12.1.2 UPDATE语句更新列值

使用UPDATE语句可以更新单列数据也可以更新多列数据。下面通过几个实例介绍一下使用UPDATE语句更新列值的操作。

实例1 复制TEACHER表数据到新表

本节所有的更新实例均是基于TEACHER表（参见本书5.2.1节的表5-1）中的数据进行的，为了不影响TEACHER表的其他应用，这里用第11章介绍的SELECT……INTO语句将TEACHER表中的数据复制到New_Teacher表中。本节给出的更新操作实例均是针对New_Teacher表进行的。代码如下。

SELECT *

INTO New_Teacher

FROM TEACHER

查看表New_Teacher的数据。

SELECT *

FROM New_Teacher

运行结果如图12.1所示。

图12.1 New_Teacher表中的记录

1．更新单列数据

实例2 更新单列数据

“三八”节到了，该月为所有女教师的工资增加100元过节费。实例代码：

UPDATE New_Teacher

SET SAL=SAL+100

WHERE TSEX='女'

查看表New_Teacher的数据。

SELECT *

FROM New_Teacher

运行结果如图12.2所示。

图12.2 女教师的工资增加100元后的纪录

可见，表中所有女教师SAL列的数据都增加了100，而其他数据都没有改变。需要注意的是教工号为8和10的两个女教师的SAL列数据仍为NULL，这是因为NULL值参与任何数学运算其结果仍为NULL，因此“NULL+100”结果仍为NULL。

2．多列数据的更新

使用UPDATE语句一次也可以更新多列数据，只要写入SET子句的更新清单中即可。

实例3 多列数据的更新

新的一年开始了，所有教师的年龄增加了1岁，同时给所有教师的工资增加10%。实例代码如下。

（1）先将女教师的工资恢复到原来值。

UPDATE New_Teacher

SET SAL=SAL-100

WHERE TSEX=’女’

（2）更新所有教师的年龄和工资信息。

UPDATE New_Teacher

SET AGE = AGE +1,

SAL= SAL*1.1

（3）查看表New_Teacher的数据。

SELECT *

FROM New_Teacher

运行结果如图12.3所示。

可见，所有的SAL列和AGE列的数据都发生了改变。

图12.3 更新表New_Teacher后的纪录

3．通过更新删除列中的数据

有时候需要通过数据的更新操作，实现删除某列数据的目的。通常的做法是把该列的值设置为NULL。当然，这样做的前提是该列允许为空值，即没有非空约束。

实例4 通过更新删除列中的数据

生物系教师的工资进行了调整，目前还不知道调整后的具体工资，因此需要把其原有的工资信息删除。实例代码：

UPDATE New_Teacher

SET SAL = NULL

WHERE DNAME ='生物'

查看表New_Teacher的数据。

SELECT *

FROM New_Teacher

运行结果如图12.4所示。

图12.4 将生物系教师原有的工资信息删除

可见，生物系所有的教师的SAL列值都变为NULL。

注意	在执行一条新的UPDATE语句（特别是带有复杂搜索条件的）之前，最好应先确定一些更新操作所影响的数据行数。

例如上例，执行数据更新前，确定一些更新操作影响的行数，即所有生物系教师的记录数，代码如下。

SELECT COUNT(*)

FROM New_Teacher

WHERE DNAME ='生物'

运行结果如图12.5所示。

图12.5 生物系教师的记录数

由此可知，UPDATE语句将更新New_Teacher表中的两行数据，这样有利于减少更新操作的错误概率。

12.1.3 利用子查询更新多行的值

在UPDATE语句的WHERE子句中，可以使用比较运算符（=、>、<、>=、<=、<>）来确定搜索条件，同样也可以使用子查询来确定需要更新的行。下面就结合具体的实例说明其应用。

实例5 复制TEACHER表数据到新表

同样，为了不影响TEACHER表中的原始数据，需要将TEACHER表中的数据复制到表New_Teacher2表中。这样，直接操作New_Teacher2表就可以了。代码如下。

SELECT *

INTO New_Teacher2

FROM TEACHER

查看表New_Teacher2的数据。

SELECT * FROM New_Teacher2

运行结果如图12.6所示。

图12.6 将TEACHER表中的数据复制到表New_Teacher2表

实例6 利用子查询更新多行的值

在NEW_ Teacher2表中，当男教师的工资少于所有男教师的平均工资时，将该男教师的工资提高5%。实例代码：

UPDATE NEW_Teacher2

SET SAL = SAL +SAL * 0.05

WHERE TSEX='男'

AND SAL < (SELECT avg(SAL)

FROM NEW_Teacher2

WHERE TSEX='男')

查看更新后表New_TEACHER2中的数据。

SELECT * FROM New_TEACHER2

运行结果如图12.7所示。

图12.7 将工资少于所有男教师的平均工资的男教师的工资提高5%

可以发现，教工号为1、3、6的男教师的SAL列数据进行了更新，其余数据均没有发生变化。

特别需要强调一点，使用子查询选择要更新的行时，可以在子查询中引用正在更新的表，如本例中，子查询语句就引用了表NEW_Teacher2。这时，子查询引用的表中的数据都认为是更新前的数据。

如在该实例中，代码执行时，DBMS首先读出NEW_Teacher2表中的第一行记录，经验证其满足WHERE子句中的搜索条件，就将其SAL列中的数据更新，即由800更新为840。而后系统从NEW_Teacher2表取出第二条记录，此时在WHERE子句中执行子查询即使用AVG聚合函数，其中要用到第一条记录的SAL列中的数据，这时采用的是更新前的数据，即仍然为800。也就是说，在整个UPDATE语句执行期间，子查询中的AVG函数得到的值始终是相同的。