ORACLE日常操作手冊(cè)

1、ORACLE日常操作手冊(cè)目錄一、數(shù)據(jù)庫(kù)的啟動(dòng)和關(guān)閉.41. 數(shù)據(jù)庫(kù)的正常啟動(dòng)步驟.42. 數(shù)據(jù)庫(kù)的正常關(guān)閉步驟.43. 幾種關(guān)閉數(shù)據(jù)庫(kù)方法對(duì)比.44. 數(shù)據(jù)庫(kù)的啟動(dòng)關(guān)閉過(guò)程.4二、創(chuàng)建數(shù)據(jù)庫(kù)用戶.51、 以DBA用戶登錄數(shù)據(jù)庫(kù)（如system,sys）.52、 用create user語(yǔ)法創(chuàng)建用戶.53、 賦表空間使用權(quán)限.54、 給用戶賦權(quán)限.55、 刪除用戶.5三、ORACL常用的數(shù)據(jù)類型.5四、基本的SQL語(yǔ)句的寫法.61、 rowid和rownum的區(qū)別.6 刪除表中重復(fù)記錄.6 使表處于可編輯狀態(tài).6 批量刪除記錄.7 分頁(yè)查詢.72、 delete和truncate 、drop的區(qū)

2、別.73、 多表關(guān)聯(lián)查詢.7不等連接實(shí)例.7 查詢員工的工資等級(jí).7內(nèi)連接實(shí)例.8 查詢?cè)敿?xì)信息記錄.8 關(guān)聯(lián)更新和刪除.8外連接實(shí)例.8 查詢沒(méi)有附件信息記錄.8自連接實(shí)例.8 查詢員工和主管之間的關(guān)系.8 遞歸查詢.84、 子查詢.8單行子查詢實(shí)例.9 查詢內(nèi)容大小大于平均大小的記錄.9 在having子句中使用子查詢.9 在from子句中使用子查詢（內(nèi)聯(lián)視圖）.9 可能碰到的兩個(gè)錯(cuò)誤.9多行子查詢實(shí)例.9 在多行查詢中使用in操作符.9 在多行子查詢中使用any操作符.10 在多行子查詢中使用all操作符.10多例子查詢實(shí)例.10 檢索每種產(chǎn)品類型中價(jià)格最低的產(chǎn)品.10關(guān)聯(lián)子查詢實(shí)例.1

3、0 在關(guān)聯(lián)子查詢中exists10 在關(guān)聯(lián)子查詢中not exists10 Exists和not exists與in和not in的比較.10嵌套子查詢實(shí)例.11 多層嵌套子查詢.115、 使用集合操作符.12 Union all使用實(shí)例.12 Union使用實(shí)例.12 Intersect使用實(shí)例.13 Minus使用實(shí)例.136、 Decode函數(shù)和Case表達(dá)式的比較.13 Decode函數(shù)使用實(shí)例.13 Case表達(dá)式使用實(shí)例.147、 其它.15五、日期和時(shí)間的存儲(chǔ)與處理.151、 常用的幾個(gè)日期函數(shù)說(shuō)明.152、 常用的日期計(jì)算實(shí)例.15 取得當(dāng)前日期是本月的第幾周.15 取得當(dāng)前日

4、期是一個(gè)星期中的第幾天,注意星期日是第一天.15 取當(dāng)前日期是星期幾中文顯示:16 如果一個(gè)表在一個(gè)date類型的字段上面建立了索引，如何使用.16 得到當(dāng)前的日期.16 得到當(dāng)天凌晨0點(diǎn)0分0秒的日期.16 得到這天的最后一秒.16 得到小時(shí)的具體數(shù)值.16 得到明天凌晨0點(diǎn)0分0秒的日期.17 本月一日的日期.17 得到下月一日的日期.17 返回當(dāng)前月的最后一天.17 得到一年的每一天.17 如何在給現(xiàn)有的日期加上2年.18 判斷某一日子所在年分是否為潤(rùn)年.18 判斷兩年后是否為潤(rùn)年.18 得到日期的季度.18六、SQL語(yǔ)句的優(yōu)化寫法.181、 oracle訪問(wèn)Table的方式.182、

5、創(chuàng)建索引.19 創(chuàng)建普通索引實(shí)例.19 創(chuàng)建全文索引實(shí)例.19 創(chuàng)建主建.203、 SQL優(yōu)化實(shí)例及問(wèn)題.20 使用like操作符的問(wèn)題.20 選擇最有效率的表名順序(只在基于規(guī)則的優(yōu)化器中有效)20 WHERE子句中的連接順序.21 SELECT子句中避免使用*21 減少訪問(wèn)數(shù)據(jù)庫(kù)的次數(shù).21 盡量多使用COMMIT.22 減少對(duì)表的查詢.22 通過(guò)內(nèi)部函數(shù)提高SQL效率.23 使用表的別名(Alias)24 用EXISTS替代IN和用NOT EXISTS替代NOT IN.24 用表連接替換EXISTS.24 用EXISTS替換DISTINCT.24 等式比較和范圍比較.25 不明確的索引等

