数据库技术与应用-基于Access第3章查询设计

1、数据库技术与应用基于Access第3章 查询设计学习目标知识目标：了解查询的基本概念和作用；了解查询的基本类型；理解查询的本质；理解表达式的组成和作用。技能目标：掌握各类查询的创建方法；能根据需要创建和使用合法表达式。能力目标：能根据系统需要设计和创建符合要求的各类查询 。 引例：“罗斯文”数据库中的“各种产品的季度订单”查询 “各种产品的季度订单”查询根据用户输入的年度，将产品、订单、订单明细表中的数据组织在一起，并在此基础上执行计算，最后再以行和列交叉的方式显示出来。 如图3-1所示。了解查询的基本概念查询是数据库中最常用的操作，它在很大程度上影响着工作效率。它可对数据库中的数据进行筛选、

2、排序、组合和各类操作。同时也可作为Access中其它对象的数据源。如：查阅多个表中的数据；对不同表中的相关字段值进行计算；将多个数据表中的值进行转置以行列的形式显示，以便于查看；对一个或多个表中的批量数据进行删除、追加、更新等操作；根据客户自行键入的条件值来查看数据信息等。 查询的概念 查询是Access数据库对象之一。利用查询，用户可以按照不同的方式查看，更改和分析数据，也可把查询作为窗体、报表和数据访问页等对象的记录源。查询的数据可来源于一个表或查询，也可来源于多个有关联的表或查询。它将这些数据组织在一起，执行各种计算，并以特定次序和方式排列显示出来。还有的查询可对表中的数据执行删除、更新

3、、追加、生成新表等操作。查询与动态数据集查询有三种视图状态：数据表视图、设计视图和SQL视图。视图之间的切换可通过单击工具栏上的“视图”旁边的箭头，在下拉式列表中进行选择。也可通过单击文件菜单，选择视图选项。在数据表视图中显示的查询结果称为动态数据集。数据透视表只是表中数据的一个映射，真正的数据仍存放在表中。 查询的类型根据查询的功能，查询的类型分为选择查询、动作查询和SQL查询三种。选择查询：最常见的查询类型。它从一个或多个的表中检索并提取符合筛选条件的数据，在可以更新记录（带有一些限制条件）的数据表中显示结果。它还可对记录进行分组计算，然后按照一定的次序和方式显示出来。 动作查询：仅在一个

4、操作中更改许多记录的查询。SQL查询：用户使用 SQL 语句创建的查询。SQL 查询的特殊示例有：联合查询、传递查询、数据定义查询和子查询。 选择查询共有三种类型：简单选择查询、参数查询和交叉表查询。 简单选择查询：通过使用用户指定的准则从表中检索数据，然后按照需要的次序显示数据。同时它还可以对对记录进行分组，并且对记录作总计、计数、平均值以及其他类型的总和的计算。参数查询：运行时可自动处理条件的改变的查询。它在每次执行时都会显示自己的对话框以提示用户输入信息。 交叉表查询：显示来源于表中某个字段的总结值（合计、计算以及平均），并将它们分组，一组列在数据表的左侧，一组列在数据表的上部。 动作查

5、询共有四种类型：删除、更新、追加和生成表。追加查询：该查询可从一个或多个表将一组记录追加到一个或多个表的尾部。 更新查询：该查询可对一个或多个表中的一组记录作全局的更改。删除查询：该查询可从一个表或多个表中删除一组记录。 生成表查询：该查询可从一个或多个表中的全部或部分数据新建表。 建立简单选择查询创建简单查询最常用两种方法：一种是利用简单查询向导创建，另一种方法就是利用设计视图自主创建。在Microsoft Access中，为了创建其它类型查询，往往会先建立一个选择查询，在逐步按要求进行修改，直至达到任务目的。 使用查询向导建立查询过程通过“简单选择查询向导”创建查询，可以在一个或多个表或查

