88、Access数据库维护与查询全攻略

Access数据库维护与查询全攻略

1. 数据库对象分类与查询的重要性

在数据库管理中，对象分类是一项重要的工作。默认的“所有Access对象”类别是预定义的，但我们也可以创建自定义类别，以更适合自己管理数据库对象的方式对对象进行分组。随着数据库的不断构建和对象列表的增加，创建自定义类别能提高我们处理数据库对象的效率。

我们创建并保存的查询对于相关人员来说非常有用。比如，Cindi、Kelly、Ethan等工作人员可以利用这些查询来监控和分析查塔姆社区健康服务及其患者的业务活动。任何工作人员都可以随时运行查询、根据需要修改查询，或者将其作为设计新查询的基础，以满足额外的信息需求。

2. 复习与快速检查

以下是一些复习问题，帮助我们巩固所学知识：
1. 什么是决定查询数据表中哪些记录被选中的标准或规则？
答案是查询条件。查询条件就像是一个筛选器，它根据我们设定的规则从数据库中挑选出符合要求的记录。
2. 在设计网格中，使用And逻辑运算符和Or逻辑运算符时，不同字段的条件分别放在哪里？
当使用And逻辑运算符时，不同字段的条件应放在同一行，这表示只有当所有条件都满足时，记录才会被选中。而使用Or逻辑运算符时，不同字段的条件应放在不同行，只要满足其中一个条件，记录就会被选中。
3. 在查询中执行计算时，需要定义什么？
需要定义一个表达式，该表达式包含数据库字段、常量和运算符的组合。例如，我们可以通过表达式计算两个字段的和、差、积、商等。
4. 使用哪个Access工具来为查询中的计算字段创建表达式？
可以使用表达式生成器。表达式生成器提供了一个直观的界面，让我们可以方便地组合字段、常量和运算符，创建复杂的表达式。
5. 什么是聚合函数？
聚合函数用于对一组值进行计算，并返回一个单一的值。常见的聚合函数有SUM（求和）、AVG（求平均值）、COUNT（计数）、MAX（求最大值）和MIN（求最小值）等。
6. 哪个运算符根据字段中的值将选定的记录分组？
是Group By运算符。它可以将具有相同字段值的记录分组，然后对每个组进行聚合计算。
7. 导航窗格中对象显示的默认类别是什么？
默认类别是“所有Access对象”。

3. 复习作业操作步骤

以下是针对Vendor数据库的复习作业操作步骤：
1. 打开数据库 ：打开在之前教程中创建并使用过的Vendor数据库。如果出现安全警告，点击“启用内容”按钮。
2. 更新供应商表记录 ：
- 以数据表视图打开Supplier表。
- 找到Supplier ID为GRE364的记录，将地址改为1550 W Main St，联系电话改为401 - 625 - 2745，联系人名字改为Andrew，联系人姓氏改为Kline。
- 关闭该表。
3. 创建联系人列表查询 ：
- 基于Supplier表创建一个查询。
- 按以下顺序包含字段：Company、Category、ContactFirst、ContactLast、Phone和InitialContact。
- 按Category字段值升序排序查询。
- 将查询保存为ContactList并运行。
4. 更新供应商表记录 ：使用ContactList查询数据表，将Aresco Surgical, Inc.的Phone字段值改为203 - 335 - 0054。
5. 调整查询数据表格式 ：
- 将ContactList查询数据表的字体大小改为12磅。
- 必要时调整列大小，确保所有字段值和列标题可见。
6. 更改查询数据表交替行颜色 ：将ContactList查询数据表的交替行颜色改为名为“Green, Accent 6, Lighter 60%”的主题颜色，然后保存并关闭查询。
7. 创建CT供应商查询 ：
- 基于Supplier和Product表创建查询。
- 从Supplier表选择Company、Category和State字段，从Product表选择ProductName、Price、Units和Weight字段。
- 按价格降序排序查询结果。
- 只选择State字段值为CT的记录，但查询结果中不显示State字段值。
- 将查询保存为CTSuppliers，运行并关闭。
8. 创建高价且无菌产品查询 ：
- 创建一个查询，列出所有价格超过200美元且无菌的产品。
- 查询结果中显示Product表的以下字段：ProductID、ProductName、Price、Units和TempControl。（注意：Sterile字段是一个“是/否”字段，不应出现在查询结果中。）
- 将查询保存为HighPriceAndSterile，运行并关闭。
9. 创建设备或温度控制查询 ：
- 创建一个查询，列出销售需要温度控制的设备或产品的供应商信息。
- 包含Supplier表的Company、Category、ContactFirst和ContactLast字段，以及Product表的ProductName、Price、TempControl和Sterile字段。
- 将查询保存为EquipmentOrTempControl，运行并关闭。
10. 创建高价产品带折扣查询 ：
- 创建一个查询，列出价格在1000美元或以上的产品，并显示基于产品价格的5%折扣金额。
- 包含Supplier表的Company字段和Product表的ProductID、ProductName和Price字段。
- 在名为DiscountAmt的计算字段中显示折扣，该字段根据Price字段值计算5%的折扣。
- 为计算字段设置标题属性“Discount Amt”。
- 按Price降序显示查询结果。
- 保存并运行查询。