6、級(jí).25 強(qiáng)制索引失效.26 避免在索引列上使用計(jì)算.26 自動(dòng)選擇索引.27 避免在索引列上使用NOT.27 避免在索引列上使用IS NULL和IS NOT NULL.28 總是使用索引的第一個(gè)列.28七、常見(jiàn)的數(shù)據(jù)庫(kù)管理和優(yōu)化配置.291、 數(shù)據(jù)庫(kù)的備份.29 導(dǎo)出/導(dǎo)入(Export/Import)29 rman備份實(shí)例.302、 數(shù)據(jù)庫(kù)的參數(shù)配置及性能調(diào)整.31 如何增加ORACLE連接數(shù).31 關(guān)于內(nèi)存參數(shù)的調(diào)整.31 32bit 和 64bit 的問(wèn)題.32 Linux上shmmax參數(shù)的設(shè)置及含義.32 解決CPU高度消耗(100%)的數(shù)據(jù)庫(kù)問(wèn).333、 存儲(chǔ)管理.35 創(chuàng)建表空

7、間.36 管理表空間.36 管理數(shù)據(jù)文件.36 查看表空間的使用情況.37本文檔約定：1、 文中的數(shù)據(jù)庫(kù)主要用到了公司cms、pms庫(kù)結(jié)構(gòu)2、 所有SQL都實(shí)際的測(cè)試通過(guò)，放心使用。3、 如果有再需要了解的部分以后可以再做補(bǔ)充。一、 數(shù)據(jù)庫(kù)的啟動(dòng)和關(guān)閉1. 數(shù)據(jù)庫(kù)的正常啟動(dòng)步驟l 以DBA的身份登錄數(shù)據(jù)庫(kù)（要在oracle安裝用戶下執(zhí)行sqlplus）oracleDB1 $sqlplus “/as sysdba”l 執(zhí)行啟動(dòng)數(shù)據(jù)庫(kù)命令SQLstartupORACLE instance started.Total System Global Area 285212672 bytesFixed S

8、ize 1218968 bytesVariable Size 88082024 bytesDatabase Buffers 188743680 bytesRedo Buffers 7168000 bytesDatabase mounted.Database opened.l 啟動(dòng)和關(guān)閉監(jiān)聽(tīng)oracleDB1 $lsnrctl startoracleDB1 $lsnrctl stop2. 數(shù)據(jù)庫(kù)的正常關(guān)閉步驟l 同樣以DBA的身份登錄數(shù)據(jù)庫(kù)oracleDB1 $sqlplus “/as sysdba”l 執(zhí)行數(shù)據(jù)庫(kù)關(guān)閉命令SQLshutdown immediate;Database closed

9、.Database dismounted.ORACLE instance shut down.3. 幾種關(guān)閉數(shù)據(jù)庫(kù)方法對(duì)比SHUTDOWN有四個(gè)參數(shù)：NORMAL、TRANSACTIONAL、IMMEDIATE、ABORT。缺省不帶任何參數(shù)時(shí)表示是NORMAL。SHUTDOWN NORMAL：不允許新的連接、等待會(huì)話結(jié)束、等待事務(wù)結(jié)束、做一個(gè)檢查點(diǎn)并關(guān)閉數(shù)據(jù)文件。啟動(dòng)時(shí)不需要實(shí)例恢復(fù)，這種方法往往不能關(guān)閉數(shù)據(jù)庫(kù)或等待很長(zhǎng)時(shí)間。SHUTDOWN TRANSACTIONAL：不允許新的連接、不等待會(huì)話結(jié)束、等待事務(wù)結(jié)束、做一個(gè)檢查點(diǎn)并關(guān)閉數(shù)據(jù)文件。啟動(dòng)時(shí)不需要實(shí)例恢復(fù)。SHUTDOWN IMMED

10、IATE：不允許新的連接、不等待會(huì)話結(jié)束、不等待事務(wù)結(jié)束、做一個(gè)檢查點(diǎn)并關(guān)閉數(shù)據(jù)文件。沒(méi)有結(jié)束的事務(wù)是自動(dòng)rollback的。啟動(dòng)時(shí)不需要實(shí)例恢復(fù)。最常用的方法。SHUTDOWN ABORT：不允許新的連接、不等待會(huì)話結(jié)束、不等待事務(wù)結(jié)束、不做檢查點(diǎn)且沒(méi)有關(guān)閉數(shù)據(jù)文件。啟動(dòng)時(shí)自動(dòng)進(jìn)行實(shí)例恢復(fù)。一般不推薦采用，只有在數(shù)據(jù)庫(kù)無(wú)法關(guān)閉時(shí)使用，可能造成數(shù)據(jù)庫(kù)的不一致。4. 數(shù)據(jù)庫(kù)的啟動(dòng)關(guān)閉過(guò)程二、 創(chuàng)建數(shù)據(jù)庫(kù)用戶1、 以DBA用戶登錄數(shù)據(jù)庫(kù)（如system,sys）SQLconn system/oracleorcl注：如果在本地服務(wù)器登錄orcl可以去掉2、 用create user語(yǔ)法創(chuàng)建用戶CRE

11、ATE USER user_name IDENTIFIED BY user_passwordDefaultTablespace tbs_users;l user_name為數(shù)據(jù)庫(kù)用戶的用戶名l user_password為數(shù)據(jù)庫(kù)用戶的密碼l tbs_users為用戶使用的表空間，默認(rèn)是users表空間。例如：CREATE USER cmsuser IDENTIFIED BY passwordDefaultTablespace users;3、 賦表空間使用權(quán)限alter user user_name quota unlimited on user_tablespace quota unlimi

