4-Oracle數(shù)據(jù)定義語言(DDL)ppt課件

1、數(shù)據(jù)庫開發(fā)技術(shù)第4章 Oracle數(shù)據(jù)定義言語DDL福建工程學(xué)院. 本章要點(diǎn)創(chuàng)建和管理Oracle表約束Oracle 數(shù)據(jù)庫表的各種類型.4.1 創(chuàng)建和管理表 描畫主要的數(shù)據(jù)庫對(duì)象 表和列名的命名規(guī)那么 創(chuàng)建表 描畫可用于定義列的數(shù)據(jù)類型 修正表定義 刪除、更名、截?cái)啾?4.1.1 數(shù)據(jù)庫對(duì)象對(duì)象描畫表存儲(chǔ)數(shù)據(jù)的根本單元，由行和列組成視圖 來自一個(gè)或多個(gè)表的數(shù)據(jù)的子集序列 產(chǎn)生主關(guān)鍵字值索引提高某些查詢的性能的數(shù)據(jù)構(gòu)造同義詞 賦予對(duì)象另外的名字.4.1.2 命名規(guī)那么和商定(表和列名)必需以字母開頭長度為130 個(gè)字符只能包含 AZ, az, 09, _, $和 #對(duì)于同一個(gè)用戶不能運(yùn)用和其他

