三章节关系数据库标准语言SQL.ppt

《三章节关系数据库标准语言SQL.ppt》由会员分享，可在线阅读，更多相关《三章节关系数据库标准语言SQL.ppt（66页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、三章节关系数据库标准语言SQL Still waters run deep.流静水深流静水深,人静心深人静心深 Where there is life,there is hope。有生命必有希望。有生命必有希望3.4 数数 据据 更更 新新 3.4.1 插入数据（插入数据（Insert）3.4.2 修改数据（修改数据（Update）3.4.3 删除数据删除数据（Delect）F插入单个元组插入单个元组F插入子查询结果插入子查询结果1.插入单个元组插入单个元组v语句格式语句格式INSERTINTO (，)VALUES(，)v功能功能 将新元组插入指定表中。将新元组插入指定表中。插入单个元组（续）

2、插入单个元组（续）例例1 将一个新学生记录：将一个新学生记录：（学号：（学号：95020；姓名：陈冬；性别：男；所在；姓名：陈冬；性别：男；所在系：系：IS；年龄：；年龄：18岁）插入到岁）插入到Student表中。表中。INSERT INTO Student VALUES(95020，陈冬陈冬，男男，IS，18)；插入单个元组（续）插入单个元组（续）例例2 插入一条选课记录插入一条选课记录(95020，1)。INSERT INTO SC(Sno，Cno)VALUES (95020，1)；新插入的元组在新插入的元组在Grade列列上取上取空值空值2.插入子查询结果插入子查询结果v语句格式语句格

3、式 INSERT INTO (，)子查询子查询；v功能功能 将子查询结果插入指定表中将子查询结果插入指定表中插入子查询结果（续）插入子查询结果（续）例例3 对每一个系，求学生的平均年龄，并把结对每一个系，求学生的平均年龄，并把结果存入数据库。果存入数据库。第一步：创建新表第一步：创建新表 CREATE TABLE Deptage (Sdept CHAR(15)/*系名系名*/Avgage SMALLINT)；/*学生平均年龄学生平均年龄*/插入子查询结果（续）插入子查询结果（续）第二步：插入数据第二步：插入数据 INSERT INTO Deptage(Sdept，Avgage)SdeptAvg

4、ageIS18MA19CS18子查询结果：子查询结果：SELECT Sdept，AVG(Sage)FROM Student GROUP BY Sdept；3.4 数数 据据 更更 新新 3.4.1 插入数据插入数据3.4.2 修改数据修改数据3.4.3 删除数据删除数据 3.4.2 修改数据修改数据v语句格式语句格式 UPDATE SET =，=WHERE；v功能功能 修改指定表中满足修改指定表中满足WHERE子句条件的元组子句条件的元组修改数据（续）修改数据（续）v三种修改方式三种修改方式F修改某一个元组的值修改某一个元组的值F修改多个元组的值修改多个元组的值F带子查询的修改语句带子查询的修

5、改语句1.修改某一个元组的值修改某一个元组的值例例4 将学生将学生95001的年龄改为的年龄改为22岁。岁。UPDATE Student SET Sage=22 WHERE Sno=95001；2.修改多个元组的值修改多个元组的值例例5 将所有学生的年龄增加将所有学生的年龄增加1岁。岁。UPDATE Student SET Sage=Sage+1；没没有有Where子子句句，说说明明要要修修改改Student表表中中所有元组的年龄属性值所有元组的年龄属性值。修改多个元组的值修改多个元组的值(续续)例例6 将信息系所有学生的年龄增加将信息系所有学生的年龄增加1岁。岁。UPDATE Student

6、 SET Sage=Sage+1 WHERE Sdept=IS；3.带子查询的修改语句带子查询的修改语句例例7 将计算机科学系全体学生的成绩置零。将计算机科学系全体学生的成绩置零。UPDATE SC SET Grade=0 WHERE CS=(SELETE Sdept FROM Student WHERE Student.Sno=SC.Sno)；子查询子查询：在：在Student表表中找出选修了课程中找出选修了课程的学生所在的系名的学生所在的系名3.4 数数 据据 更更 新新 3.4.1 插入数据插入数据3.4.2 修改数据修改数据3.4.3 删除数据删除数据 3.4.3 删除数据删除数据格式

7、：格式：DELETE FROM WHERE ；功功能能：删删除除指指定定表表中中满满足足 WHERE 子子句句条件的条件的元组元组DELETEFROM student删除数据（续）删除数据（续）三种删除方式三种删除方式F删除某一个元组的值删除某一个元组的值F删除多个元组的值删除多个元组的值F带子查询的删除语句带子查询的删除语句1.删除某一个元组的值删除某一个元组的值例例8 删除学号为删除学号为95019的学生记录。的学生记录。DELETE FROM Student WHERE Sno=95019；2.删除多个元组的值删除多个元组的值例例9 删除删除2号课程的所有选课记录。号课程的所有选课记录。

