跟我一起学Oracle 11g【9】

前言 在前面2个章节，我们比较详细的介绍了一些SQL语句的基本用法，但是在我们实际的项目开发中，其实很多时候这些基本的用法远远不能满足我们项目的需求，这个时候就需要我们的嵌套查询。 在SQL语句中，一个select-from-where语句称为一耳光查询快。将一个

前言

在前面2个章节，我们比较详细的介绍了一些SQL语句的基本用法，但是在我们实际的项目开发中，其实很多时候这些基本的用法远远不能满足我们项目的需求，这个时候就需要我们的嵌套查询。

在SQL语句中，一个select-from-where语句称为一耳光查询快。将一个查询快嵌套在另外一个的where子句或having 短语的条件的查询称为嵌套查询（Nested Query）。

比如，先举一个简单的例子：

<span>select</span><span> Sname  <span>--------------外查询语句块------- </span>
</span><span>from</span><span> Student
</span><span>where</span> Sno <span>in  <span>--------------外查询语句块------- </span></span><span>
      (      --------------内查询语句块-------     
         </span><span>select</span><span> Sno
          </span><span>From</span><span> Sc
          </span><span>where</span> Cno<span>=</span><span>2
             <span>--------------</span></span><span><span>内查询语句块</span></span><span><span>------- </span></span><span>
      );</span>

查询语句块分为外查询语句或叫父查询以及内查询或子查询。

NOTE:SQL语言允许多层嵌套查询，即一个子查询中还可以嵌套其他子查询。需要特别指出的是，子查询的select语句中不能使用order by字句，order by 只能对最终查询结果排序。

一。带有in谓词的子查询

在嵌套查询中，子查询往往是一个集合，所以谓词in是嵌套查询中最经常使用的谓词。

【例1】查询与“XiaoHong”在同一个系的学生

OK，我们一步一步的来分解这个查询步骤：

①确定“XiaoHong”所在的系

<span>select</span><span> sdept
</span><span>from</span><span> student 
</span><span>where</span> sname<span>=</span><span>'</span><span>XiaoHong</span><span>'</span>

得到的结果是：CS。

②查找所有在CS系学习的所有学生

<span>select</span><span> sno,sname,sdept 
</span><span>from</span><span> student 
</span><span>where</span> sdept<span>=</span><span>'</span><span>CS</span><span>'</span>;

ok,得到如下结果：

③合并操作。

现在我们只需要把上面的操作合并在一起就好了！

<span>select</span><span> sno,sname,sdept 
</span><span>from</span><span> student 
</span><span>where</span> sdept <span>in</span><span> 
(
　　</span><span>select</span><span> sdept
　　</span><span>from</span><span> student 
　　</span><span>where</span> sname<span>=</span><span>'</span><span>XiaoHong</span><span>'</span><span>
);</span>

得到的结果也是一样。

本例中，因为子查询的查询条件不依赖于父查询，所以称为不相关查询。

当然本例中也还可以有其他解法：自连接方法如下：

<span>select</span><span> s1.sno,s1.sname,s1.sdept 
</span><span>from</span><span> student s1,student s2 
</span><span>where</span> s1.sdept<span>=</span>s2.sdept <span>and</span> s2.sname<span>=</span><span>'</span><span>XiaoHong</span><span>'</span>;

可见，一个查询有很多种方法，当然不同的方法执行的效率可能会有很大的不同。

【例2】查询选修了课程名为”Math“的学生学号和姓名

看似这一句很短，其实这里有2个子查询。我们需要先弄清楚里面的关系，然后在来进行解答

思路如下：

1）.课程名在course表中才有，所以我们从course表中选出课程名为Math的课程号（Cno）

2）.选出课程号之后，在在SC表中，找到课程号所对应的学生号

3）.在student表中找出对应的学号和学生名字。

<span>select</span><span> sno,sname   --第三步，最后从student表中取出数据出来。 
</span><span>from</span><span> student 
</span><span>where</span> sno <span>in</span><span>
(
     </span><span>select</span><span> sno --第二步。。找出SC关系表中的选修了2号课程的学生学号 
     </span><span>from</span><span> sc 
     </span><span>where</span> cno <span>in</span><span>
           (
               </span><span>select</span><span> cno     -- 第一步。。找出course表中的课程名为math的课程号。结果为2.
　　　　　　　　 </span><span>from</span><span> course 
　　　　　　　　 </span><span>where</span> cname<span>=</span><span>'</span><span>Math</span><span>'</span><span>
　　　　　　 )
);</span>

