数据库原理-第3章-的关系数据库标准语言SQL2-课件

1、第三章 关系数据库标准语言SQL 2西南民族大学 计算机学院7/19/20221第三章 关系数据库标准语言SQL第三章 关系数据库标准语言SQL3.1 SQL概述3.2 数据定义3.3 数据查询3.4 数据更新3.5 视图3.6 小结7/19/20222第三章 关系数据库标准语言SQL3.3 数据查询 3.3.1 概述3.3.2 单表查询简单复习3.3.3 连接查询3.3.4 嵌套查询3.3.5 集合查询3.3.6 小结 BACK7/19/20223第三章 关系数据库标准语言SQL单表查询类型一、选择表中的若干列二、选择表中的若干元组三、对查询结果排序四、使用聚集函数五、对查询结果分组 7/1

2、9/20224第三章 关系数据库标准语言SQL3.3.3 连接查询 同时涉及多个表的查询称为连接查询用来连接两个表的条件称为连接条件或连接谓词 一般格式：. . 比较运算符：=、=、=、!=. BETWEEN . AND .7/19/20225第三章 关系数据库标准语言SQL连接查询 （续）连接字段连接谓词中的列名称为连接字段连接条件中的各连接字段类型必须是可比的，但不必是相同的7/19/20226第三章 关系数据库标准语言SQL连接操作的执行过程嵌套循环算法(NESTED-LOOP)首先在表1中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接件的元组，找到后就将表1中的第一个元组与该元

3、组拼接起来，形成结果表中一个元组。表2全部查找完后，再找表1中第二个元组，然后再从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组。重复上述操作，直到表1中的全部元组都处理完毕 7/19/20227第三章 关系数据库标准语言SQL连接查询 （续）SQL中连接查询的主要类型 一、广义笛卡尔积 二、等值连接和非等值连接查询 三、自身连接查询 四、外连接查询 五、复合条件连接查询 六、多表连接BACK7/19/20228第三章 关系数据库标准语言SQL一、广义笛卡尔积 不带连接谓词的连接 很少使用例： SELECT Student.* ,

4、 SC.* FROM Student, SCBACK7/19/20229第三章 关系数据库标准语言SQL二、等值与非等值连接查询 等值连接、自然连接、非等值连接例32 查询每个学生及其选修课程的情况。SELECT Student.*，SC.*FROM Student，SCWHERE Student.Sno = SC.Sno；7/19/202210第三章 关系数据库标准语言SQL等值连接连接运算符为 = 的连接操作 . = .任何子句中引用表1和表2中同名属性时，都必须加表名前缀。引用唯一属性名时可以加也可以省略表名前缀。 7/19/202211第三章 关系数据库标准语言SQL等值连接上例中：若

5、假设Student表、SC表分别有下列数据： Student表 Sno SnameSsexSageSdept95001 李勇 男 20 CS95002 刘晨 女 19 IS95003 王敏 女 18 MA95004 张立 男 19 IS7/19/202212第三章 关系数据库标准语言SQL等值连接SC表 Sno CnoGrade95001 1 9295001 2 85950019500295002 3 2 3 88 90 807/19/202213第三章 关系数据库标准语言SQL等值连接结果表： Student.Sno Sname Ssex Sage Sdept SC.Sno Cno Grad

6、e 95001 李勇 男 20 CS95001 1 92 95001 李勇 男 20CS95001 2 85 95001 李勇 男 20CS95001 3 88 95002 刘晨 女 19IS 95002 2 90 95002 刘晨 女 19IS95002 3 80 7/19/202214第三章 关系数据库标准语言SQL自然连接自然连接是等值连接的一种特殊情况，把目标列中重复的属性列去掉。例33 对例32用自然连接完成。 SELECT Student.Sno，Sname，Ssex，Sage， Sdept，Cno，Grade FROM Student，SC WHERE Student.Sno =

7、 SC.Sno；7/19/202215第三章 关系数据库标准语言SQL非等值连接查询连接运算符 不是 = 的连接操作 .比较运算符：、=、=、!=. BETWEEN . AND . BACK7/19/202216第三章 关系数据库标准语言SQL三、自身连接 一个表与其自己进行连接，称为表的自身连接需要给表起别名以示区别由于所有属性名都是同名属性，因此必须使用别名前缀7/19/202217第三章 关系数据库标准语言SQL自身连接（续）例34 查询每一门课的间接先修课（即先修课的先修课） SELECT FIRST.Cno，SECOND.Cpno FROM Course FIRST，Course S

