Access数据库教学课件 CH

Access2003数据库教程

Access2003数据库教程

Access2003数据库教程第1章数据库系统概述第2章Access2003数据库系统的基本概念第3章重要的表操作第4章查询第5章创建实用窗体第6章报表第7章数据访问页的设计第8章宏第9章模块第10章查询综合例题Access2003数据库教程第1章数据库系统概述第4章查询4.1查询简介4.2使用向导创建查询4.３使用查询设计器创建查询4.4创建SQL查询4.5查询中数据的操作第4章查询4.1查询简介4.1查询简介查询以一个表、多个表或者另一查询为基础，创建一个新的数据集作为查询的结果集。查询的结果是动态的，其内容是随着数据源的变化而变化的。可以通过查询设计器和查询设计向导来创建查询。4.1查询简介查询以一个表、多个表或者另一查询为基础，4.1.1Access查询按功能进行分类1．选择查询2．参数查询3．交叉表查询4．操作查询①删除查询②更新查询③追加查询④生成表查询5．SQL查询4.1.1Access查询按功能进行分类1．选择查询4.1.2Access查询按创建查询方式进行分类（1）使用查询设计器（2）使用各种查询向导①简单查询向导②交叉表查询向导③查找重复项查询向导④查找不匹配项查询向导返回目录4.1.2Access查询按创建查询方式进行分类（1）使用4.２使用向导创建查询

4.2.1使用简单查询向导创建查询使用简单查询可以在一个或多个表指定字段中检索数据，还可以对记录组或全部记录进行总计、计数、求平均值、最大值和最小值的运算。4.２使用向导创建查询

4.2.1使用简单查询向导创建查询例4.2.1根据“选课成绩”表，使用简单查询向导创建一个名为“学生的成绩”的查询，包括“学生编号”、“平时成绩”、“考试成绩”三个字段。例4.2.1根据“选课成绩”表，使用简单查询向导创建一个名4.2.2交叉表查询向导例4.2.2根据“选课成绩”表，使用交叉表查询向导创建一个查询，分别将每个学生各学期各课程的分数统计出来。4.2.2交叉表查询向导例4.2.2根据“选课成绩”表4.2.3查找重复项查询向导利用“查找重复项查询向导”可以创建一个查询，用于在同一个表中查找指定字段有相同值的记录。例4.2.3创建一个名为“选相同课程的同学”的查询，查找选择相同课程的同学有多少人。4.2.3查找重复项查询向导利用“查找重复项查询向导”可以4.2.4查找不匹配项查询向导使用“查找不匹配项查询向导”可以在一个表中查找与另一个表中记录不相关的记录。例4.2.4查找在“选课成绩”表中没有选课成绩的学生记录（即查找没有选课的学生）。返回目录4.2.4查找不匹配项查询向导使用“查找不匹配项查询向导”4.３使用查询设计器创建查询

4.3.1查询设计器及其使用1．打开查询设计器，界面如下：4.３使用查询设计器创建查询

4.3.1查询设计器及其使用3.查询设计器的工具栏按钮功能和说明视图每种查询有三种视图，一是设计视图；二是数据表视图，用来显示查询结果集；三是SQL视图，用来通过SQL语句进行查询。本按钮可以在这三种视图之间切换查询类型本按钮可以在选择查询、交叉表查询、生成表查询、更新查询、追加查询和删除查询这六种查询之间切换执行执行查询，将查询结果集以工作表的形式显示出来显示表弹出“显示表”对话框，用户可以从中选择查询所要使用的表和查询。总计在查询设计区中增加“总计”行，用以进行求和、求平均值等统计计算。属性显示当前光标所在位置上的对象的属性：如果光标在查询设计窗口内的数据表/查询显示区内，则将显示查询的属性。如果光标在下部的查询设计区内，则将显示字段的属性。生成器弹出“表达式生成器”对话框，用于生成准则表达式。该按钮只有在光标位于查询设计区的“准则”栏内才有效。数据库窗口返回到数据库窗口。3.查询设计器的工具栏按钮功能和说明视图每种查询有三种视图4.3.2创建选择查询例4.3.1

创建一个名为“女同学”的查询，包括“学生编号”、“姓名”、“性别”字段，将女同学的记录挑选出来，并按“学生编号”从低到高排序。4.3.2创建选择查询例4.3.1创建一个名为“女同学”的4.3.3查询条件和条件表达式1．条件表达式（1）简单条件表达式所在字段条件表达式含义说明性别”男”查询性别为“男”的记录文本值一般应使用双引号括起来，如果没有加上双引号，Access会自动加上双引号大学英语83查询大学英语为83分的记录等效为=83，允许省去等号“=”出生年月#84-1-25#查询1984年1月25日出生者的记录在条件表达式中使用日期时应该用半角的“#”号括起来。如果没有加上“#”号，Access会自动加上双“#”号。4.3.3查询条件和条件表达式1．条件表达式所在字段条件表达（2）含运算符的条件表达式①条件表达式中含有比较运算符比较运算符所在字段条件表达式含义=等于性别=”女”查询女性的记录>大于总分>240查询总分大于240的记录<>不等于大学英语<>0查询大学英语不为0的记录>=大于等于略略<小于略略<=小于等于略略4.3.3查询条件和条件表达式（2）含运算符的条件表达式比较运算符所在字段条件表达式②条件表达式中含有字符串运算符运算符所在字段条件表达式含义Like姓名Like”李*”Like”李？”查询姓李的学生的记录In姓名In(”李梅”,”马威”)查询“姓名”字段值是“李梅”或“马威”的记录（用于指定字符串只能是列出来的值）Betweenand出生年月Between#84-1-1#And#84-12-31#查询出生年月在1984年内的记录（主要用于数字型、货币型和日期型字段）“*”号表示字符串中在该位置可以为任意的若干个字符。“？”号表示字符串中在该位置可以为任意的一个字符。4.3.3查询条件和条件表达式②条件表达式中含有字符串运算符运算符所在字段条件表达式含义L③条件表达式中含有逻辑运算符运算符所在字段条件表达式含义and(与)数学>=60and<85查询数学成绩在60～84之间的记录or(或)数学<60or>85查询数学成绩在60以下或者85以上的记录not性别not"女"查询性别不是“女”的记录4.3.3查询条件和条件表达式③条件表达式中含有逻辑运算符运算符所在字段条件表达式含义an图4.3.5查询的“条件”行和“或”行4.3.3查询条件和条件表达式不同行是”或”图4.3.5查询的“条件”行和“或”行4.3.3查询条件4.3.3查询条件和条件表达式同行”或”要用逻辑运算符4.3.3查询条件和条件表达式同行”或”要用逻辑运算符表达式表达式可以用在表、查询、窗体、报表、数据页和宏等六个对象中。

