數(shù)據(jù)庫模式對象

2第5章模式對象本章要點：掌握表的創(chuàng)建與管理。理解并掌握表的完整性約束。了解索引。掌握視圖的使用。了解序列。了解同義詞。35.1表表是數(shù)據(jù)庫中最常用的模式對象，用戶的數(shù)據(jù)在數(shù)據(jù)庫中是以表的形式存儲的。表通常由一個或多個列組成，每個列表示一個屬性，而表中的一行則表示一條記錄。本節(jié)主要介紹如何創(chuàng)建表，以及如何管理表的結(jié)構(gòu)等。45.1.1數(shù)據(jù)類型表中的列用于存放數(shù)據(jù)，這些數(shù)據(jù)都需要有對應(yīng)的數(shù)據(jù)類型，例如年齡對應(yīng)整數(shù)類型，姓名對應(yīng)字符串類型，生日對應(yīng)日期類型等。而一種數(shù)據(jù)類型還可以擁有不同的長度，比如性別一般是兩個字節(jié)長度的字符串，姓名一般是4~8個字節(jié)長度的字符串。Oracle系統(tǒng)提供了功能非常完全的數(shù)據(jù)類型，常用的數(shù)據(jù)類型如表5-1所示。參見教材P8955.1.2創(chuàng)建表創(chuàng)建表需要使用CREATETABLE語句，其語法如下：CREATETABLE[schema.]table_name(column_namedata_type[DEFAULTexpression][[CONSTRAINTconstraint_name]constraint_def][,...])[TABLESPACEtablespace_name];65.1.2創(chuàng)建表【例5.1】創(chuàng)建一個表person，如下：CREATETABLEperson( idNUMBER(4), nameVARCHAR2(8), sexCHAR(2), birthdayDATE);上述語句創(chuàng)建了一個簡單的表person，該表有4個列，分別為NUMBER(4)類型的id列、VARCHAR2(8)類型的name列、CHAR(2)類型的sex列和DATE類型的birthday列。創(chuàng)建該表時沒有為該表指定存儲表空間，所以該表將被存放到默認(rèn)表空間中。75.1.3管理表中的列1．增加列為表增加列的語法形式如下：ALTERTABLEtable_nameADDcolumn_namedata_type;2．刪除列刪除表中的列時可以分為一次刪除一列和一次刪除多列。一次刪除一列的語法形式如下：ALTERTABLEtable_nameDROPCOLUMNcolumn_name;一次刪除多列的語法形式如下：ALTERTABLEtable_nameDROP(column_name,...);85.1.3管理表中的列3．修改列的名稱修改表中的列的名稱的語法如下：ALTERTABLEtable_nameRENAMECOLUMNcolumn_nameTOnew_column_name;4．修改列的數(shù)據(jù)類型修改表中的列的數(shù)據(jù)類型的語法如下：ALTERTABLEtable_nameMODIFYcolumn_namenew_data_type;95.1.3管理表中的列5．使用UNUSED關(guān)鍵字由于刪除列時，系統(tǒng)會刪除列中存儲的所有數(shù)據(jù)，并釋放該列所占用的存儲空間，所以在數(shù)據(jù)庫使用高峰期間執(zhí)行刪除列的操作會占用過多的系統(tǒng)資源，而且執(zhí)行時間也會很長。這時，數(shù)據(jù)庫管理員可以將該列設(shè)置為UNUSED狀態(tài)。將表中的列設(shè)置為UNUSED狀態(tài)的語法如下：ALTERTABLEtable_nameSETUNUSED(column_name[,...]);105.1.4重命名表重命名表有兩種語法形式，一種是使用ALTERTABLE語句，語法如下：ALTERTABLEtable_nameRENAMETOnew_table_name;【例5.7】將person表重命名為person01表，如下：SQL>ALTERTABLEpersonRENAMETOperson01;表已更改。另一種是直接使用RENAME語句，語法如下：RENAMEtable_nameTOnew_table_name;【例5.8】將person01表重命名為person表，如下：SQL>RENAMEperson01TOperson;表已重命名。115.1.5移動表在創(chuàng)建表時可以為表指定存儲空間，如果不指定，Oracle會將該表存儲到默認(rèn)表空間中。根據(jù)需要可以將表從一個表空間中移動到另一個表空間中。語法如下：ALTERTABLEtable_nameMOVETABLESPACEtablespace_name;【例5.9】移動person表。首先通過數(shù)據(jù)字典user_tables，查看person表當(dāng)前存儲在哪個表空間中，如下：SQL>SELECTtable_name,tablespace_nameFROMuser_tables2WHEREtable_name='PERSON';TABLE_NAME TABLESPACE_NAME------------------------- ------------------------------PERSON SYSTEM125.1.5移動表下面使用ALTERTABLE語句將其移動到users表空間中，如下：SQL>ALTERTABLEpersonMOVETABLESPACEusers;表已更改。再次查詢數(shù)據(jù)字典user_tables，觀察person表是否已經(jīng)移動成功，如下：SQL>SELECTtable_name,tablespace_nameFROMuser_tables2WHEREtable_name='PERSON';TABLE_NAME TABLESPACE_NAME------------------------- ------------------------------PERSON USERS135.1.6截斷表截斷表可以快速刪除表中的所有行，Oracle會重置表的存儲空間，并且不會在撤消表空間中記錄任何撤消數(shù)據(jù)，也就是說無法進行數(shù)據(jù)撤消。截斷表的語法如下：TRUNCATETABLEtable_name;【例5.10】截斷person表，如下：SQL>TRUNCATETABLEperson;表被截斷。145.1.7刪除表用戶只能刪除自己模式中的表；如果需要刪除其他模式中的表，則該用戶必須具有DROPANYTABLE的系統(tǒng)權(quán)限。刪除表的語法如下：DROPTABLEtable_name[CASCADECONSTRAINTS][PURGE];語法說明如下。CASCADECONSTRAINTS：指定刪除表的同時，刪除所有引用這個表的視圖、約束、索引和觸發(fā)器等。PURGE：表示刪除該表后，立即釋放該表所占用的資源空間。155.2實驗指導(dǎo)——查看UNUSED狀態(tài)的列實驗指導(dǎo)5-1：查看UNUSED狀態(tài)的列使用sys用戶身份連接數(shù)據(jù)庫，然后使用DESC命令分別查看數(shù)據(jù)字典obj$和col$的結(jié)構(gòu)，如下：參見教材P95查詢person表中所有列(包括UNUSED狀態(tài)的列)的名稱和序號，如下：參見教材P96從結(jié)果中可以看到有一列的名稱為SYS_C00004_09090211:07:28$，它就是age列被設(shè)置成UNUSED狀態(tài)后，Oracle系統(tǒng)自動為其更改的名稱，其序號也由原始的4被更改為165.3表的完整性約束通過為表中的列增加約束條件，可以防止用戶向該列傳遞不合要求的數(shù)據(jù)。例如人員表的性別列，使用數(shù)據(jù)類型CHAR(2)可以將該列的輸入數(shù)據(jù)限定為兩個字節(jié)長度的字符串，但不能對字符串的內(nèi)容做限制，像“ab”、“12”和“家”等都是兩個字節(jié)長度的字符串，它們都可以成功地傳遞給性別列，但是它們很明顯是不符合要求的數(shù)據(jù)。為了防止這種情況的出現(xiàn)，可以對表添加完整性約束。175.3.1約束的分類按照不同的角度，可以將表的完整性約束分成不同的類別。主要可以選取兩個角度：約束的作用域和約束的用途。(1) 按照約束的作用域可以將表的完整性約束分為如下兩大類。表級約束：應(yīng)用于表，對表中的多個列起作用。列級約束：應(yīng)用于表中的一列，只對該列起作用。185.3.1約束的分類(2) 按照約束的用途可以將表的完整性約束分為5類，如表5-2所示。約

