数据库概论.陈立军.04. 数据更新

Posted on Edited on In Views:
Insert、Delete、Truncate、Update、Merge、Output

数据更新

  • Insert
  • Delete
  • Truncate
  • Update
  • Output
  • Merge

全局变量

row_count()

  • 返回受上一语句影响的行数
  • 任何不返回行的语句将这一变量设置为 0
  • 例子：删除了多少行
1
2
delete from S
select row_count()

Insert

  • 形式
1
2
3
4
/* 插入一条指定好值的行 */
insert into 表名[(列名[, 列名]...)] values (值[,值],...,)
/* 插入子查询结果中的若干行 */
insert into 表名[(列名[, 列名]...](子查询)

插入显式行

1
2
3
4
5
6
insert into PROF values
    (P123,'王明',35,D08,498),
    (P124,'李明',38,D01,698)
insert into PROF(pno, pname, dno) values
    row(P123, '王明', D08),
    row(P125, '李明', D08)

插入子查询

  • 将平均成绩大于 90 的学生加入到 EXCELLENT 中
1
2
3
4
5
6
7
8
9
10
11
12
insert into EXCELLENT (sno, grade)
select sno,avg(grade)
from SC
group by(sno)
having avg(grade) > 90


select sno, avg(grade)
into EXCELLENT (sno, grade)
from SC
group by(sno)
having avg(grade) > 90

replace into

  • 替换原有的数据行
1
2
3
4
5
6
7
8
9
10
create table test_replace(
    id int auto_increment primary key,
    name char(10),
    cur_time datetime
)

insert into test_replace (name,cur_time)
values('A',now()),('B',now())

replace test_replace values(1,'AA',now())

Delete

  • 从表中删除符合条件的元组
  • 如果没有 where 语句，则删除所有元组
1
delete from 表名 [where 条件表达式]
1
2
3
4
5
6
7
8
9
10
/* 清除所有选课记录 */
delete from SC

/* 删除王明老师所有的任课记录 */
delete from PC
where pno in (
    select pno
    from PROF
    where pname='王明'
)
  • 删除的时候，用 in 而不是 =
    • 可能有两个人都叫王明，使用 = 会报错
    • 运行时错误
1
2
3
4
5
6
7
8
9
create table delA(a int);
create table delB(b int);
insert into delA values(1),(3),(4);
insert into delB values(3),(4),(5);

/* =/in */
delete from delA where a=(select b from delB where b<4);
delete from delA where a=(select b from delB where b>3);
/* ERROR 1242(21000):Subquery returns more than 1 row */
  • 可以使用 limit 1 限制只输出一行
1
2
3
4
delete from delB
where b<5
order by b desc
limit 1

多表删除操作

1
2
3
4
5
delete t1,t2 from t1 inner join t2 inner join t3
where t1.id=t2.id and t2.id=t3.id

delete from t1,t2 using t1 inner join t2 inner join t3
where t1.id=t2.id and t2.id=t3.id
1
2
delete PC from PROF inner join PC
where pname='王明' and PROF.pno=PC.pno

删除操作

例子

  • 删除低于平均工资的老师记录
1
2
3
4
5
6
/* 这样的语句是错误的 */
delete from PROF
where salary < (
    select avg(salary)
    from PROF
)
  • 思考：是先找到所有符合条件的行，一并删除，还是找到一个删除一个
    • 应该是先找到，然后会一并删除
    • 删除的时候会加锁，一并删除可以保持对外的一致性
  • MySQL 不允许从子查询中出现的表中删除数据
  • 解决方法，先找出平均工资，然后再删除
1
2
3
4
5
6
/* 生成临时视图 */
with tmp as(
    select avg(val) as A
    from test_del
)
delete test_del from test_del inner join tmp where val > A;

Truncate

  • 清空表
1
truncate table
  • 删除表中的所有行，而不记录单个行删除操作
    • 相对于用 delete 语句删除整个表而言，更加高效
    • 数据库一般都有日志记录，用于恢复数据库
      • delete 删除一个表会产生行级的记录
      • truncate 删除整个表只会产生页级的记录
  • truncate table 在功能上与不带 where 子句的 delete 语句相同
    • 但 truncate table 比 delete 速度快，且使用的系统和事务日志资源少
    • auto_increment 计数器重置为种子值

Update

  • 更新操作的命令格式
1
2
3
4
5
update 表名
set 列名 = 表达式|子查询
    列名 = [,表达式|子查询]
    ...
[where 条件表达式]
  • 指定对哪些列进行更新，以及更新后的值是什么

例子 1

  • 老师工资上调 5%
1
2
update PROF
set salary = salary*1.05
  • 将 D01系系主任的工资改为该系的平均工资
1
2
3
4
5
6
7
8
9
10
11
12
13
/* SQL 语句无效 */
/* 和删除一样, MySQL 不允许从子查询中出现的表中更新数据 */
update PROF
set salary = (
    select avg(salary)
    from PROF
    where dno=D01
)
where pno= (
    selectdean
    from DEPT
    where dno=D01
)
  • 解决方法是一致的，临时视图
1
2
3
with tmp as(select avg(salary) avgsal from PROF where dno=D01)
update PROF,tmp
set salary = tmp.avgsal

例子 2

  • 当 C1 课程的成绩小于该课程的平均成绩时，将其提高 5%
1
2
3
with tmp as(select avg(grade) avggrade from SC where cno=C1)
update SC,tmp
set grade = grade*1.05 where cno=C1 and grade<tmp.avggrade
  • 对所有的课程，满足上述条件的都进行修改
1
2
3
4
5
6
7
8
with tmp as(
    select cno, avg(grade) avggrade
    from SC
    group by cno
)
update SC,tmp
where SC.cno=tmp.cno
set grade = grade*1.05 where grade<tmp.avggrade

例子 3 (case when)

  • 工资超过 2000 的缴纳 10% 所得税，其余的缴纳 5% 所得税
  • 如果写成两个 SQL，需要考虑执行顺序
    • 这里需要先写 5% 的征税（工资小于等于2000），再写 10% 的征税（工资大于2000）
    • 写成多个 SQL 是不好的习惯
  • 最好写成一个 SQL
    • case when
1
2
3
4
5
update PROF
set SAL =
    case SAL
        when SAL > 2000 then SAL * 0.9
        when SAL <= 2000 then SAL * 0.95

Merge

行拷贝

  • R(ID, A, B), S(ID, A, B)
  • 使用 S 相同 ID 的记录覆盖 R 中相同 ID 的记录
1
2
3
4
5
/* 错误的写法 */
update R
set
    A = (select S.A from S where R.ID=S.ID),
    B = (select S.B from S where R.ID=S.ID)
  • 怎么处理呢？
    • 表同步问题

表同步: UPSERT

  • UPSERT：表同步
  • 在把一组记录加载到表中时，一个经典的挑战是如何识别和处理目标表中已有的记录
  • 常用的方法如下
    • 如果某记录不存在，就将它插入
    • 如果存在，就用源表中的数据更新该记录
  • 需要定义复杂的存储过程来完成一系列 INSERT 或 UPDATE 命令，这个技术通常被称为 UPSERT
  • 应用场景
    • 业务表不能够一直增大，否则会拖慢系统的性能，因此需要定时保存到历史表中

Merge 命令

  • merge 子句指定作为插入、更新或删除操作目标的表
  • using 子句指定要与目标联接的数据源
  • on 子句指定决定目标与源的匹配位置的联接条件
  • when 子句基于 on 子句的结果指定所要采取的操作
    • when matched
    • when not matched by target
    • when not matched by source

表同步一个典型的应用场景

  • 数据仓库中的 FactBuyingHabits 表跟踪客户购买产品的最后日期，事务数据库每周都会生成一个包括该周采购情况的PurchaseRecords 表
  • 需要定期将 PurchaseRecords 表中的信息合并到 FactBuyingHabits 表中
    • 对于不存在的产品-客户对，插入新行
    • 对于已存在的产品-客户对，更新最近的购买日期
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
/* 指定目标表 */
merge dbo.FactBuyingHabits as target
/* 指定源表(可以是表或子查询) */
using (
    select CustomerID, ProductID, PurchaseDate
    from dbo.Purchases
) as source
/* 指定行匹配(连接条件) */
on(
    Target.ProductID = Source.ProductID
    and Target.CustomerID = Source.CustomerID
)
/* 不同情况的操作 */
/* 匹配上 */
when matched then
    update setTarget.LastPurchaseDate = Source.PurchaseDate
/* 目标表中没有 */
when not matched by target then
    insert(CustomerID, ProductID, LastPurchaseDate)
    values(Source.CustomerID, Source.ProductID, Source.PurchaseDate)
  • Merge 命令的产生是源于本是据数据仓库的发展
  • 更新操作对于数据库而言是很关键的，一般会有记录（谁更新了什么）
    • 审计

Output

  • Output：记录更新历史
  • 执行修改操作只返回影响了多少行的信息，无从获知到底影响到了哪些行
  • 如果在修改操作语句中带上output，就可以输出具体的影响信息
1
2
output{ deleted|inserted}.{ * | column_name}
[into table_name]
  • deleted 和 inserted 是两个虚表，deleted 里面存放修改之前的值，inserted 里面存放的是修改之后的值
1
2
3
4
5
6
7
8
9
10
11
declare @recordChange table(
    beforeGrade int,
    afterGrade  int
)

update sc
set grade=grade*1.05
output deleted.grade,inserted.gradeinto @recordChange

delete from sc
output deleted.* into delHistory