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

数据库原理第三章：关系数据库标准语言SQL,授课教师：姜姗,1.自身连接,自身连接：连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，称为表的自身连接。由于所有属性名都是同名属性，因此必须使用别名前缀。需要给表起别名以示区别。,例1：查询每一门课的间接先修课（即先修课的先修课）first表（course表）second表（course表）,完成上述查询的sql语句为：SELECTo,second.cpnoFROMcoursefirst,coursesecondWHEREfirst.cpno=o,例2：查询和“李勇”一个院系的其他学生的基本情况a表（student表）b表（student表）,例2：查询和“李勇”一个院系的学生的基本情况SELECTb.*FROMStudenta,studentbWHEREa.Sname=李勇anda.Sdept=b.Sdeptandb.Sname李勇,使用JOIN关键字实现表的连接,在SELECT语句的FROM子句中，通过指定不同类型的JOIN关键字可以实现不同的表的连接方式，而在ON关键字后指定连接条件。,基本连接语法如下：SELECTcolumn_listFROMjoin_tableJOIN_TYPEjoin_tableON(join_condition)说明如下。join_table：指出参与连接操作的表名。JOIN_TYPE为连接类型，可分为3种：内部连接、外部连接和交叉连接。,1.内部连接,内部连接INNERJOIN内部连接是使用比较运算符比较要连接列中的值的连接，下面通过实例说明其使用。例：从student和sc表中查询每个学生的各门选修课成绩信息，要求返回的结果中包含学生的学号、姓名、性别、选修课程序号及成绩。Selecta.sno,sname,ssex,cno,gradeFromstudentainnerjoinscbOna.sno=b.sno,练习：查询所有考试成绩及格的学生的成绩信息，结果中包含学生的学号、姓名、性别、选修课程编号、成绩，并按照成绩进行降序排列。,Selecta.sno,sname,ssex,o,gradeFromstudentainnerjoinscbOna.sno=b.snoandGrade=60orderbyb.Grade,2.外连接,在通常的连接操作中，只有满足连接条件的行才能作为结果输出，但有些情况下，也需要输出其他相关选项，这就用到了外连接。例3：查询每个学生及其选修课程的情况。,例3：查询每个学生及其选修课程的情况。SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudent,SCWHEREStudent.Sno=SC.Sno参见P82，查询结果中没有200215123和200215125这两个学生的信息。,SQL扩展了以JOIN关键字指定连接的表达式，使表的连接运算能力有了增强（关键字Outer可省略）。外连接可分为左连接（LEFT）、右连接（RIGHT）和全连接（FULL）三种：,R,S,外连接,左外连接,右外连接,左外连接格式：SELECT,FROMLEFTOUTERJOINON右外连接格式：SELECT,FROMRIGHTOUTERJOINON外连接格式：SELECT,FROMFULLOUTERJOINON,例1：查询所有学生基本情况及他们选修课程的成绩（包括没选修任何课的学生）。Selectstudent.*,gradeFromstudentleftouterjoinscOn（student.sno=sc.sno）,Selectstudent.*,gradeFromstudentleftjoinscOn（student.sno=sc.sno）,例2：查询所有选修课程的学生的学号、姓名、课程号及成绩。Selectstudent.sno,sname,cno,gradeFromstudentrightouterjoinscOn（student.sno=sc.sno）,Selectstudent.sno,sname,cno,gradeFromstudentrightjoinscOn（student.sno=sc.sno）,4.多表连接,多表连接：两个以上的表进行连接例3.54查询每个学生的学号、姓名、选修的课程名及成绩SELECTStudent.Sno,Sname,Cname,GradeFROMStudent,SC,Course/*多表连接*/WHEREStudent.Sno=SC.SnoANDSC.Cno=Course.Cno;,3.4.3嵌套查询,SQL允许SELECT多层嵌套使用，即一个子查询中还可以嵌套子查询，用来表示复杂的查询，从而增强SQL的查询能力。以这种层层嵌套的方式来构造查询语句正是SQL中“结构化”的含义所在。,在where子句或having子句所表示的条件中，可以使用另一个查询的结果（即一个查询块：一个SELECT-FROM-WHERE语句称为一个查询块）作为条件的一部分，这种将一个查询块嵌套在另一个查询块的where子句或having子句的条件中的查询称为嵌套查询。例如：SELECTsnameFROMStudent上层模块：父查询，外层查询WHERESnoin（SELECTsnoFROMsc下层模块：子查询，内层查询WHEREcno=2）;,Sql语言允许有多层嵌套查询。即一个子查询中还允许嵌套其他子查询。需要特别指出的是子查询的select语句中不能使用orderby子句，orderby子句只能对最终查询结果排序。子查询通常与IN、比较运算符及EXISTS谓词结合使用。,1、带有In谓词的子查询,在嵌套查询中，子查询的结果往往是一个集合，所以谓词IN是嵌套查询中最常用的谓词。IN子查询用于进行一个给定值是否在子查询结果集中的判断。,例1：查询与“刘晨”在一个系学习的学生。,SELECTb.*FROMStudenta,studentbWHEREa.Sname=刘晨anda.Sdept=b.Sdeptandb.Sname刘晨,SELECT*FROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHEREsname=刘晨)andSname刘晨,步骤：先找出刘晨对应的院系，再通过院系找其余学生。,例2：查询选修了课程名为“信息系统”的学生学号和姓名。SELECTsno,snameFROMStudentWHEREsnoIN(SELECTsnoFROMscWHEREcnoin（SELECTcnoFROMcourseWHEREcname=信息系统）),上述查询可以用连接查询实现：SELECTstudent.sno,snameFROMStudent,sc,courseWHEREStudent.sno=o=ame=信息系统,练习：1、查询选修课程号为2的学生姓名；2、查询没有选修课程的学生的基本情况；3、查询至少有一个成绩在80分以上的学生的基本情况；4、查询没有选修“数据库”课程的学生的基本情况。,1、查询选修课程号为2的学生姓名；,SELECTsnameFROMStudent,scWHEREStudent.Sno=SC.So=2,SELECTSnameFROMStudentWHERESnoIN(SELECTSnoFROMSCWHERECno=2),步骤：先找出2号课程对应的学号，再通过学号找学生姓名。,2、查询没有选修课程的学生的基本情况；,SELECT*FROMStudentWHERESnonotin(SELECTsnoFROMsc),3、查询至少有一个成绩在80分以上的学生的基本情况；,SELECT*FROMStudentWHERESnoin(SELECTsnoFROMscWHEREgrade=80),4、查询没有选修“数据库”课程的学生的基本情况。,SELECT*FROMStudentWHERESnonotin(SELECTsnoFROMscWHEREcnoin(SELECTcnofromcourseWHEREcname=数据库),2、带有比较运算符的子查询,带有比较运算符的子查询是指父查询与子查询之间用比较运算符连接。当用户确切知道内层查询返回的是单值时，可以用，=，!=或等比较运算符。例如39：查询与“刘晨”在一个系学习的学生。由于一个学生只可能在一个系学习，也就是说内查询的结果是一个值，因此可以用=代替inSELECT*FROMStudentWHERESdeptIN（或者用=）(SELECTSdeptFROMStudentWHEREsname=刘晨)andSname刘晨,例1：找出一个学生超过他选修课程平均成绩的课程号。x表（sc表）y表（sc表）,例1：找出一个学生超过他选修课程平均成绩的课程号。SELECTsno,cnoFROMscxWHEREgrade=(selectavg(grade)fromscywherey.sno=x.sno),练习1、查询和“李勇”不在一个院系的学生的基本情况；2、查询年龄高于平均年龄的学生的基本信息。,1、查询和“李勇”不在一个院系的学生的基本情况；,SELECT*FROMStudentWHERESage(SELECTavg(sage)FROMstudent),2、查询年龄高于平均年龄的学生的基本信息；,SELECT*FROMStudentWHERESdept(SELECTSdeptFROMStudentWHEREsname=李勇),3、带有ANY或ALL谓词的子查询,子查询返回单值时可以用比较运算符，但返回多值时要用ANY或ALL谓词修饰。而使用ANY或ALL谓词时必须同时使用比较运算符。,需要配合使用比较运算符ANY大于子查询结果中的某个值ALL大于子查询结果中的所有值=ANY大于等于子查询结果中的某个值=ALL大于等于子查询结果中的所有值）ANY不等于子查询结果中的某个值!=（或）ALL不等于子查询结果中的任何一个值,例：查询其他系中比计算机科学系（CS）某一学生年龄小的学生姓名和年龄；,例1：查询其他系中比计算机科学系（CS）某一学生年龄小的学生姓名和年龄；SELECTSname,SageFROMStudentWHERESageCS,SELECTSname,SageFROMStudentWHERESageCS,例2：查询其他系中比计算机科学系（CS）所有学生年龄都小的学生姓名和年龄；SELECTSname,SageFROMStudentWHERESageCS,SELECTSname,SageFROMStudentWHERESageCS,4、带有EXISTS谓词的子查询,EXISTS代表存在量词。带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。若内层查询结果非空，则外层的WHERE子句返回真值若内层查询结果为空，则外层的WHERE子句返回假值由EXISTS引出的子查询，其目标列表达式通常都用*，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义。,例1：查询参加选修的学生信息。SELECT*FROMstudentWHEREEXISTS(SELECT*FROMscWHEREsc.sno=student.sno),SELECT*FROMStudentWHERESnoIN(SELECTSnoFROMSC),例2：查询未参加选修的学生信息。SELECT*FROMstudentWHERENOTEXISTS(SELECT*FROMscWHEREsc.sno=student.sno),SELECT*FROMStudentWHERESnoNOTIN(SELECTSnoFROMSC),例3：查询没有选修1号课程的学生姓名。SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=1),SELECTSnameFROMStudentWHERESnoNOTIN(SELECTSnoFROMSCWHERECno=1),三、集合查询,SELECT语句的查询结果是元组的集合，所以多个SELECT语句的结果可进行集合操作。集合操作主要包括：并操作UNION交操作INTERSECT差操作EXCEPT注意：参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同。,例1：查询计算机科学系（CS）的学生及年龄不大于19岁的学生。SELECTDISTINCT*FROMStudentWHERESdept=CSORSage=19,SELECT*FROMStudentWHERESdept=CSUNIONSELECT*FROMStudentWHERESage=19,UNION：将多个查询结果合并起来时，系统自动去掉重复元组。UNIONALL：将多个查询结果合并起来时，保留重复元组，包含空值。,例2：查询选修了课程1或者选修了课程2的学生。SELECT*FROMSCWHERECno=1UNIONSELECT*FROMSCWHERECno=2,例3：查询计算机科学系（CS）的学生与年龄不大于19岁的学生的交集。SELECTDISTINCT*FROMStudentWHERESdept=CSandSage=19,SELECT*FROMStudentWHERESdept=CSINTERSECTSELECT*FROMStudentWHERESage=19,例4：查询选修了课程1又选修了课程2的学生。SELECT*FROMSCWHERECno=1INTERSECTSELECT*FROMSCWHER