Access数据库2003教材-CH4分析

职教云网页版

Access2003数据库教程

第四章查询

Access2003数据库教程

第一章数据库系统概述

第二章Access 2003数据库系统的基本概念

第三章重要表格操作

第四章查询

第五章制作实用表格

第六章报告

第七章数据访问页面的设计

第八章宏观

第九章模块

第十章查综合例题

第四章查询

第四章查询

4.1查询概述

4.2使用向导创建查询

4.3使用查询设计器创建查询

4.4创建SQL查询

4.5处理查询中的数据

第四章查询

4.1查询概述

查询可以基于一个表、多个表或另一个查询

创建新数据集作为查询的结果集。

查询结果是动态的，其内容取决于数据源

正在变化。

可以使用查询设计器和查询设计向导创建查询。

第四章查询

4.1.1 Access查询按功能分类

1 .选择查询

2 .参数查询

3 .交叉表查询

4 .操作查询

- 删除查询

- 更新查询

- 添加查询

- 生成表查询

• 5.SQL查询

第四章查询

4.1.2 Access查询按创建查询分类

(1)使用查询设计器

)2)使用各种查询向导

- 简单查询向导

(交叉表查询向导

- 重复搜索查询向导

(查找不匹配的查询向导

返回目录

第四章查询

4.2使用向导创建查询

4.2.1使用简单查询向导创建查询

使用简单查询可以在一个或多个表指定字段中进行检查

电缆数据可以合并记录组或所有记录。

求计数、平均值、最大值、最小值的运算。

第四章查询

4.3使用查询设计器创建查询

4.3.1查询设计器及其使用

1 .打开查询设计器。 接口如下所示。

第四章查询

3 .查询设计器工具栏

按钮的功能和说明

视图

每个查询有三个视图。 一个是设计视图。 第二个是“数据表”视图，显示查询

结果集； 第三个是SQL视图，用于在SQL语句中执行查询。 这个按钮在这三个上

切换种子视图

查询类型此按钮用于选择查询、交叉表查询、生成表查询、更新查询以及进行其他查询

在查询和删除查询这六种类型的查询之间切换

执行查询并将查询结果集显示为工作表

显示“表格”弹出菜单“显示表格”对话框，从中可以选择要用于查询的表格和查询。

将总计“总计”行添加到查询设计区域，以用于统计计算，如总计、平均值等。

属性

显示当前光标所在位置的对象的属性。 光标位于查询设计窗口中的

数据表/查询显示区域显示查询的属性。 对于光标位于底部的查询

“设计”区域显示字段的属性。

显示“表达式生成器”对话框，用于生成基础表达式。 只有光才有这个按钮

仅在位于查询设计区域的“准则”栏中时有效。

数据库窗口将返回到数据库窗口。

第四章查询

4.3.3查询条件和条件表达式

1 .条件表达式

)1)简单的条件式

字段表达式的含义说明

性别“男”调查性别为“男”

录像

文本值通常必须用双引号括起来

如果不加双引号，Access将自动变为双重

引号

大学英语83查询大学英语83分

记录等于=83，允许省略等号“=”

出生年月日#84-1-25#查询1984年1月25日出生

用户记录

在条件式中使用日期时，请使用半角

的“#”括起来。 如果没有标上“#”，

Access会自动加上双重“#”。

第四章查询

)2)包含运算符的条件表达式

条件表达式中包含比较运算符

比较运算符所在字段的表达式的含义

=等于性别=“女”调查女性记录

查询大于总分240且大于总分240的记录

查阅不等于大学英语0的大学英语不为0的记录

=大致以上

稍小

=以下简称

4.3.3查询条件和条件表达式

第四章查询

条件表达式中包含字符串运算符

运算符所在字段的表达式含义

Like姓名Like“李*”

Like”李？ “查姓李的学生的记录

In氏名In (“李梅”、“马威”)

“名称”字段的值为“李”

梅”或“马威”记录(

字符串必须是列出的值。)

Between and出生年月日Between #84-1-1# And

#84-12-31#

调查出生年月日1984年内的报道

记录(主要是数字型、货币型和

日期类型字段)

“*”符号表示可以在字符串中的该位置指定任意几个字符。

「

