Access数据库技术与应用教程课件第4章 查询

第四章查询4.1查询的基本知识查询的原理通过某种规则，从数据库中的一张或多张工作表中建立这样的动态记录集查询的功能（1）选择字段（2）选择记录（3）排序记录（4）实现计算（5）建立表（6）建立基于查询的报表和窗体（7）建立基于查询的图表（8）建立辅助查询（9）实现数据的修改查询的分类（1）选择查询（2）交叉表查询（3）操作查询（4）SQL查询（5）参数查询4.2查询准则查询准则的含义查询准则是指在设计查询的过程中所定义的查询条件，它通常是运算符、常量、函数以及字段名称和属性等的组合4.2.1运算符1.算术运算符（7种，注意优先级）^、*、/、\、Mod、+、-2.连接运算符（2种，注意连接的是字符串）+、&4.2查询准则3.关系（比较）运算符（4种）Is、In、Between…And…、Like4.逻辑运算符（3种）Not、And、Or5.标识运算符（2种）！、.4.2.2函数Access中的函数是一个预先编制好的程序模块常用的标准函数（5类）数学函数、字符串函数、日期/时间函数、统计函数、其他函数4.2查询准则4.2.3查询准则建立需注意的问题不同类型数据的标识符号（1）日期型数据必须用符号“#”括起来；（2）文本型数据必须用半角的双引号括起来；（3）字段名称必须用一对方括号“[]”括起来。运算符的优先级优先级高的运算先进行，优先级相同的运算依照从左向右的顺序进行

详见表4-54.2查询准则4.2.4本书的查询数据表本章所有的例题都使用一个关于学生成绩的数据库该数据库由tStud、tCourse、tScore三张数据表组成（见表4-6、表4-7、表4-8）注意它们的内在联系（见图4-2）4.3选择查询选择查询是按照查询规则从一个或者多个表或其他查询中检索数据，并按照所需的排列顺序显示数据，主要用于浏览、检索、统计数据库的数据。

创建方式（4种）使用查询向导创建选择查询使用设计视图创建选择查询创建多表选择查询创建汇总查询4.3选择查询4.3.1使用查询向导创建【例4-6】用查询向导创建一个选择查询，查找所学生的“学号”、“姓名”和“所属院系”三个字段内容，所建查询命名为“qT1”。操作步骤：（1）导引（2）选择查询字段（3）指定查询标题4.3选择查询4.3.2使用设计视图创建选择查询【例4-7】用设计视图创建一个选择查询，查找年龄大于25的学生的“学号”、“姓名”和“所属院系”三个字段内容，所建查询命名为“qT2”。操作步骤：（1）添加数据表（2）添加查询字段（3）设置查询条件关键点：将“年龄”字段的“显示”选项去掉并在该字段的条件框中输入“>25”；（4）保存并显示查询4.3选择查询4.3.3创建多表选择查询【例4-8】用设计视图创建一个选择查询，查找女学生的“学号”、“课程名”和“成绩”三个字段内容，所建查询命名为“qT3”。操作步骤：（1）加入数据表（2）添加查询字段（3）设置查询条件关键点：将“性别”字段的“显示”选项去掉，并且在该字段的条件框中输入“女”（注意，要使用双引号将“女”字标引）。

（4）保存并显示查询4.3选择查询4.3.4创建汇总查询【例4-9】用设计视图创建一个查询，计算每名学生所选课程的学分总和，并依次显示“姓名”和“学分”，其中“学分”为计算出的学分总和，查询结果按照学分总和降序排列，所建查询名为“qT4”。操作步骤：（1）加入数据表（2）添加查询字段（3）计算关键点：在第2个“字段”中输入“学分:学分”，点击工具栏上的总计按钮，在出现“总计”选项框中，对“姓名”字段下的“总计”选项设置为“分组”，而对“学分:学分”字段下的“总计”选项设置为“总计”（4）设置排序选项（5）保存并显示查询4.4交叉表查询交叉表查询就是由用户建立起来的二维总计矩阵。使用交叉表查询可以计算并重新组织数据的结构，这样有利于数据分析和比较。

创建一个交叉表查询的三要素行标题列标题值值是用户选择在交叉表中显示的数据。是通过Sum、Avg、Max、Min和Count等总计函数或某种表达式计算得到的值。

4.4交叉表查询【例4-10】以表对象“tScore”和“tCourse”为基础，创建一个交叉表查询。要求：选择学生的“学号”为行标题、“课程号”为列标题来统计输出学分小于3分的学生平均成绩，所建查询命名为“qT5”。

操作步骤：加入数据表选择交叉表查询命令设定查询设置项关键点1：“行标题”选择“课程号”字段；“列标题”选择“成绩”字段；“值”在其“总计”设置项中选择“平均值”；关键点2：选择“学分”字段，并在其“条件”设置项中输入“<3”，在其“总计”设置项中选择“条件”；保存并显示查询4.5参数查询参数查询是指查询在执行时显示一个对话框以提示用户输人查询的信息，常用于用户对某个字段进行反复查询，而且在每次查询时都需要更改查询的具体内容的情形。分类（2类）单参数查询在字段中指定一个参数，在执行查询时，用户只需要输入一个参数值。多参数查询

