Access数据库2003教学课件-CH4.ppt

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

2、1选择查询2参数查询3交叉表查询4操作查询删除查询更新查询追加查询生成表查询5SQL查询,4.1.2Access查询按创建查询方式进行分类,（1）使用查询设计器（2）使用各种查询向导简单查询向导交叉表查询向导查找重复项查询向导查找不匹配项查询向导,返回目录,4.使用向导创建查询4.2.1使用简单查询向导创建查询,使用简单查询可以在一个或多个表指定字段中检索数据，还可以对记录组或全部记录进行总计、计数、求平均值、最大值和最小值的运算。,例4.2.1根据“选课成绩”表，使用简单查询向导创建一个名为“学生的成绩”的查询，包括“学生编号”、“平时成绩”、“考试成绩”三个字段。,4.2.2交叉表查询向导

3、,例4.2.2根据“选课成绩”表，使用交叉表查询向导创建一个查询，分别将每个学生各学期各课程的分数统计出来。,4.2.3查找重复项查询向导,利用“查找重复项查询向导”可以创建一个查询，用于在同一个表中查找指定字段有相同值的记录。例4.2.3创建一个名为“选相同课程的同学”的查询，查找选择相同课程的同学有多少人。,4.2.4查找不匹配项查询向导,使用“查找不匹配项查询向导”可以在一个表中查找与另一个表中记录不相关的记录。例4.2.4查找在“选课成绩”表中没有选课成绩的学生记录（即查找没有选课的学生）。,返回目录,4.使用查询设计器创建查询4.3.1查询设计器及其使用,1打开查询设计器，界面如下：

4、,3.查询设计器的工具栏,4.3.2创建选择查询,例4.3.1创建一个名为“女同学”的查询，包括“学生编号”、“姓名”、“性别”字段，将女同学的记录挑选出来，并按“学生编号”从低到高排序。,4.3.3查询条件和条件表达式,1条件表达式（1）简单条件表达式,（2）含运算符的条件表达式条件表达式中含有比较运算符,4.3.3查询条件和条件表达式,条件表达式中含有字符串运算符,“*”号表示字符串中在该位置可以为任意的若干个字符。,“？”号表示字符串中在该位置可以为任意的一个字符。,4.3.3查询条件和条件表达式,条件表达式中含有逻辑运算符,4.3.3查询条件和条件表达式,图4.3.5查询的“条件”行和

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

6、函数,表6.4.1部分常用日期函数,4.3.3查询条件和条件表达式,（3）运算符,4.3.3查询条件和条件表达式,例如,以“选课成绩”表为数据源，创建一个名为“学生期评成绩”的报表，如图6.4.1所示，包含学生编号、课程编号、学期、平时成绩、考试成绩和期评成绩，其中，期评成绩=平时成绩0.3+考试成绩0.7。,直接输入表达式通过表达式生成器创建,（4）录入方法,4.3.3查询条件和条件表达式,4.3.4创建参数查询,参数查询在运行时会提示用户输入参数值（查询条件），并根据用户的输入给出查询结果，从而可以实现交互式查询。参数查询实质上是把选择查询的“条件”设置成一个带有参数的“可变条件”。,例4

7、.3.2创建一个名为“按照学生姓名查询”的查询，当输入不同的学生姓名时，显示相应学生的记录数据。,4.3.5创建生成表查询,生成表查询是在表或者查询的基础上创建的，在使用时生成表查询，就能够生成一个新“表”。例4.3.3根据“学生”表，创建一个名为“创建男学生表”的生成表查询，并使用该查询生成一个包含“学生编号”、“姓名”、“性别”和“年龄”的名为“男学生”的表。如下图所示。,4.3.6创建追加查询,如果需要将一个或多个表中所有记录或符合一定条件的记录添加到另一个表的尾部，可以使用追加查询快速完成。在此，将接受添加记录的表称为目的表，将用来添加记录的表称为源表，追加查询只能追加匹配的字段。,例

