Access数据库2003教学课件-CH4剖析

1、Access2003数据库教程,Access2003数据库教程,第1章 数据库系统概述 第2章 Access 2003数据库系统的基本概念 第3章 重要的表操作 第4章 查询 第5章 创建实用窗体 第6章 报表 第7章 数据访问页的设计 第8章 宏 第9章 模块 第10章 查询综合例题,第4章 查询,4.1 查询简介 4.2 使用向导创建查询 4. 使用查询设计器创建查询 4.4 创建SQL查询 4.5 查询中数据的操作,4.1 查询简介,查询以一个表、多个表或者另一查询为基础，创建一个新的数据集作为查询的结果集。 查询的结果是动态的，其内容是随着数据源的变化而变化的。 可以通过查询设计器和查

2、询设计向导来创建查询。,4.1.1 Access查询按功能进行分类,1选择查询 2参数查询 3交叉表查询 4操作查询 删除查询 更新查询 追加查询 生成表查询 5SQL 查询,4.1.2 Access查询按创建查询方式进行分类,（1）使用查询设计器 （2）使用各种查询向导 简单查询向导 交叉表查询向导 查找重复项查询向导 查找不匹配项查询向导,返回目录,4. 使用向导创建查询4.2.1使用简单查询向导创建查询,使用简单查询可以在一个或多个表指定字段中检索数据，还可以对记录组或全部记录进行总计、计数、求平均值、最大值和最小值的运算。,4.使用查询设计器创建查询4.3.1 查询设计器及其使用,1打

3、开查询设计器，界面如下：,3. 查询设计器的工具栏,4.3.3查询条件和条件表达式,1条件表达式 （1）简单条件表达式,（2）含运算符的条件表达式 条件表达式中含有比较运算符,4.3.3查询条件和条件表达式,条件表达式中含有字符串运算符,“*”号表示字符串中在该位置可以为任意的若干个字符。,“？”号表示字符串中在该位置可以为任意的一个字符。,4.3.3查询条件和条件表达式,条件表达式中含有逻辑运算符,4.3.3查询条件和条件表达式,图4.3.5 查询的“条件”行和“或”行,4.3.3查询条件和条件表达式,不同行是”或”,4.3.3查询条件和条件表达式,同行”或”要用逻辑运算符,表达式 表达式可

4、以用在表、查询、窗体、报表、数据页和宏等六个对象中。,2表达式的设置,选课成绩!平时成绩*0.3+选课成绩!考试成绩*0.7,表名,字段名,运算符,常量,4.3.3查询条件和条件表达式,表名必须用一对中括号 括住外加一感叹号。 例如：选课成绩! 字段名必须用一对中括号 括住。 例如：平时成绩 字符串必须用一对英文半角单引号或双引号括住。 例如：女 日期时间应该用半角的“#”号括起来。 例如：#84-1-25#,(1)Access表达式的有关规则,4.3.3查询条件和条件表达式,（2）函数,表6.4.1 部分常用日期函数,4.3.3查询条件和条件表达式,（3）运算符,4.3.3查询条件和条件表达

5、式,4.3.4创建参数查询,参数查询在运行时会提示用户输入参数值（查询条件），并根据用户的输入给出查询结果，从而可以实现交互式查询。 参数查询实质上是把选择查询的“条件”设置成一个带有参数的“可变条件”。,例4.3.2 创建一个名为“按照学生姓名查询”的查询，当输入不同的学生姓名时，显示相应学生的记录数据。,4.3.5创建生成表查询,生成表查询是在表或者查询的基础上创建的，在使用时生成表查询，就能够生成一个新“表”。 例4.3.3 根据“学生”表，创建一个名为“创建男学生表”的生成表查询，并使用该查询生成一个包含“学生编号”、“姓名”、“性别”和“年龄”的名为“男学生”的表。如下图所示。,4.

6、3.6 创建追加查询,如果需要将一个或多个表中所有记录或符合一定条件的记录添加到另一个表的尾部，可以使用追加查询快速完成。在此，将接受添加记录的表称为目的表，将用来添加记录的表称为源表，追加查询只能追加匹配的字段。,例4.3.4 创建一个名为“添加记录”的追加查询，并使用该查询，将“学生”表中“家庭住址”为“广西来宾”的记录添加到“男学生”的表中。 步骤如下 图所示。,4.3.7创建更新查询,如果需要大批量修改数据，或者是输入有规律的数据，可以借助更新查询来完成。 例4.3.5 创建一个名为“年龄加1”的更新查询，并使用该查询，将“学生”表中每个学生的年龄加1。 步骤如下 图所示。,4.3.8