结果如下：

代替方法：

<span>select</span> student.sno,sname <span>from</span><span> 
student,sc,course 
</span><span>where</span> course.cname<span>=</span><span>'</span><span>Math</span><span>'</span> 
<span>and</span> course.cno<span>=</span>sc.cno <span>and</span> sc.sno<span>=</span>student.sno;

结果也是一样滴。

二。带有比较运算符的子查询

带有比较运算符的子查询是指父查询之间用比较运算符进行连接。当用户能确切知道内存查询返回的是单值时，可以用>,=,等等比较运算符。

【例3】和例1 是一样的：查询与“XiaoHong”在同一个系的学生

因为一个学生只可能在一个系学习，也就是说内查询的结果是一个值，因此可以用“=”代替“in”

如下：

<span>select</span><span> sno,sname,sdept 
</span><span>from</span><span> student 
</span><span>where</span> sdept <span>=</span><span>
(
　　</span><span>select</span><span> sdept
　　</span><span>from</span><span> student 
　　</span><span>where</span> sname<span>=</span><span>'</span><span>XiaoHong</span><span>'</span><span>
);</span>

这样 也能得到和例1的结果。

需要注意的是，子查询一定要跟在比较符之后，下面写法是错误的：

<span>----------------错误写法---------------------
select</span><span> sno,sname,sdept 
</span><span>from</span><span> student 
</span><span>where</span><span> 
(
　　</span><span>select</span><span> sdept
　　</span><span>from</span><span> student 
　　</span><span>where</span> sname<span>=</span><span>'</span><span>XiaoHong</span><span>'</span>) <span>=</span>sdept;

<span>----------------错误写法---------------------</span>

【例4】找出每个学生超过他选修课课程平均成绩的课程号

这个怎么理解呢？我们先把结果写出来，然后再来剖析它。

<span>select</span><span> sno,cno 
</span><span>from</span><span> sc x 
</span><span>where</span> grade<span>>=</span><span>
(
　　　　</span><span>select</span> <span>avg</span><span>(grade) 
　　　　</span><span>from</span><span> sc y 
　　　　</span><span>where</span><span> y.
　　　　sno</span><span>=</span>x.sno);

x是Sc表的别名，y也是一样。内存查询是求一个学生所有选修课程平均成绩，至于是哪个学生的平均成绩要看s.sno的值。，而该值是和父查询有关的，所以这类查询叫做相关子查询。

执行过程可能如下：

①从外层查询中取出SC表中的一行（也就是元组 x），将x的值sno

<span>select</span> <span>avg</span><span>(Grade) 
</span><span>from</span><span> sc y 
</span><span>where</span> y.sno<span>=</span><span>1</span>;

得到结果是98.5

②执行内查询得到的结果是：98.5，所以得到外查询是：

<span>select</span><span> sno,cno 
</span><span>from</span><span> sc x 
</span><span>where</span> Grade<span>>=</span><span>98.5</span>;

得到结果如下：

然后，外层查询取出下一个元组重复做上面的动作。。。

得到的结果是：

三。带有any（some）或all谓词的子查询

子查询返回单值可以用比较算术法，但是返回多值时要用any（有些系统用some）或者all谓词修饰符。而是用any或all谓词时则必须同时是用比较运算符。
>any 大于子查询结果中的某个值
>all 大于子查询结果中的所有值

>=any大于等于子查询结果中的某个值
>=all 大于等于子查询结果中的所有值

=any 等于子查询结果中的中的某个值
=all 等于子查询结果中的所有值（通常没有实际意思）

!=（或）any 不等于子查询结果中的某个值
!=（或）all 不等于子查询结果中的的任何一个值

【例5】查询其他系中比计算机科学系某一个学生年龄小的学生姓名和年龄

SQL语句如下：

<span>select</span><span> sname,sage 
</span><span>from</span><span> student
</span><span>where</span> sage <span> <span>any</span><span>
(
    </span><span>select</span><span> sage 
        </span><span>from</span><span> student 
    </span><span>where</span> sdept<span>=</span><span>'</span><span>CS</span><span>'</span><span>
) </span><span>and</span> sdept <span></span> <span>'</span><span>CS</span><span>'</span>; <span>--</span><span> 注意这是父查询的条件</span></span>

