第5章-数据库管理-数据库技术基础课件

数据库技术基础第5章数据库管理数据库技术基础第5章数据库管理第5章数据库管理5.1学习目标5.2视图5.3索引5.4安全性5.5完整性5.6系统表5.7小结第5章数据库管理5.1学习目标5.1学习目标学完本章后，读者应具备以下能力：理解视图的概念和作用。能建立视图，会使用视图实现数据的逻辑独立性。理解索引的概念和作用。掌握建立索引的一般规则，能利用索引改善查询性能。理解数据库的操作权限，掌握授权的方法。理解角色的概念，能利用角色简化权限管理。理解参照完整性的概念以及对SQL语句的影响。基本掌握用户定义完整性约束的方法。5.1学习目标学完本章后，读者应具备以下能力：第5章数据库管理5.1学习目标5.2视图5.3索引5.4安全性5.5完整性5.6系统表5.7小结第5章数据库管理5.1学习目标5.2视图视图是从一个或多个表中导出的表，用户可以像对表一样对它进行查询，在SELECT语句中可以出现表的地方都可以出现视图。视图是一个虚表，在数据库中只存储视图的定义（一个SELECT语句）而不存放视图的数据，这些数据仍存放在导出视图的基本表中，直到用户使用视图时才去执行视图的定义，求出数据。5.2视图视图是从一个或多个表中导出的表，用户可以像对表一5.2视图（续）

5.2.1建立视图5.2.2删除视图5.2.3查询视图5.2.4更新视图5.2.5视图的作用5.2视图（续）5.2.1建立视图SQL语言用CREATEVIEW命令建立视图，其一般格式为：CREATEVIEW<视图名>[(<列名>[，

<列名>]…)]AS<子查询>[WITHCHECKOPTION]；组成视图的列名可以全部省略或者全部指定。5.2.1建立视图SQL语言用CREATEVIEW命令建5.2.1建立视图（续）下列三种情况下必须明确指定组成视图的所有列名：某个目标列不是单纯的列名，而是聚集函数或列表达式。多表连接时选出了几个同名列作为视图的列。需要在视图中为某个列启用更合适的名字。子查询可以是任意复杂的SELECT语句，但通常不允许含有ORDERBY子句和DISTINCT短语。WITHCHECKOPTION表示，对视图进行UPDATE和INSERT操作时，要保证更新后的元组和新插入的元组满足视图定义中子查询的WHERE子句中的条件表达式。5.2.1建立视图（续）下列三种情况下必须明确指定组成视图5.2.1建立视图（续）[例5.1]建立计算机学院学生的视图。CREATEVIEWStudent_CSASSELECT*FROMStudentWHERESdept='计算机学院'例5.1在表Student上建立了视图Student_CS，但是没有明确指出视图Student_CS的列名，则构成视图的列与SELECT子句相同。即Student_CS有Sno、Sname、Sgender、Sage和Sdept共5个列，这5个列分别对应Student的Sno、Sname、Sgender、Sage和Sdept列。CREATEVIEW语句的执行结果是在DBMS的数据字典中保存了视图名和SELECT语句。5.2.1建立视图（续）[例5.1]建立计算机学5.2.1建立视图(续)[例5.2]建立英语课（1156）成绩单的视图。CREATEVIEWEnglish_Grade(Sno,Sname,Grade)ASSELECTStudent.Sno,Sname,GradeFROMStudentJOINSCONStudent.Sno=SC.SnoANDSC.Cno='1156'例2在表Student和SC上建立了视图English_Grade。它有3个列：Sno、Sname和Grade，分别对应表Student的Sno列、Sname列和表SC的Grade列。因为SELECT语句中包含了Student表与SC表的同名列Sno，所以必须在视图名后面明确说明视图的各个列名。5.2.1建立视图(续)[例5.2]建立英语课（11565.2.1建立视图（续）[例5.3]定义一个反映学生出生年份的视图。CREATEVIEWBT_S(Sno,Sname,Sbirthday)ASSELECTSno,Sname,datepart(getdate(),year)－SageFROMStudent由于Student表的Sage列存放了学生的年龄，没有存放其出生年份，例5.3定义的视图由学号、学生姓名和学生出生年份三个列组成。getdate()返回系统日期，datepart函数求出日期中的年份。由于SELECT子句中出现了表达式，则必须指明视图的列名。视图不仅可以建立在一个或多个表上，也可以建立在一个或多个已定义好的视图上，或建立在表与视图上。5.2.1建立视图（续）[例5.3]定义一个反映学生出生5.2.1建立视图（续）[例5.4]建立英语课的成绩在80分以上的学生的视图。CREATEVIEWEnglish_Grade_80ASSELECTSno,Sname,GradeFROMEnglish_GradeWHEREGrade>=80已经定义过的视图可以和表一样使用。例5.4中的FROM子句中出现了在例5.2定义过的视图English_Grade，因此，视图English_Grade_80是建立在视图English_Grade之上。5.2.1建立视图（续）[例5.4]建立英语课的成绩在85.2视图

5.2.1建立视图

5.2.2删除视图5.2.3查询视图5.2.4更新视图5.2.5视图的作用5.2视图5.2.2删除视图当不再需要一个视图时，可以删除它，语句格式为：DROPVIEW<视图名>[例5.5]删除视图Student_CS。DROPVIEWStudent_CSDROPVIEW语句执行后，DBMS从数据字典中删除视图Student_CS和定义它的SELECT语句。[例5.6]删除视图English_Grade。DROPVIEWEnglish_Grade5.2.2删除视图当不再需要一个视图时，可以删除它，语句格5.2视图

5.2.1建立视图5.2.2删除视图

5.2.3查询视图5.2.4更新视图5.2.5视图的作用5.2视图5.2.3查询视图定义视图以后，就可以像对表一样对视图进行查询。[例5.7]查找计算机学院年龄小于19岁的学生的姓名。视图Student_CS包含有计算机学院全体学生的信息，可以直接对视图进行查询。SELECTSnameFROMStudent_CSWHERESage<19

