数据库讲义ch06Word文档下载推荐.docx

数据库讲义ch06Word文档下载推荐.docx

《数据库讲义ch06Word文档下载推荐.docx》由会员分享，可在线阅读，更多相关《数据库讲义ch06Word文档下载推荐.docx（21页珍藏版）》请在冰豆网上搜索。

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

20998

04300104

7

20

41

69

04300368

23

34

64

04300377

15

28

50

04300477

8

44

75

04300522

26

54

88

04301190

27

57

92

04301205

21

60

04301378

9

56

93

04301388

25

43

04301391

53

87

04301766

49

04302472

24

51

83

04302586

04302610

73

04302633

89

04302634

04302653

04302657

04302667

22

36

04302686

55

04302696

29

04302714

94

04302717

04302768

6

04302809

86

04302853

39

04303364

04303393

04303396

04303550

32

67

04303637

30

04303707

48

78

04303864

66

04303868

04303871

63

04303892

17

04309001

5

29880

04309002

14

SELECTCount（*）,Count（实验）,Count（平时）,Count（期末）,Count（成绩）FROM教与学WHERE课程编号='

Count（*）

Count（实验）

Count（平时）

Count（期末）

Count（成绩）

38

SELECT

Sum（实验）+Sum（平时）+Sum（期末）,

Sum（成绩）,

Sum（实验）+Sum（平时）+Sum（期末）-Sum（成绩）,

Sum（实验+平时+期末-成绩）

FROM教与学WHERE课程编号='

Sum（实验）+

Sum（平时）+

Sum（期末）

Sum（成绩）

Sum（期末）-

2640

2600

40

表达式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（*）计算行数时并不管字段中是否有NULL值存在。

如果没有行，则返回零。

6.1.6删除相同行（DISTINCT）

计算教师职称的数目

SELECTCount（distinct职称）FROM教师

-----------

6.2分组查询（GROUPBY字句）

计算平均成绩

计算每门课程的平均成绩

SELECT课程编号,Avg（成绩）AS成绩之平均值

FROM教与学

GROUPBY课程编号

72.2222

05024702

80.7778

包含GROUPBY子句的查询称为分组查询，在GROUPBY子句中指定的字段称为查询的分组字段，它决定了行是怎样分组的。

从理论上讲，SQL实现该查询有以下步骤：

1）SQL将课程分成课程组。

每一组对应一门课程。

在每一组内，所有课程在“课程编号”字段具有相同的值。

2）对于每一组，SQL计算组中所有行的“成绩”字段的平均值，并且生成一个单一行的统计查询结果。

该行包含该组“课程编号”字段的值以及计算出来的课程的平均值。

求出每个单位中年龄的最大值和最小值

SELECT单位代码,Max（年龄）AS年龄之最大值,Min（年龄）AS年龄之最小值

FROM教师

GROUPBY单位代码

单位代码

年龄之最大值

年龄之最小值

01

0103

0509

65

10

求出2005年每个单位所开设的专业数

SELECT单位代码,Count（*）AS专业代码之计数

FROM专业设置

WHERE（（（年度）=2005））

03

04

06

3

07

11

08

4

6.2.1多个字段分组

计算每年每个单位的所开设专业的总和

SELECT年度,单位代码,Count（专业代码）AS专业代码之计数

GROUPBY年度,单位代码

年度

2004

2005

即使是用多个分组字段，SQL也仅能提供单一层次的分组。

在SQL中，不能用两个层次的小计来创建组和子组。

6.2.2对分组查询的限制

分组字段必须是FROM子句中指定表的实际字段。

不能以表达式计算的值为依据对行进行分组。

但在某些DBMS中是允许的，例如Access。

对每—行组来说，所有选择清单中的项目必须是单一的值。

选择项目可能是：

●常量；

●字段函数，该函数统计组中的行时产生单一的值；

●分组字段，该字段在每组的每行中具有相同的值；

●包含上述各类组合的表达式。

计算每个学生的平均成绩，要求显示学号、姓名、平均成绩

SELECT教与学.学号,学生.姓名,Avg（教与学.成绩）AS成绩之平均值

FROM教与学INNERJOIN学生ON教与学.学号=学生.学号

GROUPBY教与学.学号

服务器:

消息8120，级别16，状态1，行1

列'

学生.姓名'

在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在GROUPBY子句中。

按照学号分组实际上与按姓名分组是一样的，但是SQL报告了错误，为了解决这个问题，只要简单地将“姓名”也作为分组字段就可以了，分组“姓名”是冗余的。

GROUPBY教与学.学号,学生.姓名

姓名

刘洪斌

70

陈智球

罗晓生

李超

张兆营

王璐

郭凤宇

69.5

蔡亚梅

93.5

吴挺

79.5

洪文达

夏寿海

沙洋娟

87.5

徐小虎

侯蕾

杜鹃

88.5

吴群彪

76

张微

90

张小伟

韩可可

47.5

蔡成凯

赵小俊

董莺

95

张豆

芮丹平

78.5

袁国平

孟祥昊

73.5

廖绪国

杨旭

陈凤

91.5

雒斌

71.5

文能

72

张里

布麦尔叶木．吐尔

72.5

穆占虎

买买提艾力．艾孜

51.5

何芝盛

58

贾德

徐霓

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

Harry

Joanne

Joel

Kevin

Louise

Marie

Blonde

Mary

Paula

Samantha

Susan

selecthair,eye,count（*）

frompeople

groupbyhair,eye

Expr1002

2

6.3分组搜索条件（HAVING子句）

求出平均成绩大于等于80分的学生及平均成绩

HAVING（（（Avg（教与学.成绩））>

=80））

列出年度开设专业超过4个专业的单位，包括年度、名称、专业数

SELECT专业设置.年度,单位.名称,Count（*）AS专业代码之计数

FROM（专业设置INNERJOIN专业ON专业设置.专业代码=专业.代码）INNERJOIN单位ON专业设置.单位代码=单位.代码

GROUPBY专业设置.年度,单位.名称,专业设置.单位代码

HAVING（（（Count（*））>

=5））

ORDERBY专业设置.年度,专业设置.单位代码

名称

机械工程学院

化工学院

电子工程与光电技术学院

经济管理学院

理学院

人文与社会科学学院

SQL查询处理规则：

1）如果语句是SELECT的UNION语句，请将步骤2到步骤7应用到每个语句以生成它们独自的查询结果。

2）形成FROM子句中指定的乘积表。

如果FROM子句仅指定一个表则乘积就是这个表。

3）如果有WHERE子句，应用其搜索条件到乘积表中的每一行，保留搜索条件为TRUE的行，去除搜索条件为FALSE或NULL的行。

4）如果存在GROUPBY子句，将乘积表中保留的行分成行组，这样每组中的行在所有分组字段中具有相同的值。

5）如果存在HAVING于句，将其搜索条件应用到每个行组中，保留搜索条件为TRUE的那些行组，去除搜索条件为FALSE或NULL的行组。

6）对于每个保留的行（或行组），计算选定清单中每个项目的值并生成单独的查询结果。

对于简