Access查询设计汇总课件.ppt

1、第四章 查 询【本章主要掌握内容本章主要掌握内容】查询的基本概念查询的基本概念查询的功能与分类查询的功能与分类查询的创建与设计查询的创建与设计SQL查询查询第 一 节查 询 的 概 述 在设计数据库时，常常把数据分类，并分别存在设计数据库时，常常把数据分类，并分别存放在多个表中，但在使用时需要检索一个或多个放在多个表中，但在使用时需要检索一个或多个表中符合条件的数据。表中符合条件的数据。查询实际上就是将这些分查询实际上就是将这些分散的数据再集中起来。散的数据再集中起来。xhxmxb1001张三男xhkcdhcj10010185kcdhkcmc01数据库应用学学生生表表成成绩绩表表课课程程表表x

2、mkcmccj张三数据库应用 85查询结果查询结果一、查询的概念一、查询的概念 查询是根据一定的条件，从一个或多个表中查询是根据一定的条件，从一个或多个表中提提取数据取数据并进行加工处理，返回一个新的数据集合。并进行加工处理，返回一个新的数据集合。 利用查询可以实现数据的统计分析与计算等操利用查询可以实现数据的统计分析与计算等操作。作。 查询结果可以作为其他查询、窗体、报表的查询结果可以作为其他查询、窗体、报表的数数据源据源。 查询是一张查询是一张“虚表虚表”，是是动态的动态的数据集合。数据集合。查询主要有以下几个方面的功能1）选择字段和记录选择字段和记录选择字段：在查询的结果中可以只含有表中

3、的部选择字段：在查询的结果中可以只含有表中的部分字段。分字段。选择记录：在查询的结果中，只含有符合指定条选择记录：在查询的结果中，只含有符合指定条件的记录。件的记录。2）编辑记录编辑记录 即利用查询添加、修改和删除表中的记录。即利用查询添加、修改和删除表中的记录。3）统计和统计和计算计算 即在查询中进行各种统计计算，也可以建立一即在查询中进行各种统计计算，也可以建立一个计算字段来保存计算的结果。个计算字段来保存计算的结果。4）建立新表建立新表利用查询的结果创建一个新表。利用查询的结果创建一个新表。5）为其他数据库对象提供数据源为其他数据库对象提供数据源 即以查询的结果作为查询、窗体或报表的数据

4、源。即以查询的结果作为查询、窗体或报表的数据源。二、查询的种类二、查询的种类 在在Access 2010中，可以创建中，可以创建5种类型的查询：种类型的查询：1、选择查询、选择查询 是根据指定的条件，从一个或多个表中获取数是根据指定的条件，从一个或多个表中获取数据并显示结果。据并显示结果。 选择查询可以对记录进行分组，并且对分组的选择查询可以对记录进行分组，并且对分组的记录进行求和、计数、求平均值以及其它类型的记录进行求和、计数、求平均值以及其它类型的计算。计算。 选择查询产生的结果是一个动态的记录集，不选择查询产生的结果是一个动态的记录集，不会改变源数据表中的数据。会改变源数据表中的数据。2

5、、交叉表查询、交叉表查询 交叉表查询是对基表或查询中的数据进行计算交叉表查询是对基表或查询中的数据进行计算和重构，以方便分析数据。和重构，以方便分析数据。 能够汇总数字型字段的值，将汇总计算的结果能够汇总数字型字段的值，将汇总计算的结果显示在行与列交叉的单元格中。显示在行与列交叉的单元格中。3、参数查询、参数查询 是一种特殊的选择查询，即根据用户输入的参是一种特殊的选择查询，即根据用户输入的参数作为查询的条件。输入不同的参数，将得到不数作为查询的条件。输入不同的参数，将得到不同的结果。同的结果。 执行参数查询时，将会显示一个对话框，以提执行参数查询时，将会显示一个对话框，以提示输入参数信息。示

6、输入参数信息。 参数查询可作为窗体和报表的基础。参数查询可作为窗体和报表的基础。4、操作查询、操作查询 查询除了按指定的条件从数据源中检索记录外，查询除了按指定的条件从数据源中检索记录外，还可以对检索的记录进行编辑操作。还可以对检索的记录进行编辑操作。 操作查询可以分为：操作查询可以分为：删除查询删除查询 从一个或多个表中删除一组符合条件的记录。从一个或多个表中删除一组符合条件的记录。更新查询更新查询 对一个或多个表中的一组符合条件的记录进行对一个或多个表中的一组符合条件的记录进行批量修改某字段的值。批量修改某字段的值。追加查询追加查询 将一个或多个表中的一组符合条件的记录添加将一个或多个表中