以下是这些操作步骤的流程图：

graph LR
    A[打开Vendor数据库] --> B[更新Supplier表记录]
    B --> C[创建ContactList查询]
    C --> D[更新Supplier表记录（通过ContactList）]
    D --> E[调整ContactList格式]
    E --> F[更改ContactList交替行颜色]
    F --> G[创建CTSuppliers查询]
    G --> H[创建HighPriceAndSterile查询]
    H --> I[创建EquipmentOrTempControl查询]
    I --> J[创建HighPriceWithDiscount查询]

4. 案例问题操作

案例问题1：GoGopher!数据库操作

1. 打开数据库 ：打开在之前教程中创建并使用过的Gopher数据库。如果出现安全警告，点击“启用内容”按钮。
2. 更新会员表记录 ：
   • 在Member表中，找到MemberID为1251的记录，将Street值改为75 Hemlock Ln，Phone改为303 - 553 - 1847。
3. 删除会员表记录 ：
   • 在Member表中，找到MemberID为1228的记录并删除。
   • 关闭Member表。
4. 创建免费用户查询 ：
   • 创建一个查询，列出加入时无需付费的会员。
   • 查询结果中显示Member表的FirstName、LastName和DateJoined字段，以及Plan表的PlanCost字段。
   • 按加入日期降序排序记录，只选择费用被免除的会员记录。（注意：FeeWaived字段是一个“是/否”字段，不应出现在查询结果中。）
   • 将查询保存为NoFees并运行。
5. 更新会员表记录 ：使用NoFees查询数据表，将Kara Murray的LastName值改为Seaburg。
6. 显示总计划成本 ：使用NoFees查询数据表显示所选会员的总Plan Cost。保存并关闭查询。
7. 创建四月会员查询 ：
   • 创建一个查询，列出在2016年4月1日至4月30日期间加入GoGopher!的会员的MemberID、FirstName、LastName、DateJoined、PlanDescription和PlanCost字段。
   • 将查询保存为AprilMembers，运行并关闭。
8. 创建路易维尔且会员到期查询 ：
   • 创建一个查询，列出居住在路易维尔且会员资格在2017年1月1日或之后到期的所有会员。
   • 查询结果中显示Member表的MemberID、FirstName、LastName、Phone和Expiration字段。（注意：City字段值不应出现在查询结果中。）
   • 按姓氏升序排序查询结果。
   • 将查询保存为LouisvilleAndExpiration，运行并关闭。
9. 创建路易维尔或会员到期查询 ：
   • 复制LouisvilleAndExpiration查询并创建一个新查询，命名为LouisvilleOrExpiration。
   • 修改新查询，使其列出居住在路易维尔或会员资格在2017年1月1日或之后到期的所有会员。
   • 在查询结果中，Phone字段值之后显示City字段值，并按城市升序排序查询结果。
   • 保存并运行查询。
10. 调整查询数据表格式 ：
    • 将LouisvilleOrExpiration查询数据表的字体大小改为14磅。
    • 必要时调整列大小，确保所有字段值和列标题可见。
