高等院校计算机教材系列 数据库原理与应用（第2版） 任课教师：刘雅莉 E_mail：slxy_lyl@163.com 2014-9.

Presentation on theme: "高等院校计算机教材系列 数据库原理与应用（第2版） 任课教师：刘雅莉 E_mail：slxy_lyl@163.com 2014-9."— Presentation transcript:

1 高等院校计算机教材系列 数据库原理与应用（第2版） 任课教师：刘雅莉 2014-9

2 第5章 数据操作 5.1 数据查询功能 5.2 数据更改功能

3 5.1 数据查询功能 5.1.1 查询语句的基本结构 5.1.2 单表查询 5.1.3 多表连接查询 5.1.4 使用TOP限制结果集
5.1.5 子查询

4 5.1.1 查询语句基本格式 SELECT <目标列名序列> --需要哪些列 FROM <数据源> --来自于哪些表
[WHERE <检索条件>] 根据什么条件 [GROUP BY <分组依据列>] [HAVING <组提取条件>] [ORDER BY <排序依据列>]

5 5.1.2 单表查询 之 1. 选择表中若干列

6 1. 查询指定的列 查询表中用户感兴趣的部分属性列。 例1：查询全体学生的学号与姓名。
SELECT Sno, Sname FROM Student

7 例2.查询全体学生的姓名、学号、所在系 SELECT Sname, Sno, Sdept FROM Student

8 2. 查询全部列 例3．查询全体学生的记录 SELECT Sno,Sname,Ssex, Sage, Sdept FROM Student
等价于： SELECT * FROM Student

9 3. 查询经过计算的列 例5.查询全体学生的姓名及其出生年份。 SELECT Sname, Sage FROM Student

10 常量列 例5．查询全体学生的姓名和出生年份所在系，并在出生年份列前加入一个列，此列的每行数据均为“出生年份”常量值。
SELECT Sname,'出生年份:', 2010-Sage FROM Student

11 改变列标题 列名 | 表达式 [ AS ] 列标题 列标题 ＝列名 | 表达式 语法： 或： 例：
SELECT Sname 姓名, 'Year of Birth' 出生年份, Sage 年份， FROM Student

12 5.1.2 单表查询 之 2. 选择表中若干元组

13 1.消除取值相同的行 例6．查询选修了课程的学生的学号 SELECT Sno FROM SC 有重复行！

14 用DISTINCT去掉结果集中的重复行 SELECT DISTINCT Sno FROM SC

15 2. 查询满足条件的元组 查询条件 谓 词 比较运算符 =, >, >=, <, <=, <>（或!=）
谓 词 比较运算符 =, >, >=, <, <=, <>（或!=） NOT+比较运算符 确定范围 BETWEEN…AND, NOT BETWEEN…AND 确定集合 IN, NOT IN 字符匹配 LIKE, NOT LIKE 空值 IS NULL, IS NOT NULL 逻辑谓词 AND, OR

16 （1）比较大小 例7．查询计算机系全体学生的姓名。 SELECT Sname FROM Student
WHERE Sdept = '计算机系' 例8．查询年龄在20岁以下的学生的姓名及年龄。 SELECT Sname, Sage FROM Student WHERE Sage < 20 例9．查询考试成绩有不及格的学生的学号 SELECT DISTINCT Sno FROM SC WHERE Grade < 60

17 （2）确定范围 用BETWEEN…AND和NOT BETWEEN…AND
如果列或表达式的值在[不在]下限值和上限值范围内，则结果为True，表明此记录符合查询条件。

18 示例 例10．查询年龄在20～23岁之间的学生的姓名、所在系和年龄。
SELECT Sname, Sdept, Sage FROM Student WHERE Sage BETWEEN 20 AND 23 例11．查询年龄不在20～23之间的学生姓名、所在系和年龄。 WHERE Sage NOT BETWEEN 20 AND 23

