第4章 数据查询

1第4章数据查询4.1查询简述4.2使用向导创建查询4.3自己设计查询4.4查询实例4.5利用查询实现对表数据的修改4.6SQL查询4.7对查询结果的处理24.1查询简述1.什么是查询查询是按照一定的条件或要求对数据库中特定数据信息的查找。查询可以对一个数据库中的一个表或多个表中存储的数据信息进行查找、统计、计算、排序等。查询的结果可以作为窗体、报表、数据访问页或另一个查询的数据源。34.1查询简述Access的查询有三种视图模式:设计视图数据表视图SQL视图44.1查询简述2.Access的查询类型

Access支持以下5种类型的查询:

选择查询参数查询交叉表查询操作查询

SQL查询。54.2使用向导建立查询Access提供了多种向导以方便查询的创建。对于初学者来说，选择使用向导的帮助可以快捷地建立所需要的查询。64.2使用向导建立查询1.使用简单查询向导例:在“教学管理”数据库中新建教师基本情况的查询双击选择表或查询选择单一字段选择所有字段74.2使用向导建立查询2．使用交叉表查询向导

交叉表查询以水平方式和垂直方式对记录进行分组，并计算和重构数据，可以简化数据分析。交叉表查询计算数据总和、计数、平均值以及其他类型的综合计算。通过交叉表查询可以在一个数据表中以行标题将数据组成群组，按列标题来分别求得所需汇总的数据（如总和或平均值），然后在数据表中以表格的形式显示出来。84.2使用向导建立查询例：在“教学管理”数据库中迅速建立这样一个查询：“学生中各民族的男女生的人数”．94.2使用向导建立查询3．使用查找重复项查询向导查找重复项查询向导可以对数据表中某些具有相同的字段值的记录进行检索、分类．例：查找“教师中各种职称的人数”．104.2使用向导建立查询4．查找不匹配项查询向导查找不匹配项查询向导可以在一个表中查找与另一个表中没有相关记录的记录。例：完成“没有选课的学生”的查询．114.3自己设计查询1查询的设计视图“字段”查询中所使用的字段的名称“表”该字段所来自的数据对象（表或查询）“排序”确定是否按该字段排序以及按何种方式排序“显示”确定该字段是否在查询结果集中可见“条件”用来指定该字段的查询条件“或”用来提供多个查询条件124.3自己设计查询２．查询目标的确定在查询中显示的字段值可以是原表的字段值，也可以是经过字段表达式计算的结果．年薪：工资*12课程安排:"第"&开课学期&"学期"134.3自己设计查询3.查询条件表达式的设置

设计查询时，如果需要查找满足某一条件的记录，需要在查询设计视图中的“条件”行输入查询的条件表达式。除了直接输入常量外，还可以使用比较运算符、逻辑运算符、特殊运算符、数学运算符和Access的内部函数等来构成表达式。在查询设计区的“条件”行中输入表达式时，如果各个表达式处于同一行，则各个表达式之间应该是逻辑与的关系；如果各个表达式处于不同行，则各个表达式之间应该是逻辑或的关系。144.3自己设计查询比较运算符含义>大于>=大于等于<小于<=小于等于=等于<>不等于逻辑运算符含义Not逻辑非And逻辑与Or逻辑或例如，下列表达式：（1）考试成绩在85到70之间表达式在“条件”行对应的“成绩”字段名的单元格中输入：<=85And>=70（2）籍贯是“北京”或“南京”的表达式在“条件”行对应的“籍贯”字段名的单元格中输入："北京"Or"天津“（3）民族是少数民族的表达式在“条件”行对应的“民族”字段名下单元格中输入：Not“汉”154.3自己设计查询查找少数民族的女学生信息查询和查找少数民族学生或是女学生信息查询．164.3自己设计查询其他运算符含义Betwee…And指定值的范围在…到…之间In指定值属于列表中所列出的值Is与Null一起使用确定字段值是否为空值Like用通配符查找文本型字段值是否与其匹配通配符是？匹配任意单个字符；*匹配任意多个字符；#匹配任意单个数字；！不匹配指定的字符；[字符列表]匹配任何在列表中的单个字符。例如，下列表达式：（1）出生日期在1985年1月1日到1989年12月31日的表达式.Between#1985/1/1#And#1989/12/31#在条件表达式中输入日期值时,需要在日期值两边加上”#”符号。（2）职称为教授或副教授的表达式In(“教授”，“副教授”)在条件表达式中输入字符串时,需要在字符串两边用双引号引起来。（3）没有参加考试（即成绩为空值）的件表达式

