数据应用及基础 4

项目3创建和应用查询《数据库应用基础（Access2019）》项目目标掌握使用向导和设计器创建选择查询的方法掌握创建交叉表查询的方法掌握创建参数查询的方法掌握通过操作查询添加记录的方法掌握通过操作查询更新记录的方法掌握通过操作查询删除记录的方法本章目录01任务3.1创建选择查询02任务3.2通过执行条件筛选记录03任务3.3创建多表查询04任务3.4在查询中进行计算05任务3.5创建参数查询06任务3.6创建交叉表查询07任务3.7创建子查询08任务3.8通过追加查询添加记录09任务3.9通过更新查询修改记录10任务3.10通过删除查询删除记录创建选择查询3.1任务3.1创建选择查询–1/5选择查询概述什么是选择查询最常见的查询类型，用于从一个或多个表中检索符合条件的数据。可以以“数据表视图”显示结果，并可作为窗体、报表的数据源。以SQLSELECT语句的形式存储在数据库中。创建选择查询的两种方式查询向导：适合初学者，步骤清晰，快速生成查询。查询设计器：功能更强大，可灵活设置条件、排序和字段。本任务目标掌握使用向导和设计器创建选择查询的方法。理解SQLSELECT语句的基本结构。能够设置查询条件、排序和返回指定数量的记录。任务3.1创建选择查询–2/5使用向导创建查询操作步骤打开“教务管理数据库”。在“创建”选项卡中单击“查询向导”，选择“简单查询向导”。选择“学生”表，添加所需字段（如：班级编号、学号、姓名、性别、出生日期）。设置查询标题（如：“学生基本信息”），选择“修改查询设计”完成创建。查看查询结果数据表视图：查看查询返回的记录。设计视图：查看字段布局与条件设置。SQL视图：查看生成的SQL语句。示例SELECT学生.班级编号,学生.学号,学生.姓名,学生.性别,学生.出生日期FROM学生;任务3.1创建选择查询–3/5设置查询条件与筛选记录任务需求从学生表中检索“入学成绩在450分以上的女同学”。操作步骤单击“查询设计”，添加“学生”表。添加字段：学号、姓名、性别、入学成绩。在“条件”行中设置：性别：="女"入学成绩：>450保存查询为“入学成绩高于450的女同学”。生成的SQL语句SELECT学生.学号,学生.姓名,学生.性别,学生.入学成绩FROM学生WHERE(((学生.性别)="女")AND((学生.入学成绩)>450));关键知识点WHERE子句：用于设置筛选条件。AND/OR/NOT：用于组合多个条件。比较运算符：=,>,<,>=,<=,<>任务3.1创建选择查询–4/5排序与TOP查询按入学成绩降序排列在设计视图中，选择“入学成绩”字段的“排序”为“降序”。保存查询为“按入学成绩降序排列学生记录”。生成以下SQL语句：SELECT学生.学号,学生.姓名,学生.性别,学生.入学成绩FROM学生ORDERBY学生.入学成绩DESC;只显示前5名学生记录使用已有查询作为数据源。在“返回”框中输入“5”。生成以下SQL语句：SELECTTOP5按入学成绩降序排列学生记录.*FROM按入学成绩降序排列学生记录;关键知识点ORDERBY子句：用于排序，默认升序（ASC），可指定降序（DESC）。TOP谓词：返回前N条或前N%的记录，常与ORDERBY配合使用。任务3.1创建选择查询–5/5生成表查询与SELECT语句完整结构生成表查询将查询结果保存为一个新表。示例：检索共青团员学生，生成“共青团员学生”表。SQL语句示例：SELECT学生.学号,学生.姓名,学生.班级编号,学生.性别,学生.是否团员INTO共青团员学生FROM学生WHERE(((学生.是否团员)=True));SELECT语句完整结构（简化版）SELECT[TOPn]字段列表[INTO新表]FROM表名[WHERE条件][ORDERBY字段[ASC|DESC]]通过搜索条件筛选记录3.2任务3.2通过搜索条件筛选记录

