第三章SQL4数据查询 嵌套查询.pptx

数据库系统概论;主要内容;3.4 数据查询;3.4 数据查询 3.4.3嵌套查询;引例;? 子查询的限制;嵌套查询分类;引出子查询的谓词;一、带有IN谓词的子查询;[例40] 查询选修了课程名为“信息系统”的学生学号和姓;? 用连接查询;二、带有比较运算符的子查询;例：假设一个学生只可能在一个系学习，并且必须属于一个系，则在[例39]可以用 = 代替IN ： SELECT Sno,Sname,Sdept FROM Student WHERE Sdept = (SELECT Sdept FROM Student;刘晨 ) = Sdept ；;三、带有ANY(SOME) 或ALL谓词的子查询;需要配合使用比较运算符;[例42] 查询其他系中比计算机系任意一个(其中某一个)学生年;用集函数实现[例42] SELECT Sname, Sage;[例43] 查询其他系中比计算机系所有学生年龄都小 的学生姓名及年龄。 方法一：用ALL 谓词 SELECT Sname,Sage FROM Student WHERE Sage ALL(SELECT Sage FROM Student WHERE Sdept= CS) AND Sdept CS;方法二：用集函数 SELECT Sname,Sage FROM Student WHERE Sage (SELECT;? ANY 和ALL 谓词有时可以用集函数实现 ? ANY 与ALL 与集函数的对应关系;找出在1号课程的考试中，成绩最高的学生的学号和姓名。 SELECT sno,sname FROM student WHERE sno in (SELECT sno FROM sc WHERE cno=01 and grade= ( SELECT max(grade) FROM sc WHERE cno=01 ) );查询学生年龄平均值最大的院系 SELECT sdept FROM student GROUP BY sdept HAVING AVG(sage)=all(SELECT avg(sage) FROM student GROUP BY sdept );四、带有EXISTS谓词的子查询;[例44] 查询所有选修了1号课程的学生姓名。用嵌套查询 SELECT Sname FROM Student WHERE EXISTS;1. EXISTS谓词 –带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。 若内层查询结果非空，则返回真值 若内层查询结果为空，则返回假值 –由EXISTS引出的子查询，其目标列表达式通常都用 * ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义 2. NOT EXISTS谓词;? 相关子查询求解过程 ? 首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，然后根据内层查询结果集 是否空决定WHERE 子句的取值，若WHERE 子句为真，则取外层表的此元组放入结果表； ? 然后再取外层表的下一个元组； ? 重复这一过???，直至外层表全部检查完为止。;思路分析：;? 用连接运算 SELECT Sname FROM Student, SC WHERE Student.Sno=SC.Sno AND SC.Cno=‘1 ;;[例45] 查询没有选修1 号课程的学生姓名。 SELECT Sname FROM Student;［例］找出每个学生超过他选修课程平均成绩的课程号。 SELECT Sno ， Cno FROM SC x WHERE Grade =(SELECT AVG(Grade) FROM SC y WHERE y.Sno=x.Sno);;用EXISTS/NOT EXISTS 实现全称量词(难点); [例47] 查询至少选修了学生 200215122选修的全部课程的学生号码。用NOT EXISTS 谓词表示： SELECT Sno FROM Student WHERE NOT EXISTS (SELECT * FROM Course WHERE cno in(select cno from sc where sno=200215122 ) and NOT EXISTS (SELECT * FROM SC WHERE Sno= Student.Sno AND Cno= Course.Cno) );●;不同形式的查询间的替换 一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换。 所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。;内容回顾;作业：;2018年9月27日