数据库讲义ch06.docx
- 文档编号:7742300
- 上传时间:2023-01-26
- 格式:DOCX
- 页数:21
- 大小:265.02KB
数据库讲义ch06.docx
《数据库讲义ch06.docx》由会员分享,可在线阅读,更多相关《数据库讲义ch06.docx(21页珍藏版)》请在冰豆网上搜索。
数据库讲义ch06
6统计查询
6.1字段函数(聚合函数)
聚合函数(aggregatefunction)
一种函数,它对一组行中的某个列执行计算,并返回单个值。
聚合函数
返回值
Sum()
列的总和。
Avg()
列的平均值
Min()
列中的最小值。
Max()
列中的最大值。
Count()
列中值的数目。
Count(*)
查询结果的行数。
求出学生的平均成绩
SELECTAVG(成绩)AS平均成绩FROM教与学
平均成绩
76.5
6.1.1计算字段的总和
SUM()字段函数计算字段数据的总和。
该字段中的数据必须只有数字类型(整型,小数型,浮点型或货币型)。
SUM()函数的结果与字段中数据的基本类型相同。
求出所有课程的学分数总和与学时数总和
SELECTSum(学分)AS学分之总计,Sum(学时)AS学时之总计FROM课程
学分之总计
学时之总计
278.5
4456
该语句如在SQLServer2000中执行将有如下的提示信息:
(所影响的行数为1行)
警告:
聚合或其它SET操作消除了空值。
(后续章节介绍)
求出杜鹃同学的总成绩
SELECTSum(教与学.成绩)AS成绩之总计
FROM学生INNERJOIN教与学ON学生.学号=教与学.学号
WHERE学生.姓名='杜鹃'
成绩之总计
177
6.1.2计算字段的平均值
计算编号为05024702的课程平均成绩
SELECTAvg(成绩)AS成绩之平均值FROM教与学
WHERE课程编号='05024702'
成绩之平均值
80.7777777777778
计算工业工程系教师的平均年龄
SELECTAvg(教师.年龄)AS年龄之平均值
FROM单位INNERJOIN教师ON单位.代码=教师.单位代码
WHERE单位.名称='工业工程系'
年龄之平均值
42.5555555555556
6.1.3求极值(Max和Min)
求出课程中学分最大和最小值
SELECTMax(学分)AS学分之最大值,Min(学分)AS学分之最小值
FROM课程
学分之最大值
学分之最小值
13
1
求出最近出版的教材
SELECTMax(出版年月)AS出版年月之最大值FROM教材
出版年月之最大值
2006-7-1
求出实践学时占授课学时的最大百分比
SELECTMax(实践学时/授课学时*100)FROM课程结构
Expr1000
19.4570135746606
Max和Min的自变量可以是常量、列名、函数以及算术运算符和字符串运算符的任意组合。
可用于数字列、字符列和日期列,但不能用于布尔列。
6.1.4求计数值(count)
求05014001班级中学生的数
SELECTCount(姓名)AS姓名之计数FROM学生WHERE班级='05014001'
姓名之计数
33
求微机原理及应用(编号为01028203)课程成绩及格的人数
SELECTCount(成绩)AS成绩之计数FROM教与学
WHERE成绩>=60AND课程编号='01028203'
成绩之计数
31
求2005年所开设的专业数
SELECTCount(专业代码)FROM专业设置WHERE年度=2005
专业代码之计数
61
注意,count函数忽略了字段中数据项目的值,只是简单地计算有多少数据项。
因此,它实际上不关心你指定那个字段作为count函数的自变量。
下面的查询也有同样的结果:
SELECTCount(单位代码)FROM专业设置WHERE年度=2005
事实上,将这个查询考虑成“计算有多少个专业代码”或“计算有多少单位代码”都是不明智的,更容易想到的是“计算多少行”。
为此,SQL支持一个特殊的count(*)字段函数,该函数计算行而不是数据值。
下面是同样的查询,用count(*)函数重写:
SELECTCount(*)FROM专业设置WHERE年度=2005
将count(*)函数看作“行计数”函数,可使这个查询更易于阅读。
实际上,几乎总是用count(*)函数进行行计数,而不是用count()函数。
6.1.5NULL值和字段函数
SUM()、AVG()、MIN()、MAX()和COUNT()字段函数把字段(列)的数据值作为它们的自变量、并生成一个单一数据值的结果。
如果字段(列)中一个或多个数据的值为NULL,那会怎样呢?
标准规定,字段函数将忽略字段(列)中所有的NULL值。
列出微机原理及应用(01028203)课程的成绩表
SELECT*FROM教与学WHERE课程编号='01028203'
课程编号
职工号
学号
实验
平时
期末
成绩
01028203
20998
04300104
7
20
41
69
01028203
20998
04300368
7
23
34
64
01028203
20998
04300377
7
15
28
50
01028203
20998
04300477
8
23
44
75
01028203
20998
04300522
8
26
54
88
01028203
20998
04301190
8
27
57
92
01028203
20998
04301205
8
21
31
60
01028203
20998
04301378
9
28
56
93
01028203
20998
04301388
7
25
43
75
01028203
20998
04301391
9
25
53
87
01028203
20998
04301766
8
26
15
49
01028203
20998
04302472
8
24
51
83
01028203
20998
04302586
8
25
27
60
01028203
20998
04302610
8
21
44
73
01028203
20998
04302633
8
28
53
89
01028203
20998
04302634
8
25
31
64
01028203
20998
04302653
9
26
53
88
01028203
20998
04302657
8
24
28
60
01028203
20998
04302667
8
22
8
36
01028203
20998
04302686
8
24
55
87
01028203
20998
04302696
8
29
56
93
01028203
20998
04302714
8
29
57
94
01028203
20998
04302717
9
28
56
93
01028203
20998
04302768
6
24
43
73
01028203
20998
04302809
7
26
53
86
01028203
20998
04302853
7
23
39
69
01028203
20998
04303364
6
23
31
60
01028203
20998
04303393
7
21
26
54
01028203
20998
04303396
8
29
57
94
01028203
20998
04303550
8
27
32
67
01028203
20998
04303637
7
23
30
60
01028203
20998
04303707
8
22
48
78
01028203
20998
04303864
9
27
30
66
01028203
20998
04303868
8
28
24
60
01028203
20998
04303871
7
20
36
63
01028203
20998
04303892
8
23
17
48
01028203
20998
04309001
5
15
01028203
29880
04309002
6
14
SELECTCount(*),Count(实验),Count(平时),Count(期末),Count(成绩)FROM教与学WHERE课程编号='01028203'
Count(*)
Count(实验)
Count(平时)
Count(期末)
Count(成绩)
38
38
38
36
36
(所影响的行数为1行)
警告:
聚合或其它SET操作消除了空值。
SELECT
Sum(实验)+Sum(平时)+Sum(期末),
Sum(成绩),
Sum(实验)+Sum(平时)+Sum(期末)-Sum(成绩),
Sum(实验+平时+期末-成绩)
FROM教与学WHERE课程编号='01028203'
Sum(实验)+
Sum(平时)+
Sum(期末)
Sum(成绩)
Sum(实验)+
Sum(平时)+
Sum(期末)-
Sum(成绩)
Sum(实验+平时+期末-成绩)
2640
2600
40
0
(所影响的行数为1行)
警告:
聚合或其它SET操作消除了空值。
表达式Sum(实验)+Sum(平时)+Sum(期末)-Sum(成绩)和表达式Sum(实验+平时+期末-成绩)应该产生相同的结果,结果却不是,这是因为在“期末”和“成绩”列中存在着NULL值。
Sum(实验)+Sum(平时)+Sum(期末)总计了38个学生的实验成绩、38个学生的平时成绩和36个学生的期末成绩。
Sum(成绩)仅仅总计了36个学生的成绩。
(Sum(实验)+Sum(平时)+Sum(期末))-Sum(成绩)计算了三个总计与一个总计的差。
Sum(实验+平时+期末-成绩)仅仅将38个中的36个非NULL的学生的实验、平时、期末与总成绩的差作为自变量,学号04309001:
5+15+NULL-NULL的结果为NULL,这个差未被计算入总计,学号04309002:
6+14+NULL-NULL的结果为NULL,这个差也未被计算入总计。
处理NULL值的规则:
●所有数值为NULL的字段都将被字段函数忽略。
●如果字段中的每一个数据项目均为NULL,那么SUM()、AVG()、MIN()和MAX()字段函数返回NULL值:
COUNT()函数返回零。
●如果字段中没有数据项(即字段是空的),那么SUM()、AVG()、MIN()和MAX()字段函数返回NULL值:
COUNT()函数返回零。
●COUNT(*)计算行数时并不管字段中是否有NULL值存在。
如果没有行,则返回零。
6.1.6删除相同行(DISTINCT)
计算教师职称的数目
SELECTCount(distinct职称)FROM教师
-----------
6
(所影响的行数为1行)
6.2分组查询(GROUPBY字句)
计算平均成绩
SELECTAvg(成绩)AS成绩之平均值FROM教与学
成绩之平均值
76.5
计算每门课程的平均成绩
SELECT课程编号,Avg(成绩)AS成绩之平均值
FROM教与学
GROUPBY课程编号
课程编号
成绩之平均值
01028203
72.2222
05024702
80.7778
包含GROUPBY子句的查询称为分组查询,在GROUPBY子句中指定的字段称为查询的分组字段,它决定了行是怎样分组的。
从理论上讲,SQL实现该查询有以下步骤:
1)SQL将课程分成课程组。
每一组对应一门课程。
在每一组内,所有课程在“课程编号”字段具有相同的值。
2)对于每一组,SQL计算组中所有行的“成绩”字段的平均值,并且生成一个单一行的统计查询结果。
该行包含该组“课程编号”字段的值以及计算出来的课程的平均值。
求出每个单位中年龄的最大值和最小值
SELECT单位代码,Max(年龄)AS年龄之最大值,Min(年龄)AS年龄之最小值
FROM教师
GROUPBY单位代码
单位代码
年龄之最大值
年龄之最小值
0
0
01
55
55
0103
25
25
0509
65
28
10
56
56
求出2005年每个单位所开设的专业数
SELECT单位代码,Count(*)AS专业代码之计数
FROM专业设置
WHERE(((年度)=2005))
GROUPBY单位代码
单位代码
专业代码之计数
01
15
03
8
04
5
06
3
07
11
08
4
10
3
11
6
14
1
15
5
6.2.1多个字段分组
计算每年每个单位的所开设专业的总和
SELECT年度,单位代码,Count(专业代码)AS专业代码之计数
FROM专业设置
GROUPBY年度,单位代码
年度
单位代码
专业代码之计数
2004
01
15
2004
03
7
2004
04
5
2004
06
3
2004
07
11
2004
08
4
2004
10
3
2004
11
6
2004
14
1
2004
15
5
2005
01
15
2005
03
8
2005
04
5
2005
06
3
2005
07
11
2005
08
4
2005
10
3
2005
11
6
2005
14
1
2005
15
5
即使是用多个分组字段,SQL也仅能提供单一层次的分组。
在SQL中,不能用两个层次的小计来创建组和子组。
6.2.2对分组查询的限制
分组字段必须是FROM子句中指定表的实际字段。
不能以表达式计算的值为依据对行进行分组。
但在某些DBMS中是允许的,例如Access。
对每—行组来说,所有选择清单中的项目必须是单一的值。
选择项目可能是:
●常量;
●字段函数,该函数统计组中的行时产生单一的值;
●分组字段,该字段在每组的每行中具有相同的值;
●包含上述各类组合的表达式。
计算每个学生的平均成绩,要求显示学号、姓名、平均成绩
SELECT教与学.学号,学生.姓名,Avg(教与学.成绩)AS成绩之平均值
FROM教与学INNERJOIN学生ON教与学.学号=学生.学号
GROUPBY教与学.学号
服务器:
消息8120,级别16,状态1,行1
列'学生.姓名'在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在GROUPBY子句中。
按照学号分组实际上与按姓名分组是一样的,但是SQL报告了错误,为了解决这个问题,只要简单地将“姓名”也作为分组字段就可以了,分组“姓名”是冗余的。
SELECT教与学.学号,学生.姓名,Avg(教与学.成绩)AS成绩之平均值
FROM教与学INNERJOIN学生ON教与学.学号=学生.学号
GROUPBY教与学.学号,学生.姓名
学号
姓名
成绩之平均值
04300104
刘洪斌
70
04300368
陈智球
70
04300377
罗晓生
63
04300477
李超
76.5
04300522
张兆营
83
04301190
王璐
93
04301205
郭凤宇
69.5
04301378
蔡亚梅
93.5
04301388
吴挺
79.5
04301391
洪文达
88
04301766
夏寿海
63
04302472
沙洋娟
87.5
04302586
徐小虎
66
04302610
侯蕾
76.5
04302633
杜鹃
88.5
04302634
吴群彪
76
04302653
张微
90
04302657
张小伟
70
04302667
韩可可
47.5
04302686
蔡成凯
88
04302696
赵小俊
90
04302714
董莺
95
04302717
张豆
93
04302768
芮丹平
78.5
04302809
袁国平
86
04302853
孟祥昊
73.5
04303364
廖绪国
70
04303393
杨旭
63
04303396
陈凤
91.5
04303550
雒斌
71.5
04303637
文能
72
04303707
张里
83
04303864
布麦尔叶木.吐尔
72.5
04303868
穆占虎
66
04303871
买买提艾力.艾孜
51.5
04303892
何芝盛
58
04309001
贾德
04309002
徐霓
0501400101
区燕兰
0501400102
杨静怡
0501400103
王孝祥
0501400104
徐敏
0501400105
殷存磊
0501400106
何宇风
0501400107
韩波
0501400108
郑振武
0501400109
郭亮斌
0501400110
赵国腾
0501400111
卢志裕
0501400112
王钊
0501400113
贾振杰
0501400114
高志国
0501400115
钱卫国
0501400116
刘岗
0501400117
郑凌兵
0501400118
胡扬
0501400119
杨瑞飞
0501400120
江斌
0501400121
孙周
0501400122
赵凯凯
0501400123
费一正
0501400124
袁伟
0501400125
王健
0501400126
杨小虎
0501400127
沈虓
0501400128
丁小祥
0501400129
晁先功
0501400130
王斌
0501400131
吉旭
0501400133
陶涛
6.2.3分组字段中的NULL值
如果分组列包含一个空值,那么该行将成为结果中的一个组。
如果分组列包含多个空值,那么这些空值将放入一个组中。
此行为在SQL-92标准中定义。
people
name
hair
eye
Cindy
Brown
Blue
George
Brown
Harry
Blue
Joanne
Joel
Brown
Brown
Kevin
Brown
Louise
Blue
Marie
Blonde
Blue
Mary
Brown
Paula
Brown
Samantha
Susan
Blonde
Blue
selecthair,eye,count(*)
frompeople
groupbyhair,eye
hair
eye
Expr1002
2
Blue
2
Blonde
Blue
2
Brown
4
Brown
Blue
1
Brown
Brown
1
6.3分组搜索条件(HAVING子句)
求出平均成绩大于等于80分的学生及平均成绩
SELECT教与学.学号,学生.姓名,Avg(教与学.成绩)AS成绩之平均值
FROM教与学INNERJOIN学生ON教与学.学号=学生.学号
GROUPBY教与学.学号,学生.姓名
HAVING(((Avg(教与学.成绩))>=80))
学号
姓名
成绩之平均值
04300522
张兆营
83
04301190
王璐
93
04301378
蔡亚梅
93.5
04301391
洪文达
88
04302472
沙洋娟
87.5
04302633
杜鹃
88.5
04302653
张微
90
04302686
蔡成凯
88
04302696
赵小俊
90
04302714
董莺
95
04302717
张豆
93
04302809
袁国平
86
04303396
陈凤
91.5
04303707
张里
83
列出年度开设专业超过4个专业的单位,包括年度、名称、专业数
SELECT专业设置.年度,单位.名称,Count(*)AS专业代码之计数
FROM(专业设置INNERJOIN专业ON专业设置.专业代码=专业.代码)INNERJOIN单位ON专业设置.单位代码=单位.代码
GROUPBY专业设置.年度,单位.名称,专业设置.单位代码
HAVING(((Count(*))>=5))
ORDERBY专业设置.年度,专业设置.单位代码
年度
名称
专业代码之计数
2004
机械工程学院
15
2004
化工学院
7
2004
电子工程与光电技术学院
5
2004
经济管理学院
11
2004
理学院
6
2004
人文与社会科学学院
5
2005
机械工程学院
15
2005
化工学院
8
2005
电子工程与光电技术学院
5
2005
经济管理学院
11
2005
理学院
6
2005
人文与社会科学学院
5
SQL查询处理规则:
1)如果语句是SELECT的UNION语句,请将步骤2到步骤7应用到每个语句以生成它们独自的查询结果。
2)形成FROM子句中指定的乘积表。
如果FROM子句仅指定一个表则乘积就是这个表。
3)如果有WHERE子句,应用其搜索条件到乘积表中的每一行,保留搜索条件为TRUE的行,去除搜索条件为FALSE或NULL的行。
4)如果存在GROUPBY子句,将乘积表中保留的行分成行组,这样每组中的行在所有分组字段中具有相同的值。
5)如果存在HAVING于句,将其搜索条件应用到每个行组中,保留搜索条件为TRUE的那些行组,去除搜索条件为FALSE或NULL的行组。
6)对于每个保留的行(或行组),计算选定清单中每个项目的值并生成单独的查询结果。
对于简
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 讲义 ch06