–1/4使用比较运算符与AND组合条件需求：查询入学成绩在400到465之间的学生记录，并按入学成绩降序排列。操作要点添加“学生”表，选择班级编号、学号、姓名、性别、入学成绩。在“入学成绩”字段的“条件”单元格中输入：>=400AND<=465在“排序”中选择“降序”。两种写法对比>=400AND<=465vsBETWEEN400AND465生成的SQL语句SELECT学生.学号,学生.班级编号,学生.姓名,学生.性别,学生.入学成绩FROM学生WHERE(((学生.入学成绩)>=400AND(学生.入学成绩)<=465))ORDERBY学生.入学成绩DESC;关键知识点比较运算符：>,<,>=,<=,=,<>范围测试：BETWEEN...AND...用于判断值是否在闭区间内任务3.2通过搜索条件筛选记录–2/4使用AND与OR组合多个条件示例1：查询团员女同学（AND条件）条件：性别为“女”且是团员在两个字段的“条件”单元格中分别输入：“女”和“True”。生成的SQL语句：

WHERE(((学生.性别)="女")AND((学生.是否团员)=True))示例2：查询正高、副高或研究生学历教师（OR条件）条件：学历为“研究生”或职称为“正高”或“副高”在学历字段的“条件”中输入：“研究生”在职称字段的“或”单元格中输入：IN("正高","副高")。生成的SQL语句：

WHERE(((教师.学历)="研究生"))OR(((教师.职称)IN("正高","副高")))关键知识点AND：所有条件同时满足OR：满足任一条件即可NOT：否定条件同一行“条件”单元格之间为AND关系，“或”行与“条件”行为OR关系任务3.2通过搜索条件筛选记录–3/4使用IN与LIKE运算符IN运算符：测试值是否在列表中语法：字段名IN(值1,值2,...)示例：查询职称为“正高”或“副高”的教师职称IN("正高","副高")取反：NOTINLIKE运算符：模式匹配与通配符语法：字段名LIKE"模式"常用通配符通配符含义示例*任意多个字符"张*"

匹配姓张?单个字符"李?"

匹配姓李且名字一个字[]括号内任一字符"[张王李赵]*"

匹配张王李赵姓[!]不在括号内的字符"[!刘]*"

匹配不姓刘#单个数字"2025#"

匹配20250-20259任务3.2通过搜索条件筛选记录–4/4搜索条件综合应用日期条件书写规范使用#作为日期定界符，如>#2006-06-01#Access会根据系统区域设置自动调整显示格式空值判断IsNull：字段值为空IsNotNull：字段值非空WHERE子句常用元素比较运算符：=,>,<,>=,<=,<>范围测试：BETWEEN...AND...列表测试：IN(...)/NOTIN(...)模式匹配：LIKE+通配符条件组合：AND,OR,NOT空值判断：ISNULL/ISNOTNULL创建多表查询3.3任务3.3创建多表查询–1/3为什么需要多表查询为避免数据冗余，相关数据分散存储在不同表中多表查询可将分散的数据组合起来，形成完整信息视图内部连接（INNERJOIN）：只要两个表的公共字段有匹配值，就组合这两个表中的记录语法格式

FROM表1INNERJOIN表2ON表1.字段=表2.字段示例：查询数字媒体专业学生信息需求：从班级表和学生表中获取专业名称、学号、姓名、性别数据来源：班级表（专业名称）、学生表（学号、姓名、性别）连接条件：班级编号（两个表的公共字段）筛选条件：专业名称=“数字媒体”生成的SQL语句SELECT班级.专业名称,学生.学号,学生.姓名,学生.性别FROM班级INNERJOIN学生ON班级.班级编号=学生.班级编号WHERE(((班级.专业名称)="数字媒体"));关键要点若表间已建立关系，Access会自动添加INNERJOIN连接字段数据类型必须相同或兼容任务3.3创建多表查询–2/3三表连接查询：查询学生课程成绩需求：获取学号、姓名、课程名称、成绩数据来源分布

学生表：学号、姓名;课程表：课程名称;成绩表：成绩、学号、课程编号连接关系成绩表↔课程表：通过“课程编号”连接;成绩表↔学生表：通过“学号”连接设计视图操作添加学生表、课程表、成绩表选择字段：学号、姓名、课程名称、成绩按学号升序排序生成的SQL语句（嵌套连接）SELECT学生.学号,学生.姓名,课程.课程名称,成绩.成绩FROM学生INNERJOIN(课程INNERJOIN成绩ON课程.课程编号=成绩.课程编号)ON学生.学号=成绩.学号ORDERBY学生.学号;执行顺序先连接课程表和成绩表（按课程编号）再将结果与学生表连接（按学号）任务3.3创建多表查询–3/3内部连接（INNERJOIN）深入理解INNERJOIN完整语法