数据库执行执行此查询的时候，首先处理子查询，找出CS系中所有学生的年龄，找出一个集合（18,22）。任何处理父查询，找出所有不是CS系并且年龄小于18或22的学生即可。

本查询也可以用聚集函数来实现。首先用子查询查出CS系中最大年龄（22），任何在父查询中找出所有非CS系并且年龄小于22岁的学生即可。

<span>select</span><span> sname,sage 
</span><span>from</span><span> student
</span><span>where</span> sage <span><span>
(
    </span><span>select</span> <span>max</span><span>(sage)
    </span><span>from</span><span> student
    </span><span>where</span> sdept<span>=</span><span>'</span><span>CS</span><span>'</span><span>
) </span><span>and</span> sdept<span></span><span>'</span><span>CS</span><span>'</span></span>

也可以得到上面的结构。结果如下：

【例6】查询其他系中比计算机科学系所有学生年龄都小的学生姓名和年龄

经过上面的分析，这个很简单，如下

<span>select</span><span> sname,sage 
</span><span>from</span><span> student
</span><span>where</span> sage <span><all><span>
(
    </span><span>select</span><span> sage
    </span><span>from</span><span> student
    </span><span>where</span> sdept<span>=</span><span>'</span><span>CS</span><span>'</span><span>
) </span><span>and</span> sdept<span></span> <span>'</span><span>CS</span><span>'</span>;</all></span>

这里需要向大家说声对不起，刚开始构建数据的时候，没有构建好，所以这里查询为空，为了试验的完整性，我这里修改一下数据结构。

<span>update</span> student <span>set</span> sage<span>=</span><span>17</span> <span>where</span> sname<span>=</span><span>'</span><span>XiaoZhang</span><span>'</span>;

ok，我们现在在执行刚才的操作，可以得到如下结果。

本查询也可以使用聚集查询来实现，如下：

<span>select</span><span> sname,sage
</span><span>from</span><span> student
</span><span>where</span> sage <span><span>
(
    </span><span>select</span> <span>min</span><span>(sage)
    </span><span>from</span><span> student
    </span><span>where</span> sdept<span>=</span><span>'</span><span>CS</span><span>'</span><span>
) </span><span>and</span> Sdept <span>!=</span><span>'</span><span>CS</span><span>'</span>;</span>

事实上，聚集函数实现子查询通常比直接用all或any的效率更好。对应关系表如下：

	=	或!=			>	>=
ANY	IN	==			>Min	>=Min
ALL	==	NOT IN

四。带有exists谓词的子查询

带有exists谓词的子查询不返回任何数据，只产生逻辑真或假，也就是true或false。

【例6】查询所有选修了1号课程的学生姓名

<span>select</span><span> sname 
</span><span>from</span><span> student
</span><span>where</span> <span>exists</span><span>
(
    </span><span>select</span> <span>*</span>
    <span>from</span><span> sc
    </span><span>where</span> Sno<span>=</span>Student.Sno <span>and</span> Cno<span>=</span><span>1</span><span>
)</span>

那上面的执行流程是怎么样的呢？

①从外查询student表中取出一行数据

②把这条数据的Sno和内查询Sc表中的Sno进行比较

③若比较结果为真，则把这数据的sname放入一个结果集合中。

④然后再取student表的吓一条数据，直到取完为止。

使用exists量词后，若内存结果非空，则外层的where字句返回真值，否则返回假值。

由于exists引出的子查询，起目标列表达式通常都是“*”，因为带有exists的子查询只返回true或false，给出列名无具体意思。

exists谓词相对应的是not exists。使用not exists后，如果内存查询结果为空，则外层的where字句返回真值，否则返回假值。

【例7】查询没有选修了1号课程的学生姓名

<span>select</span><span> sname 
</span><span>from</span><span> student
</span><span>where</span> <span>not</span> <span>exists</span><span>
(
    </span><span>select</span> <span>*</span>
    <span>from</span><span> sc
    </span><span>where</span> Sno<span>=</span>Student.Sno <span>and</span> Cno<span>=</span><span>1</span><span>
)</span>

一些带exists或not exists谓词的子查询不能被其他形式的子查询等价替换，但是其他带有in谓词、比较运算符、any、all谓词的子查询都能用exists谓词的子查询替换。

啊