19 关于日期类型查询 例12．查询2009年6月份出版的全部图书的详细信息。 SELECT * FROM 图书表
WHERE 出版日期 BETWEEN '2009/6/1' AND '2009/6/30' 书号 书名 价格 出版日期 T001 Java程序设计 26.0 T002 数据结构 32.0 T003 操作系统基础 36.5 T004 计算机体系结构 29.5 T005 数据库原理 30.0 T006 汇编语言 34.0 T007 编译原理 38.0 T008 计算机网络 35.0 T009 高等数学 22.0 T010 有机化学 19.5 注意：日期类型的常量要用单引号括起来，而且年、月、日之间通常用分隔符隔开，常用的分隔符有“/”和“-”

20 （3）确定集合 使用IN运算符。 用来查找属性值属于指定集合的元组。 格式为：
列名 [ NOT ] IN (常量1, 常量2, … 常量n) 当列中的值与IN中的某个常量值相等时，则结果为True，表明此记录为符合查询条件的记录； NOT IN：当列中的值与某个常量值相同时，则结果为False，表明此记录为不符合查询条件的记录

21 示例 例13．查询信息系、数学系和计算机系学生的姓名和性别。 例14．查询信息系和计算机系之外的其他系的学生姓名、性别和所在系。
SELECT Sname, Ssex FROM Student WHERE Sdept IN ('信息系', '数学系','计算机系') 例14．查询信息系和计算机系之外的其他系的学生姓名、性别和所在系。 WHERE Sdept NOT IN ('信息系', '计算机系')

22 （4）字符串匹配 使用LIKE运算符 一般形式为： 列名 [NOT ] LIKE <匹配串> 匹配串中可包含如下四种通配符：
_：匹配任意一个字符； %：匹配0个或多个字符； [ ]：匹配[ ]中的任意一个字符；对于连续字母的匹配，例如匹配[abcd]，可简写为[a-d]， [^ ]：不匹配[ ]中的任意一个字符。

23 示例 例15.查询姓‘张’的学生的详细信息。 SELECT * FROM Student WHERE Sname LIKE '张%'
例16．查询学生表中姓‘张’、‘李’和‘刘’的学生的情况。 WHERE Sname LIKE '[张李刘]%' 例17．查询名字中第2个字为‘小’或‘大’的学生的姓名和学号。 SELECT Sname, Sno FROM Student WHERE Sname LIKE '_[小大]%'

24 示例（续） 例18．查询所有不姓“王”也不姓“张”的学生姓名 SELECT Sname FROM Student
WHERE Sname NOT LIKE '[王张]%' 或者： WHERE Sname LIKE '[^王张]%' WHERE Sname NOT LIKE '王%' AND Sname NOT LIKE '张%'

25 示例（续） 例19．查询姓“王”且名字是2个字的学生姓名。 SELECT Sname FROM Student
WHERE Sname LIKE '王_'

26 示例（续） 例20．查询姓王且名字是3个字的学生姓名 SELECT Sname FROM Student
WHERE Sname LIKE '王__' 注意：尾随空格的处理。 SELECT Sname FROM Student WHERE rtrim(Sname) LIKE '王__'

27 例21. 在Student表中查询学号的最后一位不是2、3、5的学生信息。
SELECT * FROM Student WHERE Sno LIKE '%[^235]'

28 转义字符 如果要查找的字符串正好含有通配符，比如下划线或百分号，就需要使用一个特殊子句来告诉数据库管理系统这里的下划线或百分号是一个普通的字符，而不是一个通配符， 这个特殊的子句就是ESCAPE。 ESCAPE的语法格式为：ESCAPE 转义字符 “转义字符”是任何一个有效的字符。 在匹配串中包含转义字符，表明位于该字符后面的字符是普通字符，而不是通配符。

29 示例 例如，查找field1字段中包含字符串“30%”的记录： WHERE field1 LIKE '%30!%%' ESCAPE '!'
查找field1字段中包含下划线（_）的记录： WHERE field1 LIKE '%!_%' ESCAPE '!'

30 （5）涉及空值的查询 空值（NULL）在数据库中表示不确定的值。
例如，学生选修课程后还没有考试时，这些学生有选课记录，但没有考试成绩，因此考试成绩为空值。 判断某个值是否为NULL值，不能使用普通的比较运算符。 判断取值为空的语句格式为： 列名 IS NULL 判断取值不为空的语句格式为： 列名 IS NOT NULL

