Access数据库实用教程(第4章)ppt课件

1、Access 数据库实用教程,2,第4章 查询的创建和使用,查询概述 选择查询的创建 在查询中进行计算 交叉表查询的创建 参数查询的创建 操作查询的创建 SQL查询的创建 已建查询的编辑和使用,3,4.1 查询概述,4.1.1 查询的功能 问题提出 建立数据库的目的是为更好地管理和使用其中的数据，获取有用信息。但数据是分散放在各个表中的，如何有效地获取待查询的信息 例如 ,“张三”的考试成绩？男女同学的人数,4,4.1 查询概述,4.1.1 查询的功能 问题提出 查询含义 查询对象不是数据的集合，而是操作的集合 查询的结果是一个数据集，也称为动态集。它很像一个表，但并没有存储在数据库中 创建查

2、询后，只保存查询的操作，只有在运行查询时，才会从查询数据源中抽取数据，并创建它；只要关闭查询，查询的动态集就会自动消失,5,4.1 查询概述,4.1.1 查询的功能 问题提出 查询含义 查询功能 选取数据 编辑数据 实现计算 建立新表 为窗体和报表提供数据,6,4.1 查询概述,4.1.2 查询的类型 选择查询 根据指定的查询条件，从一个或多个数据源中获取数据并显示结果 交叉表查询 将来源于某个数据源中的字段进行分组，一组列在数据表的左侧，一组列在数据表的上部，然后在行与列的交叉处显示某字段的统计值,7,4.1 查询概述,4.1.2 查询的类型 参数查询 利用对话框来提示用户输入查询条件，并根

3、据输入的条件来检索记录 操作查询 四种：生成表、删除、更新和追加,8,4.1 查询概述,4.1.2 查询的类型 SQL查询 使用SQL语句创建的查询 某些SQL查询称为SQL特定查询，包括四种，联合查询、传递查询、数据定义查询和子查询,9,4.2 选择查询的创建,4.2.1 使用查询向导 操作者可在向导指示下选择一个或多个数据源、一个或多个字段创建查询 使用向导不能创建带条件的查询,10,4.2 选择查询的创建,4.2.1 使用查询向导 使用“简单查询向导,例4-1 查找“教师”表中的记录，并显示“姓名”、“性别”、“工作时间”和“系别”等字段信息 所建查询数据源来自于一个表,例4-2 查找每

4、名学生选课成绩，并显示“学生编号”、“姓名”、“课程名称”和“考试成绩”等字段。查询名为“学生选课成绩” 所建查询数据源来自于一个表,11,4.2 选择查询的创建,4.2.1 使用查询向导 使用“查找不匹配项查询向导,例4-3 判断“学生”表中是否有重名学生，如果有显示“姓名”、“学生编号”、“性别”和“入校日期”，查询名为“学生重名查询” 根据“查找重复项查询向导”创建的查询结果，可以确定“学生”表中的“姓名”字段是否存在相同的值,12,4.2 选择查询的创建,4.2.1 使用查询向导 使用“查找重复项查询向导,例4-4 查找哪些课程没有学生选修，并显示“课程编号”和“课程名称,13,4.2

5、 选择查询的创建,4.2.2 使用设计视图 查询设计视图组成,对象窗格,设计网格,14,4.2 选择查询的创建,4.2.2 使用设计视图 步骤 添加数据源表或查询 选择查询内容字段 填写查询条件（可选） 设置排序（可选,15,4.2 选择查询的创建,4.2.2 使用设计视图 创建不带条件的查询,例4-5 使用设计视图创建查询，查找并显示授课教师的“系别”、“姓名”、“课程名称”和“学分”，要求按系别从大到小顺序显示 分析：查询用到“系别”、“姓名”、“课程名称”和“学分”等字段分别来自“教师”和“课程”两个表，但两表间没有直接关系，需通过“授课”表建立两表间关系。故应创建基于“教师”、“课程”