11. 更改查询数据表交替行颜色 ：将LouisvilleOrExpiration查询数据表的交替行颜色改为名为“Blue, Accent 1, Lighter 80%”的主题颜色，然后保存并关闭查询。
12. 创建成本统计查询 ：
    • 创建一个查询，分别使用字段名LowestCost、HighestCost和AverageCost计算所有计划的最低、最高和平均成本。
    • 为每个字段设置标题属性，使其字段名中包含空格。
    • 运行查询，调整数据表中所有列的大小以使其最合适，将查询保存为CostStatistics，然后关闭。
13. 复制查询 ：在导航窗格中复制CostStatistics查询，并将复制的查询重命名为CostStatisticsByCity。
14. 修改查询 ：修改CostStatisticsByCity查询，使其按City字段分组显示相同的统计信息，City字段应作为查询数据表中的第一个字段。运行查询，然后保存并关闭。
15. 压缩和修复数据库 ：压缩和修复Gopher数据库，然后关闭。

以下是案例问题1的操作步骤表格：
| 步骤 | 操作内容 |
| ---- | ---- |
| 1 | 打开Gopher数据库，必要时启用内容 |
| 2 | 更新Member表中MemberID为1251的记录 |
| 3 | 删除Member表中MemberID为1228的记录 |
| 4 | 创建NoFees查询 |
| 5 | 更新Member表记录（通过NoFees） |
| 6 | 显示NoFees查询的总Plan Cost |
| 7 | 创建AprilMembers查询 |
| 8 | 创建LouisvilleAndExpiration查询 |
| 9 | 创建LouisvilleOrExpiration查询 |
| 10 | 调整LouisvilleOrExpiration查询格式 |
| 11 | 更改LouisvilleOrExpiration查询交替行颜色 |
| 12 | 创建CostStatistics查询 |
| 13 | 复制CostStatistics查询为CostStatisticsByCity |
| 14 | 修改CostStatisticsByCity查询 |
| 15 | 压缩和修复Gopher数据库并关闭 |

Access数据库维护与查询全攻略

5. 案例问题2：O’Brien Educational Services数据库操作

1. 打开数据库 ：打开之前创建并使用过的OBrien数据库。若出现安全警告，点击“启用内容”按钮。
2. 更新导师表记录 ：
   • 在Tutor表中，将TutorID为79 - 0678的记录的Degree改为BA，Hire Date改为9/15/2015。
   • 关闭该表。
3. 删除学生表记录 ：
   • 在Student表中，找到StudentID为MCS8051的记录，删除其相关子数据表中的记录。
   • 删除StudentID为MCS8051的记录，然后关闭Student表。
4. 创建学生手机列表查询 ：
   • 基于Student表创建查询，按顺序包含LastName、FirstName和CellPhone字段。
   • 将查询保存为StudentCellList并运行。
5. 更新学生表记录 ：在StudentCellList查询结果中，将Haley Gosnold的手机号码改为574 - 252 - 1973。关闭查询。
6. 创建导师课程查询 ：
   • 基于Tutor和Contract表创建查询。
   • 显示Tutor表的LastName字段，以及Contract表的StudentID、ContractDate、SessionType、Length和Cost字段。
   • 先按导师姓氏升序排序，再按StudentID升序排序。
   • 将查询保存为SessionsByTutor，运行并关闭。
7. 创建小组课程查询 ：
   • 复制SessionsByTutor查询创建新查询，命名为GroupSessions。
   • 修改新查询，使其仅显示Group会话类型的记录，且查询结果中不显示SessionType字段值。
   • 保存并运行查询，然后关闭。
8. 创建特定结果查询1 ：创建并保存一个能产生如图3 - 44所示结果的查询，完成后关闭。
9. 创建特定结果查询2 ：创建并保存一个能产生如图3 - 45所示结果的查询，完成后关闭。
10. 创建成本统计查询 ：创建并保存一个查询以显示Cost字段的统计信息，如图3 - 46所示，完成后关闭。
11. 复制查询 ：在导航窗格中复制CostStatistics查询，并重命名为CostStatisticsByCity。
12. 修改查询 ：修改CostStatisticsByCity查询，使其按City字段分组显示相同的统计信息，City字段作为查询数据表的第一个字段。运行查询，然后保存并关闭。
13. 压缩和修复数据库 ：压缩和修复OBrien数据库，然后关闭。

