您当前的位置:首页 > 电脑百科 > 数据库 > MYSQL

《MySQL 教程》JOIN 表连接

时间:2022-04-18 10:04:21  来源:  作者:木子教程

前面小节介绍了表的设计三范式和单表的查询,本小节介绍如何将通过多个表进行关联查询数据,其中连表查询包括 LEFT JOIN、INNER JOIN、RIGHT JOIN。前面小节介绍过表的设计,其中有学生信息表 student、教师信息表 teacher、课程表 course、学生选课关联表 student_course。

1. 插入表测试数据

为了演示方便,分别清空学生信息表 student、教师信息表 teacher、课程表 course、学生选课关联表 student_course 数据,并分别插入测试数据。

1.1 清空表数据

分别清空学生信息表 student、教师信息表 teacher、课程表 course、学生选课关联表 student_course 数据:

 TRUNCATE TABLE student;
 TRUNCATE TABLE teacher;
 TRUNCATE TABLE course;
 TRUNCATE TABLE student_course;

执行结果如下图:

22《MySQL 教程》JOIN 表连接

 

1.2 向学生信息表 student 插入数据

 INSERT INTO student (name,age,id_number) VALUES
 ('赵小明',21,'420117199303036666'),
 ('王小红',22,'420117199303037777'),
 ('张小虎',18,'420117199303038888'),
 ('李小平',19,'420117199303039999'),
 ('刘美丽',21,'420117199303035555'),
 ('周杰',22,'420117199303034444'),
 ('秦小贤',19,'420117199303033333'),
 ('马笑',23,'420117199303032222'),
 ('艾伦',22,'420117199303031111'),
 ('包小天',20,'420117199303030000');

执行结果如下图:

22《MySQL 教程》JOIN 表连接

 

Tips:如果上述添加表数据出现报错,可能是因为之前修改表字段的时候把表的字段长度或者类型更改了,请检查字段类型和长度是否正确,还可以删除所有表按照之前建表步骤重新建表。

1.3 向教师信息表 teacher 插入数据

INSERT INTO teacher (name,age,id_number,emAIl) VALUES
('Tom',29,'420117202006040000','tom@qq.com'),
('Jack',30,'420117202006041111','jack@qq.com'),
('Mary',31,'420117202006042222','mary@qq.com'),
('Timo',35,'420117202006043333','timo@qq.com'),
('Faker',38,'420117202006044444','faker@qq.com'),
('Bob',35,'420117202006045555','bob@qq.com'),
('kelly',40,'420117202006046666','kelly@qq.com'),
('Rose',42,'420117202006047777','rose@qq.com'),
('Hale',55,'420117202006048888','hale@qq.com'),
('John',49,'420117202006049999','john@qq.com'),
('Amy',55,'42011720200604888X','amy@qq.com'),
('Judy',49,'42011720200604999X','judy@qq.com');

执行结果如下图:

22《MySQL 教程》JOIN 表连接

 

1.4 向课程表 course 插入数据

因为表 course 有 teacher_id 字段,所以插入数据之前,需要获取 teacher 表数据:

SELECT * FROM teacher;

执行结果如下图:

22《MySQL 教程》JOIN 表连接

 

teacher 表 id 的值如上图所示,对应 course 表 teacher_id,按照上面 id 可插入相关课程信息:

INSERT INTO course (course_name,teacher_id) VALUES
('高等数学',1),
('英语',2),
('政治',3),
('信息论',4),
('数据结构和算法',5),
('体育',6),
('模拟电路',7),
('数字电路',8),
('通信原理',9),
('信号系统',10),
('概率论',13),
('光学原理',14);

执行结果如下图:

22《MySQL 教程》JOIN 表连接

 

1.5 向学生选课关联表 student_course 插入数据

INSERT INTO student_course (student_id,course_id) VALUES
(1,1),
(1,2),
(2,3),
(2,4),
(3,5),
(3,6),
(4,7),
(4,8),
(5,9),
(5,10),
(6,1),
(6,2),
(7,3),
(7,4);

