Access 数据库应用技术

1. NDAT “数据库应用技术(NDAT)”资格认证培训项目 Access 数据库应用技术 信息产业部电子行业职业技能鉴定指导中心 网络与信息培训办公室

2. 第3讲 查询 3.1 排序、索引与筛选 3.2 关联查询 3.3 其它查询方法 3.4 SQL查询 3.5 操作查询 习题

3. 查询是数据库最重要和最常见的应用。 Access在查询中支持使用SQL语言, 但也提供查询设计视图,以便通过交互方式来设计查询。 本章讨论： l单表查询 包括传统的排序、索引方法及筛选方法等； l关联查询 实现多表查询的主要手段； l其它查询方法 例如创建查阅字段与子数据表,参数查询与交叉表查询,在查询中计算,以及创建多表联接等。 章末简单介绍SQL语句及其查询应用。

4. 3.1 排序、索引与筛选 排序和索引通过按字段有序地排列记录,能使查询更有效地进行。 筛选能够按条件选出数据 。 3.1.1 排序 数据表视图工具栏中 【升序排序】按钮 【降序排序】 按钮 排序方法 在数据表中单击某个要排序的字段,单击排序按钮。 清除排序 【记录】菜单中的【取消筛选/排序】命令。

5. 3.1.2 索引 与“排序”方法相比,索引方法速度快。 1. 索引的种类 (1) 按功能来分类 ① 唯一索引 ② 普通索引 ③ 主索引 (2) 按字段数来分类 单字段索引 多字段索引。 多字段索引。功能是：先按第一个索引字段排序,对于字段值相同的记录再按第二个索引字段来排序,依此类推。

6. 2. 创建索引 创建索引就是为字段设置索引属性。在表的设计视图和索引窗口(参阅图3.2)都可创建索引属性, 表3.1列出了各种索引的创建方法。

7. 3．删除索引 (1) 在索引窗口选定一行或多行,按 Delete 键。 (2) 在设计视图字段的【索引】属性框中选定“无”。 取消主索引简便方法：在设计视图选定钥匙符号行,在工具栏单击【主键】按钮。 删除索引并不删除字段本身。 4．索引窗口 打开索引窗口：显示表的设计视图,单击【表设计】工具栏中【索引】按钮,或选定【视图】菜单的【索引】命令。

8. 图3.2 创建单字段降序排序的普通索引

9. 5．多字段索引 多字段索引通常在索引窗口创建。 创建要点：在多行中,首行的【索引名称】列键入任意的名称,其他各行的【索引名称】列留空。 〖例3.3〗 在图3.2所示的索引窗口中再创建一个包含两个字段的索引,并要求首先按设备的名称降序排序,当名称相同时按价格升序排序。 图3.3 两个索引与一个两字段索引

10. (a) 选定筛选条件 (b) 显示筛选结果 图3.5 按选定内容筛选 3.1.3 筛选 筛选是让数据表显示符合条件的记录,隐藏不符合条件的记录。 1．按选定内容筛选 在数据表中选定部分数据作为筛选内容,然后使用命令显示筛选结果。 显示筛选结果 数据表视图工具栏中【按选定内容筛选】按钮 【应用筛选】按钮 【取消筛选】按钮 〖例3.4〗 让设备数据表只显示车床和磨床记录。

11. (a) 【查找】选项卡 (b) 【或】选项卡 图3.6 “按窗体筛选”条件示例 2．按窗体筛选 打开表数据表视图,单击工具栏中【按窗体筛选】按钮 ，就会显示“按窗体筛选”窗口。这种窗口有一行空字段,并具有一个“查找”选项卡和多个“或”选项卡,供输入筛选条件。 〖例3.5〗 使用“按窗体筛选”的方法,筛选符合以下条件的记录：轿车；或价格小于10000元且没有声像内容、但具有备注内容的非主要设备。

12. 图3.7 在【筛选目标】框输入筛选表达式 3．按“筛选目标”筛选 在“筛选目标”框中键入所选字段的筛选表达式。“筛选目标”框显示在数据表任一字段列的快捷菜单中。 显示筛选结果并关闭快捷菜单 按回车键。 取消筛选 在快捷菜单中选定【取消筛选/排序】命令。

13. 图3.8 “高级筛选/排序”窗口 4．高级筛选和排序 打开数据表视图,选定【记录】菜单|【筛选】|【高级筛选/排序】命令,就会显示“高级筛选/排序”窗口,。 上窗格：当前表的字段列表； 下窗格：“设计网格”，用于设置筛选条件和排序字段。 (1) 高级筛选

