MySQL查询进阶

多表查询、ALLSOME查询、聚集函数查询，分组查询，分组过滤查询

Student表格：

Dept(院系)学院号-学院名-院长：

Course表格：

Teacher表格，教师号-教师姓名-部门号-工资：

SC表格，学号-课程号-分数,反应学生和课程的对应关系：

多表查询
(1)求选修了001课程的学生的学号和姓名
需要了解学生和课程的对应关系，因此需要查询SC表；但SC表无法返回姓名，因此1、在SC表返回选修了001课程学生的学号 2、在Student表中返回学生的姓名。

Select `S#`,Sname From Student Where `S#` in (Select  `S#` From SC Where `C#` = 001);

(2)求既学过001课程又学过002课程的学生的学号
只需要返回学号，因此只查询SC表就够了；先找出选了001课程学生的学号，再看同样的学号是否也选修了002课程，如果是就选中。

Select `S#` From SC Where `C#`=002 AND `S#` in(Select `S#` From SC Where `C#`=001);

(3)列出上过ZhaoSan课程的学生姓名？
涉及到SC，Course，Teacher，Student四张表。将SC，Course，Teacher三张表做联合，设条件过滤，取出S#到Student中返回Sname

Select Sname From Student Where `S#` in (Select `S#` From SC, Teacher T, Course C Where T.Tname = 'ZhaoSan' AND C.`C#`=SC.`C#` AND C.`T#` = T.`T#`);

(4)找出工资最低的教师姓名

Select Tname From Teacher Where Salary<=All (Select Salary From Teacher );

ALL和SOME关键字
（1）找出001号课成绩不是最高的所有学生的学号
< 后接some，some表示最大值，>后接some，some表示最小值

Select `S#` From SC Where `C#`='001'  AND Score < some(Select Score From SC Where `C#` =001);

（2）找出所有课程都不及格的学生姓名
ALL关键字和some相反

 Select Sname From Student Where 60 > ALL (Select Score From SC Where `S#`=Student.`S#`);

聚集函数查询

求教师的工资总额？

Select Sum(Salary) From Teacher;

求计算机系教师的工资总额？
需要先去Dept表中查找CS的系号，然后去Teacher表中查找CS系的教师，求工资总和。

Select sum(Salary)  From Teacher T, Dept Where T.`D#` = Dept.`D#` and Dept.Dname='CS' ;

求数据库课程的平均成绩？
需要用到Course表格和SC表格，Course表格返回DataBase课程的编号，SC表格找出对应编号的课程成绩

 Select AVG(Score) From Course, SC Where Course.Cname='DataBase' and Course.`C#` = SC.`C#`;

分组查询

求每一个学生的平均成绩？

Select `S#`, AVG(Score) From SC Group by `S#`;

求每一门课程的平均成绩？
需要知道每门课程所有考生的成绩，因此需要Course和SC表格，Course表格返回课程号，SC表格返回

Select `C#`, AVG(Score) From SC Group by `C#`;

分组过滤查询

求不及格课程超过两门的同学的学号：
求有课程不及格同学的学号：

Select `S#` From SC Where Score < 60 Group by `S#` Having Count(*) > 2;