在字段中指定多个参数，在执行多参数查询时，用户依次输入多个参数值。4.5参数查询4.5.1单参数查询【例4-11】以表“tStud”为数据源创建一个参数查询，当运行该查询时，提示框中应显示“请输入爱好”，输入爱好后，在简历字段中查找具有指定爱好的学生，显示“学号”、“姓名”、“性别”、“年龄”和“简历”字段内容，所建查询命名为“qT6”。操作步骤：（1）加入数据表（2）添加查询字段（3）设定参数查询项目关键点：“在“简历”字段的“条件”行中，输入“Like"*"+[请输入爱好]+"*"”

（4）保存并显示查询4.5参数查询4.5.2多参数查询【例4-12】以表“tStud”为数据源创建一个多参数查询，只查询年龄在一定范围的学生信息，显示“学号”、“姓名”、“性别”、“年龄”和“所属院系”字段内容，所建查询命名为“qT7”。

操作步骤：（1）加入数据表（2）添加查询字段（3）设定参数查询项目关键点在“年龄”字段的“条件”行中输入“Between[年龄上限为：]And[年龄下限为：]”

（4）保存并显示查询4.6操作查询操作数据库的过程中，有时需要将表中的一部分数据复制到一个行新表中，或需要对表中的数据进行更新、删除或是追加操作，这就需要使用到操作查询

分类（4类）生成表查询根据一个或者多个表中的全部或者部分数据来新建数据表追加查询将一个或多个表中的一组记录添加到一个已有表的末尾更新查询对大批量的数据进行修改删除查询删除一批数据4.6操作查询4.6.1生成表查询【例4-13】创建生成表查询，组成字段是没有书法爱好学生的“学号”、“姓名”和“入校年”三列内容（其中“入校年”数据由“入校时间”字段计算得到，显示为四位数字年的形式），生成的数据表命名为“tTemp”，所建查询命名为“qT8”。操作步骤：（1）加入数据表（2）添加查询字段（3）设定生成表查询项目关键点：在设计视图中“字段列表”区的第三个字段中输入：入校年:Year([入校时间])；第四个字段中输入：InStr([简历],"书法")，在这个字段的“条件”行中输入：“0”，并把该字段的“显示”行中的钩去掉（4）生成表（5）保存并运行查询4.6操作查询4.6.2追加查询【例4-14】在完成【例4-13】的基础上创建追加查询，将表对象“tStud”中有书法爱好学生的“学号”、“姓名”和“入校年”三列内容追加到目标表“tTemp”的对应字段内，所建查询命名为“qT9”

操作步骤：（1）加入数据表（2）追加表（3）添加追加字段关键点：从“tStud”选择“学号”和“姓名”字段，再在“字段”行的第三个空格处输入“Year([入校时间])”，“追加到”行对应的字段中选择“入校年”，再从“tStud”选择“简历”字段，在“条件”中输入“Like"*书法*"”

（4）保存并运行查询4.6操作查询4.6.3更新查询【例4-15】创建一个更新查询，将表“tStud”中“年龄”字段值加1，，所建查询命名为“qT10”。

操作步骤：（1）加入数据表（2）追加表（3）更新字段关键点：从tStud表中选择“年龄”字段，在“年龄”字段的“更新到”中输入“[年龄]+1”

（4）保存并运行查询4.6操作查询4.6.4删除查询【例4-16】创建一个删除查询，删除表“tStud”中性别为男的记录，所建查询命名为“qT11”。操作步骤：（1）加入数据表（2）删除查询（3）设置删除条件关键点：从tStud表中选择“性别”字段，在条件行中输入“"男"”

（4）保存并运行查询4.7SQL查询SQL——结构化查询语言（StructuredQueryLanguage）是集数据定义、数据查询、数据操纵和数据控制功能于一体的关系数据库语言。4.7SQL查询4.7.1SQL语言简介SQL语言是关系数据库语言的标准1.SQL语言的特点高度的综合非过程化语言结构简洁2.SQL语言的功能数据定义功能数据查询功能数据更新功能视图管理功能数据控制功能

3.进入SQL视图的方法（2种）4.7SQL查询4.7.2SQL的数据定义SQL的数据定义功能是指定义数据库的结构，包括定义基本表、定义视图和定义索引三个部分

1.定义基本表CREATETABLE命令格式：CREATETABLE<表名>(<列名1><数据类型>[列级完整性约束条件] [,<列名2><数据类型>[列级完整性约束条件]…] [,<表级完整性约束条件>])4.7SQL查询【例4-17】用CREATETABLE语句建立一个“学生”表，它由学生编号、姓名、性别、年龄和所在系五个字段组成，其中学生编号字段为主键、不能为空并且其值是唯一的，建立完成后以“qT12”存盘。

