第4章 数据查询

1、14.1 查询简述4.2 使用向导创建查询4.3 自己设计查询4.4 查询实例4.5 利用查询实现对表数据的修改4.6 SQL查询4.7 对查询结果的处理21. 什么是查询 查询是按照一定的条件或要求对数据库中特定数据信息的查找。 查询可以对一个数据库中的一个表或多个表中存储的数据信息进行查找、统计、计算、排序等。 查询的结果可以作为窗体、报表、数据访问页或另一个查询的数据源。 3 Access的查询有三种视图模式:设计视图数据表视图SQL视图42 .Access的查询类型 Access支持以下5种类型的查询: 选择查询 参数查询 交叉表查询 操作查询 SQL查询。5 Access提供了多种向

2、导以方便查询的创建。对于初学者来说，选择使用向导的帮助可以快捷地建立所需要的查询。61.使用简单查询向导 例:在“教学管理”数据库中新建教师基本情况的查询 双击选择表或查询选择单一字段选择所有字段72 使用交叉表查询向导 交叉表查询以水平方式和垂直方式对记录进行分组，并计算和重构数据，可以简化数据分析。交叉表查询计算数据总和、计数、平均值以及其他类型的综合计算。通过交叉表查询可以在一个数据表中以行标题将数据组成群组，按列标题来分别求得所需汇总的数据（如总和或平均值），然后在数据表中以表格的形式显示出来。8例：在“教学管理”数据库中迅速建立这样一个查询：“学生中各民族的男女生的人数” 93使用查

3、找重复项查询向导查找重复项查询向导可以对数据表中某些具有相同的字段值的记录进行检索、分类 例：查找“教师中各种职称的人数” 104查找不匹配项查询向导 查找不匹配项查询向导可以在一个表中查找与另一个表中没有相关记录的记录。例：完成“没有选课的学生”的查询111 查询的设计视图“字段字段” 查询中所使用的字段的名称查询中所使用的字段的名称 “表表” 该字段所来自的数据对象（表或查询）该字段所来自的数据对象（表或查询） “排序排序” 确定是否按该字段排序以及按何种方式排序确定是否按该字段排序以及按何种方式排序 “显示显示” 确定该字段是否在查询结果集中可见确定该字段是否在查询结果集中可见 “条件条

4、件” 用来指定该字段的查询条件用来指定该字段的查询条件 “或或” 用来提供多个查询条件用来提供多个查询条件12查询目标的确定 在查询中显示的字段值可以是原表的字段值，也可以是经过字段表达式计算的结果 年薪：工资*12 课程安排:第 & 开课学期 & 学期 133. 查询条件表达式的设置 设计查询时，如果需要查找满足某一条件的记录，需要在查询设计视图中的“条件”行输入查询的条件表达式。除了直接输入常量外，还可以使用比较运算符、逻辑运算符、特殊运算符、数学运算符和Access的内部函数等来构成表达式。在查询设计区的“条件”行中输入表达式时，如果各个表达式处于同一行，则各个表达式之间

5、应该是逻辑与的关系；如果各个表达式处于不同行，则各个表达式之间应该是逻辑或的关系。14比较运算符比较运算符含义含义大于大于=大于等于大于等于小于小于=小于等于小于等于=等于等于不等于不等于逻辑运算符逻辑运算符含义含义Not 逻辑非逻辑非And 逻辑与逻辑与Or逻辑或逻辑或例如，下列表达式：（1）考试成绩在85到70之间表达式在 “条件”行对应的“成绩”字段名的单元格中输入： =70 （2）籍贯是“北京”或“南京”的表达式在 “条件”行对应的“籍贯”字段名的单元格中输入： 北京 Or 天津“（3）民族是少数民族的表达式在 “条件”行对应的“民族”字段名下单元格中输入：Not “汉”15查找少数民

6、族的女学生信息查询和查找少数民族学生或是女学生信息查询16其他运算符其他运算符含义含义BetweeAnd 指定值的范围在指定值的范围在到到之间之间In 指定值属于列表中所列出的值指定值属于列表中所列出的值Is与与Null一起使用确定字段值是否一起使用确定字段值是否为空值为空值Like用通配符查找文本型字段值用通配符查找文本型字段值是否与其匹配是否与其匹配通配符是通配符是 ？匹配任意单个字？匹配任意单个字符；符； *匹配任意多个字符；匹配任意多个字符； # 匹匹配任意单个数字；配任意单个数字； ！不匹配指！不匹配指定的字符；定的字符；字符列表字符列表匹配任何在列表匹配任何在列表中的单个字符。中的

