工作任务6 设计和创建查询

工作任务6设计和创建查询6.1任务描述

随着商店管理系统数据表的建成，为了使用户能够轻松、快捷地从数据库中检索有关商品、订单、客户和供应商的各种信息，在本任务中，我们将创建订单明细查询、客户信息的精确和模糊查询、显示每笔订单的备货期的计算型查询。

通过操作查询来完成更新商品价格、追加急需商品信息、生成紧急订单表、删除备货期为3天的记录等操作。6.2业务咨询6.2.1表达式的书写规则

表达式是MicrosoftAccess运算的基本组成部分。

表达式是算术或逻辑运算符、常数、函数和字段名称、控件以及属性的任意组合，计算结果为单个值。

表达式可执行计算、操作字符或测试数据。

在书写表达式时，要遵循以下的规则。

（1）取值范围（>、<、>=、<=、<>或Between...And），如表2.8所示。

（2）排除不匹配的值（Not），如表2.9

所示。表

达

式结

果>234对于“数量”字段，大于234的数字<1200.45对于“单价”字段，小于1

200.45的数字>="Callahan"对于“姓氏”字段，从Callahan直至字母表结尾的所有姓氏。Between##And#12/1/1999#对于“订货日期”字段，—之间的日期（ANSI-89）Between''And'12/1/1999'对于“订货日期”字段，—之间的日期（ANSI-92）表2.8 取值范围示例表

达

式结

果Not"美国"对于“货主国家/地区”字段，已发货给美国以外国家/地区的订单Not2对于ID字段，其ID不等于2的雇员NotT*对于“姓氏”字段，其姓氏不以字母“T”开头的雇员（ANSI-89）NotT%对于“姓氏”字段，其姓氏不以字母“T”开头的雇员（ANSI-92）表2.9 排除不匹配的值示例（3）列表值（In），如表2.10所示。（4）全部或部分匹配的文本值，如表2.11所示。（5）匹配模式（Like），如表2.12所示。（6）日期值，如表2.13所示。（7）空和零长度字符串，如表2.14所示。表

达

式结

果In（"加拿大","英国"）对于“货主国家/地区”字段，已发货给加拿大或英国的订单In（法国,德国,日本）对于“国家/地区名称”字段，居住在法国或德国或日本的雇员表2.10 列表值示例表

达

式结

果"伦敦"对于“发货城市”字段，已发货给伦敦的订单"伦敦"Or"休斯敦"对于“发货城市”字段，已发货给伦敦或休斯敦的订单>="N"对于“公司名称”字段，已发货给其名称以字母N～Z开头的公司的订单Like"S*"对于“发货名称”字段，已发货给其名称以字母S开头的客户的订单（ANSI-89）Like"S%"对于“发货名称”字段，已发货给其名称以字母S开头的客户的订单（ANSI-92）Right（[订单ID],2）="99"对于“订单ID”字段，其ID值以99结尾的订单Len（[公司名称]）>Val（30）对于“公司名称”字段，其名称超过30个字符的公司的订单表2.11 全部或部分匹配的文本值示例表

达

式结

果Like"S*"对于“发货名称”字段，已发货给其名称以字母S开头的客户的订单（ANSI-89）Like"S%"对于“发货名称”字段，已发货给其名称以字母S开头的客户的订单（ANSI-92）Like"*Imports"对于“发货名称”字段，已发货给其名称以词“Imports”结尾的客户的订单（ANSI-89）Like"%Imports"对于“发货名称”字段，已发货给其名称以词“Imports”结尾的客户的订单（ANSI-92）Like"[A-D]*"对于“发货名称”字段，已发货给其名称以A～D开头的客户的订单（ANSI-89）Like"[A-D]%"对于“发货名称”字段，已发货给其名称以A～D开头的客户的订单（ANSI-92）Like"*ar*"对于“发货名称”字段，已发货给其名称包括字母序列“ar”的客户的订单（ANSI-89）Like"%ar%"对于“发货名称”字段，已发货给其名称包括字母序列“ar”的客户的订单（ANSI-92）Like"MaisonDewe?"对于“发货名称”字段，已发货给客户的订单，其客户名称以“Maison”作为名称的第一部分，并具有5个字母长的第二名称，且其中前4个字母是“Dewe”，而最后的字母为未知的（ANSI-89）Like"MaisonDewe_"对于“发货名称”字段，已发货给客户的订单，其客户名称以“Maison”作为名称的第一部分，并具有5个字母长的第二名称，且其中前4个字母是“Dewe”，而最后的字母为未知的（ANSI-92）表2.12 匹配模式示例表

