第四五章 数据查询及SQL.docx
- 文档编号:29122032
- 上传时间:2023-07-20
- 格式:DOCX
- 页数:20
- 大小:23.71KB
第四五章 数据查询及SQL.docx
《第四五章 数据查询及SQL.docx》由会员分享,可在线阅读,更多相关《第四五章 数据查询及SQL.docx(20页珍藏版)》请在冰豆网上搜索。
第四五章数据查询及SQL
第四、五章考点
1.查询的功能、视图与分类;
2.查询准则:
数据的表示、系统内部函数的语法与运算符的运算规则;
3.选择查询的创建:
无条件选择查询、有条件选择查询与计算型选择查询;
4.计算字段的添加方法;
5.交叉表查询的创建;
6.参数查询的创建;
7.操作查询的创建:
生成表查询、删除查询、更新查询与追加查询;
8.SELECT语句的一般语法规则;
9.子查询的创建;
10.查询的编辑(字段的添加、删除、移动、排序和属性设置)。
第四章数据查询
第一节认识查询
第二节创建选择查询
第三节创建交叉表查询
第四节创建参数查询
第五节创建操作查询
第一节认识查询
一、什么是查询
二、查询的功能
三、查询的分类
四、查询的视图
一、什么是查询
查询是能够将存储于一个或多个表中符合要求的数据挑选出来,并对挑选的结果按照某种规则进行运算的对象。
二、查询的功能
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)查询向导
例1:
查询学生年龄,要求只显示学生编号、姓名和年龄,查询名L01。
①启动简单查询向导:
新建→简单查询向导(或双击使用向导创建查询);
②指定数据源(学生表)及字段(学生编号、姓名、年龄);
③指定查询标题(L01)。
举例:
例2:
查询学生的选课成绩,要求只显示学生编号、姓名、所选课程名和成绩,查询名L02。
注意:
若查询数据源为多个表,则多个表之间必须已建立关系。
(2)设计视图
例3:
查询学生年龄,显示学生编号、姓名和年龄,年龄升序排序,查询名L03。
①打开查询设计视图:
新建→设计视图(或双击在设计视图中创建查询);
②显示表中选择数据源;
③字段行指定查询字段(三种方法:
拖动,双击,选择),排序行指定排序方式;
④保存并指定查询名称。
举例:
例4:
查询学生选课情况,显示学生编号、姓名、所选课程编号、课程名、课程类别和学分,查询名L04。
注意:
字段列表中的“*”表示所有字段
2.有条件选择查询
将表中符合条件的记录的某些字段挑选出来。
只能用设计视图创建,需设置查询条件。
查询条件在“条件”和“或”行中设置。
举例:
例5:
查询女学生年龄信息,显示学生编号、姓名和年龄,年龄降序排序,查询名L05。
注意:
“显示”行作用指定所选字段是否在查询结果中显示。
例6:
查询选修“数据结构”并及格的学生成绩信息,显示学生编号、姓名和成绩,成绩升序排序,查询名L06。
举例:
例7:
查询18和23岁的学生信息,查询名L07。
注:
多个条件是逻辑“与”的关系时只在“条件”行设置,是“或”的关系时分别在“条件”和“或”两行设置。
3.查询条件
用来从表中选取所需记录的限制条件,又称查询条件,一般是一个表达式,称为条件表达式。
条件表达式是一个由运算符、数据和函数组合而成且能够计算出一个结果的式子。
1)数据的表示
2)Access运算符
3)Access内部函数
1)数据的表示
(1)数字型:
直接表示。
例:
28、-3.287
(2)文本值:
用双引号(")或单引号(')括起。
例:
"陈江川"、'男'
(3)日期型:
用#括起来。
例:
#2006-10-25#
例如:
查询2008年9月1日入校的学生信息。
(4)是否型:
真为True/On/Yes,假为False/Off/No。
例如:
查询团员的信息。
(5)空值:
isNull
非空值:
isNotNull
例如:
查询无职称的教师信息。
(6)对象名和字段名:
用方括号([])括起,且对象名与字段名用感叹号(!
)分隔。
例:
[年龄]、[学生]!
[姓名]
2)Access运算符
运算符是表达式的灵魂,系统主要提供了以下几类运算符:
(1)算术运算符
(2)文本运算符
(3)日期运算符
(4)比较运算符
(5)逻辑运算符
(6)特殊运算符
(1)算术运算符
+、-
Modmod(10,3)=1mod(4,2)=0mod(2,3)=2
\
*、/
-(取负)
^2^3=8
(2)文本运算符
即字符串运算符,将两个字符串连接成一个字符串又称字符串连接运算符,共有两个:
+、&。
+要求两个运算量必须是文本型
&会将非文本型运算量转换为文本型。
例:
“计算机”+“原理”
结果:
“计算机原理”
“年龄:
”&23
结果:
“年龄:
23”
(3)日期运算符
由日期运算符(+、-)构成的表达式称为日期表达式,主要有两种格式:
格式一:
日期±整数
结果是该日期整数天前或后的新日期
格式二:
日期–日期
结果是两日期相隔的天数
(4)比较运算符
比较运算符是判断两个数据间的某种关系是否成立,又称关系运算符。
>、>=、<、<=、=、<>(不等于)
关系表达式结果为是否型(逻辑型)。
例8:
查询1990年以前参加工作的教师信息,查询名L08。
(4)逻辑运算符
运算量为是否型,常用的逻辑运算符:
and(与) 两侧都为真,结果为真2<3and2<4
or(或) 一侧为真结果就为真2>3or2>4
not(非) 逻辑取反
逻辑表达式运算结果为是否型(逻辑型)。
例9:
查询20世纪90年代参加工作的教师信息,查询名L09。
例10:
查询不是2008年入校的男学生信息,查询名L10。
(5)特殊运算符
范围匹配:
between…and…
例:
between20and30
between#2007-1-1#and#2007-1-31#
例11:
查询20至22岁女学生信息,要求显示学生编号、姓名、性别、年龄,查询名L11。
列表匹配:
in(数据列表)
例:
in(18,20,21,25)
in(“教授”,”副教授”,“讲师”)
例12:
查询18、20、21和23岁的非团员信息,查询名L12。
模式匹配:
like模式字符串
例:
like“王*”
like“*授”
*多个字符,?
一个字符
例13:
查询姓张的教师信息,查询名L13。
例14:
查询姓名中包含“小”字的教师信息,查询名L14。
3)Access内部函数
函数是事先设计的一个程序,其功能是对数据进行某种处理,并返回处理的结果。
其引用格式:
函数名(参数列表)
参数是处理的数据
常用的系统内部函数有以下几类:
(1)数值函数
(2)字符函数
(3)日期时间函数
(1)数值函数
绝对值:
Abs(数值)
平方根:
Sqr(数值)
符号:
Sgn(数值) 正数返回1,0返回0,负数返回-1
取整:
Int(数值)
(2)字符函数
左子串截取:
Left(字符串,数值)
例15:
查询200803班学生信息,查询名L15。
右子串截取:
Right(字符串,数值)
例16:
查询姓名的末尾是“明”字教师信息,查询名L16。
子串截取:
Mid(字符串,数值1,数值2)
例17:
查询姓名的第二个字是“小”的教师信息,查询名L17。
求字符串长度:
Len(字符串)
例18:
查询姓名只有两个字的教师信息,查询名L18。
空格生成:
Space(数值)
字符重复函数:
String(数值,字符串)
(3)日期时间函数
求系统日期:
Date()
例19:
查询100天之内参加工作的教师信息,查询名L19。
求年份:
Year(日期)
例20:
查询工龄超过10年的教师信息,要求显示教师编号、姓名、性别、工作时间查询名L20。
求月份:
Month(日期)
求日份:
Day(日期)
例21:
查询9月1日入校的学生信息,查询名为L21。
求星期:
Weekday(日期) 星期日为第1天
(1),星期一为第2天(2)…
例22:
查询星期二入校的学生信息,查询名为L22。
求小时:
Hour(日期)
4.计算型选择查询
能够将挑选出的数据按照某种规则进行统计和计算的查询。
(1)总计查询
(2)分组总计查询
(3)添加计算字段
(1)总计查询
对表中符合条件的某些字段的值进行计算与统计。
需在设计视图中的总计行设置总计项类型。
添加总计行方法:
视图菜单→合计(或单击合计按钮)。
总计项类型:
总计 平均值
最大值最小值
计数 分组
表达式条件
第一条记录
最后一条记录
例23:
统计学生人数和平均年龄,查询名L23。
重新指定字段标题方法:
新标题:
原标题
例24:
统计女学生人数、最大年龄、最小年龄和平均年龄,查询名L24。
注意:
只用于构成查询条件的字段,其总计方式为条件。
(2)分组总计查询
根据表中某些字段的值对表中记录进行分类汇总。
用于分组字段的“总计”方式设置成“分组”。
例25:
分别统计男女学生人数,查询名L25。
例26:
统计选修各门课程的男女学生的人数和平均成绩,查询名L26。
注:
分组字段可以多个,按从左到右的顺序排定优先级,先按第一分组字段分组,第一分组字段值相同的记录再按第二分组字段分组,依次类推。
例27:
统计学生的平均成绩,显示姓名、性别和平均成绩,查询名L27。
注意:
只用于显示其值的字段,总计方式可为第一条记录或最后一条记录。
例28:
统计各同学修得的总学分,显示学生编号、姓名、总学分,查询名L28。
计算字段是表中并不存在的字段,它的值是通过对表中某些字段进行运算而得到的,故其实质是一个表达式。
添加方法:
在设计视图中的“字段”行中直接添加一个内容为表达式的新字段。
新标题:
表达式
例29:
查询一年后学生的年龄,显示学生编号、姓名和新年龄,查询名L29。
例30:
根据学生年龄,统计学生的出生年,显示姓名和出生年,查询名L30。
例31:
查询教师工龄信息,要求显示教师编号、姓名和工龄,查询名L31。
例32:
统计各班学生人数和平均年龄,查询名L32。
例33:
统计学生最大年龄与最小年龄差值,显示标题为s_data,查询名L33。
例34:
分别统计各班男女同学的平均成绩,查询名L34。
①当数据源为多个表或查询,而计算表达式中的某一字段在多个表中都存在时,引用时必须指定来源于哪个表。
例35:
分别统计男女学生最大年龄与最小年龄的差值,显示性别和s_data,查询名L35。
②表达式中可以包含统计函数,此时该字段的总计方式为表达式。
例36:
查询学生基本信息,要求姓和名分开显示,显示学生编号、姓、名、性别、年龄,查询名L36。
(注:
不考虑复姓)
例37:
查询学生基本信息,要求包含学生编号、姓名、性别、年龄、入校日期和修得的总学分,但结果中只有两个字段为学生基本信息和总学分,查询名L37。
第三节创建交叉表查询
1.交叉表的功能
2.使用向导创建
3.使用设计视图创建
1.交叉表的功能
将来源于某个表中的字段进行分组,一组列在数据表的左侧,一组列在数据表的上部,然后在数据表行与列的交叉处显示表中某个字段的各种计算值。
创建时,需指定三种字段:
行标题字段(左侧)
列标题字段(上部)
总计项字段(行列交叉处)
2.使用向导创建
例38:
统计不同年龄男女学生人数的交叉表查询,查询名L38。
(1)新建→交叉表查询向导;
(2)选择数据源;
(3)分别确定行标题字段、列标题字段和总计项字段及总计函数;
(4)指定查询名称。
注意:
该方法只能创建单数据源的交叉 表查询。
3.使用设计视图创建
例39:
统计不同年龄男女学生人数的交叉表查询L39。
①设计视图选数据源;
②字段行添加行标题、列标题和总计项字段;
③添加交叉表和总计行(查询菜单→交叉表查询或查询类型按钮→交叉表查询);
④总计行中行标题和列标题字段的总计方式为分组,总计项为一统计函数;
⑤交叉表行设置行标题、列标题和总计项字段的相应值为行标题、列标题、值;
⑥保存指定查询名。
例40:
统计各个班级选修各门课程的人数,查询名L40。
注意:
行标题和列标题字段可以是计算字段。
第四节创建参数查询
运行时临时指定查询条件的查询。
该查询运行时将弹出一个对话框,提示输入查询参数(条件)。
用[条件提示信息]替代查询条件
例41:
按性别查询学生基本信息,运行查询时提示“请输入性别:
”,要求显示姓名,性别,年龄和入校日期,查询名L41。
例42:
按班级和课程名称查询学生成绩信息,提示“请输入班级:
”和“请输入课程名称:
”,显示姓名和成绩,查询名L42。
注意:
条件提示信息也可以是多个。
第五节创建操作查询
可以自动完成对表中的数据执行一个特定操作的查询。
根据所执行操作的不同,可分为以下四类:
1.生成表查询
2.删除查询
3.更新查询
4.追加查询
1.生成表查询
将查询结果生成一个新表。
查询类型设置为生成表查询,指定新表名称及保存位置。
例43:
创建一个生成表查询,新表名称为“90分以上学生情况”,包括学生编号,姓名,性别,年龄和成绩字段,查询名为“L43”
例44:
根据教师表生成tOffice表(系别,联系电话),查询名分别为L44。
2.删除查询
自动删除表中符合条件的记录;
(1)设计视图,添加数据源;
(2)设置查询类型为删除查询;
(3)字段行添加构成删除准则的字段,并指定其“删除”行值为“Where”;
(4)设置删除准则;
(5)保存。
例45:
创建一个删除经济系讲师信息的查询,查询名L45。
例46:
创建一个删除200803班不是团员得女学生信息的查询,查询名L46。
注意:
表间关系一经建立并设置了参照完整性规则,则对表的所有操作都必须遵守该规则。
3.更新查询
根据某种规则自动对表中符合条件的数据进行更新。
(1)设计视图,添加数据源;
(2)设置查询类型为更新查询;
(3)添加需要更新的字段,并在更新到行指定更新值;
(4)添加更新准则字段,并在“准则”行设置其更新准则;
(5)保存。
例47:
创建一个将所有学生年龄增加一岁的查询,查询名L47。
例48:
创建一个将所有教师的联系电话前加“8”的查询,查询名L48。
例49:
创建一个将200804班改为200802班的查询,查询名为L49。
4.追加查询
将查询结果添加到另一表尾部。
(1)设计视图,添加数据源;
(2)添加查询(所要追加的)字段;
(3)设置查询条件(所追加记录的条件)
(4)设置查询类型为追加查询,并指定追加到的表;
(5)保存。
例50:
创建将学生基本信息(学生编号、姓名、性别、年龄、入校日期)备份到t2表的查询,查询名L50。
例51:
创建将选数据结构课程并及格的学生信息备份到t1表(学生编号、姓、名、性别)的查询,查询名L51。
注意:
所要追加的字段可以来源于多个表 或是计算字段,但必须是目标表中 已经存在的字段。
第五章创建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>];
例52:
创建一个“雇员”表,包括雇员号、姓名、性别、出生入期、部门、备注字段。
CREATETABLE雇员(雇员号SMALLINTPrimaryKey,姓名CHAR(4)NotNull,性别CHAR
(1),出生日期DATE,部门CHAR(20),
备注MEMO);
2.Alter语句
ALTERTABLE<表名>
[ADD<新字段名><数据类型>
[字段级完整性约束条件]]
[DROP[<字段名>]…]
[ALTER<字段名><数据类型>];
例53:
在“雇员”表中增加一个字段,字段名为“职务”,数据类型为“为本”;将“备注”字段删除;将“雇员号”字段的数据类型改为文本型,字段大小为8。
(1)添加新字段的SQL语句为:
ALTERTABLE雇员ADD职务CHAR(10);
(2)删除“备注”字段的SQL语句为:
ALTERTABLE雇员DROP备注;
(3)修改“雇员号”字段属性的SQL语句为:
ALTERTABLE雇员ALTER雇员号CHAR(8);
3.Drop语句
DROPTABLE<表名>;
其中,<表名>是指要删除的表的名称。
例54:
删除已建立的“雇员”表。
DROPTABLE雇员;
4.Insert语句
INSERT语句实现数据的插入功能,可以将一条新记录插入到指定表中。
其语句格式为:
INSERTINTO<表名>[(<字段名1>[,<字段名2>…])]
VALUES(<常量1>[,<常量2>]…);
例55:
将一条新记录插入到“雇员”表中。
INSERTINTO雇员VALUES("0001","张磊","男",#1960-1-1#,"办公室");
注意,文本数据应用双引号括起来,日期数据应用“#”号括起来。
例56:
将一条新记录插入到“雇员”表中,其中“雇员号”为“0002”,“姓名”为“王宏”,“性别”为“男”。
INSERTINTO雇员(雇员号,姓名,性别)VALUES("0002","王宏","男");
5.Update语句
UPDATE语句实现数据的更新功能,能够对指定表所有记录或满足条件的记录进行更新操作。
该语句的格式为:
UPDATE<表名>
SET<字段名1>=<表达式1>[,<字段名2>=<表达式2>]…[WHERE<条件>];
例57:
将“雇员”表张磊的出生日期改为“1960-1-11”
UPDATE雇员SET出生日期=#1960-1-11#WHERE姓名="张磊";
6.Delete语句
DELETE语句实现数据的删除功能,能够对指定表所有记录或满足条件的记录进行删除操作。
该语句的格式为:
DELETEFROM<表名>
[WHERE<条件>];
例58:
将“雇员”表中雇员号为“0002”的记录删除。
DELETEFROM雇员WHERE雇员号="0002";
7.Select语句
SQL语言只提供了一个查询语句:
SELECT语句功能是根据指定的条件对表中数据进行查找或计算。
其一般语法规则:
SELECT[ALL|DISTINCT]*|字段列表
FROM表名列表
WHERE条件表达式
GROUPBY分组字段列表
ORDERBY排序字段列表[ASC|DESC]
(1)字段列表:
欲查询的字段,包含计算字段,*:
所有字段
(2)ALL:
所有符合条件的记录,包括重复记录,DISTINCT:
去掉重复记录
(3)FROM子句:
指定查询数据源
(4)WHERE子句:
设置查询准则
(5)GROUPBY子句:
指定分组依据
(6)ORDERBY子句:
指定排序字段及方式(ASC为升序(默认),DESC为降序)
(7)各表名及字段之间必须用逗号(,)分隔
(8)FROM子句必选,其它子句可选
(9)SELECT必须位于语句开头,其他子句次序无关紧要。
例59:
查找并显示“教师”表中所有的字段。
例60:
查找并显示“教师”表中“姓名”、“性别”、“工作时间”和“系别”4个字段。
例61:
查找1992年以后(包含)参加工作的男教师,并显示“姓名”,“性别”,“学历”,“职称”,“系别”和“联系电话”。
例62:
计算各类职称的教师人数,并将计算字段命名为“各类职称人数”,显示职称和各类职称人数。
重新指定字段标题方法:
原标题 As 新标题
例63:
计算每名学生的平均成绩,并按平均成绩降序显示。
例64:
查找学生的选课成绩,并显示“学生编号”,“姓名”,“课程名称”和“成绩”。
三、创建SQL特定查询
1.创建联合查询
2.创建传递查询
3.创建数据定义查询
4.创建子查询
SQL子查询
所谓子查询就是包含在另一个Select语句中的Select语句。
查询设计视图的“条件”行和“或”行中包含Select子查询语句。
例68:
查询并显示“学生”表中高于平均年龄的学生记录。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 第四五章 数据查询及SQL 第四 数据 查询 SQL