access 二级 第三章查询.docx

1、access 二级 第三章查询第三章查询第一节认识查询第二节创建选择查询第三节创建交叉表查询第四节创建参数查询第五节创建操作查询第六节创建SQL查询本章考点第一节认识查询一什么是查询二查询的功能三查询的分类四查询的视图一什么是查询查询是能够将存储于一个或多个表中符合要求的数据挑选出来，并对挑选的结果按照某种规则进行运算的对象。二查询的功能1.选择字段2.选择记录3.数据集中4.实现计算5.编辑记录6.建立新表7.作为其他数据库对象的数据源三查询的分类1.选择查询：根据某规则选取表中数据，并对选取的数据进行简单计算2.交叉表查询：对表中数据进行分类并显示分类后某数据的明细值3.操作查询：自动执行

2、对表中数据进行某个特定功能的操作4.参数查询：在执行时临时指定查询条件5.SQL查询：通过直接输入SQL语句创建的查询四查询的视图显示查询的不同内容的窗口。1、设计视图：用于设计（创建和编辑）查询2、数据表视图：以表的形式显示查询执行的结果3、SQL视图：用于查看或编辑查询时所用到的SQL语句第二节创建选择查询1、无条件选择查询2、有条件选择查询3、查询准则4、计算性选择查询1、无条件选择查询:将表中某些字段的内容挑选出来。若查询字段只来源于一个表，则属于典型的投影运算，否则就是连接运算主要有以下两种创建方法：（1）查询向导（2）设计视图（1）查询向导：例：查询学生年龄，要求只显示学生编号、姓

3、名和年龄，查询名L01。选择学生表：添加学生编号、姓名和年龄字段保存表1、启动简单查询向导：新建简单查询向导（或双击使用向导创建查询）2、指定数据源（学生表）及字段（学生编号、姓名、年龄）3、指定查询标题（L01）举例：例2：查询学生的选课成绩，要求只显示学生编号、姓名、所选课程名和成绩，查询名L02注意：若查询数据源为多个表，则多个表之间必须建立关系添加学生表、选课成绩、课程三个表，加上关系添加学生编号、姓名、所选课程名和成绩保存表（1）设计视图例3：查询学生年龄，显示学生编号、姓名和年龄，年龄升序排序，查询名L03添加学生表添加学生编号 姓名 年龄 升序 排序1、打开查询设计视图：新建设计

4、视图（或双击在设计视图中创建查询2、显示表中选择数据源3、字段行指定查询字段（三种方法：拖动，双击，选择），排序行指定排序方式4、保存并指定查询名称举例例：查询学生选课情况，显示学生编号、姓名、所选课程编号、课程名、课程类别和学分，查询名L04添加学生表、选课成绩、课程，加上关系添加学生编号 姓名 课程编号 课程名称 课程类别 学分保存表注意：字段列表中的“*”表示所有字段2、有条件选择查询:将表中符合条件的记录的某些字段挑选出来只能用设计视图创建需设置查询条件查询条件在“条件”和“或”行中设置举例：例5：查询女学生年龄信息，显示学生编号、姓名和年龄、年龄降序排序，查询名L05注意：“显示”行

5、作用指定所选字段是否在查询结果中显示添加学生表添加学生编号 姓名 年龄 性别 降序 排序 “女” 条件例6：查询选修“中药学”并及格的学生成绩信息，显示学生编号、姓名和成绩，成绩升序排序，查询名L06添加学生表、课程、选课成绩三个表添加学生编号 姓名 成绩 课程名称 升序 排序 =60 “中药学” 去掉对勾保存例7：查询18和23岁的学生信息，查询名L07注：多个条件是逻辑“与”的关系时只是在“条件”行设置，是：“或”的关系时分别在“条件”和“或”两行设置。添加学生表添加学生* 年龄 18 条件 23 或 去掉对钩3、查询条件：用来从表中选取所需记录的限制条件，又称查询条件，一般是一个表达式，

