第3章 数据查询与SQL命令.

Presentation on theme: "第3章 数据查询与SQL命令."— Presentation transcript:

1 第3章 数据查询与SQL命令

2 本章主要内容 3.1 查询对象概述 3.2 通过向导或设计视图创建查询 3.3 使用SQL命令查询 3.4 操作查询 总结和作业、实验任务

3 思考问题： 哪位导师带了哪些研究生？ 导师年龄是否能自动更新？ 将“考古学”专业的研究生入学分数增加15%以降低入学难度？
研究生信息自动转存为毕业生信息？

4 3.1 查询对象概述 查询对象的实质是SQL命令，本身不包含数据。屏幕所显示的数据是查询对象在执行时从表中“抽取”的。
3.1 查询对象概述 查询对象的实质是SQL命令，本身不包含数据。屏幕所显示的数据是查询对象在执行时从表中“抽取”的。 生成查询对象的途径：查询向导、查询设计视图和SQL视图

5 3.1 查询对象概述 查询能实现的功能： 根据某种规则查找部分数据 同时显示分布在若干个表或查询中的数据 对表中的数据进行计算、分类、汇总
3.1 查询对象概述 查询能实现的功能： 根据某种规则查找部分数据 同时显示分布在若干个表或查询中的数据 对表中的数据进行计算、分类、汇总 将显示结果转换成数据表 成批更新、删除表中的数据 将筛选出的数据追加到另一个表

6 3.2 通过向导或设计视图创建查询 3.2.1 用简单查询向导生成查询 3.2.2 通过设计视图编辑或创建查询 3.2.3 条件查询
3.2 通过向导或设计视图创建查询 用简单查询向导生成查询 通过设计视图编辑或创建查询 条件查询 查询的有序输出 使用通配符查询 使用计算字段 交叉表查询

7 用简单查询向导生成查询

8 用简单查询向导生成查询 [例3-1] 基于“导师”表生成“导师情况表”查询 ,只显示姓名、职称和系编号字段。

9 3.2.1 用简单查询向导生成查询 说明： （1）作为查询对象，“导师情况 表”查询只是一条SQL命令，并不 包含数据。数据来自“导师”表。
用简单查询向导生成查询 说明： （1）作为查询对象，“导师情况 表”查询只是一条SQL命令，并不 包含数据。数据来自“导师”表。 （2）如果打开查询对象时对数据 进行更新、添加，则数据的变化实 际发生在数据源(“导师”表)中。 （3）数据源如果被删除，则查询无法打开，并显 示出错信息

10 前提：“导师”表和“研究生”表已建立1:M关系
用简单查询向导生成查询 [例3-2] 基于“导师”和“研究生”两个表生成“导师- 研究生”查询。 显示导师的编号、姓名、职称及所带研究生的学号、姓名和入学分数 前提：“导师”表和“研究生”表已建立1:M关系

11 用简单查询向导生成查询 说明： 查询对象的字段名一般沿用数据表中的字段名，如果出现相同的字段名，系统自动在字段名前加上表的名字以示区别

12 通过设计视图编辑或创建查询 打开已有查询的设计视图：单击 “设计”按钮

13 选中“学号”列，选择“插入/列”菜单，在学号前插入一空白列
通过设计视图编辑或创建查询 [例3-3]：为例3-2查询添加“博导”列（位于“职称”的右侧） 选中“学号”列，选择“插入/列”菜单，在学号前插入一空白列

14 通过设计视图编辑或创建查询 在设计视图中创建查询

15 3.2.2 通过设计视图编辑或创建查询 [例3-4]查询 各系的系名 导师姓名和 研究生姓名
通过设计视图编辑或创建查询 [例3-4]查询 各系的系名 导师姓名和 研究生姓名 思考：如果“系”与“导师”表之间、“导师”与“研究生”表之间没有建立关系，将得到什么结果？

16 通过设计视图编辑或创建查询 [例3-5]按系名显示 各研究生的姓名 “导师”表是“系”表与“研究生”表之间的联系“桥梁”

17 3.2.3 条件查询 条件查询的作用是对所选择的记录作进一步的限定 [例3-6]显示女研究生的全部信息 不显示以避免数据重复
条件查询 条件查询的作用是对所选择的记录作进一步的限定 [例3-6]显示女研究生的全部信息 “=”号可以省略 不显示以避免数据重复

18 3.2.3 条件查询 [例3-7] 列出入学分数在340-360之间的所有研究生的姓名、性别和入学分数
条件查询 [例3-7] 列出入学分数在 之间的所有研究生的姓名、性别和入学分数 And关系：两边的条件同时成立，整个表达式才成立。

19 3.2.3 条件查询 [例3-8] 选出所有入学分数在340-360之间的男研究生的姓名、性别、入学分数
条件查询 [例3-8] 选出所有入学分数在 之间的男研究生的姓名、性别、入学分数 几个条件左右并列，表明是And关系(并且) 。

20 条件查询 [例3-9] 选出研究生方向为考古学或会计学的所有研究生的姓名、性别、入学分数和研究方向 '考古学' or '会计学'

