数据库(第三章4)ppt.ppt
3.4 数据库更新数据库更新SQL中数据更新包括插入数据、修改数据和中数据更新包括插入数据、修改数据和删除数据三条语句。与查询语句删除数据三条语句。与查询语句SELECT不不同，数据更新语句一次只能对一个表中数据同，数据更新语句一次只能对一个表中数据进行更新操作。进行更新操作。3.4.1 插入数据（插入数据（INSERT）INSERT语句是将一行或多行插入到一个表语句是将一行或多行插入到一个表或视图中。或视图中。一、插入一行一、插入一行语句格式为：语句格式为：INSERT INTO (1，)2)VALUES(1，)2)；功功能能：将将给给定定的的新新行行(，)插入到指定表中。插入到指定表中。INSERT语语句句未未提提供供值值的的列列，取取默默然然值值，如如果果该该列列没没有有默默然然值值，则则取取空空值值。当当然然，在在表表定定义义时时说说明明了了NOT NULL的的属属性性列列不不能能取取空空值值，必必须须给给出出具具体体值值（非非空空值值），否则系统会报出错。否则系统会报出错。如如果果INTO子子句句中中没没有有指指明明任任何何列列名名，则则新新插入的记录必须在每个属性列上均有值。插入的记录必须在每个属性列上均有值。例例1将将一一个个新新记记录录(S9,陈陈冬冬,男男,18,IS）插插入入到到S表中。表中。INSERT INTO S VALUES(“S9”,“陈陈 冬冬”,“男男”,18,“IS”）;例例2 插插入入一一条条选选课课记记录录(“S9”,“C1”)。INSERT INTO SC(Sno,Cno)VALUES(“S9”,“C1”）;插入可能会破坏参照完整性。插入可能会破坏参照完整性。二、插入子查询结果二、插入子查询结果 其格式为其格式为：INSERT INTO(，)子查询；子查询；例例3 求每个系学生的平均年龄，并将结果存求每个系学生的平均年龄，并将结果存入数据库。入数据库。首先在首先在RDB中建一个表，含有两列：系名中建一个表，含有两列：系名和平均成绩。和平均成绩。CREATE TABLE Deptage(Sdept VARCHAR(20),Avgage INT);然后将数据插入到该表中。然后将数据插入到该表中。INSERT INTO Deptage(Sdept,Avgage)SELECT Sdept,AVG(sage)FROM SGROUP BY Sdept;3.4.2 修改数据（修改数据（UPDATE）UPDATE语语句句是是修修改改表表或或视视图图中中的的一一行行或或多多行行的值。的值。其一般格式为：其一般格式为：UPDATE SET=,=WHERE；功能：功能：修改指定表中满足修改指定表中满足WHERE子句条子句条件的行。对满足条件的行，用件的行。对满足条件的行，用的值取代的值取代的原值的原值.例例4 将学生将学生S4S4的姓名改为的姓名改为WangPing。例例5 将所有学生年龄加将所有学生年龄加2 2。例例6 将数学系全体学生的成绩置将数学系全体学生的成绩置0 0。子查询必须在更新之前完成子查询必须在更新之前完成。数据库更新操作可能会破坏数据库的完数据库更新操作可能会破坏数据库的完整性约束条件，使数据库数据出现不一整性约束条件，使数据库数据出现不一致。为此，系统一般会对任何数据库更致。为此，系统一般会对任何数据库更新操作进行完整性检查，若发现违反了新操作进行完整性检查，若发现违反了某个约束，那么这个更新操作就会被撤某个约束，那么这个更新操作就会被撤消。消。DELETE语语句句是是从从一一个个表表或或视视图图中中删删除除一一行或多行。行或多行。其一般格式为：其一般格式为：DELETE FROM WHERE；从指定表中删除满足从指定表中删除满足WHERE子句中条件子句中条件的所有行的所有行。3.4.3删除数据（删除数据（DELETE）例例7 将学号为将学号为S9S9的学生删除。的学生删除。注意完整性。注意完整性。例例8 删除全体学生的选课记录。删除全体学生的选课记录。例例9 将没有选任何课的学生删除。将没有选任何课的学生删除。例例10 删除计算机系全体学生的选课记录。删除计算机系全体学生的选课记录。3.5 视图视图(View)View)到到目目前前为为止止，我我们们一一直直是是直直接接对对基基本本表表操操作作。基基本本表表是是实实际际存存储储在在数数据据库库中中的的，它它是实际存储数据的逻辑表示。是实际存储数据的逻辑表示。有有时时，处处于于安安全全考考虑虑，我我们们并并不不希希望望所所有有用用户户都都能能看看到到整整个个数数据据库库逻逻辑辑模模型型，希希望望对对某某些些用用户户来来说说，他他只只能能看看到到让让他他看看到到的的那那部部分分数数据据。而而视视图图机机制制能能够够满满足足这这个个要要求。求。视图视图是从一个或几个基本表（或视图）是从一个或几个基本表（或视图）导出的表导出的表，可以是从一个表中选取某些，可以是从一个表中选取某些列或某些行组成的表，也可以由若干表列或某些行组成的表，也可以由若干表满足一定条件的数据组成的表。不过，满足一定条件的数据组成的表。不过，数据库中只存放视图的定义，视图表对数据库中只存放视图的定义，视图表对应的数据仍存放在相应的基本表中。应的数据仍存放在相应的基本表中。视图视图1视图视图2视图视图3视图视图4基本表基本表1基本表基本表2基本表基本表3基本表基本表43.5.1 视图的定义视图的定义 一、建立视图一、建立视图 视视图图由由子子查查询询来来定定义义的的，子子查查询询的的结结果果表表就是视图表。就是视图表。格式为：格式为：CREATE VIEW()AS WITH CHECK OPTION；说明：说明：（1）给出视图的列名。它给出视图的列名。它是可选项，若给出此项，则表示给子查是可选项，若给出此项，则表示给子查询的询的SELECT子句所选择的数据重新起个子句所选择的数据重新起个名字作为视图的列名，它们依次对应；名字作为视图的列名，它们依次对应；当省略时，表示视图的列名取子查询中当省略时，表示视图的列名取子查询中SELECT子句给出的列名。子句给出的列名。（2）当视图定义给出了）当视图定义给出了WITH CHECK OPTION 选项时，用户对视图进行更新选项时，用户对视图进行更新操作（插入、删除和修改）时要保证更操作（插入、删除和修改）时要保证更新的行必须满足子查询中的条件表达式。新的行必须满足子查询中的条件表达式。否则拒绝更新。否则拒绝更新。例例1 建立计算机系的学生视图表建立计算机系的学生视图表CS-S。（带有带有WITH CHECK OPTION）行列子集视图行列子集视图：若一个视图是从单个基本：若一个视图是从单个基本表中去掉了若干行和若干列，且保留了码，表中去掉了若干行和若干列，且保留了码，则称它为行列子集视图则称它为行列子集视图。如。如CS-S例例2 把学生的学号和把学生的学号和 平均成绩定义为视图平均成绩定义为视图S-AG。(派生列派生列)例例3 建立信息系选修了建立信息系选修了C1课的学生视图课的学生视图IS-S1。例例4 建立信息系选修建立信息系选修C1课课且成绩在且成绩在90分以分以上的学生视图上的学生视图IS-S2。(视图上再建视图视图上再建视图)例例5 建立一个反映学生出生年份的视图建立一个反映学生出生年份的视图BT-S。例例6 建立一个反映学生学号、姓名、所选课建立一个反映学生学号、姓名、所选课程名和成绩的视图程名和成绩的视图S-G。二、删除视图二、删除视图 当某个视图不再需要时，可以删除视图。当某个视图不再需要时，可以删除视图。格式为：格式为：DROP VIEW CASCADE|RESTRICT；例例7 删除删除IS-S1视图视图。3.5.2 对视图的查询对视图的查询视图定义好后，用户可以对其进行查询视图定义好后，用户可以对其进行查询。对视图进行查询时，系统会自动将其转换对视图进行查询时，系统会自动将其转换为等价的对基本表的查询。为等价的对基本表的查询。即对视图查询即对视图查询时，系统首先从数据字典中取出该视图的时，系统首先从数据字典中取出该视图的定义，然后自动把定义中的子查询与视图定义，然后自动把定义中的子查询与视图查询语句结合起来，转换成等价的对基本查询语句结合起来，转换成等价的对基本表的查询，然后再执行修正后的查询。这表的查询，然后再执行修正后的查询。这一转换称为一转换称为视图消解视图消解。例例8 从视图从视图CS-S中选择年龄小于中选择年龄小于20岁的学岁的学生生。CREATE VIEW CS-SAS SELECT Sno,Sname,Ssex,SageFROM SWHERE Sdept=“CS”;例例9 查询选修查询选修MATH课的学生的学号、姓课的学生的学号、姓名和成绩。名和成绩。(对对S-G查询查询)CREATE VIEW S-GAS SELECT S.Sno,Sname,Cname,GradeFROM S,SC,CWHERE S.Sno=SC.Sno AND SC.Cno=C.Cno;通常，视图查询的转换是直截了当的，但有时通常，视图查询的转换是直截了当的，但有时也会产生问题。例如。也会产生问题。例如。例例10 查询平均成绩在查询平均成绩在90分以上的学生学号分以上的学生学号和成绩。和成绩。CREATE VIEW S-AG(Sno,Avgg)AS SELECT Sno,AVG(Grade)FROM SCGROUP BY Sno;执行这个查询后会得到一个不正确的查询执行这个查询后会得到一个不正确的查询语句。语句。目前，多数目前，多数RDBMS对从一个或多个表中对从一个或多个表中抽取若干行和若干列构成的视图的查询抽取若干行和若干列构成的视图的查询均能正确转换。但从对含有派生列的视均能正确转换。但从对含有派生列的视图的查询就不是所有的都能转换了。图的查询就不是所有的都能转换了。每个系统对什么样的子查询可以用于视每个系统对什么样的子查询可以用于视图定义都有一定的规定。图定义都有一定的规定。3.5.3视图的更新视图的更新 对视图的更新，系统要转换为对基本表对视图的更新，系统要转换为对基本表的更新。但并不是所有的视图都是可更的更新。但并不是所有的视图都是可更新的，因为有些视图的更新不能唯一地新的，因为有些视图的更新不能唯一地有意义地转换为对相应基本表的更新有意义地转换为对相应基本表的更新。例例11 将视图将视图CS-S中，学号为中，学号为S9的姓名改为的姓名改为WangPing。CREATE VIEW CS-SAS SELECT Sno,Sname,Ssex,SageFROM SWHERE Sdept=“CS”;例例12 将将(s10,YangHua,19)插入到插入到CS-S中。中。例例13 删除计算机系学生视图删除计算机系学生视图CS-S中学号为中学号为S9的学生记录。的学生记录。例例14 将将S10的平均成绩改为的平均成绩改为90。(不可更新不可更新)CREATE VIEW S-AG(Sno,Avgg)AS SELECT Sno,AVG(Grade)FROM SCGROUP BY Sno;在在RBD中，并不是所有视图都可更新。中，并不是所有视图都可更新。一般地，行列子集视图是可更新的。除一般地，行列子集视图是可更新的。除行列子集视图外，还有一些视图理论上行列子集视图外，还有一些视图理论上是可更新的，但他们的特征还是尚待研是可更新的，但他们的特征还是尚待研究。究。目前，各个目前，各个RDBMS一般允许对行列子集一般允许对行列子集视图更新，另外，各个系统对视图的更视图更新，另外，各个系统对视图的更新还有进一步的规定。由于各个系统实新还有进一步的规定。由于各个系统实现方法上的差异，这些规定也不尽相同。现方法上的差异，这些规定也不尽相同。下面给出下面给出DB2的规定。的规定。(1)如如果果一一个个视视图图是是从从两两个个以以上上基基本本表表导导出出的的，则不允许对该视图执行更新操作。则不允许对该视图执行更新操作。因因为为，更更新新操操作作一一次次只只能能对对一一个个表表操操作作。而而对对来来自自多多个个表表的的视视图图的的更更新新要要转转化化为为对对多多个个基本表的操作，这是错误的。基本表的操作，这是错误的。(2)若若视视图图的的字字段段来来自自字字段段表表达达式式或或常常量量，则则不不允允许许对对此此视视图图执执行行INSERT和和UPDATE操操作，但允许执行作，但允许执行DELETE操作。操作。(3)若若视视图图的的字字段段来来自自集集合合函函数数，则则此此视视图图不可更新。不可更新。(4)若若视视图图定定义义中中使使用用GROUP BY子子句句，则则对该视图不允许执行更新操作。对该视图不允许执行更新操作。(5)若若视视图图定定义义中中使使用用DISTINCT短短语语，则则此视图不允许更新。此视图不允许更新。(6)若若视视图图定定义义中中有有嵌嵌套套子子查查询询，并并且且嵌嵌套套查查询询的的FROM子子句句中中涉涉及及的的表表也也是是导导出出视视图的基本表，则此视图不允许更新。图的基本表，则此视图不允许更新。CREATE VIEW Good-SCAS SELECT Sno,Cno,GradeFROM SCWHERE Grade=ALL(select Grade FROM SC);(7)一个不允许更新的视图上定义的视图一个不允许更新的视图上定义的视图也是不可更新的。也是不可更新的。3.5.4 视图的作用视图的作用 对视图的一切操作最终也要转换为对基对视图的一切操作最终也要转换为对基本表的操作。而且对于非行列子集视图本表的操作。而且对于非行列子集视图进行查询或更新时还有可能出现问题。进行查询或更新时还有可能出现问题。既然如此，为什么还要定义视图呢？这既然如此，为什么还要定义视图呢？这是因为合理使用视图能够带来许多好处。是因为合理使用视图能够带来许多好处。1视图能够简化用户的操作。视图能够简化用户的操作。(1)(1)视图机制使用户可以将注意力集中在所视图机制使用户可以将注意力集中在所关心的数据上。关心的数据上。(2)(2)视图使用户眼中的数据结构简单、清晰。视图使用户眼中的数据结构简单、清晰。如果查询的数据不是直接来自基本表，如果查询的数据不是直接来自基本表，则可以通过定义视图达到简化数据结构。则可以通过定义视图达到简化数据结构。(3)(3)可以大大简化用户的数据查询操作可以大大简化用户的数据查询操作 。例如，那些定义了若干张表连接的视图，例如，那些定义了若干张表连接的视图，就将表与表之间的连接操作对用户隐蔽就将表与表之间的连接操作对用户隐蔽起来了。换句话说，用户所做的只是对起来了。换句话说，用户所做的只是对一个虚表的简单查询，而这个虚表是怎一个虚表的简单查询，而这个虚表是怎样得来的，用户无需了解。样得来的，用户无需了解。3 3视图提供了一定程度的逻辑独立性。视图提供了一定程度的逻辑独立性。4视图能够对机密数据提供安全保护。视图能够对机密数据提供安全保护。有了视图机制，就可以在设计数据库应用有了视图机制，就可以在设计数据库应用系统时，对不同的用户定义不同的视图，系统时，对不同的用户定义不同的视图，使机密数据不出现在不应看到这些数据的使机密数据不出现在不应看到这些数据的用户视图上，这样视图机制就自动提供了用户视图上，这样视图机制就自动提供了对机密数据的安全保护功能。例如对机密数据的安全保护功能。例如Student表涉及三个系的学生数据，可以表涉及三个系的学生数据，可以在其上建立三个视图，每个视图只包含一在其上建立三个视图，每个视图只包含一个系的学生数据，并只允许每个系的系主个系的学生数据，并只允许每个系的系主任查询自己系的学生视图。任查询自己系的学生视图。作业作业 P149:6、7、8、11、12END
