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

1、机械自动化学院机械自动化学院20142014主讲：主讲： 顾顾 曦曦 电话：电话：1569718107915697181079EmailEmail：主要内容SQL概述数据定义数据更新1简单查询 连接查询聚合查询 集合运算嵌套子查询嵌套子查询SQL查询一般格式查询一般格式数据更新数据更新2 *2*31.0 概念在SQL查询中，一个SELECT-FROM-WHERE查询语句称为一个查询块。查询块。查询块的结果是集合集合。将一个查询块嵌入到另一个查询块的WHERE子句或HAVING子句中，称为嵌套子查询。SQL允许多层嵌套子查询。具体表现在如下几个方面：元素与集合间的属于关系集合之间的包含和相等关系

2、集合的存在关系元素与集合元素之间的比较关系在子查询中，不允许使用ORDER BY子句，该子句仅用于最后的输出结果排序嵌套查询分为相关子查询和非相关子查询非相关子查询：子查询的结果不依赖于上层查询相关子查询：当上层查询的元组发生变化时，其子查询必须重新执行*5嵌套查询求解方法不相关子查询： 子查询的查询条件不依赖于父查询n由里向外 逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件查找条件。*6嵌套查询求解方法（续）相关子查询：子查询的查询条件依赖于父查询首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取

3、此元组放入结果表然后再取外层表的下一个元组重复这一过程，直至外层表全部检查完为止*71.1 使用IN的子查询例3.37 查询选修过课程的学生姓名。即：在学生表Student中，将学号出现在成绩表Score中(表明该学生选修过课程)的学生姓名查询出来 SELECT studentName /*外层查询/父查询*/ FROM Student WHERE Student.studentNo IN (SELECT Score.studentNo /*内层查询/子查询*/ FROM Score)*8该查询属于非相关子查询，执行过程如图3-23。*9其查询过程为：(1) 从Score表中查询出学生的学号s

4、tudentNo，构成一个中间结果关系r；(2) 从Student表中取出第一个元组t；(3) 如果元组t的studentNo属性的值包含在中间结果关系r中（即t.studentNor），则将元组t的studentName属性的值作为最终查询结果关系的一个元组；否则丢弃元组t；(4) 如果Student表中还有元组，则取Student表的下一个元组t，并转第(3)步；否则转第(5)步；(5) 将最终结果关系显示出来*10在本例中，WHERE子句用于检测元素与集合间的属于关系其中Student.studentNo为元素，IN为“属于”嵌套语句“SELECT Score.studentNo FRO

5、M Score”的查询结果为选修过课程的所有学生的学号集合该嵌套SELECT语句称为子查询*11例3.38 查找选修过课程名中包含“系统”的课程的同学学号、姓名和班级编号。SELECT studentNo, studentName, classNoFROM StudentWHERE studentNo IN ( SELECT studentNo FROM Score WHERE courseNo IN ( SELECT courseNo FROM Course WHERE courseName LIKE %系统% ) )*12WHERE子句中的IN可以实现多重嵌套，该查询的执行过程可以通过下图

6、来表示*13使用IN的非相关子查询的查询过程归纳如下：1、首先执行最底层的子查询块，将该子查询块的结果作为中间关系；2、执行上一层(即外一层)查询块，对于得到的每个元组，判断该元组是否在它的子查询结果中：如果在，取出该元组中的相关属性作为最终输出结果(或该查询块的查询结果中间关系)的一个元组否则舍弃该元组3、如果已经执行完最上层查询块，则将最终结果作为一个新关系输出；否则返回第(2)步重复执行*141.2 使用比较运算符的子查询 元素与集合元素之间除了包含关系（IN），还有比较比较关系。常用到谓词ANY(或SOME)和ALLANY:大于最小值大于最小值 ALL:大于最大值大于最大值ALL:小于

7、最大值小于最大值如果子查询中的结果关系仅包含一个元组，则可将ALL和ANY去掉，直接使用比较运算符ANY也可以用SOME替代(SQL-92 标准) 例3.41 查询所选修课程的成绩大于所有“002”号课程成绩的同学学号及相应课程的课程号和成绩。SELECT studentNo, courseNo, scoreFROM ScoreWHERE scoreALL ( SELECT score FROM Score WHERE courseNo=002 )*16例3.42 查询年龄小于“计算机科学与技术07-01班”某个同学某个同学年龄的所有同学的学号、姓名和年龄。SELECT studentNo,

