第3章 关系数据库标准语言SQL——3.4 数据查询（下）

3.4  数据查询（下）

3.4.3  嵌套查询

     在SQL语言中，一个select-from-where语句称为一个查询块。将一个查询块嵌套在另一个查询块的where子句或having短语的条件中的查询称为嵌套查询（nested query）。

例如：

select Sname                      /*外层查询或父查询*/
from Student
where Sno in
        (select Sno                 /*内层查询或子查询*/
        from SC
        where Cno = ‘2’);

     本例中，下层查询块select Sno from where Cno = ‘2’是嵌套在上层查询块select Sname from Student where Sno in的where条件中的。

     上层的查询块称为外层查询或父查询，下层查询块称为内层查询或子查询。

     SQL语言允许多层嵌套查询，即一个子查询中还可以嵌套其他子查询。需要特别指出的是，子查询的select语句中不能使用order by子句，order by子句只能对最终查询结果排序。     嵌套查询使用户可以用多个简单查询构成复杂的查询，从而增强SQL的查询能力。以层层嵌套的方式来构造程序正是SQL中“结构化”的含义所在。

01 带有in谓词的子查询

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

例3.55  查询与“刘晨”在同一个系学习的学生。

先分步来完成此查询，然后再构造嵌套循环。

①确定“刘晨”所在系名

select Sdept from Student where Sname = ‘刘晨’;

结果为CS。

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

select Sno,Sname,Sdept from Student where Sdept = ‘CS’;

结果为

     将第一步查询嵌入到第二步查询的条件中，构造嵌套查询如下：

select Sno,Sname,Sdept        /*例3.55的解法一*/
from Student
where Sdept in(
        select Sdept
        from Student
        where Sname = ‘刘晨’);

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

     本例中的查询也可以用自身连接来完成：

select S1.Sno,S1.Sname,S1.Sdept     /*例3.55的解法二*/
from Student S1,Student S2
where S1.Sdept = S2.Sdept and S2.Sname = ‘刘晨’;

例3.56  查询选修了课程名为“信息系统”的学生学号和姓名。

select Sno,Sname
from Student
where Sno in
        (select Sno
        from SC
        where Cno in
                (select Cno
                from Course
                where Cname = ‘信息系统’
                )
        );

本查询同样可以用连接查询实现：

select Student.Sno,Sname
from Student,SC,Course
where Student.Sno = SC.Sno and
         SC.Cno = Course.Cno and
         Course.Cname = ‘信息系统’;

      有些嵌套查询可以用连接运算替代，有些是不能替代的。从例3.55和例3.56可以看到，查询涉及多个关系时，用嵌套查询逐步求解层次清楚，易于构造，具有结构化程序设计的优点。但是相比于连接运算，目前商用关系数据库管理系统对嵌套查询的优化做得还不够完善，所以在实际应用中，能够用连接运算表达的查询尽可能采用连接运算。

      例3.55和例3.56中子查询的查询条件不依赖于父查询，这类子查询称为不相关子查询。不相关子查询是较简单的一类子查询。如果子查询的查询条件依赖于父查询，这类子查询称为相关子查询（correlated subquery），整个查询语句称为相关嵌套查询（correlated nested query）语句。

02 带有比较运算符的子查询

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

例3.57  找出每个学生超过他自己选修课程平均成绩的课程号。

select Sno,Cno
from SC x
where Grade >= (
        select avg(Grade)
        from SC y
        where y.Sno = x.Sno);

     x是表SC的别名，又称为元组变量，可以用来表示SC的一个元组。内层查询时求一个学生所有选修课程平均成绩的，至于是哪个学生的平均成绩要看参数x.Sno的值，而该值是与父查询相关的，因此这类查询称为相关子查询。

     求解相关子查询不能像求解不相关子查询那样一次将子查询求解出来，然后求解父查询。内层查询由于与外层查询有关，因此必须反复求值。

03 带有any(some)或all谓词的子查询

     子查询返回单值时可以用比较运算符，但返回多指时要用any（有的系统用some）或all谓词修饰符。而使用any或all谓词时则必须同时使用比较运算符。

其语义如下所示：

>any                           大于子查询结果中的某个值

