Access查询设计汇总课件

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