8、4.3.4创建一个名为“添加记录”的追加查询，并使用该查询，将“学生”表中“家庭住址”为“广西来宾”的记录添加到“男学生”的表中。步骤如下图所示。,4.3.7创建更新查询,如果需要大批量修改数据，或者是输入有规律的数据，可以借助更新查询来完成。例4.3.5创建一个名为“年龄加1”的更新查询，并使用该查询，将“学生”表中每个学生的年龄加1。步骤如下图所示。,4.3.8创建删除查询,使用删除查询，可以将数据库中一些无用的数据批量删除。例4.3.6创建一个名为“删除不及格”的删除查询，并使用该查询，将“选课成绩”表中考试成绩不及格的学生的纪录删除。步骤如下图所示。,4.3.9创建多表查询,创建查询时

9、，如果查询的数据源不在同一个表中。则需要创建多表查询。这是这几个表必须先建立有相应的关系。例4.3.7建立一个名为“学生成绩”的查询，包括“学生编号”、学生的“姓名”、“课程名称”、“平时成绩”和“考试成绩”字段，并分别计算“平时成绩”和“考试成绩”的平均值。,4.3.10在查询中计算,在实际应用中，经常需要对查询的结果进行计算，例如求和、计数、求最大最小值、求平均值等。1总计计算例4.3.8创建一个名为“计算”的查询，计算“选课成绩”中“平时成绩”的最小值，“考试成绩”的平均分。步骤如下图所示。,2分组总计计算例4.3.9在例4.3.8的基础上，创建按学期进行计算，命名为“按学期计算”的查询

10、。步骤如下图所示。,4自定义计算字段可以在查询中添加一个自定义的用于计算的新字段。计算字段是指根据一个或多个表中的一个或多个字段并使用表达式建立的新字段。例4.3.10创建一个名为“计算期评成绩”的查询，包含“学生编号”、“姓名”、“平时成绩”和“考试成绩”字段，新建一个“期评成绩”字段，而且期评成绩=平时成绩0.3+考试成绩0.7并将期评成绩为60分以上的记录显示出来。步骤如下图所示。,图4.3.34查询的设计视图,图4.3.35表达式生成器,图4.3.36查询设置,图4.3.37“计算期评成绩”的查询结果,返回目录,4.4创建SQL查询4.4.1SQL简介,SQL(StructuredQu

11、eryLanguage)结构化查询语言，是访问关系型数据库的标准语言。JetSQL语句有两种类型：（1）数据操作语言（DML）语句：主要用来检索、添加、修改和删除数据库中的数据。（2）数据定义语言（DDL）语句：主要用来管理数据库中的对象。,常用的SQL语句有：（1）SELECT：用于创建查询，检索符合条件的数据。（2）INSERT：在现有表中添加一条或多条记录。（3）UPDATE：修改表中一条或多条记录数据。（4）DELETE：删除表中一条或多条记录。（5）SELECTINTO：将现有表的结构和数据复制到另一个新表中。,4.4.2SELECT语句简介SELECT语句是最常用的SQL语句之一，

12、主要用于创建选择查询、检索数据，SELECT包含多个子句，其语句格式为：SELECTALL|DISTINCT|TOPn|TOPnpercent*|，,FROM数据源WHEREORDERBYASC|DESC，ASC|DESC，GROUPBY，,HAVING语句格式说明：,其中：ALL：表示相关检索或操作应用于数据源中所有的行，ALL是所列四项中的默认项。DISTINCT：表示对指定的字段，将返回不包含重复项的一条记录。TOPn：表示将返回满足检索条件的前n条记录。TOPnpercent：表示将返回满足检索条件的前n%条记录。*：表示返回的记录包含所有字段的值。FROM子句：用于指定创建查询所用的

13、数据源，可以是表或已有查询，此项为必选项。,WHERE子句：用于设置检索满足的记录集，此可以是用And和Or运算符连接起来的复杂的条件。ORDERBY子句：用于指定查询结果所依据的排序字段，其中ASC表示按指定字段升序排序，DESC表示按指定字段降序排序，不写ASC或DESC时，默认是ASC。GROUPBY子句：用于设置查询结果是否按指定字段进行分组。HAVING子句：用于限制GROUPBY子句返回的结果，只有上面使用了GROUPBY子句才能够使用HAVING子句，但是使用了GROUPBY子句不一定要使用HAVING子句。,SELECTFROMGROUPBYORDERBYWHERE,SQL语句