FROM表1INNERJOIN表2ON表1.字段1=表2.字段2可使用关系比较运算符（=,<,>,<=,>=,<>）可使用多个ON子句组合条件（AND/OR）嵌套连接语法

FROM表1INNERJOIN(表2INNERJOIN表3ON连接条件)ON连接条件连接字段的要求数据类型必须相同或兼容，不能连接备注型、OLE对象型字段自动编号与长整型可互连，但单精度与双精度不可直接连接扩展知识内部连接：只返回两个表中有匹配值的记录外部连接（LEFTJOIN/RIGHTJOIN）：返回一侧表的所有记录，另一侧无匹配时显示空值在查询中进行计算3.4任务3.4在查询中计算–1/5在查询中进行计算概述两种计算类型预定义计算：针对一组记录进行统计计算求和（Sum）、平均值（Avg）、计数（Count）、最大值（Max）、最小值（Min）需要在设计网格中添加“总计”行自定义计算：对单条记录中的字段进行运算数值运算、日期运算、文本拼接等通过创建“计算字段”实现，无需添加“总计”行核心概念分组：将相同字段值的记录合并为一组，再对每组进行统计GROUPBY子句：指定分组依据HAVING子句：对分组后的结果进行筛选（类似WHERE，但作用于组）本任务目标掌握在查询中进行求和、平均、计数等统计计算学会使用GROUPBY对记录分组学会创建计算字段进行自定义计算任务3.4在查询中计算–2/5预定义计算——总成绩与平均成绩示例1：计算每个学生的总成绩数据来源：学生表+成绩表；分组依据：学号、姓名；统计函数：Sum（合计）；排序：按总成绩降序操作步骤添加学生表和成绩表，选择学号、姓名、成绩字段；单击“汇总”命令，显示“总计”行；将成绩字段的“总计”设为“合计”；设置别名：“总成绩:成绩”；在“排序”中选择“降序”。生成以下SQL语句SELECT学生.学号,学生.姓名,Sum(成绩.成绩)AS总成绩FROM学生INNERJOIN成绩ON学生.学号=成绩.学号GROUPBY学生.学号,学生.姓名ORDERBYSum(成绩.成绩)DESC;示例2：计算每个学生的平均成绩将“合计”改为“平均值”，别名设为“平均成绩:成绩”可通过“属性表”设置小数位数和格式任务3.4在查询中计算–3/5分组统计与HAVING子句示例：计算数学课的最高分、最低分、平均分数据来源：课程表+成绩表；分组依据：课程名称统计函数：Max、Min、Avg；筛选条件：只显示“数学”课程操作要点添加课程表和成绩表；添加课程名称字段和三个成绩字段将三个成绩字段的“总计”分别设为“最大值”、“最小值”、“平均值”在课程名称的“条件”单元格中输入“数学”为计算字段设置别名：最高分、最低分、平均分。生成以下SQL语句SELECT课程.课程名称,Max(成绩.成绩)AS最高分,Min(成绩.成绩)AS最低分,Avg(成绩.成绩)AS平均分FROM课程INNERJOIN成绩ON课程.课程编号=成绩.课程编号GROUPBY课程.课程名称HAVING(((课程.课程名称)="数学"));WHEREvsHAVINGWHERE：分组前筛选记录HAVING：分组后筛选组任务3.4在查询中计算–4/5计数与SQL统计函数示例：统计每班学生人数数据来源：学生表+班级表；分组依据：系部编号、专业名称、班级编号统计函数：Count（计数）；计算字段：人数:学号操作要点添加班级表和学生表；添加系部编号、专业名称、班级编号、学号字段将学号的“总计”设为“计数”；设置别名：“人数:学号”常用SQL统计函数函数功能适用场景Sum求和总成绩、总金额Avg平均值平均成绩、平均价格Count计数学生人数、订单数量Max最大值最高分、最高销售额Min最小值最低分、最低价格任务3.4在查询中计算–5/5自定义计算——计算年龄自定义计算的特点无需添加“总计”行；对每条记录独立计算；使用表达式构建计算字段示例：根据出生日期计算学生年龄数据来源：学生表；计算字段：年龄:DateDiff("yyyy",[出生日期],Now())DateDiff：计算两个日期之间的时间间隔；"yyyy"：间隔单位为年；[出生日期]：引用字段值，需加方括号；Now()：返回当前系统日期和时间操作步骤添加学生表，选择学号、姓名、性别、出生日期；在空白字段列输入计算表达式；保存查询并运行。生成以下SQL语句SELECT学生.学号,学生.姓名,学生.性别,DateDiff("yyyy",[出生日期],Now())AS年龄FROM学生;其他常用计算示例文本拼接：姓名:[姓]&[名]数值运算：总价:[单价]*[数量]创建参数查询3.5任务3.5创建参数查询–1/3什么是参数查询是一种“交互式”查询，无需修改查询设计即可改变查询结果执行时弹出对话框提示用户输入信息输入的信息动态构成查询条件为什么使用参数查询提高查询的灵活性同一查询可根据不同输入返回不同结果适合需要反复查询不同条件的场景创建方法在“条件”单元格中输入用方括号括起的提示文字语法：[请输入提示信息]示例：根据专业名称检索学生信息数据来源：班级表+学生表参数设置：在专业名称字段的“条件”中输入[请输入专业名称：]运行效果：弹出对话框，输入“计算机网络”后显示对应专业学生。生成以下SQL语句SELECT班级.专业名称,学生.学号,学生.姓名FROM班级INNERJOIN学生ON班级.班级编号=学生.班级编号WHERE(((班级.专业名称)=[请输入专业名称：]));任务3.5创建参数查询–2/3什么是模糊查询通过输入部分信息（如姓氏、名字中的字）来检索匹配记录需要结合LIKE运算符和通配符使用常用通配符*：匹配任意多个字符?：匹配单个字符[]：匹配括号内任一字符示例：根据姓名模糊查询学生信息数据来源：学生表参数设置：在姓名字段的“条件”中输入Like"*"&[请输入姓名或姓名中的一部分：]&"*"运行效果：输入“国”，可查出“李国杰”“江卫国”等所有姓名中包含“国”字的学生生成的SQL语句SELECT学生.学号,学生.姓名,学生.性别,学生.出生日期FROM学生WHERE(((学生.姓名)Like"*"&[请输入姓名或姓名中的一部分：]&"*"));关键点&为字符串连接运算符，将通配符与用户输入拼接两个*表示在输入内容前后均可有其他任意字符任务3.5创建参数查询–3/3多参数查询示例：根据姓名和课程名称查询学生成绩数据来源：学生课程成绩查询（已有查询）参数设置：在两个字段的“条件”中分别设置参数姓名：[请输入学生姓名：]课程名称：[请输入课程名称：]运行效果：依次弹出两个对话框，输入参数后显示指定学生的指定课程成绩生成的SQL语句SELECT学生课程成绩.学号,学生课程成绩.姓名,