31 示例 例22．查询没有考试成绩的学生的学号和相应的课程号。 SELECT Sno, Cno FROM SC
WHERE Grade IS NULL 例23．查询所有有考试成绩的学生的学号和课程号。 WHERE Grade IS NOT NULL

32 （6）多重条件查询 在WHERE子句中可以使用逻辑运算符AND和OR来组成多条件查询。
用AND连接的条件表示必须全部满足所有的条件的结果才为True； 用OR连接的条件表示只要满足其中一个条件结果即为True。 例24．查询计算机系年龄在20岁以下的学生姓名。 SELECT Sname FROM Student WHERE Sdept = '计算机系' AND Sage < 20

33 示例（续） 例25．查询计算机系和信息系年龄大于等于20岁的学生姓名、所在系和年龄。
SELECT Sname,Sdept, Sage FROM Student WHERE (Sdept = '计算机系' OR Sdept = '信息系') AND Sage >= 20 或： WHERE Sdept IN ('计算机系', '信息系')

34 5.1.2 单表查询 之 3. 对查询结果进行排序

35 可对查询结果进行排序。 排序子句为： 对查询结果进行排序 说明：按<列名>进行升序（ASC）或降序（DESC）排序。
ORDER BY <列名> [ASC | DESC ] [,<列名> … ] 说明：按<列名>进行升序（ASC）或降序（DESC）排序。

36 示例 例26．将学生按年龄的升序排序。 SELECT * FROM Student ORDER BY Sage
SELECT Sno, Grade FROM SC WHERE Cno='c02' ORDER BY Grade DESC 例28.查询全体学生的信息，查询结果按所在系的系名升序排列，同一系的学生按年龄降序排列。 SELECT * FROM Student ORDER BY Sdept, Sage DESC

37 5.1.2 单表查询 之 5. 使用聚合函数汇总数据

38 使用计算函数汇总数据 SQL提供的聚合函数有： 上述函数中除COUNT（*）外，其他函数在计算过程中均忽略NULL值。
COUNT（[DISTINCT] <列名> ）：统计本列列值个数； SUM（[DISTINCT] <列名> ）：计算列值总和； AVG（[DISTINCT] <列名> ）：计算列值平均值； MAX（[DISTINCT] <列名> ）：求列值最大值； MIN（[DISTINCT] <列名> ）：求列值最小值。 上述函数中除COUNT（*）外，其他函数在计算过程中均忽略NULL值。

39 示例 例29．统计学生总人数。 SELECT COUNT(*) FROM Student 例30．统计选修了课程的学生的人数。
SELECT COUNT (DISTINCT Sno) FROM SC 例31．统计 号学生的考试总成绩之和。 SELECT SUM(Grade) FROM SC WHERE Sno = ' '

40 示例（续） 注意：计算函数不能出现在WHERE子句中 例32．计算C01号课程学生的考试平均成绩。
SELECT AVG(Grade) FROM SC WHERE Cno='C01' 例33．查询C01号课程的考试最高分和最低分。 SELECT MAX(Grade) , MIN(Grade) FROM SC WHERE Cno='C01' 注意：计算函数不能出现在WHERE子句中

41 示例（续） 例34．查询“9512101”学生的选课门数、已考试课程门数以及考试最高分、最低分和平均分。
SELECT COUNT(*) AS 选课门数, COUNT(Grade) AS 考试门数, MAX(Grade) AS 最高分, MIN(Grade) AS 最低分, AVG(Grade) AS 平均分 FROM SC WHERE Sno = ' '

42 5.1.2 单表查询 之 5. 对查询结果进行分组统计

43 对查询结果进行分组计算 作用：可以控制计算的级别：对全表还是对一组。 目的：细化计算函数的作用对象。 分组语句的一般形式：
GROUP BY <分组依据列> [, … n ] [HAVING <组选择条件>]

44 （1）使用GROUP BY 例35．统计每门课程的选课人数，列出课程号和人数。
SELECT Cno as 课程号, COUNT(Sno) as 选课人数 FROM SC GROUP BY Cno 对查询结果按Cno的值分组，所有具有相同Cno值的元组为一组，然后再对每一组使用COUNT计算，求得每组的学生人数。

