數(shù)據(jù)庫應(yīng)用技術(shù)教程第四章數(shù)據(jù)庫對象ppt課件

1、第四章 數(shù)據(jù)庫對象Skywind.guogmail4.1 表 Table表的定義與維護(hù)創(chuàng)建CREATE TABLE 表名（字段定義， 行級約束）；字段定義：字段名 數(shù)據(jù)類型DEFAULT值 字段約束4.1 表 Table修正ALTER TABLE 表名 ADD 字段定義 DROP COLUMN字段 MODIFY 字段定義MODIFY:變長，不影響數(shù)據(jù)值，定義；變短，檢查數(shù)據(jù)。ADD:字段默認(rèn)值為NULL,可聯(lián)合MODIFY使用。4.1 表 Table刪除DROP TABLE 表名； CASCADE CONSTRAINTS 清空TRUNCATE TABLE 表名 相當(dāng)于DELETE FROM 表

2、名，區(qū)別：DDL無需COMMIT，DML 需COMMIT。DML需要ROLLBACK空間4.2 約束 CONSTRAINTS 根據(jù)實(shí)際的業(yè)務(wù)要求，要求數(shù)據(jù)必須滿足的一系列條件稱為商業(yè)規(guī)則。也稱為數(shù)據(jù)的一致性。實(shí)現(xiàn)商業(yè)規(guī)則有如下幾種方式：應(yīng)用程序代碼：在客戶端和程序內(nèi)部檢查，不能保證全面的一致性。觸發(fā)器：在DBMS內(nèi)部，對所有數(shù)據(jù)的變化都通過一段程序代碼進(jìn)行檢查，可以保證一致性，但執(zhí)行代價(jià)較大。約束Constraints）：對于一些常見、通用的要求，使用設(shè)置的方式建立規(guī)則。設(shè)置簡單，執(zhí)行代價(jià)較小，一致性強(qiáng)數(shù)據(jù)類型是最基本的一種約束4.2 約束的分類非空 NOT NULL 字段級獨(dú)一 UNIQUE

3、主鍵 PRIMARY KEY外鍵 FOREIGN KEY檢查 CHECK4.2 約束的使用獨(dú)一：可為空，一般不用于表間關(guān)系?？梢允嵌鄠€(gè)字段的聯(lián)合主鍵：獨(dú)一，不可為空，用于表聯(lián)接?？梢允嵌鄠€(gè)字段的聯(lián)合外鍵：表中某些字段一定會出現(xiàn)在其它表中，應(yīng)保持一致?？梢允强罩怠OREIGN KEY (字段列表) REFERENCES 其它表名字段名）檢查：只能針對單行記錄。 4.2 外鍵外鍵是描述表之間關(guān)系的橋梁和紐帶。用來保證參照完整性當(dāng)被其他數(shù)據(jù)引用的數(shù)據(jù)刪除或修改時(shí)，那些引用的數(shù)據(jù)如何處理？不允許刪除Restrict）置為空或缺省值級聯(lián)刪除Cascade），可能會導(dǎo)致雪崩。在數(shù)據(jù)導(dǎo)入導(dǎo)出時(shí)，往往需要暫

