les09_創(chuàng)建和管理表

1、9Copyright Oracle Corporation, 2001. All rights reserved.創(chuàng)建和管理表創(chuàng)建和管理表9-2Copyright Oracle Corporation, 2001. All rights reserved.目標(biāo)目標(biāo)通過本章學(xué)習(xí)，您將可以通過本章學(xué)習(xí)，您將可以: 描述主要的數(shù)據(jù)庫對象。描述主要的數(shù)據(jù)庫對象。創(chuàng)建表。創(chuàng)建表。描述各種數(shù)據(jù)類型。描述各種數(shù)據(jù)類型。修改表的定義。修改表的定義。刪除，重命名和清空表。刪除，重命名和清空表。9-3Copyright Oracle Corporation, 2001. All rights reserved.常

2、見的數(shù)據(jù)庫對象常見的數(shù)據(jù)庫對象對象對象描述描述表表 基本的數(shù)據(jù)存儲集合，由行和列組成?；镜臄?shù)據(jù)存儲集合，由行和列組成。視圖視圖從表中抽出的邏輯上相關(guān)的數(shù)據(jù)集合。從表中抽出的邏輯上相關(guān)的數(shù)據(jù)集合。序列序列 提供有規(guī)律的數(shù)值。提供有規(guī)律的數(shù)值。索引索引提高查詢的效率提高查詢的效率同義詞同義詞 給對象起別名給對象起別名9-4Copyright Oracle Corporation, 2001. All rights reserved.命名規(guī)則命名規(guī)則表名和列名表名和列名:必須以字母開頭必須以字母開頭必須在必須在 130 個字符之間個字符之間必須只能包含必須只能包含 AZ, az, 09, _, $

3、, 和和 #必須不能和用戶定義的其他對象重名必須不能和用戶定義的其他對象重名必須必須 不能是不能是Oracle 的保留字的保留字9-5Copyright Oracle Corporation, 2001. All rights reserved.CREATE TABLE 語句語句必須具備必須具備:CREATE TABLE權(quán)限權(quán)限存儲空間存儲空間必須指定必須指定:表名表名列名列名, 數(shù)據(jù)類型數(shù)據(jù)類型, 尺寸尺寸CREATE TABLE schema.table (column datatype DEFAULT expr, .);9-6Copyright Oracle Corporation, 2

4、001. All rights reserved.引用其他用戶的表引用其他用戶的表其他用戶定義的表不在當(dāng)前用戶的方案中其他用戶定義的表不在當(dāng)前用戶的方案中應(yīng)該使用用戶名座位前綴，引用其他用戶定義的對象應(yīng)該使用用戶名座位前綴，引用其他用戶定義的對象9-7Copyright Oracle Corporation, 2001. All rights reserved.DEFAULT 選項選項插入時為一個列指定默認(rèn)值插入時為一個列指定默認(rèn)值字符串字符串, 表達(dá)式表達(dá)式, 或或SQL 函數(shù)都是合法的函數(shù)都是合法的其它列的列名和偽列是非法的其它列的列名和偽列是非法的默認(rèn)值必須滿足列的數(shù)據(jù)類型定義默認(rèn)值必須

