第4章计算机二级查询

4.1查询的创建与编辑4.2查询的分类4.3SQL查询第四章查询4.1查询的创建与编辑查询是对数据库中的表数据进行查找，同时产生一个类似于表的结果。查询可以作为结果，也可以作为来源，即查询可以根据条件从数据表中检索数据，并将结果存储起来；查询也可以作为创建表、查询、窗体或报表的数据源。根据其应用目的不同，查询的分为5种类型：选择查询、计算查询、参数查询、操作查询、SQL查询4.1.1查询简介4.1.2查询视图Access中的查询视图主要包括：1、数据表视图：主要用于在行和列格式下显示表、查询以及窗体中的数据。用户可以通过这种方式进行打开查询、查看信息、更改数据、追加记录和删除记录等操作。2、设计视图：设计查询的窗口，主要用于查询的编辑操作。窗口中包含了创建查询所需要的各个组件。3、SQL视图：自动创建与查询对应的SQL语句。用户也可以通过修改SQL语句，灵活地改变查询。常用的查询向导有：

1、简单查询向导

2、交叉表查询向导

3、查找重复项查询向导

4、查找不匹配项查询向导4.1.3查询向导1、指定检索字段：检索“读者信息表”中所有记录的姓名、权限和身份证号2、汇总数值字段：检索“图书信息表”中价格最高的书籍将数据分别行列显示，并在交叉点进行数据汇总运算：统计“读者信息表”中各权限男女读者的人数确定表中是否存在重复项：查找“图书信息表”中书籍价格相同的记录查找表中与其他记录不相关的记录：查找有哪些读者还未借过书查询准则查询准则是用来在查询中限制检索记录的表达式，表达式一般由常量、变量、函数和各类运算符等构成，通常为条件表达式或逻辑表达式。

计算机是根据表达式运算结果的真或假来判断是否应该进行记录的检索。1、运算符分类常用的运算符算术运算符+、-、*、/、\、^、MOD关系运算符=、<>、<、<=、>、>=逻辑运算符NOT、AND、OR特殊运算符IN：用于指定一个字段值的列表BETWEEN：用于指定一个字段的范围LIKE：用于指定查找文本字段的字符模式ISNULL：用于指定一个字段为空为方便用户更高效、准确地进行数据运算，Access提供了大量的标准函数，并利用这些函数构造查询准则。函数类型举例数值函数Abs()、sqr()、sgn()int()、fix()、round()字符函数left()、right()、mid()space()、Cstr()、len()

日期函数day()、month()、year()hour()、minute()、second()date()、time()、now()weekday()统计函数sum()、avg()、max()、min()、count()2、函数字段准则功能职称=“副教授”院系=“外语”or“会计”课程名

like“*计算机*”院系

in（“外语”，“会计”）姓名

notlike“王*”姓名

left([姓名],1)=“张”姓名

len([姓名])<=4编号

mid([编号],1,2)=“12”查询准则示例1、文本值查询职称为“副教授”的记录查询院系为“外语”或“会计”的记录查询课程名中含“计算机”的记录查询院系为“外语”或“会计”的记录查询不姓“王”的记录查询姓“张”的记录查询名字小于或等于4个字的记录查询编号的前2位为“12”的记录字段名准则功能工作时间between#1995-01-01#and#1995-12-31#工作时间<date()-15出生日期weekday([出生日期])=7出生日期year([出生日期])=year(date())Andmonth([出生日期])=42、日期值查询工作时间在95年的记录查询工作时间在15天以上的记录查询星期六出生的记录查询出生日期为今年4月份的记录4.1.4查询设计器1、编辑查询字段增加字段删除字段显示/隐藏字段移动字段修改字段标题2、运行查询运行按钮视图切换按钮4.2查询分类按照应用目的的不同，Access中的查询分为五类：选择查询计算查询参数查询操作查询

