数据库原理与应用：第9章 嵌套查询

数据库原理与应用

第9章查询

9.1单表查询

9.2连接查询

9.3嵌套查询9.3嵌套查询嵌套查询概述嵌套查询分类嵌套查询求解方法引出子查询的谓词

嵌套查询(续)嵌套查询概述一个SELECT-FROM-WHERE语句称为一个查询块将一个查询块嵌套在另一个查询块的WHERE子句的条件中的查询称为嵌套查询，此查询块称为子查询或内层查询，包含子查询的查询块称为父查询或外层查询。

嵌套查询(续)

SELECTsno,sname /*外层查询/父查询*/FROMStudentWHEREsnoIN

(SELECTsno/*内层查询/子查询*/FROMscoreWHEREcno='3-105')嵌套查询(续)子查询不能使用ORDERBY子句有些嵌套查询可以用连接查询替代嵌套查询分类不相关子查询子查询的查询条件不依赖于父查询，子查询的查询条件没用引用父查询表中的属性值。相关子查询子查询的查询条件依赖于父查询，子查询的查询条件引用了父查询表中的属性值。嵌套查询求解方法不相关子查询是由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查询条件。嵌套查询求解方法（续）相关子查询首先取外层查询中表的第一个元组，把与内层查询相关的属性值代入并处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表；然后再取外层表的下一个元组；重复这一过程，直至外层表全部检查完为止。带有IN谓词的子查询带有比较运算符的子查询带有ANY或ALL谓词的子查询带有EXISTS谓词的子查询一、带有IN谓词的子查询[例37]查询与“王芳”在同一个班学习的学生。此查询要求可以分步来完成：①确定“王芳”所在班

SELECTclassFROMstudentWHEREsname='王芳'

结果为：带有IN谓词的子查询（续）②查找所有在95031学习的学生。

SELECT*FROMstudentWHEREclass='95031'结果为：

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

SELECT*FROMstudentWHEREclassIN(SELECTclassFROMstudentWHEREsname='王芳')此查询为不相关子查询。DBMS求解该查询时也是分步去做的。带有IN谓词的子查询（续）

用自身连接完成本查询要求

SELECTS1.*FROMstudentS1,studentS2WHERES1.Sdept=S2.SdeptAND

S2.sname='王芳'带有IN谓词的子查询（续）父查询和子查询中的表均可以定义别名

SELECT*FROMstudentS1WHERES1.classIN(SELECTclassFROMstudentS2WHERES2.sname='王芳')带有IN谓词的子查询（续）[例38]查询选修了课程名为“操作系统”的学生学号和姓名

SELECTstudent.sno,sname

/*③最后在student关系中取出sno和sname*/FROMstudentWHEREsnoIN(SELECTsno

/*②然后在score关系中找出选修了‘3-245'号课程的学生学号('103','105','109')*/FROMscoreWHEREcnoIN(SELECTcno

/*①首先在course关系中找出“操作系统”的课程号，结果为'3-245'*/FROMcourseWHEREcname='操作系统'))带有IN谓词的子查询（续）结果：带有IN谓词的子查询（续）用连接查询

SELECTstudent.sno,snameFROMstudent,score,courseWHEREstudent.sno=score.snoAND

o=oAND

ame='操作系统'二、带有比较运算符的子查询当能确切知道内层查询返回单值时，可用比较运算符（>，<，=，>=，<=，!=或<>）。与ANY或ALL谓词配合使用带有比较运算符的子查询（续）例：假设一个学生只可能在一个班学习，则在[例37]可以用=代替IN

：

SELECT*FROMstudentWHEREclass=(SELECTclassFROMstudentWHEREsname='王芳')三、带有ANY或ALL谓词的子查询ANY：任意一个ALL：所有带有ANY或ALL谓词的子查询（续）需要配合使用比较运算符>ANY 大于子查询结果中的某个值

>ALL 大于子查询结果中的所有值<ANY 小于子查询结果中的某个值<ALL 小于子查询结果中的所有值>=ANY 大于等于子查询结果中的某个值>=ALL 大于等于子查询结果中的所有值<=ANY 小于等于子查询结果中的某个值<=ALL 小于等于子查询结果中的所有值=ANY 等于子查询结果中的某个值=ALL 等于子查询结果中的所有值（没有实际意义）!=（或<>）ANY不等于子查询结果中的某个值（没有实际意义）!=（或<>）ALL不等于子查询结果中的任何一个值带有ANY或ALL谓词的子查询（续）[例39]查询其他班中比95031任意一个(其中某一个)学生后出生的学生姓名和出生日期

SELECTsname,sbirthdayFROMstudentWHEREsbirthday>ANY(SELECTsbirthdayFROMstudentWHEREclass='95031')

