Access查询设计汇总课件

第四章查询第四章查询【本章主要掌握内容】查询的基本概念查询的功能与分类查询的创建与设计SQL查询【本章主要掌握内容】第一节查询的概述第一节

在设计数据库时，常常把数据分类，并分别存放在多个表中，但在使用时需要检索一个或多个表中符合条件的数据。查询实际上就是将这些分散的数据再集中起来。xhxmxb1001张三男………………xhkcdhcj10010185………………kcdhkcmc01数据库应用…………学生表成绩表课程表xmkcmccj张三数据库应用85………………查询结果在设计数据库时，常常把数据分类，并分别存放在多个表中一、查询的概念查询是根据一定的条件，从一个或多个表中提取数据并进行加工处理，返回一个新的数据集合。利用查询可以实现数据的统计分析与计算等操作。查询结果可以作为其他查询、窗体、报表的数据源。查询是一张“虚表”，是动态的数据集合。一、查询的概念查询主要有以下几个方面的功能1）选择字段和记录选择字段：在查询的结果中可以只含有表中的部分字段。查询主要有以下几个方面的功能选择记录：在查询的结果中，只含有符合指定条件的记录。2）编辑记录即利用查询添加、修改和删除表中的记录。3）统计和计算即在查询中进行各种统计计算，也可以建立一个计算字段来保存计算的结果。4）建立新表利用查询的结果创建一个新表。5）为其他数据库对象提供数据源即以查询的结果作为查询、窗体或报表的数据源。选择记录：在查询的结果中，只含有符合指定条件的记录。2）编辑二、查询的种类在Access2010中，可以创建5种类型的查询：1、选择查询是根据指定的条件，从一个或多个表中获取数据并显示结果。选择查询可以对记录进行分组，并且对分组的记录进行求和、计数、求平均值以及其它类型的计算。选择查询产生的结果是一个动态的记录集，不会改变源数据表中的数据。二、查询的种类2、交叉表查询交叉表查询是对基表或查询中的数据进行计算和重构，以方便分析数据。能够汇总数字型字段的值，将汇总计算的结果显示在行与列交叉的单元格中。2、交叉表查询3、参数查询是一种特殊的选择查询，即根据用户输入的参数作为查询的条件。输入不同的参数，将得到不同的结果。执行参数查询时，将会显示一个对话框，以提示输入参数信息。参数查询可作为窗体和报表的基础。3、参数查询4、操作查询查询除了按指定的条件从数据源中检索记录外，还可以对检索的记录进行编辑操作。操作查询可以分为：删除查询从一个或多个表中删除一组符合条件的记录。更新查询对一个或多个表中的一组符合条件的记录进行批量修改某字段的值。4、操作查询追加查询将一个或多个表中的一组符合条件的记录添加到另一个表的末尾。生成表查询将查询的结果转存为新表。追加查询5、SQL查询SQL（StructuredQueryLanguage）是一种结构化查询语言，是数据库操作的工业化标准语言，使用SQL语言可以对任何数据库管理系统进行操作。所谓的SQL查询就是通过SQL语言来创建的查询。在查询设计视图中创建任何一个查询时，系统都将在后台构建等效的SQL语句。大多数查询功能也都可以直接使用SQL语句来实现。有一些无法在查询设计视图中创建的SQL查询称为“SQL特定查询”。5、SQL查询SQL特定查询包括：联合查询

联合查询是将多个表或查询中的字段合并到查询结果的一个字段中。

使用联合查询可以合并多个表中的数据，并可以根据联合查询生成一个新表。传递查询

传递查询可以直接将命令发送到ODBC数据库服务器中，而不需要事先建立链接。

利用传递查询可以直接使用其它数据库管理系统中的表。P154SQL特定查询包括：P154数据定义查询利用数据定义查询可以创建、删除或更改表，或者在数据库表中创建索引。子查询是包含在另一个查询之内的SQL-SELECT语句，即嵌套在查询中的查询。数据定义查询几点说明：创建传递查询、数据定义查询和联合查询不能在设计视图中创建，必须直接在SQL视图中输入相应的SQL语句。创建子查询可以直接在SQL视图中输入相应的SQL语句，或在设计视图的【字段】或【条件】行中输入SQL语句，即将子查询作为查询的条件。几点说明：选择查询参数查询交叉表查询操作查询

SQL查询查询类型☆删除查询 ☆更新查询☆追加查询☆生成表查询☆联合查询 ☆传递查询☆数据定义查询☆子查询选择查询查询类型☆删除查询 ☆联合查询 三、查询视图

查询有5种视图，分别是：

设计视图

数据表视图

SQL视图

数据透视表视图

数据透视图视图三、查询视图设计视图

即为查询设计器，通过该视图可能创建除SQL之外的各种类型的查询。数据表视图

是查询的数据浏览器，用于查看查询运行的结果。SQL视图

是查看和编辑SQL语句的窗口，用于查看和编辑用查询设计器创建的查询所产生的SQL语句。数据透视表视图和数据图视图

在此两种视图中，可以根据需要生成数据透视表或数据透视图，从而得到直观的数据分析结果。设计视图四、创建查询的方法（界面方法）创建查询（界面方法）有2种方法：

使用查询设计视图创建查询

使用查询向导创建查询四、创建查询的方法（界面方法）1、使用查询设计视图创建查询

使用查询设计视图创建查询首先要打开查询的设计视图窗口，然后根据需要进行查询的定义。★操作步骤：①打开数据库②在【创建】选项卡的【查询】组中单击【查询设计】按钮，打开查询设计器窗口③在查询设计器窗口中定义查询④保存查询1、使用查询设计视图创建查询

查询的设计视图分为上下两部分：上半部分用于显示查询的数据源（包括它们之间的关系）下半部分是定义查询的表格，表格的列对应查询中的一个字段；行对应字段的属性或要求。☆字段 查询结果中所显示的字段☆表

查询数据源，即字段的来源☆排序 查询结果中相应字段的排序方式☆显示 设置在数据表视图中是否显示该字段☆条件

查询条件（同行之间为“与”关系）☆或

查询条件（不同行之间为“或”关系）查询的设计视图分为上下两部分：2、使用查询向导创建查询Access2010提供了4种类型的查询向导：