45 Sno Cno Grade 951201 C01 80 C02 78 951202 90 952103 88 85 C03 91 C04 74 Sno Cno Grade 951201 C01 80 951202 90 952103 85 C02 78 88 C03 91 C04 74 Cno Count(Sno) C01 3 C02 2 C03 1 C04

46 例36．查询每个学生的选课门数和平均成绩。 SELECT Sno as 学号, COUNT(*) as 选课门数,
AVG(Grade) as 平均成绩 FROM SC GROUP BY Sno

47 注意 GROUP BY子句中的分组依据列必须是表中存在的列名，不能使用AS子句指派的结果集列别名。例如，例36中不能将GROU BY子句写成：GROUP BY 学号。 带有GROUP BY子句的SELECT语句的查询列表中只能出现分组依据列或聚合函数，因为分组后每个组只返回一行结果。

48 示例 例37．统计每个系的学生人数和平均年龄。 SELECT Sdept, COUNT(*) AS 学生人数,
AVG(Sage) AS 平均年龄 FROM Student GROUP BY Sdept

49 示例 例38. 带WHERE子句的分组。统计每个系的女生人数。 SELECT Sdept, Count(*) 女生人数
FROM Student WHERE Ssex = '女' GROUP BY Sdept

50 示例 例39. 按多列分组。统计每个系的男生人数和女生人数，以及男生的最大年龄和女生的最大年龄。结果按系名的升序排序。
SELECT Sdept, Ssex, Count(*) 人数, Max(Sage) 最大年龄 FROM Student GROUP BY Sdept, Ssex ORDER BY Sdept

51 （2）使用HAVING HAVING用于对分组自身进行限制，它有点象WHERE子句，但它用于组而不是对单个记录。
例37．查询修了3门以上课程的学生的学号 SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) > 3

52 示例 例40. 查询选修了3门以上课程的学生的学号和选课门数。 SELECT Sno, Count(*) 选课门数 FROM SC
GROUP BY Sno HAVING COUNT(*) > 3 处理过程：先执行GROUP BY子句对SC表数据按Sno进行分组，然后再用统计函数COUNT分别对每一组进行统计，最后筛选出统计结果满足大于3的组。

53 处理过程图示

54 示例 例41．查询修课门数等于或大于4的学生的平均成绩和选课门数。 SELECT Sno, AVG(Grade) 平均成绩,
COUNT(*) 修课门数 FROM SC GROUP BY Sno HAVING COUNT(*) >= 4

55 几个子句比较 WHERE子句用来筛选FROM子句中指定的数据源所产生的行数据。
GROUP BY子句用来对经WHERE子句筛选后的结果数据进行分组。 HAVING子句用来对分组后的结果数据再进行筛选。

56 示例 例42. 查询计算机系和信息管理系的学生人数。 方法1： 方法2： 第二种写法比第一种写法执行效率高。
SELECT Sdept, COUNT(*) FROM Student GROUP BY Sdept HAVING Sdept IN ( '计算机系', '信息管理系') 方法2： SELECT sdept, COUNT (*) FROM Student WHERE Sdept IN ('计算机系', '信息管理系') 第二种写法比第一种写法执行效率高。

57 示例 例43. 查询每个系年龄小于等于20岁的学生人 数。 SELECT Sdept, COUNT (*) FROM Stude nt
WHERE Sage <= 20 GROUP BY Sdept 该查询语句不能写成： SELECT Sdept, COUNT(*) FROM Stude nt HAVING Sage <= 20

58 5.1.3 多表连接查询 若一个查询同时涉及两个或两个以上的表，则称之为连接查询。 连接查询是关系数据库中最主要的查询
连接查询包括内连接、外连接和交叉连接等。

59 连接基础知识 连接查询中用于连接两个表的条件称为连接条件或连接谓词。 一般格式为：
[<表名1.>][<列名1>] <比较运算符> [<表名2.>][<列名2>] 必须语义相同