ANDclass<>'95031'

/*注意这是父查询块中的条件*/

带有ANY或ALL谓词的子查询（续）结果执行过程1.DBMS执行此查询时，首先处理子查询，找出‘95031’班中所有学生的出生日期，构成一个集合

(‘74-6-3’，‘75-10-2’,‘75-2-10’)。2.处理父查询，找所有不是’95031’且出生日期大于(‘74-6-3’，‘75-10-2’,‘75-2-10’)集合中任意一个（实际上只要大于‘74-6-3’）的学生。带有ANY或ALL谓词的子查询（续）ANY和ALL谓词有时可以用集函数实现ANY与ALL与集函数的对应关系

=

<>或!=

<

<=

>

>=ANY

IN

--

<MAX<=MAX>MIN>=MINALL

--

NOTIN

<MIN<=MIN>MAX>=MAX带有ANY或ALL谓词的子查询（续）用集函数实现子查询通常比直接用ANY或ALL查询效率要高，因为前者通常能够减少比较次数带有ANY或ALL谓词的子查询（续）[例39']：用集函数实现[例39]SELECTsname,sbirthdayFROMstudentWHEREsbirthday>(SELECTMIN(sbirthday)FROMstudentWHEREclass='95031')ANDclass<>'95031'带有ANY或ALL谓词的子查询（续）[例40]查询其他系中比95031班所有学生都后出生的学生姓名及出生日期。方法一：用ALL谓词

SELECTsname,sbirthdayFROMstudentWHEREsbirthday>ALL(SELECTsbirthdayFROMstudentWHEREclass='95031')ANDclass<>'95031'带有ANY或ALL谓词的子查询（续）

方法二：用集函数

SELECTsname,sbirthdayFROMstudentWHEREsbirthday>(SELECTMAX(sbirthday)FROMstudentWHEREclass='95031')ANDclass<>'95031'四、带有EXISTS谓词的子查询1.EXISTS谓词2.NOTEXISTS谓词3.不同形式的查询间的替换4.用EXISTS/NOTEXISTS实现全称量词5.用EXISTS/NOTEXISTS实现逻辑蕴函带有EXISTS谓词的子查询（续）1.EXISTS谓词存在量词

带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。若内层查询结果非空，则返回真值若内层查询结果为空，则返回假值由EXISTS引出的子查询，其目标列表达式通常都用*，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义2.NOTEXISTS谓词带有EXISTS谓词的子查询（续）[例41]查询所有选修了3-245号课程的学生姓名。用嵌套查询

SELECTsnameFROMstudentWHEREEXISTS(SELECT*FROMscore/*相关子查询*/WHEREsno=student.snoAND

cno='3-245')带有EXISTS谓词的子查询（续）

思路分析：本查询涉及student和score关系。在student中依次取每个元组的sno值，用此值去检查score关系。若score中存在这样的元组，其sno值等于此student.sno值，并且其cno='3-105'，则取此student.sname送入结果关系。带有EXISTS谓词的子查询（续）用连接运算SELECTsnameFROMstudent,scoreWHEREstudent.sno=score.snoAND

o='3-245'带有EXISTS谓词的子查询（续）[例42]查询没有选修3-245号课程的学生姓名。

SELECTsnameFROMstudentWHERENOTEXISTS(SELECT*FROMscoreWHEREsno=student.sno ANDcno='3-245')带有EXISTS谓词的子查询（续）3.不同形式的查询间的替换一些带EXISTS或NOTEXISTS谓词的子查询不能被其他形式的子查询等价替换所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。

带有EXISTS谓词的子查询（续）例：[例37]查询与“王芳”在同一个班学习的学生。可以用带EXISTS谓词的子查询替换：

SELECT*FROMstudentS1WHEREEXISTS(SELECT*FROMstudentS2WHERES2.class=S1.classANDS2.sname='王芳')带有EXISTS谓词的子查询（续）4.用EXISTS/NOTEXISTS实现全称量词(难点)SQL语言中没有全称量词

（Forall）可以把带有全称量词的谓词转换为等价的带有存在量词的谓词：

(

x)P≡

(

x(

P))带有EXISTS谓词的子查询（续）[例43]查询选修了全部课程的学生姓名。

SELECTsnameFROMstudentWHERENOTEXISTS(SELECT*FROMcourseWHERENOTEXISTS(SELECT*FROMscoreWHEREsno=student.snoANDcno=o))带有EXISTS谓词的子查询（续）

5.用EXISTS/NOTEXISTS实现逻辑蕴函(难点)SQL语言中没有蕴函(Implication)逻辑运算可以利用谓词演算将逻辑蕴函谓词等价转换为：

p

q≡

p∨q带有EXISTS谓词的子查