8、DELETE FROM SC；DELETE FROM SC；WHERE Cno=2；例例10 删除所有的学生选课记录。删除所有的学生选课记录。3.带子查询的删除语句带子查询的删除语句例例11 删除计算机科学系所有学生的选课删除计算机科学系所有学生的选课记录。记录。DELETE FROM SC WHERE CS=(SELETE Sdept FROM Student WHERE Student.Sno=SC.Sno)；子查询子查询：在：在Student表表中找出选修了课程中找出选修了课程的学生所在的系名的学生所在的系名4.更新操作与数据一致性更新操作与数据一致性DBMS在执行插入、删除、更新语句时

9、必在执行插入、删除、更新语句时必须保证数据库一致性须保证数据库一致性F必须有事务的概念和原子性必须有事务的概念和原子性F完整性检查和保证完整性检查和保证第三章第三章 关系数据库标准语言关系数据库标准语言SQL3.1 SQL概述概述3.2 数据定义数据定义3.3 查询查询3.4 数据更新数据更新3.5 视图视图3.6 数据控制数据控制3.7 嵌入式嵌入式SQL3.8 小结小结 3.5 视视 图图视图的特点视图的特点v虚表，是从一个或几个基本表（或视图）虚表，是从一个或几个基本表（或视图）导出的表导出的表v只存放视图的定义，不会出现数据冗余只存放视图的定义，不会出现数据冗余v基表中的数据发生变化，

10、从视图中查询基表中的数据发生变化，从视图中查询出的数据也随之改变出的数据也随之改变3.5 视视 图图基于视图的操作基于视图的操作v 查询查询v 删除删除v 受限更新受限更新v定义基于该视图的新视图定义基于该视图的新视图3.5 视视 图图3.5.1 定义视图（建立和删除视图）定义视图（建立和删除视图）3.5.2 查询视图查询视图3.5.3 更新视图更新视图3.5.4 视图的作用视图的作用1.建立视图建立视图语句格式语句格式:CREATE VIEW (,)AS WITH CHECK OPTION；:要建立的视图的名字。要建立的视图的名字。(,)：组成视图的所有属性列的组成视图的所有属性列的名字。要

11、么全部写出，要么全部省略。当省略了视名字。要么全部写出，要么全部省略。当省略了视图的所有属性列名后，视图中的属性列就由子查询图的所有属性列名后，视图中的属性列就由子查询Select语句中的语句中的组成。组成。1.建立视图建立视图 但是对于以下但是对于以下3种情况，必须种情况，必须明确指定明确指定视图的属视图的属性列名字：性列名字：1）某个目标列是集函数或列表达式）某个目标列是集函数或列表达式2）多表连接时选出了几个同名属性列作为视图的）多表连接时选出了几个同名属性列作为视图的属性字段属性字段3）需要在视图中为某个列启用新的更合适的名字）需要在视图中为某个列启用新的更合适的名字1.建立视图建立视

12、图 ：可以是任意的可以是任意的Select语句，但是语句，但是不能含有不能含有Order By字句和字句和Distinct短语。短语。WITH CHECK OPTION：表示对视图进行表示对视图进行修改、插入和删除操作时，必须保证所要修修改、插入和删除操作时，必须保证所要修改、插入和删除的行满足子查询中的改、插入和删除的行满足子查询中的“条件条件表达式表达式”。行列子集视图行列子集视图 例例1 建立信息系学生的视图。建立信息系学生的视图。CREATE VIEW IS_Student AS l从单个基本表导出从单个基本表导出l只是去掉了基本表的某些行和某些列只是去掉了基本表的某些行和某些列l保留

13、了码保留了码 SELECT Sno，Sname，Sage FROM Student WHERE Sdept=IS；WITH CHECK OPTION 的视图的视图例例2 建立信息系学生的视图，并要求透过该视建立信息系学生的视图，并要求透过该视图进行的更新操作只涉及信息系学生。图进行的更新操作只涉及信息系学生。CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student WHERE Sdept=IS WITH CHECK OPTION；对对IS_Student视图的更新操作视图的更新操作v修改修改操作：操作：DBMS自动加上自动加上Sd

14、ept=IS的条件的条件v删除删除操作：操作：DBMS自动加上自动加上Sdept=IS的条件的条件v插入插入操作：操作：DBMS自动检查自动检查Sdept属性值属性值是否为是否为IS,如果如果不是不是，则，则拒绝拒绝该该插入操作插入操作;如果没有如果没有提供提供Sdept 属性值，则自动定义属性值，则自动定义 Sdept 为为IS基于多个基表的视图基于多个基表的视图例例3 建立信息系选修了建立信息系选修了1号课程的学生视图。号课程的学生视图。CREATE VIEW IS_S1(Sno，Sname，Grade)AS SELECT Student.Sno，Sname，Grade FROM Stud

