ORACLE數(shù)據(jù)庫(kù)設(shè)計(jì)

1、目錄第1章Oracle數(shù)據(jù)庫(kù)安裝配置31.1 配置監(jiān)聽程序31.2 配置網(wǎng)絡(luò)服務(wù)名4第2章管理用戶訪問42.1表空間、用戶及方案概述42.2用戶訪問52.3創(chuàng)建用戶62.4修改用戶62.5 鎖定用戶賬戶72.6 解鎖用戶賬戶72.7 默認(rèn)角色72.8 授予用戶訪問權(quán)72.9撤消用戶92.10廢除用戶訪問92.11創(chuàng)建角色92.12 新建的用戶怎樣才能成功創(chuàng)建一個(gè)表10第3章建立和管理表113.1簡(jiǎn)介123.2建表133.3修改表143.4截?cái)嗪蛣h除表143.5顯示表信息15第4章 Oracle操作符16第5章 基本查詢195.1簡(jiǎn)單查詢語(yǔ)句195.2排序數(shù)據(jù)21第6章 復(fù)雜查詢226.1數(shù)據(jù)分

2、組226.2連接查詢236.3相等連接（包括SQL：1999標(biāo)準(zhǔn)內(nèi)連接、自然連接）246.4不等連接246.5自連接246.6內(nèi)連接和外連接24內(nèi)連接25左外連接25右外連接25完全外連接256.7子查詢25單行子查詢（單列）25多行子查詢（單列）25多列子查詢26其他子查詢266.8使用集合操作符27第7章 常用SQL函數(shù)27第8章 操縱數(shù)據(jù)418.1插入數(shù)據(jù)418.2更新數(shù)據(jù)438.3刪除數(shù)據(jù)44第9章 使用事務(wù)459.1概述469.2事務(wù)分類469.3回復(fù)修改479.4回復(fù)部分事務(wù)489.5事務(wù)和鎖49第10章 使用約束4910.1約束簡(jiǎn)介4910.2定義約束5010.3維護(hù)約束5110

3、.5顯示約束信息52第11章 使用視圖5311.1視圖簡(jiǎn)介5311.2建立視圖5411.3維護(hù)視圖5511.4顯示視圖信息55第12章 使用其它對(duì)象（索引序列同義詞）5612.1使用索引56建立索引57維護(hù)索引57顯示索引信息5811.2使用序列5811.21建立序列58使用序列58維護(hù)序列59顯示序列信息5911.3使用同義詞59建立同義詞59刪除同義詞59第13章 PL/SQL語(yǔ)句6013.1 PL/SQL簡(jiǎn)介6013.2 PL/SQL塊62定義并使用變量63異常64游標(biāo)6713.3過程函數(shù)包71第14章 過程函數(shù)包及觸發(fā)器7114.1過程71建立過程71顯示過程代碼7214.2函數(shù)72建

4、立函數(shù)72刪除函數(shù)73顯示函數(shù)代碼7314.3包73建立包規(guī)范73建立包體74刪除包75顯示包代碼7514.4觸發(fā)器75語(yǔ)句觸發(fā)器75行觸發(fā)器76使用觸發(fā)器的注意事項(xiàng)77編譯觸發(fā)器77刪除觸發(fā)器77顯示觸發(fā)器代碼77第十五章 使用EXP和IMP7715.1使用EXP78導(dǎo)出表78導(dǎo)出方案78導(dǎo)出數(shù)據(jù)庫(kù)7815.2使用IMP78導(dǎo)入表79導(dǎo)入方案79導(dǎo)入數(shù)據(jù)庫(kù)79第1章Oracle數(shù)據(jù)庫(kù)安裝配置當(dāng)安裝Oracle Database時(shí)，如果沒有建立數(shù)據(jù)庫(kù)，在安裝完成之后可以使用DBCA工具建立數(shù)據(jù)庫(kù)。數(shù)據(jù)庫(kù)配置助手(Database Configuration Assistant)用于建立數(shù)據(jù)庫(kù)、

5、配置數(shù)據(jù)庫(kù)選項(xiàng)、刪除數(shù)據(jù)庫(kù)和管理模板。當(dāng)建立了Oracle數(shù)據(jù)庫(kù)之后，為了使得服務(wù)器端可以監(jiān)聽該Oracle數(shù)據(jù)庫(kù)，必須配置監(jiān)聽程序。為了使得客戶端可以訪問該數(shù)據(jù)庫(kù)，必須在客戶端配置網(wǎng)絡(luò)服務(wù)名。只有合理地配置了監(jiān)聽程序和網(wǎng)絡(luò)服務(wù)名之后，客戶應(yīng)用才能訪問該數(shù)據(jù)庫(kù)。配置監(jiān)聽程序和網(wǎng)絡(luò)服務(wù)名可以使用網(wǎng)絡(luò)管理工具Net Manager完成。(源碼網(wǎng)整理：)1.1 配置監(jiān)聽程序監(jiān)聽程序用于接收客戶端的連接請(qǐng)求。當(dāng)客戶應(yīng)用訪問Oracle Server時(shí)，監(jiān)聽程序會(huì)接收并檢查連接請(qǐng)求，以確定是否可以為該客戶應(yīng)用提供數(shù)據(jù)服務(wù)。在建立了Oracle數(shù)據(jù)庫(kù)之后，為了使得客戶應(yīng)用可以訪問Oracle數(shù)據(jù)庫(kù)，必須在

6、監(jiān)聽程序中追加該數(shù)據(jù)庫(kù)。一個(gè)監(jiān)聽程序可以監(jiān)聽多個(gè)Oracle數(shù)據(jù)庫(kù)，多個(gè)監(jiān)聽程序也可以監(jiān)聽同一個(gè)Oracle數(shù)據(jù)庫(kù)。當(dāng)安裝數(shù)據(jù)庫(kù)產(chǎn)品時(shí)，會(huì)自動(dòng)建立默認(rèn)監(jiān)聽程序LISTENER。配置監(jiān)聽程序的具體步驟如下：l 展開監(jiān)聽程序，并選中LISTENER節(jié)點(diǎn)，此時(shí)在NET MANAGER窗口右端會(huì)顯示默認(rèn)監(jiān)聽位置，其中“協(xié)議”用于指定監(jiān)聽程序要使用的網(wǎng)絡(luò)協(xié)議（默認(rèn)為TCP/IP）；“主機(jī)”用于指定服務(wù)器所在機(jī)器的主機(jī)名或IP地址；“端口”用于指定監(jiān)聽程序要使用的TCP/IP端口號(hào)（默認(rèn)1521）。l 在Net Manager窗口上端的下拉列表中選擇數(shù)據(jù)庫(kù)服務(wù)，此時(shí)會(huì)顯示默認(rèn)的數(shù)據(jù)庫(kù)配置，“全局?jǐn)?shù)據(jù)庫(kù)名”

