54、微软 Access 2010 高级应用：数据库报表与 SQL 查询实战

微软 Access 2010 高级应用：数据库报表与 SQL 查询实战

1. 学生作业任务概述

学生需要完成三个不同数据库的相关任务，包括创建表格、查询以及生成报表。具体任务如下：
- 商会数据库 ：帮助商会跟踪活跃广告账户，创建相关表格和查询，并生成广告代表主报告。
- 寄售数据库 ：为寄售商店的志愿者团队准备两份报表，并将 Excel 工作簿链接到数据库。
- 老年护理数据库 ：更好地跟踪助手工作时长，创建服务表格和查询，并生成助手主报告。

2. 商会数据库任务详解

2.1 创建表格

需要创建两个表格来跟踪活跃账户，表格结构如下：
| 表格名称 | 字段名称 | 数据类型 | 字段大小 | 描述 |
| ---- | ---- | ---- | ---- | ---- |
| 广告类别表 | 类别代码 | 文本 | 2 | 主键 |
| 广告类别表 | 类别描述 | 文本 | 40 | |
| 活跃账户表 | 广告商编号 | 文本 | 4 | 主键一部分 |
| 活跃账户表 | 广告月份 | 文本 | 3 | 主键一部分 |
| 活跃账户表 | 类别代码 | 文本 | 3 | 外键，匹配广告类别表主键 |

操作步骤：
1. 按照上述结构创建广告类别表和活跃账户表。
2. 将活跃账户文本文件导入活跃账户表，将广告类别文本文件导入广告类别表。
3. 更新商会数据库的关系。

2.2 创建查询

• 广告代表与广告商查询 ：连接广告代表表和广告商表，包含广告代表编号、名字和姓氏字段，以及广告商表除广告代表编号外的所有字段，保存为“Ad Reps and Advertisers”。
• 广告类别与活跃账户查询 ：连接活跃账户表和广告类别表，包含广告商编号、广告月份、类别代码和类别描述字段，保存为“Ad Categories and Active Accounts”。

2.3 创建报表

创建一个广告代表主报告，以“Ad Reps and Advertisers”查询为主要报告基础，“Ad Categories and Active Accounts”查询为子报告基础。报告应类似于指定页面的示例报告，连接广告代表的名字和姓氏，并在页面页脚放置页码。

graph LR
    A[开始] --> B[创建表格]
    B --> C[导入数据]
    C --> D[更新关系]
    D --> E[创建查询]
    E --> F[创建报表]
    F --> G[提交数据库]
    G --> H[结束]

3. 寄售数据库任务详解

3.1 创建查询

创建一个连接卖家表和物品表的查询，包含卖家代码、名字、姓氏、物品编号、描述、价格和发布日期字段，保存为“Sellers and Items Query”。

3.2 创建报表

• 物品销售报告 ：风格类似于指定页面的折扣报告，按卖家代码分组，包含卖家名字和姓氏，详细部分包含物品编号、描述、发布日期和价格。创建一个计算控件“销售价格”，根据物品价格计算不同折扣后的价格，不包含小计和总计，选择自己喜欢的字体。
• 降价报告 ：同样按卖家代码分组，包含卖家名字和姓氏，详细部分包含物品编号、描述、发布日期和价格。创建一个计算控件“降价后价格”，根据物品发布日期计算不同折扣后的价格，不包含小计和总计，选择自己喜欢的字体。

3.3 链接 Excel 工作簿

将价格工作表链接到数据库，将链接的价格表重命名为“建议价格”，然后使用链接表管理器更新 Excel 工作表和 Access 表之间的链接。

4. 老年护理数据库任务详解

4.1 创建表格

创建一个用于存储服务数据的表格，结构与账户表类似，但客户编号字段大小为 4，命名为“服务”，导入“Services.xlsx”工作簿，并更新老年护理数据库的关系。

4.2 创建查询

创建一个连接助手表和客户表的查询，包含助手编号、名字和姓氏字段，以及客户表除助手编号外的所有字段，保存为“Helpers and Clients”。

4.3 创建报表

创建一个助手主报告，以“Helpers and Clients”查询为主要报告基础，服务表为子报告基础。报告应类似于指定页面的示例报告，添加一个“总金额”字段，为已支付金额和余额之和，连接助手的名字和姓氏，并更改客户名字和姓氏的标签，避免显示表名。