6、询中指定的字段检索数据。如果需要，向导也可以对记录组或全部记录进行总计、计数以及平均值的计算，并且可以计算字段中的最小值或最大值。【例3-1】使用查询向导创建“各产品销售数量”查询，如图3-2所示。 在设计视图中建立查询在设计视图中，用户可为查询字段进行分组、设置条件、并执行各种计算等。在创建查询的过程中，一般可以先用查询向导建立一个初步查询，然后进入查询设计视图对其进行修改，最终设计出符合要求的查询。 【例3-2】创建“订单查询”，如图3-8所示。 分析：从动态数据集可看出，该查询提取了所有批发订单的相关信息。其中包括订单表中的所有字段、以及客户表中的公司名称、地址、城市和邮政编码字段。是否

7、为批发订单还是零售订单主要根据客户ID来判断，从客户表中可看出，零售客户的ID号为AAAAA，故所有“AAAAA”的客户ID即为批发客户。设计视图设计视图分为上下两部分（如图3-10），上部为表/查询显示窗口，显示查询的数据源字段列表。下部为设计网格，显示了查询所使用到的具体的字段及其设置。条件表达式示例：当检索其中值介于 、=、= 或 之间指定值的记录时，可通过使用 Between.And 运算符或比较运算符（、=）来识别范围。 若用查询检索不包含Null值的记录时，则输入“Not Null”或“Is not Null”表达式。相反的，若用查询来检索不包含值的记录时，则输入“Is Null”

8、表达式。Null指字段中没有数据或未知的值。 若使用文本值作为筛选条件，Access会自动在两边用双引号括起来。 条件表达式示例：若对是/否数据类型设置筛选条件，可直接输入逻辑值。 若在筛选条件中包含日期数据类型，则日期常量用#来定义。 可以对相同的字段或不同的字段输入附加的筛选条件。对于不同字段之间的表达式，Microsoft Access 将使用 And 运算符，表示将返回匹配所有单元格中条件的记录。如图3-6所示。 取得货主在天津且发货日期为2003年1月份的订单图3-6 多筛选条件示例1 条件表达式示例：如果表达式是在同一字段的不同行中，Microsoft Access 将使用 Or

9、运算符，表示匹配任何一个单元格中条件的记录都将返回，如图3-7所示。图3-7 多筛选条件示例2 设置查询属性：查询设计视图，可利用查询属性对话框设计更复杂的查询。单击“视图”菜单中的“属性”选项，可弹出如图3-11所示的查询属性对话框。在其中用户可对查询的各项属性进行设置。 图3-11 查询属性对话框 创建参数查询参数查询是这样一种查询，它在执行时显示自己的对话框以提示用户输入信息，然后根据用户在提示框中输入的参数信息为条件，检索要插入到字段中的记录或值并显示出来。它可作为一般的条件参数查询，也可作为后续窗体或报表基础的参数查询。参数查询的创建步骤1）创建一个简单选择查询或交叉表查询，选取查询

10、所需的数据源表/查询，并将要在数据集中显示的字段和要作为参数使用的字段拖入设计网格。2）在要作为参数使用的每一个字段下的“条件：”或“或：”单元格中，输入包含有用方括号括起来的提示文本。在查询运行时，Microsoft Access将显示该提示文本。提示文本可以是自定义的短语，也可以包含字段名，但是必须与字段名不同。若要在文本中引用窗体或报表的控件名称，则应将格式设为“Forms!窗体名称!控件名称”。 调整参数显示顺序：在执行多参数查询时，系统会按照设计网格的顺序，从左到右显示参数提示。但也可以通过单击“查询”菜单中的“参数”命令，在参数提示对话框中调整参数提示的顺序。如图3-11所示。图3