7、用于指定數(shù)據(jù)庫(kù)的全局?jǐn)?shù)據(jù)庫(kù)名；“Oracle主目錄”用于指定Oracle數(shù)據(jù)庫(kù)軟件的安裝路徑；“SID”用于指數(shù)據(jù)庫(kù)例程名。為了監(jiān)聽新建的數(shù)據(jù)庫(kù)（如DEMO），必須追加該數(shù)據(jù)庫(kù)。l 單擊“添加數(shù)據(jù)庫(kù)”按鈕，然后進(jìn)行相應(yīng)配置，在“全局?jǐn)?shù)據(jù)庫(kù)名”處輸入DEMO數(shù)據(jù)庫(kù)的初始化參數(shù)SERVICEHOME所對(duì)應(yīng)的值，在SID處輸入DEMO數(shù)據(jù)庫(kù)的例程名。配置了監(jiān)聽程序之后，保存網(wǎng)絡(luò)配置信息。l 保存了監(jiān)聽程序配置之后，為了使得其網(wǎng)絡(luò)配置生效，必須重新啟動(dòng)監(jiān)聽程序。（通過服務(wù)器管理器重新啟動(dòng)監(jiān)聽程序）1.2 配置網(wǎng)絡(luò)服務(wù)名l 選中“服務(wù)命名”，然后單擊+按鈕，此時(shí)會(huì)顯示“Net 服務(wù)名”界面，建議使用數(shù)據(jù)

8、庫(kù)名作網(wǎng)絡(luò)服務(wù)名。l 選取與監(jiān)聽程序一致的網(wǎng)絡(luò)協(xié)議“TCP/IP”。l 指定數(shù)據(jù)庫(kù)所在主機(jī)名及其監(jiān)聽端口號(hào)。l 指定監(jiān)聽程序所配置的全局?jǐn)?shù)據(jù)庫(kù)名或者SID。l 測(cè)試網(wǎng)絡(luò)服務(wù)名配置是否成功，如果成功則表示網(wǎng)絡(luò)服務(wù)名配置正確。l 完成網(wǎng)絡(luò)服務(wù)名配置之后，保存網(wǎng)絡(luò)配置信息。第2章管理用戶訪問本章主要內(nèi)容：l 表空間、用戶及方案概述l 用戶訪問l 創(chuàng)建用戶l 修改用戶l 授予用戶訪問權(quán)l(xiāng) 撤消用戶l 廢除用戶訪問l 創(chuàng)建角色2.1 表空間、用戶及方案概述表空間是數(shù)據(jù)庫(kù)的邏輯組成部分。從物理上說(shuō)，數(shù)據(jù)庫(kù)數(shù)據(jù)存放在數(shù)據(jù)文件中；從邏輯上說(shuō)，數(shù)據(jù)庫(kù)數(shù)據(jù)存放在表空間(tablespace)中，并且表空間是由一個(gè)

9、或多個(gè)數(shù)據(jù)文件組成的。一個(gè)表空間是由一個(gè)或多個(gè)數(shù)據(jù)文件組成的。用戶（也稱為帳戶）是定義在數(shù)據(jù)庫(kù)中的一個(gè)名稱，它是Oracle數(shù)據(jù)庫(kù)的基本訪問控制機(jī)制。當(dāng)連接到Oracle數(shù)據(jù)庫(kù)時(shí)，默認(rèn)情況下必須要提供用戶名和口令。只有在輸入了正確的用戶名和口令之后，才能夠連接到數(shù)據(jù)庫(kù)，并執(zhí)行各種管理操作和數(shù)據(jù)訪問操作。方案(Schema)是用戶所擁有數(shù)據(jù)庫(kù)對(duì)象的集合。在Oracle數(shù)據(jù)庫(kù)中對(duì)象是以用戶來(lái)組織的，用戶與方案是一一對(duì)應(yīng)的關(guān)系，并且二者名稱相同。例SCOTT用戶所擁有的所有對(duì)象都屬于SCOTT方案，而SYSTEM用戶所擁有的所有對(duì)象都屬于SYSTEM方案。當(dāng)訪問數(shù)據(jù)庫(kù)對(duì)象時(shí)，有一些注意事項(xiàng)：l 在同

10、一個(gè)方案中不能存在同名對(duì)象，但不同方案可以具有同名對(duì)象。l 用戶可以直接訪問其方案對(duì)象，但如果要訪問其他方案對(duì)象，則必須具有對(duì)象權(quán)限。如用戶SCOTT可以直接查詢其方案表EMP，但如果用戶SMITH要檢索SCOTT方案的表EMP，則必須在EMP表上具有SELECT對(duì)象權(quán)限。l 當(dāng)用戶訪問其他方案對(duì)象時(shí)，必須加方案名為前綴。例，如用戶SMITH要訪問SCOTT方案的EMP表，則必須使用SCOTT.EMP。2.2 用戶訪問在多用戶環(huán)境里，一個(gè)數(shù)據(jù)庫(kù)可能有多個(gè)用戶同時(shí)在訪問。當(dāng)有不同的用戶同時(shí)訪問數(shù)據(jù)庫(kù)時(shí)，保護(hù)數(shù)據(jù)庫(kù)安全，防范非授權(quán)訪問非常重要。因此，必須在數(shù)據(jù)庫(kù)里創(chuàng)建用戶，并為用戶指定用戶名和密碼