2、對(duì)象一樣的名字不能是Oracle的保管字注：1. 名字不區(qū)分大小寫，例如：EMP 、 eMP 或eMp指的是同一個(gè)表。 2. 在不同的表中命名一樣的實(shí)體采用堅(jiān)持一致的原那么，例如：部門編號(hào)在 EMP和 DEPT中都運(yùn)用一樣的名字DEPTNO。.4.1.3 CREATE TABLE語句您必需具備 :CREATE TABLE的權(quán)限一定的存貯空間您需求指定:表名列名、列的數(shù)據(jù)類型及列的寬度CREATE TABLE schema.table (column datatype DEFAULT expr;.4.1.3 創(chuàng)建表(DDL) SQL CREATE TABLE dept1 2(deptno NUM

3、BER(2), 3 dname VARCHAR2(14), 4 loc VARCHAR2(13);表已創(chuàng)建。創(chuàng)建表驗(yàn)證能否創(chuàng)建勝利(查看表構(gòu)造) SQL DESCRIBE dept1 Name Null? Type - - - DEPTNO NOT NULL NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13).4.1.3 創(chuàng)建表: DEFAULT 選項(xiàng)指定在插入數(shù)據(jù)時(shí)，列的默許值 hiredate DATE DEFAULT SYSDATE, 合法的值可以是字面值、表達(dá)式或SQL函數(shù)非法的值是另一個(gè)列的稱號(hào)或虛擬列默許值的數(shù)據(jù)類型必需和列的數(shù)據(jù)類型匹配.4

4、.1.4 援用其他用戶的表屬于其他用戶而不屬于當(dāng)前用戶的表需運(yùn)用用戶名作為表的前綴.4.1.5 Oracle的數(shù)據(jù)類型 以下是 Oracle 數(shù)據(jù)類型的類別：數(shù)據(jù)類型字符數(shù)值日期時(shí)間RAW/LONG RAWLOB.字符數(shù)據(jù)類型CHARVARCHAR2LONG當(dāng)需求固定長度的字符串時(shí)，運(yùn)用 CHAR 數(shù)據(jù)類型。CHAR 數(shù)據(jù)類型存儲(chǔ)字母數(shù)字值。CHAR 數(shù)據(jù)類型的列長度可以是 1 到 2000 個(gè)字節(jié)。VARCHAR2數(shù)據(jù)類型支持可變長度字符串VARCHAR2數(shù)據(jù)類型存儲(chǔ)字母數(shù)字值VARCHAR2數(shù)據(jù)類型的大小在1至4000個(gè)字節(jié)范圍內(nèi)LONG 數(shù)據(jù)類型存儲(chǔ)可變長度字符數(shù)據(jù)LONG 數(shù)據(jù)類型最多

5、能存儲(chǔ) 2GB4.1.5 Oracle的數(shù)據(jù)類型 .數(shù)值數(shù)據(jù)類型可以存儲(chǔ)整數(shù)、浮點(diǎn)數(shù)和實(shí)數(shù)最高精度為 38 位數(shù)值數(shù)據(jù)類型的聲明語法：NUMBER ( p, s)P表示精度，S表示小數(shù)點(diǎn)的位數(shù)日期時(shí)間數(shù)據(jù)類型存儲(chǔ)日期和時(shí)間值，包括年、月、日，小時(shí)、分鐘、秒主要的日期時(shí)間類型有：DATE - 存儲(chǔ)日期和時(shí)間部分，準(zhǔn)確到整個(gè)的秒TIMESTAMP - 存儲(chǔ)日期、時(shí)間和時(shí)區(qū)信息，秒值準(zhǔn)確到小數(shù)點(diǎn)后6位4.1.5 Oracle的數(shù)據(jù)類型 .RAW 數(shù)據(jù)類型用于存儲(chǔ)二進(jìn)制數(shù)據(jù)RAW 數(shù)據(jù)類型最多能存儲(chǔ) 2000 字節(jié)LONG RAW 數(shù)據(jù)類型用于存儲(chǔ)可變長度的二進(jìn)制數(shù)據(jù)LONG RAW 數(shù)據(jù)類型最多能存

6、儲(chǔ) 2 GBLOBLOB 稱為“大對(duì)象數(shù)據(jù)類型，可以存儲(chǔ)多達(dá) 4GB 的非構(gòu)造化信息，例如聲音剪輯和視頻文件等LOB 數(shù)據(jù)類型允許對(duì)數(shù)據(jù)進(jìn)展高效、隨機(jī)、分段的訪問BLOBCLOBBFILECLOB 即 Character LOB字符 LOB，它可以存儲(chǔ)大量字符數(shù)據(jù)BLOB 即 Binary LOB二進(jìn)制 LOB，可以存儲(chǔ)較大的二進(jìn)制對(duì)象，如圖形、視頻剪輯和聲音文件 BFILE 即 Binary File二進(jìn)制文件，它用于將二進(jìn)制數(shù)據(jù)存儲(chǔ)在數(shù)據(jù)庫外部的操作系統(tǒng)文件中4.1.5 Oracle的數(shù)據(jù)類型 .4.1.5 數(shù)據(jù)類型 P50類型描畫VARCHAR2(size)變長字符串(字符族):1400

7、0個(gè)字符CHAR(size) 固定長度字符串(字符族) ):12000個(gè)字符NUMBER(p,s) 定點(diǎn)小數(shù)值 , p為精度, s為標(biāo)度(數(shù)字族)DATE 日期和時(shí)間值(日期族)LONG 可存放高達(dá)2GB的變長字符數(shù)據(jù)(字符族)CLOB 字符大對(duì)象可存放高達(dá)4GB的變長單字節(jié)字符數(shù)據(jù)RAW and LONG RAW 原始二進(jìn)制數(shù)據(jù)BLOB 二進(jìn)制大對(duì)象可存放高達(dá)4GB二進(jìn)制數(shù)據(jù)BFILE援用一個(gè)外部的二進(jìn)制文件;可存放高達(dá) 4GB數(shù)據(jù) P50 表3-1.4.1.6 查詢數(shù)據(jù)字典列出當(dāng)前用戶的表 列出當(dāng)前用戶不同的對(duì)象類型查詢當(dāng)前用戶的表、視圖、同義詞及序列SQL SELECTtable_nam

8、e 2FROMuser_tables;SQL SELECTDISTINCT object_type 2FROM user_objects;SQL SELECT* 2FROMuser_catalog;.4.1.7 用子查詢創(chuàng)建表 -利用現(xiàn)有的表創(chuàng)建表在 CREATE TABLE 語句的AS子句中運(yùn)用子查詢可以在創(chuàng)建表的同時(shí)插入數(shù)據(jù)所指定列的數(shù)量和類型必需和子查詢中列的數(shù)量和類型相匹配由列名和默許值定義列CREATE TABLE table column(, column.)AS subquery;.4.1.7 運(yùn)用子查詢創(chuàng)建表 Name Null? Type - - - EMPNO NOT NU

