Access查询命令幻灯片

1、第9章 查询,1,9.1.1查询的功能,利用查询可以实现如下功能： （1）选择字段：指定要在查询结果中出现的字段。 （2）选择记录：指定条件来限制查询结果中所要显示的记录。 （3）编辑记录：对记录添加、修改、删除。 （4）实现计算：对查询结果中的记录进行各种统计计算。另外，还可以建立计算字段。 （5）建立新表：将查询结果生成一个新的表对象。 （6）建立基于查询的报表和窗体：在查询结果的基础上建立窗体和报表。,2,9.1.2查询的类型,Access提供了多种查询方式，包括: (1)选择查询 (2)参数查询 (3)交叉表查询 (4)操作查询 (5)SQL查询,3,9.1.3查询中使用的“条件”,查

2、询中使用的“条件”也称“准则”，通常由常量、表达式、函数组成。,4,1常量,（1）数字型常量 由数字、小数点及正负号构成的常数。 （2）文本型常量 也称字符串或文本值，是用英文半角双引号括起来的字母、数字、汉字及部分符号。 （3）日期/时间型常量 用“#”号括起来的日期或日期时间数据，如：#1980-1-1#和#2008-8-8 8:08:08#。在“条件”行中，Access将自动提供“#”。 （4）是/否型常量 包括True、False、Yes、No等，当其不被定义为文本型常量时，Access默认其为是/否型常量。 （5）空值 空值是一个特殊的常量，用Null表示，以表明丢失或未知的数据。请

3、注意，主键字段不可以包含Null值。,5,2用于表达式的运算符,运算符有算术运算符、关系运算符、逻辑运算符和特殊运算符等。可以使用多种运算符来构造查询的表达式。,6,（1）算术运算符,表9-1算术运算符及其含义,7,（2）关系运算符,表9-2关系运算符及其含义,8,（3）逻辑运算符,表9-3逻辑运算符及其含义,9,（4）特殊运算符,表9-4特殊运算符及其含义,10,3“条件”中使用的函数 P200,表9-6字符函数及其功能-P200,11,3“条件”中使用的函数,12,13,表9-8统计函数及其功能,14,9.2 选择查询,选择查询将从一个或多个表（或查询）中检索指定的字段数据，分为 无条件查

4、询 有条件查询 创建查询有两种方法：使用向导创建和使用“设计”视图创建。 无条件的选择查询是不加任何条件限制而直接进行的查询。可对一个表或多个表进行查询。,15,9.2.1创建无条件的选择查询-例9.1,1单表查询 例9.1查询并显示“学生信息”表中的“学号”、“姓名”、“所属院系”、“党员否”4个字段的内容。,16,例9.2,2多表查询 例9.2 依据“学生信息”表、“课程信息”表和“成绩信息”表进行多表查询。查询并显示“学号”、“姓名”、“课程名”、“成绩”4个字段的内容，并按姓名升序排序。（书上图有错）,17,表9-10“设计网格”中行的作用,18,在查询操作中，还将用到一些专门用于查询

5、的工具按钮，这些按钮为建立和使用查询提供了方便。表9-11列出了这些按钮的基本功能。,19,9.2.2 创建有条件的选择查询-例9.3,进行有条件的选择查询需进入“查询”设计视图，在其“条件”行中给出条件进行查询。可对一个表或多个表进行查询。 例9.3 依据“学生”表创建一个查询，查找成绩大于等于85分的学生情况，显示学生的“学号”、“姓名”、“课程名称”、“成绩”4个字段的内容。此查询命名为“例9_3”。,20,21,例9.4,例9.4 使用“设计”视图依据“教师信息”表和“课程信息”表创建一个查询，查找1980年（含1980年）以后出生的教师讲授的课程情况，显示“教师编号”、“姓名”、“课

6、程名”3个字段的内容，此查询命名为“例9_4”。 在创建查询时，在准则中字段名必须用方括号“ ”括起来，而且数据类型应与对应字段定义的类型相符，否则将出错。 例如：Year(出生日期),22,例9.5,例9.5查询“学生信息管理”数据库中成绩大于等于90分的女生或成绩小于60分的男生信息，显示“姓名”、“性别”和“成绩”，此查询命名为“例9_5”。,23,例9.6,例9.6 依据“学生信息”表创建一个查询，查找所属院系为“东语学院”、“西语学院”和“法学院”的学生信息，只显示学生的“学号”、“姓名”、“所属院系”3个字段的内容，并按“所属院系”降序排序，此查询命名为“例9_6”。,24,例9.