5.2.3查询视图定义视图以后，就可以像对表一样对视图进行5.2.3查询视图（续）对视图进行查询时，DBMS要进行视图消解工作，把对视图的查询转换为对基本表（定义视图时涉及的表）的查询，即把对视图查询的一个SQL语句，转换为对基本表查询的SQL语句。视图消解的基本过程分为４个步骤：３从数据字典中取出定义视图的子查询（SELECT语句）。用子查询的FROM子句替换要执行的SELECT的FROM子句。根据定义视图时，视图的列和基本表的列的对应关系，映射要执行的SELECT子句的列到基本表的列。将定义视图的子查询的WHERE子句的条件表达式合并到要执行的SELECT语句的WHERE子句中，逻辑关系是与关系。5.2.3查询视图（续）对视图进行查询时，DBMS要进行视5.2.3查询视图（续）SELECTSno,SageFROMStudent_CSWHERESage<19SELECT*FROMStudentWHERESdept='计算机学院'SELECTStudent.Sno,Student.SageFROMStudentWHERESage<19ANDSdept='计算机学院'①②③④图5.1视图消解基本过程5.2.3查询视图（续）SELECTSno,SageS5.2.3查询视图（续）[例5.8]假设定义了一个求每个学生学号和平均成绩的视图：CREATEVIEWS_G(Sno,Gavg)ASSELECTSno,AVG(Grade)FROMSCGROUPBYSno现在要查询平均成绩在80分以上的学生学号和平均成绩，可以写出以下的语句：SELECT*FROMS_GWHEREGavg>=80这时，DBMS无法像前面例5.7那样得到一个等价的SELECT语句。DBMS采用第二种视图消解方法。先执行定义视图S_G的SELECT语句，得到了一个结果，把它作为一个临时表tmp_S_G，然后将上面的查询语句改写为：SELECT*FROMtmp_S_GWHEREGavg>=80同样可以得到正确结果。因此，我们可以把视图当作表一样进行查询，而不必关心DBMS如何进行处理。5.2.3查询视图（续）[例5.8]假设定义了一个求每个5.2视图

5.2.1建立视图5.2.2删除视图5.2.3查询视图

5.2.4更新视图5.2.5视图的作用5.2视图5.2.4更新视图更新视图是指向视图中插入（INSERT）、删除（DELETE）和更新（UPDATE）数据。对视图的更新操作也要通过视图消解转换为对表的更新操作。不是所有的视图都是可更新的，因为有些视图的更新不能唯一有意义地转换成对相应表的更新。5.2.4更新视图更新视图是指向视图中插入（INSERT）5.2.4更新视图（续）例如,例5.8定义的视图S_G是由“学号”和“平均成绩”两个属性列组成的，其中“平均成绩”一项是由Student表中对元组分组后计算平均值得来的。如果我们想把视图S_G中学号为2007012的学生的平均成绩改成90分，SQL语句如下：UPDATES_GSETGavg=90WHERESno='2007012'但这个对视图的更新是无法转换成对表SC的更新的，因为系统无法修改各科成绩，以使平均成绩成为90。所以S_G视图是不可更新的。5.2.4更新视图（续）例如,例5.8定义的视图S_G是由5.2.4更新视图（续）行列子集视图若一个视图是从单个表导出的，并且只是去掉了表的某些行和某些列，但保留了主码，则这类视图称为行列子集视图。应该指出的是，不可更新的视图与不允许更新的视图是两个不同的概念。前者指理论上已证明其是不可更新的视图。后者指实际系统中不支持其更新，但它本身有可能是可更新的视图。对于行列子集视图的更新，DBMS也要进行视图消解，把对视图的更新转换为对基本表的更新。5.2.4更新视图（续）行列子集视图5.2.4更新视图（续）[例５.9]将计算机学院的学生马翔的姓名改为马飞翔。UPDATEStudent_CSSETSname='马飞翔'WHERESname='马翔'DBMS进行视图消解后，得到下面的语句：UPDATEStudentSETSname='马飞翔'WHERESname='马翔'ANDSdept='计算机学院'5.2.4更新视图（续）[例５.9]将计算机学院的学生马5.2.4更新视图（续）[例5.10]计算机学院增加一个新生，学号为2007015，姓名为赵新，年龄为20岁。INSERTINTOStudent_CS(Sno,Sname,Sage)VALUES('2007015','赵新',20)转换后的更新语句为：INSERTINTOStudent(Sno,Sname,Sage)VALUES('2007015','赵新',20)5.2.4更新视图（续）[例5.10]计算机学院增加一个5.2.4更新视图（续）[例5.11]删除计算机学院学生赵新，学号是2007015。DELETEFROMStudent_CSWHERESno='2007015'转换为对表的删除操作：DELETEFROMStudentWHERESno='2007015'ANDSdept='计算机学院'5.2.4更新视图（续）[例5.11]删除计算机学院学生5.2.4更新视图（续）要防止用户通过视图对数据库进行增删改时有意或无意地对不属于视图范围内（不满足子查询的过滤条件）的基本表数据进行操作，则在视图定义时要加上WITHCHECKOPTION子句。WITHCHECKOPTION短语相当于在视图上施加了一个元组级约束条件，更新前后的元组必须满足定义视图的子查询的过滤条件。若操纵的元组不满足条件，则拒绝执行该操作。5.2.4更新视图（续）要防止用户通过视图对数据库进行增删5.2.4更新视图（续）[例5.12]建立计算机学院学生的视图，要求进行更新操作前后的元组要保证满足视图的过滤条件（即Sdept列上的值是计算机学院）。CREATEVIEWStudent_CSASSELECT*FROMStudentWHERESdept='计算机学院'WITHCHECKOPTION由于在定义Student_CS视图时加上了WITHCHECKOPTION子句，所以以后对该视图进行插入、修改时，DBMS会自动检查插入的元组和修改后的元组在Sdept列上的值是否等于计算机学院。例如，DBMS会拒绝执行下面的对视图进行修改的SQL语句。INSERTINTOStudent_CS(Sno,Sname,Sage)VALUES('2007015','赵新',20)--新插入的元组在Sdept的值不等于计算机学院