60 内连接 SQL-92 内连接语法如下： SELECT … FROM 表名 [INNER] JOIN 被连接表 ON 连接条件 WHERE …

61 操作过程 执行连接操作的过程： 首先取表1中的第1个元组，然后从头开始扫描表2，逐一查找满足连接条件的元组，
找到后就将表1中的第1个元组与该元组拼接起来，形成结果表中的一个元组。 表2全部查找完毕后，再取表1中的第2个元组，然后再从头开始扫描表2， … 重复这个过程，直到表1中的全部元组都处理完毕为止。

62 示例 例44．查询每个学生及其选课的详细信息。 SELECT * FROM Student INNER JOIN SC
ON Student.Sno = SC.Sno

63 改进例44 SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM Student JOIN SC ON Student.Sno = SC.Sno

64 示例 例46.查询计算机系学生的修课情况，要求列出学生的名字、所修课的课程号和成绩。 SELECT Sname, Cno, Grade
FROM Student JOIN SC ON Student.Sno = SC.Sno WHERE Sdept = '计算机系'

65 表别名 可以为表提供别名，其格式如下： FROM <源表名> [ AS ] <表别名>
为表指定别名可以简化表的书写。 当为表指定了别名时，在查询语句中的其他地方，所有用到表名的地方都要使用别名，而不能再使用原表名。

66 示例 SELECT Sname, Cname, Grade FROM Student s JOIN SC
例47. 查询信息系修了“计算机文化学”课程的学生信息，要求列出学生姓名、课程名和成绩。 SELECT Sname, Cname, Grade FROM Student s JOIN SC ON s.Sno = SC. Sno JOIN Course c ON c.Cno = SC.Cno WHERE Sdept = '信息系' AND Cname = '计算机文化学'

67 示例 例48．查询所有修了VB课程的学生的修课情况，要求列出学生姓名和所在系。 SELECT Sname, Sdept
FROM Student S JOIN SC ON S.Sno = SC. Sno JOIN Course C ON C.Cno = SC.cno WHERE Cname = 'VB'

68 示例 例49．有分组的多表连接查询。统计每个系的学生考试平均成绩。
SELECT Sdept, AVG(grade) as AverageGrade FROM student S JOIN SC ON S.Sno = SC.Sno GROUP BY Sdept

69 示例 例50．有分组和行选择条件的多表连接查询。统计计算机系每门课程的选课人数、平均成绩、最高成绩和最低成绩。
SELECT Cno, COUNT(*) AS Total, AVG(Grade) as AvgGrade, MAX(Grade) as MaxGrade, MIN(Grade) as MinGrade FROM Student S JOIN SC ON S.Sno = SC.Sno WHERE Sdept = '计算机系' GROUP BY Cno

70 2.自连接 为特殊的内连接 相互连接的表物理上为同一张表。 必须为两个表取别名，使之在逻辑上成为两个表。
FROM 表1 AS T1 --在内存中生成“T1”表 JOIN 表1 AS T2 --在内存中生成“T2”表

71 示例 例51. 查询与刘晨在同一个系学习的学生的姓名和所在的系。 SELECT S2.Sname, S2.Sdept
FROM Student S1 JOIN Student S2 ON S1.Sdept = S2.Sdept WHERE S1.Sname = '刘晨' AND S2.Sname != '刘晨'

72 示例 例52．查询与“操作系统”学分相同的课程的课程名和学分。 SELECT C1.Cname, C1.Credit
FROM Course C1 JOIN Course C2 ON C1.Credit = C2. Credit WHERE C2.Cname = '操作系统'

73 3.外连接 只限制一张表中的数据必须满足连接条件，而另一张表中数据可以不满足连接条件。 ANSI方式的外连接的语法格式为：
FROM 表1 LEFT | RIGHT [OUTER] JOIN 表2 ON <连接条件>

74 示例 例53. 查询学生的修课情况，包括修了课程的学生和没有修课的学生。
SELECT Student.Sno, Sname, Cno, Grade FROM Student LEFT OUTER JOIN SC ON Student.Sno = SC.Sno

