第3章关系数据库标准语言-3

1、数据库原理数据库原理第第3 3章章 关系数据库标准语言关系数据库标准语言(3)(3)主讲：杨莎主讲：杨莎数据库原理数据库原理第第3 3章章 关系数据库标准语言关系数据库标准语言3.1 SQL3.1 SQL概述概述3.2 3.2 数据定义数据定义3.3 3.3 数据查询数据查询3.4 3.4 数据更新数据更新3.5 3.5 视图视图Page 2数据库原理数据库原理3.4 3.4 数据更新数据更新 数据操纵也称为数据更新，是指对关系中的数据操纵也称为数据更新，是指对关系中的具具体数据体数据进行进行操作。操作。3.4.1 3.4.1 数据插入数据插入3.4.2 3.4.2 数据删除数据删除3.4.3

2、 3.4.3 数据修改数据修改Page 3数据库原理数据库原理3.4.1 3.4.1 数据插入数据插入n两种插入数据方式两种插入数据方式 插入单个元组 插入子查询结果Page 4数据库原理数据库原理1. 1. 插入单个元组插入单个元组n语句格式语句格式 INSERT INTO ( (1， 2 ) VALUES ( ( 1 ， 2 ) )n功能功能 将新元组插入指定表中将新元组插入指定表中Page 5数据库原理数据库原理n INTOINTO子句子句 指定要插入数据的表名及属性列 属性列的顺序可与表定义中的顺序不一致 没有指定属性列：表示要插入的是一条完整的元组，且属性列属性与表定义中的顺序一致

3、指定部分属性列：插入的元组在其余属性列上取空值n VALUESVALUES子句子句 提供的值必须与INTO子句匹配值的个数值的类型插入单个元组（续）插入单个元组（续）Page 6数据库原理数据库原理插入单个元组（续）插入单个元组（续） 例例1 1 将一个新学生记录将一个新学生记录（学号：学号： 200215128200215128 ；姓名：陈冬；性别：男；所在系：；姓名：陈冬；性别：男；所在系：ISIS；年龄：；年龄：1818岁岁）插入到插入到StudentStudent表中。表中。 INSERT INTO Student VALUES ( (200215128200215128， 陈冬陈冬

4、， 男男 ，ISIS，18)18)；等价于等价于 INSERT INTO Student(Sno,Sname,Ssex,Sdept,Sage) VALUES ( (200215128200215128， 陈冬陈冬 ， 男男 ，ISIS，18)18)；Page 7数据库原理数据库原理插入单个元组（续）插入单个元组（续） 例例22插入一条选课记录插入一条选课记录( 200215128 ( 200215128 ，1 )1 )。 INSERT INTO SC(Sno，Cno) VALUES ( 200215128 ， 1 )；NB:NB:新插入的记录在新插入的记录在GradeGrade列上取空值列上取

5、空值或者：或者： INSERT INTO SC VALUES ( 200215128 ， 1 ,NULL);Page 8数据库原理数据库原理2. 2. 插入子查询结果插入子查询结果n 语句格式语句格式 INSERT INTO ( ( 1 ， 2 ) ) 子子查询查询；n 功能功能 将子查询结果插入指定表中将子查询结果插入指定表中Page 9数据库原理数据库原理插入子查询结果（续）插入子查询结果（续）n INTOINTO子句子句( (与插入单条元组类似与插入单条元组类似) ) 指定要插入数据的表名及属性列 属性列的顺序可与表定义中的顺序不一致 没有指定属性列：表示要插入的是一条完整的元组 指定部

6、分属性列：插入的元组在其余属性列上取空值n 子查询子查询 SELECT子句目标列必须与INTO子句匹配值的个数值的类型Page 10数据库原理数据库原理插入子查询结果（续）插入子查询结果（续） 例例3 3 对每一个系，求学生的平均年龄，并把结对每一个系，求学生的平均年龄，并把结果存入数据库。果存入数据库。第一步：建表第一步：建表 CREATE TABLE Deptage ( Sdept CHAR(15) / /* * 系名系名* */ / Avg_age SMALLINT / /* *学生平均年龄学生平均年龄* */ / ) )；Page 11数据库原理数据库原理插入子查询结果（续）插入子查询

7、结果（续）第二步：插入数据第二步：插入数据 INSERT INTO Deptage(Sdept，Avgage) SELECT Sdept，AVG(Sage) FROM Student GROUP BY Sdept；Page 12数据库原理数据库原理插入子查询结果（续）插入子查询结果（续）n DBMSDBMS在执行插入语句时会检查所插元组是否破在执行插入语句时会检查所插元组是否破坏表上已定义的完整性规则坏表上已定义的完整性规则 实体完整性 参照完整性 用户定义的完整性 对于有NOT NULL约束的属性列是否提供了非空值 对于有UNIQUE约束的属性列是否提供了非重复值 对于有值域约束的属性列所提

