3.9.3 更新

发表于 更新于 分类于 ， ， ， Waline： 本文字数： 3.8k 阅读时长 ≈ 3 分钟

3.9.3 更新

有些情况下,我们可能希望在不改变整个元组的情况下改变其部分属性的值。为达到这一目的,可以使用update语句。与使用insert、 delete类似,待更新的元组可以用查询语句找到

SQL更新 所有教师的工资将增长5%

假设要进行年度工资增长,所有教师的工资将增长5%。我们写出:

1
2
update instructor
    set salary=salary * 1.05;
1
2
3
4
5
mysql> update instructor
    set salary=salary * 1.05;
Query OK, 12 rows affected (0.01 sec)
Rows matched: 12  Changed: 12  Warnings: 0

上面的更新语句将在 instructor关系的每个元组上执行一次。

SQL更新 只给那些工资低于70000美元的教师涨工资

1
2
3
update instructor
    set salary=salary*1.05
    where salary>70000;
1
2
3
4
5
mysql> update instructor
    set salary=salary*1.05
    where salary>70000;
Query OK, 8 rows affected (0.01 sec)
Rows matched: 8  Changed: 8  Warnings: 0

update的where子句可以嵌套查询语句

总之, update语句的where子句可以包含select语句的where子句中的任何合法结构(包括嵌套的select)。
和insert、 delete类似, update语句中嵌套的set可以引用待更新的关系。同样,SQL首先检查关系中的所有元组,看它们是否应该被更新,然后才执行更新。

SQL更新 对工资低于平均数的教师涨5%的工资

例如,请求”对工资低于平均数的教师涨5%的工资“可以写为如下形式:

1
2
3
4
5
6
7
update instructor
    set salary=salary*1.05
    where salary <(
        select avg_salary from(
            select avg(salary) as avg_salary from instructor
        ) as Avgs
);
1
2
3
4
5
6
7
8
9
mysql> update instructor
    set salary=salary*1.05
    where salary <(
        select avg_salary from(
            select avg(salary) as avg_salary from instructor
        ) as Avgs
);
Query OK, 5 rows affected (0.01 sec)
Rows matched: 5  Changed: 5  Warnings: 1

给工资超过100000美元的教师涨3%的工资,其余教师涨5%

我们可以写两条update语句:

1
2
3
4
5
6
update instructor
    set salary=salary*1.03
    where salary >100000;
update instructor
    set salary=salary*1.05
    where salary<100000;
1
2
3
4
5
6
7
8
9
10
11
mysql> update instructor
    set salary=salary*1.03
    where salary >100000;
update instructor
    set salary=salary*1.05
    where salary<100000;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

Query OK, 12 rows affected (0.01 sec)
Rows matched: 12  Changed: 12  Warnings: 0

update语句的顺序十分重要

注意上面这两条update语句的顺序十分重要。
假如我们改变这两条语句的顺序,工资略少于100000美元的教师将增长8%的工资。

case结构

SQL提供case结构,我们可以利用它在一条update语句中执行前面的两种更新,避免更新次序引发的问题:

1
2
3
4
5
update instructor
set salary=case
    when salary < 100000 then salary*1.05
    else salary*1.03
end;
1
2
3
4
5
6
7
8
mysql> update instructor
set salary=case
    when salary < 100000 then salary*1.05
    else salary*1.03
end;
Query OK, 12 rows affected (0.01 sec)
Rows matched: 12  Changed: 12  Warnings: 0

case语句的格式

case语句的一般格式如下:

1
2
3
4
5
6
7
case
    when pred_1, then result_1
    when pred_2, then result_2
    ...
    when pred_n, then result_n
    else result0
end
  • 如果第一个满足的是谓词pred_i,则返回结果result_i.
  • 如果没有一个谓词可以满足,则返回result0

case语句可以出现的地方

case语句可以用在任何应该出现值的地方。

使用标量子查询

标量子查询在SQL更新语句中也非常有用,它们可以用在set子句中。
考虑这样一种更新:我们把每个student元组的tot_cred属性值设为该生成功学完的课程学分的总和。我们假设如果一个学生在某门课程上的成绩既不是”F“,也不是空,那么他成功学完了这门课程。
我们需要使用set子句中的子查询来写出这种更新,如下所示:

1
2
3
4
5
6
update student S
    set tot_cred =(
        select sum(credits)
            from takes natural join course
            where S.ID=takes.ID and takes.grade <> 'F' and takes.grade is not null
);
1
2
3
4
5
6
7
8
mysql> update student S
    set tot_cred =(
        select sum(credits)
            from takes natural join course
            where S.ID=takes.ID and takes.grade <> 'F' and takes.grade is not null
);
Query OK, 13 rows affected (0.02 sec)
Rows matched: 13  Changed: 13  Warnings: 0

使用标量子查询和case

注意子查询使用了来自update语句中的相关变量S。如果一个学生没有成功学完任何课程,上述更新语句将把其tot_cred属性值设为空。
如果想把这样的属性值设为0的话,我们可以使用另一条update语句来把空值替换为0。
不过更好的方案是把上述子查询中的”select sun(credits)“子句替换为如下使用case表达式的select子句:

1
2
3
4
select case
    when sum(credits ) is not null then sum(credits)
    else 0
end

也就是改为如下形式:

1
2
3
4
5
6
7
8
9
update student S
    set tot_cred =(
            select case
                    when sum(credits ) is not null then sum(credits)
                    else 0
                end
            from takes natural join course
            where S.ID=takes.ID and takes.grade <> 'F' and takes.grade is not null
);