11、-11 “查询参数”对话框 【例3-3】创建“各城市雇员销售额明细”查询运行结果如图3-12至图3-14所示。图3-12 起始日期参数值对话框 图3-13 终止日期参数值输入框 图3-14 2005年期间各城市雇员销售额明细 表达式所谓表达式，就是用运算符将常量、标识符、函数等连接起来的式子。一般说来，表达式是一个确定的值。 1）常量 不会改变的数值或字符串值。如数字、字符串、日期、系统定义常量等。 2）标识符 在表达式中，可以通过标识符来表示一个对象或一个对象的属性。如字段、控件或属性值。其中用方括号 ( ) 包围字段、控件或属性，表明此元素是表、查询、窗体、报表、字段或控件的名称。标识符的

12、示例（1）引用控件值 键入包含控件的窗体或报表的标识符，后面紧接 ! 运算符和控件的名称。例如，引用“订单”窗体上“订单ID”控件值的格式为：Forms!订单!订单ID。（2）引用属性值 键入属性所应用的窗体、报表或控件的标识符，后面紧接着 .（点）运算符和属性名称。例如，引用“订单”窗体上“订购日期”控件的“默认值”属性的格式为： Forms!订单!订购日期.DefaultValue标识符的示例（3）引用字段值键入字段所在的表或查询，后面紧跟！运算符和字段的名称。例如在表达式中引用“订单明细”表中“数量”字段值的格式为： 订单明细!数量。函数就是一个小程序，它根据参数来进行一定的计算、比较、

13、或判断，然后返回一个值。Microsoft Access 包含许多内置的函数。 Sum（）、Avg（）等函数：返回字段值集合的总和或平均值。Count（）：计算查询中的记录数，包含 Null（空值）。CCur 函数：用于统一地取整货币值。DatePart函数：抽取一部分已有日期值。 Year函数：返回日期字段的年度值。Format函数：用于以某种格式显示值。3）函数4）运算符在Microsoft Access中，可包含的运算符有：数学运算符：加“+”、减“”、乘“*”、除“/” 关系运算符：=、=(!)、=（!）、!=、字符串运算符： &逻辑运算符： or、and其它运算符： Between

14、and 、In 、Is Null 、Like等 表达式的创建表达式可直接输入，也可通过“显示比例”和“表达式生成器”对话框来输入。表达式生成器有三个节，如图3-17所示。上方是一个用于创建和显示表达式的表达式框。中间放置了常用的一些运算符按钮。下方三个框用于创建表达式的元素。 图3-17 “表达式生成器”对话框 在查询中使用计算字段计算字段是在查询中定义的一个字段。它显示一个表达式的结果而不是存储的数据。每当表达式中的某个值改变时，该表达式都重新计算。示例如图3-18所示。图3-18 计算字段示例 使用计算字段计算的表达式示例 在计算字段中处理文本值的示例 表达式说明姓名: 姓 &名在“姓名”

15、中显示“姓”字段和“名”字段的值。地址2: 城市 & & 地区 & & 邮政编码在“地址2”字段中显示“城市”、“地区”和“邮政编码”字段的值，用空格分隔。计算字段中执行算术计算的示例 表达式说明新价格：单价*1.1在“新价格”字段中显示外加10个百分点的价格。金额：单价*库存量在“金额”字段中显示“单价”与“库存量”相乘的数值。产品销售额: 订单明细.单价*数量*(1-折扣)/100)*100在“产品销售额”字段中显示“订单明细”表中的“单价”与“数量”的字段值的乘积减去折扣费后的数值。在计算字段中处理和计算日期的示例 表达式说明Date( )- 30使用 Date函数显示距离当日前 30