简单查询向导

交叉表查询向导

查找重复项查询向导

查找不匹配项查询向导2、使用查询向导创建查询使用查询向导创建查询几点说明：

不能对记录进行筛选和排序

不能改变查询中字段的顺序

允许对数值字段进行汇总统计

允许按指定日期范围分组汇总

汇总查询必须包含且只能包含汇总统计的数值型字段和用于汇总依据的分组字段。其它字段的添加将无法得到期望的汇总效果使用查询向导创建查询几点说明：汇总查询必须包含且只能五、运行查询

查询创建完成后，将保存于数据库中。运行查询后，才能看到查询的结果。通过以下方法之一，可以运行查询：

▲在查询的设计视图下，单击【查询工具/设计】选项卡【结果】组中的【运行】按钮

▲在查询的设计视图下，单击【查询工具/设计】选项卡【结果】组中的【视图】按钮

▲右击查询的设计视图标题栏，并在快捷菜单中选择【数据表视图】命令

▲双击导航窗口中要运行的查询

▲右击导航窗口中要运行的查询，并在快捷菜单中选择【打开】命令五、运行查询第二节选择查询第二节

选择查询是最常用的查询类型，它是根据用户所指定的查询条件，从一个或多个数据源表中获取数据并显示结果。

选择查询可以对记录进行分组，并进行各种统计，如：求和、计数、求平均值等。

选择查询产生的结果是一个动态的记录集，不会改变数据源中的数据。选择查询是最常用的查询类型，它是根据用户所指定的查询一、基于单数据源表的查询

设计单数据源表查询时，主要的操作是：

确定查询的数据源（表或已建立的查询）

确定查询中要显示的字段或表达式一、基于单数据源表的查询

如果查询的数据源是两个或两个以上的表（或查询），则在设计查询时需要创建数据源之间的连接关系。

如果相关联的表已经按照公共字段创建了索引，则在查询视图中可以看到作为数据源的表（或查询）之间已经通过相关字段建立了连接。

如果相关联的表未按照公共字段创建索引，则需要在相关表之间建立连接。二、基于多数据源表的查询如果查询的数据源是两个或两个以上的表（或查询），则在

Access有3种连接类型：

内连接

左连接

右连接Access有3种连接类型：内连接（INNERJOIN）

只包含来自两张表的联接字段相等的记录。Xs.xh=Cj.xh内连接（INNERJOIN）Xs.xh=Cj.xh左连接（LEFTJOIN）

包括左表中所有记录和右表中联接字段相等的记录。Xs.xh=Cj.xh左连接（LEFTJOIN）Xs.xh=Cj.xh右连接包括右表中所有记录和左表中联接字段相等的记录。Xs.xh=Cj.xh右连接Xs.xh=Cj.xh注意：如果进行多表查询时，所使用的表或查询之间没有建立连接关系，则查询将以笛卡尔积的形式，即左表中的每一条记录均与右表所有记录合并，形成一条记录。也就是说，会在查询结果中产生大量的数据，而这样的结果是没有任何意义的。注意：双击表的关系连线可以编辑表之间的联接属性，包括：联接的字段和连接的类型。内连接左连接右连接双击表的关系连线可以编辑表之间的联接属性，包括：联接三、条件查询

在实际的查询中，经常需要查询满足某个条件的记录。带条件的查询需要通过设置查询条件来实现。查询条件是运算符、常量、字段值、函数以及字段名和属性等任意组合的关系表达式，其运算结果是一个逻辑值。三、条件查询（1）运算符算术运算符【例】125+100/5^26*2/(2+1)

单价*0.8算术运算符说明算术运算符说明+加/除-减^乘方*乘（1）运算符【例】算术运算符说明算术运算符说关系运算符关系运算符说明关系运算符说明=等于<>不等于<小于<=小于等于>大于>=大于等于☆关系运算符主要用于数据之间的比较，其运算的结果是一个逻辑值：True或False。☆各关系运算符的运算级别相同。【例】3>8 xb="男"5+2<=5*2 xm>="张"

工作日期<出生日期P117关系运算符关系运算符说明关系运算符说明=等逻辑运算符逻辑运算符说明Not当Not连接的表达式为真时，整个表达式的值为假And当And连接的表达式均为真时，整个表达式的值为真，否则为假Or当Or连接的表达式均为假时，整个表达式的值为假，否则为真☆逻辑运算符主要用于多个条件的判断，其运算结果仍然是一个逻辑值。☆逻辑运算符运算级别依次为：Not、And、Or逻辑运算符逻辑运算符说明Not当Not

逻辑运算符的真值表如下所示：XYNotXXAndYXOrYTrueTrueTrueFalseFalseTrueFalseFalseFalseTrueTrueFalseFalseTrueTrueFalseTrueTrueFalseFalse逻辑运算符的真值表如下所示：XYNotXX特殊运算符特殊运算符与比较运算相关，也是根据是否符合运算符的限定条件返回逻辑值True或False。特殊运算符说明In用于指定一个字段值的列表，列表中的任意一个值都可与查询的字段相匹配Between用于指定一个字段值的范围。指定的范围之间用And连接Like用于指定查找文本字段的字符模式。在所定义的字符模式中，可以使用统配符“?”、“*”、“#”、“[]”IsNull用于指定一个字段为空IsNotNull用于指定一个字段为非空特殊运算符特殊运算符说明In用于指定一几点说明：☆所谓的Null是指该字段中没有输入任何值。☆当在文本字段中输入了空字符串后，表中也无任何显示，但该字段并不是Null值。☆Access提供的通配符有：

? 表示任意一个字符

* 表示任意多个字符

# 表示任意一个数字位

[字符表] 表示在字符表中的单一字符

