08.表的創(chuàng)建和維護(hù)

1、表的創(chuàng)建和維護(hù),Oracle SQL和PL/SQL編程,目標(biāo),使用CREATE TABLE命令創(chuàng)建新表使用子查詢創(chuàng)建新表向現(xiàn)有表添加列修改現(xiàn)有表中的一列大小從現(xiàn)有表中刪除一列將一列標(biāo)記為未使用，在以后刪除它重命名表截?cái)啾韯h除表,表的創(chuàng)建和維護(hù),用來(lái)創(chuàng)建和修改數(shù)據(jù)庫(kù)表的命令被稱為“數(shù)據(jù)定義語(yǔ)言”（Data Definition Language，DDL）命令。這些命令實(shí)際上是專門用來(lái)創(chuàng)建或修改數(shù)據(jù)庫(kù)對(duì)象的SQL命令。,表設(shè)

2、計(jì) 3-1,首先表的名稱并確定結(jié)構(gòu)；確定哪些列將包含在這個(gè)表中，確定任何字符或數(shù)字列所需要的寬度在Oracle中命名表和列時(shí)的規(guī)則：表和列的名稱最多可以達(dá)到30個(gè)字符長(zhǎng)，而且必須以一個(gè)字母開始，這個(gè)限制只適合于表或列的名稱，而不適合用于一列中的數(shù)據(jù)量。表和列名稱中允許數(shù)字，下劃線（_）和數(shù)字符號(hào)（#），但是不能在表和列名稱中包含任何空格。用戶擁有的每一個(gè)表應(yīng)該具有一個(gè)唯一的表名稱，而且每一個(gè)表中的列名稱也應(yīng)該是唯一的不能使用O

3、racle “保留字”，例如：SELECT，DISTINCT，CHAR和NUMBER等。,表設(shè)計(jì) 3-2,確定了表的內(nèi)容，可以創(chuàng)建列了，必須完成為每一列選擇一個(gè)名稱確定每一列將存儲(chǔ)的類型。確定（在某些情況下）列的最大寬度,表設(shè)計(jì) 3-3,在選擇列名稱之前，先看一下數(shù)據(jù)類型以及他們的默認(rèn)值,創(chuàng)建表,關(guān)鍵字“CREATE TABLE”指示Oralce創(chuàng)建一個(gè)表?？梢园蛇x的schema（架構(gòu)），表示誰(shuí)將“擁有”要?jiǎng)?chuàng)建的這個(gè)表，例如

4、：如果創(chuàng)建這個(gè)表的用戶也就是這個(gè)將要擁有這個(gè)表的人，那么可以忽略架構(gòu)，將默認(rèn)采用當(dāng)前的用戶名。另一方面，如果使用用戶名為DRAKE為某個(gè)人創(chuàng)建了ACCTMANAGER表，那么架構(gòu)和表名稱將屬于DRAKE的架構(gòu)，而不是屬于你的架構(gòu)。數(shù)據(jù)庫(kù)對(duì)象的所有者有權(quán)在對(duì)象上執(zhí)行這些操作。對(duì)于一個(gè)表來(lái)說(shuō)，另一個(gè)數(shù)據(jù)庫(kù)是用戶能夠查詢或處理表中包含的數(shù)據(jù)的唯一方式就是使用表所有者或數(shù)據(jù)庫(kù)管理提供的權(quán)限。當(dāng)然表名稱是用來(lái)標(biāo)識(shí)正在創(chuàng)建的表的名稱。要想為其他

5、人的架構(gòu)創(chuàng)建一個(gè)表（也就是其他人擁有的表），你必須具有對(duì)那個(gè)用戶的架構(gòu)使用CREATE TABLE命令的權(quán)限。,CREATE TABLE[schema] tablename(columnname datatype[DEFAULT value],columnname datatype[DEFAULT value],…);,定義列,一個(gè)表最多可以包含1000列。,CREATE TABLE acctmanager(amid VARC

6、HAR2(4),amname VARCHAR2(20),amedate DATE DEFAULT SYSDATE,region CHAR(2));,創(chuàng)建表的時(shí)候允許向列分配一個(gè)默認(rèn)值。默認(rèn)值是用戶沒(méi)有在列中輸入內(nèi)容情況，Oracle自動(dòng)存儲(chǔ)的值。表名雖然是以小寫字母輸入，但ORACLE會(huì)在處理命令時(shí)自動(dòng)轉(zhuǎn)為大寫。將表名與列名取小寫字母為了與Oracle關(guān)鍵字區(qū)別。由于創(chuàng)建表的用戶就是這個(gè)表的擁有者，所以省略架構(gòu)名。amed

7、ate分配了一個(gè)默認(rèn)值SYSDATE，表明如果用戶輸入新的銷售經(jīng)理信息，而沒(méi)有包括這個(gè)人的聘用時(shí)間時(shí)，則oracle服務(wù)器會(huì)插入當(dāng)前日期。注意：如果定義amedate為空，則會(huì)生成一個(gè)錯(cuò)誤。執(zhí)行了命令之后，只是創(chuàng)建了表結(jié)構(gòu)，并沒(méi)有數(shù)據(jù)。能夠執(zhí)行CREATE TABLE命令必須有這個(gè)權(quán)限，如果沒(méi)有要求管理員授予。,查看表結(jié)構(gòu)：DESCRIBE,,DESCRIBE tablename用來(lái)顯示表的結(jié)構(gòu)注意：DESCRIBE是sql*p