束簡

寫說

明NOTNULLC非空約束。指定一列不允許存儲空值。這實際就是一種強制的CHECK約束PRIMARYKEYP主鍵約束。指定表的主鍵。主鍵由一列或多列組成，唯一標(biāo)識表中的一行UNIQUEU唯一約束。指定一列或一組列只能存儲唯一的值CHECKC檢查約束。指定一列或一組列的值必須滿足某種條件FOREIGNKEYR外鍵約束。指定表的外鍵。外鍵引用另外一個表中的一列，在自引用的情況中，則引用本表中的一列195.3.2NOTNULL約束1．添加NOTNULL約束在創(chuàng)建表時，為列添加NOTNULL約束，形式如下：column_namedata_type[CONSTRAINTconstraint_name]NOTNULL其中，CONSTRAINTconstraint_name表示為約束指定名稱。

ALTERTablepersonMODIFYnameNOTNULL;2．刪除NOTNULL約束如果需要刪除表中的列上的NOTNULL約束，依然是使用ALTERTABLE…MODIFY語句，形式如下：ALTERTABLEtable_nameMODIFYcolumn_nameNULL;205.3.3UNIQUE約束1．添加UNIQUE約束在創(chuàng)建表時，為列添加UNIQUE約束，形式如下：column_namedata_type[CONSTRAINTconstraint_name]UNIQUE[CONSTRAINTconstraint_name]UNIQUE(column_name)添加約束ALTERTABLEtable_nameADD[CONSTRAINTconstraint_name]unique(column_name)215.3.3UNIQUE約束2．刪除UNIQUE約束刪除列上的UNIQUE約束，可以使用ALTERTABLE…DROP語句，形式如下：ALTERTABLEtable_nameDROPUNIQUE(column_name);225.3.4PRIMARYKEY約束1．添加PRIMARYKEY約束在創(chuàng)建表時，為列添加PRIMARYKEY約束，形式如下：column_namedata_type[CONSTRAINTconstraint_name]PRIMARYKEY[CONSTRAINTconstraint_name]PRIMARYKEY(column_name)2．刪除PRIMARYKEY約束刪除列上的PRIMARYKEY約束，需要使用ALTERTABLE…DROP語句，不過形式上只能采取指定約束名的方式，如下：ALTERTABLEtable_nameDROPCONSTRAINconstraint_name;235.3.5CHECK約束CHECK約束是指檢查約束，用于指定一個條件，對傳遞給列的數(shù)據(jù)進行檢查，符合條件的數(shù)據(jù)才允許賦值給該列，否則提示錯誤。在創(chuàng)建表時，為列添加CHECK約束，形式如下：column_namedata_type[CONSTRAINTconstraint_name]CHECK(check_condition)或：CREATETABLEtable_name( column_namedata_type, [...,] [CONSTRAINTconstraint_name]CHECK(check_condition) [,...])其中，check_condition表示檢查條件，在條件中需要指定列名。245.3.5CHECK約束ALTERTABLEpersonADDCHECK(sexIN(‘male’,’female’));ALTERTABLEpersonADDCHECK(id>5andid<10000);ALTERTABLEpersonADDCHECK(id>5andid<10000andsexIN(‘male’,’female’));255.3.6FOREIGNKEY約束FOREIGNKEY約束是指外鍵約束，用于引用本表或另一個表中的一列或一組列。FOREIGNKEY約束具有如下特點：被引用的列或列組應(yīng)該具有主鍵約束或唯一約束。引用列的取值只能為被引用列的值或NULL值?？梢詾橐粋€列或一組列定義FOREIGNKEY約束。引用列與被引用列可以在同一個表中，這種情況稱為“自引用”。如果引用列中存儲了被引用列的某個值，則不能直接刪除被引用列中的這個值，否則會與第二條相矛盾。如果一定要刪除，需要先刪除引用列中的這個值，然后再刪除被引用列中的這個值。265.3.6FOREIGNKEY約束1．添加FOREIGNKEY約束在創(chuàng)建表時，為列添加FOREIGNKEY約束，形式如下：column_name1data_type[CONSTRAINTconstraint_name]REFERENCEStable_name(column_name2)2．指定級聯(lián)操作類型在添加FOREIGNKEY約束時，還可以指定級聯(lián)操作的類型，主要用于確定當(dāng)刪除(ONDELETE)父表中的一條記錄時，如何處理子表中的外鍵字段。有如下3種引用類型。CASCADE：此關(guān)鍵字用于表示當(dāng)刪除主表中被引用列的數(shù)據(jù)時，級聯(lián)刪除子表中相應(yīng)的數(shù)據(jù)行。SETNULL：此關(guān)鍵字用于表示當(dāng)刪除主表中被引用列的數(shù)據(jù)時，將子表中相應(yīng)引用列的值設(shè)置為NULL值。這種情況要求子表中的引用列支持NULL值。NOACTION：此關(guān)鍵字用于表示當(dāng)刪除主表中被引用列的數(shù)據(jù)時，如果子表的引用列中包含該值，則禁止該操作執(zhí)行。默認(rèn)為此選項。275.3.7禁用和激活約束在添加約束時或添加約束后，都可以設(shè)置約束的狀態(tài)，約束有如下兩種狀態(tài)。激活狀態(tài)(ENABLE)：約束只有處于激活狀態(tài)時，才會起到約束的作用。如果操作與約束沖突，則該操作將被禁止執(zhí)行。默認(rèn)為此狀態(tài)。禁用狀態(tài)(DISABLE)：如果約束處于禁用狀態(tài)，則該約束將不起任何作用，即使操作與約束沖突，也會被執(zhí)行。在創(chuàng)建表時設(shè)置約束的狀態(tài)，形式如下：column_name1data_type[CONSTRAINTconstraint_name]constraint_typeDISABLE|ENABLE285.3.8約束的驗證狀態(tài)約束的驗證狀態(tài)有兩種：驗證狀態(tài)(VALIDATE)非驗證狀態(tài)(NOVALIDATE)。這兩種狀態(tài)與上節(jié)介紹的激活、禁用狀態(tài)可以組合成如下4種約束狀態(tài)。ENABLEVALIDATE(激活驗證狀態(tài))ENABLENOVALIDATE(激活非驗證狀態(tài))DISABLEVALIDATE(禁用驗證狀態(tài))DISABLENOVALIDATE(禁用非驗證狀態(tài))295.4實驗指導(dǎo)——為圖書管理系統(tǒng)創(chuàng)建表實驗指導(dǎo)5-2：為圖書管理系統(tǒng)創(chuàng)建表表的創(chuàng)建過程如下。(1) 創(chuàng)建圖書類別表type，語句如下：CREATETABLEtype( typeidNUMBER(10)PRIMARYKEY, --主鍵列

typenameVARCHAR2(20)UNIQUENOTNULL --類別名稱(例如歷史))TABLESPACEbookspace;(2) 創(chuàng)建管理員等級表grade，語句參見教材P106(3) 創(chuàng)建圖書信息表book，語句參見教材P106(4) 創(chuàng)建管理員表manager，語句參見教材P107(5) 創(chuàng)建學(xué)生信息表student，語句參見教材P107(6) 創(chuàng)建圖書證表card，語句參見教材P107(7) 創(chuàng)建借書信息表lend，語句參見教材P107305.5索引索引是數(shù)據(jù)庫中用于存放表中每一條記錄的位置的一種對象，其主要目的是為了加快數(shù)據(jù)的讀取速度和完整性檢查。不過，創(chuàng)建索引需要占用許多存儲空間，而且在向表中添加和刪除記錄時，數(shù)據(jù)庫需要花費額外的開銷來更新索引。315.5.1創(chuàng)建B樹索引B樹索引是Oracle中最常用、也是默認(rèn)的索引類型。其邏輯結(jié)構(gòu)如圖5-2所示。325.5.1創(chuàng)建B樹索引采用B樹索引可以確保無論索引條目位于何處，Oracle都只需要花費相同的I/O就可以獲取它。例如，采用上述B樹索引搜索編號為1020的節(jié)點，其搜索過程如下。(1) 訪問根節(jié)點，將1020與1001和1013進行比較。(2) 因為1020大于1013，所以接下來搜索右邊分支，在右邊分支中將1020再與1013、1017和1021進行比較。(3) 因為1020大于1017但是小于1021，所以搜索右邊分支的第二個葉子節(jié)點，并找到要查詢的索引條目。335.5.1創(chuàng)建B樹索引創(chuàng)建B樹索引的語法如下：CREATE[UNIQUE]INDEXindex_nameONtable_name(column_name[,...])[INITRANSn][MAXTRANSn][PCTFREEn][STORAGEstorage][TABLESPACEtablespace_name];345.5.2創(chuàng)建基于函數(shù)的索引如果檢索數(shù)據(jù)時需要對字符大小寫或數(shù)據(jù)類型進行轉(zhuǎn)換，則使用這種索引可以提高檢索效率?！纠?.20】假設(shè)person表中某數(shù)據(jù)行的name列的值為CANDY，如果需要根據(jù)這個name值查找該行數(shù)據(jù)，則需要使用如下語句：SQL>SELECT*FROMpersonWHEREname='CANDY';上述語句中的字符串CANDY必須與列中存儲的值的大小寫保持一致，否則將無法查詢到該行數(shù)據(jù)。355.5.2創(chuàng)建基于函數(shù)的索引創(chuàng)建基于函數(shù)的索引與創(chuàng)建普通B樹索引沒有大的區(qū)別，只需要在列上添加函數(shù)即可?！纠?.21】在person表的name列上創(chuàng)建基于LOWER函數(shù)的索引，如下：SQL>CREATEINDEXname_lower_index2ONperson(LOWER(name))3TABLESPACEmyspace;索引已創(chuàng)建。365.5.3創(chuàng)建位圖索引位圖(位映射)索引與B樹索引不同，使用B樹索引時，通過在索引中保存排過序的索引列的值，以及數(shù)據(jù)行的ROWID來實現(xiàn)快速查找。而位圖索引不存儲ROWID值，也不存儲鍵值，它一般在包含少量不同值的列上創(chuàng)建。如果要在person表的sex列上創(chuàng)建索引，則應(yīng)該創(chuàng)建位圖索引。創(chuàng)建位圖索引的簡單語法形式如下：CREATEBITMAPINDEXindex_nameONtable_name(column_name[,...])[TABLESPACEtablespace_name];其中，BITMAP表示創(chuàng)建的索引類型為位圖索引。375.5.4管理索引1．重命名索引重命名索引的語法形式如下：ALTERINDEXindex_nameRENAMETOnew_index_name;2．合并索引合并索引可以清除索引中的存儲碎片，其語法如下：ALTERINDEXindex_nameCOALESCE[DEALLOCATEUNUSED];385.5.4管理索引如圖5-3所示是B樹索引的合并效果。3．重建索引重建索引的語法如下：ALTER[UNIQUE]INDEXindex_nameREBUILD[INITRANSn][MAXTRANSn][PCTFREEn][STORAGEstorage][TABLESPACEtablespace_name];395.5.4管理索引4．監(jiān)視索引監(jiān)視索引用于了解索引的使用情況，目的是為了確保索引得到有效的利用。要監(jiān)視某個索引，需要先打開該索引的監(jiān)視狀態(tài)，不需要時，也可以關(guān)閉索引的監(jiān)視狀態(tài)。語法如下：ALTERINDEXindex_nameMONITORING|NOMONITORINGUSAGE;5．刪除索引通常在如下情況下需要刪除某個索引：該索引不需要再使用。該索引很少被使用。索引的使用情況可以通過監(jiān)視來查看。該索引中包含較多的存儲碎片，需要重建該索引。刪除索引主要分為如下兩種情況。(1) 刪除基于約束條件的索引(2) 刪除使用CREATEINDEX語句創(chuàng)建的索引405.6視圖視圖是一個虛擬表，它并不存儲真實的數(shù)據(jù)，它的行和列的數(shù)據(jù)來自于定義視圖的子查詢語句中所引用的表，這些表通常也稱為視圖的基表。視圖可以建立在一個或多個表(或其他視圖)上，它不占用實際的存儲空間，只是在數(shù)據(jù)字典中保存它的定義信息。415.6.1創(chuàng)建視圖創(chuàng)建視圖需要使用CREATEVIEW語句，其語法如下：CREATE[ORREPLACE][FORCE|NOFORCE]VIEWview_name[(alias_name[,...])]ASsubquery[WITH{CHECKOPTION|READONLY}CONSTRAINTconstraint_name];語法說明如下。(1) ORREPLACE(2) FORCE|NOFORCE(3) view_name(4) alias_name(5) subquery(6) CHECKOPTION(7) READONLY(8) CONSTRAINTconstraint_name425.6.1創(chuàng)建視圖1．創(chuàng)建簡單視圖這里將基于單個表，而且不對子查詢檢索的列進行函數(shù)或數(shù)學(xué)計算的視圖稱為簡單視圖?！纠?.24】在scott用戶下創(chuàng)建基于一個emp表的視圖，并對該視圖進行查詢，示例步驟如下。參見教材P1142．創(chuàng)建復(fù)雜視圖這里將基于多個表，或者對子查詢檢索的列進行函數(shù)或數(shù)學(xué)計算的視圖稱為復(fù)雜視圖?！纠?.25】創(chuàng)建基于一個emp表的視圖emp_view2，并且對子查詢中檢索的sal列進行數(shù)學(xué)計算，查詢工資上漲10%以后的工資大于2500的員工信息，如下：參見教材P115435.6.2對視圖執(zhí)行DML操作對視圖執(zhí)行DML操作，實際上就是對視圖的基表執(zhí)行DML操作。一般來說，簡單視圖的所有列都支持DML操作，而對于復(fù)雜視圖來講，如果該列進行了函數(shù)或數(shù)學(xué)計算，或者在表的連接查詢中該列不屬于主表中的列，則該列不支持DML操作。1．查詢視圖中的列是否支持DML操作在對視圖執(zhí)行DML操作前，應(yīng)先了解該視圖中哪些列支持DML操作，可以通過數(shù)據(jù)字典user_updatable_columns進行查看。2．對視圖執(zhí)行DML操作可以對視圖中支持DML操作的所有列執(zhí)行DML操作，操作結(jié)果將直接反映到基表中。445.6.2對視圖執(zhí)行DML操作3．使用WITHCHECKOPTION子句在創(chuàng)建視圖時使用WITHCHECKOPTION子句，可以限定對視圖執(zhí)行的DML操作必須滿足視圖中子查詢的條件。4．使用WITHREADONLY子句如果希望視圖僅僅只能讀取基表中的數(shù)據(jù)，而不希望通過它可以更改基表中的數(shù)據(jù)，可以在創(chuàng)建視圖時使用WITHREADONLY子句。455.6.3查詢視圖的定義信息【例5.32】查詢emp_view視圖的定義信息，如下：SQL>SETLONG200SQL>COLUMNtextFORMATA50SQL>COLUMNview_nameFORMATA10SQL>SELECTview_name,text,read_only2FROMuser_viewsWHEREview_name='EMP_VIEW';VIEW_NAME TEXT R---------- ------------------------------------------- -EMP_VIEW SELECTempno,ename,job,sal,deptno N FROMempWHEREsal>2500 WITHCHECKOPTION465.6.3查詢視圖的定義信息對上述語句說明如下。SETLONG：用于設(shè)置SQL*Plus中每列數(shù)據(jù)可以顯示的最多字符數(shù)。這里根據(jù)需要將其設(shè)置為200，因為text列的內(nèi)容較多。view_name：視圖名稱。text：視圖中子查詢語句的內(nèi)容。read_only：視圖是否為只讀。Y表示是，N表示否。475.6.4修改與刪除視圖修改視圖可以使用CREATEORREPLACEVIEW語句，使用該語句修改視圖，實際上是刪除原來的視圖，然后創(chuàng)建一個全新的視圖，只不過前后兩個視圖的名稱一樣而已。刪除視圖時需要使用DROPVIEW語句，其語法如下：DROPVIEWview_name;刪除視圖后，不會影響該視圖的基表中的數(shù)據(jù)。485.7序列在Oracle中，可以使用序列自動生成一個整數(shù)序列，主要用來自動為表中的數(shù)據(jù)類型的主鍵列提供有序的唯一值，這樣就可以避免在向表中添加數(shù)據(jù)時，手工指定主鍵值。而且使用手工指定主鍵值這種方式時，由于主鍵值不允許重復(fù)，因此它要求操作人員在指定主鍵值時自己判斷新添加的值是否已經(jīng)存在，這很顯然是不可取的。495.7.1創(chuàng)建序列序列與視圖一樣，并不占用實際的存儲空間，只是在數(shù)據(jù)字典中保存它的定義信息。創(chuàng)建序列需要使用CREATESEQUENCE語句，其語法如下：CREATESEQUENCEsequence_name[STARTWITHstart_number]