16、天的日期。发货季度: Qtr & DatePart(q,发货日期)使用 DatePart函数在“发货季度”字段中显示“Qtr”与发货日期的季度值的连接值。订购年份: Year(订购日期)使用Year函数在“订购年份”字段中显示“订购日期”字段的年度值。年份: Format(发货日期,yyyy) 使用Format函数在“年份”字段中显示“发货日期”字段值所处的年号。计算字段中使用SQL和域合计函数的示例 表达式说明订单数：Count(*)在“订单”字段中使用 Count函数计算查询中的记录数，包含 Null（空白）记录字段。产品销售额: Sum(CCur(订单明细.单价*数量*(1-折扣)/10

17、0)*100)在“产品销售额”字段中显示每张订单的总金额，其中使用Ccur函数对计算求得的每种产品的销售金额进行取整，然后使用Sum函数取得每张订单的金额数值总和。【例题3-4】 在“各城市雇员销售额明细”查询中稍做修改，创建计算字段“销售金额”，执行结果如图3-19所示。 图3-19 2005年期间各城市雇员销售额明细 创建交叉表查询交叉表查询是查询的另一种类型。交叉表查询显示来源于表中某个字段总结值（合计、计算以及平均），并将它们分组，一组列在数据表的左侧，一组列在数据表的上部。创建交叉表查询常用两种方法：交叉表查询向导和设计视图。 使用向导创建交叉表查询【例3-5】创建“各产品库存明细”

18、查询，如图3-22所示 图3-22 “各产品库存明细”查询 使用设计视图创建交叉表 【例3-6】创建“05年各客户订货情况”查询，如图3-28所示。 图3-28 “05年各客户订货情况”查询 使用设计视图创建交叉表查询注意事项（1）可以指定多个字段为行标题，但列标题和值只能分别指定给一个字段。（2）选取为列标题和行标题的字段在总计单元格中都必须保留为默认的“分组”选项。而选取为值的字段则可在总计单元格中选择合适的合计函数类型。（3）只有一个字段可以设置为“值”。（4）查询结果不会显示“总计：”行中选项为“条件”的那些字段，如要显示该字段，则必须再次选取。总计选项介绍单击设计视图“工具栏”上的“

19、合计”按钮，设计视图会显示“总计：”行。合理为各字段选择总计选项可实现对字段的分组计算。“总计：”行中共有十二个选项，分为四类：分组依据（分组）、总计函数（共九个）、表达式、和总计字段记录限制（条件）。【例3-7】创建“各类销售额”查询，如图3-30所示。图3-30 “各类销售额”查询 创建动作查询动作查询是仅在一个操作中更改许多记录的查询。共有四种类型：删除、更新、追加与生成表。创建追加查询 【例3-8】创建“追加跑车类产品”查询，查询结果如图3-32所示。 图3-32 “跑车类产品”表 更新查询对一个或多个表中的一组记录作全局的更改。 删除查询可从一个或多个表中删除一组记录。 【例3-9】

20、创建“删除跑车类零库存产品”查询。 生成表查询从一个或多个表中的全部或部分数据新建表。 使用SQL创建查询Access查询对象的实质是一条SQL语句。 查询设计视图和向导其实就是Access提供地、可以自动生成SQL语句的可视化工具。而打开查询的操作也就是运行相应的SQL语句的过程。必须使用SQL语句才能创建的查询称为SQL 查询。分为四类：联合查询、传递查询、数据定义查询和子查询。 结构化查询语言（SQL）简介SQL（Structure Query Language），称为结构化查询语言，是一种综合的、通用的、功能极强的关系数据库语言，几乎被所有关系数据库系统所支持。它在Access中，就称

21、为Access SQL。Access中，选择查询主要由数据查询语句DQL构成，而操作查询则主要由数据操作语言DML生成。SELECT语句 对于任一种SQL语句，SELECT语句都是最常用的查询语句。SELECT语句的作用是让数据库系统根据客户的要求搜索出客户所需要的信息资料，并且按照用户规定的格式进行整理后返回。SELECT语法格式为： SELECT 目标字段列表达式 as 别名 FROM 表名 INNER JOIN 表名 ON 联接条件 WHERE 条件表达式 GROUP BY HAVING 内部函数表达式 ORDER BY 字段列ASC ，DESC；【例题3-10】使用SQL创建如图3-8