graph LR
    A[开始] --> B[创建服务表格]
    B --> C[导入工作簿]
    C --> D[更新关系]
    D --> E[创建查询]
    E --> F[创建报表]
    F --> G[提交数据库]
    G --> H[结束]

5. SQL 查询概述

5.1 SQL 简介

SQL（结构化查询语言）是用于查询和更新数据库的重要语言，几乎所有数据库管理系统都以某种方式使用它。虽然一些用户可以通过 Access 的查询功能完成所有查询，但负责管理和维护数据库系统的人员应该熟悉 SQL。

5.2 SQL 查询目标

学会创建 SQL 查询，包括涉及条件的查询、对查询结果进行排序、对记录进行分组并执行分组计算、在查询中连接表格、创建包含子查询的查询，以及使用 INSERT、UPDATE 和 DELETE 命令更新数据。

6. SQL 查询操作步骤

6.1 启动 Access

假设 Windows 7 正在运行，启动 Access 的步骤如下：
1. 点击 Windows 7 任务栏上的“开始”按钮，显示开始菜单。
2. 在“搜索程序和文件”文本框中输入“Microsoft Access”，等待搜索结果出现在开始菜单上。
3. 点击开始菜单搜索结果中的“Microsoft Access 2010”启动 Access。
4. 如果 Access 窗口未最大化，点击其标题栏上关闭按钮旁边的“最大化”按钮将窗口最大化。

6.2 打开数据库

从 USB 闪存驱动器打开 Camashaly Design 数据库的步骤如下：
1. 将 USB 闪存驱动器连接到计算机的 USB 端口，点击功能区上的“文件”打开后台视图。
2. 在后台视图中点击“打开”，显示“打开”对话框。
3. 导航到要打开的文件位置（USB 闪存驱动器，然后是 CIS 101 文件夹 [或你的班级文件夹]，再到 Access 文件夹）。
4. 点击“Camashaly Design”选择要打开的文件。
5. 点击“打开”对话框中的“打开”按钮打开所选文件，并在 Access 窗口中显示打开的数据库。
6. 如果出现安全警告，点击“启用内容”选项按钮。

6.3 SQL 查询设计准则

在使用 SQL 查询数据库时，需要遵循以下准则：
1. 选择字段 ：检查要构建的查询要求，确定要包含的字段。
2. 确定表格 ：对于每个字段，确定其所在的表格。
3. 确定条件 ：确定数据必须满足的任何条件，以包含在结果中。如果查询中有多个表格，确定确保数据正确匹配的条件。
4. 确定排序顺序 ：确定数据是否需要以某种方式排序，如果是，确定按哪个或哪些字段排序。
5. 确定分组 ：确定数据是否需要以某种方式分组，如果是，确定按哪个字段分组，并确定为该组要进行的任何计算。
6. 确定更新操作 ：确定是否需要插入、更改或删除行，以及涉及的表格。

通过以上任务和操作步骤，学生可以全面掌握数据库表格创建、查询设计、报表生成以及 SQL 查询的相关技能。

微软 Access 2010 高级应用：数据库报表与 SQL 查询实战

7. SQL 查询具体操作示例

7.1 创建涉及条件的 SQL 查询

在 SQL 中创建涉及条件的查询时，我们可以使用 WHERE 子句来筛选数据。例如，假设我们要从 Customers 表中查询所有来自“New York”的客户，SQL 语句如下：

SELECT * FROM Customers
WHERE City = 'New York';

这个查询会返回 Customers 表中 City 字段值为“New York”的所有记录。

操作步骤如下：
1. 打开 Access 并切换到 SQL 视图。
2. 在 SQL 视图中输入上述 SQL 语句。
3. 点击“运行”按钮查看查询结果。

7.2 对查询结果进行排序

使用 ORDER BY 子句可以对查询结果进行排序。例如，我们要对 Orders 表中的订单按照订单日期进行升序排序，SQL 语句如下：

SELECT * FROM Orders
ORDER BY OrderDate ASC;

如果要进行降序排序，将 ASC 改为 DESC 即可。

