Oracle數(shù)據(jù)庫第講三

oracle第4講1.維護數(shù)據(jù)的完整性2.序列(sequence)3.管理索引

4.管理權限和角色 主講李珩oracle數(shù)據(jù)庫維護數(shù)據(jù)的完整性■

介紹數(shù)據(jù)的完整性用于確保數(shù)據(jù)庫數(shù)據(jù)遵從一定的商業(yè)的邏輯規(guī)則。在oracle中，數(shù)據(jù)完整性可以使用約束、觸發(fā)器、應用程序(過程、函數(shù))三種方法來實現(xiàn)，在這三種方法中，因為約束易于維護，并且具有最好的性能，所以作為維護數(shù)據(jù)完整性的首選.主講李珩oracle數(shù)據(jù)庫維護數(shù)據(jù)的完整性■

約束約束用于確保數(shù)據(jù)庫數(shù)據(jù)滿足特定的商業(yè)規(guī)則。在oracle中，約束包括:notnull、unique,primarykey,foreignkey,和check

五種.主講李珩oracle數(shù)據(jù)庫維護數(shù)據(jù)的完整性■

notnull(非空)如果在列上定義了notnull,那么當插入數(shù)據(jù)時，必須為列提供數(shù)據(jù)。■

unique(唯一)當定義了唯一約束后，該列值是不能重復的.但是可以為null?！?/p>

primarykey(主鍵)用于唯一的標示表行的數(shù)據(jù),當定義主鍵約束后，該列不但不能重復而且不能為null。需要說明的是:一張表最多只能有一個主鍵,但是可以有多個unqiue約束?！?/p>

foreignkey(外鍵)用于定義主表和從表之間的關系.外鍵約束要定義在從表上，主表則必須具有主鍵約束或是unique約束.,當定義外鍵約束后，要求外鍵列數(shù)據(jù)必須在主表的主鍵列存在或是為null主講李珩oracle數(shù)據(jù)庫維護數(shù)據(jù)的完整性■

check用于強制行數(shù)據(jù)必須滿足的條件,假定在sal列上定義了check約束,并要求sal列值在1000～2000之間如果不再1000～2000之間就會提示出錯。主講李珩oracle數(shù)據(jù)庫維護數(shù)據(jù)的完整性■

商店售貨系統(tǒng)表設計案例(1)現(xiàn)有一個商店的數(shù)據(jù)庫，記錄客戶及其購物情況，由下面三個表組成：商品goods（商品號goodsId，商品名goodsName，單價unitprice，商品類別category，供應商provider);客戶customer（客戶號customerId,姓名name,住址address,電郵email性別sex,身份證cardId);購買purchase（客戶號customerId,商品號goodsId,購買數(shù)量nums);請用SQL語言完成下列功能：1建表，在定義中要求聲明：(1)每個表的主外鍵；(2)客戶的姓名不能為空值；(3)單價必須大于0，購買數(shù)量必須在1到30之間；(4)電郵不能夠重復;(5)客戶的性別必須是男或者女,默認是男商店表設計主講李珩oracle數(shù)據(jù)庫維護數(shù)據(jù)的完整性—維護■