11、，這樣可以保證只有經(jīng)過授權(quán)的，即有正確用戶名和密碼的用戶才能訪問數(shù)據(jù)庫(kù)。數(shù)據(jù)庫(kù)管理員（DBA）是最高級(jí)別的用戶，他可以創(chuàng)建其他用戶。在創(chuàng)建了用戶后，DBA需要按用戶的需求為用戶分配權(quán)限。權(quán)限指用戶執(zhí)行特定語(yǔ)句的許可，這意味并非所有用戶都被允許修改重要數(shù)據(jù)。例如，某個(gè)用戶可能只需要有連接數(shù)據(jù)庫(kù)和查詢某些表的記錄的權(quán)限。類似地，另一個(gè)用戶可能要求有創(chuàng)建和修改表的權(quán)限。DBA有訪問數(shù)據(jù)庫(kù)的一切權(quán)限，并有權(quán)為其他用戶分配權(quán)限。下表列出了部分DBA權(quán)限：DBA權(quán)限有權(quán)執(zhí)行CREATE USER創(chuàng)建新用戶DROP USER撤消用戶DROP ANY TABLE撤消表BACKUP ANY TABLE為表制作備

12、份SELECT ANY TABLE查詢數(shù)據(jù)庫(kù)對(duì)象，如表和視圖CREATE ANY TABLE創(chuàng)建表為了維護(hù)存儲(chǔ)在數(shù)據(jù)庫(kù)中數(shù)據(jù)的安全，Oracle提供了以下數(shù)據(jù)庫(kù)安全措施：l 管理和控制數(shù)據(jù)庫(kù)訪問l 用Oracle數(shù)據(jù)字典驗(yàn)證權(quán)限l 為指定用戶提供對(duì)數(shù)據(jù)庫(kù)特定對(duì)象（表、視圖和序列等）的訪問l 為數(shù)據(jù)庫(kù)對(duì)象提供同義詞可被數(shù)據(jù)庫(kù)操作采用的數(shù)據(jù)庫(kù)的安全策略包括：l 系統(tǒng)安全：系統(tǒng)安全涉及系統(tǒng)級(jí)的訪問，如允許用戶通過指出用戶名和密碼連接Oracle，為用戶分配磁盤空間，限定用戶所能執(zhí)行的操作。用戶能執(zhí)行的操作包括：查詢數(shù)據(jù)庫(kù)對(duì)象的內(nèi)容、創(chuàng)建數(shù)據(jù)庫(kù)對(duì)象和更改數(shù)據(jù)庫(kù)對(duì)象。l 數(shù)據(jù)安全：數(shù)據(jù)安全涉及對(duì)數(shù)據(jù)庫(kù)對(duì)

13、象的訪問和使用，以及用戶在數(shù)據(jù)庫(kù)對(duì)象上所擁有的權(quán)限的程度。2.3 創(chuàng)建用戶CREATE USER語(yǔ)句用于創(chuàng)建新用戶。在創(chuàng)建新用戶時(shí)，必須為新用戶指定用戶名和密碼。為了使新用戶能登錄服務(wù)器和訪問數(shù)據(jù)庫(kù)，DBA必須顯式地為用戶分配權(quán)限。只有擁有CREATE USER權(quán)限的用戶才能創(chuàng)建新用戶。例如，DBA創(chuàng)建了新用戶Susan，但Susan無(wú)權(quán)創(chuàng)建其他新用戶。只有當(dāng)Susan有了CREATE USER這個(gè)權(quán)限后，她才能創(chuàng)建別的新用戶。創(chuàng)建用戶的語(yǔ)法示例：CREATE USER user IDENTIFIED BY passwordDEFAULT TABLESPACE data01TEMPORARY

14、TABLESPACE tempQUOTA 3M ON data01PASSWORD EXPIRE;其中IDENTIFIED BY用于指定用戶口令；DEFAULT TABLESPACE用于指定用戶的默認(rèn)表空間，當(dāng)建立表或者索引時(shí)，如果不指定TABLESPACE子句，那么Oracle會(huì)自動(dòng)在默認(rèn)表空間上為這些對(duì)象分配空間；TEMPORARY TABLESPACE用于指定用戶的臨時(shí)表空間，當(dāng)用戶執(zhí)行排序操作時(shí)，或臨時(shí)數(shù)據(jù)超過PGA工作區(qū)，則會(huì)在該表空間上建立臨時(shí)段；QUOTA用于指定表空間配額，即用戶對(duì)象在表空間上可占用的最大空間；PASSWORD EXPIRE用于指定終止口令，最終強(qiáng)制用戶在登錄時(shí)

15、改變口令。當(dāng)建立了新用戶之后，需要注意以下問題：l 初始創(chuàng)建的數(shù)據(jù)庫(kù)用戶沒有任何權(quán)限，不能執(zhí)行任何數(shù)據(jù)庫(kù)操作。l 如果在建立用戶時(shí)不指定DEFAULT TABLESPACE子句，那么Oracle會(huì)將數(shù)據(jù)庫(kù)默認(rèn)表空間作為用戶的默認(rèn)表空間。在Oracle Database 10g之前，如果不指定DEFAULT TABLESPACE子句，那么Oracle會(huì)將SYSTEM表空間作為用戶的默認(rèn)表空間。l 如果在建立用戶時(shí)不指定TEMPORARY TABLESPACE子句，那么Oracle會(huì)將數(shù)據(jù)庫(kù)默認(rèn)臨時(shí)表空間作為用戶的臨時(shí)表空間。l 如果在建立用戶時(shí)沒有為特定表空間指定QUOTA子句，那么用戶在特定表

16、空間上的配額為0，這樣用戶將不能在相應(yīng)表空間上建立數(shù)據(jù)對(duì)象。2.4 修改用戶修改用戶信息是使用ALTER USER命令完成的。一般情況下，該命令是由DBA來(lái)執(zhí)行的，如果以其他用戶身份修改用戶信息，必須要具有ALTER USER系統(tǒng)權(quán)限。1修改口令創(chuàng)建用戶時(shí)為每個(gè)用戶指定一個(gè)初始密碼。之后可修改密碼，修改密碼的方法有兩種：l 管理員修改：ALTER USER user IDENTIFIED BY password;(password為用戶的新密碼)l 用戶自己修改：用戶登錄后輸入命令：PASSWORD；修改表空間配額表空間配額用于限制用戶對(duì)象在表空間上可占用的最大空間。如果用戶對(duì)象己經(jīng)占滿了表空

