第四章-表的設(shè)計(jì)、創(chuàng)建及維護(hù)

第4章表的設(shè)計(jì)、創(chuàng)建及維護(hù)知識點(diǎn)回顧Oracle服務(wù)器包括：oracle數(shù)據(jù)庫和oracle實(shí)例Oracle實(shí)例結(jié)構(gòu)Oracle數(shù)據(jù)庫的物理組件和邏輯組件結(jié)構(gòu)化查詢語言(sql)及分類創(chuàng)建新用戶，并授予權(quán)限和角色2本講目標(biāo)了解Oracle數(shù)據(jù)類型掌握表的設(shè)計(jì)及創(chuàng)建掌握數(shù)據(jù)定義語言的使用3SQL簡介2-1SQL是StructuredQueryLanguage（結(jié)構(gòu)化查詢語言）的首字母縮寫詞SQL是數(shù)據(jù)庫語言，Oracle使用該語言存儲和檢索信息表是主要的數(shù)據(jù)庫對象，用于存儲數(shù)據(jù)通過SQL可以實(shí)現(xiàn)與Oracle服務(wù)器的通信SELECTenameFROMEmp;發(fā)送SQL查詢Oracle服務(wù)器enameBLAKESMITHALLENDAVIDMARTIN發(fā)送命令輸出到用戶端用戶4SQL簡介2-2SQL支持下列類別的命令：數(shù)據(jù)定義語言（DDLDataDefinitionLanguage）數(shù)據(jù)操縱語言（DMLDataManipulationLanguage）事務(wù)控制語言（TCLTransactionControlLanguage）數(shù)據(jù)控制語言（DCLDataControlLanguage）數(shù)據(jù)定義語言CREATEALTERDROP數(shù)據(jù)操縱語言INSERTSELECTDELETEUPDATE事務(wù)控制語言COMMITSAVEPOINTROLLBACK數(shù)據(jù)控制語言GRANTREVOKE5Oracle數(shù)據(jù)類型5-1創(chuàng)建表時，必須為各個列指定數(shù)據(jù)類型以下是Oracle數(shù)據(jù)類型的類別：數(shù)據(jù)類型字符數(shù)值日期時間RAW/LONGRAWLOB6Oracle數(shù)據(jù)類型5-2字符數(shù)據(jù)類型CHARVARCHAR2LONG當(dāng)需要固定長度的字符串時，使用CHAR數(shù)據(jù)類型。CHAR數(shù)據(jù)類型存儲字母數(shù)字值。CHAR數(shù)據(jù)類型的列長度可以是1到2000個字節(jié)。VARCHAR2數(shù)據(jù)類型支持可變長度字符串VARCHAR2數(shù)據(jù)類型存儲字母數(shù)字值VARCHAR2數(shù)據(jù)類型的大小在1至4000個字節(jié)范圍內(nèi)LONG數(shù)據(jù)類型存儲可變長度字符數(shù)據(jù)LONG數(shù)據(jù)類型最多能存儲2GB7Oracle數(shù)據(jù)類型5-3數(shù)值數(shù)據(jù)類型可以存儲整數(shù)、浮點(diǎn)數(shù)和實(shí)數(shù)最高精度為