21 3.2.3 条件查询 [例3-10] 显示导师“马腾跃”所带的女研究生或者入学分数超过320分的男研究生的信息 上下并列的两个
条件查询 [例3-10] 显示导师“马腾跃”所带的女研究生或者入学分数超过320分的男研究生的信息 上下并列的两个 条件，表示or关系

22 条件查询 [例3-11] 找出所有没有导师编号的研究生姓名 没有导师编号

23 3.2.4 查询的有序输出 [例3-12] 按性别的升序和导师编号的降序显示记录
查询的有序输出 [例3-12] 按性别的升序和导师编号的降序显示记录 如果有若干个列是排序关键字段，Access按从左到右的顺序依次排序

24 3.2.4 查询的有序输出 [例3-13] 按性别的升序和导师编号的降序显示记录，输出字段顺序为：姓名、导师编号、性别和入学分数
查询的有序输出 [例3-13] 按性别的升序和导师编号的降序显示记录，输出字段顺序为：姓名、导师编号、性别和入学分数 输出字段顺序 排序顺序

25 3.2.5 使用通配符查询 如果想查找某个姓“马”的导师的信息，该如何给出查询条件呢？
使用通配符查询 如果想查找某个姓“马”的导师的信息，该如何给出查询条件呢？ 通配符可以用作其它字符的占位符，用以实现在仅知部分内容的情况下完成对文本、数值等类型字段的查询

26 使用通配符查询（P62表3.1）

27 3.2.5 使用通配符查询 [例3-14] 找出研究方向的第3个字为“医”的所有姓“马”的研究生的信息。
使用通配符查询 [例3-14] 找出研究方向的第3个字为“医”的所有姓“马”的研究生的信息。 注意：应用通配符时不能用“=”，要用“like” 第3个字为“医” Like 是执行字符串匹配操作的特殊运算符 所有姓“马”的

28 3.2.5 使用通配符查询 [例3-15] 找出年龄为50余岁的导师，要求其所带的研究生中没有姓马或赵的学生。 年龄为50余岁
使用通配符查询 [例3-15] 找出年龄为50余岁的导师，要求其所带的研究生中没有姓马或赵的学生。 年龄为50余岁 排除姓“马”、“赵”的 确定给定的字符串是否与指定的模式匹配。模式可以包含常规字符和通配符字符。模式匹配过程中，常规字符必须与字符串中指定的字符完全匹配。然而，可使用字 符串的任意片段匹配通配符。与使用 = 和 != 字符串比较运算符相比，使用通配符可使 LIKE 运算符更加灵活。如果任何参数都不属于字符串数据类型，Microsoft® SQL Server™ 会将其转换成字符串数据类型(如果可能)。

29 3.2.6 使用计算字段 计算字段的值由表达式计算而得，不保存在表中 若表达式中引用的字段或值发生了变化，必须重新执行查询
使用计算字段 计算字段的值由表达式计算而得，不保存在表中 若表达式中引用的字段或值发生了变化，必须重新执行查询 [例3-16] 输出导师的编号、姓名、出生年份和性别。 计算字段，“出生年份”为别名 2009-[年龄] 改成： Year(Now())-[年龄]

30 3.2.7 交叉表查询 交叉表用于显示表中某个字段的汇总值，包括总和、计数和平均等，如查询研究生表中不同性别的不同研究方向的学生人数。
交叉表查询 交叉表用于显示表中某个字段的汇总值，包括总和、计数和平均等，如查询研究生表中不同性别的不同研究方向的学生人数。 1. 用向导生成交叉表

31 3.2.7 交叉表查询 [例3-17]生成一个交叉表，显示不同性别不同研究方向各导师所带研究生的平均分
交叉表查询 [例3-17]生成一个交叉表，显示不同性别不同研究方向各导师所带研究生的平均分 分类汇总的依据可以有多个 缺点：(1) 标题“总计 入学分数”含义不清 (2) 平均数的小数点位数太多

32 交叉表查询 2. 在设计视图中完善【3-17】交叉表 原为：总计 入学分数 修改这两栏的属性

33 3.2.7 交叉表查询 3. 引用两个表的字段生成交叉表 [例3-18] 显示不同系不同性别的导师人数
交叉表查询 3. 引用两个表的字段生成交叉表 [例3-18] 显示不同系不同性别的导师人数 先建立一个查询，包含两个表中的所需字段 根据所建查询用向导创建交叉表

34 3.3 使用SQL命令查询 查询的实质是SQL命令

35 3.3 使用SQL命令查询 3.3.1 SQL语言概述 3.3.2 基于单一记录源的查询 3.3.3 基于多个记录源的查询
基于单一记录源的查询 基于多个记录源的查询 合计、汇总与计算 嵌套查询

36 3.3.1 SQL语言概述 SQL语言易学易用，语句由近似自然语言的英语单词组成. 例如， Select 学号,姓名,入学分数,研究方向
SQL(Structured Query Language)意为结构化查询语言，可用于定义、查询、更新、管理关系型数据库系统。 SQL语言易学易用，语句由近似自然语言的英语单词组成. 例如， Select 学号,姓名,入学分数,研究方向 From 研究生 Where 性别='男' 显示“研究生”表中所有男生的学号、姓名、入学分数和研究方向