17、間配額所允許的最大空間，那么該用戶將不能在該表空間上分配新的空間。此時(shí)如果執(zhí)行了涉及到空間分配的SQL操作（如INSERT、UPDATE、CREATE TABLE等），則會(huì)顯示錯(cuò)誤，修改表空間語(yǔ)法：ALTER USER user QUOTA 10M ON data01;2.5 鎖定用戶賬戶為了禁止特定數(shù)據(jù)庫(kù)用戶用戶訪問數(shù)據(jù)庫(kù)，DBA可以鎖定用戶賬戶，ALTER USER user ACCOUNT LOCK;2.6 解鎖用戶賬戶為了使得數(shù)據(jù)庫(kù)用戶可以訪問數(shù)據(jù)庫(kù)，DBA可以解鎖用戶賬戶。ALTER USER user ACCOUNT UNLOCK；2.7 默認(rèn)角色當(dāng)將多個(gè)角色授予數(shù)據(jù)庫(kù)用戶之后，通

18、過使用ALTER USER 命令可以設(shè)置用戶的默認(rèn)角色。（當(dāng)為用戶指定了默認(rèn)角色后，以該用戶身份登錄時(shí)會(huì)自動(dòng)激活其默認(rèn)角色，并不激活非默認(rèn)角色）ALTER USER user DEFAULT ROLE select_role;補(bǔ)充： 默認(rèn)角色和非默認(rèn)角色的區(qū)別是什么? 這個(gè)理解有多種，第一種：默認(rèn)角色可以是我們創(chuàng)建數(shù)據(jù)庫(kù)就可以見的connect/dba等，非默認(rèn)角色需要我們自己創(chuàng)建；第二種：一個(gè)用戶可以有多個(gè)角色，默認(rèn)的角色登錄即生效，非默認(rèn)的需要激活才能使用。 默認(rèn)角色和權(quán)限集是Oracle安裝過程中預(yù)先定義的。每個(gè)版本的默認(rèn)角色都有所變化。CREATE USER SMIS IDENTIFI

19、ED BY SMIS；grant dba,connect to SMIS;create role r_px; grant r_px to SMIS;select * from dba_role_privs where grantee='SMIS'alter user SMIS default role all except r_px;select * from dba_role_privs where grantee='SMIS'2.8 授予用戶訪問權(quán)用戶創(chuàng)建后，數(shù)據(jù)庫(kù)管理員需要為他分配權(quán)限。權(quán)限關(guān)系到數(shù)據(jù)庫(kù)的安全，它決定了用戶在數(shù)據(jù)庫(kù)上所能執(zhí)行的操作。GRAN

20、T語(yǔ)句用于為用戶分配權(quán)限。語(yǔ)法如下：GRANT privilege TO user; 注釋：授予所有系統(tǒng)權(quán)限GRANT ALL PRIVILEGES to test_2;注釋：授予所有對(duì)象權(quán)限 GRANT ALL PRIVILEGES ON DEMO.DEPT TO TEST_1;可分配給用戶的權(quán)限有：l 系統(tǒng)權(quán)限：允許用戶訪問數(shù)據(jù)庫(kù)的權(quán)限稱為系統(tǒng)權(quán)限。l 對(duì)象權(quán)限：允許用戶在數(shù)據(jù)庫(kù)對(duì)象上執(zhí)行查詢、更新、刪除或添加數(shù)據(jù)等操作的權(quán)限，此稱為對(duì)象權(quán)限。常用的系統(tǒng)權(quán)限有：系統(tǒng)權(quán)限有權(quán)執(zhí)行CREATE SESSION連接數(shù)據(jù)庫(kù)CREATE TABLE創(chuàng)建表CREATE VIEW創(chuàng)建視圖CREATE P

21、UBLIC SYNONYM建立同義詞CREATE SEQUENCE創(chuàng)建序列CREATE PROCEDURE建立過程、函數(shù)和包CREATE TRIGGER建立觸發(fā)器CREATE CLUSTER建立簇CREATE TYPE建立對(duì)象類型CREATE DATABASE LINK建立數(shù)據(jù)庫(kù)鏈另外，Oracle還提供了一類ANY系統(tǒng)權(quán)限，當(dāng)用戶具有該類系統(tǒng)權(quán)限時(shí)，可以在任何方案中執(zhí)行相應(yīng)操作。例如，如果用戶具有SELECT ANY TABLE系統(tǒng)權(quán)限，那么用戶可以查詢?nèi)魏畏桨傅谋恚ǔ龜?shù)據(jù)字典基表和數(shù)據(jù)字典視圖DBA_XXX-DBA和特權(quán)用戶專訪）。如：GRANT CREATE SESSION,CREATE

22、 SEQUENCE,CREATE VIEW TO john;上述命令執(zhí)行后，用戶john將擁有創(chuàng)建會(huì)話、視圖和序列的系統(tǒng)權(quán)限。*Oracle提供了100多種系統(tǒng)權(quán)限（ALTER TABLE，ALTER VIEW，ALTER PROCEDURE，DROP TABLE，DROP VIEW，DROP PROCEDURE等）。一般情況下，授予系統(tǒng)權(quán)限是由DBA來(lái)完成的；如果要以其他用戶身份授予系統(tǒng)權(quán)限，則要求該用戶必須具有GRANT ANY PRIVILEGE系統(tǒng)權(quán)限，或者具有相應(yīng)系統(tǒng)權(quán)限及其轉(zhuǎn)授系統(tǒng)權(quán)限選項(xiàng)（WITH ADMIN OPTION），授予系統(tǒng)權(quán)限是使用GRANT命令來(lái)完成的，其語(yǔ)法如下：

23、GRANT system_priv,system_priv,TO user | role | public,user | role | public.WITH ADMIN OPTION; 注：user也可以是用戶組PUBLIC；UNLIMITED TABLESPACE權(quán)限不能被授予角色下表列出了所有對(duì)象權(quán)限。對(duì)象權(quán)限適用于ALTER表、序列DELETE表、視圖EXECUTE過程INDEX表INSERT表、視圖REFERENCES表（基于表建立從表）SELECT表、視圖、序列UPDATE表、視圖如果用戶在某個(gè)同義詞上擁有權(quán)限，而這個(gè)同義詞又需要引用其他基表，那么用戶在同義詞上擁有的權(quán)限會(huì)轉(zhuǎn)換為在