38位數(shù)值數(shù)據(jù)類型的聲明語法：NUMBER[(p[,s])]P表示精度，S表示小數(shù)點(diǎn)的位數(shù)日期時間數(shù)據(jù)類型存儲日期和時間值，包括年、月、日，小時、分鐘、秒主要的日期時間類型有：DATE-存儲日期和時間部分，精確到整個的秒TIMESTAMP-存儲日期、時間和時區(qū)信息，秒值精確到小數(shù)點(diǎn)后6位8Oracle數(shù)據(jù)類型5-4RAW數(shù)據(jù)類型用于存儲二進(jìn)制數(shù)據(jù)RAW數(shù)據(jù)類型最多能存儲2000字節(jié)LONGRAW數(shù)據(jù)類型用于存儲可變長度的二進(jìn)制數(shù)據(jù)LONGRAW數(shù)據(jù)類型最多能存儲2GBLOBLOB稱為“大對象”數(shù)據(jù)類型，可以存儲多達(dá)4GB的非結(jié)構(gòu)化信息，例如聲音剪輯和視頻文件等LOB數(shù)據(jù)類型允許對數(shù)據(jù)進(jìn)行高效、隨機(jī)、分段的訪問BLOBCLOBBFILECLOB即CharacterLOB（字符LOB），它能夠存儲大量字符數(shù)據(jù)BLOB即BinaryLOB（二進(jìn)制LOB），可以存儲較大的二進(jìn)制對象，如圖形、視頻剪輯和聲音文件BFILE即BinaryFile（二進(jìn)制文件），它用于將二進(jìn)制數(shù)據(jù)存儲在數(shù)據(jù)庫外部的操作系統(tǒng)文件中9Oracle數(shù)據(jù)類型5-5Oracle中偽列就像一個表列，但是它并沒有存儲在表中偽列可以從表中查詢，但不能插入、更新和刪除它們的值常用的偽列有ROWID和ROWNUMROWID是表中行的存儲地址，該地址可以唯一地標(biāo)識數(shù)據(jù)表中的一行，可以使用ROWID偽列快速地定位表中的一行ROWNUM是查詢返回的結(jié)果集中行的序號，可以使用它來限制查詢返回的行數(shù)10數(shù)據(jù)定義語言數(shù)據(jù)定義語言用于改變數(shù)據(jù)庫結(jié)構(gòu)，包括創(chuàng)建、更改和刪除數(shù)據(jù)庫對象用于操縱表結(jié)構(gòu)的數(shù)據(jù)定義語言命令有：CREATETABLEALTERTABLETRUNCATETABLEDROPTABLE11表的創(chuàng)建和維護(hù)用來創(chuàng)建和修改數(shù)據(jù)庫表的命令被稱為“數(shù)據(jù)定義語言”（DataDefinitionLanguage，DDL）命令。這些命令實(shí)際上是專門用來創(chuàng)建或修改數(shù)據(jù)庫對象的SQL命令。12表設(shè)計(jì)3-1首先表的名稱并確定結(jié)構(gòu)；確定哪些列將包含在這個表中，確定任何字符或數(shù)字列所需要的寬度在Oracle中命名表和列時的規(guī)則：表和列的名稱最多可以達(dá)到30個字符長，而且必須以一個字母開始，這個限制只適合于表或列的名稱，而不適合用于一列中的數(shù)據(jù)量。表和列名稱中允許數(shù)字，下劃線（_）和數(shù)字符號（#），但是不能在表和列名稱中包含任何空格。用戶擁有的每一個表應(yīng)該具有一個唯一的表名稱，而且每一個表中的列名稱也應(yīng)該是唯一的不能使用Oracle“保留字”，例如：SELECT，DISTINCT，CHAR和NUMBER等。13表設(shè)計(jì)3-2確定了表的內(nèi)容，可以創(chuàng)建列了，必須完成為每一列選擇一個名稱確定每一列將存儲的類型。確定（在某些情況下）列的最大寬度14表設(shè)計(jì)3-3在選擇列名稱之前，先看一下數(shù)據(jù)類型以及他們的默認(rèn)值Oracle數(shù)據(jù)類型數(shù)據(jù)類型說明VARCHAR2(n)長度可變的字符數(shù)據(jù)，其中n表示這個列的最大長度，最大大小是4000個字符。這種數(shù)據(jù)類型沒有默認(rèn)大小，必須指定一個最小值。例子：VARCHAR2(9),最多可以包含9個字母、數(shù)字或符號CHAR(n)長度固定的字符，其中n表示列的長度，默認(rèn)大小是1，最大大小是2000個字符。例子：CHAR(9)可以包含9個字母，數(shù)字或符號，但是輸入的字符少于9個，則會向右邊添加空格，使數(shù)據(jù)達(dá)到9個字符的長度。NUMBER(p,s)數(shù)字列，其中p表示精度（或小數(shù)右邊和左邊的總位數(shù)），最大值是38位NUMBER(7,2)DATE日期類型，默認(rèn)的格式“DD-MM-YY”，可以使用TO_CHAR格式顯示日期其他元素LONG最多存儲2G的長度可變的字符數(shù)據(jù)CLOB用于最多4GB的單字節(jié)字符數(shù)據(jù)RAW(n)最多存儲2000字節(jié)的原始二進(jìn)制數(shù)據(jù)。LONGRAW最多2GB的未結(jié)構(gòu)化數(shù)據(jù)BLOB最多可存儲4GB的未結(jié)構(gòu)化數(shù)據(jù)BFILE存儲了操作系統(tǒng)存儲的一個二進(jìn)制文件的文件定位器TIMESTAMPDATE的數(shù)據(jù)擴(kuò)展，不需要TO_CHAR函數(shù)就可引用時，分，秒INTERVAL用來確定特定的時間間隔或時間量15創(chuàng)建表關(guān)鍵字“CREATETABLE”指示Oralce創(chuàng)建一個表。可以包括可選的schema（架構(gòu)），表示誰將“擁有”要創(chuàng)建的這個表，例如：如果創(chuàng)建這個表的用戶也就是這個將要擁有這個表的人，那么可以忽略架構(gòu)，將默認(rèn)采用當(dāng)前的用戶名。另一方面，如果使用用戶名為DRAKE為某個人創(chuàng)建了ACCTMANAGER表，那么架構(gòu)和表名稱將屬于DRAKE的架構(gòu)，而不是屬于你的架構(gòu)。數(shù)據(jù)庫對象的所有者有權(quán)在對象上執(zhí)行這些操作。要想為其他人的架構(gòu)創(chuàng)建一個表（也就是其他人擁有的表），你必須具有對那個用戶的架構(gòu)使用CREATETABLE命令的權(quán)限。

