第4章 完整和安全.ppt

2020 1 5 ftt 1 SQL语言高级应用 第4章完整性和安全性 2020 1 5 ftt 2 数据完整性 何为数据完整性 P9 由DBMS而不是用户程序自动检查数据的一致性 相容性 保证数据符合完整性约束条件 数据须符合的条件或数据之间的关系数据类型准确无误数据的值满足范围设置同一表格数据之间不存在冲突多个表格数据之间不存在冲突 2020 1 5 ftt 3 完整性约束条件 整个完整性控制都是围绕完整性约束条件进行的 从这个角度说 完整性约束条件是完整性控制机制的核心 2020 1 5 ftt 4 完整性约束条件作用的对象列 对属性的取值类型 范围 精度等的约束条件元组 对元组中各个属性列间的联系的约束关系 对若干元组间 关系集合上以及关系之间的联系的约束完整性约束条件作用的对象状态静态对静态对象的约束是反映数据库状态合理性的约束这是最重要的一类完整性约束动态对动态对象的约束是反映数据库状态变迁的约束 完整性约束条件 2020 1 5 ftt 5 完整性约束条件分类 对象状态动态列级约束动态元组约束动态关系约束动态 静态列级约束静态元组约束静态关系约束静态 列元组关系对象粒度 2020 1 5 ftt 6 静态列级约束 静态列级约束是对一个列的取值域的说明 这是最常见最简单同时也最容易实现的一类完整性约束五类静态列级约束对数据类型的约束 包括数据的类型 长度 单位 精度等例 规定学生姓名的数据类型应为字符型 长度为8 对数据格式的约束例 规定学号的格式为前两位表示入学年份 后四位为顺序编号 出生日期的格式为YY MM DD 对取值范围或取值集合的约束例 规定成绩的取值范围为0 100 年龄的取值范围为14 29 性别的取值集合为 男 女 对空值的约束 空值表示未定义或未知的值 它与零值和空格不同 有的列允许空值 有的则不允许 例 规定成绩可以为空值 其他约束例 关于列的排序说明 组合列等 2020 1 5 ftt 7 静态元组约束 静态元组约束就是规定组成一个元组的各个列之间的约束关系 例 订货关系中包含发货量 订货量等列 发货量不得超过订货量例 教师关系中包含职称 工资等列 教授的工资不得低于700元静态元组约束只局限在单个元组上 2020 1 5 ftt 8 静态关系约束 在一个关系的各个元组之间或者若干关系之间常常存在各种联系或约束 常见静态关系约束实体完整性约束参照完整性约束函数依赖约束统计约束定义某个字段值与一个关系多个元组的统计值之间的约束关系例 规定部门经理的工资不得高于本部门职工平均工资的5倍 不得低于本部门职工平均工资的2倍 本部门职工的平均工资值是一个统计计算值 2020 1 5 ftt 9 动态列级约束 动态列级约束是修改列定义或列值时应满足的约束条件 常见动态列级约束修改列定义时的约束例 规定将原来允许空值的列改为不允许空值时 如果该列目前已存在空值 则拒绝这种修改 修改列值时的约束修改列值有时需要参照其旧值 并且新旧值之间需要满足某种约束条件 例 职工工资调整不得低于其原来工资 学生年龄只能增长 2020 1 5 ftt 10 动态元组约束 动态元组约束是指修改某个元组的值时需要参照其旧值 并且新旧值之间需要满足某种约束条件 例 职工工资调整不得低于其原来工资 工龄 1 5 2020 1 5 ftt 11 动态关系约束 动态关系约束是加在关系变化前后状态上的限制条件 例 事务一致性 原子性等约束条件 2020 1 5 ftt 12 完整性约束条件小结 2020 1 5 ftt 13 DBMS的完整性控制机制 定义功能一个完善的完整性控制机制应该允许用户定义各类完整性约束条件 检查功能立即执行的约束 Immediateconstraints 检查是否违背完整性约束的时机通常是在一条语句执行完后立即检查 我们称这类约束为立即执行的约束延迟执行的约束 Deferredconstraints 在某些情况下 完整性检查需要延迟到整个事务执行结束后再进行 我们称这类约束为延迟执行的约束违约反应拒绝该操作其他处理方法 2020 1 5 ftt 14 关系系统三类完整性的实现 提供有定义和检查实体完整性 参照完整性和用户定义的完整性的功能 对于违反实体完整性规则和用户定义的完整性规则的操作一般都是采用拒绝执行的方式进行处理 而对于违反参照完整性的操作 并不都是简单地拒绝执行 有时还需要采取另一种方法 即接受这个操作 同时执行一些附加的操作 以保证数据库的状态仍然是正确的 2020 1 5 ftt 15 实体完整性的实现 例 在 学号不能为空 的约束中 约束作用的对象为Sno属性则插入或修改Student元组时 作何处理 要求Sno不能为空否则 拒绝执行该操作 2020 1 5 ftt 16 用户定义完整性的实现 例 在 教授工资不得低于1000元 的约束中 约束作用的对象为工资Sal属性则插入或修改职工元组时 作如何处理 如果职称 教授 时 Sal不能小于1000否则 拒绝执行该操作 2020 1 5 ftt 17 参照完整性的实现 外码是否可以接受空值的问题删除被参照关系的元组时的问题修改被参照关系中主码的问题 2020 1 5 ftt 18 外码是否可以接受空值的问题 外码是否能够取空值是依赖于应用环境的语义的 在实现参照完整性时 系统除了应该提供定义外码的机制 还应提供定义外码列是否允许空值的机制 2020 1 5 ftt 19 例1 在职工 部门数据库中 EMP关系包含有外码Deptno 某一元组的这一列若为空值 表示这个职工尚未分配到任何具体的部门工作 这和应用环境的语义是相符的 因此EMP的Deptno列应允许空值 例2 在学生 选课数据库中 Student关系为被参照关系 其主码为Sno SC为参照关系 外码为Sno 若SC的Sno为空值 则表明尚不存在的某个学生 或者某个不知学号的学生 选修了某门课程 其成绩记录在Grade列中 这与学校的应用环境是不相符的 因此SC的Sno列不能取空值 2020 1 5 ftt 20 删除被参照关系的元组时的问题 出现违约操作的情形需要删除被参照关系的某个元组 而参照关系有若干元组的外码值与被删除的被参照关系的主码值相对应违约反应 可有三种策略级联删除 CASCADES 将参照关系中所有外码值与被参照关系中要删除元组主码值相对应的元组一起删除 受限删除 RESTRICTED 只有当参照关系中没有任何元组的外码值与要删除的被参照关系的元组的主码值相对应时 系统才执行删除操作 否则拒绝此删除操作 置空值删除 NULLIFIES 删除被参照关系的元组 并将参照关系中所有与被参照关系中被删除元组主码值相等的外码值置为空值 这三种处理方法 哪一种是正确的 要依应用环境的语义来定 2020 1 5 ftt 21 例 要删除Student关系中Sno 200801的元组 而SC关系中有4个元组的Sno都等于200801 级联删除 将SC关系中所有4个Sno 200801的元组一起删除 如果参照关系同时又是另一个关系的被参照关系 则这种删除操作会继续级联下去 受限删除 系统将拒绝执行此删除操作 置空值删除 将SC关系中所有Sno 200801的元组的Sno值置为空值 在学生选课数据库中 显然第一种方法和第二种方法都是对的 第三种方法不符合应用环境语义 2020 1 5 ftt 22 修改被参照关系中主码的问题 两种策略不允许修改主码允许修改主码允许修改主码策略违约操作要修改被参照关系中某些元组的主码值 而参照关系中有些元组的外码值正好等于被参照关系要修改的主码值要修改参照关系中某些元组的主码值 而被参照关系中没有任何元组的外码值等于被参照关系修改后的主码值违约反应修改的关系是被参照关系 与删除类似级联修改受限修改置空值修改修改的关系是参照关系 与插入类似受限修改级联修改 2020 1 5 ftt 23 修改被参照关系中主码的问题 续 级联修改修改被参照关系中主码值同时 用相同的方法修改参照关系中相应的外码值 受限修改拒绝此修改操作 只当参照关系中没有任何元组的外码值等于被参照关系中某个元组的主码值时 这个元组的主码值才能被修改 置空值修改修改被参照关系中主码值 同时将参照关系中相应的外码值置为空值 2020 1 5 ftt 24 例 学生200801休学一年后复学 这时需要将Student关系中Sno 200801的元组中Sno值改为200923 而SC关系中有4个元组的Sno 200801级联修改 将SC关系中4个Sno 200801元组中的Sno值也改为200923 如果参照关系同时又是另一个关系的被参照关系 则这种修改操作会继续级联下去 受限修改 只有SC中没有任何元组的Sno 200801时 才能修改Student表中Sno 200801的元组的Sno值改为200923 置空值修改 将Student表中Sno 200801的元组的Sno值改为200923 而将S表中所有Sno 200801的元组的Sno值置为空值 在学生选课数据库中只有第一种方法是正确的 2020 1 5 ftt 25 小结 如何进行完整性维护系统的设计 1如何表达完整性约束条件 2如何检查完整性约束条件 如何检查 3违反条件时候的如何处理 2020 1 5 ftt 26 DBMS中完整性的定义 使用约束 2020 1 5 ftt 27 CREATETABLE定义约束 约束涉及单个属性的 有两种说明方法定义为列级约束条件定义为表级约束条件若约束涉及多个属性的 只有一种说明方法定义为表级约束条件 2020 1 5 ftt 28 列级约束例1 CREATETABLE语句中定义列级约束CREATETABLES snoNUMERIC 6 PRIMARYKEY 列级约束snameCHAR 8 NOTNULL ageNUMERIC 3 0 sexCHAR 2 bplaceCHAR 20 表级约束例2 CREATETABLE语句中定义表级约束CREATETABLESC snoNUMERIC 6 NOTNULL cnovarchar 3 notnull PRIMARYKEY sno cno 表级约束gradeNUMERIC 2 2020 1 5 ftt 29 例3 在Employee表中说明Eno为主码 有两种方法 将属性直接说明为主码 列级约束CREATETABLEEmployee Enochar 4 PRIMARYKEY Enamevarchar 8 Sexchar 2 Ageint Dnochar 2 在属性列表后单独说明主码 表级约束CREATETABLEEmployee Enochar 4 Enamevarchar 8 Sexchar 2 Ageint Dnochar 2 PRIMARYKEY Eno 2020 1 5 ftt 30 ALTERTABLE定义约束 添加约束语法 ALTERTABLEADD CONSTRAINT 例4 ALTERTABLE添加约束 ALTERTABLEEmployeeADDCONSTRAINTuq enameUNIQUE Ename 删除约束 语法 ALTERTABLEDROP CONSTRAINT 例5 ALTERTABLE删除约束 ALTERTABLEEmployeeDROPCONSTRAINTuq ename 2020 1 5 ftt 31 UNIQUE约束 允许空值在一个表上可以放置多个UNIQUE约束可以在必须有唯一值 但不是表的主关键字的一个列或多个列上应用UNIQUE约束通过在指定列上创建唯一索引强制UNIQUE约束例6 添加唯一性约束 ALTERTABLEDepartmentADDCONSTRAINTuq dnameUNIQUEnonclustered dname 例7 增加Ename必须取唯一值的约束条件 ALTERTABLEEmployee 用户为该唯一值约束设定约束名为UQ Ename ADDCONSTRAINTUQ EnameUNIQUE Ename 或者 ALTERTABLEEmployeeADDUNIQUE Ename 系统将自动为此约束名赋值 2020 1 5 ftt 32 PRIMARYKEY约束 每个表只能定义一个PRIMARYKEY约束输入的值必须是唯一的不允许空值它在指定的列上创建一个唯一索引例8 添加主码约束ALTERTABLEDepartmentADDCONSTRAINTpk dnoPRIMARYKEYclustered dno 2020 1 5 ftt 33 IDENTITY属性 每一个表都可有一标识列 其中包含由系统自动生成的能够标识表中每一行数据的唯一序列值 格式 IDENTITY SEED INCREMENT SEED初始值 表中的第一行数据的标识列的取值 默认值为1INCREMEN步长值 每一新标识值比上一个增长多少 默认值为1使用IDENTITY列时 应注意 每张表只允许有一个IDENTITY列该列必须使用下列数据类型之一 decimal numeric int smallint和tinyint该列必须设置成不允许为空值 且不能有默认值 2020 1 5 ftt 34 DEFAULT约束 只应用于INSERT语句每列只能定义一个DEFAULT约束不能用于IDENTITY属性或TIMESTAMP数据类型TIMESTAMP数据库范围内的唯一数字 8字节 一个表中只能一个此类型的列允许使用一些系统提供的值例9 增加列Title的默认值取值为 助工 ALTERTABLEEmployeeADDCONSTRAINTDF TitleDEFAULT 助工 forTitle 2020 1 5 ftt 35 CHECK约束 在每次执行INSERT或UPDATE语句时验证数据可以引用同一表中的其他列不能在具有IDENTITY属性的列或具有timestamp或uniqueidentifier数据类型的列上放置CHECK约束不能含有子查询例10 增加列性别取值约束 ALTERTABLEEmployeeADDCONSTRAINTck sexCHECK sexin 男 女 2020 1 5 ftt 36 CHECK约束也可涉及到表中多个域 称为元组约束 在对整个元组完成插入或对某一元组的修改完成之后 系统将检查元组是否符合CHECK条件表达式 例11 添加涉及多列的CHECK约束 ALTERTABLESalaryADDCONSTRAINTck RightSalaryCHECK Insure Fund Basepay 注意 完整性约束的检查将花费系统一定的时间 特别是那些复杂的CHECK条件 虽然非常有用 但不应泛滥使用 2020 1 5 ftt 37 FOREIGNKEY约束 必须引用一个PRIMARYKEY约束或UNIQUE约束提供单列或多列引用完整性不自动创建索引用户在引用的表上必须有SELECT或REFERENCES权限在同一表中只使用REFERENCES子句例12 添加外部码约束 ALTERTABLESCADDCONSTRAINTfk snoFOREIGNKEY sno REFERENCESS sno 2020 1 5 ftt 38 例13 说明Employee表中Dno为外部码 被参照关系为Department 两种方法分别如下 列级约束 只使用REFERENCES子句CREATETABLEEmployee Enochar 4 PRIMARYKEY Namevarchar 8 Sexchar 2 Ageint Dnochar 2 REFERENCESDepartment Dno 表级约束CREATETABLEEmployee Enochar 4 PRIMARYKEY Namevarchar 8 Sexchar 2 Ageint Dnochar 2 FOREIGNKEY Dno REFERENCESDepartment Dno 2020 1 5 ftt 39 参照完整性检查和违约处理 ONDELETE NOACTION CASCADE SETNULL SETDEFAULT ONUPDATE NOACTION CASCADE SETNULL SETDEFAULT 高版本SQLSERVER支持 SETNULL SETDEFAULT 2020 1 5 ftt 40 ONDELETE 指定如果已创建表中的行具有引用关系 并且被引用行已从父表中删除 则对这些行采取的操作 默认值为NOACTION NOACTION数据库引擎将引发错误 并回滚对父表中相应行的删除操作 CASCADE如果从父表中删除一行 则将从引用表中删除相应行 SETNULL 高版本SQLSERVER支持 如果父表中对应的行被删除 则组成外键的所有值都将设置为NULL 若要执行此约束 外键列必须可为空值 SETDEFAULT 高版本SQLSERVER支持 如果父表中对应的行被删除 则组成外键的所有值都将设置为默认值 若要执行此约束 所有外键列都必须有默认定义 如果某个列可为空值 并且未设置显式的默认值 则将使用NULL作为该列的隐式默认值 2020 1 5 ftt 41 例14 添加并验证外码约束的CASCADE级联操作 ALTERTABLEEmployeeADDCONSTRAINTfk empFOREIGNKEY Dno REFERENCESDepartment Dno ONDELETECASCADEONUPDATECASCADE 2020 1 5 ftt 42 ONUPDATE 指定在发生更改的表中 如果行有引用关系且引用的行在父表中被更新 则对这些行采取什么操作 默认值为NOACTION NOACTION数据库引擎将引发错误 并回滚对父表中相应行的更新操作 CASCADE如果在父表中更新了一行 则将在引用表中更新相应的行 SETNULL 高版本SQLSERVER支持 如果更新了父表中的相应行 则会将构成外键的所有值设置为NULL 若要执行此约束 外键列必须可为空值 SETDEFAULT 高版本SQLSERVER支持 如果更新了父表中的相应行 则会将构成外键的所有值都设置为其默认值 若要执行此约束 所有外键列都必须有默认定义 如果某个列可为空值 并且未设置显式的默认值 则将使用NULL作为该列的隐式默认值 2020 1 5 ftt 43 数据完整性回顾 如何进行完整性维护系统的设计 1如何表达完整性约束条件 2如何检查完整性约束条件 3违反条件时候的如何处理 2020 1 5 ftt 44 1 提供定义完整性约束条件的机制SQL标准使用了一系列概念来描述完整性 包括关系模型的实体完整性 参照完整性和用户定义完整性这些完整性一般由SQL的DDL语句来实现 数据完整性回顾 2020 1 5 ftt 45 2 提供完整性检查的方法引起数据库状态改变的操作有哪些 INSERT UPDATE DELETE语句执行上述操作后开始检查3 违约处理DBMS若发现用户的操作违背了完整性约束条件 就采取一定的动作拒绝 NOACTION 执行该操作级联 CASCADE 执行其他操作其他用户定义的操作 2020 1 5 ftt 46 DBMS定义约束 2020 1 5 ftt 47 实现数据完整性 数据完整性的类型实体完整性参照完整性用户定义完整性 域完整性 强制完整性声明数据完整性 在对象定义中定义的条件SQLServer自动强制使用约束 默认值和规则实现过程数据完整性 在脚本中定义的条件脚本强制使用触发器和存储过程实现 2020 1 5 ftt 48 SQL语言高级应用 4 2触发器 2020 1 5 ftt 49 主要内容 触发器的概念触发器的功能和类型如何使用企业管理器创建和管理触发器如何使用T SQL语句创建和管理触发器 2020 1 5 ftt 50 Employee Salary 2020 1 5 ftt 51 触发器的基本概念 触发器是一种特殊的存储过程 它不能被显式地调用 而是在往表中增删改记录时 被自动激活 可以用来对表实施复杂的完整性约束 触发器和引起触发器执行的T SQL语句被当作一次事务处理 因此可以在触发器中回滚这个事务 如果发现引起触发器执行的T SQL语句执行了一个非法操作 则可以通过回滚事务使语句不能执行 回滚后SQLServer会自动返回到此事务执行前的状态 2020 1 5 ftt 52 触发器的作用 可以对数据库进行级联修改 可以完成比CHECK约束更复杂的限制 可以发现改变前后表中数据的不同 并根据这些不同来进行相应的操作 对于一个表上的不同的操作 INSERT UPDATE或者DELETE 可以采用不同的触发器 即使是对相同的语句也可调用不同的触发器完成不同的操作 2020 1 5 ftt 53 Inserted表和Deleted表 SQLServer为每个触发器都创建了两个专用表 Inserted表和Deleted表 它们为逻辑表 由系统来维护 用户不能对它们进行修改 并存放在内存而不是数据库中 表结构总是与引起该触发器作用的表的结构相同 触发器执行完成后 与该触发器相关的这两个表也会被删除 Inserted表存放由于执行Insert或Update语句而要从表中插入的所有行 Deleted表存放由于执行Delete或Update语句而要从表中删除的所有行 一个Update事务可以看作先执行一个Delete操作 再执行一个Insert操作 2020 1 5 ftt 54 触发器产生的临时视图 Col1intPKCol2char 10 Test UpdateEventUpdatetestSetCol2 second whereCol1 1 Col1 1Col2 second DeletedTable UpdateTrigger Col1 1Col2 first InsertedTable InsertEventInsertintotestvalues 1 first Col1 1Col2 first 空 DeletedTable InsertTrigger InsertedTable DeleteEventDeletefromtestwhereCol1 1 DeleteTrigger InsertedTable DeletedTable 2020 1 5 ftt 55 INSTEADOF和AFTER触发器 INSTEADOF触发器和AFTER触发器 INSTEADOF触发器用于替代引起触发器执行的T SQL语句 可作用于表 视图 AFTER触发器在一个INSERT UPDATE或DELETE语句之后执行 进行约束检查等动作都将在AFTER触发器被激活之前发生 只作用于表 2020 1 5 ftt 56 触发器的机制 发生事件 对一个关系的增删改操作 检查触发器的条件 执行触发器的动作 由数据库自动完成 操作的剩余部分 成立 不成立 2020 1 5 ftt 57 创建触发器 创建前须知 CREATETRIGGER语句必须是一个批中的第一条语句 创建触发器的权限默认是属于表的所有者的 而且不能授权给其他人 触发器是数据库对象 其名称必须符合命名规则 只能在当前数据库中触发器 但触发器可以引用其他数据库的对象 触发器不能再临时表或系统表上创建 触发器可以引用临时表 但不能引用系统表 TRUNCATETABLE的操作不被记入事务日志 也不会激活DELETE触发器 2020 1 5 ftt 58 创建触发器时需考虑 触发器的名称触发器所基于的表或视图触发器激活的时机激活触发器的修改语句 有效的选项是INSERT UPDATE和DELETE触发器执行的语句一个表上可以有多个具有不同名称的各种类型触发器 每个触发器可以完成不同的功能 但每个触发器只能作用在一个表上 每个触发器可以被所有的三种动作 INSERT UPDATE和DELETE 所激发 2020 1 5 ftt 59 使用T SQL语句创建触发器 基本语法 CREATETRIGGERtrigger nameON table name view name FOR AFTER INSTEADOF INSERT UPDATE DELETE ASsql statement 2020 1 5 ftt 60 例1 设数据库中有一职工历史表Emp his 其表结构与Employee表相同 为Employee表创建一触发器 当删除职工时 将删除职工的信息保存到表Emp his中 CREATETRIGGERTri del empONEmployeeAFTERDELETEASINSERTINTOEmp hisSELECT FROMdeleted 2020 1 5 ftt 61 例2 创建一个触发器 实现无法删除有职工存在的部门信息 否则 将给出错误信息 CREATETRIGGERTri del deptONDepartmentAFTERDELETEASIFEXISTS SELECTEnoFROMEmployee DELETEDWHEREEmployee Dno DELETED Dno BEGINRAISERROR Thedepartmentthatisnotemptycannotbedeleted 10 1 ROLLBACKTRANSACTIONEND RAISERROR将引发一个错误号为50000的错误消息 2020 1 5 ftt 62 例3 实现前面的那个例子 为Employee表创建一触发器 当插入一职工时 在工资表中生成该职工的工资记录 设置其基本工资为500 其他工资项为0 CREATETRIGGERTri ins employeeONEmployeeAFTERINSERTASDECLARE enochar 4 SELECT eno EnoFROMinsertedINSERTINTOSalary Eno Basepay Service Price Rest Insure Fund VALUES eno 500 0 0 0 0 0 2020 1 5 ftt 63 例4 为employee表创建一触发器 当修改某位职工的职称时 应同时修改salary表中的基本工资Basepay 具体标准为 当职称修改为工程师时 增加150元 当修改为高工时 增加300元 CREATETRIGGERTri updatetitle employeeONEmployeeFORUPDATEASIFUPDATE Title IF SELECTTitleFROMinserted 工程师 UPDATESalarySETBasepay Basepay 150WHEREEno SELECTEnoFROMinserted ELSEIF SELECTTitleFROMinserted 高工 UPDATESalarySETBasepay Basepay 300WHEREEno SELECTEnoFROMinserted 2020 1 5 ftt 64 INSTEADOF触发器 例5 假设有一查询每个部门职工人数的视图 则创建一触发器使得删除视图数据即为删除该部门的职工 CREATEVIEWV dnumber Dno Dnumber ASSELECTDno COUNT Eno FROMEmployeeGROUPBYDnoCREATETRIGGERTri Del V dnumberONV dnumberINSTEADOFDELETEASDELETEFROMEmployeeWHEREDno SELECTDnoFROMdeleted 2020 1 5 ftt 65 触发器编程注意 对于SQLSERVER 一个增删改操作对应于确定的触发器只会作用一次 而不管在这个操作中有多少记录受到了该操作的影响 因为它是语句级的 ORALCE中可支持行级触发器 FOREACHROW 对于更新触发器 可指定更新特殊的列 column 执行不能顺利完成 可给出错误信息并回滚事务 取消所有操作 没有出错 提交事务 2020 1 5 ftt 66 修改触发器 修改ALTERTRIGGEREnterpriseManager 2020 1 5 ftt 67 查看依赖关系 在重命名数据库对象时 最好先查看与该数据库对象有依赖关系的其他数据库对象 以决定是否有触发器需要为此而修改定义 可用EnterpriseManager 2020 1 5 ftt 68 获得触发器的有关信息 显示表上触发器的类型sp helptrigger显示了触发器的名称 拥有者 并用五个布尔值表明了触发器的类型和触发器被激活的时机 显示触发器的代码sp helptext 2020 1 5 ftt 69 使表上的触发器无效或重新有效 修改表的ALTERTABLE语句 使用DISABLETRIGGER子句可以使该表上的某一触发器无效 使用ENABLETRIGGER子句可以使触发器重新有效 2020 1 5 ftt 70 删除触发器 触发器被删除时 触发器所在表中的数据不会因此改变 当某个表被删除时 该表上的所有触发器也自动被删除 使用DROPTRIGGER语句可删除当前数据库的一个或多个触发器 使用EnterpriseManager 2020 1 5 ftt 71 域中的完整性限制 MicrosoftSQLServer的实现 2020 1 5 ftt 72 主要内容 用户自定义数据类型创建 修改 删除和查看规则规则的概念创建 修改和删除规则默认值默认值的概念创建 修改和删除默认值规则与CHECK约束 默认值与DEFAULT约束的异同点 2020 1 5 ftt 73 1用户自定义数据类型 利用用户定义的数据类型可以使在不同表中重复出现的各列具有相同的特性 使相似的数据种类标准化 可以将规则和默认值捆绑到用户定义的数据类型上 那么该规则和默认值就可以约束使用此数据类型的每个列 如果在model数据库中创建了用户自定义的数据类型 则它将出现在所有以后新建的数据库中 但是定义在用户数据库中的用户自定义数据类型 则只会出现在定义它的数据库中 2020 1 5 ftt 74 创建用户自定义数据类型 使用企业管理器使用T SQL语句可以使用系统存储过程sp addtype来创建用户自定义数据类型 别名类型 具体语法 sp addtypetype system data type null type 2020 1 5 ftt 75 例1 基于系统提供的varchar数据类型创建别名类型 sp addtypetelephone varchar 8 NULL 例2 创建不允许空值并且名为birthday的别名数据类型 基于datetime EXECsp addtypebirthday datetime NOTNULL SQLSERVER高版本将使用CREATETYPE代替sp addtype的使用 CREATETYPEtelephoneFROMvarchar 8 NULL CREATETYPE支持创建用户定义表类型CREATETYPEEmployeetypeASTABLE EnoVARCHAR 5 EnameVARCHAR 10 2020 1 5 ftt 76 查看用户自定义数据类型 使用系统存储过程sp help来查看用户自定义数据类型的信息 基于的系统数据类型长度 精度是否允许为空值在这一数据类型上捆绑的规则和默认值等 2020 1 5 ftt 77 删除用户自定义数据类型 使用企业管理器使用T SQL语句使用系统存储过程sp droptype来删除用户自定义数据类型 语法 sp droptype type 注意 正在被表或数据库使用的用户自定义数据类型不能被删除 2020 1 5 ftt 78 例3 删除别名数据类型birthday sp droptype birthday SQLSERVER高版本中的使用 DROPTYPEbirthday 2020 1 5 ftt 79 2规则 在向表的某列插入或更新数据时 用它来限制输入的新值的取值范围 规则是实现域完整性的方法之一 用来验证一个数据库中的数据是否处于一个指定的值域范围内 规则与CHECK约束的不同之处 CHECK约束是用CREATETABLE语句在建表时指定的 而规则需要作为单独的数据库对象来实现 在一列上只能使用一个规则 但可以使用多个CHECK约束 规则可以应用于多个列 还可以应用于用户自定义的数据类型 而CHECK约束只能应用于它定义的列 2020 1 5 ftt 80 创建规则 使用T SQL语句CREATERULE语法 CREATERULEruleAScondition expressioncondition expression可以是任何在查询的where子句中出现的表达式 但不能包括列名或其他数据库对象名 在表达式中有一个以 开头的变量 该变量代表在修改该列的纪录是用户输入的数值 2020 1 5 ftt 81 例4 创建具有范围的规则CREATERULErange ruleAS value 18例5 创建具有列表的规则CREATERULEsex ruleAS sexIN 男 女 例6 创建具有模糊匹配的规则CREATERULEtelephone ruleAS telephonelike 0 9 0 9 0 9 0 9 0 9 0 9 0 9 0 9 2020 1 5 ftt 82 捆绑规则 使用系统存储过程sp bindrule可以将规则捆绑到列或用户自定义数据类型上 语法 Sp bindrule rule object name futureonly flag rule 规则的名称object name为规则要捆绑到的列名或用户自定义数据类型名futureonly flag可选参数 仅在要捆绑到用户自定义数据类型时使用 2020 1 5 ftt 83 例7 将规则绑定到列 EXECsp bindrule range rule Employee Age 例8 将规则绑定到别名数据类型 EXECsp bindrule telephone rule telephone 例9 使用futureonly flag 以下示例将telephone rule规则绑定到别名数据类型telephone 由于已指定futureonly 因此不影响类型为telephone的现有列 EXECsp bindrule telephone rule telephone futureonly 2020 1 5 ftt 84 使用sp unbindrule系统存储过程可以解除捆绑到列或用户自定义数据类型上的规则 语法 sp unbindrule object name futureonly flag 2020 1 5 ftt 85 例10 解除绑定列上的规则 EXECsp unbindrule Employee Age 例11 解除绑定别名类型的规则 EXECsp unbindrule telephone futureonly 2020 1 5 ftt 86 查看规则 使用企业管理器使用系统存储过程sp help可以查看规则的拥有者 创建时间等基本信息 使用系统存储过程sp helptext可以查看规则的定义 2020 1 5 ftt 87 删除规则 使用DROPRULE语句可以删除当前数据库中的一个或多个规则 在删除一个规则之前 应首先将规则从它所捆绑的列或用户自定义数据类型上解除下来 当要删除的规则仍捆绑在列或用户自定义数据类型上时 执行DROPRULE会显示错误信息 同时DROPRULE操作将被撤销 2020 1 5 ftt 88 例12 删除规则 先解除绑定在列上的该规则EXECsp unbindrule Employee Age 然后在删除该规则DROPRULErange rule 2020 1 5 ftt 89 3默认值 默认值可以是常量 内置函数或数学表达式 使用CREATETABLE语句创建表时 使用DEFAULT子句为表中的列提供默认值 使用CREATEDEFAULT语句来创建一个默认对象 然后再将其捆绑到列或用户自定义数据类型上 2020 1 5 ftt 90 创建默认值 使用T SQL语句CREATEDEFAULT可以创建默认对象 语法 CREATEDEFAULTdefaultASconstant expression 2020 1 5 ftt 91 捆绑默认值 可以使用sp bindefault系统存储过程语法 sp bindefault default object name futureonly flag 使用sp unbindefault系统存储过程可以解除捆绑导列或用户自定义数据类型上的默认值 语法 sp unbindefault object name futureonly flag 2020 1 5 ftt 92 例13 创建默认值addressdefault CREATEDEFAULTaddressdefaultAS 杭州下沙 例14 绑定默认值 EXECsp bindefault addressdefault Employee Address 例15 解除默认值 EXECsp unbindefault Employee Address 2020 1 5 ftt 93 查看默认值 可使用企业管理器 可使用系统存储过程sp help可以查看默认值的拥有者 创建时间等基本信息 可使用系统存储过程sp helptext可以查看默认值的定义 2020 1 5 ftt 94 删除默认值 使用DROPDEFAULT语句可以删除当前数据库中的一个或多个默认值 注意 在删除默认值之前 应使用sp unbindefault系统存储过程来解除该默认值在列或用户自定义数据类型上的捆绑 2020 1 5 ftt 95 数据完整性小结 数据库的完整性是为了保证数据库中存储的数据是正确的RDBMS完整性实现的机制完整性约束定义机制完整性检查机制违背完整性约束条件时RDBMS应采取的动作在关系系统中 最重要的完整性约束是实体完整性参照完整性用户定义的完整性 2020 1 5 ftt 96 数据库完整性的定义一般由SQL的DDL语句实现作为数据库模式的一部分存入数据字典在数据库数据修改时 RDBMS的完整性检查机制就按照数据字典中定义的这些约束进行检查完整性机制的实施会影响系统性能 随着硬件性能的提高 数据库技术的发展 目前的RDBMS都提供了定义和检查实体完整性 参照完整性和用户定义的完整性的功能 对于违反完整性的操作要根据应用语义来选择合适的处理策略 以保证数据库的正确性 2020 1 5 ftt 97 可能破坏参照完整性的情况及违约处理 2020 1 5 ftt 98 完整性小结 续 触发器 实现数据库完整性的一个重要方法是触发器是定义在关系表上的由事件驱动的特殊过程可以用于数据库完整性检查也可以用来实现数据库系统的其他功能数据库安全性应用系统的一些业务流程和控制流程基于规则的数据和业务控制功能不同的RDBMS实现触发器的语法不同 2020 1 5 ftt 99 作业1 准备第三次上机实验内容 1 详见附录A 上机实验八完整性约束的实现 在MicrosoftSQLServer环境中 上机实现书中第4章的各例题 2020 1 5 ftt 100 SQL语言高级应用 二 4 3数据库安全性 2020 1 5 ftt 101 数据库保护 数据库中的数据由DBMS统一管理控制 为适应数据共享的环境 DBMS必须提供数据的安全性 完整性 并发控制和数据备份恢复等数据保护能力 以保证数据库中的数据安全可靠和正确有效 2020 1 5 ftt 102 1 完整性 数据库的完整性是指数据库中数据的正确性与相容性 SQL语言定义完整性约束条件CREATETABLE语句ALTERTABLE语句实体完整性 primarykey unique参照完整性 foreignkey用户定义完整性 check default 2020 1 5 ftt 103 2 安全性 安全性 保护数据库 防止不合法的使用所造成的数据泄露和破坏 保证数据安全性的主要措施 存取控制 控制用户只能存取他有权存取的数据 规定不同用户对于不同数据对象所允许执行的操作 2020 1 5 ftt 104 3 并发控制 并发控制 当多个用户并发地对数据库进行操作时 对他们加以控制 协调 以保证并发操作正确执行 保持数据库的一致性 SQL语言并发控制能力 提供事务开始 事务结束 提交 撤销等概念 2020 1 5 ftt 105 4 数据备份恢复 恢复 当发生各种类型的故障导致数据库处于不一致状态时 将数据库恢复到一致状态的功能 SQL语言恢复功能 提供事务回滚 重做等概念 UNDO REDO 2020 1 5 ftt 106 数据安全性 数据库系统的安全性是数据库保护的首要问题 安全性是相对的 加强安全性是应用开发一个重要的方面 安全性与保密性不可分 本节主要讨论安全性的一般概念和方法 介绍SQLServer数据库系统的安全性措施 2020 1 5 ftt 107 数据库安全 数据库的破坏一般来自以下的四个方面 系统故障 并发所引起的数据不一致 输入或更新数据库数据有误 人为的破坏 所谓数据库安全 Security 就是要防止其中第四种 2020 1 5 ftt 108 数据库安全性概念 何为数据库安全性 保护数据库以防止不合法的使用所造成的数据泄露 更改或破坏 系统安全保护措施是否有效是数据库系统的主要性能指标之一 安全性问题和保密问题密切相关 前者主要涉及数据的存取控制 修改和传播的技术手段 后者在很大程度上是法律 政策 伦理 道德等问题 一些国家已成立了专门机构对数据的安全保密制订了法律道德准则和政策法规 2020 1 5 ftt 109 数据库安全保密方式 数据库安全保密方式有 系统处理的和物理的两个方面 所谓物理的是指 对于强力逼迫透露口令 在通讯线路上窃听 以至盗窃物理存储设备等行为而采取的将数据编为密码 加强警卫以识别用户身份和保护存储设备等措施 所谓系统处理的是指利用计算机系统方面的技术手段 本节主要讲述系统处理方面的数据库安全措施 2020 1 5 ftt 110 计算机安全措施设置模型 在一般计算机系统中 安全措施是一级一级层层设置的 有如下的模型 2020 1 5 ftt 111 数据库安全措施 数据库系统的安全措施是建立在计算机系统基础之上的 通常有五个方面 1 用户标识和鉴定2 存取控制3 定义视图4 审计5 数据加密 2020 1 5 ftt 112 安全措施一 用户标识和鉴定 用户标识和鉴定常用的方法有 1 用一个用户名或用户标识号标明用户身份 2 口令 Password 3 系统提供一个随机数 用户根据预先约定好的某一过程或者函数进行计算 系统根据用户计算结果是否正确进一步鉴定用户身份 用户标识和鉴定可重复多次 2020 1 5 ftt 113 安全措施二 存取控制 对于获得上机权的用户还要根据预先定义好的用户权限进行存取控制 保证用户只能存取他有权存取的数据 所谓用户权限是指不同的用户对于不同的数据对象允许执行的操作权限 它由两部分组成 数据对象和操作类型 2020 1 5 ftt 114 安全措施三 视图