数据库原理-嵌套查询

1、数据库原理及应用数据库原理及应用 第三章第三章 关系数据库标准语言关系数据库标准语言SQL 3.1 SQL概述概述 3.2 数据定义数据定义 3.3 查询查询 3.4 数据更新数据更新 3.5 视图视图 3.3 查查 询询 l3.3.1 概述概述 l3.3.2 单表查询单表查询 l3.3.3 连接查询连接查询 l3.3.4 嵌套查询嵌套查询 l3.3.5 集合查询集合查询 l3.3.6 小结小结 l3.3.4 嵌套查询嵌套查询 3.3.4 嵌套查询嵌套查询 l嵌套查询概述嵌套查询概述 l嵌套查询分类嵌套查询分类 l嵌套查询求解方法嵌套查询求解方法 l引出子查询的谓词引出子查询的谓词 3.3.4

2、 嵌套查询嵌套查询 【例例1】查询选修了查询选修了2号课程的学生的姓名号课程的学生的姓名 SELECT Sname FROM Student,SC WHERE 方法方法1 1：复合条件查询：复合条件查询 Student.Sno=SC.Sno and Cno= 2 3.3.4 嵌套查询嵌套查询 【例例1】查询选修了查询选修了2号课程的学生的姓名号课程的学生的姓名 方法方法2 2：换个角度想问题：换个角度想问题 思路：从思路：从StudentStudent表中查询学生表中查询学生 的姓名，这个学生应该的姓名，这个学生应该是选修了是选修了 2 2号课程的学生。号课程的学生。 思路：从思路：从Stud

3、entStudent表中查询学生表中查询学生 的姓名，这个学生应该的姓名，这个学生应该在选修了在选修了 2 2号课程的学生集合里。号课程的学生集合里。 3.3.4 嵌套查询嵌套查询 【例例1】查询选修了查询选修了2号课程的学生的姓名号课程的学生的姓名 方法方法2 2：换个角度想问题：换个角度想问题 （SELECT Sno FROM SC WHERE Cno= 2 ）；）； 外层查询外层查询/父查询父查询 内层查询内层查询/子查询子查询 SELECT Sname FROM Student WHERE SnoIN 3.3.4 嵌套查询嵌套查询 一、嵌套查询概述一、嵌套查询概述 一个一个SELECT

4、-FROM-WHERE语句称语句称 为一个查询块为一个查询块 将一个查询块嵌套在另一个查询块的将一个查询块嵌套在另一个查询块的 WHERE子句或子句或HAVING短语的条件短语的条件 中的查询称为嵌套查询中的查询称为嵌套查询 3.3.4 嵌套查询嵌套查询 子查询的限制子查询的限制 不能使用不能使用ORDER BY子句子句 层层嵌套方式反映了层层嵌套方式反映了 SQL语言的结语言的结 构化构化 有些嵌套查询可以用连接运算替代有些嵌套查询可以用连接运算替代 不相关子查询不相关子查询 子子查询的查询条件查询的查询条件不依赖于父不依赖于父查询查询 相关子查询相关子查询 子查询的查询条件子查询的查询条件

5、依赖依赖于父查询于父查询 二、嵌套查询分类二、嵌套查询分类 3.3.4 嵌套查询嵌套查询 三、嵌套查询求解方法三、嵌套查询求解方法 l不相关子查询不相关子查询 是由里向外逐层处理。即每个子查询在是由里向外逐层处理。即每个子查询在 上一级查询处理之前求解，子查询的结果上一级查询处理之前求解，子查询的结果 用于建立其父查询的查找条件。用于建立其父查询的查找条件。 3.3.4 嵌套查询嵌套查询 l相关子查询相关子查询 首先取外层查询中表的第一个元组，根据它与首先取外层查询中表的第一个元组，根据它与 内层查询相关的属性值处理内层查询，若内层查询相关的属性值处理内层查询，若 WHERE子句返回值为真，则

6、取此元组放入结子句返回值为真，则取此元组放入结 果表；果表； 然后再取外层表的下一个元组；然后再取外层表的下一个元组； 重复这一过程，直至外层表全部检查完为止。重复这一过程，直至外层表全部检查完为止。 3.3.4 嵌套查询嵌套查询 三、嵌套查询求解方法（续）三、嵌套查询求解方法（续） 四、引出子查询的谓词四、引出子查询的谓词 l带有带有ININ谓词的子查询谓词的子查询 l带有比较运算符的子查询带有比较运算符的子查询 l带有带有ANYANY或或ALLALL谓词的子查询谓词的子查询 l带有带有EXISTSEXISTS谓词的子查询谓词的子查询 3.3.4 嵌套查询嵌套查询 （1）带有）带有IN谓词的

