MySQL连表update操作

半月无霜

发布于 2023-03-03 15:13:06

4.2K0

发布于 2023-03-03 15:13:06

文章被收录于专栏：半月无霜

MySQL连表update操作

一、介绍

记录一下MySQL连表后进行update的操作，这可以一口气同时改动到多张表的数据，可以取到关联表的数据进行更新。

作用还是比较大的，如下

二、使用

1）建表

分别有以下四张表，分别为

学生表
班级表
学生评分表
班级评分表

CREATE TABLE `tb_student` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `code` varchar(20) NOT NULL COMMENT '学号',
  `name` varchar(20) NOT NULL COMMENT '姓名',
  `class_id` int(11) NOT NULL COMMENT '班级ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT = '学生表';

CREATE TABLE `tb_student_grade` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `student_id` int(11) NOT NULL COMMENT '学生ID',
  `grade` int(11) NOT NULL COMMENT '评分',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT = '学生评分表';

CREATE TABLE `tb_class` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `class_name` varchar(20) DEFAULT NULL COMMENT '班级名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT = '班级表';

CREATE TABLE `tb_class_grade` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `class_id` int(11) NOT NULL COMMENT '班级ID',
  `grade` int(11) NOT NULL COMMENT '评分',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT = '班级评分表';

模型如下图

2）连表更新

如果班级里张三比较调皮，在班级座位后面睡觉被校长发现了，要把这位学生和所在班级的评分，各扣10分

如果是以前，我可能是写两条update语句的sql，现在的话，可以关联起来这样写，如下

首先我们可以先写一个这样的sql，通过学生名称查询到学生分数，所在班级分数，那么这条sql就长这样

select 
	t1.grade as "学生分数",
	t2.grade as "班级分数"
from tb_student t0
join tb_student_grade t1 on t0.id = t1.student_id
join tb_class_grade t2 on t0.class_id = t2.class_id
where t0.`name` = '张三';

那么此时，我们只需要做一点小小的改动，就可以把上述sql改为连表update的了。

将select 字段... from 改为update
在where条件前写上自己，需要更新的字段

简单两步，改动结果后的sql如下

update tb_student t0
join tb_student_grade t1 on t0.id = t1.student_id
join tb_class_grade t2 on t0.class_id = t2.class_id
set t1.grade = t1.grade - 10,
	t2.grade = t2.grade - 10
where t0.`name` = '张三';

执行完成就可以了，再查询看看效果