14. 图3.9 两字段排序 执行筛选：单击工具栏中的【应用筛选】按钮。 取消筛选：单击工具栏中的【取消筛选】按钮。 (2) 多字段排序 在“高级筛选/排序”窗口可以设置单字段或多字段排序。 【排序】行的单元格中包括“升序”、“降序”和“(不排序)”3个选项。 显示排序结果：单击工具栏中的【应用筛选】按钮。

15. 3.2 关联查询 多表查询通过表间联接来实现(参阅第3.3.6节)。关联查询先在多个数据表之间确定关系,然后利用查询设计视图创建“查询”。 3.2.1 表间关系 1．一个样例：设备管理数据库 本书以“设备管理数据库”为例设计了4个表。其中除已建立的“设备”表外,还包括“大修”表、“增值”表和“部门代码”表等3个表。 “大修”表 结构: 大修([编号] 文本(5),[年月] 文本(4),[费用] 数字(整型)) 记录: 记录号 编号 年月 费用 1 016-1 9711 2764 2 016-1 9912 3520 3 037-2 9806 6204 4 038-1 9511 2850

16. “增值”表 结构: 增值([编号] 文本(5), [金额] 数字(整型)) 记录: 记录号 编号 金额 1 016-1 2510 2 016-1 1000 3 038-1 1200 “部门代码”表 结构: 部门代码([代码] 文本(2),[名称] 文本(3)) 记录: 记录号 代码 名称 1 11 办公室 2 12 设备科 3 21 一车间 4 22 二车间 5 23 三车间

17. 2．表的关联 表的关联是指在表间建立关系。在关联的两个表中, 对于父表记录指针每一次新的定位,子表的记录指针都会随之移动,从中找出关联字段值相等的记录。 在建立关系的两个表中,关联字段的字段名允许不同,但类型必须相同。 3. 关系的类型 ① 一对多关系 ② 一对一关系 ③ 多对一关系 ④ 多对多关系 图3.10 一对多关系与多对一关系

18. 4．主键和外键 (1) 主键 主键值能唯一标识表中的每个记录。所以主键必须有唯一索引,且不允许存在Null值。 主键一般为单字段。当单字段不能保证唯一值时,可将两个或更多的字段指定为主键(多字段主键)。 主键的功效: ① 为主键字段输入数据时能保持唯一性,且不会产生Null值。 ② 因为主键是缺省的排序依据,并能快速查找各表中的信息。 ③ 主键也能创建参照完整性。 ④ 将主键作为关联字段,默认创建一对多或一对一关系。 (2) 外键 在关联表中,若一个表用主键作为关联字段,则另一表的关联字段称为外键。

19. 3.2.2 在关系窗口中创建关系 关系窗口（见图3.11 ）方便用户创建、修改、查看表间关系。 〖例3.6〗 为设备表和大修表创建一对多关系。 (1) 在设计视图中打开设备表。 (2) 为设备表编号字段创建主键。 (3) 在关系窗口添加设备表和大修表：打开【关系】窗口。打开如图3.12所示的【显示表】对话框。添加设备表和大修表。 (4) 创建关系：用鼠标从“设备”字段列表中的编号字段拖到“大修”字段列表中的编号字段,释放后显示【编辑关系】对话框(见图3.13)。单击【创建】按钮，两个关联字段间即显示关系线(参阅图3.11)。

20. 图3.11 【关系】窗口及其相关对话框 图3.12 【显示表】对话框 图3.13 【编辑关系】对话框

21. (1) 编辑关系 可打开【编辑关系】对话框来更改关系，显示该对话框的方法如下： ① 双击关系线。 ② 右击关系线,然后在快捷菜单中选定【编辑关系】命令。 ③ 单击关系线,使它变粗成为选定状态,然后选定【关系】菜单中的【编辑关系】命令。 (2) 删除关系 在关系线的快捷菜单中选定【删除】命令。 永久性关联 在关系窗口创建的关联是“永久性关联”。这种关系一旦在 【关系】窗口建立,将自动在查询、窗体、或报表设计中起作用,直到关系被删除或者修改。 临时关联 如果未在“关系窗口”创建关联,可在查询设计视图的“联接窗格”中直接创建联接(见第3.2.4节)。不过这样的联接只在本次查询起作用,所以称为“临时关联”。

