L07_Oracle数据库编程_QUST.pdf

《L07_Oracle数据库编程_QUST.pdf》由会员分享，可在线阅读，更多相关《L07_Oracle数据库编程_QUST.pdf（87页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、数据操作语句数据查询（二）嵌套查询（子查询）概述 一个SELECT-FROM-WHERE语句称为一个查询块 将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询 包含子查询的语句称为主查询或外层查询 子查询也可以嵌套在另一个子查询中 为了与外层查询有所区别，总是把子查询写在圆括号中概述SELECTSnameFROMStudentWHERESno IN（SELECTSno FROMSCWHERECno=2）；外层查询/父查询 内层查询/子查询概述 子查询语句通常出现在外层查询的WHERE子句或HAVING子句中，与运算符一起构成查询条件 不相关子查询 子查询

2、条件不依赖于父查询 相关子查询 子查询条件依赖于父查询概述WHERE列名 NOTIN(子查询)WHERE列名 比较运算符(子查询)WHEREEXISTS(子查询)子查询的结果往往是一个集合，IN就是在这个集合中进行操作连接词IN测试元组是否是集合中成员子查询不能使用ORDERBY子句有些嵌套查询可以用连接运算替代示例求选修了课程名为J的学生学号和姓名SC：S#C#GS：S#SNSDSAC：C#CNPC#在C中找课程J的编号 在SC中找选修该课的学号 在S中找选修该课的学生姓名S1ACS20S2BCS21S3CMA19S4DCI19S5EMA20S6FCS22C1GC2HC1C3IC1C4JC2

3、C5KC4S1C1AS1C2AS1C3AS1C5BS2C1BS2C2CS2C4CS3C2BS3C3CS3C4BS4C3BS4C5DS5C2CS5C3BS5C5BS6C4AS6C5ASELECTS.S#,S.SNFROMSJOINSCONS.S#=SC.S#,JOINCONSC.C#=C.C#WHEREC.CN=“J”;分析：可用连接来实现，但最后结果只包含S中的字段，应该考虑更为有效、直观的方法：示例 在C中找课程J的编号 在SC中找选修该课的学号 在S中找选修该课的学生姓名SELECTC#FROMCWHERECN=J；SELECTS#FROMSCWHEREC#IN（C4）；SELECTS#，

4、SNFROMSWHERES#IN(S2,S3,S6)；C：C#CNPC#S1ACS20S2BCS21S3CMA19S4DCI19S5EMA20S6FCS22C1GC2HC1C3IC1C4JC2C5KC4S1C1AS1C2AS1C3AS1C5BS2C1BS2C2CS2C4CS3C2BS3C3CS3C4BS4C3BS4C5DS5C2CS5C3BS5C5BS6C4AS6C5AS：S#SNSDSA示例SELECTS#FROMSCWHEREC#IN(C4)；SELECTS#，SNFROMSWHERES#IN（S2,S3,S6）；最后的查询语句:SELECTC#FROMCWHERECN=J()();在C中

5、找课程J的编号 在SC中找选修该课的学号 在S中找选修该课的学生姓名SELECTC#FROMCWHERECN=J；SELECTS#FROMSCWHEREC#IN（C4）；SELECTS#，SNFROMSWHERES#IN(S2,S3,S6)；示例 查询与“刘晨”在同一个系学习的学生 第一步：确定“刘晨”所在系名SELECTSdeptFROMStudentWHERESname=刘晨；SdeptIS示例 查询与“刘晨”在同一个系学习的学生 第二步：查找所有在IS系学习的学生SELECTSno，Sname，SdeptFROMStudentWHERESdept=IS；SnoSnameSdept9500

6、1刘晨IS95004张立IS示例 查询与“刘晨”在同一个系学习的学生 第三步：将第一步查询嵌入到第二步查询的条件中 SELECTSno,Sname,Sdept FROMStudent WHERESdept IN(SELECTSdept FROMStudentWHERESname=刘晨)ANDSname!=刘晨;示例 查询考试成绩大于90分的学生的学号和姓名SELECTSno,Sname FROMStudentWHERESno IN(SELECTSno FROMSCWHEREGrade90)等价于SELECTSC.Sno,Sname FROMStudentJOINSCONStudent.Sno=