2．表达式的设置[选课成绩]![平时成绩]*0.3+[选课成绩]![考试成绩]*0.7表名字段名运算符常量4.3.3查询条件和条件表达式表达式2．表达式的设置[选课成绩]![平时成绩]*0.3+[表名必须用一对中括号[]括住外加一感叹号。例如：[选课成绩]!字段名必须用一对中括号[]括住。例如：[平时成绩]字符串必须用一对英文半角单引号或双引号括住。例如："女"日期时间应该用半角的“#”号括起来。例如：#84-1-25#(1)Access表达式的有关规则4.3.3查询条件和条件表达式表名必须用一对中括号[]括住外加一感叹号。(1)Ac（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查询条件和条件表达式（2）函数函数功能说明Year返回值是表示年份的四位整数例如（3）运算符运算符说明+加例如计算[平时成绩]+[考试成绩]-减例如计算[总工资]-[扣款]*乘例如计算[平时成绩]*0.3/除例如计算[总分]/[人数]^乘幂例如计算[订购量]^24.3.3查询条件和条件表达式（3）运算符运算符说明+加例如计算[平时成绩]+[考试成绩例如,以“选课成绩”表为数据源，创建一个名为“学生期评成绩”的报表，如图6.4.1所示，包含学生编号、课程编号、学期、平时成绩、考试成绩和期评成绩，其中，期评成绩=平时成绩×0.3+考试成绩×0.7。直接输入表达式通过表达式生成器创建（4）录入方法4.3.3查询条件和条件表达式例如,以“选课成绩”表为数据源，创建一个名为“学生期评成绩”4.3.4创建参数查询参数查询在运行时会提示用户输入参数值（查询条件），并根据用户的输入给出查询结果，从而可以实现交互式查询。参数查询实质上是把选择查询的“条件”设置成一个带有参数的“可变条件”。4.3.4创建参数查询参数查询在运行时会提示用户输入参数值（例4.3.2创建一个名为“按照学生姓名查询”的查询，当输入不同的学生姓名时，显示相应学生的记录数据。例4.3.2创建一个名为“按照学生姓名查询”的查询，当输入4.3.5创建生成表查询生成表查询是在表或者查询的基础上创建的，在使用时生成表查询，就能够生成一个新“表”。例4.3.3根据“学生”表，创建一个名为“创建男学生表”的生成表查询，并使用该查询生成一个包含“学生编号”、“姓名”、“性别”和“年龄”的名为“男学生”的表。如下图所示。4.3.5创建生成表查询生成表查询是在表或者查询的基础上创建Access数据库教学课件CHAccess数据库教学课件CH4.3.6创建追加查询如果需要将一个或多个表中所有记录或符合一定条件的记录添加到另一个表的尾部，可以使用追加查询快速完成。在此，将接受添加记录的表称为目的表，将用来添加记录的表称为源表，追加查询只能追加匹配的字段。4.3.6创建追加查询如果需要将一个或多个表中所有记录或符例4.3.4创建一个名为“添加记录”的追加查询，并使用该查询，将“学生”表中“家庭住址”为“广西来宾”的记录添加到“男学生”的表中。步骤如下图所示。例4.3.4创建一个名为“添加记录”的追加查询，并使用该查Access数据库教学课件CHAccess数据库教学课件CH4.3.7创建更新查询如果需要大批量修改数据，或者是输入有规律的数据，可以借助更新查询来完成。例4.3.5创建一个名为“年龄加1”的更新查询，并使用该查询，将“学生”表中每个学生的年龄加1。步骤如下图所示。4.3.7创建更新查询如果需要大批量修改数据，或者是输入有规Access数据库教学课件CH4.3.8创建删除查询使用删除查询，可以将数据库中一些无用的数据批量删除。例4.3.6创建一个名为“删除不及格”的删除查询，并使用该查询，将“选课成绩”表中考试成绩不及格的学生的纪录删除。步骤如下图所示。4.3.8创建删除查询使用删除查询，可以将数据库中一些无用的Access数据库教学课件CH4.3.9创建多表查询创建查询时，如果查询的数据源不在同一个表中。则需要创建多表查询。这是这几个表必须先建立有相应的关系。例4.3.7建立一个名为“学生成绩”的查询，包括“学生编号”、学生的“姓名”、“课程名称”、“平时成绩”和“考试成绩”字段，并分别计算“平时成绩”和“考试成绩”的平均值。4.3.9创建多表查询创建查询时，如果查询的数据源不在同一个4.3.10在查询中计算在实际应用中，经常需要对查询的结果进行计算，例如求和、计数、求最大最小值、求平均值等。1．总计计算例4.3.8创建一个名为“计算”的查询，计算“选课成绩”中“平时成绩”的最小值，“考试成绩”的平均分。步骤如下图所示。4.3.10在查询中计算在实际应用中，经常需要对查询的结果进Access数据库教学课件CHAccess数据库教学课件CH2．分组总计计算例4.3.9在例4.3.8的基础上，创建按学期进行计算，命名为“按学期计算”的查询。步骤如下图所示。2．分组总计计算4．自定义计算字段可以在查询中添加一个自定义的用于计算的新字段。计算字段是指根据一个或多个表中的一个或多个字段并使用表达式建立的新字段。例4.3.10创建一个名为“计算期评成绩”的查询，包含“学生编号”、“姓名”、“平时成绩”和“考试成绩”字段，新建一个“期评成绩”字段，而且期评成绩=平时成绩×0.3+考试成绩×0.7并将期评成绩为60分以上的记录显示出来。步骤如下图所示。4．自定义计算字段图4.3.34查询的设计视图

图4.3.35表达式生成器

图4.3.34查询的设计视图图4.3.35表达式生成器图4.3.36查询设置图4.3.36查询设置图4.3.37“计算期评成绩”的查询结果返回目录图4.3.37“计算期评成绩”的查询结果返回目录4.4创建SQL查询

4.4.1SQL简介SQL(StructuredQueryLanguage)结构化查询语言，是访问关系型数据库的标准语言。JetSQL语句有两种类型：（1）数据操作语言（DML）语句：主要用来检索、添加、修改和删除数据库中的数据。（2）数据定义语言（DDL）语句：主要用来管理数据库中的对象。4.4创建SQL查询

4.4.1SQL简介SQL(Str常用的SQL语句有：（1）SELECT：用于创建查询，检索符合条件的数据。（2）INSERT：在现有表中添加一条或多条记录。（3）UPDATE：修改表中一条或多条记录数据。（4）DELETE：删除表中一条或多条记录。（5）SELECTINTO：将现有表的结构和数据复制到另一个新表中。常用的SQL语句有：4.4.2SELECT语句简介SELECT语句是最常用的SQL语句之一，主要用于创建选择查询、检索数据，SELECT包含多个子句，其语句格式为：SELECT[ALL|DISTINCT|TOPn|TOPnpercent]

[*|<字段名1>，<字段名2>,…]FROM数据源

[WHERE<条件表达式>]

[ORDERBY<字段1>[ASC|DESC][，<字段2>[ASC|DESC][，…]]

[GROUPBY<字段1>[，<字段2>,…]]

[HAVING<条件表达式>]语句格式说明：4.4.2SELECT语句简介其中：ALL：表示相关检索或操作应用于数据源中所有的行，ALL是所列四项中的默认项。DISTINCT：表示对指定的字段，将返回不包含重复项的一条记录。TOPn：表示将返回满足检索条件的前n条记录。TOPnpercent：表示将返回满足检索条件的前n%条记录。*：表示返回的记录包含所有字段的值。FROM子句：用于指定创建查询所用的数据源，可以是表或已有查询，此项为必选项。其中：WHERE子句：用于设置检索满足<条件表达式>的记录集，此<条件表达式>可以是用And和Or运算符连接起来的复杂的条件。ORDERBY子句：用于指定查询结果所依据的排序字段，其中ASC表示按指定字段升序排序，DESC表示按指定字段降序排序，不写ASC或DESC时，默认是ASC。GROUPBY子句：用于设置查询结果是否按指定字段进行分组。HAVING子句：用于限制GROUPBY子句返回的结果，只有上面使用了GROUPBY子句才能够使用HAVING子句，但是使用了GROUPBY子句不一定要使用HAVING子句。WHERE子句：用于设置检索满足<条件表达式>的记录集，此<SELECTFROMGROUPBYORDERBYWHERESQL语句与查询设计器的比较SELECTSQL语句与查询设计器的比较4.4.3使用SELECT语句创建单表选择查询例4.4.1使用“教学管理系统”数据库中“教师”表的数据创建一个SQL选择查询，要求按照工作时间先后顺序检索经济系和计算机系的女教师，查询结果仅显示教师姓名、性别、学历、职称、系别和工作时间。步骤如下图所示。4.4.3使用SELECT语句创建单表选择查询例4.4.1图4.4.1新建查询对话框

图4.4.2关闭显示表对话框

图4.4.1新建查询对话框图4.4.2关闭显示表对图4.4.3选择“查询／ＳＱＬ特定查询／联合”打开联合查询窗口，录入查询语句图4.4.4另存为对话框图4.4.3选择“查询／ＳＱＬ特定查询／联合”打开联合查询图4.4.5例4401-SQL查询结果图4.4.5例4401-SQL查询结果例4.4.2使用“教学管理系统”数据库中“学生”表的数据创建一个SQL选择查询，要求检索来自山东的学生，查询结果要求先按年龄降序排序，再按学生编号升序排序，查询结果中包含“学生编号”、“姓名”、“性别”、“年龄”和“简历”字段内容。SQL如图所示。例4.4.2使用“教学管理系统”数据库中“学生”表的数据创建4.4.4使用SELECT语句创建多表选择查询使用SELECT语句创建多表选择查询时，由于数据源来自多个表，因此要对FROM子句的数据源部分作相应的修改，主要是指明多个表的连接关系。SELECT语句修改成以下格式：4.4.4使用SELECT语句创建多表选择查询使用SELESELECT[ALL|DISTINCT|TOPn|TOPnpercent][*|<字段名1>，<字段名2>,…]FROM数据表或查询[INNERJOIN数据表或查询ON<条件表达式>][WHERE<条件表达式>][ORDERBY字段1[ASC|DESC][，字段2[ASC|DESC][，…]]4.4.4使用SELECT语句创建多表选择查询SELECT[ALL|DISTINCT|TOPn|T例4.4.4使用“教学管理系统”数据库中“课程”表和“选课成绩”表创建一个SQL选择查询，要求统计各门课程期末考试的平均成绩，并以各门课程期末考试的平均成绩降序排序。SQL语句如下图所示：4.4.4使用SELECT语句创建多表选择查询例4.4.4使用“教学管理系统”数据库中“课程”表和“选课说明：（1）本例在SELECT语句后面的字段列表中用到了：AVG(选课成绩.考试成绩)AS平均成绩，表示以“平均成绩”作为AVG(选课成绩.考试成绩)字段的别名，别名只要符合字段的命名规则即可，在查询结果的字段名称行中会显示别名。AVG()是统计平均值的函数。（2）本例在统计各门课程的平均成绩时用到了分组子句：GROUPBY课程.课程名称，其中“课程名称”是分组字段。（3）Round函数可为字段限制小数位数，如：Round(AVG(选课成绩.考试成绩),2)说明：图4.4.11各门课程平均成绩查询结果图4.4.11各门课程平均成绩查询结果例4.4.3使用“教学管理系统”数据库中“学生”表、“课程”表、“选课成绩”表和“教师”表的数据创建一个SQL选择查询，要求显示“学生”表的“学生编号”、“姓名”、“性别”字段，“课程”表的“课程名称”字段，“选课成绩”表的“考试成绩”字段，“教师”表的“姓名”字段。查询结果要求先按课程名称降序排序，再按学生成绩降序排序。SQL语句如下图所示：SELECT学生.学生编号,学生.姓名,学生.性别,课程.课程名称,选课成绩.考试成绩,教师.姓名FROM学生INNERJOIN((教师INNERJOIN课程ON教师.教师编号=课程.任课教师编号)

INNERJOIN选课成绩ON课程.课程编号=选课成绩.课程编号)ON学生.学生编号=选课成绩.学生编号ORDERBY课程.课程名称DESC,选课成绩.考试成绩DESC;例4.4.3使用“教学管理系统”数据库中“学生”表、“课SELECTFROMGROUPBYORDERBYWHERESQL语句与查询设计器的比较INNERJOIN…on……SELECTSQL语句与查询设计器的比较INNERJOIN3．函数函数被用来完成一些特殊的运算，函数由函数名、括号和参数构成，某些函数的括号中可以无参数。如Sum([平时成绩]),在这里Sum是函数名,[平时成绩]是参数，函数的返回值即函数值是平时成绩之和。可以直接使用函数的返回值，也可以将函数的返回值作为条件的比较对象。3．函数在SQL语句中常用的统计函数有：（1）MAX(字段名)：返回指定字段数据的最大值。（2）MIN(字段名)：返回指定字段数据的最小值。（3）COUNT(字段名)：返回指定字段数据中非空数据的个数。（4）COUNT(*)：返回查询结果中总的行数。（5）AVG(字段名)：返回指定字段数据的平均值。（6）SUM(字段名)：返回指定字段数据的总和。在SQL语句中常用的统计函数有：表4.3.2总计项函数的功能分类中文名英文名功能汇总函数合计Sum为每一组中指定的字段进行求和运算平均值Avg为每一组中指定的字段进行求平均值运算最小值Min为每一组中指定的字段进行求最小值最大值Max为每一组中指定的字段进行求最大值计数Count根据指定的字段计算每一组中记录的个数标准差StDev根据指定的字段计算每一组的统计标准差方差Var根据指定的字段计算每一组的统计方差其他总计项第一条记录First找出每一组中首条记录指定字段的值最后一条记录Last找出每一组中最后一条记录指定字段的值表达式Expression用来在设计网格的“字段”行中建立计算字段条件Where限定表中的哪些记录可以参加分组汇总，即指定查询条件分组GroupBy用来指定分组汇总字段表4.3.2总计项函数的功能分类中文名英文名功能汇合计S在SQL中使用统计函数例4.4.5统计“学生”表中的学生人数。SELECTCOUNT（*）AS学生表人数FROM学生例4.4.6统计“选课成绩”表中的考试成绩的最高分。SELECTMAX（考试成绩）AS最高分FROM选课成绩在SQL中使用统计函数例4.4.5统计“学生”表中的学生人例4.4.7统计“选课成绩”表中的考试成绩的最高分、最低分。SELECTMAX（考试成绩）AS最高分,MIN（考试成绩）AS最低分FROM选课成绩例4.4.84408-按学期统计“选课成绩”表中的考试成绩、平时成绩的平均分SELECTAVG(考试成绩)AS考试平均分,AVG(平时成绩)AS平时平均分FROM选课成绩GROUPBY学期;在SQL中使用统计函数例4.4.7统计“选课成绩”表中的考试成绩的最高分、最低分例4.4.9统计“学生”表中男女生的平均年龄SELECT性别,AVG(年龄)AS平均年龄FROM学生GROUPBY性别;例4.4.10统计“学生”表中男女生的平均成绩SELECT学生.性别,AVG(选课成绩.考试成绩)AS考试平均成绩FROM学生INNERJOIN选课成绩ON学生.学生编号=选课成绩.学生编号GROUPBY学生.

性别;在SQL中使用统计函数例4.4.9统计“学生”表中男女生的平均年龄例4.4.1例4.4.11(实验6分组合计1)：设有数据表学生（学号，姓名，性别，班级），用SQL语句建立一个查询，统计并显示出学生表中姓氏在2人以上的姓氏及其人数，假设姓氏都是一个字。在SQL中使用统计函数Selectleft(姓名,1)AS姓氏,count(姓名)AS人数From学生Groupbyleft([姓名],1)Havingcount(*)>=2例4.4.11(实验6分组合计1)：设有数据表学生（例4.4.12(实验6分组合计2)：统计表“01级学生名单”中资环系学生人数超过5人以上的姓氏和人数，并按人数从多到少显示。在SQL中使用统计函数SELECTleft(姓名,1)AS姓氏,count(姓名)AS人数FROM01级学生名单WHERE班级like"资环*"GROUPBYleft(姓名,1)HAVINGcount(姓名)>=5ORDERBYcount(姓名)DESC;例4.4.12(实验6分组合计2)：统计表“01级学生名单”SQL执行顺序当WHERE子句、GROUPBY子句、HAVING子句和ORDERBY子句同时出现时，SQL执行顺序如下：（1）执行WHERE子句，从表中选取满足条件的记录。（2）由GROUPBY子句对选取的记录按指定的列分组。（3）执行函数。（4）执行HAVING子句选取满足条件的分组。（5）执行ORDERBY子句对分组结果排序。SQL执行顺序当WHERE子句、GROUPBY子句、HAV关于查询的几个常见的概念（1）投影查询Select字段名表From表名（2）选择查询Select字段名表From表名Where条件（3）排序查询Select字段名表From表名Orderby字段名关于查询的几个常见的概念（1）投影查询4.5查询中数据的操作略4.5查询中数据的操作略本章小结Access中的查询可以实现对一个数据库中的一个表或多个表中存储的数据进行检索、统计、计算、排序等操作。本章介绍了Access中各种查询：选择查询、参数查询、交叉表查询、操作查询和SQL查询。本章小结Access中的查询可以实现对一个数据库中的一个表或本章结束，谢谢大家！！本章结束，谢谢大家！！Access2003数据库教程

Access2003数据库教程

Access2003数据库教程第1章数据库系统概述第2章Access2003数据库系统的基本概念第3章重要的表操作第4章查询第5章创建实用窗体第6章报表第7章数据访问页的设计第8章宏第9章模块第10章查询综合例题Access2003数据库教程第1章数据库系统概述第4章查询4.1查询简介4.2使用向导创建查询4.３使用查询设计器创建查询4.4创建SQL查询4.5查询中数据的操作第4章查询4.1查询简介4.1查询简介查询以一个表、多个表或者另一查询为基础，创建一个新的数据集作为查询的结果集。查询的结果是动态的，其内容是随着数据源的变化而变化的。可以通过查询设计器和查询设计向导来创建查询。4.1查询简介查询以一个表、多个表或者另一查询为基础，4.1.1Access查询按功能进行分类1．选择查询2．参数查询3．交叉表查询4．操作查询①删除查询②更新查询③追加查询④生成表查询5．SQL查询4.1.1Access查询按功能进行分类1．选择查询4.1.2Access查询按创建查询方式进行分类（1）使用查询设计器（2）使用各种查询向导①简单查询向导②交叉表查询向导③查找重复项查询向导④查找不匹配项查询向导返回目录4.1.2Access查询按创建查询方式进行分类（1）使用4.２使用向导创建查询

4.2.1使用简单查询向导创建查询使用简单查询可以在一个或多个表指定字段中检索数据，还可以对记录组或全部记录进行总计、计数、求平均值、最大值和最小值的运算。4.２使用向导创建查询

4.2.1使用简单查询向导创建查询例4.2.1根据“选课成绩”表，使用简单查询向导创建一个名为“学生的成绩”的查询，包括“学生编号”、“平时成绩”、“考试成绩”三个字段。例4.2.1根据“选课成绩”表，使用简单查询向导创建一个名4.2.2交叉表查询向导例4.2.2根据“选课成绩”表，使用交叉表查询向导创建一个查询，分别将每个学生各学期各课程的分数统计出来。4.2.2交叉表查询向导例4.2.2根据“选课成绩”表4.2.3查找重复项查询向导利用“查找重复项查询向导”可以创建一个查询，用于在同一个表中查找指定字段有相同值的记录。例4.2.3创建一个名为“选相同课程的同学”的查询，查找选择相同课程的同学有多少人。4.2.3查找重复项查询向导利用“查找重复项查询向导”可以4.2.4查找不匹配项查询向导使用“查找不匹配项查询向导”可以在一个表中查找与另一个表中记录不相关的记录。例4.2.4查找在“选课成绩”表中没有选课成绩的学生记录（即查找没有选课的学生）。返回目录4.2.4查找不匹配项查询向导使用“查找不匹配项查询向导”4.３使用查询设计器创建查询

4.3.1查询设计器及其使用1．打开查询设计器，界面如下：4.３使用查询设计器创建查询

4.3.1查询设计器及其使用3.查询设计器的工具栏按钮功能和说明视图每种查询有三种视图，一是设计视图；二是数据表视图，用来显示查询结果集；三是SQL视图，用来通过SQL语句进行查询。本按钮可以在这三种视图之间切换查询类型本按钮可以在选择查询、交叉表查询、生成表查询、更新查询、追加查询和删除查询这六种查询之间切换执行执行查询，将查询结果集以工作表的形式显示出来显示表弹出“显示表”对话框，用户可以从中选择查询所要使用的表和查询。总计在查询设计区中增加“总计”行，用以进行求和、求平均值等统计计算。属性显示当前光标所在位置上的对象的属性：如果光标在查询设计窗口内的数据表/查询显示区内，则将显示查询的属性。如果光标在下部的查询设计区内，则将显示字段的属性。生成器弹出“表达式生成器”对话框，用于生成准则表达式。该按钮只有在光标位于查询设计区的“准则”栏内才有效。数据库窗口返回到数据库窗口。3.查询设计器的工具栏按钮功能和说明视图每种查询有三种视图4.3.2创建选择查询例4.3.1

创建一个名为“女同学”的查询，包括“学生编号”、“姓名”、“性别”字段，将女同学的记录挑选出来，并按“学生编号”从低到高排序。4.3.2创建选择查询例4.3.1创建一个名为“女同学”的4.3.3查询条件和条件表达式1．条件表达式（1）简单条件表达式所在字段条件表达式含义说明性别”男”查询性别为“男”的记录文本值一般应使用双引号括起来，如果没有加上双引号，Access会自动加上双引号大学英语83查询大学英语为83分的记录等效为=83，允许省去等号“=”出生年月#84-1-25#查询1984年1月25日出生者的记录在条件表达式中使用日期时应该用半角的“#”号括起来。如果没有加上“#”号，Access会自动加上双“#”号。4.3.3查询条件和条件表达式1．条件表达式所在字段条件表达（2）含运算符的条件表达式①条件表达式中含有比较运算符比较运算符所在字段条件表达式含义=等于性别=”女”查询女性的记录>大于总分>240查询总分大于240的记录<>不等于大学英语<>0查询大学英语不为0的记录>=大于等于略略<小于略略<=小于等于略略4.3.3查询条件和条件表达式（2）含运算符的条件表达式比较运算符所在字段条件表达式②条件表达式中含有字符串运算符运算符所在字段条件表达式含义Like姓名Like”李*”Like”李？”查询姓李的学生的记录In姓名In(”李梅”,”马威”)查询“姓名”字段值是“李梅”或“马威”的记录（用于指定字符串只能是列出来的值）Betweenand出生年月Between#84-1-1#And#84-12-31#查询出生年月在1984年内的记录（主要用于数字型、货币型和日期型字段）“*”号表示字符串中在该位置可以为任意的若干个字符。“？”号表示字符串中在该位置可以为任意的一个字符。4.3.3查询条件和条件表达式②条件表达式中含有字符串运算符运算符所在字段条件表达式含义L③条件表达式中含有逻辑运算符运算符所在字段条件表达式含义and(与)数学>=60and<85查询数学成绩在60～84之间的记录or(或)数学<60or>85查询数学成绩在60以下或者85以上的记录not性别not"女"查询性别不是“女”的记录4.3.3查询条件和条件表达式③条件表达式中含有逻辑运算符运算符所在字段条件表达式含义an图4.3.5查询的“条件”行和“或”行4.3.3查询条件和条件表达式不同行是”或”图4.3.5查询的“条件”行和“或”行4.3.3查询条件4.3.3查询条件和条件表达式同行”或”要用逻辑运算符4.3.3查询条件和条件表达式同行”或”要用逻辑运算符表达式表达式可以用在表、查询、窗体、报表、数据页和宏等六个对象中。

2．表达式的设置[选课成绩]![平时成绩]*0.3+[选课成绩]![考试成绩]*0.7表名字段名运算符常量4.3.3查询条件和条件表达式表达式2．表达式的设置[选课成绩]![平时成绩]*0.3+[表名必须用一对中括号[]括住外加一感叹号。例如：[选课成绩]!字段名必须用一对中括号[]括住。例如：[平时成绩]字符串必须用一对英文半角单引号或双引号括住。例如："女"日期时间应该用半角的“#”号括起来。例如：#84-1-25#(1)Access表达式的有关规则4.3.3查询条件和条件表达式表名必须用一对中括号[]括住外加一感叹号。(1)Ac（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查询条件和条件表达式（2）函数函数功能说明Year返回值是表示年份的四位整数例如（3）运算符运算符说明+加例如计算[平时成绩]+[考试成绩]-减例如计算[总工资]-[扣款]*乘例如计算[平时成绩]*0.3/除例如计算[总分]/[人数]^乘幂例如计算[订购量]^24.3.3查询条件和条件表达式（3）运算符运算符说明+加例如计算[平时成绩]+[考试成绩例如,以“选课成绩”表为数据源，创建一个名为“学生期评成绩”的报表，如图6.4.1所示，包含学生编号、课程编号、学期、平时成绩、考试成绩和期评成绩，其中，期评成绩=平时成绩×0.3+考试成绩×0.7。直接输入表达式通过表达式生成器创建（4）录入方法4.3.3查询条件和条件表达式例如,以“选课成绩”表为数据源，创建一个名为“学生期评成绩”4.3.4创建参数查询参数查询在运行时会提示用户输入参数值（查询条件），并根据用户的输入给出查询结果，从而可以实现交互式查询。参数查询实质上是把选择查询的“条件”设置成一个带有参数的“可变条件”。4.3.4创建参数查询参数查询在运行时会提示用户输入参数值（例4.3.2创建一个名为“按照学生姓名查询”的查询，当输入不同的学生姓名时，显示相应学生的记录数据。例4.3.2创建一个名为“按照学生姓名查询”的查询，当输入4.3.5创建生成表查询生成表查询是在表或者查询的基础上创建的，在使用时生成表查询，就能够生成一个新“表”。例4.3.3根据“学生”表，创建一个名为“创建男学生表”的生成表查询，并使用该查询生成一个包含“学生编号”、“姓名”、“性别”和“年龄”的名为“男学生”的表。如下图所示。4.3.5创建生成表查询生成表查询是在表或者查询的基础上创建Access数据库教学课件CHAccess数据库教学课件CH4.3.6创建追加查询如果需要将一个或多个表中所有记录或符合一定条件的记录添加到另一个表的尾部，可以使用追加查询快速完成。在此，将接受添加记录的表称为目的表，将用来添加记录的表称为源表，追加查询只能追加匹配的字段。4.3.6创建追加查询如果需要将一个或多个表中所有记录或符例4.3.4创建一个名为“添加记录”的追加查询，并使用该查询，将“学生”表中“家庭住址”为“广西来宾”的记录添加到“男学生”的表中。步骤如下图所示。例4.3.4创建一个名为“添加记录”的追加查询，并使用该查Access数据库教学课件CHAccess数据库教学课件CH4.3.7创建更新查询如果需要大批量修改数据，或者是输入有规律的数据，可以借助更新查询来完成。例4.3.5创建一个名为“年龄加1”的更新查询，并使用该查询，将“学生”表中每个学生的年龄加1。步骤如下图所示。4.3.7创建更新查询如果需要大批量修改数据，或者是输入有规Access数据库教学课件CH4.3.8创建删除查询使用删除查询，可以将数据库中一些无用的数据批量删除。例4.3.6创建一个名为“删除不及格”的删除查询，并使用该查询，将“选课成绩”表中考试成绩不及格的学生的纪录删除。步骤如下图所示。4.3.8创建删除查询使用删除查询，可以将数据库中一些无用的Access数据库教学课件CH4.3.9创建多表查询创建查询时，如果查询的数据源不在同一个表中。则需要创建多表查询。这是这几个表必须先建立有相应的关系。例4.3.7建立一个名为“学生成绩”的查询，包括“学生编号”、学生的“姓名”、“课程名称”、“平时成绩”和“考试成绩”字段，并分别计算“平时成绩”和“考试成绩”的平均值。4.3.9创建多表查询创建查询时，如果查询的数据源不在同一个4.3.10在查询中计算在实际应用中，经常需要对查询的结果进行计算，例如求和、计数、求最大最小值、求平均值等。1．总计计算例4.3.8创建一个名为“计算”的查询，计算“选课成绩”中“平时成绩”的最小值，“考试成绩”的平均分。步骤如下图所示。4.3.10在查询中计算在实际应用中，经常需要对查询的结果进Access数据库教学课件CHAccess数据库教学课件CH2．分组总计计算例4.3.9在例4.3.8的基础上，创建按学期进行计算，命名为“按学期计算”的查询。步骤如下图所示。2．分组总计计算4．自定义计算字段可以在查询中添加一个自定义的用于计算的新字段。计算字段是指根据一个或多个表中的一个或多个字段并使用表达式建立的新字段。例4.3.10创建一个名为“计算期评成绩”的查询，包含“学生编号”、“姓名”、“平时成绩”和“考试成绩”字段，新建一个“期评成绩”字段，而且期评成绩=平时成绩×0.3+考试成绩×0.7并将期评成绩为60分以上的记录显示出来。步骤如下图所示。4．自定义计算字段图4.3.34查询的设计视图

图4.3.35表达式生成器

图4.3.34查询的设计视图图4.3.35表达式生成器图4.3.36查询设置图4.3.36查询设置图4.3.37“计算期评成绩”的查询结果返回目录图4.3.37“计算期评成绩”的查询结果返回目录4.4创建SQL查询

4.4.1SQL简介SQL(StructuredQueryLanguage)结构化查询语言，是访问关系型数据库的标准语言。JetSQL语句有两种类型：（1）数据操作语言（DML）语句：主要用来检索、添加、修改和删除数据库中的数据。（2）数据定义语言（DDL）语句：主要用来管理数据库中的对象。4.4创建SQL查询

4.4.1SQL简介SQL(Str常用的SQL语句有：（1）SELECT：用于创建查询，检索符合条件的数据。（2）INSERT：在现有表中添加一条或多条记录。（3）UPDATE：修改表中一条或多条记录数据。（4）DELETE：删除表中一条或多条记录。（5）SELECTINTO：将现有表的结构和数据复制到另一个新表中。常用的SQL语句有：4.4.2SELECT语句简介SELECT语句是最常用的SQL语句之一，主要用于创建选择查询、检索数据，SELECT包含多个子句，其语句格式为：SELECT[ALL|DISTINCT|TOPn|TOPnpercent]

[*|<字段名1>，<字段名2>,…]FROM数据源

[WHERE<条件表达式>]

[ORDERBY<字段1>[ASC|DESC][，<字段2>[ASC|DESC][，…]]

[GROUPBY<字段1>[，<字段2>,…]]

[HAVING<条件表达式>]语句格式说明：4.4.2SELECT语句简介其中：ALL：表示相关检索或操作应用于数据源中所有的行，ALL是所列四项中的默认项。DISTINCT：表示对指定的字段，将返回不包含重复项的一条记录。TOPn：表示将返回满足检索条件的前n条记录。TOPnpercent：表示将返回满足检索条件的前n%条记录。*：表示返回的记录包含所有字段的值。FROM子句：用于指定创建查询所用的数据源，可以是表或已有查询，此项为必选项。其中：WHERE子句：用于设置检索满足<条件表达式>的记录集，此<条件表达式>可以是用And和Or运算符连接起来的复杂的条件。ORDERBY子句：用于指定查询结果所依据的排序字段，其中ASC表示按指定字段升序排序，DESC表示按指定字段降序排序，不写ASC或DESC时，默认是ASC。GROUPBY子句：用于设置查询结果是否按指定字段进行分组。HAVING子句：用于限制GROUPBY子句返回的结果，只有上面使用了GROUPBY子句才能够使用HAVING子句，但是使用了GROUPBY子句不一定要使用HAVING子句。WHERE子句：用于设置检索满足<条件表达式>的记录集，此<SELECTFROMGROUPBYORDERBYWHERESQL语句与查询设计器的比较SELECTSQL语句与查询设计器的比较4.4.3使用SELECT语句创建单表选择查询例4.4.1使用“教学管理系统”数据库中“教师”表的数据创建一个SQL选择查询，要求按照工作时间先后顺序检索经济系和计算机系的女教师，查询结果仅显示教师姓名、性别、学历、职称、系别和工作时间。步骤如下图所示。4.4.3使用SELECT语句创建单表选择查询例4.4.1图4.4.1新建查询对话框

图4.4.2关闭显示表对话框

图4.4.1新建查询对话框图4.4.2关闭显示表对图4.4.3选择“查询／ＳＱＬ特定查询／联合”打开联合查询窗口，录入查询语句图4.4.4另存为对话框图4.4.3选择“查询／ＳＱＬ特定查询／联合”打开联合查询图4.4.5例4401-SQL查询结果图4.4.5例4401-SQL查询结果例4.4.2使用“教学管理系统”数据库中“学生”表的数据创建一个SQL选择查询，要求检索来自山东的学生，查询结果要求先按年龄降序排序，再按学生编号升序排序，查询结果中包含“学生编号”、“姓名”、“性别”、“年龄”和“简历”字段内容。SQL如图所示。例4.4.2使用“教学管理系统”数据库中“学生”表的数据创建4.4.4使用SELECT语句创建多表选择查询使用SELECT语句创建多表选择查询时，由于数据源来自多个表，因此要对FROM子句的数据源部分作相应的修改，主要是指明多个表的连接关系。SELECT语句修改成以下格式：4.4.4使用SELECT语句创建多表选择查询使用SELESELECT[ALL|DISTINCT|TOPn|TOPnpercent][*|<字段名1>，<字段名2>,…]FROM数据表或查询[INNERJOIN数据表或查询ON<条件表达式>][WHERE<条件表达式>][ORDERBY字段1[ASC|DESC][，字段2[ASC|DESC][，…]]4.4.4使用SELECT语句创建多表选择查询SELECT[ALL|DISTINCT|TOPn|T例4.4.4使用“教学管理系统”数据库中“课程”表和“选课成绩”表创建一个SQL选择查询，要求统计各门课程期末考试的平均成绩，并以各门课程期末考试的平均成绩降序排序。SQL语句如下图所示：4.4.4使用SELECT语句创建多表选择查询例4.4.4使用“教学管理系统”数据库中“课程”表和“选课说明：（1）本例在SELECT语句后面的字段列表中用到了：AVG(选课成绩.考试成绩)AS平均成绩，表示以“平均成绩”作为AVG(选课成绩.考试成绩)字段的别名，别名只要符合字段的命名规则即可，在查询结果的字段名称行中会显示别名。AVG()是统计平均值的函数。（2）本例在统计各门课程的平均成绩时用到了分组子句：GROUPBY课程.课程名称，其中“课程名称”是分组字段。（3）Round函数可为字段限制小数位数，如：Round(AVG(选课成绩.考试成绩),2)说明：图4.4.11各门课程平均成绩查询结果图4.4.11各门课程平均成绩查询结果例4.4.3使用“教学管理系统”数据库中“学生”表、“课程”表、“选课成绩”表和“教师”表的数据创建一个SQL选择查询，要求显示“学生”表的“学生编号”、“姓名”、“性别”字段，“课程”表的“课程名称”字段，“选课成绩”表的“考试成绩”字段，“教师”表的“姓名”字段。查询结果要求先按课程名称降序排序，再按学生成绩降序排序。SQL语句如下图所示：SELECT学生.学生编号,学生.姓名,学生.性别,课程.课程名称,选课成绩.考试成绩,教师.姓名FROM学生INNERJOIN((教师INNERJOIN课程ON教师.教师编号=课程.任课教师编号)

INNERJOIN选课成绩ON课程.课程编号=选课成绩.课程编号)ON学生.学生编号=选课成绩.学生编号ORDERBY课程.课程名称DESC,选课成绩.考试成绩DESC;例4.4.3使用“教学管理系统”数据库中“学生”表、“课SELECTFROMGROUPBYORDERBYWHERESQL语句与查询设计器的比较INNERJOIN…on……SELECTSQL语句与查询设计器的比较INNERJOIN3．函数函数被用来完成一些特殊的运算，函数由函数名、括号和参数构成，某些函数的括号中可以无参数。如Sum([平时成绩]),在这里Sum是函数名,[平时成绩]是参数，函数的返回值即函数值是平时成绩之和。可以直接使用函数的返回值，也可以将函数的返回值作为条件的比较对象。3．函数在SQL语句中常用的统计函数有：（1）MAX(字段名)：返回指定字段数据的最大值。（2）MIN(字段名)：返回指定字段数据的最小值。（3）COUNT(字段名)：返回指定字段数据中非空数据的个数。（4）COUNT(*)：返回查询结果中总的行数。（5）AVG(字段名)：返回指定字段数据的平均值。（6）SUM(字段名)：返回指定字段数