access 二级 第三章查询.docx
《access 二级 第三章查询.docx》由会员分享，可在线阅读，更多相关《access 二级 第三章查询.docx（22页珍藏版）》请在冰豆网上搜索。
access二级第三章查询
第三章查询
第一节认识查询
第二节创建选择查询
第三节创建交叉表查询
第四节创建参数查询
第五节创建操作查询
第六节创建SQL查询
本章考点
第一节认识查询
一．什么是查询
二．查询的功能
三．查询的分类
四．查询的视图
一．什么是查询
查询是能够将存储于一个或多个表中符合要求的数据挑选出来，并对挑选的结果按照某种规则进行运算的对象。
二．查询的功能
1.选择字段
2.选择记录
3.数据集中
4.实现计算
5.编辑记录
6.建立新表
7.作为其他数据库对象的数据源
三．查询的分类
1.选择查询：
根据某规则选取表中数据，并对选取的数据进行简单计算
2.交叉表查询：
对表中数据进行分类并显示分类后某数据的明细值
3.操作查询：
自动执行对表中数据进行某个特定功能的操作
4.参数查询：
在执行时临时指定查询条件
5.SQL查询：
通过直接输入SQL语句创建的查询
四．查询的视图
显示查询的不同内容的窗口。
1、设计视图：
用于设计（创建和编辑）查询
2、数据表视图：
以表的形式显示查询执行的结果
3、SQL视图：
用于查看或编辑查询时所用到的SQL语句
第二节创建选择查询
1、无条件选择查询
2、有条件选择查询
3、查询准则
4、计算性选择查询
1、无条件选择查询:
将表中某些字段的内容挑选出来。
若查询字段只来源于一个表，则属于典型的投影运算，否则就是连接运算
主要有以下两种创建方法：
（1）查询向导
（2）设计视图
（1）查询向导：
例１：
查询学生年龄，要求只显示学生编号、姓名和年龄，查询名L01。
选择学生表：
添加学生编号、姓名和年龄字段
保存表
1、启动简单查询向导：
新建简单查询向导（或双击使用向导创建查询）
2、指定数据源（学生表）及字段（学生编号、姓名、年龄）
3、指定查询标题（L01）
举例：
例2：
查询学生的选课成绩，要求只显示学生编号、姓名、所选课程名和成绩，查询名L02
注意：
若查询数据源为多个表，则多个表之间必须建立关系
添加学生表、选课成绩、课程三个表，加上关系
添加学生编号、姓名、所选课程名和成绩
保存表
（1）设计视图
例3：
查询学生年龄，显示学生编号、姓名和年龄，年龄升序排序，查询名L03
添加学生表
添加学生编号姓名年龄
升序排序
1、打开查询设计视图：
新建设计视图（或双击在设计视图中创建查询
2、显示表中选择数据源
3、字段行指定查询字段（三种方法：
拖动，双击，选择），排序行指定排序方式
4、保存并指定查询名称
举例
例４：
查询学生选课情况，显示学生编号、姓名、所选课程编号、课程名、课程类别和学分，查询名L04
添加学生表、选课成绩、课程，加上关系
添加学生编号姓名课程编号课程名称课程类别学分
保存表
注意：
字段列表中的“*”表示所有字段
2、有条件选择查询:
将表中符合条件的记录的某些字段挑选出来
只能用设计视图创建需设置查询条件
查询条件在“条件”和“或”行中设置
举例：
例5：
查询女学生年龄信息，显示学生编号、姓名和年龄、年龄降序排序，查询名L05
注意：
“显示”行作用指定所选字段是否在查询结果中显示
添加学生表
添加学生编号姓名年龄性别
降序排序
“女”条件
例6：
查询选修“中药学”并及格的学生成绩信息，显示学生编号、姓名和成绩，成绩升序排序，查询名L06
添加学生表、课程、选课成绩三个表
添加学生编号姓名成绩课程名称
升序排序
>=60“中药学”去掉对勾
保存
例7：
查询18和23岁的学生信息，查询名L07
注：
多个条件是逻辑“与”的关系时只是在“条件”行设置，是：
“或”的关系时分别在“条件”和“或”两行设置。
添加学生表
添加学生*年龄
18条件
23或去掉对钩
3、查询条件：
用来从表中选取所需记录的限制条件，又称查询条件，一般是一个表达式，称为条件表达式
条件表达式是一个由运算符、数据和函数组合而成且能够计算出一个结果的式子
1）数据的表示
2）ACCESS运算符
3）ACCESS内部函数
1）数据的表示：
（1）数字型：
直接表示。
例：
28、3287
（2）文本值：
用双引号（“）或单引号（‘）括起。
例：
“陈江川”、‘男’
（3）日期型：
用＃括起来。
例：
#2006-10-25#
例如：
查询２００８年９月１日入校的学生信息。
（4）是否行：
真为True/on/yes假为False/off/no
例如：
查询团员的信息
（5）空值：
isNULL
非空值：
ISNOTNULL
例如：
查询无职称的教师信息
（6）对象名和字段名：
用方括号（［］）括起，且对象名与字段名感叹号（！
）分隔
例：
［年龄］、［学生］！
姓名］
2）ACCESS算符
运算符是表达式的灵魂，系统主要提供了以下几类运算符：
（1）算术运算符
（2）文本运算符
（3）日期运算符
（4）比较运算符
（5）逻辑运算符
（6）特殊运算符
（1）算术运算符：
+、-
MOD MOD（10,3）=1MOD（2,3）=2
*、/
－（取负）
＾
（1）文本运算符
既字符串运算符，将两个字符串连接成一个字符串连接运算符，共有两个：
+、&
+要求两个运算量必须是文本型
&会将非文本型运算量转换为文本型
例：
“计算机”＋“原理”
“年龄：
”&23
结果：
“年龄：
23”
（2）日期运算符
由日期运算符（+、-）构成的表达式称为日期表达式，主要有两种格式：
格式一：
日期+、—整数1990-1-1-20
结果是该日期整数天前或后的新日期
格式二：
日期－日期
结果是两日期相隔的天数
（3）比较运算符
比较运算符是判断两个数据间的某种关系是否成立，又称关系运算符
＞、＞＝、＜、＜＝、＝、＜＞（不等于）
关系表达是结果为是否型（逻辑型）
例8：
查询1990年以前参加工作的教师信息，查询名L08.
添加表教师
添加教师*表达式1:
Year（[工作时间]）
1990条件去掉对钩
（4）逻辑运算符
运算量为是否型，常用的逻辑运算符：
AND（与）两侧都为真，结果为真2<3and2<4
or（或）一侧为真结果就为真2>3or2>4
not（非）逻辑取反
逻辑表达式运算结果为是否型（逻辑型）
例9：
查询20世纪90年代参加工作的教师信息，查询名L09
添加教师表
添加教师*表达式1:
Year（[工作时间]）
>=1990And<2000条件去掉对钩
例10:
查询不是2008年入校的男学生信息，查询l10
添加学生表
添加学生*表达式1:
Year（[入校日期]）性别
<>2008"男"条件去掉对钩
（5）特殊运算符
范围匹配：
between．．．and．．．．
例：
between20and30
Between#2007-1-1#and2007-1-31#
例11：
查询20至22岁女学生信息，要求显示学生编号、姓名、性别、年龄，查询名L11
添加表学生
添加学生编号姓名性别年龄
“女”between20And23条件
保存表
列表匹配：
in（数据列表）
例in（18,20,21,25）
In（“教授”,”副教授”,”讲师”）
例12:
查询18、20、21和23岁的非团员信息，查询名L12
添加表学生
学生*团员否年龄
Noin（18,20,21,23）
保存
模式匹配：
like模式字符串
例：
like＂王＂
like＂*授＂
*多个字符，？
一个字符
例13：
查询姓张的教师信息，查询名L13
添加表教师
教师*姓名
Like“李*”去掉对钩
保存表
例14：
查询姓名中包含“小”字的教师信息，查询名L14
添加表教师
教师*姓名
Like“*小*”去掉对钩
保存表
3）Access内部函数
函数是事先设计的一个程序，其功能是对数据进行某种处理，并返回处理的结果
其引用格式：
函数名（参数列表）一个或者多个，或者没有。
参数是处理的数据。
常用函数：
（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：
查询姓名的末尾是明字的教师信息，查询名L16
RIGHT（[姓名],1）“明”
子串截取：
MID（字符串,数值1,数值2）MID（“1234567”,2,3）=”234”
例17：
查询姓名的第二个字是“小”的教师信息，查询名L17
MID（[姓名],2,1）=”小”
求字符串长度：
LEN（字符串）
例18：
查询姓名只有两个字的教师信息，查询名L18
LEN（[姓名]）=2
空格生成：
SPACE（数值）SPACE（5）5个空格
子符重复涵数：
STRING（数值,字符串）字符串有几个重复
（3）日期时间函数
求系统日期：
DATE（数值）
例：
查询100天之内参加工作的教师信息，查询名L19
DATE（）-[工作时间]
<100条件
求年份：
YEAR（日期）
例20：
查询工龄超过10年的教师信息，要求显示教师编号、姓名、性别、工作时间，查询名L20
YEAR（date（））-YEAR（[工作时间]）>10条件
表达式一：
可改成工龄。
求月份：
MONTH（日期）
求日份：
DAY（日期）
例21：
查询9月1日入校的学生信息，查询名为L21。
二个字段，月份天数
MONTH（[入校日期]）=9DAY（[入校日期]）=1
条件可改成或者。
求星期：
WEEKDAY（日期）星期日为第1天
（1）-----
例：
查询星期二入校的学生信息，查询名为L22
WEEKDAY（[入校日期]）=3
求小时：
HOUR（日期）
4、计算型选择查询：
能够将挑选出的数据，按照某种规则进行统计和计算的查询。
（1）总计查询；
（2）分组总计查询；（3）添加计算字段。
（1）总计查询：
对表中符合条件的某些字段的值进行计算与统计
需在设计视图中的总计行设置总计项类型。
添加总计行方法：
视图菜单↓总计（或单击会计按钮）。
总计平均值
最大值最小值
计数分组
表达式条件
第一条记录
例24：
统计女学生人数、最大年龄、最小年龄和平均年龄，查询名L24。
注意：
只用于构成查询条件的字段，其总计方式为条件。
添加学生表
学生编号年龄年龄年龄性别
计数最大最小平均值条件=”女”
（2）分组总计查询
根据表中某些字段的值对表中记录进行分类汇总。
用于分组字段的“总计”方式设置成“分组”。
例25：
分别统计男女学生人数，查询名L25。
学生编号性别
计数分组
例26：
统计选修各六课程的男女学生的人数和平均成绩，查询名L26
注：
分组字段可以多个，按从左到右的顺序排定优先级，先按第一分组字段落分组，第一分组字段值相同的记录，再按第二分组字段分组，依次类推。
添加两个表：
学生、选课成绩，加上关系。
学生编号成绩课程编号性别
计数平均值分组分组
例27：
统计学生的平均成绩，显示姓名、性别各平均成绩，查询名L27
注意：
只用于显示其值的字段作为第一条或最后一条记录。
添加二个表：
学生、选课成绩，加上关系
学生编号姓名成绩性别
分组第一条记录平均值第一条记录
例28：
统计各同学修得的总学分，显示学生编号、姓名、总学分，查询名L28
计算字段是表中并不存在的字段，它的值是通过对表中某些字段进行运算而得到的，故实质是一个表达式。
添加方法：
在设计视图中的“字段”行中直接添加一个内容为表达式的新字段。
新标题：
表达式
添加三个表：
课程、选课成绩、学生，加上关系。
学生编号姓名（学分）总分
分组第一条记录总计
例29：
查询一年后学生的年龄，显示学生的编号、姓名和新年龄，查询名L29。
添加表：
学生
学生编号姓名年龄（新年龄：
[年龄]+1）
例30：
根据学生的年龄，统计学生的出生年，显示姓名和出生年，查询名L30.
添加：
学生表
姓名（年龄）出生年：
YEAR（date（））-[年龄]
例31：
查询教师工龄信息，要求显示教师编号、姓名和工龄，查询名L31。
添加：
教师表
教师编号姓名（工作时间）工龄:
year（date（））-year（[工作时间]）
例32：
统计各班学生人数和平均年龄，查询名L32。
添加学生表
班级:
mid（[学生编号],3,2）人数:
学生编号年龄
分组计数平均值
例33：
统计学生最大年龄与最小年龄差值，显示标题为SDATA，查询名L33。
s-date:
max（[年龄]）-min（[年龄]）
表达式
例34：
分别统计各班男女同学的平均成绩，查询名Links4.
添加学生表
班级:
mid（[学生编号],3,2）性别成绩
分组分组平均值
1当数据源为多个表或查询，而计算表达式中的某一字段在多个表中都存在时，引用时必须指定来源
例35：
分别统计男女学生最大年龄与最小年龄的差值，显示性别和sdata，查询名L35
性别s-date:
max（[年龄]）-min（[年龄]）
分组表达式
②表达式中可以包含统计函数，此时该字段的总计方式为表达式。
例36：
查询学生基本信息，要求包含学生编号、姓、名、性别、年龄，查询名L36（不考虑复姓）
学生编号姓:
left（[姓名]）,1）名:
mid（[姓名],2,2）年龄
例37：
查询学生基本信息，要求包含学生编号、姓名、性别、年龄、入校日期和修得的总学分，但结果中只有二个字段为学生基本信息和总学分，查询名L37.
添加三个表：
学生、选课成绩、课程，加上关系
学生信息：
[学生]!
[学生编号]+[姓名]&[性别]&[年龄]&[入校日期]
第三节创建交叉表查询
1、交叉表的功能；2、使用向导创建；3、使用设计视图创建
1、交叉表的功能
将来源于某个表中的字段进行分组，一组列在数据表的左侧，一组列在数据表的上部，然后在数据表行与列的交叉处显示表中某个字段的各种计算值。
创建时，需指定三种字段：
行标题字段（左侧）
列标题字段（上部）
总计项字段（行列交叉处）
2、使用向导创建
例38：
统计不同年龄男女学生人数的交叉表查询，查询名L38.
（1）新建↓交叉表查询向导；
（2）选择数据源；
（3）分别确定行标题字段、列标题字段和总计项字段及总计函数；
（4）指定查询名称。
注意：
该方法只能创建单数据源的交叉表查询。
打开查询表向导，点右键选查询类型里的交叉表
添加学生表
性别年龄学生编号
分组分组计数
行标题列标题值
3、使用设计视图创建
例39：
统计不同年龄男女学生人数的交叉表查询L39
①设计视图选数据源；
②字段行添加行标题、列标题和总计项字段；
③添加交叉表和总计行（查询菜单↓交叉表查询或查询类型按钮↓交叉表查询）；
④总计行中行标题和列标题字段的总计方式为分组，总计项为一统计函数；
⑤交叉表行设置行标题，列标题和总计项字段的相应值为行标题、列标题、值
⑥保存指定查询名。
例40：
统计各个班级选修各门课程的人数，查询名l40.
注意：
行标题和列标题字段是计算字段。
添加学生、选课成绩、课程三个表，交叉表
班级:
left（[学生]）!
[学生编号],6）课程名称学生编号
分组分组计数
行标题列标题值
第四节创建参数查询
运行时临时指定查询条件的查询。
该查询运行时将弹出一个对话框，提示输入查询参数（条件）。
用[条件提示信息]替代查询条件
条件
提示信息
例41：
按性别查询学生基本信息，运行查询时提示”请输入性别:
”，要求显示姓名，性别，年龄和入校日期，查询名l41.
姓名性别年龄入校日期
[”请输入性别:
”]条件
例42：
按班级和课程名称查询学生成绩信息，提示“请输入班级：
”和“请输入课程名称：
”，显示姓名和成绩，查询名L42。
添加三个表：
学生，选课成绩，课程
姓名成绩表达式1：
left（[学生]）!
[学生编号],6）课程名称
[请输入班级：
][请输入课程名称：
]
注意：
条件提示信息也可以是多个。
第五节创建操作查询
可以自动完成对表中的数据执行一个选定操作的查询。
根据所执行操作的不同，可分为以下四类：
1、生成表查询；2、删除查询；3、更新查询；4、追加查询
1、生成表查询
将查询结果生成一个新表
查询类型设置为生成表查询，指定新表名称及保存位置。
例43：
创建一个生成表查询，新表名称为“90分以上学生情况”，包括学生编号，姓名，性别，年龄和成绩字段，查询名为L43
添加二个表：
学生，选课成绩，选生成表查询
学生编号姓名性别入校日期成绩
>=90条件
生成新表：
90分以上学生情况查询名L43，生成后双击L43执行或者点！
执行，出现新表90分以上学生情况，修改设计。
例44：
根据老师表生成toffice表（系别，联系电话），查询名分别为L44
添加教师表，右键选生成表查询
系别联系电话
输入toffice表名，查询名L44
2、删除查询
自动删除表中符合条件的记录；
（1）设计视图，添加数据源；
（2）设置查询类型为删除查询；
（3）字段行添加构成删除准则的字段，并指定其“删除”行值为“Where”;
（4）设置删除准则；
（5）保存。
例45：
创建一个删除经济系讲师信息的查询，查询名L45
添加表：
教师，选删除查询
教师*系别职称
“经济系”“讲师”
点！
执行
例46：
创建一个删除200803班不是团员的女学生信息的查询，查询名L46.
注意：
表间关系一经建立并设置了参照完整性规则，则对表的所有操作都必须遵守该规则。
也就是说子表有数据，不允许删除父表。
添加表：
学生，选删除查询。
学生*left（[学生编号],6）性别团员否
200803“女”no
点！
执行
3、更新查询
根据某种规则自动对表中符合条件的数据进行更新。
（1）设计视图，添加数据源；
（2）设置查询类型为更新查询；
（3）添加需要更新的字段，并在更新到行指定更新值；
（4）添加更新准则字段，并在“准则”行设置其更新准则;
（5）保存。
例47：
创建一个将所有学生年龄增加一岁的查询，查询名L47
添加学生表
年龄
[年龄]+1更新到
保存并运行！
例48：
创建一个将所有教师的联系电话前加“8”的查询，查询名l48
添加表教师
联系电话
“8”&[联系电话]
保存并运行！
例49：
创建一个将200804班改为200802班的查询，查询名为l49
添加学生表
Left（[学生编号],6）学生编号
“200802”&right（[学生编号],2）更新到
200804条件
注意：
学生编号替换后还剩几位&right（[学生编号],2）。
4、追加查询
将查询结果添加到另一表尾部。
（1）设计视图，添加数据源；
（2）添加查询（所要追加的）字段；
（3）设置查询条件（所追加记录的条件）；
（4）设置查询类型为追加查询，并指定追加到的表；
（5）保存。
例50：
创建将学生基本信息（学生编号、姓名、性别、年龄、入校日期）备份到T2表的查询，查询名L50
先复制学生表结构到T2，选学生表，选追加查询注意T2表是已存在的，并且结构要一样。
学生编号姓名性别年龄入校日期
保存
例51：
创建将选中药学课程并及格的学生信息备份到T1表（学生编号、姓、名、性别）的查询，查询名L51
添加表：
学生、选课成绩、课程
学生编号姓:
Left（[姓名],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语句（查询）。
1、Create语句:
新建一个表的格式：
CREATETABLE<表名>（<字段名1><数据类型1>[字段级完整性约束条件1]
[,<字段名2><数据类型2>[字段级完整性约束条件2][,---]
[,<字段名n><数据类型n>[字段级完整性约束条件n][,---]
[,<表级完整性约束条件1>];）
需记忆的：
字符型char:
长度
备注型memo:
主关键字：
PrimatyKey
字段是否允许为空：
Notnull
Null
完整性约束：
Check
文本
Text（n）/char注：
n为字段大小；char相当于text（255）
备注
Txt/Memo
数字[字节]
Byte/Tinylnt
数字[整型]
Short/Smalllnt
数字[长整型]
Long/lnteger
数字[单精度]
Single
数字[双精度]
Diuble/Fliat
数字[小数]
Decimal（m,n）注：
m为精度，n为数值范围
数字[同步复制ID]
GUID
日期、时间
DateTime/Date/Time
货币
Currency/Miney
自动编号
Autolncrement（m,n）注：
m为初始值，n为步进值
是/否
Bit/YesNo
OLE对象
lmage
例52：
创建一个“雇员”表，包括雇员号、姓名、性别、出生日期、部门、备注字段。
CREATETABLE雇员（雇员号SMALLINTPrimaryKey,姓名CHAR（4）NotNull,性别CHAR
（1）,出生日期DATE,部门CHAR（20）,备注MEMO）
CREATETABLE雇员（雇员号SMALLINTPrimaryKey（第一个字段，数字整形-主关键字）,姓名CHAR（4）NotNull（字符型，不允许为空）,性别CHAR
（1）（1个长度的字符型）,出生日期DATE（日期型）,部门CHAR（20）,备注MEMO（备注））
复制一下以上的语句，打开查询，在设计视图中创建查询，右键SQL视图，粘贴。
2、Alter语句
ALTERTABLE<表名〉
[ADD<新字段名﹥<数据类型﹥]
[字段级完整性约束条件]增加字段
[DROP[<字段名>]]删除字段
[ALTER<字段名><数据类型>]修改原
升级会员 