前面小节介绍了表的设计三范式和单表的查询,本小节介绍如何将通过多个表进行关联查询数据,其中连表查询包括 LEFT JOIN、INNER JOIN、RIGHT JOIN。前面小节介绍过表的设计,其中有学生信息表 student、教师信息表 teacher、课程表 course、学生选课关联表 student_course。
为了演示方便,分别清空学生信息表 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
('赵小明',21,'420117199303036666'),
('王小红',22,'420117199303037777'),
('张小虎',18,'420117199303038888'),
('李小平',19,'420117199303039999'),
('刘美丽',21,'420117199303035555'),
('周杰',22,'420117199303034444'),
('秦小贤',19,'420117199303033333'),
('马笑',23,'420117199303032222'),
('艾伦',22,'420117199303031111'),
('包小天',20,'420117199303030000');
执行结果如下图:
Tips:如果上述添加表数据出现报错,可能是因为之前修改表字段的时候把表的字段长度或者类型更改了,请检查字段类型和长度是否正确,还可以删除所有表按照之前建表步骤重新建表。
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');
执行结果如下图:
因为表 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);
执行结果如下图:
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);
执行结果如下图:
以课程表 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 语句说明如下:
同样以表 course 和 teacher 内连接为例:
SELECT c.id AS course_id,c.*,t.* FROM course c INNER JOIN teacher t ON c.teacher_id=t.id;
执行结果如下图:
Tips:INNER JOIN 为内连接,展示的是左右两表都有对应的数据。
同样以表 course 和 teacher 右连接为例:
SELECT c.id AS course_id,c.*,t.* FROM course c RIGHT JOIN teacher t ON c.teacher_id=t.id;
执行结果如下图:
Tips:RIGHT JOIN 为右连接,是以右边的表为’基准’,若左表没有对应的值,用 NULL 来填补。
以本小节所有数据全部连接查询为例:
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 可以理解为 student 和 student_course 左连接查询之后的结果集再对 course 右连接查询。
本小节介绍了如何使用 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。