第4章 數(shù)據(jù)庫(kù)常用對(duì)象對(duì)象

1、1第4章 數(shù)據(jù)庫(kù)對(duì)象 從數(shù)據(jù)庫(kù)用戶的角度看，數(shù)據(jù)庫(kù)中的數(shù)據(jù)是以表、視圖等方式存儲(chǔ)的。此外，用戶還可以通過為表創(chuàng)建索引，以提高查詢的執(zhí)行速度。在Oracle系統(tǒng)中，對(duì)表、視圖和索引的創(chuàng)建、維護(hù)是通過模式實(shí)現(xiàn)的。在本章中，我們將初步介紹Oracle中的模式概念，并重點(diǎn)介紹如何在模式中創(chuàng)建基本對(duì)象基本表、索引以及其他數(shù)據(jù)庫(kù)常用對(duì)象2本章學(xué)習(xí)目標(biāo)： 理解什么是模式 了解模式對(duì)象與非模式對(duì)象之間的區(qū)別 初步掌握如何創(chuàng)建用戶賬戶，并進(jìn)行基本的管理 了解在創(chuàng)建表時(shí)Oracle允許使用的數(shù)據(jù)類型 掌握如何在SQL*Plus環(huán)境中創(chuàng)建、修改基本的表 了解其他索引的的創(chuàng)建和管理 了解索引組織表、臨時(shí)表、視圖、同義

2、詞、序列的創(chuàng)建和基本使用34.1.1 用戶和模式 l 在Oracle中，用戶不能直接操作物理結(jié)構(gòu)。用戶可以直接操作的是表、索引和視圖這樣的邏輯對(duì)象。l 在Oracle數(shù)據(jù)庫(kù)中，邏輯對(duì)象不是隨意保存在數(shù)據(jù)庫(kù)中的，Oracle是通過“模式”來組織和管理這些數(shù)據(jù)庫(kù)對(duì)象。 l 一個(gè)模式下對(duì)象不能重名，但是兩個(gè)不同模式下可以存在同名的對(duì)象。一個(gè)模式對(duì)象可能被存于多個(gè)數(shù)據(jù)文件中（因?yàn)榇嬗谀骋粋€(gè)表空間，而表空間包含多個(gè)數(shù)據(jù)文件）概述 Oracle模式（方案）是邏輯結(jié)構(gòu)與對(duì)象的集合 模式包含相關(guān)的數(shù)據(jù)庫(kù)對(duì)象，與物理存儲(chǔ)無關(guān)。 一個(gè)模式對(duì)應(yīng)一個(gè)數(shù)據(jù)庫(kù)用戶，且跟用戶同名。用戶可以創(chuàng)建模式對(duì)象，用于包含或引用數(shù)據(jù)。

3、 模式對(duì)象的創(chuàng)建： OEM工具 語(yǔ)句對(duì)象對(duì)象名稱名稱作用作用TABLETABLE表表用于存儲(chǔ)數(shù)據(jù)的基本結(jié)構(gòu)用于存儲(chǔ)數(shù)據(jù)的基本結(jié)構(gòu)VIEWVIEW視圖視圖以不同的側(cè)面反映表的數(shù)據(jù)，是一種邏以不同的側(cè)面反映表的數(shù)據(jù)，是一種邏輯上的表輯上的表INDEXINDEX索引索引加快表的查詢速度加快表的查詢速度CLUSTERCLUSTER聚簇聚簇將不同表的字段并用的一種特殊結(jié)構(gòu)的將不同表的字段并用的一種特殊結(jié)構(gòu)的表集合表集合SEQUENCESEQUENCE序列序列生成數(shù)字序列，用于在插入時(shí)自動(dòng)填充生成數(shù)字序列，用于在插入時(shí)自動(dòng)填充表的字段表的字段SYNONYMSYNONYM同義詞同義詞為簡(jiǎn)化和便于記憶，給對(duì)象

4、起的別名為簡(jiǎn)化和便于記憶，給對(duì)象起的別名DATABASELINKDATABASELINK數(shù)據(jù)庫(kù)鏈接數(shù)據(jù)庫(kù)鏈接為訪問遠(yuǎn)程對(duì)象創(chuàng)建的通道為訪問遠(yuǎn)程對(duì)象創(chuàng)建的通道STOREDPROCEDURESTOREDPROCEDURE、FUNCTION存儲(chǔ)過程和函數(shù)存儲(chǔ)過程和函數(shù)存儲(chǔ)于數(shù)據(jù)庫(kù)中的可調(diào)用的程序和函數(shù)存儲(chǔ)于數(shù)據(jù)庫(kù)中的可調(diào)用的程序和函數(shù)PACKAGEPACKAGE、PACKAGEBODY包和包體包和包體將存儲(chǔ)過程、函數(shù)及變量按功能和類別將存儲(chǔ)過程、函數(shù)及變量按功能和類別進(jìn)行捆綁進(jìn)行捆綁TRIGGERTRIGGER觸發(fā)器觸發(fā)器由由DML操作或數(shù)據(jù)庫(kù)事件觸發(fā)的事件操作或數(shù)據(jù)庫(kù)事件觸發(fā)的事件處理程序處理程序