[!字符表] 表示不在字符表中的单一字符方括号内字符表中的各个字符间用“,”隔开字符表可以设置一个范围，用连字符“-”分隔范围的下界和上界。如：[1-5]、[!a-d]几点说明：方括号内字符表中的各个字符间用“,”隔开字符串运算符字符串运算符具有连接字符串的功能。字符串运算符说明+两边的操作数必须都是字符型&两边的操作数可以是字符型或数值型在进行连接操作前先进行操作数类型的转换，即转换为字符型字符串运算符字符串运算符说明+两边的操【例】"ABC"+"123""ABC"&123.4#2011-10-01#&12"123"+50.4"ABC"+123.4#2011-10-01#+20100+200100&200ABC123ABC123.42011-10-0112173.4出错2011-10-21300100200【例】ABC123ABC123.42011-10-01121

在各类运算符混合运算的表达式中，各运算符的运算级别有高低之分：字符串运算符关系运算符【例】

NOT5>12/6AND"A"+"B"<"A"+"C"NOT5>2AND"AB"<"AC"NOT.T.AND.T..F.AND.T..F.数值运算符逻辑运算符在各类运算符混合运算的表达式中，各运算符的运算级别有【例】用文字表示使以下条件表达式为“真”的条件：学生表中：①

性别=-1And系名="信息工程学院"信息工程学院中所有的男生②

性别=-1Or系名="信息工程学院"所有的男生以及信息工程学院中所有的学生教师表中：③

性别=0And职称="教授"Or民族<>"汉族"女性教授以及非汉族教师④

性别=0And(职称="教授"Or民族<>"汉族")女性教授以及女性非汉族教师【例】用文字表示使以下条件表达式为“真”的条件：（2）条件查询举例

在查询的设计视图中，查询条件应使用查询定义窗口中的条件选项来设置，即在相应的字段的【条件】文本框中输入条件。▲条件表达式中通常省略字段名。▲有多个条件，且涉及不同的字段时，则分别设置相应字段的条件。▲同行的条件之间是“与”关系；不同行的条件之间是“或”关系（2）条件查询举例【例】查找每名学生“体育”课程的成绩，要求显示“学号”、“姓名”、“课程名称”和“成绩”4个字段。【例】查找每名学生“体育”课程的成绩，要求显示“学号”、“姓SELECT学生表.XH,学生表.XM,课程表.KCMC,成绩表.CJFROM(学生表INNERJOIN成绩表ON

学生表.XH=成绩表.XH)INNERJOIN课程表ON

成绩表.KCDM=课程表.KCDMWHERE(((课程表.KCMC)="体育"));说明：WHERE子句

用于指定查询的条件SELECT学生表.XH,学生表.XM,课程表.KCM【例】查找成绩在70至95分之间（含70和95分）的女生，以及成绩大于等于80分的男生。要求显示“姓名”、“性别”、“课程名称”、“成绩”4个字段并按性别升序、成绩降序排序。【例】查找成绩在70至95分之间（含70和95分）的女生，以SELECT学生表.XM,学生表.XB,课程表.KCMC,成绩表.CJFROM(学生表INNERJOIN成绩表ON

学生表.XH=成绩表.XH)INNERJOIN课程表ON

成绩表.KCDM=课程表.KCDMWHERE(((学生表.XB)="女")AND((成绩表.CJ)>=70And(成绩表.CJ)<=95))OR(((学生表.XB)="男")AND((成绩表.CJ)>=80))ORDERBY学生表.XB,成绩表.CJDESC;ORDERBY子句

指定查询结果的排序依据

DESC 降序排序

ASC 升序排序（默认排序）SELECT学生表.XM,学生表.XB,课程表.KCM【例】查找没有选课学生的课程名称。要求输出：课程代码和课程名称。【分析】①没有选课学生的课程，即意味着，该门课程在成绩表中没有记录。②因此，必须kc表和cj表连接时采用左连接类型。【例】查找没有选课学生的课程名称。要求输出：课程代码和课程名SELECT课程表.KCDM,课程表.KCMC,成绩表.CJFROM课程表LEFTJOIN成绩表ON

课程表.KCDM=成绩表.KCDMWHERE(((成绩表.CJ)IsNull));SELECT课程表.KCDM,课程表.KCMC,成绩表【例】查找成绩表中成绩最高的10名学生。要求输出：姓名、学号、课程名称和成绩。【分析】即：将成绩按降序排序，且只显示前10条记录。【例】查找成绩表中成绩最高的10名学生。要求输出：姓名、学号SELECTTOP10学生表.XH,学生表.XM,课程表.KCMC,成绩表.CJFROM(学生表INNERJOIN成绩表ON学生表.XH=成绩表.XH)INNERJOIN课程表ON成绩表.KCDM=课程表.KCDMORDERBY成绩表.CJDESC;SELECTTOP10学生表.XH,学生表.XM,【例】查找有补考学生的课程代码和课程名称（相同课程只显示一次）。【分析】即：该门课程的成绩有小于60分的记录。【例】查找有补考学生的课程代码和课程名称（相同课程只显示一次SELECTDISTINCTROW

课程表.KCDM,课程表.KCMCFROM课程表INNERJOIN成绩表ON课程表.KCDM=成绩表.KCDMWHERE(((成绩表.CJ)<60));SELECTDISTINCTROW课程表.KCDM,课四、查询中的计算和统计

在设计查询时，不仅可以进行条件的设置，还可以进行计算和分类汇总（计数、求和、求平均值等）。（1）表达式

表达式是用运算符、常量、字段值、函数以及字段名和属性等连接起来的一个式子。系统提供了算术运算、关系运算、字符运算和逻辑运算等4种基本运算表达式。四、查询中的计算和统计（2）系统函数

函数是一个预先定义（系统预先定义或用户自定义）的程序模块。标准函数（系统预定义）自定义函数（用户自定义）系统提供的标准函数包括：数值函数、字符函数、日期时间函数和聚合函数等。P126（2）系统函数P126求和函数格式：Sum(<字符串表达式>)功能：返回字段中值的总和返回值数据类型：数值型说明：字符串表达式可以是一个字段名，也可以是一个含字段名的表达式，但所含的字段必须是数值型字段。【例】Sum(Cj.cj)Sum(单价*数量)Sum((单价-进价)*数量)聚合函数求和函数【例】聚合函数求平均函数格式：Avg(<字符串表达式>)功能：返回字段中值的平均值返回值数据类型：数值型说明：☆字符串表达式可以是一个字段名，也可以是一个含字段名的表达式，但所含的字段必须是数值型字段。☆Avg函数不计算任何Null值字段【例】Avg(Cj.cj)Avg(单价)Avg(应发工资-扣款)求平均函数【例】统计记录个数函数格式：Count(<字符串表达式>)功能：统计记录个数返回值数据类型：数值型说明：☆字符串表达式可以是一个字段名，也可以是一个含字段名的表达式（该字段不一定是数值型）。☆使用格式Count(<字符串表达式>)时，系统将统计指定字段值不为Null值的记录的个数；而使用格式Count(*)时，系统将统计所有记录的个数，包括含Null值字段的所有记录个数。【例】Sum(Cj.cj)/Count(Cj.cj)