6、称为条件表达式条件表达式是一个由运算符、数据和函数组合而成且能够计算出一个结果的式子1）数据的表示2）ACCESS运算符3）ACCESS内部函数1）数据的表示：（1）数字型：直接表示。例：28、3287（2）文本值：用双引号（“）或单引号（）括起。例：“陈江川”、男（3）日期型：用括起来。例：#2006-10-25#例如：查询年月日入校的学生信息。（4）是否行：真为True/on/yes假为False/off/no例如：查询团员的信息（5）空值：is NULL非空值：IS NOT NULL例如：查询无职称的教师信息（6）对象名和字段名：用方括号（）括起，且对象名与字段名感叹号（！）分隔例：年龄

7、、学生！姓名2） ACCESS算符运算符是表达式的灵魂，系统主要提供了以下几类运算符：（1）算术运算符（2）文本运算符（3）日期运算符（4）比较运算符（5）逻辑运算符（6）特殊运算符（1）算术运算符：+、-MODMOD(10,3)=1 MOD(2,3)=2*、/（取负）（1）文本运算符既字符串运算符，将两个字符串连接成一个字符串连接运算符，共有两个：+、&+要求两个运算量必须是文本型&会将非文本型运算量转换为文本型例：“计算机”“原理”“年龄：”&23结果：“年龄：23”（2）日期运算符由日期运算符（+、-）构成的表达式称为日期表达式，主要有两种格式：格式一：日期+、整数1990-1-1-20

8、结果是该日期整数天前或后的新日期格式二：日期日期结果是两日期相隔的天数（3）比较运算符比较运算符是判断两个数据间的某种关系是否成立，又称关系运算符、（不等于）关系表达是结果为是否型（逻辑型）例8：查询1990年以前参加工作的教师信息，查询名L08.添加表教师添加教师* 表达式1: Year(工作时间) 1990 条件 去掉对钩（4）逻辑运算符运算量为是否型，常用的逻辑运算符：AND（与）两侧都为真，结果为真23 and 23 or 24not（非）逻辑取反逻辑表达式运算结果为是否型（逻辑型）例9：查询20世纪90年代参加工作的教师信息，查询名L09添加教师表添加教师* 表达式1: Year(工

9、作时间) =1990 And 2000 条件 去掉对钩例10:查询不是2008年入校的男学生信息，查询l10添加学生表添加学生* 表达式1: Year(入校日期) 性别 2008 男 条件 去掉对钩（5）特殊运算符范围匹配：betweenand例：between 20 and 30Between #2007-1-1# and 2007-1-31#例11：查询20至22岁女学生信息，要求显示学生编号、姓名、性别、年龄，查询名L11添加表学生添加学生编号 姓名 性别 年龄 “女” between 20 And 23 条件保存表列表匹配：in（数据列表）例in(18,20,21,25)In(“教授”

10、,”副教授”,”讲师”)例12:查询18、20、21和23岁的非团员信息，查询名L12添加表学生学生* 团员否 年龄 No in(18,20,21,23)保存模式匹配：like模式字符串例：like王like*授*多个字符，？一个字符例13：查询姓张的教师信息，查询名L13添加表教师教师* 姓名 Like “李*” 去掉对钩保存表例14：查询姓名中包含“小”字的教师信息，查询名L14添加表教师教师* 姓名 Like “*小*” 去掉对钩保存表3）Access内部函数函数是事先设计的一个程序，其功能是对数据进行某种处理，并返回处理的结果其引用格式：函数名（参数列表） 一个或者多个，或者没有。参数

11、是处理的数据。常用函数：（1）数值函数（2）字符函数（3）日期时间函数（1）数值函数：绝对值：ABS（数值） 不区分大小写，正数和0返回本身，负数相返数。平方根：SQR（数值） 得到平方，整数。 SQR（9）得到正3符号：SGN（数值） 正数返1，0返回0，负数返回-1取整：INT（数值） 去掉小数，INT（3.4）得到3（2）字符函数：左子串截取：LEFT（字符串，数值） LEFT(“中国”,1)=”中”例15：查询20083班学生信息，查询名L15字段右边 条件LEFT(学生编号,6) “200803”右子串截取：RIGHT(字符串，数值)例16：查询姓名的末尾是明字的教师信息，查询名L1

