1、数据库系统概论 An Introduction to Database System第三章 关系数据库标准语言SQL (续1）,1查询选修7号课程学生的学号和成绩，按成绩降序排列 2查询选修表的所有信息，按课程号升序，课程成绩降序排列 3查询选修了课程的学生学号 4查询计算机系年龄在20岁以下的学生姓名。 5查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。,复习单表查询1,1查询选修7号课程学生的学号和成绩，按成绩降序排列 SELECT Sno, Grade FROM SC WHERE Cno=7ORDER BY Grade DESC 2查询选修表的所有信息，按课程号
2、升序，课程成绩降序排列 SELECT Cno, Sno, Grade FROM SC ORDER BY Cno, Grade DESC 3查询选修了课程的学生学号 SELECT Sno FROM SC；4查询计算机系年龄在20岁以下的学生姓名。SELECT SnameFROM StudentWHERE Sdept= CS AND Sage20；5查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。 SELECT Sname，Ssex FROM Student WHERE Sdept IN ( IS，MA，CS ),查询名字中第2个字为“阳“字的学生的姓名和学号。 查询所有
3、不姓刘的学生姓名。 查询缺少成绩的学生的学号和相应的课程号。 查所有有成绩的学生学号和课程号。 查询计算机系年龄在20岁以下的学生姓名。,复习单表查询2,查询名字中第2个字为“阳“字的学生的姓名和学号。SELECT Sname，SnoFROM StudentWHERE Sname LIKE _阳%；查询所有不姓刘的学生姓名。SELECT Sname，Sno，SsexFROM StudentWHERE Sname NOT LIKE 刘%； 查询缺少成绩的学生的学号和相应的课程号。SELECT Sno，CnoFROM SCWHERE Grade IS NULL 4 查所有有成绩的学生学号和课程号。
4、SELECT Sno，CnoFROM SCWHERE Grade IS NOT NULL； 5 查询计算机系年龄在20岁以下的学生姓名。SELECT SnameFROM StudentWHERE Sdept= CS AND Sage20；,复习单表查询3,1、找出全体男生的总人数2、找出选修了课程的学生总人数3、找出7号课程的最高分、最低分和平均分4、找出各个系的学生人数 5、找出各个系男女学生的人数,6、找出选修了3门以上课程的学生的学号7、找出门门课程的成绩在80分以上的学生的学号 8、找出均分90分且没有课程成绩80分的学生的学号 9、找出至少有2门课程不及格的学生的学号,1找出全体男生
5、的总人数 SELECT COUNT(*) FROM S WHERE Ssex=男 2找出选修了课程的学生总人数 SELECT COUNT(DISTINCT Sno) FROM SC 3找出7号课程的最高分、最低分和平均分 SELECT MAX(Grade), MIN(Grade), AVG(Grade) FROM SC WHERE Cno=7,4找出各个系的学生人数 SELECT Sdept, COUNT(*) FROM S GROUP BY Sdept 5找出各个系男女学生的人数 SELECT Sdept, Ssex, COUNT(*) FROM S GROUP BY Sdept, Ssex
6、,6找出选修了3门以上课程的学生的学号SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(Cno)3 7找出门门课程的成绩在80分以上的学生的学号 SELECT Sno FROM SCGROUP BY SnoHAVING MIN(Grade)80,8找出均分90分且没有课程成绩90 AND MIN(Grade)=80 9找出至少有2门课程不及格的学生的学号 SELECT Sno FROM SC WHERE Grade=2,3.4 数据查询,3.4.1 单表查询 3.4.2 连接查询 3.4.3 嵌套查询 3.4.4 集合查询 3.4.5 Select语句的
7、一般形式,3.4.2 连接查询,3.4.2 连接查询(1) 举例,找出选修了“数据结构”课程的学生学号及成绩 SELECT Sno, Grade FROM SC, C WHERE SC.Cno=C.Cno AND Cname=数据结构 用SQL92中的连接格式可写为： SELECT Sno, Grade FROM SC JOIN C ON SC.Cno=C.Cno WHERE Cname=数据结构,3.4.2 连接查询(2) 举例,找出选修了“数据结构”课程的学生姓名及成绩 SELECT Sname, Grade FROM S, SC, C WHERE S.Sno=SC.Sno AND SC.
8、Cno=C.Cno AND Cname=数据结构 用SQL92中的连接格式可写为： SELECT Sname, Grade FROM S JOIN SC ON S.Sno=SC.Sno JOIN C ON SC.Cno=C.Cno WHERE Cname=数据结构,SELECT FROM WHERE语句称为查询块 嵌套查询：将查询块嵌套在另一个查询块的WHERE 子句或HAVING 短语中 意义： 体现编程“结构化”的含义 可以用多个简单查询进行复杂的查询,3.4.3 嵌套查询,3.4.3 嵌套查询,找出选修了1号课程的学生姓名及性别 SELECT Sname, Ssex FROM S WHE
9、RE Sno IN ( SELECT Sno FROM SC WHERE Cno=1 ) 说明： 嵌套查询的执行过程是由里向外进行 子查询中不能使用ORDER BY子句 理论上用子查询的速度要快,外层查询 (父查询),内层查询 (子查询),例39 查询与“刘晨”在同一个系学习的学生。此查询要求可以分步来完成 确定“刘晨”所在系名 SELECT Sdept FROM Student WHERE Sname= 刘晨 ；结果为： CS,3.4.3 嵌套查询,嵌套查询的执行过程是由里向外进行:, 查找所有在CS系学习的学生。 SELECT Sno，Sname，Sdept FROM Student WH
10、ERE Sdept= CS ； 结果为：,3.4.3 嵌套查询,找出与“刘晨”在同一个系学习的学生的姓名、性别及年龄 SELECT Sname, Ssex, Sage FROM S,自连接时使用别名,WHERE Sdept =,嵌套查询有时可以用连接查询来实现 SELECT S1.Sname , S1.Ssex , S1.Sage FROM S S1 , S S2 WHERE S1.Sdept=S2.Sdept AND S2.Sname= 刘晨,( SELECT Sdept FROM S WHERE Sname= 刘晨 ),带有IN谓词的子查询（续）,查询选修了课程名为“信息系统”的学生学号和
11、姓名SELECT Sno，Sname 最后在Student关系中FROM Student 取出Sno和Sname,WHERE Sno IN(SELECT Sno 然后在SC关系中找出选FROM SC 修了3号课程的学生学号WHERE Cno IN,(SELECT Cno 首先在Course关系中找出FROM Course “信息系统”的课程号，为3号WHERE Cname= 信息系统);,复合条件连接与嵌套查询比较,复合条件连接：WHERE子句中含多个连接条件例37查询选修2号课程且成绩在90分以上的所有学生 SELECT Student.Sno, SnameFROM Student, SCW
12、HERE Student.Sno = SC.Sno AND /* 连接谓词*/SC.Cno= 2 AND SC.Grade 90； /* 其他限定条件 */ 可以用嵌套查询实现？,SELECT Student.Sno, Sname FROM Student WHERE Sno IN（ SELECT Sno FROM SC WHERE CNO= 2 AND Grade 90）,嵌套查询(续),子查询的限制 不能使用ORDER BY子句 层层嵌套方式反映了 SQL语言的结构化 有些嵌套查询可以用连接运算替代,常见错误询,多表连接时，对不唯一的属性忘记了表名前缀 SELECT Sno, Sname,
13、 Cno, Grade,WHERE条件中出现集函数（统计函数） WHERE MIN (Grade)80,非GROUP BY中的属性出现在SELECT后面，且未用统计函数 SELECT Sno, Cno, AVG(Grade) From Sc GROUP by Sno,有HAVING短语，但没有GROUP BY子句,HAVING中出现没有统计函数的属性 HAVING Cno 1 AND AVG(Grade)80,3.4.3 嵌套查询,一、带有IN谓词的子查询 二、 带有比较运算符的子查询 三、 带有ANY（SOME）或ALL谓词的子查询,二、带有比较运算符的子查询,当能确切知道内层查询返回单值时
14、，可用比较运算符（，=，）。 与ANY或ALL谓词配合使用,带有比较运算符的子查询（续）,例：假设一个学生只可能在一个系学习，并且必须属于一个系，则在例39可以用 = 代替IN ：SELECT Sno，Sname，SdeptFROM StudentWHERE Sdept =(SELECT SdeptFROM StudentWHERE Sname= 刘晨)；,带有比较运算符的子查询（续）,子查询一定要跟在比较符之后错误的例子：SELECT Sno，Sname，SdeptFROM StudentWHERE ( SELECT SdeptFROM StudentWHERE Sname= 刘晨 ) =
15、Sdept；,带有比较运算符的子查询（续）,例41找出每个学生超过他选修课程平均成绩的课程号。SELECT Sno， CnoFROM SC xWHERE Grade =(SELECT AVG(Grade) FROM SC yWHERE y.Sno=x.Sno);,相关子查询,带有比较运算符的子查询（续）,可能的执行过程： 1. 从外层查询中取出SC的一个元组x，将元组x的Sno值（200215121）传送给内层查询。SELECT AVG(Grade)FROM SC yWHERE y.Sno=200215121; 2. 执行内层查询，得到值88（近似值），用该值代替内层查询，得到外层查询：SEL
16、ECT Sno， CnoFROM SC xWHERE Grade =88；,带有比较运算符的子查询（续）,3. 执行这个查询，得到（200215121，1）（200215121，3） 4.外层查询取出下一个元组重复做上述1至3步骤，直到外层的SC元组全部处理完毕。结果为:（200215121，1）（200215121，3）（200215122，2）,3.4.3 嵌套查询,一、带有IN谓词的子查询 二、 带有比较运算符的子查询三、 带有ANY（SOME）或ALL谓词的子查询,三、带有ANY（SOME）或ALL谓词的子查询,谓词语义 ANY：任意一个值 ALL：所有值,带有ANY（SOME）或AL
17、L谓词的子查询 （续）,需要配合使用比较运算符 ANY 大于子查询结果中的某个值 ALL 大于子查询结果中的所有值 = ANY 大于等于子查询结果中的某个值 = ALL 大于等于子查询结果中的所有值 ）ANY 不等于子查询结果中的某个值 !=（或）ALL 不等于子查询结果中的任何一个值,带有ANY（SOME）或ALL谓词的子查询 （续）,例42 查询其他系中比计算机科学某一学生年龄小的学生姓名和年龄SELECT Sname，SageFROM StudentWHERE Sage CS ; /*父查询块中的条件 */,带有ANY（SOME）或ALL谓词的子查询 （续）,结果：执行过程：1.RDBM
18、S执行此查询时，首先处理子查询，找出CS系中所有学生的年龄，构成一个集合(20，19)2. 处理父查询，找所有不是CS系且年龄小于 20 或 19的学生,带有ANY（SOME）或ALL谓词的子查询 （续）,用聚集函数实现例42 SELECT Sname，SageFROM StudentWHERE Sage CS ;,带有ANY（SOME）或ALL谓词的子查询 （续）,例43 查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄。方法一：用ALL谓词SELECT Sname，SageFROM StudentWHERE Sage CS ;,带有ANY（SOME）或ALL谓词的子查询 （续）,
19、方法二：用聚集函数SELECT Sname，SageFROM StudentWHERE Sage CS ;,带有ANY（SOME）或ALL谓词的子查询 （续）,表3.5 ANY（或SOME），ALL谓词与聚集函数、IN谓词的等价转换关系,3.4.3 嵌套查询,一、带有IN谓词的子查询 二、 带有比较运算符的子查询三、 带有ANY（SOME）或ALL谓词的子查询四、 带有EXISTS谓词的子查询,带有EXISTS谓词的子查询(续）,1. EXISTS谓词 存在量词 带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。 若内层查询结果非空，则外层的WHE
20、RE子句返回真值 若内层查询结果为空，则外层的WHERE子句返回假值 由EXISTS引出的子查询，其目标列表达式通常都用* ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义 2. NOT EXISTS谓词 若内层查询结果非空，则外层的WHERE子句返回假值 若内层查询结果为空，则外层的WHERE子句返回真值,带有EXISTS谓词的子查询(续）,例44查询所有选修了1号课程的学生姓名。思路分析： 本查询涉及Student和SC关系 在Student中依次取每个元组的Sno值，用此值去检查SC关系 若SC中存在这样的元组，其Sno值等于此Student.Sno值，并且其Cno= 1，则取此Student.Sname送入结果关系,3.4 数据查询,3.4.1 单表查询 3.4.2 连接查询 3.4.3 嵌套查询3.4.5 Select语句的一般形式,3.4.5 SELECT语句的一般格式,SELECT ALL|DISTINCT 别名 ， 别名 FROM 别名 ， 别名 WHERE GROUP BY HAVING ORDER BY ASC|DESC,下课了。,休息一会儿。,追 求,