单价*Count(单价)统计记录个数函数【例】最大、最小值函数格式：Max(<字符表达式>)Min(<字符表达式>)功能：返回一组指定字段中的最大、最小值说明：字符表达式可以是一个字段名，也可以是一个含字段名的表达式，（该字段并不一定是数值型）。【例】

Max(Cj.cj)Min(出生日期)最大、最小值函数【例】（3）查询中的计算功能举例▲预定义计算利用设计视图网格的【总计】行进行各种统计▲自定义计算

创建计算字段进行任意类型的计算当需要统计的数据在表中没有相应的字段，或者用于计算的数据值来源于多个字段时，应在查询中使用计算字段。计算字段是指根据一个或多个字段使用表达式建立的新字段（查询中的显示字段）。创建计算字段是在查询设计视图的【字段】行中直接输入计算表达式。（3）查询中的计算功能举例当需要统计的数据在表中没有相应的【例】统计各门课程的成绩。要求输出“课程名称”、“总分”、“平均分”、“最高分”和“最低分”。【分析】

本题输出的字段中含有4个统计字段，因此将利用设计视图网格的中【总计】行进行统计。

【总计】行的打开方式：单击【查询工具/设计】选项卡【显示/隐藏】组中的【汇总】按钮。▲预定义计算【例】统计各门课程的成绩。要求输出“课程名称”、“总分”、“【总计】下拉列表框中含以下各选项：

合计

平均值

最小值

最大值

计数

Var（方差）

StDev（标准差）(方差的平方根就是标准差)【总计】下拉列表框中含以下各选项：

GroupBy 定义要执行计算的组

First 求在表或查询中第一条记录的 字段值

Last 求在表或查询中最后一条记录 的字段值

Expression 在字段中自定义计算公式