8、ECOND WHERE FIRST.Cpno = SECOND.Cno； 7/19/202218第三章 关系数据库标准语言SQL自身连接（续） FIRST表（Course表） Cno CnameCpnoCcredit 1数据库 5 4 2数学 2 3信息系统 1 4 4操作系统 6 3 5数据结构 7 4 6数据处理 2 7PASCAL语言 6 47/19/202219第三章 关系数据库标准语言SQL自身连接（续）SECOND表（Course表） Cno CnameCpnoCcredit 1数据库 5 4 2数学 2 3信息系统 1 4 4操作系统 6 3 5数据结构 7 4 6数据处理 2

9、7PASCAL语言 6 47/19/202220第三章 关系数据库标准语言SQL自身连接（续）查询结果 1 7 3 5 5 6 cnocpno BACK7/19/202221第三章 关系数据库标准语言SQL四、外连接（Outer Join） 外连接与普通连接的区别普通连接操作只输出满足连接条件的元组外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出7/19/202222第三章 关系数据库标准语言SQL外连接（续） 例 33 查询每个学生及其选修课程的情况（包括没有选修课程的学生） 用外连接操作SELECT Student.Sno，Sname，Ssex， Sage，Sdept，

10、Cno，Grade FROM Student LEFT JOIN SC ON Student.Sno = SC.Sno； 7/19/202223第三章 关系数据库标准语言SQL外连接（续） 结果： Student.Sno Sname Ssex Sage Sdept Cno Grade 95001 李勇 男 20 CS 1 92 95001 李勇 男 20 CS 2 85 95001 李勇 男 20 CS 3 88 95002 刘晨 女 19 IS 2 90 95002 刘晨 女 19 IS 3 80 95003 王敏 女 18 MA NULL NULL 95004 张立 男 19 IS NUL

11、L NULLBACK7/19/202224第三章 关系数据库标准语言SQL五、复合条件连接WHERE子句中含多个连接条件时，称为复合条件连接例35查询选修2号课程且成绩在90分以上的所有学生的 学号、姓名SELECT Student.Sno, student.SnameFROM Student, SCWHERE Student.Sno = SC.Sno AND /* 连接谓词*/ SC.Cno= 2 AND /* 其他限定条件 */ SC.Grade 90； /* 其他限定条件 */BACK7/19/202225第三章 关系数据库标准语言SQL六、多表连接例36 查询每个学生的学号、姓名、选修

12、的课程名及成绩。 SELECT Student.Sno，Sname，Cname，Grade FROM Student，SC，Course WHERE Student.Sno = SC.Sno and SC.Cno = Course.Cno；结果：Student.Sno Sname Cname Grade 95001 李勇 数据库 92 95001 李勇 数学 85 95001 李勇 信息系统 88 95002 刘晨 数学 90 95002 刘晨 信息系统 80 BACK7/19/202226第三章 关系数据库标准语言SQL3.3.4 嵌套查询嵌套查询概述嵌套查询分类嵌套查询求解方法引出子查询的

13、谓词 BACK7/19/202227第三章 关系数据库标准语言SQL嵌套查询概述一个SELECT-FROM-WHERE语句称为一个查询块将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询 7/19/202228第三章 关系数据库标准语言SQL SELECT Sname外层查询/父查询 FROM Student WHERE Sno IN （SELECT Sno 内层查询/子查询 FROM SC WHERE Cno= 2 ）；7/19/202229第三章 关系数据库标准语言SQL子查询的限制不能使用ORDER BY子句层层嵌套方式反映了 SQL语言的结构化有

14、些嵌套查询可以用连接运算替代7/19/202230第三章 关系数据库标准语言SQL嵌套查询分类不相关子查询子查询的查询条件不依赖于父查询相关子查询子查询的查询条件依赖于父查询7/19/202231第三章 关系数据库标准语言SQL嵌套查询求解方法不相关子查询是由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。7/19/202232第三章 关系数据库标准语言SQL嵌套查询求解方法（续）相关子查询首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表；然后再取外层表的下一个元组；重复这一

15、过程，直至外层表全部检查完为止。7/19/202233第三章 关系数据库标准语言SQL引出子查询的谓词带有IN谓词的子查询带有比较运算符的子查询带有ANY或ALL谓词的子查询带有EXISTS谓词的子查询7/19/202234第三章 关系数据库标准语言SQL一、带有IN谓词的子查询例37 查询与“刘晨”在同一个系学习的学生。 此查询要求可以分步来完成 确定“刘晨”所在系名 SELECT Sdept FROM Student WHERE Sname= 刘晨 ；结果为： Sdept IS7/19/202235第三章 关系数据库标准语言SQL带有IN谓词的子查询（续） 查找所有在IS系学习的学生。 S