12、ted on user_tablespace;4、 給用戶賦權(quán)限GRANT connect, resource TO cmsuser;l Connect用戶能登錄數(shù)據(jù)庫(kù)的權(quán)限l Resource用戶能創(chuàng)建一些數(shù)據(jù)庫(kù)對(duì)像的權(quán)限，表、視圖，存儲(chǔ)過(guò)程，一般是授予開(kāi)發(fā)人員的5、 刪除用戶DropUser cmsuser Cascade;l 使用cascade參數(shù)可以刪除該用戶的全部objects三、 ORACL常用的數(shù)據(jù)類型l INTEGER存儲(chǔ)整數(shù)，整數(shù)不包括浮點(diǎn)數(shù);它是一個(gè)整數(shù)數(shù)字，如：1、10、15l NUMBER，是以十進(jìn)制格式進(jìn)行存儲(chǔ)的，它便于存儲(chǔ)，但是在計(jì)算上，系統(tǒng)會(huì)自動(dòng)的將它轉(zhuǎn)換成為二進(jìn)

13、制進(jìn)行運(yùn)算的。它的定義方式是NUMBER(P，S)，P是精度，最大38位，S是刻度范圍，可在-84.127間取值。例如：NUMBER(5，2)可以用來(lái)存儲(chǔ)表示-999.99.999.99間的數(shù)值。P、S可以在定義是省略，例如：NUMBER(5)、NUMBER等;l CHAR，描述定長(zhǎng)的字符串，如果實(shí)際值不夠定義的長(zhǎng)度，系統(tǒng)將以空格填充。它的聲明方式如下CHAR(L)，L為字符串長(zhǎng)度，缺省為1，作為變量最大32767個(gè)字符，作為數(shù)據(jù)存儲(chǔ)在ORACLE8中最大為2000。l VARCHAR2(VARCHAR)，描述變長(zhǎng)字符串。它的聲明方式如下VARCHAR2(L)，L為字符串長(zhǎng)度，沒(méi)有缺省值，作為

14、變量最大32767個(gè)字節(jié)，作為數(shù)據(jù)存儲(chǔ)在ORACLE8中最大為4000。在多字節(jié)語(yǔ)言環(huán)境中，實(shí)際存儲(chǔ)的字符個(gè)數(shù)可能小于L值，例如：當(dāng)語(yǔ)言環(huán)境為中文(SIMPLIFIED CHINESE_CHINA.ZHS16GBK)時(shí)，一個(gè)VARCHAR2(200)的數(shù)據(jù)列可以保存200個(gè)英文字符或者100個(gè)漢字字符。l NCHAR、NVARCHAR2，國(guó)家字符集，與環(huán)境變量NLS指定的語(yǔ)言集密切相關(guān)，使用方法和CHAR、VARCHAR2相同。不過(guò)最大參數(shù)為NCHAR(2000)、NVARCHAR2(2000)l DATE唯一的一種日期類型-，用來(lái)存儲(chǔ)時(shí)間信息，站用7個(gè)字節(jié)(從世紀(jì)到秒)l LOB（oracl

15、e8以前叫l(wèi)ong）變量主要是用來(lái)存儲(chǔ)大量數(shù)據(jù)的數(shù)據(jù)庫(kù)字段，最大可以存儲(chǔ)4G字節(jié)的內(nèi)容，CLOB：存儲(chǔ)單字節(jié)字符數(shù)據(jù)（如英文）NCLOB：用來(lái)存儲(chǔ)定寬多字節(jié)字符數(shù)據(jù)（如漢字），BLOB：用來(lái)存儲(chǔ)無(wú)結(jié)構(gòu)的二進(jìn)制數(shù)據(jù)（word、pdf文檔）。四、 基本的SQL語(yǔ)句的寫法1、 rowiddelete和rownum的區(qū)別rowid是Oracle數(shù)據(jù)庫(kù)中的每一行都有一個(gè)唯一的行標(biāo)識(shí)符，稱為rowid,它是一個(gè)18位數(shù)字，以64為基數(shù)，該徝包含了該行在oracle數(shù)據(jù)庫(kù)中的物理位置，查詢r(jià)owid如下：SQL Select rowid,id From infobase Where Rownum 5;ROW

16、ID ID AAAYKRAAEAAGGpcAAI 1000000AAAYKRAAEAAGGpcAAJ 1000001AAAYKRAAEAAGGpcAAK 1000002AAAYKRAAEAAGGpcAAL 1000003Rowid應(yīng)用實(shí)例： 刪除表中重復(fù)記錄DeleteFrom Infobase aWhere Rowid Select Rownum,id From infobase Where Rownum 5;ROWNUM ID 1 10000002 10000013 10000024 1000003rownum應(yīng)用實(shí)例： 批量刪除記錄如果要?jiǎng)h除的數(shù)據(jù)量很大，一次刪除可能需要占用系統(tǒng)大量的內(nèi)

17、存，給數(shù)據(jù)庫(kù)帶來(lái)很大的壓力，可以進(jìn)行分步批量刪除并提交，避免這種情況createor replace procedure del_dataas -創(chuàng)建過(guò)程并執(zhí)行beginfor i in 1.1000loopdelete from cmsuser_base Where posterid=Servlet提交and rownum 100;commit;end loop;End del_data; 分頁(yè)查詢Select *From (Select * From InfobaseOrder By OriginaltimeDesc)Where Rownum = 10;2、 delete和truncate