Where 指定用于分组的字段的条件GroupBy 定义要执行计算的组【例】统计各门课程的平均分。要求输出“课程名称”、“平均分”、“最高分”和“最低分”。可以为显示的各个字段分别设置其属性。【例】统计各门课程的平均分。要求输出“课程名称”、“平均分”SELECT课程表.KCMC,Sum(成绩表.CJ)AS总分,Avg(成绩表.CJ)AS平均分,Min(成绩表.CJ)AS最低分,Max(成绩表.CJ)AS最高分FROM课程表INNERJOIN成绩表ON课程表.KCDM=成绩表.KCDMGROUPBY课程表.KCMC;SELECT课程表.KCMC,Sum(成绩表.CJ)A当查询的输出字段含有聚合函数时，通常情况下应设置分组字段。所谓分组字段即为：进行数据统计时的分组依据。当查询中含有统计字段，而某一字段既不是统计字段，也不是分组字段，其值是无意义的。【总计】行设为【Where】的字段不能出现在查询的结果中。当查询的输出字段含有聚合函数时，通常情况下应设置分组字段。【例】查询数学系平均成绩在75分以上，且各门课程的成绩不得低于60分的学生的学号、姓名、平均分和最低分，并按照平均分从高到低排列（平均分精确到1位小数）。【例】查询数学系平均成绩在75分以上，且各门课程的成绩不得低SELECT学生表.XH,学生表.XM,Avg(成绩表.CJ)AS平均分,Min(成绩表.CJ)AS最低分FROM院系代码表INNERJOIN(学生表INNERJOIN成绩表ON学生表.XH=成绩表.XH)ON院系代码表.YXZYDM=学生表.YXZYDMWHERE(((院系代码表.YXMC)="数学系"))GROUPBY学生表.XH,学生表.XMHAVING(((Avg(成绩表.CJ))>75)AND((Min(成绩表.CJ))>60))ORDERBYAvg(成绩表.CJ)DESC;SELECT学生表.XH,学生表.XM,Avg(成绩表WHERE与HAVING的区别：▲WHERE子句是从数据源中提取数据时，对记录的筛选。▲HAVING子句必须位于GROUPBY子句之后，是对统计结果的筛选。▲HAVING子句中通常应该含有聚合函数，否则应该使用WHERE子句。WHERE与HAVING的区别：▲自定义计算

在设计视图中创建新的计算字段，并将表达式输入到字段行中，表达式可以由多个计算组成。【例】统计学生表中不同姓氏的学生的人数，输出字段为：姓氏和人数，并且按人数降序排序。（假设姓氏为姓名的第1个汉字）。【分析】

本题中分组依据为姓氏的第1个字，并且作为输出的字段。为此需要创建一个新的计算字段，该字段使用了字符串截取函数Left（）。▲自定义计算▲

Left()函数格式：Left（String,Length）功能：从字符表达式String左侧的第一个字符开始，截取由Length的值所指定的若干个字符返回值数据类型：字符型说明：☆String为被截取的字符串，当其值为Null时，则返回Null值。☆Length为返回的字符数。其值必须为正，若Length的值为小数，则四舍五入至整数值后即为返回的字符个数；若Length的值为负数，则出错。☆Length的值为0时，则返回空字符串；Length的值大于String的长度时，则返回全部字符。字符串截取函数▲Left()函数字符串截取函数▲Right()函数格式：Right（String，Length）功能：从字符表达式String右侧的第一个字符开始，截取由Length的值所指定的若干个字符返回值数据类型：字符型字符串截取函数▲Right()函数字符串截取函数▲Mid()函数格式：Mid（String,Start[

,Length]）功能：从字符表达式String返回一个子字符串返回值数据类型：字符型说明：☆Start为长整型，用于指定所取的字符串在String中的开始位置；☆

Length为长整型，指定要返回的字符个数，缺省时指所取字符串直至String的最后一个字符；☆Start的值大于指定字符串的长度，则返回一个空字符串；Start的值为0或负数，则均出错；☆Length的值大于所能取的字符数目，则取至最后一个字符。字符串截取函数▲Mid()函数字符串截取函数【例】

Left("ABCD中国",8-5)

ABC

Left("ABCD中国",0)

（空字符串）

Left("ABCD中国",8)

ABCD中国

Right("ABCD中国",4)

CD中国

Right("ABCD中国",2.4)

中国字符串截取函数【例】字符串截取函数【例】

Mid("ABCD中国",3,8)

CD中国

Mid("ABCD中国",12,2)

（空字符串）

Mid("ABCD中国",0,4)

出错

Mid("ABCD中国",3,-1)

出错字符串截取函数【例】字符串截取函数【例】统计学生表中不同姓氏的学生的人数，输出字段为：姓氏和人数，并且按人数降序排序。（假设姓氏为姓名的第1个汉字）。【例】统计学生表中不同姓氏的学生的人数，输出字段为：姓氏和人SELECTLeft([xm],1)AS姓氏,Count(学生表.XH)AS人数FROM学生表GROUPBYLeft([xm],1)ORDERBYCount(学生表.XH)DESC;SELECTLeft([xm],1)AS姓氏,Cou【例】统计学生表中“王”姓和“李”姓的学生人数，输出字段为：姓氏和人数，并按姓氏升序排序。【例】统计学生表中“王”姓和“李”姓的学生人数，输出字段为：SELECTLeft([xm],1)AS姓氏,Count(学生表.XH)AS人数FROM学生表GROUPBYLeft([xm],1)HAVING(((Left([xm],1))="王"))OR(((Left([xm],1))="张"))ORDERBYLeft([xm],1);SELECTLeft([xm],1)AS姓氏,Cou【例】统计学生表中不同年龄的学生人数。输出时按年龄升序排序。【分析】

年龄计算方法：当前的年份–出生日期的年份因此在创建的计算字段，需要使用相关的日期函数。【例】统计学生表中不同年龄的学生人数。输出时按年龄升序排序。截取日期分量函数格式： Year（Date）

Month（Date）

Day（Date）功能：分别返回日期表达式年份、月份和日期的整数。返回值数据类型：整型说明：☆Date为任何能够表示日期的表达式（日期或字符表达式等）。☆参数中若略去年份，则返回系统日期的年份。☆参数中若含有Null值，则返回Null。

相类似的函数有：Hour()函数、Minute()函数和Second()函数。日期函数截取日期分量函数相类似的函数有：Hour()【例】

Year(#2011-10-01#)

2011Year(Null)

NullYear("Oct,15")

（系统日期的年份）

Month("2012-09-20")

9Day(3-2)

31Access将1899年12月31日作为数值1。日期函数【例】Access将1899年12月31日作为数值1获取系统日期/时间函数格式： Date（

）

Time（）

Now（）功能：分别返回当前系统日期、当前系统时间和当前系统日期和时间。返回值数据类型：日期型

返回的日期格式由操作系统设置的日期格式决定。日期函数获取系统日期/时间函数返回的日期格式由操作系统【例】统计学生表中不同年龄的学生人数。输出时按年龄升序排序。【例】统计学生表中不同年龄的学生人数。输出时按年龄升序排序。SELECTYear(Date())-Year([csrq])AS年龄,Count(学生表.XH)AS人数FROM学生表GROUPBYYear(Date())-Year([csrq])ORDERBYYear(Date())-Year([csrq]);SELECTYear(Date())-Year([csrq【例】统计各院系女生人数及所占比例。要求输出：院系名称、女生人数、女生比例；输出时按女生比例降序排序，比例相同时则按院系名称升序排序（女生比例保留1位小数）。【分析】

由于需要统计女生所占的比例，因此不能用Where子句进行性别筛选。本题需要用到一个条件函数IIf（）。【例】统计各院系女生人数及所占比例。要求输出：院系名称、女生▲IIf()函数格式：IIF（Expression，Truepart，Falsepart）功能：根据Expression的值，返回Truepart和Falsepart两个值中的一个。说明：

当逻辑表达式Expression的值为真时，返回Truepart的值，否则返回Falsepart的值。【例】根据成绩输出相应的分数等级：不及格、及格和优秀。IIf(cj.cj>=90,

"优秀",_ IIf(cj.cj<60,"不及格","及格"))▲IIf()函数【例】统计各院系女生人数及所占比例。要求输出：院系名称、女生人数、女生比例；输出时按女生比例降序排序，比例相同时则按院系名称升序排序（女生比例保留1位小数）。【例】统计各院系女生人数及所占比例。要求输出：院系名称、女生SELECT院系代码表.YXMC,Sum(IIf([xb]="女",1,0))AS女生人数,Sum(IIf([xb]="女",1,0))/Count([xh])AS女生所占比例FROM院系代码表INNERJOIN学生表ON院系代码表.YXZYDM=学生表.YXZYDMGROUPBY院系代码表.YXMCORDERBYSum(IIf([xb]="女",1,0))/Count([xh])DESC,院系代码表.YXMC;SELECT院系代码表.YXMC,Sum(IIf([xb第三节交叉表查询第三节交叉表查询是以行和列的字段作为标题和条件选取数据，并在行与列的交叉处对数据进行汇总和统计计算。交叉表查询是以行和列的字段作为标题和条件选取数据，并设计交叉表查询需要字义3种字段：

处于数据表最左端的行标题字段把某一字段或相关数据放入指定的一行中。

处于数据表最上边的列标题字段对每一列指定的字段进行统计，并将统计结果放在该列。

处于行和列交叉位置的值字段可以为该字段指定总计项。在交叉表查询中：只能指定一个列字段和一个总计类型的字段。可以通过设置一个或多个行标题，创建多级交叉表查询。设计交叉表查询需要字义3种字段：在交叉表查询中：一、使用向导创建交叉表查询【例】基于xsb表创建一个交叉表查询，要求统计各班的男女生人数。★操作步骤：①单击【创建】选项卡【查询】组中的【查询向导】按钮，并在对话框中选择【交叉表查询向导】②按向导提示依次确定数据源、行字段、列字段、汇总项及是否包括各行小计③为查询命名并保存所设计的查询一、使用向导创建交叉表查询

交叉表查询向导的数据源只能来自于一张表或查询。若来自多个表，可先建立一个查询，而后以此查询作为数据源使用向导创建，或者直接在设计视图下创建。行标题最多3个，列标题1个，值字段1个。交叉表查询向导的数据源只能来自于一张表或查询。若TRANSFORMCount(xsb.[XH])ASXH之计数SELECTxsb.[BJBH]FROMxsbGROUPBYxsb.[BJBH]PIVOTxsb.[XB];TRANSFORMCount(xsb.[XH])ASX二、在设计视图下创建交叉表查询使用设计视图可创建基于多表的交叉表查询。【例】以交叉表的形式查询每个学生的各门课程成绩及平均分。要求：输出时平均分保留一位小数。

在查询的设计视图下，单击【查询工具/设计】选项卡【查询类型】组中【交叉表】按钮，可以在设计网格中增加【交叉表】行。二、在设计视图下创建交叉表查询在查询的设计视图下，单。★操作步骤：①打开查询设计视图，并添加数据源及显示字段②单击【查询工具/设计】选项卡【查询类型】组中【交叉表】按钮，使查询类型为交叉表查询③在【交叉表】行上设置：行字段、列字段④在【交叉表】行上设置：值字段（将该字段的【总计】行改为：第一条记录）⑤添加汇总字段并设置汇总函数（将该字段的【交叉表】行设置为：行标题）。Access查询设计汇总课件行标题列标题值行标题列标题值TRANSFORMFirst(成绩表.CJ)ASCJ之FirstSELECT学生表.XM,Avg(成绩表.CJ)AS平均分FROM(学生表INNERJOIN成绩表ON学生表.XH=成绩表.XH)INNERJOIN课程表ON成绩表.KCDM=课程表.KCDMGROUPBY学生表.XMPIVOT

课程表.KCMC;TRANSFORMFirst(成绩表.CJ)ASCJ之如果在【交叉表】行中，设置某个字段的选项为：“值

”，则在【总计】行中可以有多种选择。如果获取的是单一数据，则应该选择“First”或“Last”。ORDERBY子句中不能含有聚合函数。如果在【交叉表】行中，设置某个字段的选项为：“值”，则创建交叉表查询时，使用交叉表查询向导和设计视图的比较：☆数据源来自于一个表或查询时，使用交叉表查询向导创建；☆数据源来自于多个表或查询时，使用设计视图创建；☆如果【行标题】或【列标题】需要建立新字段得到，则使用设计视图创建。创建交叉表查询时，使用交叉表查询向导和设计视图的比较：第四节参数查询第四节

所谓参数查询是在运行查询的过程中，系统提示用户输入参数，并根据参数的值来检索符合条件的记录。在Access中，可以建立单参数查询，也可以建立多参数查询。

参数查询是一个特殊的选择查询，具有较大的灵活性，常作为窗体、报表的数据源。所谓参数查询是在运行查询的过程中，系统提示用户输入参一、单参数查询【例】设计一个能够查询指定课程代码的学生成绩查询，要求输出：学号、姓名、课程代码、课程名称、成绩。（运行时课程代码分别取：60001、60011）★操作步骤：①打开查询的设计视图②添加数据源③设置显示字段④在作为参数字段的【条件】行上输入提示文本（注：提示文本需要用方括号括起来）一、单参数查询Access查询设计汇总课件SELECT学生表.XH,学生表.XM,课程表.KCMC,课程表.KCDM,成绩表.CJFROM学生表INNERJOIN(课程表INNERJOIN成绩表ON课程表.KCDM=成绩表.KCDM)ON学生表.XH=成绩表.XHWHERE课程表.KCDM=[请输入需要查询的课程代码：];SELECT学生表.XH,学生表.XM,课程表.KCM二、多参数查询【例】设计一个能够查询指定课程代码、指定班级编号的学生成绩查询，要求输出：学号、姓名、班级编号、课程代码、课程名称、成绩。（运行时课程代码同上例，班级编号取：04020）二、多参数查询SELECT学生表.XH,学生表.XM,成绩表.KCDM,课程表.KCMC,成绩表.CJFROM学生表INNERJOIN(课程表INNERJOIN成绩表ON课程表.KCDM=成绩表.KCDM)ON学生表.XH=成绩表.XHWHERE

成绩表.KCDM=[请输入课程代码：]AND学生表.BJBH=[请输入班级编号：];SELECT学生表.XH,学生表.XM,成绩表.KCD【例】查询指定省份学生的信息（jg字段的前两个字符为该学生所属省份）。输出字段为：省份、jg、xm、xb和yxmc，查询结果先按jg升序排列，jg相同时按xm降序排列。【例】查询指定省份学生的信息（jg字段的前两个字符为该学生所SELECTLeft([jg],2)AS省份,学生表.JG,学生表.XM,学生表.XB,院系代码表.ZYMCFROM院系代码表INNERJOIN学生表ON院系代码表.YXZYDM=学生表.YXZYDMWHERELeft([jg],2)=[请输入省份:]ORDERBY学生表.JG,学生表.XMDESC;SELECTLeft([jg],2)AS省份,学生表【例】查询指定城市（jg字段中的第3、4个字符）男女学生的人数，要求输出字段为：城市名、性别和人数。【例】查询指定城市（jg字段中的第3、4个字符）男女学生的人SELECTMid([jg],3,2)AS城市名,学生表.XB,Count(学生表.XB)AS人数FROM学生表GROUPBYMid([jg],3,2),学生表.XBHAVINGMid([jg],3,2)=[城市名:];Access查询设计汇总课件第五节操作查询第五节

操作查询是在选择查询的基础上创建的，可以对表中符合符合条件的记录进行追加、修改、删除和生成新表操作。操作查询包括：追加查询更新查询删除查询生成表查询操作查询是在选择查询的基础上创建的，可以对表中符合符一、删除查询删除查询是从表中删除与查询结果中相对应的记录。★操作步骤：①打开查询的设计视图②添加数据源③更改查询的类型为删除查询④设置删除的条件查询运行后将从原表中永久删除指定的记录，且无法恢复！为避免误删除，可以先预览查询的结果或对原数据表进行备份。删除查询删除的是整个记录，而不是指定的字段或字段中的数据。一、删除查询查询运行后将从原表中永久删除指定的记录，且无法【例】

将“成绩表”表中成绩小于60分的记录删除。（成绩表）【例】将“成绩表”表中成绩小于60分的记录删除。（成绩表）DELETE成绩表.*,成绩表.CJFROM成绩表WHERE成绩表.CJ<60;DELETE成绩表.*,成绩表.CJ【例】从cj表中删除姓名为“史建平”的记录。【分析】

此删除查询涉及两张表：学生表和成绩表。删除的是成绩表表中的记录；条件设置来自于学生表。若删除查询的数据源来自多表，则：删除的只能是子表中的记录；主表中的字段用于条件设置；与两表之间是否建立参照完整性规则无关。【例】从cj表中删除姓名为“史建平”的记录。若删除查询的数据设计视图中的【删除】行：☆From 指定删除记录的表☆Where 指定删除的条件设计视图中的【删除】行：DELETE

学生表.XM,成绩表.*FROM学生表INNERJOIN成绩表ON学生表.XH=成绩表.XHWHERE学生表.XM="史建平";DELETE学生表.XM,成绩表.*二、更新查询更新查询是根据查询的结果，对一个或多个表中相关记录的字段值进行更新。

更新查询的最大优势在于：用相同的表达式批量修改多条记录的字段值。二、更新查询★操作步骤：

●打开查询的设计视图

●添加数据源

●选择查询类型

●设置更新的字段、字段的值及更新的条件【例】

计算教师工资表中的住房补贴（应是基本工资和综合津贴之和的5%。(工资表)UPDATE工资表SET工资表.ZFBT=([工资表]![JBGZ]+[工资表]![ZHJT])*0.05;

更新的字段可以是一个或多个。★操作步骤：【例】UPDATE工资表SET工资表.ZFAccess查询设计汇总课件三、追加查询追加查询是把查询的结果添加到另一个表的末尾（查询的数据源可以是一个或多个表）。【例】

将学生表中“性别”为“男”的记录追加到学生ACCESS表中。（学生表）★操作步骤：①打开查询的设计视图，并添加数据源②更改查询类型及设置目标表③设置追加字段及追加条件三、追加查询【例】★操作步骤：目标表必须是已经存在的表。查询输出和目标表对应字段的字段名必须相同。查询输出和目标表对应字段的数据类型如果不相同，系统将先进行类型转换；如果不能进行类型转换，则将出错。目标表必须是已经存在的表。INSERTINTO学生ACCESS(XH,XM,XB)SELECT学生表.XH,学生表.XM,学生表.XBFROM学生表WHERE学生表.XB="男";INSERTINTO学生ACCESS(XH,XM,四、生成表查询生成表查询是对一个或多个表中的记录进行检索，将查询的结果创建新表。将成绩在90分以上学生的基本信息存储到90分以上的学生信息表中（多条相同记录只保存一条）。（学生表和成绩表）【例】★操作步骤：①打开查询的设计视图②添加数据源③更改查询类型并命名新表的表名④选择新表中的字段及设置查询的条件四、生成表查询将成绩在90分以上学生的基本信息存储到90分以切换到数据表视图可预览新建的表。运行查询后，才创建新表（创建新表前，系统将有提示）。切换到数据表视图可预览新建的表。SELECTDISTINCTROW

学生表.*INTO90分以上的学生信息FROM学生表INNERJOIN成绩表ON学生表.XH=成绩表.XHWHERE成绩表.CJ=90;SELECTDISTINCTROW学生表.*INTO

操作查询不仅选择表中数据，还对表中数据进行修改。因此，为了避免因误操作引起的不必要的改变，在数据库窗口中的每个操作查询图标之后显示一个感叹号，以引起注意。操作查询不仅选择表中数据，还对表中数据进行修改。因此第六节使用向导创建查询第六节一、查找重复项查询向导

数据表中除设置为主键的字段不能有重复的值外，其它的各个字段均允许有重复的值。查找重复项查询即从表中查找具有相同字段值的记录。一、查找重复项查询向导【例】查找学生表中班级编号和民族代码均相同的记录。要求输出：xh、xm、bjbh和mzdm。★操作步骤：①打开查找重复项查询向导（创建新查询）②选择数据源③选取设为重复值的字段④可以选择另外查询的字段【例】查找学生表中班级编号和民族代码均相同的记录。要求输出：Access查询设计汇总课件SELECT学生表.bjbh,学生表.MZDM,学生表.XH,学生表.XMFROM学生表WHERE(((学生表.bjbh)In(SELECT[bjbh]FROM[学生表]AsTmpGROUPBY[bjbh],[MZDM]HAVINGCount(*)>1And[MZDM]=[学生表].[MZDM])))ORDERBY学生表.bjbh,学生表.MZDM;SELECT学生表.bjbh,学生表.MZDM,学生表二、查找不匹配项查询向导

不匹配项查询就是在一个表中搜索另一个表中没有相关记录的行。【例】查询没有选课的课程信息★操作步骤：①打开查找不匹配项查询向导（创建新查询）②选择参考表③选择不匹配表④选择两表的联接字段二、查找不匹配项查询向导Access查询设计汇总课件SELECT课程表.KCDM,课程表.KCMCFROM课程表LEFTJOIN成绩表ON课程表.[KCDM]=成绩表.[KCDM]WHERE成绩表.KCDMIsNull;SELECT课程表.KCDM,课程表.KCMC第七节SQL查询第七节SQL查询SQL结构化查询语言是标准的关系型数据语言。查询的本质是一条SQL语句，即每个查询都对应着一条SQL查询命令。保存查询时，保存的是一条SQL语句，而不是查询的结果。创建查询，既可以在其设计视图中实现，也可以在其SQL视图中直接输入SQL语句实现。某些特殊的查询不能在设计视图中进行，只能通过SQL语句才能实现。SQL结构化查询语言是标准的关系型数据语言。SQL（结构化查询语言）是一种数据库查询语言，它的功能包括：

☆数据定义

☆数据查询

☆数据操纵

☆数据控制SQL（结构化查询语言）是一种数据库查询语言，它的功一、SQL语言的特点

高度的综合

高度非过程化

交互式与嵌入式相结合

语言简洁，易学易用一、SQL语言的特点二、SQL语言的数据定义功能

SQL语言的数据定义功能包括定义表、定义视图、定义索引。即对表、视图和索引的创建、修改和删除。数据定义的CREATE语句是定义基本表的结构、定义视图和定义索引。SQL语句用途CREATETABLE创建表ALTERTABLE在已有表中添加新字段或约束DROPTABLE从数据库中删除表，或者从字段或字段组中删除索引CREATEINDEX为字段或字段组创建索引DROPINDEX删除指定索引二、SQL语言的数据定义功能SQL语句用途CREAT1、定义基本表语法格式：CREATETABLE<表名>(<字段名1><数据类型>[<宽度>][<字段级约束条件>][,<字段名2><数据类型>[<宽度>][<字段级约束条件>]…][,CONSTRAINT<记录级约束条件>])功能：创建表的结构说明：字段名不能缺省；数据类型使用指定的英语单词；部分数据类型有其固定的长度，不需要设置。

注意教材P150该命令的语法格式。1、定义基本表注意教材P150该命令的语法格式。CONSTRAINT子句用于设置记录级完整性约束条件。完整性约束条件有两类：☆字段级完整性约束条件☆记录级完整性约束条件

如果约束条件涉及到多个字段时，则必须用CONSTRAINT定义为记录级约束条件如果单字段既可以定义为字段级约束条件，也可以使用CONSTRAINT定义为记录级约束条件。CONSTRAINT子句用于设置记录级完整性约束条件。完整性约束是定义相关字段间的约束条件。约束条件有以下几种设置：

☆NotNull

字段不能为空值。

☆PrimaryKey 设置主键。设置单个字段为主 键时，应该将该字设置为字段

级约束条件。

☆Unique 创建唯一索引。语法格式：

CONSTRAINT

索引名

PrimaryKey(主键字段1[,主键字段2,…])|

索引名Unique(索引字段1[,索引字段2,…])注意：单字段可以放在字段后，不需要CONSTRAINT完整性约束是定义相关字段间的约束条件。约束条件有以下几种设【例】创建xs表，结构如下表所示：字段名数据类型字段宽度空值支持xh文本型10

主键xm文本型8否xb是否型是csrq日期时间是zpOLE对象型是jl备注型是【例】创建xs表，结构如下表所示：字段名数据类型字段宽度空值CREATETABLExs(xhChar(10)PrimaryKey,xmChar(8)NotNull,xbLogical,csrqDate,zpGeneral,jlMemo)CREATETABLExs【例】创建cj表，结构如下表所示：

并将xh+kcdm设置为主键，索引名为xhkcdm。CREATETABLEcj(xhString(10),kcdmChar(6),cjSmallIntNotNull,

CONSTRAINTxhkcdmPrimaryKey(xh,kcdm))字段名数据类型字段宽度空值支持xh文本型10

kcdm文本型6cj整型否【例】创建cj表，结构如下表所示：并将xh+kc【例】创建ts表，结构如下表所示：

并将isbn+gch设置为唯一索引，索引标识abc字段名数据类型字段宽度空值支持ID自动编号

isbn文本型20sm文本型20否rkrq日期型dj单精度型gch文本型15【例】创建ts表，结构如下表所示：并将isbn+CREATETABLEts(IDCounter,isbnChar(20),smString(20),rkrqDate,cbsString(20),djSingle,gchChar(15),

CONSTRAINTabcUnique(isbn,gch))CREATETABLEts2、修改基本表

ALTERTABLE用于修改已经建立的表的结构。修改基本表的结构包括：增加字段和字段级完整性约束条件、修改原有字段的定义、删除原有的字段和完整性约束条件等。语法格式一：（增加字段）ALTERTABLE<表名>

ADD<新字段名><数据类型>[<大小>][<约束条件>][,……]功能：增加字段或索引2、修改基本表【例】在xs表中增加一个字段，字段名：byxx数据类型：文本型字段大小：20。ALTERTABLExsADDbyxxChar(20)【例】在ts表中增加一个由字段isbn和gch组成的唯一索引，索引名：isbngch。ALTERTABLEtsADD

CONSTRAINTisbngchUNIQUE(isbn,gch)【例】在xs表中增加一个字段，字段名：byxx数据类语法格式二：（删除字段或删除索引）ALTERTABLE<表名>DROP[<字段名>[,……]][CONSTRAINT<索引名称>]功能：删除字段或索引语法格式二：（删除字段或删除索引）【例】删除xs表中的byxx和rxrq字段。ALTERTABLExsDROPbyxx,rxrq【例】删除ts表中的唯一索引isbngch。ALTERTABLEtsDROPCONSTRAINTisbngch【例】删除xs表中的byxx和rxrq字段。语法格式三：（修改字段）ALTERTABLE<表名>

ALTER<字段名><数据类型>[<大小>]功能：修改字段的数据类型、大小和约束条件。使用ALTER语句对表的结构进行修改时，一次只能修改一个字段。思考：

如何对字段重命名？语法格式三：（修改字段）使用ALTER语句对表的结构进行【例】将xs表中xb字段的数据类型修改为字符型且只含一个字符。ALTERTABLExsALTERxbChar(1)【例】将ts表中isbn字段的数据类型修改为可含20个字符的文本型，且字段不支持空值、是表的主键。ALTERTABLEtsALTERisbnChar(20)NotNullPrimaryKey【例】将xs表中xb字段的数据类型修改为字符型且只含一个字符3、删除表删除表使用DROPTABLE语句。语法格式：DROPTABLE<表名>功能：删除指定的表说明：基本表一旦删除，表中的数据、索引都将自动被删除，而建立在此表上的查询虽然仍然存在，但已无法使用。【例】删除数据库中的x