5、表表1 Oracle數(shù)據(jù)庫(kù)模式對(duì)象數(shù)據(jù)庫(kù)模式對(duì)象Oracle非模式對(duì)象 表空間 用戶 角色 模式和用戶一一對(duì)應(yīng)關(guān)系，模式是對(duì)象集合，用戶可以管理創(chuàng)建模式對(duì)象，有時(shí)可以替換使用，對(duì)應(yīng)的名稱是一致的。但概念是完全不同的。比如創(chuàng)建表時(shí)可以指定創(chuàng)建到某個(gè)模式，也可以說成用戶。4.1.2 用戶 用戶的創(chuàng)建和授權(quán)（必須具有connect或create session權(quán)限） 創(chuàng)建表必須有create table和unlimited tablespace權(quán)限，resource權(quán)限比較大，包含了創(chuàng)建表等的權(quán)限。 默認(rèn)用戶創(chuàng)建對(duì)應(yīng)的用戶表空間是users，除非顯式指定，臨時(shí)表空間temp 還可以給用戶指定使用的空

6、間配額 quota關(guān)鍵字 賬戶的鎖定和解除鎖定方法 刪除用戶，如果用戶中有對(duì)象必須指定cascade選項(xiàng)84.2 表 在Oracle系統(tǒng)中，表是主要的模式對(duì)象，也是基本的數(shù)據(jù)存儲(chǔ)結(jié)構(gòu)。一個(gè)表通常由多個(gè)列(字段)組成，并且每個(gè)列都具有名稱，表中的每一行存放一條信息。 4.2.1 創(chuàng)建表 用戶必須具備 : CREATE TABLE的權(quán)限 一定的存貯空間 需要指定: 表名 列名、列的類型及列的寬度CREATE TABLE schema.table (column datatype DEFAULT expr;命名規(guī)則 必須以字母開頭 長(zhǎng)度為130 個(gè)字符 只能包含 AZ, az, 09, _, $和

7、# 對(duì)于同一個(gè)用戶不能使用和其他對(duì)象相同的名字 不能是Oracle的保留字DEFAULT 選項(xiàng)l指定在插入數(shù)據(jù)時(shí)，列的默認(rèn)值 hiredate DATE DEFAULT SYSDATE, l合法的值可以是字面值、表達(dá)式或SQL函數(shù)l非法的值是另一個(gè)列的名稱或虛擬列l(wèi)默認(rèn)的數(shù)據(jù)類型必須和列的類型匹配數(shù)據(jù)類型類型類型描述描述VARCHAR2(size)變長(zhǎng)字符串變長(zhǎng)字符串CHAR(size) 固定長(zhǎng)度字符串固定長(zhǎng)度字符串NUMBER(p,s) 變長(zhǎng)數(shù)值變長(zhǎng)數(shù)值DATE，timestamp 日期和時(shí)間值，日期和時(shí)間值，date用戶轉(zhuǎn)換用戶轉(zhuǎn)換LONG 可存放高達(dá)可存放高達(dá)2GB的的變長(zhǎng)字符數(shù)據(jù)變長(zhǎng)字

8、符數(shù)據(jù)CLOB可存放高達(dá)可存放高達(dá)4GB的變長(zhǎng)單字節(jié)數(shù)據(jù)的變長(zhǎng)單字節(jié)數(shù)據(jù)RAW and LONG RAW 二進(jìn)制數(shù)據(jù)二進(jìn)制數(shù)據(jù)BLOB可存放高達(dá)可存放高達(dá)4GB二進(jìn)制數(shù)據(jù)二進(jìn)制數(shù)據(jù)BFILEBinary data stored in an external file; up to 4 gigabytes建議使用固定長(zhǎng)度類型，nchar表示uncode類型，一個(gè)字符占兩個(gè)字節(jié)位置，rowid和rownum的使用。創(chuàng)建表 SQL CREATE TABLE dept1 2(deptno NUMBER(2), 3 dname VARCHAR2(14), 4 loc VARCHAR2(13);l 創(chuàng)建表

9、l驗(yàn)證是否創(chuàng)建成功 SQL DESCRIBE dept1 Name Null? Type - - - DEPTNO NOT NULL NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13)表特性 Tablespace子句指定表創(chuàng)建在哪個(gè)表空間，可以用user_tables查看表空間 Storage子句，指定表如何使用存儲(chǔ)空間，可以根據(jù)不同表進(jìn)行設(shè)置 設(shè)置數(shù)據(jù)塊子句，主要減少行遷移 重做子句，指定對(duì)表的操作是否記錄到日志 Cache子句可以指定將某個(gè)常用的表數(shù)據(jù)常駐內(nèi)存的數(shù)據(jù)庫(kù)高速緩存中。查詢數(shù)據(jù)字典l 列出當(dāng)前用戶的表 l列出當(dāng)前用戶不同的對(duì)象類型l查詢當(dāng)前

10、用戶的表、視圖、同義詞及序列SQL SELECTtable_name 2FROMuser_tables;SQL SELECTDISTINCT object_type 2FROM user_objects;SQL SELECT* 2FROMuser_catalog;用子查詢創(chuàng)建表 在 CREATE TABLE 語(yǔ)句的AS子句中使用子查詢可以在創(chuàng)建表的同時(shí)插入數(shù)據(jù) 所指定列的數(shù)量和類型必須和子查詢中列的數(shù)量和類型相匹配 由列名和默認(rèn)值定義列CREATE TABLE table column(, column.)AS subquery;使用子查詢創(chuàng)建表 Name Null? Type - - - E

11、MPNO NOT NULL 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;184.2.2 修改表 在表被創(chuàng)建之后，如果發(fā)現(xiàn)對(duì)表的定義有不滿意的地方，還可以對(duì)表進(jìn)行修改操作。對(duì)表進(jìn)行修改操作包括：增加或刪除表中的字段、改變表的存儲(chǔ)參數(shù)設(shè)置，以及對(duì)表進(jìn)行增加、刪減和重命名等操作。同樣對(duì)表的修改

12、即可以通過在SQL*Plus中使用ALTER TABLE語(yǔ)句實(shí)現(xiàn)，也可以通過OEM圖形化工具實(shí)現(xiàn)。 ALTER TABLE 語(yǔ)句 使用 ALTER TABLE 語(yǔ)句可以： 添加一個(gè)列 修改已存在的列 為新列定義默認(rèn)值A(chǔ)LTER TABLE tableADD (column datatype DEFAULT expr , column datatype.);ALTER TABLE tableMODIFY (column datatype DEFAULT expr , column datatype.);添加新列 EMPNO ENAME ANNSAL HIREDATE - - - 7698 BLA

13、KE 3420001-MAY-81 7654 MARTIN 1500028-SEP-81 7499 ALLEN 1920020-FEB-81 7844 TURNER 1800008-SEP-81. EMPNO ENAME ANNSAL HIREDATE - - - 7698 BLAKE 3420001-MAY-81 7654 MARTIN 1500028-SEP-81 7499 ALLEN 1920020-FEB-81 7844 TURNER 1800008-SEP-81. JOB JOB添加新列 使用 ADD 子句添加新列 columns. EMPNO ENAME ANNSAL HIREDA

14、TE 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 selected.SQL ALTER TABLE dept30 2 ADD (job VARCHAR2(9);l新列成為表的最后一列修改列 可以修改列的數(shù)據(jù)類型、長(zhǎng)度及默認(rèn)值 改變列的默認(rèn)值僅僅影響后來插入的新記錄 刪除列,alter table t_name drop column col_n（刪除一個(gè)列的話必須帶column關(guān)鍵

15、字，如果兩個(gè)以上的列不能帶關(guān)鍵字，并且用小括號(hào)括起來）ALTER TABLEdept30MODIFY(ename VARCHAR2(15); 設(shè)置列為無用狀態(tài) Alter table emp set unused(job,sal) 數(shù)據(jù)字典user_unused_col_tabs,all_unused_col_tabs Alter table emp drop unused column 調(diào)整表特性 Move子句 手動(dòng)給表分配存儲(chǔ)空間 回收無用表空間244.2.3 刪除表定義 當(dāng)刪除表定義時(shí)，需要注意用戶一般情況下只能刪除自己模式中的表，如果用戶要?jiǎng)h除其他模式中的表，則用戶必須具有DROP A

16、NY TABLE系統(tǒng)權(quán)限。要?jiǎng)h除不再需要的表，可以使用DROP TABLE語(yǔ)句。如下面的語(yǔ)句刪除了表EMPLOYEE。 刪除表 表中的所有數(shù)據(jù)和結(jié)構(gòu)都被刪除 任何懸而未決的事務(wù)都將被提交 所有的索引被刪除. 不能回滾該語(yǔ)句SQL DROP TABLE dept30;4.2.4 修改對(duì)象的名稱 要修改表、視圖、序列或同義詞的名稱，可以使用RENAME 語(yǔ)句 必須擁有對(duì)象的所有權(quán).SQL RENAME dept1 TO department;4.2.5 截?cái)啾?TRUNCATE TABLE 語(yǔ)句： 刪除表中所有記錄 釋放該表的存儲(chǔ)空間 使用TRUNCATE移除后不能回滾 換一種方法可以使用DELE