8、studentName, year(NOW()-year(birthday) AS age FROM Student WHERE ageANY ( SELECT year(NOW()-year(birthday) FROM Student a, Class b WHERE className=计算机科学与技术0701班 AND a.classNo=b.classNo )*17小于任一个，即小于年龄最大的本查询执行过程是：首先执行子查询，找出“计算机科学与技术07-01班”同学的年龄集合然后在Student表中将年龄小于该集合中最大同学年龄（22）的所有同学查找出来。在比较运算符中，=ANY等价

9、于IN谓词，!=ALL等价于NOT IN谓词 注意：子查询一定要跟在比较符之后*18ANY（或SOME），ALL谓词与聚集函数、IN谓词的等价转换关系*19=或或!=ANY IN MAXMIN= MINALLNOT INMINMAX= MAX1.3 使用存在量词EXISTS的子查询量词有两种：存在量词、全称量词SQL仅提供存在量词的运算，使用谓词EXISTS表示全称量词转化通过NOT EXISTS谓词来实现WHERE子句中的谓词EXISTS用来判断其后的子查询的结果集合中是否存在元素；谓词EXISTS大量用于相关子查询相关子查询中。*20例3.43 查询选修了“计算机原理”课程的同学姓名、所在

10、班级编号。该查询可直接通过连接运算实现，也可以通过IN子查询来实现。还可以通过存在量词实现：SELECT studentName, classNo FROM Student xWHERE EXISTS ( SELECT * FROM Score a, Course b WHERE a.courseNo=b.courseNo AND a.studentNo=x.studentNo AND courseName=计算机原理 )相关子查询 *21外层查出结果逐条代入，有结果，则保留为结果集外层查出结果逐条代入，有结果，则保留为结果集本查询涉及Student、Score和Course三个关系，属于相关

11、子查询，查询过程如下：(1) 首先取Student表的第一个元组x，并取其学号x.studentNo；(2) 执行子查询，该子查询对表Score和Course进行连接，并选择其学号为x.studentNo，其课程名为“计算机原理”的元组；(3) 如果子查询中可以得到结果(即存在元组)，则将Student表中元组x的学生姓名和所在班级编号组成一个新元组放在结果集合中；否则(即不存在元组)，直接丢弃元组x；(4) 如果Student表中还有元组，则取Student表的下一个元组x，并取其学号x.studentNo，转第(2)步；否则转第(5)步；(5) 将结果集合中的元组作为一个新关系输出子查询的

12、目标列通常是*存在量词EXISTS只判断其后的子查询的结果集合中是否存在元是否存在元素素，无列名*22例3.44 查询选修了所有课程的学生姓名。分析：本查询要使用全称量词，含义是：选择这样的学生，任意一门课程他都选修了SQL中没有全称量词，使用存在量词和取非运算来实现含义：查询这样的学生x，不存在他没有选修的课程c。*23SELECT studentName FROM Student xWHERE NOT EXISTS ( SELECT * FROM Course c WHERE NOT EXISTS ( SELECT * FROM Score WHERE studentNo=x.studen

13、tNo AND courseNo=c.courseNo ) )*24/*判断学生x.studentNo没有选修课程c.courseNo*/相关子查询在SQL中属于复杂的查询，其子查询的查询条件依赖于外层查询的元组值当外层查询的元组值发生变化时，其子查询要重新依据新的条件进行查询使用EXISTS的相关子查询处理过程是：(1) 首先取外层查询的第一个元组；(2) 依据该元组的值，执行子查询；(3) 如果子查询的结果非空(EXISTS量词返回真值)，将外层查询的该元组放入到结果集中；否则(EXISTS量词返回假值)，舍弃外层查询的该元组；(4) 取外层查询的下一个元组，返回第(2)步重复上述过程，直

14、到外层查询所有的元组处理完毕；(5) 将结果集合中的元组作为一个新关系输出*25带有EXISTS谓词的子查询总结1. EXISTS谓词n带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。若内层查询结果非空，则外层的WHERE子句返回真;若内层查询结果为空，则外层的WHERE子句返回假;n由EXISTS引出的子查询，其目标列表达式通常都用* ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义2. NOT EXISTS谓词若内层查询结果非空，则外层的WHERE子句返回假值若内层查询结果为空，则外层的WHERE子句返回真值*26 3、不同形

15、式的查询间的替换n一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换n所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换 4、用双重否定 NOT EXISTS +NOT EXISTS实现全称量词 例:查询所有选修了1号课程的学生姓名1)用嵌套查询 SELECT StudentName FROM Student a WHERE EXISTS ( SELECT * FROM Score WHERE StudentNo=a. StudentNo AND courseNo= 1 )*282)用连接运算SELECT Studen