UPDATEStudent_CSSETSdept='环境学院'--试图将Sdept的值由计算机学院更改为环境学院WHERESno='2007012'5.2.4更新视图（续）[例5.12]建立计算机学院学生5.2视图

5.2.1建立视图5.2.2删除视图5.2.3查询视图5.2.4更新视图

5.2.5视图的作用5.2视图5.2.5视图的作用视图能够简化用户的操作视图可以减少冗余数据视图对重构数据库提供了一定程度的逻辑独立性视图能够对机密数据提供安全保护5.2.5视图的作用视图能够简化用户的操作5.2.5视图的作用（续）例如，将学生关系拆分为SX和SY两个关系：Student(Sno，Sname，Ssex，Sage，Sdept)SX(Sno，Sname，Sage)SY(Sno，Ssex，Sdept)这时，表Student为SX表和SY表自然连接的结果。如果建立一个视图Student：CREATEVIEWStudent(Sno，

Sname，

Ssex，

Sage，

Sdept)ASSELECTSX.Sno，

SX.Sname，

SY.Ssex，

SX.Sage，

SY.SdeptFROMSX，

SYWHERESX.Sno=SY.Sno；尽管数据库的逻辑结构改变了，但应用程序并不必修改。当然，应用程序中修改数据的语句可能仍会因表结构的改变而改变。5.2.5视图的作用（续）例如，将学生关系拆分为SX和SY第5章数据库管理5.1学习目标5.2视图5.3索引5.4安全性5.5完整性5.6系统表5.7小结第5章数据库管理5.1学习目标5.3索引1.1学习目标1.2数据库系统的定义1.3数据库系统的特点123图5.2图书目录和图书的关系5.3索引1.1学习目标1.2数据库系统的定义1.35.3索引（续）

5.3.1索引的基本概念5.3.2索引的建立和维护5.3索引（续）5.3.1索引的基本概念索引的定义索引是一个独立的、物理的数据库结构，基于表的一列或多列而建立，按照列值从小到大或从大到小排序，提供了一个新存取路径。20070122007014200711320072562007278SnoSnameSgenderSageSdept2007012马翔男19计算机学院2007113刘大方男18管理学院2007256史玉明女19管理学院2007278龚兵男19管理学院2007014张晓敏女18计算机学院图5.3建立在表Student的列Sno上的索引5.3.1索引的基本概念索引的定义2007012200705.3.1索引的基本概念（续）图5.3的左面给出了在表Student中学号列上建立的索引，索引项的值按照从小到大的次序排序。表本身提供了一个存取路径，即顺序访问，按照元组存放的先后顺序逐个访问每个元组，2007012、2007113、2007256……索引提供了另外一个存取路径，顺序访问索引，读出一条索引后，再根据指针读取表中的元组。按照索引提供的访问路径，访问元组的次序为2007012、2007014、2007113…5.3.1索引的基本概念（续）图5.3的左面给出了在表StSnoSnameSgenderSageSdept2007012马翔男19计算机学院2007113刘大方男18管理学院2007256史玉明女19管理学院2007278龚兵男19管理学院2007014张晓敏女18计算机学院0140121132562781130145.3.1索引的基本概念（续）图5.4Ｂ＋树形式的索引SnoSnameSgenderSageSdept200701图5.3中以线性表（数组）的形式表示索引。实际上，在数据库中，索引往往被组织成一棵B+树。在B+树中，所有的码都出现在B+

树的叶子节点中，并按照码值从小到大的顺序组织成了一个链表。非叶子节点由n个码和n+1个指针组成，码和指针的排列次序为P0，K1，P1，K2，…，Pn，Kn，K1≤K2…≤Kn，P0，P1，…，Pn指向了n+1个节点，指针Pi-1指向节点中的每个码的值都小于等于Ki。B+

树的具体内容请读者参阅“数据结构”或“数据库原理”的相关书籍。图5.3的索引被组织成一个2阶B+树的形式如图5.4（为了节省空间，节点中的码只给出了后3位）。B+树索引提供了另外一个存取路径，可以快速地定位表中的某个元组。例如，要读取元组2007014，首先在根节点中查找，然后根据指针P0，找到最左面的叶子节点，最后，根据叶子节点的指针，读取所需要的元组。B+树是一棵平衡树，从根节点到任何一个叶子节点的路径长度相同。因此，假设B+树的深度为L，读取表中任何一个元组需要读B+树中的L个节点。5.3.1索引的基本概念（续）图5.3中以线性表（数组）的形式表示索引。实际上，在数据库中索引的分类按照表中建立索引的那一列（或列组合）中的数据是否各不相同，可以将索引分为唯一索引（UNIQUE）和非唯一索引（NOTUNIQUE）。按照索引的结构，可以将其划分为两大类，聚簇索引（ClusteredIndex）和非聚簇索引（Nonclusteredindex）。聚簇索引要求将表中的元组与索引键值以同样的物理顺序存储，非聚簇索引则无此要求。聚簇索引能提高某些类型的查询效率。每个表最多只能有一个聚簇索引。5.3.1索引的基本概念（续）索引的分类5.3.1索引的基本概念（续）SnoSnameSgenderSageSdept2007012马翔男19计算机学院2007113刘大方男18管理学院2007256史玉明女19管理学院2007278龚兵男19管理学院2007014张晓敏女18计算机学院student计算机学院管理学院图5.5非唯一索引5.3.1索引的基本概念（续）SnoSnameSgenderSageSdept200701建立索引的原则值得建立索引：记录有一定规模，而查询只局限于少数记录。索引用得上：索引列在WHERE子句中频繁使用。通常，下列情况需要在表中的某一列或某些列上建立索引：经常用作查询条件的列。需要频繁地按范围搜索的列。连接中频繁使用的列。在ORDERBY子句中经常使用的列。主关键字或外关键字的列。值是唯一的列（如IDENTITY）。先装数据，后建立索引。5.3.1索引的基本概念（续）建立索引的原则5.3.1索引的基本概念（续）5.3索引