>all                             大于子查询结果中的所有值

<any                           小于子查询结果中的某个值

<all                             小于子查询结果中的所有值

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

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

<=any                         小于等于子查询结果中的某个值

<=all                           小于等于子查询结果中的所有值

=any                           等于子查询结果中的某个值

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

!=(或<>)any               不等于子查询结果中的某个值

!=(或<>)all                 不等于子查询结果中的任何一个值

例3.58  查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄。

select Sname,Sage
from Student
where Sage < any(
        select Sage
        from Student
        where Sdept = ‘CS’)
and Sdept <> ’CS’           /*注意这是父查询块中的条件*/

结果如下：

     本查询也可以用聚集函数来实现，首先用子查询找出CS系中最大年龄（20），然后在父查询中查所有非CS系且年龄小于20岁的学生。SQL语句如下：

select Sname,Sage
from Student
where Sage < (
        select MAX(Sage)
        from Student
        where Sdept = ‘CS’)
and Sdept <> ‘CS’;

例3.59  查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名及年龄。

select Sname,Sage
from Student
where Sage < all(
        select Sage
        from Student
        where Sdept = ‘CS’)
and Sdept <> ‘CS’;

查询结果为：

     本查询同样也可以用聚集函数实现。SQL语句如下：

select Sname,Sage
from Student
where Sage < (
        select MIN(Sage)
        from Student
        where Sdept = ‘CS’)
and Sdept <> ‘CS’;

     事实上，用聚集函数实现子查询通常比直接用any或all查询效率要高。any、all与聚集函数的对应关系如表3.7所示。

表3.7  any（或some）、all谓词与聚集函数、in谓词的等价转换关系

     表3.7中，=any等价于in谓词，<any等价于<max，<>all等价于not in谓词，<all等价于<min，等等。

04 带有exists谓词的子查询

     exists代表存在量词。带有exists谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。

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

select Sname
from Student
where exists (
        select *
        from SC
        where Sno = Student.Sno and 
                Cno = ‘1’);

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

     由exists引出的子查询，其目标列表达式通常都用*，因为带exists的子查询只返回真值或假值，给出列名无实际意义。

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

例3.61  查询没有选修1号课程的学生姓名。

select Sname
from Student
where not exists (
            select *
            from SC
           where Sno = Student.Sno and 
                Cno = ‘1’);

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

例如带in谓词的例3.55可以用如下带exists谓词的子查询替换：

select Sno,Sname,Sdept         /*例3.55的解法四*/
from Student S1
where exists(
        select *
        from Student S2
        where S2.Sdept= S1.Sdept and
                S2.Sname = ‘刘晨’);

例3.62  查询选修了全部课程的学生姓名。

SQL中没有全称量词（for all），但是可以把带有全称量词的谓词转换为等价的带有存在量词的谓词：

     由于没有全称量词，可将题目的意思转换成等价的用存在量词的形式：查询这样的学生，没有一门课程是他不重修的。其SQL语句如下：

select Sname
from Student
where not exists (
        select *
        from Course
        where not exists (
                select *
                from SC
                where Sno = Student.Sno and 
                        Cno = Course.Cno));

例3.63  查询至少选修了学生201215122选修的全部课程的学生号码。

本查询可以用逻辑蕴涵来表达：查询学号为x的学生，对所有的课程y，只要201215122学生选修了课程y，则x也选修了y。形式化表示如下：

用p表示谓词“学生201215122”选修了课程y

用q表示谓词“学生x选修了课程y”

则上述查询为

SQL语言中没有蕴涵（implication）逻辑运算，但是可以利用谓词演算将一个逻辑蕴涵的谓词等价转换为

该查询可以转换为如下等价形式：

它所表达的语义为：不存在这样的课程y，学生201215122选修了y，而学生x没有选。用SQL语言表示如下：

select distinct Sno
from SC SCX
where not exists 
        (select *
        from SC SCY
        where SCY.Sno = ‘201215122’ and
        not exists
                (select *
                from SC SCZ
                where SCZ.Sno = SCX.Sno and
                    SCZ.Cno = SCY.Cno
                )
        );