75 示例 例54. 查询哪些课程没有人选，列出其课程名。 SELECT Cname FROM Course C LEFT JOIN SC
ON C.Cno = SC.Cno WHERE SC.Cno IS NULL

76 示例 例55. 查询计算机系没有选课的学生，列 出学生姓名和性别。 SELECT Sname,Sdept,Cno,grade
FROM Student S LEFT JOIN SC ON S.Sno = SC.Sno WHERE Sdept = '计算机系' AND SC.Sno IS NULL

77 示例 例56. 统计计算机系每个学生的选课门数，包括没有选课的学生，结果按选课门数递减排序。 SELECT S.Sno AS 学号,
COUNT(SC.Cno) AS 选课门数 FROM Student S LEFT JOIN SC ON S.Sno = SC.Sno WHERE Sdept = '计算机系' GROUP BY S.Sno ORDER BY COUNT(SC.Cno) DESC

78 5.1.4 使用TOP限制结果集行数 可以使用TOP谓词来限制输出的结果行数。 TOP n [ percent ] [WITH TIES ]
TOP n：取查询结果的前n行数据。 TOP n percnet：取查询结果的前n%行。 WITH TIES：包括并列的结果。 TOP谓词写在SELECT单词的后边 2017年3月11日9时56分

79 示例 例57 查询年龄最大的三个学生的姓名、年龄及所在的系。 SELECT TOP 3 Sname, Sage, Sdept
例57 查询年龄最大的三个学生的姓名、年龄及所在的系。 SELECT TOP 3 Sname, Sage, Sdept FROM Student ORDER BY Sage DESC 2017年3月11日9时56分

80 示例 查询年龄最大的三个学生的姓名、年龄及所在的系。包括并列的情况。
SELECT TOP 3 WITH TIES Sname, Sage, Sdept FROM Student ORDER BY Sage DESC 2017年3月11日9时56分

81 示例 例58 查询VB考试成绩最高的前三名的学生 的姓名、所在系和VB考试成绩。
SELECT TOP 3 WITH TIES Sname, Sdept, G rade FROM Student S JOIN SC on S.Sno = SC.Sn o JOIN Course C ON C.Cno = SC.Cno WHERE Cname = 'VB' ORDER BY Grade DESC 2017年3月11日9时56分

82 示例 例59 查询选课人数最少的两门课程（不包括没有人选的课程），列出课程号和选课人数。
例59 查询选课人数最少的两门课程（不包括没有人选的课程），列出课程号和选课人数。 SELECT TOP 2 WITH TIES Cno, COUNT(*) 选课人数 FROM SC GROUP BY Cno ORDER BY COUNT(Cno) ASC 2017年3月11日9时56分

83 5.1.5 子查询 在SQL语言中，一个SELECT－FROM－WHERE语句称为一个查询块。
子查询是一个 SELECT 查询，它嵌套在 SELECT、INSERT、UPDATE、DELETE 语句的 WHERE 或 HAVING 子句内，或其它子查询中。 子查询的 SELECT 查询总是使用圆括号括起来。

84 IN 1. 基于集合的测试 例60. 查询与刘晨在同一个系的学生。 SELECT Sno, Sname, Sdept
FROM Student WHERE Sdept ( SELECT Sdept FROM Student WHERE Sname = '刘晨' ) AND Sname != '刘晨' IN ② ①

85 注意 使用子查询进行基于集合的测试的语句的一般格式为： 列名 [NOT] IN (子查询)

86 注意 使用子查询进行基于集合的测试时，由该子查询返回的结果集中的列的个数、数据类型以及语义必须与表达式中的列的个数、数据类型以及语义相同。当子查询返回结果之后，外层查询将用这个结果作为筛选条件。

87 示例 例61. 查询成绩为大于90分的学生的学号、姓名。 SELECT Sno, Sname FROM Student
WHERE Sno IN ( SELECT Sno FROM SC WHERE Grade > 90 ) ② ①

88 示例 例62．查询选修了“c02”课程的学生，列出姓名和性别。 SELECT Sname, Ssex FROM Student
WHERE Sno IN (SELECT Sno FROM SC WHERE Cno = ‘c02’) 也可以用多表连接实现： FROM Student S JOIN SC ON S.Sno = SC.Sno WHERE Cno ='c02'

