子查询（Subsquery）
一、子查询的概念
将一个查询块嵌套在另一个查询块的 WHERE 子句或 HAVING 短语的条件中的查询称为子查询。一个 SELECT-FROM-WHERE 语句称为一个查询块。
例如：select sname from student where sno in (select studentid from sc where courseid=2);
子查询返回的数据通常在主查询里作为一个条件，从而进一步限制数据库返回的数据。它可以用于 SELECT、INSERT、UPDATE 和 DELETE 语句。
在某些情况下，子查询能够间接地基于一个或多个条件把多个表里的数据关联起来，从而代替结合操作。当查询里使用子查询时，子查询首先被执行，然后主查询再根据子查询返回的结果执行。子查询可以用于主查询的 WHERE 或 HAVING 子句。逻辑和关系操作符，比如=、>、<、<>、!=、IN、NOT IN、AND、OR，可以用于子查询里，也可以在 WHERE 或 HAVING 子句里对子查询进行操作。
注意：子查询规则——标准查询的规则同样适用于子查询，结合操作、函数、转换和其他选项都可以在子查询里使用。
二、子查询必须遵守的规则
1、子查询必须位于圆括号里。
2、除非主查询里有多个字段让子查询进行比较，否则子查询的 SELECT 子句里只能有一个字段。
3、子查询里不能使用 ORDER BY 子句。在子查询里，我们可以用 GROUP BY 子句实现 ORDER BY 功能。
4、返回多条记录的子查询只能与多值操作符（比如 IN）配合使用。
5、子查询不能直接被包围在函数里。
6、操作符 BETWEEN 不能用于子查询，但子查询内部可以使用它。
二、子查询的分类
按照内外层是否进行连接划分，子查询可分为嵌套子查询和相关子查询。
1、嵌套子查询
嵌套子查询——子查询独立，不受主查询影响，运算完成后返回结果给主查询。
例1：查询雇员 ID 是1 的上级领导所管理的所有员工数据：
SELECT * FROM demo_employee WHERE 上级=(SELECT 上级 FROM demo_empoyee WHERE 雇员ID=1);
子查询可以嵌入到另一个子查询里，就象子查询嵌套在普通查询里一样。在有子查询时，子查询先于主查询执行。类似地，在嵌套的子查询里，最内层的子查询先被执行，然后再依次执行外层的子查询，直到主查询。
2、相关子查询
相关子查询——主查询将必要信息传入子查询对比，符合的子查询数据才会被外层查询返回结果。
例2：查询雇员ID是1 的上级领导所管理的所有员工数据： SELECT * FROM demo_employee E WHERE EXISTS (SELECT 1 FROM demo_employee D WHERE E.上级=D.上级 AND D.雇员ID=1);
子查询可以与数据操作语言（DML）配合使用。下面我们以数据库 learnsql 中的表做例子加以说明。
三、在 INSERT 语句中使用子查询
INSERT 语句将子查询返回的结果插入到另一个表。
例3：把小时工资高于雇员220984332的所有雇员的 EMP_ID、LAST_NAME、FIRST_NAME 和 PAY_RATE 插入到一个名为 RICH_EMPLOYEES 的表里：
INSERT INTO RICH_EMPLOYEES
SELECT E.EMP_ID, E.LAST_NAME, E.FIRST_NAME, EP.PAY_RATE FROM EMPLOYEE_TBL E, EMPLOYEE_PAY_TBL EP WHERE E.EMP_ID=EP.EMP_ID AND EP.PAY_RATE>(SELECT PAY_RATE FROM EMPLOYEE_PAY_TBL WHERE EMP_ID='220984332');
四、在 UPDATE 语句中使用子查询
子查询可以与 UPDATE 语句配合使用来更新一个表里的一个或多个字段。
例4：把居住在 Indianapolis 的全部雇员的小时工资提高 10%：
UPDATE EMPLOYEE_PAY_TBL
SET PAY_RATE=PAY_RATE*1.1
WHERE EMP_ID IN (SELECT EMP_ID FROM EMPLOYEE_TBL WHERE CITY='INDIANAPOLIS');
这个子查询返回多条记录，因此要使用操作符 IN 而不是等号。
五、在 DELETE 语句中使用子查询
子查询也可以与 DELETE 语句配合使用。
例5：从 EMPLOYEE_PAY_TBL 表里删除 BRANDON GLASS 的记录，由于不知道他的标识号码，我们可以利用一个子查询，根据名字从 EMPLOYEE_TBL 表里获取他的标识号码：
DELETE FROM EMPLOYEE_PAY_TBL
WHERE EMP_ID=(SELECT EMP_IDFROM EMPLOYEE_TBL
WHERE LAST_NAME='GLASS'
AND FIRST_NAME='BRANDON');
六、子查询举例
有学生表 Student(Sno, Sname, Sage, Ssex)，包括学号、姓名、年龄、性别四个字段；
课程表 Course(Cno, Cname, Tno)，包括课程编号、课程名、教师编号三个字段；
成绩表 SC(Sno, Cno, Score)，包括学号、课程编号、成绩三个字段；
教师表 Teacher(Tno, Tname)，包括教师编号、姓名两个字段。
完成如下查询：
1、查询全部学生都选修的课程的课程号和课程名
Select Cno, Cname
From Course
Where Cno in (select Cno from SC
group by Cno);
2、查询没学过"叶平"老师讲授的任一门课程的学生姓名
Select Sname
From Student
Where Sno not in (select Sno from Course,Teacher,SC
where Course.Tno=Teacher.Tno and SC.Cno=course.Cno
and Tname='叶平');
3、查询所有课程成绩小于60分的同学的学号、姓名；
Select Sno,Sname
From Student
Where Sno not in (select S.Sno
from Student S, SC
where S.Sno=SC.Sno and SC.Score>=60);
4、查询有两门以上课程不及格的学生的学号及其平均成绩
Select Sno, avg(Score)
From SC
Where Sno in (select Sno from SC
where Score<60
group by Sno
having count(*)>2)
Group by Sno;
5、查询没有学全所有课的同学的学号、姓名；
Select Student.Sno, Student.Sname
From Student,SC
Where Student.Sno=SC.Sno
Group by Student.Sno, Student.Sname
Having count(SC.Cno)<(select count(Cno) from Course);
6、查询学过编号"001"并且也学过编号"002"课程的同学的学号、姓名
Select Student.Sno, Student.Sname
From Student,SC
Where Studet.Sno=SC.Sno and SC.Cno='001'
and exists (Select * from SC as SC_2
where SC_2.Sno=SC.Sno and SC_2.Cno='002');
7、查询至少有一门课与学号为"1001"的同学所学相同的同学的学号和姓名；
Select S.Sno, S.Sname
From Student S, SC
Where S.Sno=SC.Sno
and SC.Cno in (select Cno from SC
where Sno='1001');
8、查询学过学号为"001"同学所学一门课的其他同学的学号和姓名；
Select distinct S.Sno, S.Sname
From Student S, SC
Where S.Sno=SC.Sno
and Cno in (select Cno from SC
where Sno='001');
9、查询和"1002"号同学学习的课程完全相同的其他同学的学号和姓名；
Select Sno from SC
Where Cno in (select Cno from SC where Sno='1002')
group by Sno
having count(*)=(select count(*) from SC where Sno='1002');
10、向 SC 表中插入一些记录，这些记录要求符合以下条件：没有上过编号"003"课程的同学学号、"002"号课的平均成绩；
Insert SC
Select Sno, '002', (select avg(Score) from SC where Cno='002')
From Student
Where Sno not in (select Sno from SC where Cno='003');
11、查询没学过"叶平"老师课的同学的学号、姓名
Select Student.Sno, Student.Sname
From Student
Where Sno not in (Select distinct SC.Sno
From SC,Course,Teacher
Where SC.Cno=Course.Cno
and Teacher.Tno=Course.Tno
and Teacher.Tname='叶平');
12、查询学过"叶平"老师所教的所有课的同学的学号、姓名；
Select Sno,Sname
From Student
Where Sno in (select Sno from SC,Course,Teacher
where SC.Cno=Course.Cno
And Teacher.Tno=Course.Tno
and Teacher.Tname='叶平'
group by Sno
having Count(SC.Cno)=(select count(Cno) From Course,Teacher
Where Teacher.Tno=Course.Tno
and Tname='叶平') );
13、查询选修"叶平"老师所授课程的学生中，成绩最高的学生姓名及其成绩
Select Student.Sname,SC.Score
From Student,SC,Course C,Teacher
Where Student.Sno=SC.Sno and SC.Cno=C.Cno
and C.Tno=Teacher.Tno
and Teacher.Tname='叶平'
and SC.score=(select max(score) from SC
where Cno=C.Cno);
子查询和连接查询可以结合使用，完成许多复杂的数据库查询任务。本章讲的内容是我们课程的重点，希望大家好好掌握。
课后练习：
1. 在用于 SELECT 语句时，子查询的功能是什么？
2. 在子查询与 UPDATE 语句配合使用时，能够更新多个字段吗？
3. 下面的语法正确吗？如果不正确，正确的语法应该是怎样的？
a.
SELECT CUST_ID, CUST_NAME
FROM CUSTOMER_TBL
WHERE CUST_ID =
(SELECT CUST_ID
FROM ORDERS_TBL
WHERE ORD_NUM = '16C17');
b.
SELECT EMP_ID, SALARY
FROM EMPLOYEE_TBL
WHERE SALARY BETWEEN '20000'
AND (SELECT SALARY
FROM EMPLOYEE_ID
WHERE SALARY = '40000');
c.
UPDATE PRODUCTS_TBL
SET COST = 1.15
WHERE CUST_ID =
(SELECT CUST_ID
FROM ORDERS_TBL
WHERE ORD_NUM = '32A132');
4. 下面语句执行的结果是什么？
DELETE FROM EMPLOYEE_TBL
WHERE EMP_ID IN
(SELECT EMP_ID
FROM EMPLOUEE_PAY_TBL);
5. 使用数据库 learnsql 完成练习：
1）更新表 CUSTOMER_TBL，找到 ORD_NUM 列中订单号为23E934的顾客，把顾客名称修改为 DAVIDS MARKET。
2）查询小时工资高于 JOHN DOE 的全部雇员的姓名，JOHN DOE 的雇员标识号码是343559876。
3）列出所有价格高于全部产品平均价格的产品。
6. 使用数据库 company 完成下面的子查询练习：
1）查询每个部门中工资最高的人的姓名、薪水和部门编号。（提示：先求出每个部门中的最高工资，再使用连接查询。）
2）查询每个部门平均工资所在的等级。
3）查询每个部门内平均的薪水等级。
4）查询雇员中有哪些人是经理人。
5）不准用库函数，求雇员表中薪水的最高值。
6）查询平均薪水最高的部门的部门编号。
（提示： ① 先求出每个部门的平均薪水和部门号（把这个看成一张表）；
② 再求出平均薪水最高值（把这个看成一个值）；
③ 对①表使用②条件进行查询即可。）
7）求平均薪水最高的部门的部门名称（在子查询6的基础之上）。
7. 使用数据库数据库 stuscore 完成下面的子查询练习：
1）查询出所有大连地区的学生的成绩。
2）查询从来没有参加任何考试的学生的信息。
3）查询有考试成绩纪录的学生的信息.
4）将所有大连地区学生的课程编号为3的课程成绩加10分。
5）删除所有C#课的成绩。
6）查询课程编号为3的课程的平均成绩，显示有此门课程成绩的学生编号、成绩及距平均分之间的差值。
7）查询参加了所有考试的学生的姓名。
8）查询有成绩纪录的学生人数。
9）查询有2门以上成绩的学生的姓名和联系方式。
10）查询哪些学生有c#考试成绩，显示这些学生的姓名。
11）查询出科目中教师的姓名，同时增加编号。
12）查询出联系电话为null的学生信息，联系电话用'无'代替。
13）将查询出的地区为大连的都显示为null.
14）查询科目平均成绩超过70分的教师姓名。
8. 在4.1节的练习1中，我们为数据库创建的四个表----学生表（Student）、课程表（Course）、成绩表（Score）以及教师信息表（Teacher）填充了数据。在此基础上完成以下查询：
1）查询Score表中分数最高的学生学号和课程号。
2）假设使用如下命令建立了一个grade表：
create table grade (low int(3), upp int(3), rank char(1));
insert into grade values(90,100,'A');
insert into grade values(80,89,'B');
insert into grade values(70,79,'C');
insert into grade values(60,69,'D');
insert into grade values(0,59,'E');
现查询所有同学的Sno、Cno和rank列。
3）查询选修"3-105"课程的成绩高于"109"号同学成绩的所有同学的记录。
4）查询score中选学多门课程的同学中分数为非最高分成绩的记录。
5）查询成绩高于学号为"109"、课程号为"3-105"的成绩的所有记录。
6）查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
7）查询"张旭"教师任课的学生成绩。
8）查询选修某课程的同学人数多于5人的教师姓名。
9）查询选修编号为"3-105"课程且成绩至少高于选修编号为"3-245"的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
10）查询选修编号为"3-105"且成绩高于选修编号为"3-245"课程的同学的Cno、Sno和Degree.
11）查询成绩比该课程平均成绩低的同学的成绩表。
12）查询所有任课教师的Tname和Depart.
13）查询所有未讲课的教师的Tname和Depart.
14）查询和"李军"同性别的所有同学的Sname.
15）查询和"李军"同性别并同班的同学Sname.
16）查询所有选修"计算机导论"课程的"男"同学的成绩表。