Access查询设计汇总课件

第四章 查 询第四章 查 询【本章主要掌握内容本章主要掌握内容】查询的基本概念查询的基本概念查询的功能与分类查询的功能与分类查询的创建与设计查询的创建与设计SQL查询查询【本章主要掌握内容】第 一 节查 询 的 概 述第 一 节 在在设设计计数数据据库库时时，常常常常把把数数据据分分类类，并并分分别别存存放放在在多多个个表表中中，但但在在使使用用时时需需要要检检索索一一个个或或多多个个表表中中符符合合条条件件的的数数据据。查查询询实实际际上上就就是是将将这这些些分分散的数据再集中起来。散的数据再集中起来。xhxmxb1001张三男xhkcdhcj10010185kcdhkcmc01数据库应用学学生生表表成成绩绩表表课课程程表表xmkcmccj张三数据库应用 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数数据据库服务器中，而不需要事先建立链接。库服务器中，而不需要事先建立链接。利利用用传传递递查查询询可可以以直直接接使使用用其其它它数数据据库库管管理理系统中的表。系统中的表。P154SQL特定查询包括：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 AC NOT .T.AND .T.F.AND .T.F.数值运算符逻辑运算符 在各类运算符混合运算的表达式中，各运算符的运算级别有【例例】用文字表示使以下条件表达式为用文字表示使以下条件表达式为“真真”的条件：的条件：学生表中：学生表中：性别性别=-1 And 系名系名=信息工程学院信息工程学院 信息工程学院中所有的男生信息工程学院中所有的男生 性别性别=-1 Or 系名系名=信息工程学院信息工程学院 所有的男生以及信息工程学院中所有的学生所有的男生以及信息工程学院中所有的学生教师表中：教师表中：性性别别=0 And 职职称称=教教授授 Or 民民族族 汉汉族族 女性教授以及非汉族教师女性教授以及非汉族教师 性性别别=0 And (职职称称=教教授授 Or 民民族族 汉汉族族)女性教授以及女性非汉族教师女性教授以及女性非汉族教师【例】用文字表示使以下条件表达式为“真”的条件：（2）条件查询举例条件查询举例 在在查查询询的的设设计计视视图图中中，查查询询条条件件应应使使用用查查询询定定义义窗窗口口中中的的条条件件选选项项来来设设置置，即即在在相相应应的的字字段段的的【条件条件】文本框中输入条件文本框中输入条件。条件表达式中通常省略字段名。条件表达式中通常省略字段名。有有多多个个条条件件，且且涉涉及及不不同同的的字字段段时时，则则分分别别设设置相应字段的条件。置相应字段的条件。同同行行的的条条件件之之间间是是“与与”关关系系；不不同同行行的的条条件件之之间是间是“或或”关系关系（2）条件查询举例【例例】查查找找每每名名学学生生“体体育育”课课程程的的成成绩绩，要要求求显显示示“学学号号”、“姓姓名名”、“课课程程名名称称”和和“成绩成绩”4个字段。个字段。【例】查找每名学生“体育”课程的成绩，要求显示“学号”、“姓SELECT 学学 生生 表表.XH,学学 生生 表表.XM,课课 程程 表表.KCMC,成绩表成绩表.CJFROM(学学生生表表 INNER JOIN 成成绩绩表表 ON 学学生生表表.XH=成成绩绩表表.XH)INNER JOIN 课课程程表表 ON 成成绩绩表表.KCDM=课程表课程表.KCDMWHERE(课程表课程表.KCMC)=体育体育);说明：说明：WHERE子句子句用于指定查询的条件用于指定查询的条件SELECT 学生表.XH,学生表.XM,课程表.KCM【例例】查查找找成成绩绩在在70至至95分分之之间间（含含70和和95分分）的的女女生生，以以及及成成绩绩大大于于等等于于80分分的的男男生生。要要求求显显示示“姓姓名名”、“性性别别”、“课课程程名名称称”、“成成绩绩”4个个字字段并按性别升序、成绩降序排序。段并按性别升序、成绩降序排序。【例】查找成绩在70至95分之间（含70和95分）的女生，以SELECT 学生表学生表.XM,学生表学生表.XB,课程表课程表.KCMC,成绩表成绩表.CJFROM(学生表学生表 INNER JOIN 成绩表成绩表 ON 学生表学生表.XH=成绩表成绩表.XH)INNER JOIN 课程表课程表 ON 成成绩表绩表.KCDM=课程表课程表.KCDMWHERE(学生表学生表.XB)=女女)AND(成绩表成绩表.CJ)=70 And(成绩表成绩表.CJ)=80)ORDER BY 学生表学生表.XB,成绩表成绩表.CJ DESC;ORDER BY子句子句 指定查询结果的排序依据指定查询结果的排序依据 DESC降序排序降序排序 ASC升序排序（默认排序）升序排序（默认排序）SELECT 学生表.XM,学生表.XB,课程表.KCM【例例】查查找找没没有有选选课课学学生生的的课课程程名名称称。要要求求输输出出：课程代码和课程名称。课程代码和课程名称。【分析分析】没没有有选选课课学学生生的的课课程程，即即意意味味着着，该该门门课课程程在在成绩表中没有记录。成绩表中没有记录。因此，必须因此，必须kc表和表和cj表连接时采用左连接类型。表连接时采用左连接类型。【例】查找没有选课学生的课程名称。要求输出：课程代码和课程名SELECT 课程表课程表.KCDM,课程表课程表.KCMC,成绩表成绩表.CJFROM 课程表课程表 LEFT JOIN 成绩表成绩表 ON 课程表课程表.KCDM=成绩表成绩表.KCDMWHERE(成绩表成绩表.CJ)Is Null);SELECT 课程表.KCDM,课程表.KCMC,成绩表【例例】查查找找成成绩绩表表中中成成绩绩最最高高的的10名名学学生生。要要求求输出：姓名、学号、课程名称和成绩。输出：姓名、学号、课程名称和成绩。【分析分析】即：将成绩按降序排序，且只显示前即：将成绩按降序排序，且只显示前10条记录。条记录。【例】查找成绩表中成绩最高的10名学生。要求输出：姓名、学号SELECT TOP 10 学生表学生表.XH,学生表学生表.XM,课程课程表表.KCMC,成绩表成绩表.CJFROM(学生表学生表 INNER JOIN 成绩表成绩表 ON 学生表学生表.XH=成绩表成绩表.XH)INNER JOIN 课程表课程表 ON 成成绩表绩表.KCDM=课程表课程表.KCDMORDER BY 成绩表成绩表.CJ DESC;SELECT TOP 10 学生表.XH,学生表.XM,【例例】查查找找有有补补考考学学生生的的课课程程代代码码和和课课程程名名称称（相同课程只显示一次）。（相同课程只显示一次）。【分析分析】即：该门课程的成绩有小于即：该门课程的成绩有小于60分的记录。分的记录。【例】查找有补考学生的课程代码和课程名称（相同课程只显示一次SELECT DISTINCTROW 课程表课程表.KCDM,课程课程表表.KCMCFROM 课程表课程表 INNER JOIN 成绩表成绩表 ON 课程表课程表.KCDM=成绩表成绩表.KCDMWHERE(成绩表成绩表.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=成绩表.KCDMGROUP 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=学生表.YXZYDMWHERE(院系代码表.YXMC)=数学系)GROUP BY 学生表.XH,学生表.XMHAVING(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的值。【例】根据成绩输出相应的分数等级：不及格、及格和优秀。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 院系代码表 INNER JOIN 学生表 ON 院系代码表.YXZYDM=学生表.YXZYDMGROUP BY 院系代码表.YXMCORDER 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.BJBHFROM xsbGROUP BY xsb.BJBHPIVOT xsb.XB;TRANSFORM Count(xsb.XH)AS X二、在设计视图下创建交叉表查询二、在设计视图下创建交叉表查询 使用设计视图可创建基于多表的交叉表查询。【例】以交叉表的形式查询每个学生的各门课程成绩及平均分。要求：输出时平均分保留一位小数。在查询的设计视图下，单击【查询工具/设计】选项卡【查询类型】组中【交叉表】按钮，可以在设计网格中增加【交叉表】行。二、在设计视图下创建交叉表查询 在查询的设计视图下，单。操作步骤：操作步骤：打开查询设计视图，并添加数据源及显示字段单击【查询工具/设计】选项卡【查询类型】组中【交叉表】按钮，使查询类型为交叉表查询在【交叉表】行上设置：行字段、列字段在【交叉表】行上设置：值字段（将该字段的【总计】行改为：第一条记录）添加汇总字段并设置汇总函数（将该字段的【交叉表】行设置为：行标题）。Access查询设计汇总课件行标题行标题列标题列标题值值行标题列标题值TRANSFORM First(成绩表.CJ)AS CJ之FirstSELECT 学生表.XM,Avg(成绩表.CJ)AS 平均分FROM(学生表 INNER JOIN 成绩表 ON 学生表.XH=成绩表.XH)INNER JOIN 课程表 ON 成绩表.KCDM=课程表.KCDMGROUP BY 学生表.XMPIVOT 课程表.KCMC;TRANSFORM First(成绩表.CJ)AS CJ之 如果在【交叉表】行中，设置某个字段的选项为：“值值”，则在【总计】行中可以有多种选择。如果获取的是单一数据，则应该选择“First”或“Last”。ORDER BY子句中不能含有聚合函数。如果在【交叉表】行中，设置某个字段的选项为：“值”，则创创建建交交叉叉表表查查询询时时，使使用用交交叉叉表表查查询询向向导导和和设设计计视图的比较：视图的比较：数据源来自于一个表或查询时，使用交叉表查询向导创建；数据源来自于多个表或查询时，使用设计视图创建；如果【行标题】或【列标题】需要建立新字段得到，则使用设计视图创建。创建交叉表查询时，使用交叉表查询向导和设计视图的比较：第 四 节参 数 查 询第 四 节 所所谓谓参参数数查查询询是是在在运运行行查查询询的的过过程程中中，系系统统提提示示用用户户输输入入参参数数，并并根根据据参参数数的的值值来来检检索索符符合合条条件的记录。件的记录。在在Access中中，可可以以建建立立单单参参数数查查询询，也也可可以以建建立立多参数多参数查询。查询。参参数数查查询询是是一一个个特特殊殊的的选选择择查查询询，具具有有较较大大的的灵活性，常作为窗体、报表的数据源。灵活性，常作为窗体、报表的数据源。所谓参数查询是在运行查询的过程中，系统提示用户输入参一、单参数查询一、单参数查询【例】设计一个能够查询指定课程代码的学生成绩查询，要求输出：学号、姓名、课程代码、课程名称、成绩。（运行时课程代码分别取：60001、60011）操作步骤：操作步骤：打开查询的设计视图添加数据源设置显示字段在作为参数字段的【条件】行上输入提示文本（注：提示文本需要用方括号括起来注：提示文本需要用方括号括起来）一、单参数查询Access查询设计汇总课件SELECT 学生表.XH,学生表.XM,课程表.KCMC,课程表.KCDM,成绩表.CJFROM 学生表 INNER JOIN(课程表 INNER JOIN 成绩表 ON 课程表.KCDM=成绩表.KCDM)ON 学生表.XH=成绩表.XHWHERE 课程表课程表.KCDM=请输入需要查询的课请输入需要查询的课程代码：程代码：;SELECT 学生表.XH,学生表.XM,课程表.KCM二、多参数查询二、多参数查询【例】设计一个能够查询指定课程代码、指定班级编号的学生成绩查询，要求输出：学号、姓名、班级编号、课程代码、课程名称、成绩。（运行时课程代码同上例，班级编号取：04020）二、多参数查询SELECT 学生表.XH,学生表.XM,成绩表.KCDM,课程表.KCMC,成绩表.CJFROM 学生表 INNER JOIN(课程表 INNER JOIN 成绩表 ON 课程表.KCDM=成绩表.KCDM)ON 学生表.XH=成绩表.XHWHERE 成成绩绩表表.KCDM=请请输输入入课课程程代代码码：AND 学生表学生表.BJBH=请输入班级编号：请输入班级编号：;SELECT 学生表.XH,学生表.XM,成绩表.KCD【例】查询指定省份学生的信息（jg字段的前两个字符为该学生所属省份）。输出字段为：省份、jg、xm、xb和yxmc，查询结果先按jg升序排列，jg相同时按xm降序排列。【例】查询指定省份学生的信息（jg字段的前两个字符为该学生所SELECT Left(jg,2)AS 省份省份,学生表.JG,学生表.XM,学生表.XB,院系代码表.ZYMCFROM 院系代码表 INNER JOIN 学生表 ON 院系代码表.YXZYDM=学生表.YXZYDMWHERE 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),学生表学生表.XBHAVING Mid(jg,3,2)=城市名:;Access查询设计汇总课件第 五 节操作查询第 五 节 操操作作查查询询是是在在选选择择查查询询的的基基础础上上创创建建的的，可可以以对对表表中中符符合合符符合合条条件件的的记记录录进进行行追追加加、修改修改、删除删除和和生成新表生成新表操作。操作。操作查询包括：操作查询包括：追加查询追加查询 更新查询更新查询 删除查询删除查询 生成表查询生成表查询 操作查询是在选择查询的基础上创建的，可以对表中符合符一、删除查询一、删除查询 删除查询是从表中删除与查询结果中相对应的记录。操作步骤：操作步骤：打开查询的设计视图添加数据源更改查询的类型为删除查询设置删除的条件查询运行后将从原表中永永久久删删除除指指定定的的记记录录，且且无无法法恢恢复复！为避免误删除，可以先预览查询的结果或对原数据表进行备份。删除查询删除的是整个记录，而不是指定的字段或字段中的数据。一、删除查询查询运行后将从原表中永久删除指定的记录，且无法【例】将将“成绩表成绩表”表中成绩小于表中成绩小于60分的记录删除。分的记录删除。（成绩表）（成绩表）【例】将“成绩表”表中成绩小于60分的记录删除。（成绩表）DELETE 成绩表成绩表.*,成绩表成绩表.CJFROM 成绩表成绩表WHERE 成绩表成绩表.CJ1 And MZDM=学生表.MZDM)ORDER BY 学生表.bjbh,学生表.MZDM;SELECT 学生表.bjbh,学生表.MZDM,学生表 二、查找不匹配项查询向导二、查找不匹配项查询向导 不不匹匹配配项项查查询询就就是是在在一一个个表表中中搜搜索索另另一一个个表表中中没没有相关记录的行。有相关记录的行。【例】查询没有选课的课程信息操作步骤：操作步骤：打开查找不匹配项查询向导（创建新查询）选择参考表选择不匹配表选择两表的联接字段 二、查找不匹配项查询向导Access查询设计汇总课件SELECT SELECT 课程表课程表.KCDM,.KCDM,课程表课程表.KCMC.KCMCFROM FROM 课程表课程表 LEFT JOIN LEFT JOIN 成绩表成绩表 ON ON 课程表课程表.KCDM=.KCDM=成绩表成绩表.KCDM.KCDMWHERE WHERE 成绩表成绩表.KCDM Is Null;.KCDM Is Null;SELECT 课程表.KCDM,课程表.KCMC第 七节 SQL查询第 七节 SQL查询 SQL结构化查询语言是标准的关系型数据语言。结构化查询语言是标准的关系型数据语言。查查询询的的本本质质是是一一条条SQL语语句句，即即每每个个查查询询都都对对应着一条应着一条SQL查询命令。查询命令。保保存存查查询询时时，保保存存的的是是一一条条SQL语语句句，而而不不是是查询的结果。查询的结果。创创建建查查询询，既既可可以以在在其其设设计计视视图图中中实实现现，也也可可以在其以在其SQL视图中直接输入视图中直接输入SQL语句实现。语句实现。某某些些特特殊殊的的查查询询不不能能在在设设计计视视图图中中进进行行，只只能能通过通过SQL语句才能实现。语句才能实现。SQL结构化查询语言是标准的关系型数据语言。SQL（结结构构化化查查询询语语言言）是是一一种种数数据据库库查查询询语语言，它的功能包括：言，它的功能包括：数据定义数据定义 数据查询数据查询 数据操纵数据操纵 数据控制数据控制 SQL（结构化查询语言）是一种数据库查询语言，它的功一、一、SQL语言的特点语言的特点 高度的综合高度的综合 高度非过程化高度非过程化 交互式与嵌入式相结合交互式与嵌入式相结合 语言简洁，易学易用语言简洁，易学易用一、SQL语言的特点二、二、SQL语言的数据定义功能语言的数据定义功能 SQL语语言言的的数数据据定定义义功功能能包包括括定定义义表表、定定义义视视图图、定定义义索索引引。即即对对表表、视视图图和和索索引引的的创创建建、修修改和删除。改和删除。数数据据定定义义的的CREATE语语句句是是定定义义基基本本表表的的结结构构、定义视图和定义索引。定义视图和定义索引。SQL语句句用用 途途CREATE TABLE创建表建表ALTER TABLE在已有表中添加新字段或在已有表中添加新字段或约束束DROP TABLE从数据从数据库中中删除表，或者从字段或字除表，或者从字段或字段段组中中删除索引除索引CR