7、单个字符。例如，下列表达式：例如，下列表达式：（1）出生日期在）出生日期在1985年年1月月1日到日到1989年年12月月31日的表达式日的表达式. Between #1985/1/1# And #1989/12/31#在条件表达式中输入日期值时在条件表达式中输入日期值时,需要在日期值两需要在日期值两边加上边加上”#”符号。符号。（2）职称为教授或副教授的表达式）职称为教授或副教授的表达式In (“教授教授”，“副教授副教授”)在条件表达式中输入字符串时在条件表达式中输入字符串时,需要在字符串两需要在字符串两边用双引号引起来。边用双引号引起来。 （3）没有参加考试（即成绩为空值）的件表达）没有

8、参加考试（即成绩为空值）的件表达式式 Is Null（4）姓名是姓）姓名是姓“张张”的表达式的表达式Like “张张*”（5）学号第一位是）学号第一位是0，第二位是，第二位是0、1（即（即00级、级、01级学生）的表达式级学生）的表达式 Like “001*”17函数 1.常用数值函数 函数功能示例结果Abs (数值表达式)返回数值表达式值的绝对值Abs (-30)30Int (数值表达式)返回数值表达式值的整数部分值，如果数值表达式的值是负数，返回小于或等于数值表达式值的第一负整数int(5.5) int(-5.5) 5-6Fix (数值表达式)返回数值表达式值的整数部分值，如果数值表达式的

9、值是负数，返回大于或等于数值表达式值的第一负整数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 (数值表达式)返回数值表达式值指定的空格个数组成的空字符串教

10、学 & Space(2) & 管理教学 管理String (数值表达式, 字符表达式)返回一个由字符表达值的第一个字符重复组成的由数值表达式值指定长度的字符串string(4,abcdabcdabcd)aaaaLen (字符表达式)返回字符表达式的字符个数Len(教学&管理)4Left (字符表达式, 数值表达式)按数值表达式值取字符表达式值的左边子字符串left(数据库管理系统,3)数据库Right (字符表达式, 数值表达式)按数值表达式值取字符表达式值的右边子字符串right(数据库管理系统,2)系统Mid (字符表达式, 数值表达式1,数值表达式2)从字符表达式