15、ent，SC WHERE Sdept=IS AND Student.Sno=SC.Sno AND SC.Cno=1；基于视图的视图基于视图的视图例例4 建立信息系选修了建立信息系选修了1号课程且成绩在号课程且成绩在90分分以上的学生的视图。以上的学生的视图。CREATE VIEW IS_S2 AS SELECT Sno，Sname，Grade FROM IS_S1 WHERE Grade=90；带表达式的视图带表达式的视图例例5 定义一个反映学生出生年份的视图。定义一个反映学生出生年份的视图。CREATE VIEW BT_S(Sno，Sname，Sbirth)AS 注意：注意：带表达式的视图必

16、须明确定义组成视图的带表达式的视图必须明确定义组成视图的各个属性列名各个属性列名 SELECT Sno，Sname，2004-Sage FROM Student 建立分组视图建立分组视图例例6 将学生的学号及他的平均成绩定义为一个将学生的学号及他的平均成绩定义为一个视图视图 CREATE VIEW S_G(Sno，Gavg)AS 注意：注意：假设假设SC表中表中“成绩成绩”列列Grade为为数字数字型型 SELECT Sno，AVG(Grade)FROM SCGROUP BY Sno；一类不易扩充的视图一类不易扩充的视图例例 7 将将Student表中所有女生记录定义为一个视图表中所有女生记录

17、定义为一个视图 CREATE VIEW F_Student1(stdnum，name，sex，age，dept)缺缺 点点：修修 改改 Student表表 结结 构构 后后，Student表表 与与F_Student1视视图图的的映映象象关关系系被被破破坏坏，导导致致该该视视图不能正常工作。图不能正常工作。AS SELECT*FROM Student WHERE Ssex=女女；常见的视图形式常见的视图形式F行列子集视图行列子集视图FWITH CHECK OPTION的视图的视图F基于多个基表的视图基于多个基表的视图F基于视图的视图基于视图的视图F带表达式的视图带表达式的视图F分组视图分组视图