执行结果如下图:

22《MySQL 教程》JOIN 表连接

 

2.LEFT JOIN 左连接

以课程表 course 和 teacher 左连接为例:

SELECT c.id AS course_id,c.*,t.* FROM course c LEFT JOIN teacher t ON c.teacher_id=t.id; 

执行结果如下图:

22《MySQL 教程》JOIN 表连接

 

Tips:这对上述 sql 语句说明如下:

  • c.id AS course_id 表示将 course表 中 id 字段重命名为 course_id 展示,其目的是为了防止和 teacher表 中 id 字段混淆;
  • c.* 表示 course 表所有字段数据;
  • t.* 表示 teacher 表字段所有数据;
  • ON 后面跟着的条件是连接表的条件;
  • course c 表示将 course 简写为 c, teacher t 表示将 teacher 简写为 t;
  • LEFT JOIN 为左连接,是以左边的表为’基准’,若右表没有对应的值,用 NULL 来填补。

3.INNER JOIN 内连接

同样以表 course 和 teacher 内连接为例:

SELECT c.id AS course_id,c.*,t.* FROM course c INNER JOIN teacher t ON c.teacher_id=t.id;

执行结果如下图:

22《MySQL 教程》JOIN 表连接

 

Tips:INNER JOIN 为内连接,展示的是左右两表都有对应的数据。

4.RIGHT JOIN 右连接

同样以表 course 和 teacher 右连接为例:

SELECT c.id AS course_id,c.*,t.* FROM course c RIGHT JOIN teacher t ON c.teacher_id=t.id;

执行结果如下图:

22《MySQL 教程》JOIN 表连接

 

Tips:RIGHT JOIN 为右连接,是以右边的表为’基准’,若左表没有对应的值,用 NULL 来填补。

5. 多表混合连接

以本小节所有数据全部连接查询为例:

SELECT * FROM 
student a 
LEFT JOIN 
student_course b 
ON a.id=b.student_id 
RIGHT JOIN 
course c 
ON b.course_id=c.id 
INNER JOIN teacher d 
ON c.teacher_id=d.id;

执行结果如下图:

22《MySQL 教程》JOIN 表连接

 

Tips:多表混合连接查询时,后面可以把前面执行的结果集整体当成一个表,例如 SELECT * FROM student a LEFT JOIN student_course b ON a.id=b.student_id RIGHT JOIN course c ON b.course_id=c.id 可以理解为 student 和 student_course 左连接查询之后的结果集再对 course 右连接查询。

6. 小结

本小节介绍了如何使用 LEFT JOIN、INNER JOIN、RIGHT JOIN 对表数据进行连接查询,需要注意的是,ON 后面的表示对连表条件,并且还能对表连接查询的结果集用 WHERE 进行筛选,例如:

SELECT * FROM 
student a 
LEFT JOIN 
student_course b 
ON a.id=b.student_id 
RIGHT JOIN 
course c 
ON b.course_id=c.id 
INNER JOIN teacher d 
ON c.teacher_id=d.id
WHERE a.age > 18 AND d.age < 55;
代码块1234567891011

以上 sql 语句后面使用了 WHERE 条件筛选,表示学生年龄大于 18,教师年龄小于 55。



