Access查询设计汇总课件

,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,第四章 查 询,第四章 查 询,【,本章主要掌握内容,】,查询的基本概念,查询的功能与分类,查询的创建与设计,SQL,查询,【本章主要掌握内容】,第 一 节,查 询 的 概 述,第 一 节,在设计数据库时，常常把数据分类，并分别存放在多个表中，但在使用时需要检索一个或多个表中符合条件的数据。,查询实际上就是将这些分散的数据再集中起来。,xh,xm,xb,1001,张三,男,xh,kcdh,cj,1001,01,85,kcdh,kcmc,01,数据库应用,学生表,成绩表,课程表,xm,kcmc,cj,张三,数据库应用,85,查询结果,在设计数据库时，常常把数据分类，并分别存放在多个表中,一、查询的概念,查询是根据一定的条件，从一个或多个表中,提取数据,并进行加工处理，返回一个新的数据集合。,利用查询可以实现数据的统计分析与计算等操作。,查询结果可以作为其他查询、窗体、报表的,数据源,。,查询是一张,“,虚表,”，,是,动态的,数据集合。,一、查询的概念,查询主要有以下几个方面的功能,1,）,选择字段和记录,选择字段：在查询的结果中可以只含有表中的部分字段。,查询主要有以下几个方面的功能,选择记录：在查询的结果中，只含有符合指定条件的记录。,2,）,编辑记录,即利用查询添加、修改和删除表中的记录。,3,）,统计和,计算,即在查询中进行各种统计计算，也可以建立一个计算字段来保存计算的结果。,4,）,建立新表,利用查询的结果创建一个新表。,5,）,为其他数据库对象提供数据源,即以查询的结果作为查询、窗体或报表的数据源。,选择记录：在查询的结果中，只含有符合指定条件的记录。2）编辑,二、查询的种类,在,Access 2010,中，可以创建,5,种类型的查询：,1,、选择查询,是根据指定的条件，从一个或多个表中获取数据并显示结果。,选择查询可以对记录进行分组，并且对分组的记录进行求和、计数、求平均值以及其它类型的计算。,选择查询产生的结果是一个动态的记录集，不会改变源数据表中的数据。,二、查询的种类,2,、交叉表查询,交叉表查询是对基表或查询中的数据进行计算和重构，以方便分析数据。,能够汇总数字型字段的值，将汇总计算的结果显示在行与列交叉的单元格中。,2、交叉表查询,3,、参数查询,是一种特殊的选择查询，即根据用户输入的参数作为查询的条件。输入不同的参数，将得到不同的结果。,执行参数查询时，将会显示一个对话框，以提示输入参数信息。,参数查询可作为窗体和报表的基础。,3、参数查询,4,、操作查询,查询除了按指定的条件从数据源中检索记录外，还可以对检索的记录进行编辑操作。,操作查询可以分为：,删除查询,从一个或多个表中删除一组符合条件的记录。,更新查询,对一个或多个表中的一组符合条件的记录进行批量修改某字段的值。,4、操作查询,追加查询,将一个或多个表中的一组符合条件的记录添加到另一个表的末尾。,生成表查询,将查询的结果转存为新表。,追加查询,5,、,SQL,查询,SQL,（,Structured Query Language,）是一种结构化查询语言，是数据库操作的工业化标准语言，使用,SQL,语言可以对任何数据库管理系统进行操作。,所谓的,SQL,查询就是,通过,SQL,语言来创建的查询。,在查询设计视图中创建任何一个查询时，系统都将在后台构建等效的,SQL,语句。大多数查询功能也都可以直接使用,SQL,语句来实现。,有一些无法在查询设计视图中创建的,SQL,查询称为“,SQL,特定查询,”。,5、SQL查询,SQL,特定查询包括：,联合查询,联合查询是,将多个表或查询中的字段合并到查询结果的一个字段中。,使用联合查询可以合并多个表中的数据，并可以根据联合查询生成一个新表,。,传递查询,传递查询可以,直接将命令发送到,ODBC,数据库服务器中，而不需要事先建立链接。,利用传递查询可以直接使用其它数据库管理系统中的表。,P154,SQL特定查询包括：P154,数据定义查询,利用数据定义查询可以创建、删除或更改表，或者在数据库表中创建索引。,子查询,是包含在另一个查询之内的,SQL-SELECT,语句，即嵌套在查询中的查询。,数据定义查询,几点说明：,创建,传递查询、数据定义查询和联合查询不能,在,设计视图,中创建，必须,直接在,SQL,视图,中输入相应的,SQL,语句。,创建子查询可以直接在,SQL,视图中输入相应的,SQL,语句，或在设计视图的,【,字段,】,或,【,条件,】,行中输入,SQL,语句，即将子查询作为查询的条件,。,几点说明：,选择查询,参数查询,交叉表查询,操作查询,SQL,查询,查询类型,删除查询,更新查询,追加查询,生成表查询,联合查询,传递查询,数据定义查询,子查询,选择查询查询类型删除查询联合查询,三、查询视图,查询有,5,种视图，分别是：,设计视图,数据表视图,SQL,视图,数据透视表视图,数据透视图视图,三、查询视图,设计视图,即为查询设计器，通过该视图可能创建除,SQL,之外的各种类型的查询。,数据表视图,是查询的数据浏览器，用于查看查询运行的结果。,SQL,视图,是查看和编辑,SQL,语句的窗口，用于查看和编辑用查询设计器创建的查询所产生的,SQL,语句。,数据透视表视图和数据图视图,在此两种视图中，可以根据需要生成数据透视表或数据透视图，从而得到直观的数据分析结果。,设计视图,四、创建查询的方法（界面方法）,创建查询,（界面方法）,有,2,种方法：,使用查询设计视图创建查询,使用查询向导创建查询,四、创建查询的方法（界面方法）,1,、使用查询设计视图创建查询,使用查询设计视图创建查询首先要打开查询的设计视图窗口，然后根据需要进行查询的定义。,操作步骤：,打开数据库,在,【,创建,】,选项卡的,【,查询,】,组中单击,【,查询设计,】,按钮，打开查询设计器窗口,在查询设计器窗口中定义查询,保存查询,1、使用查询设计视图创建查询,查询的设计视图分为上下两部分：,上半部分,用于显示查询的数据源（包括它们之间的关系）,下半部分,是定义查询的表格，表格的,列对应查询中的一个字段,；,行对应字段的属性或要求,。,字段查询结果中所显示的字段,表,查询数据源，即字段的来源,排序查询结果中相应字段的排序方式,显示设置在数据表视图中是否显示该字段,条件,查询条件（同行之间为,“,与,”,关系）,或,查询条件（不同行之间为,“,或,”,关系）,查询的设计视图分为上下两部分：,2,、使用查询向导创建查询,Access 2010,提供了,4,种类型的查询向导：,简单查询向导,交叉表查询向导,查找重复项查询向导,查找不匹配项查询向导,2、使用查询向导创建查询,使用查询向导创建查询几点说明：,不能对记录进行筛选和排序,不能改变查询中字段的顺序,允许对数值字段进行汇总统计,允许按指定日期范围分组汇总,汇总查询必须包含且只能包含,汇总统计的数值型字段,和用于汇总依据的,分组字段,。其它字段的添加将无法得到期望的汇总效果,使用查询向导创建查询几点说明： 汇总查询必须包含且只能,五、运行查询,查询创建完成后，将保存于数据库中。运行查询后，才能看到查询的结果。通过以下方法之一，可以运行查询：,在查询的设计视图下，单击,【,查询工具,/,设计,】,选项卡,【,结果,】,组中的,【,运行,】,按钮,在查询的设计视图下，单击,【,查询工具,/,设计,】,选项卡,【,结果,】,组中的,【,视图,】,按钮,右击查询的设计视图标题栏，并在快捷菜单中选择,【,数据表视图,】,命令,双击导航窗口中要运行的查询,右击导航窗口中要运行的查询，并在快捷菜单中选择,【,打开,】,命令,五、运行查询,第 二 节,选 择 查 询,第 二 节,选择,查询是,最常用,的查询类型，它是根据用户所指定的查询条件，从一个或多个数据源表中获取数据并显示结果。,选择查询可以对记录进行分组，并进行各种统计，如：求和、计数、求平均值等。,选择查询产生的结果是一个动态的记录集，不会改变数据源中的数据。,选择查询是最常用的查询类型，它是根据用户所指定的查询,一、基于单数据源表的查询,设计,单数据源表,查询时，主要的操作是：,确定查询的数据源（表或已建立的查询）,确定查询中要显示的字段或表达式,一、基于单数据源表的查询,如果查询的数据源是两个或两个以上的表（或查询），则在设计查询时需要创建数据源之间的连接关系。,如果相关联的表已经按照,公共字段,创建了索引，则在查询视图中可以看到作为数据源的表（或查询）之间已经通过相关字段建立了连接。,如果相关联的表未按照公共字段创建索引，则需要在相关表之间建立连接。,二 、基于多数据源表的查询,如果查询的数据源是两个或两个以上的表（或查询），则在,Access,有,3,种连接类型：,内连接,左连接,右连接,Access有3种连接类型：,内连接（,INNER JOIN,）,只包含来自两张表的联接字段相等的记录,。,Xs.xh=Cj.xh,内连接（INNER JOIN）Xs.xh=Cj.xh,左连接（,LEFT JOIN,）,包括,左表中,所有记录,和右表中联接字段相等的记录。,Xs.xh=Cj.xh,左连接（LEFT JOIN）Xs.xh=Cj.xh,右连接,包括,右表中,所有记录,和左表中联接字段相等的记录。,Xs.xh=Cj.xh,右连接Xs.xh=Cj.xh,注意：,如果进行多表查询时，所使用的表或查询之间没有建立连接关系，则查询将以,笛卡尔积,的形式，即,左表中的每一条记录均与右表所有记录合并,，形成一条记录,。也就是说，会在查询结果中产生大量的数据，而这样的结果是没有任何意义的。,注意：,双击表的关系连线可以编辑表之间的联接属性，包括：联接的字段和连接的类型。,内连接,左连接,右连接,双击表的关系连线可以编辑表之间的联接属性，包括：联接,三、条件查询,在实际的查询中，经常需要查询满足某个条件的记录。,带条件的查询需要通过设置查询条件来实现。查询条件是,运算符,、,常量,、,字段值,、,函数,以及字段名和属性等任意组合的关系表达式，其,运算结果是一个逻辑值,。,三、条件查询,（1）,运算符,算术运算符,【,例,】,125 + 100 / 5,2,6 * 2 / ( 2 + 1 ),单价 *,0,.,8,算术运算符,说 明,算术运算符,说 明,+,加,/,除,-,减,乘方,*,乘,（1）运算符【例】算术运算符说 明算术运算符说,关系运算符,关系运算符,说 明,关系运算符,说 明,=,等于,不等于,小于,大于, =,大于等于,关系运算符主要用于数据之间的比较，其运算的结果是一个逻辑值：,True,或,False,。,各关系运算符的运算级别相同。,【,例,】,3 8xb = ,男,5 + 2 = ,张,工作日期, 12/6 AND A + B 2 AND AB =70 And (,成绩表,.CJ)=80),ORDER BY,学生表,.XB,成绩表,.CJ DESC;,ORDER BY,子句,指定查询结果的排序依据,DESC,降序排序,ASC,升序排序（默认排序）,SELECT 学生表.XM, 学生表.XB, 课程表.KCM,【,例,】,查找没有选课学生的课程名称。要求输出：课程代码和课程名称。,【,分析,】,没有选课学生的课程，即意味着，该门课程在成绩表中没有记录。,因此，必须,kc,表和,cj,表连接时采用左连接类型。,【例】查找没有选课学生的课程名称。要求输出：课程代码和课程名,SELECT,课程表,.KCDM,课程表,.KCMC,成绩表,.CJ,FROM,课程表,LEFT JOIN,成绩表,ON,课程表,.KCDM =,成绩表,.KCDM,WHERE,(,成绩表,.CJ),Is Null,);,SELECT 课程表.KCDM, 课程表.KCMC, 成绩表,【,例,】,查找成绩表中成绩最高的,10,名学生。要求输出：姓名、学号、课程名称和成绩。,【,分析,】,即：将成绩按降序排序，且只显示前,10,条记录。,【例】查找成绩表中成绩最高的10名学生。要求输出：姓名、学号,SELECT,TOP 10,学生表,.XH,学生表,.XM,课程表,.KCMC,成绩表,.CJ,FROM (,学生表,INNER JOIN,成绩表,ON,学生表,.XH =,成绩表,.XH) INNER JOIN,课程表,ON,成绩表,.KCDM =,课程表,.KCDM,ORDER BY,成绩表,.CJ,DESC,;,SELECT TOP 10 学生表.XH, 学生表.XM,【,例,】,查找有补考学生的课程代码和课程名称（相同课程只显示一次）。,【,分析,】,即：该门课程的成绩有小于,60,分的记录。,【例】查找有补考学生的课程代码和课程名称（相同课程只显示一次,SELECT,DISTINCTROW,课程表,.KCDM,课程表,.KCMC,FROM,课程表,INNER JOIN,成绩表,ON,课程表,.KCDM =,成绩表,.KCDM,WHERE (,成绩表,.CJ)60);,SELECT DISTINCTROW 课程表.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,（标准差）,(,方差的平方根就是标准差,),【总计】下拉列表框中含以下各选项：,Group By,定义要执行计算的,组,First,求在表或查询中第一条记录的,字段值,Last,求在表或查询中最后一条记录,的字段值,Expression,在字段中自定义计算公式,Where,指定用于分组的字段的条件,Group By定义要执行计算的组,【,例,】,统计各门课程的平均分。要求输出,“,课程名称,”,、,“,平均分,”、“,最高分,”,和,“,最低分,”,。,可以为显示的各个字段分别设置其属性。,【例】统计各门课程的平均分。要求输出“课程名称”、“平均分”,SELECT,课程表,.KCMC, Sum(,成绩表,.CJ),AS,总分, Avg(,成绩表,.CJ),AS,平均分, Min(,成绩表,.CJ),AS,最低分, Max(,成绩表,.CJ),AS,最高分,FROM,课程表,INNER JOIN,成绩表,ON,课程表,.KCDM =,成绩表,.KCDM,GROUP BY,课程表,.KCMC;,SELECT 课程表.KCMC, Sum(成绩表.CJ) A,当查询的输出字段含有聚合函数时，通常情况下应设置分组字段。所谓分组字段即为：,进行数据统计时的分组依据,。,当查询中含有统计字段，而某一字段既不是统计字段，也不是分组字段，其值是无意义的。,【,总计,】,行设为,【Where】,的字段不能出现在查询的结果中。,当查询的输出字段含有聚合函数时，通常情况下应设置分组字段。,【,例,】,查询数学系平均成绩在,75,分以上，且各门课程的成绩不得低于,60,分的学生的学号、姓名、平均分和最低分，并按照平均分从高到低排列（平均分精确到,1,位小数）。,【例】查询数学系平均成绩在75分以上，且各门课程的成绩不得低,SELECT,学生表,.XH,学生表,.XM, Avg(,成绩表,.CJ) AS,平均分, Min(,成绩表,.CJ) AS,最低分,FROM,院系代码表,INNER JOIN (,学生表,INNER JOIN,成绩表,ON,学生表,.XH =,成绩表,.XH) ON,院系代码表,.YXZYDM =,学生表,.YXZYDM,WHERE (,院系代码表,.YXMC)=,数学系,),GROUP BY,学生表,.XH,学生表,.XM,HAVING,(Avg(,成绩表,.CJ)75) AND (Min(,成绩表,.CJ)60),ORDER BY Avg(,成绩表,.CJ) DESC;,SELECT 学生表.XH, 学生表.XM, Avg(成绩表,WHERE,与,HAVING,的区别：,WHERE,子句是从数据源中提取数据时，对记录的筛选。,HAVING,子句必须位于,GROUP BY,子句之后，是对统计结果的筛选。,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,个汉字）。,【例】统计学生表中不同姓氏的学生的人数，输出字段为：姓氏和人,SELECT,Left(xm,1),AS,姓氏, Count(,学生表,.XH) AS,人数,FROM,学生表,GROUP BY Left(xm,1),ORDER BY Count(,学生表,.XH) DESC;,SELECT Left(xm,1) AS 姓氏, Cou,【,例,】,统计学生表中,“,王,”,姓和,“,李,”,姓的学生人数，输出字段为：姓氏和人数，并按姓氏升序排序。,【例】统计学生表中“王”姓和“李”姓的学生人数，输出字段为：,SELECT Left(xm,1) AS,姓氏, Count(,学生表,.XH) AS,人数,FROM,学生表,GROUP BY Left(xm,1),HAVING (Left(xm,1)=,王,) OR (Left(xm,1)=,张,),ORDER BY Left(xm,1);,SELECT Left(xm,1) AS 姓氏, Cou,【,例,】,统计学生表中不同年龄的学生人数。输出时按年龄升序排序。,【,分析,】,年龄计算方法：,当前的年份,出生日期的年份,因此在创建的计算字段，需要使用相关的日期函数。,【例】统计学生表中不同年龄的学生人数。输出时按年龄升序排序。,截取日期分量函数,格式：,Year,（,Date,）,Month,（,Date,）,Day,（,Date,）,功能：,分别返回日期表达式年份、月份和日期的整数。,返回值数据类型：,整型,说明：,Date,为任何能够表示日期的表达式（日期或字符表达式等）。,参数中若略去年份，则返回系统日期的年份。,参数中若含有,Null,值，则返回,Null,。,相类似的函数有：,Hour( ),函数、,Minute( ),函数和,Second( ),函数。,日期函数,截取日期分量函数 相类似的函数有：Hour( ),【,例,】,Year ( #2011-10-01# ),2011,Year ( Null ),Null,Year ( Oct , 15 ),（系统日期的年份）,Month ( 2012-09-20 ),9,Day ( 3 - 2 ),31,Access,将,1899,年,12,月,31,日作为数值,1,。,日期函数,【例】 Access将1899年12月31日作为数值1,获取系统日期,/,时间函数,格式：,Date,（,）,Time,（ ）,Now,（ ）,功能：,分别返回当前系统日期、当前系统时间和当前系统日期和时间。,返回值数据类型：,日期型,返回的日期格式由操作系统设置的日期格式决定。,日期函数,获取系统日期 / 时间函数 返回的日期格式由操作系统,【,例,】,统计学生表中不同年龄的学生人数。输出时按年龄升序排序。,【例】统计学生表中不同年龄的学生人数。输出时按年龄升序排序。,SELECT Year(Date()-Year(csrq) AS,年龄, Count(,学生表,.XH) AS,人数,FROM,学生表,GROUP BY Year(Date()-Year(csrq),ORDER BY Year(Date()-Year(csrq);,SELECT Year(Date()-Year(csrq,【,例,】,统计各院系女生人数及所占比例。要求输出：院系名称、女生人数、女生比例；输出时按女生比例降序排序，比例相同时则按院系名称升序排序（女生比例保留,1,位小数）。,【,分析,】,由于需要统计女生所占的比例，因此不能用,Where,子句进行性别筛选。本题需要用到一个条件函数,IIf（ ）。,【例】统计各院系女生人数及所占比例。要求输出：院系名称、女生,IIf ( ),函数,格式：,IIF,（,Expression,，,Truepart,，,Falsepart,）,功能：,根据,Expression,的值，返回,Truepart,和,Falsepart,两个值中的一个。,说明：,当逻辑表达式,Expression,的值为真时，返回,Truepart,的值，否则返回,Falsepart,的值。,【,例,】,根据成绩输出相应的分数等级：不及格、及格和优秀。,I,If,( cj.cj,= 9,0,优秀, , _,IIf ( cj.cj 60 , ,不及格, , ,及格, ) ),IIf ( ) 函数,【,例,】,统计各院系女生人数及所占比例。要求输出：院系名称、女生人数、女生比例；输出时按女生比例降序排序，比例相同时则按院系名称升序排序（女生比例保留,1,位小数）。,【例】统计各院系女生人数及所占比例。要求输出：院系名称、女生,SELECT,院系代码表,.YXMC, Sum(IIf(xb=,女,1,0) AS,女生人数, Sum(IIf(xb=,女,1,0)/Count(xh) AS,女生所占比例,FROM,院系代码表,INNER JOIN,学生表,ON,院系代码表,.YXZYDM =,学生表,.YXZYDM,GROUP BY,院系代码表,.YXMC,ORDER BY Sum(IIf(xb=,女,1,0)/Count(xh) DESC ,院系代码表,.YXMC;,SELECT 院系代码表.YXMC, Sum(IIf(xb,第 三 节,交 叉 表 查 询,第 三 节,交叉表查询是以行和列的字段作为标题和条件选取数据，并在行与列的交叉处对数据进行汇总和统计计算。,交叉表查询是以行和列的字段作为标题和条件选取数据，并,设计交叉表查询需要字义,3,种字段：,处于数据表最左端的,行标题字段,把某一字段或相关数据放入指定的一行中。,处于数据表最上边的,列标题字段,对每一列指定的字段进行统计，并将统计结果放在该列。,处于行和列交叉位置的,值字段,可以为该字段指定总计项。,在交叉表查询中：,只能指定一个列字段和一个总计类型的字段。,可以通过设置一个或多个行标题，创建多级交叉表查询。,设计交叉表查询需要字义3种字段：在交叉表查询中：,一、使用向导创建交叉表查询,【,例,】,基于,xsb,表创建一个交叉表查询，要求统计各班的男女生人数。,操作步骤：,单击,【,创建,】,选项卡,【,查询,】,组中的,【,查询向导,】,按钮，并在对话框中选择,【,交叉表查询向导,】,按向导提示依次确定,数据源,、,行字段,、,列字段,、,汇总项,及是否包括各行小计,为查询命名并保存所设计的查询,一、使用向导创建交叉表查询,交叉表查询向导的数据源只能来自于一张表或查询。若来自多个表，可先建立一个查询，而后以此查询作为数据源使用向导创建，或者直接在设计视图下创建。,行标题最多,3,个，列标题,1,个，值字段,1,个。,交叉表查询向导的数据源只能来自于一张表或查询。若,TRANSFORM,Count(xsb.XH) AS XH,之计数,SELECT xsb.BJBH,FROM xsb,GROUP BY xsb.BJBH,PIVOT,xsb.XB;,TRANSFORM Count(xsb.XH) AS X,二、在设计视图下创建交叉表查询,使用设计视图可创建基于多表的交叉表查询。,【,例,】,以交叉表的形式查询每个学生的各门课程成绩及平均分。要求：输出时平均分保留一位小数。,在查询的设计视图下，单击,【,查询工具,/,设计,】,选项卡,【,查询类型,】,组中,【,交叉表,】,按钮，可以在设计网格中增加,【,交叉表,】,行。,二、在设计视图下创建交叉表查询 在查询的设计视图下，单,。,操作步骤：,打开查询设计视图，并添加数据源及显示字段,单击,【,查询工具,/,设计,】,选项卡,【,查询类型,】,组中,【,交叉表,】,按钮，使查询类型为,交叉表查询,在,【,交叉表,】,行上设置：,行字段,、,列字段,在,【,交叉表,】,行上设置：,值字段,（将该字段的,【,总计,】,行改为：第一条记录）,添加汇总字段并设置汇总函数（将该字段的,【,交叉表,】,行设置为：,行标题,）,。,Access查询设计汇总课件,行标题,列标题,值,行标题列标题值,TRANSFORM,First(,成绩表,.CJ) AS CJ,之,First,SELECT,学生表,.XM, Avg(,成绩表,.CJ) AS,平均分,FROM (,学生表,INNER JOIN,成绩表,ON,学生表,.XH =,成绩表,.XH) INNER JOIN,课程表,ON,成绩表,.KCDM =,课程表,.KCDM,GROUP BY,学生表,.XM,PIVOT,课程表,.KCMC;,TRANSFORM First(成绩表.CJ) AS CJ之,如果在,【,交叉表,】,行中，设置某个字段的选项为：,“,值,”,，则在,【,总计,】,行中可以有多种选择。,如果获取的是单一数据，则应该选择,“,First,”,或,“,Last,”,。,ORDER BY,子句中不能含有聚合函数。,如果在【交叉表】行中，设置某个字段的选项为：“ 值 ”，则,创建交叉表查询时，使用交叉表查询向导和设计视图的比较：,数据源来自于一个表或查询时，使用交叉表查询向导创建；,数据源来自于多个表或查询时，使用设计视图创建；,如果,【,行标题,】,或,【,列标题,】,需要建立新字段得到，则使用设计视图创建。,创建交叉表查询时，使用交叉表查询向导和设计视图的比较：,第 四 节,参 数 查 询,第 四 节,所谓参数查询是在运行查询的过程中，系统提示用户输入参数，并根据参数的值来检索符合条件的记录。,在,Access,中，可以建立,单参数,查询，也可以建立,多参数,查询。,参数查询是一个特殊的选择查询，具有较大的灵活性，常作为窗体、报表的数据源。,所谓参数查询是在运行查询的过程中，系统提示用户输入参,一、单参数查询,【,例,】,设计一个能够查询指定课程代码的学生成绩查询，要求输出：学号、姓名、课程代码、课程名称、成绩。,（运行时课程代码分别取：,60001,、,60011,）,操作步骤：,打开查询的设计视图,添加数据源,设置显示字段,在作为参数字段的,【,条件,】,行上输入提示文本（,注：提示文本需要用方括号括起来,）,一、单参数查询,Access查询设计汇总课件,SELECT,学生表,.XH,学生表,.XM,课程表,.KCMC,课程表,.KCDM,成绩表,.CJ,FROM,学生表,INNER JOIN (,课程表,INNER JOIN,成绩表,ON,课程表,.KCDM =,成绩表,.KCDM) ON,学生表,.XH =,成绩表,.XH,WHERE,课程表,.KCDM=,请输入需要查询的课程代码：,;,SELECT 学生表.XH, 学生表.XM, 课程表.KCM,二、多参数查询,【,例,】,设计一个能够查询指定课程代码、指定班级编号的学生成绩查询，要求输出：学号、姓名、班级编号、课程代码、课程名称、成绩。,（运行时课程代码同上例，班级编号取：,04020,）,二、多参数查询,SELECT,学生表,.XH,学生表,.XM,成绩表,.KCDM,课程表,.KCMC,成绩表,.CJ,FROM,学生表,INNER JOIN (,课程表,INNER JOIN,成绩表,ON,课程表,.KCDM =,成绩表,.KCDM) ON,学生表,.XH =,成绩表,.XH,WHERE,成绩表,.KCDM=,请输入课程代码：, AND,学生表,.BJBH=,请输入班级编号：,;,SELECT 学生表.XH, 学生表.XM, 成绩表.KCD,【,例,】,查询指定省份学生的信息（,jg,字段的前两个字符为该学生所属省份）。输出字段为：省份、,jg,、,xm,、,xb,和,yxmc,，查询结果先按,jg,升序排列，,jg,相同时按,xm,降序排列。,【例】查询指定省份学生的信息（jg字段的前两个字符为该学生所,SELECT,Left(jg,2) AS,省份,学生表,.JG,学生表,.XM,学生表,.XB,院系代码表,.ZYMC,FROM,院系代码表,INNER JOIN,学生表,ON,院系代码表,.YXZYDM =,学生表,.YXZYDM,WHERE,Left(jg,2)=,请输入省份,: ,ORDER BY,学生表,.JG,学生表,.XM DESC;,SELECT Left(jg,2) AS 省份, 学生表,【,例,】,查询指定城市（,jg,字段中的第,3,、,4,个字符）男女学生的人数，要求输出字段为：城市名、性别和人数。,【例】查询指定城市（jg字段中的第3、4个字符）男女学生的人,SELECT Mid(jg,3,2) AS,城市名,学生表,.XB, Count(,学生表,.XB) AS,人数,FROM,学生表,GROUP BY Mid(jg,3,2),学生表,.XB,HAVING,Mid(jg,3,2)=,城市名,: ;,Access查询设计汇总课件,第 五 节,操作查询,第 五 节,操作查询是在选择查询的基础上创建的，可以对表中符合符合条件的记录进行,追加,、,修改,、,删除,和,生成新表,操作。,操作查询包括：,追加查询,更新查询,删除查询,生成表查询,操作查询是在选择查询的基础上创建的，可以对表中符合符,一、删除查询,删除查询是从表中删除与查询结果中相对应的记录。,操作步骤：,打开查询的设计视图,添加数据源,更改查询的类型为删除查询,设置删除的条件,查询运行后将从原表中,永久删除指定的记录，且无法恢复,！为避免误删除，可以先预览查询的结果或对原数据表进行备份。,删除查询删除的是整个记录，而不是指定的字段或字段中的数据。,一、删除查询查询运行后将从原表中永久删除指定的记录，且无法,【,例,】,将“成绩表”表中成绩小于,60,分的记录删除。（成绩表）,【例】 将“成绩表”表中成绩小于60分的记录删除。（成绩表）,DELETE,成绩表,.*,成绩表,.CJ,FROM,成绩表,WHERE,成绩表,.CJ60;,DELETE 成绩表.*, 成绩表.CJ,【,例,】,从,cj,表中删除姓名为“史建平”的记录。,【,分析,】,此删除查询涉及两张表：学生表和成绩表。删除的是成绩表表中的记录；条件设置来自于学生表