CREATETABLE[schema]tablename(columnname

datatype[DEFAULTvalue],columnname

datatype[DEFAULTvalue],…);16定義列一個表最多可以包含1000列。CREATETABLEacctmanager(amidVARCHAR2(4),amnameVARCHAR2(20),amedateDATEDEFAULTSYSDATE,regionCHAR(2));創(chuàng)建表的時候允許向列分配一個默認(rèn)值。默認(rèn)值是用戶沒有在列中輸入內(nèi)容情況，Oracle自動存儲的值。表名雖然是以小寫字母輸入，但ORACLE會在處理命令時自動轉(zhuǎn)為大寫。將表名與列名取小寫字母為了與Oracle關(guān)鍵字區(qū)別。由于創(chuàng)建表的用戶就是這個表的擁有者，所以省略架構(gòu)名。amedate分配了一個默認(rèn)值SYSDATE，表明如果用戶輸入新的銷售經(jīng)理信息，而沒有包括這個人的聘用時間時，則oracle服務(wù)器會插入當(dāng)前日期。注意：如果定義amedate為空，則會生成一個錯誤。執(zhí)行了命令之后，只是創(chuàng)建了表結(jié)構(gòu)，并沒有數(shù)據(jù)。能夠執(zhí)行CREATETABLE命令必須有這個權(quán)限，如果沒有要求管理員授予。17查看表結(jié)構(gòu)：DESCRIBEDESCRIBEtablename用來顯示表的結(jié)構(gòu)注意：DESCRIBE是sql*plus的命令而不是SQL命令。命令的簡寫DESC結(jié)果中列出了定義的列和數(shù)據(jù)類型，還顯示了這一列是否為空，也就是是否允許NULL值，注意：amid如果定義為了主鍵，則會顯示NOTNULL，在講約束的時候會講到。18通過子查詢創(chuàng)建表創(chuàng)建一個包含現(xiàn)有表的數(shù)據(jù)的表，可以使用CREATETABLE命令并包括一個包含子查詢的AS子句。CREATETABLEtablename[(columnname,…)]AS(subquery);CREATETABLEsecustomerordersAS(SELECTcustomer#,state,ISBN,Category,quantity,cost,retailFROMcustomersNATURALJOINordersNATURALJOINOrderitemsNATURALJOINbooksWHEREstateIN('FL','GA','AL'));19修改現(xiàn)有表對表結(jié)構(gòu)進(jìn)行更改：添加一列，刪除一列，更改列的大小通過ALTERTABLE命令來來實(shí)現(xiàn)ORACLE的特性：可以修改表而不必關(guān)閉數(shù)據(jù)庫，即使用戶正在訪問這個表，仍然可以修改這個表而不必中斷服務(wù)。

ALTERTABLEtablenameADD|MODIFY|DROPCOLUMN|coloumnname[definition];20ALTERTABLE…ADD命令示例：acctmanager表中添加電話分機(jī)號碼ALTERTABLEtablenameADD(columnnamedatatype,[DEFAULT]…);ALTERTABLEacctmanagerADD(extNUMBER(4));21ALTERTBALE…MODIFY命令對列進(jìn)行的更改包括：更改一列的大?。ㄔ黾踊驕p?。└臄?shù)據(jù)類型更改或添加一列的默認(rèn)值A(chǔ)LTERTABLEtablenameMODIFY(columnnamedatatype,[DEFAULT]…);22ALTERTBALE…MODIFY命令修改這個表的時候要注意三個規(guī)則：1.一列必須與它已經(jīng)包含的數(shù)據(jù)字段一樣寬。（例如：假設(shè)已經(jīng)將一個列定義為15個字符寬的VARCHAR2數(shù)據(jù)類型，但是，這個特定列中的最大項(xiàng)目只包含12個字符，因此你只能將這一列的大小減小到12個字符寬。）示例：customers表lastname列最大項(xiàng)字符是8，減小到6要報(bào)錯ALTERTABLEcustomersMODIFY(lastnameVARCHAR(6));23ALTERTBALE…MODIFY命令2.如果一個NUMBER列已經(jīng)包含了數(shù)據(jù)，那么你不能降低這一列的精確度或小數(shù)位數(shù)。ALTERTABLEbooksMODIFY(costNUMBER(4,2));24ALTERTBALE…MODIFY命令3.更改一列的默認(rèn)值不會更改表中已經(jīng)存在的數(shù)據(jù)值。ALTERTABLEacctmanagerMODIFY(extDEFAULT1200);同時更改多列ALTERTABLEacctmanagerMODIFY(extDEFAULT1200,amnamevarchar2(25));25ALTERTABLE…DROPCOLUMN命令使用該命令可以從一個表中刪除現(xiàn)有列注意：因?yàn)樵撟泳鋵h除列及其內(nèi)容，所以使用要格外小心ALTERTABLEtablenameDROPCOLUMNcloumnname;26ALTERTABLE…DROPCOLUMN命令使用DROPCOLUMN子句時，記住以下幾點(diǎn)：與帶有ADD或MODIFY的子句的ALTERTABLE命令不同，DROPCOLUMN子句只能引用一個列。如果從表中刪除一列，那么刪除將是永久的，如果不小心從表中錯誤地刪除了列，那么你不能“取消”這種損壞，唯一的選擇是將這一列重新添加到表中，然后手工重新輸入以前包含的所有數(shù)據(jù)。你不能刪除表中剩余的最后一列，如果一個表只包含一列并且你嘗試刪除這一列，那么這個命令將會失敗，將返回一個出錯消息。27ALTERTABLE…DROPCOLUMN命令繼續(xù)刪除ALTERTABLEacctmanagerDROPCOLUMNext;ALTERTABLEacctmanagerDROPCOLUMNREGION;ALTERTABLEacctmanagerDROPCOLUMNAMEDATE;ALTERTABLEacctmanagerDROPCOLUMNAMNAME;ALTERTABLEacctmanagerDROPCOLUMNAMID;28ALTERTABLE…SETUNUSED/DROPUNUSEDCOLUMNS命令當(dāng)Oracle服務(wù)器從一個非常大的表中刪除一列時，這將降低用戶的查詢或其他SQL命令的處理速度，為了避免這種問題，可以在ALTERTABLE命令中包括SETUNUSED子句，將這一標(biāo)記記為以后再刪除.如果將一列標(biāo)記為刪除，那么這一列就是標(biāo)記為不可用的，它不會顯示在表結(jié)構(gòu)中。因?yàn)檫@一列是不可用的，所以它也不會出現(xiàn)在任何查詢的結(jié)果中，也不能在這一列上執(zhí)行除了ALTERTABLE…DROPUNUSED命令之外的其他任何操作。換句話說，將一列設(shè)置為“unused”（不使用）之后.這一列及其所有的內(nèi)容都將無法使用，以后也不能恢復(fù)它。這是推遲從存儲設(shè)備中物理清除數(shù)據(jù)——通常推遲到服務(wù)器正在處理的查詢很少時，例如在營業(yè)時間之后。ALTERTABLE命令使用DROPUNUSED子句來完成已經(jīng)標(biāo)記為“不使用”的任何列的刪除過程。29ALTERTABLE…SETUNUSED/DROPUNUSEDCOLUMNS命令A(yù)LTERTABLEtablenameSETUNUSED(columnname);或者ALTERTABLEtablenameSETUNUSEDCOLUMNcolumnname;ALTERTABLEtablen