17、TE語(yǔ)句刪除記錄SQL TRUNCATE TABLE department; 4.2.6 給表添加注釋 使用COMMENT語(yǔ)句，可以為表或列添加注釋 注釋可以通過以下字典表查詢 ALL_COL_COMMENTS USER_COL_COMMENTS ALL_TAB_COMMENTS USER_TAB_COMMENTSSQL COMMENT ON TABLE emp 2 IS Employee Information; 4.3 數(shù)據(jù)完整性（約束） 數(shù)據(jù)庫(kù)不僅僅是存儲(chǔ)數(shù)據(jù)，它也必須保證所保存的數(shù)據(jù)的正確性。如果數(shù)據(jù)不準(zhǔn)確或不一致，那么該數(shù)據(jù)的完整性可能會(huì)受到破壞，從而給數(shù)據(jù)庫(kù)本身的可靠性帶來問題。為

18、了維護(hù)數(shù)據(jù)庫(kù)中數(shù)據(jù)的完整性，在創(chuàng)建表時(shí)常常需要定義一些約束。通過為表中的字段定義約束，可以防止將錯(cuò)誤的數(shù)據(jù)插入到表中。對(duì)約束的定義即可以在CREATE TABLE語(yǔ)句中進(jìn)行，也可以在ALTER TABLE語(yǔ)句中進(jìn)行。 4.3.1 什么是約束 約束在表一級(jí)實(shí)施相應(yīng)規(guī)則 約束可以阻止刪除和其它表具有相關(guān)性的表 以下是Oracle中有效的約束類型： NOT NULL UNIQUE Key PRIMARY KEY FOREIGN KEY CHECK約束準(zhǔn)則 給約束命名或者由Oreacle服務(wù)器產(chǎn)生一個(gè)形式為SYS_Cn的名稱 創(chuàng)建約束: 創(chuàng)建表的同時(shí)便創(chuàng)建約束 表創(chuàng)建以后再創(chuàng)建約束 在列或者表一級(jí)定義