7、的一组符合条件的记录添加到另一个表的末尾。到另一个表的末尾。生成表查询生成表查询 将查询的结果转存为新表。将查询的结果转存为新表。5、SQL查询查询 SQL（Structured Query Language）是一种）是一种结构化查询语言，是数据库操作的工业化标准语结构化查询语言，是数据库操作的工业化标准语言，使用言，使用SQL语言可以对任何数据库管理系统进语言可以对任何数据库管理系统进行操作。行操作。 所谓的所谓的SQL查询就是查询就是通过通过SQL语言来创建的查语言来创建的查询。询。 在查询设计视图中创建任何一个查询时，系统在查询设计视图中创建任何一个查询时，系统都将在后台构建等效的都将在

8、后台构建等效的SQL语句。大多数查询功语句。大多数查询功能也都可以直接使用能也都可以直接使用SQL语句来实现。语句来实现。 有一些无法在查询设计视图中创建的有一些无法在查询设计视图中创建的SQL查询查询称为称为“SQL特定查询特定查询”。 SQL特定查询包括：特定查询包括：联合查询联合查询 联合查询是联合查询是将多个表或查询中的字段合并到将多个表或查询中的字段合并到查询结果的一个字段中。查询结果的一个字段中。 使用联合查询可以合并多个表中的数据，并使用联合查询可以合并多个表中的数据，并可以根据联合查询生成一个新表可以根据联合查询生成一个新表。传递查询传递查询 传递查询可以传递查询可以直接将命令

9、发送到直接将命令发送到ODBC数据数据库服务器中，而不需要事先建立链接。库服务器中，而不需要事先建立链接。 利用传递查询可以直接使用其它数据库管理利用传递查询可以直接使用其它数据库管理系统中的表。系统中的表。P154数据定义查询数据定义查询 利用数据定义查询可以创建、删除或更改表，利用数据定义查询可以创建、删除或更改表，或者在数据库表中创建索引。或者在数据库表中创建索引。子查询子查询 是包含在另一个查询之内的是包含在另一个查询之内的SQL-SELECT语语句，即嵌套在查询中的查询。句，即嵌套在查询中的查询。几点说明：几点说明： 创建创建传递查询、数据定义查询和联合查询不能传递查询、数据定义查询

10、和联合查询不能在在设计视图设计视图中创建，必须中创建，必须直接在直接在SQL视图视图中输入中输入相应的相应的SQL语句。语句。 创建子查询可以直接在创建子查询可以直接在SQL视图中输入相应的视图中输入相应的SQL语句，或在设计视图的语句，或在设计视图的【字段字段】或或【条件条件】行中输入行中输入SQL语句，即将子查询作为查询的条件语句，即将子查询作为查询的条件。 选择查询选择查询 参数查询参数查询 交叉表查询交叉表查询 操作查询操作查询 SQL查询查询查询类型查询类型删除查询删除查询更新查询更新查询追加查询追加查询生成表查询生成表查询联合查询联合查询传递查询传递查询数据定义查询数据定义查询子查

11、询子查询三、查询视图三、查询视图 查询有查询有5种视图，分别是：种视图，分别是： 设计视图设计视图 数据表视图数据表视图 SQL视图视图 数据透视表视图数据透视表视图 数据透视图视图数据透视图视图 设计视图设计视图 即为查询设计器，通过该视图可能创建除即为查询设计器，通过该视图可能创建除SQL之外的各种类型的查询。之外的各种类型的查询。 数据表视图数据表视图 是查询的数据浏览器，用于查看查询运行的结是查询的数据浏览器，用于查看查询运行的结果。果。 SQL视图视图 是查看和编辑是查看和编辑SQL语句的窗口，用于查看和编语句的窗口，用于查看和编辑用查询设计器创建的查询所产生的辑用查询设计器创建的查

12、询所产生的SQL语句。语句。 数据透视表视图和数据图视图数据透视表视图和数据图视图 在此两种视图中，可以根据需要生成数据透视在此两种视图中，可以根据需要生成数据透视表或数据透视图，从而得到直观的数据分析结果。表或数据透视图，从而得到直观的数据分析结果。四、创建查询的方法（界面方法）四、创建查询的方法（界面方法） 创建查询创建查询（界面方法）（界面方法）有有2种方法：种方法： 使用查询设计视图创建查询使用查询设计视图创建查询 使用查询向导创建查询使用查询向导创建查询1、使用查询设计视图创建查询、使用查询设计视图创建查询 使用查询设计视图创建查询首先要打开查询的使用查询设计视图创建查询首先要打开查

