【数据库之SQL复杂查询】SQL复杂查询基本语法

一：子查询概念

出现在where子句中的select语句被称为子查询，子查询返回一个集合，通过与返回的集合比较来确定查询集合。

二：子查询应用

2.1 in | not in
基本语法 ：表达式 [not] in 子查询
解释：表达式是子查询中的元素，表现为 表达式[不]包含于子查询返回的集合中，子查询返回的集合[不]包含表达式。

eg:列出李四 张三同学的全部信息

select * from student 
where Sname in("张三" "李四"`);

eg:列出学过001课程号的同学的姓名和学号

select Sname,S# from student
where S# in(select S# from Sc where c#="001");

eg:列出既学过001号课程 又学过002号课程 同学的学号

select S# from Sc
where C#="001" and S# in (select from Sc where C#="002");

eg：列出没学过李明老师讲过课程的所有同学的姓名

select Sname from student 
where S# not in 
(select S# from SC,Course C,Teacher T ,Sc where Tname="李明" and T.T#=C.T# and C.C#=Sc.C#);

2.2 θ some | θ all
基本语法 ：表达式 θ some 子查询/表达式 θ all 子查询
其中 θ是比较运算符 ：<、>、=、<=、>=、<>(不等于)
解释：表达式与子查询返回的集合中的每一个元素满足定义的θ关系。

eg:找出工资最低的教师姓名

select Tname from Teacher 
where salary <=(select salary from Teacher);

eg:找出001号课程成绩不是最高的所有学生的学号

select S# from Sc 
where C#="001" and score <=some(select score from Sc where C#="001");

eg:找出所有课程都不及格的学生姓名

select Sname from student 
where  60 >all（select score from Sc where student.S#=SC.S#）

这里用到相关子查询 即 在子查询中用到外部变量student。这里用student.S#=SC.S#
保证查询为一个学生的全部课程。

eg:找出980311号同学成绩最低的课程号

select C# from Sc 
where S#="980311" and score<= all(select score from Sc where  S#="980311" );

eg：找出张三成绩最低的课程号

select C# from Course,student
where Sname="张三" and student.S#=Sc.S# and score <= all(select score from Sc where student.S#=course.S#);

2.3 exists | not exists
基本语法： [not] exists 子查询

eg:检索选修了张三老师的课程的所有同学的姓名

select Sname from student 
where exists (select * from Sc ,Course C, Teacher T where T.T#=C.T# and C.C#=Sc.C# and  and Tname="张三" and student.S#=Sc.S#)；

或者

select Sname from student S,Sc,Courese C,Teacher T
where T.T#=C.T# and Tname="张三" and S.S#=Sc.S# and C.C#=Sc.C#

eg:检索学过001号教师主讲的所有课程的所有学生的姓名
<=>对于一名学生而言，不存在有一门001号教师主讲的课程，该生没学过。这样的学生的姓名就是要查找的。

select Sname from student 
where not exists (select * from Course where T#="001" and not exists (select * from Sc where Sc.C#=Course.C# and student.S#=Sc.S#));

eg: 列出没学过李明老师讲授任何一门课程的学生姓名
<=>对于一个学生而言，不存在李明老师讲授的一门课程 ，该同学学过。这样的学生的姓名就是要查找的。

select Sname from student S
where not exists (select * from Teacher T,Course C where T.T#=C.T# and Tname="李明" and exists(select * from Sc where Sc.S#=S.S# and Sc.C#=C.C#));

或者

select Sname from student S
where not exists (select *from Teacher T,Course C,Sc where Sc.S#=S.S# and T.T#=C.T# and C.C#=Sc.C# and Tname="李明" );

eg:列出至少学过 3150705同学学过所有课程的 学生的学号
<=>对于一个学生而言，不存在一门3150705同学学过的课程，该同学没有学过。这样的学生的学号就是要查找的。

select S# from student S
where not exists (select * from Sc Sc1 where S#="3050705" and not exists (select * from Sc  Sc2 where Sc2.C#=Sc1.C# and Sc2.S#=S.S#));

三：结果计算与聚集函数

前面我们select再做结果投影是都是列名，其实selec后面还可以一些表达式，聚集函数。
知识点：
select 列名|表达式|聚集函数[,[select 列名|表达式|聚集函数],……]
from 表名1[,[表名2]……]
where 检索条件

3.1结果计算
求任意两位老师的薪水差额。

select T1.Tname as TR1,T2.Tname as TR2, T1.salary-T2.salary as Ts
from Teacher T1,Teacher T2
where T1.salary>T2.salary;

求学生的出生日期，当前是2017年

select S.S#,S.Sname,2017-S.Sage+1 as Tyear
from student;

3.2聚集函数：下面介绍都是对列做操作
求个数 count();
求平均 avg();
eg:求数据库课程的平均成绩

select avg(score)
from Course C,Sc
where C.C#=Sc.C# and Cname="数据库"；sum();

求总和

eg:求教师工资总值。

select sum(salay)
from Teacher;

eg：求计算机系教师工资的总和。

select sum(salary)
from Teacher 
where  D# in (select D# from Dept where Dname="计算机" )；

or

select sum(salary)
from Teacher
where exists (select * from Teacher T,Dept D where T.D#=D.D# and D.Dname="计算机");

求最大 max();
求最小 min();

四:分组查询和分组过滤

分组概念：SQL可以将检索到的元组按照某一条件进行分类，具有相同条件值的元祖可以划分到同一组或同一集合中，同时处理多个组或集合的聚集运算。

1. 分组查询
   基本语法：
   select 表达式| 聚集函数 |列名
   from 列名[,[列名]……]
   where 检索条件
   group by 分组条件

eg:求每一个同学的平均成绩

select S#,avg(score)
from Sc
group by S#;

eg:求每一门课程的平均成绩

select C#,avg(score)
from Sc
group by C#;

1. 分组过滤
   过滤概念：对集合（或分组）进行条件过滤，即满足条件的集合（分组）留下，不满足的过滤掉。

基本语法:
select 表达式| 聚集函数 |列名
from 列名[,[列名]……]
where 检索条件
group by 分组条件 having [过滤条件]

eg：求不及格课程超过两门的同学的学号
先根据学号将同学分组，然后在分组里过滤掉不满足的组

select S#
from Sc
where score<60
group by S# having (count(*)>2);

eg:求十个人以上不及格的课程号

select C#
from Sc
where score<60
group by C# having count(*)>10;

eg:求两门课程以上不及格的同学的学号及其平均成绩。
错误的写法：

select S#,avg(score)
from Sc
where score <60
group by S# having Count(*)>2;

这种写法求出的结果是：两门课程以上不及格的同学的学号和不及格全部课程的平均成绩。where子句是对元组过滤。having 子句对分组后的组进行过滤。

正确的写法。
先找出有两门以上不及格课程的同学的学号。然后在对学号进行分组，求出平均成绩。

select S#,avg(score)
from Sc
where S# in (select S# from Sc where score<60 group by S# having count(*)>60)
group by S#