12、6RIGHT(姓名,1) “明”子串截取：MID(字符串,数值1,数值2) MID(“1234567”,2,3)=”234”例17：查询姓名的第二个字是“小”的教师信息，查询名L17MID(姓名,2,1)=”小”求字符串长度：LEN（字符串）例18：查询姓名只有两个字的教师信息，查询名L18LEN(姓名)=2空格生成：SPACE（数值） SPACE(5) 5个空格子符重复涵数：STRING(数值,字符串) 字符串有几个重复（3）日期时间函数求系统日期：DATE(数值)例：查询100天之内参加工作的教师信息，查询名L19DATE()-工作时间10 条件表达式一：可改成工龄。求月份：MONTH（日

13、期）求日份：DAY（日期）例21：查询9月1日入校的学生信息，查询名为L21。二个字段，月份 天数MONTH(入校日期)=9 DAY(入校日期)=1条件可改成或者。求星期：WEEKDAY(日期) 星期日为第1天（1）-例：查询星期二入校的学生信息，查询名为L22WEEKDAY(入校日期)=3求小时：HOUR（日期）4、计算型选择查询：能够将挑选出的数据，按照某种规则进行统计和计算的查询。（1）总计查询；（2）分组总计查询；（3）添加计算字段。（1）总计查询：对表中符合条件的某些字段的值进行计算与统计需在设计视图中的总计行设置总计项类型。添加总计行方法：视图菜单总计（或单击会计按钮）。总计 平均

14、值最大值 最小值计数 分组表达式 条件第一条记录例24：统计女学生人数、最大年龄、最小年龄和平均年龄，查询名L24。注意：只用于构成查询条件的字段，其总计方式为条件。添加学生表学生编号 年龄 年龄 年龄 性别 计数 最大 最小 平均值 条件=”女”（2）分组总计查询根据表中某些字段的值对表中记录进行分类汇总。用于分组字段的“总计”方式设置成“分组”。例25：分别统计男女学生人数，查询名L25。学生编号 性别计数 分组例26：统计选修各六课程的男女学生的人数和平均成绩，查询名L26注：分组字段可以多个，按从左到右的顺序排定优先级，先按第一分组字段落分组，第一分组字段值相同的记录，再按第二分组字段

15、分组，依次类推。添加两个表：学生、选课成绩，加上关系。学生编号 成绩 课程编号 性别计数 平均值 分组 分组例27：统计学生的平均成绩，显示姓名、性别各平均成绩，查询名L27注意：只用于显示其值的字段作为第一条或最后一条记录。添加二个表：学生、选课成绩，加上关系学生编号 姓名 成绩 性别分组 第一条记录 平均值 第一条记录例28：统计各同学修得的总学分，显示学生编号、姓名、总学分，查询名L28计算字段是表中并不存在的字段，它的值是通过对表中某些字段进行运算而得到的，故实质是一个表达式。添加方法：在设计视图中的“字段”行中直接添加一个内容为表达式的新字段。新标题：表达式添加三个表：课程、选课成绩

