第9章设计和创建查询

1、 随着公司销售规模的不断壮大，在随着公司销售规模的不断壮大，在经营管理过程中，需要随时能够方便且经营管理过程中，需要随时能够方便且快捷地查询商品、订单、客户、进货以快捷地查询商品、订单、客户、进货以及库存的信息。及库存的信息。 同时需要进行商品的利润率统计，更新同时需要进行商品的利润率统计，更新和统计商品的库存量，按月统计销售部门的和统计商品的库存量，按月统计销售部门的销售额，结算客户的消费额度，统计业务员销售额，结算客户的消费额度，统计业务员的销售业绩等数据的处理与分析，为公司的的销售业绩等数据的处理与分析，为公司的销售决策提供可靠的依据。销售决策提供可靠的依据。 在本任务中，我们将通过多参

2、数查询、在本任务中，我们将通过多参数查询、更新查询、计算查询、交叉表查询和更新查询、计算查询、交叉表查询和SQL查查询来实现以上查询需求。询来实现以上查询需求。9.2.1 交叉表查询交叉表查询 交叉表查询是交叉表查询是Access支持的另一类支持的另一类查询对象。查询对象。 使用交叉表查询可以计算并重新组使用交叉表查询可以计算并重新组织数据的结构，这样可以更加方便地分织数据的结构，这样可以更加方便地分析数据。析数据。 交叉表査询是将来源于某个表中的交叉表査询是将来源于某个表中的字段进行分组，一组放在交叉表最左端字段进行分组，一组放在交叉表最左端的行标题处，它将某一字段的相关数据的行标题处，它将

3、某一字段的相关数据放入指定的行中，一组放在交叉表最上放入指定的行中，一组放在交叉表最上面的列标题处，它将某一字段的相关数面的列标题处，它将某一字段的相关数据放入指定的列中，并在交叉表行与列据放入指定的列中，并在交叉表行与列的交叉处显示表中某个字段的各种计算的交叉处显示表中某个字段的各种计算值，如总计、平均值及计数等。值，如总计、平均值及计数等。 例如，在例如，在“商贸管理系统商贸管理系统”数据库数据库的的“订单订单”表中，如果希望得到各个业表中，如果希望得到各个业务员的销售总金额一览表，就需要应用务员的销售总金额一览表，就需要应用交叉表查询来实现。交叉表查询来实现。 在交叉表查询中，最多可以指

4、定在交叉表查询中，最多可以指定3个个行标题，但只能指定一个列字段和一个行标题，但只能指定一个列字段和一个总计类型的字段。总计类型的字段。 创建交叉表查询时，可以使用交叉创建交叉表查询时，可以使用交叉表查询向导，也可以使用查询设计器来表查询向导，也可以使用查询设计器来创建。创建。 若使用交叉表查询向导，那么其查若使用交叉表查询向导，那么其查询的数据源只能有一个，否则，需要先询的数据源只能有一个，否则，需要先创建包含查询字段的查询，然后利用该创建包含查询字段的查询，然后利用该查询作为交叉表查询的数据源。查询作为交叉表查询的数据源。 SQL查询是用户使用查询是用户使用 SQL语句创建语句创建的查询。

5、的查询。 可以用结构化查询语言（可以用结构化查询语言（SQL）来）来查询、更新和管理查询、更新和管理Access关系数据库。关系数据库。 在查询设计视图中创建查询时，在查询设计视图中创建查询时，Access将在后台构造等效的将在后台构造等效的SQL语句。语句。 实际上，在查询设计视图的属性表实际上，在查询设计视图的属性表中，大多数查询属性在中，大多数查询属性在SQL视图中都有视图中都有等效的可用子句和选项。等效的可用子句和选项。 如果需要，则可以在如果需要，则可以在SQL视图中查视图中查看和编辑看和编辑SQL语句。语句。SELECT ALL | * | DISTINCT | TOP 查询查询项