37 3.3.1 SQL语言概述 SQL语言不涉及数据库内部细节，通用性好。
不能设计出与用户交互的图形界面，需用VB、VC等程序语言作为客户端开发工具，同时在程序中嵌入SQL命令。 SQL命令的所有子句既可以写在同一行上，也可以分行书写,大小写字母的含义相同； 命令用分号“;”结束(也可以不写)。

38 3.3.1 SQL语言概述 SELECT 字段列表 [INTO 新表] FROM 记录源 [WHERE <条件表达式>]
[GROUP BY <分组表达式>] [HAVING <条件表达式>] [ORDER BY 字段列表[ASC|DESC]] [ ]中的内容为可选项

39 基于单一记录源的查询 单一记录源：被查询的对象是一个表或查询 进入SQL视图： 先进入空白查询设计视图(不选择表)

40 基于单一记录源的查询 1. 选取记录源的全部字段或部分字段 [例3-19] 输出“导师”表的全部字段 *表示记录源的全部字段

41 基于单一记录源的查询 [例3-20] 以例[3-19]所建立的 “导师SQL”查询为记录源，显示其中的“导师编号”、“姓名”和“职称”字段 SELECT 导师编号,姓名,职称 FROM 导师SQL

42 3.3.2 基于单一记录源的查询 说明： （1）字段名之间的逗号“,”必须是英文字符
基于单一记录源的查询 说明： （1）字段名之间的逗号“,”必须是英文字符 （2）如果重命名查询“导师SQL”，则本查询中引用的记录源名字将自动更新。 （3）作为记录源的“导师SQL”查询不能删除 （4）当真正的记录源“导师”表中的数据更新时，查询的执行结果也自动更新。

43 3.3.2 基于单一记录源的查询 2. 用DISTINCT消除重复记录 [例3-21]显示“导师”表中所有“职称”名称 SELECT 职称
基于单一记录源的查询 2. 用DISTINCT消除重复记录 [例3-21]显示“导师”表中所有“职称”名称 SELECT 职称 FROM 导师 SELECT DISTINCT 职称 FROM 导师

44 3.3.2 基于单一记录源的查询 3. 用TOP显示前面的若干条记录 语法格式：TOP n （n为指定记录数）
基于单一记录源的查询 3. 用TOP显示前面的若干条记录 语法格式：TOP n （n为指定记录数） [例3-22] 显示“导师”表中的前4条记录 SELECT TOP 4 * FROM 导师

45 3.3.2 基于单一记录源的查询 4. 用WHERE子句对记录进行选择 语法格式：
基于单一记录源的查询 4. 用WHERE子句对记录进行选择 语法格式： WHERE <表达式> <关系运算符> <表达式> （关系运算符的名称和含义见表3.2）

46 3.3.2 基于单一记录源的查询 4. 用WHERE子句对记录进行选择 [例3-23] 找年龄不低于60岁的导师，显示部分字段
基于单一记录源的查询 4. 用WHERE子句对记录进行选择 [例3-23] 找年龄不低于60岁的导师，显示部分字段 SELECT 姓名,性别,年龄 FROM 导师 WHERE 年龄>=60

47 3.3.2 基于单一记录源的查询 可用逻辑运算符形成逻辑表达式，见表3.3。 逻辑运算符的优先级依次为：NOT>AND>OR
基于单一记录源的查询 可用逻辑运算符形成逻辑表达式，见表3.3。 逻辑运算符的优先级依次为：NOT>AND>OR 运算符 名称 含义 NOT 非 取反，若表达式成立，则取反后不成立，反之则成立 AND 与 若干个关系表达式同时成立才成立，否则不成立 OR 或 若干个关系表达式中，只要一个成立，逻辑表达式就成立

48 3.3.2 基于单一记录源的查询 [例3-24]找出所有年龄不低于60岁的女导师，显示其姓名、性别和年龄。 SELECT 姓名,性别,年龄
基于单一记录源的查询 [例3-24]找出所有年龄不低于60岁的女导师，显示其姓名、性别和年龄。 SELECT 姓名,性别,年龄 FROM 导师 WHERE 导师.年龄>=60 AND 性别='女'

49 3.3.2 基于单一记录源的查询 [例3-25] 输出所有入学分数在350以上的女研究生，以及
基于单一记录源的查询 [例3-25] 输出所有入学分数在350以上的女研究生，以及 所有考古学研究方向的研究生姓名、性别、入学分数、研究 方向。 SELECT 姓名,性别,入学分数,研究方向 FROM 研究生 WHERE 入学分数>350 AND 性别='女' OR 研究方向='考古学'

50 基于单一记录源的查询 思考与练习： [例3-26] 请写出SQL语句：输出例[3-25]规则以外的全部研究生的姓名、性别、入学分数、研究方向。

51 3.3.2 基于单一记录源的查询 5. 用特殊运算符过滤记录
基于单一记录源的查询 5. 用特殊运算符过滤记录 例如:“入学分数 BETWEEN 320 AND 360”指入学分数在320~360分之间，包括320分和360分者；而“入学分数 IN(320,360)”指入学分数等于320或者360。