19、約束 在數(shù)據(jù)字典中可以查詢約束4.3.2 定義約束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 constrai

20、nt_type,column,. CONSTRAINT constraint_name constraint_type (column, .),NOT NULL約束 確保該列不允許存放空值 EMPNO ENAME JOB . COMM DEPTNO 7839 KINGPRESIDENT 10 7698 BLAKEMANAGER 30 7782 CLARKMANAGER 10 7566 JONESMANAGER 20 .NOT NULL 約束 在列一級(jí)上定義約束SQL CREATE TABLE employee( 2 empno NUMBER(4), 3enameVARCHAR2(10) NOT

21、 NULL, 4jobVARCHAR2(9), 5mgrNUMBER(4), 6hiredate DATE, 7salNUMBER(7,2), 8 commNUMBER(7,2), 9deptnoNUMBER(7,2) NOT NULL);UNIQUE 約束 DEPTNO DNAME LOC - - - 10 ACCOUNTING NEW YORK 20 RESEARCHDALLAS 30 SALESCHICAGO 40 OPERATIONS BOSTON 50 SALESDETROIT 60 BOSTONSALESUNIQUE 約束 可以定義在表或列一級(jí)SQL CREATE TABLE de

22、ptment( 2 deptno NUMBER(2), 3dname VARCHAR2(14), 4loc VARCHAR2(13), 5CONSTRAINT dept_dname_uk UNIQUE(dname);PRIMARY KEY 約束 DEPTNO DNAME LOC - - - 10 ACCOUNTING NEW YORK 20 RESEARCHDALLAS 30 SALESCHICAGO 40 OPERATIONS BOSTON 20 MARKETINGDALLAS FINANCENEW YORK-PRIMARY KEY 約束 可以定義在表或列一級(jí)SQL CREATE TABLE

23、 deptment( 2 deptno NUMBER(2), 3dname VARCHAR2(14), 4loc VARCHAR2(13), 5CONSTRAINT dept_dname_uk UNIQUE (dname), 6CONSTRAINT dept_deptno_pk PRIMARY KEY(deptno);FOREIGN KEY 約束 DEPTNO DNAME LOC - - - 10 ACCOUNTING NEW YORK 20 RESEARCHDALLAS . EMPNO ENAME JOB . COMM DEPTNO 7839 KINGPRESIDENT 10 7698 BL

24、AKEMANAGER 30 . 7571 FORDMANAGER . 200 9 7571 FORDMANAGER . 200- -FOREIGN KEY 約束 可以定義在表或列一級(jí)SQL CREATE TABLE employee( 2 empno NUMBER(4), 3enameVARCHAR2(10) NOT NULL, 4jobVARCHAR2(9), 5mgrNUMBER(4), 6hiredate DATE, 7salNUMBER(7,2), 8 commNUMBER(7,2), 9deptnoNUMBER(7,2) NOT NULL, 10CONSTRAINT emp_dept

25、no_fk FOREIGN KEY (deptno) 11REFERENCES dept (deptno);FOREIGN KEY 約束中的關(guān)鍵字 FOREIGN KEY在表或列一級(jí)上定義外鍵 REFERENCES標(biāo)識(shí)父表及相應(yīng)的列 ON DELETE CASCADE如果刪除父表中的記錄則允許級(jí)聯(lián)刪除子表中相關(guān)連的記錄CHECK約束 定義每條記錄都必須滿足的條件 表達(dá)式不允許使用: 對(duì)偽列的引用，例如： CURRVAL, NEXTVAL, LEVEL及 ROWNUM 可以調(diào)用SYSDATE, UID, USER及 USERENV 函數(shù) 對(duì)其它記錄其它值的查詢., deptnoNUMBER(2)

26、, CONSTRAINT emp_deptno_ck CHECK (DEPTNO BETWEEN 10 AND 99),.添加約束 添加、刪除但不是刪除約束 啟用一個(gè)已經(jīng)禁用的約束 使用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)

27、REFERENCES emp(empno);刪除約束 從 EMP表刪除管理員約束SQL ALTER TABLE emp 2 DROP CONSTRAINT emp_mgr_fk;l從DEPT表刪除主鍵約束PRIMARY KEY，同時(shí)刪除也刪除了與該約束關(guān)聯(lián)的EMP表DEPTNO上的外鍵 FOREIGN KEY約束。SQL ALTER TABLEdept 2 DROP PRIMARY KEY CASCADE;禁用約束 在ALTER TABLE 語(yǔ)句中使用DISABLE子句可以使約束失效 使用CASCADE 選項(xiàng)可以使與該約束是從屬關(guān)系的約束同時(shí)失效SQL ALTER TABLEdeptment

28、2 DISABLE CONSTRAINTdept_deptno_pk CASCADE;啟用約束 使用ENABLE子句可以激活已經(jīng)禁用的約束 如果您啟用唯一UNIQUE或主鍵PRIMARY KEY約束，唯一 UNIQUE或主鍵 PRIMARY KEY索引便自動(dòng)創(chuàng)建SQL ALTER TABLEdeptment 2 ENABLE CONSTRAINTdept_deptno_pk;查詢約束 查詢 USER_CONSTRAINTS表可以檢索出所有約束名稱及定義CONSTRAINT_NAME C SEARCH_CONDITION- - -DEPT_DEPTNO_CK C deptno BETWEEN 1

29、0 AND 99DEPT_DEPTNO_PK PDEPT_DNAME_UK USQL SELECT constraint_name, constraint_type, 2 search_condition 3 FROM user_constraints 4 WHERE table_name = DEPTMENT;查詢與約束相關(guān)的列CONSTRAINT_NAME COLUMN_NAME- -EMP_DEPTNO_FK DEPTNOEMP_EMPNO_PK EMPNOEMP_MGR_FK MGRSYS_C00674 EMPNOSYS_C00675 DEPTNOSQL SELECTconstrain

30、t_name, column_name 2 FROMuser_cons_columns 3 WHEREtable_name = DEPTMENT; 在 USER_CONS_COLUMNS中查詢與約束相關(guān)的列小結(jié) 在Oracle中可以創(chuàng)建下列類型的約束： NOT NULL UNIQUE key PRIMARY KEY FOREIGN KEY CHECK 通過數(shù)據(jù)字典表 USER_CONSTRAINTS可以查詢約束名稱及其定義。4.4 查看表信息 在Oracle系統(tǒng)中，用戶可以通過數(shù)據(jù)字典或OEM圖形界面查詢Oracle系統(tǒng)內(nèi)部表和用戶創(chuàng)建表的信息。對(duì)于數(shù)據(jù)庫(kù)管理員而言，可以利用ANALYZE語(yǔ)句

31、可以對(duì)表進(jìn)行分析，通過分析可以獲取關(guān)于指定對(duì)象的狀態(tài)和統(tǒng)計(jì)信息，并且能夠?qū)χ付▽?duì)象的存儲(chǔ)格式進(jìn)行驗(yàn)證。分析表存儲(chǔ)空間 驗(yàn)證存儲(chǔ)結(jié)構(gòu) 查看統(tǒng)計(jì)信息 查看鏈接記錄和遷移記錄 4.4 索引 索引(INDEX)是為了加快數(shù)據(jù)的查找而創(chuàng)建的數(shù)據(jù)庫(kù)對(duì)象，特別是對(duì)大表，索引可以有效地提高查找速度，也可以保證數(shù)據(jù)的惟一性。索引是由Oracle自動(dòng)使用和維護(hù)的，一旦創(chuàng)建成功，用戶不必對(duì)索引進(jìn)行直接的操作。索引是獨(dú)立于表的數(shù)據(jù)庫(kù)結(jié)構(gòu)，即表和索引是分開存放的，當(dāng)刪除索引時(shí)，對(duì)擁有索引的表的數(shù)據(jù)沒有影響。 在創(chuàng)建PRIMARY KEY和UNIQUE約束條件時(shí)，系統(tǒng)將自動(dòng)為相應(yīng)的列創(chuàng)建惟一(UNIQUE)索引。索引的名