8、lus的命令而不是SQL命令。命令的簡(jiǎn)寫DESC,結(jié)果中列出了定義的列和數(shù)據(jù)類型，還顯示了這一列是否為空，也就是是否允許NULL值，注意：amid如果定義為了主鍵，則會(huì)顯示NOT NULL，在講約束的時(shí)候會(huì)講到。,通過(guò)子查詢創(chuàng)建表,創(chuàng)建一個(gè)包含現(xiàn)有表的數(shù)據(jù)的表，可以使用CREATE TABLE命令并包括一個(gè)包含子查詢的AS子句。,CREATE TABLE tablename[(columnname,…)]AS (subquery);

9、,CREATE TABLE secustomerordersAS (SELECT customer#,state,ISBN,Category,quantity,cost,retailFROM customers NATURAL JOIN orders NATURAL JOINOrderitems NATURAL JOIN booksWHERE state IN('FL','GA','AL

10、'));,修改現(xiàn)有表,對(duì)表結(jié)構(gòu)進(jìn)行更改：添加一列，刪除一列，更改列的大小通過(guò)ALTER TABLE命令來(lái)來(lái)實(shí)現(xiàn)ORACLE的特性：可以修改表而不必關(guān)閉數(shù)據(jù)庫(kù)，即使用戶正在訪問(wèn)這個(gè)表，仍然可以修改這個(gè)表而不必中斷服務(wù)。,ALTER TABLE tablenameADD|MODIFY|DROP COLUMN|coloumnname[definition];,ALTER TABLE…ADD命令,示例：acctmanager表中添

11、加電話分機(jī)號(hào)碼,ALTER TABLE tablenameADD(columnname datatype,[DEFAULT]…);,ALTER TABLE acctmanagerADD(ext NUMBER(4));,ALTER TBALE…MODIFY命令,對(duì)列進(jìn)行的更改包括：更改一列的大?。ㄔ黾踊驕p?。└臄?shù)據(jù)類型更改或添加一列的默認(rèn)值,ALTER TABLE tablenameMODIFY(columnname dat

12、atype,[DEFAULT]…);,ALTER TBALE…MODIFY命令,修改這個(gè)表的時(shí)候要注意三個(gè)規(guī)則：1.一列必須與它已經(jīng)包含的數(shù)據(jù)字段一樣寬。（例如：假設(shè)已經(jīng)將一個(gè)列定義為15個(gè)字符寬的VARCHAR2數(shù)據(jù)類型，但是，這個(gè)特定列中的最大項(xiàng)目只包含12個(gè)字符，因此你只能將這一列的大小減小到12個(gè)字符寬。）示例：customers表lastname列最大項(xiàng)字符是8，減小到6要報(bào)錯(cuò),ALTER TABLE customers

13、MODIFY (lastname VARCHAR(6));,ALTER TBALE…MODIFY命令,2.如果一個(gè)NUMBER列已經(jīng)包含了數(shù)據(jù)，那么你不能降低這一列的精確度或小數(shù)位數(shù)。,ALTER TABLE booksMODIFY (cost NUMBER(4,2));,ALTER TBALE…MODIFY命令,3.更改一列的默認(rèn)值不會(huì)更改表中已經(jīng)存在的數(shù)據(jù)值。,ALTER TABLE acctmanagerMODIFY (ext

14、 DEFAULT 1200);,同時(shí)更改多列ALTER TABLE acctmanagerMODIFY (ext DEFAULT 1200, amname varchar2(25));,ALTER TABLE…DROP COLUMN命令,使用該命令可以從一個(gè)表中刪除現(xiàn)有列,注意：因?yàn)樵撟泳鋵h除列及其內(nèi)容，所以使用要格外小心,ALTER TABLE tablenameDROP COLUMN cloumnname;,ALTER TA

15、BLE…DROP COLUMN命令,使用DROP COLUMN子句時(shí)，記住以下幾點(diǎn)：與帶有ADD或MODIFY的子句的ALTER TABLE命令不同，DROP COLUMN子句只能引用一個(gè)列。如果從表中刪除一列，那么刪除將是永久的，如果不小心從表中錯(cuò)誤地刪除了列，那么你不能“取消”這種損壞，唯一的選擇是將這一列重新添加到表中，然后手工重新輸入以前包含的所有數(shù)據(jù)。你不能刪除表中剩余的最后一列，如果一個(gè)表只包含一列并且你嘗試刪除這一列

16、，那么這個(gè)命令將會(huì)失敗，Oracle9i將返回一個(gè)出錯(cuò)消息。,ALTER TABLE…DROP COLUMN命令,,繼續(xù)刪除,ALTER TABLE acctmanagerDROP COLUMN ext;,ALTER TABLE acctmanagerDROP COLUMN REGION;,ALTER TABLE acctmanagerDROP COLUMN AMEDATE;,ALTER TABLE acctmanagerDRO