6、和“授课”3个表的查询,16,4.2 选择查询的创建,4.2.2 使用设计视图 创建带条件的查询,例4-6 查找1992年参加工作的男教师，并显示“姓名”、“性别”、“学历”、“职称”、“系别”和“电话号码” 分析：要创建上述查询，需两个条件，一是性别值为“男”，二是工作时间值为1992，且两个字段值均应等于条件给定的值。故两条件是“与”关系,17,4.2 选择查询的创建,4.2.2 使用设计视图 创建带条件的查询,例4-6 查找1992年参加工作的男教师，并显示“姓名”、“性别”、“学历”、“职称”、“系别”和“电话号码,18,4.2 选择查询的创建,4.2.3 查询条件 是指在查询中用于限

7、制检索记录的条件表达式，由运算符、常量、字段值、函数和字段名组成 运算符：是构成条件表达式的基本元素。Access提供了四种运算符，分别是算术运算符、关系运算符、逻辑运算符和特殊运算符,19,4.2 选择查询的创建,4.2.3 查询条件 运算符 算术运算符：+ - * / 关系运算符：= 逻辑运算符：and or not 其他：Between and ，like，in，Is Null，Is Not Null,20,4.2 选择查询的创建,4.2.3 查询条件 运算符 函数 数值函数：Abs，Int，Sqr， 字符函数：Left，Right，Len，,21,4.2 选择查询的创建,4.2.3 查

8、询条件 运算符 函数 条件表达式示例,22,4.2 选择查询的创建,4.2.3 查询条件 设置查询条件时注意 日期型数据两边加“#”，字符型数据两边加半角双引号 在条件中字段名必须用方括号括起来 两个以上条件时，同行相与，异行相或 条件中引用表名时，应用方括号括起来，与字段名之间用“！”。如 教师档案表!姓名,23,设置查询条件,例如：性别为“男”的教师,24,设置查询条件,例如：高级职称的教师,25,设置查询条件,例如：高级职称的“女”教师,26,设置查询条件,例如：05级学生的成绩,假定学号的前四位为年级,27,设置查询条件,例如：没有联系电话的教师,28,设置查询条件,例如：姓“李”的学

9、生,29,4.3 在查询中进行计算,4.3.1 查询中的计算功能 预定义计算 是系统提供的用于对查询中的记录组或全部记录进行的计算 包括：合计、平均值、最小值、最大值、计数等 自定义计算 用一个或多个字段进行数值、日期和文本计算 需要在查询设计网格中直接添加字段,30,4.3 在查询中进行计算,4.3.2 总计查询,例4-8 统计教师人数,31,4.3 在查询中进行计算,4.3.2 总计查询,例4-9 统计1992年参加工作的教师人数,32,4.3 在查询中进行计算,4.3.2 总计查询,例4-10 在“1992年参加工作人数统计”查询中，将以“教师编号”字段统计的结果显示标题改为“教师人数”

