1、工作任务6 设计和创建查询,6.1 任务描述现在商店管理系统中相关的一些数据表已建成。为了使用户能够轻松、快捷地从数据库中检索有关商品、订单、客户和供应商的各种信息，本任务将创建订单明细查询、客户信息的精确和模糊查询、显示每笔订单的备货期的计算型查询。,通过操作查询来完成更新商品价格、追加急需商品信息、生成紧急订单表、删除备货期为3天的记录等操作。,6.2 业务咨询,6.2.1 表达式的书写规则表达式是Microsoft Access运算的基本组成部分。表达式是算术或逻辑运算符、常数、函数和字段名称、控件以及属性的任意组合，计算结果为单个值。表达式可执行计算、操作字符或测试数据。,在书写表达式
2、时，要遵循以下的规则： （1）取值范围操作符（、=、或Between.And），如表2.8所示。（2）排除不匹配的值（Not），如表2.9 所示。,表2.8 取值范围示例,表2.9 排除不匹配的值示例,（3）列表值（In），如表2.10所示。 （4）全部或部分匹配的文本值，如表2.11所示。 （5）匹配模式（Like），如表2.12所示。 （6）日期值，如表2.13所示。 （7）空和零长度字符串，如表2.14所示。,表2.10 列表值示例,表2.11 全部或部分匹配的文本值示例,表2.12 匹配模式示例,表2.13 日期值示例,表2.14 空和零长度字符串示例,6.2.2 表达式生成器,表达式
3、生成器由表达式框、运算符按钮和表达式元素三部分组成，如图2.34所示。选中一个表，打开它的“设计视图”；选择一个数字型字段，展开“有效性规则”属性右边的按钮，如下图所示。,图2.34 表达式生成器,1表达式框,表达式框:用户可在其中创建表达式。生成器的下方区域:可以创建表达式的元素，然后将这些元素粘贴到表达式框中以形成表达式。也可以直接在表达式框中键入表达式。,2运算符按钮,单击某个运算符按钮，表达式生成器将在表达式框中插入相应的运算符。,选择左下方列表框中的运算符文件夹和中部列表框中相应的运算符类别，可以得到表达式中所能使用的运算符的完整列表。右侧的列表框中列出的是所选类别中的所有运算符。,
4、3表达式元素,生成器下方有3个列表框。左侧的列表框:包含文件夹，该文件夹列出了表、查询、窗体及报表等数据库对象，以及内置和用户定义的函数、运算符以及常用表达式。,中间的列表框:列出了左侧列表框中选定的文件夹内特定的元素或特定的元素类别。例如，如果在左边的框中选择“内置函数”，那么中间的列表框中将列出Microsoft Access函数的类别。,右侧的列表框:列出了在左侧和中间列表框中选定的元素的值。例如，如果在左侧的列表框中选择“内置函数”，并在中间列表框中选定一种函数类别，那么右侧的列表框中将列出选定的类别中所有的内置函数。,6.3 任务实施,6.3.1 查询订单明细在“订单”表中，为了便于
5、输入数据和减少数据库的冗余度，我们将其中的商品信息和客户信息均采用编号形式进行输入。,在查看订单时，我们可以通过表之间数据的关联性，采用多表查询显示订单明细信息，即增加商品名称、型号规格、单价、客户的公司名称以及地址等信息。,（1）打开“商店管理系统”数据库。（2）在数据库窗口中选择“查询”对象，双击“在设计视图中创建查询”，打开查询设计器。（3）在“显示表”对话框中选择“订单”、“商品”和“客户”表作为查询数据源。,（4）在查询设计器下方的字段行中添加如图2.35所示的字段。 （5）将查询保存为“订单明细查询”，运行查询的结果如图2.36所示。,6.3.2 查询北京和上海的客户信息,进行信息
6、查询时，经常会涉及多个条件，即需要对多个字段设置条件或在一个字段上设置多个条件，我们可使用And和Or运算符来构造复合条件。,例如：要查询北京和上海的客户信息，需在“客户”表的“城市”字段上设置条件“北京”或“上海”。（1）打开“商店管理系统”数据库。 （2）选中“客户”表作为查询数据源。 （3）单击【创建】菜单中的【查询设计】项。 （4）在弹出“显示表”对话框中选“客户”表。如图所示。,（5）在查询设计器下方的字段行中添加“客户”表中的所有字段。即直接将“客户”字段列表中的“*”拖曳到下方的“字段”行中。,（6）双击“城市”字段，将其添加到下方的设计区中。取消勾选其“显示”复选框，并在“条件
7、”行中输入“北京”，在“或”行中输入“上海”，如图2.37所示。,图2.38 “北京和上海的客户信息”查询结果,（7）将查询保存为“北京和上海的客户信息”，运行查询的结果如图2.38所示。,6.3.3 查询客户地址含有“路”的客户信息,在进行信息查询时，除了能够按照条件进行精确查询外，Access也提供了模糊条件查询，即用Like运算符来构造条件表达式。这里，我们将查询客户地址中含有“路”的客户信息。（1）利用查询设计器新建查询。,（2）设置“客户”表作为查询数据源。（3）将“客户”表中的所有字段添加到设计区中。（4）在“地址”字段下方设置条件：Like “*路*“，如图2.39所示。,（5）
8、将查询保存为“客户地址中含有路的客户信息”，运行查询的结果如图2.40所示。,图2.40 “客户地址中含有路的客户信息”查询结果,6.3.4 查询显示每笔订单的备货期,前面创建的查询仅仅是从数据源中获取符合条件的记录，并没有对符合条件的记录进行更深入的分析和计算。在实际应用中，常常需要对查询的结果进行计算。,例如，在“订单”表中，我们需要查看每笔订单的备货期，以便及时提供所需商品。备货期则可通过发货日期和订购日期计算得出。,（1）利用查询设计器新建查询，并将“订单”表作为数据源。（2）双击“订单”表中的“*”，将表的全部字段加入到下方的设计区中。,（3）构造计算型字段“备货期”。在设计区后面的
9、“字段”列中写入字段名称“备货期: 发货日期-订购日期”，如图2.41所示。（4）将查询保存为“显示每笔订单的备货期”，运行查询的结果如图2.42所示。,6.3.5 将“商品”表中“笔记本电脑”类的商品价格下调5%,在创建和维护数据库的过程中，常常需要对表中的记录进行更新和修改。如果用户通过数据表视图来更新表中记录，那么当更新的记录很多或更新的记录符合一定条件时，简单有效的方法是利用Access提供的更新查询。,（1）打开查询设计器，将“商品”表和“类别”表添加到查询设计器中作为数据源。（2）将“商品”表的“单价”字段和“类别”表的“类别名称”字段添加到查询设计器的设计区中。,（3）选择【查询
10、工具】【更新】命令，指定创建更新查询。,（4）在“单价”字段的“更新到”网格内输入如图2.43所示的利用原来的“单价”字段的内容计算新单价的公式，即单价*(1-0.05)。,（5）在“类别名称”字段的“条件”网格内输入“笔记本电脑”。（6）将查询保存为“笔记本电脑单价下调5%”。,（7）运行查询，弹出如图2.44所示的提示对话框，单击【是】按钮，执行更新操作。（8）从数据库窗口中选择“表”对象，打开“商品”表，看到每种笔记本电脑的“单价”都下调了5%，如图2.45所示。,6.3.6 将数量低于5件的商品追加到“急需商品信息”表中,在进行数据库维护时，常常需要将某个表中符合一定条件的记录添加到另
11、外一个表中。Access提供的追加查询能够很容易地实现一组记录的添加。,例如，在工作任务5中，我们通过 复制“商品”表的结构创建了“急需商品信息”表，但未在“急需商品信息”表中输入记录，即该表为空表。这里，我们将通过追加查询，将“商品”表中数量低于5件的商品信息添加到“急需商品信息”表中。,（1）打开查询设计器，添加“商品”表到查询设计器中作为数据源。（2）将“商品”表的所有字段依次添加到查询设计器的设计区中。,（3）选择【查询工具】【追加查询】命令，弹出 如图2.46所示的“追加”对话框。在“表名称”下拉列表中选择“急需商品信息”表，默认数据库为“当前数据库”。,（4）单击【确定】按钮，返回
12、查询设计器。查询设计器标题栏上显示的查询类型由“选择查询”变为了“追加查询”。在“数量”字段下方设置“条件”为“=5”，如图2.47所示。,（5）将查询保存为“追加数量低于5件的商品信息”。（6）运行查询，弹出如图2.48所示的提示对话框，单击【是】按钮，执行追加操作。,（7）从数据库窗口中选择“表”对象，打开“急需商品信息”表，即可看见查询结果。该表中的所有商品的数量均小于等于5，如图2.49所示。,6.3.7 将“订单”表中备货期低于3日的订单生成新表“紧急订单”,在Access中，从表中访问数据要比从查询中访问数据快得多。如果经常要从几个表中提取数据，那么最好的方法是使用Access提供
13、的生成表查询，从多个表中提取数据，然后组合起来生成一个新表并永久保存。,生成表查询是利用已有的数据创建一个新表，实际上就是将查询出的动态集合以表的形式保存。通常，可以将复杂的查询结果保存为一个临时表，这样可以提高工作效率。,现在，为了提高商品销售和物流环节的工作效率，及时处理紧急订单，我们可将“订单”表中备货期低于3日的订单信息生成新表。,（1）打开查询设计器，添加“订单”表到查询设计器中作为数据源。 （2）将“订单”表的所有字段依次添加到查询设计器的设计区中。,（3）选择【查询工具】【生成表】命令，弹出如图2.50所示的“生成表”对话框。在“表名称”下拉列表中选择“紧急订单”， 默认数据库为
14、“当前数据库”。,（4）单击【确定】按钮，返回查询设计器。查询设计器标题栏上显示的查询类型由“选择查询”变为了“生成表查询”。在“发货日期”字段下方设置“条件”为“=订购日期+3”，如图2.51所示。,（5）将查询保存为“生成备货期低于3日的紧急订单表”。 （6）运行查询，弹出如图2.52所示的提示对话框，单击【是】按钮，执行生成表操作。,（7）在数据库窗口中选择“表”对象，即可看见新生成的“紧急订单”表。打开该表，即可看见查询结果，如图2.53 所示。,6.3.8 删除“紧急订单”表中备货期为3天的订单,在数据库的维护过程中，经常需要对表中的一些过时或无用的数据进行删除。尽管用户可以比较容易
15、地从数据表中删除某条记录，但如果要删除满足某些条件的一组记录时，就可以使用Access提供的删除查询，利用该查询可以一次删除一组同类的记录。,这里，我们将通过删除查询，将生成的“紧急订单”表中备货期为3天的记录删除。 （1）打开查询设计器，添加“紧急订单”表到查询设计器中作为数据源。 （2）将作为控制条件的“发货日期”字段添加到查询设计器的设计区中。,（3）选择【查询工具】【删除】命令，指定创建删除查询。查询设计器标题栏上显示的查询类型由“选择查询”变为了“删除查询”，如图2.54所示。在“发货日期”字段下方设置删除条件“订购日期+3”。,（4）将查询保存为“删除紧急订单表中备货期为3天的订单
16、”。（5）运行查询，弹出如图2.55所示的提示对话框，单击【是】按钮，执行删除操作。,（6）从数据库窗口中选择“表”对象，打开“紧急订单”表即可看见查询结果，表中备货期为3天的订单已被删除，如图2.56所示。,6.4 任务拓展,6.4.1 查询指定价格区间内的商品信息 在查询设计器中打开“商品”表作为数据源，并选中所有字段加入到设计器的下方。 在“单价”下方的 条件中输入“Between最低单价 and 最高单价”，如图所示。 将查询保存为“查询指定价格区间的商品信息”，运行查询。 输入价格在200-500之间的商品信息。结果如图所示。,6.4.2 查看B部6月份的销售额 （1）利用查询设计器
17、，将“订单”表和“商品”表作为数据源； （2）将“订单”表的所有字段放到下方设计区中。 （3）构造计算型字段“销售额”。鼠标右键单击字段行中的空白网格，在弹出的菜单中选“生成器”命令，弹出“表达式生成器”对话框，如图所示。 （4）构造如图所示的表达式。然后单击【确定】按钮。,（5）在“订购日期”字段下方设置查询条件“Month(订购日期)=6”，在“销售部门”字段下方设置查询条件“B部”，如图所示。 （6）保存查询为“查看B部6月份的销售额”，并运行查询。结果如下图所示。,6.4.3查询供应商地址中不含“路”的记录,（1）单击【创建】-【查询设计】，在查询设计器窗口中将“供应商”表作为数据源；
18、 （2）将“供应商”表的全部字段放入到下方的设计区。 （3）在“地址”字段的下方设置条件为“not like *路*”。 （4）将查询保存为“查询供应商地址不含“路”的信息” （5）运行查询，结果如图所示。,6.5 任务检测,（1）打开商店管理系统，选择“查询”对象，查看数据库窗口中的查询是否如图2.64所示，包含11个查询。,（2）分别运行其中的7个选择查询，查看查询运行的结果是否如图2.36、图2.38、图2.40、图2.42、图2.59、图2.62和图2.63所示。,（3）选择“表”对象，查看商品数据是否已更新、急需商品信息数据是否已追加、是否生成了“紧急订单”表、“紧急订单”表中是否包含备货期为3天的订单。,6.6 任务总结,本任务通过创建“订单明细查询”、“北京和上海的客户信息”、“客户地址中含有路的信息”等查询，介绍了多表查询、多条件复合查询、模糊条件查询以及计算型查询的设计和创建方法。,通过更新查询、追加查询、生成表查询和删除查询实现更新商品价格、追加急需商品信息、生成紧急订单表、删除备货期为3天的记录。此外，在任务拓展中还进一步介绍了多参数查询、使用表达式生成器构建表达式等操作。,