以下是案例问题2的操作步骤流程图：

graph LR
    A[打开OBrien数据库] --> B[更新Tutor表记录]
    B --> C[删除Student表记录]
    C --> D[创建StudentCellList查询]
    D --> E[更新Student表记录（通过StudentCellList）]
    E --> F[创建SessionsByTutor查询]
    F --> G[创建GroupSessions查询]
    G --> H[创建特定结果查询1]
    H --> I[创建特定结果查询2]
    I --> J[创建CostStatistics查询]
    J --> K[复制CostStatistics查询为CostStatisticsByCity]
    K --> L[修改CostStatisticsByCity查询]
    L --> M[压缩和修复OBrien数据库并关闭]

6. 案例问题3：Rosemary Animal Shelter数据库操作

1. 打开数据库 ：打开之前创建并使用过的Shelter数据库。若出现安全警告，点击“启用内容”按钮。
2. 删除赞助人表记录 ：
   • 在Patron表中，删除PatronID为36064的记录。（先使用插入子数据表对话框显示相关Donation子数据表中的记录并删除）
   • 关闭Patron表，不保存表布局更改。
3. 创建动物类型查询 ：
   • 基于Animal表创建查询，按顺序包含AnimalID、AnimalName、AnimalType、Gender和ArrivalDate字段。
   • 将查询保存为AnimalsByType并运行。
4. 修改查询设计 ：修改AnimalsByType查询设计，使其先按AnimalType升序排序，再按Gender升序排序。保存并运行查询。
5. 更新动物表记录 ：在AnimalsByType查询数据表中，找到Animal ID为D23的记录，将其到达日期改为5/19/2016。关闭查询。
6. 创建大额捐赠查询 ：
   • 创建一个查询，显示Patron表的PatronID、FirstName和LastName字段，以及Donation表中所有超过150美元捐赠的Description和DonationValue字段。
   • 按捐赠价值升序排序查询。
   • 将查询保存为LargeDonations，运行并关闭。
7. 复制查询 ：复制LargeDonations查询创建新查询，命名为LargeCashDonations。
8. 修改查询 ：修改LargeCashDonations查询，使其仅显示价值超过150美元的现金捐赠记录，查询结果中不包含Description字段值。使用查询数据表计算平均现金捐赠。保存并关闭查询。
9. 创建猫领养查询 ：
   • 创建一个查询，显示Patron表的PatronID、FirstName和LastName字段，以及Animal表中已被领养动物的AnimalName、AnimalType、Age、Gender和Adopted字段。
   • 查询结果中不显示Adopted字段值。
   • 将查询保存为CatAdoptions并运行。
10. 筛选查询结果 ：对CatAdoptions查询数据表的结果进行筛选，仅显示猫的记录。
11. 格式化查询数据表 ：
    • 对CatAdoptions查询的数据表进行格式化，使其不显示网格线，使用紫色2作为交替行标准颜色，字体大小为12磅。（使用HOME选项卡文本格式组中的网格线按钮选择合适的网格线选项）
    • 必要时调整列大小以显示完整的字段名和值。
    • 保存并关闭查询。
12. 创建非现金或六月捐赠查询 ：
    • 创建一个查询，显示Patron表的PatronID、FirstName和LastName字段，以及Donation表的Description、DonationDate和DonationValue字段。
    • 查询包含非现金捐赠记录或2016年6月进行的捐赠记录。
    • 先按赞助人姓氏升序排序，再按捐赠价值降序排序。
    • 将查询保存为NonCashOrJuneDonations，运行并关闭。
13. 复制查询 ：复制NonCashOrJuneDonations查询创建新查询，命名为DonationsAfterStorageCharge。
14. 修改查询 ：
    • 修改DonationsAfterStorageCharge查询，使其显示所有日期的非现金捐赠记录。
    • 创建一个名为NetDonation的计算字段，显示从DonationValue字段值中减去3.50美元后的结果，以考虑每个非现金捐赠物品的存储成本。
    • 为计算字段设置标题属性“Net Donation”。
    • 按捐赠价值升序显示结果。
    • 运行查询，将DonationValue字段和计算字段格式化为货币格式，保留两位小数。
    • 再次运行查询，必要时调整数据表中的列大小以使其最合适。
    • 保存并关闭查询。