5.3.1索引的基本概念

5.3.2索引的建立和维护5.3索引5.3.2索引的建立和维护建立索引格式

CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEX<索引名>ON<表名>(<列名>[<次序>][，<列名>[<次序>]]…)；<表名>是要建索引的表的名字。索引可以建立在表的一列或多列上，各列名之间用逗号分隔每个<列名>后面还可以用<次序>指定索引值的排列次序，可选ASC（升序）或DESC（降序），默认值为ASC。5.3.2索引的建立和维护建立索引UNIQUE表明此索引的每一个索引值只对应唯一的数据记录。CLUSTERED表示要建立的索引是聚簇索引。NONCLUSTERED意味着建立非聚簇索引。例如，执行下面的CREATEINDEX语句：CREATECLUSTEREDINDEXStusnameONStudent(Sname)将在Student表Sname列上建立一个聚簇索引，而且Student表中的记录将按照Sno值的升序存放。5.3.1索引的建立和维护（续）UNIQUE表明此索引的每一个索引值只对应唯一的数据记录。例[例5.13]

为学生-课程数据库中的Student、Couse、SC三个表建立索引。其中Student表按学号升序建唯一索引，Course表按课程号降序建唯一索引，SC表按学号升序和课程号降序建唯一索引。CREATEUNIQUEINDEXStusnoONStudent(Sno)CREATEUNIQUEINDEXCoucnoONCourse(CnoDESC)CREATEUNIQUEINDEXSCnoONSC(SnoASC,CnoDESC)5.3.1索引的建立和维护（续）[例5.13]为学生-课程数据库中的Student、Cou删除索引格式

DROPINDEX<表名.索引名>[例5.14]删除Student表的Stusname索引。DROPINDEXStudent.Stusno删除索引时，系统从数据字典中删去有关该索引的描述，同时从数据区删除索引的数据。5.3.1索引的建立和维护（续）删除索引[例5.14]删除Student表的Stusname第5章数据库管理5.1学习目标5.2视图5.3索引5.4安全性5.5完整性5.6系统表5.7小结第5章数据库管理5.1学习目标5.4安全性数据库中存放了一个组织的全部数据，许多日常工作都要依赖于数据库系统，如果数据库的数据安全出现了问题，会影响到组织的正常运行。5.4安全性数据库中存放了一个组织的全部数据，许多日常工作5.4安全性

5.4.1登录名与用户5.4.2权限5.4.3授权5.4.4收回权限5.4.5角色5.4.6一个实例5.4.7权限审核5.4安全性5.4.1登录名与用户增加登录名格式一sp_addlogin[@loginame=]'login'[,[@passwd=]'password'][,[@defdb=]'database'][,[@deflanguage=]'language'][,[@sid=]sid][,[@encryptopt=]'encryption_option']登录名用户名图5.6使用SQLServer的登录过程5.4.1登录名与用户增加登录名登录名用户名图5.6使用参数[@loginame=]'login'：登录的名称。[@passwd=]'password'：登录密码。[@defdb=]'database'：登录的默认数据库。[@deflanguage=]'language'：用户登录到

SQLServer时系统指派的默认语言。[@sid=]sid：安全标识号

(SID)。[@encryptopt=]'encryption_option'：指定当密码存储在系统表中时，密码是否要加密。功能：存储过程sp_addlogin添加SQLServer登录名。权限：只有

sysadmin和

securityadmin固定服务器角色的成员才可以执行

sp_addlogin。5.4.1登录名与用户（续）参数5.4.1登录名与用户（续）[例5.15]增加登录名st1、st2、st3、st4、U5、U6和U7，密码使用默认设置值NULL，默认数据库为S_C_SC。以sa登录进SQLServer，使用查询分析器，执行下面的语句：sp_addlogin @loginame='st1',@defdb='S_C_SC'GOsp_addlogin @loginame='st2',@defdb='S_C_SC'GOsp_addlogin @loginame='st3',@defdb='S_C_SC'GOsp_addlogin @loginame='st4',@defdb='S_C_SC'GOsp_addlogin @loginame='U5',@defdb='S_C_SC'GOsp_addlogin @loginame='U6',@defdb='S_C_SC'GOsp_addlogin @loginame='U7',@defdb='S_C_SC'GO5.4.1登录名与用户（续）[例5.15]增加登录名st1、st2、st3、st4、U格式二：sp_grantlogin[@loginame=]'login'参数：[@loginame=]‘login’表示要添加的

WindowsNT用户或组的名称。WindowsNT组和用户必须用

WindowsNT域名限定，格式为"域\用户"，例如

London\Joeb。功能：使

WindowsNT用户或组账户得以使用

Windows身份验证连接到

SQLServer。权限：仅

sysadmin或

securityadmin固定服务器角色的成员可以执行

sp_grantlogin。5.4.1登录名与用户（续）格式二：5.4.1登录名与用户（续）[例5.16]将计算机机lsnmobile的Windows本地用户lsn增加为登录名。sp_grantlogin'lsnmobile\lsn'GO5.4.1登录名与用户（续）[例5.16]将计算机机lsnmobile的Windows删除登录名格式一：

sp_droplogin[@loginame=]'login'参数：[@loginame=]‘login’表示将被删除的登录。login必须已经存在于

SQLServer中。功能：删除

SQLServer登录，以阻止使用该登录名访问

SQLServer。权限：只有

sysadmin和

