|
|
51CTO旗下网站
|
|
移动端

跨表查询经常有,何为跨表更新?

有点 SQL 基础的朋友肯定听过 「跨表查询」,那啥是跨表更新啊?一起来看一下。

作者:日拱一兵来源:segmentfault|2020-02-03 14:25

有点 SQL 基础的朋友肯定听过 「跨表查询」,那啥是跨表更新啊?

 背景

项目新导入了一批人员数据,这些人的有的部门名称发生了变化,有的联系方式发生了变化,暂且称该表为

t_dept_members, 系统中有另外一张表 t_user_info 记录了人员信息。要求将 t_dept_members 中有变化的信息更新到 t_user 表中,这个需求就是「跨表更新」啦

憨B SQL 直接被秒杀

不带脑子出门的就写出了下面的 SQL

看到身后 DBA 小段总在修仙,想着让他帮润色一下😜,于是发给了他,然后甩手回来就是这个样子:​

看到这个 SQL 语句我都惊呆了,还能这样写,在无情的嘲笑下,一声 KO 我直接倒下。死也得死的明白,咱得查查这是咋回事啊

Mysql Update Join

我们经常使用 join 查询表中具有(在 INNER JOIN 情况下)或可能没有(在 LEFT JOIN 情况下)另一个表中匹配行的表中的行。

同样,在 MySQL 中, 我们也可以在 UPDATE 语句中使用 JOIN 子句执行跨表更新,语法就是这样:

  1. UPDATE T1, T2,  
  2. [INNER JOIN | LEFT JOIN] T1 ON T1.C1 = T2. C1  
  3. SET T1.C2 = T2.C2,  
  4.     T2.C3 = expr  
  5. WHERE condition 

我们还是详细的说明一下上面的语法:

  •  首先,在 UPDATE 子句之后,指定主表(T1)和希望主表联接到的表(T2)。请注意,必须在UPDATE 子句之后至少指定一个表
  •  接下来,指定你要使用的联接类型,即 INNER JOIN 或 LEFT JOIN 以及联接谓词。 JOIN子句必须出现在 UPDATE 子句之后(这个大家都是知道的哈)
  •  然后,将新值分配给要更新的 T1或 T2 表中的列
  •  最后,在 WHERE 子句中指定一个条件以将行限制为要更新的行

如果你遵循 update 语法,你会发现有另外一种语法也可以完成跨表更新

  1. UPDATE T1, T2  
  2. SET T1.c2 = T2.c2,  
  3.       T2.c3 = expr  
  4. WHERE T1.c1 = T2.c1 AND condition 

上面的语法其实隐式使用了 inner join 关键字,完全等同于下面的样子:

  1. UPDATE T1,T2  
  2. INNER JOIN T2 ON T1.C1 = T2.C1  
  3. SET T1.C2 = T2.C2,  
  4.       T2.C3 = expr  
  5. WHERE condition 

个人建议还是加上 inner join 关键字吧,这样可读性更好,尽享丝滑,你觉得呢?

我摸鱼看到的,觉得是灵魂翻译

谈太廉,秀你码 (Talk is cheap,show me the code)

Update Join 例子

年底了,又到了评绩效的时候了,就是那个叫 KPI 的东东(你们有吗),听说要根据 KPI 调工资了。有两张表

第一张表「employees-员工表」

建表语句如下:

  1. create table employees  
  2.  
  3.     employee_id bigint auto_increment comment '员工ID,主键',  
  4.     employee_name varchar(50) null comment '员工名称',  
  5.     performance int(4) null comment '绩效分数 1,2,3,4,5',  
  6.     salary float null comment '员工薪水',  
  7.     constraint employees_pk  
  8.         primary key (employee_id)  
  9.  
  10. comment '员工表'; 

第二张表「merits-绩效字典表」

建表语句如下:

  1. create table merits  
  2.  
  3.     performance int(4) null,  
  4.     percentage float null  
  5.  
  6. comment '绩效字典表'; 

先生成一些模拟数据

  1. -- 绩效字典初始化数据  
  2. INSERT INTO merits(performance, percentage)  
  3. VALUES (1, 0),  
  4.        (2, 0.01),  
  5.        (3, 0.03),  
  6.        (4, 0.05),  
  7.        (5, 0.08);  
  8. -- 员工表初始化数据  
  9. INSERT INTO employees(employee_name, performance, salary)  
  10. VALUES ('拱哥', 1, 1000),  
  11.        ('小段总', 3, 20000),  
  12.        ('大人', 4, 18000),  
  13.        ('司令', 5, 28000),  
  14.        ('老六', 2, 10000),  
  15.        ('罗蒙', 3, 20000);    

调薪规则:

原有薪资 + (原有薪资 * 当前绩效对应的调薪百分比)

按照调薪规则写 update 语句:

  1. UPDATE employees  
  2.     INNER JOIN  
  3.     merits ON employees.performance = merits.performance  
  4. SET salarysalary = salary + salary * percentage; 

拱哥绩效不好,没给涨工资......

三横一竖一咕嘎,四个小猪🐷来吃zha,咕嘎咕嘎又来俩

临近年底,公司又来了两位新同事, 但是公司年度绩效已经评完,所以新员工绩效为 NULL

  1. INSERT INTO employees(employee_name, performance, salary)  
  2. VALUES ('馮大', NULL, 8000),  
  3.        ('馮二', NULL, 5000); 

新员工工作干的不错,也要 1.5% 涨点工资的。如果我们还是用 UPDATE INNER JOIN,按照上面的更新语句是不可能完成的,因为条件等式不成立,这是我们就要用到 UPDATE LEFT JOIN 了

  1. UPDATE employees  
  2.     LEFT JOIN  
  3.     merits ON employees.performance = merits.performance  
  4. SET salarysalary = salary + salary * 0.015  
  5. WHERE merits.percentage IS NULL; 

到这里,新员工的涨薪工作也做完,拱哥由于知识点了解不透彻,灰溜溜的回家过年

  •  如果你也恰巧刚知道这个知识点,请点个「赞」
  •  如果你早都知道了这个知识点,还请留言送上「嘘声」
  •  如果你年终奖丰厚,希望你2020年更进一步
  •  如果你和我一样没有年终奖,别灰心,我们携手进步

流感严重😷,春运旅途多加小心

欢迎关注我的公众号 「日拱一兵」,趣味原创解析Java技术栈问题,将复杂问题简单化,将抽象问题图形化落地

如果对我的专题内容感兴趣,或抢先看更多内容,欢迎访问我的博客 dayarch.top 

【编辑推荐】

  1. MySQL 优化:为什么 SQL 走索引还那么慢?
  2. 聊一聊 MySQL 数据库中的那些锁
  3. 数据库优化,以实际SQL入手,带你一步一步走上SQL优化之路
  4. 数据库迁移,要停机发布么?看看别家的程序员怎么做的
  5. 聊一聊 MySQL 中的数据编辑过程中涉及的两阶段提交
【责任编辑:庞桂玉 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

订阅专栏+更多

Python应用场景实战手册

Python应用场景实战手册

Python应用场景实战手册
共3章 | KaliArch

22人订阅学习

一步到位玩儿透Ansible

一步到位玩儿透Ansible

Ansible
共17章 | 骏马金龙1

205人订阅学习

云架构师修炼手册

云架构师修炼手册

云架构师的必备技能
共3章 | Allen在路上

141人订阅学习

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO官微