6、项1 查询项查询项2FROM数据源数据源WHERE 条件条件GROUP BY 分组表达式分组表达式HAVING 条件条件ORDER BY 排序项排序项 | ASC | DESC 在上面的语法格式中，在上面的语法格式中， 外的语句是必须外的语句是必须的，而的，而 内的是可选的。内的是可选的。 对于以对于以“|”分隔的操作符，表明语法中必分隔的操作符，表明语法中必须从须从“|”分隔的操作符中选择一个。分隔的操作符中选择一个。 查询项是指要输出的查询项目。查询项是指要输出的查询项目。 通常是字段名或表达式，也可以是常数。通常是字段名或表达式，也可以是常数。 数据源可以是表，也可以是查询。数据源可以是

7、表，也可以是查询。 排序项指定排序的关键字，可以是排序项指定排序的关键字，可以是一个字段，也可以是多个字段。一个字段，也可以是多个字段。 在在SELECT语句中，语句中，SELECT指定指定需要检索的字段，需要检索的字段，FROM指定要查询的指定要查询的表，表，WHERE指定选择记录的条件，另指定选择记录的条件，另外还可以包含外还可以包含ORDER BY语句来指定排语句来指定排序记录。序记录。 ALL：返回查询到的所有记录，包括那：返回查询到的所有记录，包括那些重复的记录，些重复的记录，ALL关键字可以省略。关键字可以省略。 *：返回数据源中所有字段的信息，如：返回数据源中所有字段的信息，如S

8、ELECT * FROM商品。商品。 DISTINCT：如果多个记录的选择字段的数：如果多个记录的选择字段的数据相同，则只返回一个。据相同，则只返回一个。 TOP：显示查询头尾若干记录。也可返回：显示查询头尾若干记录。也可返回记录的百分比，这是要用记录的百分比，这是要用 TOP N PERCENT子句（其中子句（其中N表示百分比）。表示百分比）。 FROM子句指定子句指定SELECT语句中的语句中的数据来源。数据来源。 FROM子句后面可以是一个或多个子句后面可以是一个或多个表达式，它们之间用逗号分隔。表达式，它们之间用逗号分隔。 其中的表达式可为单一表名称，也其中的表达式可为单一表名称，也可

9、为已保存的查询或由可为已保存的查询或由INNER JOIN、LEFT JOIN、RIGHT JOIN得到的复合得到的复合结果。结果。 WHERE子句是一个行选择说明子子句是一个行选择说明子句，用这个语句可以指定查询条件，然句，用这个语句可以指定查询条件，然后对表中的记录进行限制。后对表中的记录进行限制。 当当WHERE后面的行选择说明为真的后面的行选择说明为真的时候，才将这些行作为查询的行，而且在时候，才将这些行作为查询的行，而且在WHERE中还可以有多种约束条件，这些中还可以有多种约束条件，这些条件可以通过条件可以通过“AND”、“OR”这样的这样的 逻辑运算符连接起来。逻辑运算符连接起来。

10、 GROUP BY子句指明了按照哪几个字子句指明了按照哪几个字段来分组，而将记录分组后，用段来分组，而将记录分组后，用HAVING子句过滤这些记录。子句过滤这些记录。 GROUP BY子句的语法如下。子句的语法如下。SELECT fidldlistFROM tableWHERE criteriaGROUP BY groupfieldlist HAVING groupcriteria ORDER BY子句按一个或多个（最多子句按一个或多个（最多16 个）字段排序查询结果，可以是升序（个）字段排序查询结果，可以是升序（ASC）也可以是降序（也可以是降序（DESC），默认是升序。），默认是升序。 O

11、RDER子句通常放在子句通常放在SQL语句的最后。语句的最后。 如果如果ORDER子句中定义了多个字段，则子句中定义了多个字段，则按照字段的先后顺序排列。按照字段的先后顺序排列。9.3.1 统计订单的销售金额统计订单的销售金额 在在“订单订单”表中，表中，“销售金额销售金额”字段字段的数据值可通过的数据值可通过“订购量订购量”与与“商品商品”表表中的中的“销售价销售价”相乘计算出。相乘计算出。 因此，在编辑因此，在编辑“订单订单”表时，该列数表时，该列数据可不用输入。据可不用输入。 这里，我们使用更新查询为其进行数这里，我们使用更新查询为其进行数据填充。据填充。（1）打开）打开“商贸管理系统商

