子查询

子查询是这样的一个查询，它的查询结果别作另一个查询的参数。

子查询和连接一样，提供了使用单个查询访问多个表中数据的方法，子查询可以在select，insert，delete，update语句中，使他们能够利用返回的结果。

1、基本表

http://blog.csdn.net/qinyushuang/archive/2010/12/31/6110245.aspx

2、创建和使用返回单值的查询

子查询可以返回一个单纯的数据，这时子查询就如同一个常量，可以将它用于比较运算符，下面就是子查询返回单值的一些应用。

eg1：在多表查询中使用 子查询

从student表中查询修过“计算机入门”这门课程的学生的学号， 姓名和这门课程的成绩

select sno,sname,mark from student where cno =(select cno from course where cname = '计算机入门') 

当然也可以用表的连接的方式来实现上述查询。

 

eg2：在子查询中使用聚合函数

从teacher表中查询年龄高于平均年龄的教师的教工号，姓名，所在系和年龄信息。

select tno,tname,dname,age from teacher where age>(select avg(age)from teacher) order by age 

eg3：比较判别式两边都采用聚合分析的子查询

从teacher表中查询教师的教工号，姓名，所在系，课程号，年龄，并要求教师所在系的平均年龄大于所有教师的平均年龄

select tno,tname,dname,cno,age from teacher as t where(select avg(age) from teacher where dname =t.dname ) >(select avg(age) from teacher)  

 

eg4：在select子句中使用子查询

从teacher表中查询所有教师的教工号，姓名，所在系，课程号，以及在student表中修了这门课程的学生人数

select tno,tname,dname,cno,(select count(*)from student where cno = teacher.cno)as s_sum from teacher order by s_sum 

注意：在select子句中使用子查询时，子查询必须返回单值。必须！！

 

3、创建和使用返回多行的子查询 

一个子查询除了返回一个单一值外，还可以返回一个关系 ，该关系包括若干元组（记录）。SQL提供了若干对于该关系的操作符，并产生一个布尔型的结果，这些操作符主要用在子查询的结果上，这些操作符包括：in , exists,some(any),all,unique等

（1）（not）in子查询

in子查询将源表中的列值与子查询的返回结果进行比较，如果列值与结果集中的列数据之一匹配，那么in判别式的结果为true，查询结果中就包含这行数据。in前可以加not这时候不匹配时，结果为true。

eg1：in子查询实现自连接

在student表中查询存在不及格课程的学生的姓名，所在系，所有课程及成绩信息。采用自连接的形式代码如下

select distinct s1.sname,s1.dname,s1.cno,s1.mark from student as s1,student as s2 where s1.sno = s2.sno and s2.mark < 60 order by s1.mark

查询结果如下：

这里采用in子查询的方式来实现上述查询，代码如下：

select sname,dname,cno,mark from student where sno in (select sno from student where mark < 60) order by mark 

 

查询结果：

 

 

eg2：in子查询实现集合交运算

在student表中，查询修过了1号并且10号课程的学生的学号，姓名及所在系

select sno,sname,dname from student where cno = 1 and sno in (select sno from student where cno =10) 

查询结果：

（2）（not）exists子查询

有一些情况只要子查询返回一个true或者false，子查询后的数据内容本身并不重要，这时候可以使用exists子查询。exists判定式用来测试集合的结果是否为空， 只要查询结果至少返回一个值，则exists判定式的结果为true，如果查询的结果中没有值，那么exists判定式的结果为false。前面也可以在not，意思刚好相反。

eg1：从teacher表中查询教师的教工号 ，姓名，所在系和课程号，要求只查询那些所开设的课程在student表中有学生修过的教师信息

select tno,tname,dname,cno from teacher where exists(select cno from student where cno = teacher.cno) 

查询结果：

6号和7号课程由于没有人选而未出现在结果中。

eg2：使用exists实现两个表的交集

从teacher表中查询女教师开设的，并且7月份考试的课程号，开课教师的姓名，以及教师所在的系

分析：其实是要查询课程号为teacher表中女教师开设的课程后与course表中7月份考试的课程号的交集

select cno,tname,dname from teacher where tsex = '女' and exists(select * from course where month(ctest)= 7 and cno = teacher.cno) 

当然上面的查询也可以用连接表的方式来实现如下：

select t.cno ,tname,dname from teacher as t inner join course as c on t.cno = c.cno where tsex = '女' and month(ctest) = 7 order by t.cno 

查询结果都是：

 

（3）some（any）/all子查询

some和any可以相互替换。

eg1：从student表中查询平均成绩最高的学生的学号及其平均成绩信息

select sno,avg(mark) as avg_score from student group by sno having avg(mark)>=all(select avg(mark)from student group by sno) 