52 3.3.2 基于单一记录源的查询 [例3-27]找出所有入学分数在320-360之间的“考古 学”和“会计学”方向的研究生姓名、性别、入学
基于单一记录源的查询 [例3-27]找出所有入学分数在 之间的“考古 学”和“会计学”方向的研究生姓名、性别、入学 分数、研究方向。 SELECT 姓名, 性别, 入学分数, 研究方向 FROM 研究生 WHERE 研究方向 IN('考古学','会计学') AND 入学分数 BETWEEN 320 AND 360

53 3.3.2 基于单一记录源的查询 [例3-28]找出所有没有导师的研究生，显示他们的所有信息。 SELECT * FROM 研究生
基于单一记录源的查询 [例3-28]找出所有没有导师的研究生，显示他们的所有信息。 SELECT * FROM 研究生 WHERE 导师编号 IS NULL 导师编号为空

54 3.3.2 基于单一记录源的查询 [例3-29]找出所有姓马或者姓名中含有“国”字的研究生，显示他们的所有信息。 SELECT *
基于单一记录源的查询 [例3-29]找出所有姓马或者姓名中含有“国”字的研究生，显示他们的所有信息。 SELECT * FROM 研究生 WHERE 姓名 LIKE '马*' OR 姓名 LIKE '*国*' 姓“马”或姓名中含有“国”

55 3.3.2 基于单一记录源的查询 [例3-30] 显示学号尾数不在0-4范围中的研究生。 SELECT * FROM 研究生
基于单一记录源的查询 [例3-30] 显示学号尾数不在0-4范围中的研究生。 SELECT * FROM 研究生 WHERE 学号 LIKE '*[!0-4]' 学号尾数不在0~4 条件也可写成：WHERE 学号 LIKE '*[5-9]'

56 3.3.2 基于单一记录源的查询 6. 用ORDER BY子句将记录排序输出 排序的方式：升序、降序
基于单一记录源的查询 6. 用ORDER BY子句将记录排序输出 排序的方式：升序、降序 [例3-31] 按性别的升序和入学分数的降序，输出 入学分数在340分以上的研究生的全部信息。

57 3.3.2 基于单一记录源的查询 SELECT * FROM 研究生 WHERE 入学分数>340
基于单一记录源的查询 SELECT * FROM 研究生 WHERE 入学分数>340 ORDER BY 性别, 入学分数 DESC 性别升序、入学分数降序

58 3.3.3 基于多个记录源的查询 数据源－多表 建立表之间的关系－公共属性 连接数据表的方式： WHERE子句 JOIN子句：
基于多个记录源的查询 数据源－多表 建立表之间的关系－公共属性 连接数据表的方式： WHERE子句 JOIN子句： 内连接、左外连接和右外连接

59 3.3.3 基于多个记录源的查询 1. 用WHERE实现表间关系 [例3-32] 输出全体导师的姓名及所带的研究生姓名
基于多个记录源的查询 1. 用WHERE实现表间关系 [例3-32] 输出全体导师的姓名及所带的研究生姓名 SELECT 导师.姓名,研究生.姓名 FROM 导师,研究生 WHERE 导师.导师编号=研究生.导师编号 查询结果排除： (1)没带研究生的导师 (2)无导师编号的研究生

60 3.3.3 基于多个记录源的查询 说明： 不同表的同名字段前要冠以表名以示区别
基于多个记录源的查询 说明： 不同表的同名字段前要冠以表名以示区别 ACCESS允许字段名中保留空格，字段名中若有空格，引用时用[ ]括起来。例如字段名为“姓 名”，则引用是写成：“导师.[姓 名]”

61 3.3.3 基于多个记录源的查询 2. 用内连接INNER JOIN实现表与表的连接 INNER JOIN出现在FROM子句中，格式为：
基于多个记录源的查询 2. 用内连接INNER JOIN实现表与表的连接 INNER JOIN出现在FROM子句中，格式为： FROM <表1> INNER JOIN <表2> ON <条件表达式> [例3-33] 输出全体导师的姓名及所带的研究生姓名 SELECT 导师.姓名,研究生.姓名 FROM 导师 INNER JOIN 研究生 ON 导师.导师编号=研究生.导师编号

62 基于多个记录源的查询 思考问题：请观察[例3-32] 与[例3-33]的区别？

63 基于多个记录源的查询 使用WHERE 子句与使用INNER JOIN子句的区别在于：使用INNER JOIN子句可以实现在数据表“一”方添加新记录，而WHERE子句不可以。

64 3.3.3 基于多个记录源的查询 注意：在较长的查询命令中可用别名代替表名。
基于多个记录源的查询 注意：在较长的查询命令中可用别名代替表名。 [例3-34] 输出导师马腾跃的姓名、性别及其所带研究生的姓名、性别、入学分数。 SELECT t.姓名,t.性别,s.姓名,s.性别,入学分数 FROM 导师 AS t INNER JOIN 研究生 AS s ON t.导师编号=s.导师编号 WHERE t.姓名='马腾跃' t作为“导师”表的别名，s作为“研究生”表的别名 入学分数只有“研究生”表才有，可以不使用表名