18、、drop的區(qū)別TRUNCATE TABLE 在功能上與不帶WHERE子句的DELETE語(yǔ)句相同：二者均刪除表中的全部行。但 TRUNCATE TABLE比 DELETE速度快，且使用的系統(tǒng)和事務(wù)日志資源少。Drop 則是刪除整個(gè)表，與TRUNCATE操作類型相同，都是DDL操作（數(shù)據(jù)定義語(yǔ)言）DeleteFrom infobase Id = 1;Commit;-或Delete infobase Where Id = 1;Commit;TruncateTable infobase;DropTable infobase;3、 多表關(guān)聯(lián)查詢根據(jù)連接中使用操作符的不同，連接條件可以分為兩類：l 等連

19、接：在連接中使用等于操作符(=)l 不等連接：在連接中使用除等號(hào)之外的操作符，如：、between等除連接條件區(qū)分之外，連接本身也有3種不同的類型：l 內(nèi)連接：只有當(dāng)連接中的列包含滿足連接條件的值時(shí)才會(huì)返回一行。這就是說(shuō)，如果某一行的連接條件中的一列是空值，那么這行就不返回。l 外連接：即使連接條件中的一列包含空值也會(huì)返回一行。l 自連接：返回連接的同一個(gè)表中的行。不等連接實(shí)例 查詢員工的工資等級(jí)Select e.first_name,e.title,e.salary,sg.salary_grade_idFrom employees e,salary_grades sgWhere e.sala

20、ry Between sg.low_salary And sg.high_salary;-employees員工表，salary_grades工資等級(jí)表內(nèi)連接實(shí)例 查詢?cè)敿?xì)信息記錄Select a.Id, b.Name, a.Title, a.ContentFrom Infobase aJoin Class b On a.Classid = ( | b.Id | );-或Select a.Id, b.Name, a.Title, a.ContentFrom Infobase a Class bWhere a.Classid = ( | b.Id |); 關(guān)聯(lián)更新和刪除Update Infoba

21、se aSet a.Title = (Select Title From Infobase_TempWhere Id = a.Id);Commit;Delete From Infobase aWhere Exists ( Select 1 From Class bWhere a.classid = b.id);Commit;外連接實(shí)例 查詢沒(méi)有附件信息記錄Select a.*From Infobase aLeft Join Attachment b On a.Id = b.InfoidWhere b.Infoid Is Null;-這是典型兩表相減查詢，也可用not in，但是種寫法效率會(huì)高些

22、-這是一個(gè)左外連接例子，右外連接跟左外連接一樣，只是表的位置不同自連接實(shí)例 查詢員工和主管之間的關(guān)系Select w.Last_Name | works for | m.Last_NameFrom Employees m, Employees wWhere w.Employee_Id = m.Manager_Id; 遞歸查詢下面的語(yǔ)法也可以看做成一個(gè)隱含的自連接查詢，它是一個(gè)字列和父列的遞歸查詢Select *From ClassStart With Parentid = 0001Connect By Prior Id = Parentid-id,parentid那么通過(guò)表示每一條記錄的par