15. 创建捐赠统计查询 ：
    • 基于Donation表创建一个查询，显示所有捐赠的DonationValue字段的总和、平均值和数量。
    • 指定字段名为TotalDonations、AverageDonation和NumberofDonations，并为每个字段指定包含空格的标题。
    • 将查询保存为DonationStatistics并运行，调整查询数据表的列大小以使其最合适。
    • 修改字段属性，使Total Donations和Average Donation列中的值显示两位小数并使用标准格式。
    • 再次运行查询，保存并关闭。
    • 复制DonationStatistics查询创建新查询，命名为DonationStatisticsByDescription。
    • 修改DonationStatisticsByDescription查询，使其按Description字段分组显示所有捐赠的DonationValue字段的总和、平均值和数量，Description字段作为第一个字段。按Total Donations降序排序记录。保存、运行并关闭查询。
16. 压缩和修复数据库 ：压缩和修复Shelter数据库，然后关闭。

以下是案例问题3的操作步骤表格：
| 步骤 | 操作内容 |
| ---- | ---- |
| 1 | 打开Shelter数据库，必要时启用内容 |
| 2 | 删除Patron表中PatronID为36064的记录 |
| 3 | 创建AnimalsByType查询 |
| 4 | 修改AnimalsByType查询设计 |
| 5 | 更新Animal表记录（通过AnimalsByType） |
| 6 | 创建LargeDonations查询 |
| 7 | 复制LargeDonations查询为LargeCashDonations |
| 8 | 修改LargeCashDonations查询 |
| 9 | 创建CatAdoptions查询 |
| 10 | 筛选CatAdoptions查询结果 |
| 11 | 格式化CatAdoptions查询数据表 |
| 12 | 创建NonCashOrJuneDonations查询 |
| 13 | 复制NonCashOrJuneDonations查询为DonationsAfterStorageCharge |
| 14 | 修改DonationsAfterStorageCharge查询 |
| 15 | 创建DonationStatistics及相关操作 |
| 16 | 压缩和修复Shelter数据库并关闭 |

7. 案例问题4：Stanley EcoTours数据库操作与故障排除

数据库维护与查询

1. 打开数据库 ：打开之前创建并使用过的Stanley数据库。若出现安全警告，点击“启用内容”按钮。
2. 更新客人表记录 ：在Guest表中，将Paul Barry的电话号码改为406 - 497 - 1068，然后关闭该表。
3. 创建旅游价格查询 ：
   • 基于Tour表创建查询，按顺序包含TourName、Location、Country、PricePerPerson和TourType字段。
   • 按PricePerPerson字段值升序排序。
   • 将查询保存为ToursByPrice并运行。
4. 显示总价格 ：使用ToursByPrice查询数据表显示旅游的总Price Per Person。保存并关闭查询。
5. 创建客人旅游日期查询 ：
   • 创建一个查询，显示Guest表的GuestLast、City和State/Prov字段，以及Reservation表的ReservationID、StartDate和People字段。
   • 将查询保存为GuestTourDates并运行。
   • 将查询数据表的交替行颜色改为名为“Green, Accent 6, Lighter 40%”的主题颜色。
   • 在数据表视图中，使用自动筛选器按Start Date从旧到新对查询结果进行排序。
   • 保存并关闭查询。
6. 创建蒙大拿客人查询 ：
   • 创建一个查询，显示所有来自蒙大拿州（MT）的客人的GuestFirst、GuestLast、City、ReservationID、TourID和StartDate字段。
   • 查询结果中不包含State/Prov字段。
   • 按客人姓氏升序排序查询。
   • 将查询保存为MontanaGuests并运行，然后关闭。
7. 创建加拿大或吉普车旅游查询 ：
   • 创建一个查询，显示数据库中三个表的相关数据：Guest表的GuestLast、City、State/Prov和Country字段，Reservation表的StartDate字段，Tour表的TourName、Location和TourType字段。
   • 查询仅选择来自加拿大的客人或参加吉普车旅游的客人的记录。
   • 按Location升序排序查询。
   • 将查询保存为CanadaOrJeep并运行，必要时调整数据表列大小以使其最合适，然后保存并关闭。
