MySQL跨表查询与跨表更新

有点 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 子句执行跨表更新,语法就是这样:

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

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

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

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

UPDATE T1, T2
SET T1.c2 = T2.c2,
   T2.c3 = expr
WHERE T1.c1 = T2.c1 AND condition

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

UPDATE T1,T2
INNER JOIN T2 ON T1.C1 = T2.C1
SET T1.C2 = T2.C2,
   T2.C3 = expr
WHERE condition

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

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

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

Update Join 例子

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

第一张表「employees-员工表」

建表语句如下:

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

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

建表语句如下:

create table merits
(
  performance int(4) null,
  percentage float null
)
comment '绩效字典表';

先生成一些模拟数据

-- 绩效字典初始化数据
INSERT INTO merits(performance, percentage)
VALUES (1, 0),
    (2, 0.01),
    (3, 0.03),
    (4, 0.05),
    (5, 0.08);

-- 员工表初始化数据
INSERT INTO employees(employee_name, performance, salary)
VALUES ('拱哥', 1, 1000),
    ('小段总', 3, 20000),
    ('大人', 4, 18000),
    ('司令', 5, 28000),
    ('老六', 2, 10000),
    ('罗蒙', 3, 20000);

调薪规则:

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

按照调薪规则写 update 语句:

UPDATE employees
  INNER JOIN
  merits ON employees.performance = merits.performance
SET salary = salary + salary * percentage;

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

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

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

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

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

UPDATE employees
  LEFT JOIN
  merits ON employees.performance = merits.performance
SET salary = salary + salary * 0.015
WHERE merits.percentage IS NULL;

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

(0)