16、ELECT Sno，Sname，Sdept FROM Student WHERE Sdept= IS ； 结果为：Sno Sname Sdept95001 刘晨 IS95004 张立 IS7/19/202236第三章 关系数据库标准语言SQL 构造嵌套查询将第一步查询嵌入到第二步查询的条件中 SELECT Sno，Sname，Sdept FROM Student WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname= 刘晨 )；此查询为不相关子查询。DBMS求解该查询时也是分步去做的。7/19/202237第三章 关系数据库标准语言SQL带

17、有IN谓词的子查询（续） 用自身连接完成本查询要求： SELECT S1.Sno，S1.Sname，S1.Sdept FROM Student S1，Student S2 WHERE S1.Sdept = S2.Sdept AND S2.Sname = 刘晨；7/19/202238第三章 关系数据库标准语言SQL带有IN谓词的子查询（续）父查询和子查询中的表均可以定义别名 SELECT Sno，Sname，Sdept FROM Student S1 WHERE S1.Sdept IN (SELECT Sdept FROM Student S2 WHERE S2.Sname= 刘晨 )；7/19

18、/202239第三章 关系数据库标准语言SQL带有IN谓词的子查询（续）例38查询选修了课程名为“信息系统”的学生学号和姓名 SELECT Sno，Sname 最后在Student关系中 FROM Student 取出Sno和Sname WHERE Sno IN (SELECT Sno 然后在SC关系中找出选 FROM SC 修了3号课程的学生学号 WHERE Cno IN (SELECT Cno 首先在Course关系中找出“信 FROM Course 息系统”的课程号，结果为3号 WHERE Cname= 信息系统);7/19/202240第三章 关系数据库标准语言SQL带有IN谓词的子查

19、询（续）结果： SnoSname 95001 李勇 95002 刘晨7/19/202241第三章 关系数据库标准语言SQL带有IN谓词的子查询（续）用连接查询实现 SELECT Sno，Sname FROM Student，SC，Course WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno AND Course.Cname=信息系统；BACK7/19/202242第三章 关系数据库标准语言SQL二、带有比较运算符的子查询 当能确切知道内层查询返回单值时，可用比较运算符（，=，=，!=或）。与ANY或ALL谓词配合使用7/19/202243第

20、三章 关系数据库标准语言SQL带有比较运算符的子查询（续）例：假设一个学生只可能在一个系学习，并且必须属于一个系，则在例37可以用 = 代替IN ： SELECT Sno，Sname，Sdept FROM Student WHERE Sdept = （ SELECT Sdept FROM Student WHERE Sname= 刘晨 ）；7/19/202244第三章 关系数据库标准语言SQL带有比较运算符的子查询（续） 子查询一定要跟在比较符之后 错误的例子： SELECT Sno，Sname，Sdept FROM Student WHERE ( SELECT Sdept FROM Stud

21、ent WHERE Sname= 刘晨 ) = Sdept；BACK7/19/202245第三章 关系数据库标准语言SQL三、带有ANY或ALL谓词的子查询谓词语义ANY：任意一个值ALL：所有值7/19/202246第三章 关系数据库标准语言SQL带有ANY或ALL谓词的子查询（续）需要配合使用比较运算符 ANY大于子查询结果中的某个值 ALL大于子查询结果中的所有值 ANY小于子查询结果中的某个值 = ANY大于等于子查询结果中的某个值 = ALL大于等于子查询结果中的所有值= ANY小于等于子查询结果中的某个值 = ALL小于等于子查询结果中的所有值= ANY等于子查询结果中的某个值 =

22、ALL等于子查询结果中的所有值（通常没有实际意义）!=（或）ANY不等于子查询结果中的某个值!=（或）ALL不等于子查询结果中的任何一个值7/19/202247第三章 关系数据库标准语言SQL带有ANY或ALL谓词的子查询（续）例39 查询其他系中比信息系任意一个(其中某一个)学生年龄小的学生姓名和年龄 SELECT Sname，Sage FROM Student WHERE Sage ANY (SELECT Sage FROM Student WHERE Sdept= IS ) AND Sdept IS ; /* 注意这是父查询块中的条件 */7/19/202248第三章 关系数据库标准语言

23、SQL带有ANY或ALL谓词的子查询（续）结果： Sname Sage 王敏 18执行过程： 1.DBMS执行此查询时，首先处理子查询，找出IS系中所有学生的年龄，构成一个集合(19，18) 2. 处理父查询，找所有不是IS系且年龄小于19 或 18的学生7/19/202249第三章 关系数据库标准语言SQL带有ANY或ALL谓词的子查询（续）ANY和ALL谓词有时可以用聚集函数实现ANY与ALL与聚集函数的对应关系 = 或!= =ANY IN - MAXMIN= MINALL - NOT IN MINMAX= MAX7/19/202250第三章 关系数据库标准语言SQL带有ANY或ALL谓词

24、的子查询（续）用聚集函数实现子查询通常比直接用ANY或ALL查询效率要高，因为前者通常能够减少比较次数7/19/202251第三章 关系数据库标准语言SQL带有ANY或ALL谓词的子查询（续）例39：用聚集函数实现例39 SELECT Sname，Sage FROM Student WHERE Sage (SELECT MAX(Sage) FROM Student WHERE Sdept= IS ) AND Sdept IS ;7/19/202252第三章 关系数据库标准语言SQL带有ANY或ALL谓词的子查询（续）例40 查询其他系中比信息系所有学生年龄都小的学生姓名及年龄。方法一：用ALL

25、谓词 SELECT Sname，Sage FROM Student WHERE Sage ALL (SELECT Sage FROM Student WHERE Sdept= IS ) AND Sdept IS ;查询结果为空表。7/19/202253第三章 关系数据库标准语言SQL带有ANY或ALL谓词的子查询（续） 方法二：用聚集函数 SELECT Sname，Sage FROM Student WHERE Sage (SELECT MIN(Sage) FROM Student WHERE Sdept= IS ) AND Sdept IS ;BACK7/19/202254第三章 关系数据库

26、标准语言SQL四、带有EXISTS谓词的子查询1. EXISTS谓词2. NOT EXISTS谓词3. 不同形式的查询间的替换4. 相关子查询的效率*5. 用EXISTS/NOT EXISTS实现全称量词7/19/202255第三章 关系数据库标准语言SQL带有EXISTS谓词的子查询(续）1. EXISTS谓词存在量词 带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。若内层查询结果非空，则返回真值若内层查询结果为空，则返回假值由EXISTS引出的子查询，其目标列表达式通常都用* ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义2

27、. NOT EXISTS谓词7/19/202256第三章 关系数据库标准语言SQL带有EXISTS谓词的子查询(续）例41 查询所有选修了1号课程的学生姓名。用嵌套查询 SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC /*相关子查询*/ WHERE Sno=Student.Sno AND Cno= 1 )； 7/19/202257第三章 关系数据库标准语言SQL带有EXISTS谓词的子查询(续）用连接运算SELECT SnameFROM Student, SCWHERE Student.Sno=SC.Sno AND SC.Cno

28、= 1;7/19/202258第三章 关系数据库标准语言SQL带有EXISTS谓词的子查询(续）例42 查询没有选修1号课程的学生姓名。 SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno = Student.Sno AND Cno=1)；注意：此例用连接运算难于实现 7/19/202259第三章 关系数据库标准语言SQL带有EXISTS谓词的子查询(续）3. 不同形式的查询间的替换一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换所有带IN谓词、比较运算符、ANY和ALL

29、谓词的子查询都能用带EXISTS谓词的子查询等价替换。 7/19/202260第三章 关系数据库标准语言SQL带有EXISTS谓词的子查询(续）例：例37查询与“刘晨”在同一个系学习的学生。可以用带EXISTS谓词的子查询替换： SELECT Sno，Sname，Sdept FROM Student S1 WHERE EXISTS （SELECT * FROM Student S2 WHERE S2.Sdept = S1.Sdept AND S2.Sname = 刘晨 ）；SELECT Sno，Sname，Sdept FROM Student WHERE Sdept IN (SELECT Sd

30、ept FROM Student WHERE Sname= 刘晨 )；7/19/202261第三章 关系数据库标准语言SQL带有EXISTS谓词的子查询(续）* 5.用EXISTS/NOT EXISTS实现全称量词(难点)SQL语言中没有全称量词 （For all）可以把带有全称量词的谓词转换为等价的带有存在量词的谓词： 7/19/202262第三章 关系数据库标准语言SQL例44 查询至少选修了学生95002选修的全部课程的学生号码。解题思路：用逻辑蕴函表达：查询学号为x的学生，对所有的课程y，只要95002学生选修了课程y，则x也选修了y。形式化表示：用P表示谓词 “学生95002选修了课

31、程y”用q表示谓词 “学生x选修了课程y”则上述查询为: (y) p q 7/19/202263第三章 关系数据库标准语言SQL等价变换： (y)p q (y (p q ) (y ( p q) y(pq)变换后语义：不存在这样的课程y，学生95002选修了y，而学生x没有选。7/19/202264第三章 关系数据库标准语言SQL用NOT EXISTS谓词表示： SELECT DISTINCT Sno FROM SC SCX WHERE NOT EXISTS (SELECT * FROM SC SCY WHERE SCY.Sno = 95002 AND NOT EXISTS (SELECT *

32、FROM SC SCZ WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno)；BACK7/19/202265第三章 关系数据库标准语言SQL3.3.5 集合查询一般商用数据库支持的集合操作种类并操作(UNION)交操作(INTERSECT)/SQL SERVER用in差操作(MINUS|EXCEPT)/SQL SERVER用not in形式 UNION （INTERSECT、 MINUS|EXCEPT） 参加操作的各结果表的列数必须相同；对应项的数据类型也必须相同7/19/202266第三章 关系数据库标准语言SQL1. 并操作例45 查询计算机科学系的学生及年

33、龄不大于19岁的学生。方法一： SELECT * FROM Student WHERE Sdept= CS UNION SELECT * FROM Student WHERE Sage=19；7/19/202267第三章 关系数据库标准语言SQL并操作（续）方法二： SELECT DISTINCT * FROM Student WHERE Sdept= CS OR Sage=19；7/19/202268第三章 关系数据库标准语言SQL并操作（续）例46 查询选修了课程1或者选修了课程2的学生。方法一： SELECT Sno FROM SC WHERE Cno= 1 UNION SELECT S

34、no FROM SC WHERE Cno= 2 ；7/19/202269第三章 关系数据库标准语言SQL并操作（续）方法二： SELECT DISTINCT Sno FROM SC WHERE Cno= 1 OR Cno= 2 ；7/19/202270第三章 关系数据库标准语言SQL并操作（续）例47 设数据库中有一教师表Teacher(Tno, Tname,.)。查询学校中所有师生的姓名。 SELECT Sname FROM Student UNION SELECT Tname FROM Teacher;7/19/202271第三章 关系数据库标准语言SQL2 交操作集合交操作也可用其他方法

35、间接实现。例48 查询计算机科学系的学生与年龄不大于19岁的学生的交集方法一：本例实际上就是查询计算机科学系中年龄不大于19岁的学生 SELECT * FROM Student WHERE Sdept= CS AND Sage=19；7/19/202272第三章 关系数据库标准语言SQL 交操作方法二、 SELECT * FROM Student WHERE Sdept= CS INTERSECT SELECT * FROM Student whERE Sage19；7/19/202277第三章 关系数据库标准语言SQL差操作方法二、 SELECT * FROM Student WHERE S

36、dept= CS EXCEPT SELECT * FROM Student WHERE Sage=19；7/19/202278第三章 关系数据库标准语言SQL差操作（续）例52 查询学生姓名与教师姓名的差集 分析：本例实际上是查询学校中未与教师同名的学生姓名 SELECT DISTINCT Sname FROM StudentEXCEPT SELECT Tname FROM Teacher;BACK7/19/202279第三章 关系数据库标准语言SQL3.3.6小结SELECT语句的一般格式：SELECT ALL|DISTINCT 别名 ， 别名 FROM 别名 ， 别名 WHERE GROU

37、P BY ， .HAVING ORDER BY ASC|DESC ， ASC|DESC ；7/19/202280第三章 关系数据库标准语言SQL目标列表达式目标列表达式格式(1) . *(2) .，. ：由属性列、作用于属性列的聚集函数和常量的任意算术运算（+，-，*，/）组成的运算公式。7/19/202281第三章 关系数据库标准语言SQL聚集函数格式 COUNT SUM AVG (DISTINCT|ALL ) MAX MIN COUNT (DISTINCT|ALL *)7/19/202282第三章 关系数据库标准语言SQL条件表达式格式（1） ANY|ALL (SELECT语句)7/19/

38、202283第三章 关系数据库标准语言SQL条件表达式格式 （2） NOT BETWEEN AND (SELECT (SELECT 语句) 语句)7/19/202284第三章 关系数据库标准语言SQL条件表达式格式 （3） (， ) NOT IN (SELECT语句)7/19/202285第三章 关系数据库标准语言SQL条件表达式格式 (4) NOT LIKE (5) IS NOT NULL (6) NOT EXISTS (SELECT语句)7/19/202286第三章 关系数据库标准语言SQL条件表达式格式 （7） AND AND OR ORBACK7/19/202287第三章 关系数据库标

39、准语言SQL3.4 数 据 更 新 3.4.1 插入数据3.4.2 修改数据3.4.3 删除数据 BACK7/19/202288第三章 关系数据库标准语言SQL3.4.1 插入数据两种插入数据方式 插入单个元组 插入子查询结果7/19/202289第三章 关系数据库标准语言SQL1. 插入单个元组语句格式INSERTINTO (，)VALUES ( ， )功能 将新元组插入指定表中。7/19/202290第三章 关系数据库标准语言SQL插入单个元组（续）例1 将一个新学生记录（学号：95020；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）插入到Student表中。 INSERT INTO

40、 Student VALUES (95020，陈冬，男，IS，18)；7/19/202291第三章 关系数据库标准语言SQL插入单个元组（续）例2 插入一条选课记录( 95020，1 )。 INSERT INTO SC(Sno，Cno) VALUES ( 95020 ， 1 )； 新插入的记录在Grade列上取空值7/19/202292第三章 关系数据库标准语言SQL插入单个元组（续） INTO子句指定要插入数据的表名及属性列属性列的顺序可与表定义中的顺序不一致没有指定属性列：表示要插入的是一条完整的元组，且属性列属性与表定义中的顺序一致指定部分属性列：插入的元组在其余属性列上取空值 VALU

41、ES子句 提供的值必须与INTO子句匹配值的个数值的类型7/19/202293第三章 关系数据库标准语言SQL2. 插入子查询结果语句格式 INSERT INTO ( ， ) 子查询；功能 将子查询结果插入指定表中7/19/202294第三章 关系数据库标准语言SQL插入子查询结果（续）例3 对每一个系，求学生的平均年龄，并把结果存入数据库。第一步：建表 CREATE TABLE Deptage (Sdept CHAR(15) /* 系名*/ Avgage SMALLINT)； /*学生平均年龄*/ 7/19/202295第三章 关系数据库标准语言SQL插入子查询结果（续）第二步：插入数据 I

42、NSERT INTO Deptage(Sdept，Avgage) SELECT Sdept，AVG(Sage) FROM Student GROUP BY Sdept；7/19/202296第三章 关系数据库标准语言SQL插入子查询结果（续）DBMS在执行插入语句时会检查所插元组是否破坏表上已定义的完整性规则实体完整性参照完整性用户定义的完整性对于有NOT NULL约束的属性列是否提供了非空值对于有UNIQUE约束的属性列是否提供了非重复值对于有值域约束的属性列所提供的属性值是否在值域范围内7/19/202297第三章 关系数据库标准语言SQL3.4.2 修改数据语句格式 UPDATE SET

43、 =，= WHERE ；功能 修改指定表中满足WHERE子句条件的元组7/19/202298第三章 关系数据库标准语言SQL修改数据（续）三种修改方式修改某一个元组的值修改多个元组的值带子查询的修改语句7/19/202299第三章 关系数据库标准语言SQL1. 修改某一个元组的值例4 将学生95001的年龄改为22岁。 UPDATE Student SET Sage=22 WHERE Sno= 95001 ； 7/19/2022100第三章 关系数据库标准语言SQL2. 修改多个元组的值例5 将所有学生的年龄增加1岁。 UPDATE Student SET Sage= Sage+1；7/19/

44、2022101第三章 关系数据库标准语言SQL修改多个元组的值(续)例6 将信息系所有学生的年龄增加1岁。 UPDATE Student SET Sage= Sage+1 WHERE Sdept= IS ；7/19/2022102第三章 关系数据库标准语言SQL3. 带子查询的修改语句例7 将计算机科学系全体学生的成绩置零。 UPDATE SC SET Grade=0 WHERE CS= (SELETE Sdept FROM Student WHERE Student.Sno = SC.Sno)；7/19/2022103第三章 关系数据库标准语言SQL修改数据（续）SET子句 指定修改方式 要

45、修改的列 修改后取值WHERE子句指定要修改的元组缺省表示要修改表中的所有元组7/19/2022104第三章 关系数据库标准语言SQL修改数据（续）DBMS在执行修改语句时会检查修改操作是否破坏表上已定义的完整性规则实体完整性用户定义的完整性 NOT NULL约束 UNIQUE约束 值域约束参照完整性7/19/2022105第三章 关系数据库标准语言SQL3.4.3 删除数据格式 DELETE FROM WHERE ；功能删除指定表中满足WHERE子句条件的元组WHERE子句指定要删除的元组缺省表示要修改表中的所有元组7/19/2022106第三章 关系数据库标准语言SQL删除数据（续）三种删

46、除方式删除某一个元组的值删除多个元组的值带子查询的删除语句7/19/2022107第三章 关系数据库标准语言SQL1. 删除某一个元组的值例8 删除学号为95019的学生记录。 DELETE FROM Student WHERE Sno=95019；7/19/2022108第三章 关系数据库标准语言SQL2. 删除多个元组的值例9 删除2号课程的所有选课记录。 DELETE FROM SC WHERE Cno=2；例10 删除所有的学生选课记录。 DELETE FROM SC；7/19/2022109第三章 关系数据库标准语言SQL3. 带子查询的删除语句例11 删除计算机科学系所有学生的选课

47、记录。 DELETE FROM SC WHERE CS= (SELETE Sdept FROM Student WHERE Student.Sno=SC.Sno)；7/19/2022110第三章 关系数据库标准语言SQL删除数据(续)DBMS在执行插入语句时会检查所插元组是否破坏表上已定义的完整性规则参照完整性不允许删除级联删除7/19/2022111第三章 关系数据库标准语言SQL更新数据与数据一致性DBMS在执行插入、删除、更新语句时必须保证数据库一致性必须有事务的概念和原子性完整性检查和保证BACK7/19/2022112第三章 关系数据库标准语言SQL3.5 视 图视图的特点虚表，是从

48、一个或几个基本表（或视图）导出的表只存放视图的定义，不会出现数据冗余基表中的数据发生变化，从视图中查询出的数据也随之改变7/19/2022113第三章 关系数据库标准语言SQL3.5 视 图基于视图的操作 查询 删除 受限更新定义基于该视图的新视图7/19/2022114第三章 关系数据库标准语言SQL3.5 视 图3.5.1 定义视图3.5.2 查询视图3.5.3 更新视图3.5.4 视图的作用7/19/2022115第三章 关系数据库标准语言SQL3.5.1 定义视图1. 建立视图语句格式 CREATE VIEW ( ，) AS WITH CHECK OPTION；7/19/2022116

49、第三章 关系数据库标准语言SQL 建立视图（续）DBMS执行CREATE VIEW语句时只是把视图的定义存入数据字典，并不执行其中的SELECT语句。在对视图查询时，按视图的定义从基本表中将数据查出。7/19/2022117第三章 关系数据库标准语言SQL组成视图的属性列名两种选择：全部省略或全部指定全部省略: 由子查询中SELECT目标列中的诸字段组成全部指定:(1) 某个目标列是集函数或列表达式(2) 多表连接时选出了几个同名列作为视图的字段(3)需要在视图中为某个列启用新的更合适的名字7/19/2022118第三章 关系数据库标准语言SQL行列子集视图 例1 建立信息系学生的视图。 CR

50、EATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student WHERE Sdept= IS；从单个基本表导出只是去掉了基本表的某些行和某些列7/19/2022119第三章 关系数据库标准语言SQL 建立视图（续）WITH CHECK OPTION透过视图进行增删改操作时，不得破坏视图定义中的谓词条件（即子查询中的条件表达式）7/19/2022120第三章 关系数据库标准语言SQL WITH CHECK OPTION的视图例2 建立信息系学生的视图，并要求透过该视图进行的更新操作只涉及信息系学生。 CREATE VIEW IS_Stude

51、nt AS SELECT Sno，Sname，Sage FROM Student WHERE Sdept= IS WITH CHECK OPTION；7/19/2022121第三章 关系数据库标准语言SQL对IS_Student视图的更新操作修改操作：DBMS自动加上Sdept= IS的条件删除操作：DBMS自动加上Sdept= IS的条件插入操作：DBMS自动检查Sdept属性值是否为IS 如果不是，则拒绝该插入操作如果没有提供Sdept属性值，则自动定义Sdept为IS7/19/2022122第三章 关系数据库标准语言SQL基于多个基表的视图例4 建立信息系选修了1号课程的学生视图。 CR

52、EATE VIEW IS_S1(Sno，Sname，Grade) AS SELECT Student.Sno，Sname，Grade FROM Student，SC WHERE Sdept= IS AND Student.Sno=SC.Sno AND SC.Cno= 1；7/19/2022123第三章 关系数据库标准语言SQL基于视图的视图例5 建立信息系选修了1号课程且成绩在90分以上的学生的视图。 CREATE VIEW IS_S2 AS SELECT Sno，Sname，Grade FROM IS_S1 WHERE Grade=90；7/19/2022124第三章 关系数据库标准语言SQ

53、L带表达式的视图例6 定义一个反映学生出生年份的视图。 CREATE VIEW BT_S(Sno，Sname，Sbirth) AS SELECT Sno，Sname，2006-Sage FROM Student设置一些派生属性列, 也称为虚拟列Sbirth 带表达式的视图必须明确定义组成视图的各个属性列名7/19/2022125第三章 关系数据库标准语言SQL 建立分组视图例7 将学生的学号及他的平均成绩定义为一个视图 假设SC表中“成绩”列Grade为数字型 CREAT VIEW S_G(Sno，Gavg) AS SELECT Sno，AVG(Grade) FROM SC GROUP BY

54、Sno；7/19/2022126第三章 关系数据库标准语言SQL 建立视图（续）一类不易扩充的视图以 SELECT * 方式创建的视图可扩充性差，应尽可能避免7/19/2022127第三章 关系数据库标准语言SQL 建立视图（续）例8将Student表中所有女生记录定义为一个视图 CREATE VIEW F_Student1(stdnum，name，sex，age，dept) AS SELECT * FROM Student WHERE Ssex=女；缺点：修改基表Student的结构后，Student表与F_Student1视图的映象关系被破坏，导致该视图不能正确工作。7/19/202212

55、8第三章 关系数据库标准语言SQL 建立视图（续） CREATE VIEW F_Student2 (stdnum，name，sex，age，dept) AS SELECT Sno，Sname，Ssex，Sage，Sdept FROM Student WHERE Ssex=女；为基表Student增加属性列不会破坏Student表与F_Student2视图的映象关系。7/19/2022129第三章 关系数据库标准语言SQL常见的视图形式小结行列子集视图WITH CHECK OPTION的视图基于多个基表的视图基于视图的视图带表达式的视图分组视图7/19/2022130第三章 关系数据库标准语言S

56、QL2. 删除视图DROP VIEW CASCADE；该语句从数据字典中删除指定的视图定义由该视图导出的其他视图定义仍在数据字典中，但已不能使用，必须显式删除删除基表时，由该基表导出的所有视图定义都必须显式删除CASCADE：级联删除7/19/2022131第三章 关系数据库标准语言SQL删除视图(续） 例9 删除视图IS_S1 DROP VIEW IS_S1；例9 删除视图IS_S1及由其导出的所有视图。 DROP VIEW IS_S1 CASCADE； 7/19/2022132第三章 关系数据库标准语言SQL3.5.2 查询视图从用户角度：查询视图与查询基本表相同DBMS实现视图查询的方法

57、视图消解法（View Resolution）进行有效性检查，检查查询的表、视图等是否存在。如果存在，则从数据字典中取出视图的定义把视图定义中的子查询与用户的查询结合起来，转换成等价的对基本表的查询执行修正后的查询7/19/2022133第三章 关系数据库标准语言SQL查询视图（续）例1 在信息系学生的视图中找出年龄小于20岁的学生。 SELECT Sno，Sage FROM IS_Student WHERE Sage20；IS_Student视图的定义 (视图定义例1)： CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student

58、WHERE Sdept= IS；7/19/2022134第三章 关系数据库标准语言SQL查询视图（续）视图消解法转换后的查询语句为：SELECT Sno，Sage FROM StudentWHERE Sdept= IS AND Sage=90；S_G视图定义： CREATE VIEW S_G (Sno，Gavg) AS SELECT Sno，AVG(Grade)FROM SCGROUP BY Sno；7/19/2022138第三章 关系数据库标准语言SQL查询转换错误：SELECT Sno，AVG(Grade)FROM SCWHERE AVG(Grade)=90GROUP BY Sno；正确：

59、SELECT Sno，AVG(Grade)FROM SCGROUP BY SnoHAVING AVG(Grade)=90；7/19/2022139第三章 关系数据库标准语言SQL3.5.3 更新视图用户角度：更新视图与更新基本表相同DBMS实现视图更新的方法视图消解法（View Resolution）指定WITH CHECK OPTION子句后 DBMS在更新视图时会进行检查，防止用户通过视图对不属于视图范围内的基本表数据进行更新7/19/2022140第三章 关系数据库标准语言SQL更新视图（续）例1 将信息系学生视图IS_Student中学号95002 的学生姓名改为“刘辰”。UPDATE

60、 IS_StudentSET Sname= 刘辰WHERE Sno= 95002；转换后的语句：UPDATE StudentSET Sname= 刘辰WHERE Sno= 95002 AND Sdept= IS；7/19/2022141第三章 关系数据库标准语言SQL更新视图（续）例2 向信息系学生视图IS_S中插入一个新的学生记录：95029，赵新，20岁INSERTINTO IS_StudentVALUES(95029，赵新，20)；转换为对基本表的更新：INSERTINTO Student(Sno，Sname，Sage，Sdept)VALUES(95029，赵新，20，IS )；7/19