12、贸管理系统”数据库。数据库。（2）在数据库窗口中选择）在数据库窗口中选择“查询查询”对象，对象，双击双击“在设计视图中创建查询在设计视图中创建查询”，打开，打开查询设计器。查询设计器。（3）在）在“显示表显示表”对话框中选择对话框中选择“订单订单”表和表和“商品商品”表作为查询数据源。表作为查询数据源。（4）将）将“订单订单”表的表的“销售金额销售金额”字段添字段添加到查询设计器的设计区中。加到查询设计器的设计区中。 （5）选择【查询】）选择【查询】【更新查询】命令，【更新查询】命令，将查询类型由选择查询改为更新查询。将查询类型由选择查询改为更新查询。 （6）在）在“销售金额销售金额”字段的字

13、段的“更新到更新到”网网格处单击鼠标右键，从弹出的快捷菜单中格处单击鼠标右键，从弹出的快捷菜单中选择【生成器】命令，弹出选择【生成器】命令，弹出“表达式生成表达式生成器器”对话框，然后构建如图对话框，然后构建如图3.16所示的销所示的销售金额计算表达式。售金额计算表达式。 （7）单击【确定】按钮，返回查询设计）单击【确定】按钮，返回查询设计器，构建出如图器，构建出如图3.17所示的所示的“销售金额销售金额”计算表达式。计算表达式。 （8）将查询保存为）将查询保存为“统计订单的销售金统计订单的销售金额额”。（9）运行查询，执行更新操作。）运行查询，执行更新操作。（10）打开）打开“订单订单”数据

14、表，即可看见更数据表，即可看见更新后的新后的“订单订单”表如图表如图3.18所示。所示。 “库存表库存表”中原有的中原有的“库存量库存量”为期为期初库存量，当有了进货之后，该库存量初库存量，当有了进货之后，该库存量是会随之而发生变化的。是会随之而发生变化的。 我们可以通过更新查询来实现库存我们可以通过更新查询来实现库存量的修改，即新的库存量量的修改，即新的库存量=原库存量原库存量+进进货数量。货数量。 （1）打开查询设计器，将）打开查询设计器，将“库存表库存表”和和“进货表进货表”添加到查询设计器中作为数添加到查询设计器中作为数据源。据源。（2）将）将“库存表库存表”的的“库存量库存量”字段添

15、加字段添加到查询设计器的设计区中。到查询设计器的设计区中。（3）选择【查询】）选择【查询】【更新查询】命令，【更新查询】命令，将查询类型更改为更新查询。将查询类型更改为更新查询。 （4）在）在“库存量库存量”字段的字段的“更新到更新到”网网格处单击鼠标右键，从弹出的快捷菜单格处单击鼠标右键，从弹出的快捷菜单中选择【生成器】命令，弹出中选择【生成器】命令，弹出“表达式表达式生成器生成器”对话框，然后构建如图对话框，然后构建如图3.19所示所示的库存量计算表达式。的库存量计算表达式。 （5）单击【确定】按钮，返回查询设计）单击【确定】按钮，返回查询设计器，构建出库存量计算表达式。器，构建出库存量计

16、算表达式。 （6）将查询保存为）将查询保存为“更新库存量更新库存量”。（7）运行查询，执行更新操作。）运行查询，执行更新操作。（8）打开）打开“库存表库存表”数据表，即可看见更数据表，即可看见更新后的新后的“库存表库存表”，如图，如图3.20所示。所示。 为了查看到详细的订单信息，这里，我们将为了查看到详细的订单信息，这里，我们将通过多表查询和字段别名，在订单明细查询中显通过多表查询和字段别名，在订单明细查询中显示订单编号、商品名称、规格型号、销售价、订示订单编号、商品名称、规格型号、销售价、订购日期、发货日期、订购量、销售金额、销售部购日期、发货日期、订购量、销售金额、销售部门、业务员、是否

17、付款、付款时间以及客户地址门、业务员、是否付款、付款时间以及客户地址等信息。等信息。 （1）打开查询设计器，将）打开查询设计器，将“订单订单”、“商商品品”和和“客户客户”表添加到查询设计器中作表添加到查询设计器中作为数据源。为数据源。 （2）依次添加）依次添加“订单编号订单编号”、“商品名称商品名称”、“规格型号规格型号”、“销售价销售价”、“订购日期订购日期”、“发货日期发货日期”、“订购量订购量”、“销售金额销售金额”、“销售部门销售部门”、“业务员业务员”、“是否付款是否付款”、“付款时间付款时间”、“公司名称公司名称”、“城市城市”、 “电话电话”和和“地址地址”字段到查询设计器的设