8、供的属性值是否在值域范围内Page 13数据库原理数据库原理3.4 3.4 数据更新数据更新3.4.1 3.4.1 数据插入数据插入3.4.2 3.4.2 数据删除数据删除3.4.3 3.4.3 数据修改数据修改( (更新更新) )Page 14数据库原理数据库原理3.4.2 3.4.2 数据删除数据删除n 语句格式语句格式 DELET FROM WHERE ；功能功能w 删除指定表中满足WHERE子句条件的元组 WHERE子句子句w 指定要删除的元组w 缺省表示要删除表中的所有元组，表的定义仍保存在数据字典中Page 15数据库原理数据库原理删除数据（续）删除数据（续）n 三种删除方式三种删

9、除方式1. 删除某一个元组的值2. 删除多个元组的值3. 带子查询的删除语句Page 16数据库原理数据库原理1. 1. 删除某一个元组的值删除某一个元组的值 例例4 4 删除学号为删除学号为200215128200215128的学生记录。的学生记录。 DELETE FROM Student WHERE Sno=200215128； Page 17数据库原理数据库原理2. 2. 删除多个元组的值删除多个元组的值 例例5 5 删除删除2 2号课程的所有选课记录。号课程的所有选课记录。 DELETE FROM SC； WHERE Cno=2； 例例6 6 删除所有的学生选课记录。删除所有的学生选课

10、记录。 DELETE FROM SC；Page 18数据库原理数据库原理3. 3. 带子查询的删除语句带子查询的删除语句 例例7 7 删除计算机科学系所有学生的选课记录。删除计算机科学系所有学生的选课记录。 DELETE FROM SC WHERE Sno IN (SELETE Sno FROM Student WHERE Sdept=cs)；Page 19数据库原理数据库原理3.4.3 3.4.3 数据修改更新数据修改更新n 语句格式语句格式 UPDATE SET =，= WHERE ；n 功能功能修改指定表中满足WHERE子句条件的元组Page 20数据库原理数据库原理修改数据（续）修改数

11、据（续）n SETSET子句子句 指定修改方式指定修改方式 要修改的列 修改后取值n WHEREWHERE子句子句 指定要修改的元组 缺省表示要修改表中的所有元组Page 21数据库原理数据库原理修改数据（续）修改数据（续）n三种修改方式三种修改方式 修改某一个元组的值 修改多个元组的值 带子查询的修改语句Page 22数据库原理数据库原理1. 1. 修改某一个元组的值修改某一个元组的值 例例8 8 将学生将学生200215121200215121的年龄改为的年龄改为2222岁。岁。 UPDATE Student SET Sage=22 WHERE Sno= 200215121 ； Page

12、23数据库原理数据库原理2. 2. 修改多个元组的值修改多个元组的值 例例9 9 将所有学生的年龄增加将所有学生的年龄增加1 1岁。岁。 UPDATE Student SET Sage= Sage+1；Page 24数据库原理数据库原理修改多个元组的值修改多个元组的值( (续续) ) 例例10 10 将信息系所有学生的年龄增加将信息系所有学生的年龄增加1 1岁。岁。 UPDATE Student SET Sage= Sage+1 WHERE Sdept= IS ；Page 25数据库原理数据库原理3. 3. 带子查询的修改语句带子查询的修改语句 例例1111 将计算机科学系全体学生的成绩置零。

13、将计算机科学系全体学生的成绩置零。 UPDATE SC SET Grade=0 WHERE Sno IN (SELETE Sno FROM Student WHERE Sdept=CS)；Page 26数据库原理数据库原理修改数据（续）修改数据（续）n DBMSDBMS在执行修改语句时会检查修改操作是否破坏在执行修改语句时会检查修改操作是否破坏表上已定义的完整性规则表上已定义的完整性规则 实体完整性 主码不允许修改 用户定义的完整性 NOT NULL约束 UNIQUE约束 值域约束Page 27数据库原理数据库原理第第3 3章章 关系数据库标准语言关系数据库标准语言3.1 SQL3.1 SQL

14、概述概述3.2 3.2 数据定义数据定义3.3 3.3 数据查询数据查询3.4 3.4 数据更新数据更新3.5 3.5 视图视图Page 28数据库原理数据库原理n 视图的特点视图的特点 虚表，是从一个或几个基本表（或视图）导出的表 只存放视图的定义，不会出现数据冗余 基表中的数据发生变化，从视图中查询出的数据也随之改变视图的特点视图的特点Page 29数据库原理数据库原理3.5 3.5 视视 图图3.5.1 3.5.1 定义视图定义视图3.5.2 3.5.2 查询视图查询视图3.5.3 3.5.3 更新视图更新视图3.5.4 3.5.4 视图的作用视图的作用Page 30数据库原理数据库原理