65 3.3.3 基于多个记录源的查询 [例3-35] 请写出SQL语句：显示所有系的系名、系中每位导师的姓名和每位导师所带研究生的姓名。
基于多个记录源的查询 [例3-35] 请写出SQL语句：显示所有系的系名、系中每位导师的姓名和每位导师所带研究生的姓名。 Select 系.系名,导师.姓名,研究生.姓名 from (系 inner join 导师 on 系.系编号=导师.系编号 ) inner join 研究生 on 导师.导师编号=研究生.导师编号 Select 系.系名,导师.姓名,研究生.姓名 from 系 ,导师, 研究生 where 系.系编号=导师.系编号 and 导师.导师编号=研究生.导师编号

66 3.3.3 基于多个记录源的查询 3. 用左外连接实现表与表的连接 4. 用右外连接实现表与表的连接
基于多个记录源的查询 3. 用左外连接实现表与表的连接 FROM <表1> LEFT JOIN <表2> ON <条件条件式> 将左侧表(<表1>)中连接字段的值全部显示 4. 用右外连接实现表与表的连接 FROM <表1> RIGHT JOIN <表2> ON <条件表达式> 将右侧表(<表2>)中连接字段的值全部显示

67 3.3.3 基于多个记录源的查询 [例3-36]显示每位导师的姓名及其研究生姓名 SELECT 导师.姓名, 研究生.姓名
基于多个记录源的查询 [例3-36]显示每位导师的姓名及其研究生姓名 [例3-37]显示全部研究生姓名及其带教导师的姓名 SELECT 导师.姓名, 研究生.姓名 FROM 导师 LEFT JOIN 研究生 ON 导师.导师编号 = 研究生.导师编号 左侧表：导师 右侧表：研究生 SELECT 导师.姓名, 研究生.姓名 FROM 导师 RIGHT JOIN 研究生 ON 导师.导师编号=研究生.导师编号

68 基于多个记录源的查询 [例3-36] (16条记录) [例3-37] (18条记录)

69 合计、汇总与计算 1. 合计函数 （1）COUNT函数 （2）SUM函数 （3）MAX和MIN函数 （4）AVG函数

70 3.3.4 合计、汇总与计算 1. 合计函数 （1）COUNT函数：统计符合条件的记录条数 [例3-38]统计分数高于340分的男研究生人数
合计、汇总与计算 1. 合计函数 （1）COUNT函数：统计符合条件的记录条数 [例3-38]统计分数高于340分的男研究生人数 也可以用“*”, “学号”, “性别”作为计数的对象 SELECT COUNT(姓名) FROM 研究生 WHERE 入学分数>=340 AND 性别='男' SELECT COUNT(姓名) AS 合格人数 FROM 研究生 WHERE 入学分数>=340 AND 性别='男'

71 3.3.4 合计、汇总与计算 （2）SUM函数：求和。 参与的字段必须为数值类型 [例3-39] 求导师陈平林所带的研究生入学分数总和
合计、汇总与计算 （2）SUM函数：求和。 参与的字段必须为数值类型 [例3-39] 求导师陈平林所带的研究生入学分数总和 SELECT SUM(入学分数) AS 入学分数总计 FROM 导师,研究生 WHERE 导师.导师编号=研究生.导师编号 AND 导师.姓名='陈平林'

72 3.3.4 合计、汇总与计算 （3）MAX和MIN函数：分别用于在指定的记 录范围内找出具有最大值和最小值的字段。
合计、汇总与计算 （3）MAX和MIN函数：分别用于在指定的记 录范围内找出具有最大值和最小值的字段。 [例3-40]找出男生中的最高分和最低分 SELECT MAX(入学分数) AS 最高分, MIN(入学分数) AS 最低分 FROM 研究生 WHERE 性别='男'

73 3.3.4 合计、汇总与计算 （4）AVG函数：求平均，参数为数值类型。 [例3-41]输出计算机系所属研究生的平均入学分数
合计、汇总与计算 （4）AVG函数：求平均，参数为数值类型。 [例3-41]输出计算机系所属研究生的平均入学分数 SELECT AVG(入学分数) AS 入学平均分 FROM 系, 导师, 研究生 WHERE 系.系编号=导师.系编号 AND 导师.导师编号=研究生.导师编号 AND 系名='计算机系' SELECT AVG(入学分数) AS 入学平均分 FROM (系 INNER JOIN 导师 ON 系.系编号=导师.系编号) INNER JOIN 研究生 ON 导师.导师编号=研究生.导师编号 WHERE 系名='计算机系'; “系”表与“研究生”表没有直接联系，必须以“导师”表为桥梁进行组合查询

74 3.3.4 合计、汇总与计算 2. 分组查询 将输出记录分成若干组，以字段值相同的记录为一组，配合合计函数进行统计汇总操作。
合计、汇总与计算 2. 分组查询 将输出记录分成若干组，以字段值相同的记录为一组，配合合计函数进行统计汇总操作。 格式： GROUP BY 分组表达式1[,分组表达式2[,...]]

75 3.3.4 合计、汇总与计算 [例3-42]按性别统计导师的平均年龄 SELECT 性别, AVG(年龄) AS 平均年龄 FROM 导师
合计、汇总与计算 [例3-42]按性别统计导师的平均年龄 SELECT 性别, AVG(年龄) AS 平均年龄 FROM 导师 GROUP BY 性别 SELECT 性别,ROUND(AVG(年龄),1) AS 平均年龄 FROM 导师 GROUP BY 性别 注：ROUND(X,N):使输出值X保留N位小数