4、時(shí)禁止外鍵外鍵的循環(huán)引用4.2 約束的語法上述約束，均可寫在CREATE TABLE，ALTER TABLE 中也可以直接作為CONSTRAINTS 對象創(chuàng)建作為表維護(hù)語言的一部分NOT NULL含NULL只能寫在行級其他四種約束，即可寫在對應(yīng)的行上，也可以寫在語句的最后如涉及到多個(gè)字段的聯(lián)合，只能寫在最后約束創(chuàng)建舉例CREATE TABLE SALES(SID CHAR(3) NOT NULL, LID CHAR(3) NULL, SALARY NUMBER(4,0)PRIMARY KEY(AID), FOREIGN KEY(LID) REFERNCE LOCATIONS(LID),CHEC

5、K(SALARY0 AND UPPER(AID)=AID);約束的狀態(tài)ENABLENOVALIDATEENABLEVALIDATEExisting dataNew dataDISABLENOVALIDATEDISABLEVALIDATE=約束檢查的時(shí)機(jī)DML statementCheck nondeferred constraintsCOMMITCheck deferred constraintsPrimary and Unique Key 與索引Is an index available for use?YesNoNoYesYesNoCreate nonunique indexCreate

6、unique indexDo not use indexUse existing indexKey enabled?Constraintdeferrable?NoYesConstraintDeferrable?Is the indexnon-unique?No4.3 視圖 View視圖是一個(gè)虛表不存儲數(shù)據(jù)，在使用時(shí)對原有數(shù)據(jù)的重新組織。它是一個(gè)邏輯結(jié)構(gòu)，不占據(jù)存儲空間?；诒?、在大部分SQL中，特別是DQL和DML，語法地位等價(jià)于表 使用視圖的意義限制存取字段級權(quán)限和行級權(quán)限）。不同用戶不同視角和權(quán)限。簡化查詢：別名、減少代碼長度和復(fù)雜性如，在求和值上做TOPN查詢提供不同的查詢角度：可以將常

7、用的連接查詢構(gòu)造成視圖。兼容數(shù)據(jù)庫設(shè)計(jì)的修改：新表拼出舊表，視圖兼容舊表的程序。視圖的創(chuàng)建與刪除l創(chuàng)建視圖lCREATE OR REPLACE VIEW 名字l ( 字段列表 ) lASlSELECT 子查詢l WITH CHECK OPTION lOR REPLACE:對已有同名視圖進(jìn)行替換，有違反定義的修改，不允許。無則返錯。l如省略字段列表則用子查詢返回的字段名定義 l刪除視圖lDROP VIEW 視圖名視圖上的DML簡單視圖可以直接執(zhí)行復(fù)雜視圖表的數(shù)目多于1個(gè)有字段表達(dá)式作為視圖的列有聚組函數(shù)作為視圖的列復(fù)雜視圖的DML不一定能夠正確執(zhí)行帶有CHECK OPTION的視圖的DML，不能

8、違反條件4.4 唯一性編號目的自動產(chǎn)生唯一編號，可看作編號發(fā)生器。數(shù)字要求遞增或按一定規(guī)律變化），不重復(fù)不斷號用作主鍵避免并發(fā)沖突減少應(yīng)用程序代碼提高性能一個(gè)錯誤的例子自增長字段在MSSQL和MYSQL中，使用自增長字段作為解決方案自增長字段是表格定義時(shí)，依附于某個(gè)字段通常是主鍵的屬性插入時(shí)無須為此字段指定值，即自增長不同表之間不能復(fù)用必須有一種方式，獲得上次insert的值序列 Sequences序列是一個(gè)獨(dú)立的編號發(fā)生器在一個(gè)事務(wù)中，通過訪問序列的NEXTVAL和CURRVAL可以獲得序列的下一個(gè)值和當(dāng)前值保證多個(gè)事務(wù)之間不會出現(xiàn)并發(fā)沖突不與某個(gè)表綁定，可以在多個(gè)表之間復(fù)用序列的創(chuàng)建與維護(hù)

9、創(chuàng)建CREATE SEQUENCE 序列名 INCREMENT BY n START WITH n MAXVALUE n | NOMAXVALUE MINVALUE n | NOMINVALUE CYCLE|NOCYCLE CACHE N| NOCACHE刪除DROP SEQUENCE 序列名使用序列的例子CREATE TABLE INV_MASTER (INV_NO NUMBER, )CREATE TABLE INV_DETAIL (INV_NO NUMBER, LINE_NO NUMBER, )CREATE SEQUENCE SEQ_INV INSERT INTO INV_MASTER (

10、INV_NO, ) VALUES (SEQ_INV.NEXTVAL, )INSERT INTO INV_DETAIL (INV_NO, LINE_NO, ) VALUES (SEQ_INV.CURRVAL, 1, )4.5 索引索引是這樣一種數(shù)據(jù)庫對象，它占據(jù)一定的存儲空間，能夠在查詢時(shí)被自動使用，從而提高查詢性能?；谝粋€(gè)表中某個(gè)字段或字段的聯(lián)合索引的使用不影響語句的正確性和結(jié)果避免全表掃描由查詢優(yōu)化器自動使用，并非所有查詢都能獲益通過某些額外選項(xiàng)可以提示查詢優(yōu)化器利用索引可能會加快DQL速度，但對DML速度的影響是雙重的建立索引的考慮建立索引字段取值范圍較大字段中NULL值比例較大字段經(jīng)常

11、做為查詢或連接條件多數(shù)查詢期望返回行數(shù)較少 不建立索引表較小表經(jīng)常更新字段不常做為條件或出現(xiàn)在表達(dá)式內(nèi)期望返回行數(shù)較多根據(jù)系統(tǒng)運(yùn)行情況，對性能瓶頸進(jìn)行優(yōu)化索引的分類邏輯分類唯一索引 vs 非唯一索引自動索引 vs 手工創(chuàng)建的索引UK, PK, FK基于函數(shù)的索引物理分類Partitioned or non-partitioned B-treeNormal or reverse keyBitmapB-Tree Index的作用Index entry headerKey column lengthKey column valueROWIDRootBranchLeafIndex entryBitma

12、p Index的作用keystartROWIDendROWID bitmapTableIndexBlock 10Block 11Block 12File 3比較B-Tree和Bitmap索引B-treeSuitable for high-cardinality columnsUpdates on keys relativelyinexpensiveInefficient for queries using OR predicatesUseful for OLTPBitmapSuitable for low-cardinality columns Updates to key columns v

13、eryexpensiveEfficient for queries using OR predicatesUseful for data warehousing索引的創(chuàng)建與維護(hù)創(chuàng)建CREATE UNIQUE BITMAP INDEX 索引名ON 表名 (字段列表) REVERSE重建 Rebuild修改索引存儲位置和翻轉(zhuǎn)等參數(shù)通過重建，刪除不用的入口項(xiàng)，提高效率ALTER INDEX 索引名 REBUILD ONLINE整理索引ALTER INDEX 索引名 COALESCE;刪除索引DROP INDEX 索引名 4.6 其他數(shù)據(jù)庫對象4.6.1 聚簇 Cluster4.6.2 分區(qū) Part

14、ition4.7 數(shù)據(jù)庫設(shè)計(jì)初步數(shù)據(jù)庫設(shè)計(jì)的過程概念設(shè)計(jì)邏輯設(shè)計(jì)物理設(shè)計(jì)范式1NF：不存在多值的字段。（不能違背）2NF：多個(gè)主鍵時(shí)，不存在只由一個(gè)主鍵決定的信息如在orders里面加入顧客姓名3NF：不存在由非主鍵決定的其他信息如在SALES表中既有LID，又有CITY主鍵的選擇業(yè)務(wù)主鍵和邏輯主鍵業(yè)務(wù)主鍵就是將業(yè)務(wù)中有唯一屬性的字段挑出來做主鍵邏輯主鍵無實(shí)在意義的唯一性編號 選擇原則一、對于業(yè)務(wù)數(shù)據(jù)，最好采用邏輯主鍵;二、對于業(yè)務(wù)復(fù)合主鍵有多個(gè)字段，需要采用邏輯主鍵;三、對于基礎(chǔ)數(shù)據(jù)，基于多方面考慮，是可以采用業(yè)務(wù)主鍵的。這類表初始化以后數(shù)據(jù)不會經(jīng)常發(fā)生改變。四、取消業(yè)務(wù)主鍵后，在查詢經(jīng)常會

15、用到的相關(guān)的業(yè)務(wù)字段建立INDEX，可以提高查詢效率;五、使用邏輯主鍵,表的業(yè)務(wù)數(shù)據(jù)唯一性由程序來檢查控制，使業(yè)務(wù)數(shù)據(jù)重復(fù)這類臟數(shù)據(jù)控制在業(yè)務(wù)允許的范圍;六、業(yè)務(wù)數(shù)據(jù)的重復(fù)這類臟數(shù)據(jù)也可以通過分析結(jié)果數(shù)據(jù)得到;七、業(yè)務(wù)數(shù)據(jù)的邏輯主鍵使用numeric自增長型，在遷移數(shù)據(jù)時(shí)，取消目標(biāo)表的自增長，數(shù)據(jù)遷移完成后，再重建邏輯主鍵。違反范式的危害違反范式可能產(chǎn)生兩個(gè)問題：1.數(shù)據(jù)冗余 數(shù)據(jù)冗余必然會帶來數(shù)據(jù)不一致2.更新異常更新異常包括插入、修改和刪除帶來的異常也就是對一個(gè)表的修改，可以會破壞其他表的信息，或造成信息不一致，或者信息丟失。一個(gè)數(shù)據(jù)庫設(shè)計(jì)的演化背景：一個(gè)網(wǎng)上論壇，用戶可以注冊、發(fā)帖、回復(fù)

16、帖子等。設(shè)計(jì)一、只有一個(gè)表用戶名、主頁、聯(lián)系地址發(fā)帖標(biāo)題、發(fā)帖內(nèi)容、回復(fù)標(biāo)題、回復(fù)內(nèi)容 符合1NF，沒有主鍵設(shè)計(jì)二、添加“發(fā)帖ID”、“回復(fù)ID字段符合1NF，主鍵用戶名,發(fā)帖ID,回復(fù)ID）不符合2NF一個(gè)良好的設(shè)計(jì)設(shè)計(jì)三用戶信息：用戶名，主頁， 帖子信息：發(fā)帖ID，標(biāo)題，內(nèi)容 回復(fù)信息：回復(fù)ID，標(biāo)題，內(nèi)容 發(fā)貼：用戶名，發(fā)帖ID 回復(fù)：發(fā)帖ID，回復(fù)ID 滿足1NF、2NF、3NF進(jìn)一步設(shè)計(jì)設(shè)計(jì)四用戶信息：用戶名，主頁， 帖子信息：用戶名，發(fā)帖ID，標(biāo)題，內(nèi)容 回復(fù)信息：發(fā)帖ID，回復(fù)ID，標(biāo)題，內(nèi)容 帖子信息表不滿足2NF、回復(fù)信息表不滿足2NF是上面設(shè)計(jì)中1-N關(guān)系的一種融合在一定程度上，是可以接受的不良設(shè)計(jì)舉例使用漢語拼音等作為表名、字段名使用錯誤的數(shù)據(jù)類型，如使用字符串表示日期、使用數(shù)值表示外部編號違反1NF，使用特殊符號作為分隔符直接使用簡單實(shí)體的名字作為實(shí)體標(biāo)識，造成潛在的不一致將關(guān)系作為屬性，比如成績表把期中、期