22. 图3.16 确定是否相关 图3.17 【关系】对话框 3.2.3 用表向导创建关联 表向导不仅能创建表,而且能创建一对多关系，但要求在当前表设置主键,以便自动将“一方”的主键添加到“多方”作为外键。

23. 图3.22 【查询】对象选项卡 3.2.4 在设计视图中创建查询 〖例3.9〗 在例3.6的基础上创建一个名称为“大修费用”的选择查询,要求对所有的大修设备列出其编号、名称及每次大修的费用。 假定设备和大修两个表已按例3.6实现了关联,操作步骤如下。 打开【查询】对象选项卡，如图3.22所示。

24. 图3.21 查询设计视图 3.2.4 在设计视图中创建查询 1．查询设计视图 2. Access的查询类型 3．已建查询的编辑与运行

25. 图3.22 【查询】对象选项卡 4. 创建选择查询示例 〖例3.9〗 在例3.6的基础上创建一个名称为“大修费用”的选择查询,要求对所有的大修设备列出其编号、名称及每次大修的费用。 打开【查询】对象选项卡：如图3.22所示。 (2) 在设计视图中添加表：【在设计视图中创建查询】,参阅图3.21添加“设备”和“大修”表，上窗格即显示两个字段列表以及两表之间的联接线 。 (3) 选择字段：“设备.编号”、 “设备.名称”和“大修.费用” ，如图3.23所示。 图3.23 已设置字段的查询设计视图

26. 图3.24 “大修费用”数据表 (4) 保存查询：查询名称“大修费用”。 (5) 运行查询：单击查询设计工具栏中的【运行】按钮,即显示如图3.24所示的查询结果——“大修费用”数据表。

27. 3.2.5 参照完整性 字段级和记录级的有效性规则都是表内规则。参照完整性则属于表间规则, 用于在编辑记录时维持已定义的表间关系。 1. 实施参照完整性的条件 (1) 两表必须关联,而且父表的关联字段是主键,或具有唯一索引。 (2) 子表中任一关联字段值在父表关联字段值中必须存在。 2. 参照完整性的规则与其实施 参照完整性的规则见表3.2。 在图3.13的【编辑关系】对话框中含有【实施参照完整性】、【级联更新相关字段】和【级联删除相关记录】3个复选框,可从中选择要不要实施参照完整性,以及实施何种参照完整性。

28. 图3.33 查阅列的列表 3.3 其它查询方法 3.3.1 查阅字段 若在表中创建了一个查阅字段（查阅列）,就可在该字段查阅另一个表(或查询)中相关字段的值。

29. 创建查阅列的方法见表3.3。无论使用何种方法,都将自动启动查阅向导。

30. 3.3.2 子数据表 若两表以一对多或一对一关系关联,就可为当前数据表(主数据表) 创建一个子数据表。 子数据表能显示、编辑与主数据表关联的数据。 子数据表最多嵌套八级 。 1．创建子数据表 〖例3.12〗 在例3.6已为设备表和大修表创建了一对多关系的基础上,将大修表设置为设备表的子数据表。 (1) 在设备管理数据库窗口的【表】选项卡中双击“设备”表,使显示设备数据表(参阅图2.8)。 (2) 指定子数据表：打开【插入子数据表】对话框(参阅图3.34)。选定“大修”选项,并保持【链接子字段】框和【链接主字段】框中的默认字段“编号”。单击【确定】按钮,数据表左侧出现一列 展开指示器,大修表已成为其子数据表。

31. 图3.34 【插入子数据表】 对话框

32. 3.3.3 参数查询 【参数查询】能在查询运行中显示一个对话框，以供即时键入查询参数值 。 【参数查询】基本操作步骤： (1) 打开查询设计视图(见图3.35)。 (2) 在设计网格的“条件”行单元格设置参数表达式。 (3) 运行查询：单击【查询设计】工具栏中的【运行】按钮,使弹出如图3.37(a)所示【输入参数值】对话框。

33. 图3.35 设置查询参数 图3.36 【查询】菜单 (a) (b) 图3.37 键入查询范围 图3.38 按条件查询的结果

34. 3.3.4 在查询中计算 本节与下节主要介绍Access提供的查询计算功能,包括计算函数、创建计算字段,以及专用于查询计算的交叉表查询等。 1．计算函数 添加或消除【总计】行：打开查询设计视图，使用【视图】菜单的【总计】命令。 在【总计】行中,每个单元格的组合框均含有12个选项,可供用户定义总计功能：聚合函数7个，Group By,First和Last函数,Where,Expression

