3. 多层select的使用：子查询的定义、无关子查询、相关子查询

子查询的定义

SELECT语句可以嵌套在其他许多语句中，这些语句包括SELECT、INSERT、UPDATE及DELETE等，
这些嵌套的SELECT语句就被称为子查询。

---

无关子查询

无关子查询在外部查询之前执行，然后返回数据供外部查询使用，无关子查询中不包含对于外部查询的任何引用。

1. 查询与“张三”在同一个系学习的学生的学号、姓名和专业。

SELECT sno, sname, sdept 
FROM student
WHERE sdept IN
	(SELECT sdept FROM  student
	 WHERE sname='张三')

2. 查询 1号课的考试成绩 比“张三”高的学生的学号和姓名。

SELECT student.sno,sname 
FROM student,sc
WHERE student.sno = sc.sno and cno='1' 
and score>(SELECT score FROM sc WHERE cno='1' and  sno =(SELECT sno FROM student WHERE sname='张三'))

3. 查询计算机系年龄最大的学生的学号和姓名。(用了ALL)

SELECT sno,sname 
FROM student 
WHERE sage>= ALL
 (SELECT sage 
  FROM student 
  WHERE sdept='CS')
 

4. 查询选修了“1”号课程的学生姓名和所在系。

SELECT sname,sdept 
FROM student
WHERE sno IN
 (SELECT sno FROM sc    
  WHERE cno='1')

5.查询有2个以上学生平均成绩超过60分的系

SELECT sdept 
FROM student,(SELECT sno FROM SC GROUP BY sno HAVING AVG(score)>=60) s      --派生表ss为成绩大于60分的学生的学号
WHERE student.sno=s.sno 
GROUP BY sdept 
HAVING COUNT(*)>=2

---

相关子查询

在相关子查询中，子查询的执行依赖于外部查询，多数情况下是子查询的WHERE子句中引用了外部查询的表。

相关子查询的执行过程与无关子查询完全不同，无关子查询中子查询只执行一次，而相关子查询中的子查询需要重复地执行。

相关子查询的执行过程：

①子查询为外部查询的每一行执行一次，外部查询将子查询引用的列的值传给子查询。
②如果子查询的任何行与其匹配，外部查询就返回结果行。
③再回到第一步，直到处理完外部表的每一行。

1. 查询成绩比该课的平均成绩低的学生的学号、课程号、成绩。

SELECT sno,cno,score 
FROM  sc a  
WHERE score<
	(SELECT avg(score)  
	 FROM sc b  
	 WHERE b.cno=a.cno)

2. 查询有2门以上课程的成绩在80分以上的学生的学号、姓名、专业。

SELECT sno,sname, specialty 
FROM student s
WHERE (SELECT COUNT(*) FROM sc 
                  WHERE sc.sno=s.sno 
                  and score >=80)>=2

---

带有EXISTS的子查询（存在性测试）

运算符EXISTS:
如果在子查询的结果集内包含有一行或多行，则存在性测试返回TRUE
如果不包含任何行，则返回FALSE。

NOT EXISTS: 将对存在性测试结果取反。

查询所有选修了‘1’号课程的学生姓名。

SELECT sname FROM student
WHERE EXISTS (SELECT * FROM sc
			  WHERE sno=student.sno AND cno= ‘1')

使用子查询时要注意以下几点