14、与查询设计器的比较,4.4.3使用SELECT语句创建单表选择查询,例4.4.1使用“教学管理系统”数据库中“教师”表的数据创建一个SQL选择查询，要求按照工作时间先后顺序检索经济系和计算机系的女教师，查询结果仅显示教师姓名、性别、学历、职称、系别和工作时间。步骤如下图所示。,图4.4.1新建查询对话框,图4.4.2关闭显示表对话框,图4.4.3选择“查询特定查询联合”打开联合查询窗口，录入查询语句,图4.4.4另存为对话框,图4.4.5例4401-SQL查询结果,例4.4.2使用“教学管理系统”数据库中“学生”表的数据创建一个SQL选择查询，要求检索来自山东的学生，查询结果要求先按年龄降序排

15、序，再按学生编号升序排序，查询结果中包含“学生编号”、“姓名”、“性别”、“年龄”和“简历”字段内容。SQL如图所示。,4.4.4使用SELECT语句创建多表选择查询,使用SELECT语句创建多表选择查询时，由于数据源来自多个表，因此要对FROM子句的数据源部分作相应的修改，主要是指明多个表的连接关系。SELECT语句修改成以下格式：,SELECTALL|DISTINCT|TOPn|TOPnpercent*|，,FROM数据表或查询INNERJOIN数据表或查询ONWHEREORDERBY字段1ASC|DESC，字段2ASC|DESC，,4.4.4使用SELECT语句创建多表选择查询,例4.4

16、.4使用“教学管理系统”数据库中“课程”表和“选课成绩”表创建一个SQL选择查询，要求统计各门课程期末考试的平均成绩，并以各门课程期末考试的平均成绩降序排序。SQL语句如下图所示：,4.4.4使用SELECT语句创建多表选择查询,说明：（1）本例在SELECT语句后面的字段列表中用到了：AVG(选课成绩.考试成绩)AS平均成绩，表示以“平均成绩”作为AVG(选课成绩.考试成绩)字段的别名，别名只要符合字段的命名规则即可，在查询结果的字段名称行中会显示别名。AVG()是统计平均值的函数。（2）本例在统计各门课程的平均成绩时用到了分组子句：GROUPBY课程.课程名称，其中“课程名称”是分组字段。

17、（3）Round函数可为字段限制小数位数，如：Round(AVG(选课成绩.考试成绩),2),图4.4.11各门课程平均成绩查询结果,例4.4.3使用“教学管理系统”数据库中“学生”表、“课程”表、“选课成绩”表和“教师”表的数据创建一个SQL选择查询，要求显示“学生”表的“学生编号”、“姓名”、“性别”字段，“课程”表的“课程名称”字段，“选课成绩”表的“考试成绩”字段，“教师”表的“姓名”字段。查询结果要求先按课程名称降序排序，再按学生成绩降序排序。SQL语句如下图所示：,SELECT学生.学生编号,学生.姓名,学生.性别,课程.课程名称,选课成绩.考试成绩,教师.姓名FROM学生INNE

18、RJOIN(教师INNERJOIN课程ON教师.教师编号=课程.任课教师编号)INNERJOIN选课成绩ON课程.课程编号=选课成绩.课程编号)ON学生.学生编号=选课成绩.学生编号ORDERBY课程.课程名称DESC,选课成绩.考试成绩DESC;,SELECTFROMGROUPBYORDERBYWHERE,SQL语句与查询设计器的比较,INNERJOINon,3函数函数被用来完成一些特殊的运算，函数由函数名、括号和参数构成，某些函数的括号中可以无参数。如Sum(平时成绩),在这里Sum是函数名,平时成绩是参数，函数的返回值即函数值是平时成绩之和。可以直接使用函数的返回值，也可以将函数的返回值

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

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