securityadmin固定服务器角色的成员才能执行

sp_droplogin。5.4.1登录名与用户（续）删除登录名5.4.1登录名与用户（续）格式二：sp_revokelogin[@loginame=]'login'参数：[@loginame=]'login'：是

WindowsNT用户或组的名称。功能：从

SQLServer中删除用

sp_grantlogin或

sp_denylogin创建的

WindowsNT用户或组的登录项。权限：只有

sysadmin和

securityadmin固定服务器角色的成员才能执行

sp_revokelogin。5.4.1登录名与用户（续）格式二：5.4.1登录名与用户（续）[例5.17]删除登录名U5。sp_droplogin'U5'GO5.4.1登录名与用户（续）[例5.17]删除登录名U5。5.4.1登录名与用户（续增加数据库用户格式：sp_grantdbaccess[@loginame=]'login'[,[@name_in_db=]'name_in_db'[OUTPUT]]参数：[@loginame=]'login'：SQLServer中的登录名。[@name_in_db=]'name_in_db'[OUTPUT]：数据库中用户的名称5.4.1登录名与用户（续）增加数据库用户5.4.1登录名与用户（续）功能：为

SQLServer登录或

WindowsNT用户或组在当前数据库中添加一个用户安全账户，并使其能够被授予在数据库中执行活动的权限。权限：只有

sysadmin固定服务器角色、db_accessadmin和

db_owner固定数据库角色的成员才能执行

sp_grantdbaccess。5.4.1登录名与用户（续）功能：为SQLServer登录或WindowsNT[例5.18]使登录名st1、st2、st3、st4、u5、u6和u7分别成为数据库S_C_SC的用户U1、U2、U3、U4、U5、U6和U7。USES_C_SCGOsp_grantdbaccess'st1','U1'GOsp_grantdbaccess'st2','U2'GOsp_grantdbaccess'st3','U3'GOsp_grantdbaccess'st4','U4'GOsp_grantdbaccess‘u5','U5'GOsp_grantdbaccess‘u6','U6'GOsp_grantdbaccess‘u7','U7'GO5.4.1登录名与用户（续）[例5.18]使登录名st1、st2、st3、st4、u5删除数据库用户格式：sp_revokedbaccess[@name_in_db=]'name'参数：[@name_in_db=]‘name’：是要删除的账户名。功能：从当前数据库中删除安全账户。权限：只有

sysadmin固定服务器角色成员及

db_accessadmin和

db_owner固定数据库角色成员才能执行

sp_revokedbaccess。[例5.19]删除数据库S_C_SC的用户U5。sp_revokedbaccess'U5'GO5.4.1登录名与用户（续）删除数据库用户[例5.19]删除数据库S_C_SC的用户U5.4安全性

5.4.1登录名与用户

5.4.2权限5.4.3授权5.4.4收回权限5.4.5角色5.4.6一个实例5.4.7权限审核5.4安全性5.4.2权限Select,Insert,Update,Delete,AllSelect,Insert,Update,Delete,Alter,Index,All数据库表视图列Connect,CreateUpdate图5.6数据库对象的层次和权限5.4.2权限Select,Insert,Update5.4.2权限（续）１.数据库成为数据库管理系统的用户后，还要获得对数据库的Connect权限，才能连接到数据库，使用数据库。Create权限说明用户可以在数据库中使用CREATE语句创建视图、表等数据库对象。数据库的创建者自动拥有对数据库的全部权限。5.4.2权限（续）１.数据库5.4.2权限（续）2.表按照对表的操作类型划分，有6类权限。如果某用户对一个表拥有Select、Insert、Delete、Update权限，则表示该用户可以在SELECT、INSERT、DELETE、UPDATE语句中引用该表，即可以对表执行选择、插入、删除和修改操作。Alter权限表示可以用ALTER语句改变表的模式以及施加在表上的各种约束。拥有Index权限意味着能用CREATEINDEX语句在表上建立索引。All是AllPrivileges的缩写，即拥有对表的所有6种权限。表的建立者或者表的所有者拥有对表所有的操作权利。5.4.2权限（续）2.表3.视图视图的4种权限的含义同表，视图的创建者自动拥有全部权限。4.列列的权限是对表权限的进一步细化。一般地讲，拥有对表的Update权限也就拥有了对表中所有列的Update权限，但是，在分配对表的Update权限时，也可以排除对某些列的Update权限。5.4.2权限（续）3.视图5.4.2权限（续）5.4安全性

5.4.1登录名与用户5.4.2权限

5.4.3授权5.4.4收回权限5.4.5角色5.4.6一个实例5.4.7权限审核5.4安全性5.4.3授权SQL语言用GRANT语句向用户授予操作权限，GRANT语句的一般格式为：GRANT<权限>[，<权限>]…[ON<对象名>]TO<用户>[，<用户>]…[WITHGRANTOPTION]；GRANT语句在数据库管理系统的数据字典里记录下哪个用户拥有哪些（个）数据库对象的何种权限。5.4.3授权SQL语言用GRANT语句向用户授予操作权限[例5.20]把查询Student表的权限授给用户U1。GRANTSELECTONStudentTOU1请思考，谁有权限执行这个语句呢？[例5.21]把对Student表和Course表的全部操作权限授予用户U2和U3。GRANTALLPRIVILEGESONStudentTOU2,U3GOGRANTALLPRIVILEGESONCourseTOU2,U3GO5.4.3授权（续）[例5.20]把查询Student表的权限授给用户U1。5.[例5.22]把对表SC的查询权限授予所有用户。GRANTSELECTONSCTOPUBLIC注意：PUBLIC代表所有的用户，这些用户既可以是目前数据库管理系统中已经有的用户，也可以是目前没有，以后才增加的用户。[例5.23]把查询Student表和修改学生学号的权限授给用户U4。GRANTUPDATE(Sno),SELECTONStudentTOU4请注意，U4只能更改列Sno的值，而不能修改表Student的其他列。如果允许U4修改表Student的全部列，则应该执行下面的语句：GRANTUPDATE,SELECTONStudentTOU45.4.3授权(续)[例5.22]把对表SC的查询权限授予所有用户。5.4.3[例5.24]把对表SC的INSERT权限授予U5用户，并允许将此权限再授予其他用户。GRANTINSERTONSCTOU5WITHGRANTOPTIONWITHGRANTOPTION表明，U5不仅拥有了对表SC的INSERT权限，还可以传播此权限。例如U5可以将此权限授予U6：GRANTINSERTONSCTOU6WITHGRANTOPTION同样，U6还可以将此权限授予U7：GRANTINSERTONSCTOU7因为U6未给U7传播的权限，U7不能再传播此权限。5.4.3授权(续)[例5.24]把对表SC的INSERT权限授予U5用户，并允5.4安全性