76 合计、汇总与计算 使用GROUP BY 子句进行分组时，显示的字段只能是参与分组的字段以及基于分组字段的合计函数计算结果。

77 3.3.4 合计、汇总与计算 ORDEY BY子句总是出现在SELECT语句的最后
合计、汇总与计算 ORDEY BY子句总是出现在SELECT语句的最后 [例3-43]统计各导师所带不同性别研究生的入学最高分，按导师编号升序，最高分的值降序排序。 SELECT 导师编号, 性别, MAX(入学分数) AS 最高分 FROM 研究生 GROUP BY 导师编号, 性别 ORDER BY 导师编号, MAX(入学分数) DESC 思考：分组子句可否写为“GROUP BY 导师编号”？

78 3.3.4 合计、汇总与计算 3. HAVING函数：该函数与GROUP BY子句联合使用，可对分组后的结果进行限制。
合计、汇总与计算 3. HAVING函数：该函数与GROUP BY子句联合使用，可对分组后的结果进行限制。 [例3-44]计算每一位导师所带研究生的平均入学分数、人数和他们的导师编号，没有导师的3位研究生自成一组，对人数超过2人的组输出统计结果。

79 3.3.4 合计、汇总与计算 注意：HAVING不能用WHERE代替 对分组人数超过2人的组输出统计结果
合计、汇总与计算 SELECT ROUND(AVG(入学分数),1), COUNT(姓名) AS 本组人数, 导师编号 FROM 研究生 GROUP BY 导师编号 HAVING COUNT(姓名)>2 对分组人数超过2人的组输出统计结果 注意：HAVING不能用WHERE代替

80 3.3.4 合计、汇总与计算 4. 计算列 实质是一个表达式，使用方式同一个字段 [例3-45]显示入校时间已达2年的研究生信息及年数
合计、汇总与计算 4. 计算列 实质是一个表达式，使用方式同一个字段 [例3-45]显示入校时间已达2年的研究生信息及年数 表达式(计算列) 给表达式赋列名 SELECT 研究生.*, YEAR(NOW())-YEAR(入学日期) AS 在校年数 FROM 研究生 WHERE YEAR(NOW())-YEAR(入学日期)=2 入校时间已达两年

81 3.3.5 嵌套查询 嵌套：将第一次查询的结果作为第二次查询的条件 [例3-46]显示所有入学分数高于平均分的研究生数据 错误： 正确：
嵌套查询 嵌套：将第一次查询的结果作为第二次查询的条件 [例3-46]显示所有入学分数高于平均分的研究生数据 错误： 正确： SELECT 姓名,入学分数 FROM 研究生 WHERE 入学分数>AVG(入学分数) SELECT 姓名, 入学分数 FROM 研究生 WHERE 入学分数>(SELECT AVG(入学分数)FROM 研究生) 内嵌套查询先被执行

82 第1行可否为 : SELECT 姓名,入学分数,AVG(入学分数) ？？？
嵌套查询 将上例修改为： 显示字段同时输出平均分 SELECT 姓名, 入学分数, ROUND((SELECT AVG(入学分数) FROM 研究生),2) AS 平均分 FROM 研究生 WHERE 入学分数>(SELECT AVG(入学分数) FROM 研究生) 第1行可否为 : SELECT 姓名,入学分数,AVG(入学分数) ？？？

83 3.3.5 嵌套查询 [例3-47] 找出年龄最高的导师信息及其所在系的系名 SELECT 系名, 导师.* FROM 系, 导师
嵌套查询 [例3-47] 找出年龄最高的导师信息及其所在系的系名 SELECT 系名, 导师.* FROM 系, 导师 WHERE 系.系编号=导师.系编号 AND 年龄=(SELECT MAX(年龄) FROM 导师) 嵌套查询：先找出所有导师中的最高年龄

84 3.3.5 嵌套查询 [例3-48] 找出社科系最年轻导师的全部信息 SELECT 系名, 导师.* FROM 系, 导师
嵌套查询 [例3-48] 找出社科系最年轻导师的全部信息 SELECT 系名, 导师.* FROM 系, 导师 WHERE 系.系名='社科系' AND 系.系编号=导师.系编号 AND 年龄=(SELECT MIN(年龄) FROM 系,导师 WHERE 系.系名='社科系' AND 系.系编号=导师.系编号) 嵌套查询：找出社科系导师的最小年龄

85 3.4 操作查询 操作查询的特点： 执行后并非显示结果（ 与SELECT查询的区别） 可以按某种规则更新字段值 可以删除表中记录
3.4 操作查询 操作查询的特点： 执行后并非显示结果（ 与SELECT查询的区别） 可以按某种规则更新字段值 可以删除表中记录 可以将SELECT查询的结果生成一个新的数据表 可以将SELECT查询的执行结果追加到另外一个数据表中

86 3.4 操作查询 生成表查询 追加查询 更新查询 删除查询