24、基表上的權(quán)限。缺省地，用戶對(duì)他所創(chuàng)建的對(duì)象擁有完全的權(quán)限。如用戶在用戶模式上創(chuàng)建了一個(gè)表，那么缺省地，他在用戶模式上擁有所創(chuàng)建的這個(gè)表的所有權(quán)限。授予對(duì)象權(quán)限一般情況下是由對(duì)象所有者或者DBA用戶來(lái)完成的；如果以其他用戶身份授予對(duì)象權(quán)限，則要求用戶必須具有該對(duì)象權(quán)限及轉(zhuǎn)授對(duì)象權(quán)限選項(xiàng)（WITH GRANT OPTION），語(yǔ)法如下：GRANT object_priv (columns) ,object_priv(columns) | ALL PRIVILEGES ON schema.objectTO user | role | PUBLIC , user | role | PUBLIC WIT

25、H GRANT OPTION ;例：grant update on scott.emp to blake;Grant update(sal) on emp to blake;（只能在insert、update和references上授予列權(quán)限2.9 撤消用戶DROP USER語(yǔ)句用于刪除用戶。語(yǔ)法：DROP USER username;撤消用戶時(shí)，用戶創(chuàng)建的對(duì)象并沒有被撤消。為了撤消創(chuàng)建對(duì)象的用戶語(yǔ)法：DROP USER username CASCADE；（不指定CASCADE不能撤消創(chuàng)建對(duì)象的用戶）2.10 廢除用戶訪問一般情況下，收回系統(tǒng)權(quán)限是由DBA來(lái)完成的；如果以其他用戶身份收回系統(tǒng)權(quán)

26、限，則要求該用戶必須具有相應(yīng)系統(tǒng)權(quán)限及其轉(zhuǎn)授系統(tǒng)權(quán)限選項(xiàng)(WITH ADMIN OPTION)。收回系統(tǒng)權(quán)限是使用REVOKE命令來(lái)完成的。語(yǔ)法如下：REVOKE system_priv,system_privFROM user | role | PUBLIC ,user | role | PUBLIC 用戶的權(quán)限可使用REVOKE語(yǔ)句廢除。一旦某個(gè)用戶的權(quán)限被撤消，由他創(chuàng)建的所有用戶和從他那獲得權(quán)限的所有用戶也都被撤消。如，Jim把CREATE TABLE權(quán)限授給John。當(dāng)Jim的權(quán)限被撤消時(shí)，同時(shí)自動(dòng)撤消John的權(quán)限。如：GRANT CREATE TABLE TO John; REVO

27、KE CREATE TABLE FROM John;（不能撤消自己的權(quán)限）一般情況下，收回對(duì)象權(quán)限是由對(duì)象所有者或者DBA用戶來(lái)完成的；如果以其他用戶身份收回對(duì)象權(quán)限，則要求用戶必須具有該對(duì)象權(quán)限及轉(zhuǎn)授對(duì)象權(quán)限選項(xiàng)（WITH GRANT OPTION）。語(yǔ)法如下：REVOKE object_priv , object_priv | ALL PRIVILEGES ON schema. objectFROM user | role | PUBLIC , user | role | PUBLIC CASCADE CONSTRAINTS; CASCADE CONSTRAINTS 用于刪除任何與該對(duì)象相

28、關(guān)的約束和對(duì)象，例如索引、觸發(fā)器、權(quán)限、完整性約束等。2.11 創(chuàng)建角色角色是相關(guān)權(quán)限的命名集合，使用角色的主要目的是為了簡(jiǎn)化權(quán)限管理。角色可以是權(quán)限的組合，也可以是角色的組合。角色包括預(yù)定義角色和自定義角色兩類。常用的預(yù)定義角色有：（oracle 10g）l CONNECT Role：分配給臨時(shí)用戶的角色。通常，為只需要查詢材料而無(wú)須創(chuàng)建表的用戶分配這個(gè)角色。l RESOURCE Role：這個(gè)角色分配給常規(guī)用戶l DBA Role：這個(gè)角色擁有一切系統(tǒng)權(quán)限，包括不加限制的表空間配額以及WITH ADMIN OPTION選項(xiàng)。默認(rèn)的DBA用戶為SYS和SYSTEM，他們可以將任何系統(tǒng)權(quán)限授予

29、其他用戶。讀者需要注意，DBA角色不具備SYSDBA和SYSOPER特權(quán)。創(chuàng)建角色的語(yǔ)法：CREATE ROLE role NOT IDENTIFIED ; 不驗(yàn)證用于公用角色或用戶默認(rèn)角色或CREATE ROLE roleIDENTIFIED BY password; 數(shù)據(jù)庫(kù)驗(yàn)證為角色分配密碼或修改角色密碼：ALTER ROLE roleIDENTIFIED BY password;（password為要設(shè)置的新密碼）如：CREATE ROLE Acadre;ALTER ROLE Acadre IDENTIFIED BY success;為角色授予權(quán)限GRANT privilege TO r

30、ole;例：只授予CONNECT權(quán)限給角色Acadre，這樣他們不可能操作數(shù)據(jù)庫(kù)。GRANT connect TO Acadre;將角色指派給用戶一個(gè)角色可指派給多個(gè)用戶。類似的，一個(gè)用戶也可具有多個(gè)角色。當(dāng)把角色指派給用戶時(shí)，賦予該角色的權(quán)限也自動(dòng)分配給用戶。為用戶分配角色的語(yǔ)法：GRANT role TO user;也可以使用ALTER USER語(yǔ)句授權(quán)用戶。ALTER USER John Default ROLE Student;授予用戶John以缺省角色student。2.12 新建的用戶怎樣才能成功創(chuàng)建一個(gè)表當(dāng)一個(gè)用戶剛被創(chuàng)建時(shí)是不具備任何權(quán)限的，因此要在該用戶模式下創(chuàng)建表，需授予C

31、REATE SESSION、CREATE TABLE、以及UNLIMITED TABLESPACE（或分配配額）權(quán)限，因?yàn)椋寒?dāng)用戶要連接到數(shù)據(jù)庫(kù)時(shí)必須擁有CREATE SESSION權(quán)限當(dāng)用戶要?jiǎng)?chuàng)建表時(shí)必須擁有CREATE TABLE權(quán)限，同時(shí)用戶還需要在表空間中擁有配額或者被授予UNLIMITED TABLESPACE。現(xiàn)在我們來(lái)做一個(gè)測(cè)試：1）、創(chuàng)建用戶TEST，密碼為passwd_1:SQL> CREATE USER testIDENTIFIED BY passwd_I1用戶已創(chuàng)建2）當(dāng)用TEST連接數(shù)據(jù)庫(kù)時(shí)：SQL> conn test/passwd_1ERROR:ORA-