？ “”符号表示可以在字符串中的该位置指定任何字符。

4.3.3查询条件和条件表达式

第四章查询

条件表达式中包含逻辑运算符

运算符所在字段的表达式含义

and(and )数学=60 and 85调查数学成绩在60~84之间的记录

or (或)数学60 or 85数学成绩60以下或85以上的记录

not性别not '女'查询性别不是"女"的记录

4.3.3查询条件和条件表达式

第四章查询

图4.3.5查询的“条件”和“或”行

4.3.3查询条件和条件表达式

不同的行是“或”

第四章查询

4.3.3查询条件和条件表达式

使用对等或逻辑运算符

第四章

• 2．表达式的设置

[选课成绩]![平时成绩]*0.3+[选课成绩]![考试成绩]*0.7表名 字段名运算符常量

4.3.3查询条件和条件表达式第4章 查询

⚫ 表名必须用一对中括号[ ]括住外加一感叹号。例如：[选课成绩]!⚫ 字段名必须用一对中括号[ ]括住。例如：[平时成绩]⚫字符串必须用一对英文半角单引号或双引号括住。例如："女"⚫日期时间应该用半角的“#”号括起来。例如：#84-1-25#(1)Access表达式的有关规则4.3.3查询条件和条件表达式第4章 查询（2）函数

函数 功能 说明Year（日期表达式）

返回值是表示年份的四位整数

例如“工作时间”字段的值是1983年1月25日,

则Year（[工作时间]）的值是1983Date() 返回值是系统当天的日期（含年月日）

例如当前系统日期是2008年11月8日，则Date()的值是2008-11-8Day(Date()) 返回值是系统当天的日期（只是日）例如当前系统日期是2008年11月8日，则Day(Date())的值是8Now() 返回值是系统当天的日期和时间

例如当前系统日期是2008年11月8日，时间是16:05:16，则Now()的值是2008-11-8: 16:05:16表6.4.1 部分常用日期函数4.3.3查询条件和条件表达式第4章 查询（3）运算符运算符 说明

+ 加 例如计算[平时成绩]+ [考试成绩]- 减 例如计算[总工资]-[扣款]* 乘 例如计算[平时成绩]*0.3/ 除 例如计算[总分]/[人数]^ 乘幂 例如计算[订购量]^24.3.3查询条件和条件表达式第4章 查询4.3.4创建参数查询

• 参数查询在运行时会提示用户输入参数值（查询条件），并根据用户的输入给出查询结果，从而可以实现交互式查询。

招生办

第4章 查询

• 例4.3.2 创建一个名为“按照学生姓名查询”的查询，当输入不同的学生姓名时，显示相应学生的记录数据。第4章 查询4.3.5创建生成表查询

• 生成表查询是在表或者查询的基础上创建的，在使用时生成表查询，就能够生成一个新“表”。• 例4.3.3 根据“学生”表，创建一个名为“创建男学生表”的生成表查询，并使用该查询生成一个包含“学生编号”、“姓名”、“性别”和“年龄”的名为“男学生”的表。如下图所示。第4章 查询第4章 查询第4章 查询

4.3.6 创建追加查询

• 如果需要将一个或多个表中所有记录或符合一定条件的记录添加到另一个表的尾部，可以使用追加查询快速完成。在此，将接受添加记录的表称为目的表，将用来添加记录的表称为源表，追加查询只能追加匹配的字段。第4章 查询• 例4.3.4 创建一个名为“添加记录”的追加查询，并使用该查询，将“学生”表中“家庭住址”为“广西来宾”的记录添加到“男学生”的表中。

• 步骤如下 图所示。第4章 查询第4章 查询第4章 查询4.3.7创建更新查询

• 如果需要大批量修改数据，或者是输入有规律的数据，可以借助更新查询来完成。

• 例4.3.5 创建一个名为“年龄加1”的更新查询，并使用该查询，将“学生”表中每个学生的年龄加1。• 步骤如下 图所示。第4章 查询

第4章 查询

4.3.8创建删除查询

• 使用删除查询，可以将数据库中一些无用的数据批量删除。

• 例4.3.6 创建一个名为“删除不及格”的删除查询，并使用该查询，将“选课成绩”表中考试成绩不及格的学生的纪录删除。• 步骤如下 图所示。第4章 查询