SQL查询4.2.1选择查询选择查询是Access支持的多种类型查询对象中最常见、最重要的一种，它从一个或多个表中根据准则（或条件）检索数据。选择查询就是对数据的筛选，是在大量数据中筛选出满足条件的若干行和列记录。1、显示检索数据的全部字段内容举例：检索所有男读者选择查询的设计主要包括以下几种情况：2、显示检索数据的部分字段内容举例：检索所有男读者显示字段：(读者姓名、出生日期、性别)3、带多个条件的数据检索举例：(1)检索所有90后男读者(2)检索“人民邮电出版社”和“电子工业出版社”书籍4、对运算后的字段进行条件检索举例：(1)检索下旬出生的读者(2)检索书名超过(不包含)6个字的书籍练习：(1)检索出生日期为单号的读者思考：显示所有“人民邮电出版社”书籍的借阅情况（读者姓名、书籍名称、出版社、借书日期、还书日期）(2)检索星期三出生的读者(3)查找所有90-95年之间出生的读者4.2.2计算查询计算查询通过在查询对象中设计一个或多个计算查询列来完成表内部或各表之间数据的运算。计算查询主要有三类操作：添加新字段(自定义计算查询)单字段汇总(预定义计算/汇总查询)多字段汇总(交叉表计算查询)选择查询的设计主要包括以下几种情况：在实际查询过程中，我们经常会碰到这样一个情况：需要对已有的数据进行计算，再将计算结果显示出来。这时候就可以使用“添加字段计算”的方法了。1、添加新字段在Access中，添加字段计算有一个固定的方法，就是将计算表达式写在查询设计器的“字段”行。【基本格式】显示字段名：字段计算表达式运行查询时，出现在“字段名”位置半角的冒号，是表达式前后参数的分割符运行查询时，表达式的运算结果出现在对应字段的记录行举例：(1)显示所有读者信息(姓、名、出生日期、性别)练习：检索下半年出生的读者基本信息(姓名、出生日期、生日月份)思考：计算读者年龄的差值(年龄差值：最大年龄-最小年龄)(2)计算所有读者的年龄(姓名、性别、年龄)我们利用查询计算数据的时候，也经常会碰到需要进行“分类汇总”的情况，也就是说，我们需要的数据不能直接从数据表中获取，而需要对来源表进行一些函数运算才能总结出来。这时候就需要用到“汇总计算”了。2、单字段汇总汇总计算可以分为单字段汇总和多字段汇总2种，Access也提供了一个进行汇总计算的固定方法:1、设计—显示/隐藏—汇总（我们也可以右击查询设计器的下半部分，找到“汇总”命令）2、再在“总计”行进行汇总方式的设置汇总计算利用预定义方式就可以实现，系统提供了一些常用的聚合函数，方便用户进行快速设置。总计项含义总计项含义SUM求某字段的累加值VAR求某个字段值的方差AVG求某个字段的平均值GROUPBY定义要执行计算的组MIN求某个字段的最小值FIRST求在表中或查询中第一条记录的字段值MAX求某个字段的最大值LAST求在表或查询中最后一条记录的字段值COUNT求某个字段中的非空值数EXPRESSION

