数据库系统原理课件：数据查询功能与视图VIP

1、1第4章 SQL语言4.1 SQL语言概述4.2 数据定义功能与约束4.3 数据操纵功能4.4 数据查询功能与视图2查询语句的基本结构SELECT FROM WHERE GROUP BY HAVING ORDER BY 34.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 6CREATE TABLE 演员( 姓名 char ( 20 ), 工号 char ( 10 ), 性别

2、char (2), PRIMARY KEY (工号 ) )CREATE TABLE 电影( 片名 char ( 60 ), 导演 char ( 20 ), 年代 datetime, PRIMARY KEY (片名，年代 ) )CREATE TABLE 出演( 工号 char ( 10 ), 片名 char ( 60 ), 年代 datetime, 片酬 money, PRIMARY KEY (工号,片名,年代 ) )7姓名工号性别王菲A010女宁静A120女梁朝伟A231男葛优A751男片名导演年代重庆森林王家卫1994红河谷冯小宁1998不见不散冯小刚1998花样年华王家卫2000工号片名年

3、代片酬A120红河谷199850kA231花样年华2000100kA751不见不散1998120k演员电影出演8查询指定的列查询演员的姓名与性别SELECT 姓名，性别 FROM 演员查询结果姓名性别王菲女宁静女梁朝伟男葛优男9查询全部列查询全体演员的记录SELECT * FROM 演员查询结果姓名工号性别王菲A010女宁静A120女梁朝伟A231男葛优A751男10数据库管理系统中的函数getdate()取当前的系统日期year(date)取日期时间型参数date的年份month(date)day(date)11使用常量列和计算列查询电影的片名以及拍摄距今时间SELECT 片名, 拍摄距今,