16、tNameFROM Student a, Score b WHERE a. StudentNo=b. StudentNo AND b. courseNo = 1*29*30SELECT共有6个子句，其中SELECT和FROM是必须的，其它是可选项，必须严格按照如下顺序排列必须严格按照如下顺序排列SELECT ALL | DISTINCT AS , AS , . FROM AS , AS , . WHERE GROUP BY , , . HAVING ORDER BY ASC | DESC , ASC | DESC, . *31其中：(1) 可以是下面的可选格式：.*, ., , (2) FRO

17、M子句指定查询所涉及的表、视图或查询表。为操作方便，常给表取一个别名，称为元组变量(3) WHERE子句给出查询的条件，随后的中可以使用下面的谓词运算符： 比较运算符：，=，=，=，!=； 逻辑运算符：AND，OR，NOT； 范围运算符：NOT BETWEEN.AND； 集合运算符：NOT IN； 空值运算符：IS NOT null； 字符匹配运算符：NOT LIKE； 存在量词运算符：NOT EXISTS。*32在在中可以包中可以包含含子查询子查询，但不可以直接，但不可以直接使用使用聚合函数聚合函数若要使用聚合函数，必须若要使用聚合函数，必须引出一个子查询。引出一个子查询。(4) GROUP

18、 BY 子句表示的含义是：首先按进行分组，值相同的分为一组；在同组情况下，再按进行分组，值相同的分为一组；依次类推包含GROUP BY时，SELECT通常选择GROUP BY的分组属性以及聚合属性(通常将聚合函数作用于聚合属性，如avg(score)、sum(creditHour)等)输出。(5) HAVING 子句给出分组后的选择条件，用来选择满足条件的分组。 随后的中可直接使用聚合函数，也可使用子查询*33(6) ORDER BY子句实现对查询结果的排序它是SQL查询的最后一个操作，必须放在最后；其中的可以是列名，也可以是表达式；如果是表达式，则先计算表达式的值，然后排序输出；排序有升序A

19、SC和降序DESC，默认为升序。(7) 集合运算集合运算SELECT语句之间可以进行集合运算，包括并UNION、交INTERSECT、差EXCEPT运算。*343.1 插入数据插入数据 3.2 删除数据删除数据3.3 修改数据修改数据*353.1 插入数据插入方式有两种：一是插入单条记录，二是插入子查询的结果。后者是一次插入多条记录。插入一条元组：INSERT INTO ( , . ) VALUES ( , . ) 插入多条元组插入多条元组：INSERT INTO ( , . ) 其中：子查询子查询：由SELECT语句引出的一个查询。*37例3.77将少数民族同学的选课信息插入到Student

20、Nation表中。创建表StudentNation： CREATE TABLE StudentNation ( studentNo char(7) NOT NULL , /*学号*/ courseNo char(3) NOT NULL , /*课程号*/ score numeric(5, 1) default 0 NOT NULL /*成绩*/ CHECK( score BETWEEN 0.0 AND 100.0), CONSTRAINT StudentNationPK PRIMARY KEY (studentNo, courseNo) )*38SQL语句：INSERT INTO Studen

21、tNation SELECT * FROM Score WHERE studentNo IN ( SELECT studentNo FROM Student WHERE nation汉族 )*39无属性列表，表示全部属性例3.78将汉族同学的选课信息插入到StudentNation表中。INSERT INTO StudentNation(studentNo, courseNo) SELECT studentNo, courseNo FROM Score WHERE studentNo IN ( SELECT studentNo FROM Student WHERE nation=汉族 )*40

22、该查询仅将汉族同学的学号和课程号插入到StudentNation表中；成绩列自动取0值，StudentNation表在定义该列时其默认值为0。*41CREATE TABLE StudentNation ( . score numeric(5, 1) default 0 NOT NULL /*成绩*/. )3.2 删除数据删除命令：DELETE FROM WHERE ：要删除记录的表名；WHERE ：指出被删除的记录所满足的条件该项可以省略，若省略则表示删除表中的所有记录；WHERE子句中可以包含子查询。*42例3.79 删除学号为0800001同学的选课记录。DELETE FROM Score WHERE studentNo=0800001*43例3.81删除平均分在60分到70分之间的同学选课记录DELETE FROM Score WHERE studentNo IN ( SELECT studentNo FROM Score GROUP BY studentNo HAVING avg(score) BETWEEN 6