32、01045: user TEST lacks CREATE SESSION privilege; logon denied警告: 您不再連接到 ORACLE。/因?yàn)槿鄙貱REATE SESSION的權(quán)限，登陸失敗。3）利用SYS給TEST授予CREATE SESSION權(quán)限：SQL> grant create session to test;授權(quán)成功。4）SQL> conn test/passwd_1已連接。5）在test的方案中創(chuàng)建表exam1:SQL> create table exam1 (student_id int,paper_id int);create tabl

33、e exam1ERROR 位于第 1 行:ORA-01031: 權(quán)限不足/因?yàn)槲唇oTEST用戶授予create table 權(quán)限，因此不能夠創(chuàng)建表exam1.6) 給TEST用戶授予CRETE TABLE 權(quán)限SQL> grant create table to test;授權(quán)成功。7）SQL> create table exam1(student_id int,paper_id int);create table exam1ERROR 位于第 1 行:ORA-01950: 表空間'SYSTEM'中無(wú)權(quán)限/因?yàn)樵趧?chuàng)建用戶時(shí)沒有指定表空間，因此默認(rèn)的表空間是SYSTE

34、M表空間，而TEST用戶還需要在表空間SYSTEM中既沒有擁有配額又沒有被授予UNLIMITED TABLESPACE權(quán)限，因此對(duì)于這種情況有兩種解決辦法：第一種方法：SQL> alter user testquota 15m on system;用戶已更改。/在SYSTEM表空間中，給用戶TEST分配15M的使用空間SQL> create table exam1(student_id int,paper_id int);表已創(chuàng)建第二種方法：SQL> grant unlimited tablespace to test授權(quán)成功。SQL> create table exa

35、m2(student_id int,paper_id int);第3章建立和管理表本章主要內(nèi)容：l 表簡(jiǎn)介l 創(chuàng)建表l 修改表l 截?cái)嗪蛣h除表l 顯示表的信息3.1 簡(jiǎn)介 表是Oracle數(shù)據(jù)庫(kù)最基本的對(duì)象，它用于存儲(chǔ)用戶數(shù)據(jù)。l 設(shè)計(jì)表當(dāng)設(shè)計(jì)表時(shí)，需要考慮以下因素：ü 當(dāng)規(guī)劃表和列時(shí)，應(yīng)該使用有意義的名稱。當(dāng)定義表名和列名時(shí)，只能使用字符（AZ，a-z）、數(shù)字（09）、_、$和#，名稱必須以字符開始，并且長(zhǎng)度不能超過30個(gè)字符。ü 當(dāng)規(guī)劃表名和列名時(shí)，要使用一致的縮寫格式、單數(shù)或復(fù)數(shù)格式。ü 為了給用戶和其他人員提供有意義的幫助信息，應(yīng)該使用COMMENT命令描

36、述表、列的作用ü 當(dāng)設(shè)計(jì)表時(shí)，應(yīng)該使用第一范式（1NF）、第二范式（2NF）和第三范式（3NF）規(guī)范化每張數(shù)據(jù)庫(kù)表。ü 當(dāng)定義表列時(shí)，應(yīng)該選擇合適的數(shù)據(jù)類型和長(zhǎng)度。ü 當(dāng)定義表列時(shí)，為了節(jié)省存儲(chǔ)空間，應(yīng)該將NULL列放在后面。l 常用數(shù)據(jù)類型當(dāng)建立表時(shí)，不僅需要指定表名、列名，而且要根據(jù)情況為列選擇合適的數(shù)據(jù)類型和長(zhǎng)度。下面是常用數(shù)據(jù)類型。ü CHAR（N）或CHAR（N BYTE）：定義固定長(zhǎng)度的字符串（以字節(jié)為單位），最大長(zhǎng)度為2000字節(jié)。如果CHAR（100）或CHAR（100 BYTE），表示可存儲(chǔ)100個(gè)字節(jié)的字符串，并且占用空間是固定的（1

37、00個(gè)字節(jié)）ü CHAR（N CHAR）：定義固定長(zhǎng)度的字符串（以字符個(gè)數(shù)為單位）。如果CHAR（100 CHAR），表示該列最多可以存儲(chǔ)100個(gè)字符（單字節(jié)或多字節(jié)）。如果該列存放的全是漢字，則占用空間最多為200個(gè)字節(jié)；如果存放的全部是英文字符，則占用空間最多為100個(gè)字節(jié)。ü VARCHAR2（N）或VARCHAR2（N BYTE）：用于定義變長(zhǎng)字符串（以字節(jié)為單位），其最大長(zhǎng)度為4000字節(jié)。ü VARCHAR2（N CHAR）：用于定義變長(zhǎng)字符串（以字符為單位）。ü NUMBER（P，S）：定義數(shù)據(jù)類型的數(shù)據(jù)，P表示數(shù)字的總位數(shù)（最大字節(jié)個(gè)數(shù)，

38、而S表示小數(shù)點(diǎn)后面的位數(shù)。當(dāng)定義整數(shù)類型時(shí)，可以直接使用NUMBER的子類型INT。ü DATE ：定義日期時(shí)間數(shù)據(jù)，其長(zhǎng)度為7個(gè)字節(jié)。當(dāng)查詢DATE類型列時(shí)，其數(shù)據(jù)的默認(rèn)顯示格式為（DDMONYY），如“294月05”。ü TIMESTAMP：是DATE的擴(kuò)展，在該數(shù)據(jù)類型上執(zhí)行DML操作與DATE類型完全相同。但當(dāng)查詢時(shí)，數(shù)據(jù)的顯示格式為（DDMONYY HH.MI.SS AM），如“294月03 04.02.03.000000 下午”。ü RAW(N)：定義二進(jìn)制數(shù)據(jù)，N的上限值為2000。ü 大對(duì)象數(shù)據(jù)類型：早期版本（6，7）中，存儲(chǔ)大批量字符數(shù)