5.4.1登录名与用户5.4.2权限5.4.3授权

5.4.4收回权限5.4.5角色5.4.6一个实例5.4.7权限审核5.4安全性5.4.4收回权限授予的权限可以由授予者用REVOKE语句收回。REVOKE语句的一般格式为：REVOKE<权限>[，<权限>]…[ON<对象名>]FROM<用户>[，<用户>]…；5.4.4收回权限授予的权限可以由授予者用REVOKE语句[例5.25]把用户U4修改学生学号的权限收回。。REVOKEUPDATE(Sno)ONStudentFROMU4[例5.26]收回所有用户对表SC的查询权限。REVOKESELECTONSCFROMPUBLIC[例5.27]把用户U5对SC表的INSERT权限收回。REVOKEINSERTONSCFROMU5CASCADE5.4.4收回权限[例5.25]把用户U4修改学生学号的权限收回。。安全性

5.4.1登录名与用户5.4.2权限5.4.3授权5.4.4收回权限

5.4.5角色5.4.6一个实例5.4.7权限审核5.4安全性5.4.5角色角色是一个DBMS的用户的集合，该集合中的用户要操作相同的数据库对象，需要拥有相同的权限。I角色1角色2U1U2U3U4U5T1T1T2T2T3USAS图5.7角色及其授权5.4.5角色角色是一个DBMS的用户的集合，该集合中的用1.角色管理(1)增加/删除角色[例5.28]在数据库中增加角色Managers，删除角色Sales。sp_addrole'Managers'GOsp_droprole'Sales'GO5.4.5角色(续)1.角色管理(1)增加/删除角色5.4.5角色(续)(2)给角色指派/撤销成员[例5.29]使数据库用户John成为角色Sales的成员，从数据库角色Manager中删除成员Jeff。sp_addrolemember'Sales','John'GOsp_droprolemember'Managers','Jeff'GO[例5.30]特别地，一个角色也可以作为另一个角色的成员，让角色Managers成为角色Sales的成员。sp_droprolemember'Sales','Managers'GOManagers成为Sales的成员则意味着Managers的成员属于Managers和Sales两个角色。5.4.5角色(续)(2)给角色指派/撤销成员5.4.5角色(续)2.固定角色(1)服务器角色Sysadmin： 能够在SQLServer内作任何事情。Serveradmin ：能够修改SQLServer的设置和停止SQLServer。Setupadmin：可以管理链接服务器和启动过程。Securityadmin：管理服务器用户。Processadmin：可以管理在

SQLServer中运行的进程。Dbcreator： 能够创建、修改和删除数据库。Diskadmin： 能够管理磁盘文件。Bulkadmin： 可以执行BULKINSERT语句。5.4.5角色(续)2.固定角色5.4.5角色(续)(2)数据库角色Db_Owner：在数据库中有全部权限。Db_accessadmin：管理数据库用户。Db_datareader：可以读取数据库内任何用户表中的所有数据。Db_datawriter：可以更改数据库内任何用户表中的所有数据。Db_ddladmin：可以发出所有的DDL，但不能发出GRANT、REVOKE语句。Db_backupoperator： 具有备份数据库的限利。5.4.5角色(续)(2)数据库角色5.4.5角色(续)Db_securityadmin 可以管理角色和角色成员资格；用GRANT、REVOKE语句将任何数据对象的全部权限授予任何数据库用户。Db_denydatareader 不能读取数据库内任何用户表中的任何数据Db_denydatawriter 不能更改数据库内任何用户表中的任何数据。Db_securityadmin 可以管理角色和角色成员资格[例5.31]使用户DBManager拥有创建数据库的权限。只要将DBmanager增加到服务器固定角色Dbcreator，用户Dbmanager就拥有了创建数据库的权限。sp_addsrvrolememberDBmanager,DbcreatorGO[例5.32]使用户Manager拥有管理数据库的常用权限。--建立数据库用户Manager（登录名也是Manager），由该用户管理数据库的用户管理和权限管理5.4.5角色(续)[例5.31]使用户DBManager拥有创建数据库的权限sp_grantdbaccess'Manager','Manager'GOsp_addrolemember'Db_accessadmin','Manager'--管理数据库用户GO--可以发出所有的DDL，但不能发出GRANT、REVOKE语句sp_addrolemember'Db_ddladmin','Manager'GOsp_addrolemember'Db_securityadmin','Manager'--可以管理角色和角色成员资格；--用GRANT、REVOKE语句将任何数据对象的全部权限授予任何数据库用户。GOsp_addrolemember'Db_backupoperator','Manager'--具有备份数据库的权利。GO5.4.5角色(续)sp_grantdbaccess'Manager','Ma5.4安全性

5.4.1登录名/用户5.4.2权限5.4.3授权5.4.4收回权限5.4.5角色