22、所示的“订单查询”。 图3-8 订单查询【例题3-11】使用SQL创建“订单小计”查询，查询结果如图3-41所示。 图3-41 “订单小计”查询 SQL使用示例SQL语句说明（结果）SELECT 名字,姓氏 FROM 雇员 WHERE 姓氏=黄;显示“姓氏”与“名字”字段中姓黄的雇员的值。SELECT 产品ID,产品名称 FROM 产品 WHERE 类别ID=Forms!新产品!类别ID;在“产品”表“产品ID”与“产品名称”字段中，显示“类别ID”与打开的“新产品”窗体中指定的“类别ID”相符的记录。SELECT Avg(扩展价格) AS 平均扩展价格 FROM 扩展订单明细 WHERE 扩

23、展价格1000;计算“扩展价格”字段中大于 1000 的订单的平均扩展价格，并将其显示在“平均扩展价格”字段中。SELECT 类别 ID,Count(产品ID) AS 产品ID 数量 FROM 产品 GROUP BY 类别ID HAVING Count(产品ID)10;在“产品ID 数量”的字段中，显示产品总数超过 10 个的分类的产品总数。分析评价查询的实现总的说来可以通过两种方式进行：一种是利用向导和设计视图这两种数据库工具建立查询对象，另一种是使用结构化查询语言SQL创建。 无论哪种方法创建，其本质都是生成SQL语句的过程。Access允许用户通过在SQL视图中直接输入SQL语句的方法创

24、建和修改查询。 在查询的创建过程中，数据源的合理选取是决定查询结果是否正确的重要因素。数据源之间必须确保各数据源能通过联接线连接起来，同时也要避免选取多余的数据源，导致重复查询。 同时表达式在查询中的使用非常频繁。创建表达式时，要考虑对应字段的数据类型和表达式的语法格式。 因此要想创建优秀的查询，除了熟练掌握各种查询的创建方法外，关键还取决于表的结构、表间关系的设置、对表达式的理解程度。职业工作站任务：在汽车销售系统中创建“各种产品的季度订单”查询，运行结果如图3-41至图3-42所示。要求能根据用户输入的年度查询出年度各季度各产品的销售总额。并将查询结果按照行与列交叉的方式显示出来。图3-4

25、1 查询年度参数输入值 图3-42 05年各产品各季度的销售额 任务分析：该查询从运行结果可看出是涉及参数查询和交叉表查询的综合查询。1）确定参数字段：从参数框中可知参数字段为年份值，该值从表中无法直接获取，但是可以通过Year函数从“订购日期”字段提取年度值。2）确定交叉表字段 ：除车辆名称字段直接来源于产品表外，其他字段的值均只能通过计算获得。其中订购年份和季度字段的值可分别通过Year函数和DatePart函数从“订购日期”字段中提取；销售金额则可通过对各产品销售价、销售数量和折扣进行一定计算得到。 另外，车辆名称字段和订购年份字段均以行的方式来显示字段值，故都为行字段。季度字段值以列标

26、题的方式给出，故为列字段。而显示在数据网格中的销售金额则为值字段。 任务实现：1）创建一个新查询，进入设计视图。从显示表对话框中选择数据源表：“订单”、“订单明细”和“产品”表。2）将“产品”表中的“车名”字段拖入网格。创建计算字段：订购年份、第x季度、产品金额三个计算字段，其表达式分别为：Year(订购日期)、“第” & DatePart(“q”,订购日期,1,0) & “季度”、Sum(CCur(订单明细.销售价*数量*(1-折扣)/100)*100)。3）单击“查询”菜单中的“交叉表查询”命令，将查询转换为交叉表查询。在“车名”字段和“订购年份”字段的“总计：”单元格中选取“分组”，“交