18、计字段到查询设计器的设计区中。区中。 （3）在）在“字段字段”行中，将最后的行中，将最后的“地址地址”字段修改为字段修改为“客户地址客户地址:地址地址”以作为以作为“地地址址”字段的别名，如图字段的别名，如图3.21所示。所示。 （4）以）以“查看订单明细信息查看订单明细信息”为名保存查为名保存查询。运行查询后得到如图询。运行查询后得到如图3.22所示的查询结所示的查询结果。果。 在销售管理过程中，为了了解某一在销售管理过程中，为了了解某一时段的销售情况，查看订单时，可根据时段的销售情况，查看订单时，可根据给出的时间范围，使用双参数查询动态给出的时间范围，使用双参数查询动态地显示指定时间内的订

19、单。地显示指定时间内的订单。（1）打开查询设计器，将）打开查询设计器，将“订单订单”表添加表添加到查询设计器中作为数据源。到查询设计器中作为数据源。（2）将）将“订单订单”表的所有字段添加到查询表的所有字段添加到查询设计器的设计区中。设计器的设计区中。 （3）在）在“订购日期订购日期”字段下方设置条件字段下方设置条件“Between起始时间起始时间And结束时间结束时间”， 如图如图3.23所示。所示。 （4）以）以“按时间段查询订单信息按时间段查询订单信息”为名为名 保存查询，运行查询时，将依次弹出如保存查询，运行查询时，将依次弹出如 图图3.24所示的两个所示的两个“输入参数值输入参数值”

20、对话框。对话框。 （5）如果想查询）如果想查询2011-4-20到到2011-5-20的的订单，则在第一个文本框中输入订单，则在第一个文本框中输入“2011-4-20”，单击【确定】按钮后，在第二个，单击【确定】按钮后，在第二个文本框中输入文本框中输入“2011-5-20”，再次单击【，再次单击【确定】按钮后，得到如图确定】按钮后，得到如图3.25所示的查询所示的查询结果。结果。 在商品的销售管理过程中，为了分析商品在商品的销售管理过程中，为了分析商品的销售利润率，我们可利用表中已有字段的数的销售利润率，我们可利用表中已有字段的数据，通过添加计算字段据，通过添加计算字段“销售毛利率销售毛利率”

21、，并构，并构建建“销售毛利率销售毛利率”的表达式，即销售毛利率的表达式，即销售毛利率=（销售价（销售价进货价）进货价）/销售价销售价100%，然后运，然后运行查询来实现。行查询来实现。（1）打开查询设计器，将）打开查询设计器，将“商品商品”表添加表添加到查询设计器中作为数据源。到查询设计器中作为数据源。（2）将）将“商品商品”表的所有字段添加到查询表的所有字段添加到查询设计器的设计区中。设计器的设计区中。（3）构建）构建“销售毛利率销售毛利率”字段。字段。（4）以）以“查看各种商品的销售毛利率查看各种商品的销售毛利率”为为名保存查询。运行查询，得到如图名保存查询。运行查询，得到如图3.28所所

22、示的查询结果。示的查询结果。 在前面创建的选择查询中，查询的在前面创建的选择查询中，查询的结果是从数据表中显示满足条件的记录结果是从数据表中显示满足条件的记录或显示部分字段的明细数据。或显示部分字段的明细数据。 如果想得到汇总结果，那么可通过如果想得到汇总结果，那么可通过交叉表查询实现数据的分类汇总。交叉表查询实现数据的分类汇总。 这里，我们将利用交叉表查询汇总这里，我们将利用交叉表查询汇总各部门各业务员的销售业绩。各部门各业务员的销售业绩。 （1）在数据库窗口中选择）在数据库窗口中选择“查询查询”对象，对象，单击【新建】按钮，弹出如图单击【新建】按钮，弹出如图3.29所示的所示的“新建查询新