关键SQL语句：CREATETABLE学生(学生编号CHAR(5)PRIMARYKEYNOTNULLUNIQUE,姓名CHAR(8),性别CHAR(2),年龄INT,所在系CHAR(15))4.7SQL查询2.修改基本表ALTERTABLE命令格式：ALTERTABLE<表名> [ADD<新字段><数据类型>[完整性约束]] [DROP<字段>|<完整性约束>]【例4-18】在【例4-17】的基础上，在学生表中增加“入学时间”列，设置其数据类型为日期型，所建查询命名为“qT13”。关键SQL语句：ALTERTABLE学生ADD入学时间DATE4.7SQL查询【例4-19】在【例4-18】的基础上，将学生表中的年龄的数据类型改6个字符长度的字符串，所建查询命名为“qT14”。

关键SQL语句：ALTERTABLE学生ALTER年龄CHAR(6)【例4-20】在【例4-18】的基础上，删除学生表中“所在系”字段，所建查询命名为“qT15”。关键SQL语句：ALTERTABLE学生DROP所在系4.7SQL查询3.删除基本表DROPTABLE命令格式： DROPTABLE<表名>【例4-21】删除【例4-17】建立的学生表，所建查询命名为“qT16”。

关键SQL语句：DROPTABLE学生4.7SQL查询4.7.3SQL的数据操作利用SQL语句可以进行多种的数据操作，包括向表中插入数据、更新数据、删除数据等操作4.7SQL查询1.插入数据INSERTINTO命令格式：

INSERTINTO <表名>[(<列名1>[,<列名2>,…])] VALUES([<常量1>[,<常量2>,…])【例4-22】用INSERT语句向“tStud”表插入新的一个学生记录：“"000026","柳琴","女","20","04","1999-11-11","爱好：体育舞蹈"”，所建查询命名为“qT17”。关键SQL语句：INSERTINTOtStudVALUES("000026","柳琴","女","20","04","1999-11-11","爱好：体育舞蹈","")4.7SQL查询2.更新数据UPDATE命令格式：

UPDATE <表名>SET<列名>=<表达式>[,<列名>=<表达式>][,…] [WHERE<条件>]【例4-23】用UPDATE语句将“tStud”表中的“李四”同学的入校时间改为“1997-9-1”，所建查询命名为“qT18”。

关键SQL语句：UPDATEtStudSET入校时间=#1997-9-1#WHERE姓名="李四"4.7SQL查询3.删除数据DELETE命令格式：

DELETEFROM<表名>WHERE[<条件>]【例4-24】用DELETE语句将“tStud”表中的所有男同学的数据记录删除，所建查询命名为“qT19”。

关键SQL语句：DELETEFROMtStudWHERE性别="男"4.7SQL查询4.7.4SQL的数据查询1.SELECT语句格式SELECT[谓词]<字段列表>|<目标表达式>|<函数>[AS别名]FROM表名[WHERE条件…][GROUPBY字段名][HAVING分组的条件][ORDERBY字段名[ASC|DESC]];4.7SQL查询2.简单查询【例4-25】查询“tStud”数据表中男生的情况，按入校时间降序排序，所建查询命名为“qT20”。关键SQL语句SELECT*FROMtStudWHERE(tStud.性别="男")ORDERBYtStud.入校时间DESC4.7SQL查询2.简单查询【例4-26】查询“tStud”数据表中年龄最大的三位男生的情况，只显示“学号”、“姓名”、“性别”“年龄”、“所属院系”五个字段的信息，所建查询命名为“qT21”。

关键SQL语句SELECT*FROMtStudWHERE(tStud.性别="男")ORDERBYtStud.入校时间DESC4.7SQL查询3.联接查询【例4-27】创建一个查询，查找男同学的“学号”、“姓名”、“课程号”和“成绩”的内容，所建查询命名为“qT22”。

关键SQL语句SELECTtStud.学号,tStud.姓名,tScore.课程号,tScore.成绩FROMtStud,tScoreWHEREtStud.学号=tScore.学号ANDtStud.性别="男"4.7SQL查询【例4-28】创建一个查询，查找年龄大于25的学生的“姓名”、“课程名”和“成绩”三个字段内容，所建查询命名为“qT23”。关键SQL语句SELECTtStud.姓名,tCourse.课程名,tScore.成绩FROM(tCourseINNERJOINtScoreONtCourse.课程号=tScore.课程号)INNERJOINtStudONtScore.学号=tStud.学号WHEREtStud.年龄>254.7SQL查询4.使用函数的查询【例4-29】统计各院系的人数，所建查询命名为“qT24”。关键SQL语句SELECT所属院系,Count(所属院系)AS人数FROMtStudGROUPBY所属院系【例4-30】统计各门课程的平均分、最高分和最低分，所建查询命名为“qT25”。

关键SQL语句SELECTtScore.课程号,tCourse.课程名,tScore.成绩,Avg(tScore.成绩)AS成绩之平均值,Max(tScore.成绩)AS成绩之最大值,Min(tScore.成绩)AS成绩之最小值FROMtScore,tCourseWHEREtScore.课程号=tCourse.课程号4.7SQL查询5.联合查询基本格式为：SELECT*FROM表或查询UNIONSELECT*FROM表或查询

【例4-31】创建一个查询，查找年龄大于等于30岁或者年龄小于等于20岁的学生，显示“学号”、