13、询的设计视图窗口，然后根据需要进行查询的定义。设计视图窗口，然后根据需要进行查询的定义。操作步骤：操作步骤：打开数据库打开数据库在在【创建创建】选项卡的选项卡的【查询查询】组中单击组中单击【查询查询设计设计】按钮，打开查询设计器窗口按钮，打开查询设计器窗口在查询设计器窗口中定义查询在查询设计器窗口中定义查询保存查询保存查询 查询的设计视图分为上下两部分：查询的设计视图分为上下两部分： 上半部分上半部分 用于显示查询的数据源（包括它们之间的关系）用于显示查询的数据源（包括它们之间的关系） 下半部分下半部分 是定义查询的表格，表格的是定义查询的表格，表格的列对应查询中的一列对应查询中的一个字段个字

14、段；行对应字段的属性或要求行对应字段的属性或要求。字段字段查询结果中所显示的字段查询结果中所显示的字段表表查询数据源，即字段的来源查询数据源，即字段的来源排序排序查询结果中相应字段的排序方式查询结果中相应字段的排序方式显示显示设置在数据表视图中是否显示该字设置在数据表视图中是否显示该字段段条件条件查询条件（同行之间为查询条件（同行之间为“与与”关系）关系）或或查询条件（不同行之间为查询条件（不同行之间为“或或”关系）关系）2、使用查询向导创建查询、使用查询向导创建查询 Access 2010提供了提供了4种类型的查询向导：种类型的查询向导： 简单查询向导简单查询向导 交叉表查询向导交叉表查询向

15、导 查找重复项查询向导查找重复项查询向导 查找不匹配项查询向导查找不匹配项查询向导使用查询向导创建查询几点说明：使用查询向导创建查询几点说明： 不能对记录进行筛选和排序不能对记录进行筛选和排序 不能改变查询中字段的顺序不能改变查询中字段的顺序 允许对数值字段进行汇总统计允许对数值字段进行汇总统计 允许按指定日期范围分组汇总允许按指定日期范围分组汇总 汇总查询必须包含且只能包含汇总查询必须包含且只能包含汇总统计的数值汇总统计的数值型字段型字段和用于汇总依据的和用于汇总依据的分组字段分组字段。其它字段的。其它字段的添加将无法得到期望的汇总效果添加将无法得到期望的汇总效果五、运行查询五、运行查询 查

16、询创建完成后，将保存于数据库中。运行查查询创建完成后，将保存于数据库中。运行查询后，才能看到查询的结果。通过以下方法之一，询后，才能看到查询的结果。通过以下方法之一，可以运行查询：可以运行查询： 在查询的设计视图下，单击在查询的设计视图下，单击【查询工具查询工具/设计设计】选项卡选项卡【结果结果】组中的组中的【运行运行】按钮按钮 在查询的设计视图下，单击在查询的设计视图下，单击【查询工具查询工具/设计设计】选项卡选项卡【结果结果】组中的组中的【视图视图】按钮按钮 右击查询的设计视图标题栏，并在快捷菜单右击查询的设计视图标题栏，并在快捷菜单中选择中选择【数据表视图数据表视图】命令命令 双击导航窗

17、口中要运行的查询双击导航窗口中要运行的查询 右击导航窗口中要运行的查询，并在快捷菜右击导航窗口中要运行的查询，并在快捷菜单中选择单中选择【打开打开】命令命令第 二 节选 择 查 询 选择选择查询是查询是最常用最常用的查询类型，它是根据用户的查询类型，它是根据用户所指定的查询条件，从一个或多个数据源表中获所指定的查询条件，从一个或多个数据源表中获取数据并显示结果。取数据并显示结果。 选择查询可以对记录进行分组，并进行各种统选择查询可以对记录进行分组，并进行各种统计，如：求和、计数、求平均值等。计，如：求和、计数、求平均值等。 选择查询产生的结果是一个动态的记录集，不选择查询产生的结果是一个动态的

18、记录集，不会改变数据源中的数据。会改变数据源中的数据。一、基于单数据源表的查询一、基于单数据源表的查询 设计单数据源表查询时，主要的操作是： 确定查询的数据源（表或已建立的查询）确定查询的数据源（表或已建立的查询） 确定查询中要显示的字段或表达式确定查询中要显示的字段或表达式 如果查询的数据源是两个或两个以上的表（或如果查询的数据源是两个或两个以上的表（或查询），则在设计查询时需要创建数据源之间的查询），则在设计查询时需要创建数据源之间的连接关系。连接关系。 如果相关联的表已经按照如果相关联的表已经按照公共字段公共字段创建了索创建了索引，则在查询视图中可以看到作为数据源的表引，则在查询视图中可