7、7,例9.7 查询“学生信息”表中所有爱好摄影的学生名单，显示“姓名”、“所属院系”、“简历”3个字段的内容，此查询命名为“例9_7”。,25,9.2.3计算查询,在实际应用中，常常要对查询结果进行计算，如：求和、求平均值、计数、求最大值、最小值等。本节将介绍如何在建立查询的同时实现计算。 实现计算查询方法有两种：查询向导和设计视图。,26,例9.8,1利用查询向导实现计算查询 例9.8 依据“学生信息”表和“成绩信息”表创建查询，计算每位学生的总成绩。只显示“学号”、“姓名”、“总成绩”3列数据，并按“总成绩”降序排序，其中“总成绩”是通过计算生成的，此查询命名为“例9_8”。,27,图9-

8、11采用“汇总”查询 图9-12汇总选项,图9-13计算字段的命名,28,例9.9,例9.9 依据“成绩信息”表和“课程信息”表创建查询，计算各科平均成绩。只显示“课程名”、“平均成绩”两列数据，并按“平均成绩”降序排序，其中“平均成绩”通过计算生成的，“平均成绩”保留小数位数为2位，此查询命名为“例9_9”,29,例9.10,2利用“设计”视图实现计算查询 （难）例9.10 依据“学生信息”表创建查询， 统计1992年出生的人数，此查询命名为“例9_10”，查询结果如图所示。 字段“学号”“总计”行选择“计数” 字段“出生日期”的“总计”行选择“条件”,30,例9.11 (连接： 各子句的作

9、用如下： ALL 选择所有符合条件的记录。 DISTINCT忽略在选定字段中包含重复数据的记录。 *选择表中所有的字段。如：SELECT * FROM 学生 可以是字段、常数或系统内部函数，使用“，”将每项分开。 FROM 说明要选择的数据来自与哪个或哪些表。 WHERE指定查询条件。 GROUP BY指定分组字段。 HAVING对分组的组内指定条件。 ORDER BY指定排序字段。 ASC指定升序排序。 DESC指定降序排序。,62,例9.25-SQL语句查询功能,使用SELECT语句查找学生信息管理数据库中90分以上（含90分）的学生成绩，并显示“学号”、“姓名”、“课程名”和“成绩”，并

10、按学号升序显示。 在“学生信息管理”数据库中，选择“查询”对象，双击“在设计视图中创建查询”选项，关闭“显示表”对话框，选择菜单项“视图|SQL视图”输入： SELECT 学生信息.学号, 学生信息.姓名, 课程信息.课程名, 成绩信息.成绩 FROM 学生信息, 课程信息, 成绩信息 WHERE 学生信息.学号=成绩信息.学号 AND 课程信息.课程ID=成绩信息.课程ID AND 成绩信息.成绩=90 ORDER BY 学生信息.学号; 单击“运行”按钮可显示查询结果。,63,2SQL语言的定义功能-CREATE语句,（1）CREATE语句 CREATE语句用于创建基本表、索引。其中，定义

11、表的一般格式为： CREATE TABLE ( , )； 例9.26 创建“学生”表，表中包括学号、姓名、性别、出生日期、简历字段。 在查询的SQL视图中输入： CREATE TABLE 学生(学号 INT Primary Key,姓名 CHAR(4),性别 CHAR(1),出生日期 DATE,简历 MEMO )； 单击“运行”按钮可创建“学生”表。,64,2SQL语言的定义功能-ALTER语句,（2）ALTER语句 ALTER TABLE语句用于基本表结构的修改，其一般格式为： ALTER TABLE ADD DROP ALTER ； 例9.27 在上例生成的“学生”表中增加一个字段，字段名

12、为“电话号码”，数据类型为“文本”；将“简历”字段删除；将“学号”字段数据类型为“文本”，长度改为为“10”。 添加新字段在查询的SQL视图中输入： ALTER TABLE 学生 ADD 电话号码 CHAR(8);单击“运行”按钮。 删除字段在查询的SQL视图中输入：ALTER TABLE 学生 DROP 简历; 单击“运行”。 修改字段在查询的SQL视图中输入： ALTER TABLE 学生 ALTER 学号 CHAR(10);单击“运行”。 注意：使用ALTER语句时不能一次添加或删除多个字段。,65,2SQL语言的定义功能-DROP语句,（3）DROP语句 当某个基本表、索引不再需要时，

13、可以使用DROP对其进行删除，其一般格式为： DROP TABLE ； DROP INDEX ； 例9.28 删除“学生”表。 在查询的SQL视图中输入：DROP TABLE 学生; 单击“运行”按钮。,66,3SQL语言的数据操作功能-INSERT语句,（1）INSERT 语句 INSERT语句用于将一条新记录插入指定表中，其一般格式为： INSERT INTO (,) VALUES （,）； 例9.29 添加一条记录到“学生信息”表中。 在查询的SQL视图中输入：INSERT INTO学生信息(学号,姓名) VALUES（01010101,张三）;，单击“运行”按钮。,67,3SQL语言的

14、数据操作功能-UPDATE语句,（2）UPDATE 语句 UPDATE语句用于将指定表所有记录或满足条件的记录进行更新操作，其一般格式为： UPDATE SET = ,) WHERE 例9.30 对“学生信息”表中的姓名为“张三”的记录修改，学号改为“02020202”，性别为“男”。 在查询的SQL视图中输入：UPDATE 学生信息 SET 学号=02020202，性别=男“ WHERE 姓名=张三; 单击“运行”按钮。,68,3SQL语言的数据操作功能-DELETE语句,（3）DELETE 语句 DELETE语句用于将指定表中所有记录或满足条件的记录删除。其一般格式为： DELETE FR