8. 复制查询 ：复制CanadaOrJeep查询创建新查询，命名为IdahoAndJuly。
9. 修改查询 ：
   • 修改IdahoAndJuly查询，使其选择所有来自爱达荷州且在2016年7月某个时间开始旅游的客人的记录。
   • 查询结果中不包含State/Prov字段值和Country字段值。
   • 运行查询，必要时调整数据表列大小以使其最合适，然后保存并关闭。
10. 创建单人预订查询 ：
    • 创建一个查询，显示Reservation表的ReservationID、StartDate和People字段，以及Tour表中所有People字段值为1的预订的TourName、Location、Country、PricePerPerson和SingleSupplement字段。
    • 在查询中添加一个名为TotalCost的字段，显示SingleSupplement字段值与PricePerPerson字段值相加的结果。
    • 为计算字段设置标题属性“Total Cost”。
    • 按TotalCost降序显示结果，查询结果中不包含People字段值。
    • 运行查询，将TotalCost字段格式化为显示0位小数。
    • 再次运行查询，必要时调整数据表列大小以使其最合适，然后保存并关闭。
11. 创建价格统计查询 ：
    • 基于Tour表创建一个查询，确定所有旅游的每人最低、平均和最高价格。
    • 指定字段名为LowestPrice、AveragePrice和HighestPrice。
    • 为每个字段设置包含空格的标题属性。
    • 将查询保存为PriceStatistics并运行。
    • 在设计视图中，为每列指定标准格式和两位小数。
    • 运行查询，调整所有数据表列的大小以使其最合适，保存更改并关闭。
    • 复制PriceStatistics查询创建新查询，命名为PriceStatisticsByTourType。
    • 修改PriceStatisticsByTourType查询，使其按TourType字段分组显示价格统计信息，TourType字段作为第一个字段。保存更改，运行并关闭查询。
12. 压缩和修复数据库 ：压缩和修复Stanley数据库，然后关闭。

故障排除

1. 修复预订日期和州查询 ：
   • 打开Tour数据库，若出现安全警告，点击“启用内容”按钮。
   • 运行Tour数据库中的BookingByDateAndState查询，该查询未产生预期结果。
   • 修复查询，使Booking表的数据首先列出，数据仅按StartDate升序排序，结果中不显示国家值。
   • 保存并关闭修正后的查询。
2. 修复怀俄明州少人预订查询 ：运行WYGuestsFewerPeople查询，结果显示无记录。该查询应显示来自怀俄明州且预订人数少于4人的客人的数据。查找并纠正查询设计中的错误，运行查询，然后关闭。
3. 修复加拿大或九月开始查询 ：运行CanadaOrSeptStart查询，该查询应显示所有来自加拿大或预订开始日期在2016年9月1日或之后的客人的记录。查找并纠正查询设计中的错误，运行查询，然后关闭。压缩和修复Tour数据库，然后关闭。

以下是案例问题4的操作步骤流程图：

graph LR
    A[打开Stanley数据库] --> B[更新Guest表记录]
    B --> C[创建ToursByPrice查询]
    C --> D[显示ToursByPrice总价格]
    D --> E[创建GuestTourDates查询]
    E --> F[创建MontanaGuests查询]
    F --> G[创建CanadaOrJeep查询]
    G --> H[复制CanadaOrJeep查询为IdahoAndJuly]
    H --> I[修改IdahoAndJuly查询]
    I --> J[创建SingleReservations查询]
    J --> K[创建PriceStatistics及相关操作]
    K --> L[压缩和修复Stanley数据库并关闭]
    L --> M[打开Tour数据库并修复BookingByDateAndState查询]
    M --> N[修复WYGuestsFewerPeople查询]
    N --> O[修复CanadaOrSeptStart查询并压缩修复关闭Tour数据库]

通过以上一系列的操作和案例，我们可以全面地掌握Access数据库的维护和查询技巧，更好地管理和分析数据库中的数据。