3、关系数据库标准语言SQL(2).ppt

1、1,数据库系统概论 An Introduction to Database System,2,第三章 关系数据库标准语言SQL,3,3,第三章 关系数据库标准语言SQL,3.1 SQL概述 3.2 数据定义 3.3 查询 3.4 数据更新 3.5 视图 3.6 数据控制 3.7 小结,4,4,3.4 数 据 更 新,3.4.1 插入数据 3.4.2 修改数据 3.4.3 删除数据,5,5,3.4.1 插入数据,两种插入数据方式 插入单个元组 插入子查询结果,6,6,1. 插入单个元组,语句格式 INSERT INTO (，) VALUES ( ， ) 功能 将新元组插入指定表中。,7,7,插入

2、单个元组（续）,INTO子句 指定要插入数据的表名及属性列 属性列的顺序可与表定义中的顺序不一致 没有指定属性列：表示要插入的是一条完整的元组，且属性列属性与表定义中的顺序一致 指定部分属性列：插入的元组在其余属性列上取空值 VALUES子句: 提供的值必须与INTO子句匹配 值的个数 值的类型,8,8,插入单个元组（续）,例1 将一个新学生记录（学号：95020；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）插入到Student表中。 INSERT INTO Student VALUES (95020，陈冬，男，IS，18)；,9,9,插入单个元组（续）,例2 插入一条选课记录( 950

3、20，1 )。 INSERT INTO SC(Sno，Cno) VALUES ( 95020 ， 1 )； 新插入的记录在Grade列上取空值,10,10,2. 插入子查询结果,语句格式 INSERT INTO ( ， ) 子查询； 功能 将子查询结果插入指定表中,11,11,插入子查询结果（续）,例3 对每一个系，求学生的平均年龄，并把结果存入数据库。 第一步：建表 CREATE TABLE Deptage (Sdept CHAR(15)， /* 系名*/ Avgage SMALLINT)； /*学生平均年龄*/,12,12,插入子查询结果（续）,第二步：插入数据 INSERT INTO D

4、eptage(Sdept，Avgage) SELECT Sdept，AVG(Sage) FROM Student GROUP BY Sdept；,13,13,3.4 数 据 更 新,3.4.1 插入数据 3.4.2 修改数据 3.4.3 删除数据,14,14,3.4.2 修改数据,语句格式 UPDATE SET =，= WHERE ； 功能 修改指定表中满足WHERE子句条件的元组,15,15,修改数据（续）,SET子句 指定修改方式 要修改的列 修改后取值 WHERE子句 指定要修改的元组 缺省表示要修改表中的所有元组,16,16,修改数据（续）,三种修改方式 修改某一个元组的值 修改多个元

5、组的值 带子查询的修改语句,17,17,1. 修改某一个元组的值,例4 将学生95001的年龄改为22岁。 UPDATE Student SET Sage=22 WHERE Sno= 95001 ；,18,18,2. 修改多个元组的值,例5 将所有学生的年龄增加1岁。 UPDATE Student SET Sage= Sage+1；,19,19,修改多个元组的值(续),例6 将信息系所有学生的年龄增加1岁。 UPDATE Student SET Sage= Sage+1 WHERE Sdept= IS ；,20,20,3. 带子查询的修改语句,例7 将计算机科学系全体学生的成绩置零。 UPDA

6、TE SC SET Grade=0 WHERE CS= (SELETE Sdept FROM Student WHERE Student.Sno = SC.Sno)；,相关子查询,常量=（子查询）,21,21,3. 带子查询的修改语句,例7 将计算机科学系全体学生的成绩置零。 UPDATE SC SET Grade=0 WHERE Sno in (SELETE Sno FROM Student WHERE Sdept=CS)；,不相关子查询， 经常使用,22,22,3.4 数 据 更 新,3.4.1 插入数据 3.4.2 修改数据 3.4.3 删除数据,23,23,3.4.3 删除数据,语句格