23、建查询”对话框。对话框。 （2）选择）选择“交叉表查询向导交叉表查询向导”后，单击后，单击【确定】按钮，弹出如图【确定】按钮，弹出如图3.30所示的所示的“交交叉表查询向导叉表查询向导”第第1步对话框。步对话框。 （3）指定交叉表查询的数据源为）指定交叉表查询的数据源为“订单订单”表，单击【下一步】按钮，弹出如图表，单击【下一步】按钮，弹出如图3.31 所示的所示的“交叉表查询向导交叉表查询向导”第第2步对话框，步对话框，确定交叉表查询中的行标题字段。确定交叉表查询中的行标题字段。 （4）将行标题）将行标题“销售部门销售部门”字段添加到字段添加到“选选定字段定字段”列表框中，单击【下一步】按钮

24、，列表框中，单击【下一步】按钮，弹出如图弹出如图3.32所示的所示的“交叉表查询向导交叉表查询向导”第第3步对话框，然后确定交叉表查询中的列标题步对话框，然后确定交叉表查询中的列标题字段。字段。 （5）将）将“业务员业务员”字段作为列标题，单字段作为列标题，单击【下一步】按钮，弹出如图击【下一步】按钮，弹出如图3.33所示的所示的“交叉表查询向导交叉表查询向导”第第4步对话框，然后步对话框，然后确定行和列的交叉点的值字段及函数。确定行和列的交叉点的值字段及函数。 （6）选择）选择“销售金额销售金额”字段作为交叉表中的字段作为交叉表中的值，并设置值，并设置“函数函数”为为“求和求和”。 （7）单

25、击【下一步】按钮，弹出如图）单击【下一步】按钮，弹出如图3.34所所示的示的“交叉表查询向导交叉表查询向导”第第5步对话框。输步对话框。输入入“汇总统计各部门各业务员的销售业绩汇总统计各部门各业务员的销售业绩”作为查询的名称。作为查询的名称。 （8）选中）选中“查看查询查看查询”单选按钮，单击【完单选按钮，单击【完成】按钮，显示如图成】按钮，显示如图3.35所示的查询结果。所示的查询结果。 （9）关闭查询，完成交叉表查询的创建。）关闭查询，完成交叉表查询的创建。 为了查看各部门每月销售金额的完为了查看各部门每月销售金额的完成情况，这里，我们使用交叉表查询汇成情况，这里，我们使用交叉表查询汇总统

26、计各部门每月的销售金额。总统计各部门每月的销售金额。 （1）在数据库窗口中选择）在数据库窗口中选择“查询查询”对象，单对象，单 击【新建】按钮，弹出击【新建】按钮，弹出“新建查询新建查询”对话框。对话框。 （2）选择）选择“交叉表查询向导交叉表查询向导”后，单击【确后，单击【确 定】按钮，弹出定】按钮，弹出“交叉表查询向导交叉表查询向导”第第1步对步对话框。话框。 （3）在对话框中选中）在对话框中选中“视图视图”中的【查询】中的【查询】单选按钮，从列表中指定交叉表查询数据源单选按钮，从列表中指定交叉表查询数据源为为“查看订单明细信息查看订单明细信息”查询。单击【下一查询。单击【下一步】按钮，弹

27、出如图步】按钮，弹出如图3.36所示的所示的“交叉表查交叉表查 询向导询向导”第第2步对话框，确定交叉表查询中的步对话框，确定交叉表查询中的行标题字段。行标题字段。 （4）将行标题）将行标题“销售部门销售部门”字段添加到字段添加到“选选定字段定字段”列表中，单击【下一步】按钮，弹列表中，单击【下一步】按钮，弹出如图出如图3.37所示的所示的“交叉表查询向导交叉表查询向导”第第3步步对话框，确定交叉表查询中的列标题字段。对话框，确定交叉表查询中的列标题字段。（5）将）将“订购日期订购日期”字段作为列标题，单字段作为列标题，单击【下一步】按钮，弹出如图击【下一步】按钮，弹出如图3.38所示的所示的

28、“交叉表查询向导交叉表查询向导”第第4步对话框，确定步对话框，确定按多大的日期间隔划分按多大的日期间隔划分“日期日期/时间时间”列列信息。信息。 （6）选择按）选择按“季度季度”划分，单击【下一划分，单击【下一步】按钮，弹出如图步】按钮，弹出如图3.39所示的所示的“交叉表交叉表查询向导查询向导”第第5步对话框，确定行和列交步对话框，确定行和列交叉点的值字段及函数。叉点的值字段及函数。 （7）选择）选择“销售金额销售金额”字段作为交叉表字段作为交叉表中的值，并设置中的值，并设置“函数函数”为为“求和求和”。 （8）单击【下一步】按钮，弹出）单击【下一步】按钮，弹出“交叉交叉表查询向导表查询向导