15、OM WHERE) 例9.31 删除“学生信息”表中的姓名为“张三”的记录。 查询SQL视图中输入：DELETE FROM 学生信息 WHERE姓名=张三; ，单击“运行”。,69,9.6.2使用SQL修改查询-例9.32,在实际应用中，大多数使用SQL语句，是直接在SQL视图中修改已创建的查询。 例9.32 使用查询“设计”视图查找教师讲授的课程情况，显示“姓名”、“课程名”2个字段的内容，此查询命名为“例9_32”。使用SQL修改查询条件，查找出1980年以后出生的所在院系为“数学”的教师讲授的课程情况。 操作步骤如下： （1）在“学生信息管理”数据库中，选择“查询”对象，双击“在设计视图

16、中创建查询” 在“显示表”对话框中依次双击“教师信息”、“课程信息”表关闭“显示表”对话框双击“姓名”及“课程名”字段将字段添加到设计网格区保存此查询命名为“例9_32”。 （2）选择菜单项“视图|SQL视图”，将显示如图9-34所示“SQL视图”窗口。,图9-34“SQL视图”窗口,70,例9.32-2,（3）在该窗口中分号前输入WHERE (Year(教师信息.出生日期)=1980)AND 教师信息.所在院系=数学，如图9-35所示，单击保存按钮单击“运行”显示查询结果如图9-36所示。,图9-35 “例9_32”的SQL修改结果,图9-36 “例9_32”的查询结果,71,9.6.3创建

17、SQL查询,SQL查询主要包括联合查询、子查询、数据定义查询和传递查询等4种。这里只介绍联合查询和子查询。 1创建联合查询 联合查询是将两个或更多个表或查询中的字段合并到查询结果的一个字段中。使用联合查询可以合并两个表中的数据。,72,9.6.3创建SQL查询-例9.33,例9.33 显示“学生信息”表中所有东语学院的记录和“学生成绩明细”表中非俄语学院的记录，如图9-37所示。此查询命名为“例9_33”。 操作步骤如下： （1）在“学生信息管理”数据库中，选择“查询”对象，双击“在设计视图中创建查询”，关闭“显示表”对话框。 （2）执行菜单项“查询SQL特定查询联合”，显示“联合查询”窗口，

18、在窗口中输入SQL语句，如图9-38所示。 （3）单击“运行”显示结果如图9-37所示。保存此查询，命名为“例9_33”。 说明： （1）如果将“联合查询”转换为其它查询，则输入的SQL语句将全部丢失。 （2）如果需要返回重复记录，应在Union后输入ALL。 （3）每个Select 语句都必须以同一顺序返回相同数量的字段，对应字段除了可将数字和文本字段作为对应字段外，其余对应字段都具有兼容的数据类型。,图9-38“联合查询”窗口,图9-37“例9_33”的查询结果,73,9.6.3创建SQL查询,2创建子查询 在Access中，可以将一个查询（即子查询）的结果作为另一个查询的条件，子查询是由