5、滿足列的數(shù)據(jù)類型定義. hire_date DATE DEFAULT SYSDATE, . 9-8Copyright Oracle Corporation, 2001. All rights reserved.語法語法確認(rèn)確認(rèn)創(chuàng)建表創(chuàng)建表CREATE TABLE dept(deptno NUMBER(2),dname VARCHAR2(14),loc VARCHAR2(13);DESCRIBE dept9-9Copyright Oracle Corporation, 2001. All rights reserved.Oracle 數(shù)據(jù)庫中的表數(shù)據(jù)庫中的表用戶定義的表用戶定義的表:用戶自己創(chuàng)建

6、并維護(hù)的一組表用戶自己創(chuàng)建并維護(hù)的一組表包含了用戶所需的信息包含了用戶所需的信息數(shù)據(jù)字典數(shù)據(jù)字典:由由Oracle Server自動創(chuàng)建的一組表自動創(chuàng)建的一組表包含數(shù)據(jù)庫信息包含數(shù)據(jù)庫信息9-10Copyright Oracle Corporation, 2001. All rights reserved.SELECT table_name FROM user_tables ;SELECT * FROM user_catalog ;查詢數(shù)據(jù)字典查詢數(shù)據(jù)字典 查看用戶定義的各種數(shù)據(jù)庫對象查看用戶定義的各種數(shù)據(jù)庫對象 查看用戶定義的表查看用戶定義的表, 視圖視圖, 同義詞和序列同義詞和序列SELE

7、CT DISTINCT object_type FROM user_objects ; 查看用戶定義的表查看用戶定義的表.9-11Copyright Oracle Corporation, 2001. All rights reserved.數(shù)據(jù)類型數(shù)據(jù)類型數(shù)據(jù)類型數(shù)據(jù)類型描述描述VARCHAR2(size)可變長字符數(shù)據(jù)可變長字符數(shù)據(jù)CHAR(size) 定長字符數(shù)據(jù)定長字符數(shù)據(jù)NUMBER(p,s) 可變長數(shù)值數(shù)據(jù)可變長數(shù)值數(shù)據(jù)DATE 日期型數(shù)據(jù)日期型數(shù)據(jù)LONG 可變長字符數(shù)據(jù)，最大可達(dá)到可變長字符數(shù)據(jù)，最大可達(dá)到2GCLOB字符數(shù)據(jù)，最大可達(dá)到字符數(shù)據(jù)，最大可達(dá)到4GRAW and

8、LONG RAW 裸二進(jìn)制數(shù)據(jù)裸二進(jìn)制數(shù)據(jù)BLOB二進(jìn)制數(shù)據(jù)，最大可達(dá)到二進(jìn)制數(shù)據(jù)，最大可達(dá)到4GBFILE存儲外部文件的存儲外部文件的二進(jìn)制數(shù)據(jù)，最大可達(dá)到二進(jìn)制數(shù)據(jù)，最大可達(dá)到4GROWID行地址行地址9-12Copyright Oracle Corporation, 2001. All rights reserved.日期數(shù)據(jù)類型日期數(shù)據(jù)類型數(shù)據(jù)類型數(shù)據(jù)類型 描述描述TIMESTAMP時間撮時間撮INTERVAL YEAR TO MONTH若干年月若干年月INTERVAL DAY TO SECOND若干天到秒若干天到秒Oracle9i對日期的改進(jìn)對日期的改進(jìn):加入了新的日期型數(shù)據(jù)類型加入

9、了新的日期型數(shù)據(jù)類型.有效的存儲新數(shù)據(jù)類型有效的存儲新數(shù)據(jù)類型.提高對時區(qū)和本地時區(qū)的支持提高對時區(qū)和本地時區(qū)的支持.9-13Copyright Oracle Corporation, 2001. All rights reserved.日期數(shù)據(jù)類型日期數(shù)據(jù)類型TIMESTAMP 數(shù)據(jù)類型是對數(shù)據(jù)類型是對 DATE 數(shù)據(jù)類型的擴(kuò)展數(shù)據(jù)類型的擴(kuò)展按按DATE數(shù)據(jù)類型存放數(shù)據(jù)類型存放 年年, 月月, 日日, 小時小時, 分鐘分鐘, 秒秒 以及微以及微秒甚至納秒秒甚至納秒TIMESTAMP 數(shù)據(jù)類型的一般形式數(shù)據(jù)類型的一般形式:TIMESTAMP(fractional_seconds_precisi

10、on)9-14Copyright Oracle Corporation, 2001. All rights reserved.TIMESTAMP WITH TIME ZONETIMESTAMP WITH TIME ZONE 是一個帶有時區(qū)的是一個帶有時區(qū)的 TIMESTAMP 時區(qū)部分按照小時和分鐘顯示本地時區(qū)與時區(qū)部分按照小時和分鐘顯示本地時區(qū)與UTC的時差的時差TIMESTAMP(fractional_seconds_precision)WITH TIME ZONE9-15Copyright Oracle Corporation, 2001. All rights reserved.TIM

11、ESTAMP WITH LOCAL TIMETIMESTAMP WITH LOCAL TIME ZONE 是一種帶有本是一種帶有本地時區(qū)的地時區(qū)的 TIMESTAMP 數(shù)據(jù)庫按照數(shù)據(jù)庫的本地時區(qū)存放數(shù)據(jù)數(shù)據(jù)庫按照數(shù)據(jù)庫的本地時區(qū)存放數(shù)據(jù)時區(qū)不顯示在數(shù)據(jù)后面，時區(qū)不顯示在數(shù)據(jù)后面， Oracle 自動將數(shù)據(jù)轉(zhuǎn)換為用自動將數(shù)據(jù)轉(zhuǎn)換為用戶所在的時區(qū)戶所在的時區(qū)TIMESTAMP WITH LOCAL TIME ZONE 的一般形式的一般形式TIMESTAMP(fractional_seconds_precision)WITH LOCAL TIME ZONE9-16Copyright Oracle C

12、orporation, 2001. All rights reserved.INTERVAL YEAR TO MONTH 數(shù)據(jù)數(shù)據(jù)INTERVAL YEAR TO MONTH 存放若干年和若干月的一存放若干年和若干月的一個時間段。個時間段。INTERVAL YEAR (year_precision) TO MONTHINTERVAL 123-2 YEAR(3) TO MONTHIndicates an interval of 123 years, 2 months.INTERVAL 123 YEAR(3)Indicates an interval of 123 years 0 months.I

13、NTERVAL 300 MONTH(3)Indicates an interval of 300 months.INTERVAL 123 YEARReturns an error, because the default precision is 2, and 123 has 3 digits.9-17Copyright Oracle Corporation, 2001. All rights reserved.INTERVAL DAY TO SECOND 數(shù)據(jù)數(shù)據(jù)INTERVAL DAY TO SECOND 存放若干天到若干秒的存放若干天到若干秒的一個時間段一個時間段INTERVAL DAY

14、 (day_precision) TO SECOND (fractional_seconds_precision)INTERVAL 4 5:12:10.222 DAY TO SECOND(3)Indicates 4 days, 5 hours, 12 minutes, 10 seconds, and 222 thousandths of a second.INTERVAL 123 YEAR(3).INTERVAL 7 DAYIndicates 7 days.INTERVAL 180 DAY(3) Indicates 180 days.9-18Copyright Oracle Corporati

15、on, 2001. All rights reserved.INTERVAL DAY TO SECOND 數(shù)據(jù)數(shù)據(jù)INTERVAL DAY TO SECOND存放若干天到若干秒的一存放若干天到若干秒的一個時間段個時間段INTERVAL 4 5:12:10.222 DAY TO SECOND(3)Indicates 4 days, 5 hours, 12 minutes, 10 seconds, and 222 thousandths of a second.INTERVAL 4 5:12 DAY TO MINUTEIndicates 4 days, 5 hours and 12 minutes

16、.INTERVAL 400 5 DAY(3) TO HOURIndicates 400 days 5 hours.INTERVAL 11:12:10.2222222 HOUR TO SECOND(7)indicates 11 hours, 12 minutes, and 10.2222222 seconds.9-19Copyright Oracle Corporation, 2001. All rights reserved.使用子查詢創(chuàng)建表使用子查詢創(chuàng)建表時候用時候用 AS subquery 選項，將創(chuàng)建表和插入數(shù)據(jù)結(jié)合選項，將創(chuàng)建表和插入數(shù)據(jù)結(jié)合起來起來指定的列和子查詢中的列要一一對應(yīng)指定

17、的列和子查詢中的列要一一對應(yīng)通過列名和默認(rèn)值定義列通過列名和默認(rèn)值定義列CREATE TABLE table (column, column.)AS subquery;9-20Copyright Oracle Corporation, 2001. All rights reserved.使用子查詢創(chuàng)建表舉例使用子查詢創(chuàng)建表舉例DESCRIBE dept80CREATE TABLE dept80 AS SELECT employee_id, last_name, salary*12 ANNSAL, hire_date FROM employees WHERE department_id = 80

18、;9-21Copyright Oracle Corporation, 2001. All rights reserved.ALTER TABLE 語句語句使用使用 ALTER TABLE 語句可以語句可以:追加新的列追加新的列修改現(xiàn)有的列修改現(xiàn)有的列為新追加的列定義默認(rèn)值為新追加的列定義默認(rèn)值刪除一個列刪除一個列9-22Copyright Oracle Corporation, 2001. All rights reserved.ALTER TABLE 語句語句使用使用 ALTER TABLE 語句追加語句追加, 修改修改, 或或刪除列的語法刪除列的語法.ALTER TABLE tableAD

19、D (column datatype DEFAULT expr , column datatype.);ALTER TABLE tableMODIFY (column datatype DEFAULT expr , column datatype.);ALTER TABLE tableDROP (column);9-23Copyright Oracle Corporation, 2001. All rights reserved.追加一個新列追加一個新列DEPT80追加一個新列追加一個新列DEPT80新列新列9-24Copyright Oracle Corporation, 2001. All

20、 rights reserved.追加一個新列追加一個新列使用使用 ADD 子句追加一個新列子句追加一個新列新列是表中的最后一列新列是表中的最后一列ALTER TABLE dept80ADD (job_id VARCHAR2(9);9-25Copyright Oracle Corporation, 2001. All rights reserved.修改一個列修改一個列可以修改列的數(shù)據(jù)類型可以修改列的數(shù)據(jù)類型, 尺寸尺寸, 和默認(rèn)值和默認(rèn)值對默認(rèn)值的修改只影響今后對表的修改對默認(rèn)值的修改只影響今后對表的修改ALTER TABLEdept80MODIFY(last_name VARCHAR2(3

21、0);9-26Copyright Oracle Corporation, 2001. All rights reserved.刪除一個列刪除一個列使用使用 DROP COLUMN 子句刪除不再需要的列子句刪除不再需要的列.ALTER TABLE dept80DROP COLUMN job_id; 9-27Copyright Oracle Corporation, 2001. All rights reserved.ALTER TABLE tableSET UNUSED (column);ALTER TABLE tableSET UNUSED COLUMN column;SET UNUSED 選

22、項選項使用使用 SET UNUSED 使一個或多個列被標(biāo)記為不可用使一個或多個列被標(biāo)記為不可用使用使用 DROP UNUSED COLUMNS 選項刪除不可用的列選項刪除不可用的列ORALTER TABLE tableDROP UNUSED COLUMNS;9-28Copyright Oracle Corporation, 2001. All rights reserved.刪除表刪除表數(shù)據(jù)和結(jié)構(gòu)都被刪除數(shù)據(jù)和結(jié)構(gòu)都被刪除所有正在運行的相關(guān)事物被提交所有正在運行的相關(guān)事物被提交所有相關(guān)索引被刪除所有相關(guān)索引被刪除DROP TABLE 語句不能回滾語句不能回滾DROP TABLE dept80;9-29Copyright Oracle Corporation, 2001. All rights reserved.改變對象的名稱改變對象的名稱執(zhí)行執(zhí)行RENAME語句語句改變表改變表, 視圖視圖, 序列序列, 或同義詞的名稱或同義詞的名稱必須是對象的擁有者必須是對象的擁有者RENAME