达

式结

果##对于“发货日期”字段，于发货的订单（ANSI-89）''

对于“发货日期”字段，于发货的订单（ANSI-92）Date（）对于“规定日期”字段，日期为今天的订单BetweenDate（）AndDateAdd（"M",3,Date（））对于“规定日期”字段，规定日期为今天至从今天起三个月之间的订单<Date（）−30对于“订货日期”字段，已超过30天的订单Year（[订货日期]）

=1999对于“订货日期”字段，订货日期在1999年内的订单DatePart（"q",[订货日期]）

=4对于“订货日期”字段，日期为第四季度的订单DateSerial（Year（[订货日期]）,Month（[订货日期]+1,1）−1对于“订货日期”字段，日期为每月最后一天的订单Year（[订货日期]）=Year（Now（））

AndMonth（[订货日期]）

=Month（Now（））对于“订货日期”字段，日期为当年当月的订单表2.13 日期值示例表

达

式结

果IsNull对于“发货地区”字段，客户的“发货地区”字段为Null。Null表示可以在字段中输入或用于表达式和查询，以标明丢失或未知的数据。在VisualBasic中，Null关键字表示Null值。有些字段（如主键字段）不可以包含Null值。（空）的订单IsNotNull对于“发货地区”字段，客户的“发货地区”字段包含值的订单""对于“传真”字段，显示没有传真机的客户的订单，用“传真”字段中的零长度字符串

（零长度字符串是指不含字符的字符串。可以使用零长度字符串来表明你知道该字段没有值。输入零长度字符串的方法是键入两个彼此之间没有空格的双引号）值，而不是Null（空）值来表明。表2.14 空和零长度字符串示例6.2.2表达式生成器

表达式生成器由表达式框、运算符按钮和表达式元素三部分组成，如图2.34所示。图2.34表达式生成器1．表达式框

生成器的上方是一个表达式框，用户可在其中创建表达式。

使用生成器的下方区域可以创建表达式的元素，然后将这些元素粘贴到表达式框中以形成表达式。

也可以直接在表达式框中键入表达式的组分部分。2．运算符按钮

常用运算符的按钮位于生成器的中部。

如果单击某个运算符按钮，表达式生成器将在表达式框中的插入点位置插入相应的运算符。

选择左下方列表框中的运算符文件夹和中部列表框中相应的运算符类别，可以得到表达式中所能使用的运算符的完整列表。

右侧的列表框中列出的是所选类别中的所有运算符。3．表达式元素

生成器下方有3个列表框。

左侧的列表框包含文件夹，该文件夹列出了表、查询、窗体及报表等数据库对象，以及内置和用户定义的函数、运算符以及常用表达式。

中间的列表框列出了左侧列表框中选定的文件夹内特定的元素或特定的元素类别。

例如，如果在左边的框中选择“内置函数”，那么中间的列表框中将列出MicrosoftAccess函数的类别。

右侧的列表框列出了在左侧和中间列表框中选定的元素的值。

例如，如果在左侧的列表框中选择“内置函数”，并在中间列表框中选定一种函数类别，那么右侧的列表框中将列出选定的类别中所有的内置函数。6.3任务实施6.3.1查询订单明细

在“订单”表中，为了便于输入数据和减少数据库的冗余度，我们将其中的商品信息和客户信息均采用编号形式进行输入。