9、LL NUMBER(4) ENAME VARCHAR2(10) ANNSAL NUMBER HIREDATE DATESQL DESCRIBE dept30SQL CREATE TABLE dept30 2AS 3SELECT empno, ename, sal*12 ANNSAL, hiredate 4FROM emp 5WHERE deptno = 30;表已創(chuàng)建。.4.1.8 ALTER TABLE 語句運(yùn)用 ALTER TABLE 語句可以：添加或刪除一個(gè)列修正已存在的列為新列定義默許值A(chǔ)LTER TABLE tableADD (column datatype DEFAULT expr

10、 , column datatype.);ALTER TABLE tableMODIFY (column datatype DEFAULT expr , column datatype.);.添加新列 DEPT30 EMPNO ENAME ANNSAL HIREDATE - - 7698BLAKE 3420001-MAY-81 7654MARTIN 1500028-SEP-81 7499ALLEN 1920020-FEB-81 7844TURNER 1800008-SEP-81.“在DEPT30 表中添加新列JOB DEPT30 EMPNO ENAME ANNSAL HIREDATE - -

11、7698BLAKE 3420001-MAY-81 7654MARTIN 1500028-SEP-81 7499ALLEN 1920020-FEB-81 7844TURNER 1800008-SEP-81. JOB JOB新列.添加新列運(yùn)用 ADD 子句添加新列 columns. EMPNO ENAME ANNSAL HIREDATE JOB- - - - - 7698 BLAKE 34200 01-MAY-81 7654 MARTIN 15000 28-SEP-81 7499 ALLEN 19200 20-FEB-81 7844 TURNER 18000 08-SEP-81.6 rows se

12、lected.SQL ALTER TABLE dept30 2 ADD (job VARCHAR2(9);表已更改。新列成為表的最后一列.修正列可以修正列的數(shù)據(jù)類型、長度及默許值改動(dòng)列的默許值僅僅影響后來插入的新記錄ALTER TABLEdept30MODIFY(ename VARCHAR2(15); -原來是VARCHAR2(10)表已更改。.刪除一列(Oracle8i以上版本)從數(shù)據(jù)表中刪除一列至少還必需剩下一列ALTER TABLEdept30DROP COLUMN ename; -一次只允許刪除一列表已更改。.4.1.9 刪除表表中的一切數(shù)據(jù)和構(gòu)造都被刪除任何與該表相關(guān)的視圖或同義詞依

13、然存在，但是卻是不可用的任何懸而未決的事務(wù)都將被提交一切的索引被刪除.不能回滾該語句只需表的創(chuàng)建者或具有DROP ANY TABLE權(quán)限的用戶才干刪除表SQL DROP TABLE dept30;表已刪除。.4.1.10 修正對(duì)象的稱號(hào)要修正表、視圖、序列或同義詞的稱號(hào)，可以運(yùn)用RENAME 語句您必需擁有對(duì)象的一切權(quán).SQL RENAME dept1 TO department;表已重命名。RENAME old_name TO new_name;.4.1.11 截?cái)啾鞹RUNCATE TABLE 語句：刪除表中一切記錄釋放該表的存儲(chǔ)空間運(yùn)用TRUNCATE移除后不能回滾DELETE語句同樣可

14、以刪除表中記錄，但是它并不釋放存儲(chǔ)空間。SQL TRUNCATE TABLE department;表被截?cái)唷?4.1.12 給表或列添加注釋運(yùn)用COMMENT語句，可以為表或列添加注釋(不超越2000字節(jié))注釋可以經(jīng)過以下字典表查詢ALL_COL_COMMENTSUSER_COL_COMMENTSALL_TAB_COMMENTSUSER_TAB_COMMENTSSQL COMMENT ON TABLE emp 2 IS Employee Information;注釋已創(chuàng)建。.4.2 約束描畫主要的約束類型建立表的同時(shí)創(chuàng)建約束對(duì)已存在的表添加、刪除約束禁用、啟用約束.什么是約束阻止無效數(shù)據(jù)進(jìn)入