19、以看到作为数据源的表（或查询）之间已经通过相关字段建立了连接。（或查询）之间已经通过相关字段建立了连接。 如果相关联的表未按照公共字段创建索引，如果相关联的表未按照公共字段创建索引，则需要在相关表之间建立连接。则需要在相关表之间建立连接。二二 、基于多数据源表的查询、基于多数据源表的查询 Access有有3种连接类型：种连接类型： 内连接内连接 左连接左连接 右连接右连接内连接（内连接（INNER JOIN） 只包含来自两张表的联接字段相等的记录只包含来自两张表的联接字段相等的记录。Xs.xh=Cj.xh左连接（左连接（LEFT JOIN） 包括包括左表中左表中所有记录所有记录和右表中联接字段

20、相等的和右表中联接字段相等的记录。记录。Xs.xh=Cj.xh右连接右连接 包括包括右表中右表中所有记录所有记录和左表中联接字段相等的和左表中联接字段相等的记录。记录。Xs.xh=Cj.xh注意：注意： 如果进行多表查询时，所使用的表或查询之间如果进行多表查询时，所使用的表或查询之间没有建立连接关系，则查询将以没有建立连接关系，则查询将以笛卡尔积笛卡尔积的形式，的形式，即即左表中的每一条记录均与右表所有记录合并左表中的每一条记录均与右表所有记录合并，形成一条记录形成一条记录。也就是说，会在查询结果中产生。也就是说，会在查询结果中产生大量的数据，而这样的结果是没有任何意义的。大量的数据，而这样的

21、结果是没有任何意义的。 双击表的关系连线可以编辑表之间的联接属性，双击表的关系连线可以编辑表之间的联接属性，包括：联接的字段和连接的类型。包括：联接的字段和连接的类型。内连接内连接左连接左连接右连接右连接三、条件查询三、条件查询 在实际的查询中，经常需要查询满足某个条件在实际的查询中，经常需要查询满足某个条件的记录。的记录。 带条件的查询需要通过设置查询条件来实现。带条件的查询需要通过设置查询条件来实现。查询条件是查询条件是运算符运算符、常量常量、字段值字段值、函数函数以及字以及字段名和属性等任意组合的关系表达式，其段名和属性等任意组合的关系表达式，其运算结运算结果是一个逻辑值果是一个逻辑值。

22、（1）运算符 算术运算符【例】 125 + 100 / 5 2 6 * 2 / ( 2 + 1 ) 单价 * 0.8算术运算符算术运算符说说 明明算术运算符算术运算符说说 明明+加/除-减乘方*乘 关系运算符关系运算符关系运算符说说 明明关系运算符关系运算符说说 明明=等于不等于小于大于 =大于等于关系运算符主要用于数据之间的比较，其运算的结果是一个逻辑值：True或False。各关系运算符的运算级别相同。【例】 3 8xb = 男 5 + 2 = 张 工作日期 12/6 AND A + B 2 AND AB AC NOT .T. AND .T. .F. AND .T. .F.数值运算符逻辑运

23、算符【例例】用文字表示使以下条件表达式为用文字表示使以下条件表达式为“真真”的条件：的条件：学生表中：学生表中： 性别性别 = -1 And 系名系名 = 信息工程学院信息工程学院 信息工程学院中所有的男生信息工程学院中所有的男生 性别性别 = -1 Or 系名系名 = 信息工程学院信息工程学院 所有的男生以及信息工程学院中所有的学生所有的男生以及信息工程学院中所有的学生教师表中：教师表中： 性别性别 = 0 And 职称职称 = 教授教授 Or 民族民族 汉族汉族 女性教授以及非汉族教师女性教授以及非汉族教师 性别性别 = 0 And ( 职称职称 = 教授教授 Or 民族民族 汉汉族族 )

24、 女性教授以及女性非汉族教师女性教授以及女性非汉族教师（2）条件查询举例条件查询举例 在查询的设计视图中，查询条件应使用查询定在查询的设计视图中，查询条件应使用查询定义窗口中的条件选项来设置，即在相应的字段的义窗口中的条件选项来设置，即在相应的字段的【条件条件】文本框中输入条件文本框中输入条件。条件表达式中通常省略字段名。条件表达式中通常省略字段名。有多个条件，且涉及不同的字段时，则分别设有多个条件，且涉及不同的字段时，则分别设置相应字段的条件。置相应字段的条件。同行的条件之间是同行的条件之间是“与与”关系；不同行的条件之关系；不同行的条件之间是间是“或或”关系关系【例例】查找每名学生查找每名