19、SQL SELECT语句组成。可以在查询设计网格的“条件”行的单元格中输入SELECT语句，并将子查询语句括在圆括号中。,74,9.6.3创建SQL查询-例9.34,例9.34 查询成绩比平均成绩高出10分的学生信息，只显示“学号”、“姓名”和“成绩”3个字段的内容，并按成绩升序排序，查询结果如图9-39所示。此查询命名为“例9_34”。 操作步骤如下： 在“学生信息管理”数据库中，选择“查询”对象，双击“在设计视图中创建查询” 添加“学生信息”和“成绩信息”表在“字段”行单元格内选择“学号”、“姓名”和“成绩”按图9-40所示设置“条件”行单击“运行”显示结果如图9-39所示。,图9-39“

20、例9_34”的查询结果,图9-40 “例9_34”的查询“设计”视图,75,9.7 编辑修改查询,在前面介绍查询实例时，已多次用到在“查询”设计视图中修改已创建的查询。本节将系统介绍相关的操作，具体操作包括：字段的添加、删除、移动或更改；表的添加与移出。,76,9.7.1编辑查询中的字段,1添加字段 2移动字段 3删除字段,77,9.7.2编辑查询中的数据源,在“查询”设计视图窗口的“字段列表”中，列出了可以添加到“设计网格”上的所有字段。但是，如果在列出的所有字段中，没有所要选择的字段，就需要将该字段所属的表或查询添加到“字段列表”中；反之，如果“字段列表”中列出的表或查询未被使用，可以将其

21、删除。 1添加表或查询 2删除表或查询,78,作业,P201开始 例9.1例9.24 将教师所给数据库在未打开状态下重命名为“学号姓名学生成绩管理”数据库（例如：20101111王武学生信息管理.mdb ）后（或打开后用 主菜单“文件”“备份数据库”），在其中进行操作。 提交作业数据库（例如： 20101111王武学生信息管理.mdb）。 查询保存注意： 如例9.1命名为9-1，。,79,9.2.1创建无条件的选择查询-例9.1,1单表查询 例9.1查询并显示“学生信息”表中的“学号”、“姓名”、“所属院系”、“党员否”4个字段的内容。,80,例9.2,2多表查询 例9.2 依据“学生信息”表

22、、“课程信息”表和“成绩信息”表进行多表查询。查询并显示“学号”、“姓名”、“课程名”、“成绩”4个字段的内容，并按姓名升序排序。（书上图有错）,81,9.2.2 创建有条件的选择查询-例9.3,进行有条件的选择查询需进入“查询”设计视图，在其“条件”行中给出条件进行查询。可对一个表或多个表进行查询。 例9.3 依据“学生”表创建一个查询，查找成绩大于等于85分的学生情况，显示学生的“学号”、“姓名”、“课程名称”、“成绩”4个字段的内容。此查询命名为“例9_3”。,82,例9.4,例9.4 使用“设计”视图依据“教师信息”表和“课程信息”表创建一个查询，查找1980年（含1980年）以后出生

23、的教师讲授的课程情况，显示“教师编号”、“姓名”、“课程名”3个字段的内容，此查询命名为“例9_4”。 在创建查询时，在准则中字段名必须用方括号“ ”括起来，而且数据类型应与对应字段定义的类型相符，否则将出错。 例如：Year(出生日期),83,例9.5,例9.5查询“学生信息管理”数据库中成绩大于等于90分的女生或成绩小于60分的男生信息，显示“姓名”、“性别”和“成绩”，此查询命名为“例9_5”。,84,例9.6,例9.6 依据“学生信息”表创建一个查询，查找所属院系为“东语学院”、“西语学院”和“法学院”的学生信息，只显示学生的“学号”、“姓名”、“所属院系”3个字段的内容，并按“所属院

24、系”降序排序，此查询命名为“例9_6”。,85,例9.7,例9.7 查询“学生信息”表中所有爱好摄影的学生名单，显示“姓名”、“所属院系”、“简历”3个字段的内容，此查询命名为“例9_7”。,86,例9.8,1利用查询向导实现计算查询 例9.8 依据“学生信息”表和“成绩信息”表创建查询，计算每位学生的总成绩。只显示“学号”、“姓名”、“总成绩”3列数据，并按“总成绩”降序排序，其中“总成绩”是通过计算生成的，此查询命名为“例9_8”。,87,例9.9,例9.9 依据“成绩信息”表和“课程信息”表创建查询，计算各科平均成绩。只显示“课程名”、“平均成绩”两列数据，并按“平均成绩”降序排序，其中