eg2：从teacher表中查询比其他系中比计算机系中所有教师年龄都小的教师的教工号，姓名， 所在系及年龄信息

select tno,tname,dname,age from teacher where age < all(select age from teacher where dname = '计算机') and dname <>'计算机' 

这个查询等价于（使用聚合函数替代all/some关键字）：

select tno,tname,dname,age from teacher where age < (select min(age) from teacher where dname = '计算机') and dname <>'计算机' 

查询结果：

实际上使用聚合函数要比直接使用some或者all查询的效率更高。

some/all与聚集函数的对应关系如下表：

 

（4）unique子查询

unique运算符用来测试集合中是否存在重复元组（记录），如果子查询中没有重复元组，则unique判定式的结果为true，否则为false。

eg1：从course表中查询在student表中只有一个学生修过的课程的课程号， 课程名，学时和考试信息

select cno,cname,ctime ,ctest from course where unique (select sno from student where cno = course.cno) order by cno 

注意：sql不支持unique判定式子，所以没有办法给出执行的结果哦。

但是在sql server中可以在子查询中使用聚合函数达到完成上述查询的目的。

查询代码如下：

select cno ,cname,ctime ,ctest from course where (select count(*) from student where cno = course.cno) = 1 

查询结果如下：

4、相关子查询（略）

5、嵌套子查询

位于其他子查询内的子查询，叫做嵌套地子查询。

eg1：从course表中查询计算机系教师开设的课程信息，并且要求在student表中所有修过这门课程的同学的成绩都及格

select * from course as c where cno in (select cno from teacher where dname = '计算机' and not exists(select * from student where cno = teacher.cno and mark < 60)) order by cno 

运行结果：

当然上述嵌套子查询也可以用下面的查询代码来替代：

select * from course where cno in(select cno from teacher where dname = '计算机') and not exists(select * from student where cno = course.cno and mark < 60) order by cno 

 

6、使用子查询创建视图

创建视图的语句 create view as ...其中as后面就是一个子查询的语句。

利用视图可以使得查询方式显得更直观。

eg：从course表中查询在student表中平均分高于70分的课程号，课程名称和考试时间。

方法一、

--创建一个视图，包括student表中的课程号和各课程的平均成绩信息 create view view_avgmark(cno ,avg_mark) as select cno ,avg(mark) from student group by cno --通过创建的视图进行查询 select cno,cname,ctest from course where cno in (select cno from view_avgmark where avg_mark>70) order by cno 

方法二、

select cno ,cname,ctest from course where cno in (select cno from student group by cno having avg(mark)>70) order by cno 

查询结果：

 

7、树查询

在实际生活中， 很多事务都是按照树型结构组织的，如在一个野战团中的团，营，连，排的从属关系以及人员的上下级关系。

在关系数据库中所有信息都被存储在一个二维表中，这种树型结果是通过隐含的方式来表示的。如在军人信息表（soldierinfo）中，利用一个字段cap指向它的直接上司的编号（soldierno），当其记录为null时，即表明是最高领导。由于关系数据库中这种树型结构不能很直观地表示出来，因此提供了树查询的功能来表示出来。

注意：SQL不支持树查询的功能。但是Oracle支持树查询的功能。

进行树查询时，要让系统确定3点内容：

• 父子关系如何表示
• 按照什么顺序输出。先父后子，还是先子后父
• 以哪个节点为根节点输出。这意味着可以查询整棵树，也可以查询一棵子树

在Oracle中，树查询的语法如下：

select column from table_name connect by prior 父主键 = 子外键 start with column = value 

说明：在connect by子句中“父主键 = 子外键”在soldierinfo表中可以表示为“soldierno = cap”，即表示如果一个记录的soldieno属性值为另一个记录cap属性值，关键字prior用来指定记录的输出顺序，它如果放在父主键之前，表示先输出父主键，放在子外键之前表示先输出子外键。start with子句中表达式用来选择根记录作为树查询的起始点。

eg：假设soldierinfo的树结构为上图，在soldierinfo表中查询以jones作为根节点的子树

select lpad(' ',4*(level - 1))||soldiername,soldierno,cap from soldierinfo connect by prior soldierno = cap start with soldierno = Jones 

上述查询中，lpad(' ',4*(level - 1))用来产生一定数量的空格， 其中的 level表示输出的每一个记录所在的层号，“||”是Oracle中的连接运算符。上述查询达到查询以jones作为根节点的子树的目的。

如果要查询soldierinfo表中所有人员的层次结构关系，只要将最后一句改为：start with soldierno is null。因为最高领导不再有上级领导，因此soldierno为null值的必定是最高一层的根节点。从最高一层的根节点进行树查询，查出来的肯定是整个树的结构关系。