87 3.4.1 生成表查询 作用：将SELECT命令执行的结果形成一个表保存在数据库中。 操作方法：
生成表查询 作用：将SELECT命令执行的结果形成一个表保存在数据库中。 操作方法： SQL代码：在SELECT命令的字段名列表后加上子句INTO <新表名> 在查询设计视图中完成：打开查询设计视图 → 执行“查询”/“生成表查询”菜单命令

88 3.4.1 生成表查询 [例3-49]将所有女研究生的全部信息按导师编号降 序、入学分数升序生成Female表。 SQL代码：
生成表查询 [例3-49]将所有女研究生的全部信息按导师编号降 序、入学分数升序生成Female表。 SQL代码： SELECT 研究生.* INTO Female FROM 研究生 WHERE 性别='女' ORDER BY 导师编号 DESC,入学分数 练习：通过设计视图完成

89 3.4.2 追加查询 作用：将一个表中符合条件的部分或全部记录添加到另一个表中 操作方法： 打开查询设计视图，选择表
追加查询 作用：将一个表中符合条件的部分或全部记录添加到另一个表中 操作方法： 打开查询设计视图，选择表 “查询”菜单选择“追加查询”.

90 3.4.2 追加查询 [例3-50] 将研究生表中的全部男研究生信息追加到 表Female中，要求不包含记录中“性别”信息
追加查询 [例3-50] 将研究生表中的全部男研究生信息追加到 表Female中，要求不包含记录中“性别”信息 只追加男研究生的信息 但不追加“性别”字段

91 追加查询 例3-50对应的SQL代码（了解）： INSERT INTO Female (学号,姓名,入学日期,分数,研究方向,导师编号) SELECT 学号,姓名,入学日期,入学分数,研究方向,导师编号 FROM 研究生 WHERE 性别='男'

92 3.4.2 追加查询 几点说明： 待追加的字段与追加到的字段的名称可以不一致，但类型相同或兼容。 追加操作不应该破坏数据的完整性约束。
追加查询 几点说明： 待追加的字段与追加到的字段的名称可以不一致，但类型相同或兼容。 追加操作不应该破坏数据的完整性约束。 待追加的字段数可以少于目的表的字段数，但追加到目的表主键的字段不能省略，追加到外键字段的值也必须是有效的值。

93 3.4.3 更新查询 [例3-51] 将“导师”表每位导师的年龄增加1岁 作用：根据某种规则批量修改表中的数据
更新查询 作用：根据某种规则批量修改表中的数据 操作：打开设计视图， “查询”/“更新查询” [例3-51] 将“导师”表每位导师的年龄增加1岁 UPDATE 导师 SET 导师.年龄 = [年龄]+1 SQL代码： 更新查询对表中记录的修改不得违反所有的数据完整性约束 字段名必须用[ ]括起来

94 更新查询 [例3-52] 计算机系所有男同学的分数增加10% 不能写成110%

95 3.4.4 删除查询 作用：按规则一次删除表中所有符合条件的记录 操作：打开设计视图， “查询”/“删除查询”
删除查询 作用：按规则一次删除表中所有符合条件的记录 操作：打开设计视图， “查询”/“删除查询” 执行删除查询前先复制一个副本以备恢复数据

96 3.4.4 删除查询 [例3-53] 用删除查询删除所有入学分数在340分或以上的男研究生的记录。 SQL代码：
删除查询 [例3-53] 用删除查询删除所有入学分数在340分或以上的男研究生的记录。 删除查询在删除记录时自动遵守“参照完整性规则” DELETE 入学分数, 性别 FROM 研究生 WHERE ((入学分数>=340) AND (性别='男')) SQL代码：

97 3.4.4 删除查询 [例3-54] 删除陈平林导师所带研究生中分数最低者
删除查询 [例3-54] 删除陈平林导师所带研究生中分数最低者 本例首先要找出陈平林所带研究生的入学分数最低分，因此在条件中需嵌套查询。 (SELECT MIN(入学分数) FROM 导师,研究生 WHERE 导师.导师编号=研究生.导师编号 AND 导师.姓名='陈平林')

98 第3章主要内容总结 查询对象概述 查询的实质、生成查询的方法 通过向导或设计视图创建查询 条件查询、有序输出、通配符、计算字段、交叉表查询
使用SQL命令查询 SQL及其查询语句的格式 基于单一记录源（*，distinct，top，where，特殊运算符，order by） 多个记录源的查询（where、join） 合计（count、sum、max、min、avg）、汇总（group by、having）与计算 嵌套查询 操作查询 生成表（into）、追加、更新、删除

99 本章作业 P （1）~（4）

100 实验（二）——数据查询与SQL命令 第6周4个学时，第3章P86实验题： 要求（1）~（13）写预习报告，给出相应的SQL命令
所有的题目都要完成，以“数据查询与SQL命令 –班级姓名.mdb”提交实验成果 实验素材见公共邮箱

101 06年4月全国计算机二级考试

102 06年4月全国计算机二级考试

103 06年4月全国计算机二级考试

104 06年9月全国计算机二级考试 1. SQL的含义是 A）结构化查询语言 B）数据定义语言 C）数据库查询语言 D）数据库操纵与控制语言
2. 在SQL的SELECT语句中，用于实现选择运算的是 A）FOR B）WHILE C）IF D）WHERE