在查看订单时，我们可以通过表之间数据的关联性，采用多表查询显示订单明细信息，即增加商品名称、型号规格、单价、客户的公司名称以及地址等信息。

（1）打开“商店管理系统”数据库。

（2）在数据库窗口中选择“查询”对象，双击“在设计视图中创建查询”，打开查询设计器。

（3）在“显示表”对话框中选择“订单”、“商品”和“客户”表作为查询数据源。（4）在查询设计器下方的字段行中添加如图2.35所示的字段。（5）将查询保存为“订单明细查询”，运行查询的结果如图2.36所示。6.3.2查询北京和上海的客户信息

在进行信息查询时，往往会涉及多个条件，即需对多个字段设置条件或在一个字段上设置多个条件，我们可使用And和Or运算符来构造复合条件。

这里，我们要查询北京和上海的客户信息，需在“客户”表的“城市”字段上设置条件“北京”或“上海”。

（1）打开“商店管理系统”数据库。

（2）在数据库窗口中选择“查询”对象，双击“在设计视图中创建查询”，打开查询设计器。

（3）在“显示表”对话框中选择“客户”表作为查询数据源。

（4）在查询设计器下方的字段行中添加“客户”表中的所有字段。这里，我们可直接将“客户”字段列表中的“*”拖曳到下方的“字段”行中。

（5）双击“城市”字段，将其添加到下方的设计区中。取消勾选其“显示”复选框，并在“条件”行中输入“北京”，在“或”行中输入“上海”，如图2.37所示。

（6）将查询保存为“北京和上海的客户信息”，运行查询的结果如图2.38所示。图2.38“北京和上海的客户信息”查询结果6.3.3查询客户地址含有“路”的客户信息

在进行信息查询时，除了能够按照条件进行精确查询外，Access也提供了模糊条件查询，即用Like运算符来构造条件表达式。

这里，我们将查询客户地址中含有“路”的客户信息。

（1）利用查询设计器新建查询。

（2）设置“客户”表作为查询数据源。

（3）将“客户”表中的所有字段添加到设计区中。

（4）在“地址”字段下方设置条件：Like"*路*"，如图2.39所示。图2.39设置客户地址条件

（5）将查询保存为“客户地址中含有‘路’的客户信息”，运行查询的结果如图2.40所示。图2.40“客户地址中含有‘路’的客户信息”查询结果6.3.4查询显示每笔订单的备货期

前面创建的查询仅仅是从数据源中获取符合条件的记录，并没有对符合条件的记录进行更深入的分析和计算。

在实际应用中，常常需要对查询的结果进行计算。

例如，在“订单”表中，我们需要查看每笔订单的备货期，以便及时提供所需商品。

备货期则可通过发货日期和订购日期计算得出。

（1）利用查询设计器新建查询，并将“订单”表作为数据源。

（2）双击“订单”表中的“*”，将表的全部字段加入到下方的设计区中。

（3）构造计算型字段“备货期”。在设计区后面的“字段”列中写入字段名称“备货期:[发货日期]-[订购日期]”，如图2.41所示。

（4）将查询保存为“显示每笔订单的备货期”，运行查询的结果如图2.42所示。6.3.5将“商品”表中“笔记本电脑”类的商品价格下调5%

在创建和维护数据库的过程中，常常需要对表中的记录进行更新和修改。

如果用户通过数据表视图来更新表中记录，那么当更新的记录很多或更新的记录符合一定条件时，简单有效的方法是利用Access提供的更新查询。

这里，我们只需要查询所有类别为“笔记本电脑”的商品，然后将其单价更改为“单价*（1-0.05）”即可。

（1）打开查询设计器，将“商品”表和“类

别”表添加到查询设计器中作为数据源。

（2）将“商品”表的“单价”字段和“类别”表的“类别名称”字段添加到查询设计器的

设计区中。（3）选择【查询】→【更新查询】命令，指定创建更新查询。查询设计器标题栏上显示的查询类型由“选择查询”变为了“更新查询”。

（4）在“单价”字段的“更新到”网格内输入如图2.43所示的利用原来的“单价”字段的内容计算新单价的公式，即[单价]*(1-0.05)。