5.4.6一个实例5.4.7权限审核5.4安全性5.4.6一个实例在SQLServer中建立3.4节的实例数据库，数据库名称为S_C_SC，数据库中有Student、Course和SC三个表。假设由用户DBManager负责创建数据库和三个表，作为数据库的所有者。由用户Manager负责管理S_C_SC数据库的用户、角色，给用户赋予必要的权限，创建索引、视图等工作。使用数据库的用户有学生、职员和教师三类群体。所有的用户都可以查询Student、Course和SC中的内容。另外，教师需要给学生登记考试成绩，要拥有对SC的Grade列的修改权限。职员负责更新表Student和Course，还要将学生的选课信息添加到SC表，因此，要拥有对SC表的INSERT权限。5.4.6一个实例在SQLServer中建立3.4节的实使用户DBManage和Manager成为固定服务器和固定数据库角色，获得应有的权限。参见例5.31和5.32。建立数据库角色Professors、Clerks用户Manager具有建立角色的权限，由他（她）负责用查询分析器执行下面的系统存储过程，建立角色。sp_addroleProfessorsGosp_addroleClerksGO5.4.6一个实例(续)使用户DBManage和Manager成为固定服务器和固定将用户作为成员加入角色。sp_addrolememberProfessors,U5GO分配权限给角色--PUBLIC是固定数据库角色，数据库的所有用户都是它的成员GRANTSELECTONStudentTOPUBLICGRANTSELECTONSCTOPUBLICGRANTSELECTONCourseTOPUBLICGOGRANTINSERT,DELETE,UPDATEONStudentTOClerksGRANTINSERT,DELETE,UPDATEONCourseTOClerksGRANTINSERTONSCTOClerksGOGRANTUPDATE(Grade)ONSCTOProfessorsGo5.4.6一个实例(续)将用户作为成员加入角色。5.4.6一个实例(续)5.4安全性

5.4.1登录名/用户5.4.2权限5.4.3授权5.4.4收回权限5.4.5角色5.4.6一个实例

5.4.7权限审核5.4安全性5.4.7权限审核用户提交给DBMS的每条SQL语句在执行前都要经过权限审核，只有拥有必要的权限，才能执行SQL语句。假设用户U1要执行SQL语句：UPDATEStudentSETSage=Sage+1WHERESno='2000015'DBMS逐一检查下面的条件，如果满足了其中的一条，则U1拥有对表Student的Sage列的UPDATE权限，可以执行UPDATE语句，否则，DBMS将拒绝执行该语句。如果U1是Student的创建者。如果拥有对Student的UPDATE权限。如果U1所在的某个角色拥有对Student的UPDATE权限。如果PUBLIC拥有对Student的UPDATE权限。5.4.7权限审核用户提交给DBMS的每条SQL语句在执行第5章数据库管理5.1学习目标5.2视图5.3索引5.4安全性5.5完整性5.6系统表5.7小结第5章数据库管理5.5完整性实体完整性（EntityIntegrity）若属性A是构成关系R主码的属性组中的任何一个属性，则任何一个元组在属性A上不能取空值（Null）。参照完整性（ReferentialIntegrity）如果关系R的属性组A是关系S的主码，则称A是关系R的外码（FOREIGNKEY）。关系R的任何一个元组在外关键字F上的取值要么是空值，要么是被参照关系S中一个元组的主码值。用户定义的完整性（User-definedIntegrity）用户定义的完整性就是针对某一具体应用环境而施加的约束条件。它反映某一具体应用所涉及的数据必须满足的语义要求。5.5完整性实体完整性（EntityIntegrity）

5.5.1实体完整性5.5.2参照完整性5.5.3属性值限制5.5.4元组级限制5.5.5完整性修改5.5.6空值的处理5.5完整性(码)5.5完整性(码)5.5.1实体完整性[例5.33]将Student表中的Sno属性定义为主码。CREATETABLEStudent(SnoCHAR(7)PRIMARYKEY,--在列级定义主码SnameVARCHAR(12)NOTNULL,SgenderCHAR(2),SageSMALLINT,SdeptVARCHAR(30))或者CREATETABLEStudent(SnoCHAR(7),SnameVARCHAR(12)NOTNULL,SgenderCHAR(2),SageSMALLINT,SdeptVARCHAR(30),PRIMARYKEY(Sno))--在表级定义主码对多属性构成的主码只有一种说明方法。5.5.1实体完整性[例5.33]将Student表中的[例5.34]将SC表中的Sno，Cno属性组定义为主码。CREATETABLESC(SnoCHAR(7),CnoCHAR(4),GradeSMALLINT,PRIMARYKEY(Sno,Cno))--在表级定义主码5.5.1实体完整性（续）[例5.34]将SC表中的Sno，Cno属性组定义为主码。

5.5.1实体完整性

5.5.2参照完整性5.5.3属性值限制5.5.4元组级限制5.5.5完整性修改5.5.6空值的处理5.5.1实体完整性（续）5.5.1实体完整性（续）5.5.2参照完整性关系SC中一个元组表示一个学生选修的某门课程的成绩，Sno代表Student中的一个学生，Cno代表Course中的一门课程。因此Sno，Cno分别是对Student和Course的参照引用。[例5.35]定义SC中的参照完整性CREATETABLESC(SnoCHAR(7),CnoCHAR(4),GradeSMALLINT,PRIMARYKEY(Sno，

Cno),FOREIGNKEY(Sno)REFERENCESStudent(Sno),FOREIGNKEY(Cno)REFERENCESCourse(Cno))5.5.2参照完整性关系SC中一个元组表示一个学生选修的某对表SC和Student有4种可能破坏参照完整性的情况SC表中增加一个元组，该元组的Sno属性的值在表Student中找不到一个元组其Sno属性的值与之相等。修改SC表中的一个元组，修改后该元组的Sno属性的值在表Student中找不到一个元组其Sno属性的值与之相等。5.5.2参照完整性(续)对表SC和Student有4种可能破坏参照完整性的情况5.5从Student表中删除一个元组后，造成SC表中某些元组的Sno属性的值在表Student中找不到一个元组其Sno属性的值与之相等。修改Student表中的一个元组的Sno属性后，造成SC表中某些元组的Sno属性的值在表Student中找不到一个元组其Sno属性的值与之相等。5.5.2参照完整性(续)从Student表中删除一个元组后，造成SC表中某些元组的S处理策略拒绝(reject)。不允许该操作执行。该策略一般设置为默认策略。瀑布删除(cascade)当删除或修改被参照表(上例中Student)的一个元组时造成了不一致，则删除参照表中(上例中的SC)的所有造成不一致的元组。设置为空值（set-null）5.5.2参照完整性(续)处理策略5.5.2参照完整性(续)图5.8表之间的关系图5.5.2参照完整性(续)图5.8表之间的关系图5.5.2参照完整性(续)