7、创建删除查询,使用删除查询，可以将数据库中一些无用的数据批量删除。 例4.3.6 创建一个名为“删除不及格”的删除查询，并使用该查询，将“选课成绩”表中考试成绩不及格的学生的纪录删除。 步骤如下 图所示。,4.3.9创建多表查询,创建查询时，如果查询的数据源不在同一个表中。则需要创建多表查询。这是这几个表必须先建立有相应的关系。 例4.3.7 建立一个名为“学生成绩”的查询，包括“学生编号”、学生的“姓名”、“课程名称”、“平时成绩”和“考试成绩”字段，并分别计算“平时成绩”和“考试成绩”的平均值。,4.3.10在查询中计算,在实际应用中，经常需要对查询的结果进行计算，例如求和、计数、求最大最

8、小值、求平均值等。 1总计计算 例4.3.8 创建一个名为“计算”的查询，计算“选课成绩”中“平时成绩”的最小值，“考试成绩”的平均分。 步骤如下图所示。,2分组总计计算 例4.3.9 在例4.3.8的基础上，创建按学期进行计算，命名为“按学期计算”的查询。步骤如下图所示。,4自定义计算字段 可以在查询中添加一个自定义的用于计算的新字段。计算字段是指根据一个或多个表中的一个或多个字段并使用表达式建立的新字段。 例4.3.10 创建一个名为“计算期评成绩”的查询，包含“学生编号”、“姓名”、“平时成绩”和“考试成绩”字段，新建一个“期评成绩”字段，而且 期评成绩=平时成绩0.3+考试成绩0.7

9、并将期评成绩为60分以上的记录显示出来。 步骤如下图所示。,图4.3.34 查询的设计视图,图4.3.35 表达式生成器,图4.3.36 查询设置,图4.3.37 “计算期评成绩”的查询结果,返回目录,4.4 创建SQL查询4.4.1 SQL简介,SQL(Structured Query Language)结构化查询语言，是访问关系型数据库的标准语言 。 Jet SQL语句有两种类型： （1）数据操作语言（DML）语句：主要用来检索、添加、修改和删除数据库中的数据。 （2）数据定义语言（DDL）语句：主要用来管理数据库中的对象。,常用的SQL语句有： （1）SELECT：用于创建查询，检索符合

10、条件的数据。 （2）INSERT：在现有表中添加一条或多条记录。 （3）UPDATE：修改表中一条或多条记录数据。 （4）DELETE：删除表中一条或多条记录。 （5）SELECT INTO：将现有表的结构和数据复制到另一个新表中。,4.4.2 SELECT语句简介 SELECT语句是最常用的SQL语句之一，主要用于创建选择查询、检索数据，SELECT包含多个子句，其语句格式为： SELECT ALL|DISTINCT| TOP n| TOP n percent *|，, FROM 数据源 WHERE ORDER BY ASC|DESC ， ASC|DESC， GROUP BY ， , HAV

11、ING 语句格式说明：,其中： ALL：表示相关检索或操作应用于数据源中所有的行，ALL是所列四项中的默认项。 DISTINCT：表示对指定的字段，将返回不包含重复项的一条记录。 TOP n：表示将返回满足检索条件的前n条记录。 TOP n percent：表示将返回满足检索条件的前n%条记录。 *：表示返回的记录包含所有字段的值。 FROM子句：用于指定创建查询所用的数据源，可以是表或已有查询，此项为必选项。,WHERE子句：用于设置检索满足的记录集，此可以是用And和Or运算符连接起来的复杂的条件。 ORDER BY子句：用于指定查询结果所依据的排序字段，其中ASC表示按指定字段升序排序，

12、DESC表示按指定字段降序排序，不写ASC或DESC时，默认是ASC。 GROUP BY子句：用于设置查询结果是否按指定字段进行分组。 HAVING子句：用于限制GROUP BY子句返回的结果，只有上面使用了GROUP BY子句才能够使用HAVING子句，但是使用了GROUP BY子句不一定要使用HAVING子句。,SELECT FROM GROUP BY ORDER BY WHERE,SQL语句与查询设计器的比较,4.4.3 使用SELECT语句创建单表选择查询,例4.4.1 使用“教学管理系统”数据库中“教师”表的数据创建一个SQL选择查询，要求按照工作时间先后顺序检索经济系和计算机系的女

13、教师，查询结果仅显示教师姓名、性别、学历、职称、系别和工作时间 。 步骤如下图所示。,图4.4.1 新建查询对话框,图4.4.2 关闭显示表对话框,图4.4.3 选择“查询特定查询联合”打开联合查询窗口，录入查询语句,图4.4.4 另存为对话框,图4.4.5 例4401-SQL查询结果,例4.4.2使用“教学管理系统”数据库中“学生”表的数据创建一个SQL选择查询，要求检索来自山东的学生，查询结果要求先按年龄降序排序，再按学生编号升序排序，查询结果中包含“学生编号”、“姓名”、“性别”、“年龄”和“简历”字段内容。 SQL如图所示 。,4.4.4 使用SELECT语句创建多表选择查询,使用SE