32、字同約束的名字一致。 索引有兩種：B*樹索引和位圖(BITMAP)索引。 B*樹索引是通常使用的索引，也是默認(rèn)的索引類型。在這里主要討論B*樹索引。B*樹是一種平衡2叉樹，左右的查找路徑一樣。這種方法保證了對(duì)表的任何值的查找時(shí)間都相同。 B*樹索引可分為：惟一索引、非惟一索引、一列簡(jiǎn)單索引和多列復(fù)合索引。 創(chuàng)建索引一般要掌握以下原則：只有較大的表才有必要建立索引，表的記錄應(yīng)該大于50條，查詢數(shù)據(jù)小于總行數(shù)的2%4%。雖然可以為表創(chuàng)建多個(gè)索引，但是無助于查詢的索引不但不會(huì)提高效率，還會(huì)增加系統(tǒng)開銷。因?yàn)楫?dāng)執(zhí)行DML操作時(shí)，索引也要跟著更新，這時(shí)索引可能會(huì)降低系統(tǒng)的性能。一般在主鍵列或經(jīng)常出現(xiàn)在W

33、HERE子句或連接條件中的列建立索引，該列稱為索引關(guān)鍵字。索引的創(chuàng)建CREATE UNIQUE|BITMAP INDEX 索引索引名名 ON 表名表名(列名列名1，列名，列名2，.)；其中：其中：UNIQUE代表創(chuàng)建惟一索引，不指明為創(chuàng)建非惟一代表創(chuàng)建惟一索引，不指明為創(chuàng)建非惟一索引。索引。BITMAP 代表創(chuàng)建位圖索引，如果不指明該參數(shù)，代表創(chuàng)建位圖索引，如果不指明該參數(shù)，則創(chuàng)建則創(chuàng)建B*樹索引。列名是創(chuàng)建索引的關(guān)鍵字列，可以樹索引。列名是創(chuàng)建索引的關(guān)鍵字列，可以是一列或多列。是一列或多列。刪除索引的語(yǔ)法DROP INDEX 索引名；刪除索引的人應(yīng)該是索引的創(chuàng)建者或擁有DROP ANY IN