29、”第第6步对话框，指定查询名步对话框，指定查询名称，这里输入称，这里输入“汇总统计各部门每月的汇总统计各部门每月的销售金额销售金额”。 （9）选择）选择“修改设计修改设计”单选按钮，单击单选按钮，单击【完成】按钮，显示如图【完成】按钮，显示如图3.40所示的查所示的查 询设计器。询设计器。 （10）将列标题的字段修改为）将列标题的字段修改为Format(订订购日期购日期,m) & 月月，如图，如图3.41所示。所示。（11）运行查询，显示如图）运行查询，显示如图3.42所示的查所示的查询结果。询结果。（12）保存查询，关闭查询窗口。）保存查询，关闭查询窗口。 除了选择查询、参数查询、操作查询及

30、除了选择查询、参数查询、操作查询及交叉表查询外，交叉表查询外，Access还支持用还支持用SQL（结构（结构化查询语言）语句进行查询，从而使查询更化查询语言）语句进行查询，从而使查询更方便、快捷。方便、快捷。 在查询中使用在查询中使用SQL语句可以完成查语句可以完成查询向导或设计视图难以完成的查询，例询向导或设计视图难以完成的查询，例如，传递查询、数据定义查询等。如，传递查询、数据定义查询等。 这里，我们使用这里，我们使用SQL的的Select语句，查语句，查询询“订单订单”表中未付款的订单。表中未付款的订单。 （1）在数据库窗口中选择）在数据库窗口中选择“查询查询”对象，对象，双击双击“在设

31、计视图中创建查询在设计视图中创建查询”，打开查，打开查询设计器，不添加查询源，直接关闭询设计器，不添加查询源，直接关闭“显显示表示表”对话框。对话框。 （2）选择【查询】）选择【查询】【SQL特定查询】特定查询】【联合】命令，指定创建【联合】命令，指定创建SQL查询。查询。查询设计器标题栏显示的查询类型从选查询设计器标题栏显示的查询类型从选择查询变为了联合查询，如图择查询变为了联合查询，如图3.43所示。所示。 （3）在窗口编辑器中输入如图）在窗口编辑器中输入如图3.44所示的语所示的语句。句。 （4）运行查询，得到如图）运行查询，得到如图3.45所示的查询结所示的查询结果。果。 （5）以）以

32、“查询查询订单订单表中未付款的订单表中未付款的订单”为名保存查询，关闭查询窗口。为名保存查询，关闭查询窗口。 在在SQL查询中，当进行复合条件查询时，查询中，当进行复合条件查询时，查询条件有多个，条件表达式之间可以通过查询条件有多个，条件表达式之间可以通过And和和Or来连接。来连接。 这里我们要查询这里我们要查询A部部2011年年5月份的销售记月份的销售记录，涉及的条件有两个，分别为销售部门录，涉及的条件有两个，分别为销售部门=A部部、订购日期、订购日期Between 2011-5-1 And 2011-5-31。 （1）在数据库窗口选择）在数据库窗口选择“查询查询”对象，对象，双击双击“在

33、设计视图中创建查询在设计视图中创建查询”，打开，打开查询设计器，不添加查询源，直接关闭查询设计器，不添加查询源，直接关闭“显示表显示表”对话框。对话框。 （2）选择【查询】）选择【查询】【SQL特定查询】特定查询】【联合】命令，指定创建【联合】命令，指定创建SQL查询。查询。查询设计器标题栏显示的查询类型从选查询设计器标题栏显示的查询类型从选择查询变为了联合查询。择查询变为了联合查询。 （3）在窗口编辑器中输入如图）在窗口编辑器中输入如图3.46所示的语所示的语句。句。 （4）运行查询，得到如图）运行查询，得到如图3.47所示的查询结所示的查询结果。果。 （5）以）以“查询查询A部部2011年年5月份销售记录月份销售记录”为为名保存查询，关闭