17、P COLUMN AMNAME;,ALTER TABLE acctmanagerDROP COLUMN AMID;,ALTER TABLE…SET UNUSED/DROP UNUSED COLUMNS命令,當(dāng)Oracle服務(wù)器從一個(gè)非常大的表中刪除一列時(shí)，這將降低用戶的查詢或其他SQL命令的處理速度，為了避免這種問(wèn)題，可以在ALTER TABLE命令中包括SET UNUSED子句，將這一標(biāo)記記為以后再刪除.如果將一列標(biāo)記為刪除，那么

18、這一列就是標(biāo)記為不可用的，它不會(huì)顯示在表結(jié)構(gòu)中。因?yàn)檫@一列是不可用的，所以它也不會(huì)出現(xiàn)在任何查詢的結(jié)果中，也不能在這一列上執(zhí)行除了ALTER TABLE…DROP UNUSED命令之外的其他任何操作。換句話說(shuō)，將一列設(shè)置為“unused”（不使用）之后.這一列及其所有的內(nèi)容都將無(wú)法使用，以后也不能恢復(fù)它。這是推遲從存儲(chǔ)設(shè)備中物理清除數(shù)據(jù)——通常推遲到服務(wù)器正在處理的查詢很少時(shí)，例如在營(yíng)業(yè)時(shí)間之后。ALTER TABLE命令使用DRO

19、P UNUSED子句來(lái)完成已經(jīng)標(biāo)記為“不使用”的任何列的刪除過(guò)程。,ALTER TABLE…SET UNUSED/DROP UNUSED COLUMNS命令,,ALTER TABLE tablenameSET UNUSED(columnname);或者ALTER TABLE tablenameSET UNUSED COLUMN columnname;,ALTER TABLE tablenameDROP UNUSED COL

20、UMNS;,刪除已經(jīng)設(shè)置為“不使用”的任何列，而且可以使用這些列中包含的數(shù)據(jù)以前所占用的任何存儲(chǔ)空間,ALTER TABLE…SET UNUSED/DROP UNUSED COLUMNS命令,示例：,ALTER TABLE acctmanagerSET UNUSED(region);,ALTER TABLE acctmanagerDROP UNUSED COLUMNS;,重命名表,,RENAME oldtablename TO ne

21、wtablename;,Oracle允許使用RENAME…TO命令更改你所擁有的任何表的名稱,在一個(gè)組織工作中，不要更改其他用戶訪問(wèn)的表的名稱，除非你事先通知了他們新的表的名稱,RENAME acctmanager TO acctman;,截?cái)啾?在截?cái)嘁粋€(gè)表時(shí)，表中包含的所有行都將被刪除，但表本身將會(huì)保留，換句話說(shuō)，列依然存在，只是其中沒(méi)有存儲(chǔ)值。實(shí)際上與刪除一個(gè)表中的所有行是相同的，但是，如果只是刪除一個(gè)表中的所有行，那么這些行多占

22、用的存儲(chǔ)空間仍然是分配給這個(gè)表的。要想刪除存儲(chǔ)在一個(gè)表中的行并釋放這些行所占有的存儲(chǔ)空間，可以使用TRUNCATE TABLE命令。,TRUNCATE TABLE tablename;,刪除表,,DROP TABLE tablename;,刪除時(shí)一定要非常小心，刪除一個(gè)表之后，表及它所包含的所有數(shù)據(jù)都將丟失，這個(gè)表所創(chuàng)建的任何索引也將刪除，索引后面講解。,DROP TABLE acctman;,總結(jié),可以使用CREATE…TABLE命令

23、創(chuàng)建一個(gè)表。必須根據(jù)列名稱，數(shù)據(jù)類型，某些數(shù)據(jù)類型的寬度定義表中將要包含的每一列。一個(gè)表最多可以包含1000列。表中的每一個(gè)列名稱都必須是唯一的。表和列的名稱最多可以包含30個(gè)字符，名稱必須以一個(gè)字母開始，不能包含任何空格。要想根據(jù)現(xiàn)有的表中包含的結(jié)構(gòu)和數(shù)據(jù)創(chuàng)建一個(gè)表，使用CREATE TABLE…AS命令，以便使用一個(gè)子查詢從現(xiàn)有的表中提取必要的數(shù)據(jù)。你可以使用ALTER TABLE命令來(lái)更改一個(gè)表的結(jié)構(gòu)，可以使用ALTER

24、 TABLE添加、調(diào)整甚至刪除列。使用帶有DROP COLUMN子句的ALTER TABLE命令時(shí)，只能指定刪除一列。SET UNUSED子句可以用來(lái)標(biāo)記一列，這樣以后就可以釋放它的存儲(chǔ)空間了可以使用RENAME…TO命令來(lái)重命名一個(gè)表。通過(guò)TRUNCATE TABLE命令可以刪除一個(gè)表中的所有行。要想完全刪除一個(gè)表的結(jié)構(gòu)以及其內(nèi)容，可以使用DROP TABLE命令。,2024/3/21,Inspur group,,,謝謝大家