学生课程成绩.课程名称,学生课程成绩.成绩FROM学生课程成绩WHERE(((学生课程成绩.姓名)=[请输入学生姓名：])AND((学生课程成绩.课程名称)=[请输入课程名称：]));创建交叉表查询3.6任务3.6创建交叉表查询–1/5什么是交叉表查询一种特殊的查询类型，用于对数据进行分组汇总行标题：显示在数据表左侧，分组字段（可多个）列标题：显示在数据表顶部，分组字段（仅一个）值字段：显示在行与列交叉处，进行统计计算（如求和、平均、计数等）为什么使用交叉表查询解决普通查询中同一学生、同一课程重复出现的问题使数据呈现更加直观、紧凑便于进行数据分析与比较典型应用场景学生成绩表：行标题为专业/班级/姓名，列标题为课程名称，值为成绩销售统计表：行标题为产品，列标题为月份，值为销售额行标题1行标题2课程A课程B课程C专业1张三859078专业1李四928884专业2王五768591任务3.6创建交叉表查询–2/5按专业查询学生各科成绩需求分析行标题：专业名称、班级编号、学号、姓名列标题：课程名称值字段：成绩（使用First函数，每生每课仅一个成绩）操作步骤添加班级表、学生表、课程表、成绩表；在“设计”选项卡中单击“交叉表查询”设置字段的“交叉表”属性：专业名称、班级编号、学号、姓名→行标题课程名称→列标题成绩→值（总计设为“First”）定义参数：在“查询参数”中定义“专业名称”，数据类型为“短文本”在专业名称的“条件”中输入：[专业名称]保存为“按专业查询成绩”运行效果：输入专业名称（如“数字媒体”），显示该专业学生的各科成绩任务3.6创建交叉表查询–3/5按班级查询各科平均成绩需求分析行标题：班级编号列标题：课程名称值字段：平均成绩（使用Avg函数）操作步骤添加学生表、成绩表、课程表；添加字段：班级编号（两次）、课程名称、成绩单击“交叉表查询”；设置字段的“交叉表”属性：左边的班级编号→行标题课程名称→列标题成绩→值（总计设为“平均值”）右边的班级编号→总计设为“Where”，交叉表留空定义参数：定义“班级编号”，数据类型为“短文本”在右边班级编号的“条件”中输入：[班级编号]设置成绩字段的格式为“标准”，小数位数2保存为“按班级查询各科平均成绩”运行效果：输入班级编号（如“电2401”），显示该班级各科平均成绩任务3.6创建交叉表查询–4/5按班级查询各科平均成绩生成的SQL语句PARAMETERS班级编号Text(255);TRANSFORMAvg(成绩.成绩)AS成绩之平均值SELECT学生.班级编号FROM...WHERE(((学生.班级编号)=[班级编号]))GROUPBY学生.班级编号PIVOT课程.课程名称;任务3.6创建交叉表查询–5/5交叉表查询语法总结TRANSFORM...PIVOT语句结构PARAMETERS参数名数据类型;TRANSFORM聚合函数(值字段)SELECT行标题字段列表FROM表名[WHERE条件]GROUPBY行标题字段列表PIVOT列标题字段[IN(固定列值列表)];子句作用对应设计视图PARAMETERS定义查询参数“查询参数”对话框TRANSFORM指定值字段的统计方式“总计”行（值字段）SELECT指定行标题字段“交叉表”设为“行标题”GROUPBY对行标题分组自动生成WHERE筛选记录“条件”单元格PIVOT指定列标题字段“交叉表”设为“列标题”IN固定列标题顺序可选项各子句说明创建子查询3.7任务3.7创建子查询–1/4什么是子查询一个SELECT语句嵌套在另一个SELECT语句或其他SQL语句中包含子查询的语句称为主查询，子查询放在圆括号内用于为主查询提供条件或数据子查询的常见用途返回单个值（如平均值、最大值）供主查询比较返回一组值供主查询进行成员测试（IN）进行存在性测试（EXISTS）子查询的基本结构SELECT字段列表FROM表名WHERE字段比较运算符(SELECT...FROM...WHERE...);三种主要类型比较子查询：返回一个值，与主查询比较（>、<、=等）IN子查询：返回一组值，检查主查询值是否在列表中EXISTS子查询：返回是否存在记录，进行存在性测试任务3.7创建子查询–2/4比较子查询——查询数学课成绩高于平均成绩的学生实现思路子查询计算数学课的平均成绩主查询筛选成绩大于子查询返回值的记录操作步骤添加学生表、成绩表、课程表添加字段：学号、姓名、课程名称、成绩在成绩字段的“条件”中输入：