27、叉表：”单元格中选为“行标题”；将“第X季度”字段的“总计：”单元格选为“分组”，“交叉表：”单元格值选为“列标题”；将“产品金额”字段列的“总计”单元格值选为“表达式”，“交叉表：”单元格值选为“值”；图3-43 “各种产品的季度订单”查询设计视图 4）再创建一个计算字段，直接在“字段：”单元格中输入“Year(订购日期)”，并将“总计：”值选为“条件”，“交叉表”值为空，得到如图3-43所示的交叉表设计视图。 本章小结查询是关系型数据库管理系统中最重要精髓之一。利用查询，用户可以按照不同的方式查看，更改和分析数据，也可把查询作为窗体、报表和数据访问页等对象的记录源。根据查询的功能，可以将A

28、ccess查询类型分成三类：选择查询、操作查询、SQL查询。其中选择查询又分为简单选择查询、参数查询和交叉表查询。不同类型的查询生成的动态数据集合也具有不同的特点。创建查询前，首先要明确所要创建的查询的功能，根据功能来确定查询类型，然后要明确查询所提取的字段的来源，弄清除条件是什么，在哪个字段表示条件等具体信息，再根据这些信息来采用合适的创建方法。创建查询有两种方法：使用向导创建和使用设计视图创建。在创建查询的过程中，一般可以先用查询向导建立一个初步查询，然后进入查询设计视图对其进行修改，最终设计出符合要求的查询。表达式是许多 Microsoft Access 操作的基本组成部分，它是用运算符

29、将常量、标识符、函数等连接起来的式子。在查询中可使用表达式来设置筛选条件、创建计算字段和设置更新目标值。执行的计算不同，则输入表达式的位置也不一样。如果要设置查询筛选条件，则在“条件：”单元格中输入；如果要创建计算字段，则在“字段：”单元格中输入；如果要根据表达式的结果更新记录，则在“更新到：”单元格中输入。创建表达式必须遵循它的组成规则，您可直接输入、也可通过“显示比例”和“表达式生成器”对话框来输入。无论哪种查询，其实质就是一条SQL语句。查询设计视图和向导其实就是Access提供地、可以自动生成SQL语句的可视化工具。而打开查询的操作也就是运行相应的SQL语句的过程。Access还提供了

30、SQL视图方便用户来查看和修改SQL语句，因此用户可以通过在SQL视图中直接编辑SQL语句来创建查询。 主要概念和观念 主要概念查询 查询的类型 动态数据集 参数查询 表达式 交叉表查询 动作查询 查询的实质 SQL 主要观念动态数据集 多表查询 动作查询的创建 查询的实质 简答题 1）简述查询与表的区别。 2）简述查询的类型。 3）简述各总计选项的意义。 4）简述查询的本质。 5）什么是表达式？ 技术应用1)假如要在查询结果中规定仅显示10条记录，该如何设置？2) 在参数查询中，如何修改参数的提示顺序？3) 在创建交叉表的过程中，在“行标题”中设定条件与在“条件”字段设定条件有什么差别4）能

31、否直接删除“产品”表中的记录，为什么？5）当用户需要为查询结果中的字段进行改名显示时，该如何设置？ 技能题 单项操作训练1)以“产品”表和 “类别”表为数据源，创建“产品列表”查询。如图3-44所示。图3-44 产品列表查询 2）以“订单”表为数据源，创建“零售订单查询”，如图3-45所示。图3-45 零售订单查询 3）以“产品”表和“订单明细”表为数据源，创建“扩展订单明细”查询。如图3-46所示。 图3-46 扩展订单明细查询 4）以“类别”、“产品”和“扩展订单明细”为数据源创建“各产品销售额”查询，如图3-47所示。 图3-47 各产品销售额查询 5）以“雇员”、“订单”和“订单明细”表为数据源，使用SQL语句创建“雇员销售额”。如图 3-48所示。图3-48 雇员销售额 综合操作训练1)创建“各种产品的季度订单”查询，运行结果如图3-49至3-50所示。图3-49 查询年度参数输入框 图3-50 “各种产品的季度订单”查询 2）创建“各产品季度销售额”查询，运行结果如图3-51至3-52所示。 图3-52 “各产品季度销售额”查询 图3-51 查询年度值输入框 案例1 查询在实际应用中的作用 某公司每月各部