23、ent是誰(shuí)，就可以形成一個(gè)樹(shù)狀結(jié)構(gòu)-Parentid = 0001指定樹(shù)的根從哪個(gè)節(jié)點(diǎn)開(kāi)始4、 子查詢子查詢有兩種基本類型：l 單行子查詢：不向外部的SQL返回結(jié)果，或者只返回一行。l 多行子查詢：向外部的SQL返回一行或多行。另外子查詢還有三種子類型：l 多列子查詢：向外部的SQL語(yǔ)句返回多列。l 關(guān)聯(lián)子查詢：引用外的SQL語(yǔ)句中的一錢或多列。l 嵌套子查詢：位于另外一個(gè)子查詢中。子查詢最多可以嵌套255層。單行子查詢實(shí)例 查詢內(nèi)容大小大于平均大小的記錄Select *From InfobaseWhere Contentsize (Select Avg(Contentsize) From I

24、nfobase); 在having子句中使用子查詢檢索那些平均價(jià)格低于同類產(chǎn)品平均價(jià)格最大值的產(chǎn)品的product_type_id和平均價(jià)格：Select Product_Type_Id,Avg(Price)From ProductsGroup By Product_Type_IdHaving Avg(Price) (Select Max(Avg(Price)From ProductsGroup By Product_Type_Id); 在from子句中使用子查詢（內(nèi)聯(lián)視圖）就外部查詢的from子句而言，子查詢的輸出僅僅是另外一個(gè)數(shù)據(jù)源。檢索Productid大于100的產(chǎn)品Select Pr

25、oductidFrom (Select Productid From ProductWhere Productid Select Productid, Productname2 From Product3 Where Productid =4 (Select Productid From Product Where Productname Like 恒泰%);Select Productid, ProductnameORA-01427:單行子查詢返回多于一個(gè)行(2)、子查詢不能包含order by子句，必須在外查詢中進(jìn)行任何排序多行子查詢實(shí)例 在多行查詢中使用in操作符檢索信息表里符合clas

26、sid條件的記錄：Select *From InfobaseWhere Classid In(Select ( |Id |)From Class Where Name Like營(yíng)業(yè)部%) 在多行子查詢中使用any操作符檢查是否有任何員工的工資低于salary_grades表中任何一級(jí)的最低工資：Select e.Employee_Id, e.Last_NameFrom Employees eWhere e.Salary All (Select sg.high_salaryFrom Salary_Grades Sg);多例子查詢實(shí)例 檢索每種產(chǎn)品類型中價(jià)格最低的產(chǎn)品Select *From Pr

27、oductsWhere (Product_Type_Id, Price) In(Select Product_Type_Id, Min(Price)From ProductsGroup By Product_Type_Id);-上面的寫法也如同下面的寫法，返回結(jié)果一樣Select *From Products aWhere Price = (Select Min(Price)From ProductsWhere Product_Type_Id = a.Product_Type_Id);-注意：這個(gè)例子是日常的開(kāi)發(fā)很典型的例子，會(huì)經(jīng)常用到，一定要學(xué)會(huì)應(yīng)用關(guān)聯(lián)子查詢實(shí)例 在關(guān)聯(lián)子查詢中exists

28、檢索那些負(fù)責(zé)管理其它員工的員工記錄：Select Employee_Id, Last_NameFrom EmployeesOuter Where Exists(Select Employee_IdFrom Employees InnerInner Where Inner.Manager_Id = Outer.Employee_Id); 在關(guān)聯(lián)子查詢中not exists檢索從未購(gòu)買過(guò)的產(chǎn)品Select Product_Id,NameFrom Products aWhere Not Exists (Select 1 From Purchases Where Product_Id = a.Prod

29、uct_Id)- 子句的1是個(gè)虛擬列，沒(méi)有意義，改成其它值也可以 Exists和not exists與in和not in的比較Exists與in不同，Exists只檢查行的存在性，而in則要檢查實(shí)際值的存在性。通常來(lái)講，Exists的性能要比in要高一些，因此應(yīng)該盡可能地使用Exists，而不用in。在編寫使用Not Exists和Not in的查詢時(shí)必須要謹(jǐn)慎。當(dāng)一個(gè)值列表包含一個(gè)空值時(shí)，Not Exists就返回true,而Not in 則返回false。考慮下面這個(gè)例子：本例使用了Not Exists，檢索那些在products表中沒(méi)有任何產(chǎn)品的產(chǎn)品類型：Select Product_T

30、ype_Id,NameFrom Product_Types aWhere Not Exists(Select 1From ProductsWhere Product_Type_Id = a.Product_Type_Id);PRODUCT_TYPE_ID NAME 5 Magazine注意上面這個(gè)例子返回了一行記錄。下面這個(gè)例子使用Not in重寫了上面這個(gè)例子，而此時(shí)沒(méi)有返回任何行：Select Product_Type_Id,NameFrom Product_Types aWhere Product_Type_Id Not In (Select Product_Type_IdFrom Pr

31、oducts);PRODUCT_TYPE_ID NAME 這所以沒(méi)有返回行，就是因?yàn)樽硬樵兎祷豍roduct_Type_Id值的列表，其中包含一個(gè)空值。而產(chǎn)品#12的Product_Type_Id是空值。因此外部查詢中的Not in操作符返回false,因此沒(méi)有任何行。這個(gè)問(wèn)題可以使用Nvl()函數(shù)將空值轉(zhuǎn)換成一個(gè)值解決。下面的例子中，Nvl()函數(shù)將空值的Product_Type_Id轉(zhuǎn)換成0：Select Product_Type_Id,NameFrom Product_Types aWhere Product_Type_Id Not In (Select nvl(Product_Type

32、_Id,0)From Products);PRODUCT_TYPE_ID NAME 5 Magazine這次返回了我們要得到的那行記錄。嵌套子查詢實(shí)例 多層嵌套子查詢?cè)谧硬樵儍?nèi)部可以嵌套其它子查詢，嵌套層次最多為255。在編寫時(shí)應(yīng)該盡量少使用嵌套子查詢技術(shù)，因?yàn)槭褂帽磉B接時(shí)，查詢性能會(huì)更高。下面的例子包含了一個(gè)嵌套子查詢，子查詢包含了另外一個(gè)子查詢，而它自己又被包含在一個(gè)外部查詢中：Select Product_Type_Id,Avg(Price)From ProductsGroup By Product_Type_IdHaving Avg(Price) = 1)Group By Produc

33、t_Type_Id);這個(gè)查詢包含了3個(gè)查詢：一個(gè)嵌套子查詢、一個(gè)子查詢和一個(gè)外部查詢?？梢杂衫锏酵庾约褐鸩椒治?，得到運(yùn)行結(jié)果。5、 使用集合操作符集合操作符可以將兩個(gè)或多個(gè)查詢返回的行組合起來(lái)，當(dāng)使用集合操作符的時(shí)候，必須牢記下列的限制條件：所有查詢所返回的列數(shù)以及列的類型必須匹配，列名可以不同。集合操作符主要有：l Union all 返回各個(gè)查詢檢索出的所有行，包括重復(fù)的行。l Union 返回各個(gè)查詢檢索出的所有行，不包括重復(fù)的行。l Intersect 返回兩個(gè)查詢共有行。l Minus 返回第二個(gè)查詢檢索出的行從第一個(gè)查詢檢索出的行中減去之后剩余的記錄。 Union all使用實(shí)例

34、Union all 返回各個(gè)查詢檢索出的所有行，包括重復(fù)的行SelectId,classidFrom infobase Where Rownum 5Union AllSelect Id,classid From infobase_temp;ID CLASSID 1000000 (000100010002)1000001 (000200020003000100010002)1000002 (000100010003)1000005 (00010001000400030007)1000000 (000100010002)1000001 (000200020003000100010002)可以使用o