7、SC.Sno WHEREGrade90等价于等价于示例 查询计算机系选了“C002”课程的学生，列出姓名和性别SELECTSname,Ssex FROMStudentWHERESno IN(SELECTSno FROMSCWHERECno=C002)ANDSdept=计算机系 等价于SELECTSname,Ssex FROMStudentSJOINSCONS.Sno=SC.SnoWHERESdept=计算机系ANDCno=C002等价于等价于示例 查询选修了“VB”课程的学生的学号和姓名SELECTSno,Sname FROMStudentWHERESno IN(SELECTSno FROMS

8、CWHERECno IN(SELECTCno FROMCourseWHERECname=VB)嵌套查询 在选修了VB课程的这些学生中，统计他们的选课门数和平均成绩SELECTSno 学号,COUNT(*)选课门数,AVG(Grade)平均成绩FROMSCWHERESno IN(SELECTSno FROMSCJOINCourseCONC.Cno=SC.CnoWHERECname=VB)GROUPBYSno不能用连接形式实现不能用连接形式实现嵌套查询 查询选修了“VB”课程的学生的学号、姓名和VB成绩SELECTStudent.Sno,Sname,GradeFROMStudentJOINSCON

9、Student.Sno=SC.SnoJOINCourseONCourse.Cno=SC.CnoWHERECname=VB不能纯用子查询实现不能纯用子查询实现嵌套查询 嵌套查询由内向外处理 SQL允许多层嵌套 嵌套查询中最常用的谓词是IN 嵌套查询层次分明、容易理解比较测试 当能确切知道内层查询返回单值时，可用比较运算符（，=，=或）WHERE列名 比较运算符（子查询）要求子查询语句必须是返回单值的查询语句 只返回至多一个记录、一个字段的查询为单值子查询 与ANY或ALL谓词配合使用比较测试 查询选了“C004”号课程且成绩高于此课程的平均成绩的学生的学号和成绩SELECTSno,GradeFR

10、OMSCWHERECno=C004ANDGrade(SELECTAVG(Grade)FROMSCWHERECno=C004)比较测试 查询计算机系年龄最大的学生的姓名和年龄SELECTSname,SageFROMStudentWHERESdept=计算机系ANDSage=(SELECTMAX(Sage)FROMStudentWHERESdept=计算机系)比较测试 查询考试平均成绩高于全体学生的总平均成绩的学生的学号和平均成绩SELECTSno,AVG(Grade)平均成绩FROMSCGROUPBYSnoHAVING AVG(Grade)(SELECTAVG(Grade)FROMSC)比较测试

11、 查询没有选修“C001”号课程的学生姓名和所在系SELECTSname,Sdept FROMStudentWHERESno NOTIN(SELECTSno FROMSCWHERECno=C001)注意：不能用连接查询和在子查询中否定的形式实现比较测试 找出每个学生超过他选修课程平均成绩的课程号 先得到某个学生的平均成绩（SELECTAVG(Grade)/某学生平均成绩FROM选课表/成绩在SC表中WHERE某学生）比较测试 找出每个学生超过他选修课程平均成绩的课程号 嵌入父查询SELECTSno,Cno FROMSC scxWHEREGrade=(SELECTAVG(Grade)FROMSC

12、sczWHEREscz.Sno=scx.Sno)再论子查询ANY、SOME和ALL比较运算符 ANY|SOME|ALL（子查询）ANY、SOME 只要子查询中有一行能使结果为真，则结果为真 ANY和SOME在功能上是一样的 ALL 当子查询中所有行都使结果为真时，则结果为真ANY、SOME和ALL表达方法含义ANY（或=ANY）,SOME（或=SOME）大于（或等于）子查询结果中的某个值ALL 或=ALL大于（或等于）子查询结果中的所有值ANY（或=ANY）SOME（或=SOME）小于（或等于）子查询结果中的某个值ALL（或=ALL）小于（或等于）子查询结果中的所有值=ANY,=SOME等于子

13、查询结果中的某个值=ALL等于子查询结果中的所有值!=ANY（或ANY）!=SOME（或SOME）不等于子查询结果中的某个值!=ALL（或ALL）不等于子查询结果中的任何一个值ANY、SOME 查询其它系中比信息管理系某一学生年龄小的学生姓名和年龄SELECTSname,Sdept,SageFROMStudentWHERESageSOME(SELECTSageFROMStudentWHERESdept=信息管理系)ANDSdept!=信息管理系;等价于？ANY、SOME等价于：查询比信息管理系最大年龄小的其他系学生的姓名、所在系和年龄SELECTSname，SageFROMStudentWHE

14、RESage(SELECTMAX(Sage)FROMStudentWHERESdept=信息管理系)ANDSdept 信息管理系;用聚集函数实现子查询通常比直接用ANY或ALL查询效率要高ANY、SOME 查询至少获得一次成绩大于等于90的学生的姓名、所修的课程号和成绩SELECTSname,Cno,GradeFROMStudentSJOINSCONS.Sno=SC.SnoWHERES.Sno=SOME(SELECTSno FROMSCWHEREGrade=90);ANY、SOME 等价于：查询成绩大于等于90分的学生的姓名及所修的全部课程号和考试成绩SELECTSname,Cno,Grade

15、FROMStudentSJOINSCONS.Sno=SC.SnoWHERESC.Grade=90;SELECTSname,Cno,GradeFROMStudentSJOINSCONS.Sno=SC.SnoWHERESC.Sno IN(SELECTSC.Sno FROMSCWHERESC.Grade=90);ALL 查询比信息管理系所有学生的年龄都小的其他系学生的姓名及年龄SELECTSname,SageFROMStudentWHERESageALL(SELECTSageFROMStudentWHERESdept=信息管理系)ANDSdept!=信息管理系;ALL 等价于：查询其它系中年龄小于信

16、息管理系最小年龄的学生姓名和年龄SELECTSname,SageFROMStudentWHERESageSOME”意味着大于值中的任何一个，即大于最小的一个值“=SOME”与“IN”运算符功能相同“ALL”意味着大于所有的值，即大于最大值ANY、SOME与ALL与集函数的对应关系 表达式=SOME|ANY（子查询）表达式 IN（子查询）表达式=SOME|ANY（子查询）表达式=MIN（子查询）表达式=SOME|ANY（子查询）表达式=MAX（子查询）ANY、SOME与ALL与集函数的对应关系 表达式=ALL（子查询）表达式=MIN（子查询）表达式 ALL（子查询）表达式 NOTIN（子查询）表

17、达式=ALL（子查询）表达式=MAX（子查询）ANY、SOME与ALL与集函数的对应关系 ANY、SOME和ALL谓词有时可以用集函数实现=或或!=ANY/SOMEIN-MAXMIN=MINALL-NOT INMINMAX=MAXEXISTSWHERENOTEXISTS（子查询）EXISTS代表存在量词 不返回查询的数据，只产生逻辑真值和假值 EXISTS：当子查询中有满足条件的数据时，返回真值，否则返回假值 NOTEXISTS：当子查询中有满足条件的数据时，返回假值；否则返回真值EXISTS 查询至少一门不及格的学生姓名若内层查询结果非空，则为真否则为假相当于一个变量，根据它的值处理内层查询

18、,S中有多少个学号，内层查询就进行多少次称为相关子查询：查询条件依赖于外层查询中某个值等价于：SELECTS.SNFROMSJOINSCONS.S#=SC.S#WHERESC.G=D;SELECTSNFROMSWHEREEXISTS（SELECT*FROMSCWHERES#=S.S#ANDG=D）；EXISTS 查询选修了“C002”号课程的学生姓名SELECTSname FROMStudentWHEREEXISTS(SELECT*FROMSCWHERESC.Sno=Student.Sno ANDSC.Cno=C002)1.无条件执行外层查询语句，在外层查询的结果集中取第一行结果，得到Sno的

19、一个当前值2.将外层的Sno值作为已知值执行内层查询，如内层中有满足条件的记录，则返回True，否则返回False3.顺序处理外层Student表的第2、3、行数据EXISTS 带EXISTS谓词的查询是先外后内，即先执行外层查询，再执行内层查询 外层查询的值决定内层查询的结果 内层查询的执行次数由外层查询的结果决定 由EXISTS引出的子查询，其目标列表达式通常都用*因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义EXISTS 查询没有选修“C1”课程的学生姓名SELECT SNFROM SWHERE S#NOT IN（SELECT S#FROM SCWHERE C#=“C1”

20、);SELECT SNFROM SWHERE NOT EXISTS（SELECT*FROM SCWHERE S#=S.S#AND C#=“C1”);EXISTS 查询选修全部课程的学生信息 在课程关系中找到所有课程，即对课程表做投影运算cno（Course）在选课表中判断每一个学号所选修的课程，即对选课表做投影运算sno，cno（SC）关系代数表达式为 sno，cno（SC）cno（Course）EXISTS 查询选修全部课程的学生信息 SQL语言中没有全称量词 带全称量词的谓词 带存在量词的谓词(x)P(x)=(x)(P(x)等价于：输出这样的学生的学号，不存在某门课程，在他的选课记录里没有