39、據(jù)采用數(shù)據(jù)類型LONG，存儲(chǔ)大批量二進(jìn)制數(shù)據(jù)采用LONG RAW類型。從8版本開始，建議使用CLOB存儲(chǔ)大批量字符，建議使用BLOB類型存儲(chǔ)大批量二進(jìn)制數(shù)據(jù)。下表列出了這些數(shù)據(jù)類型之間的區(qū)別：LONG、LONG RAWLOB(CLOG、BLOB)表只能有一個(gè)LONG或LONG RAW列表可以有多個(gè)LOB列最大長(zhǎng)度：2GB最大長(zhǎng)度：4GB不支持對(duì)象類型支持對(duì)象類型存放在表段中小于4000：存放在表段中大于4000：存放到LOB段SELECT：直接返回?cái)?shù)據(jù)SELECT：返回定位符列數(shù)據(jù)順序訪問列數(shù)據(jù)可以隨機(jī)訪問ü 偽列ROWID和ROWNUMØ ROWID用于惟一地標(biāo)識(shí)表行，它

40、間接給出了表行的物理位置，并且ROWID是定位表行最快的方式。如果某表包含了完全相同的行數(shù)據(jù)，為了刪除重復(fù)行，那么可以考慮使用ROWID作為條件。當(dāng)使用INSERT語(yǔ)句插入數(shù)據(jù)時(shí)，Oracle會(huì)自動(dòng)生成ROWID，并將其值與表數(shù)據(jù)一起存放到表行。ROWID與表列一樣可以直接查詢，如SELECT dname,rowid FROM dept;Ø ROWNUM用于返回標(biāo)識(shí)行數(shù)據(jù)順序的數(shù)字值。當(dāng)執(zhí)行SELECT語(yǔ)句返回?cái)?shù)據(jù)時(shí)，第1行的ROWNUM為1，第2行的ROWNUM為2，以此類推3.2 建表語(yǔ)法：CREATE TABLE schema.table_name( Column_name d

41、atatype DEFAULT expr, );其中：schema用于指定方案名（與用戶名完全相同），table_name用于指定表名，column_name用于指定列名，datatype用于指定列的數(shù)據(jù)類型，DEFAULT子句用于指定列的默認(rèn)值。（每張表最多可定義1000列）注釋：查詢表結(jié)構(gòu)：DESCü 在當(dāng)前方案中建表CREATE TABLE dept01( dno NUMBER(2),name VARCHAR2(10),loc VARCHAR2(20);ü 在其他方案中建表CREATE TABLE scott.dept02( dno NUMBER(2),name VA

42、RCHAR2(10),loc VARCHAR2(20)；ü 在建表時(shí)為列指定默認(rèn)值CREATE TABLE scott.dept03( dno NUMBER(2),name VARCHAR2(10),loc VARCHAR2(20) DEFAULT 呼和浩特)；ü 使用子查詢建表CREATE TABLE emp04(name,salary,job,dno) ASSELECT ename,sal,job,deptno FROM emp WHERE deptno=30;ü 建立臨時(shí)表臨時(shí)表用于存放會(huì)話或事務(wù)的私有數(shù)據(jù)。臨時(shí)表包括事務(wù)臨時(shí)表和會(huì)話臨時(shí)表兩種類型，其中事務(wù)

43、臨時(shí)表是指數(shù)據(jù)只在當(dāng)時(shí)事務(wù)內(nèi)有效的臨時(shí)表，會(huì)話臨時(shí)表是指數(shù)據(jù)只在當(dāng)前會(huì)話內(nèi)有效的臨時(shí)表。當(dāng)建立臨時(shí)表時(shí)，需要使用CREATE GLOBAL TEMPORARY TABLE命令。通過使用ON COMMIT DELETE ROWS選項(xiàng)可以指定事務(wù)臨時(shí)表，通過使用ON COMMIT PRESERVE ROWS選項(xiàng)可以指定會(huì)話臨時(shí)表，例：CREATE GLOBAL TEMPORARY TABLE temp1(cola INT)ON COMMIT DELETE ROWS說(shuō)明：當(dāng)執(zhí)行了以上語(yǔ)句之后，會(huì)建立事務(wù)臨時(shí)表TEMP1。因?yàn)槭聞?wù)臨時(shí)表的數(shù)據(jù)只在當(dāng)前事務(wù)內(nèi)有效，所以在事務(wù)結(jié)束之后會(huì)自動(dòng)清除其數(shù)據(jù)。3.

44、3 修改表如果表結(jié)構(gòu)不符合實(shí)際情況，建表之后，可用ALTER TABLE改變表結(jié)構(gòu)。l 增加列語(yǔ)法：ALTER TABLE table_name ADD(column datatype DEFAULT expr,column datatype; 如ALTER TABLE emp01 ADD eno NUMBER(4);l 修改列定義語(yǔ)法：ALTER TABLE table_name MODIFY(column datatype DEFAULT expr,column datatype; 如ALTER TABLE emp01 MODIFY job VARCHAR2(15);l 刪除列語(yǔ)法：ALT

45、ER TABLE table_name DROP(column);如：ALTER TABLE emp01 DROP COLUMN dno;l 修改列名語(yǔ)法：ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name;如：ALTER TABLE emp01 RENAME COLUMN eno TO empno;l 修改表名語(yǔ)法：RENAME object_name TO new_object_name;如：RENAME emp01 TO employee;l 增加注釋語(yǔ)法：COMMENT ON TABLE table_n

46、ame IS text; COMMENT ON COLUMN table_name.column IS text;如：COMMENT ON TABLE employee IS 存放雇員信息; COMMENT ON COLUMN IS 描述雇員姓名;3.4 截?cái)嗪蛣h除表l 截?cái)啾懋?dāng)表結(jié)構(gòu)必須保留，而表數(shù)據(jù)不再需要時(shí)，可以使用TRUNCATE TABLE命令截?cái)啾?。?zhí)行此命令時(shí)，會(huì)刪除表的所有數(shù)據(jù)，并釋放表所占用的空間，但會(huì)保留表的結(jié)構(gòu)。語(yǔ)法：TRUNCATE TABLE table_name;說(shuō)明：當(dāng)刪除表的所有數(shù)據(jù)時(shí)，既可以使用DELETE語(yǔ)句，也可以使用TRUNC