Tags:MySQL   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,不构成投资建议。投资者据此操作,风险自担。如有任何标注错误或版权侵犯请与我们联系,我们将及时更正、删除。
▌相关推荐
MySQL 核心模块揭秘
server 层会创建一个 SAVEPOINT 对象,用于存放 savepoint 信息。binlog 会把 binlog offset 写入 server 层为它分配的一块 8 字节的内存里。 InnoDB 会维护自己的 savepoint...【详细内容】
2024-04-03  Search: MySQL  点击:(10)  评论:(0)  加入收藏
MySQL 核心模块揭秘,你看明白了吗?
为了提升分配 undo 段的效率,事务提交过程中,InnoDB 会缓存一些 undo 段。只要同时满足两个条件,insert undo 段或 update undo 段就能被缓存。1. 关于缓存 undo 段为了提升分...【详细内容】
2024-03-27  Search: MySQL  点击:(17)  评论:(0)  加入收藏
从 MySQL 到 ByteHouse,抖音精准推荐存储架构重构解读
ByteHouse是一款OLAP引擎,具备查询效率高的特点,在硬件需求上相对较低,且具有良好的水平扩展性,如果数据量进一步增长,可以通过增加服务器数量来提升处理能力。本文将从兴趣圈层...【详细内容】
2024-03-22  Search: MySQL  点击:(29)  评论:(0)  加入收藏
我们一起聊聊MySQL 索引的底层逻辑
数据结构以及算法索引的本质其实就是一种数据结构。我们都希望查询数据的速度能尽可能的快,因此数据库系统的设计者会从查询算法的角度进行优化。最基本的查询算法当然是顺序...【详细内容】
2024-01-03  Search: MySQL  点击:(92)  评论:(0)  加入收藏
MySQL 记录、页、索引的数据结构简析
引言本文在介绍 MySQL 内存中记录、页、索引、游标的数据结构的基础上,通过简单分析插入操作过程中行格式的转换介绍了不同数据结构的关系,其中不涉及加锁相关逻辑。原理记录...【详细内容】
2023-12-28  Search: MySQL  点击:(72)  评论:(0)  加入收藏
数据恢复新姿势:使用MySQL Shell进行更高效灵活的数据恢复
上篇文章(转战MySQL Shell!数据库备份新姿势,轻松搞定备份操作!)简单介绍了使用MySQL Shell进行数据库备份,本文基于上文的备份进行数据恢复演示操作。一、恢复单表因为上次备份的...【详细内容】
2023-12-19  Search: MySQL  点击:(116)  评论:(0)  加入收藏
如何解决 MySQL 主从延时问题?
最近面试了十几个同学,关于 MySQL 主从延时问题,笔者一般都会问。 MySQL 主从延时的原因是什么? 具体哪个环节发生延时? 如何解决呢?对于这“三连问”,极少有同学能通关,甚至有同学...【详细内容】
2023-12-13  Search: MySQL  点击:(129)  评论:(0)  加入收藏
MySQL Repeatable-Read 实现的一些误解
背景首先1992 年发表的SQL Standard 对隔离级别进行的定义是根据几个异象(Dirty Read, Non-Repeatable Read, Phantom Read) , 当然这个定义非常模糊, 后面Jim Grey 也有文...【详细内容】
2023-12-12  Search: MySQL  点击:(144)  评论:(0)  加入收藏
为何在中国 MySQL 远比 PostgreSQL 流行?
首先在全球范围内,MySQL 一直是领先于 PostgreSQL (下文简称 PG) 的。下图是 DB-Engines 的趋势图,虽然 PG 是近 10 年增长最快的数据库,但 MySQL 依然保持着优势。再来看一下...【详细内容】
2023-12-11  Search: MySQL  点击:(202)  评论:(0)  加入收藏
浅析 MySQL 代价模型:告别盲目使用 EXPLAIN,提前预知索引优化策略
背景 在 MySQL 中,当我们为表创建了一个或多个索引后,通常需要在索引定义完成后,根据具体的数据情况执行 EXPLAIN 命令,才能观察到数据库实际使用哪个索引、是否使用索引。这使...【详细内容】
2023-12-07  Search: MySQL  点击:(182)  评论:(0)  加入收藏
▌简易百科推荐
MySQL 核心模块揭秘
server 层会创建一个 SAVEPOINT 对象,用于存放 savepoint 信息。binlog 会把 binlog offset 写入 server 层为它分配的一块 8 字节的内存里。 InnoDB 会维护自己的 savepoint...【详细内容】
2024-04-03  爱可生开源社区    Tags:MySQL   点击:(10)  评论:(0)  加入收藏
MySQL 核心模块揭秘,你看明白了吗?
为了提升分配 undo 段的效率,事务提交过程中,InnoDB 会缓存一些 undo 段。只要同时满足两个条件,insert undo 段或 update undo 段就能被缓存。1. 关于缓存 undo 段为了提升分...【详细内容】
2024-03-27  爱可生开源社区  微信公众号  Tags:MySQL   点击:(17)  评论:(0)  加入收藏
MySQL:BUG导致DDL语句无谓的索引重建
对于5.7.23之前的版本在评估类似DDL操作的时候需要谨慎,可能评估为瞬间操作,但是实际上线的时候跑了很久,这个就容易导致超过维护窗口,甚至更大的故障。一、问题模拟使用5.7.22...【详细内容】
2024-03-26  MySQL学习  微信公众号  Tags:MySQL   点击:(14)  评论:(0)  加入收藏
从 MySQL 到 ByteHouse,抖音精准推荐存储架构重构解读
ByteHouse是一款OLAP引擎,具备查询效率高的特点,在硬件需求上相对较低,且具有良好的水平扩展性,如果数据量进一步增长,可以通过增加服务器数量来提升处理能力。本文将从兴趣圈层...【详细内容】
2024-03-22  字节跳动技术团队    Tags:ByteHouse   点击:(29)  评论:(0)  加入收藏
MySQL自增主键一定是连续的吗?
测试环境:MySQL版本:8.0数据库表:T (主键id,唯一索引c,普通字段d)如果你的业务设计依赖于自增主键的连续性,这个设计假设自增主键是连续的。但实际上,这样的假设是错的,因为自增主键不...【详细内容】
2024-03-10    dbaplus社群  Tags:MySQL   点击:(14)  评论:(0)  加入收藏
准线上事故之MySQL优化器索引选错
1 背景最近组里来了许多新的小伙伴,大家在一起聊聊技术,有小兄弟提到了MySQL的优化器的内部策略,想起了之前在公司出现的一个线上问题,今天借着这个机会,在这里分享下过程和结论...【详细内容】
2024-03-07  转转技术  微信公众号  Tags:MySQL   点击:(32)  评论:(0)  加入收藏
MySQL数据恢复,你会吗?
今天分享一下binlog2sql,它是一款比较常用的数据恢复工具,可以通过它从MySQL binlog解析出你要的SQL,并根据不同选项,可以得到原始SQL、回滚SQL、去除主键的INSERT SQL等。主要...【详细内容】
2024-02-22  数据库干货铺  微信公众号  Tags:MySQL   点击:(54)  评论:(0)  加入收藏
如何在MySQL中实现数据的版本管理和回滚操作?
实现数据的版本管理和回滚操作在MySQL中可以通过以下几种方式实现,包括使用事务、备份恢复、日志和版本控制工具等。下面将详细介绍这些方法。1.使用事务:MySQL支持事务操作,可...【详细内容】
2024-02-20  编程技术汇    Tags:MySQL   点击:(54)  评论:(0)  加入收藏
MySQL数据库如何生成分组排序的序号
经常进行数据分析的小伙伴经常会需要生成序号或进行数据分组排序并生成序号。在MySQL8.0中可以使用窗口函数来实现,可以参考历史文章有了这些函数,统计分析事半功倍进行了解。...【详细内容】
2024-01-30  数据库干货铺  微信公众号  Tags:MySQL   点击:(55)  评论:(0)  加入收藏
mysql索引失效的场景
MySQL中索引失效是指数据库查询时无法有效利用索引,这可能导致查询性能显著下降。以下是一些常见的MySQL索引失效的场景:1.使用非前导列进行查询: 假设有一个复合索引 (A, B)。...【详细内容】
2024-01-15  小王爱编程  今日头条  Tags:mysql索引   点击:(88)  评论:(0)  加入收藏
站内最新
站内热门
站内头条