34、DEX系統(tǒng)權(quán)限的用戶。索引的刪除對(duì)表沒有影響。查看索引通過查詢數(shù)據(jù)字典USER_INDEXES可以檢查創(chuàng)建的索引。通過查詢數(shù)據(jù)字典USER_IND_COLUMNS可以檢查索引的列?！居?xùn)練1】 顯示emp表的索引：SELECT INDEX_NAME, INDEX_TYPE, UNIQUENESS FROM USER_INDEXES WHERE TABLE_NAME=EMP; 執(zhí)行結(jié)果： INDEX_NAME - - E M P _ J O B S A L N O R M A L NONUNIQUE PK_EMP NORMAL UNIQUE 說明：由本訓(xùn)練可見，emp表共有兩個(gè)索引， 其中EMP_

35、JOBSAL是剛剛創(chuàng)建的，屬于非惟一索引。PK_EMP為生成主鍵時(shí)系統(tǒng)創(chuàng)建的索引，屬于惟一索引。視圖 視圖是基于一張表或多張表或另外一個(gè) 視圖的邏輯表。視圖不同于表，視圖本身不包含任何數(shù)據(jù)。表是實(shí)際獨(dú)立存在的實(shí)體，是用于存儲(chǔ)數(shù)據(jù)的基本結(jié)構(gòu)。而視圖只是一種定義，對(duì)應(yīng)一個(gè)查詢語(yǔ)句。視圖的數(shù)據(jù)都來自于某些表，這些表被稱為基表。通過視圖來查看表，就像是從不同的角度來觀察一個(gè)(或多個(gè))表。 視圖有如下一些優(yōu)點(diǎn)： 可以提高數(shù)據(jù)訪問的安全性，通過視圖往往只可 以訪問數(shù)據(jù)庫(kù)中表的特定部分，限制了用戶訪問表的全部行和列。 簡(jiǎn)化了對(duì)數(shù)據(jù)的查詢，隱藏了查詢的復(fù)雜性。 視圖的數(shù)據(jù)來自一個(gè)復(fù)雜的查詢，用戶對(duì)視圖的檢索卻

36、很簡(jiǎn)單。 一個(gè)視圖可以檢索多張表的數(shù)據(jù)，因此用戶通過 訪問一個(gè)視圖，可完成對(duì)多個(gè)表的訪問。 視圖是相同數(shù)據(jù)的不同表示，通過為不同的用戶 創(chuàng)建同一個(gè)表的不同視圖，使用戶可分別訪問同一個(gè)表的不同部分。 視圖可以在表能夠使用的任何地方使用，但在對(duì) 視圖的操作上同表相比有些限制，特別是插入和修改操作。對(duì)視圖的操作將傳遞到基表，所以在表上定義的約束條件和觸發(fā)器在視圖上將同樣起作用。 視圖的創(chuàng)建創(chuàng)建視圖需要CREAE VIEW系統(tǒng)權(quán)限，視圖的創(chuàng)建語(yǔ)法如下：CREATE OR REPLACE FORCE|NOFORCE VIEW 視圖名(別名1，別名2.)AS 子查詢 WITH CHECK OPTION C

37、ONSTRAINT 約束名WITH READ ONLY其中：OR REPLACE 表示替代已經(jīng)存在的視圖。FORCE表示不管基表是否存在，創(chuàng)建視圖。NOFORCE表示只有基表存在時(shí)，才創(chuàng)建視圖，是默認(rèn)值。 別名是為子查詢中選中的列新定義的名字，替代 查詢表中原有的列名。 子查詢是一個(gè)用于定義視圖的SELECT查詢語(yǔ)句， 可以包含連接、分組及子查詢。 WITH CHECK OPTION表示進(jìn)行視圖插入或修改 時(shí)必須滿足子查詢的約束條件。后面的約束名是該約束條件的名字。 WITH READ ONLY 表示視圖是只讀的。刪除視圖 DROP VIEW 視圖名; 刪除視圖者需要是視圖的建立者或者擁有 D

38、ROP ANY VIEW權(quán)限。視圖的刪除不影響基表，不會(huì)丟失數(shù)據(jù)。視圖的操作l對(duì)視圖經(jīng)常進(jìn)行的操作是查詢操作，但也可以在一對(duì)視圖經(jīng)常進(jìn)行的操作是查詢操作，但也可以在一定條件下對(duì)視圖進(jìn)行插入、刪除和修改操作。定條件下對(duì)視圖進(jìn)行插入、刪除和修改操作。l對(duì)視圖的這些操作最終傳遞到基表。但是對(duì)視圖的對(duì)視圖的這些操作最終傳遞到基表。但是對(duì)視圖的操作有很多限定。如果視圖設(shè)置了只讀，則對(duì)視圖只操作有很多限定。如果視圖設(shè)置了只讀，則對(duì)視圖只能進(jìn)行查詢，不能進(jìn)行修改操作。能進(jìn)行查詢，不能進(jìn)行修改操作。l基表本身的限制和約束也必須要考慮。如果生成子基表本身的限制和約束也必須要考慮。如果生成子查詢的語(yǔ)句是一個(gè)分組查