15、3.6.1 3.6.1 定义视图定义视图n 建立视图建立视图n 删除视图删除视图Page 31数据库原理数据库原理一、建立视图一、建立视图n 语句格式语句格式 CREATE VIEWCREATE VIEW ( ( ， ) AS AS WITH CHECK OPTIONWITH CHECK OPTION ；n 子查询不允许含有子查询不允许含有ORDER BYORDER BY子句和子句和DISTINCTDISTINCT短语短语n 组成视图的属性列名：全部省略或全部指定组成视图的属性列名：全部省略或全部指定Page 32数据库原理数据库原理组成视图的属性列名组成视图的属性列名全部省略或全部指定全部省

16、略或全部指定: :1.1. 省略省略: : 由子查询中由子查询中SELECTSELECT目标列中的诸字段组成目标列中的诸字段组成2.2. 明确指定视图的所有列名明确指定视图的所有列名: :(1) 某个目标列是集函数或列表达式(2) 多表连接时选出了几个同名列作为视图的字段(3) 需要在视图中为某个列启用新的更合适的名字Page 33数据库原理数据库原理 建立视图（续）建立视图（续）n RDBMSRDBMS执行执行CREATE VIEWCREATE VIEW语句时只是把视图定义存语句时只是把视图定义存入数据字典，并不执行其中的入数据字典，并不执行其中的SELECTSELECT语句。语句。n 在对

17、视图查询时，按视图的定义从基本表中将数在对视图查询时，按视图的定义从基本表中将数据查出。据查出。Page 34数据库原理数据库原理常见的视图形式常见的视图形式n 行列子集视图行列子集视图n 基于多个基表的视图基于多个基表的视图n WITH CHECK OPTIONWITH CHECK OPTION的视图的视图n 基于视图的视图基于视图的视图n 带表达式的视图带表达式的视图n 分组视图分组视图Page 35数据库原理数据库原理建立视图（续）建立视图（续） 例例1 1 建立信息系学生的视图。建立信息系学生的视图。 CREATE VIEW CREATE VIEW IS_StudentIS_Stude

18、nt AS AS SELECT SELECT SnoSno，SnameSname，SageSage FROM Student FROM Student WHERE WHERE SdeptSdept= IS= IS；从单个基本表导出只是去掉了基本表的某些行和某些列保留了主键Page 36例9数据库原理数据库原理建立视图（续）建立视图（续） 例例22建立信息系学生的视图，并要求进行修改和建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生插入操作时仍需保证该视图只有信息系的学生 。 CREATE VIEW IS_StudentCREATE VIEW IS_Student

19、AS AS SELECT Sno SELECT Sno，SnameSname，SageSage FROM Student FROM Student WHERE Sdept= IS WHERE Sdept= IS WITH CHECK OPTIONWITH CHECK OPTION；Page 37数据库原理数据库原理建立视图（续）建立视图（续）对对IS_StudentIS_Student视图的更新操作：视图的更新操作：n 修改操作：自动加上修改操作：自动加上Sdept= ISSdept= IS的条件的条件n 删除操作：自动加上删除操作：自动加上Sdept= ISSdept= IS的条件的条件n

20、插入操作：自动检查插入操作：自动检查SdeptSdept属性值是否为属性值是否为IS IS 如果不是，则拒绝该插入操作 如果没有提供Sdept属性值，则自动定义Sdept为ISPage 38数据库原理数据库原理建立视图（续）建立视图（续）n 基于多个基表的视图基于多个基表的视图 例例3 3 建立信息系选修了建立信息系选修了1 1号课程的学生视图。号课程的学生视图。 CREATE VIEW IS_S1(CREATE VIEW IS_S1(SnoSno，SnameSname，Grade)Grade) AS AS SELECT SELECT Student.Student.SnoSno，SnameS

21、name，GradeGrade FROM FROM StudentStudent，SCSC WHERE WHERE SdeptSdept= IS AND= IS AND Student.SnoStudent.Sno= =SC.SnoSC.Sno AND AND SC.CnoSC.Cno= 1= 1；Page 39数据库原理数据库原理建立视图（续）建立视图（续）n 基于视图的视图基于视图的视图 例例4 4 建立信息系选修了建立信息系选修了1 1号课程且成绩在号课程且成绩在9090分以上的学分以上的学生的视图。生的视图。 CREATE VIEW IS_S2CREATE VIEW IS_S2 AS

22、AS SELECT SELECT SnoSno，SnameSname，GradeGrade FROM IS_S1FROM IS_S1 WHERE WHERE CnoCno=1 and Grade=1 and Grade=90=90；Page 40例8数据库原理数据库原理建立视图（续）建立视图（续）n 带表达式的视图带表达式的视图 例例5 5 定义一个反映学生出生年份的视图。定义一个反映学生出生年份的视图。 CREATE VIEW BT_S(Sno，Sname，Sbirth) AS SELECT Sno，Sname，2014-Sage FROM Student；Page 41数据库原理数据库原理

23、建立视图（续）建立视图（续）n 分组视图分组视图 例例6 6 将学生的学号及他的平均成绩定义为一个视图将学生的学号及他的平均成绩定义为一个视图 假设假设SCSC表中表中“成绩成绩”列列GradeGrade为数字型为数字型 CREAT VIEW S_G(Sno，Gavg) AS SELECT Sno，AVG(Grade) FROM SC GROUP BY Sno；Page 42数据库原理数据库原理 建立视图（续）建立视图（续）n 不指定属性列不指定属性列 例例7 7 将将StudentStudent表中所有女生记录定义为一个视图表中所有女生记录定义为一个视图 CREATE VIEW CREATE

24、 VIEW F_StudentF_Student( (F_SnoF_Sno，namename，sexsex，ageage，dept)dept) AS AS SELECT SELECT * * FROM StudentFROM Student WHERE WHERE SsexSsex=女女； 缺点：缺点： 修改基表修改基表StudentStudent的结构后，的结构后，StudentStudent表与表与F_StudentF_Student视图的映象关系视图的映象关系被破坏，导致该视图不能正确工作。被破坏，导致该视图不能正确工作。Page 43数据库原理数据库原理二、删除视图二、删除视图n 语句

25、的格式：语句的格式：DROP VIEW DROP VIEW ； 该语句从数据字典中删除指定的视图定义 如果该视图上还导出了其他视图，使用CASCADE级联删除语句，把该视图和由它导出的所有视图一起删除 删除基表时，由该基表导出的所有视图定义都必须显式地使用DROP VIEW语句删除 Page 44数据库原理数据库原理删除视图删除视图( (续）续）例例8 8 删除视图删除视图BT_SBT_S： DROP VIEW BT_SDROP VIEW BT_S； 删删除视图除视图IS_S1IS_S1： DROP DROP VIEW IS_S1VIEW IS_S1；结果：拒绝执行改进：级联删除 DROP D

26、ROP VIEW IS_S1 CASCADE; VIEW IS_S1 CASCADE; Page 45数据库原理数据库原理3.6 3.6 视视 图图3.6.1 3.6.1 定义视图定义视图3.6.2 3.6.2 查询视图查询视图3.6.3 3.6.3 更新视图更新视图3.6.4 3.6.4 视图的作用视图的作用Page 46数据库原理数据库原理3.6.2 3.6.2 查询视图查询视图n 用户角度：查询视图与查询基本表相同用户角度：查询视图与查询基本表相同n RDBMSRDBMS实现视图查询的方法实现视图查询的方法 视图消解法（View Resolution）进行有效性检查转换成等价的对基本表的

27、查询执行修正后的查询Page 47数据库原理数据库原理查询视图（续）查询视图（续） 例例9 9 在信息系学生的视图中找出年龄小于在信息系学生的视图中找出年龄小于2020岁岁的学生。的学生。 SELECT Sno，Sage FROM IS_Student WHERE Sage20；IS_Student视图的定义 (参见视图定义例1)Page 48数据库原理数据库原理查询视图（续）查询视图（续）视图消解转换后的查询语句为：视图消解转换后的查询语句为： SELECT Sno，Sage FROM Student WHERE Sdept= IS AND Sage=90； Page 52数据库原理数据库原

28、理查询转换查询转换错误：错误：SELECT Sno，AVG(Grade)FROM SCWHERE AVG(Grade)=90GROUP BY Sno；正确：正确：SELECT Sno，AVG(Grade)FROM SCGROUP BY SnoHAVING AVG(Grade)=90；Page 53数据库原理数据库原理3.6 3.6 视视 图图3.6.1 3.6.1 定义视图定义视图3.6.2 3.6.2 查询视图查询视图3.6.3 3.6.3 更新视图更新视图3.6.4 3.6.4 视图的作用视图的作用Page 54数据库原理数据库原理更新视图（续）更新视图（续） 例例12 12 将信息系学生

29、视图将信息系学生视图IS_StudentIS_Student中学号中学号200215122200215122的学生的学生姓名改为姓名改为“刘辰刘辰”。UPDATE IS_StudentSET Sname= 刘辰WHERE Sno= 200215122 ；转换后的语句：UPDATE StudentSET Sname= 刘辰WHERE Sno= 200215122 AND Sdept= IS；Page 55数据库原理数据库原理更新视图（续）更新视图（续） 例例13 13 向信息系学生视图向信息系学生视图IS_SIS_S中插入一个新的学中插入一个新的学生记录：生记录：200215129200215129，赵新，赵新，2020岁岁INSERTINTO IS_StudentVALUES(95029，赵新，20)；转换为对基本表的更新：转