JOIN 表连接

前面小节介绍了表的设计三范式和单表的查询,本小节介绍如何将通过多个表进行关联查询数据,其中连表查询包括 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;

执行结果如下图:

图片描述

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');

执行结果如下图:

图片描述

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');

执行结果如下图:

图片描述

1.4 向课程表 course 插入数据

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

SELECT * FROM teacher;

执行结果如下图:

图片描述

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);

执行结果如下图:

图片描述

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);

执行结果如下图:

图片描述

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; 

执行结果如下图:

图片描述

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

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

3.INNER JOIN内连接

同样以表 courseteacher 内连接为例:

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

执行结果如下图:

图片描述

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

4.RIGHT JOIN右连接

同样以表 courseteacher 右连接为例:

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

执行结果如下图:

图片描述

TipsRIGHT 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;

执行结果如下图:

图片描述

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 可以理解为 studentstudent_course 左连接查询之后的结果集再对 course 右连接查询。

6.小结

本小节介绍了如何使用 LEFT JOININNER JOINRIGHT 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;

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