多表查询、ALLSOME查询、聚集函数查询,分组查询,分组过滤查询
Student表格:
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200703011701114.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RvcnlZYW5n,size_16,color_FFFFFF,t_70#pic_center)
Dept(院系)学院号-学院名-院长:
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200703012345795.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RvcnlZYW5n,size_16,color_FFFFFF,t_70#pic_center)
Course表格:
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200703013040904.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RvcnlZYW5n,size_16,color_FFFFFF,t_70#pic_center)
Teacher表格,教师号-教师姓名-部门号-工资:
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200703013738305.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RvcnlZYW5n,size_16,color_FFFFFF,t_70#pic_center)
SC表格,学号-课程号-分数,反应学生和课程的对应关系:
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200703014602208.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RvcnlZYW5n,size_16,color_FFFFFF,t_70#pic_center)
多表查询
(1)求选修了001课程的学生的学号和姓名
需要了解学生和课程的对应关系,因此需要查询SC表;但SC表无法返回姓名,因此1、在SC表返回选修了001课程学生的学号 2、在Student表中返回学生的姓名。
Select `S#`,Sname From Student Where `S#` in (Select `S#` From SC Where `C#` = 001);
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200703022528999.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RvcnlZYW5n,size_16,color_FFFFFF,t_70#pic_center)
(2)求既学过001课程又学过002课程的学生的学号
只需要返回学号,因此只查询SC表就够了;先找出选了001课程学生的学号,再看同样的学号是否也选修了002课程,如果是就选中。
Select `S#` From SC Where `C#`=002 AND `S#` in(Select `S#` From SC Where `C#`=001);
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200703025716271.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RvcnlZYW5n,size_16,color_FFFFFF,t_70#pic_center)
(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#`);
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200703130116900.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RvcnlZYW5n,size_16,color_FFFFFF,t_70#pic_center)
(4)找出工资最低的教师姓名
Select Tname From Teacher Where Salary<=All (Select Salary From Teacher );
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200703131919197.png#pic_center)
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);
![在这里插入图片描述](https://img-blog.csdnimg.cn/2020070313252043.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RvcnlZYW5n,size_16,color_FFFFFF,t_70#pic_center)
(2)找出所有课程都不及格的学生姓名
ALL关键字和some相反
Select Sname From Student Where 60 > ALL (Select Score From SC Where `S#`=Student.`S#`);
聚集函数查询
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200707224133995.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RvcnlZYW5n,size_16,color_FFFFFF,t_70#pic_center)
求教师的工资总额?
Select Sum(Salary) From Teacher;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200707231820976.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RvcnlZYW5n,size_16,color_FFFFFF,t_70#pic_center)
求计算机系教师的工资总额?
需要先去Dept表中查找CS的系号,然后去Teacher表中查找CS系的教师,求工资总和。
Select sum(Salary) From Teacher T, Dept Where T.`D#` = Dept.`D#` and Dept.Dname='CS' ;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200707235330161.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RvcnlZYW5n,size_16,color_FFFFFF,t_70#pic_center)
求数据库课程的平均成绩?
需要用到Course表格和SC表格,Course表格返回DataBase课程的编号,SC表格找出对应编号的课程成绩
Select AVG(Score) From Course, SC Where Course.Cname='DataBase' and Course.`C#` = SC.`C#`;
分组查询
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200707232010803.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RvcnlZYW5n,size_16,color_FFFFFF,t_70#pic_center)
求每一个学生的平均成绩?
Select `S#`, AVG(Score) From SC Group by `S#`;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200707232838818.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RvcnlZYW5n,size_16,color_FFFFFF,t_70#pic_center)
求每一门课程的平均成绩?
需要知道每门课程所有考生的成绩,因此需要Course和SC表格,Course表格返回课程号,SC表格返回
Select `C#`, AVG(Score) From SC Group by `C#`;
分组过滤查询
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200708002036370.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RvcnlZYW5n,size_16,color_FFFFFF,t_70#pic_center)
求不及格课程超过两门的同学的学号:
求有课程不及格同学的学号:
Select `S#` From SC Where Score < 60 Group by `S#` Having Count(*) > 2;