数据库系统概论(王珊第五版)第十讲

数据库原理数据库原理 第三章：关系数据库标准语言第三章：关系数据库标准语言SQLSQL授课教师：姜姗1.自身连接自身连接v自身连接：连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，称为表的自身连接。v由于所有属性名都是同名属性，因此必须使用别名前缀。需要给表起别名以示区别。v例1：查询每一门课的间接先修课（即先修课的先修课）first表（course表）second表（course表）cnocnamecpnoccredit1数据库542数学23信息系统144操作系统635数据结构746数据处理27Pascal语言64cnocnamecpnoccredit1数据库542数学23信息系统144操作系统635数据结构746数据处理27Pascal语言64v完成上述查询的sql语句为：SELECT o,second.cpnoFROM course first,course secondWHERE first.cpno=ov例例2：查询和：查询和“李勇李勇”一个院系的一个院系的其他学生其他学生的基本情况的基本情况 a表（student表）b表（student表）学号sno姓名sname性别ssex年龄sage所在系sdept200215121李勇男20CS200215122刘晨女19CS200215123王敏女18MA200215125张立男19IS学号sno姓名sname性别ssex年龄sage所在系sdept200215121李勇男20CS200215122刘晨女19CS200215123王敏女18MA200215125张立男19ISv例例2：查询和：查询和“李勇李勇”一个院系的学生的基本情况一个院系的学生的基本情况 SELECT b.*FROM Student a,student bWHERE a.Sname=李勇 and a.Sdept=b.Sdept and b.Sname李勇 使用使用JOIN关键字实现表的连接关键字实现表的连接v在SELECT语句的FROM子句中，通过指定不同类型的JOIN关键字可以实现不同的表的连接方式，而在ON关键字后指定连接条件。v基本连接语法如下：SELECTcolumn_listFROMjoin_tableJOIN_TYPEjoin_tableON(join_condition)说明如下。join_table：指出参与连接操作的表名。JOIN_TYPE为连接类型，可分为3种：内部连接、外部连接和交叉连接。1.内部连接内部连接v内部连接INNERJOIN内部连接是使用比较运算符比较要连接列中的值的连接，下面通过实例说明其使用。例：从student和sc表中查询每个学生的各门选修课成绩信息，要求返回的结果中包含学生的学号、姓名、性别、选修课程序号及成绩。Select a.sno,sname,ssex,cno,gradeFrom student a inner join sc bOn a.sno=b.snov练习：查询所有考试成绩及格的学生的成绩信息，结果中包含学生的学号、姓名、性别、选修课程编号、成绩，并按照成绩进行降序排列。Select a.sno,sname,ssex,o,gradeFrom student a inner join sc bOn a.sno=b.sno and Grade=60order by b.Grade2.外连接外连接v在通常的连接操作中，只有满足连接条件的行才能作为结果输出，但有些情况下，也需要输出其他相关选项，这就用到了外连接。v例3：查询每个学生及其选修课程的情况。SnoSnameSsexSageSdeptCnoGrade200215121李勇男20CS192200215121李勇男20CS285200215121李勇男20CS388200215122刘晨女19CS290200215122刘晨女19CS380200215123王敏女18MA200215125张立男19ISv例3：查询每个学生及其选修课程的情况。SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM Student,SC WHERE Student.Sno=SC.Sno 参见P82，查询结果中没有200215123和200215125这两个学生的信息。vSQL扩展了以JOIN关关键键字字指定连接的表达式，使表的连接运算能力有了增强（关键字Outer 可省略）。v外连接可分为左连接（LEFT）、右连接（RIGHT）和全连接（FULL）三种：ABCBDa1b12b15a1b24b26a2b36b47a2b48b58RSABCDa1b125a1b246a2b36nulla2b487nullb5null8外连接外连接ABCDa1b125a1b246a2b36nulla2b487左外连接左外连接ABCDa1b125a1b246a2b487nullb5null8右外连接右外连接左外连接格式：左外连接格式：SELECT,FROM LEFT OUTER JOIN ON 右外连接格式：右外连接格式：SELECT,FROM RIGHT OUTER JOIN ON 外连接格式：外连接格式：SELECT,FROM FULL OUTER JOIN ON 例1：查询所有学生基本情况及他们选修课程的成绩（包括没选修任何课的学生）。Select student.*,gradeFrom student left outer join sc On（student.sno=sc.sno）Select student.*,gradeFrom student left join sc On（student.sno=sc.sno）例2：查询所有选修课程的学生的学号、姓名、课程号及成绩。Select student.sno,sname,cno,gradeFrom student right outer join sc On（student.sno=sc.sno）Select student.sno,sname,cno,gradeFrom student right join sc On（student.sno=sc.sno）4.多表连接多表连接v多表连接：两个以上的表进行连接例3.54查询每个学生的学号、姓名、选修的课程名及成绩 SELECT Student.Sno,Sname,Cname,Grade FROM Student,SC,Course /*多表连接*/WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno;3.4.3 嵌套查询嵌套查询vSQL允许SELECT多层嵌套使用，即一个子查询中还可以嵌套子查询，用来表示复杂的查询，从而增强SQL的查询能力。v以这种层层嵌套的方式来构造查询语句正是SQL中“结构化结构化”的含义所在。v在where子句或having子句所表示的条件中，可以使用另一个查询的结果（即一个查询块：一个SELECT-FROM-WHERE 语句称为一个查询块）作为条件的一部分，这种将一个查询块嵌套在另一个查询块的where子句或having子句的条件中的查询称为嵌套查询。v例如：SELECT snameFROM Student 上层模块：父查询，外层查询 WHERE Sno in （SELECT sno FROM sc 下层模块：子查询，内层查询 WHERE cno=2）;vSql语言允许有多层嵌套查询。即一个子查询中还允许嵌套其他子查询。需要特别指出的是子查询的子查询的select 语句中不能使用语句中不能使用order by 子句子句，order by子句只能对最终查询结果排序。v子查询通常与IN、比较运算符及、比较运算符及EXISTS谓词结合使谓词结合使用。用。1、带有、带有In谓词的子查询谓词的子查询 v在嵌套查询中，子查询的结果往往是一个集合，所以谓词IN是嵌套查询中最常用的谓词。vIN子查询用于进行一个给定值是否在子查询结果集中的判断。例例1 1：查询与查询与“刘晨刘晨”在一个系学习的学生。在一个系学习的学生。SELECT b.*FROM Student a,student bWHERE a.Sname=刘晨 and a.Sdept=b.Sdept and b.Sname刘晨 SELECT*FROM StudentWHERE Sdept IN (SELECT Sdept FROM Student WHERE sname=刘晨)and Sname刘晨 步骤：先找出刘晨对应的院系，再通过院系找其余学生。步骤：先找出刘晨对应的院系，再通过院系找其余学生。v例2：查询选修了课程名为“信息系统”的学生学号和姓名。SELECT sno,sname FROM Student WHERE sno IN (SELECT sno FROM sc WHERE cno in （SELECT cno FROM course WHERE cname=信息系统）)v上述查询可以用连接查询实现：SELECT student.sno,sname FROM Student,sc,course WHERE Student.sno=sc.sno and o=o and ame=信息系统 练习：1、查询选修课程号为2的学生姓名；2、查询没有选修课程的学生的基本情况；3、查询至少有一个成绩在80分以上的学生的基本情况；4、查询没有选修“数据库”课程的学生的基本情况。1 1、查询选修课程号为、查询选修课程号为2 2的学生姓名；的学生姓名；SELECT snameFROM Student,scWHERE Student.Sno=SC.Sno and o=2SELECT SnameFROM StudentWHERE Sno IN (SELECT Sno FROM SC WHERE Cno=2)步骤：先找出步骤：先找出2号课程对应的学号，再通过学号找学生姓名。号课程对应的学号，再通过学号找学生姓名。2 2、查询没有选修课程的学生的基本情况；查询没有选修课程的学生的基本情况；SELECT*FROM StudentWHERE Sno not in (SELECT sno FROM sc)3 3、查询至少有一个成绩在查询至少有一个成绩在80分以上的学生的基本情况；分以上的学生的基本情况；SELECT *FROM StudentWHERE Sno in (SELECT sno FROM sc WHERE grade=80)4 4、查询没有选修查询没有选修“数据库数据库”课程的学生的基本情况课程的学生的基本情况。SELECT *FROM StudentWHERE Sno not in (SELECT sno FROM sc WHERE cno in(SELECT cno from course WHERE cname=数据库)2、带有比较运算符的子查询、带有比较运算符的子查询v带有比较运算符的子查询是指父查询与子查询之间用比较运算符连接。当用户确切知道内层查询返回的是单值时，可以用，=，!=或 等比较运算符。v例如39：查询与“刘晨”在一个系学习的学生。由于一个学生只可能在一个系学习，也就是说内查询的结果是一个值，因此可以用=代替in SELECT*FROM Student WHERE Sdept IN（或者用 =）(SELECT Sdept FROM Student WHERE sname=刘晨)and Sname刘晨 例1：找出一个学生超过他选修课程平均成绩的课程号。x表 （sc表）y表（sc表）学号sno课程号cno成绩grade200215121192200215121285200215121388200215122290200215122380学号sno课程号cno成绩grade200215121192200215121285200215121388200215122290200215122380例1：找出一个学生超过他选修课程平均成绩的课程号。SELECT sno,cno FROM sc x WHERE grade=(select avg(grade)from sc y where y.sno=x.sno)练习练习1、查询和、查询和“李勇李勇”不在一个院系的学生的基本情况；不在一个院系的学生的基本情况；2、查询年龄高于平均年龄的学生的基本信息。、查询年龄高于平均年龄的学生的基本信息。1、查询和、查询和“李勇李勇”不在一个院系的学生的基本情况；不在一个院系的学生的基本情况；SELECT*FROM StudentWHERE Sage(SELECT avg(sage)FROM student)2、查询年龄高于平均年龄的学生的基本信息；、查询年龄高于平均年龄的学生的基本信息；SELECT*FROM StudentWHERE Sdept (SELECT Sdept FROM Student WHERE sname=李勇)3、带有、带有ANY或或ALL谓词的子查询谓词的子查询v子查询返回单值时可以用比较运算符，但返回多值时要用ANY或ALL谓词修饰。v而使用ANY或ALL谓词时必须同时使用比较运算符。v需要配合使用比较运算符 ANY大于子查询结果中的某个值大于子查询结果中的某个值 ALL大于子查询结果中的所有值 ANY小于子查询结果中的某个值小于子查询结果中的某个值 =ANY 大于等于子查询结果中的某个值大于等于子查询结果中的某个值 =ALL大于等于子查询结果中的所有值=ANY 小于等于子查询结果中的某个值小于等于子查询结果中的某个值 =ALL小于等于子查询结果中的所有值=ANY等于子查询结果中的某个值等于子查询结果中的某个值 =ALL等于子查询结果中的所有值（通常没实际意义）!=（或）ANY 不等于子查询结果中的某个值不等于子查询结果中的某个值!=（或）ALL不等于子查询结果中的任何一个值例：查询其他系中比计算机科学系（例：查询其他系中比计算机科学系（CS）某一学生某一学生年龄年龄小的学生姓名和年龄；小的学生姓名和年龄；学号sno姓名sname性别ssex年龄sage所在系sdept200215121李勇男20CS200215122刘晨女19CS200215123王敏女18MA200215125张立男19IS例例1：查询其他系中比计算机科学系（：查询其他系中比计算机科学系（CS）某一学生某一学生年龄小的学生姓名和年龄；年龄小的学生姓名和年龄；SELECT Sname,Sage FROM Student WHERE Sage ANY (SELECT Sage FROM Student WHERE Sdept=CS)AND Sdept CSSELECT Sname,SageFROM StudentWHERE Sage (SELECT MAX(Sage)FROM Student WHERE Sdept=CS)AND Sdept CS例例2：查询其他系中比计算机科学系（：查询其他系中比计算机科学系（CS）所有学生所有学生年龄都小的学生姓名和年龄；年龄都小的学生姓名和年龄；SELECT Sname,Sage FROM Student WHERE Sage ALL (SELECT Sage FROM Student WHERE Sdept=CS)AND Sdept CSSELECT Sname,SageFROM StudentWHERE Sage (SELECT MIN(Sage)FROM Student WHERE Sdept=CS)AND Sdept CS4、带有、带有EXISTS谓词的子查询谓词的子查询vEXISTS代表存在量词。带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。若内层查询结果非空，则外层的WHERE子句返回真值若内层查询结果为空，则外层的WHERE子句返回假值v由EXISTS引出的子查询，其目标列表达式通常都用*，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义。v例例1：查询参加选修的学生信息。：查询参加选修的学生信息。SELECT *FROM student WHERE EXISTS (SELECT*FROM sc WHERE sc.sno=student.sno)SELECT *FROM StudentWHERE Sno IN (SELECT Sno FROM SC )v例例2：查询未参加选修的学生信息。：查询未参加选修的学生信息。SELECT *FROM student WHERE NOT EXISTS (SELECT*FROM sc WHERE sc.sno=student.sno)SELECT *FROM StudentWHERE Sno NOT IN (SELECT Sno FROM SC )例例3：查询没有选修：查询没有选修1号课程的学生姓名。号课程的学生姓名。SELECT SnameFROM StudentWHERE NOT EXISTS (SELECT*FROM SC WHERE Sno=Student.Sno AND Cno=1)SELECT SnameFROM StudentWHERE Sno NOT IN (SELECT Sno FROM SC WHERE Cno=1)三、集合查询三、集合查询vSELECT语句的查询结果是元组的集合，所以多个SELECT语句的结果可进行集合操作。v集合操作主要包括：并操作并操作 UNION交操作交操作 INTERSECT差操作差操作 EXCEPTv注意：参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同。例1：查询计算机科学系（CS）的学生及年龄不大于19岁的学生。SELECT DISTINCT *FROM Student WHERE Sdept=CS OR Sage=19SELECT *FROM StudentWHERE Sdept=CSUNION SELECT*FROM StudentWHERE Sage=19UNION：将多个查询结果合并起来时，系统自动去掉重复元组。UNION ALL：将多个查询结果合并起来时，保留重复元组，包含空值。例例2 2：查询选修了课程：查询选修了课程1 1或者选修了课程或者选修了课程2 2的学生。的学生。SELECT*FROM SC WHERE Cno=1 UNION SELECT*FROM SC WHERE Cno=2例例3 3：查询计算机科学系（：查询计算机科学系（CSCS）的学生与年龄不大于）的学生与年龄不大于1919岁岁的学生的交集。的学生的交集。SELECT DISTINCT *FROM StudentWHERE Sdept=CS and Sage=19SELECT*FROM StudentWHERE Sdept=CSINTERSECTSELECT*FROM StudentWHERE Sage=19v例例4：查询选修了课程：查询选修了课程1又选修了课程又选修了课程2的学生。的学生。SELECT *FROM SC WHERE Cno=1 INTERSECT SELECT *FROM SC WHERE Cno=2例例5 5：查询计算机科学系（：查询计算机科学系（CSCS）的学生与年龄不大于）的学生与年龄不大于1919岁的学生的差集。岁的学生的差集。SELECT*FROM Student WHERE Sdept=CS EXCEPT SELECT *FROM Student WHERE Sage 19SELECT语句的基本格式语句的基本格式SELECT ALL|DISTINCT 别名别名 ，别名别名 FROM 别名别名 ，别名别名 WHERE GROUP BY ,HAVING 条件表达式条件表达式 ORDER BY ASC|DESC,ASC|DESC 总结总结v连接查询以JOIN关键字指定的外连接v嵌套查询带有In谓词的子查询带有比较运算符的子查询带有EXISTS谓词的子查询v集合查询并操作 UNION，交操作 INTERSECT，差操作 EXCEPT