16、、学生，加上关系。学生编号 姓名 （学分）总分分组 第一条记录 总计例29：查询一年后学生的年龄，显示学生的编号、姓名和新年龄，查询名L29。添加表：学生学生编号 姓名 年龄（新年龄：年龄+1）例30：根据学生的年龄，统计学生的出生年，显示姓名和出生年，查询名L30.添加：学生表姓名 （年龄）出生年：YEAR(date()-年龄例31：查询教师工龄信息，要求显示教师编号、姓名和工龄，查询名L31。添加：教师表教师编号 姓名 (工作时间)工龄:year(date()-year(工作时间)例32：统计各班学生人数和平均年龄，查询名L32。添加学生表班级:mid(学生编号,3,2) 人数:学生编号

17、年龄分组 计数 平均值例33：统计学生最大年龄与最小年龄差值，显示标题为S DATA，查询名L33。 s-date:max(年龄)-min(年龄) 表达式例34：分别统计各班男女同学的平均成绩，查询名Links4.添加学生表班级:mid(学生编号,3,2) 性别 成绩分组 分组 平均值1 当数据源为多个表或查询，而计算表达式中的某一字段在多个表中都存在时，引用时必须指定来源例35：分别统计男女学生最大年龄与最小年龄的差值，显示性别和s data，查询名L35性别 s-date:max(年龄)-min(年龄)分组 表达式表达式中可以包含统计函数，此时该字段的总计方式为表达式。例36：查询学生基本

18、信息，要求包含学生编号、姓、名、性别、年龄，查询名L36（不考虑复姓）学生编号 姓:left(姓名),1) 名:mid(姓名,2,2) 年龄例37：查询学生基本信息，要求包含学生编号、姓名、性别、年龄、入校日期和修得的总学分，但结果中只有二个字段为学生基本信息和总学分，查询名L37.添加三个表：学生、选课成绩、课程，加上关系学生信息：学生!学生编号+姓名&性别&年龄&入校日期第三节 创建交叉表查询1、交叉表的功能；2、使用向导创建；3、使用设计视图创建1、交叉表的功能将来源于某个表中的字段进行分组，一组列在数据表的左侧，一组列在数据表的上部，然后在数据表行与列的交叉处显示表中某个字段的各种计算

19、值。创建时，需指定三种字段：行标题字段（左侧）列标题字段（上部）总计项字段（行列交叉处）2、使用向导创建例38：统计不同年龄男女学生人数的交叉表查询，查询名L38.（1）新建交叉表查询向导；（2）选择数据源；（3）分别确定行标题字段、列标题字段和总计项字段及总计函数；（4）指定查询名称。注意：该方法只能创建单数据源的交叉表查询。打开查询表向导，点右键选查询类型里的交叉表添加学生表性别 年龄 学生编号分组 分组 计数行标题 列标题 值3、使用设计视图创建例39：统计不同年龄男女学生人数的交叉表查询L39设计视图选数据源；字段行添加行标题、列标题和总计项字段；添加交叉表和总计行（查询菜单交叉表查询

20、或查询类型按钮交叉表查询）；总计行中行标题和列标题字段的总计方式为分组，总计项为一统计函数；交叉表行设置行标题，列标题和总计项字段的相应值为行标题、列标题、值保存指定查询名。例40：统计各个班级选修各门课程的人数，查询名l40.注意：行标题和列标题字段是计算字段。添加学生、选课成绩、课程三个表，交叉表班级:left(学生)!学生编号,6) 课程名称 学生编号分组 分组 计数行标题 列标题 值第四节创建参数查询运行时临时指定查询条件的查询。该查询运行时将弹出一个对话框，提示输入查询参数（条件）。用条件提示信息替代查询条件条件提示信息例41：按性别查询学生基本信息，运行查询时提示”请输入性别:”，

21、要求显示姓名，性别，年龄和入校日期，查询名l41.姓名 性别 年龄 入校日期 ”请输入性别:” 条件例42：按班级和课程名称查询学生成绩信息，提示“请输入班级：”和“请输入课程名称：”，显示姓名和成绩，查询名L42。添加三个表：学生，选课成绩，课程姓名 成绩 表达式1：left(学生)!学生编号,6) 课程名称 请输入班级： 请输入课程名称：注意：条件提示信息也可以是多个。第五节创建操作查询可以自动完成对表中的数据执行一个选定操作的查询。根据所执行操作的不同，可分为以下四类：1、生成表查询；2、删除查询；3、更新查询；4、追加查询1、生成表查询将查询结果生成一个新表查询类型设置为生成表查询，指

22、定新表名称及保存位置。例43：创建一个生成表查询，新表名称为“90分以上学生情况”，包括学生编号，姓名，性别，年龄和成绩字段，查询名为L43添加二个表：学生，选课成绩，选生成表查询学生编号 姓名 性别 入校日期 成绩 =90 条件生成新表：90分以上学生情况 查询名L43，生成后双击L43执行或者点！执行，出现新表90分以上学生情况，修改设计。例44：根据老师表生成toffice表（系别，联系电话），查询名分别为L44添加教师表，右键选生成表查询系别 联系电话输入toffice表名，查询名L442、删除查询自动删除表中符合条件的记录；（1）设计视图，添加数据源；（2）设置查询类型为删除查询；（

23、3）字段行添加构成删除准则的字段，并指定其“删除”行值为“Where”;（4）设置删除准则；（5）保存。例45：创建一个删除经济系讲师信息的查询，查询名L45添加表：教师，选删除查询教师* 系别 职称 “经济系” “讲师”点！执行例46：创建一个删除200803班不是团员的女学生信息的查询，查询名L46.注意：表间关系一经建立并设置了参照完整性规则，则对表的所有操作都必须遵守该规则。也就是说子表有数据，不允许删除父表。添加表：学生，选删除查询。学生* left(学生编号,6) 性别 团员否 200803 “女” no点！执行3、更新查询根据某种规则自动对表中符合条件的数据进行更新。（1）设计视