>(SELECTAvg(成绩.成绩)FROM成绩WHERE课程.课程名称="数学")生成的SQL语句SELECT学生.学号,学生.姓名,课程.课程名称,成绩.成绩FROM课程INNERJOIN(学生INNERJOIN成绩ON学生.学号=成绩.学号)ON课程.课程编号=成绩.课程编号WHERE(((成绩.成绩)>(SELECTAvg(成绩.成绩)FROM成绩

WHERE课程.课程名称="数学")));关键点子查询独立执行，返回一个单值（平均分）主查询的WHERE子句使用“>”进行比较子查询中可使用主查询的字段（本例中课程名称）任务3.7创建子查询–3/4IN子查询——查询平均成绩高于80分的学生实现：在学号字段的“条件”中输入：

IN(SELECT学号FROM成绩GROUPBY学号HAVINGAVG(成绩)>80)SQL语句SELECT学生.班级编号,学生.学号,学生.姓名,学生.性别FROM学生WHERE(((学生.学号)IN(SELECT学号FROM成绩

GROUPBY学号HAVINGAVG(成绩)>80)));EXISTS子查询——查询未包含在授课表中的课程实现：使用NOTEXISTS进行存在性测试SQL语句SELECT课程.课程编号,课程.课程名称FROM课程WHERE((NOTEXISTS(SELECT*FROM授课

WHERE课程.课程编号=授课.课程编号)));任务3.7创建子查询–4/4五种子查询类型编写子查询的注意事项子查询必须放在圆括号内子查询应能独立运行并返回预期结果使用EXISTS时，SELECT列表通常用*复杂子查询可能影响性能，必要时可用JOIN替代注意数据类型匹配类型描述典型用法存在性子查询检查是否存在满足条件的记录EXISTS/NOTEXISTS比较子查询返回一个值，与主查询比较>、<、=等IN子查询检查值是否在子查询结果集中IN/NOTINANY/SOME子查询与子查询中任一值比较满足条件>ANY、=SOMEALL子查询与子查询中所有值比较满足条件>ALL通过追加查询添加记录3.8任务3.8通过追加查询添加记录–1/4什么是追加查询一种操作查询，用于向表中添加新记录；通过INSERTINTO语句实现可以在“SQL视图”中直接编写，也可通过“设计视图”创建为什么使用追加查询批量添加数据，提高录入效率可从其他表或查询中复制数据支持参数输入，实现交互式添加两种追加方式单记录追加：每次添加一条记录，通过参数提供字段值多记录追加：从其他表或查询中批量复制记录基本语法--单记录追加INSERTINTO目标表(字段1,字段2,...)VALUES(值1,值2,...);--多记录追加INSERTINTO目标表(字段1,字段2,...)SELECT字段1,字段2,...FROM来源表WHERE条件;任务3.8通过追加查询添加记录–2/4单记录追加查询（带参数）需求：向学生表中添加新记录，字段值在运行时输入实现方法：在SQL视图中直接编写带参数的INSERT语句SQL语句INSERTINTO学生(学号,班级编号,姓名,性别,出生日期)VALUES([学号],[班级编号],[姓名],[性别],[出生日期]);操作步骤创建空白查询，切换到SQL视图输入上述SQL语句保存查询为“添加学生记录（追加查询）”运行查询，在对话框中依次输入各字段值运行效果弹出对话框提示输入学号、班级编号、姓名等输入完成后，新记录追加到学生表末尾任务3.8通过追加查询添加记录–3/4多记录追加查询——批量向成绩表追加记录需求：根据学生所在班级，从授课表中获取课程编号，为未录入成绩学生批量生成成绩记录操作要点第一步：创建选择查询，找出需要追加的数据添加学生表、授课表；选择字段：学号、课程编号、班级编号设置条件：学号：NOTIN(SELECT学号FROM成绩)（尚未录入成绩的学生）班级编号：[学生].[班级编号]（与授课表班级匹配）第二步：将选择查询转换为追加查询单击“追加”命令，指定目标表为“成绩”设计网格中出现“追加行”，映射目标字段生成的SQL语句INSERTINTO成绩(学号,课程编号)SELECT学生.学号,授课.课程编号FROM授课,学生WHERE(((学生.学号)NotIn(SELECT学号FROM成绩))AND((授课.班级编号)=[学生].[班级编号]));任务3.8通过追加查询添加记录–4/4追加查询小结与注意事项两种语法对比单记录追加 INSERTINTO...VALUES... 逐条添加新记录，配合参数使用多记录追加 INSERTINTO...SELECT... 批量复制数据，从其他表导入注意事项目标表的字段列表与源数据必须一一对应（字段数量、数据类型）如果目标表有主键，必须确保追加的值唯一且非空若目标表包含自动编号字段：省略该字段可让Access自动生成新编号包含该字段可保留原始值运行前建议先执行相同的选择查询，预览将要追加的数据追加查询的应用场景从临时表导入数据到正式表批量生成关联记