18、2.删除视图删除视图DROP VIEW ；F该语句从数据字典中删除指定的视图该语句从数据字典中删除指定的视图定义定义F由该视图导出的其他视图定义仍在数由该视图导出的其他视图定义仍在数据字典中，但已不能使用，必须显式据字典中，但已不能使用，必须显式删除删除删除视图删除视图(续）续）例例9 删除视图删除视图IS_S1DROP VIEW IS_S1；DROP VIEWIS_S2；3.5 视视 图图3.5.1 定义视图定义视图3.5.2 查询视图查询视图3.5.3 更新视图更新视图3.5.4 视图的作用视图的作用3.5.2 查询视图查询视图对用户来说，查询视图与查询基本表相同对用户来说，查询视图与查询

19、基本表相同newDBMS实现实现视图查询的方法：视图查询的方法：视图消解法视图消解法l进行有效性检查，检查查询的表、视图等是否存进行有效性检查，检查查询的表、视图等是否存在。如果存在，则从数据字典中取出视图的定义在。如果存在，则从数据字典中取出视图的定义l把视图定义中的子查询与用户的查询结合起来，把视图定义中的子查询与用户的查询结合起来，转换成等价的对基本表的查询转换成等价的对基本表的查询l执行修正后的查询执行修正后的查询查询视图（续）查询视图（续）例例1 在信息系学生的视图（在信息系学生的视图（IS_Student）中）中找出年龄小于找出年龄小于20岁的学生学号和年龄。岁的学生学号和年龄。S

20、ELECT Sno，Sage FROM IS_Student WHERE Sage20；查询视图（续）查询视图（续）转换后的查询语句为：转换后的查询语句为：IS_Student 视图的定义视图的定义：CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student WHERE Sdept=IS；SELECT Sno，Sage FROM Student WHERE Sdept=IS AND Sage=90；把该查询语句和视图定义中的子查询结合把该查询语句和视图定义中的子查询结合起来，形成最终的查询语句如下：起来，形成最终的查询语句如下：查询

21、转换查询转换 SELECT Sno，AVG(Grade)FROM SCGROUP BY SnoHAVING AVG(Grade)=90；错误：错误：SELECT Sno，AVG(Grade)FROM SCWHERE AVG(Grade)=90GROUP BY Sno；正确：正确：3.5 视视 图图3.5.1 定义视图定义视图3.5.2 查询视图查询视图3.5.3 更新视图更新视图3.5.4 视图的作用视图的作用3.5.3 更新视图更新视图更新视图更新视图：F就是通过视图来就是通过视图来插入插入(Insert)、删除删除(Delete)和和修改修改(Update)基本表中基本表中 的数据的数据F

22、对视图的更新，最终要转换为对基本表的对视图的更新，最终要转换为对基本表的更新更新 F为了防止更新不属于该视图范围的数据，为了防止更新不属于该视图范围的数据，可以在可以在定义视图时定义视图时加上加上 With Check Option子句子句 3.5.3 更新视图更新视图v修改视图修改视图语句格式：语句格式：Update Set Where v插入视图插入视图语句格式：语句格式：Insert Into （属性名（属性名1,属性名属性名2,）Values （属性（属性1值值,属性属性2值值,）3.5.3 更新视图更新视图v删除视图删除视图语句格式：语句格式：DeleteFrom Where 修改视

23、图修改视图例例1 将信息系学生视图将信息系学生视图IS_Student中学号中学号95002 的学生姓名改为的学生姓名改为“刘辰刘辰”。UPDATE IS_StudentSET Sname=刘辰刘辰WHERE Sno=95002；转换后的语句：转换后的语句：UPDATE StudentSET Sname=刘辰刘辰WHERE Sno=95002 AND Sdept=IS；插入视图插入视图例例2 向信息系学生视图向信息系学生视图IS_Student中插入一个中插入一个新的学生记录：新的学生记录：95029，赵新，赵新，20岁。岁。INSERTINTO IS_StudentVALUES（95029，

24、赵新赵新，20）；）；转换为对基本表的更新：转换为对基本表的更新：INSERTINTO Student（Sno，Sname，Sage，Sdept）VALUES（95029，赵新赵新，20，IS）；）；删除视图删除视图例例3 删除信息系学生视图删除信息系学生视图 IS_Student中学号为中学号为95029的记录。的记录。DELETEFROM IS_StudentWHERE Sno=95029；DELETEFROM StudentWHERE Sno=95029 AND Sdept=IS；转换为对基本表的删除：转换为对基本表的删除：更新视图的限制更新视图的限制v一些视图是不可更新的，因为对这些视

25、图的更一些视图是不可更新的，因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的新不能唯一地有意义地转换成对相应基本表的更新。更新。例例4 把学生学号和平均成绩视图把学生学号和平均成绩视图 S_G中学号为中学号为95001的学生的平均成绩改为的学生的平均成绩改为90分。分。更新视图（续）更新视图（续）对视图对视图S_G的更新语句如下的更新语句如下:UPDATE S_GSET Gavg=90WHERE Sno=95001；CREATE VIEW S_G(Sno，Gavg)AS SELECT Sno，AVG(Grade)FROM SC GROUP BY Sno；视图视图S_G的定义如下的定义

26、如下:视图的可更新性视图的可更新性理论上可更新理论上可更新 理论上不可更新理论上不可更新允许更新允许更新 不允许更新不允许更新一般的，行列子集视图一般的，行列子集视图是是可更新可更新的的实际系统对视图更新的限制实际系统对视图更新的限制v允许对行列子集视图进行更新允许对行列子集视图进行更新v对其他类型视图的更新不同系统有不同限制对其他类型视图的更新不同系统有不同限制DB2 对视图更新的限制：对视图更新的限制：(1)若视图是由两个以上基本表导出的，则此视若视图是由两个以上基本表导出的，则此视图不允许更新。图不允许更新。(2)若视图的字段来自字段表达式或常数，则不若视图的字段来自字段表达式或常数，则

27、不允许对此视图执行允许对此视图执行INSERT和和UPDATE操作，操作，但允许执行但允许执行DELETE操作。操作。更新视图（续）更新视图（续）(3)若视图的字段来自集函数，则此视图不允若视图的字段来自集函数，则此视图不允许更新许更新(4)若视图定义中含有若视图定义中含有GROUP BY子句，则此子句，则此视图不允许更新。视图不允许更新。(5)若视图定义中含有若视图定义中含有DISTINCT短语，则此视短语，则此视图不允许更新。图不允许更新。更新视图（续）更新视图（续）(6)若视图定义中有嵌套查询，并且内层查询的若视图定义中有嵌套查询，并且内层查询的FROM子句中涉及的表也是导出该视图的基子

28、句中涉及的表也是导出该视图的基本表，则此视图不允许更新。本表，则此视图不允许更新。(7)一个不允许更新的视图上定义的视图也不允一个不允许更新的视图上定义的视图也不允许更新许更新3.5 视视 图图3.5.1 定义视图定义视图3.5.2 查询视图查询视图3.5.3 更新视图更新视图3.5.4 视图的作用视图的作用3.5.4 视图的作用视图的作用1.视图能够视图能够 简化简化 用户的操作用户的操作2.视图使用户能以视图使用户能以多种角度多种角度看待同一数据看待同一数据3.视图对视图对 重构数据库重构数据库 提供了一定程度的逻提供了一定程度的逻辑独立性辑独立性4.视图能够对机密数据提供视图能够对机密数据提供安全保护安全保护