5.5.1实体完整性5.5.2参照完整性

5.5.3属性值限制5.5.4元组级限制5.5.5完整性修改5.5.6空值的处理5.5.2参照完整性(续)5.5.2参照完整性(续)5.5.3属性值限制非空值限制[例5.36]在定义SC表时，说明Grade属性不允许取空值。GradeSMALLINTNOTNULL如果不明确说明的话，属性的值允许取空值5.5.3属性值限制非空值限制[例5.36]在定义SC表指定允许的取值范围[例5.37]Student表的Ssex只允许取“男”和“女”。Sgender char(2)CHECK(SgenderIN('男','女'))[例5.38]SC表的Grade的值应该在0和100之间。GradeSMALLINTCHECK(Grade>=0ANDGrade<=100)5.5.3属性值限制（续）指定允许的取值范围[例5.37]Student表的Ssex5.5完整性

5.5.1实体完整性5.5.2参照完整性5.5.3属性值限制

5.5.4元组级限制5.5.5完整性修改5.5.6空值的处理5.5完整性5.5.4元组级限制元组级的限制同属性值限制相比，可以设置属性之间取值的组合[例5.39]当学生的性别是男时，其名字不能以Ms.打头。CREATETABLEStudent(SnoCHAR(7)PRIMARYKEY,SnameVARCHAR(12)NOTNULL,SgenderCHAR(2),SageSMALLINT,SdeptVARCHAR(30),CHECK(Sgender='女'ORSnameNOTLIKE'Ms.%'))5.5.4元组级限制元组级的限制同属性值限制相比，可以设置5.5完整性

5.5.1实体完整性5.5.2参照完整性5.5.3属性值限制5.5.4元组级限制

5.5.5完整性修改5.5.6空值的处理5.5完整性5.5.5完整性修改命名在完整性约束条件，例如PRIMARYKEY、CHECK前增加一个关键字CONSTRAINT，后面跟上一个名字。[例5.40]将Student表的主关键字限制命名为StudentKeyCREATETABLEStudent(SnoCHAR(7),SnameVARCHAR(12)NOTNULL,SgenderCHAR(2),SageSMALLINT,SdeptVARCHAR(30),CONSTRAINTStudentKeyPRIMARYKEY(Sno))5.5.5完整性修改命名在完整性约束条件，例如PRIMAR修改表中的完整性限制[例41]去掉表Student中的StudentKey限制，增加Ssex只能取男和女的显示限制。ALTERTABLEStudentDROPCONSTRAINTStudentKeyALTERTABLEStudentADDCONSTRAINTGenderCHECK(SgenderIN('男','女'))5.5.5完整性修改(续)修改表中的完整性限制[例41]去掉表Student中的St5.5完整性

5.5.1实体完整性5.5.2参照完整性5.5.3属性值限制5.5.4元组级限制5.5.5完整性修改

5.5.6空值的处理5.5完整性5.5.6空值的处理空值的产生[例5.42]向SC表中插入一个元组，学生号是2000012，课程号是1128，成绩为空。INSERTINTOSC(Sno,Cno,Grade)VALUES('2007012','1128',NULL)或INSERTINTOSC(Sno,Cno)VALUES('2007012','1128')在插入语句中，没有赋值的属性，其值为空值。[例5.43]将Student表中学生号为2000012的学生所属院系改为空值。UPDATEStudentSETSdept=NULLWHERESno='2007012'另外，外连接也会产生空值。5.5.6空值的处理空值的产生[例5.42]向SC表中插空值的判断判断一个属性的值是否为空值，不能写成Grade=NULL，而应该用ISNULL来表示。[例5.44]从SC表中找出缺考的学生号和课程号（假设缺考学生的成绩为空值）。SELECTSno,CnoFROMSCWHEREGradeISNULL5.5.6空值的处理(续)空值的判断判断一个属性的值是否为空值，不能写成Grade=能否取空值的限制主关键字中的属性不能取空值，加了UNIQUE限制的属性不能取空值，作了NOTNULL限制的属性不能取空值。有空值的算术运算、比较运算和逻辑运算5.5.6空值的处理(续)能否取空值的限制5.5.6空值的处理(续)[例5.45]选出选修课程号1156的不及格的学生。SELECTSnoFROMSCWHEREGrade<60ANDCno='1156'选出的学生是那些参加了考试（Grade属性为非空值）而不及格的学生，不包括缺考的学生。因为前者使条件Grade<60的值为TRUE，后者使条件的值为UNKNOWN。5.5.6空值的处理(续)[例5.45]选出选修课程号1156的不及格的学生。5.5[例5.46]选出选修1156号课程的不及格的学生以及缺考的学生。SELECTSnoFROMSCWHEREGrade<60ANDCno='1156'UNIONSELECTSnoFROMSCWHEREGradeISNULLANDCno='1156'或者SELECTSnoFROMSCWHERECno='1156'AND(Grade<60ORGradeISNULL)5.5.6空值的处理(续)[例5.46]选出选修1156号课程的不及格的学生以及缺考在聚集函数中遇到空值时，除了COUNT(*)外，都跳过空值而去处理非空值。xyxANDyxORy