24、图，添加数据源；（2）设置查询类型为更新查询；（3）添加需要更新的字段，并在更新到行指定更新值；（4）添加更新准则字段，并在“准则”行设置其更新准则;（5）保存。例47：创建一个将所有学生年龄增加一岁的查询，查询名L47添加学生表年龄年龄+1 更新到保存并运行！例48：创建一个将所有教师的联系电话前加“8”的查询，查询名l48添加表教师联系电话“8”&联系电话保存并运行！例49：创建一个将200804班改为200802班的查询，查询名为l49添加学生表Left(学生编号,6) 学生编号 “200802”&right(学生编号,2) 更新到200804 条件注意：学生编号替换后还剩几位&righ

25、t(学生编号,2)。4、追加查询将查询结果添加到另一表尾部。（1）设计视图，添加数据源；（2）添加查询（所要追加的）字段；（3）设置查询条件（所追加记录的条件）；（4）设置查询类型为追加查询，并指定追加到的表；（5）保存。例50：创建将学生基本信息（学生编号、姓名、性别、年龄、入校日期）备份到T2表的查询，查询名L50先复制学生表结构到T2，选学生表，选追加查询注意T2表是已存在的，并且结构要一样。学生编号 姓名 性别 年龄 入校日期保存例51：创建将选中药学课程并及格的学生信息备份到T1表（学生编号、姓、名、性别）的查询，查询名L51添加表：学生、选课成绩、课程学生编号 姓: Left(姓名

26、,1) 名: Right(姓名,2) 性别 成绩 课程名称 =60 “中药学” 条件选追加查询，T1表掩码不能有条件注意：所要追加的字段可以来源于多个表或是计算字段，但必须是目标表中已经存在的字段。第六节创建SQL诗句所创建的查询。（打开一个查询，右击选SQL视图，出现SQL诗句。）SQL诗句的语法规则相对较复杂，我们只需要掌握以下两个问题即可。1、SQL查询语句的一般语法2、SQL子查询SQL动词:1、Create语句（新建）;2、Alter语句（修改）;3、Drop语句（丢掉某个表）;4、Insert语句（插入）;5、Update语句（更新）;6、Delete语句（删除）;7、Select

27、语句（查询）。1、Create语句:新建一个表的格式：CREATE TABLE(字段级完整性约束条件1 ,字段级完整性约束条件2,-,字段级完整性约束条件n,-,;）需记忆的：字符型char:长度备注型memo:主关键字：Primaty Key字段是否允许为空：Not nullNull完整性约束：Check文本Text(n)/char 注：n为字段大小；char相当于text(255)备注Txt/Memo数字字节Byte/Tinylnt数字整型Short/Smalllnt数字长整型Long/lnteger数字单精度Single数字双精度Diuble/Fliat数字小数Decimal(m,n)

28、注：m为精度，n为数值范围数字同步复制IDGUID日期、时间Date Time/Date/Time货币Currency/Miney自动编号Autolncrement(m,n) 注：m为初始值，n为步进值是/否Bit/YesNoOLE对象lmage例52：创建一个“雇员”表，包括雇员号、姓名、性别、出生日期、部门、备注字段。CREATE TABLE 雇员(雇员号 SMALLINT Primary Key,姓名 CHAR(4) Not Null,性别 CHAR(1),出生日期 DATE,部门 CHAR(20),备注 MEMO)CREATE TABLE 雇员(雇员号 SMALLINT Primary Key（第一个字段，数字整形-主关键字）,姓名 CHAR(4) Not Null（字符型，不允许为空）,性别 CHAR(1)（1个长度的字符型）,出生日期 DATE（日期型）,部门 CHAR(20),备注 MEMO（备注)）复制一下以上的语句，打开查询，在设计视图中创建查询，右键SQL视图，粘贴。2、Alter语句ALTER TABLE表名ADD新字段名数据类型字段级完整性约束条件 增加字段DROP 删除字段ALTER 修改原