105 06年9月全国计算机二级考试 3. 以下关于空值的叙述中，错误的是 A）空值表示字段还没有确定值 B）Access使用NULL来表示空值
D）空值不等于数值0 4. 若要查询某字段的值为“JSJ”的记录，在查询设计视图对应字段的准则中，错误的表达式是 A）JSJ B）”JSJ” C）”*JSJ” D）Like “JSJ”

106 06年9月全国计算机二级考试 从所显示的内容中可以判断出该查询要查找的是 A）性别为“女”并且1980以前参加工作的记录
5. 下面显示的是查询设计视图的“设计网格”部分： 从所显示的内容中可以判断出该查询要查找的是 A）性别为“女”并且1980以前参加工作的记录 B）性别为“女”并且1980以后参加工作的记录 C）性别为“女”或者1980以前参加工作的记录 D）性别为“女”或者1980以后参加工作的记录

107 07年4月全国计算机二级考试 1.Access中已建立了“工资”表，表中包括“职工”、“所在单位”、“基本工资”和“应发工资”等字段，如果要按单位统计应发工资总数，那么在查询设计视图的“所在单位”的“总计”行和“应发工资”的“总计”行中分别选择的是 A) sum，group by B)count，group by C)group by，sum D)group by，count 2.在创建交叉表查询时，列标题字段的值显示在交叉表的位置是() A) 第一行 B)第一列 C)上面若干行 D)左面若干列

108 07年4月全国计算机二级考试 3.在Access中已建立了“学生”表，表中有“学号”、“姓名”、“性别”和“入学成绩”等字段。执行如下SQL命令： Select 性别，avg（入学成绩）from 学生 group by 性别 其结果是 A)计算并显示所有学生的性别和入学成绩的平均值 B)按性别分组计算并显示性别和入学成绩的平均值 C)计算并显示所有学生的入学成绩的平均值 D)按性别分组计算并显示所有学生的入学成绩的平均值 4.在SQL的select命令中用()短语对查询的结果进行排序。 Order by

109 07年9月全国计算机二级考试 1.将表A的记录添加到表B中，要求保持表B中原有的记录，可以使用的查询是 A）选择查询　　　　　B）生成表查询 C）追加查询　　　　　D）更新查询 2.在Access中，查询的数据源可以是 A）表　　　　　B）查询 C）表和查询　　D）表、查询和报表 3如果在查询的条件中使用了通配符方括号“[　]”，它的含义是 A）通配任意长度的字符　 　B）通配不在括号内的任意字符 C）通配方括号内列出的任一单个字符　　　　 　D）错误的使用方法

110 07年9月全国计算机二级考试 4.现有某查询设计视图（如下图所示），该查询要查找的是
A）身高在160以上的女性和所有的男性 B）身高在160以上的男性和所有的女性 C）身高在160以上的所有人或男性 D）身高在160以上的所有人

111 08年4月全国计算机二级考试 1.下列SQL查询语句中，与下面查询设计视图所示的查询结果等价的是
A)SELECT　姓名，性别，所属院系，简历　FROM　tStud 　　WHERE　性别="女"　AND　所属院系　IN("03","04") B)SELECT　姓名，简历　FROM　tStud 　　WHERE　性别="女"　AND　所属院系　IN("03","04") C)SELECT　姓名，性别，所属院系，简历　FROM　tStud 　　WHERE　性别="女"　AND　所属院系="03"　OR　所属院系="04" D)SELECT　姓名，简历　FROM　tStud 　　WHERE　性别="女"　AND　所属院系="03"　OR　所属院系="04"

112 08年4月全国计算机二级考试 3. 如果在数据库中已有同名的表，要通过查询覆盖原来的表，应该使用的查询类型是 A)删除　　B)追加　C)生成表　D)更新 4. 条件"Not　工资额>2000"的含义是 A)选择工资额大于2000的记录 B)选择工资额小于2000的记录 C)选择除了工资额大于2000之外的记录 D)选择除了字段工资额之外的字段，且大于2000的记录 5. 在Access中，要在查找条件中与任意一个数字字匹配，可使用的通配符是【】。 #

113 08年9月全国计算机二级考试 1. 假设有一组数据：工资为800元，职称为“讲师”，性别为“男”，在下列逻辑表达式中结果为“假”的是 A)工资>800　AND　职称=“助教”　OR　职称=“讲师” B)性别=“女”　OR　NOT　职称=“助教” C)工资=800　AND　(职称=“讲师”　OR　性别=“女”) D)工资>800　AND　(职称=“讲师”　OR　性别=“男”）

114 08年9月全国计算机二级考试 2. 在建立查询时，若要筛选出图书编号是“T01”或“T02”的记录，可以在查询设计视图准则行中输入 A)“T01”　or　“T02”　　 　B)“T01”　and　“T02” C)in(“T01”　and　“T02”)　　 D)not　in(“T01”　and　“T02”) 3. 在Access数据库中使用向导创建查询，其数据可以来自 A)多个表　　　　　　　　　　B)一个表 C)一个表的一部分　　　　　　D)表或查询