25、学生“体育体育”课程的成绩，要求课程的成绩，要求显示显示“学号学号”、“姓名姓名”、“课程名称课程名称” 和和“成绩成绩”4个字段。个字段。SELECT 学生表学生表.XH, 学生表学生表.XM, 课程表课程表.KCMC, 成绩表成绩表.CJFROM (学生表学生表 INNER JOIN 成绩表成绩表 ON 学生学生表表.XH = 成绩表成绩表.XH) INNER JOIN 课程表课程表 ON 成成绩表绩表.KCDM = 课程表课程表.KCDMWHERE (课程表课程表.KCMC)=体育体育);说明：说明： WHERE子句子句用于指定查询的条件用于指定查询的条件【例例】查找成绩在查找成绩在70

26、至至95分之间（含分之间（含70和和95分）分）的女生，以及成绩大于等于的女生，以及成绩大于等于80分的男生。要求显分的男生。要求显示示“姓名姓名”、“性别性别”、“课程名称课程名称”、“成绩成绩”4个字个字段并按性别升序、成绩降序排序。段并按性别升序、成绩降序排序。SELECT 学生表学生表.XM, 学生表学生表.XB, 课程课程表表.KCMC, 成绩表成绩表.CJFROM (学生表学生表 INNER JOIN 成绩表成绩表 ON 学生学生表表.XH = 成绩表成绩表.XH) INNER JOIN 课程表课程表 ON 成绩表成绩表.KCDM = 课程表课程表.KCDMWHERE (学生表学生

27、表.XB)=女女) AND (成绩成绩表表.CJ)=70 And (成绩表成绩表.CJ)=80)ORDER BY 学生表学生表.XB, 成绩表成绩表.CJ DESC; ORDER BY子句子句 指定查询结果的排序依据指定查询结果的排序依据 DESC降序排序降序排序 ASC升序排序（默认排序）升序排序（默认排序）【例例】查找没有选课学生的课程名称。要求输出：查找没有选课学生的课程名称。要求输出：课程代码和课程名称。课程代码和课程名称。【分析分析】没有选课学生的课程，即意味着，该门课程在没有选课学生的课程，即意味着，该门课程在成绩表中没有记录。成绩表中没有记录。因此，必须因此，必须kc表和表和cj

28、表连接时采用左连接类型。表连接时采用左连接类型。SELECT 课程表课程表.KCDM, 课程表课程表.KCMC, 成绩成绩表表.CJFROM 课程表课程表 LEFT JOIN 成绩表成绩表 ON 课程课程表表.KCDM = 成绩表成绩表.KCDMWHERE (成绩表成绩表.CJ) Is Null);【例例】查找成绩表中成绩最高的查找成绩表中成绩最高的10名学生。要求名学生。要求输出：姓名、学号、课程名称和成绩。输出：姓名、学号、课程名称和成绩。【分析分析】 即：将成绩按降序排序，且只显示前即：将成绩按降序排序，且只显示前10条记录。条记录。SELECT TOP 10 学生表学生表.XH, 学生

29、表学生表.XM, 课程课程表表.KCMC, 成绩表成绩表.CJFROM (学生表学生表 INNER JOIN 成绩表成绩表 ON 学生学生表表.XH = 成绩表成绩表.XH) INNER JOIN 课程表课程表 ON 成绩表成绩表.KCDM = 课程表课程表.KCDMORDER BY 成绩表成绩表.CJ DESC;【例例】查找有补考学生的课程代码和课程名称查找有补考学生的课程代码和课程名称（相同课程只显示一次）。（相同课程只显示一次）。【分析分析】即：该门课程的成绩有小于即：该门课程的成绩有小于60分的记录。分的记录。SELECT DISTINCTROW 课程表课程表.KCDM, 课程课程表表

30、.KCMCFROM 课程表课程表 INNER JOIN 成绩表成绩表 ON 课程课程表表.KCDM = 成绩表成绩表.KCDMWHERE (成绩表成绩表.CJ)60);四、查询中的计算和统计四、查询中的计算和统计 在设计查询时，不仅可以进行条件的设置，还在设计查询时，不仅可以进行条件的设置，还可以进行计算和分类汇总（计数、求和、求平均可以进行计算和分类汇总（计数、求和、求平均值等）。值等）。（1）表达式表达式 表达式表达式是是用用运算符、常量、字段值、函数以及运算符、常量、字段值、函数以及字段名和属性等字段名和属性等连接起来的一个连接起来的一个式式子。系统提供子。系统提供了了算术运算算术运算、