14、LECT语句创建多表选择查询时，由于数据源来自多个表，因此要对FROM子句的数据源部分作相应的修改，主要是指明多个表的连接关系。 SELECT语句修改成以下格式：,SELECT ALL|DISTINCT| TOP n| TOP n percent *|，, FROM 数据表或查询 INNER JOIN 数据表或查询 ON WHERE ORDER BY 字段1ASC|DESC，字段2ASC|DESC，,4.4.4 使用SELECT语句创建多表选择查询,例4.4.4 使用“教学管理系统”数据库中“课程”表和“选课成绩”表创建一个SQL选择查询，要求统计各门课程期末考试的平均成绩，并以各门课程期末考

15、试的平均成绩降序排序。 SQL语句如下图所示：,4.4.4 使用SELECT语句创建多表选择查询,说明： （1）本例在SELECT语句后面的字段列表中用到了：AVG(选课成绩.考试成绩) AS 平均成绩，表示以“平均成绩”作为AVG(选课成绩.考试成绩)字段的别名，别名只要符合字段的命名规则即可，在查询结果的字段名称行中会显示别名。AVG()是统计平均值的函数。 （2）本例在统计各门课程的平均成绩时用到了分组子句：GROUP BY 课程.课程名称，其中“课程名称”是分组字段。 （3）Round函数可为字段限制小数位数，如： Round (AVG(选课成绩.考试成绩),2),图4.4.11 各门

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

17、号) INNER JOIN 选课成绩 ON 课程.课程编号=选课成绩.课程编号) ON 学生.学生编号=选课成绩.学生编号 ORDER BY 课程.课程名称 DESC , 选课成绩.考试成绩 DESC;,SELECT FROM GROUP BY ORDER BY WHERE,SQL语句与查询设计器的比较,INNER JOIN on,3函数 函数被用来完成一些特殊的运算，函数由函数名、括号和参数构成，某些函数的括号中可以无参数。如Sum(平时成绩),在这里Sum是函数名, 平时成绩是参数，函数的返回值即函数值是平时成绩之和。 可以直接使用函数的返回值，也可以将函数的返回值作为条件的比较对象。,在

18、SQL语句中常用的统计函数有： （1）MAX(字段名)：返回指定字段数据的最大值。 （2）MIN(字段名)：返回指定字段数据的最小值。 （3）COUNT(字段名)：返回指定字段数据中非空数据的个数。 （4）COUNT(*)：返回查询结果中总的行数。 （5）AVG(字段名)：返回指定字段数据的平均 值。 （6）SUM(字段名)：返回指定字段数据的总和。,表4.3.2 总计项函数的功能,在SQL中使用统计函数,例4.4.5 统计“学生”表中的学生人数。 SELECT COUNT（*） AS 学生表人数 FROM 学生,例4.4.6 统计“选课成绩”表中的考试成绩的最高分。 SELECT MAX（考

19、试成绩） AS 最高分 FROM 选课成绩,例4.4.7 统计“选课成绩”表中的考试成绩的最高分、最低分。 SELECT MAX（考试成绩） AS 最高分, MIN（考试成绩） AS 最低分 FROM 选课成绩,例4.4.8 4408-按学期统计“选课成绩”表中的考试成绩、平时成绩的平均分 SELECT AVG(考试成绩) AS 考试平均分, AVG(平时成绩) AS 平时平均分 FROM 选课成绩 GROUP BY 学期;,在SQL中使用统计函数,例4.4.9 统计“学生”表中男女生的平均年龄 SELECT 性别,AVG(年龄) AS 平均年龄 FROM 学生 GROUP BY 性别;,例4.4.10 统计“学生”表中男女生的平均成绩 SELECT 学生.性别, AVG(选课成绩 .考试成绩) AS 考试平均成绩 FROM 学生 INNER JOIN 选课成绩 ON 学生.学生编号=选课成绩.学生编号 GROUP BY 学生. 性别;,在SQL中使用统计函数,例4.4.11 (实验6分组合计1) ：设有数据表 学生（学号，姓名，性别，班级），用SQL语句建立一个查询，统计并显示出学生表中姓氏在2人以上的姓氏及其人数，假设姓氏都是一个字 。,在SQL中使用统计函数,Select left(姓名,1) AS 姓氏, count(姓名) AS