多表查询类型
笛卡尔乘积
select * from teacher,course; 或者： select * from teacher join course;
内连接 （取交集） mysql> select * from teacher join course on teacher.tno=course.tno ; +-----+--------+------+--------+-----+ | tno | tname | cno | cname | tno | +-----+--------+------+--------+-----+ | 101 | oldboy | 1001 | linux | 101 | | 102 | hesw | 1002 | python | 102 | | 103 | oldguo | 1003 | mysql | 103 | +-----+--------+------+--------+-----+ 3 rows in set (0.00 sec) mysql> select * from teacher,course where teacher.tno=course.tno; +-----+--------+------+--------+-----+ | tno | tname | cno | cname | tno | +-----+--------+------+--------+-----+ | 101 | oldboy | 1001 | linux | 101 | | 102 | hesw | 1002 | python | 102 | | 103 | oldguo | 1003 | mysql | 103 | +-----+--------+------+--------+-----+
外连接 （left join , right join ） mysql> select * from teacher left join course on teacher.tno=course.tno ; +-----+--------+------+--------+------+ | tno | tname | cno | cname | tno | +-----+--------+------+--------+------+ | 101 | oldboy | 1001 | linux | 101 | | 102 | hesw | 1002 | python | 102 | | 103 | oldguo | 1003 | mysql | 103 | | 104 | oldx | NULL | NULL | NULL | | 105 | oldw | NULL | NULL | NULL | +-----+--------+------+--------+------+ 5 rows in set (0.00 sec) mysql> select * from teacher right join course on teacher.tno=course.tno; +------+--------+------+--------+-----+ | tno | tname | cno | cname | tno | +------+--------+------+--------+-----+ | 101 | oldboy | 1001 | linux | 101 | | 102 | hesw | 1002 | python | 102 | | 103 | oldguo | 1003 | mysql | 103 | | NULL | NULL | 1004 | k8s | 108 | +------+--------+------+--------+-----+ 4 rows in set (0.00 sec)
多表连接语法
a 和 b 有直接的关联关系：
select a.x,b.y from a join b on a.z=b.z where group by having order by limit;
a 和 b 没有直接的关联关系：
假如：a 和 c 有关， b和c 有关
a join c on a.i = c.j join b on c.x=b.y
套路 ：
1. 根据题意将所有涉及到的表找出来 a b
2. 找到a和b直接或者间接的关联条件
3. 用join on 语句把所有表连接到一起
4. 罗列其他查询条件
练习题 let`s go！
-- 项目构建 drop database school if exists; CREATE DATABASE school CHARSET utf8mb4; USE school CREATE TABLE student( sno INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号', sname VARCHAR(20) NOT NULL COMMENT '姓名', sage TINYINT UNSIGNED NOT NULL COMMENT '年龄', ssex ENUM('f','m') NOT NULL DEFAULT 'm' COMMENT '性别' )ENGINE=INNODB CHARSET=utf8mb4; CREATE TABLE course( cno INT NOT NULL PRIMARY KEY COMMENT '课程编号', cname VARCHAR(20) NOT NULL COMMENT '课程名字', tno INT NOT NULL COMMENT '教师编号' )ENGINE=INNODB CHARSET utf8mb4; CREATE TABLE sc ( sno INT NOT NULL COMMENT '学号', cno INT NOT NULL COMMENT '课程编号', score INT NOT NULL DEFAULT 0 COMMENT '成绩' )ENGINE=INNODB CHARSET=utf8mb4; CREATE TABLE teacher( tno INT NOT NULL PRIMARY KEY COMMENT '教师编号', tname VARCHAR(20) NOT NULL COMMENT '教师名字' )ENGINE=INNODB CHARSET utf8mb4; INSERT INTO student(sno,sname,sage,ssex) VALUES (1,'zhang3',18,'m'), (2,'zhang4',18,'m'), (3,'li4',18,'m'), (4,'wang5',19,'f'), (5,'zh4',18,'m'), (6,'zhao4',18,'m'), (7,'ma6',19,'f'), (8,'oldboy',20,'m'), (9,'oldgirl',20,'f'), (10,'oldp',25,'m'); INSERT INTO teacher(tno,tname) VALUES (101,'oldboy'), (102,'hesw'), (103,'oldguo'), (104,'oldx'), (105,'oldw'); INSERT INTO course(cno,cname,tno) VALUES (1001,'linux',101), (1002,'python',102), (1003,'mysql',103), (1004,'k8s',108); INSERT INTO sc(sno,cno,score) VALUES (1,1001,80), (1,1002,59), (2,1002,90), (2,1003,100), (3,1001,99), (3,1003,40), (4,1001,79), (4,1002,61), (4,1003,99), (5,1003,40), (6,1001,89), (6,1003,77), (7,1001,67), (7,1003,82), (8,1001,70), (9,1003,80), (10,1003,96); SELECT * FROM student; SELECT * FROM teacher; SELECT * FROM course; SELECT * FROM sc;
练习1 ：统计每个学员，学习课程的门数和课程名列表 练习2 ：每位老师教的学生数量和学生名列表 练习3 ：每位老师教所教课程的平均分 练习4 ：查找学习了hesw但没学习oldguo课程的学生名。 练习5 ：查询出只选修了一门课程的全部学生的学号和姓名 练习6 ：查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分 练习7 ：查询平均成绩大于85的所有学生的学号、姓名和平均成绩 练习8 ：统计各位老师,所教课程的及格率 练习9 ：统计每门课程:优秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的学生列表
答案
--- 练习1 ：统计每个学员，学习课程的门数和课程名列表 -- 关系图： student ----> sc ----> course ---> teacher select CONCAT(student.sname,"_",student.sno),COUNT(*),GROUP_CONCAT(course.cname) from student join sc on student.sno=sc.sno join course on sc.cno=course.cno group by student.sno --- 练习2 ：每位老师教的学生数量和学生名列表 -- 关系图： student ----> sc ----> course ---> teacher select CONCAT(teacher.tname,"_",teacher.tno),COUNT(*),GROUP_CONCAT(student.sname) from teacher join course on teacher.tno=course.tno join sc on course.cno=sc.cno join student on sc.sno=student.sno group by teacher.tno --- 练习3 ：每位老师教所教课程的平均分 select CONCAT(teacher.tname,"_",teacher.tno,"_",course.cno),AVG(sc.score) from teacher join course on teacher.tno=course.tno join sc on course.cno=sc.cno group by teacher.tno , course.cno --- 练习4 ：查找学习了hesw但没学习oldguo课程的学生名。 select a.sname from (select student.sname from teacher join course on teacher.tno=course.tno join sc on course.cno=sc.cno join student on sc.sno=student.sno where teacher.tname = 'hesw') as a left join (select student.sname from teacher join course on teacher.tno=course.tno join sc on course.cno=sc.cno join student on sc.sno=student.sno where teacher.tname = 'oldguo') as b on a.sname=b.sname where b.sname is null SELECT student.`sname`,GROUP_CONCAT(teacher.tname) FROM student JOIN sc ON sc.`sno`=student.`sno` JOIN course ON course.cno=sc.cno JOIN teacher ON teacher.tno=course.`tno` GROUP BY student.sno HAVING GROUP_CONCAT(teacher.tname) LIKE '%hesw%' AND GROUP_CONCAT(teacher.tname) NOT LIKE '%oldguo%' --- 练习5 ：查询出只选修了一门课程的全部学生的学号和姓名 select student.sname,student.sno ,COUNT(*) from course join sc on course.cno=sc.cno join student on sc.sno=student.sno group by student.sno having COUNT(*) = 1 --- 练习6 ：查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分 select course.cname ,MAX(sc.score),MIN(sc.score) from course join sc on course.cno=sc.cno group by course.cno; --- 练习7 ：查询平均成绩大于85的所有学生的学号、姓名和平均成绩 select student.sno,student.sname,AVG(sc.score) from student join sc on student.sno=sc.sno group by student.sno having AVG(sc.score)>85; --- 练习8 ：统计各位老师,所教课程的及格率 case when 条件1 then 输出 1 end --- 练习9 ：统计每门课程:优秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的学生列表 select course.cname , GROUP_CONCAT(case when sc.score>=85 then student.sname end), GROUP_CONCAT(case when sc.score>=70 and sc.score<85 then student.sname end), GROUP_CONCAT(case when sc.score>=60 and sc.score<70 then student.sname end), GROUP_CONCAT(case when sc.score<60 then student.sname end) from course join sc on course.cno=sc.cno join student on sc.sno=student.sno