11、值中返回以数值表达式1规定起点，以数值表达式2指定长度的字符串Mid(“abcd”&”efg”,3,3)cdeLtrim (字符表达式)返回去掉字符表达式前导空格的字符串教学 &(ltrim( 管理)教学管理Rtrim (字符表达式)返回去掉字符表达式尾部空格的字符串Rtrim(教学 )&管理教学管理Trim (字符表达式)返回去掉字符表达式前导和尾部空格的字符串trim( 教学 )&管理教学管理193. 常用日期函数 函数功能示例结果Date ()返回当前系统日期Month (日期表达式)返回日期表达式对应的月份值month(#2010-03-02#)3Yea

12、r (日期表达式)返回日期表达式对应的年份值Year(#2010-03-02#)2010Day (日期表达式)返回日期表达式对应的日期值day(#2010-03-02#)2Weekday (日期表达式)返回日期表达式对应的星期值Weekday(#2010-04-02#)6204. 常用统计函数 函数功能示例结果Sum(字符表达式)返回表达式所对应的数字型字段的列值的总和Sum(成绩)计算成绩字段列的总和Avg(字符表达式)返回表达式所对应的数字型字段的列中所有值的平均值。Null 值将被忽略Avg(成绩)计算成绩字段列的平均值Count(字符表达式)Count(*)返回含字段的表达式列中值的数

13、目或者表或组中所有行的数目（如果指定为COUNT(*)）。该字段中的值为Null（空值）时，COUNT(数值表达式)将不把空值计算在内，但是 COUNT(*)在计数时包括空值Count(成绩)统计有成绩的学生人数Max(字符表达式)返回含字段表达式列中的最大值(对于文本数据类型,按字母排序的最后一个值)。忽略空值Max(成绩)返回成绩字段列的最大值Min(字符表达式)返回含字段表达式列中最小的值(对于文本数据类型,按字母排序的第一个值)。忽略空值Min(成绩)返回成绩字段列的最小值215. 常用域聚合函数 函数功能示例结果DSum(字符表达式1, 字符表达式2 ,字符表达式3)返回指定记录集的

14、一组值的总和DSum(成绩,选课,学号=10150226)求“选课”表中学号为“10150226”的学生选修课程的总分DAvg(字符表达式1, 字符表达式2 ,字符表达式3)返回指定记录集的一组值的平均值DAvg(成绩,选课,课程号=TC01)求“选课”表中课程号为“TC01”的课程的平均分DCount(字符表达式1, 字符表达式2 ,字符表达式3)返回指定记录集的记录数DCount(学号,学生,性别=男)统计“学生”表中男同学人数DMax(字符表达式1, 字符表达式2 ,字符表达式3)返回一列数据的最大值DMax(成绩,选课,课程号=TC01)求“选课”表中课程号为“TC01”的课程的最高分

15、DMin(字符表达式1, 字符表达式2 ,字符表达式3)返回一列数据的最小值DMi(成绩,选课,课程号=TC01)求“选课”表中课程号为“TC01”的课程的平低分DLookup(字符表达式1, 字符表达式2 ,字符表达式3)查找指定记录集中特定字段的值DLookup(姓名,教师,教师编号=13001)查找“教师”表中教师编号为“13001”的教师的姓名22例：查找参加工作在1980年到2000年之前的学历为硕士或博士，工资在3000元以下少数民族的教师的信息 234.联接类型对查询结果的影响24 例：课程的选修情况的查询 251 选择查询 查询“计算机系学生的选课成绩为及格的”的记录，要求查询

16、结果中包含学生号、学生名、课程号、课程名、成绩。每门课程的成绩按从高到底排列 262参数查询 例：根据输入的“系名”的内容，查找该系教师的情况 273 汇总查询例：查询每个学生学习课程的成绩的总分、平均分、最高分和最低分。 28Group By默认值，用于定义要执行计算的组。这个字段中的记录将按值进行分组。默认值，用于定义要执行计算的组。这个字段中的记录将按值进行分组。Sum计算每一分组中字段值的总和。适用于数字、日期计算每一分组中字段值的总和。适用于数字、日期/时间、货币和自动编号型字段。时间、货币和自动编号型字段。Avg计算每一分组中字段的平均值。适用于数字、日期计算每一分组中字段的平均值

17、。适用于数字、日期/时间、货币和自动编号型字段。时间、货币和自动编号型字段。Min计算每一分组中字段的最小值。适用于文本、数字、日期计算每一分组中字段的最小值。适用于文本、数字、日期/时间、货币和自动编号型字段。时间、货币和自动编号型字段。对于文本型字段，将按照字符的对于文本型字段，将按照字符的ASCII码顺序进行比较。码顺序进行比较。Max计算每一分组中字段的最大值。适用范围与计算每一分组中字段的最大值。适用范围与Min相同相同Count计算每一分组中字段值的计数，该字段中的值为计算每一分组中字段值的计数，该字段中的值为Null（空值）时，将不计算在内。（空值）时，将不计算在内。Where与

18、与“条件条件”行内容配合可以在分组前先筛选记录，并且查询结果中的这个字段将不能被显行内容配合可以在分组前先筛选记录，并且查询结果中的这个字段将不能被显示出来。示出来。StDev计算每一分组中的字段值的标准偏差值。只适用于数字、日期计算每一分组中的字段值的标准偏差值。只适用于数字、日期/时间、货币和自动编号型字时间、货币和自动编号型字段。段。Var计算每一分组中的字段值的方差值。只适用于数字、日期计算每一分组中的字段值的方差值。只适用于数字、日期/时间、货币和自动编号型字段。时间、货币和自动编号型字段。First返回每一分组中该字段的第一个值。返回每一分组中该字段的第一个值。Last返回每一分组

19、中该字段的最后一个值。返回每一分组中该字段的最后一个值。Expression在字段中自定义计算公式，可以套用多个总计函数。在字段中自定义计算公式，可以套用多个总计函数。294交叉表查询例：利用交叉表查询来完成每个学生的第一个学期的选课信息的查询，查询包括学生姓名、所学课程名、成绩和平均分等信息 30查询不仅可以实现对数据的查找，还能很有效地对表中的记录作删除、更新、追加的操作，并且通过查询可以生成新表。311生成表查询生成表查询是将对一个或多个表的查询得到的全部或部分数据来创建新表，这样可以对一些特定的数据进行备份。例：将计算机系的学生记录生成一个新表 322 更新查询更新查询可以对一个或多个

20、表中符合查询条件的数据作批量的更改。例如：将教师中职称为讲师的工资提高5% 333 追加查询追加查询可以是向一个表的尾部添加记录。追加查询可以将从另一个数据表中读取数据记录添加到当前数据库的表中。例如：将电子工程系的学生的记录合并到计算机系学生表中。 344删除查询利用删除查询可以从一个或多个表中删除符合查询条件的一组记录。例如：删除“计算机系学生表”中年龄大于等于21岁的记录。 35SQL查询是使用SQL语句创建的一种查询。SQL(Structured Query Language)结构化查询语言是标准的关系型数据库语言，使用SQL语言可以对数据库实施数据定义、数据操作和数据控制及管理。 3

21、6.1 SQL视图 37SQL语句功能（1）数据定义功能定义、删除、修改关系模式（基本表）定义、删除视图（视图）定义、删除索引（索引）（2）数据操纵功能数据查询数据插入、删除、修改（3）数据控制功能用户访问权限的授予、收回38SQL语句功能SQL语句功能操作符数据定义(表、索引)CREATE，ALTER，DROP数据查询SELECT数据更新INSERT，UPDATE，DELETE393 SQL的数据定义功能（）定义基本表语句格式为：create table 表名 (列名 数据类型 default 缺省值 not null ，列名 数据类型 default 缺省值 not null ，prima

22、ry key（列名 ，列名 ） ，foreign key （列名 ，列名 ） references 表名 （列名 ，列名 ） ，check（条件）) ；例： 定义“学生”表,它包括学号、姓名、出生日期、性别等列（字段），学号为主键，对性别有值的约束条件。CREATE TABLE 学生( 学号 CHAR（4）, 姓名 CHAR（8） NOT NULL, 出生日期 DATE, 性别 CHAR（2）, PRIMARY KEY (学号), CHECK (性别=男 OR 性别=女) ）；40（）.修改基本表定义语句格式：ALTER TABLE 表名ADD 子句 增加列或完整性约束条件DROP 子句删除完

23、整性约束条件MODIFY 子句 修改列定义例： 在“学生”表中增加一个“系号”列 例：将“学生“表中的”姓名”列增加到12个字符的宽度 41（3）删除基本表语句格式：DROP TABLE 表名；注意: 删除基本表后，基本表的定义、表中数据、索引都被删除。例：删除“学生”表 DROP TABLE 学生；42（4） 建立索引语句格式:CREATE UNIQUE/DISTINCT INDEX 索引名ON 表名 (列名 ASC/DESC , 列名ASC/DESC)说明： UNIQUE（DISTINCT）：惟一性索引，不允许表中不同的行在索引列上取相同值。若已有相同值存在，则系统给出相关信息，不建此索引

24、。系统并拒绝违背惟一性的插入、更新。ASC/DESC 选项指定索引排序升序或降序，不指定顺序，索引按升序排列。例： 在“学生”表的“学号”列上建立名为“学号INX”的惟一索引。CREATE UNIQUE INDEX 学号INX ON 学生（学号）；43（5） 删除索引 语句格式： DROP INDEX 索引名 例： 删除建立在学号的索引 DROP INDEX 学号INX444 SQL的数据查询功能数据库查询是数据库的核心操作，SQL语言提供了SELECT语句进行数据查询。该语句的功能强，变化形式较多。SELECT查询语句格式如下：SELECT DISTINCT , , （查询的结果的目标列名表

25、）FROM , , （要操作的关系表或查询名）WHERE （查询结果应满足选择或联接条件）GROUP BY , HAVING（对查询结果分组及分组的条件）ORDER BY ASC|DESC （对查询结果排序）45（）简单查询例： 查找成绩在70到80分之间的学生选课情况SELECT *FROM 选课WHERE 成绩 BETWEEN 70 AND 80;46例：查找出所有姓李的学生的情况SELECT * FROM S WHERE SNAME LIKE 李% ;注意：在SQL语言中字符“%”代表任意一串字符，“_” 代表任意一个字符。在Access2003中用“*” 代表任意一串字符， “？” 代

26、表任意一个字符。47（2）连接查询 一个查询同时涉及两个以上的表时，称其为连接查询。 例： 查询数学系、计算机系、电子工程系的学生的姓名、系名称SELECT 姓名，系名称FROM 学生，系科WHERE 学生.系号=系科.系号 And 系名称 IN（数学系，计算机系，电子工程系）；注意：用“学生.系号=系科.系号”指明两个表的联接条件；“系名称 IN（数学系，计算机系，电子工程）”为选择条件。 48例9 检索计算机系的学生的学号,姓名,年龄 SELECT学号, 姓名,（Date()-出生日期）/365 As 年龄 FROM 学生，系科 WHERE 学生.系号=系科.系号 And 系名称=计算机系

27、;注意：”（Date()-出生日期）/365” 为年龄的字段表达式。49（3）嵌套查询 一个查询语句的WHERE子句中包含一个由比较符号或谓词引导查询语句时，称查询为嵌套查询，被嵌入的查询称为子查询。可以引导子查询的谓词有：IN、ANY、ALL、EXISTS。例： 找出年龄小于李芳的学生姓名SELECT 姓名 FROM 学生WHERE 出生日期 (SELECT 出生日期 FROM 学生 WHERE 姓名=李芳);注意：使用比较符引导的子查询，查询的结果必须为惟一值。 50例：求选修了C程序设计课程的所有学生的学号SELECT 学号FROM 选课WHERE 课程号 IN ( SELECT 课程号

28、 FROM 课程 WHERE 课程名称=C程序设计);注意：本例为嵌套查询-带有子查询的SELEC语句。51例： 找出学习TC02课程的学生姓名.SELECT 姓名 FROM 学生 WHERE EXISTS (SELECT * FROM 选课 WHERE 学号=学生.学号 AND 课程号=TC02)；注意：本例使用EXISTS（量词）的嵌套查询52（4）使用聚集函数的查询 在查询中使用聚集函数，可以对查询的结果进行统计计算。 常用五个的聚集函数：平均值：Avg总和： Sum最小值： Min最大值：Max计数： Count53例：求学号为011534的学生的总分和平均分SELECT Sum(成绩

29、) AS 总分, Avg(成绩) AS 平均分FROM 选课WHERE 学号=011534；注意：本例使用了统计函数Sum（总和）和Avg（平均）例： 求至少选修三门以上课程的学生的学号及选课门数SELECT 学号, COUNT(*) as 选课门数FROM 选课GROUP BY 学号 HAVING COUNT(*)3 ; 注意：分组带有附加条件。每个组的记录条数大于3。54（5）集合查询集合查询是将多个Select 语句的结果进行集合操作构成一个查询例：查询女学生及选课成绩大于80的学生的学号Select 学号From 学生Where 性别=女UnionSelect 学号From 选课Whe

30、re 成绩80555 SQL的数据更新功能SQL中数据更新包括插入数据、修改数据和删除数据三条语句 （1）插入数据语句格式1:INSERT INTO ( ,) VALUES (常量 ，常量.); 说明：该语句一次完成一个记录的插入。语句格式2： INSERT INTO ( ,) 子查询; 说明：子查询嵌入INSERT语句，查询的结果插入到表中。即一次完成批量记录数据的插入。56例： 向学生表插入数据 INSERT INTO 学生(学号, 姓名, 系号) VALUES(031510, 李新, 15)；例：将系号为15的全体学生选修C程序设计课程的信息添入选课表.INSERT INTO 选课 (

31、学号, 课程号,成绩 )SELECT 学号, 课程号, NullFROM 学生,课程WHERE 系号=15 And 课程名称=C程序设计; 57（2）删除数据语句格式: DELETE FROM WHERE 说明:无WHERE子句时,表示删除表中的全部数据. WHERE子句中可以带子查询.例：删除学号为001155的学生记录 DELETE FROM 学生 WHERE 学号=001155;例：删除系号为1的所有学生的选课记录 DELETE FROM 选课 WHERE 1= (SELECT 系号 FROM 学生 WHERE 学生.学号=选课.学号);58（3） 修改数据语句格式: UPDATE SE

32、T = | , = |. WHERE ;说明:修改时,对满足条件表达式的行,将用表达式的值或子查询的结果(唯一值)替换相应列的值。例： 将选课表中的所有选修TC04课程的学生成绩提高5分 UPDATE 选课 SET 成绩 = 成绩 + 5 WHERE 课程号=TC04; 例： 将系号为1的全体学生的TC02课程成绩置0. UPDATE 选课 SET 成绩 = 0 WHERE 课程号=TC02 And 学号 IN (SELECT 学号 FROM 学生 WHERE 系号=1 And 学生.学号=选课.学号); 596 SQL特定查询在Access中将通过SQL语句才能实现查询称为SQL特定查询。SQL特定查询可以分为四类：联合查询、传递查询、数据定义查询和子查询。60（1） 联合查询联合查询是将两个查询的结果集合并在一起，对两个查询要求是：查询结果的字段名、类型相同，字段排列的顺序一致。例：查找选修课程号为TC02