第3章-access查询

第3章查询3.1查询的基本概念3.23.33.4创建特殊用途查询创建计算查询创建选择查询3.6结构化查询语言3.5创建操作查询3.1查询的基本概念在Access中，要从一个表或多个表中检索信息，就要创建查询。查询就是向数据库提出询问，并要求数据库按给定的条件、范围以及方式等，从指定的数据源中查找，提取指定的字段和记录，返回一个新的数据集合。可以使用查询作为窗体、报表或数据访问页的数据源。3.1.1查询简介1、查询的设计方法在Access中创建查询的方法主要有两种：向导及设计视图。查询向导能够有效地指导用户进行创建查询的工作，对于创建过程中的选项设置都有详细解释，并能以图形的方式显示结果。对创建查询来说，设计视图功能更为丰富，查询视图分为上下两部分，上部分显示的是查询的数据源及其字段列表，下半部分显示并设置查询中字段的属性。在查询设计视图中，可以完成新建查询的设计，或修改已有的查询，也可以修改作为窗体、报表或数据访问页数据源的SQL语句。在查询设计视图中所做的更改，也会反映到相应的SOL语句。2、查询的功能查询能够实现以下几个主要功能。（1）选择字段和记录根据给定的条件，查找并显示相应的记录，可以仅显示部分字段。（2）修改记录通过查询，可以对符合条件的记录进行添加、修改和删除等操作。（3）统计与计算在查询结果中进行统计。例如，统计学生的平均年龄、男女学生的人数等；还可以建立计算字段，用以保存计算的结果。（4）建立新表利用生成表查询或者SQL查询，可以建立一个新的数据表。（5）为窗体、报表或数据访问页提供数据为了从一个或多个表中选择合适的数据显示在窗体或报表中，可以先建立一个查询，然后将该查询的结果作为数据源。每次输出报表或打开窗体时，该査询就从它的基表中检索出符合条件的最新记录。3、查询的类型根据对数据源的操作方式以及查询结果，Access中的查询可以分为选择查询、交叉表查询、参数查询、操作查询和SQL查询这5种类型。这5类查询的应用目标不同，对数据源的操作方式以及操作结果也不同。3.1.2查询准则在实际应用中，并非只是简单的査询，往往需要指定一定的条件。例如，査找1992年参加工作的男教师，这种带条件的査询需要通过设置査询条件来实现。查询的准则就是在设计查询的过程中所定义的查询条件。查询条件是运算符、常量、函数以及字段名和属性等的组合，能够计算出一个结果。大多数情况下，查询准则就是一个关系表达式。1、运算符表达式中常用的运算符包括算术运算符、比较运算符、连接运算符、逻辑运算符和特殊运算符等。一个表达式可以包含多个运算符，每一个运算符都有其执行的先后顺序。与Excel中的运算符优先级一样，Access中也有运算符的优先级。运算符的使用根据实际需要而变化，如比较运算符不仅仅用于数字间的对比，查找不及格的学生可表示为“成绩60”，查找1989年以后出生的学生可以表示为“出生日期=#1989-1-1#”等，查找在1990年出生的条件表达式为“出生日期Between#1990-1-1#And#1990-12-31#”；Like运算符中可使用通配符查找指定模式的字符串，查找姓“李”的学生可表示为Like李*。2、函数Access2010提供了十分丰富的函数，灵活运用这些函数，不仅可以简化许多运算，而且能够加强和完善Access2010的许多功能。3、查询条件示例查询条件是一个表达式，Access将它与查询字段值进行比较，以确定是否包括含有每个值的记录。查询条件可以是精确查询，也可以利用通配符进行模糊查询。查询视图主要用于设计、修改查询或按不同方式查看查询结果，在Access中提供了3种常用视图，分别是数据表视图、设计视图和SQL视图。除这3种视图外，还有数据透视表视图和数据透视图视图，下面主要介绍前3种视图。3.1.3查询视图1、数据表视图查询的数据表视图是以行和列的格式显示查询结果的窗口，如图所示。在这个视图中，可以进行编辑字段、添加和删除数据、查找数据等操作，而且也可以对查询进行排序、筛选等，也可以进行行高、列宽及单元格风格设置，来调整视图的显示风格。具体的操作方法和数据表操作相同。查询的数据表视图是一个查询结果完成后的显示方式。2、设计视图查询的设计视图是用来设计查询的窗口，是查询设计器的图形化表示，利用它可以完成多种结构复杂、功能完善的查询。查询设计视图由上、下两个窗口构成，即表/查询显示窗口和查询设计网格（也称QBE网格）窗口，如图所示。1）表/查询显示窗口表/查询显示窗口显示的是当前查询所包含的数据源（表和查询）以及表间关系。在该窗口中可以添加或删除表，可以建立表间关系。2）查询设计网格窗口查询设计网格窗口用于设计显示查询字段、查询准则等。其中每一行都包含查询字段的相关信息，列是查询的字段列表。在查询的设计视图下，Access2010还提供了查询属性设置，可以方便地控制查询的运行。要设置查询属性，可以在表/查询窗口内右击，在弹出的快捷菜单中选择“属性”命令，或直接单击数据库工具栏中的“属性”按钮，即可弹出“查询属性”对话框，如图所示。常用的查询属性设置主要包括下列几项：输出所有字段：该属性用来控制查询中字段的输出，只有当用户设计的查询用于窗体并希望查询中表的所有字段也适用于窗体时才可以设置为“是”，没有特别要求时应保留默认的“否”。上限值：当用户希望查询返回“第一个”或“上限”记录时，可以使用该选项。唯一的记录：使用该选项可以达到消除查询中重复行的目的。运行权限：当在网络上与其他用户一起共享时，可以使用该选项来设置用户查看数据和修改数据的权限。记录锁定：对于网络中共享的查询来说，可以使用该选项来控制查询编辑的整体层次。3、SQL视图查询的SQL视图用来显示或编辑打开查询的SQL视图窗口，如图所示。要正确使用SQL视图，必须熟练掌握SQL命令的语法和使用方法，在后面章节中将详细介绍。3.2创建选择查询选择查询是按照一定的准则从一个或多个表中获取数据，并按照所需的次序进行排列显示。选择查询是最简单的一种查询，其他查询是在选择查询的基础上扩展的。3.2.1使用向导创建选择查询使用向导创建选择查询，可以从一个或多个表和查询中选择要显示的字段。如果查询中的字段来自多个表，这些表应该建立关系。【例3-1】P77,【例3-2】P773.2.2使用设计视图创建查询1、设置查询的条件创建选择查询的关键在于查询条件的设置。査询条件是运算符、常量、字段值、函数以及字段名和属性等的任意组合，用于查找符合条件的结果。查询条件在创建带条件的査询时经常用到。查询设计视图中的准则就是查询记录应符合的条件，它与设计表时设置字段的有效性规则的方法相似。在设置查询条件时，可参考前面介绍的查询准则。在查询设计视图中添加选择准则时，首先要考虑为哪个字段添加“准则”，其次考虑要为这个字段添加什么样的“准则”。例如，要在“选课成绩表”中筛选出成绩在80分和90分之间的学生，那么如何在查询中添加准则呢？首先通过“显示表”对话框添加“选课成绩表”到查询窗口中，然后选取这个表中的“成绩”字段作为查询中的一个字段，如果不想让这个字段中的值显示在数据表中，就取消此字段的可见性，将它的“显示”属性设为“否”。添加完这个字段就可以添加条件规则了，在“成绩”字段的“规则”属性中添加“Between80And90”即可，如图所示。为了提高效率，Access还提供了一个名为表达式生成器的工具。在这个工具中提供了数据库中所有表或查询中的字段名称，窗体、报表中的各种控件，以及很多函数、常量、操作符和通用表达式。将它们进行合理搭配，就可以完成任意一种表达式，十分方便。在查询的规则行中右击，在弹出的快捷菜单中选择“生成器”命令，或者单击工具栏中的“生成器”按钮，即可打开表达式生成器，如图所示。2、使用设计视图创建单表查询在数据库窗口中选择“对象”栏中的“查询”选项，然后在右侧窗格中双击“使用设计器创建查询”，在弹出的“显示表”对话框中，用户可以选择一个或多个表作为查询的数据来源，然后直接双击表名称或单击“添加”按钮将所选的表添加到设计视图的窗口中。添加完成后，单击“关闭”按钮。选择多表时，各表之间要先建立好关系。3、使用设计视图创建多表查询P81【例3-3】P82【例3-4】3.3创建计算查询如果系统提供的查询只能完成一些简单的数据检索，将无法满足需要。因为用户对数据表中的数据记录进行查询时，往往需要在原始数据之上进行某些计算才能得到有实际意义的信息。例如，对于销售额的一个简单统计来获得有关销售情况的信息，对产品的价格进行折算等，这些都需要在查询中用到计算。在实际应用中，常常需要对记录或字段进行汇总统计。Access查询提供了利用函数建立总计查询等方式，总计查询可以对查询中的某列进行总和（Sum）、平均（Avg）、计数（Count）、最小值（Min）和最大值（Max）等计算。3.3.1了解查询计算在查询字段中显示的计算结果不存储在基准的窗体中。Access2010在每次执行查询时都将重新进行计算，以使计算结果永远都以数据库中最新的数据为准。在Access的查询中可以执行许多类型的计算。例如，可以计算一个字段值的总和或平均值，一个字段的值再乘上另两个字段的值，或者计算从当前日期算起一个月后的日期。在Access的查询中，可以执行下列计算以生成新的数据结果。1.预定义计算预定义计算即“总计”计算，是系统提供的用于对查询中的记录组或全部记录进行的计算，它包括总和、平均值、数量、最小值、最大值、标准偏差和方差这几种计算方法。自定义计算可以用一个或多个字段的数据进行数值、日期和文本计算。例如，使用自定义计算，将某一字段值乘以某一数量，可以找出存储在不同字段的两个日期间的差别，可以组合文本字段中的几个值，或者创建子查询。使用设计网格“总计”行的选项就可以对记录组执行计算，并对计算字段计算出总和、平均值、数量或其他类型的总和。对于自定义计算，必须直接在设计网格中创建新的计算字段。创建计算字段的方法是，将表达式输入查询设计网格中的空“字段”单元格。表达式可以由多个计算组成，如Sum([库存量]+[订购量]+[再订购量])。也可以指定计算字段的准则，以影响计算的结果。2.自定义计算3.3.2创建总计查询1、总计项在查询设计视图的“总计”行的列表中包含12个选项，这12个选项可以分为4类：分组（GroupBy）、合计函数（Aggregate）、表达式（Expression）以及限制条件（Where）。分组的作用是把普通记录分组，以便Access执行合计计算。合计函数的作用是对一个字段进行指定的数学计算或选择的操作。表达式的作用是把几个汇总运算分组并执行该组的汇总。限制条件的作用是对某个字段执行总计时在计算以前对记录进行限制。2、所有记录的汇总计算在使用“总计”计算功能时，可以对所有的记录或记录组中的记录进行计算。正面通过实例演示如何计算所有记录的某个字段的总和、平均值、数量或其他汇总。P85【例3-5】，P86【例3-6】，P87【例3-7】3、创建自定义计算在Access2010查询中，除了在下拉式列表框中选择一个总计选项外，还可以创建自己的总计表达式。可以在一个表达式中使用几种类型的总计，如使用取平均值（Avg）和求和（Sum）或多项求和等；也可以根据由若干函数组成的计算字段建立表达式，基于来自不同表中的几个字段的计算字段建立表达式。P88【例3-8】3.3.3添加计算字段计算字段是指根据一个或多个表中的一个或多个字段，使用表达式建立的新字段。在前面的例子中，统计函数字段为“学号之计数”，其可读性较差，可以通过创建计算字段来调整该字段的显示效果。另外，有时需要统计的数据在表中没有相应的字段，或者用于计算的数据值来源于多个字段，这时就需要创建计算字段。1、添加计算字段2、建立班级平均成绩查询3、建立每个学生平均成绩查询4、建立高于班平均成绩查询P89【例3-9】P89【例3-10】P91【