47、ATE TABLE命令。注意的是，DELETE（DML）操作可以回退，但TRUNCATE TABLE（DDL）操作不能回退。如：TRUNCATE TABLE employee;l 刪除表當(dāng)表不再需要時(shí)，可以使用DROP TABLE命令刪除表。用此命令，不僅會(huì)刪除表的所有數(shù)據(jù)，而且會(huì)刪除表結(jié)構(gòu)。語(yǔ)法：DROP TABLE table_name CASCADE CONSTRAINTS PURGE;CASCADE CONSTRAINTS用于指定級(jí)聯(lián)刪除從表的外鍵約束，PURGE用于指定徹底刪除表（這個(gè)選項(xiàng)是10g的新特征）如：DROP TABLE employee;l 恢復(fù)被刪除表當(dāng)執(zhí)行DROP T

48、ABLE語(yǔ)句刪除表時(shí)，Oracle會(huì)將被刪除表存放到數(shù)據(jù)庫(kù)回收站。從Oracle Database 10g開始，使用FLASHBACK TABLE命令可以恢復(fù)被刪除表。語(yǔ)法：FLASHBACK TABLE table_name TO BEFORE DROP;3.5 顯示表信息l USER_TABLES當(dāng)建立表時(shí)，Oracle會(huì)將表信息存放到數(shù)據(jù)字典。通過查詢數(shù)據(jù)字典視圖USER_TABLES，可以顯示當(dāng)前用戶的所有表信息。如：conn scott/tigerSELECT table_name FROM user_tables;l USER_OBJECTS當(dāng)建立數(shù)據(jù)庫(kù)對(duì)象（表、視圖、索引等）時(shí)，

49、Oracle會(huì)將對(duì)象信息存放到數(shù)據(jù)字典中。通過查詢數(shù)據(jù)字典視圖USER_OBJECTS，可顯示所有數(shù)據(jù)庫(kù)對(duì)象。如：SELECT object_name FROM user_objects WHERE object_type=TABLE;l USER_TAB_COMMENTS當(dāng)執(zhí)行COMMENT命令為表、視圖增加注釋信息時(shí)，Oracle會(huì)將注釋存放到數(shù)據(jù)字典中。通過查詢數(shù)據(jù)字典視圖USER_TAB_COMMENTS，可以顯示當(dāng)前用戶所有表的注釋。如：SELECT comments FROM user_tab_comments WHERE table_name=EMPLOYEE;l USER_CO

50、L_COMMENTS當(dāng)執(zhí)行COMMENT命令為列增加注釋信息時(shí)，Oracle會(huì)將注釋存放到數(shù)據(jù)字典中。通過查詢數(shù)據(jù)字典視圖USER_COL_COMMENTS，可以顯示當(dāng)前用戶所有表的列注釋。如：SELECT comments FROM user_col_comments WHERE table_name=EMPLOYEE AND column_name=NAME;注釋：表名及列名均大寫，因?yàn)镺racle中是以大寫字母存儲(chǔ)對(duì)象名及列名第4章 Oracle操作符Oracle中有很多的操作符，每種操作符都有自己的含義，在使用時(shí)需要很好的理解其中的內(nèi)涵。這些操作符與平時(shí)大家見到的一些操作符幾乎是一樣的

51、，含義也差不多。需要注意一點(diǎn)的是，oracle中的賦值語(yǔ)句用:=的方式，而不是=。第5章 基本查詢l 簡(jiǎn)單查詢語(yǔ)句l 限制數(shù)據(jù)l 排序數(shù)據(jù)5.1 簡(jiǎn)單查詢語(yǔ)句查詢所有列：select * from dept;查詢指定列：desc emp(顯示表結(jié)構(gòu)) select empno,ename,sal from emp;查詢?nèi)掌诹校喝掌诹惺侵竏ate類型列，默認(rèn)顯示格式為dd-mon-yy，不同語(yǔ)言和地區(qū)的日期顯示結(jié)果有所不同。如果想以自己習(xí)慣的日期格式顯示日期值，必須要用to_char函數(shù)進(jìn)行轉(zhuǎn)換。當(dāng)日期語(yǔ)言為SIMPLIFIEDCHINESE時(shí)，格式：17-12月-80 Select ename

52、,to_char(hiredate,YYYY-MM-DD from emp; 上述語(yǔ)句顯示格式：1980-12-17取消重復(fù)行：默認(rèn)會(huì)顯示所有行，但完全相同的查詢結(jié)果沒有實(shí)際意義，因此有時(shí)需要取消重復(fù)結(jié)果 Select distinct deptno,job from emp;(顯示三列都不相同的記錄集) Select distinct deptno;(只顯示deptno不同的記錄集)使用算術(shù)表達(dá)式：當(dāng)招行查詢操作時(shí)，可在數(shù)字列上用算術(shù)表達(dá)式(+,-,*,/) Select ename,sal,sal*12 from emp;使用列別名：默認(rèn)情況下，列標(biāo)題是大寫格式的列名或表達(dá)式，如果使用列別

53、名，列別名可跟在列名后，并且在二者之間可以加AS關(guān)鍵字，若列別名區(qū)分大小寫、包含特殊字符或空格，必須用雙引號(hào)引住 Select ename as name,sal*12 “Annual Salary” from emp;處理NULL：NULL表示未知值，既不是空格也不是0。若沒為列提供數(shù)據(jù)且該列無(wú)默認(rèn)值，則其數(shù)據(jù)為NULL。當(dāng)算術(shù)表達(dá)式包含NULL時(shí)，其結(jié)果也是NULL。 Select ename,sal,comm,sal+comm from emp; Select ename,sal,comm,sal+nvl(comm,0) from emp; Nvl(comm,0)說(shuō)明：如果comm存在數(shù)

54、值，則函數(shù)返回其原有數(shù)值；如果comm列為NULL，則函數(shù)返回0。 連接字符串：連接字符串是使用|操作符完成的。如果在字符串中要加入數(shù)字值，那么在|后可以直接指定數(shù)字；如果在字符串中加入字符和日期值，則必須用單引號(hào)引住。 Select ename|的崗位是|job “Employee” from emp; 或Select ename|的崗位是|job AS Employee from emp;注釋：dual是一個(gè)虛擬表，用來(lái)查那些不屬于實(shí)際表里的內(nèi)容，如：select   sysdate   from   dual;  select   3+3   from   dual;1 限制數(shù)據(jù)（條件查詢）條件查詢中條件表達(dá)式中需要使用各種比較操作符，如（=，<>(!=)，>=，<=，>，<，BETWEENAND，IN(list)，LIKE，IS NULL）使用數(shù)字值：select ename,sal from emp where sal>2000;使用字符值：select ename,sal from emp where