89 示例 例63. 查询选修了“VB”课程的学生的学号和姓名。 （1）在Course表中，找出“VB”课程名对应的课程号；
（2）根据得到的“VB”课程号，在SC表中找出选了该课程号的学生的学号； （3）根据得到的学号，在Student表中找出对应的学生的学号和姓名。 SELECT Sno, Sname FROM Student WHERE Sno IN ( SELECT Sno FROM SC WHERE Cno IN ( SELECT Cno FROM Course WHERE Cname = 'VB') )

90 示例 例64. 在选修了VB的这些学生中，统计他们的选课门数和平均成绩。 SELECT Sno 学号, COUNT(*) 选课门数,
AVG(Grade) 平均成绩 FROM SC WHERE Sno IN ( --选VB的学生 SELECT Sno FROM SC JOIN Course C ON C.Cno = SC.Cno WHERE Cname = 'VB') GROUP BY Sno

91 2.进行比较测试 带比较运算符的子查询指父查询与子查询之间用比较运算符连接
当用户能确切知道内层查询返回的是单值时，可用>、<、=、>=、<=、<>运算符。

92 示例 例65. 查询选了“c04”号课程且成绩高于此课程的平均成绩的学生的学号和成绩。 首先计算“c04”号课程的平均成绩：
SELECT AVG(Grade) from SC WHERE Cno = ‘c04’ --79 然后，查找“c04”号课程所有的考试成绩中，高于79的学生： SELECT Sno , Grade FROM SC WHERE Cno = ’c04’ AND Grade > 79 将两个查询语句合起来即为满足我们要求的查询语句： SELECT Sno , Grade FROM SC WHERE Cno = ’c04’ AND Grade > ( SELECT AVG(Grade) FROM SC WHERE Cno = ’c04’)

93 不相关子查询 用子查询进行基于集合测试和比较测试时，都是先执行子查询，然后再在子查询的结果基础之上执行外层查询。
子查询都只执行一次，子查询的查询条件不依赖于外层查询， 我们将这样的子查询称为不相关子查询或嵌套子查询（nested subquery）

94 示例 例66. 查询计算机系年龄最大的学生的姓名和年龄。 SELECT Sname, Sage FROM Student
WHERE Sdept = '计算机系' AND Sage = ( SELECT MAX(Sage) FROM Student WHERE Sdept = '计算机系')

95 示例 例67. 查询考试平均成绩高于全体学生的总平均成绩的学生的学号和平均成绩。 SELECT Sno, AVG(Grade) 平均成绩
FROM SC GROUP BY Sno HAVING AVG(Grade) > ( SELECT AVG(Grade) FROM SC )

96 示例 例68. 查询VB考试成绩高于VB平均成绩的学生的姓名、所在系和VB成绩。 SELECT Sname, Sdept, Grade
FROM Student S JOIN SC ON S.Sno = SC.Sno JOIN Course C ON C.Cno = SC.Cno WHERE Cname = 'VB' AND Grade > ( SELECT AVG(Grade) FROM SC WHERE Cname = 'VB')

97 3. 存在性测试 通常使用EXISTS谓词，其形式为： WHERE [NOT] EXISTS（子查询）

98 例69.查询选修了c01课程的学生姓名。 SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE Sno = Student.Sno AND Cno = 'c01')

99 注意 处理过程为：先外后内；由外层的值决定内层的结果；内层执行次数由外层结果数决定。
由于EXISTS的子查询只能返回真或假值，因此在这里给出列名无意义。所以在有EXISTS的子查询中，其目标列表达式通常都用*。

100 上句的处理过程 1.找外层表Student表的第一行，根据其Sno值处理内层查询 2.由外层的值与内层的结果比较，由此决定外层条件的真、假
…行。

101 示例 例71.查询没有选修c01号课程的学生姓名和所在系。 SELECT Sname, Sdept FROM Student
WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno = Student.Sno AND Cno = 'c01') 或： WHERE Sno NOT IN ( SELECT Sno FROM SC WHERE Cno = 'c01' ) 注意：不能用连接查询和在子查询中否定的形式实现。