35. 图3.39 役龄计算字段 图3.40 设备役龄数据表 2．计算字段 在查询设计视图中可定义字段表达式,该表达式可包含一个或多个字段中的数据来进行数值、日期和文本计算。 创建计算字段有如下两种方法： 在设计网格的空【字段】单元格中键入字段表达式。 (2) 如果表达式要包含一个或多个聚合函数,应该使用【总计】选项Expression来创建计算字段。

36. 图3.41 大修费用明细【交叉表查询】数据表 3.3.5 交叉表查询 交叉表查询不仅能进行【总计】计算,还能重构数据和分析数据。这种查询以表或查询为数据源,分别按行和列对数据分组,并且既可按行【总计】,又可按行与列【总计】,结果产生一个数据表。 〖例3.15〗 创建名称为“大修费用明细”的交叉表查询。要求如图3.41所示,能显示设备的编号、名称和费用小计,并按大修年月显示每次大修的费用。

37. 图3.42 大修费用明细【交叉表查询】设计视图 打开查询设计视图(参阅图3.23)。选定【查询】菜单中的【交叉表查询】命令,设计网格中即显示【总计】行和【交叉表】行。按图3.42所示设置。 关于交叉表查询的说明： (1) 创建交叉表查询必须指定行标题、列标题和值。行标题允许多个,列标题和值都只许一个。 (2) 行标题和列标题都用于分组,故它们的【总计】行单元格均应保持默认的Group By。

38. 3.3.6 创建多表联接 关联查询通过表间联接来实现,等效于联接查询中的“内部联接”。 1. 联接的类型 ⑴ 内部联接：默认方式。 ⑵ 左外部联接 ⑶ 右外部联接 2. 在查询设计视图中定义联接 ① 创建联接 在查询设计视图中从一个表中要联接的字段拖到另一表中要联接的字段,鼠标释放后即会在两字段间显示联接线。 ② 指定联接类型 双击联接线,使显示【联接属性】对话框(参阅图3.43), 对话框中的三个选项按钮,用于指定内部联接、左外部联接和右外部联接。

39. 图3.43 【联接属性】对话框 注意： 如果两表创建了关系,也可通过【关系】窗口来更改联接类型。 在关系窗口更改联接类型,不会改变原来由关系产生的默认联接类型，除非在查询设计视图中删除相关字段列表后,再将它重新添加进去。 在查询设计视图中设置的联接将自动覆盖由关系产生的默认联接。 若两个字段列表之间没有联接线(未设置联接),查询结果显示两表间记录的全部组合(M×N个记录)。 ③ 删除联接 与删除关系的方法相同,即,在联接线的快捷菜单中选定【删除】命令。

40. 表3.5 各种类型的联接线(一对多) 图3.44“左外部联接”数据表 〖例3.16〗 修改例3.9创建的“大修费用”查询,使在查询结果中不仅能显示每次大修的费用,而且对未发生大修的设备也能显示其编号与名称。 ⑴ 在设计视图中打开“大修费用”查询 (见图3.23)，设备和大修两个字段列表之间已经具有联接线。 ⑵ 设置“左外部联接”：双击联接线,使显示如图3.43所示的【联接属性】对话框。单击该对话框中的第二个选项按钮。单击【确定】按钮,原联接线已变为带右箭头的联接线(见表3.5)。 ⑶ 运行查询即显示如图3.44所示的查询结果。

41. 3.3.7 用向导创建查询 查询不仅可在设计视图中创建,也可用向导创建。由向导产生的初始查询设计视图可供修改。 启动向导的方法：在数据库窗口打开【查询】对象选项卡,并双击【使用向导创建查询】快捷选项,使显示【简单查询向导】对话框 。 〖例3.18〗 试用查询向导创建一个汇总设备大修费用的查询,要求： (1) 设备编号头3位小于038； (2) 显示设备的名称,以及按设备编号汇总的大修费用小计； (3) 查询结果按大修费用小计升序排列。 （步骤请阅教材）

42. 图3.56 【SQL视图】窗口中的SQL语句 3.4 SQL查询 在查询设计视图中创建查询时,Access 就自动生成等效的SQL语句。也可直接向【SQL视图】窗口键入SQL命令来创建查询。 3.4.1 SQL视图 SQL视图是用于显示或编辑SQL 查询的窗口,主要在以下两种情况下使用。 图3.55 【视图】菜单 (1) 已经创建了查询,需要查看SQL语句,或修改SQL语句来更改查询设计。 打开【SQL视图】窗口 ：打开查询设计视图或数据表视图,选定【视图】菜单 中的【SQL视图】命令。