21、选这门课（双重否定）EXISTS 查询选修全部课程的学生信息 如何证明或反驳所有的课程都被某个特定学生Student.Sno选了？找出反例，即有一门课程Course.Cno是Student.sno没有选的，这个“反例”表示为：SELECT*FROMCourseWHERENOT EXISTS(SELECT*FROMSCWHERESC.cno=Co ANDSC.sno=Student.sno)有这样一门课有这样一门课这门课Student.Sno没有选这门课Student.Sno没有选EXISTS 查询选修全部课程的学生信息 没有这样的课程Course.Cno，就能使前面的条件为真NOTEXISTS

22、(SELECT*FROMCourseWHERENOTEXISTS(SELECT*FROMSCWHERESC.cno=Co ANDSC.sno=Student.sno)不存在这样一个课程Co，它没有被Student.sno选不存在这样一个课程Co，它没有被Student.sno选EXISTS 查询选修全部课程的学生信息SELECT SNFROM SWHERE NOT EXISTSSELECT *FROM CWHERE NOT EXISTSSELECT *FROM SCWHERE S#=S.S#ANDC#=C.C#();要使该命题为真那么这一层查询结果应该为空这就要求这一句返回的都为假最终要求这一

23、层的查询结果都非空即所有的课程都选修EXISTS 查询选修全部课程的学生信息选修全部课课选修全部课课 (C#(学号为学号为S#的学生没修课程的学生没修课程C#)在在SC中不存在选课单中不存在选课单(S#,C#,)？在在SC中查选课单中查选课单(S#,C#,)SELECT SNFROM SWHERE NOT EXISTSSELECT *FROM CWHERE NOT EXISTSSELECT *FROM SCWHERE S#=S.S#ANDC#=C.C#();EXISTS 查询选修全部课程的学生信息SELECTStudent.*FROMStudentWHERENOTEXISTS(SELECT*F

24、ROMCourseWHERENOTEXISTS(SELECT*FROMSCWHERESC.Cno=Course.CnoANDSC.Sno=Student.Sno)EXISTS如果查询要求检索的对象集合是必须符合某个带有“所有”这类关键词的条件，可按如下步骤执行：为要检索的对象命名并用文字表述要检索的候选对象的一个反例（在反例中，在前面提到的“所有”对象中至少有一个对象不符合规定的条件）建立SELECT语句的搜索条件以表达步骤1所创建的反例 建立包含步骤所创建的语句的搜索条件，说明不存在上面定义的那种反例 用步骤3的搜索条件来建立最终的SELECT语句EXISTS 查询至少选修学生821102选

25、修的全部课程的学生学号 找到学号821102学生选修的课程Cno（Sno821102（SC）在SC表中针对每一个学号，找选修的课程sno，cno（SC）若中学号的象集Cno包含了中的Cno，则中的Sno包含在结果中，即除法运算sno，cno（SC）cno（Sno821102（SC）EXISTS 查询至少选修学生821102选修的全部课程的学生学号：构造反例 有一个“0811102”学生选的课程是?.Sno没有选的 我们把该学生命名为?.Sno 这里的“?”表示表并不固定是Student表还是SC表，以保持范围变量的灵活性EXISTS 查询至少选修学生821102选修的全部课程的学生学号：将步骤

26、构造的反例表达为搜索条件Select*FROMCourseWHERESC.Sno=0811102 andnotexists(select*fromSCo=Course.Cnoandx.Sno=?.Sno)EXISTS查询至少选修学生821102选修的全部课程的学生学号：建立表示这类反例不存在的搜索条件notexists(select*fromCoursejoinSConCourse.Cno=SC.CnowhereSC.Sno=0811102andnotexists(select*fromSCxwherex.Cno=Course.Cnoandx.Sno=?.Sno)EXISTS查询至少选修学生8

27、21102选修的全部课程的学生学号：建立完整的SELECT语句SELECTDISTINCTSC.Sno FROMSCWHERENOTEXISTS(SELECT*FROMCourseWHERESC.Sno 821102ANDNOTEXISTS(SELECT*FROMSCXWHERESC.SnoX.Sno ANDX.Cno=Course.Cno)ANDSC.Sno!=0811102;/*去掉本人*/集合查询集合查询 SELECT语句的查询结果是元组的集合，所以多个SELECT语句的结果可进行集合操作 标准SQL直接支持的集合操作种类 并操作(UNION)一般商用数据库支持的集合操作种类 并操作(U

28、NION)交操作(INTERSECT)差操作(MINUS)并运算 并运算可将两个或多个查询语句的结果集合并为一个结果集，这个运算可以使用 UNION 运算符实现 UNION是一个特殊的运算符，通过它可以实现让两个或更多的查询产生单一的结果集并运算SELECT语句1UNIONALLSELECT语句nALL表示在结果集中包含所有查询语句产生的全部记录，包括重复的记录如果没有指定ALL，则系统默认是删除合并后结果集中的重复记录并运算 UNION操作一般用在要从不同的表中查询语义相同的列，并合并查询结果的情况 所有的SELECT语句列表中列的个数必须相同，而且对应列的语义应该相同 各SELECT语句中

29、每个列的数据类型必须兼容 合并后的结果采用第一个SELECT语句的列标题 如果要对查询的结果进行排序，则ORDERBY子句写在最后一个查询语句之后并运算再选择再选择C4课程成绩为课程成绩为A的学号姓名的学号姓名先选择先选择C1课程成绩为课程成绩为A的学号姓名的学号姓名SELECT S.S#,S.SNFROM S,SCWHERE S.S#=SC.S#ANDSC.C#=C1ANDSC.G=AUNIONSELECT S.S#,S.SNFROM S，SCWHERE S.S#=SC.S#ANDSC.C#=C4 ANDSC.G=A;查询结果S#SN查询结果S#SNS1 AS2 BS6 FS1 AS2 BS

30、6 F求求C1和和C4课程成绩为课程成绩为A的学生的学号及姓名的学生的学号及姓名S#SN SD SAS#SN SD SAS1 ACS 17S2 BCS 21S3 CMA 19S4 DCI 17S5 EMA 20S6 FCS 20S9 ZIS 18S#C#GS1 ACS 17S2 BCS 21S3 CMA 19S4 DCI 17S5 EMA 20S6 FCS 20S9 ZIS 18S#C#GS1 C1 AS1 C3 AS1 C5 BS2 C1 AS2 C2 CS2 C4 AS3 C3S3 C4S4 C3 BS4 C5 DS5 C2S5 C3S5 C5S6 C4 AS1 C1 AS1 C3 AS1

31、 C5 BS2 C1 AS2 C2 CS2 C4 AS3 C3S3 C4S4 C3 BS4 C5 DS5 C2S5 C3S5 C5S6 C4 AS：SC：参加参加UNION操作的各结果表的列数、对应项的数据类型必须相同操作的各结果表的列数、对应项的数据类型必须相同并运算等价于：等价于：SELECT S.S#,S.SNFROM S,SCWHERE(S.S#=SC.S#ANDSC.C#=C1 ANDSC.G=A)OR(S.S#=SC.S#ANDSC.C#=C4 ANDSC.G=A);S#SN SD SAS#SN SD SAS1 ACS 17S2 BCS 21S3 CMA 19S4 DCI 17S5

32、 EMA 20S6 FCS 20S9 ZIS 18S#C#GS1 ACS 17S2 BCS 21S3 CMA 19S4 DCI 17S5 EMA 20S6 FCS 20S9 ZIS 18S#C#GS1 C1 AS1 C3 AS1 C5 BS2 C1 BS2 C2 CS2 C4 CS3 C3 0S3 C4 0S4 C3 BS4 C5 DS5 C2 0S5 C3 0S5 C5 0S6 C4 AS1 C1 AS1 C3 AS1 C5 BS2 C1 BS2 C2 CS2 C4 CS3 C3 0S3 C4 0S4 C3 BS4 C5 DS5 C2 0S5 C3 0S5 C5 0S6 C4 AS：SC：查

33、询结果S#SN查询结果S#SNS1 AS2 BS2 BS6 FS1 AS2 BS2 BS6 F有重复的行有重复的行DISTINCT S.S#,S.SN求求C1和和C4课程成绩为课程成绩为A的学生的学号及姓名的学生的学号及姓名WHERE S.S#=SC.S#AND SC.G=A AND(SC.C#=C1OR SC.C#=C4);WHERE S.S#=SC.S#AND SC.G=A AND SC.C#IN(C1,C4);并运算 查询计算机科学系的学生及年龄不大于19岁的学生SELECT*FROMStudentWHERESdept=CSUNIONSELECT*FROMStudentWHERESage

34、=19；或SELECTDISTINCT*FROMStudentWHERESdept=CSOR Sage=19；并运算将对计算机系学生的查询结果与信息管理系学生的查询结果合并为一个结果集，将查询结果按年龄从小到大的顺序排序SELECTSno 学号,Sname 姓名,Sage年龄,Sdept 所在系FROMStudentWHERESdept=计算机系UNIONSELECTSno,Sname,Sage,Sdept FROMStudentWHERESdept=信息管理系ORDERBYSageASC交运算 返回同时在两个集合中出现的记录SELECT语句1INTERSECTSELECT语句2INTERSE

35、CTSELECT语句n交运算 查询计算机科学系的学生与年龄不大于19岁的学生的交集SELECT*FROMStudentWHERESdept=CSINTERSECTSELECT*FROMStudentWHERESage=19；或SELECT*FROMStudentWHERESdept=CSAND Sage=90 THEN 好 WHEN AVG(Grade)BETWEEN 80 AND 89 THEN 比较好 WHEN AVG(Grade)BETWEEN 70 AND 79 THEN 一般WHEN AVG(Grade)BETWEEN 60 AND 69 THEN 不太好小结小结 当查询语句的目标列

36、中包含聚合函数时 若没有分组子句，则目标列中只能写聚合函数，而不能再写其他列名 若包含分组子句，则在查询的目标列中除了可以写聚合函数外，只能写分组依据列 对行的过滤条件一般用WHERE子句实现，对组的过滤条件用HAVING子句实现小结 不能将对统计后的结果进行筛选的条件写在WHERE子句中，应该写在HAVING子句中 例如：查询平均年龄大于20的系 WHEREAVG(Sage)20 HAVINGAVG(Sage)20X小结 不能将列值与统计结果值进行比较的条件写在WHERE子句中，这种条件一般都用子查询来实现 例：查询年龄大于平均年龄的学生 WHERESageAVG(Sage)WHERESage(SELECTAVG(Sage)FROMStudent)X小结 当查询目标列来自多个表时，必须用多表连接实现 外层查询的列可以用在子查询中，但子查询语句中的列不能用在外层查询中 使用自连接时，或嵌套查询外层与内层用同一张表时，必须为表取别名，使其在逻辑上成为两张表小结 带否定条件的查询一般用子查询实现NOTIN或（NOTEIXSTS），不用多表连接实现 一些带EXISTS或NOTEXISTS谓词的子查询不能被其他形式的子查询等价替换 所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换结束