7、式 DELETE FROM WHERE ； 功能 删除指定表中满足WHERE子句条件的元组 DELETE语句删除的是表中的数据，不是表的定义 WHERE子句 指定要删除的元组 缺省表示要删除表中的所有元组,24,24,删除数据（续）,三种删除方式 删除某一个元组的值 删除多个元组的值 带子查询的删除语句,25,25,1. 删除某一个元组的值,例8 删除学号为95019的学生记录。 DELETE FROM Student WHERE Sno=95019；,26,26,2. 删除多个元组的值,例9 删除2号课程的所有选课记录。 DELETE FROM SC WHERE Cno=2； 例10 删除所

8、有的学生选课记录。 DELETE FROM SC；,27,27,3. 带子查询的删除语句,例11 删除计算机科学系所有学生的选课记录。 DELETE FROM SC WHERE CS= (SELETE Sdept FROM Student WHERE Student.Sno=SC.Sno)；,相关子查询,常量=（子查询）,28,28,3. 带子查询的删除语句,例11 删除计算机科学系所有学生的选课记录。 DELETE FROM SC WHERE Sno in (SELETE Sno FROM Student WHERE Sdept=CS)；,不相关子查询，常用,29,29,第三章 关系数据库标

9、准语言SQL,3.1 SQL概述 3.2 数据定义 3.3 查询 3.4 数据更新 3.5 视图 3.6 数据控制 3.7 小结,30,30,3.5 视 图,视图的特点 视图是原始数据库数据的一种变换，是查看表中数据 的另外一种方式； 可将视图看成是一个移动的窗口，通过它可看到感兴 趣的数据； 虚表，是从一个或几个基本表（或视图）导出的表 数据库中只存放视图的定义，数据仍存放在原来的基表中 基表中的数据发生变化，从视图中查询出的数据也随之改变,31,31,3.5 视 图,3.5.1 定义视图 3.5.2 查询视图 3.5.3 更新视图 3.5.4 视图的作用,32,32,3.5.1 建立视图,

10、语句格式 CREATE VIEW ( ,) AS WITH CHECK OPTION； 子查询不允许含有ORDER BY子句和DISTINCT短语,33,33,建立视图（续）,DBMS执行CREATE VIEW语句时只是把视图的定义存入数据字典，并不执行其中的SELECT语句。 在对视图查询时，按视图的定义从基本表中将数据查出。,34,34,组成视图的属性列名,全部省略或全部指定 省略: 由子查询中SELECT目标列中的诸字段组成 明确指定视图的所有列名: (1) 某个目标列是集函数或列表达式 (2) 多表连接时选出了几个同名列作为视图的字段 (3) 需要在视图中为某个列启用新的名字,35,3

11、5,行列子集视图,例1 建立信息系学生的视图。 CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student WHERE Sdept= IS； 从单个基本表导出，只是去掉了基本表的某些行和某些列，保留了码，称这类视图为行列子集视图。,省略了视图IS_Student的列名,36,36,建立视图（续）,WITH CHECK OPTION 透过视图进行增删改操作时，不得破坏视图定义中的谓词条件（即子查询中的条件表达式）,37,37,WITH CHECK OPTION的视图,例2 建立信息系学生的视图，并要求透过该视图进行的更新操作只涉及信息

12、系学生。 CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student WHERE Sdept= IS WITH CHECK OPTION；,38,38,对IS_Student视图的更新操作,修改操作：DBMS自动加上Sdept= IS的条件 删除操作：DBMS自动加上Sdept= IS的条件 插入操作：DBMS自动检查Sdept属性值是否为IS 如果不是，则拒绝该插入操作 如果没有提供Sdept属性值，则自动定义Sdept为IS,39,39,基于多个基表的视图,例3 建立信息系选修了1号课程的学生视图。 CREATE VIEW IS

13、_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；,由于视图IS_S1的属性列中包含了Student表与SC表中的同名列Sno，所以必须明确指定视图的所有列名。,40,40,基于视图的视图,例4 建立信息系选修了1号课程且成绩在90分以上的学生的视图。 CREATE VIEW IS_S2 AS SELECT Sno，Sname，Grade FROM IS_S1 WHERE Grade=90；,41,

