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

1,第4章 SQL语言,4.1 SQL语言概述 4.2 数据定义功能与约束 4.3 数据操纵功能 4.4 数据查询功能与视图,2,查询语句的基本结构,SELECT FROM WHERE GROUP BY HAVING ORDER BY ,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,查询语句的结构,SELECT FROM ,6,CREATE TABLE 演员( 姓名 char ( 20 ), 工号 char ( 10 ), 性别 char (2), PRIMARY KEY (工号 ) ),CREATE TABLE 电影( 片名 char ( 60 ), 导演 char ( 20 ), 年代 datetime, PRIMARY KEY (片名，年代 ) ),CREATE TABLE 出演( 工号 char ( 10 ), 片名 char ( 60 ), 年代 datetime, 片酬 money, PRIMARY KEY (工号,片名,年代 ) ),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,查询结果中相同行被自动消除,查询数据库中的电影拍摄年代 SELECT year(年代) 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,查询语句的结构,SELECT FROM WHERE ,18,常用查询条件,19,查询条件：比较运算1,查询女演员的姓名 SELECT 姓名 FROM 演员 WHERE 性别女,20,查询条件：比较运算2,查询拍摄距今超过10年电影的片名 SELECT 片名 FROM 电影 WHERE year(getdate()-year(年代) 10 查询结果,21,字符匹配的条件运算符,用于查找指定列中符合匹配模式的元组 列名 NOT LIKE 匹配串中可包含如下四种通配符 _ 匹配任意一个字符； % 匹配0个或多个字符； 匹配 中的任意一个字符； 不匹配 中的任意一个字符,22,查询条件的设置:字符匹配 1,查询 “张”“王”“李”姓演员的情况 SELECT 姓名 FROM 演员 WHERE 姓名 LIKE 张王李% 查询结果,23,查询条件的设置:字符匹配 2,查询姓名第2个字不是“菲”或者“优” 的演员的情况 SELECT 姓名 FROM 演员 WHERE 姓名 LIKE _菲优% 查询结果,24,查询条件的设置:字符匹配 3,查询姓名由2个字组成的演员的情况 SELECT 姓名 FROM 演员 WHERE 姓名 LIKE _ _ 查询结果,25,判断空值的条件运算符,空值（NULL）表示不确定的值 判断取值为空的语句格式： 列名 IS NULL 判断取值不为空的语句格式： 列名 IS NOT NULL,26,涉及空值的查询,查询有片酬记录的出演情况,SELECT * FROM 出演 WHERE 片酬 IS NOT NULL,查询结果,27,多重条件的组织,在WHERE子句中可以使用逻辑运算符AND和OR来组成多重条件查询 用AND连接的条件表示必须全部满足所有的条件的元组才被选中 用OR连接的条件表示只要满足其中一个条件的元组即被选中,28,多重条件查询 1,查询王姓女演员的情况,SELECT * FROM 演员 WHERE 姓名 LIKE 王% AND 性别女,查询结果,29,多重条件查询 2,查询王姓演员和全体男演员的情况 SELECT * FROM 演员 WHERE 姓名 LIKE 王% OR 性别男 查询结果,30,确定范围的条件运算符,BETWEENAND和 NOT BETWEENAND 格式 列名 | 表达式 NOT BETWEEN 下限值 AND 上限值,31,查询条件的设置:确定范围 1,查询拍摄距今5至10年的电影,SELECT 片名 FROM 电影 WHERE year(getdate()year( 年代) BETWEEN 5 AND 10,32,查询条件的设置:确定范围 2,查询拍摄距今5至10年的电影 SELECT 片名 FROM 电影 WHERE year(getdate()year( 年代) = 5 AND year(getdate()year( 年代) = 10,33,查询条件的设置:确定范围 3,查询拍摄距今不到5年或者10年以上的电影 SELECT 片名 FROM 电影 WHERE year(getdate()year( 年代) NOT BETWEEN 5 AND 10 查询结果,34,查询条件的设置:确定范围 4,查询拍摄距今不到5年或者10年以上的电影 SELECT 片名 FROM 电影 WHERE year(getdate()year( 年代) 10,35,集合的条件运算符,IN和NOT IN 格式 列名 NOT IN （集合）,36,查询条件的设置:确定集合 1,查询拍摄距今为5，10或者15年的电影 SELECT 片名 FROM 电影 WHERE year(getdate() - year( 年代) IN (5, 10, 15) 查询结果,37,查询条件的设置:确定集合 2,查询拍摄距今为5，10或者15年的电影 SELECT 片名 FROM 电影 WHERE year(getdate()year( 年代) = 5 OR year(getdate()year( 年代) = 10 OR year(getdate()year( 年代) = 15,38,查询条件的设置:确定集合 3,查询拍摄距今年限不是5，10或者15的电影 SELECT 片名 FROM 电影 WHERE year(getdate() - year( 年代) NOT IN (5, 10, 15) 查询结果,39,查询条件的设置:确定集合 4,查询拍摄距今年限不是5，10或者15的电影 SELECT 片名 FROM 电影 WHERE year(getdate()year( 年代) != 5 AND year(getdate()year( 年代) != 10 AND year(getdate()year( 年代) != 15,40,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 视图,41,使用计算函数汇总数据,计算函数= 集合函数= 聚合函数= 聚集函数 对一组值进行计算并返回一个单值,42,SQL提供的计算函数,COUNT（ * ）: 统计表中元组个数 COUNT（ ）: 统计本列列值个数 MAX（ ）：求列值最大值 MIN（ ）：求列值最小值 SUM（ ）: 计算列值总和 AVG（ ）: 计算列值平均值,43,使用计算函数的注意事项,在SELECT，HAVING子句中使用 计算函数不能直接出现在WHERE子句中 如果使用了计算函数或者分组，那么 SELECT子句中的列必须是计算函数或者是分组依据列 SUM 与AVG只能用于数值类型的列，参数可以是计算列 要求只有一个参数 除COUNT（*）外，其他函数在计算过程中均忽略NULL值,44,出演表例子,45,COUNT函数的使用 1,查询演员出演的总人次数 查询演员出演记录的总条数,SELECT COUNT(*) FROM 出演 SELECT COUNT(演员姓名) FROM 出演 SELECT COUNT(片名) FROM 出演 SELECT COUNT(年代) FROM 出演 SELECT COUNT(片酬) FROM 出演 查询结果,46,COUNT函数的使用 2,查询有片酬记录的总条数,SELECT COUNT(片酬) AS 片酬记录条数 FROM 出演 查询结果,47,COUNT函数的使用 3,查询出演表中不同演员的人数,SELECT COUNT(DISTINCT 演员姓名) AS 演员总数 FROM 出演 查询结果,48,SUM函数的使用 1,查询出演表中支付的片酬总数,SELECT SUM(片酬) AS 总片酬 FROM 出演 查询结果,49,SUM函数的使用 2,查询梁朝伟的片酬总数 SELECT 演员姓名，SUM(片酬) AS 总片酬 FROM 出演 WHERE 演员姓名 梁朝伟 查询结果 列 出演.演员姓名 在选择列表中无效，因为该列未包含在聚合函数中，并且没有 GROUP BY 子句。,50,SUM函数的使用 3,查询梁朝伟的片酬总数 SELECT SUM(片酬) AS 梁朝伟的总片酬 FROM 出演 WHERE 演员姓名 梁朝伟 查询结果,51,AVG函数的使用 1,查询每人次出演支付的片酬平均数 SELECT AVG(片酬) AS 平均片酬 FROM 出演 查询结果,52,NULL对计算函数的影响,SELECT AVG(片酬), SUM(片酬)/COUNT(片酬), SUM(片酬)/COUNT(*) FROM 出演 查询结果,53,AVG函数的使用 2,查询梁朝伟的平均片酬 SELECT 演员姓名，AVG(片酬) AS 平均片酬 FROM 出演 WHERE 演员姓名 梁朝伟 查询结果 列 出演.演员姓名 在选择列表中无效，因为该列未包含在聚合函数中，并且没有 GROUP BY 子句。,54,AVG函数的使用 3,查询梁朝伟的平均片酬,SELECT AVG(片酬) AS 梁朝伟的平均片酬 FROM 出演 WHERE 演员姓名 梁朝伟 查询结果,55,MIN与MAX函数的使用 1,查询出演表中支付的最高片酬,SELECT MAX(片酬) AS 最高片酬 FROM 出演 查询结果,56,MIN与MAX函数的使用 2,查询最低片酬以及获得最低片酬的演员姓名 SELECT 演员姓名，MIN(片酬) AS 最低片酬 FROM 出演 查询结果 列 出演.演员姓名 在选择列表中无效，因为该列未包含在聚合函数中，并且没有 GROUP BY 子句。,57,MIN与MAX函数的使用 3,查询最低片酬以及有关演员的姓名 SELECT 演员姓名 FROM 出演 WHERE 片酬 MIN(片酬) 查询结果 聚合不应出现在 WHERE 子句中，除非该聚合位于 HAVING 子句或选择列表所包含的子查询中，并且要对其进行聚合的列是外部引用。,58,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 视图,59,查询语句的结构,SELECT FROM WHERE GROUP BY HAVING ,60,对查询结果进行分组计算,分组把一个表划分为子集，可以细化计算函数的作用对象 HAVING子句指定满足条件的组 在一个查询语句中，可以使用任意多个列进行分组 分组依据列不能是text，ntext，image和bit类型,61,GROUP BY子句的使用,查询每位演员拍片数目以及个人平均片酬，个人最低/最高片酬,SELECT 演员姓名，COUNT(片名) 拍片数目，AVG(片酬) 个人平均片酬， MIN(片酬) 个人最低片酬， MAX(片酬) 个人最高片酬 FROM 出演 GROUP BY 演员姓名,62,63,64,上述查询的结果,65,HAVING子句的功能,HAVING子句用于对分组后的结果再进行过滤 HAVING子句的作用对象是组而不是行 在HAVING子句中可以使用计算函数 HAVING与GROUP BY子句一起使用,66,HAVING子句的使用,查询出演不少于2部电影的演员姓名,67,68,69,使用HAVING子句的查询方案,SELECT 演员姓名，COUNT(片名) 拍片数目 FROM 出演 GROUP BY 演员姓名 HAVING COUNT(*)=2,70,上述查询的执行步骤与结果,先用GROUP BY按演员姓名分组 再用COUNT(*)统计每组包含的记录条数 最后挑选记录条数=2的组，返回对应的演员姓名和该组记录的条数 查询结果,71,计算函数与分组的联合使用 1,查询梁朝伟的个人片酬总数与个人平均片酬,SELECT 演员姓名，SUM(片酬) 个人总片酬， AVG(片酬) 个人平均片酬 FROM 出演 GROUP BY 演员姓名 HAVING 演员姓名 梁朝伟,72,查询结果,73,计算函数与分组的联合使用 2,SELECT 演员姓名, AVG(片酬) 个人平均片酬 FROM 出演 WHERE year(年代)100000,查询2000年以前个人最低片酬大于100k的演员的个人平均片酬,74,75,上述查询的结果,76,计算函数与分组的联合使用 3,SELECT 演员姓名, AVG(片酬) 个人平均片酬 FROM 出演 GROUP BY 演员姓名 HAVING MIN(片酬)100000 and year(年代)2000 查询结果,列 出演.年代 在 HAVING 子句中无效，因为该列既不包含在聚合函数中，也不包含在 GROUP BY 子句中。,查询2000年以前个人最低片酬大于100k的演员的个人平均片酬,77,计算函数与分组的联合使用 4,查询2000年以前演员的个人平均片酬 SELECT 演员姓名, AVG(片酬) 个人平均片酬 FROM 出演 GROUP BY 演员姓名 WHERE year(年代)2000 查询结果,在关键字 WHERE 附近有语法错误。,78,计算函数与分组的联合使用 5,SELECT 演员姓名, AVG(片酬) 个人平均片酬 FROM 出演 WHERE MIN(片酬)100000 and year(年代)2000 GROUP BY 演员姓名 查询结果,聚合不应出现在 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,查询语句的结构,SELECT FROM WHERE GROUP BY HAVING ORDER BY ,81,对查询结果进行排序 1,排序子句的格式为： ORDER BY ASC | DESC , n 为排序的依据列，可以是列名或列的别名 ASC表示对列进行升序排序，DESC表示对列进行降序排序 默认的排序方式为升序排序,82,对查询结果进行排序 2,指定多个排序列时，首先按最前面的列进行排序 如果排序后存在多个列值相同的记录，则对这些记录依据后续排序列进行排序,83,对查询结果进行排序示例 1,查询全体演员的情况，按姓名排序 SELECT * FROM 演员 ORDER BY 姓名 查询结果,84,对查询结果进行排序示例 2,查询全体演员的情况，先女后男再按姓名排序 SELECT * FROM 演员 ORDER BY 性别 DESC, 姓名 查询结果,85,分组与排序的联合使用 1,SELECT 演员姓名, AVG(片酬) 个人平均片酬 FROM 出演 WHERE year(年代)100000 ORDER BY 演员姓名 DESC 查询结果,查询2000年以前个人最低片酬大于100k的演员的个人平均片酬,86,分组与排序的联合使用 2,SELECT 演员姓名, AVG(片酬) 个人平均片酬 FROM 出演 WHERE year(年代)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，表2 WHERE . . 连接列必须是可比较的 语义相同,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，电影 电影2 WHERE 电影2.导演 = 王家卫 AND 电影2.年代 = 电影1.年代 AND 电影1.导演 != 王家卫,98,电影 1,电影 2,查询结果,99,外连接,连接时，输出一方的全部元组和对方的匹配元组 FROM 表1 LEFT | RIGHT OUTER JOIN 表2 ON ,100,左外连接,FROM 表1 LEFT OUTER JOIN 表2 ON ,输出：表1全部，表2满足连接条件元组,101,右外连接,FROM 表1 RIGHT OUTER JOIN 表2 ON ,输出:表1满足连接条件元组，表2全部,102,左外连接的使用,查询所有演员出演影片的情况，包括没有出演记录的演员,SELECT 姓名, 片名 FROM 演员 LEFT OUTER JOIN 出演 ON 演员.工号=出演.工号,103,演员,出演,查询结果,104,右外连接的使用,查询所有演员出演影片的情况，包括没有出演记录的演员,SELECT 姓名, 片名 FROM 出演 RIGHT OUTER JOIN 演员 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,子查询的用法,子查询要写在圆括号中 查询块中不能使用ORDER BY子句 引入子查询的保留字为IN 子查询通常用在外层查询的WHERE子句或HAVING子句中,109,子查询的用法,带有IN谓词的子查询 带有比较运算符的子查询 带有ANY（SOME）或ALL谓词的子查询 带有EXISTS谓词的子查询,110,演员,电影,出演,111,子查询的使用,查询片酬最低的演员姓名,SELECT 演员.姓名 FROM 演员, 出演 WHERE 演员.工号=出演.工号 AND 片酬 IN ( SELECT MIN (片酬) FROM 出演 ),112,演员,电影,出演,113,查询结果,114,WHERE Sage ANY（ ） AND Sdept IS ORDER BY Sage DESC,例（带ANY或ALL谓词的子查询）找出比IS系至少一名学生年龄小的其他系学生名单。,SELECT Sname，Sage，Sdept FROM Student,SELECT distinct Sage FROM Student WHERE Sdept=IS,115,WHERE （ Sage ALL（ ） ） AND （ Sdept IS ）ORDER BY Sage DESC,例 查询比IS系所有学生年龄都小的其他各系学生姓名, 年龄，系名。,SELECT Sage FROM Student WHERE Sdept=IS,SELECT Sname，Sage，Sdept FROM Student,116,EXISTS的使用,由EXISTS引出的子查询，其目标列表达式通常都用* ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义 EXISTS 若内层查询结果非空，则外层的WHERE子句返回真值 若内层查询结果为空，则外层的WHERE子句返回假值,117,查询所有选修了1号课程的学生姓名,用嵌套查询 SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno= 1 ),118,查询所有选修了1号课程的学生姓名,用连接查询 SELECT Sname FROM Student, SC WHERE Student.Sno=SC.Sno AND SC.Cno= 1,119,查询选修了全部课程的学生姓名,SELECT Sname FROM Student WHERE NOT EXISTS （SELECT * FROM Course WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno= Student.Sno AND Cno= ourse.Cno ） ）,集合查询,120,121,例1 查找选修了课程1或课程2的学生学号和姓名。,SELECT SC.Sno, Sname FROM SC, Student WHERE (Cno=2) AND (SC.Sno =Student.Sno),SELECT SC.Sno, Sname FROM SC, Student WHERE (Cno=1) AND (SC.Sno =Student.Sno),UNION,集合查询就是对几个查询块的结果集进行并、交、差运算。,122,例1 查找选修了课程1或课程2的学生学号和姓名。,SELECT SC.Sno, Sname FROM SC, Student WHERE ( (Cno=1) OR (Cno=2) AND (SC.Sno =Student.Sno),集合查询就是对几个查询块的结果集进行并、交、差运算。,123,AND Sno IN （SELECT Sno FROM SC WHERE Cno =2）,例2 查找既选修课程1，又选修课程2的学生集合,这实际上是查找选修了课程1学生，与选修了课程2 的学生的交集。,SELECT Sno FROM SC WHERE Cno =1,124,INTERSECT ( SELECT Sno FROM SC WHERE Cno =2),例2 查找既选修课程1，又选修课程2的学生集合,这实际上是查找选修了课程1学生，与选修了课程2 的学生的交集。,(SELECT Sno FROM SC WHERE Cno =1),125,AND Sno NOT IN（ ）,例3 查找选修了课程1，但不选修课程2的学生。,这实际上是查找选修课程1的学生，与选修课程2的学生的差集：,SELECT Sno FROM SC WHERE Cno=1,SELECT Sno FROM SC WHERE Cno =2,126,EXCEPT (SELECT Sno FROM SC WHERE Cno=2),例3 查找选修了课程1，但不选修课程2的学生。,这实际上是查找选修课程1的学生，与选修课程2的学生的差集：,(SELECT Sno FROM SC WHERE Cno=1),127,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 视图,128,视图概念的示意图,基本表1,基本表2,129,视图,基本表：CREAT TABLE语句创建的表 基本表中的数据存储在磁盘上 视图：从基本表中选取出来的数据组成的逻辑窗口 视图包含的数据存放在基本表中，数据库中只存放视图的定义 视图可以建立在基本表上，也可以建立在其他的视图上,130,定义视图的语法,CREATE VIEW (视图列名表) AS 子查询语句 子查询通常不包含ORDER BY和DISTINCT子句 视图列名或者全部指定，或者全部省略。省略时，视图的列名与子查询列名相同。 以下三种情况必须明确指定组成视图的所有列名： 某个目标列是计算函数或列表达式； 多表连接时选出了几个同名列作为视图的字段； 需要在视图中为某个列选用新的更合适的列名,131,视图的定义示例,把在1998年拍摄过电影的演员情况定义为视图 CREATE VIEW 出演1998 AS SELECT 姓名, 性别, 片名 FROM 演员, 出演 WHERE 演员.工号=出演.工号 AND 出演.工号 IN ( SELECT 工号 FROM 出演 WHERE year(年代)=1998 ),132,视图出演1998的显示数据,133,使用视图的查询,查询在1998年拍摄过电影的女演员的情况 SELECT * FROM 出演1998 WHERE 性别=女 查询结果,134,删除视图的语法,DROP VIEW 例如，删除视图出演1998 DROP VIEW 出演1998,135,视图的作用,简化数据查询语句 使用户能从多角度看到同一数据 提高了数据的安全性 提供了一定程度的逻辑独立性,