39、詢，或查詢中出現(xiàn)計(jì)算列，查詢的語(yǔ)句是一個(gè)分組查詢，或查詢中出現(xiàn)計(jì)算列，這時(shí)顯然不能對(duì)表進(jìn)行插入。這時(shí)顯然不能對(duì)表進(jìn)行插入。l主鍵和主鍵和NOT NULL列如果沒有出現(xiàn)在視圖的子查詢列如果沒有出現(xiàn)在視圖的子查詢中，也不能對(duì)視圖進(jìn)行插入。在視圖中插入的數(shù)據(jù)，中，也不能對(duì)視圖進(jìn)行插入。在視圖中插入的數(shù)據(jù)，也必須滿足基表的約束條件。也必須滿足基表的約束條件。視圖的查看USER_VIEWS字典中包含了視圖的定義。USER_UPDATABLE_COLUMNS字典包含了哪些列可以更新、插入、刪除。USER_OBJECTS字典中包含了用戶的對(duì)象?？梢酝ㄟ^DESCRIBE命令查看字典的其他列信息。在這里給出一個(gè)

40、訓(xùn)練例子。同義詞 同義詞(SYNONYM)是為模式對(duì)象起的別名， 可以為表、視圖、序列、過程、函數(shù)和包等數(shù)據(jù)庫(kù)模式對(duì)象創(chuàng)建同義詞。 同義詞有兩種：公有同義詞和私有同義詞。公有同義詞是對(duì)所有用戶都可用的。創(chuàng)建公有同義詞必須擁有系統(tǒng)權(quán)限CREATE PUBLIC SYNONYM； 創(chuàng)建私有同義詞需要CREATE SYNONYM系統(tǒng)權(quán)限。私有同義詞只對(duì)擁有同義詞的賬戶有效，但私有同義詞也可以通過授權(quán)，使其對(duì)其他用戶有效。 同義詞通過給本地或遠(yuǎn)程對(duì)象分配一個(gè)通用或簡(jiǎn)單的名稱，隱藏了對(duì)象的擁有者和對(duì)象的真實(shí)名稱，也簡(jiǎn)化了SQL語(yǔ)句。 如果同義詞同對(duì)象名稱重名，私有同義詞又同公有同義詞重名，那么，識(shí)別的順

41、序是怎樣的呢？如果存在對(duì)象名，則優(yōu)先識(shí)別，其次識(shí)別私有同義詞，最后識(shí)別公有同義詞。比如,執(zhí)行以下的SELECT語(yǔ)句： SELECT * FROM ABC; 如果存在表ABC，就對(duì)表ABC執(zhí)行查詢語(yǔ)句； 如果不存在表ABC，就去查看是否有私有同義詞ABC，如果有就對(duì)ABC執(zhí)行查詢(此時(shí)ABC是另外一個(gè)表的同義詞)；如果沒有私有同義詞ABC，則去查找公有同義詞；如果找不到，則查詢失敗。同義詞的創(chuàng)建和使用CREATE PUBLIC SYNONYM 同義詞名 FOR 模式名.對(duì)象名數(shù)據(jù)庫(kù)鏈路名；其中： PUBLIC代表創(chuàng)建公有同義詞，若省略則代表創(chuàng)建私有同義詞。模式名代表?yè)碛袑?duì)象的模式賬戶名。 數(shù)據(jù)庫(kù)

42、鏈路名是指向遠(yuǎn)程對(duì)象的數(shù)據(jù)庫(kù)鏈接。 刪除同義詞的語(yǔ)法如下 DROP SYNONYM 同義詞名； 刪除同義詞的人必須是同義詞的擁有者 或有DROP ANY SYNONYM權(quán)限的人。刪除同義詞不會(huì)刪除對(duì)應(yīng)的對(duì)象。同義詞的查看通過查詢數(shù)據(jù)字典USER_OBJECTS和USER_SYNONYMS，可以查看同義詞信息 4 系統(tǒng)定義同義詞 系統(tǒng)為常用的對(duì)象預(yù)定義了一些同義詞， 利用它們可以方便地訪問用戶的常用對(duì)象。這些同義詞如表5-2所示。表5-2 Oracle數(shù)據(jù)庫(kù)模式對(duì)象序列 序列(SEQUENCE)是序列號(hào)生成器，可以為表中的行自動(dòng)生成序列號(hào)，產(chǎn)生一組等間隔的數(shù)值(類型為數(shù)字)。其主要的用途是生成表

43、的主鍵值，可以在插入語(yǔ)句中引用，也可以通過查詢檢查當(dāng)前值，或使序列增至下一個(gè)值。 創(chuàng)建序列需要CREATE SEQUENCE系統(tǒng)權(quán)限。CREATE SEQUENCE 序列名 INCREMENT BY nSTART WITH nMAXVALUE n|NOMAXVALUEMINVALUE n|NOMINVALUECYCLE|NOCYCLECACHE n|NOCACHE； INCREMENT BY 用于定義序列的步長(zhǎng)，如果 省略，則默認(rèn)為1，如果出現(xiàn)負(fù)值，則代表序列的值是按照此步長(zhǎng)遞減的。 START WITH 定義序列的初始值(即產(chǎn)生的第 一個(gè)值)，默認(rèn)為1。 MAXVALUE 定義序列生成器能產(chǎn)