31、关系运算关系运算、字符运算字符运算和和逻辑运算逻辑运算等等4种基本运算表达式。种基本运算表达式。（2）系统函数系统函数 函数函数是是一个预先定义（系统预先定义或用户自一个预先定义（系统预先定义或用户自定义）的程序模块。定义）的程序模块。 标准函数（系统预定义）标准函数（系统预定义） 自定义函数（用户自定义）自定义函数（用户自定义） 系统提供的标准函数包括：数值函数、字符函系统提供的标准函数包括：数值函数、字符函数、日期时间函数和数、日期时间函数和聚合函数聚合函数等。等。P126 求和函数格式：格式：Sum( )功能：功能：返回字段中值的总和返回值数据类型：返回值数据类型：数值型说明：说明： 字

32、符串表达式可以是一个字段名，也可以是一个含字段名的表达式，但所含的字段必须是数值型字段。【例】 Sum( Cj.cj ) Sum( 单价 * 数量 ) Sum( ( 单价 - 进价 ) * 数量 )聚合函数聚合函数 求平均函数格式：格式：Avg( )功能：功能：返回字段中值的平均值返回值数据类型：返回值数据类型：数值型说明：说明：字符串表达式可以是一个字段名，也可以是一个含字段名的表达式，但所含的字段必须是数值型字段。Avg函数不计算任何Null值字段【例】 Avg( Cj.cj ) Avg( 单价 ) Avg( 应发工资 - 扣款 ) 统计记录个数函数格式：格式：Count( )功能：功能：

33、统计记录个数返回值数据类型：返回值数据类型：数值型说明：说明：字符串表达式可以是一个字段名，也可以是一个含字段名的表达式（该字段不一定是数值型）。使用格式Count( )时，系统将统计指定字段值不为Null值的记录的个数；而使用格式Count( * ) 时，系统将统计所有记录的个数，包括含Null值字段的所有记录个数。【例】 Sum( Cj.cj ) / Count( Cj.cj ) 单价 * Count( 单价 ) 最大、最小值函数格式：格式：Max( ) Min ( )功能：功能：返回一组指定字段中的最大、最小值说明：说明： 字符表达式可以是一个字段名，也可以是一个含字段名的表达式，（该字

34、段并不一定是数值型）。【例】 Max( Cj.cj ) Min( 出生日期 )（3）查询中的计算功能举例预定义计算 利用设计视图网格的【总计】行进行各种统计自定义计算 创建计算字段进行任意类型的计算当需要统计的数据在表中没有相应的字段，或者用于计算的数据值来源于多个字段时，应在查询中使用计算字段。计算字段是指根据一个或多个字段使用表达式建立的新字段（查询中的显示字段）。创建计算字段是在查询设计视图的【字段】行中直接输入计算表达式。【例】统计各门课程的成绩。要求输出“课程名称”、“总分”、“平均分”、“最高分”和“最低分”。【分析】 本题输出的字段中含有4个统计字段，因此将利用设计视图网格的中【

35、总计】行进行统计。 【总计】行的打开方式：单击【查询工具/设计】选项卡【显示/隐藏】组中的【汇总】按钮。预定义计算预定义计算【总计】下拉列表框中含以下各选项： 合计 平均值 最小值 最大值 计数 Var（方差） StDev（标准差）(方差的平方根就是标准差) Group By定义要执行计算的组组 First求在表或查询中第一条记录的字段值 Last求在表或查询中最后一条记录的字段值 Expression在字段中自定义计算公式 Where指定用于分组的字段的条件【例】统计各门课程的平均分。要求输出“课程名称”、“平均分”、“最高分”和“最低分”。 可以为显示的各个字段分别设置其属性。SELECT

36、 课程表.KCMC, Sum(成绩表.CJ) AS 总分, Avg(成绩表.CJ) AS 平均分, Min(成绩表.CJ) AS 最低分, Max(成绩表.CJ) AS 最高分FROM 课程表 INNER JOIN 成绩表 ON 课程表.KCDM = 成绩表.KCDMGROUP BY 课程表.KCMC; 当查询的输出字段含有聚合函数时，通常情况下应设置分组字段。所谓分组字段即为：进行数据统计时的分组依据。 当查询中含有统计字段，而某一字段既不是统计字段，也不是分组字段，其值是无意义的。 【总计】行设为【Where】的字段不能出现在查询的结果中。【例】查询数学系平均成绩在75分以上，且各门课程的

37、成绩不得低于60分的学生的学号、姓名、平均分和最低分，并按照平均分从高到低排列（平均分精确到1位小数）。SELECT 学生表.XH, 学生表.XM, Avg(成绩表.CJ) AS 平均分, Min(成绩表.CJ) AS 最低分FROM 院系代码表 INNER JOIN (学生表 INNER JOIN 成绩表 ON 学生表.XH = 成绩表.XH) ON 院系代码表.YXZYDM = 学生表.YXZYDMWHERE (院系代码表.YXMC)=数学系)GROUP BY 学生表.XH, 学生表.XMHAVING (Avg(成绩表.CJ)75) AND (Min(成绩表.CJ)60)ORDER BY