相关推荐

  • Mysql跨表更新 多表update sql语句总结

    假定我们有两张表,一张表为Product表存放产品信息,其中有产品价格列Price:另外一张表是ProductPrice表,我们要将ProductPrice表中的价格字段Price更新为Price表中价格字段的80%. 在Mysql中我们有几种手段可以做到这一点,一种是update table1 t1, table2 ts ...的方式: 复制代码 代码如下: UPDATE product p, productPrice pp SET pp.price = pp.price * 0.8 WHER

  • MySQL Union合并查询数据及表别名、字段别名用法分析

    本文实例讲述了MySQL Union合并查询数据及表别名.字段别名用法.分享给大家供大家参考,具体如下: union关键字 SELECT s_id, f_name, f_price FROM fruits WHERE f_price < 9.0 UNION ALL SELECT s_id, f_name, f_price FROM fruits WHERE s_id IN(101,103); 为表取别名 SELECT * from orders AS o WHERE o.o_num = 3000

  • MySQL数据库高级查询和多表查询

    MySQL多表查询 添加练习表 -- 用户表(user) CREATE TABLE `user`( `id` INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户id(主键)', `username` VARCHAR(50) COMMENT '用户姓名', `age` CHAR(3) COMMENT '用户年龄' ); -- 订单表(orders) CREATE TABLE `orders`( `id` INT AUTO_INCREMENT PRIMARY KEY

  • MySql数据库单表查询与多表连接查询效率对比

    这段时间在做项目的过程中,遇到一个模块,数据之间的联系很复杂,在建表的时候就很纠结,到底该怎么去处理这些复杂的数据呢,是单表查询,然后在业务层去处理数据间的关系,还是直接通过多表连接查询来处理数据关系呢? 通过查阅资料和阅读博客,有以下两个回答: 一.<高性能mysql>中的回答 很多高性能的应用都会对关联查询进行分解.简单地,可以对每个表进行一次单表查询,然后将结果在应用程序中进行关联.例如,下面这个查询: select * from tag join tag_post on tag_pos

  • mybatis-plus实现自定义SQL、多表查询与多表分页查询语句实例

    目录 前言 1.自定义SQL 2.多表查询 3.多表分页查询 4.多表分页条件查询 总结 前言 本文介绍了在mybatis-plus中如何实现:自定义SQL语句,多表查询语句,多表分页查询语句 在说怎么实现之前我们要先明白一个概念,就是mybatis-plus是在mybatis的基础上进行增强,并不做改变,所以mybatis的操作在mybatis-plus中也是一样可以使用的,咱们直接上代码 1.自定义SQL 在mapper中自定义一个方法即可 @Repository public interf

  • MySQL跨表查询与跨表更新

    有点 SQL 基础的朋友肯定听过 「跨表查询」,那啥是跨表更新啊? 背景 项目新导入了一批人员数据,这些人的有的部门名称发生了变化,有的联系方式发生了变化,暂且称该表为 t_dept_members, 系统中有另外一张表 t_user_info 记录了人员信息.要求将 t_dept_members 中有变化的信息更新到 t_user 表中,这个需求就是「跨表更新」啦 憨B SQL 直接被秒杀 不带脑子出门的就写出了下面的 SQL 看到身后 DBA 小段总在修仙,想着让他帮润色一下

  • sql跨表查询的三种方案总结

    目录 前言 方案一:连接多个库,同步执行查询 优点 缺点 代码执行 方案二:在主数据库增加冗余表,通过定时更新,造成同库联表查询 优点 缺点 相似实现场景 方案三:dbLink本地连接多个库,在本地进行数据分析 优点 缺点 前言 最近又个朋友问我,如何进行sql的跨库关联查询? 首先呢,我们知道mysql是不支持跨库连接的,但是老话说得好,只要思想不滑坡,思想总比困难多! PS: 问题摆在这里了,还能不解决是怎么的? 经过一番思考我给他提出了三个方案,虽然都不尽善尽美,但各领风骚! 连接方案,以

  • MySql多表查询 事务及DCL

    目录 一.多表查询 1.查询语法 2. 准备sql 3. 笛卡尔积 4.多表查询的分类 4.1 内连接查询 4.2 外链接查询 4.3 子查询 4.4 子查询不同情况 5.多表查询练习 二.事务 1. 事务的基本介绍 2. 操作 2.MySQL数据库中事务默认自动提交 3. 事务的四大特征 一.多表查询 1.查询语法 select   列名列表  from   表名列表  where.... 2. 准备sql # 创建部门表 CREATE TABLE dept( id INT PRIMARY K

  • Android Room数据库多表查询的使用实例

    Android-Room数据库(介绍) 前言 在SQLite数据库中,我们可以指定对象之间的关系,因此我们可以将一个或多个对象与一个或多个其他对象绑定.这就是所谓的一对多和多对多的关系. 既然要多表查询,所以表之间就得有关联.这时候我们就得使用新的注解符@ForeignKey 接下来的内容,就需要上节的内容了 @Entity public class Company { @PrimaryKey(autoGenerate = true) private int id; private String

  • Mybatis详解动态SQL以及单表多表查询的应用

    目录 单表查询操作 参数占位符#{}和${} SQL 注入 like模糊查询 多表查询操作 一对一多表查询 一对多多表查询 动态SQL使用 if标签 trim标签 where标签 set标签 foreach标签 单表查询操作 参数占位符#{}和${} #{}:相当于JDBC里面替换占位符的操作方式(#{}->“”).相当于预编译处理(预编译处理可以防止SQL注入问题) ${}:相当于直接替换(desc这种关键字),但这种不能预防SQL注入 select * from userinfo where

  • Java MyBatis 多表查询详解

    目录 多表查询: 一对一: 一对多: 多对多: 总结 多表查询: 学生表.班级表.课程表.班级课程表 一对一: 一个学生只属于一个班级. 查询: id  name  age  gender   banjiName SELECT s.id,s.`name`,s.age,s.gender,b.id AS banjiId,b.name AS banjiName FROM student AS s INNER JOIN banji AS b ON s.banji_id=b.id; MyBatis中使用a

随机推荐