AnIntroductiontoDatabaseSystem中国人民大学信息学院3.4数据查询3.4.1单表查询3
.4.2连接查询3.4.3嵌套查询3.4.4集合查询3.4.5Select语句的一般形式3.4.2连接查询
连接查询：同时涉及多个表的查询连接条件或连接谓词：用来连接两个表的条件 一般格式：[<表名1>.]<列名1><比较
运算符>[<表名2>.]<列名2>[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2
>.]<列名3>连接字段：连接谓词中的列名称连接条件中的各连接字段类型必须是可比的，但名字不必是相同的连接操作的执行过程嵌
套循环法(NESTED-LOOP)首先在表1中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接件的元组，找到后就将表1中的
第一个元组与该元组拼接起来，形成结果表中一个元组。表2全部查找完后，再找表1中第二个元组，然后再从头开始扫描表2，逐一查找满足连
接条件的元组，找到后就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组。重复上述操作，直到表1中的全部元组都处理完毕
排序合并法(SORT-MERGE)常用于=连接首先按连接属性对表1和表2排序对表1的第一个元组，从头开始扫描表2，顺序查找
满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。当遇到表2中第一条大于表1连接字段值的元组
时，对表2的查询不再继续排序合并法找到表1的第二条元组，然后从刚才的中断点处继续顺序扫描表2，查找满足连接条件的元组，找到后就
将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。直接遇到表2中大于表1连接字段值的元组时，对表2的查询不再继续重复上
述操作，直到表1或表2中的全部元组都处理完毕为止索引连接(INDEX-JOIN)对表2按连接字段建立索引对表1中的每个元组
，依次根据其连接字段值查询表2的索引，从中找到满足条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组
连接查询（续）一、等值与非等值连接查询二、自身连接三、外连接四、复合条件连接一、等值与非等值连接查询等值连接
：连接运算符为=[例33]查询每个学生及其选修课程的情况 SELECTStudent.，SC.
FROMStudent，SC WHEREStudent.Sno=SC.Sno；
等值与非等值连接查询（续）等值与非等值连接查询（续）自然连接：[例34]对[例33]用自然连接完成。SELE
CTStudent.Sno，Sname，Ssex，Sage，Sdept，Cno，GradeFROMStudent
，SCWHEREStudent.Sno=SC.Sno；连接查询（续）一、等值与非等值连接查询二、自身连接三
、外连接四、复合条件连接二、自身连接自身连接：一个表与其自己进行连接需要给表起别名以示区别由于所有属性名都是同名属性
，因此必须使用别名前缀[例35]查询每一门课的间接先修课（即先修课的先修课）SELECTFIRST.Cno，SE
COND.CpnoFROMCourseFIRST，CourseSECONDWHEREFIRS
T.Cpno=SECOND.Cno；自身连接（续）FIRST表（Course表）自身连接（续）SEC
OND表（Course表）自身连接（续）查询结果：连接查询（续）一、等值与非等值连接查询二、自身连接三、外连接
四、复合条件连接三、外连接外连接与普通连接的区别普通连接操作只输出满足连接条件的元组外连接操作以指定表为连接主体，将主体
表中不满足连接条件的元组一并输出[例36]改写[例33]SELECTStudent.Sno，Sname，Ssex，
Sage，Sdept，Cno，GradeFROMStudentLEFTOUTJOINSCON(Stud
ent.Sno=SC.Sno)；外连接（续）执行结果：外连接（续）左外连接列出左边关系（如本例Stu
dent）中所有的元组右外连接列出右边关系中所有的元组连接查询（续）一、等值与非等值连接查询二、自身连接三、
外连接四、复合条件连接四、复合条件连接复合条件连接：WHERE子句中含多个连接条件[例37]查询选修2号课程且成绩在9
0分以上的所有学生 SELECTStudent.Sno,Sname FROMStudent,SC W
HEREStudent.Sno=SC.SnoAND
/连接谓词/SC.Cno=‘2’ANDSC
.Grade>90； /其他限定条件/复合条件连接（续）[例38]查询每个学生的学号、姓名
、选修的课程名及成绩SELECTStudent.Sno，Sname，Cname，GradeFROMStud
ent，SC，Course/多表连接/WHEREStudent.Sno=SC.Sno
andSC.Cno=Course.Cno；?3.4数据查询3.4.1单表查询3.4.2
连接查询3.4.3嵌套查询3.4.4集合查询3.4.5Select语句的一般形式嵌套查询(续)嵌套查询概述
一个SELECT-FROM-WHERE语句称为一个查询块将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中
的查询称为嵌套查询嵌套查询(续)SELECTSname
/外层查询/父查询/FROMStudentWHERESnoIN
（SELECTSno/内层查询/子查询/
FROMSCWHERECno=''2''）；嵌
套查询(续)子查询的限制不能使用ORDERBY子句层层嵌套方式反映了SQL语言的结构化有些嵌套查询可以用连接运算替代
嵌套查询求解方法不相关子查询：子查询的查询条件不依赖于父查询由里向外逐层处理。即每个子查询在上一级查询处理之前求解
，子查询的结果用于建立其父查询的查找条件。嵌套查询求解方法（续）相关子查询：子查询的查询条件依赖于父查询首先取外层查询中表
的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表然后再取外层表的下一个元
组重复这一过程，直至外层表全部检查完为止3.4.3嵌套查询一、带有IN谓词的子查询二、带有比较运算符的子查
询三、带有ANY（SOME）或ALL谓词的子查询四、带有EXISTS谓词的子查询一、带有IN谓词的子查询[例
39]查询与“刘晨”在同一个系学习的学生。此查询要求可以分步来完成①确定“刘晨”所在系名
SELECTSdeptFROMStudent
WHERESname=''刘晨''； 结果为：
CS带有IN谓词的子查询（续）②查找所有在IS系学习的学生。SELECTSno，Sname，
SdeptFROMStudentWHE
RESdept=''CS''；结果为：带有IN谓词的子查询（续）将第一步查询嵌入到第二步查询的条件中SEL
ECTSno，Sname，Sdept FROMStudent WHERESdeptIN
(SELECTSdeptFROMStudent
WHERESname=‘刘晨’)；此查询为不相关子查询。带有IN谓词的子查询（续）用
自身连接完成[例39]查询要求SELECTS1.Sno，S1.Sname，S1.SdeptFROM
StudentS1，StudentS2WHERES1.Sdept=S2.SdeptAND
S2.Sname=''刘晨''；带有IN谓词的子查询（续）[例40]查询选修了课程
名为“信息系统”的学生学号和姓名 SELECTSno，Sname③最后在St
udent关系中 FROMStudent取
出Sno和Sname WHERESnoIN(SELECTSno
②然后在SC关系中找出选FROMSC
修了3号课程的学生学号WHERECnoIN
(SELECTCno①首先在Cours
e关系中找出FROMCourse“信
息系统”的课程号，为3号WHERECname=‘信息系统’
));带有IN谓词的子查询（续）用连接查询实现[例40]SE
LECTSno，SnameFROMStudent，SC，CourseWHEREStuden
t.Sno=SC.SnoANDSC.Cno=Course.CnoAND
Course.Cname=‘信息系统’；3.4.3嵌套查询一、带有IN谓词的
子查询二、带有比较运算符的子查询三、带有ANY（SOME）或ALL谓词的子查询四、带有EXISTS谓词的子查询二
、带有比较运算符的子查询当能确切知道内层查询返回单值时，可用比较运算符（>，<，=，>=，<=，!=或<>）。与ANY或A
LL谓词配合使用带有比较运算符的子查询（续）例：假设一个学生只可能在一个系学习，并且必须属于一个系，则在[例39]可以用=
代替IN：SELECTSno，Sname，SdeptFROMStudentWHE
RESdept=(SELECTSdept
FROMStudentWHERESname=‘刘晨’)；带有比较运算符的
子查询（续）子查询一定要跟在比较符之后错误的例子：SELECTSno，Sname，Sdept
FROMStudentWHERE(SELECTSdept
FROMStudentWHERESname=‘刘晨’)
=Sdept；带有比较运算符的子查询（续）［例41］找出每个学生超过他选修课程平均成
绩的课程号。SELECTSno，CnoFROMSCxWHEREGrade>=(SELE
CTAVG(Grade) FROMSCy
WHEREy.Sno=x.Sno);带有比较运算符的子查询（续）可能的执行过程：
1.从外层查询中取出SC的一个元组x，将元组x的Sno值（200215121）传送给内层查询。SELECT
AVG(Grade)FROMSCyWHEREy.Sno=''200215121'';2.执行
内层查询，得到值88（近似值），用该值代替内层查询，得到外层查询：SELECTSno，CnoF
ROMSCxWHEREGrade>=88；带有比较运算符的子查询（续）3.执行这个查询，得到
（200215121，1）（200215121，3）4.外层查询取出下一个元组重复做上述1至3步骤，直到外层的
SC元组全部处理完毕。结果为:（200215121，1）（200215121，3）（20021512
2，2）3.4.3嵌套查询一、带有IN谓词的子查询二、带有比较运算符的子查询三、带有ANY（SOM
E）或ALL谓词的子查询四、带有EXISTS谓词的子查询三、带有ANY（SOME）或ALL谓词的子查询谓词语义AN
Y：任意一个值ALL：所有值带有ANY（SOME）或ALL谓词的子查询（续）需要配合使用比较运算符>ANY 大于
子查询结果中的某个值>ALL 大于子查询结果中的所有值L 小于子查询结果中的所有值>=ANY 大于等于子查询结果中的某个值>=ALL 大于等于子查询结果中的所有值<=
ANY 小于等于子查询结果中的某个值<=ALL 小于等于子查询结果中的所有值=ANY 等于子查询结果中的某个值
=ALL 等于子查询结果中的所有值（通常没有实际意义）!=（或<>）ANY 不等于子查询结果中的某个值!=（或
<>）ALL 不等于子查询结果中的任何一个值带有ANY（SOME）或ALL谓词的子查询（续）[例42]查询其他系中比计算
机科学某一学生年龄小的学生姓名和年龄SELECTSname，SageFROMStudent
WHERESageFROMStudentW
HERESdept=''CS'')ANDSdept<>‘CS'';/父
查询块中的条件/带有ANY（SOME）或ALL谓词的子查询（续）结果： 执行过程：1.RDBMS执行此
查询时，首先处理子查询，找出CS系中所有学生的年龄，构成一个集合(20，19)2.处理父查询，找所有不是C
S系且年龄小于20或19的学生带有ANY（SOME）或ALL谓词的子查询（续）用聚集函数实现[例42
]SELECTSname，SageFROMStudentWHERESage<
(SELECTMAX(Sage)
FROMStudentWHERESd
ept=‘CS'')ANDSdept<>''CS’;带有ANY（SOME）或ALL谓词的子查询
（续）[例43]查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄。方法一：用ALL谓词SELECT
Sname，SageFROMStudentWHERESage(SELECTSageFROMStudent
WHERESdept=''CS'')AND
Sdept<>''CS’;带有ANY（SOME）或ALL谓词的子查询（续）方法二：用聚集函数
SELECTSname，SageFROMStudentWHERESage<
(SELECTMIN(Sage)
FROMStudentWHERE
Sdept=''CS'')ANDSdept<>''CS’;带有ANY（SOME）或ALL
谓词的子查询（续）表3.5ANY（或SOME），ALL谓词与聚集函数、IN谓词的等价转换关系3.4.3嵌套查询
一、带有IN谓词的子查询二、带有比较运算符的子查询三、带有ANY（SOME）或ALL谓词的子查询四、带有
EXISTS谓词的子查询带有EXISTS谓词的子查询(续）1.EXISTS谓词存在量词?带有EXISTS谓词的子查询不
返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。若内层查询结果非空，则外层的WHERE子句返回真值若内层查询
结果为空，则外层的WHERE子句返回假值由EXISTS引出的子查询，其目标列表达式通常都用，因为带EXISTS的子查询只返回
真值或假值，给出列名无实际意义2.NOTEXISTS谓词若内层查询结果非空，则外层的WHERE子句返回假值若内层查询结果
为空，则外层的WHERE子句返回真值带有EXISTS谓词的子查询(续）[例44]查询所有选修了1号课程的学生姓名。思路
分析：本查询涉及Student和SC关系在Student中依次取每个元组的Sno值，用此值去检查SC关系若SC中存在这样的元
组，其Sno值等于此Student.Sno值，并且其Cno=''1''，则取此Student.Sname送入结果关系带有EXIST
S谓词的子查询(续）用嵌套查询SELECTSnameFROMStudentWHEREE
XISTS(SELECTFROMSC
WHERESno=Student.SnoANDCno=''1'')；带有EXI
STS谓词的子查询(续）用连接运算 SELECTSname FROMStudent,SC WHEREStudent
.Sno=SC.SnoANDSC.Cno=''1'';带有EXISTS谓词的子查询(续）[例45]查询没有选修1号课程
的学生姓名。SELECTSnameFROMStudentWHERENOTEXISTS
(SELECTFROMSC
WHERESno=Student.SnoANDCno=''1'')；带有EXISTS谓词的子查询(
续）不同形式的查询间的替换一些带EXISTS或NOTEXISTS谓词的子查询不能被其他形式的子查询等价替换所有带IN谓词
、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换用EXISTS/NOTEXISTS实现全称量
词(难点)SQL语言中没有全称量词?（Forall）可以把带有全称量词的谓词转换为等价的带有存在量词的谓词：
(?x)P≡?(?x(?P))带有EXISTS谓词的子查询(续）例：[例39]查询与“刘晨”在同一个系
学习的学生。可以用带EXISTS谓词的子查询替换：SELECTSno，Sname，Sdept
FROMStudentS1WHEREEXISTS(SELECT
FROMStudentS2WHERES2
.Sdept=S1.SdeptANDS2.Sname
=‘刘晨’)；带有EXISTS谓词的子查询(续）[例46]查询选修了全部课程的学生姓名。SELECT
SnameFROMStudentWHERENOTEXISTS
（SELECTFROMCourse
WHERENOTEXISTS
(SELECTFROMSC
WHERESno=Student.Sno
ANDCno=Course.Cno
））；带有EXIST
S谓词的子查询(续）用EXISTS/NOTEXISTS实现逻辑蕴函(难点)SQL语言中没有蕴函(Implication
)逻辑运算可以利用谓词演算将逻辑蕴函谓词等价转换为：p?q≡?p∨q带有E
XISTS谓词的子查询(续）[例47]查询至少选修了学生200215122选修的全部课程的学生号码。解题思路：用逻辑蕴函表
达：查询学号为x的学生，对所有的课程y，只要200215122学生选修了课程y，则x也选修了y。形式化表示： 用P表示谓词“
学生200215122选修了课程y” 用q表示谓词“学生x选修了课程y” 则上述查询为:(?y)p?q带有EXI
STS谓词的子查询(续）等价变换： (?y)p?q≡?(?y(?(p?q))
≡?(?y(?(?p∨q)))≡??y(p∧?q)变换后语义：
不存在这样的课程y，学生200215122选修了y，而学生x没有选。带有EXISTS谓词的子查询(续）?用NOTEXISTS
谓词表示：SELECTDISTINCTSnoFROMSCSCXWHE
RENOTEXISTS(SELECT
FROMSCSCYWHERESCY.Sno=''200215122''
ANDNOTEXISTS
(SELECT
FROMSCSCZWHERESCZ.
Sno=SCX.SnoAND
SCZ.Cno=SCY.Cno))；3.4数据查询3.4.1单表查询3.4.2连接查询3.4.3嵌套查询
3.4.4集合查询3.4.5Select语句的一般形式3.4.4集合查询集合操作的种类并操作UNION交操作IN
TERSECT差操作EXCEPT参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同集合查询（续）[例48
]查询计算机科学系的学生及年龄不大于19岁的学生。方法一：SELECTFROMSt
udentWHERESdept=''CS''UNIONSELECT
FROMStudentWHERESage<=19；UNION：将多个查询结果合并起来时，系统自
动去掉重复元组。UNIONALL：将多个查询结果合并起来时，保留重复元组集合查询（续）方法二：SELEC
TDISTINCTFROMStudentWHERESdept=''CS''OR
Sage<=19；集合查询（续）[例49]查询选修了课程1或者选修了课程2的学生。SELECT
SnoFROMSCWHERECno=''1''UNION
SELECTSnoFROMSCWHERECno=''2''；集合查询（续）[例
50]查询计算机科学系的学生与年龄不大于19岁的学生的交集SELECTFROMStudentWHERESdep
t=''CS''INTERSECTSELECTFROMStudentWHERESage<=19集合查询（续）[
例50]实际上就是查询计算机科学系中年龄不大于19岁的学生 SELECT FROMStudent
WHERESdept=''CS''ANDSage<=19；集合查询（续）[例51]查询选修课程1
的学生集合与选修课程2的学生集合的交集SELECTSnoFROMSCWHERECno=''1
''INTERSECTSELECTSnoFROMSCWHERECno=''2''；
集合查询（续）[例51]实际上是查询既选修了课程1又选修了课程2 的学生SELECTSno
FROMSCWHERECno=''1''ANDSnoIN
(SELECTSno
FROMSC
WHERECno=''2'')；集合查询（续）[例52]查询计算机科学系的学生与年
龄不大于19岁的学生的差集。SELECTFROMStudentWHERESdept=''CS
''EXCEPTSELECTFROMStudentWHERESage<=19;
集合查询（续）[例52]实际上是查询计算机科学系中年龄大于19岁的学生SELECT
FROMStudentWHERESdept=''CS''ANDSage>19；3.4数据查询
3.4.1单表查询3.4.2连接查询3.4.3嵌套查询3.4.4集合查询3.4.5Select语句的一般形式
3.4.5SELECT语句的一般格式SELECT[ALL|DISTINCT]<目标列表达式>[别名][
，<目标列表达式>[别名]]…FROM<表名或视图名>[别名][，<表名或视图
名>[别名]]…[WHERE<条件表达式>][GROUPBY<列名1>[HAVING<条件表达式>
]][ORDERBY<列名2>[ASC|DESC]下课了。。。CS刘晨200215122CS
李勇200215121SdeptSnameSno相关子查询19张立18王敏SageSname?=
<>或!=<<=>>=ANYIN--MIN>=MIN
ALL--NOTINMAX>=MAX数据库系统概论AnIntroductiontoDatabaseSystem第三章关系数据库标准语言SQL(续1）803200215122CS19女刘晨200215122902200215122CS19女刘晨200215122883200215121CS20男李勇200215121852200215121CS20男李勇200215121921200215121CS20男李勇200215121GradeCnoSC.SnoSdeptSageSsexSnameStudent.Sno查询结果：CnoCnameCpnoCcredit1数据库542数学?23信息系统144操作系统635数据结构746数据处理27PASCAL语言64CnoCnameCpnoCcredit1数据库542数学?23信息系统144操作系统635数据结构746数据处理27PASCAL语言64655371PcnoCnoNULLNULLIS19男张立200215125NULLNULLMA18女王敏200215123803CS19女刘晨200215122902CS19女刘晨200215122883CS20男李勇200215121852CS20男李勇200215121921CS20男李勇200215121GradeCnoSdeptSageSsexSnameStudent.Sno |
|