38、Avg(成绩表.CJ) DESC;WHERE与HAVING的区别：WHERE子句是从数据源中提取数据时，对记录的筛选。HAVING子句必须位于GROUP BY子句之后，是对统计结果的筛选。HAVING子句中通常应该含有聚合函数，否则应该使用WHERE子句。自定义计算自定义计算 在设计视图中创建新的计算字段，并将表达式输入到字段行中，表达式可以由多个计算组成。【例】统计学生表中不同姓氏的学生的人数，输出字段为：姓氏和人数，并且按人数降序排序。（假设姓氏为姓名的第1个汉字）。【分析】 本题中分组依据为姓氏的第1个字，并且作为输出的字段。为此需要创建一个新的计算字段，该字段使用了字符串截取函数Lef

39、t（ ）。 Left ( )函数格式：格式：Left（String , Length）功能：功能：从字符表达式String左侧的第一个字符开始，截取由Length的值所指定的若干个字符返回值数据类型：返回值数据类型：字符型说明：说明：String为被截取的字符串，当其值为Null时，则返回Null值。Length为返回的字符数。其值必须为正，若Length的值为小数，则四舍五入至整数值后即为返回的字符个数；若Length的值为负数，则出错。 Length的值为0时，则返回空字符串；Length的值大于String的长度时，则返回全部字符。字符串截取函数字符串截取函数Right ( ) 函数格式

40、：格式：Right（String，Length）功能：功能：从字符表达式String右侧的第一个字符开始，截取由Length的值所指定的若干个字符返回值数据类型：返回值数据类型：字符型字符串截取函数字符串截取函数Mid ( ) 函数格式：格式：Mid（String , Start , Length ）功能：功能：从字符表达式String返回一个子字符串返回值数据类型：返回值数据类型：字符型说明：说明：Start为长整型，用于指定所取的字符串在String中的开始位置； Length为长整型，指定要返回的字符个数，缺省时指所取字符串直至String的最后一个字符；Start的值大于指定字符串的长

41、度，则返回一个空字符串；Start的值为0或负数，则均出错；Length的值大于所能取的字符数目，则取至最后一个字符。字符串截取函数字符串截取函数【例】 Left ( ABCD中国 , 8 - 5 )ABC Left ( ABCD中国 , 0 )（空字符串） Left ( ABCD中国 , 8 )ABCD中国 Right ( ABCD中国 , 4 )CD中国 Right ( ABCD中国 , 2.4 )中国字符串截取函数字符串截取函数【例】 Mid ( ABCD中国 , 3 , 8 )CD中国 Mid ( ABCD中国 , 12 , 2 )（空字符串） Mid ( ABCD中国 , 0 , 4

42、 )出错 Mid ( ABCD中国 , 3 , -1 )出错字符串截取函数字符串截取函数【例】统计学生表中不同姓氏的学生的人数，输出字段为：姓氏和人数，并且按人数降序排序。（假设姓氏为姓名的第1个汉字）。SELECT Left(xm,1) AS 姓氏, Count(学生表.XH) AS 人数FROM 学生表GROUP BY Left(xm,1)ORDER BY Count(学生表.XH) DESC;【例】统计学生表中“王”姓和“李”姓的学生人数，输出字段为：姓氏和人数，并按姓氏升序排序。SELECT Left(xm,1) AS 姓氏, Count(学生表.XH) AS 人数FROM 学生表GR

43、OUP BY Left(xm,1)H AV I N G ( ( ( L e f t ( x m , 1 ) ) = 王 ) ) O R (Left(xm,1)=张)ORDER BY Left(xm,1);【例】统计学生表中不同年龄的学生人数。输出时按年龄升序排序。【分析】 年龄计算方法：当前的年份 出生日期的年份 因此在创建的计算字段，需要使用相关的日期函数。 截取日期分量函数格式：格式：Year（Date）Month（Date）Day（Date）功能：功能：分别返回日期表达式年份、月份和日期的整数。返回值数据类型：返回值数据类型：整型说明：说明：Date为任何能够表示日期的表达式（日期或字符

