数据库上课第七讲SQL语言_4(复杂查询).

机械自动化学院2014主讲：顾曦电话：[phone]Email：[e-mail]主要内容SQL概述数据定义数据更新1简单查询连接查询聚合查询集合运算嵌套子查询SQL查询一般格式数据更新212:33212:3331.0概念在SQL查询中，一个SELECT-FROM-WHERE查询语句称为一个查询块。查询块的结果是集合。将一个查询块嵌入到另一个查询块的WHERE子句或HAVING子句中，称为嵌套子查询。SQL允许多层嵌套子查询。具体表现在如下几个方面：元素与集合间的属于关系集合之间的包含和相等关系集合的存在关系元素与集合元素之间的比较关系在子查询中，不允许使用ORDERBY子句，该子句仅用于最后的输出结果排序嵌套查询分为相关子查询和非相关子查询非相关子查询：子查询的结果不依赖于上层查询相关子查询：当上层查询的元组发生变化时，其子查询必须重新执行12:335嵌套查询求解方法不相关子查询：子查询的查询条件不依赖于父查询由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。12:336嵌套查询求解方法（续）相关子查询：子查询的查询条件依赖于父查询首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表然后再取外层表的下一个元组重复这一过程，直至外层表全部检查完为止12:3371.1使用IN的子查询[例3.37]查询选修过课程的学生姓名。即：在学生表Student中，将学号出现在成绩表Score中(表明该学生选修过课程)的学生姓名查询出来SELECTstudentName/*外层查询/父查询*/FROMStudentWHEREStudent.studentNoIN(SELECTScore.studentNo/*内层查询/子查询*/FROMScore)12:338该查询属于非相关子查询，执行过程如图3-23。12:339其查询过程为：(1)从Score表中查询出学生的学号studentNo，构成一个中间结果关系r；(2)从Student表中取出第一个元组t；(3)如果元组t的studentNo属性的值包含在中间结果关系r中（即t.studentNo∈r），则将元组t的studentName属性的值作为最终查询结果关系的一个元组；否则丢弃元组t；(4)如果Student表中还有元组，则取Student表的下一个元组t，并转第(3)步；否则转第(5)步；(5)将最终结果关系显示出来12:3310在本例中，WHERE子句用于检测元素与集合间的属于关系其中Student.studentNo为元素，IN为“属于”嵌套语句“SELECTScore.studentNoFROMScore”的查询结果为选修过课程的所有学生的学号集合该嵌套SELECT语句称为子查询12:3311[例3.38]查找选修过课程名中包含“系统”的课程的同学学号、姓名和班级编号。SELECTstudentNo,studentName,classNoFROMStudentWHEREstudentNoIN(SELECTstudentNoFROMScoreWHEREcourseNoIN(SELECTcourseNoFROMCourseWHEREcourseNameLIKE'%系统%'))12:3312WHERE子句中的IN可以实现多重嵌套，该查询的执行过程可以通过下图来表示12:3313使用IN的非相关子查询的查询过程归纳如下：1、首先执行最底层的子查询块，将该子查询块的结果作为中间关系；2、执行上一层(即外一层)查询块，对于得到的每个元组，判断该元组是否在它的子查询结果中：如果在，取出该元组中的相关属性作为最终输出结果(或该查询块的查询结果中间关系)的一个元组否则舍弃该元组3、如果已经执行完最上层查询块，则将最终结果作为一个新关系输出；否则返回第(2)步重复执行12:33141.2使用比较运算符的子查询元素与集合元素之间除了包含关系（IN），还有比较关系。常用到谓词ANY(或SOME)和ALLANY:大于最小值ALL:大于最大值ANY:小于最小值ALL:小于最大值如果子查询中的结果关系仅包含一个元组，则可将ALL和ANY去掉，直接使用比较运算符ANY也可以用SOME替代(SQL-92标准)[例3.41]查询所选修课程的成绩大于所有“002”号课程成绩的同学学号及相应课程的课程号和成绩。SELECTstudentNo,courseNo,scoreFROMScoreWHEREscoreALL(SELECTscoreFROMScoreWHEREcourseNo='002')12:3316[例3.42]查询年龄小于“计算机科学与技术07-01班”某个同学年龄的所有同学的学号、姓名和年龄。SELECTstudentNo,studentName,year(NOW())-year(birthday)ASageFROMStudentWHEREageANY(SELECTyear(NOW())-year(birthday)FROMStudenta,ClassbWHEREclassName='计算机科学与技术0701班'ANDa.classNo=b.classNo)12:3317小于任一个，即小于年龄最大的本查询执行过程是：首先执行子查询，找出“计算机科学与技术07-01班”同学的年龄集合然后在Student表中将年龄小于该集合中最大同学年龄（22）的所有同学查找出来。在比较运算符中，=ANY等价于IN谓词，!=ALL等价于NOTIN谓词注意：子查询一定要跟在比较符之后12:3318ANY（或SOME），ALL谓词与聚集函数、IN谓词的等价转换关系12:3319=或!===ANYINMAX=MAXMIN=MINALLNOTINMIN=MINMAX=MAX1.3使用存在量词EXISTS的子查询量词有两种：存在量词、全称量词SQL仅提供存在量词的运算，使用谓词EXISTS表示全称量词转化通过NOTEXISTS谓词来实现WHERE子句中的谓词EXISTS用来判断其后的子查询的结果集合中是否存在元素；谓词EXISTS大量用于相关子查询中。12:3320[例3.43]查询选修了“计算机原理”课程的同学姓名、所在班级编号。该查询可直接通过连接运算实现，也可以通过IN子查询来实现。还可以通过存在量词实现：SELECTstudentName,classNoFROMStudentxWHEREEXISTS(SELECT*FROMScorea,CoursebWHEREa.courseNo=b.courseNoANDa.studentNo=x.studentNoANDcourseName='计算机原理')相关子查询12:3321外层查出结果逐条代入，有结果，则保留为结果集本查询涉及Student、Score和Course三个关系，属于相关子查询，查询过程如下：(1)首先取Student表的第一个元组x，并取其学号x.studentNo；(2)执行子查询，该子查询对表Score和Course进行连接，并选择其学号为x.studentNo，其课程名为“计算机原理”的元组；(3)如果子查询中可以得到结果(即存在元组)，则将Student表中元组x的学生姓名和所在班级编号组成一个新元组放在结果集合中；否则(即不存在元组)，直接丢弃元组x；(4)如果Student表中还有元组，则取Student表的下一个元组x，并取其学号x.studentNo，转第(2)步；否则转第(5)步；(5)将结果集合中的元组作为一个新关系输出子查询的目标列通常是*存在量词EXISTS只判断其后的子查询的结果集合中是否存在元素，无列名12:3322[例3.44]查询选修了所有课程的学生姓名。分析：本查询要使用全称量词，含义是：选择这样的学生，任意一门课程他都选修了SQL中没有全称量词，使用存在量词和取非运算来实现含义：查询这样的学生x，不存在他没有选修的课程c。12:3323SELECTstudentNameFROMStudentxWHERENOTEXISTS(SELECT*FROMCoursecWHERENOTEXISTS(SELECT*FROMScoreWHEREstudentNo=x.studentNoANDcourseNo=c.courseNo))12:3324/*判断学生x.studentNo没有选修课程c.courseNo*/相关子查询在SQL中属于复杂的查询，其子查询的查询条件依赖于外层查询的元组值当外层查询的元组值发生变化时，其子查询要重新依据新的条件进行查询使用EXISTS的相关子查询处理过程是：(1)首先取外层查询的第一个元组；(2)依据该元组的值，执行子查询；(3)如果子查询的结果非空(EXISTS量词返回真值)，将外层查询的该元组放入到结果集中；否则(EXISTS量词返回假值)，舍弃外层查询的该元组；(4)取外层查询的下一个元组，返回第(2)步重复上述过程，直到外层查询所有的元组处理完毕；(5)将结果集合中的元组作为一个新关系输出12:3325带有EXISTS谓词的子查询总结1.EXISTS谓词带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。若内层查询结果非空，则外层的WHERE子句返回真;若内层查询结果为空，则外层的WHERE子句返回假;由EXISTS引出的子查询，其目标列表达式通常都用*，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义2.NOTEXISTS谓词若内层查询结果非空，则外层的WHERE子句返回假值若内层查询结果为空，则外层的WHERE子句返回真值12:33263、不同形式的查询间的替换一些带EXISTS或NOTEXISTS谓词的子查询不能被其他形式的子查询等价替换所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换4、用双重否定NOTEXISTS+NOTEXISTS实现全称量词例:查询所有选修了1号课程的学生姓名1)用嵌套查询SELECTStudentNameFROMStudentaWHEREEXISTS(SELECT*FROMScoreWHEREStudentNo=a.StudentNoANDcourseNo='1')12:33282)用连接运算SELECTStudentNameFROMStudenta,ScorebWHEREa.StudentNo=b.StudentNoANDb.courseNo='1'12:332912:3330SELECT共有6个子句，其中SELECT和FROM是必须的，其它是可选项，必须严格按照如下顺序排列SELECT[ALL|DISTINCT]目标列表达式[AS][别名][,目标列表达式[AS][别名],...]FROM表名|视图名|查询表[AS][别名][,表名|视图名|查询表[AS][别名],...][WHERE条件表达式][GROUPBY列名1[,列名2,...][HAVING条件表达式]][ORDERBY列名表达式[ASC|DESC][,列名表达式[ASC|DESC],...]]12:3331其中：(1)目标列表达式可以是下面的可选格式：[表名|别名.]*,[表名|别名.]列名,函数,聚合函数(2)FROM子句指定查询所涉及的表、视图或查询表。为操作方便，常给表取一个别名，称为元组变量(3)WHERE子句给出查询的条件，随后的条件表达式中可以使用下面的谓词运算符：比较运算符：，=，，=，=，，!=；逻辑运算符：AND，OR，NOT；范围运算