7、子查询（续）谓词的子查询（续） 【例例2 2】查询选修了课程名为查询选修了课程名为“信息系统信息系统” 的学生学号和姓名的学生学号和姓名 首先在首先在Course关系中找出关系中找出“信信 息系统息系统”的课程号，结果为的课程号，结果为3号号 然后在然后在SC关系中找出选关系中找出选 修了修了3号课程的学生学号号课程的学生学号 最后在最后在Student关系中关系中 取出取出Sno和和Sname (SELECT Cno FROM Course WHERE Cname= 信息系统信息系统); (SELECT Sno FROM SC WHERE Cno IN SELECT Sno，Sname FR

8、OM Student WHERE Sno IN IN可由可由=代替代替 （2）带有比较运算符的子查询）带有比较运算符的子查询 当能确切知道内层查询返回单值时，可当能确切知道内层查询返回单值时，可 用比较运算符（用比较运算符（，=，=，!= 或或）。）。 l与与ANY或或ALL谓词配合使用谓词配合使用 某一个某一个全部全部 （3）ANY或或ALL谓词的子查询谓词的子查询 【例例3】 查询其他系中比信息系任意一个查询其他系中比信息系任意一个(某一某一 个个)学生年龄小的学生姓名和年龄学生年龄小的学生姓名和年龄 SELECT Sname，Sage FROM Student WHERE Sage AN

9、Y (SELECT Sage FROM Student WHERE Sdept= IS ) AND Sdept IS ; /* 注意这是父查询块中的条件注意这是父查询块中的条件 */ （3）ANY或或ALL谓词的子查询谓词的子查询 ANY和和ALL谓词有时可以用集函数实现谓词有时可以用集函数实现 ANY与与ALL与集函数的对应关系与集函数的对应关系 = 或或!= = ANY IN - MAXMIN= MIN ALL - NOT IN MIN MAX = MAX 用集函数实现子查询通常比直接用用集函数实现子查询通常比直接用ANY或或ALL 查询效率要高，因为前者通常能够减少比较次数查询效率要高，

10、因为前者通常能够减少比较次数 IN （SELECT Sno FROM SC WHERE Cno= 2 ）；）； SELECT Sname FROM Student WHERE Sno SELECT Sname FROM Student WHERE EXISTS (SELECT (SELECT * * FROM SC / FROM SC /* *相关子查询相关子查询* */ / WHERE Sno= WHERE Sno=Student.SnoStudent.Sno AND Cno= 2 ) AND Cno= 2 )； 1、什么是嵌套查询、什么是嵌套查询 2、嵌套查询分类、嵌套查询分类 不相关子查

11、询不相关子查询 相关子查询相关子查询 3、嵌套查询执行方法、嵌套查询执行方法 3.4.3 嵌套查询嵌套查询 4、引出子查询的谓词、引出子查询的谓词 （4）带有）带有EXISTS谓词的子查询谓词的子查询 1. EXISTS谓词谓词 2. NOT EXISTS谓词谓词 3. 不同形式的查询间的替换不同形式的查询间的替换 4. 用用EXISTS/NOT EXISTS实现全称量词实现全称量词 5. 用用EXISTS/NOT EXISTS实现逻辑蕴函实现逻辑蕴函 （4）带有）带有EXISTS谓词的子查询谓词的子查询 1. EXISTS谓词谓词 存在量词存在量词 带有带有EXISTS谓词的子查询不返回任何

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

13、生姓名号课程的学生姓名 SELECT SnameSELECT Sname FROM FROM StudentStudent WHERE WHERE EXISTSEXISTS (SELECT (SELECT * * FROM SC FROM SC WHERE WHERE 思路分析：思路分析： 查询查询Student中这中这 样的学生姓名，样的学生姓名， 只要在只要在SC表中存表中存 在该学生选修在该学生选修1号号 课程的记录。课程的记录。 Sno=Student.Sno AND Cno= 1 )； （4）带有）带有EXISTS谓词的子查询谓词的子查询 【例例1 1】查询所有选修了查询所有选修了2

14、 2号课程的学生姓名号课程的学生姓名 SELECT Sname SELECT Sname FROM Student, SCFROM Student, SC WHERE WHERE Student.Sno=SC.SnoStudent.Sno=SC.Sno ANDAND SC.Cno= 1SC.Cno= 1; ; 等值连接实现：等值连接实现： （4）带有）带有EXISTS谓词的子查询谓词的子查询 SELECT SnameSELECT Sname FROM FROM StudentStudent WHEREWHERE (SELECT (SELECT * * FROM SC FROM SC WHERE

15、 Sno = WHERE Sno = Student.Student.Sno Sno AND Cno=1) AND Cno=1)； 连接运算的实现连接运算的实现? NOT EXISTSNOT EXISTS 【例例4 4】查询没有选修查询没有选修2 2号课程的学生姓名号课程的学生姓名 思路分析：思路分析： 查询查询Student中这中这 样的学生姓名，样的学生姓名， 只要在只要在SC表中不表中不 存在该学生选修存在该学生选修1 号课程的记录。号课程的记录。 （4）带有）带有EXISTS谓词的子查询谓词的子查询 SELECT Sname SELECT Sname FROM Student, SCF

16、ROM Student, SC WHERE Student.Sno=SC.Sno WHERE Student.Sno=SC.Sno AND SC.Cno AND SC.Cno 1; 1; 【例例4 4】查询没有选修查询没有选修2 2号课程的学生姓名号课程的学生姓名 Student表表 CS CS MA IS 20 19 18 19 男男 女女 女女 男男 李勇李勇 刘晨刘晨 王敏王敏 张立张立 200215121 200215122 200215123 200515125 Sdept SageSsexSnameSno SC表表 92 85 88 90 80 1 2 3 2 3 20021512

17、1 200215121 200215121 200215122 200215122 Grade CnoSno SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno = Student.Sno AND Cno=1)； 结果表结果表 Sname 刘晨刘晨 王敏王敏 张立张立 Student表表 SC表表 92 85 88 90 80 1 2 3 2 3 200215121 200215121 200215121 200215122 200215122 Grade CnoSno SELECT Sname SELE

18、CT Sname FROM Student, SCFROM Student, SC WHERE WHERE Student.Sno=SC.Sno Student.Sno=SC.Sno AND SC.Cno AND SC.Cno 1; 1; CS CS MA IS 20 19 18 19 男男 女女 女女 男男 李勇李勇 刘晨刘晨 王敏王敏 张立张立 200215121 200215122 200215123 200515125 Sdept SageSsexSnameSno 结果表结果表 Sname 李勇李勇 注意：约束的注意：约束的 是单个元组是单个元组 （4）带有）带有EXISTS谓词的子查

19、询谓词的子查询 3. 3. 不同形式的查询间的替换不同形式的查询间的替换 所有所有带带ININ谓词、比较运算符、谓词、比较运算符、ANYANY和和ALLALL 谓词的子查询都能用带谓词的子查询都能用带EXISTSEXISTS谓词的谓词的 子查询等价替换。子查询等价替换。 一些一些带带EXISTSEXISTS或或NOT EXISTSNOT EXISTS谓词的子查谓词的子查 询不能被其他形式的子查询等价替换询不能被其他形式的子查询等价替换 （4）带有）带有EXISTS谓词的子查询谓词的子查询 4.4.用用EXISTSEXISTS（ ）/NOT EXISTS/NOT EXISTS实现全称量词（实现全

20、称量词（ ） 谓词：谓词： P(x1,x2,xn)被称为被称为n元谓词。其中元谓词。其中P是谓词，代表一个是谓词，代表一个 确定的确定的特征特征或或关系关系(名名)。x1,x2,xn称为谓词的参量，一称为谓词的参量，一 般表示个体。般表示个体。 Like（I，Music） 例如：例如： “我喜欢音乐我喜欢音乐” （4）带有）带有EXISTS谓词的子查询谓词的子查询 4.4.用用EXISTSEXISTS（ ）/NOT EXISTS/NOT EXISTS实现全称量词（实现全称量词（ ） 如果谓词公式如果谓词公式P(x)P(x)对于对于所有可能变量所有可能变量 x x都具有都具有 值，则用值，则用 ( (x)P(x)x)P(x)表示。表示。 对于谓词公式对于谓词公式P(x)P(x)，至少，至少有一个有一个变元变元 X X可使可使 P(x )P(x )为为T T值，则用值，则用 ( (x)P(x)x)P(x)表示。表示。 （4）带有）带有EXISTS谓词的子查询谓词的子查询 l( x)P ( x( P) ( ( x)Px)P： 例如：表示谓词例如：表示谓词“学生选修了课