15、表中約束在表一級(jí)實(shí)施強(qiáng)迫規(guī)那么約束可以阻止刪除和其它表具有相關(guān)性的表以下是Oracle中有效的約束類型：NOT NULLUNIQUE KeyCHECKPRIMARY KEYFOREIGN KEY值約束完好性約束.約束準(zhǔn)那么給約束命名或者由Oreacle效力器產(chǎn)生一個(gè)方式為SYS_Cn的稱號(hào)創(chuàng)建約束的方式:創(chuàng)建表的同時(shí)便創(chuàng)建約束表創(chuàng)建以后再創(chuàng)建約束在列或者表一級(jí)定義約束在數(shù)據(jù)字典中可以查詢約束.命名約束對(duì)于一個(gè)帳號(hào)，每個(gè)約束名必需是獨(dú)一的命名約束的商定格式:_如：emp_deptno_fk約束縮寫PRIMARY KEYpkFOREIGN KEYfkUNIQUEukCHECKck 或 ccNOT

16、NULLnn圖 普遍采用的約束的縮寫表示.定義約束CREATE TABLE schema.table (column datatype DEFAULT exprcolumn_constraint,table_constraint);CREATE TABLE emp( empno NUMBER(4), ename VARCHAR2(10), deptno NUMBER(7,2) NOT NULL, CONSTRAINT emp_empno_pk PRIMARY KEY (EMPNO);.定義約束列約束級(jí)表約束級(jí)column CONSTRAINT constraint_name constrain

17、t_type,column,. CONSTRAINT constraint_name constraint_type (column, .),.NOT NULL約束確保該列不允許存放空值 EMP EMPNO ENAME JOB . COMM DEPTNO 7839KINGPRESIDENT 10 7698BLAKEMANAGER 30 7782CLARKMANAGER 10 7566JONESMANAGER 20 .NOT NULL 約束(該列沒有記錄為NUILL)沒有 NOT NULL約束(任何一個(gè)記錄該列可以為空值)NOT NULL 約束.NOT NULL 約束在列一級(jí)上定義約束SQL C

18、REATE TABLE employee( 2 empno NUMBER(4), 3enameVARCHAR2(10) NOT NULL, 4jobVARCHAR2(9), 5mgrNUMBER(4), 6hiredateDATE, 7salNUMBER(7,2), 8 commNUMBER(7,2), 9deptnoNUMBER(7,2) NOT NULL);.UNIQUE 約束 DEPT DEPTNO DNAME LOC - - 10ACCOUNTINGNEW YORK 20RESEARCHDALLAS 30SALESCHICAGO 40OPERATIONSBOSTONUNIQUE 約束

19、50SALESDETROIT 60BOSTONInsert into不允許插入 (SALES 曾經(jīng)存在)允許插入.UNIQUE 約束可以定義在表或列一級(jí)SQL CREATE TABLE dept( 2 deptno NUMBER(2), 3dname VARCHAR2(14), 4loc VARCHAR2(13), 5CONSTRAINT dept_dname_uk UNIQUE(dname);注: Oracle效力器是經(jīng)過隱含地在獨(dú)一關(guān)鍵字上創(chuàng)建獨(dú)一索引的方式實(shí)施UNIQUE約束的。.PRIMARY KEY 約束 DEPT DEPTNO DNAME LOC - - 10ACCOUNTINGN

20、EW YORK 20RESEARCHDALLAS 30SALESCHICAGO 40OPERATIONSBOSTONPRIMARY KEYInsert into 20MARKETINGDALLAS FINANCENEW YORK不允許 (DEPTNO-20 曾經(jīng)存在)不允許(DEPTNO 不允許為空).PRIMARY KEY 約束可以定義在表或列一級(jí)SQL CREATE TABLE dept( 2 deptno NUMBER(2), 3dname VARCHAR2(14), 4loc VARCHAR2(13), 5CONSTRAINT dept_dname_uk UNIQUE (dname),

21、 6CONSTRAINT dept_deptno_pk PRIMARY KEY(deptno);注: Oracle效力器會(huì)自動(dòng)為主關(guān)鍵字列創(chuàng)建一個(gè)獨(dú)一索引。.FOREIGN KEY 約束 DEPT DEPTNO DNAME LOC - - 10ACCOUNTINGNEW YORK 20RESEARCHDALLAS .PRIMARYKEY EMP EMPNO ENAME JOB . COMM DEPTNO 7839KINGPRESIDENT 10 7698BLAKEMANAGER 30 .FOREIGNKEY 7571FORDMANAGER . 200 9 7571FORDMANAGER . 2

22、00Insert into不允許插入(DEPTNO-9 在DEPT表中不存在)允許.FOREIGN KEY 約束可以定義在表或列一級(jí)SQL CREATE TABLE employee( 2 empno NUMBER(4), 3enameVARCHAR2(10) NOT NULL, 4jobVARCHAR2(9), 5mgrNUMBER(4), 6hiredateDATE, 7salNUMBER(7,2), 8 commNUMBER(7,2), 9deptnoNUMBER(7,2) NOT NULL, 10CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno)

23、 11REFERENCES dept (deptno);.FOREIGN KEY 約束可以定義在表或列一級(jí)SQL CREATE TABLE employee( 2 empno NUMBER(4), 3enameVARCHAR2(10) NOT NULL, 4jobVARCHAR2(9), 5mgrNUMBER(4), 6hiredateDATE, 7salNUMBER(7,2), 8 commNUMBER(7,2), 9deptnoNUMBER(7,2) 10 constraint emp_deptno_fk references dept(deptno);.FOREIGN KEY 約束中的關(guān)

24、鍵字FOREIGN KEY在表或列一級(jí)上定義外鍵REFERENCES標(biāo)識(shí)父表及相應(yīng)的列ON DELETE CASCADE假設(shè)刪除父表中的記錄那么允許級(jí)聯(lián)刪除子表中相關(guān)連的記錄或用ON DELETE SET NULL.CHECK約束定義每條記錄都必需滿足的條件表達(dá)式不允許運(yùn)用:對(duì)偽列的援用，例如： CURRVAL, NEXTVAL, LEVEL及 ROWNUM對(duì)其它記錄其它值的查詢可以調(diào)用SYSDATE, UID, USER及 USERENV 函數(shù)., deptnoNUMBER(2), CONSTRAINT emp_deptno_ck CHECK (DEPTNO BETWEEN 10 AND 9

25、9),.添加約束添加、刪除但不是修正約束啟用一個(gè)曾經(jīng)禁用的約束運(yùn)用MODIFY子句添加NOT NULL約束 ALTER TABLE table ADD CONSTRAINT constraint type (column);.添加約束為EMP表添加一個(gè) FOREIGN KEY約束，指定雇員的管理員編號(hào)必需是在EMP表中曾經(jīng)存在的雇員的雇員編號(hào)。SQL ALTER TABLE emp 2 ADD CONSTRAINT emp_mgr_fk 3 FOREIGN KEY(mgr) REFERENCES emp(empno);表已更改。.刪除約束從 EMP表刪除管理員約束SQL ALTER TABLE

26、 emp 2 DROP CONSTRAINT emp_mgr_fk;表已更改。從DEPT表刪除主鍵約束PRIMARY KEY，同時(shí)刪除也刪除了與該約束關(guān)聯(lián)的EMP表DEPTNO上的外鍵 FOREIGN KEY約束。SQL ALTER TABLEdept 2 DROP PRIMARY KEY CASCADE;表已更改。.禁用約束在ALTER TABLE 語句中運(yùn)用DISABLE子句可以使約束失效運(yùn)用CASCADE 選項(xiàng)可以使與該約束是從屬關(guān)系的約束同時(shí)失效SQL ALTER TABLEdeptment 2 DISABLE CONSTRAINTdept_deptno_pk CASCADE;表已更改。.啟用約束運(yùn)用ENABLE子句可以激活曾經(jīng)禁用的約束假設(shè)您啟用獨(dú)一UNIQUE或主鍵PRIMARY KEY約束，獨(dú)一 UNIQUE或主鍵 PRIMARY KEY索引便自動(dòng)創(chuàng)建SQL ALTER TABLEdeptment 2 ENABLE CONSTRAINTdept_deptno_pk;表