创建表达式中包含统计函数的计算字段STDEV求某个字段值的标准偏差WHERE指定不可用于分组的字段准则举例：(1)统计各权限的读者人数(2)计算各出版社书籍的平均价格(3)统计各出版社书籍的平均价格(保留一位小数)(4)检索各出版社最贵的书籍(5)检索“人民邮电出版社”最贵的书籍练习：分别计算男女读者的平均年龄(平均年龄保留一位小数)思考：统计学生权限的男女读者人数(权限、性别、人数)从上例可以看出，进行分类汇总运算操作的时候，不一定只有一个字段的分组，如果有多个字段的分组，显示的数据就没有那么简洁了。3、多字段汇总为迎合用户的计算需求，Access还提供了一种多字段汇总的方式——交叉表汇总。我们以对两个或两个以上的字段分组，对第三个字段进行汇总的查询为例，这种汇总方式是将一个分组字段的值放在查询结果的第一列(即行标题位置)，再将另一个分组字段的值放在查询结果的第一行(即列标题位置)，最后将汇总的结果放在行和列的交叉单元格内形成统计结果，所以，我们称之为“交叉表”。交叉表查询的设计并不复杂，系统提供了交叉表查询的设计方式，用户在建立了一个新的查询后，只需要点击“设计—查询类型—交叉表”就可以切换到交叉表查询了。同前面的“汇总计算”相比，交叉表的设计多出了一个“交叉”行，用户可以利用这一行来设置数据的排列方式。举例：(1)统计各权限男女读者的人数(2)统计各年份入校的男女读者人数练习：统计各年龄的男女读者人数思考：统计各出版社不同价格段的书籍个数(每10元为一个价格段，0-10,11-20,21-30……)4.2.3参数查询参数查询是动态的，它利用输入框(InputBox)提示用户输入参数并检索符合所输入参数的记录或值。举例：(1)输入权限，查找该权限的所有读者记录(2)输入权限和年份，查找该权限对应年份出生的所有读者基本方法：在条件行输入运算符[提示语句](3)输入姓氏，查找该姓氏的所有读者(4)输入年龄，查找大于该年龄的所有读者练习：输入任意一个汉字，查找姓名中包含这个汉字的所有读者思考：输入年龄下限和年龄上限，查找该年龄段的所有读者4.2.4操作查询操作查询是指仅在一个操作中更改多个记录的查询。主要包括：更新查询、追加查询、删除查询、生成表查询。

操作查询是对原有数据的破坏操作，创建该类型的查询前，建议对数据进行必要的备份！(1)更新查询：对表中数据进行有规律的成批替换。更新操作会破坏原有数据，在运行查询前，必须进行原有数据的备份。举例：将所有学生的读者编号前2位改为“24”，后面字符不变（如“5010006”改为“2410006”）操作步骤：1、打开“查询设计视图”2、在工具栏的“设计”选项卡的“查询类型”分组中点击“更新查询”3、在“字段”栏选择需要更新的字段；在“条件”栏输入需要更新数据必须满足的条件；在“更新到”栏输入更新表达式4、保存查询后，再次运行查询就可以更新指定数据了思考：将所有书籍价格提高5%练习：将读者编号的3-4位替换为入校年份(2)追加查询：对某个数据表进行筛选后，将数据追加到另一个相同结构的数据表中。举例：将所有“教职工”和“学生”权限的读者追加到Reader表中(假设已存在一个与读者信息表相同结构的Reader表)操作步骤：1、复制"读者信息表"表结构到stu2、新建查询，打开“查询设计视图”3、在工具栏的“设计”选项卡的“查询类型”分组中点击“追加查询”3、在“条件”栏设置条件；4、保存查询后，再次运行查询就可以更新指定数据了思考：条件还可以如何设置？In(“教职工”,”学生”)(3)删除查询：对现有表中满足条件的记录进行删除。删除操作会破坏原有数据，在运行查询前，必须进行原有数据的备份。举例：删除所有“教职工”权限的读者操作步骤：1、新建查询，打开“查询设计视图”3、在工具栏的“设计”选项卡的“查询类型”分组中点击“删除查询”3、在“条件”栏设置条件；4、保存查询后，再次运行查询就可以更新指定数据了(4)生成表查询：将查询结果以新表的形式保存举例：将所有“教职工”和“学生”权限的读者追加到新表Reader中(假设不存在一个与读者信息表相同结构的Reader表)操作步骤：1、打开“查询设计视图”3、在工具栏的“设计”选项卡的“查询类型”分组中点击“生成表查询”3、在“条件”栏设置条件；4、保存查询后，再次运行查询就可以更新指定数据了4.3SQL查询在Access中，创建和修改查询最便利的方法是使用“查询设计视图”。但是，在创建查询时并不是所有的查询都可以在系统提供的查询“设计”视图中进行，有的查询只能通过SQL语句来实现。例如：检索“计算机等级考试成绩单”中所有考试合格的学生由于一级、二级语种与三级、四级语种的合格判定条件不一样，所以很难利用查询设计器一步完成上述功能SQL的九个核心动词:

数据定义语言(DDL)：CREATE、DROP、ALTER

数据查询语言(DQL)：SELECT

数据操纵语言(DML)：INSERT、UPDATE、DELETE

数据控制语言(DCL)：GRANT、REVOKESQL简介结构化查询语言(StructureQueryLanguage)简称SQL，是目前关系型数据库的通用语言。它集数据定义(DDL)、数据操纵(DML)、数据管理(DCL)的功能于一体，可以独立完成数据库的全部操作。打开“查询设计器”后，点击【设计】选项卡【结果】分组中的【视图】按钮，选择【SQL视图】即可完成视图切换。SQL查询视图的切换SQL中的查询语句——SELECT【格式】SELECT[ALL|DISTINCT|topN]<选项>as显示列名

FROM<表名>【功能】无条件查询【说明】(1)ALL表示显示全部查询记录，包括重复记录

DISTINCT表示显示无重复结果的记录TOPN表示前N条记录

(2)选项:字段名,表达式或函数

显示列名:在输出结果中自行设置的一个名称1.基本查询【例1】显示所有读者信息【例2】检索有哪些读者借过书select*

from读者信息表selectdistinct

读者编号from借阅情况表SQL中的查询语句——SELECT【例3】显示前5条读者信息【例4】显示所有读者的“编号”和“内容”信息

“内容”显示为“姓名-权限”

selecttop5*from读者信息表select读者编号as编号,姓名&"-"&权限as内容from读者信息表SQL中的查询语句——SELECT【练习1】显示最贵的书籍价格【练习2】显示所有读者的姓、名selectmax(书籍价格)as最贵书籍价格from图书信息表selectleft(姓名,1)as姓,mid(姓名,2,7)as名from读者信息表SQL中的查询语句——SELECT【格式】

SELECT<选项>as<显示列名>

FROM<表名>WHERE<条件表达式>【功能】从一个表中查询满足条件的数据。【说明】条件表达式为筛选条件

2.条件查询SQL中的查询语句——SELECT【例1】显示所有姓“李”的读者信息【例2】显示所有1995年出生的读者信息select*from读者信息表where姓名like“李*"select*from读者信息表where出生日期between#1995-1-1#and#1995-12-31#select*from读者信息表whereyear(出生日期)=1995SQL中的查询语句——SELECT【练习1】显示所有男教职工的读者信息【练习2】显示所有未还的借阅信息select*from读者信息表where权限="教职工"and性别="男"图书未还：在借阅情况表中的某条记录，“实还日期”为空select*from借阅情况表或whereIsNull(实还日期)select*from借阅情况表where实还日期IsNullSQL中的查询语句——SELECT在一个查询语句中同时涉及到两个或两个以上的表时，这种查询称之为连接查询（也称为多表查询）。在多表之间查询必须处理表与表之间的连接关系。3.多表查询【格式】SELECT[ALL|DISTINCT]别名.选项AS列名