43. (2) 在SQL视图中键入SQL语句来创建查询。 步骤如下： ① 在数据库窗口【查询】选项卡中双击【在设计视图中创建查询】快捷选项,使显示【显示表】对话框； ② 单击【关闭】按钮将该对话框关闭； ③ 选定【视图】菜单中的【SQL视图】命令,使显示SQL视图窗口,并在其中键入SQL语句。 显示查询结果：单击数据库窗口工具栏中的【运行】按钮。

44. 3.4.2 SQL-SELECT语句 SQL-SELECT语句的一般格式： SELECT [ALL|DISTINCT|TOP <数值表达式> [PERCENT]] [<别名>.]<表达式> [AS <列名>][,[<别名>.]<表达式>[AS <列名>] … ] FROM <表名> [[INNER|LEFT|RIGHT JOIN] <表名> [ON <联接条件>] … ],… [INTO <表名>] [WHERE <搜索条件>] [GROUP BY <组表达式>[,<组表达式> … ]][HAVING <搜索条件>] [UNION [ALL] <SELECT语句>] [ORDER BY <关键字表达式> [ASCDESC] [,<关键字表达式> [ASCDESC] … ]] 示例： (1) SELECT 编号,价格*0.17 AS 增值税 FROM 设备 (2) SELECT 编号,名称 INTO 设备目录 FROM 设备 (3) SELECT 大修.编号, 设备.名称, 设备.部门 AS 部门名 FROM 设备 INNER JOIN 大修 ON 大修.编号 = 设备.编号 WHERE 设备.编号>"03"

45. 〖例3.20〗 求出每一设备的增值金额。 SELECT 增值.编号, Sum(增值.金额) AS 金额小计 FROM 增值 GROUP BY 增值.编号 〖例3.22〗 列出价格低于20000元设备的名称、启用日期与部门,并按启用日期升序排序。 SELECT 名称,启用日期,部门 FROM 设备 WHERE 价格<20000 ORDER BY 启用日期 ASC

46. 3.4.4 SQL特定查询 1．联合查询 联合查询用于合并两个表中的数据。UNION子句用于实现联合查询。 2．数据定义查询 (1) 创建表结构 语句格式： CREATE TABLE <表名>(<字段名>(<字段大小>),…, CONSTRAINT [<索引名称>] PRIMARY KEY ([<字段名>])) (2) 创建索引 语句格式： CREATE INDEX <索引名称> ON <表名>([<字段名>,…] (3) 修改表结构 语句格式： ALTER TABLE <表名> ADD|ALTER <字段名> <字段类型> (4) 删除字段 语句格式： ALTER TABLE <表名> DROP <字段名>

47. 图3.57 【更新查询】设计视图 3.5 操作查询 3.5.1 更新查询  更新查询可以更改一个或多个表或查询中的数据。 〖例3.25〗 对于启用日期在1994年之前和2000年之后,或者价格高于20万元的设备,将其编号中的“-”号更改为“#”号。 要点：单击【查询】菜单中的【更新查询】命令, 设计网格中就增加一个【更新到】行。

48. 图3.60 【追加】对话框 3.5.2 追加查询 追加查询用于将一个或多个表中的一组记录添加到一个表的末尾。以查询设计视图中添加的表为数据源表,在【追加】对话框选定的表为目标表。 要点：单击【查询】菜单中的【追加查询】命令,即显示【追加】对话框。

49. 图3.62 追加了二个记录 图3.61 【追加查询】设计视图

50. 3.5.3 删除查询 “删除查询”可以从一个或多个表中删除符合指定条件的记录。 〖例3.27〗 修改图3.61,删除例3.26在大修表中添加的记录。 假定图3.61设计视图已打开,操作步骤如下： (1) 设计网格的设置：单击编号列,选定【编辑】菜单中的【删除列】命令将该列删除。单击【查询】菜单中的【删除查询】命令,使设计视图切换到【删除查询】类型(见图3.63)。在上窗格添加一个大修字段列表,然后将该字段列表中的星号(*)拖曳到设计网格的第一列。 (2) 查询预览：选定【视图】菜单中的【数据表视图】命令,即显示如图3.64所示数据表,其中列出了由设备表的部门值指出的大修表中要删除的记录。