44、表达式等）。参数中若略去年份，则返回系统日期的年份。参数中若含有Null值，则返回Null。 相类似的函数有：Hour( )函数、Minute( )函数和Second( )函数。日期函数日期函数【例】 Year ( #2011-10-01# )2011 Year ( Null )Null Year ( Oct , 15 )（系统日期的年份） Month ( 2012-09-20 )9 Day ( 3 - 2 )31 Access将1899年12月31日作为数值1。日期函数日期函数 获取系统日期 / 时间函数格式：格式：Date（ ）Time（ ）Now（ ）功能：功能：分别返回当前系统日期、当

45、前系统时间和当前系统日期和时间。返回值数据类型：返回值数据类型：日期型 返回的日期格式由操作系统设置的日期格式决定。日期函数日期函数【例】统计学生表中不同年龄的学生人数。输出时按年龄升序排序。SELECT Year(Date()-Year(csrq) AS 年龄, Count(学生表.XH) AS 人数FROM 学生表GROUP BY Year(Date()-Year(csrq)ORDER BY Year(Date()-Year(csrq);【例】统计各院系女生人数及所占比例。要求输出：院系名称、女生人数、女生比例；输出时按女生比例降序排序，比例相同时则按院系名称升序排序（女生比例保留1位小数

46、）。【分析】 由于需要统计女生所占的比例，因此不能用Where子句进行性别筛选。本题需要用到一个条件函数IIf（ ）。IIf ( ) 函数格式：格式：IIF（Expression，Truepart，Falsepart）功能：功能：根据Expression的值，返回Truepart和Falsepart两个值中的一个。说明：说明： 当逻辑表达式Expression的值为真时，返回Truepart的值，否则返回Falsepart的值。【例】根据成绩输出相应的分数等级：不及格、及格和优秀。IIf ( cj.cj = 90 , 优秀 , _IIf ( cj.cj 60 , 不及格 , 及格 ) )【例】

47、统计各院系女生人数及所占比例。要求输出：院系名称、女生人数、女生比例；输出时按女生比例降序排序，比例相同时则按院系名称升序排序（女生比例保留1位小数）。SELECT 院系代码表.YXMC, Sum(IIf(xb=女,1,0) AS 女生人数, Sum(IIf(xb=女,1,0)/Count(xh) AS 女生所占比例FROM 院系代码表 INNER JOIN 学生表 ON 院系代码表.YXZYDM = 学生表.YXZYDMGROUP BY 院系代码表.YXMCORDER BY Sum(IIf(xb=女,1,0)/Count(xh) DESC , 院系代码表.YXMC;第 三 节交 叉 表 查

48、询 交叉表查询是以行和列的字段作为标题和条件选取数据，并在行与列的交叉处对数据进行汇总和统计计算。 设计交叉表查询需要字义3种字段： 处于数据表最左端的行标题字段行标题字段 把某一字段或相关数据放入指定的一行中。 处于数据表最上边的列标题字段列标题字段 对每一列指定的字段进行统计，并将统计结果放在该列。 处于行和列交叉位置的值字段值字段 可以为该字段指定总计项。在交叉表查询中： 只能指定一个列字段和一个总计类型的字段。 可以通过设置一个或多个行标题，创建多级交叉表查询。一、使用向导创建交叉表查询一、使用向导创建交叉表查询【例】基于xsb表创建一个交叉表查询，要求统计各班的男女生人数。操作步骤：

49、操作步骤：单击【创建】选项卡【查询】组中的【查询向导】按钮，并在对话框中选择【交叉表查询向导】按向导提示依次确定数据源、行字段、列字段、汇总项及是否包括各行小计为查询命名并保存所设计的查询 交叉表查询向导的数据源只能来自于一张表或查询。若来自多个表，可先建立一个查询，而后以此查询作为数据源使用向导创建，或者直接在设计视图下创建。 行标题最多3个，列标题1个，值字段1个。TRANSFORM Count(xsb.XH) AS XH之计数SELECT xsb.BJBHFROM xsbGROUP BY xsb.BJBHPIVOT xsb.XB;二、在设计视图下创建交叉表查询二、在设计视图下创建交叉表查

50、询 使用设计视图可创建基于多表的交叉表查询。【例】以交叉表的形式查询每个学生的各门课程成绩及平均分。要求：输出时平均分保留一位小数。 在查询的设计视图下，单击【查询工具/设计】选项卡【查询类型】组中【交叉表】按钮，可以在设计网格中增加【交叉表】行。操作步骤：操作步骤：打开查询设计视图，并添加数据源及显示字段单击【查询工具/设计】选项卡【查询类型】组中【交叉表】按钮，使查询类型为交叉表查询在【交叉表】行上设置：行字段、列字段在【交叉表】行上设置：值字段（将该字段的【总计】行改为：第一条记录）添加汇总字段并设置汇总函数（将该字段的【交叉表】行设置为：行标题）行标题行标题列标题列标题值值TRANSF