4、 year(getdate()-year(年代), 年 FROM 电影查询结果片名重庆森林拍摄距今11年红河谷拍摄距今7年不见不散拍摄距今7年花样年华拍摄距今5年12改变列标题计算列、函数列和常量列的显示结果都没有列标题，通过指定列的别名可以改变查询结果的列标题改变列标题的语法： 列名 | 表达式 AS 列标题 或：列标题 列名 | 表达式13改变列标题的示例查询电影的片名以及拍摄距今时间SELECT 片名, year(getdate()-year(年代) AS 拍摄距今年限 FROM 电影查询结果片名拍摄距今年限重庆森林11红河谷7不见不散7花样年华514姓名工号性别王菲A010女宁静A12

5、0女梁朝伟A231男葛优A751男片名导演年代重庆森林王家卫1994红河谷冯小宁1998不见不散冯小刚1998花样年华王家卫2000工号片名年代片酬A120红河谷199850kA231花样年华2000100kA751不见不散1998120k演员电影出演15查询结果中相同行被自动消除查询数据库中的电影拍摄年代SELECT year(年代) AS 拍摄年代 FROM 电影查询结果拍摄年代199419982000165.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

6、 视图17查询语句的结构SELECT FROM WHERE 18常用查询条件 WHERE子句常用查询条件 谓 词 比较（比较运算符） =, , =, , =, （或!=），NOT+上述比较运算符 确定范围 BETWEEN AND, NOT BETWEEN AND 确定集合 IN, NOT IN 字符匹配 LIKE, NOT LIKE 空值 IS NULL, IS NOT NULL 多重条件（逻辑谓词） AND, OR 19查询条件：比较运算1查询女演员的姓名SELECT 姓名 FROM 演员 WHERE 性别女姓名王菲宁静20查询条件：比较运算2查询拍摄距今超过10年电影的片名SELECT 片

7、名 FROM 电影 WHERE year(getdate()-year(年代) 10查询结果片名重庆森林21字符匹配的条件运算符用于查找指定列中符合匹配模式的元组列名 NOT LIKE 匹配串中可包含如下四种通配符 _ 匹配任意一个字符； % 匹配0个或多个字符； 匹配 中的任意一个字符； 不匹配 中的任意一个字符22查询条件的设置:字符匹配 1查询 “张”“王”“李”姓演员的情况SELECT 姓名 FROM 演员 WHERE 姓名 LIKE 张王李%查询结果姓名王菲23查询条件的设置:字符匹配 2查询姓名第2个字不是“菲”或者“优” 的演员的情况SELECT 姓名 FROM 演员 WHERE

8、 姓名 LIKE _菲优%查询结果姓名宁静梁朝伟24查询条件的设置:字符匹配 3查询姓名由2个字组成的演员的情况SELECT 姓名 FROM 演员 WHERE 姓名 LIKE _ _查询结果姓名王菲宁静葛优25判断空值的条件运算符空值（NULL）表示不确定的值判断取值为空的语句格式： 列名 IS NULL判断取值不为空的语句格式： 列名 IS NOT NULL26涉及空值的查询查询有片酬记录的出演情况SELECT * FROM 出演 WHERE 片酬 IS NOT NULL查询结果27多重条件的组织在WHERE子句中可以使用逻辑运算符AND和OR来组成多重条件查询用AND连接的条件表示必须全部

9、满足所有的条件的元组才被选中用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 电影 W

10、HERE 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查询条件的设置

11、:确定范围 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()ye

12、ar( 年代) = 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(ge

13、tdate()year( 年代) != 10 AND year(getdate()year( 年代) != 15 405.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使用计算函数汇总数据计算函数=集合函数=聚合函数=聚集函数对一组值进行计算并返回一个单值42SQL提供的计算函数 COUNT（ * ）: 统计表中元组个数COUNT（ ）: 统计本列列值个数MAX（ ）：求列值最大值MIN（ ）：求列值最小值SUM（ ）: 计算列值总和AVG（ ）:

14、计算列值平均值43使用计算函数的注意事项在SELECT，HAVING子句中使用计算函数不能直接出现在WHERE子句中如果使用了计算函数或者分组，那么 SELECT子句中的列必须是计算函数或者是分组依据列SUM 与AVG只能用于数值类型的列，参数可以是计算列要求只有一个参数除COUNT（*）外，其他函数在计算过程中均忽略NULL值44出演表例子 45COUNT函数的使用 1查询演员出演的总人次数查询演员出演记录的总条数SELECT COUNT(*) FROM 出演SELECT COUNT(演员姓名) FROM 出演SELECT COUNT(片名) FROM 出演SELECT COUNT(年代)

15、FROM 出演 SELECT COUNT(片酬) FROM 出演查询结果46COUNT函数的使用 2查询有片酬记录的总条数SELECT COUNT(片酬) AS 片酬记录条数FROM 出演查询结果47COUNT函数的使用 3查询出演表中不同演员的人数SELECT COUNT(DISTINCT 演员姓名) AS 演员总数FROM 出演查询结果48SUM函数的使用 1查询出演表中支付的片酬总数SELECT SUM(片酬) AS 总片酬FROM 出演查询结果49SUM函数的使用 2查询梁朝伟的片酬总数SELECT 演员姓名，SUM(片酬) AS 总片酬FROM 出演WHERE 演员姓名 梁朝伟查询结

16、果 列 出演.演员姓名 在选择列表中无效，因为该列未包含在聚合函数中，并且没有 GROUP BY 子句。50SUM函数的使用 3查询梁朝伟的片酬总数SELECT SUM(片酬) AS 梁朝伟的总片酬FROM 出演WHERE 演员姓名 梁朝伟查询结果51AVG函数的使用 1查询每人次出演支付的片酬平均数SELECT AVG(片酬) AS 平均片酬FROM 出演查询结果52NULL对计算函数的影响SELECT AVG(片酬), SUM(片酬)/COUNT(片酬), SUM(片酬)/COUNT(*)FROM 出演查询结果53AVG函数的使用 2查询梁朝伟的平均片酬SELECT 演员姓名，AVG(片酬

17、) AS 平均片酬FROM 出演WHERE 演员姓名 梁朝伟查询结果 列 出演.演员姓名 在选择列表中无效，因为该列未包含在聚合函数中，并且没有 GROUP BY 子句。54AVG函数的使用 3查询梁朝伟的平均片酬SELECT AVG(片酬) AS 梁朝伟的平均片酬FROM 出演WHERE 演员姓名 梁朝伟查询结果55MIN与MAX函数的使用 1查询出演表中支付的最高片酬SELECT MAX(片酬) AS 最高片酬FROM 出演查询结果56MIN与MAX函数的使用 2查询最低片酬以及获得最低片酬的演员姓名SELECT 演员姓名，MIN(片酬) AS 最低片酬FROM 出演查询结果 列 出演.演

18、员姓名 在选择列表中无效，因为该列未包含在聚合函数中，并且没有 GROUP BY 子句。57MIN与MAX函数的使用 3查询最低片酬以及有关演员的姓名SELECT 演员姓名FROM 出演WHERE 片酬 MIN(片酬) 查询结果 聚合不应出现在 WHERE 子句中，除非该聚合位于 HAVING 子句或选择列表所包含的子查询中，并且要对其进行聚合的列是外部引用。585.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

19、WHERE GROUP BY HAVING 60对查询结果进行分组计算分组把一个表划分为子集，可以细化计算函数的作用对象HAVING子句指定满足条件的组在一个查询语句中，可以使用任意多个列进行分组分组依据列不能是text，ntext，image和bit类型61GROUP BY子句的使用查询每位演员拍片数目以及个人平均片酬，个人最低/最高片酬SELECT 演员姓名，COUNT(片名) 拍片数目，AVG(片酬) 个人平均片酬， MIN(片酬) 个人最低片酬， MAX(片酬) 个人最高片酬FROM 出演GROUP BY 演员姓名626364上述查询的结果 65HAVING子句的功能HAVING子句用

20、于对分组后的结果再进行过滤HAVING子句的作用对象是组而不是行在HAVING子句中可以使用计算函数HAVING与GROUP BY子句一起使用66HAVING子句的使用查询出演不少于2部电影的演员姓名676869使用HAVING子句的查询方案SELECT 演员姓名，COUNT(片名) 拍片数目FROM 出演GROUP BY 演员姓名HAVING COUNT(*)=270上述查询的执行步骤与结果先用GROUP BY按演员姓名分组再用COUNT(*)统计每组包含的记录条数最后挑选记录条数=2的组，返回对应的演员姓名和该组记录的条数查询结果71计算函数与分组的联合使用 1查询梁朝伟的个人片酬总数与个

21、人平均片酬SELECT 演员姓名，SUM(片酬) 个人总片酬， AVG(片酬) 个人平均片酬FROM 出演GROUP BY 演员姓名HAVING 演员姓名 梁朝伟72查询结果73计算函数与分组的联合使用 2SELECT 演员姓名, AVG(片酬) 个人平均片酬FROM 出演WHERE year(年代)100000查询2000年以前个人最低片酬大于100k的演员的个人平均片酬7475上述查询的结果 76计算函数与分组的联合使用 3SELECT 演员姓名, AVG(片酬) 个人平均片酬FROM 出演GROUP BY 演员姓名HAVING MIN(片酬)100000 and year(年代)2000

22、查询结果列 出演.年代 在 HAVING 子句中无效，因为该列既不包含在聚合函数中，也不包含在 GROUP BY 子句中。查询2000年以前个人最低片酬大于100k的演员的个人平均片酬77计算函数与分组的联合使用 4查询2000年以前演员的个人平均片酬SELECT 演员姓名, AVG(片酬) 个人平均片酬FROM 出演GROUP BY 演员姓名WHERE year(年代)100000 and year(年代)2000GROUP BY 演员姓名查询结果聚合不应出现在 WHERE 子句中，除非该聚合位于 HAVING 子句或选择列表所包含的子查询中，并且要对其进行聚合的列是外部引用。查询2000年

23、以前个人最低片酬大于100k的演员的个人平均片酬795.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指定多个排序列时

24、，首先按最前面的列进行排序如果排序后存在多个列值相同的记录，则对这些记录依据后续排序列进行排序83对查询结果进行排序示例 1查询全体演员的情况，按姓名排序SELECT * FROM 演员 ORDER BY 姓名查询结果84对查询结果进行排序示例 2查询全体演员的情况，先女后男再按姓名排序SELECT * FROM 演员 ORDER BY 性别 DESC, 姓名查询结果85分组与排序的联合使用 1SELECT 演员姓名, AVG(片酬) 个人平均片酬FROM 出演WHERE year(年代)100000 ORDER BY 演员姓名 DESC查询结果查询2000年以前个人最低片酬大于100k的演员

25、的个人平均片酬86分组与排序的联合使用 2SELECT 演员姓名, AVG(片酬) 个人平均片酬FROM 出演WHERE year(年代)100000查询结果在关键字 GROUP 附近有语法错误。查询2000年以前个人最低片酬大于100k的演员的个人平均片酬875.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内连接FROM 表1，表2 WHERE . . 连接

26、列必须是可比较的语义相同90姓名工号性别王菲A010女宁静A120女梁朝伟A231男葛优A751男片名导演年代重庆森林王家卫1994红河谷冯小宁1998不见不散冯小刚1998花样年华王家卫2000黑骏马管虎2000工号片名年代片酬A120红河谷1998NULLA231花样年华2000100kA751不见不散1998120k演员电影出演91内连接的使用 1查询演员出演电影的情况SELECT 演员.姓名，出演.片名，出演.年代FROM 演员，出演WHERE 演员.工号 出演.工号92姓名性别工号王菲女A010宁静女A120梁朝伟男A231葛优男A751工号片名年代片酬A120红河谷1998NULL

27、A231花样年华2000100kA751不见不散1998120k演员出演查询结果93内连接的使用 2查询演员与导演的合作情况SELECT 姓名,导演, 电影.片名, year(电影.年代) 年代 FROM 演员，电影，出演WHERE 演员.工号=出演.工号 AND 出演.片名=电影.片名 AND 出演.年代=电影.年代 94片名年代导演重庆森林1994王家卫红河谷1998冯小宁不见不散1998冯小刚花样年华2000王家卫黑骏马2000管虎工号片名年代片酬A120红河谷1998NULLA231花样年华2000100kA751不见不散1998120k演员电影出演姓名性别工号王菲女A010宁静女A1

28、20梁朝伟男A231葛优男A75195上述查询的结果 96内连接的使用3 自连接查询与王家卫影片同年拍摄的其他导演的影片SELECT 电影1.片名， 电影1.导演， year（电影1.年代） 年代FROM 电影 电影1，电影 电影2WHERE 电影2.导演 = 王家卫 AND 电影2.年代 = 电影1.年代 AND 电影1.导演 != 王家卫97片名导演年代重庆森林王家卫1994红河谷冯小宁1998不见不散冯小刚1998花样年华王家卫2000黑骏马管虎2000电影 1年代导演片名1994王家卫重庆森林1998冯小宁红河谷1998冯小刚不见不散2000王家卫花样年华2000管虎黑骏马电影 2查询

29、结果98外连接连接时，输出一方的全部元组和对方的匹配元组FROM 表1 LEFT | RIGHT OUTER JOIN 表2 ON 99左外连接FROM 表1 LEFT OUTER JOIN 表2 ON 输出：表1全部，表2满足连接条件元组100右外连接FROM 表1 RIGHT OUTER JOIN 表2 ON 输出:表1满足连接条件元组，表2全部101左外连接的使用 查询所有演员出演影片的情况，包括没有出演记录的演员SELECT 姓名, 片名FROM 演员 LEFT OUTER JOIN 出演 ON 演员.工号=出演.工号102姓名性别工号王菲女A010宁静女A120梁朝伟男A231葛优男

30、A751工号片名年代片酬A120红河谷1998NULLA231花样年华2000100kA751不见不散1998120k演员出演查询结果103右外连接的使用 查询所有演员出演影片的情况，包括没有出演记录的演员SELECT 姓名, 片名FROM 出演 RIGHT OUTER JOIN 演员 ON 演员.工号=出演.工号104工号姓名性别A010王菲女A120宁静女A231梁朝伟男A751葛优男片名年代片酬工号红河谷1998NULLA120花样年华2000100kA231不见不散1998120kA751演员出演查询结果1055.4 数据查询功能与视图5.4.1 单表查询5.4.2 查询条件的设置5.

31、4.3 聚合函数5.4.4 分组5.4.5 查询结果的排序5.4.6 多表连接查询5.4.7 子查询5.4.8 视图106子查询SQL的查询块：一个SELECTFROMWHERE的组合体子查询内层查询：嵌套在SELECT、UPDATE或DELETE等语句中的查询块主查询外层查询：包含子查询的语句107子查询的用法子查询要写在圆括号中引入子查询的保留字为IN子查询通常用在外层查询的WHERE子句或HAVING子句中108姓名工号性别王菲A010女宁静A120女梁朝伟A231男葛优A751男片名导演年代重庆森林王家卫1994红河谷冯小宁1998不见不散冯小刚1998花样年华王家卫2000黑骏马管虎

32、2000工号片名年代片酬A120红河谷1998NULLA231花样年华2000100kA751不见不散1998120k演员电影出演109子查询的使用 查询片酬最低的演员姓名SELECT 演员.姓名FROM 演员, 出演WHERE 演员.工号=出演.工号 AND 片酬 IN ( SELECT MIN (片酬) FROM 出演 )110姓名工号性别王菲A010女宁静A120女梁朝伟A231男葛优A751男片名导演年代重庆森林王家卫1994红河谷冯小宁1998不见不散冯小刚1998花样年华王家卫2000黑骏马管虎2000工号片名年代片酬A120红河谷1998NULLA231花样年华2000100kA

33、751不见不散1998120k演员电影出演111查询结果112 WHERE Sage ANY（ ） AND Sdept IS ORDER BY Sage DESC 例（带ANY或ALL谓词的子查询）找出比IS系至少一名学生年龄小的其他系学生名单。 SELECT Sname，Sage，Sdept FROM StudentSELECT distinct Sage FROM Student WHERE Sdept=IS113WHERE （ Sage ALL（ ） ） AND （ Sdept IS ）ORDER BY Sage DESC 例 查询比IS系所有学生年龄都小的其他各系学生姓名, 年龄，系名

34、。SELECT Sage FROM Student WHERE Sdept=IS SELECT Sname，Sage，Sdept FROM Student114 例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集合查询就是对几个查询块的结果集进行并、交、差运算。115 例1 查找选修了课程1或课程2的学生学号和姓名。 SELECT SC.Sno, Sname FROM SC, Student WHERE ( (Cno=1) OR (Cno=2) AND (SC.Sno =Student.Sno)集合查询就是对几个查询块的结果集进行并、交、差运算。116 AND Sno IN （SELECT Sno FROM SC WHERE Cno =2） 例2 查找既选修课程1，又选修课程2的学生集合 这实际上是查找选修了课程1学生，与选修了课程2 的学生的交集。SELECT SnoFROM SCW