35、rder by子句根據(jù)兩個(gè)查詢中的列的位置對(duì)列進(jìn)行排序。SelectId,classidFrom infobase Where Rownum 5Union AllSelect Id,classid From infobase_tempOrder By 1;ID CLASSID 1000000 (000100010002)1000000 (000100010002)1000001 (000200020003000100010002)1000001 (000200020003000100010002)1000002 (000100010003)1000005 (000100010004000300

36、07) Union使用實(shí)例返回各個(gè)查詢檢索出的所有行，不包括重復(fù)的行。因?yàn)閁nion查詢時(shí)要有排重操作，所以Union all要比Union操作效率要高一些。SelectId,classidFrom infobase Where Rownum 5UnionSelect Id,classid From infobase_temp;ID CLASSID 1000000 (000100010002)1000001 (000200020003000100010002)1000002 (000100010003)1000005 (00010001000400030007) Intersect使用實(shí)例In

37、tersect 返回兩個(gè)查詢共有行只檢索出那些infobase與infobase_temp共有的行SelectId,classidFrom infobase Where Rownum 5intersectSelect Id,classid From infobase_temp;ID CLASSID 1000000 (000100010002)1000001 (000200020003000100010002) Minus使用實(shí)例Minus 返回第二個(gè)查詢檢索出的行從第一個(gè)查詢檢索出的行中減去之后剩余的記錄.下例是從infobase返回的行中減去從infobase_temp中返回的行，然后返回剩

38、余的行：SelectId,classidFrom infobase Where Rownum Select decode(1,1,2,3) From dual;DECODE(1,1,2,3)2因?yàn)閷?duì)1與1進(jìn)行比較，由于兩者相等，所以返回2（否則返回3）Decode通常在寫SQL時(shí)與dual表結(jié)合給變量賦值。下面這個(gè)例子對(duì)more_products中的available列進(jìn)行比較。如果available等于Y，返回字符串Product is available,否則返回字符串Product is not available:Select Prd_Id,Available,Decode(Avail

39、able,Y,Product is available,Product is not available)From More_Products;PRD_ID AVAILABLE DECODE(AVAILABLE,Y,PRODUCTI 1 Y Product is available2 Y Product is available3 N Product is not available4 N Product is not available5 Y Product is available可以向Decode()傳遞多個(gè)搜索和結(jié)果參數(shù)，如下例：Select Product_Id,Product_Ty

40、pe_Id,Decode(Product_Type_Id, 1,Book,2, Video,3,Dvd,CD)From Products;如果Product_Type_Id=1，返回Book如果Product_Type_Id=2，返回Video如果Product_Type_Id=3，返回Dvd如果Product_Type_Id等于其它值，返回CD Case表達(dá)式使用實(shí)例case允許if-then-else類型的邏輯處理，而不需要使用pl/sql。Case的工作方式與Decode()類似，通常我們?cè)谟休^少的判斷時(shí)使用decode，因?yàn)闂l件多話會(huì)看著很混亂；所以盡量使用case，它與ANSI兼容。

41、有兩種類型的case表達(dá)式：l 簡(jiǎn)單case表達(dá)式，使用表達(dá)式確定返回值。l 搜索case表達(dá)式，使用條件確定返回值。使用簡(jiǎn)單表達(dá)式例子：Select Product_Id,Product_Type_Id,Case Product_Type_IdWhen 1Then BookWhen 2Then VideoWhen 3Then DvdElse CDEndFrom Products;PRODUCT_ID PRODUCT_TYPE_ID CASEPRODUCT_TYPE_IDWHEN1THENB 1 1 Book2 1 Book3 2 Video4 2 Video5 2 Video6 2 Vide

42、o7 3 Dvd8 3 Dvd9 4 CD使用搜索case表達(dá)式Select Product_Id,Product_Type_Id,CaseWhen Product_Type_Id = 1Then BookWhen Product_Type_Id = 2 Then VideoWhen Product_Type_Id = 3 Then DvdElse CDEndFrom Products;返回結(jié)果中上面是一樣的7、 其它五、 日期和時(shí)間的存儲(chǔ)與處理1、 常用的幾個(gè)日期函數(shù)說(shuō)明l MONTHS_BETWEEN兩日期相差多少月l ADD_MONTHS 加月份到日期l NEXT_DAY 指定日期的下一

