软件技术基础数据库技术.ppt

关系数据库语言SQL（续2）,概述 SQL的数据定义 SQL的数据操纵 SQL的视图定义 SQL的数据控制,3.5 SQL的数据控制(1 of 8),SQL的数据控制也叫数据保护。包括数据的安全性、完整性、并发控制和恢复功能。 数据库的安全性是指保护数据库，防止不合法的使用所造成的数据泄露和破坏。 SQL语言为定义和回收存取控制权利提供了手段。 具体办法：进行存取控制，即规定不同用户对于不同数据对象所允许执行的操作，并控制各用户只能存取他有权存取的数据。,授权机制(2 of 8),授权机制:大的DBMS中有一个超级用户DBA，其他用户能否存在、对某类数据具有何种操作权力是由DBA决定的，系统提供授权机制。 执行过程： （1）用数据控制语言把授权决定告知系统； （2）系统把授权的结果存入数据字典； （3）当用户提出 操作请求时，系统根据授权情况进行检查， 以决定是否执行。,例如：SYBASE系统中，有 四类用户: 三级管理：,系统管理员SA,数据库对象属主,一般用户,授权某用户建库,可再授权某用户建立数据库对象,可再授权某用户对数据库对象的访问,（根据权限对数据库对象进行操作）,授权机制(3 of 8),数据库属主,授权机制(4 of 8),又如：SQL Server通过角色将用户分为不同的类： 固定服务器角色 固定数据库角色,权力的授予与收回(5 of 8),授予：,GRANT 权力 ，权力 ON 对象类型 对象名 TO 用户名 ，用户名 WITH GRANT OPTION ；,若干权力,操作对象,获得权力的用户,有此项，被授权用户可再授权给其他用户,收回：,REVOKE 权力 ，权力 ON 对象类型 对象名 FROM 用户名 ，用户名 ；,谁定义？DBA和表的建立者（即表的属主）,SQL中的用户权限(6 of 8),Example (7 of 8),例1：把查询学生表的权限授予给用户U1,例2：把对Student和Course表的全部权限授予给用户U2和U3.,GRANT ALL PRIVILEGES ON TABLE Student, Course TO U2,U3,例3：把查询Student表和修改学生学号的权限授予给用户U4.,GRANT UPDATE (Sno), SELECT TABLE ON Student TO U4,例4：把对SC表的INSERT权限授予给用户U5，并允许他再将此权限授予给其他用户。,GRANT INSERT ON TABLE SC TO U5 WITH GRANT OPTION,例5：把U4对Student表修改学生学号的权限收回.,REVOKE UPDATE (Sno) ON TABLE Student FROM U4,GRANT SELECT ON TABLE Student TO U1,SQL的安全性控制(8 of 8),数据库的属主DBA、表的属主以及其他数据库对象的属主在他创建的对象上具有一切可能的权力，其他用户得不到主人的授权就不能在该对象上操作； SQL的授权机制十分灵活，各种系统又作了适当的补充， 使用十分方便； SQL的安全性控制除了上述授权机制外，还可设置口令进一步保密。,SQL应用举例,应用：数据库中有三个关系（基本表）： S(S#, SNAME, AGE, SEX) SC(S#, C#, GRADE) C(C#, CNAME, TEACHER) 试用SQL语句表达下列查询和更新语句。,思考中,1、检索学习课程为C2的学生学号与成绩,SELECT S#, GRADE FROM SC WHERE C#=C2,SQL应用,S(S#, SNAME, AGE, SEX) SC(S#, C#, GRADE) C(C#, CNAME, TEACHER),2、检索学习课程为C2的学生学号与姓名,方法一：联接查询 SELECT S#, SNAME FROM S, SC WHERE S.S#=SC.S# AND C#=C2,SQL应用,S(S#, SNAME, AGE, SEX) SC(S#, C#, GRADE) C(C#, CNAME, TEACHER),2、检索学习课程为C2的学生学号与姓名,方法二：嵌套查询 SELECT S#, SNAME FROM S WHERE S# IN (SELECT S# FROM SC WHERE C#=C2),SQL应用,S(S#, SNAME, AGE, SEX) SC(S#, C#, GRADE) C(C#, CNAME, TEACHER),2、检索学习课程为C2的学生学号与姓名,方法三：嵌套查询 SELECT S#, SNAME FROM S WHERE C2 IN (SELECT C# FROM SC WHERE S#=S.S#),“相关子查询”,子查询中查询条件依赖于外层查询中的某个值,SQL应用,S(S#, SNAME, AGE, SEX) SC(S#, C#, GRADE) C(C#, CNAME, TEACHER),2、检索学习课程为C2的学生学号与姓名,方法四：存在量词 SELECT S#, SNAME FROM S WHERE EXISTS (SELECT * FROM SC WHERE SC.S#=S.S# AND C#=C2),SQL应用,S(S#, SNAME, AGE, SEX) SC(S#, C#, GRADE) C(C#, CNAME, TEACHER), (内层查询结果非空),3、检索不学课程C2的学生学号与姓名,方法一：存在量词 SELECT S#, SNAME FROM S WHERE NOT EXISTS (SELECT * FROM SC WHERE SC.S#=S.S# AND C#=C2),SQL应用,S(S#, SNAME, AGE, SEX) SC(S#, C#, GRADE) C(C#, CNAME, TEACHER),3、检索不学课程C2的学生学号与姓名,方法二：嵌套查询 SELECT S#, SNAME FROM S WHERE S# NOT IN (SELECT S# FROM SC WHERE C#=C2),SQL应用,S(S#, SNAME, AGE, SEX) SC(S#, C#, GRADE) C(C#, CNAME, TEACHER),4、检索学习课程名为Maths的学生学号与姓名,方法一：多层嵌套 SELECT S#, SNAME FROM S WHERE S# IN (SELECT S# FROM SC WHERE C# IN (SELECT C# FROM C WHERE CNAME=Mathes),SQL应用,S(S#, SNAME, AGE, SEX) SC(S#, C#, GRADE) C(C#, CNAME, TEACHER),4、检索学习课程名为Maths的学生学号与姓名,方法二：联接查询 SELECT S.S#, SNAME FROM S, SC, C WHERE S.S#=SC.S# AND SC.C#=C.C# AND CNAME=Maths,SQL应用,S(S#, SNAME, AGE, SEX) SC(S#, C#, GRADE) C(C#, CNAME, TEACHER),5、检索学习课程号为C2或C4的学生学号,SELECT S# FROM SC WHERE C#=C2 OR C#=C4,SQL应用,S(S#, SNAME, AGE, SEX) SC(S#, C#, GRADE) C(C#, CNAME, TEACHER),6、检索至少学习课程号为C2和C4的学生学号,SELECT X.S# FROM SC AS X, SC AS Y WHERE X.S#=Y.S# AND X.C#=C2 AND Y.C#=C4,同一关系SC在一层中出现两次，分别引入别名X和Y,SQL应用,S(S#, SNAME, AGE, SEX) SC(S#, C#, GRADE) C(C#, CNAME, TEACHER),7、检索学习全部课程的学生姓名。,SELECT SNAME FROM S WHERE NOT EXISTS (SELECT * FROM C WHERE NOT EXISTS (SELECT * FROM SC WHERE SC.S#=S.S# AND SC.C#=C.C#),SQL应用,S(S#, SNAME, AGE, SEX) SC(S#, C#, GRADE) C(C#, CNAME, TEACHER),8、检索所学课程包含学生S3所学课程的学生学号,SELECT DISTINCT S# FROM SC AS X WHERE NOT EXISTS (SELECT * FROM SC AS Y WHERE Y.S#=S3 AND NOT EXISTS (SELECT * FROM SC AS Z WHERE Z.S#=X.S# AND Z.C#=Y.C#),SQL应用,S(S#, SNAME, AGE, SEX) SC(S#, C#, GRADE) C(C#, CNAME, TEACHER),9、统计每一年龄选修课程的学生人数,SELECT AGE, COUNT(DISTINCT S.S#) FROM S, SC WHERE S.S#=SC.S# GROUP BY AGE,SQL应用,S(S#, SNAME, AGE, SEX) SC(S#, C#, GRADE) C(C#, CNAME, TEACHER),10、检索平均成绩最高的学生学号,SELECT S# FROM SC GROUP BY S# HAVING AVG（GRADE） =ALL （ SELECT AVG（GRADE） FROM SC GROUP BY S#）,SQL应用,S(S#, SNAME, AGE, SEX) SC(S#, C#, GRADE) C(C#, CNAME, TEACHER),SQL中，不允许对集函数进行复合运算因此 不能写成”SELECT MAX(AVG(GRADE)”形式,11、检索至少有一门成绩超过学生S4 一门成绩的学生 学号。,SELECT DISTINCT S# FROM SC WHERE GRADE ANY （ SELECT GRADE FROM SC WHERE S#=S4,SQL应用,S(S#, SNAME, AGE, SEX) SC(S#, C#, GRADE) C(C#, CNAME, TEACHER),12、求男同学每一年龄组（超过50人）有多少？要求查询结果按人数升序排列，人数相同时按年龄降序排列。,SELECT AGE, COUNT(S#) FROM S WHERE SEX=M GROUP BY AGE HAVING COUNT(*)50 ORDER BY 2, AGE DESC,SQL应用,S(S#, SNAME, AGE, SEX) SC(S#, C#, GRADE) C(C#, CNAME, TEACHER),13、在关系SC中，把平均成绩大于80分的男学生的学号和平均成绩插入到另一个关系S_GRADE(S#, AVG_GRADE)中。,INSERT INTO S_GRADE(S#, AVG_GRADE) SELECT S#, AVG(GRADE) FROM SC WHERE S# IN (SELECT S# FROM S WHERE SEX=M) GROUP BY S# HAVING AVG(GRADE)80,SQL应用,S(S#, SNAME, AGE, SEX) SC(S#, C#, GRADE) C(C#, CNAME, TEACHER),14、把C4课程中小于该课程平均成绩的成绩从关系SC中删除。,DELETE FORM SC WHERE C#=C4 AND GRADE(SELECT AVG(GRADE) FROM SC WHERE C#=C4),SQL应用,S(S#, SNAME, AGE, SEX) SC(S#, C#, GRADE) C(C#, CNAME, TEACHER),15、把C4课程中低于该门课程平均成绩的成绩提高5。,UPDATE SC SET GRADE=GRADE*1.05 WHERE C#=C4 AND GRADE(SELECT AVG(GRADE) FROM SC WHERE C#=C4),SQL应用,S(S#, SNAME, AGE, SEX) SC(S#, C#, GRADE) C(C#, CNAME, TEACHER),