25、“平均成绩”通过计算生成的，“平均成绩”保留小数位数为2位，此查询命名为“例9_9”,88,例9.10,2利用“设计”视图实现计算查询 （难）例9.10 依据“学生信息”表创建查询， 统计1992年出生的人数，此查询命名为“例9_10”，查询结果如图所示。 字段“学号”“总计”行选择“计数” 字段“出生日期”的“总计”行选择“条件”,89,例9.11,例9.11 依据“教师信息”表创建一查询，查找教师的“教师编号”、“姓名”和“联系电话”3个字段的内容，要求将 “教师编号”与“姓名”两个字段合二为一，这样查询的3个字段的内容以两列形式显示，标题分别为“编号姓名”和“联系电话”，所建查询命名为“

26、例9_11”，查询结果如图9.19所示。,90,例9.12,（难） 例9.12 依据“成绩信息”表和“课程信息”表创建一个查询，计算并输出每门课程最高成绩与最低成绩的差值，显示标题为“最高成绩与最低成绩之差”（如图9-21所示），查询命名为“例9_12”。 在字段“课程名”总计行选择“分组” 空白单元格中输入表达式：最高成绩与最低成绩之差: Max(成绩)-Min(成绩) 在此字段总计行选择“表达式”,91,9.3.1单参数查询-例9.13,单参数查询是在字段中指定一个参数，在执行参数查询时，用户只需输入一项参数值，系统将以此参数为条件给出查询结果。 例9.13依据“学生信息”表创建一个查询，

27、按输入的“所属院系”值查找并显示该院系学生的“学号”、“所属院系”、“姓名”3个字段的内容，当运行该查询时，应显示参数提示信息：“请输入院系名称：”。所建查询命名为“例9_13”。,92,例9.14,例9.14 依据“学生信息”表创建一个查询，当运行该查询时，应显示参数提示信息“请输入爱好”，当输入爱好内容后，将查找出具有指定爱好的学生名单。查询只显示“学号”、“姓名”、“简历”3个字段的内容，所建查询命名为“例9_14”。,93,9.3.2多参数查询-例9.15,如果有多个条件需要输入，则需建立多参数查询。 例9.15创建一参数查询，当运行该查询时，分别提示参数“请输入成绩下限”和“请输入成

28、绩上限”，当输入数值后，将查找出指定成绩范围内的学生信息。查询只显示“学号”、“姓名”、“课程名”、“成绩”4个字段的内容，所建查询命名为“例9_15”。,94,例9.16,例9.16创建一参数查询，假定字段“学号”的第6至7位是专业编号，按输入的专业编号和课程名进行查询，显示“专业编号”、“姓名”、“课程名”和“成绩”4个字段的内容。当运行该查询时，提示信息分别是：“请输入专业编号”和“请输入课程名”，所建查询命名为“例9_16”。,95,9.4.1创建交叉表查询-例9.17,使用“交叉表查询向导”创建交叉表查询 例9.17 依据“学生信息”表，查询各院系男、女生人数分布情况，查询结构如图9

29、-23所示，将此查询命名为“例9_17”。,96,例9.18,例9.18 依据“教师信息”表，查询各系按“性别”分组的每种职称人数的分布，如图9-27所示。将此查询命名为“例9_18”。,97,9.4.2使用“查询”设计视图创建交叉表查询-例9.19,在“查询”设计视图中，可以通过选择查询类型来创建交叉表查询。 例9.19 在“学生信息管理”数据库中，建立如图9-28所示的交叉表查询，此查询命名为“例9_19”。,98,9.5.1生成表查询-例9.20,生成表查询是将查询的结果生成一个新表。 例9.20在“学生信息管理”数据库中创建生成表查询“例9_20”，生成的新表命名为“学生成绩明细”。 利用双击“在设计视图中创建查询”对上例的“查询例9_19”进行并命名为“例9_20”,99,9.5.2更新查询-例9.21,更新查询用于更改多条记录中的一个或多个字段值，用户可以添加一些条件来筛选要更新的记录，大部分更新查询需要用表达式来规定更新条件。 注意：更