多表连接查询

语法要求

1. 找到多张表之间的关联条件列

2. 列书写时必须是：表名.列名

3. 所涉及到的查询列都放在 SELECT 后

4. 所有的过滤、分组、排序等条件都需按顺序写在 ON 的后面

5. 对于多表连接中，驱动表选择数据行少的表，后续所有表的关联列尽量是主键或唯一键（表设计阶段），至少建立一个索引。

# 两张表的案例：
SELECT country.Name,
country.SurfaceArea,
city.Name,
city.Population
FROM city
JOIN country
ON city.CountryCode=country.Code
WHERE city.Population<100;

# 多张表的案例：
...
...
FROM t1
JOIN t2
ON t1.c1=t2.c1
JOIN t3
ON t2.c3=t3.c3

案例实战：

use school
student ：学生表
sno：    学号
sname：学生姓名
sage： 学生年龄
ssex： 学生性别

teacher ：教师表
tno：     教师编号
tname：教师名字

course ：课程表
cno：  课程编号
cname：课程名字
tno：  教师编号

score  ：成绩表
sno：  学号
cno：  课程编号
score：成绩

-- 项目构建
drop database school;
CREATE DATABASE school CHARSET utf8;
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=utf8;

CREATE TABLE course(
cno INT NOT NULL PRIMARY KEY COMMENT '课程编号',
cname VARCHAR(20) NOT NULL COMMENT '课程名字',
tno INT NOT NULL  COMMENT '教师编号'
)ENGINE=INNODB CHARSET utf8;

CREATE TABLE score (
sno INT NOT NULL COMMENT '学号',
cno INT NOT NULL COMMENT '课程编号',
score INT  NOT NULL DEFAULT 0 COMMENT '成绩'
)ENGINE=INNODB CHARSET=utf8;

CREATE TABLE teacher(
tno INT NOT NULL PRIMARY KEY COMMENT '教师编号',
tname VARCHAR(20) NOT NULL COMMENT '教师名字'
)ENGINE=INNODB CHARSET utf8;

INSERT INTO student(sno,sname,sage,ssex)
VALUES (1,'zhang3',18,'m');

INSERT INTO student(sno,sname,sage,ssex)
VALUES
(2,'zhang4',18,'m'),
(3,'li4',18,'m'),
(4,'wang5',19,'f');

INSERT INTO student
VALUES
(5,'zh4',18,'m'),
(6,'zhao4',18,'m'),
(7,'ma6',19,'f');

INSERT INTO student(sname,sage,ssex)
VALUES
('oldboy',20,'m'),
('oldgirl',20,'f'),
('oldp',25,'m');


INSERT INTO teacher(tno,tname) VALUES
(101,'oldboy'),
(102,'hesw'),
(103,'oldguo');

DESC course;
INSERT INTO course(cno,cname,tno)
VALUES
(1001,'linux',101),
(1002,'python',102),
(1003,'mysql',103);

DESC score;
INSERT INTO score(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 score;

统计zhang3,学习了几门课？

SELECT student.sname,COUNT(score.cno)
FROM student
JOIN score
ON student.sno=score.sno
WHERE sname='zhang3';

查询zhang3,学习的课程名称有哪些?

SELECT student.sname,course.cname
FROM student
JOIN score
ON student.sno=score.sno
JOIN course
ON score.cno=course.cno
WHERE student.sname='zhang3';

查询oldguo老师教的学生名

SELECT teacher.tname,student.sname
FROM teacher
JOIN course
ON teacher.tno=course.tno
JOIN score
ON score.cno=score.cno
JOIN student
ON score.sno=student.sno
WHERE teacher.tname='oldguo'
GROUP BY student.sname;

查询oldguo所教课程的平均分数

SELECT teacher.tname,AVG(score.score)
FROM teacher
JOIN course
ON teacher.tno=course.tno
JOIN score
ON sourse.cno=score.cno
WHERE teacher.tname='oldguo';

每位老师所教课程的平均分,并按平均分排序

SELECT teacher.tname,AVG(score.score)
FROM teacher
JOIN course
ON teacher.tno=course.tno
JOIN score
ON course.cno=score.cno
GROUP BY teacher.tname,course.cname
ORDER BY AVG(score.score) DESC;

查询oldguo所教的不及格的学生姓名

SELECT teacher.tname,student.sname,score.score
FROM teacher
JOIN course
ON teacher.tno=course.tno
JOIN score
ON course.cno=score.cno
JOIN student
ON score.sno=student.sno
WHERE teacher.tname='oldguo' AND score.score<60;
# 此处最后的条件score.score<60不要用 HAVING 子句，
# 因为score.score在整个逻辑里不需要经过计算

查询所有老师所教学生不及格的信息

SELECT teacher.tname,course.cname,student.sname,score.score
FROM teacher
JOIN course
ON teacher.tno=course.tno
JOIN score
ON course.cno=score.cno
JOIN student
ON score.sno=student.sno
WHERE score.score<60;

综合练习

1. 查询平均成绩大于60分的同学的学号和平均成绩；
2. 查询所有同学的学号、姓名、选课数、总成绩；
3. 查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分 
4. 统计各位老师,所教课程的及格率
5. 查询每门课程被选修的学生数
6. 查询出只选修了一门课程的全部学生的学号和姓名
7. 查询选修课程门数超过1门的学生信息
8. 统计每门课程:优秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的学生列表
9. 查询平均成绩大于85的所有学生的学号、姓名和平均成绩