43、天l LAST_DAY 一個(gè)月中的最后一天l ROUND Round日期l TRUNC Truncate日期l TO_CHAR(x,format)函數(shù)用于將時(shí)間值轉(zhuǎn)換為字符串，該函數(shù)還可以提供一個(gè)可選的參數(shù)format來(lái)說(shuō)明x的格式。如：MONTH DDD,YYYYl TO_DATE(x,format)將字符串x轉(zhuǎn)換成date類型。2、 常用的日期計(jì)算實(shí)例下面是幾個(gè)關(guān)于日期方面的SQL實(shí)例 取得當(dāng)前日期是本月的第幾周SQL select to_char(sysdate,YYYYMMDD W HH24:MI:SS) from dual;TO_CHAR(SYSDATE,YYYYMMDDWHH242

44、0090202 1 18:00:43SQL select to_char(sysdate,W) from dual;TO_CHAR(SYSDATE,W)1 取得當(dāng)前日期是一個(gè)星期中的第幾天,注意星期日是第一天SQL select sysdate,to_char(sysdate,D) from dual;SYSDATE TO_CHAR(SYSDATE,D) 2009-2-2 18 2select to_char(sysdate,yyyy) from dual; -年select to_char(sysdate,Q from dual; -季select to_char(sysdate,mm) f

45、rom dual; -月select to_char(sysdate,dd) from dual; -日ddd年中的第幾天WW年中的第幾個(gè)星期W該月中第幾個(gè)星期D周中的星期幾hh小時(shí)(12)hh24小時(shí)(24)Mi分ss秒 取當(dāng)前日期是星期幾中文顯示:SQL select to_char(sysdate,day) from dual;TO_CHAR(SYSDATE,DAY)星期四 如果一個(gè)表在一個(gè)date類型的字段上面建立了索引，如何使用alter session set NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS 得到當(dāng)前的日期select sysdate f

46、rom dual;SYSDATE2009-2-2 18 得到當(dāng)天凌晨0點(diǎn)0分0秒的日期select trunc(sysdate) from dual;TRUNC(SYSDATE)2009-2-2 得到這天的最后一秒select trunc(sysdate) + 0.99999 from dual;TRUNC(SYSDATE)+0.999992009-2-2 23:59:59 得到小時(shí)的具體數(shù)值select trunc(sysdate) + 1/24 from dual;TRUNC(SYSDATE)+1/242009-2-2 1:00:00select trunc(sysdate) + 7/24

47、 from dual;TRUNC(SYSDATE)+7/242009-2-2 7:00:00 得到明天凌晨0點(diǎn)0分0秒的日期select trunc(sysdate+1) from dual;TRUNC(SYSDATE+1)2009-2-3 本月一日的日期select trunc(sysdate,mm) from dual;TRUNC(SYSDATE,MM)2009-2-1 得到下月一日的日期select trunc(add_months(sysdate,1),mm) from dual;TRUNC(ADD_MONTHS(SYSDATE,1),M2009-3-1 返回當(dāng)前月的最后一天SQL s

48、elect last_day(sysdate) from dual;LAST_DAY(SYSDATE)2009-2-28 18:11:3SQL select last_day(trunc(sysdate) from dual;LAST_DAY(TRUNC(SYSDATE)2009-2-28SQL select trunc(last_day(sysdate) from dual;TRUNC(LAST_DAY(SYSDATE)2009-2-28SQL select trunc(add_months(sysdate,1),mm) - 1 from dual;TRUNC(ADD_MONTHS(SYSD

49、ATE,1),M2009-2-28 得到一年的每一天Select Trunc(Sysdate, yyyy) + Rn - 1 Date0From (Select Rownum Rn From All_Objects Where Rownum SELECT TO_CHAR(SYSDATE,DDD) FROM DUAL;TO_CHAR(SYSDATE,DDD)033 如何在給現(xiàn)有的日期加上2年SQL select add_months(sysdate,24) from dual;ADD_MONTHS(SYSDATE,24)2011-2-2 18:15:56 判斷某一日子所在年分是否為潤(rùn)年SQL s

50、elect decode(to_char(last_day(trunc(sysdate,y)+31),dd),29,閏年,平年) from dual;DECODE(TO_CHAR(LAST_DAY(TRUNC(平年 判斷兩年后是否為潤(rùn)年SQL select decode(to_char(last_day(trunc(add_months(sysdate,24),y)+31),dd),29,閏年,平年) from dual;DECODE(TO_CHAR(LAST_DAY(TRUNC(平年 得到日期的季度SQL select ceil(to_number(to_char(sysdate,mm)/3

51、) from dual;CEIL(TO_NUMBER(TO_CHAR(SYSDATE1SQL select to_char(sysdate, Q) from dual;TO_CHAR(SYSDATE,Q)1六、 SQL語(yǔ)句的優(yōu)化寫法1、 oracle訪問(wèn)Table的方式ORACLE 采用兩種訪問(wèn)表中記錄的方式:l 全表掃描全表掃描就是順序地訪問(wèn)表中每條記錄. ORACLE采用一次讀入多個(gè)數(shù)據(jù)塊(database block)的方式優(yōu)化全表掃描.l 通過(guò)ROWID訪問(wèn)表你可以采用基于ROWID的訪問(wèn)方式情況,提高訪問(wèn)表的效率, , ROWID包含了表中記錄的物理位置信息.ORACLE采用索引(I

52、NDEX)實(shí)現(xiàn)了數(shù)據(jù)和存放數(shù)據(jù)的物理位置(ROWID)之間的聯(lián)系.通常索引提供了快速訪問(wèn)ROWID的方法,因此那些基于索引列的查詢就可以得到性能上的提高.2、 創(chuàng)建索引索引是表的一個(gè)概念部分,用來(lái)提高檢索數(shù)據(jù)的效率.通過(guò)索引查詢數(shù)據(jù)比全表掃描要快.當(dāng)ORACLE找出執(zhí)行查詢和Update語(yǔ)句的最佳路徑時(shí), ORACLE優(yōu)化器將使用索引.同樣在聯(lián)結(jié)多個(gè)表時(shí)使用索引也可以提高效率.另一個(gè)使用索引的好處是,它提供了主鍵(primary key)的唯一性驗(yàn)證.除了那些LONG或LONG RAW、LOB數(shù)據(jù)類型,你可以索引幾乎所有的列.通常,在大型表中使用索引特別有效.當(dāng)然,你也會(huì)發(fā)現(xiàn),在掃描小表時(shí),使

53、用索引同樣能提高效率.雖然使用索引能得到查詢效率的提高,但是我們也必須注意到它的代價(jià).索引需要空間來(lái)存儲(chǔ),也需要定期維護(hù),每當(dāng)有記錄在表中增減或索引列被修改時(shí),索引本身也會(huì)被修改.這意味著每條記錄的INSERT , DELETE , UPDATE將為此多付出4 , 5次的磁盤I/O . 因?yàn)樗饕枰~外的存儲(chǔ)空間和處理,那些不必要的索引反而會(huì)使查詢反應(yīng)時(shí)間變慢。大多數(shù)情況下,優(yōu)化器通過(guò)WHERE子句訪問(wèn)INDEX.定期的重構(gòu)索引是有必要的.ALTER INDEX REBUILD 創(chuàng)建普通索引實(shí)例CreateIndex infobase_titleOn infobase(title);-創(chuàng)建唯一

54、索引Createunique Index infobase_keyOn infobase (Id); 創(chuàng)建全文索引實(shí)例-創(chuàng)建全文索引CREATE INDEX infobase_content ON infobase(content)INDEXTYPE IS CTXSYS.CONTEXT;-在全文索引進(jìn)行檢索SELECT * FROM infobase WHERE CONTAINS (content,first) 0;-創(chuàng)建同步全文索引過(guò)程create or replace procedure sync_contentisbeginexecute immediatealter index inf

55、obase_content rebuild online | parameters ( sync ) ;execute immediatealter index infobase_content rebuild online | parameters ( optimize full maxtime unlimited ) ;end sync_content;/-創(chuàng)建作業(yè)執(zhí)行同步過(guò)程variable n number;begindbms_job.submit(:n,sync_content;,sysdate,sysdate+1/48);commit;end;/ 創(chuàng)建主建AlterTable in

56、fobaseAdd Constraints infobase_key Primary Key(Id);-表上創(chuàng)建主建相當(dāng)于在列上的建了一個(gè)唯一的索引3、 SQL優(yōu)化實(shí)例及問(wèn)題 使用like操作符的問(wèn)題在WHERE子句中,如果索引列所對(duì)應(yīng)的值的第一個(gè)字符由通配符(WILDCARD)開(kāi)始,索引將不被采用.如：like %標(biāo)題。這一點(diǎn)一定要注意。因?yàn)樵谖覀冮_(kāi)發(fā)的過(guò)程中經(jīng)常遇到這樣的問(wèn)題，like %標(biāo)題%會(huì)掃描全表，會(huì)給數(shù)據(jù)庫(kù)的性能帶來(lái)很大的壓力。要盡可能避免這種寫法，如果有必要可以用全文索引代替。如下面的例子：selectcount(*)from infobasewhere classid in

57、(0001000300030001),(0001000300030002)and (category like %600755% or category like%600976%);-可用全文索引代替select count(*) from infobasewhere classid in (0001000300030001),(0001000300030002)and (CONTAINS (category, 600755) 0or CONTAINS (category, 600976) 0 );但like 標(biāo)題%這種寫法會(huì)使用索引 選擇最有效率的表名順序(只在基于規(guī)則的優(yōu)化器中有效)ORA

58、CLE的解析器按照從右到左的順序處理FROM子句中的表名,因此FROM子句中寫在最后的表(基礎(chǔ)表 driving table)將被最先處理. 在FROM子句中包含多個(gè)表的情況下,你必須選擇記錄條數(shù)最少的表作為基礎(chǔ)表.當(dāng)ORACLE處理多個(gè)表時(shí),會(huì)運(yùn)用排序及合并的方式連接它們.首先,掃描第一個(gè)表(FROM子句中最后的那個(gè)表)并對(duì)記錄進(jìn)行派序,然后掃描第二個(gè)表(FROM子句中最后第二個(gè)表),最后將所有從第二個(gè)表中檢索出的記錄與第一個(gè)表中合適記錄進(jìn)行合并.例如:表 TAB1 16,384條記錄表 TAB2 1條記錄選擇TAB2作為基礎(chǔ)表 (最好的方法)select count(*) from tab

59、1,tab2執(zhí)行時(shí)間0.96秒選擇TAB2作為基礎(chǔ)表 (不佳的方法)select count(*) from tab2,tab1執(zhí)行時(shí)間26.09秒如果有3個(gè)以上的表連接查詢,那就需要選擇交叉表(intersection table)作為基礎(chǔ)表,交叉表是指那個(gè)被其他表所引用的表.例如:EMP表描述了LOCATION表和CATEGORY表的交集.SELECT *FROM LOCATION L ,CATEGORY C,EMP EWHERE E.EMP_NO BETWEEN 1000 AND 2000AND E.CAT_NO = C.CAT_NOAND E.LOCN = L.LOCN將比下列SQL更

60、有效率SELECT *FROM EMP E ,LOCATION L ,CATEGORY CWHERE E.CAT_NO = C.CAT_NOAND E.LOCN = L.LOCNAND E.EMP_NO BETWEEN 1000 AND 2000 WHERE子句中的連接順序ORACLE采用自下而上的順序解析WHERE子句,根據(jù)這個(gè)原理,表之間的連接必須寫在其他WHERE條件之前,那些可以過(guò)濾掉最大數(shù)量記錄的條件必須寫在WHERE子句的末尾.例如:第二個(gè)SQL要比第一個(gè)SQL查詢效率高：SELECT *FROM EMP EWHERE SAL 50000AND JOB = MANAGERAND 2