第4章 查询

4.3.9创建多表查询

• 创建查询时，如果查询的数据源不在同一个表中。则需要创建多表查询。这是这几个表必须先建立有相应的关系。

• 例4.3.7 建立一个名为“学生成绩”的查询，包括“学生编号”、学生的“姓名”、“课程名称”、“平时成绩”和“考试成绩”字段，并分别计算“平时成绩”和“考试成绩”的平均值。第4章 查询4.3.10在查询中计算

• 在实际应用中，经常需要对查询的结果进行计算，例如求和、计数、求最大最小值、求平均值等。• 1．总计计算• 例4.3.8 创建一个名为“计算”的查询，计算“选课成绩”中“平时成绩”的最小值，“考试成绩”的平均分。• 步骤如下图所示。第4章 查询

第4章 查询第4章 查询• 2．分组总计计算

• 例4.3.9 在例4.3.8的基础上，创建按学期进行计算，命名为“按学期计算”的查询。步骤如下图所示。第4章 查询

• 4．自定义计算字段

• 可以在查询中添加一个自定义的用于计算的新字段。计算字段是指根据一个或多个表中的一个或多个字段并使用表达式建立的新字段。• 例4.3.10 创建一个名为“计算期评成绩”的查询，包含“学生编号”、“姓名”、“平时成绩”和“考试成绩”字段，新建一个“期评成绩”字段，而且• 期评成绩=平时成绩×0.3+考试成绩×0.7• 并将期评成绩为60分以上的记录显示出来。• 步骤如下图所示。第4章 查询

图4.3.34 查询的设计视图 图4.3.35 表达式生成器第4章 查询图4.3.36 查询设置第4章 查询

图4.3.37 “计算期评成绩”的查询结果返回目录第4章 查询

4.4 创建SQL查询4.4.1 SQL简介

• SQL(Structured Query Language)结构化查询语言，是访问关系型数据库的标准语言 。• Jet SQL语句有两种类型：

– （1）数据操作语言（DML）语句：主要用来检索、添加、修改和删除数据库中的数据。– （2）数据定义语言（DDL）语句：主要用来管理数据库中的对象。第4章 查询

• 常用的SQL语句有：

• （1）SELECT：用于创建查询，检索符合条件的数据。

• （2）INSERT：在现有表中添加一条或多条记录。• （3）UPDATE：修改表中一条或多条记录数据。• （4）DELETE：删除表中一条或多条记录。• （5）SELECT INTO：将现有表的结构和数据复制到另一个新表中。第4章 查询

• 4.4.2 SELECT语句简介

• SELECT语句是最常用的SQL语句之一，主要用于创建选择查询、检索数据，SELECT包含多个子句，其语句格式为：• SELECT [ALL|DISTINCT| TOP n| TOP n percent][*|<字段名1>，<字段名2>,…]FROM 数据源

[WHERE <条件表达式>]

