Oracle数据库第讲三

oracle第4讲1.维护数据的完整性2.序列(sequence)3.管理索引

4.管理权限和角色 主讲李珩oracle数据库维护数据的完整性■

介绍数据的完整性用于确保数据库数据遵从一定的商业的逻辑规则。在oracle中，数据完整性可以使用约束、触发器、应用程序(过程、函数)三种方法来实现，在这三种方法中，因为约束易于维护，并且具有最好的性能，所以作为维护数据完整性的首选.主讲李珩oracle数据库维护数据的完整性■

约束约束用于确保数据库数据满足特定的商业规则。在oracle中，约束包括:notnull、unique,primarykey,foreignkey,和check

五种.主讲李珩oracle数据库维护数据的完整性■

notnull(非空)如果在列上定义了notnull,那么当插入数据时，必须为列提供数据。■

unique(唯一)当定义了唯一约束后，该列值是不能重复的.但是可以为null。■

primarykey(主键)用于唯一的标示表行的数据,当定义主键约束后，该列不但不能重复而且不能为null。需要说明的是:一张表最多只能有一个主键,但是可以有多个unqiue约束。■

foreignkey(外键)用于定义主表和从表之间的关系.外键约束要定义在从表上，主表则必须具有主键约束或是unique约束.,当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null主讲李珩oracle数据库维护数据的完整性■

check用于强制行数据必须满足的条件,假定在sal列上定义了check约束,并要求sal列值在1000～2000之间如果不再1000～2000之间就会提示出错。主讲李珩oracle数据库维护数据的完整性■

商店售货系统表设计案例(1)现有一个商店的数据库，记录客户及其购物情况，由下面三个表组成：商品goods（商品号goodsId，商品名goodsName，单价unitprice，商品类别category，供应商provider);客户customer（客户号customerId,姓名name,住址address,电邮email性别sex,身份证cardId);购买purchase（客户号customerId,商品号goodsId,购买数量nums);请用SQL语言完成下列功能：1建表，在定义中要求声明：(1)每个表的主外键；(2)客户的姓名不能为空值；(3)单价必须大于0，购买数量必须在1到30之间；(4)电邮不能够重复;(5)客户的性别必须是男或者女,默认是男商店表设计主讲李珩oracle数据库维护数据的完整性—维护■

