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