（5）在“类别名称”字段的“条件”网格内输入“笔记本电脑”。

（6）将查询保存为“笔记本电脑单价下调5%”。

（7）运行查询，弹出如图2.44所示的提示对话框，单击【是】按钮，执行更新操作。

（8）从数据库窗口中选择“表”对象，打开“商品”表，可以看见查询结果，每种笔记本电脑的“单价”都下调了5%，如图2.45所示。6.3.6将数量低于5件的商品追加到“急需商品信息”表中

在进行数据库维护时，常常需要将某个表中符合一定条件的记录添加到另外一个表中。Access提供的追加查询能够很容易地实现一组记录的添加。

例如，在工作任务5中，我们通过

复制“商品”表的结构创建了“急需商

品信息”表，但未在“急需商品信息”

表中输入记录，即该表为空表。

这里，我们将通过追加查询，将“商品”表中数量低于5件的商品信息添加到“急需商品信息”表中。

（1）打开查询设计器，添加“商品”表到查询设计器中作为数据源。

（2）将“商品”表的所有字段依次添加到查询设计器的设计区中。

（3）选择【查询】→【追加查询】命令，弹出

如图2.46所示的“追加”对话框。在“表名称”下拉列表中选择“急需商品信息”表，默认数据库为“当前数据库”。

（4）单击【确定】按钮，返回查询设计器。查询设计器标题栏上显示的查询类型由“选择查询”变为了“追加查询”。在“数量”字段下方设置“条件”为“<=5”，如图2.47所示。

（5）将查询保存为“追加数量低于5件

的商品信息”。

（6）运行查询，弹出如图2.48所示的提示对话框，单击【是】按钮，执行追加操作。

（7）从数据库窗口中选择“表”对象，打开“急需商品信息”表，即可看见查询结果。该表中的所有商品的数量均小于等于5，如图2.49所示。6.3.7将“订单”表中备货期低于3日的订单生成新表“紧急订单”

在Access中，从表中访问数据要比从查询中访问数据快得多。

如果经常要从几个表中提取数据，那么最好的方法是使用Access提供的生成表查询，从多个表中提取数据，然后组合起来生成一个新表并永久保存。

生成表查询是利用已有的数据创建一个新表，实际上就是将查询出的动态集合以表的形式保存。

通常，可以将复杂的查询结果保存为一个临时表，这样可以提高工作效率。

这里，为了提高商品销售和物流环节的工作效率，及时处理紧急订单，我们可将“订单”表中备货期低于3日的订单信息生成新表。（1）打开查询设计器，添加“订单”表到查询设计器中作为数据源。（2）将“订单”表的所有字段依次添加到查询设计器的设计区中。

（3）选择【查询】→【生成表查询】命令，弹出如图2.50所示的“生成表”对话框。在

“表名称”下拉列表中选择“紧急订单”，

默认数据库为“当前数据库”。

（4）单击【确定】按钮，返回查询设计器。查询设计器标题栏上显示的查询类型由“选择查询”变为了“生成表查询”。在“发货日期”字段下方设置“条件”为“<=[订购日期]+3”，如图2.51所示。（5）将查询保存为“生成备货期低于3日的紧急订单表”。（6）运行查询，弹出如图2.52所示的提示对话框，单击【是】按钮，执行生成表操作。

（7）在数据库窗口中选择“表”对象，即可看见新生成的“紧急订单”表。打开该表，即可看见查询结果，如图2.53

所示。6.3.8删除“紧急订单”表中备货期为3天的订单

在数据库的维护过程中，经常需要对表中的一些过时或无用的数据进行删除。

尽管用户可以比较容易地从数据表中删除某条记录，但如果要删除满足某些条件的一组记录时，就可以使用Access提供

的删除查询，利用该查询可以一次删除一组同类的记录。

这里，我们将通过删除查询，将生成的“紧急订单”表中备货期为3天的记录删除。（1）打开查询设计器，添加“紧