《数据库系统原理教学课件》

1,第4章SQL语言,4.1SQL语言概述4.2数据定义功能与约束4.3数据操纵功能4.4数据查询功能与视图,2,查询语句的基本结构,SELECTFROMWHEREGROUPBYHAVINGORDERBY,3,4.4数据查询功能与视图,5.4.1单表查询5.4.2查询条件的设置5.4.3聚合函数5.4.4分组5.4.5查询结果的排序5.4.6多表连接查询5.4.7子查询5.4.8视图,4,单表查询,数据源只涉及一张表的查询选择列,5,查询语句的结构,SELECTFROM,6,CREATETABLE演员(姓名char(20),工号char(10),性别char(2),PRIMARYKEY(工号),CREATETABLE电影(片名char(60),导演char(20),年代datetime,PRIMARYKEY(片名，年代),CREATETABLE出演(工号char(10),片名char(60),年代datetime,片酬money,PRIMARYKEY(工号,片名,年代),7,演员,电影,出演,8,查询指定的列,查询演员的姓名与性别SELECT姓名，性别FROM演员查询结果,9,查询全部列,查询全体演员的记录SELECT*FROM演员查询结果,10,数据库管理系统中的函数,getdate()取当前的系统日期year(date)取日期时间型参数date的年份month(date)day(date),11,使用常量列和计算列,查询电影的片名以及拍摄距今时间SELECT片名,拍摄距今,year(getdate()-year(年代),年FROM电影查询结果,12,改变列标题,计算列、函数列和常量列的显示结果都没有列标题，通过指定列的别名可以改变查询结果的列标题改变列标题的语法：列名|表达式AS列标题或：列标题列名|表达式,13,改变列标题的示例,查询电影的片名以及拍摄距今时间SELECT片名,year(getdate()-year(年代)AS拍摄距今年限FROM电影查询结果,14,演员,电影,出演,15,查询结果中相同行被自动消除,查询数据库中的电影拍摄年代SELECTyear(年代)AS拍摄年代FROM电影查询结果,16,5.4数据查询功能与视图,5.4.1单表查询5.4.2查询条件的设置5.4.3聚合函数5.4.4分组5.4.5查询结果的排序5.4.6多表连接查询5.4.7子查询5.4.8视图,17,查询语句的结构,SELECTFROMWHERE,18,常用查询条件,19,查询条件：比较运算1,查询女演员的姓名SELECT姓名FROM演员WHERE性别女,20,查询条件：比较运算2,查询拍摄距今超过10年电影的片名SELECT片名FROM电影WHEREyear(getdate()-year(年代)10查询结果,21,字符匹配的条件运算符,用于查找指定列中符合匹配模式的元组列名NOTLIKE匹配串中可包含如下四种通配符_匹配任意一个字符；%匹配0个或多个字符；匹配中的任意一个字符；不匹配中的任意一个字符,22,查询条件的设置:字符匹配1,查询“张”“王”“李”姓演员的情况SELECT姓名FROM演员WHERE姓名LIKE张王李%查询结果,23,查询条件的设置:字符匹配2,查询姓名第2个字不是“菲”或者“优”的演员的情况SELECT姓名FROM演员WHERE姓名LIKE_菲优%查询结果,24,查询条件的设置:字符匹配3,查询姓名由2个字组成的演员的情况SELECT姓名FROM演员WHERE姓名LIKE_查询结果,25,判断空值的条件运算符,空值（NULL）表示不确定的值判断取值为空的语句格式：列名ISNULL判断取值不为空的语句格式：列名ISNOTNULL,26,涉及空值的查询,查询有片酬记录的出演情况,SELECT*FROM出演WHERE片酬ISNOTNULL,查询结果,27,多重条件的组织,在WHERE子句中可以使用逻辑运算符AND和OR来组成多重条件查询用AND连接的条件表示必须全部满足所有的条件的元组才被选中用OR连接的条件表示只要满足其中一个条件的元组即被选中,28,多重条件查询1,查询王姓女演员的情况,SELECT*FROM演员WHERE姓名LIKE王%AND性别女,查询结果,29,多重条件查询2,查询王姓演员和全体男演员的情况SELECT*FROM演员WHERE姓名LIKE王%OR性别男查询结果,30,确定范围的条件运算符,BETWEENAND和NOTBETWEENAND格式列名|表达式NOTBETWEEN下限值AND上限值,31,查询条件的设置:确定范围1,查询拍摄距今5至10年的电影,SELECT片名FROM电影WHEREyear(getdate()year(年代)BETWEEN5AND10,32,查询条件的设置:确定范围2,查询拍摄距今5至10年的电影SELECT片名FROM电影WHEREyear(getdate()year(年代)=5ANDyear(getdate()year(年代)=2,70,上述查询的执行步骤与结果,先用GROUPBY按演员姓名分组再用COUNT(*)统计每组包含的记录条数最后挑选记录条数=2的组，返回对应的演员姓名和该组记录的条数查询结果,71,计算函数与分组的联合使用1,查询梁朝伟的个人片酬总数与个人平均片酬,SELECT演员姓名，SUM(片酬)个人总片酬，AVG(片酬)个人平均片酬FROM出演GROUPBY演员姓名HAVING演员姓名梁朝伟,72,查询结果,73,计算函数与分组的联合使用2,SELECT演员姓名,AVG(片酬)个人平均片酬FROM出演WHEREyear(年代)100000,查询2000年以前个人最低片酬大于100k的演员的个人平均片酬,74,75,上述查询的结果,76,计算函数与分组的联合使用3,SELECT演员姓名,AVG(片酬)个人平均片酬FROM出演GROUPBY演员姓名HAVINGMIN(片酬)100000andyear(年代)2000查询结果,列出演.年代在HAVING子句中无效，因为该列既不包含在聚合函数中，也不包含在GROUPBY子句中。,查询2000年以前个人最低片酬大于100k的演员的个人平均片酬,77,计算函数与分组的联合使用4,查询2000年以前演员的个人平均片酬SELECT演员姓名,AVG(片酬)个人平均片酬FROM出演GROUPBY演员姓名WHEREyear(年代)100000andyear(年代)2000GROUPBY演员姓名查询结果,聚合不应出现在WHERE子句中，除非该聚合位于HAVING子句或选择列表所包含的子查询中，并且要对其进行聚合的列是外部引用。,查询2000年以前个人最低片酬大于100k的演员的个人平均片酬,79,5.4数据查询功能与视图,5.4.1单表查询5.4.2查询条件的设置5.4.3聚合函数5.4.4分组5.4.5查询结果的排序5.4.6多表连接查询5.4.7子查询5.4.8视图,80,查询语句的结构,SELECTFROMWHEREGROUPBYHAVINGORDERBY,81,对查询结果进行排序1,排序子句的格式为：ORDERBYASC|DESC,n为排序的依据列，可以是列名或列的别名ASC表示对列进行升序排序，DESC表示对列进行降序排序默认的排序方式为升序排序,82,对查询结果进行排序2,指定多个排序列时，首先按最前面的列进行排序如果排序后存在多个列值相同的记录，则对这些记录依据后续排序列进行排序,83,对查询结果进行排序示例1,查询全体演员的情况，按姓名排序SELECT*FROM演员ORDERBY姓名查询结果,84,对查询结果进行排序示例2,查询全体演员的情况，先女后男再按姓名排序SELECT*FROM演员ORDERBY性别DESC,姓名查询结果,85,分组与排序的联合使用1,SELECT演员姓名,AVG(片酬)个人平均片酬FROM出演WHEREyear(年代)100000ORDERBY演员姓名DESC查询结果,查询2000年以前个人最低片酬大于100k的演员的个人平均片酬,86,分组与排序的联合使用2,SELECT演员姓名,AVG(片酬)个人平均片酬FROM出演WHEREyear(年代)100000查询结果,在关键字GROUP附近有语法错误。,查询2000年以前个人最低片酬大于100k的演员的个人平均片酬,87,5.4数据查询功能与视图,5.4.1单表查询5.4.2查询条件的设置5.4.3聚合函数5.4.4分组5.4.5查询结果的排序5.4.6多表连接查询5.4.7子查询5.4.8视图,88,多表连接查询,查询结果来源于多张表匹配不同表的公共列，实现多表连接连接的类型内连接外连接,89,连接的格式,.BETWEEN.AND.连接字段：连接谓词中的列名称连接条件中的各连接字段类型必须是可比的，但名字不必是相同的,90,内连接,最常用的比较运算符是FROM表1，表2WHERE.连接列必须是可比较的语义相同,91,演员,电影,出演,92,内连接的使用1,查询演员出演电影的情况,SELECT演员.姓名，出演.片名，出演.年代FROM演员，出演WHERE演员.工号出演.工号,93,演员,出演,查询结果,94,内连接的使用2,查询演员与导演的合作情况,SELECT姓名,导演,电影.片名,year(电影.年代)年代FROM演员，电影，出演WHERE演员.工号=出演.工号AND出演.片名=电影.片名AND出演.年代=电影.年代,95,演员,电影,出演,96,上述查询的结果,97,内连接的使用3自连接,查询与王家卫影片同年拍摄的其他导演的影片,SELECT电影1.片名，电影1.导演，year（电影1.年代）年代FROM电影电影1，电影电影2WHERE电影2.导演=王家卫AND电影2.年代=电影1.年代AND电影1.导演!=王家卫,98,电影1,电影2,查询结果,99,外连接,连接时，输出一方的全部元组和对方的匹配元组FROM表1LEFT|RIGHTOUTERJOIN表2ON,100,左外连接,FROM表1LEFTOUTERJOIN表2ON,输出：表1全部，表2满足连接条件元组,101,右外连接,FROM表1RIGHTOUTERJOIN表2ON,输出:表1满足连接条件元组，表2全部,102,左外连接的使用,查询所有演员出演影片的情况，包括没有出演记录的演员,SELECT姓名,片名FROM演员LEFTOUTERJOIN出演ON演员.工号=出演.工号,103,演员,出演,查询结果,104,右外连接的使用,查询所有演员出演影片的情况，包括没有出演记录的演员,SELECT姓名,片名FROM出演RIGHTOUTERJOIN演员ON演员.工号=出演.工号,105,演员,出演,查询结果,106,5.4数据查询功能与视图,5.4.1单表查询5.4.2查询条件的设置5.4.3聚合函数5.4.4分组5.4.5查询结果的排序5.4.6多表连接查询5.4.7子查询5.4.8视图,107,子查询,SQL的查询块：一个SELECTFROMWHERE的组合体子查询内层查询：嵌套在SELECT、UPDATE或DELETE等语句中的查询块主查询外层查询：包含子查询的语句,108,子查询的用法,子查询要写在圆括号中查询块中不能使用ORDERBY子句引入子查询的保留字为IN子查询通常用在外层查询的WHERE子句或HAVING子句中,109,子查询的用法,带有IN谓词的子查询带有比较运算符的子查询带有ANY（SOME）或ALL谓词的子查询带有EXISTS谓词的子查询,110,演员,电影,出演,111,子查询的使用,查询片酬最低的演员姓名,SELECT演员.姓名FROM演员,出演WHERE演员.工号=出演.工号AND片酬IN(SELECTMIN(片酬)FROM出演),112,演员,电影,出演,113,查询结果,114,WHERESageANY（）ANDSdeptISORDERBYSageDESC,例（带ANY或ALL谓词的子查询）找出比IS系至少一名学生年龄小的其他系学生名单。,SELECTSname，Sage，SdeptFROMStudent,SELECTdistinctSageFROMStudentWHERESdept=IS,115,WHERE（SageALL（）AND（SdeptIS）ORDERBYSageDESC,例查询比IS系所有学生年龄都小的其他各系学生姓名,年龄，系名。,SELECTSageFROMStudentWHERESdept=IS,SELECTSname，Sage，SdeptFROMStudent,116,EXISTS的使用,由EXISTS引出的子查询，其目标列表达式通常都用*，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义EXISTS若内层查询结果非空，则外层的WHERE子句返回真值若内层查询结果为空，则外层的WHERE子句返回假值,117,查询所有选修了1号课程的学生姓名,用嵌套查询SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=1),118,查询所有选修了1号课程的学生姓名,用连接查询SELECTSnameFROMStudent,SCWHEREStudent.Sno=SC.SnoANDSC.Cno=1,119,查询选修了全部课程的学生姓名,SELECTSnameFROMStudentWHERENOTEXISTS（SELECT*FROMCourseWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=ourse.Cno）,集合查询,120,121,例1查找选修了课程1或课程2的学生学号和姓名。,SELECTSC.Sno,SnameFROMSC,StudentWHERE(Cno=2)AND(SC.Sno=Student.Sno),SELECTSC.Sno,SnameFROMSC,StudentWHERE(Cno=1)AND(SC.Sno=Student.Sno),UNION,集合查询就是对几个查询块的结果集进行并、交、差运算。,122,例1查找选修了课程1或课程2的学生学号和姓名。,SELECTSC.Sno,SnameFROMSC,StudentWHERE(Cno=1)OR(Cno=2)AND(SC.Sno=Student.Sno),集合查询就是对几个查询块的结果集进行并、交、差运算。,123,ANDSnoIN（SELECTSnoFROMSCWHERECno=2）,例2查找既选修课程1，又选修课程2的学生集合,这实际上是查找选修了课程1学生，与选修了课程2的学生的交集。,SELECTSnoFROMSCWHERECno=1,124,INTERSECT(SELECTSnoFROMSCWHERECno=2),例2查找既选修课程1，又选修课程2的学生集合,这实际上是查找选修了课程1学生，与选修了课程2的学生的交集。,(SELECTSnoFROMSCWHERECno=1),125,ANDSnoNOTIN（）,例3查找选修了课程1，但不选修课程2的学生。,这实际上是查找选修课程1的学生，与选修课程2的学生的差集：,SELECTSnoFROMSCWHERECno=1,SELECTSnoFROMSCWHERECno=2,126,EXCEPT(SELECTSnoFROMSCWHERECno=2),例3查找选修了课程1，但不选修课程2的学生。,这实际上是查找选修课程1的学生，与选修课程2的学生的差集：,(SELECTSnoFROMSCWHERECno=1),127,