商店售货系统表设计案例(2)如果在建表时忘记建立必要的约束，则可以在建表后使用altertable命令为表增加约束.但是要注意:增加notnull约束时,需要使用modify选项,而增加其它四种约束使用add选项。(1)每个表的主外码；(2)客户的姓名不能为空值；--增加商品名也不能为空(3)单价必须大于0，购买数量必须在1到30之间；(4)电邮不能够重复;--增加身份证也不重复(5)客户的性别必须是男或者女,默认是男(6)增加客户的住址只能是‘海淀’、‘朝阳’、‘东城’、‘西城’、‘通州’、‘崇文’主讲李珩oracle数据库altertable表名addconstraint约束名约束种类(字段）altertable表名modify字段名notnull维护数据的完整性—维护■

删除约束当不再需要某个约束时，可以删除.altertable表名dropconstraint约束名称;

在删除主键约束的时候，可能有错误,比如：altertable表名dropprimarykey;这是因为如果在两张表存在主从关系，那么在删除主表的主键约束时，必须带上cascade选项如象altertable表名dropprimarykeycascade;特别说明一下主讲李珩oracle数据库维护数据的完整性—维护■

列级定义列级定义是在定义列的同时定义约束。■

表级定义表级定义是指在定义了所有列后，再定义约束.这里需要注意:notnull约束只能在列级上定义。主讲李珩oracle数据库语法：在最后一个字段后，定义约束constraint约束名primarykey(字段)constraint约束名foreignkey(字段)references主表(字段)constraint约束名unique(字段)notnull(字段)constraint约束名check(字段条件)序列(sequence)■

一个问题在某张表中，存在一个id列(整数),我们希望在添加记录的时候，该列从1开始，自动的增长，怎么处理?

主讲李珩oracle数据库序列(sequence)■介绍oracle中，是是通过使用用序列(sequence)来处理自自动增长列列。(1)可可以为表中中的列自动动产生值.(2)由由用户创建建数据库对对象,并可可由多个用用户共享.(3)一一般用于主主键或唯一一列.■案例说明createsequencemy_seq---创建序列名名startwith1---从1开始incrementby1---每次增长1minvalue1---最小值maxvalue99999999//nomaxvalue(不设设置置最最大大值值)——-最大大值值cycle---循环环,从1开始始，，每每次次增增长长1，最最大大值值99999999,之后后又又从从1开始始循循环环--nocycle--一直直累累加加，，不不循循环环nocache---不缓缓存存--cache10--一次次产产生生10个号号，，效效率率高高，，缺缺点点是是产产生生跳跳号号主讲讲李李珩珩oracle数据据库库序列列(sequence)■细节节说说明明一旦旦定定义义了了某个个序序列列，你你就就可可以以用用CURRVAL，，NEXTVALCURRVAL：返回回sequence的的当当前前值值NEXTVAL：增加加sequence的的值值，，然然后后返返回回sequence值值比如如：：序列列名名.CURRVAL序列列名名.NEXTVAL什么么时时候候使使用用sequence：：-不不包包含含子子查查询询、、snapshot、、VIEW的的SELECT语语句句-INSERT语语句句的的子子查查询询中中-INSERT语语句句的的VALUES中中!!!UPDATE的的SET中中update表名名set列值值=序列列名名.nextvalwhere主讲讲李李珩珩oracle数据据库库序列列(sequence)■细节节说说明明可以以看看如如下下例例子子：：INSERTINTOempVALUES(my_seq.nextval,'TOMCAT','CLERK',7566,SYSDATE,1200,NULL,20);SELECTmy_seq.currvalFROMDUAL;但是是要要注注意意的的是是：：第一一次次NEXTVAL返返回回的的是是初初始始值值；随随后后的的NEXTVAL会会自自动动增增加加你你定定义义的的INCREMENTBY值值，，然然后后返返回回增增加加后后的的值值。。CURRVAL总总是是返返回回当当前前SEQUENCE的的值值，，但是是在在第第一一次次NEXTVAL初初始始化化之之后后才才能能使使用用CURRVAL，，否否则则会会出出错错。一一次次NEXTVAL会会增增加加一一次次SEQUENCE的的值值，，所所以以如如果果你你在在同同一一个个语语句句里里面面使使用用多多个个NEXTVAL，，其其值值就就是是不不一一样样的的。。如如果果指指定定CACHE值值，，ORACLE就就可可以以预预先先在在内内存存里里面面放放置置一一些些sequence，，这这样样存存取取的的快快些些。。cache里里面面的的取取完完后后，，oracle自自动动再再取取一一组组到到cache。。使使用用cache或或许许会会跳跳号号，，比比如如数数据据库库突突然然不不正正常常down掉掉（（shutdownabort),cache中中的的sequence就就会会丢丢失失.所所以以可可以以在在createsequence的的时时候候用用nocache防防止止这这种种情情况况。。主讲李李珩珩oracle数据库库管理索索引—原理介介绍■介绍索引是是用于加加速数数据存存取的的数据据对象.合理的的使用用索引可可以大大大降降低i/o次数,从而提提高数数据访访问性性能。。索引引有很很多种种我们们主要要介绍绍常用用的几几种:为什么么添加加了索索引后后，会会加快快查询询速度度呢?主讲李李珩珩oracle数据库库管理索索引—创建索索引■单列索索引单列索索引是是基于于单个个列所所建立立的索索引,语法:■复合索索引复合索索引是是基于于两列列或是是多列列的索索引。。在同同一张张表上上可以以有多多个索索引，，但是是要求求列的的组合合必须须不同同,语法:createindexindex_nameontable(columnname,columnname…);createindexindex_nameontable(columnname);主讲李李珩珩oracle数据库库管理索索引—使用原原则■使用原原则①在大大表上上建立立索引引才有有意义义②在where子句或或是连连接条条件上上经常常引用用的列列上建建立索索引③索引引的层层次不不要超超过4层这里能能不能能给学学生演演示这这个效效果呢呢?如何构构建一一个大大表呢呢?主讲李李珩珩oracle数据库库管理索索引—索引的的缺点点■索引缺缺点分分析索引有有一些些先天天不足足：1：建立立索引引，系系统要要占用用大约约为表表的1.2倍的硬硬盘和和内存存空间间来保保存索索引。。2：更新新数据据的时时候，，系统统必须须要有有额外外的时时间来来同时时对索索引进进行更更新，，以维维持数数据和和索引引的一一致性性。实实践表表明，，不恰恰当的的索引引不但但于事事无补补，反反而会会降低低系统统性能能。因因为大大量的的索引引在进进行插插入、、修改改和删删除操操作时时比没没有索索引花花费更更多的的系统统时间间。比如在如如下字段段建立索索引应该该是不恰恰当的：：1、很少或或从不引引用的字字段；2、逻辑型型的字段段，如男男或女(是或否)等。综上上所述，，提高查查询效率率是以消消耗一定定的系统统资源为为代价的的，索引引不能盲盲目的建建立，这这是考验验一个DBA是否优秀秀的很重重要的指指标主讲李李珩珩oracle数据库管理索引引—其它索引引■介绍按照数据据存储方方式，可可以分为为B*树、反向向索引、、位图索索引;按照索引引列的个个数分类类，可以以分为单单列索引引、复合合索引；；按照索引引列值的的唯一性性，可以以分为唯唯一索引引和非唯唯一索引引.此外还有有函数索索引，全全局索引引，分区区索引…对于索引引我还要要说:在不同的的情况我我们会在在不同的的列上建建立索引引，甚至至建立不不同种类类的索引,请记住，，技术是是死的，，人是活活的。比比如：B*-树索引建建立在重重复值很很少的列列上，而而位图索索引则建建立在重重复值很很多、不不同值相相对固定定的列上上。主讲李李珩珩oracle数据库管理权限限和角色色■介绍这一部分分我们主主要看看看oracle种如何管管理权限限和角色色，权限限和角色色的区别别在那里里。当刚刚建建立用户户时，用用户没有有任何权权限，也也不能执执行任何何操作。。如果要要执行某某种特定定的数据据库操作作，则必必须为其其授予系系统的权权限；如如果用户户要访问问其它方方案的对对象，则则必须为为其授予予对象的的权限.为了简化化权限的的管理，，可以使使用角色色。这里里我们会会详细的的介绍主讲李李珩珩oracle数据库管理权限限和角色色■权限权限是指指执行特特定类型型sql命令或是是访问其其它方案案对象的的权利，，包括系统权限限和对象权限限两种:主讲李李珩珩oracle数据库管理权限限和角色色—系统权限限■系统权限限介绍系统权限限是指执执行特定定类型sql命令的权权利.它用于控控制用户户可以执执行的一一个或是是一组数数据库操操作.比如当用用户具有有createtable权限时，，可以在在其方案案中建表表,当用户具具有createanytable权限时,可以在任任何方案案中建表表.oracle提供了200多种系统统权限。。常用的的有：createsession连接数据据库createtable建表createview建视图createpublicsynonym建同义词词createprocedure建过程、、函数、、包createtrigger建触发器器createcluster建簇■显示系统统权限oracle提供了208种系统权权限(11g)，而且oracle的版本越越高，提提供的系系统权限限就越多多,我们可以以查询数数据字典典视图system_privilege_map，可以显显示所有有系统权权限select*fromsystem_privilege_maporderbyname;主讲李李珩珩oracle数据库管理权限限和角色色—系统权限限■授予系统统权限一般情况况，授予予系统权权限是有有dba完成的，，如果用用其它用用户来授授予系统统权限，，则要求求该用户户必须具具有grantanyprivilege的系统权权限在授授予系统统权限时时，可以带有有withadminoption选项，这这样，被被授予权权限的用用户或是是角色还还可以将将该系统统权限授授予其它它的用户户或是角角色。为为了让大大家快速速入门，，我们举举例说明明:1.创建两个个用户ken,tom.初始阶段段他们没没有任何何权限，，如果登登陆就会会给出错错误的信信息1.1创建两个个用户，，并指定定密码.2.给用户ken授权:2.1:授予createsession和createtable权限时带带withadminoption2.2授予createview时不带withadminoption主讲李李珩珩oracle数据库管理权限限和角色色—系统权限限3.给用户tom授权我们可以以通过ken给tom授权，因因为withadminoption是加上的的。当然也可可以通过过dba给tom授权,我们就用用ken给tom授权:①grantcreatesession,createtabletotom;②grandtcreateviewtotom;ok吗?[不ok]■回收系统统权限一般情况况下，回回收系统统权限是是dba来完成的的，如果果其它的的用户来来回收系系统权限限，要求求该用户户必须具具有相应应系统权权限及转转授系统统权限的的选项(withadminoption）。回收收系统权权限使用用revoke来完成,当回收了了系统权权限后，，用户就就不能执执行相应应的操作作了，但但是请注注意，系系统权限限级联收收回问题题？[不是级联联回收!]sys--------------->ken------------->tom(createsession)(createsession)(createsession)用system执行如下下操作:revokecreatesessionfromken;请思考tom还能登录录？主讲李李珩珩oracle数据库管理权限限和角色色—对象权限限■对象权限限介绍指访问其其它方案案对象的的权利,用户可以以直接访访问自己己方案的的对象，，但是如如果要访访问别的的方案的的对象，，则必须须具有对对象的权权限.比如smith用户要访访问scott.emp表(scott:方案,emp:表)则必须在在scott.emp表上具有有对象的的权限。。常用的的有：alter修改delete删除select查询insert添加update修改index索引references引用execute执行查看oracle提供的所所有的对对象权限限[dba用户可以以查看]selectdistinctprivilegefromdba_tab_privs;主讲李李珩珩oracle数据库管理权限限和角色色—对象权限限■授予对象象权限在oracle9i前，授予予对象权权限是由由对象的的所有者者来完成成的，如如果用其其它的用用户来操操作，则则需要用用户具有有相应的的(withgrantoption)权限,从oracle9i开始,dba,sys,system可以将任任何对象象上的对对象权限限授予其其它用户户.授予对象象权限是是用grant命令来完完成的.我们看几几个案例例:1.monkey用户要操操作scott.emp表,则必须授授予相应应的对象象权限①希望monkey可以查询询scott.emp的表数据据，怎样样操作?②希望monkey可以修改改scott.emp的表数据据，怎样样操作?③希望monkey可以删除除scott.emp的表数据据，怎样样操作?④有没有更更加简单单的方法法,一次把所所有权限限赋给monkey?grant对象权限限on数据库对对象to用户名[,角色名][,public][withgrantoption]主讲李李珩珩oracle数据库管理权限限和角色色—对象权限限2.能否对monkey访问权限限更加精细控制制.(授予列权权限)①希望monkey只可以修修改scott.emp的表的sal字段,怎样操作作?②希望monkey只可查询询scott.emp的表的ename,sal数据,怎样操作作?3.授予alter权限如果black用户要修修改scott.emp表的结构构,则必须授授予alter对象权限限4.授予execute权限如果用户户想要执执行其它它方案的的包/过程/函数,则须有execute权限.比如为了了让ken可以执行行包dbms_transaction,可以授execute权限主讲李李珩珩oracle数据库管理权限限和角色色—对象权限限5.授予index权限如果想在在别的方方案的表表上建立立索引,则必须具具有index对象权限限,如为了让让black可以在scott.emp上建立索索引，就就给其index的对象权权限sql>connscott/tigersql>grantindexonscott.emptoblack6.使用withgrantoption选项该选项用用于转授授对象权权限.但是该选选项只能能被授予予用户,而不能授授予角色色sql>connscott/tigersql>grantselectonemptoblackwithgrantoptionsql>connblack/m123sql>grantselectonscott.emptojones主讲李李珩珩oracle数据库管理权限限和角色色—对象权限限■回收对象象权限在oracle9i中，收回回对象的的权限可可以由对对象的所所有者来来完成，，也可以以用dba用户(sys,system)来完成这里要说说明的时时：收回回对象权权限后，，用户就就不能执执行相应应的sql命令,但是要注注意的是是对象的的权限是是否会被被级联收收回?[级联回收收]请看一个个案例:scott------------->blake---------------->jonesselectonemp selectonempselectonemprevoke对象权限限on数据库对对象from用户名[,角色名]主讲李李珩珩oracle数据库管理权限限和角色色—角色■介绍角色就是是相关权权限的命命令集合合,使用角色色的主要要目的就就是为了了简化权权限的管管理.假定有用用户1,2,3为了让他他们都拥拥有权限限①连接数数据库②在scott.emp表上select,insert,update,,,,如果采用用直接授授权操作作，则需需要进行行12次授权。。因为要进进行12次授权操操作,所以比较较麻烦喔喔!怎么办?主讲李李珩珩oracle数据库管理权限限和角色色—角色■介绍我们如果果采用角角色就可可以简化化：首先将createsession,selectonscott.emp,insertonscott.emp,updateonscott.emp授予角色色，然后后将该角角色授予予a,b,c用户，这这样就可可以三次次授权搞搞定.角色分为为预定义义和自定定义角色色两类：：可以考虑虑使用自自定义角角色来解解决问题题的。主讲李李珩珩oracle数据库管理权限限和角色色—角色■预定义角角色预定义角角色是指指oracle所提供的的角色,每种角色色都用于于执行一一些特定定的管理理任务,下面我们们介绍常常用的预预定义角角色connect,resource,dba㈠connect角色connect角色具有有一般应应用开发发人员需需要的大大部分权权限,当建立了了一个用户后,多数情况下下,只要给用户户授予connect和resource角色就够了了,那么，connect角色具有哪哪些系统权权限呢?createsesssiongrant角色名to用户使用system登录可以以查询所有有预定义角角色：select*fromdba_roles;主讲李珩珩oracle数据库管理权限和和角色—角色■预定义角色色㈡resource角色resource角角色具有应应用开发人人员所需要要的其它权权限,比如如建立存储储过程、触触发器等。。这里需要要注意的是是resource角色隐含含了unlimitedtablespace系系统权限。。resource角角色包含以以下系统权权限:createclustercreateindextypecreatetablecreatesequencecreatetypecreateprocedurecreatetrigger主讲李珩珩oracle数据库管理权限和和角色—角色■预定义角色色㈢dba角色dba角色具有所所有的系统统权限，及及withadminoption选项,默认的dba用户为sys和system他们可以将将任何系统统权限授予予其它用户户.但是要注意意的是dba角色不具备备sysdba和sysoper的特权(启动和关闭闭数据库)主讲李珩珩oracle数据库练习：请写写出正确的的语句，完完成提出的的问题。1.创建一个用用户jack2.把jack设为具有dba角色的用户户管理权限和和角色—角色■自定义角色色顾名思义就就是自己定定义的角色色，根据自自己的需要要来定义.一般是dba来建立，如如果用的别别的用户来来建立，则则需要具有有createrole的系统权限限.在建立角色色时可以指指定验证方方式(不验证，数数据库验证证等)㈠建立角色(不验证)如果角色是是公用的角角色，可以以采用不验验证的方式式建立角色色.createrole角色名notidentified;㈡建立角色(数据库验证证)采用这样的的方式时,角色名、口口令存放在在数据库中中。当激活活该角色时时，必须提提供口令.在建立这种种角色时，，需要为其其提供口令令createrole角色名identifiedbym123;主讲李珩珩oracle数据库管理权限和和角色—角色■角色授权当建立角色色时，角色色没有任何何权限,为了使得角角色完成特特定任务，，必须为其其授予相应应的系统权权限和对象象权限。㈠给角色授授权给角色授予予权限和给给用户授权权没有太多多区别，但但是要注意意，系统权权限的unlimitedtablespace和对象权限限的withgrantoption选项是不能能授予角色色的。grant对象权限on数据库对象象to自定义角色色名[withadminoption]练习:1.用system给某个自定定义角色授授予createsession权限