WindowsServer数据库课件-第03章SQL语言.ppt

数据库系统概论AnIntroductiontoDatabaseSystem第三章关系数据库标准语言SQL（4）,复习,1.统计每人不及格的门数,2.查询显示开设课程的名称、及格人数及最高分,3.查询显示所有计算机系学生的姓名，以及他们所选课程和相应的成绩。,复习,单表查询,SELECTSno，count(cno)FROMSCWHEREgrade=60groupbycno,2.查询显示开设课程的名称、及格人数及最高分,3.显示全部计算机系学生的姓名，以及他们所选课程名和相应的成绩。,复习,连接查询,SELECTFROMWHERE,sname,cname,gradestudent,sc,o=oandstudent.sno=sc.snoandsdeptCS,复习,连接查询,SELECT*FROMStudent，SC,4.描述下列语句的含义。,WHEREStudent.Sno=SC.Sno；,Student.Sno，Sname，Ssex，Sage，Sdept，Cno，Grade,SELECTC1.Cno，C2.CpnoFROMCourseC1，CourseC2WHEREC1.Cpno=C2.Cno；,学习目标,了解外连接以及查询实现掌握不相关子查询(IN、比较运算符、ANY、ALL)掌握相关子查询(Exists谓词),3.4查询,3.4.1单表查询3.4.2连接查询3.4.3嵌套查询3.4.4集合查询,广义笛卡尔积查询等值连接(含自然连接)查询非等值连接查询自身连接查询外连接查询复合条件连接查询。,SQL中连接查询的主要类型,4.外连接,(1)外连接与普通连接的区别普通连接操作只输出满足连接条件的元组,外连接操作以指定表为连接主体，将主体表中的全部记录输出显示,【例】将每个学生的基本信息与其所选课程信息对应显示。,结果：Student.SnoSnameSsexSageSdeptCnoGrade0215121李勇男20CS1920215121李勇男20CS2850215121李勇男20CS3880215122刘晨女19IS2900215122刘晨女19IS3800215123王敏女18MA0215125张立男19IS,示例：外连接,SELECT,FROM连接类型JOINJOINONONONWHERE,INNERLEFTRIGHTFULLCROSS,提示：JOIN的顺序和ON连接条件的顺序相反。外连接只能对两个表进行,(2)语法格式,4.外连接,Student,Sc,SELECTstudent.*,sc.*FROMstudentLEFTJOINscONstudent.sno=sc.sno,RIGHT,FULL,例：查询所有学生的个人信息及选课信息。,4.外连接,4.外连接,SELECTsname,gradeFROMstudentJOINscJOINcourseONo=oONstudent.sno=sc.snoWHEREcname=数学,例查询选修“数学”课程的学生的姓名和该课程成绩。,查询显示全部计算机系学生的姓名，以及他们所选课程的门数。,练习,SELECTsname,count(cno)课程门数FROMstudentleftjoinscONstudent.sno=sc.snoWHEREsdeptCSGROUPBYSname,特点：WHERE子句中含多个条件。例35查询选修2号课程且成绩在90分以上所有学生的学号、姓名。SELECTStudent.Sno,student.SnameFROMStudent,SCWHEREStudent.Sno=SC.SnoANDSC.Cno=2ANDSC.Grade90；,5.复合条件连接,特点：连接操作在两个以上的表之间进行例36查询每个学生的学号、姓名、选修的课程名及成绩.SELECTStudent.Sno，Sname，Cname，GradeFROMStudent，SC，CourseWHEREStudent.Sno=SC.SnoandSC.Cno=Course.Cno；,6.多表连接,3.4查询,3.4.1单表查询3.4.2连接查询3.4.3嵌套查询3.4.4集合查询,3.4.3嵌套查询,嵌套查询概述嵌套查询分类及求解方法引出子查询的谓词,(1)一个SELECT-FROM-WHERE语句称为一个查询块(2)将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语条件中的查询称为嵌套查询。,1.嵌套查询概述,例：SELECTSname外层查询/父查询FROMStudentWHERESnoIN（SELECTSno内层查询/子查询FROMSCWHERECno=2);,嵌套查询概述,(3)说明：子查询的限制注意：子查询不能使用ORDERBY子句ORDERBY只对查询最终结果的排序层层嵌套方式反映了SQL语言的结构化不同的DBMS允许的最大嵌套层数不同有些嵌套查询可以用连接查询替代,3.4.3嵌套查询,嵌套查询概述嵌套查询分类及求解方法引出子查询的谓词,(1)不相关子查询：子查询的查询条件不依赖于父查询查询过程：由里向外逐层处理每个子查询在上一级查询处理之前先求解；子查询的结果用于建立父查询的查找条件。,2.嵌套查询的分类,(1)不相关子查询：,2.嵌套查询的分类,例：SELECTSname外层查询/父查询FROMStudentWHERESnoIN（SELECTSno内层查询/子查询FROMSCWHERECno=2);,(2)相关子查询：子查询的查询条件依赖于父查询查询过程：首先取外层查询表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表；然后再取外层表的下一个元组。重复这一过程，直至外层表全部检查完为止。,嵌套查询的分类,3.4.3嵌套查询,嵌套查询概述嵌套查询分类及求解方法引出子查询的谓词,3.引出子查询的谓词,带有IN谓词的子查询带有比较运算符的子查询带有ANY或ALL谓词的子查询带有EXISTS谓词的子查询,例39查询与刘晨在同一个系学习的学生。可以分步实现：确定“刘晨”所在系名SELECTSdeptFROMStudentWHERESname=刘晨；,1.带有IN谓词的子查询,结果为：deptIS,例39查询与刘晨在同一个系学习的学生。查找所有在IS系学习的学生SELECTSno，Sname，SdeptFROMStudentWHERESdept=IS；结果为：SnoSnameSdept0215122刘晨IS0215125张立IS,1.带有IN谓词的子查询(续),1.带有IN谓词的子查询(续),方法1：将第一步查询嵌入到第二步查询的条件中SELECTSno，Sname，SdeptFROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname=刘晨),构造嵌套查询,说明：此查询为不相关子查询；DBMS求解该查询时也是分步去做的。,方法2：也可用自身连接完成本查询要求S1S2SELECTS1.Sno，S1.Sname，S1.SdeptFROMStudentS1，StudentS2WHERES1.Sdept=S2.SdeptANDS2.Sname=刘晨；,1.带有IN谓词的子查询(续),例38查询选修了课程名为“信息系统”的学生学号和姓名,1.带有IN谓词的子查询(续),最后在Student关系中取出Sno和SnameSELECTSno，SnameFROMStudentWHERESnoIN(),SELECTCnoFROMCourseWHERECname=信息系统,首先在Course关系中找出“信息系统”的课程号,结果：3,然后在SC关系中找出选修了3号课程的学生学号SELECTSnoFROMSCWHERECnoIN（）,组合以后的代码：SELECTSno，SnameFROMStudentWHERESnoIN(SELECTSnoFROMSCWHERECnoIN(SELECTCnoFROMCourseWHERECname=信息系统);,1.带有IN谓词的子查询(续),结果：SnoSname0215121李勇0215122刘晨,1.带有IN谓词的子查询(续),另：本例也可用连接查询SELECTSno，SnameFROMStudent，SC，CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.CnoANDCourse.Cname=信息系统；,是指父查询和子查询之间用比较运算符连接的查询适用范围：当能确切知道内层查询返回单值时；内层查询返回值是集合，要与ANY或ALL谓词配合使用后。,2.带有比较运算符的子查询,2.带有比较运算符的子查询(续),例：假设一个学生只可能在一个系学习，并且必须属于一个系，则在例37可以改写为：SELECTSno，Sname，SdeptFROMStudentWHERESdept=(SELECTSdeptFROMStudentWHERESname=刘晨)；注意：(1)子查询一定要用括号括起来,2.带有比较运算符的子查询(续),注意：(2)子查询一定要跟在比较符之后。,SELECTSno，Sname，SdeptFROMStudentWHERE(SELECTSdeptFROMStudentWHERESname=刘晨)=Sdept；,谓词语义：ANY：表示任意一个值，ALL：表示所有值,3.带有ANY或ALL谓词子查询,需要配合使用比较运算符：(=)ANY大于(大于等于)子查询结果中的某个值(=)ALL大于(大于等于)子查询结果中的所有值(=)ANY小于(小于等于)子查询结果中的某个值(=)ALL小于(小于等于)子查询结果中的所有值=ANY等于子查询结果中的某个值=ALL等于子查询结果中的所有值(通常无意义)!=ANY不等于子查询结果中的某个值!=ALL不等于子查询结果中的任何一个值,3.带有ANY或ALL谓词子查询,哪些无实际意义？,例42查询其他系中比信息系任一个(其中某一个)学生年龄小的学生姓名和年龄。SELECTSname，SageFROMStudentWHERESageANY(SELECTSageFROMStudentWHERESdept=IS),执行过程：,3.带有ANY或ALL谓词子查询(续),ANDSdeptIS;,是哪个查询的条件？,ANY和ALL谓词有时可以用集函数实现ANY、ALL与集函数的对应关系,3.带有ANY或ALL谓词子查询(续),IN,-,=MIN,-,NOTIN,=MAX,用ANY实现例39如下：SELECTSname，SageFROMStudentWHERESageIS;,3.带有ANY或ALL谓词子查询(续),用集函数实现例39如下：SELECTSname，SageFROMStudentWHERESageIS;,例43查询其他系中比IS系所有学生年龄都小的学生姓名及年龄。方法一：用ALL谓词SELECTSname,SageFROMStudentWHERESageIS；,3.带有ANY或ALL谓词子查询(续),例40查询其他系中比IS系所有学生年龄都小的学生姓名及年龄。方法二：用集函数SELECTSname，SageFROMStudentWHERESageIS；,3.带有ANY或ALL谓词子查询(续),用聚集函数实现子查询能够减少比较次数，提高查询效率(CPU)。,比较两种方法的效率,3.4.3嵌套查询,带有IN谓词的子查询带有比较运算符的子查询带有ANY或ALL谓词的子查询带有EXISTS谓词的子查询,4.带有EXISTS谓词的子查询,(1)EXISTS谓词(2)NOTEXISTS谓词(3)不同形式的查询间的替换(4)相关子查询的效率(5)用EXISTS/NOTEXISTS实现全称量词(6)用EXISTS/NOTEXISTS实现逻辑蕴函,4.带有EXISTS谓词的子查询,(1)EXISTS谓词相当于存在量词带有EXISTS谓词的子查询不返回具体数据，只产生逻辑值“True”或“False”。由EXISTS引出的子查询，其目标列表达式通常都用*。(2)NOTEXISTS谓词,例44查询所有选修了2号课程的学生姓名。,4.带有EXISTS谓词的子查询,用带in谓词的嵌套查询SELECTSnameFROMStudentWHERESnoin(SELECTSnoFROMSCWHERECno=2),例44查询所有选修了2号课程的学生姓名。,用带EXISTS谓词的嵌套查询SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=2),4.带有EXISTS谓词的子查询,用带EXISTS谓词的嵌套查询SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=2),4.带有EXISTS谓词的子查询,外,内,带EXISTS谓词查询的执行过程：,内,4.带有EXISTS谓词的子查询,外,内,内,内,内,带EXISTS谓词查询的执行过程：,用带EXISTS谓词的嵌套查询SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=2),4.带有EXISTS谓词的子查询,外,内,内,内,内,内,内,带EXISTS谓词查询的执行过程：,用带EXISTS谓词的嵌套查询SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=2),用带EXISTS谓词的嵌套查询SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=2),4.带有EXISTS谓词的子查询,带EXISTS谓词查询的执行过程：,外,内,内,内,内,内,内,用带EXISTS谓词的嵌套查询SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=2),4.带有EXISTS谓词的子查询,带EXISTS谓词查询的执行过程：,内,外,例44查询所有选修了2号课程的学生姓名。,4.带有EXISTS谓词的子查询,本查询涉及Student和SC关系。在Student中依次取每个元组的Sno值，用此值去检查SC关系。若SC中存在这样的元组，其Sno值等于此Student.Sno值，并且其Cno=2，则取此Student.Sname送入结果关系,父查询指针下移，子查询回到首行。否则，子查询移到底部才返回False值,总结：对每一个父查询的元组，都要求解一遍子查询,例44查询所有选修了2号课程的学生姓名。,4.带有EXISTS谓词的子查询,用ExistsSELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=2),用INSELECTSnameFROMStudentWHERESnoin(SELECTSnoFROMSCWHERECno=2),例44查询所有选修了2号课程的学生姓名。,4.带有EXISTS谓词的子查询,用连接查询SELECTSnameFROMStudent,SCWHEREStudent.Sno=SC.SnoANDSC.Cno=2；,例45查询没有选修2号课程的学生姓名。SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=2)；,4.带有EXISTS谓词的子查询,该要求很难用连接查询实现,4.带有EXISTS谓词的子查询,用嵌套查询SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=2),例45查询没有选修2号课程的学生姓名。,4.带有EXISTS谓词的子查询,(3)不同形式的查询间的替换一些带EXISTS或NOTEXISTS谓词的子查询不能被其他形式的子查询等价替换所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。,总结：EXISTS谓词的表达能力高于其他谓词的表达形式的。,例37查询与刘晨在同一个系学习的学生，用带EXISTS谓词的子查询替换：SELECTSno，Sname，SdeptFROMStudentS1WHEREEXISTS(SELECT*FROMStudentWHERESdept=S1.SdeptANDSname=刘晨),4.带有EXISTS谓词的子查询,4.带有EXISTS谓词的子查询,(4)相关子查询的效率由于带EXISTS谓词的相关子查询只关心内层查询是否有返回值，并不需要查具体值，因此其效率并不一定低于其他查询。,4.带有EXISTS谓词的子查询,(5)用EXISTS/NOTEXISTS实现全称量词(难点)SQL语言中没有全称量词（Forall）可以把带有全称量词的谓词转换为等价的带有存在量词的谓词：(x)P(x(P),例43查询选修了全部课程的学生姓名。SELECTSnameFROMStudentWHERENOTEXISTS（SELECT*FROMCourseWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=Course.Cno)）；,4.带有EXISTS谓词的子查询,4.带有EXISTS谓词的子查询,Student,Sc,外,中,内,SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMCourseWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=Course.Cno),SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMCourseWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=Course.Cno),4.带有EXISTS谓词的子查询,Student,Sc,外,中,内,内,SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMCourseWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=Course.Cno),4.带有EXISTS谓词的子查询,Student,Sc,外,中,内,内,内,内,内,内,SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMCourseWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=Course.Cno),4.带有EXISTS谓词的子查询,Student,Sc,外,中,内,内,内,SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMCourseWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=Course.Cno),4.带有EXISTS谓词的子查询,Student,Sc,外,中,内,内,内,内,SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMCourseWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=Course.Cno),4.带有EXISTS谓词的子查询,Student,Sc,外,中,内,内,内,内,内,SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMCourseWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=Course.Cno),4.带有EXISTS谓词的子查询,Student,Sc,外,中,内,内,内,内,内,例：查询选修了课程的学生姓名方法一：SelectsnameFromstudentWhereExists(Select*FromscWherestudent.sno=sc.sno),方法二：SelectDISTINCTsnameFromstudent,scWherestudent.sno=sc.sno,方法三：SelectsnameFromstudentWheresnoin(selectdistinctsnofromsc),请比较：本例中三种方法的查询效率如何？,3.4查询,3.4.1单表查询3.4.2连接查询3.4.3嵌套查询3.4.4集合查询,3.4.4集合查询,标准SQL直接支持的集合操作种类并操作(UNION)一般商用数据库支持的集合操作种类并操作(UNION)交操作(INTERSECT)差操作(EXCEPT),3.4.4集合查询,1.并操作(UNION):参加UNION操作的各结果表的列数必须相同；对应项的数据类型也必须相同。一般格式为：UNION,例48查询计算机系的学生及年龄不大于19岁的学生。方法一：SELECT*FROMStudentWHERESdept=CSUNIONSELECT*FROMStudentWHERESage=19;,3.4.4集合查询,方法二：SELECTDISTINCT*FROMStudentWHERESdept=CSORSage=19；,例49查询选修了课程1或者选修了课程2的学生。方法一：SELECTSnoFROMSCWHERECno=1UNIONSELECTSnoFROMSCWHERECno=2;,3.4.4集合查询,结果有几条记录？,方法二：SELECTDISTINCTSnoFROMSCWHERECno=1ORCno=2;,补充设数据库中有教师表Teacher(Tno,Tname,)查询学校中所有师生的姓名。SELECTSnameFROMStudentUNIONSELECTTnameFROMTeacher;,3.4.4集合查询,还能用前面类似的等价方式表示吗？,2.交操作(INTERSECT)：标准SQL没有提供交操作，但SQLSERVER2008提供。例50查询计算机系的学生与年龄不大于19岁的学生的交集。SELECT*FROMStudentWHERESdept=CSANDSage=19；SELECT*FROMStudentWHERESdept=CSINTERSECTSELECT*FROMStudentWHERESage=19；,3.4.4集合查询,例51查询既选修1号课又选修2号课的学生集合,3.4.4集合查询,SELECTSnoFROMSCWHERECno=1ANDSnoIN(SELECTSnoFROMSCWHERECno=2)；,思考：查询教师和学生同名的集合。,SELECTSnoFROMSCWHERECno=1INTERSECTSELECTSnoFROMSCWHERECno=2,3.差操作(EXCEPT)：标准SQL中没有提供差操作，但SQLSERVER2008提供了此操作。,3.4.4集合查询,例52查询计算机科学系的学生与年龄不大于19岁的学生的差集。,3.4.4集合查询,SELECT*FROMStudentWHERESdept=CSEXCEPTSELECT*FROMStudentWHERESage19；,例53查询学生和教师不重名的学生姓名。,3.4.4集合查询,SELECTSnameFROMStudentEXCEPTSELECTTnameFROMTeacher,SELECTsnameFROMStudentWHEREsnamenotin(selectsnamefromteacher),3.4.4集合查询,SELECT*FROMStudentORDERBYSnoUNIONSELECT*FROMTeacherORDERBYTno；,new,SELECTSnameFROMStudentUNIONSELECTTnameFROMTeacherORDERBYTname；,4.对集合操作结果的排序,4.对集合操作结果的排序ORDERBY子句通常用数字序号指定排序属性；只用于对最终查询结果排序，不能对中间结果排序任何情况下，ORDERBY子句只能出现在最后。,new,3.4.4集合查询,SELECTSnameFROMStudentUNIONSELECTTnameFROMTeacherORDERBYSname,1,SELECT语句的一般格式：SELECTALL|DISTINCT别名FROM别名,别名WHEREGROUPBYHAVINGORDERBYASC|DESC,3.4查询小结,3.5数据更新,3.5.1插入数据3.5.2修改数据3.5.3删除数据,3.5.1插入数据,两种插入数据方式：插入单个元组插入子查询结果,3.5.1插入数据,1.插入单个元组语句格式：INSERTINTO(,)VALUES(,)功能：将新元组插入指定表中。,3.5.1插入数据,INTO子句指定要插入数据的表名及属性列属性列顺序与表定义中的顺序可以不一致省略属性列表：表示插入一条完整元组，且属性列属性与表定义顺序一致指定部分属性列：插入的元组在其余属性列上取空值【省略属性时要注意！】,VALUES子句提供的值必须与INTO子句中的属性匹配值的个数、类型,3.5.1插入数据,注意！DBMS在执行插入语句时会检查所插元组是否破坏表上已定义的完整性规则实体完整性参照完整性用户定义的完整性对于有NOTNULL约束对于有UNIQUE约束对于有值域约束,例1将一个新学生记录插入到Student表中,内容如下：（95020，陈冬，男，IS，18岁）INSERTINTOStudentVALUES(0215128,陈冬,男,IS,18),3.5.1插入数据,例2向SC表插入一条选课记录(0215128,1)INSERTINTOSC(Sno，Cno)VALUES(0215128，1)；新插入的记录在Grade列上取空值。,3.5.1插入数据,2.插入子查询结果语句格式INSERTINTO(,)子查询；功能：将子查询结果插入指定表中。,3.5.1插入数据,例3对每一个系，求学生的平均年龄，并把结果存入数据库。首先建表：CREATETABLEDept_avgage(SdeptCHAR(15),/*定义系名*/AvgageSMALLINT/*定义平均年龄*/);,3.5.1插入数据,例3对每一个系，求学生的平均年龄