An Introduction to Database System

An Introduction to Database System
6.2 关系数据库标准语言SQL 1 SQL概述 2 数据定义 3 查询 4 数据更新 5 视图 6 数据控制 7 嵌入式SQL An Introduction to Database System

数据更新 1 插入数据 2 修改数据 3 删除数据 An Introduction to Database System

插入数据两种插入数据方式插入单个元组插入子查询结果 An Introduction to Database System

1. 插入单个元组语句格式 INSERT INTO <表名> [(<属性列1>[，<属性列2 >…)] VALUES (<常量1> [，<常量2>] … ) 功能将新元组插入指定表中。 An Introduction to Database System

插入单个元组（续） [例1] 将一个新学生记录（学号：95020；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）插入到Student表中。 INSERT INTO Student VALUES ('95020'，'陈冬'，'男'，'IS'，18)； An Introduction to Database System

插入单个元组（续） [例2] 插入一条选课记录( '95020'，'1 ')。 INSERT INTO SC(Sno，Cno) VALUES (' '，' 1 ')；新插入的记录在Grade列上取空值 An Introduction to Database System

插入单个元组（续） INTO子句指定要插入数据的表名及属性列属性列的顺序可与表定义中的顺序不一致没有指定属性列：表示要插入的是一条完整的元组，且属性列属性与表定义中的顺序一致指定部分属性列：插入的元组在其余属性列上取空值 VALUES子句提供的值必须与INTO子句匹配值的个数值的类型 An Introduction to Database System

2. 插入子查询结果语句格式 INSERT INTO <表名> [(<属性列1> [，<属性列2>… )] 子查询；功能将子查询结果插入指定表中 An Introduction to Database System

插入子查询结果（续） [例3] 对每一个系，求学生的平均年龄，并把结果存入数据库。第一步：建表 CREATE TABLE Deptage (Sdept CHAR(15) /* 系名*/ Avgage SMALLINT)； /*学生平均年龄*/ An Introduction to Database System

插入子查询结果（续）第二步：插入数据 INSERT INTO Deptage(Sdept，Avgage) SELECT Sdept，AVG(Sage) FROM Student GROUP BY Sdept； An Introduction to Database System

插入子查询结果（续） INTO子句(与插入单条元组类似) 指定要插入数据的表名及属性列属性列的顺序可与表定义中的顺序不一致没有指定属性列：表示要插入的是一条完整的元组指定部分属性列：插入的元组在其余属性列上取空值子查询 SELECT子句目标列必须与INTO子句匹配值的个数值的类型 An Introduction to Database System

插入子查询结果（续） DBMS在执行插入语句时会检查所插元组是否破坏表上已定义的完整性规则实体完整性参照完整性用户定义的完整性对于有NOT NULL约束的属性列是否提供了非空值对于有UNIQUE约束的属性列是否提供了非重复值对于有值域约束的属性列所提供的属性值是否在值域范围内 An Introduction to Database System

修改数据语句格式 UPDATE <表名> SET <列名>=<表达式>[，<列名>=<表达式>]… [WHERE <条件>]；功能修改指定表中满足WHERE子句条件的元组 An Introduction to Database System

修改数据（续）三种修改方式修改某一个元组的值修改多个元组的值带子查询的修改语句 An Introduction to Database System

1. 修改某一个元组的值 [例4] 将学生95001的年龄改为22岁。 UPDATE Student SET Sage=22 WHERE Sno=' '； An Introduction to Database System

2. 修改多个元组的值 [例5] 将所有学生的年龄增加1岁。 UPDATE Student SET Sage= Sage+1； An Introduction to Database System

修改多个元组的值(续) [例6] 将信息系所有学生的年龄增加1岁。 UPDATE Student SET Sage= Sage+1 WHERE Sdept=' IS '； An Introduction to Database System

3. 带子查询的修改语句 [例7] 将计算机科学系全体学生的成绩置零。 UPDATE SC SET Grade=0 WHERE 'CS'= (SELETE Sdept FROM Student WHERE Student.Sno = SC.Sno)； An Introduction to Database System

修改数据（续） SET子句指定修改方式要修改的列修改后取值 WHERE子句指定要修改的元组缺省表示要修改表中的所有元组 An Introduction to Database System

修改数据（续） DBMS在执行修改语句时会检查修改操作是否破坏表上已定义的完整性规则实体完整性主码不允许修改用户定义的完整性 NOT NULL约束 UNIQUE约束值域约束 An Introduction to Database System

删除数据 DELETE FROM <表名> [WHERE <条件>]；功能删除指定表中满足WHERE子句条件的元组 WHERE子句指定要删除的元组缺省表示要修改表中的所有元组 An Introduction to Database System

删除数据（续）三种删除方式删除某一个元组的值删除多个元组的值带子查询的删除语句 An Introduction to Database System

1. 删除某一个元组的值 [例8] 删除学号为95019的学生记录。 DELETE FROM Student WHERE Sno='95019'； An Introduction to Database System

2. 删除多个元组的值 [例9] 删除2号课程的所有选课记录。 DELETE FROM SC； WHERE Cno='2'； [例10] 删除所有的学生选课记录。 An Introduction to Database System

3. 带子查询的删除语句 [例11] 删除计算机科学系所有学生的选课记录。 DELETE FROM SC WHERE 'CS'= (SELETE Sdept FROM Student WHERE Student.Sno=SC.Sno)； An Introduction to Database System

删除数据(续) DBMS在执行插入语句时会检查所插元组是否破坏表上已定义的完整性规则参照完整性不允许删除级联删除 An Introduction to Database System

更新数据与数据一致性 DBMS在执行插入、删除、更新语句时必须保证数据库一致性必须有事务的概念和原子性完整性检查和保证 An Introduction to Database System

6.2 关系数据库标准语言SQL SQL概述数据定义查询数据更新视图数据控制嵌入式SQL An Introduction to Database System

视图视图的特点虚表，是从一个或几个基本表（或视图）导出的表只存放视图的定义，不会出现数据冗余基表中的数据发生变化，从视图中查询出的数据也随之改变 An Introduction to Database System

基于视图的操作查询删除受限更新定义基于该视图的新视图 An Introduction to Database System

1 定义视图 2 查询视图 3 更新视图 4 视图的作用 An Introduction to Database System

1. 建立视图语句格式 CREATE VIEW <视图名> [(<列名> [，<列名>]…)] AS <子查询> [WITH CHECK OPTION]； An Introduction to Database System

建立视图（续） DBMS执行CREATE VIEW语句时只是把视图的定义存入数据字典，并不执行其中的SELECT语句。在对视图查询时，按视图的定义从基本表中将数据查出。 An Introduction to Database System

组成视图的属性列名全部省略或全部指定省略: 由子查询中SELECT目标列中的诸字段组成明确指定视图的所有列名: (1) 某个目标列是集函数或列表达式 (2) 目标列为 * (3) 多表连接时选出了几个同名列作为视图的字段 (4) 需要在视图中为某个列启用新的更合适的名字 An Introduction to Database System

行列子集视图 [例1] 建立信息系学生的视图。 CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student WHERE Sdept= 'IS'；从单个基本表导出只是去掉了基本表的某些行和某些列保留了码 An Introduction to Database System

建立视图（续） WITH CHECK OPTION 透过视图进行增删改操作时，不得破坏视图定义中的谓词条件（即子查询中的条件表达式） An Introduction to Database System

WITH CHECK OPTION的视图 [例2] 建立信息系学生的视图，并要求透过该视图进行的更新操作只涉及信息系学生。 CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student WHERE Sdept= 'IS' WITH CHECK OPTION； An Introduction to Database System

对IS_Student视图的更新操作修改操作：DBMS自动加上Sdept= 'IS'的条件删除操作：DBMS自动加上Sdept= 'IS'的条件插入操作：DBMS自动检查Sdept属性值是否为'IS' 如果不是，则拒绝该插入操作如果没有提供Sdept属性值，则自动定义Sdept为'IS' An Introduction to Database System

基于多个基表的视图 [例4] 建立信息系选修了1号课程的学生视图。 CREATE 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'； An Introduction to Database System

基于视图的视图 [例5] 建立信息系选修了1号课程且成绩在90分以上的学生的视图。 CREATE VIEW IS_S2 AS SELECT Sno，Sname，Grade FROM IS_S1 WHERE Grade>=90； An Introduction to Database System

带表达式的视图 [例6] 定义一个反映学生出生年份的视图。 CREATE VIEW BT_S(Sno，Sname，Sbirth) AS SELECT Sno，Sname，2000-Sage FROM Student 设置一些派生属性列, 也称为虚拟列--Sbirth 带表达式的视图必须明确定义组成视图的各个属性列名 An Introduction to Database System

建立分组视图 [例7] 将学生的学号及他的平均成绩定义为一个视图假设SC表中“成绩”列Grade为数字型 CREAT VIEW S_G(Sno，Gavg) AS SELECT Sno，AVG(Grade) FROM SC GROUP BY Sno； An Introduction to Database System

建立视图（续）一类不易扩充的视图以 SELECT * 方式创建的视图可扩充性差，应尽可能避免 An Introduction to Database System

建立视图（续） [例8]将Student表中所有女生记录定义为一个视图 CREATE VIEW F_Student1(stdnum，name，sex，age，dept) AS SELECT * FROM Student WHERE Ssex='女'；缺点：修改基表Student的结构后，Student表与 F_Student1视图的映象关系被破坏，导致该视图不能正确工作。 An Introduction to Database System

建立视图（续） CREATE VIEW F_Student2 (stdnum，name，sex，age，dept) AS SELECT Sno，Sname，Ssex，Sage，Sdept FROM Student WHERE Ssex='女'；为基表Student增加属性列不会破坏Student表与F_Student2视图的映象关系。 An Introduction to Database System

常见的视图形式行列子集视图 WITH CHECK OPTION的视图基于多个基表的视图基于视图的视图带表达式的视图分组视图 An Introduction to Database System

2. 删除视图 DROP VIEW <视图名>；该语句从数据字典中删除指定的视图定义由该视图导出的其他视图定义仍在数据字典中，但已不能使用，必须显式删除删除基表时，由该基表导出的所有视图定义都必须显式删除 An Introduction to Database System

删除视图(续） [例9] 删除视图IS_S1 DROP VIEW IS_S1； An Introduction to Database System

查询视图从用户角度：查询视图与查询基本表相同 DBMS实现视图查询的方法实体化视图（View Materialization）有效性检查：检查所查询的视图是否存在执行视图定义，将视图临时实体化，生成临时表查询视图转换为查询临时表查询完毕删除被实体化的视图(临时表) new An Introduction to Database System

查询视图（续）视图消解法（View Resolution）进行有效性检查，检查查询的表、视图等是否存在。如果存在，则从数据字典中取出视图的定义把视图定义中的子查询与用户的查询结合起来，转换成等价的对基本表的查询执行修正后的查询 An Introduction to Database System

查询视图（续） [例1] 在信息系学生的视图中找出年龄小于20岁的学生。 SELECT Sno，Sage FROM IS_Student WHERE Sage<20； IS_Student视图的定义 (视图定义例1)： CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student WHERE Sdept= 'IS‘； An Introduction to Database System

查询视图（续）视图实体化法视图消解法转换后的查询语句为： SELECT Sno，Sage FROM Student WHERE Sdept= 'IS' AND Sage<20； An Introduction to Database System

查询视图（续） [例2] 查询信息系选修了1号课程的学生 SELECT Sno，Sname FROM IS_Student，SC WHERE IS_Student.Sno =SC.Sno AND SC.Cno= '1'； An Introduction to Database System

查询视图（续）视图消解法的局限有些情况下，视图消解法不能生成正确查询。采用视图消解法的DBMS会限制这类查询。 An Introduction to Database System

查询视图（续） [例3]在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩 SELECT * FROM S_G WHERE Gavg>=90； S_G视图定义： CREATE VIEW S_G (Sno，Gavg) AS SELECT Sno，AVG(Grade) FROM SC GROUP BY Sno； An Introduction to Database System

查询转换错误： SELECT Sno，AVG(Grade) FROM SC WHERE AVG(Grade)>=90 GROUP BY Sno；正确： SELECT Sno，AVG(Grade) FROM SC GROUP BY Sno HAVING AVG(Grade)>=90； An Introduction to Database System

更新视图用户角度：更新视图与更新基本表相同 DBMS实现视图更新的方法视图实体化法（View Materialization）视图消解法（View Resolution）指定WITH CHECK OPTION子句后 DBMS在更新视图时会进行检查，防止用户通过视图对不属于视图范围内的基本表数据进行更新 An Introduction to Database System

更新视图（续） [例1] 将信息系学生视图IS_Student中学号95002 的学生姓名改为“刘辰”。 UPDATE IS_Student SET Sname= '刘辰' WHERE Sno= '95002'；转换后的语句： UPDATE Student SET Sname= '刘辰' WHERE Sno= '95002' AND Sdept= 'IS'； An Introduction to Database System

更新视图（续） [例2] 向信息系学生视图IS_S中插入一个新的学生记录：95029，赵新，20岁 INSERT INTO IS_Student VALUES(‘95029’，‘赵新’，20)；转换为对基本表的更新： INTO Student(Sno，Sname，Sage，Sdept) VALUES('95029'，'赵新'，20，'IS' )； An Introduction to Database System

更新视图（续） [例3] 删除视图CS_S中学号为95029的记录 DELETE FROM IS_Student WHERE Sno= '95029'；转换为对基本表的更新： FROM Student WHERE Sno= '95029' AND Sdept= 'IS'； An Introduction to Database System

更新视图的限制一些视图是不可更新的，因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的更新(对两类方法均如此) 例：视图S_G为不可更新视图。 CREATE VIEW S_G (Sno，Gavg) AS SELECT Sno，AVG(Grade) FROM SC GROUP BY Sno； An Introduction to Database System

更新视图（续）对于如下更新语句： UPDATE S_G SET Gavg=90 WHERE Sno= '95001'；无论实体化法还是消解法都无法将其转换成对基本表SC的更新 An Introduction to Database System

实际系统对视图更新的限制允许对行列子集视图进行更新对其他类型视图的更新不同系统有不同限制 DB2对视图更新的限制： (1) 若视图是由两个以上基本表导出的，则此视图不允许更新。 (2) 若视图的字段来自字段表达式或常数，则不允许对此视图执行INSERT和UPDATE操作，但允许执行DELETE操作。 An Introduction to Database System

更新视图（续） (3) 若视图的字段来自集函数，则此视图不允许更新。 (4) 若视图定义中含有GROUP BY子句，则此视图不允许更新。 (5) 若视图定义中含有DISTINCT短语，则此视图不允许更新。 (6) 若视图定义中有嵌套查询，并且内层查询的FROM子句中涉及的表也是导出该视图的基本表，则此视图不允许更新。 (7) 一个不允许更新的视图上定义的视图也不允许更新 An Introduction to Database System

更新视图（续）例：视图GOOD_SC(修课成绩在平均成绩之上的元组) CREATE VIEW GOOD_SC AS SELECT Sno，Cno，Grade FROM SC WHERE Grade > (SELECT AVG(Grade) FROM SC)； An Introduction to Database System

视图 1 定义视图 2 查询视图 3 更新视图 4 视图的作用 An Introduction to Database System

1. 视图能够简化用户的操作当视图中数据不是直接来自基本表时，定义视图能够简化用户的操作基于多张表连接形成的视图基于复杂嵌套查询的视图含导出属性的视图 An Introduction to Database System

2. 视图使用户能以多种角度看待同一数据视图机制能使不同用户以不同方式看待同一数据，适应数据库共享的需要 An Introduction to Database System

3.视图对重构数据库提供了一定程度的逻辑独立性
例：数据库逻辑结构发生改变学生关系Student(Sno，Sname，Ssex，Sage，Sdept) “垂直”地分成两个基本表： SX(Sno，Sname，Sage) SY(Sno，Ssex，Sdept) An Introduction to Database System

3.视图对重构数据库提供了一定程度的逻辑独立性
通过建立一个视图Student： CREATE VIEW Student(Sno，Sname，Ssex，Sage，Sdept) AS SELECT SX.Sno，SX.Sname，SY.Ssex，SX.Sage，SY.Sdept FROM SX，SY WHERE SX.Sno=SY.Sno；使用户的外模式保持不变，从而对原Student表的查询程序不必修改 An Introduction to Database System

3. 视图对重构数据库提供了一定程度的逻辑独立性
物理独立性与逻辑独立性的概念视图在一定程度上保证了数据的逻辑独立性视图只能在一定程度上提供数据的逻辑独立性由于对视图的更新是有条件的，因此应用程序中修改数据的语句可能仍会因基本表结构的改变而改变。 An Introduction to Database System

4. 视图能够对机密数据提供安全保护对不同用户定义不同视图，使每个用户只能看到他有权看到的数据通过WITH CHECK OPTION对关键数据定义操作时间限制 An Introduction to Database System

建立视图（续） [例3 ] 建立1号课程的选课视图，并要求透过该视图进行的更新操作只涉及1号课程，同时对该视图的任何操作只能在工作时间进行。 CREATE VIEW IS_SC AS SELECT Sno，Cno，Grade FROM SC WHERE Cno= '1' AND TO_CHAR(SYSDATE,'HH24') BETWEEN 9 AND 17 AND TO_CHAR(SYSDATE,'D') BETWEEN 2 AND 6 WITH CHECK OPTION; new An Introduction to Database System

6.2 关系数据库标准语言SQL 1 SQL概述 2 数据定义 3 查询 4 数据更新 5 视图 6 数据控制 7 嵌入式SQL An Introduction to Database System

6.2.6 数据控制 1 概述 2 授权 3 收回权限 An Introduction to Database System

概述数据控制亦称为数据保护，包括数据的：安全性控制完整性控制并发控制恢复 An Introduction to Database System

SQL语言的数据控制功能 SQL语言提供了数据控制功能，能够在一定程度上保证数据库中数据的完全性、完整性，并提供了一定的并发控制及恢复能力。 An Introduction to Database System

1. 完整性数据库的完整性是指数据库中数据的正确性与相容性。 SQL语言定义完整性约束条件 CREATE TABLE语句 ALTER TABLE语句码取值唯一的列参照完整性其他约束条件 An Introduction to Database System

2. 并发控制并发控制: 当多个用户并发地对数据库进行操作时，对他们加以控制、协调，以保证并发操作正确执行，保持数据库的一致性。 SQL语言并发控制能力：提供事务、事务开始、事务结束、提交等概念 An Introduction to Database System

3. 恢复恢复: 当发生各种类型的故障导致数据库处于不一致状态时，将数据库恢复到一致状态的功能。 SQL语言恢复功能：提供事务回滚、重做等概念（UNDO、REDO） An Introduction to Database System

4. 安全性安全性：保护数据库，防止不合法的使用所造成的数据泄露和破坏。保证数据安全性的主要措施存取控制：控制用户只能存取他有权存取的数据规定不同用户对于不同数据对象所允许执行的操作 An Introduction to Database System

DBMS实现数据安全性保护的过程用户或DBA把授权决定告知系统 SQL的GRANT和REVOKE DBMS把授权的结果存入数据字典当用户提出操作请求时，DBMS根据授权定义进行检查，以决定是否执行操作请求 An Introduction to Database System

安全性(续) 谁定义？ DBA和表的建立者（即表的属主）如何定义？ SQL语句： GRANT REVOKE An Introduction to Database System

授权 GRANT语句的一般格式： GRANT <权限>[,<权限>]... [ON <对象类型> <对象名>] TO <用户>[,<用户>]... [WITH GRANT OPTION]; 谁定义？DBA和表的建立者（即表的属主） REVOKE功能：将对指定操作对象的指定操作权限授予指定的用户。 An Introduction to Database System

(1) 操作权限 An Introduction to Database System

(2) 用户的权限建表（CREATETAB）的权限:属于DBA DBA授予-->普通用户基本表或视图的属主拥有对该表或视图的一切操作权限接受权限的用户: 一个或多个具体用户 PUBLIC（全体用户） An Introduction to Database System

(4) WITH GRANT OPTION子句指定了WITH GRANT OPTION子句: 获得某种权限的用户还可以把这种权限再授予别的用户。没有指定WITH GRANT OPTION子句: 获得某种权限的用户只能使用该权限，不能传播该权限 An Introduction to Database System

例题例1 把查询Student表权限授给用户U1 GRANT SELECT ON TABLE Student TO U1; An Introduction to Database System

例题（续）例2 把对Student表和Course表的全部权限授予用户U2和U3 GRANT ALL PRIVILIGES ON TABLE Student, Course TO U2, U3; 例4 An Introduction to Database System

例题（续）例3 把对表SC的查询权限授予所有用户 GRANT SELECT ON TABLE SC TO PUBLIC; An Introduction to Database System

例题（续）例4 把查询Student表和修改学生学号的权限授给用户U4 GRANT UPDATE(Sno), SELECT ON TABLE Student TO U4; An Introduction to Database System

例题（续）例5 把对表SC的INSERT权限授予U5用户，并允许他再将此权限授予其他用户 GRANT INSERT ON TABLE SC TO U5 WITH GRANT OPTION; An Introduction to Database System

传播权限执行例5后，U5不仅拥有了对表SC的INSERT权限，还可以传播此权限： GRANT INSERT ON TABLE SC TO U6 WITH GRANT OPTION; 同样，U6还可以将此权限授予U7： GRANT INSERT ON TABLE SC TO U7; 但U7不能再传播此权限。 U5--> U6--> U7 An Introduction to Database System

例题（续）例6 DBA把在数据库S_C中建立表的权限授予用户U8 GRANT CREATETAB ON DATABASE S_C TO U8; An Introduction to Database System

6.2.6 数据控制 1. 概述 2 授权 3 收回权限 An Introduction to Database System

SQL收回权限的功能 REVOKE语句的一般格式为： REVOKE <权限>[,<权限>]... [ON <对象类型> <对象名>] FROM <用户>[,<用户>]...; 功能：从指定用户那里收回对指定对象的指定权限 An Introduction to Database System

例题例7 把用户U4修改学生学号的权限收回 REVOKE UPDATE(Sno) ON TABLE Student FROM U4; An Introduction to Database System

例题（续）例8 收回所有用户对表SC的查询权限 REVOKE SELECT ON TABLE SC FROM PUBLIC; An Introduction to Database System

例题（续）例9 把用户U5对SC表的INSERT权限收回 REVOKE INSERT ON TABLE SC FROM U5; An Introduction to Database System

权限的级联回收系统将收回直接或间接从U5处获得的对SC 表的INSERT权限: -->U5--> U6--> U7 收回U5、U6、U7获得的对SC表的INSERT 权限: <--U5<-- U6<-- U7 An Introduction to Database System

小结: SQL灵活的授权机制 DBA拥有对数据库中所有对象的所有权限，并可以根据应用的需要将不同的权限授予不同的用户。用户对自己建立的基本表和视图拥有全部的操作权限，并且可以用GRANT语句把其中某些权限授予其他用户。被授权的用户如果有“继续授权”的许可，还可以把获得的权限再授予其他用户。所有授予出去的权力在必要时又都可以用REVOKE语句收回。 An Introduction to Database System

下课了。。。追求休息一会儿。。。 An Introduction to Database System

An Introduction to Database System

Similar presentations

Presentation on theme: "An Introduction to Database System"— Presentation transcript:

Similar presentations

About project

反馈

请登录

Auth with social network:

An Introduction to Database System

Similar presentations

Presentation on theme: "An Introduction to Database System"— Presentation transcript:

Similar presentations

About project

反馈