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

关系数据库语言SQL 续2 概述SQL的数据定义SQL的数据操纵SQL的视图定义SQL的数据控制 3 5SQL的数据控制 1of8 SQL的数据控制也叫数据保护 包括数据的安全性 完整性 并发控制和恢复功能 数据库的安全性是指保护数据库 防止不合法的使用所造成的数据泄露和破坏 SQL语言为定义和回收存取控制权利提供了手段 具体办法 进行存取控制 即规定不同用户对于不同数据对象所允许执行的操作 并控制各用户只能存取他有权存取的数据 授权机制 2of8 授权机制 大的DBMS中有一个超级用户DBA 其他用户能否存在 对某类数据具有何种操作权力是由DBA决定的 系统提供授权机制 执行过程 1 用数据控制语言把授权决定告知系统 2 系统把授权的结果存入数据字典 3 当用户提出操作请求时 系统根据授权情况进行检查 以决定是否执行 例如 SYBASE系统中 有四类用户 三级管理 系统管理员SA 数据库对象属主 一般用户 授权某用户建库 可再授权某用户建立数据库对象 可再授权某用户对数据库对象的访问 根据权限对数据库对象进行操作 授权机制 3of8 数据库属主 授权机制 4of8 又如 SQLServer通过角色将用户分为不同的类 固定服务器角色固定数据库角色 权力的授予与收回 5of8 授予 GRANT权力 权力 ON对象类型对象名 TO用户名 用户名 WITHGRANTOPTION 若干权力 操作对象 获得权力的用户 有此项 被授权用户可再授权给其他用户 收回 REVOKE权力 权力 ON对象类型对象名 FROM用户名 用户名 谁定义 DBA和表的建立者 即表的属主 SQL中的用户权限 6of8 Example 7of8 例1 把查询学生表的权限授予给用户U1 例2 把对Student和Course表的全部权限授予给用户U2和U3 GRANTALLPRIVILEGESONTABLEStudent CourseTOU2 U3 例3 把查询Student表和修改学生学号的权限授予给用户U4 GRANTUPDATE Sno SELECTTABLEONStudentTOU4 例4 把对SC表的INSERT权限授予给用户U5 并允许他再将此权限授予给其他用户 GRANTINSERTONTABLESCTOU5WITHGRANTOPTION 例5 把U4对Student表修改学生学号的权限收回 REVOKEUPDATE Sno ONTABLEStudentFROMU4 GRANTSELECTONTABLEStudentTOU1 SQL的安全性控制 8of8 数据库的属主DBA 表的属主以及其他数据库对象的属主在他创建的对象上具有一切可能的权力 其他用户得不到主人的授权就不能在该对象上操作 SQL的授权机制十分灵活 各种系统又作了适当的补充 使用十分方便 SQL的安全性控制除了上述授权机制外 还可设置口令进一步保密 SQL应用举例 应用 数据库中有三个关系 基本表 S S SNAME AGE SEX SC S C GRADE C C CNAME TEACHER 试用SQL语句表达下列查询和更新语句 思考中 1 检索学习课程为C2的学生学号与成绩 SELECTS GRADEFROMSCWHEREC C2 SQL应用 S S SNAME AGE SEX SC S C GRADE C C CNAME TEACHER 2 检索学习课程为C2的学生学号与姓名 方法一 联接查询SELECTS SNAMEFROMS SCWHERES S SC S ANDC C2 SQL应用 S S SNAME AGE SEX SC S C GRADE C C CNAME TEACHER 2 检索学习课程为C2的学生学号与姓名 方法二 嵌套查询SELECTS SNAMEFROMSWHERES IN SELECTS FROMSCWHEREC C2 SQL应用 S S SNAME AGE SEX SC S C GRADE C C CNAME TEACHER 2 检索学习课程为C2的学生学号与姓名 方法三 嵌套查询SELECTS SNAMEFROMSWHERE C2 IN SELECTC FROMSCWHERES S S 相关子查询 子查询中查询条件依赖于外层查询中的某个值 SQL应用 S S SNAME AGE SEX SC S C GRADE C C CNAME TEACHER 2 检索学习课程为C2的学生学号与姓名 方法四 存在量词SELECTS SNAMEFROMSWHEREEXISTS SELECT FROMSCWHERESC S S S ANDC C2 SQL应用 S S SNAME AGE SEX SC S C GRADE C C CNAME TEACHER 内层查询结果非空 3 检索不学课程C2的学生学号与姓名 方法一 存在量词SELECTS SNAMEFROMSWHERENOTEXISTS SELECT FROMSCWHERESC S S S ANDC C2 SQL应用 S S SNAME AGE SEX SC S C GRADE C C CNAME TEACHER 3 检索不学课程C2的学生学号与姓名 方法二 嵌套查询SELECTS SNAMEFROMSWHERES NOTIN SELECTS FROMSCWHEREC C2 SQL应用 S S SNAME AGE SEX SC S C GRADE C C CNAME TEACHER 4 检索学习课程名为Maths的学生学号与姓名 方法一 多层嵌套SELECTS SNAMEFROMSWHERES IN SELECTS FROMSCWHEREC IN SELECTC FROMCWHERECNAME Mathes SQL应用 S S SNAME AGE SEX SC S C GRADE C C CNAME TEACHER 4 检索学习课程名为Maths的学生学号与姓名 方法二 联接查询SELECTS S SNAMEFROMS SC CWHERES S SC S ANDSC C C C ANDCNAME Maths SQL应用 S S SNAME AGE SEX SC S C GRADE C C CNAME TEACHER 5 检索学习课程号为C2或C4的学生学号 SELECTS FROMSCWHEREC C2 ORC C4 SQL应用 S S SNAME AGE SEX SC S C GRADE C C CNAME TEACHER 6 检索至少学习课程号为C2和C4的学生学号 SELECTX S FROMSCASX SCASYWHEREX S Y S ANDX C C2 ANDY C C4 同一关系SC在一层中出现两次 分别引入别名X和Y SQL应用 S S SNAME AGE SEX SC S C GRADE C C CNAME TEACHER 7 检索学习全部课程的学生姓名 SELECTSNAMEFROMSWHERENOTEXISTS SELECT FROMCWHERENOTEXISTS SELECT FROMSCWHERESC S S S ANDSC C C C SQL应用 S S SNAME AGE SEX SC S C GRADE C C CNAME TEACHER 8 检索所学课程包含学生S3所学课程的学生学号 SELECTDISTINCTS FROMSCASXWHERENOTEXISTS SELECT FROMSCASYWHEREY S S3 ANDNOTEXISTS SELECT FROMSCASZWHEREZ S X S ANDZ C Y C SQL应用 S S SNAME AGE SEX SC S C GRADE C C CNAME TEACHER 9 统计每一年龄选修课程的学生人数 SELECTAGE COUNT DISTINCTS S FROMS SCWHERES S SC S GROUPBYAGE SQL应用 S S SNAME AGE SEX SC S C GRADE C C CNAME TEACHER 10 检索平均成绩最高的学生学号 SELECTS FROMSCGROUPBYS HAVINGAVG GRADE ALL SELECTAVG GRADE FROMSCGROUPBYS SQL应用 S S SNAME AGE SEX SC S C GRADE C C CNAME TEACHER SQL中 不允许对集函数进行复合运算因此不能写成 SELECTMAX AVG GRADE 形式 11 检索至少有一门成绩超过学生S4一门成绩的学生学号 SELECTDISTINCTS FROMSCWHEREGRADE ANY SELECTGRADEFROMSCWHERES S4 SQL应用 S S SNAME AGE SEX SC S C GRADE C C CNAME TEACHER 12 求男同学每一年龄组 超过50人 有多少 要求查询结果按人数升序排列 人数相同时按年龄降序排列 SELECTAGE COUNT S FROMSWHERESEX M GROUPBYAGEHAVINGCOUNT 50ORDERBY2 AGEDESC SQL应用 S S SNAME AGE SEX SC S C GRADE C C CNAME TEACHER 13 在关系SC中 把平均成绩大于80分的男学生的学号和平均成绩插入到另一个关系S GRADE S AVG GRADE 中 INSERTINTOS GRADE S AVG GRADE SELECTS AVG GRADE FROMSCWHERES IN SELECTS FROMSWHERESEX M GROUPBYS HAVINGAVG GRADE 80 SQL应用 S S SNAME AGE SEX SC S C GRADE C C CNAME TEACHER 14 把C4课程中小于该课程平均成绩的成绩从关系SC中删除 DELETEFORMSCWHEREC C4 ANDGRADE SELECTAVG GRADE FROMSCW