IsNull（4）姓名是姓“张”的表达式Like“张*”（5）学号第一位是0，第二位是0、1（即00级、01级学生）的表达式

Like“0[01]*”17函数

1.常用数值函数函数功能示例结果Abs(数值表达式)返回数值表达式值的绝对值Abs(-30)30Int(数值表达式)返回数值表达式值的整数部分值，如果数值表达式的值是负数，返回小于或等于数值表达式值的第一负整数int(5.5)int(-5.5)5-6Fix(数值表达式)返回数值表达式值的整数部分值，如果数值表达式的值是负数，返回大于或等于数值表达式值的第一负整数Fix(5.5)Fix(-5.5)5-5Sqr(数值表达式)返回数值表达式值的平方根值Sqr(9)3Sgn(数值表达式)返回数值表达式值的符号对应值，数值表达式的值大于0，等于0，小于0，返回值分别为1，0，-1Sgn(5.3)Sgn(0)Sgn(-6.5)10-1Round(数值表达式1,数值表达式2)对数值表达式1的值按数值表达式2指定的位数四舍五入Round(35.57,1)Round(35.52,0)35.636182.常用字符函数函数功能示例结果Space(数值表达式)返回数值表达式值指定的空格个数组成的空字符串"教学"&Space(2)&"管理"教学管理String(数值表达式,字符表达式)返回一个由字符表达值的第一个字符重复组成的由数值表达式值指定长度的字符串string(4,"abcdabcdabcd")aaaaLen(字符表达式)返回字符表达式的字符个数Len("教学"&"管理")4Left(字符表达式,数值表达式)按数值表达式值取字符表达式值的左边子字符串left("数据库管理系统",3)数据库Right(字符表达式,数值表达式)按数值表达式值取字符表达式值的右边子字符串right("数据库管理系统",2)系统Mid(字符表达式,数值表达式1[,数值表达式2])从字符表达式值中返回以数值表达式1规定起点，以数值表达式2指定长度的字符串Mid(“abcd”&”efg”,3,3)cdeLtrim(字符表达式)返回去掉字符表达式前导空格的字符串"教学"&(ltrim("管理"))教学管理Rtrim(字符表达式)返回去掉字符表达式尾部空格的字符串Rtrim("教学")&"管理"教学管理Trim(字符表达式)返回去掉字符表达式前导和尾部空格的字符串trim("教学")&"管理"教学管理193.常用日期函数函数功能示例结果Date()返回当前系统日期Month(日期表达式)返回日期表达式对应的月份值month(#2010-03-02#)3Year(日期表达式)返回日期表达式对应的年份值Year(#2010-03-02#)2010Day(日期表达式)返回日期表达式对应的日期值day(#2010-03-02#)2Weekday(日期表达式)返回日期表达式对应的星期值Weekday(#2010-04-02#)6204.常用统计函数函数功能示例结果Sum(字符表达式)返回表达式所对应的数字型字段的列值的总和Sum(成绩)计算成绩字段列的总和Avg(字符表达式)返回表达式所对应的数字型字段的列中所有值的平均值。Null值将被忽略Avg(成绩)计算成绩字段列的平均值Count(字符表达式)Count(*)返回含字段的表达式列中值的数目或者表或组中所有行的数目（如果指定为COUNT(*)）。该字段中的值为Null（空值）时，COUNT(数值表达式)将不把空值计算在内，但是COUNT(*)在计数时包括空值Count(成绩)统计有成绩的学生人数Max(字符表达式)返回含字段表达式列中的最大值(对于文本数据类型,按字母排序的最后一个值)。忽略空值Max(成绩)返回成绩字段列的最大值Min(字符表达式)返回含字段表达式列中最小的值(对于文本数据类型,按字母排序的第一个值)。忽略空值Min(成绩)返回成绩字段列的最小值215.常用域聚合函数函数功能示例结果DSum(字符表达式1,字符表达式2[,字符表达式3])返回指定记录集的一组值的总和DSum("成绩","选课",[学号]="10150226")求“选课”表中学号为“10150226”的学生选修课程的总分DAvg(字符表达式1,字符表达式2[,字符表达式3])返回指定记录集的一组值的平均值DAvg("成绩","选课",[课程号]="TC01")求“选课”表中课程号为“TC01”的课程的平均分DCount(字符表达式1,字符表达式2[,字符表达式3])返回指定记录集的记录数DCount("学号","学生",[性别]="男")统计“学生”表中男同学人数DMax(字符表达式1,字符表达式2[,字符表达式3])返回一列数据的最大值DMax("成绩","选课",[课程号]="TC01")求“选课”表中课程号为“TC01”的课程的最高分DMin(字符表达式1,字符表达式2[,字符表达式3])返回一列数据的最小值DMi("成绩","选课",[课程号]="TC01")求“选课”表中课程号为“TC01”的课程的平低分DLookup(字符表达式1,字符表达式2[,字符表达式3])查找指定记录集中特定字段的值DLookup("姓名","教师",[教师编号]="13001")查找“教师”表中教师编号为“13001”的教师的姓名224.3自己设计查询例：查找参加工作在1980年到2000年之前的学历为硕士或博士，工资在3000元以下少数民族的教师的信息234.3自己设计查询4.联接类型对查询结果的影响244.3自己设计查询

例：课程的选修情况的查询254.4查询实例1．选择查询查询“计算机系学生的选课成绩为及格的”的记录，要求查询结果中包含学生号、学生名、课程号、课程名、成绩。每门课程的成绩按从高到底排列

264.4查询实例2．参数查询

例：根据输入的“系名”的内容，查找该系教师的情况．274.4查询实例3．汇总查询例：查询每个学生学习课程的成绩的总分、平均分、最高分和最低分。

284.4查询实例GroupBy默认值，用于定义要执行计算的组。这个字段中的记录将按值进行分组。Sum计算每一分组中字段值的总和。适用于数字、日期/时间、货币和自动编号型字段。Avg计算每一分组中字段的平均值。适用于数字、日期/时间、货币和自动编号型字段。Min计算每一分组中字段的最小值。适用于文本、数字、日期/时间、货币和自动编号型字段。对于文本型字段，将按照字符的ASCII码顺序进行比较。Max计算每一分组中字段的最大值。适用范围与Min相同Count计算每一分组中字段值的计数，该字段中的值为Null（空值）时，将不计算在内。Where与“条件”行内容配合可以在分组前先筛选记录，并且查询结果中的这个字段将不能被显示出来。StDev计算每一分组中的字段值的标准偏差值。只适用于数字、日期/时间、货币和自动编号型字段。Var计算每一分组中的字段值的方差值。只适用于数字、日期/时间、货币和自动编号型字段。First返回每一分组中该字段的第一个值。Last返回每一分组中该字段的最后一个值。Expression在字段中自定义计算公式，可以套用多个总计函数。294.4查询实例4．交叉表查询

例：利用交叉表查询来完成每个学生的第一个学期的选课信息的查询，查询包括学生姓名、所学课程名、成绩和平均分等信息．

304.5利用查询实现对表数据的更改查询不仅可以实现对数据的查找，还能很有效地对表中的记录作删除、更新、追加的操作，并且通过查询可以生成新表。314.5利用查询实现对表数据的更改1．生成表查询

生成表查询是将对一个或多个表的查询得到的全部或部分数据来创建新表，这样可以对一些特定的数据进行备份。例：将计算机系的学生记录生成一个新表．324.5利用查询实现对表数据的更改2．更新查询

更新查询可以对一个或多个表中符合查询条件的数据作批量的更改。例如：将教师中职称为讲师的工资提高5%．334.5利用查询实现对表数据的更改3．追加查询

追加查询可以是向一个表的尾部添加记录。追加查询可以将从另一个数据表中读取数据记录添加到当前数据库的表中。例如：将电子工程系的学生的记录合并到计算机系学生表中。344.5利用查询实现对表数据的更改4．删除查询利用删除查询可以从一个或多个表中删除符合查询条件的一组记录。例如：删除“计算机系学生表”中年龄大于等于21岁的记录。354.6SQL查询

SQL查询是使用SQL语句创建的一种查询。SQL(Structured

Query

Language)结构化查询语言是标准的关系型数据库语言，使用SQL语言可以对数据库实施数据定义、数据操作和数据控制及管理。364.6SQL查询.1．SQL视图374.6SQL查询２．SQL语句功能（1）数据定义功能定义、删除、修改关系模式（基本表）定义、删除视图（视图）定义、删除索引（索引）（2）数据操纵功能数据查询数据插入、删除、修改（3）数据控制功能用户访问权限的授予、收回384.6SQL查询２．SQL语句功能SQL语句功能操作符数据定义(表、索引)CREATE，ALTER，DROP数据查询SELECT数据更新INSERT，UPDATE，DELETE394.6SQL查询3．SQL的数据定义功能（１）定义基本表语句格式为：createtable表名(列名数据类型[default缺省值][notnull][，列名数据类型[default缺省值][notnull]]…… [，primarykey（列名[，列名]…）] [，foreignkey（列名[，列名]…）

references表名（列名[，列名]…）]

[，check（条件）])；例：定义“学生”表,它包括学号、姓名、出生日期、性别等列（字段），学号为主键，对性别有值的约束条件。CREATETABLE学生

(学号CHAR（4）,

姓名CHAR（8）NOTNULL,

出生日期DATE,

性别CHAR（2）, PRIMARYKEY(学号), CHECK(性别=‘男’OR性别=‘女’)）；404.6SQL查询（２）.修改基本表定义语句格式：ALTERTABLE表名

[ADD子句]增加列或完整性约束条件

[DROP子句] 删除完整性约束条件

[MODIFY子句]修改列定义例：在“学生”表中增加一个“系号”列

ALTERTABLE学生

ADD(系号CHAR(6));例：将“学生“表中的”姓名”列增加到12个字符的宽度

ALTERTABLE学生

MODIFY(姓名CHAR(12));414.6SQL查询（3）删除基本表语句格式：

DROPTABLE表名；注意:删除基本表后，基本表的定义、表中数据、索引都被删除。例：删除“学生”表

DROPTABLE学生；424.6SQL查询（4）建立索引语句格式:CREATE[UNIQUE/DISTINCT]INDEX索引名ON表名(列名[ASC/DESC][,列名ASC/DESC]]…)说明：UNIQUE（DISTINCT）：惟一性索引，不允许表中不同的行在索引列上取相同值。若已有相同值存在，则系统给出相关信息，不建此索引。系统并拒绝违背惟一性的插入、更新。ASC/DESC选项指定索引排序升序或降序，不指定顺序，索引按升序排列。例：在“学生”表的“学号”列上建立名为“学号INX”的惟一索引。CREATEUNIQUEINDEX学号INXON学生（学号）；434.6SQL查询（5）删除索引语句格式：

DROPINDEX索引名例：删除建立在学号的索引

DROPINDEX学号INX444.6SQL查询4．SQL的数据查询功能数据库查询是数据库的核心操作，SQL语言提供了SELECT语句进行数据查询。该语句的功能强，变化形式较多。

SELECT查询语句格式如下：SELECT[DISTINCT]<列名>[,<列名>,……]（查询的结果的目标列名表）FROM<表名>[,<表名>,……]

（要操作的关系表或查询名）[WHERE<条件表达式>]

（查询结果应满足选择或联接条件）[GROUPBY<列名>[,<列名>…][HAVING<条件>]（对查询结果分组及分组的条件）[ORDERBY<列名>[ASC|DESC]

（对查询结果排序）454.6SQL查询（１）简单查询例：查找成绩在70到80分之间的学生选课情况SELECT*FROM选课WHERE成绩BETWEEN70AND80;464.6SQL查询例：查找出所有姓李的学生的情况

SELECT*

FROMS

WHERESNAMELIKE‘李%’;注意：在SQL语言中字符“%”代表任意一串字符，“_”

代表任意一个字符。在Access2003中用“*”代表任意一串字符，“？”代表任意一个字符。474.6SQL查询（2）连接查询一个查询同时涉及两个以上的表时，称其为连接查询。

例：查询数学系、计算机系、电子工程系的学生的姓名、系名称SELECT姓名，系名称FROM学生，系科WHERE学生.系号=系科.系号And系名称IN（数学系，计算机系，电子工程系）；注意：用“学生.系号=系科.系号”指明两个表的联接条件；“系名称IN（数学系，计算机系，电子工程）”为选择条件。

484.6SQL查询例9检索计算机系的学生的学号,姓名,年龄

SELECT学号,姓名,（Date()-出生日期）/365As年龄

FROM学生，系科

WHERE学生.系号=系科.系号And系名称=’计算机系’;注意：”（Date()-出生日期）/365”

为年龄的字段表达式。494.6SQL查询（3）嵌套查询一个查询语句的WHERE子句中包含一个由比较符号或谓词引导查询语句时，称查询为嵌套查询，被嵌入的查询称为子查询。可以引导子查询的谓词有：IN、ANY、ALL、EXISTS。例：找出年龄小于李芳的学生姓名SELECT姓名FROM学生WHERE出生日期>(SELECT出生日期

FROM学生

WHERE姓名=‘李芳’);注意：使用比较符引导的子查询，查询的结果必须为惟一值。

504.6SQL查询例：求选修了C程序设计课程的所有学生的学号．SELECT学号FROM选课WHERE课程号IN(SELECT课程号

FROM课程

WHERE课程名称=’C程序设计’);注意：本例为嵌套查询-----带有子查询的SELEC语句。514.6SQL查询例：找出学习TC02课程的学生姓名.SELECT姓名

FROM学生

WHEREEXISTS(SELECT*FROM选课

WHERE学号=学生.学号AND课程号=‘TC02’)；注意：本例使用EXISTS（量词）的嵌套查询524.6SQL查询（4）使用聚集函数的查询在查询中使用聚集函数，可以对查询的结果进行统计计算。常用五个的聚集函数：平均值： Avg总和： Sum最小值：Min最大值： Max计数： Count534.6SQL查询例：求学号为011534的学生的总分和平均分SELECTSum(成绩)AS总分,Avg(成绩)AS平均分FROM选课WHERE学号=‘011534’；注意：本例使用了统计函数Sum（总和）和Avg（平均）例：求至少选修三门以上课程的学生的学号及选课门数SELECT学号,COUNT(*)as选课门数FROM选课GROUPBY学号HAVINGCOUNT(*)>3;

注意：分组带有附加条件。每个组的记录条数大于3。544.6SQL查询（5）集合查询集合查询是将多个Select语句的结果进行集合操作构成一个查询．例：查询女学生及选课成绩大于80的学生的学号

Select学号

From学生

Where性别='女'

Union

Select学号

From选课

Where成绩>80554.6SQL查询5．SQL的数据更新功能

SQL中数据更新包括插入数据、修改数据和删除数据三条语句（1）插入数据语句格式1:INSERTINTO<表名>[(<列名>[,<列名>……])]VALUES(常量[，常量…….]);

说明：该语句一次完成一个记录的插入。语句格式2：

INSERTINTO<表名>[(<列名>[,<列名>……])]

子查询;说明：子查询嵌入INSERT语句，查询的结果插入到表中。即一次完成批量记录数据的插入。564.6SQL查询例：向学生表插入数据

INSERTINTO学生(学号,姓名,系号)VALUES(‘031510’,‘李新’,‘15’)；例：将系号为15的全体学生选修C程序设计课程的信息添入选课表.INSERTINTO选课(学号,课程号,成绩)SELECT学号,课程号,NullFROM学生,课程WHERE系号='15'And课程名称='C程序设计';574.6SQL查询（2）删除数据语句格式:

DELETEFROM<表名>[WHERE<条件表达式>]说明:无WHERE子句时,表示删除表中的全部数据.WHERE子句中可以带子查询.例：删除学号为001155的学生记录

DELETEFROM学生WHERE学号=‘001155’;例：删除系号为1的所有学生的选课记录

DELETEFROM选课WHERE'1'=(SELECT系号

FROM学生

WHERE学生.学号=选课.学号);584.6SQL查询（3）修改数据语句格式:

UPDATE<表名>SET<列名>=<表达式>|<子查询>[,<列名>=<表达式>|<子查询>……..][WHERE<条件表达式>];说明:修改时,对满足条件表达式的行,将用表达式的值或子查询的结果(唯一值)替换相应列的值。例：将选课表中的所有选修TC04课程的学生成绩提高5分

UPDATE选课

SET成绩=成绩+5WHERE课程号=‘TC04’;例：将系号为1的全体学生的TC02课程成绩置0.

UPDATE选课

SET成绩=0WHERE课程号='TC02'And学号IN(SELECT学号FROM学生

WHERE系号='1'And学生.学号=选课.学号);594.6SQL查询6．SQL特定查询在Access中将通过SQL语句才能实现查询称为SQL特定查询。SQL特定查询可以分为四类：联合查询、传递查询、数据定义查询和