102 示例 逆向思维 例71． 查询计算机系没有选修“VB”课程的学生的姓名和性别。
SELECT Sname, Ssex FROM Student WHERE Sno NOT IN ( --查询选了VB的学生 SELECT Sno FROM SC JOIN Course ON SC.Cno = Course.Cno WHERE Cname = 'VB') AND Sdept = '计算机系' 逆向思维

103 5.2 数据更改功能 5.2.1 插入数据 5.2.2 更新数据 5.2.3 删除数据

104 5.2.1 插入数据 INSERT [INTO] <表名> [(<列名表>)]
VALUES （值表） 功能：新增一个符合表结构的数据行，将值表数据按表中列定义顺序[或列名表顺序]赋给对应列名。

105 注意 值列表中的值与列名表中的列按位置顺序对应，它们的数据类型必须一致。
如果<表名>后边没有指明列名，则新插入记录的值的顺序必须与表中列的定义顺序一致，且每一个列均有值（可以为空）。

106 示例 例72．将新生记录（9521104，陈冬，男，18岁，信息系）插入到Student表中。
INSERT INTO Student VALUES (' ', '陈冬', '男', 18, '信息系') 例73．在SC表中插入一新记录，学号为“ ”，选的课程号为“c01”，成绩暂缺。 INSERT INTO SC(Sno, Cno) VALUES(' ', 'c01’) 实际插入的值为：(' ', 'c01'，NULL)

107 5.2.2 更新数据 用UPDATE语句实现。 格式： UPDATE <表名>
SET <列名＝表达式> [,… n] [WHERE <更新条件>]

108 1.无条件更新 例74. 将所有学生的年龄加1。 UPDATE Student SET Sage = Sage + 1

109 （1）基于本表的有条件更新 例75. 将‘9512101’学生的年龄改为21岁。 UPDATE Student SET Sage = 21
WHERE Sno = ' '

110 （2）基于其他表条件的更新 例76：将计算机系全体学生的成绩加5分。 （1）用子查询实现
UPDATE SC SET Grade = Grade + 5 WHERE Sno IN (SELECT Sno FROM Student WHERE Sdept = '计算机系' ) （2）用多表连接实现 FROM SC JOIN Student ON SC.Sno = Student.Sno WHERE Sdept = '计算机系'

111 示例 例77. 将学分最低的课程的学分加2分。 UPDATE Course SET Credit = Credit + 2
WHERE Credit = ( SELECT MIN(Credit) FROM Course )

112 示例 例78. 数学系学生的VB考试成绩增加10分。 用子查询实现。 UPDATE SC SET Grade = Grade + 10
WHERE Cno IN ( SELECT Cno FROM Course WHERE Cname = 'VB') AND Sno IN ( SELECT Sno FROM Student WHERE Sdept ='数学系 ') 用多表连接实现。 FROM SC JOIN Course C ON C.Cno = SC.Cno JOIN Student S ON S.Sno = SC.Sno WHERE Cname = 'VB' AND Sdept = '数学系'

113 5.2.3 删除数据 用DELETE语句实现 格式： DELETE FROM <表名> [WHERE <删除条件>]

114 1. 无条件删除 例79. 删除所有学生的选课记录。 DELETE FROM SC

115 2. 有条件删除 （1）基于本表条件的删除。 例80．删除所有不及格学生的修课记录。 DELETE FROM SC
WHERE Grade < 60

116 （2）基于其他表条件的删除 例81．删除计算机系不及格学生的修课记录。 DELETE FROM SC
WHERE Grade < 60 AND Sno IN ( SELECT Sno FROM Student WHERE Sdept = '计算机系' )

117 示例 例81. 删除信息系考试成绩不及格学生的不及格课程的选课记录。 用多表连接形式实现。 用子查询形式实现。 DELETE FROM SC
FROM Student S JOIN SC ON S.Sno = SC.sno WHERE Sdept = '信息系' AND Grade < 60 用子查询形式实现。 DELETE FROM SC WHERE Sno IN ( SELECT Sno FROM Student WHERE Sdept = '信息系') AND Grade < 60