14、41,带表达式的视图,例5 定义一个反映学生出生年份的视图。 CREATE VIEW BT_S(Sno，Sname，Sbirth) AS SELECT Sno，Sname，2010-Sage FROM Student 设置一些派生属性列, 也称为虚拟列-Sbirth 带表达式的视图必须明确定义组成视图的各个属性列名,42,42,建立分组视图,例6 将学生的学号及他的平均成绩定义为一个视图 CREAT VIEW S_G(Sno，Gavg) AS SELECT Sno，AVG(Grade) FROM SC GROUP BY Sno； 带有集函数和GROUP BY子句的查询定义的视图称为分组视图。,

15、由于select后的目标列中有集函数，所以必须明确指定视图的所有列名。,43,43,建立视图（续）,例7将Student表中所有女生记录定义为一个视图 CREATE VIEW F_Student1(stdnum，name，sex，age，dept) AS SELECT * FROM Student WHERE Ssex=女；,由于在视图中为列指定了新的名字，所以必须明确指定视图的所有列名 。,44,44,建立视图（续）,以 SELECT * 方式创建的视图可扩充性差，应尽可能避免,45,45,常见的视图形式,行列子集视图 WITH CHECK OPTION的视图 基于多个基表的视图 基于视图的

16、视图 带表达式的视图 分组视图,46,46,2. 删除视图,DROP VIEW ； 该语句从数据字典中删除指定的视图定义 由该视图导出的其他视图定义仍在数据字典中，但已不能使用，必须显式删除 删除基表时，由该基表导出的所有视图定义都必须显式删除,47,47,删除视图(续）,例8 删除视图IS_S1 DROP VIEW IS_S1； 执行此语句后，IS_S1视图的定义将从数据字典中删除。由IS_S1视图导出IS_S2视图的定义虽然仍在数据字典中，但是该视图已无法使用了，因此应该同时删除。,48,48,3.5 视 图,3.5.1 定义视图 3.5.2 查询视图 3.5.3 更新视图 3.5.4 视

17、图的作用,49,49,3.5.2 查询视图,从用户角度：查询视图与查询基本表相同 DBMS实现视图查询的方法 视图消解法（View Resolution） 进行有效性检查 把视图定义中的子查询与用户的查询结合起来，转换成等价的对基本表的查询 执行修正后的查询,50,50,查询视图（续）,例1 在信息系学生的视图中找出年龄小于20岁的学生。 SELECT Sno，Sage FROM IS_Student WHERE Sage=90 GROUP BY Sno；,WHERE子句中不能用集函数作为条件表达式,正确： SELECT Sno，AVG(Grade) FROM SC GROUP BY Sno

18、HAVING AVG(Grade)=90；,这类查询应该对基本表进行,56,56,查询转换,目前，多数关系数据库系统对行列子集视图的查询均能进行正确转换。但对非行列子集视图的查询就不一定能转换了，因此这类查询应该直接对基本表进行（如例3）。,57,57,3.5 视 图,3.5.1 定义视图 3.5.2 查询视图 3.5.3 更新视图 3.5.4 视图的作用,58,58,3.5.3 更新视图,用户角度：更新视图与更新基本表相同 DBMS实现视图更新的方法：由于视图是不实际存储数据的虚表，因此对视图的更新，最终要转换为对基本表的更新。 定义视图时，指定WITH CHECK OPTION子句后，DB

19、MS在更新视图时会进行检查，防止用户通过视图对不属于视图范围内的基本表数据进行更新,59,59,更新视图（续）,例1 将信息系学生视图IS_Student中学号95002的学生姓名改为“刘辰”。 UPDATE IS_Student SET Sname= 刘辰 WHERE Sno= 95002； 转换后的语句： UPDATE Student SET Sname= 刘辰 WHERE Sno= 95002 AND Sdept= IS；,60,60,更新视图（续）,例2 向信息系学生视图IS_Student中插入一个新的学生记录：95029，赵新，20岁 INSERT INTO IS_Student

20、VALUES(95029，赵新，20)； 转换为对基本表的更新： INSERT INTO Student(Sno，Sname，Sage) VALUES(95029，赵新，20 )； 插入的数据并不在视图的范围之内。,61,61,更新视图（续）,为了防止这种情况发生，可以使用with check option子句对插入或更改的数据进行限制。 转换为对基本表的更新： INSERT INTO Student(Sno，Sname，Sage，Sdept) VALUES(95029，赵新，20，IS )；,系统自动将系名IS放入VALUES子句中,62,62,更新视图（续）,例3 删除视图IS_Stude

21、nt中学号为95029的记录 DELETE FROM IS_Student WHERE Sno= 95029； 转换为对基本表的更新： DELETE FROM Student WHERE Sno= 95029 AND Sdept= IS；,63,63,更新视图的限制,一些视图是不可更新的，因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的更新 例：视图S_G为不可更新视图。,CREATE VIEW S_G (Sno，Gavg) AS SELECT Sno，AVG(Grade) FROM SC GROUP BY Sno；,64,64,更新视图（续）,对于如下更新语句： UPDATE S_

22、G SET Gavg=90 WHERE Sno= 95001； 无法将其转换成对基本表SC的更新。因为系统无法修改各科成绩，以使平均成绩成为90。所以S_G视图是不可更新的。,65,65,实际系统对视图更新的限制,允许对行列子集视图进行更新 对其他类型视图的更新不同系统有不同限制,66,66,3.5 视 图,3.5.1 定义视图 3.5.2 查询视图 3.5.3 更新视图 3.5.4 视图的作用,67,67,3.5.4 视图的作用,1. 视图能够简化用户的操作 当视图中数据不是直接来自基本表时，定义视图能够简化用户的操作 2. 视图使用户能以多种角度看待同一数据 3.视图对重构数据库提供了一定

23、程度的逻辑独立性,68,68,3.视图对重构数据库提供了一定程度的逻辑独立性,例：数据库逻辑结构发生改变 学生关系Student(Sno，Sname，Ssex，Sage，Sdept) “垂直”地分成两个基本表： SX(Sno，Sname，Sage) SY(Sno，Ssex，Sdept),69,69,通过建立一个视图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； 使用户

24、的外模式保持不变，从而对原Student表的查询程序不必修改,3.视图对重构数据库提供了一定程度的逻辑独立性,70,70,4. 视图能够对机密数据提供安全保护 对不同用户定义不同视图，使每个用户只能看到他有权看到的数据 5. 适当的利用视图可以更清晰的表达查询,3.5.4 视图的作用,71,71,利用视图可以更清晰的表达查询,例如经常需要执行查询“对每个同学找出他获得最高成绩的课程号”. SELECT sno, cno FROM sc x WHERE grade = (SELECT MAX(grade) FROM sc y WHERE y.sno = x.sno),72,72,利用视图可以更清

25、晰的表达查询,可以先定义一个视图,求出每个同学获得的最高成绩: Create view maxgrade as select sno,max(grade) mgrade from sc group by sno,73,73,利用视图可以更清晰的表达查询,然后执行查询 Select sc.sno,cno From sc,maxgrade Where sc.sno=maxgrade.sno and sc.grade=maxgrade.mgrade,74,74,第三章 关系数据库标准语言SQL,3.1 SQL概述 3.2 数据定义 3.3 查询 3.4 数据更新 3.5 视图 3.6 数据控制(P1

26、37“4.2.4授权与回收”) 3.7 小结,75,75,3.6 数据控制,概述 3.6.1 授权 3.6.2 收回权限 小结,76,76,概述,数据控制包括数据的： 安全性控制 完整性控制 并发控制 恢复,这里主要讨论SQL语言的安全性控制功能。,77,77,安全性,安全性：保护数据库，防止不合法的使用所造成的数据泄露和破坏。 保证数据安全性的主要措施 存取控制：控制用户只能存取他有权存取的数据; 规定不同用户对于不同数据对象所允许执行的操作.,78,78,DBMS实现数据安全性保护的过程,用户或DBA把授权决定告知系统 由SQL的GRANT和REVOKE语句来完成 DBMS把授权的结果存入

27、数据字典(系统中各类数据描述的集合) 当用户提出操作请求时，DBMS根据授权定义进行检查，以决定是否执行操作请求,79,79,安全性(续),谁定义？ DBA和表的建立者（即表的属主） 如何定义？ SQL语句： GRANT REVOKE,80,80,3.6.1 授 权,GRANT语句的一般格式： GRANT ,. ON TO ,. WITH GRANT OPTION; GRANT功能：将对指定操作对象的指定操作权限授予指定的用户。,81,81,(1) 操作权限,允许用户引用本表的列作为外键约束,82,82,(2) 用户的权限,发出GRANT： DBA 数据库对象创建者（即属主Owner） 拥有该

28、权限的用户 按受权限的用户 一个或多个具体用户 PUBLIC（全体用户）,83,83,(3) WITH GRANT OPTION子句,指定了WITH GRANT OPTION子句: 获得某种权限的用户还可以把这种权限再授予别的用户。 没有指定WITH GRANT OPTION子句: 获得某种权限的用户只能使用该权限，不能传播该权限。 不允许循环授权，即被授权者不能把权限再授回给授权者或其祖先。,84,84,例题,例1 把查询Student表权限授给用户U1 GRANT SELECT ON TABLE Student TO U1;,85,85,例题（续）,例2 把对Student表和Course

29、表的全部权限授予用户U2和U3 GRANT ALL PRIVILEGES ON TABLE Student, Course TO U2, U3;,86,86,例题（续）,例3 把对表SC的查询权限授予所有用户 GRANT SELECT ON TABLE SC TO PUBLIC;,87,87,例题（续）,例4 把查询Student表和修改学生学号的权限授给用户U4 GRANT UPDATE(Sno), SELECT ON TABLE Student TO U4; 对属性列的授权时必须明确指出相应属性列名,88,88,例题（续）,例5 把对表SC的INSERT权限授予U5用户，并允许他再将此权限

30、授予其他用户 GRANT INSERT ON TABLE SC TO U5 WITH GRANT OPTION;,89,89,传播权限,执行例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,90,90,传播权限（续）,下表是执行了上述语句后，学生-课程数据库中的用户权限定义表,91,91,3.6 数据控制,概述 3.6.1 授权

31、3.6.2 收回权限 小结,92,92,3.6.2 SQL收回权限的功能,REVOKE语句的一般格式为： REVOKE ,. ON FROM ,.; 功能：从指定用户那里收回对指定对象的指定权限,93,93,例题,例6 把用户U4修改学生学号的权限收回 REVOKE UPDATE(Sno) ON TABLE Student FROM U4;,94,94,例题（续）,例7 收回所有用户对表SC的查询权限 REVOKE SELECT ON TABLE SC FROM PUBLIC;,95,95,例题（续）,例8 把用户U5对SC表的INSERT权限收回 REVOKE INSERT ON TABLE

32、 SC FROM U5 CASCADE;,96,96,权限的级联回收,执行例8后，系统将收回直接或间接从U5处获得的对SC表的INSERT权限: -U5- U6- U7,97,97,小结:SQL灵活的授权机制,DBA拥有对数据库中所有对象的所有权限，并可以根据应用的需要将不同的权限授予不同的用户。 用户对自己建立的基本表和视图拥有全部的操作权限，并且可以用GRANT语句把其中某些权限授予其他用户。 被授权的用户如果有“继续授权”的许可，还可以把获得的权限再授予其他用户。 所有授予出去的权力在必要时又都可以用REVOKE语句收回。,98,98,第三章 关系数据库标准语言SQL,3.1 SQL概述

33、 3.2 数据定义 3.3 查询 3.4 数据更新 3.5 视图 3.6 数据控制 3.7 小结,99,99,3.8 小 结,SQL的特点 综合统一 高度非过程化 面向集合的操作方式 同一种语法结构提供两种使用方式 语言简捷，易学易用,100,100,小结（续）,交互式SQL 数据定义 查询 数据更新 数据控制,101,101,小结（续）,102,102,1.SQL语言是（ ） A.高级语言 B.宿主语言 C.汇编语言 D.非过程化语言 2.在SQL语言查询语句中，SELECT子句实现关系代数的（ ） A.投影运算 B.选择运算 C.连接运算 D.交运算 3.在SQL语言查询语句中，WHERE子句实现关系代数的（ ） A.投影运算 B.选择运算 C.连接运算 D.交运算,练习,103,103,4.为在查询结果中去掉重复元组，要使用保留字（ ） A.UNIQUE B.COUNT C.DISTINCT D.UNION 5.SQL语言中，下列涉及空值的操作，不正确的是（ ） A.AGE IS NULL B.AGE I