JOIN 表连接
前面小节介绍了表的设计三范式和单表的,本小节介绍如何将通过多个表进行关联数据,其中连表 LEFT JOIN、INNER JOIN、RIGHT JOIN。前面小节介绍过表的设计,其中有学生信息表 student、教师信息表 teacher、课程表 course、学生选课关联表 student_course。
1. 插入表测试数据
为了演示方便,分别清空学生信息表 student、教师信息表 teacher、课程表 course、学生选课关联表 student_course 数据,并分别插入测试数据。
分别清空学生信息表 student、教师信息表 teacher、课程表 course、学生选课关联表 student_course 数据:
TRUNCATE TABLE student; TRUNCATE TABLE teacher; TRUNCATE TABLE course; TRUNCATE TABLE student_course;
执行结果如下图:
INSERT INTO student (name,age,id_number) VALUES ('赵小明',,'420117199303036666'), ('王小红',,'420117199303037777'), ('张小虎',,'420117199303038888'), ('李小平',,'420117199303039999'), ('刘美丽',,'420117199303035555'), ('周杰',,'420117199303034444'), ('秦小贤',,'420117199303033333'), ('马笑',,'420117199303032222'), ('艾伦',,'420117199303031111'), ('包小天',,'420117199303030000');
执行结果如下图:
Tips:如果上述表数据出现报错,可能是因为之前表字段的时候把表的字段长度或者类型更改了,请检查字段类型和长度是否正确,还可以所有表按照之前建表步骤重新建表。
INSERT INTO teacher (name,age,id_number,email) VALUES('Tom',,'420117202006040000','tom@qq.com'),('Jack',,'420117202006041111','jack@qq.com'),('Mary',,'420117202006042222','mary@qq.com'),('Timo',,'420117202006043333','timo@qq.com'),('Faker',,'420117202006044444','faker@qq.com'),('Bob',,'420117202006045555','bob@qq.com'),('kelly',,'420117202006046666','kelly@qq.com'),('Rose',,'420117202006047777','rose@qq.com'),('Hale',,'420117202006048888','hale@qq.com'),('John',,'420117202006049999','john@qq.com'),('Amy',,'42011720200604888X','amy@qq.com'),('Judy',,'42011720200604999X','judy@qq.com');
执行结果如下图:
因为表 course 有 teacher_id 字段,所以插入数据之前,需要 teacher 表数据:
SELECT * FROM teacher;
执行结果如下图:
teacher 表 id 的值如上图所示,对应 course 表 teacher_id,按照上面 id 可插入相关课程信息:
INSERT INTO course (course_name,teacher_id) VALUES('高等数学',),('英语',),('政治',),('信息论',),('数据结构和算法',),('体育',),('模拟电路',),('数字电路',),('通信原理',),('信号系统',),('概率论',),('光学原理',);
执行结果如下图:
INSERT INTO student_course (student_id,course_id) VALUES(,),(,),(,),(,),(,),(,),(,),(,),(,),(,),(,),(,),(,),(,);
执行结果如下图:
2.LEFT JOIN 左连接
以课程表 course 和 teacher 左连接为例:
SELECT .id AS course_id,.*,t.* FROM course LEFT JOIN teacher t ON .teacher_id=t.id;
执行结果如下图:
Tips:这对上述 语句说明如下:
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 .id AS course_id,.*,t.* FROM course INNER JOIN teacher t ON .teacher_id=t.id;
执行结果如下图:
Tips:INNER JOIN
为内连接,展示的是左右两表都有对应的数据。
4.RIGHT JOIN 右连接
同样以表 course
和 teacher
右连接为例:
SELECT .id AS course_id,.*,t.* FROM course RIGHT JOIN teacher t ON .teacher_id=t.id;
执行结果如下图:
Tips:RIGHT JOIN
为右连接,是以右边的表为’基准’,若左表没有对应的值,用 NULL
来填补。
5. 多表混合连接
以本小节所有数据全部连接为例:
SELECT * FROM student LEFT JOIN student_course ON .id=.student_id RIGHT JOIN course ON .course_id=.id INNER JOIN teacher ON .teacher_id=.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
可以理解为 student
和 student_course
左连接之后的结果集再对 course
右连接。
6. 小结
本小节介绍了如何使用 LEFT JOIN
、INNER JOIN
、RIGHT JOIN
对表数据进行连接,需要注意的是,ON
后面的表示对连表条件,并且还能对表连接的结果集用 WHERE
进行筛选,例如:
SELECT * FROM student LEFT JOIN student_course ON .id=.student_id RIGHT JOIN course ON .course_id=.id INNER JOIN teacher ON .teacher_id=.idWHERE .age > AND .age < ;
以上 语句后面使用了 WHERE 条件筛选,表示学生年龄大于 18,教师年龄小于 55。