10、,33,4.3 在查询中进行计算,4.3.3 分组总计计算 将记录进行分组，对每个组的值进行统计 分组统计时，只要在该字段的“总计”行上选择“分组”就可以了,例4-11 计算各类职称的教师人数，并显示“职称”和“人数,34,4.3 在查询中进行计算,4.3.4 计算字段 统计的数据不在表中，或者用于计算的数据值来源于多个字段时,例4-12 计算每名教师的工龄，并显示“姓名”、“系别”、“职称”和“工龄,按照题目要求，需将“工龄”设置为计算字段，其值可根据系统当前日期和工作时间计算得出。计算表达式为：Year(Date()-Year(工作时间,35,4.3 在查询中进行计算,4.3.4 计算字段

11、 统计的数据不在表中，或者用于计算的数据值来源于多个字段时,例4-12 计算每名教师的工龄，并显示“姓名”、“系别”、“职称”和“工龄,36,4.4 交叉表查询的创建,4.4.1 交叉表查询的概念 所谓交叉表查询，就是将来源于某个表中的字段进行分组，一组列在数据表的左侧，一组列在数据表的上部，然后在数据表行与列的交叉处显示表中某个字段的各种计算值 与Excel中的数据透视表一样,37,4.4 交叉表查询的创建,4.4.2 使用查询向导,例4-14 创建一个交叉表查询，统计每班男女生人数,注意：若所建交叉表查询涉及多张表,使用向导时，应先建立多表查询，再建立交叉表查询,38,4.4 交叉表查询的

12、创建,4.4.3 使用设计视图,例4-15 创建一个交叉表查询，使其显示各班每门课程的总评成绩的平均分,39,4.5 参数查询的创建,参数查询的含义 利用对话框，提示用户输入参数，并检索符合所输参数的记录或值 可以建立一个参数的查询，也可以建立多个参数的查询,40,4.5 参数查询的创建,4.5.1 单参数查询 创建单参数查询，即指定一个参数。在执行单参数查询时，输入一个参数值,例4-16 按学生姓名查找某学生成绩，并显示“学生编号”、“姓名”、“课程名称”及“考试成绩”等,41,4.5 参数查询的创建,4.5.2 多参数查询 创建多参数查询，即指定多个参数。在执行多参数查询时，需依次输入多个

13、参数值,例4-17 建立一个查询，使其显示某门课程某成绩范围内的学生“姓名”、“课程名称”和“总评成绩,42,4.6 操作查询的创建,操作查询的含义 操作查询是指仅在一个操作中更改许多记录的查询 例如，在一个操作中删除一组记录，更新一组记录等等,43,4.6 操作查询的创建,操作查询的类型 生成表查询: 利用一个或多个表中的数据创建一个新表 删除查询: 从一个或多个表中删除指定的数据 更新查询: 对一个或多个表中的记录进行更新 追加查询: 从一个或多个表中将一组记录追加到另一个或多个表中,44,4.6 操作查询的创建,4.6.1 生成表查询 生成表查询是利用一个或多个表中的全部或部分数据生成一

14、个新表,例4-18 将总评成绩在90分以上的学生信息存储到一个新表中，表名为“90分以上学生情况”，表内容为“学生编号”、“姓名”、“性别”、“年龄”和“考试成绩”等字段,45,4.6 操作查询的创建,4.6.2 删除查询 能够从一个或多个表中删除一条或多条记录,例4-19 将选课成绩表中总评成绩小于60分的记录删除,46,4.6 操作查询的创建,4.6.3 更新查询,例20 将所有1988年及以前参加工作的教师职称改为副教授,47,4.6 操作查询的创建,4.6.4 追加查询 能将一个或多个表中经过选择的数据追加到另一个已存在表的尾部,例4-21 建立一个追加查询将考试成绩在8090分之间的

15、学生成绩添加到已建立的“90分以上学生情况”表中,48,4.6 操作查询的创建,注意： 操作查询执行后，不能撤消刚 刚做 过的更改操作,49,4.7 SQL查询的创建,4.7.1 显示SQL语句 在Access中，任何一个查询都对应着一条SQL语句。 在创建查询时，系统会自动地将操作命令转换为SQL语句，只要打开查询，切换到SQL视图，就可以看到系统生成的SQL语句,50,4.7 SQL查询的创建,4.7.2 常用SQL语句 SQL语句 查询：SELECT 操纵：INSERT、UPDATE、DELETE 定义：CREATE、DROP、ALTER 控制：GRANT、REVOTE,51,4.7 S

16、QL查询的创建,4.7.2 常用SQL语句 INSERT语句,格式： INSERT INTO (,) VALUES (),52,4.7 SQL查询的创建,4.7.2 常用SQL语句 INSERT语句,例4-22 在“授课”表中插入一条新记录(8, 105, 96011,INSERT INTO 授课 VALUES (8, 105, 96011,53,4.7 SQL查询的创建,4.7.2 常用SQL语句 UPDATE语句,格式： UPDATE SET = WHERE,54,4.7 SQL查询的创建,4.7.2 常用SQL语句 UPDATE语句,例4-23 将“教师”表中“教师编号”为“96011”

17、教师的工作时间改为：1992-2-26,UPDATE 教师 SET 工作时间= #1992-2-26# WHERE 教师编号=96011,55,4.7 SQL查询的创建,4.7.2 常用SQL语句 DELETE语句,格式： DELETE FROM WHERE,56,4.7 SQL查询的创建,4.7.2 常用SQL语句 DELETE语句,例4-24 将“教师”表中教师编号为“98014”的记录删除,DELETE * FROM 教师 WHERE 教师编号=98014,57,4.7 SQL查询的创建,4.7.2 常用SQL语句 SELECT语句,格式： SELECT , FROM , WHERE G

18、ROUP BY ASC|DESC,58,4.7 SQL查询的创建,4.7.2 常用SQL语句 SELECT语句,例4-25 查找并显示“教师”表中“姓名”、“性别”、“工作时间”和“系别”4个字段,SELECT 姓名,性别,工作时间,系别 FROM 教师,59,4.7 SQL查询的创建,4.7.2 常用SQL语句 SELECT语句,例4-26 查找1992年参加工作的男教师，并显示“姓名”、“性别”、“学历”、“职称”、“系别”和“联系电话,SELECT 姓名, 性别, 学历, 职称, 系别, 电话号码 FROM 教师 WHERE 性别=男 AND YEAR(工作时间)=1992,60,4.7

19、 SQL查询的创建,4.7.2 常用SQL语句 SELECT语句,例4-27 查找具有高级职称的教师，并显示“姓名”和“职称,SELECT 姓名, 职称 FROM 教师 WHERE 职称 IN(教授,副教授,61,4.7 SQL查询的创建,4.7.2 常用SQL语句 SELECT语句,例4-28 仅显示年龄排在前5位的学生“姓名”和“年龄,SELECT TOP 5 姓名, 年龄 FROM 学生 ORDER BY 年龄 DESC,62,4.7 SQL查询的创建,4.7.2 常用SQL语句 SELECT语句,例4-29 计算各类职称的教师人数，显示字段名为“各类职称人数,SELECT Count(

20、教师编号) AS 各类职称人数 FROM 教师 GROUP BY 职称,63,4.7 SQL查询的创建,4.7.3 SQL特定查询的创建 联合查询,格式： SELECT FROM , WHERE UNION ALL SELECT FROM , WHERE,64,4.7 SQL查询的创建,4.7.3 SQL特定查询的创建 联合查询,例4-30 显示“90分以上学生情况”表中所有记录和“学生选课成绩”查询中70分以下的记录，显示内容为“学生编号”、“姓名”、“总评成绩”等3个字段,SELECT 学生编号,姓名,考试成绩 FROM 学生选课成绩 WHERE 考试成绩70 UNION SELECT 学

21、生编号,姓名,考试成绩 FROM 90分以上学生情况,65,4.7 SQL查询的创建,4.7.3 SQL特定查询的创建 数据定义查询 CREATE语句,格式： CREATE TABLE ( 字段完整性约束条件， 字段完整性约束条件,66,4.7 SQL查询的创建,4.7.3 SQL特定查询的创建 数据定义查询 CREATE语句,例4-31 使用CREATE TABLE语句创建“学生情况”表,CREATE TABLE 学生情况 (学生ID SMALLINT,姓名 CHAR(4),性别 CHAR(1), 出生日期 DATE,家庭住址 CHAR(20),联系电话 CHAR(8), 备注 MEMO,Primary Key (学生ID,67,4.7 SQL查询的创建,4.7.3 SQL特定查询的创建 数据定义查询 ALTER语句,格式： ALTER TABLE ADD 字段完整性约束条件 DROP 完整性约束名 MODIFY,68,4.7 SQL查询的创建,4.7.3 SQL特定查询的创建 数据定义查询 ALTER语句,例4-32 将“学生情况”表中“学生ID”字段的数据类型改为文本型，字段大小为10,ALTER TABLE 学生情况