数据库系统原理教学.pptx
《数据库系统原理教学.pptx》由会员分享,可在线阅读,更多相关《数据库系统原理教学.pptx(128页珍藏版)》请在得力文库 - 分享文档赚钱的网站上搜索。
1、1查询语句的基本结构SELECT FROM WHERE GROUP BY HAVING ORDER BY 第1页/共128页24.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 视图第2页/共128页3单表查询数据源只涉及一张表的查询选择列第3页/共128页4查询语句的结构SELECT FROM 第4页/共128页5CREATE TABLE 演员(姓名 char(20),工号 char(10),性别 char(2),PRIMARY KEY(工号)CREATE
2、TABLE 电影(片名 char(60),导演 char(20),年代 datetime,PRIMARY KEY(片名,年代)CREATE TABLE 出演(工号 char(10),片名 char(60),年代 datetime,片酬 money,PRIMARY KEY(工号,片名,年代)第5页/共128页6姓名姓名工号工号性别性别王菲王菲A010女女宁静宁静A120女女梁朝伟梁朝伟A231男男葛优葛优A751男男片名片名导演导演年代年代重庆森林重庆森林王家卫王家卫1994红河谷红河谷冯小宁冯小宁1998不见不散不见不散冯小刚冯小刚1998花样年华花样年华王家卫王家卫2000工号工号片名片名年
3、代年代片酬片酬A120红河谷红河谷199850kA231花样年华花样年华2000100kA751不见不散不见不散1998120k演员演员电影电影出演出演第6页/共128页7查询指定的列查询演员的姓名与性别SELECT 姓名,性别 FROM 演员查询结果姓名姓名性别性别王菲王菲女女宁静宁静女女梁朝伟梁朝伟男男葛优葛优男男第7页/共128页8查询全部列查询全体演员的记录SELECT*FROM 演员查询结果姓名姓名工号工号性别性别王菲王菲A010女女宁静宁静A120女女梁朝伟梁朝伟A231男男葛优葛优A751男男第8页/共128页9数据库管理系统中的函数getdate()取当前的系统日期year(d
4、ate)取日期时间型参数date的年份month(date)day(date)第9页/共128页10使用常量列和计算列查询电影的片名以及拍摄距今时间SELECT 片名,拍摄距今,year(getdate()-year(年代),年 FROM 电影查询结果片名片名重庆森林重庆森林拍摄距今拍摄距今11年年红河谷红河谷拍摄距今拍摄距今7年年不见不散不见不散拍摄距今拍摄距今7年年花样年华花样年华拍摄距今拍摄距今5年年第10页/共128页11改变列标题计算列、函数列和常量列的显示结果都没有列标题,通过指定列的别名可以改变查询结果的列标题改变列标题的语法:列名|表达式 AS 列标题 或:列标题 列名|表达式
5、第11页/共128页12改变列标题的示例查询电影的片名以及拍摄距今时间SELECT 片名,year(getdate()-year(年代)AS 拍摄距今年限 FROM 电影查询结果片名片名拍摄距今年限拍摄距今年限重庆森林重庆森林11红河谷红河谷7不见不散不见不散7花样年华花样年华5第12页/共128页13姓名姓名工号工号性别性别王菲王菲A010女女宁静宁静A120女女梁朝伟梁朝伟A231男男葛优葛优A751男男片名片名导演导演年代年代重庆森林重庆森林王家卫王家卫1994红河谷红河谷冯小宁冯小宁1998不见不散不见不散冯小刚冯小刚1998花样年华花样年华王家卫王家卫2000工号工号片名片名年代年代
6、片酬片酬A120红河谷红河谷199850kA231花样年华花样年华2000100kA751不见不散不见不散1998120k演员演员电影电影出演出演第13页/共128页14查询结果中相同行被自动消除查询数据库中的电影拍摄年代SELECT year(年代)AS 拍摄年代 FROM 电影查询结果拍摄年代拍摄年代199419982000第14页/共128页155.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 视图第15页/共128页16查询语句的结构SELECT F
7、ROM WHERE 第16页/共128页17常用查询条件 WHERE子句常用查询条件子句常用查询条件 谓谓 词词 比较(比较运算符)比较(比较运算符)=,=,=,=,=,=,(或(或!=!=),),NOT+NOT+上述比较运算符上述比较运算符 确定范围确定范围 BETWEEN AND,NOT BETWEEN AND BETWEEN AND,NOT BETWEEN AND 确定集合确定集合 IN,NOT IN IN,NOT IN 字符匹配字符匹配 LIKE,NOT LIKE LIKE,NOT LIKE 空值空值 IS NULL,IS NOT NULL IS NULL,IS NOT NULL 多重
8、条件(逻辑谓词)多重条件(逻辑谓词)AND,OR AND,OR 第17页/共128页18查询条件:比较运算1查询女演员的姓名SELECT 姓名 FROM 演员 WHERE 性别女姓名姓名王菲王菲宁静宁静第18页/共128页19查询条件:比较运算2查询拍摄距今超过10年电影的片名SELECT 片名 FROM 电影 WHERE year(getdate()-year(年代)10查询结果片名片名重庆森林重庆森林第19页/共128页20字符匹配的条件运算符用于查找指定列中符合匹配模式的元组列名 NOT LIKE 匹配串中可包含如下四种通配符 _ 匹配任意一个字符;%匹配0个或多个字符;匹配 中的任意一
9、个字符;不匹配 中的任意一个字符第20页/共128页21查询条件的设置:字符匹配 1查询“张”“王”“李”姓演员的情况SELECT 姓名 FROM 演员 WHERE 姓名 LIKE 张王李%查询结果姓名姓名王菲王菲第21页/共128页22查询条件的设置:字符匹配 2查询姓名第2个字不是“菲”或者“优”的演员的情况SELECT 姓名 FROM 演员 WHERE 姓名 LIKE _菲优%查询结果姓名姓名宁静宁静梁朝伟梁朝伟第22页/共128页23查询条件的设置:字符匹配 3查询姓名由2个字组成的演员的情况SELECT 姓名 FROM 演员 WHERE 姓名 LIKE _ _查询结果姓名姓名王菲王菲
10、宁静宁静葛优葛优第23页/共128页24判断空值的条件运算符空值(NULL)表示不确定的值判断取值为空的语句格式:列名 IS NULL判断取值不为空的语句格式:列名 IS NOT NULL第24页/共128页25涉及空值的查询查询有片酬记录的出演情况lSELECT *FROM 出演 WHERE 片酬 IS NOT NULLl查询结果第25页/共128页26多重条件的组织在WHERE子句中可以使用逻辑运算符AND和OR来组成多重条件查询用AND连接的条件表示必须全部满足所有的条件的元组才被选中用OR连接的条件表示只要满足其中一个条件的元组即被选中第26页/共128页27多重条件查询 1查询王姓女
11、演员的情况lSELECT *FROM 演员 WHERE 姓名 LIKE 王%AND 性别女 l查询结果第27页/共128页28多重条件查询 2查询王姓演员和全体男演员的情况SELECT *FROM 演员 WHERE 姓名 LIKE 王%OR 性别男 查询结果第28页/共128页29确定范围的条件运算符 BETWEENAND和 NOT BETWEENAND格式 列名|表达式 NOT BETWEEN 下限值 AND 上限值第29页/共128页30查询条件的设置:确定范围 1查询拍摄距今5至10年的电影lSELECT 片名 FROM 电影 WHERE year(getdate()year(年代)BE
12、TWEEN 5 AND 10不见不散红河谷花样年华片名l查询结果第30页/共128页31查询条件的设置:确定范围 2查询拍摄距今5至10年的电影SELECT 片名 FROM 电影 WHERE year(getdate()year(年代)=5 AND year(getdate()year(年代)=10 第31页/共128页32查询条件的设置:确定范围 3查询拍摄距今不到5年或者10年以上的电影SELECT 片名 FROM 电影 WHERE year(getdate()year(年代)NOT BETWEEN 5 AND 10查询结果片名片名重庆森林重庆森林第32页/共128页33查询条件的设置:确
13、定范围 4查询拍摄距今不到5年或者10年以上的电影SELECT 片名 FROM 电影 WHERE year(getdate()year(年代)10 第33页/共128页34集合的条件运算符 IN和NOT IN格式 列名 NOT IN(集合)第34页/共128页35查询条件的设置:确定集合 1查询拍摄距今为5,10或者15年的电影SELECT 片名 FROM 电影 WHERE year(getdate()-year(年代)IN(5,10,15)查询结果片名片名花样年华花样年华第35页/共128页36查询条件的设置:确定集合 2查询拍摄距今为5,10或者15年的电影SELECT 片名 FROM 电
14、影 WHERE year(getdate()year(年代)=5 OR year(getdate()year(年代)=10 OR year(getdate()year(年代)=15 第36页/共128页37查询条件的设置:确定集合 3查询拍摄距今年限不是5,10或者15的电影SELECT 片名 FROM 电影 WHERE year(getdate()-year(年代)NOT IN(5,10,15)查询结果片名片名重庆森林重庆森林红河谷红河谷不见不散不见不散第37页/共128页38查询条件的设置:确定集合 4查询拍摄距今年限不是5,10或者15的电影SELECT 片名 FROM 电影 WHERE
15、 year(getdate()year(年代)!=5 AND year(getdate()year(年代)!=10 AND year(getdate()year(年代)!=15 第38页/共128页395.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 视图第39页/共128页40使用计算函数汇总数据计算函数=集合函数=聚合函数=聚集函数对一组值进行计算并返回一个单值第40页/共128页41SQL提供的计算函数 COUNT(*):统计表中元组个数COUNT()
16、:统计本列列值个数MAX():求列值最大值MIN():求列值最小值SUM():计算列值总和AVG():计算列值平均值第41页/共128页42使用计算函数的注意事项在SELECT,HAVING子句中使用计算函数不能直接出现在WHERE子句中如果使用了计算函数或者分组,那么 SELECT子句中的列必须是计算函数或者是分组依据列SUM 与AVG只能用于数值类型的列,参数可以是计算列要求只有一个参数除COUNT(*)外,其他函数在计算过程中均忽略NULL值第42页/共128页43出演表例子 第43页/共128页44COUNT函数的使用 1查询演员出演的总人次数查询演员出演记录的总条数lSELECT C
17、OUNT(*)FROM 出演lSELECT COUNT(演员姓名)FROM 出演lSELECT COUNT(片名)FROM 出演lSELECT COUNT(年代)FROM 出演 lSELECT COUNT(片酬)FROM 出演l查询结果第44页/共128页45COUNT函数的使用 2查询有片酬记录的总条数lSELECT COUNT(片酬)AS 片酬记录条数lFROM 出演l查询结果第45页/共128页46COUNT函数的使用 3查询出演表中不同演员的人数lSELECT COUNT(DISTINCT 演员姓名)AS 演员总数lFROM 出演l查询结果第46页/共128页47SUM函数的使用 1查
18、询出演表中支付的片酬总数lSELECT SUM(片酬)AS 总片酬lFROM 出演l查询结果第47页/共128页48SUM函数的使用 2查询梁朝伟的片酬总数SELECT 演员姓名,SUM(片酬)AS 总片酬FROM 出演WHERE 演员姓名 梁朝伟查询结果 列 出演.演员姓名 在选择列表中无效,因为该列未包含在聚合函数中,并且没有 GROUP BY 子句。第48页/共128页49SUM函数的使用 3查询梁朝伟的片酬总数SELECT SUM(片酬)AS 梁朝伟的总片酬FROM 出演WHERE 演员姓名 梁朝伟查询结果第49页/共128页50AVG函数的使用 1查询每人次出演支付的片酬平均数SEL
19、ECT AVG(片酬)AS 平均片酬FROM 出演查询结果第50页/共128页51NULL对计算函数的影响SELECT AVG(片酬),SUM(片酬)/COUNT(片酬),SUM(片酬)/COUNT(*)FROM 出演查询结果第51页/共128页52AVG函数的使用 2查询梁朝伟的平均片酬SELECT 演员姓名,AVG(片酬)AS 平均片酬FROM 出演WHERE 演员姓名 梁朝伟查询结果 列 出演.演员姓名 在选择列表中无效,因为该列未包含在聚合函数中,并且没有 GROUP BY 子句。第52页/共128页53AVG函数的使用 3查询梁朝伟的平均片酬lSELECT AVG(片酬)AS 梁朝伟
20、的平均片酬lFROM 出演lWHERE 演员姓名 梁朝伟l查询结果第53页/共128页54MIN与MAX函数的使用 1查询出演表中支付的最高片酬lSELECT MAX(片酬)AS 最高片酬lFROM 出演l查询结果第54页/共128页55MIN与MAX函数的使用 2查询最低片酬以及获得最低片酬的演员姓名SELECT 演员姓名,MIN(片酬)AS 最低片酬FROM 出演查询结果 列 出演.演员姓名 在选择列表中无效,因为该列未包含在聚合函数中,并且没有 GROUP BY 子句。第55页/共128页56MIN与MAX函数的使用 3查询最低片酬以及有关演员的姓名SELECT 演员姓名FROM 出演W
21、HERE 片酬 MIN(片酬)查询结果 聚合不应出现在 WHERE 子句中,除非该聚合位于 HAVING 子句或选择列表所包含的子查询中,并且要对其进行聚合的列是外部引用。第56页/共128页575.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 视图第57页/共128页58查询语句的结构SELECT FROM WHERE GROUP BY HAVING 第58页/共128页59对查询结果进行分组计算分组把一个表划分为子集,可以细化计算函数的作用对象HAVIN
22、G子句指定满足条件的组在一个查询语句中,可以使用任意多个列进行分组分组依据列不能是text,ntext,image和bit类型第59页/共128页60GROUP BY子句的使用查询每位演员拍片数目以及个人平均片酬,个人最低/最高片酬lSELECT 演员姓名,COUNT(片名)拍片数目,AVG(片酬)个人平均片酬,MIN(片酬)个人最低片酬,MAX(片酬)个人最高片酬lFROM 出演lGROUP BY 演员姓名第60页/共128页61第61页/共128页62第62页/共128页63上述查询的结果 第63页/共128页64HAVING子句的功能HAVING子句用于对分组后的结果再进行过滤HAVIN
23、G子句的作用对象是组而不是行在HAVING子句中可以使用计算函数HAVING与GROUP BY子句一起使用第64页/共128页65HAVING子句的使用查询出演不少于2部电影的演员姓名第65页/共128页66第66页/共128页67第67页/共128页68使用HAVING子句的查询方案lSELECT 演员姓名,COUNT(片名)拍片数目lFROM 出演lGROUP BY 演员姓名lHAVING COUNT(*)=2第68页/共128页69上述查询的执行步骤与结果先用GROUP BY按演员姓名分组再用COUNT(*)统计每组包含的记录条数最后挑选记录条数=2的组,返回对应的演员姓名和该组记录的条
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 系统 原理 教学
限制150内