商店售貨系統(tǒng)表設計案例(2)如果在建表時忘記建立必要的約束，則可以在建表后使用altertable命令為表增加約束.但是要注意:增加notnull約束時,需要使用modify選項,而增加其它四種約束使用add選項。(1)每個表的主外碼；(2)客戶的姓名不能為空值；--增加商品名也不能為空(3)單價必須大于0，購買數(shù)量必須在1到30之間；(4)電郵不能夠重復;--增加身份證也不重復(5)客戶的性別必須是男或者女,默認是男(6)增加客戶的住址只能是‘海淀’、‘朝陽’、‘東城’、‘西城’、‘通州’、‘崇文’主講李珩oracle數(shù)據(jù)庫altertable表名addconstraint約束名約束種類(字段）altertable表名modify字段名notnull維護數(shù)據(jù)的完整性—維護■

刪除約束當不再需要某個約束時，可以刪除.altertable表名dropconstraint約束名稱;

在刪除主鍵約束的時候，可能有錯誤,比如：altertable表名dropprimarykey;這是因為如果在兩張表存在主從關系，那么在刪除主表的主鍵約束時，必須帶上cascade選項如象altertable表名dropprimarykeycascade;特別說明一下主講李珩oracle數(shù)據(jù)庫維護數(shù)據(jù)的完整性—維護■

列級定義列級定義是在定義列的同時定義約束?！?/p>

表級定義表級定義是指在定義了所有列后，再定義約束.這里需要注意:notnull約束只能在列級上定義。主講李珩oracle數(shù)據(jù)庫語法：在最后一個字段后，定義約束constraint約束名primarykey(字段)constraint約束名foreignkey(字段)references主表(字段)constraint約束名unique(字段)notnull(字段)constraint約束名check(字段條件)序列(sequence)■

一個問題在某張表中，存在一個id列(整數(shù)),我們希望在添加記錄的時候，該列從1開始，自動的增長，怎么處理?

主講李珩oracle數(shù)據(jù)庫序列(sequence)■介紹oracle中，是是通過使用用序列(sequence)來處理自自動增長列列。(1)可可以為表中中的列自動動產(chǎn)生值.(2)由由用戶創(chuàng)建建數(shù)據(jù)庫對對象,并可可由多個用用戶共享.(3)一一般用于主主鍵或唯一一列.■案例說明createsequencemy_seq---創(chuàng)建序列名名startwith1---從1開始incrementby1---每次增長1minvalue1---最小值maxvalue99999999//nomaxvalue(不設設置置最最大大值值)——-最大大值值cycle---循環(huán)環(huán),從1開始始，，每每次次增增長長1，最最大大值值99999999,之后后又又從從1開始始循循環(huán)環(huán)--nocycle--一直直累累加加，，不不循循環(huán)環(huán)nocache---不緩緩存存--cache10--一次次產(chǎn)產(chǎn)生生10個號號，，效效率率高高，，缺缺點點是是產(chǎn)產(chǎn)生生跳跳號號主講講李李珩珩oracle數(shù)據(jù)據(jù)庫庫序列列(sequence)■細節(jié)節(jié)說說明明一旦旦定定義義了了某個個序序列列，你你就就可可以以用用CURRVAL，，NEXTVALCURRVAL：返回回sequence的的當當前前值值NEXTVAL：增加加sequence的的值值，，然然后后返返回回sequence值值比如如：：序列列名名.CURRVAL序列列名名.NEXTVAL什么么時時候候使使用用sequence：：-不不包包含含子子查查詢詢、、snapshot、、VIEW的的SELECT語語句句-INSERT語語句句的的子子查查詢詢中中-INSERT語語句句的的VALUES中中!!!UPDATE的的SET中中update表名名set列值值=序列列名名.nextvalwhere主講講李李珩珩oracle數(shù)據(jù)據(jù)庫庫序列列(sequence)■細節(jié)節(jié)說說明明可以以看看如如下下例例子子：：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就就可可以以預預先先在在內(nèi)內(nèi)存存里里面面放放置置一一些些sequence，，這這樣樣存存取取的的快快些些。。cache里里面面的的取取完完后后，，oracle自自動動再再取取一一組組到到cache。。使使用用cache或或許許會會跳跳號號，，比比如如數(shù)數(shù)據(jù)據(jù)庫庫突突然然不不正正常常down掉掉（（shutdownabort),cache中中的的sequence就就會會丟丟失失.所所以以可可以以在在createsequence的的時時候候用用nocache防防止止這這種種情情況況。。主講李李珩珩oracle數(shù)據(jù)庫庫管理索索引—原理介介紹■介紹索引是是用于加加速數(shù)數(shù)據(jù)存存取的的數(shù)據(jù)據(jù)對象.合理的的使用用索引可可以大大大降降低i/o次數(shù),從而提提高數(shù)數(shù)據(jù)訪訪問性性能。。索引引有很很多種種我們們主要要介紹紹常用用的幾幾種:為什么么添加加了索索引后后，會會加快快查詢詢速度度呢?主講李李珩珩oracle數(shù)據(jù)庫庫管理索索引—創(chuàng)建索索引■單列索索引單列索索引是是基于于單個個列所所建立立的索索引,語法:■復合索索引復合索索引是是基于于兩列列或是是多列列的索索引。。在同同一張張表上上可以以有多多個索索引，，但是是要求求列的的組合合必須須不同同,語法:createindexindex_nameontable(columnname,columnname…);createindexindex_nameontable(columnname);主講李李珩珩oracle數(shù)據(jù)庫庫管理索索引—使用原原則■使用原原則①在大大表上上建立立索引引才有有意義義②在where子句或或是連連接條條件上上經(jīng)常常引用用的列列上建建立索索引③索引引的層層次不不要超超過4層這里能能不能能給學學生演演示這這個效效果呢呢?如何構構建一一個大大表呢呢?主講李李珩珩oracle數(shù)據(jù)庫庫管理索索引—索引的的缺點點■索引缺缺點分分析索引有有一些些先天天不足足：1：建立立索引引，系系統(tǒng)要要占用用大約約為表表的1.2倍的硬硬盤和和內(nèi)存存空間間來保保存索索引。。2：更新新數(shù)據(jù)據(jù)的時時候，，系統(tǒng)統(tǒng)必須須要有有額外外的時時間來來同時時對索索引進進行更更新，，以維維持數(shù)數(shù)據(jù)和和索引引的一一致性性。實實踐表表明，，不恰恰當?shù)牡乃饕坏谑率聼o補補，反反而會會降低低系統(tǒng)統(tǒng)性能能。因因為大大量的的索引引在進進行插插入、、修改改和刪刪除操操作時時比沒沒有索索引花花費更更多的的系統(tǒng)統(tǒng)時間間。比如在如如下字段段建立索索引應該該是不恰恰當?shù)模海?、很少或或從不引引用的字字段；2、邏輯型型的字段段，如男男或女(是或否)等。綜上上所述，，提高查查詢效率率是以消消耗一定定的系統(tǒng)統(tǒng)資源為為代價的的，索引引不能盲盲目的建建立，這這是考驗驗一個DBA是否優(yōu)秀秀的很重重要的指指標主講李李珩珩oracle數(shù)據(jù)庫管理索引引—其它索引引■介紹按照數(shù)據(jù)據(jù)存儲方方式，可可以分為為B*樹、反向向索引、、位圖索索引;按照索引引列的個個數(shù)分類類，可以以分為單單列索引引、復合合索引；；按照索引引列值的的唯一性性，可以以分為唯唯一索引引和非唯唯一索引引.此外還有有函數(shù)索索引，全全局索引引，分區(qū)區(qū)索引…對于索引引我還要要說:在不同的的情況我我們會在在不同的的列上建建立索引引，甚至至建立不不同種類類的索引,請記住，，技術是是死的，，人是活活的。比比如：B*-樹索引建建立在重重復值很很少的列列上，而而位圖索索引則建建立在重重復值很很多、不不同值相相對固定定的列上上。主講李李珩珩oracle數(shù)據(jù)庫管理權限限和角色色■介紹這一部分分我們主主要看看看oracle種如何管管理權限限和角色色，權限限和角色色的區(qū)別別在那里里。當剛剛建建立用戶戶時，用用戶沒有有任何權權限，也也不能執(zhí)執(zhí)行任何何操作。。如果要要執(zhí)行某某種特定定的數(shù)據(jù)據(jù)庫操作作，則必必須為其其授予系系統(tǒng)的權權限；如如果用戶戶要訪問問其它方方案的對對象，則則必須為為其授予予對象的的權限.為了簡化化權限的的管理，，可以使使用角色色。這里里我們會會詳細的的介紹主講李李珩珩oracle數(shù)據(jù)庫管理權限限和角色色■權限權限是指指執(zhí)行特特定類型型sql命令或是是訪問其其它方案案對象的的權利，，包括系統(tǒng)權限限和對象權限限兩種:主講李李珩珩oracle數(shù)據(jù)庫管理權限限和角色色—系統(tǒng)權限限■系統(tǒng)權限限介紹系統(tǒng)權限限是指執(zhí)執(zhí)行特定定類型sql命令的權權利.它用于控控制用戶戶可以執(zhí)執(zhí)行的一一個或是是一組數(shù)數(shù)據(jù)庫操操作.比如當用用戶具有有createtable權限時，，可以在在其方案案中建表表,當用戶具具有createanytable權限時,可以在任任何方案案中建表表.oracle提供了200多種系統(tǒng)統(tǒng)權限。。常用的的有：createsession連接數(shù)據(jù)據(jù)庫createtable建表createview建視圖createpublicsynonym建同義詞詞createprocedure建過程、、函數(shù)、、包createtrigger建觸發(fā)器器createcluster建簇■顯示系統(tǒng)統(tǒng)權限oracle提供了208種系統(tǒng)權權限(11g)，而且oracle的版本越越高，提提供的系系統(tǒng)權限限就越多多,我們可以以查詢數(shù)數(shù)據(jù)字典典視圖system_privilege_map，可以顯顯示所有有系統(tǒng)權權限select*fromsystem_privilege_maporderbyname;主講李李珩珩oracle數(shù)據(jù)庫管理權限限和角色色—系統(tǒng)權限限■授予系統(tǒng)統(tǒng)權限一般情況況，授予予系統(tǒng)權權限是有有dba完成的，，如果用用其它用用戶來授授予系統(tǒng)統(tǒng)權限，，則要求求該用戶戶必須具具有grantanyprivilege的系統(tǒng)權權限在授授予系統(tǒng)統(tǒng)權限時時，可以帶有有withadminoption選項，這這樣，被被授予權權限的用用戶或是是角色還還可以將將該系統(tǒng)統(tǒng)權限授授予其它它的用戶戶或是角角色。為為了讓大大家快速速入門，，我們舉舉例說明明:1.創(chuàng)建兩個個用戶ken,tom.初始階段段他們沒沒有任何何權限，，如果登登陸就會會給出錯錯誤的信信息1.1創(chuàng)建兩個個用戶，，并指定定密碼.2.給用戶ken授權:2.1:授予createsession和createtable權限時帶帶withadminoption2.2授予createview時不帶withadminoption主講李李珩珩oracle數(shù)據(jù)庫管理權限限和角色色—系統(tǒng)權限限3.給用戶tom授權我們可以以通過ken給tom授權，因因為withadminoption是加上的的。當然也可可以通過過dba給tom授權,我們就用用ken給tom授權:①grantcreatesession,createtabletotom;②grandtcreateviewtotom;ok嗎?[不ok]■回收系統(tǒng)統(tǒng)權限一般情況況下，回回收系統(tǒng)統(tǒng)權限是是dba來完成的的，如果果其它的的用戶來來回收系系統(tǒng)權限限，要求求該用戶戶必須具具有相應應系統(tǒng)權權限及轉(zhuǎn)轉(zhuǎn)授系統(tǒng)統(tǒng)權限的的選項(withadminoption）?；厥帐障到y(tǒng)權權限使用用revoke來完成,當回收了了系統(tǒng)權權限后，，用戶就就不能執(zhí)執(zhí)行相應應的操作作了，但但是請注注意，系系統(tǒng)權限限級聯(lián)收收回問題題？[不是級聯(lián)聯(lián)回收!]sys--------------->ken------------->tom(createsession)(createsession)(createsession)用system執(zhí)行如下下操作:revokecreatesessionfromken;請思考tom還能登錄錄？主講李李珩珩oracle數(shù)據(jù)庫管理權限限和角色色—對象權限限■對象權限限介紹指訪問其其它方案案對象的的權利,用戶可以以直接訪訪問自己己方案的的對象，，但是如如果要訪訪問別的的方案的的對象，，則必須須具有對對象的權權限.比如smith用戶要訪訪問scott.emp表(scott:方案,emp:表)則必須在在scott.emp表上具有有對象的的權限。。常用的的有：alter修改delete刪除select查詢insert添加update修改index索引references引用execute執(zhí)行查看oracle提供的所所有的對對象權限限[dba用戶可以以查看]selectdistinctprivilegefromdba_tab_privs;主講李李珩珩oracle數(shù)據(jù)庫管理權限限和角色色—對象權限限■授予對象象權限在oracle9i前，授予予對象權權限是由由對象的的所有者者來完成成的，如如果用其其它的用用戶來操操作，則則需要用用戶具有有相應的的(withgrantoption)權限,從oracle9i開始,dba,sys,system可以將任任何對象象上的對對象權限限授予其其它用戶戶.授予對象象權限是是用grant命令來完完成的.我們看幾幾個案例例:1.monkey用戶要操操作scott.emp表,則必須授授予相應應的對象象權限①希望monkey可以查詢詢scott.emp的表數(shù)據(jù)據(jù)，怎樣樣操作?②希望monkey可以修改改scott.emp的表數(shù)據(jù)據(jù)，怎樣樣操作?③希望monkey可以刪除除scott.emp的表數(shù)據(jù)據(jù)，怎樣樣操作?④有沒有更更加簡單單的方法法,一次把所所有權限限賦給monkey?grant對象權限限on數(shù)據(jù)庫對對象to用戶名[,角色名][,public][withgrantoption]主講李李珩珩oracle數(shù)據(jù)庫管理權限限和角色色—對象權限限2.能否對monkey訪問權限限更加精細控制制.(授予列權權限)①希望monkey只可以修修改scott.emp的表的sal字段,怎樣操作作?②希望monkey只可查詢詢scott.emp的表的ename,sal數(shù)據(jù),怎樣操作作?3.授予alter權限如果black用戶要修修改scott.emp表的結構構,則必須授授予alter對象權限限4.授予execute權限如果用戶戶想要執(zhí)執(zhí)行其它它方案的的包/過程/函數(shù),則須有execute權限.比如為了了讓ken可以執(zhí)行行包dbms_transaction,可以授execute權限主講李李珩珩oracle數(shù)據(jù)庫管理權限限和角色色—對象權限限5.授予index權限如果想在在別的方方案的表表上建立立索引,則必須具具有index對象權限限,如為了讓讓black可以在scott.emp上建立索索引，就就給其index的對象權權限sql>connscott/tigersql>grantindexonscott.emptoblack6.使用withgrantoption選項該選項用用于轉(zhuǎn)授授對象權權限.但是該選選項只能能被授予予用戶,而不能授授予角色色sql>connscott/tigersql>grantselectonemptoblackwithgrantoptionsql>connblack/m123sql>grantselectonscott.emptojones主講李李珩珩oracle數(shù)據(jù)庫管理權限限和角色色—對象權限限■回收對象象權限在oracle9i中，收回回對象的的權限可可以由對對象的所所有者來來完成，，也可以以用dba用戶(sys,system)來完成這里要說說明的時時：收回回對象權權限后，，用戶就就不能執(zhí)執(zhí)行相應應的sql命令,但是要注注意的是是對象的的權限是是否會被被級聯(lián)收收回?[級聯(lián)回收收]請看一個個案例:scott------------->blake---------------->jonesselectonemp selectonempselectonemprevoke對象權限限on數(shù)據(jù)庫對對象from用戶名[,角色名]主講李李珩珩oracle數(shù)據(jù)庫管理權限限和角色色—角色■介紹角色就是是相關權權限的命命令集合合,使用角色色的主要要目的就就是為了了簡化權權限的管管理.假定有用用戶1,2,3為了讓他他們都擁擁有權限限①連接數(shù)數(shù)據(jù)庫②在scott.emp表上select,insert,update,,,,如果采用用直接授授權操作作，則需需要進行行12次授權。。因為要進進行12次授權操操作,所以比較較麻煩喔喔!怎么辦?主講李李珩珩oracle數(shù)據(jù)庫管理權限限和角色色—角色■介紹我們?nèi)绻捎媒墙巧涂煽梢院喕菏紫葘reatesession,selectonscott.emp,insertonscott.emp,updateonscott.emp授予角色色，然后后將該角角色授予予a,b,c用戶，這這樣就可可以三次次授權搞搞定.角色分為為預定義義和自定定義角色色兩類：：可以考慮慮使用自自定義角角色來解解決問題題的。主講李李珩珩oracle數(shù)據(jù)庫管理權限限和角色色—角色■預定義角角色預定義角角色是指指oracle所提供的的角色,每種角色色都用于于執(zhí)行一一些特定定的管理理任務,下面我們們介紹常常用的預預定義角角色connect,resource,dba㈠connect角色connect角色具有有一般應應用開發(fā)發(fā)人員需需要的大大部分權權限,當建立了了一個用戶后,多數(shù)情況下下,只要給用戶戶授予connect和resource角色就夠了了,那么，connect角色具有哪哪些系統(tǒng)權權限呢?createsesssiongrant角色名to用戶使用system登錄可以以查詢所有有預定義角角色：select*fromdba_roles;主講李珩珩oracle數(shù)據(jù)庫管理權限和和角色—角色■預定義角色色㈡resource角色resource角角色具有應應用開發(fā)人人員所需要要的其它權權限,比如如建立存儲儲過程、觸觸發(fā)器等。。這里需要要注意的是是resource角色隱含含了unlimitedtablespace系系統(tǒng)權限。。resource角角色包含以以下系統(tǒng)權權限:createclustercreateindextypecreatetablecreatesequencecreatetypecreateprocedurecreatetrigger主講李珩珩oracle數(shù)據(jù)庫管理權限和和角色—角色■預定義角色色㈢dba角色dba角色具有所所有的系統(tǒng)統(tǒng)權限，及及withadminoption選項,默認的dba用戶為sys和system他們可以將將任何系統(tǒng)統(tǒng)權限授予予其它用戶戶.但是要注意意的是dba角色不具備備sysdba和sysoper的特權(啟動和關閉閉數(shù)據(jù)庫)主講李珩珩oracle數(shù)據(jù)庫練習：請寫寫出正確的的語句，完完成提出的的問題。1.創(chuàng)建一個用用戶jack2.把jack設為具有dba角色的用戶戶管理權限和和角色—角色■自定義角色色顧名思義就就是自己定定義的角色色，根據(jù)自自己的需要要來定義.一般是dba來建立，如如果用的別別的用戶來來建立，則則需要具有有createrole的系統(tǒng)權限限.在建立角色色時可以指指定驗證方方式(不驗證，數(shù)數(shù)據(jù)庫驗證證等)㈠建立角色(不驗證)如果角色是是公用的角角色，可以以采用不驗驗證的方式式建立角色色.createrole角色名notidentified;㈡建立角色(數(shù)據(jù)庫驗證證)采用這樣的的方式時,角色名、口口令存放在在數(shù)據(jù)庫中中。當激活活該角色時時，必須提提供口令.在建立這種種角色時，，需要為其其提供口令令createrole角色名identifiedbym123;主講李珩珩oracle數(shù)據(jù)庫管理權限和和角色—角色■角色授權當建立角色色時，角色色沒有任何何權限,為了使得角角色完成特特定任務，，必須為其其授予相應應的系統(tǒng)權權限和對象象權限。㈠給角色授授權給角色授予予權限和給給用戶授權權沒有太多多區(qū)別，但但是要注意意，系統(tǒng)權權限的unlimitedtablespace和對象權限限的withgrantoption選項是不能能授予角色色的。grant對象權限on數(shù)據(jù)庫對象象to自定義角色色名[withadminoption]練習:1.用system給某個自定定義角色授授予createsession權限