44、生的最大值。 選項(xiàng)NOMAXVALUE是默認(rèn)選項(xiàng)，代表沒有最大值定義，這時(shí)對(duì)于遞增序列，系統(tǒng)能夠產(chǎn)生的最大值是10的27次方；對(duì)于遞減序列，最大值是-1。 MINVALUE定義序列生成器能產(chǎn)生的最小值。 選項(xiàng)NOMAXVALUE是默認(rèn)選項(xiàng)，代表沒有最小值定義，這時(shí)對(duì)于遞減序列，系統(tǒng)能夠產(chǎn)生的最小值是?10的26次方；對(duì)于遞增序列，最小值是1。 CYCLE和NOCYCLE 表示當(dāng)序列生成器的 值達(dá)到限制值后是否循環(huán)。CYCLE代表循環(huán)，NOCYCLE代表不循環(huán)。如果循環(huán)，則當(dāng)遞增序列達(dá)到最大值時(shí)，循環(huán)到最小值；對(duì)于遞減序列達(dá)到最小值時(shí)，循環(huán)到最大值。如果不循環(huán)，達(dá)到限制值后，繼續(xù)產(chǎn)生新值就會(huì)發(fā)生

45、錯(cuò)誤。 CACHE(緩沖)定義存放序列的內(nèi)存塊的大小，默認(rèn) 為20。NOCACHE表示不對(duì)序列進(jìn)行內(nèi)存緩沖。對(duì)序列進(jìn)行內(nèi)存緩沖，可以改善序列的性能。 DROP SEQUENCE 序列名; 刪除序列的人應(yīng)該是序列的創(chuàng)建者或擁有 DROP ANY SEQUENCE系統(tǒng)權(quán)限的用戶。序列一旦刪除就不能被引用了。 序列的某些部分也可以在使用中進(jìn)行修改， 但不能修改SATRT WITH選項(xiàng)。對(duì)序列的修改只影響隨后產(chǎn)生的序號(hào)，已經(jīng)產(chǎn)生的序號(hào)不變。修改序列的語(yǔ)法如下：刪除序列刪除序列ALTER SEQUENCE 序列名 INCREMENT BY nMAXVALUE n|NOMAXVALUEMINVALUE n

46、|NOMINVALUECYCLE|NOCYCLECACHE n|NOCACHE； 方法是使用CURRVAL和NEXTVAL來引用序列的值。 調(diào)用NEXTVAL將生成序列中的下一個(gè)序列號(hào)，調(diào)用 時(shí)要指出序列名，即用以下方式調(diào)用: 序列名.NEXTVAL CURRVAL用于產(chǎn)生序列的當(dāng)前值，無論調(diào)用多少 次都不會(huì)產(chǎn)生序列的下一個(gè)值。如果序列還沒有通過調(diào)用 N E X T V A L 產(chǎn) 生 過 序 列 的 下 一 個(gè) 值 ， 先 引 用CURRVAL沒有意義。調(diào)用CURRVAL的方法同上，要指出序列名，即用以下方式調(diào)用: 序列名.CURRVAL.序列的使用序列的使用 產(chǎn)生序列的第一個(gè)值： SELE

47、CT ABC.NEXTVAL FROM DUAL; 執(zhí)行結(jié)果： NEXTVAL - 10 產(chǎn)生序列的下一個(gè)值： SELECT ABC.NEXTVAL FROM DUAL; 執(zhí)行結(jié)果： NEXTVAL - 11 SELECT ABC.CURRVAL FROM DUAL; 執(zhí)行結(jié)果： CURRVAL - 11 說明：第一次調(diào)用NEXTVAL產(chǎn)生序列的 初始值，根據(jù)定義知道初始值為10。第二次調(diào)用產(chǎn)生11，因?yàn)樾蛄械牟介L(zhǎng)為1。調(diào)用CURRVAL，顯示當(dāng)前值11，不產(chǎn)生新值。 序列的應(yīng)用：產(chǎn)生員工序列號(hào)。 創(chuàng)建序列： C R E A T E S E Q U E N C E E M P I D INCR

48、EMENT BY 1 START WITH 10 MAXVALUE 9999999 NOCYCLE NOCACHE; 執(zhí)行結(jié)果： 序列已創(chuàng)建。 使用序列生成新的員工編號(hào)： INSERT INTO emp(empno) VALUES (TO_CHAR(EMPID.NEXTVAL); 注意：通過查詢看到插入的序號(hào)是連續(xù)的，但如果在插入的過程中使用了回退或發(fā)生了系統(tǒng)崩潰等情況，可能會(huì)產(chǎn)生序號(hào)的間隔。 同過數(shù)據(jù)字典USER_OBJECTS可以查看 用戶擁有的序列。 通過數(shù)據(jù)字典USER_SEQUENCES可以 查看序列的設(shè)置。 【訓(xùn)練1】 查看用戶的序列： SELECT SEQUENCE_NAME,MIN_VALUE, MAX_VALUE,INCREMENT_BY,LAST_NUMBER FROM USER_SEQ