FROM<表1名as别名1>,<表2名as别名2>WHERE<条件表达式>【功能】多表连接进行多字段查询【说明】条件表达式为多表连接条件SQL中的查询语句——SELECT【例】显示图书的分类情况，要求显示书籍类别、书籍细类selecta.书籍类别,b.书籍细类from图书类别表asa,图书细类表asbwherea.类别代码=b.类别代码查询中用到的相关表结构及关系如图所示SQL中的查询语句——SELECT【练习】显示图书借还情况，要求显示读者姓名、书籍名称、借书日期、应还日期、实还日期selecta.姓名,b.书籍名称,c.借书日期,c.应还日期,c.实还日期from读者信息表asa,图书信息表asb,借阅情况表ascwherea.读者编号=c.读者编号andb.书籍编号=c.书籍编号查询中用到的相关表结构及表间关系如图所示SQL中的查询语句——SELECT在SQL语句中，一个SELECT－FROM－WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询或子查询。4.嵌套查询使用子查询可以定义字段或定义字段的条件，但子查询不能单独作为一个查询，它必须与其他查询相结合，通常是做为另外一个查询的字段、准则（或条件）来使用。如：查询年龄比“许丹”大的所有读者记录。SQL中的查询语句——SELECT【例】显示与“秦晨”相同权限的所有读者信息【练习】显示书籍价格高于平均价格的所有图书信息select*from图书信息表where书籍价格>(selectavg(书籍价格)from图书信息表)select*from读者信息表where权限=(select权限from读者信息表where姓名="秦晨")SQL中的查询语句——SELECT【格式】SELECT[ALL|DISTINCT]<字段列表>FROM<表>[系统别名][WHERE<条件表达式>][GROUPBY<分类字段列表>…][HAVING<分组过滤条件>][ORDERBY<排序项>[ASC|DESC][,……]查询结果按指定的字段分组。定义在GROUPBY

之后,表示指定每一分组所应满足的条件，只有满足条件的分组才在结果中显示。若未定义GROUPBY，则其作用等同于WHERE子句。指定查询结果按排序项输出，缺省为ASC，它必须放在整个语句之后。5.分组查询SQL中的查询语句——SELECT【例1】按年龄从小到大的顺序显示所有读者信息【例2】统计各权限的读者人数select*from读者信息表或orderby出生日期descselect权限,count(*)as人数from读者信息表groupby权限

select*from读者信息表orderbyyear(date())-year(出生日期)

SQL中的查询语句——SELECT【练习1】将读者信息表按身份证号中的生日(第7-14位)降序排序select*from读者信息表orderbymid(身份证号,7,8)descselect权限,count(*)as人数from读者信息表groupby权限

having权限="学生"【练习2】计算学生权限的读者人数SQL中的查询语句——SELECT(6)联合查询联合查询可以将两个或两个以上的表或查询所对应的多个字段的记录合并为一个查询表中的记录。【格式】<SELECT命令>UNION[ALL]<SELECT命令>【例】查询所有“教职工”和“学生”权限的读者select读者编号,姓名from读者信息表where权限="教职工"unionselect读者编号,姓名from读者信息表where权限="学生"【说明】联合查询不包含备注、OLE、超链接字段SQL中的查询语句——SELECT数据操纵功能是实现SQL利用数据操纵语言（DataManipulationLanguage）对数据库中的数据进行增删改操作的。数据定义查询的核心动词也有三个：INSERT、UPDATE、DELETE数据操纵查询（1）更新查询：更新查询是一种特殊的查询方式，其目的不是为了进行数据的检索，而是为了有效的进行成批数据的有规律替换。【格式】UPDATE表名SET字段名=表达式WHERE<筛选条件>【例】将所有图书的价格提高5元update图书信息表set书籍价格

=书籍价格+5【练习】将“人民邮电出版社”的图书价格提高5元update图书信息表set书籍价格

=书籍价格+5where出版社="人民邮电出版社"（2）追加查询：将查询到的数据以追加的方式添加到指定表中。进行追加查询操作的时候要注意指定表中的主键。【格式】INSERTINTO表名SELECT查询语句【例】将“读者信息表”中的所有“学生”读者追加到“学生读者表”insertinto学生读者表select*from读者信息表where权限="学生"（3）删除查询：将指定表中满足条件的记录做成批删除操作。该操作不能恢复，所以在操作前尽量对原数据进行备份。【格式】DELETE*FROM表名WHERE<筛选条件>【例】删除所有“教职工”权限的读者delete*from读者信息表where权限="教职工"数据定义查询是SQL的一种特定查询。使用数据定义语言（

DataDefinitionLanguage）可以进行数据库、数据表或索引的创建、删除、更改操作。使用数据定义查询修改数据库对象可能会有风险，因为完成操作时并不出现确认对话框，如果操作出错，可能造成数据丢失或意外更改表的设计。所以，最好对要运行此类查询的表制作备份。数据操纵查询的核心动词包括三个：CREATE、ALTER、DROP数据定义查询(1)创建表——CREATETABLE【格式】CREATETABLE表名(字段名

字段类型(字段大小)，……)常见字段类型引用方法文本text数值short/long/single/double日期/时间datetime是否yesno【例】利用SQL新建一张“成绩表”，表结构如下：createtable成绩表(姓名text(8),性别yesno,考试时间datetime,成绩single)字段名字段类型字段大小姓名文本型8性别逻辑型默认考试时间日期时间型默认成绩单精度型默认(2)修改表——ALTERTABLE【格式】ALTERTABLE表名ADD字段名数据类型(宽度)DROP字段名ALTER字段名数据类型(宽度)【说明】ADD：添加新字段DROP：删除旧字段ALTER：更新已有字段【例】删除“成绩表”的“性别”字段altertable成绩表drop性别(3)删除表——DROPTABLE【格式】droptable表名【例】利用SQL删除“成绩表”droptable成绩表(1)创建表——CREATETABLE【格式】CREATETABLE表名(字段名

字段类型(字段大小)，……)常见字段类型引用方法文本text数值short/long/single/double日期/时间datetime是否yesno【例】利用SQL新建一张“成绩表”，表结构如下：createtable成绩表(姓名text(8),性别yesno,考试时间datetime,成绩single)字段名字段类型字段大小姓名文本型8性别逻辑型默认考试时间日期时间型默认成绩单精度型默认(2)修改表——ALTERTABLE【格式】ALTERTABLE表名ADD字段名数据类型(宽度)DROP字段名ALTER字段名数据类型(宽度)【说明】ADD：添加新字段DROP：删除旧字段ALTER：更新已有字段【例】删除“成绩表”的“性别”字段altertable成绩表drop性别(3)删除表——DROPTABLE【格式】droptable表名【例】利用SQL删除“成绩表”droptable成绩表本章习题【1】在ACCESS数据库中使用向导创建查询，其数据可以来自________。A)一个表的一部分B)表或查询C)多个表D)一个表本章习题【2】在显示查询结果时，如果要将数据表中的"籍贯"字段名，显示为"出生地"，可在查询设计视图中改动________。A)条件B)显示C)排序D)字段本章习题【3】在创建交叉表查询时，列标题字段的值显示在交叉表的位置是________。A)上面若干行B)左面若干列C)第一行D)第一列本章习题【4】创建参数查询时，在查询设计视图准则中应将参数提示文本放置在________中。A)[]B)<>C){}D)()本章习题【5】下列不属于操作查询的是________。A)更新查询B)删除查询C)参数查询D)生成表查询本章习题【6】在建立查询时，若要筛选出图书编号是"T01"或"T02"的记录，可以在查询设计器条件行中输入________。A)IN("T01"AND"T02") B)NOTIN("T01"AND"T02")C)"T01"OR"T02"D)"T01"AND"T02"本章习题【7】表达式FIX（-3.25）和FIX（3.75）的结果分别是________。A)-3，4B)-4，4C)-3，3D)-4，3本章习题【8】RND()为随机函数，执行如下语句：S=INT(100*RND())执行完毕后，S的值是________。A)[1，99]的随机整数B)[1，100]的随机整数C)[0，99]的随机整数D)[0，100]的随机整数本章习题【9】假设有一组数据：工资为8