操作步骤如下：
1. 打开 Access 并切换到 SQL 视图。
2. 输入上述 SQL 语句。
3. 点击“运行”按钮查看排序后的结果。

7.3 对记录进行分组并执行分组计算

使用 GROUP BY 子句可以对记录进行分组，结合聚合函数（如 SUM 、 COUNT 等）可以执行分组计算。例如，我们要统计每个客户的订单总数，SQL 语句如下：

SELECT CustomerID, COUNT(OrderID) AS OrderCount
FROM Orders
GROUP BY CustomerID;

这个查询会将 Orders 表中的记录按照 CustomerID 进行分组，并统计每个客户的订单数量。

操作步骤如下：
1. 打开 Access 并切换到 SQL 视图。
2. 输入上述 SQL 语句。
3. 点击“运行”按钮查看分组计算结果。

8. 在查询中连接表格

在 SQL 中，使用 JOIN 关键字可以在查询中连接多个表格。常见的连接类型有内连接（ INNER JOIN ）、左连接（ LEFT JOIN ）、右连接（ RIGHT JOIN ）和全连接（ FULL JOIN ）。

8.1 内连接示例

假设我们有 Customers 表和 Orders 表，要查询每个客户及其对应的订单信息，使用内连接的 SQL 语句如下：

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

这个查询会返回 Customers 表和 Orders 表中 CustomerID 匹配的记录。

操作步骤如下：
1. 打开 Access 并切换到 SQL 视图。
2. 输入上述 SQL 语句。
3. 点击“运行”按钮查看连接结果。

8.2 左连接示例

如果我们要查询所有客户及其对应的订单信息，即使某些客户没有订单，也会显示这些客户的信息，使用左连接的 SQL 语句如下：

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

操作步骤与内连接类似。

graph LR
    A[Customers 表] -->|INNER JOIN| C[连接结果]
    B[Orders 表] -->|INNER JOIN| C
    A -->|LEFT JOIN| D[左连接结果]
    B -->|LEFT JOIN| D

9. 创建包含子查询的查询

子查询是指在一个查询中嵌套另一个查询。例如，我们要查询订单金额大于平均订单金额的订单信息，SQL 语句如下：

SELECT * FROM Orders
WHERE OrderAmount > (SELECT AVG(OrderAmount) FROM Orders);

这个查询会先计算 Orders 表中订单金额的平均值，然后返回订单金额大于这个平均值的所有订单记录。

操作步骤如下：
1. 打开 Access 并切换到 SQL 视图。
2. 输入上述 SQL 语句。
3. 点击“运行”按钮查看查询结果。

10. 使用 INSERT、UPDATE 和 DELETE 命令更新数据

10.1 INSERT 命令

INSERT 命令用于向表格中插入新记录。例如，向 Employees 表中插入一条新员工记录，SQL 语句如下：

INSERT INTO Employees (EmployeeName, Department, Salary)
VALUES ('John Doe', 'Sales', 5000);

操作步骤如下：
1. 打开 Access 并切换到 SQL 视图。
2. 输入上述 SQL 语句。
3. 点击“运行”按钮执行插入操作。

10.2 UPDATE 命令

UPDATE 命令用于更新表格中的现有记录。例如，将 Employees 表中所有 Department 为“Sales”的员工的 Salary 提高 10%，SQL 语句如下：

UPDATE Employees
SET Salary = Salary * 1.1
WHERE Department = 'Sales';

操作步骤如下：
1. 打开 Access 并切换到 SQL 视图。
2. 输入上述 SQL 语句。
3. 点击“运行”按钮执行更新操作。

10.3 DELETE 命令

DELETE 命令用于删除表格中的记录。例如，删除 Employees 表中 EmployeeName 为“John Doe”的记录，SQL 语句如下：

DELETE FROM Employees
WHERE EmployeeName = 'John Doe';

操作步骤如下：
1. 打开 Access 并切换到 SQL 视图。
2. 输入上述 SQL 语句。
3. 点击“运行”按钮执行删除操作。

通过以上详细的示例和操作步骤，我们可以更深入地理解和掌握 SQL 查询的各种操作，包括创建不同类型的查询、连接表格、使用子查询以及更新数据等。这些技能对于管理和操作数据库非常重要，能够帮助我们更高效地处理和分析数据。