[ORDER BY <字段1>[ASC|DESC][， <字段2>[ASC|DESC][，…]][GROUP BY <字段1>[， <字段2>,…]][HAVING <条件表达式>]语句格式说明：第4章 查询• 其中：

• ALL：表示相关检索或操作应用于数据源中所有的行，ALL是所列四项中的默认项。• DISTINCT：表示对指定的字段，将返回不包含重复项的一条记录。

教育信息化管理系统

• ORDER BY子句：用于指定查询结果所依据的排序字段，其中ASC表示按指定字段升序排序，DESC表示按指定字段降序排序，不写ASC或DESC时，默认是ASC。• GROUP BY子句：用于设置查询结果是否按指定字段进行分组。

• HAVING子句：用于限制GROUP BY子句返回的结果，只有上面使用了GROUP BY子句才能够使用HAVING子句，但是使用了GROUP BY子句不一定要使用HAVING子句。第4章 查询SELECTFROM

GROUP BYORDER BYWHERESQL语句与查询设计器的比较第4章 查询

4.4.3 使用SELECT语句创建单表选择查询• 例4.4.1 使用“教学管理系统”数据库中“教师”表的数据创建一个SQL选择查询，要求按照工作时间先后顺序检索经济系和计算机系的女教师，查询结果仅显示教师姓名、性别、学历、职称、系别和工作时间 。• 步骤如下图所示。第4章 查询

图4.4.1 新建查询对话框图4.4.2 关闭显示表对话框第4章 查询

图4.4.3 选择“查询／ＳＱＬ特定查询／联合”打开联合查询窗口，录入查询语句图4.4.4 另存为对话框第4章 查询

图4.4.5 例4401-SQL查询结果第4章 查询

• 例4.4.2使用“教学管理系统”数据库中“学生”表的数据创建一个SQL选择查询，要求检索来自山东的学生，查询结果要求先按年龄降序排序，再按学生编号升序排序，查询结果中包含“学生编号”、“姓名”、“性别”、“年龄”和“简历”字段内容。 SQL如图所示 。第4章 查询

4.4.4 使用SELECT语句创建多表选择查询• 使用SELECT语句创建多表选择查询时，由于数据源来自多个表，因此要对FROM子句的数据源部分作相应的修改，主要是指明多个表的连接关系。• SELECT语句修改成以下格式：第4章 查询

• SELECT [ALL|DISTINCT| TOP n| TOP n percent] [*|<字段名1>，<字段名2>,…]• FROM 数据表或查询 [INNER JOIN 数据表或查询 ON <条件表达式>]• [WHERE <条件表达式>]

• [ORDER BY 字段1[ASC|DESC][，字段2[ASC|DESC][，…]]4.4.4 使用SELECT语句创建多表选择查询第4章 查询

• 例4.4.4 使用“教学管理系统”数据库中“课程”表和“选课成绩”表创建一个SQL选择查询，要求统计各门课程期末考试的平均成绩，并以各门课程期末考试的平均成绩降序排序。 SQL语句如下图所示：4.4.4 使用SELECT语句创建多表选择查询第4章 查询

• 说明：

• （1）本例在SELECT语句后面的字段列表中用到了：AVG(选课成绩.考试成绩) AS 平均成绩，表示以“平均成绩”作为AVG(选课成绩.考试成绩)字段的别名，别名只要符合字段的命名规则即可，在查询结果的字段名称行中会显示别名。AVG()是统计平均值的函数。• （2）本例在统计各门课程的平均成绩时用到了分组子句：GROUP BY 课程.课程名称，其中“课程名称”是分组字段。

• （3）Round函数可为字段限制小数位数，如：• Round (AVG(选课成绩.考试成绩),2)第4章 查询图4.4.11 各门课程平均成绩查询结果第4章 查询

• 例4.4.3 使用“教学管理系统”数据库中“学生”表、“课程”表、 “选课成绩”表和“教师”表的数据创建一个SQL选择查询，要求显示“学生”表的“学生编号”、“姓名”、“性别”字段，“课程”表的“课程名称”字段，“选课成绩”表的“考试成绩”字段，“教师”表的“姓名”字段。查询结果要求先按课程名称降序排序，再按学生成绩降序排序。• SQL语句如下图所示：• SELECT 学生.学生编号, 学生.姓名, 学生.性别, 课程.课程名称, 选课成绩.考试成绩, 教师.姓名

• FROM 学生 INNER JOIN ((教师 INNER JOIN 课程 ON 教师.教师编号=课程.任课教师编号) INNER JOIN 选课成绩 ON 课程.课程编号=选课成绩.课程编号) ON 学生.学生编号=选课成绩.学生编号• ORDER BY 课程.课程名称 DESC , 选课成绩.考试成绩 DESC;第4章 查询

SELECTFROM

GROUP BYORDER BYWHERE

SQL语句与查询设计器的比较INNER JOIN …on……第4章 查询• 3．函数

• 函数被用来完成一些特殊的运算，函数由函数名、括号和参数构成，某些函数的括号中可以无参数。如Sum([平时成绩]),在这里Sum是函数名, [平时成绩]是参数，函数的返回值即函数值是平时成绩之和。• 可以直接使用函数的返回值，也可以将函数的返回值作为条件的比较对象。

第4章 查询

• 在SQL语句中常用的统计函数有：

• （1）MAX(字段名)：返回指定字段数据的最大值。• （2）MIN(字段名)：返回指定字段数据的最小值。• （3）COUNT(字段名)：返回指定字段数据中非空数据的个数。• （4）COUNT(*)：返回查询结果中总的行数。• （5）AVG(字段名)：返回指定字段数据的平均值。

• （6）SUM(字段名)：返回指定字段数据的总和。第4章 查询表4.3.2 总计项函数的功能分类 中文名 英文名 功能汇总函数

合计 Sum 为每一组中指定的字段进行求和运算平均值 Avg 为每一组中指定的字段进行求平均值运算最小值 Min 为每一组中指定的字段进行求最小值最大值 Max 为每一组中指定的字段进行求最大值计数 Count 根据指定的字段计算每一组中记录的个数标准差 StDev 根据指定的字段计算每一组的统计标准差方差 Var 根据指定的字段计算每一组的统计方差其他总计项

第一条记录 First 找出每一组中首条记录指定字段的值最后一条记录 Last 找出每一组中最后一条记录指定字段的值表达式 Expression 用来在设计网格的“字段”行中建立计算字段条件 Where 限定表中的哪些记录可以参加分组汇总，即指定查询条件分组 Group By 用来指定分组汇总字段第4章 查询

在SQL中使用统计函数

• 例4.4.5 统计“学生”表中的学生人数。• SELECT COUNT（*） AS 学生表人数• FROM 学生• 例4.4.6 统计“选课成绩”表中的考试成绩的最高分。• SELECT MAX（考试成绩） AS 最高分• FROM 选课成绩第4章 查询

• 例4.4.7 统计“选课成绩”表中的考试成绩的最高分、最低分。

• SELECT MAX（考试成绩） AS 最高分, MIN（考试成绩） AS 最低分• FROM 选课成绩

• 例4.4.8 4408-按学期统计“选课成绩”表中的考试成绩、平时成绩的平均分• SELECT AVG(考试成绩) AS 考试平均分, AVG(平时成绩) AS 平时平均分• FROM 选课成绩• GROUP BY 学期;在SQL中使用统计函数第4章 查询• 例4.4.9 统计“学生”表中男女生的平均年龄

• SELECT 性别,AVG(年龄) AS 平均年龄• FROM 学生• GROUP BY 性别;

• 例4.4.10 统计“学生”表中男女生的平均成绩• SELECT 学生.性别, AVG(选课成绩 .考试成绩) AS 考试平均成绩• FROM 学生 INNER JOIN 选课成绩 ON 学生.学生编号=选课成绩.学生编号

• GROUP BY 学生. 性别;在SQL中使用统计函数第4章 查询

• 例4.4.11 (实验6分组合计1) ：设有数据表 学生（学号，姓名，性别，班级），用SQL语句建立一个查询，统计并显示出学生表中姓氏在2人以上的姓氏及其人数，假设姓氏都是一个字 。在SQL中使用统计函数Select left(姓名,1) AS 姓氏, count(姓名) AS 人数From 学生

• 例4.4.12(实验6分组合计2)：统计表“01级学生名单”中资环系学生人数超过5人以上的姓氏和人数，并按人数从多到少显示。在SQL中使用统计函数

SELECT left(姓名,1) AS 姓氏, count(姓名) AS 人数FROM 01级学生名单

WHERE 班级 like "资环*"GROUP BY left(姓名,1)HAVING count(姓名)>=5ORDER BY count(姓名) DESC;第4章 查询SQL执行顺序

• 当WHERE子句、GROUP BY子句、HAVING子句和ORDER BY子句同时出现时，SQL执行顺序如下：• （1） 执行WHERE子句，从表中选取满足条件的记录。

• （2） 由GROUP BY子句对选取的记录按指定的列分组。• （3）执行函数。

• （4）执行HAVING子句选取满足条件的分组。• （5）执行 ORDER BY 子句对分组结果排序。第4章 查询关于查询的几个常见的概念（1）投影查询

Select 字段名表From 表名（2）选择查询

Select 字段名表From 表名Where 条件（3）排序查询Select 字段名表From 表名Order by 字段名第4章 查询本章小结

• Access中的查询可以实现对一个数据库中的一个表或多个表中存储的数据进行检索、统计、计算、排序等操作。• 本章介绍了Access中各种查询：选择查询、参数查询、交叉表查询、操作查询和SQL查询。