第四讲 SQL计算与分组统计

3.2 数值型
数值型数据是可以进行四则运算的数据，分为整形和实 型。 ACCESS数值类型的种类及其取值范围
数字类型 字节(整) 整数 长整数 单精度数 双精度数 0～255
值的范围 32768～32767 2 147 483 648～2 147 483 647 3.4×1038～3.4×1038 1.79734×10308～ 1.79734×10308
10. HAVING 子句
SELECT 学生编号, avg(成绩) as avgC, count(成绩) as countC, max(成绩) As maxC, min(成绩) As minC, sum(成绩) As sumC, sum(成绩)/count(成绩) As averageC FROM 选课成绩 GROUP BY学生编号 HAVING 学生编号=‘980104’
1.字符型 2.数值型 3.日期时间型 4.逻辑型
3.1字符型
字符型数据描述不具有计算能力的文字数 据类型，是最常用的数据类型之一。 字符型数据（Character）是由汉字和 ASCII字符(英文字符、数字字符、空格及其 他专用字符)组成; 使用时必须用定界符双引号(“”)或单引号 （‘ ’)括起来。
2.3 ACCESS-SQL支持项函数 支持项函数
用表达式生成器生成表达式并不总是方便的，其意义 用表达式生成器生成表达式并不总是方便的， 在于我们可以从中知道特定的SQL提供了那些函数， 提供了那些函数， 在于我们可以从中知道特定的 提供了那些函数 及其函数的语法。 及其函数的语法。
3.常用数据类型 常用数据类型
10. HAVING 子句
SELECT 学生编号, avg(成绩) as avgC, count(成绩) as countC, max(成绩) As maxC, min(成绩) As minC, sum(成绩) As sumC, sum(成绩)/count(成绩) As averageC FROM 选课成绩 GROUP BY学生编号 HAVING left(学生编号)=‘98’
小数位数 无 无 无 7 15
字段长度 1字节 2字节 4字节 4字节 8字节
3.3 日期时间型
日期时间型数据（Date Time）是描述日期 和时间的数据。 其默认格式为{mm/dd/yyyy hh:mm:ss}。
– 其中yyyy代表年,前两个mm代表月，dd代表日， hh代表小时,后两个mm代表分钟，ss代表秒。
– 函数名(自变量表 函数名 自变量表) 自变量表
自变量表可以是一个变量或多个自变量，也可以为空，即：函 自变量表可以是一个变量或多个自变量，也可以为空， 数名()，函数名后面的一对圆括号不能省略。 数名 ，函数名后面的一对圆括号不能省略。
按功能可划分为： 按功能可划分为：
– 数学函数、字符串函数、日期和时间函数、类型转换 数学函数、字符串函数、日期和时间函数、 函数、测试函数。 函数、测试函数。
11.WHERE 和 HAVING
WHERE 针对被查询表所提的条件，不能用计算项； HAVING 子句是针对SELECT结果的，可以用已经命名的 计算项。 作业： 7. 在选课成绩表中求“99”级每个学生的选课门数。分别 写出用“WHERE”和“HAVING”子句实现的SELECT语句。 8. 在选课成绩表中求“101”课程的选课人数，最高分，最 低分，平均成绩。分别写出用“WHERE”和“HAVING”子 句实现的SELECT语句。 9.在选课成绩表中求“101”，“102”课程的选课人数，最 高分，最低分，平均成绩。分别写出用“WHERE”和 “HAVING”子句实现的SELECT语句。
5.1数学函数(数值函数 数学函数 数值函数 数学函数 数值函数)
SELECT 选课成绩.学生编号 学生编号, 选课成绩 学生编号 选课成绩.课程编号 课程编号, 选课成绩 课程编号 选课成绩.成绩 成绩 成绩, 成绩]-80) as 成绩 成绩2, 选课成绩 成绩 ([成绩 Abs([成绩 成绩]-80) AS abs成绩 成绩2, 成绩 成绩 ([成绩 成绩]/23) as 成绩 ， 成绩3， 成绩 Round ([成绩 成绩]/23) as Round成绩 成绩3 成绩 成绩 FROM 选课成绩 选课成绩;
2.QUERY-交互式查询界面 交互式查询界面 SQL-SELECT生成器 生成器
2.1 启动 启动QUERY
2.2 定义查询
右键快捷
例子
成绩>=60
‘合格’ ‘不合格’
例子
改为：, 选课成绩.课程编号, IIf([成 绩]>=60,'合格','不合格') AS 考查总评 FROM 选课成绩;
9. 多项分组
SELECT 学生编号,课程编号, avg(成绩) as avgC, count(成绩) as countC, max(成绩) As maxC, min(成绩) As minC, sum(成绩) As sumC, sum(成绩)/count(成绩) As averageC FROM 选课成绩 GROUP BY学生编号,课程编号
第四讲 SQL计算与分组统计 计算与分组统计
1. 计算项的必要性
例子
Select 学生编号，课程编号，成绩， iif(成绩>=60,’合格’,’不合格’) as 考查总评 From 选课成绩
例子
Select 学生编号，课程编号，成绩，iif(成绩>=60,’合 格’,’不合格’) as 考查总评，（成绩-60）/20+1 as 绩点 From 选课成绩
– 数值型：整数、小数或科学计数 数值型：整数、 – 字符型：用 “ ” 、 ‘ ’括起来 字符型： – 逻辑型：T 或 F 逻辑型：
4.2变 量（项的名称） 变 项的名称）
在命令操作、程序运行过程中其值可以变化的量。 在命令操作、程序运行过程中其值可以变化的量。 包括： 包括：
– 内存变量 – 字段变量。 字段变量。
作业1：编制一个查询测试Int,Fix,Sgn函数的功能.
5.2字符串函数 字符串函数
字符串长度函数：LEN 字符串长度函数： 取左、右子串函数 取左、右子串函数LEFT|RIGHT 删除字符串中空格：LTRIM, RTRIM和 删除字符串中空格： 和 TRIM
SELECT 选课成绩.学生编号 学生编号, 选课成绩 学生编号 选课成绩.课程编号 课程编号, 选课成绩 课程编号 [学生编号 课程编号 AS 学生课程编号 学生编号]+[课程编号 学生课程编号, 学生编号 课程编号] Len([学生编号 AS len学生编号 学生编号]) 学生编号, 学生编号 学生编号 ' '+[课程编号 AS 表达式 课程编号] 表达式1, 课程编号 Ltrim(' '+[课程编号 AS ltrim表达式 课程编号]) 表达式1 课程编号 表达式 FROM 选课成绩 选课成绩;
长度固定为8位。 省略时间部分可表示日期。
3.4 逻辑型
逻辑型数据（Logic）是描述客观事物真假 的数据，用于表示逻辑判断结果。 逻辑型数据只有真（.T.）和假（.F.）两种 值， 长度固定为1位。
4.常值、变量 常值、 常值
常量 变量
4.1、常 量 、
分类（根据其数据类型） 分类（根据其数据类型）
8. 分组子句： GROUP BY
SELECT 学生编号, avg(成绩) as avgC, count(成绩) as countC, max(成绩) As maxC, min(成绩) As minC, sum(成绩) As sumC, sum(成绩)/count(成绩) As averageC FROM 选课成绩 GROUT BY 学生编号; 作业： 4. 在选课成绩表中求每个学生的选课门数； 5. 在选课成绩表中求按课程的选课人数，最高分，最低分，平 均成绩； 6. 在选课成绩表中求每个学生的绩点（用IIF函数计算绩点）。
7. SQL分类 统计函数
平均 计数 最大/最小 求和
SQL分类统计函数
SELECT avg(成绩) as avgC, count(成绩) as countC, max(成绩) As maxC, min(成绩) As minC, sum(成绩) As sumC, sum(成绩)/count(成绩) As averageC FROM 选课成绩;
命名规则
– – – – 1. 以字母、汉字、下划线开头； 以字母、汉字、下划线开头； 2. 由字母、汉字、数字、下划线组成； 由字母、汉字、数字、下划线组成； 3. 至多 至多128个/10字符； 字符； 个 字符 4. 不可与系统保留字同名。 不可与系统保留字同名。
5.函数 函数
函数是预先编好的一系列子程序 应用遇到此类问题就以调用相应的函数,也就是执 应用遇到此类问题就以调用相应的函数 也就是执 行相应的函数子程序。 行相应的函数子程序。 函数的一般形式是： 函数的一般形式是：
作业2：编制一个查询测试Trim,Rtrim,Left,Right函数 的功能.
6.IFF函数嵌套 函数嵌套
SELECT 学生编号, 课程编号, 成绩,
IIf([成绩]>=60,'合格',IIf([成绩]>=40,'允许补考','不允许补考')) AS 表达式1
FROM 选课成绩;
作业3：编制一 个查询将成绩 分为A,B,C,D 四级。