3.4.4  集合查询

     select语句的查询结果是元组的集合，所以多个select语句的结果可进行集合操作。集合操作主要包括并操作union、交操作intersect和差操作except。

      注意：参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同。

例3.64  查询计算机科学系的学生及年龄不大于19岁的学生。

select *
from Student
where Sdept = ‘CS’
union
select *
from Student
where Sage <= 19;

       本查询实际上是求计算机科学系的所有学生与年龄不大于19岁的学生的并集。使用union将多个查询结果合并起来时，系统会自动去掉重复元组。如果要保留重复元组则用union all操作符。

例3.65  查询选修了课程1或者选修了课程2的学生。

select Sno
from SC
where Cno = ‘1’
union
select Sno
from SC
where Cno = ‘2’;

例3.66  查询计算机科学系的学生与年龄不大于19岁的学生的交集。

select *
from Student
where Sdept = ‘CS’
intersect
select *
from Student
where Sage <= 19;

这实际上就是查询计算机科学系中年龄不大于19岁的学生。

select * from Student
where Sdept = ‘CS’ and
Sage <= 19;

例3.67  查询既选修了课程1又选修了课程2的学生。就是查询选修课程1的学生集合与选修课程2的学生集合的交集。

select Sno
from SC
where Cno = ‘1’
intersect
select Sno
from SC
where Cno = ‘2’;

本例也可以表示为

select Sno from SC
where Cno = ‘1’ and Sno in
        (select Sno
        from SC
        where Cno = ‘2’);

例3.68  查询计算机科学系的学生与年龄不大于19岁的学生的差集。

select *
from Student
where Sdept = ‘CS’
except
select *
from Student
where Sage <= 19;

也就是查询计算机科学系中年龄大于19岁的学生。

select * from Student
where Sdept = ‘CS’ and Sage > 19;

3.4.5  基于派生表的查询

     子查询不仅可以出现在where子句中，还可以出现在from子句中，这时子查询生成的临时派生表（derived table）成为主查询的查询对象。

例如，例3.57找出每个学生超过他自己选修课程平均成绩的课程号，也可以用如下的查询完成：

select Sno,Cno
from SC,(select Sno,avg(Grade) from SC group by Sno)
                as Avg_sc(avg_sno,avg_grade)
        where SC.Sno = Avg_sc.avg_sno and
        SC.Grade >= Avg_sc.avg_grade;

     这里from子句中的子查询将生成一个派生表Avg_sc。该表由avg_sno和avg_grade两个属性组成，记录了每个学生的学号及平均成绩。主查询将SC表与Avg_sc按学号相等进行连接，选出选修课成绩大于其平均成绩的课程号。

     如果子查询中没有聚集函数，派生表可以不指定属性列，子查询select子句后面的列名为其默认属性。

例如例3.60查询所有选修了1号课程的学生姓名，可以用如下查询完成：

select Sname
from Student,(select Sno from SC where where Cno = ‘1’) as SC1
where Student.Sno = SC1.Sno;

     需要说明的是，通过from子句生成派生表时，as关键字可以省略，但必须为派生关系指定一个别名。而对于基本表，别名是可选择项。

3.4.6  select语句的一般格式

select语句的一般格式：

select [all|disinct] <目标列表达式> [别名] [,<目标列表达式> [别名]]···

from <表名或视图名> [别名] [,<表名或视图名>[别名]]···|(<select语句>) [as] <别名>

[where <条件表达式>]

[group by <列名1> [having <条件表达式>]]

[order by <列名2> [asc|desc]];

01 目标列表达式的可选格式

     （1） *

     （2）<表名>.*

     （3）count（[distinct|all] *）

     （4）[<表名>.]<属性列名表达式> [,[<表名>.] <属性列名表达式>]···

     其中，<属性列名表达式>可以是由属性列、作用于属性列的聚集函数和常量的任意算术运算（+，-，*，/）组成的运算公式。

02 聚集函数的一般格式

03 where子句的条件表达式的可选格式

（1）

（2）

（3）

（4）

<属性列名>  [not]  like  <匹配串>

（5）

<属性列名>  is  [not]  null

（6）

[not]  exists  (select语句)

（7）