高級查詢+索引+視圖

1、SQL高級查詢安裝完ORACLE后，在數(shù)據(jù)庫中創(chuàng)建一個SCOTT用戶及其所屬的4個表：dept,emp,bonus和salgrade.位于$ORACLE_HOMERDBMSADMIN文件夾下的scott.sql腳本文件就是用來創(chuàng)建這4個表并向其中插入數(shù)據(jù)的。在SQL*PLUS中，以“connect sys/password as sysdba”連接到數(shù)據(jù)庫，并運行該腳本文件就可以創(chuàng)建該scott用戶方案的示例數(shù)據(jù)庫。 隨著ORACLE數(shù)據(jù)庫技術的發(fā)展，scott用戶所屬的表已經變得不能展示ORACLE數(shù)據(jù)庫及其產品的最基本的特征了。所以從ORACLE9i開始就提供了可用來學習其各種特性的豐富的

2、示例數(shù)據(jù)庫，以適應ORACLE產品文檔，學習培訓，軟件開發(fā)，應用案例的需要如HR（人力資源 Human Resource）:HR方案是一個基本的關系數(shù)據(jù)庫方案。有七個表：雇員(employees)，部門(departments)，地點(locations)，國家(countries)，地區(qū)(regions)，崗位(jobs)，工作履歷(job_history).5.3.1連接查詢連接查詢：是從兩個或兩個以上表或視圖中進行查詢FROM子句告訴數(shù)據(jù)庫應該到哪個或哪些表或視圖中查詢所需要的數(shù)據(jù)。WHERE子句告訴數(shù)據(jù)庫進行連接或比較的連接條件是什么，用于連接或比較的列往往是具有主鍵，外鍵關系的列。當

3、從兩個或兩個以上表中查詢數(shù)據(jù),并且在WHERE子句中沒有指定連接條件(但可能有查詢條件)時,就會導致一個表的查詢結果集的所有行都連接到另一個表的查詢結果集的所有行上,即笛卡兒連接,就會產生笛卡兒積.例:笛卡兒積應避免出現(xiàn)笛卡兒連接,即應該在FROM子句中有n個表時,在WHERE子句中至少有n-1個連接條件.簡單連接相等連接,不相等連接用來建立兩表之間關系的連接條件的運算符是等號(=)的連接,就是相等連接.這種類型的連接把來自兩個表的在指定列中具有相等值的行連接起來.建立相等連接并不需要指定列具有相同的名稱.例:相等連接:查詢雇員編碼為177的雇員姓名及其所在的部門名稱.用來建立兩表之間關系的連

4、接條件的運算符不是等號的連接,就是不相等連接.不相等連接涉及的運算符有不等于(或!=),=,LIKE,IN,BETWEEN.AND.例,不相等查詢:查詢雇員JACK當前的工資比哪些崗位的最高工資還高JOIN連接SQL支持另一種使用關鍵字JOIN的連接.SELECT table1.column,table2.column FROM join_table1 join_type join_table2 on (join_condition)如:內連接的例子:查詢雇員編碼為177的雇員姓名及其所在的部門名稱.內連接:返回的查詢結果集中僅包含符合查詢條件(WHERE搜索條件或HAVING條件)和連接條件

5、的行.內連接消除與另一個表中的任何行不匹配的行.外連接:除返回所有匹配的行外,還會返回一部分或全部不匹配的行.左外連接:包括JOIN左側表的所有行.右外連接:包括JOIN右側表的所有行.完全連接:包括JOIN左側和右側表的所有行.LEFT JOIN,RIGHT JOIN ,FULL JOIN關鍵字.5.3.2子查詢子查詢是指嵌入在其他SQL語句中的一個查詢。子查詢可以出現(xiàn)在SELECT子句，WHERE子句，F(xiàn)ROM子句，HAVING子句和SET子句中。子查詢中還可以繼續(xù)嵌套子查詢。子查詢的分類：從父查詢與子查詢的關系上可分為不相關子查詢和相關子查詢。相關子查詢：子查詢的查詢條件依賴于父查詢中的

6、表不相關子查詢：子查詢的查詢條件不依賴于父查詢中的表單行子查詢：單行子查詢不向外層查詢返回記錄或只返回一行記錄。子查詢可以放在SELECT語句的WHERE，HAVING，F(xiàn)ROM子句中。在WHERE子句中使用單行子查詢時，要使用單行運算符，如比較運算符=，,=,=,.例:單行子查詢:查詢公司中工資最高的雇員信息多行子查詢：可以向外層查詢返回多行記錄。如果不能確認子查詢會返回多少行記錄，則在子查詢中使用多行運算符比使用單行運算符更安全，否則容易失敗。在WHERE子句中使用多行子查詢時，要使用多行運算符，如IN ，NOT IN ，EXISTS，NOT EXISTS，ALL，ANY，其中ALL和AN

7、Y必須與比較運算符結合使用）。例多行子查詢:查詢與部門20的所有員工有相同工資的雇員信息. 查詢比部門20的所有員工工資還高的雇員信息.多列子查詢：前面的子查詢的結果集都只有一列。實際上并沒有限制子查詢只能返回一列。多列子查詢是指返回多個列數(shù)據(jù)的子查詢語句，當多列子查詢返回單行數(shù)據(jù)時，在WHERE子句中可以使用單行比較符；當多列子查詢返回多行數(shù)據(jù)時，在WHERE子句中必須使用多行運算符，如IN 。例:多列子查詢:查詢公司中與198號員工的工資,崗位都相同的雇員信息.相關子查詢是指引用了父查詢中某些表或某些列的子查詢（但父查詢不能引用子查詢中的表或列）找出每個學生超過他選修課程平均成績的課程號.

8、 SELECT Sno,Cno FROM SC x WHERE Grade=(SELECT AVG(Grade) FROM SC y WHERE y.Sno=x.Sno );內層查詢是求一個學生所有選修課平均成績的,至于是哪個學生的平均成績要看參數(shù)x.Sno的值,而該值是與父查詢相關的,所以稱為相關子查詢.例:查詢每個部門中最高工資的雇員信息SELECT department_id, salary FROM employees aWHERE salary = (SELECT MAX(salary) FROM employees b WHERE b.department_id = a.depar

9、tment_id)ORDER BY department_id;求解過程:父子配合求解,外層查詢過程直接影響內層查詢的執(zhí)行.1)首先取外層查詢中表的第一個元組,根據(jù)它與內層查詢相關的屬性值處理內層查詢,若WHERE子句返回值為真,則取此元組放入結果表.2)然后再取外層表的下一個元組3)重復這個過程,直到外層循環(huán)執(zhí)行完.EXISTS運算符用于檢查子查詢所返回的行的存在性,而不返回任何數(shù)據(jù),即只產生邏輯真值或假值.查詢負責管理其他雇員的管理員信息.(假設雇員多的部門可能有若干個管理員,因此從employees表查詢,不從departments表查詢)SELECT employee_id, firs

10、t_name, job_id, department_idFROM employees aWHERE EXISTS (SELECT 1 FROM employees b WHERE b.manager_id = a.employee_id)ORDER BY department_id, employee_id;情景查詢在執(zhí)行SELECT操作過程中,經常需要對不同的數(shù)據(jù)顯示不同的結果.類似于IFELSE.邏輯.可以使用DECODE()函數(shù)和CASE表達式完成情景查詢.DECODE()函數(shù)DECODE(value, if1, then1, if2,then2, if3,then3, . . . e

11、lse ) Value 代表某個表的任何類型的任意列或一個通過計算所得的任何結果。當每個value值被測試，如果value的值為if1，Decode 函數(shù)的結果是then1；如果value等于if2，Decode函數(shù)結果是then2；等等。事實上，可以給出多個if/then 配對。如果value結果不等于給出的任何配對時，Decode 結果就返回elseselect count(*) , decode(region_id,1,歐洲,3,亞洲,其他洲) from countriesgroup by region_id;CASE表達式簡單的CASE表達式case search_expr when

12、expr_1 then display_result_1 when expr_2 then display_result_2. when expr_n then display_result_n else default_display_result end 搜索CASE表達式case when expr_1 then display_result_1 when expr_2 then display_result_2. when expr_n then display_result_n else default_display_result end6 索引和視圖建立在表上的索引是一個獨立于表

13、的 方案對象,它可以存儲在與表不同的磁盤或表空間中.通過在表中的一列或多列上創(chuàng)建索引,就能為數(shù)據(jù)的檢索提供快捷的存取路徑, 迅速地找到表中的數(shù)據(jù),而不必掃描整個表.數(shù)據(jù)庫的索引類似于書的目錄在書中,目錄是內容和頁碼的清單;在數(shù)據(jù)庫中,索引是數(shù)據(jù)和存儲的列表.索引一旦被創(chuàng)建,在表上執(zhí)行DML操作時ORACLE就會自動對索引進行維護,并且由ORACLE決定何時使用索引,用戶完全不需要在SQL中指定使用哪個索引,如何使用索引.索引的原理假設emp表的數(shù)據(jù)如圖,其中ROWID偽列表示的是對應記錄的物理存儲位置.EMPNOEMPNOENAMEENAMESALSALDEPTNODEPTNOROWIDROW

14、ID7369SMITH80020AAAMZJAAEAAAAIVAAA7499ALLEN160030AAAMZJAAEAAAAIVAAB7521WARD125030AAAMZJAAEAAAAIVAAC7566JONES297520AAAMZJAAEAAAAIVAAD7654MARTIN125030AAAMZJAAEAAAAIVAAE7698BLAKE285030AAAMZJAAEAAAAIVAAF7782CLARK245010AAAMZJAAEAAAAIVAAGSELECT sal FROM emp WHERE ename=JONES在ename列上沒有索引的情況下,必須搜索所有的記錄如果使用如

15、下語句CREATE INDEX idx_emp_ename ON emp(ename);就會在emp表的ename列上創(chuàng)建一個名為idx_emp_ename索引.創(chuàng)建索引時,ORACLE會對emp表執(zhí)行一次全表搜索,獲取每條記錄的 ename列的數(shù)據(jù),并將他們按升序排列,然后將排序后的ename列的數(shù)據(jù)和對應記錄的ROWID相匹配存儲到索引段中.如圖ENAMEENAMEROWIDROWIDALLENAAAMZJAAEAAAAIVAABBLAKEAAAMZJAAEAAAAIVAAFCLARKAAAMZJAAEAAAAIVAAGJONESAAAMZJAAEAAAAIVAADMARTINAAAMZJ

16、AAEAAAAIVAAESMITHAAAMZJAAEAAAAIVAAAWARDAAAMZJAAEAAAAIVAAC在ENAME列有索引的情況下,ORACLE將首先對索引中的ENAME列進行快速搜索(因為已經排序,可以使用各種快速搜索算法,如二分法).通過在索引中找到的JONES讀取對應的ROWID,然后通過該ROWID在emp表中讀取相應的SAL值.索引的基本概念索引的分類可以按列的多少,索引列是否唯一,索引數(shù)據(jù)的組織形式對索引進行分類.單列索引和復合索引唯一索引和非唯一索引標準(B樹)索引位圖索引索引創(chuàng)建索引的一般語法CREATE INDEX語句創(chuàng)建索引。CREATE UNIQUE|BITM

17、AP INDEX index_name ON table_name (column_nameASC|DESC,n,column_expression)B+樹索引B+樹索引就是通常使用的“傳統(tǒng)”索引，是數(shù)據(jù)庫中最常使用的一類索引結構。按B樹結構(或使用B樹算法)組織并存放索引數(shù)據(jù).在B樹索引中是通過在索引中保存排序過的索引列及其對應的ROWID來實現(xiàn)快速查詢.B+樹索引B+樹索引B+樹最底層的塊稱為葉子節(jié)點（leaf node）或葉子塊（leaf block），包含各個索引鍵以及一個rowid（指向索引的行）。葉子節(jié)點之上的內部塊稱為分支塊（branch block），用于在結構中實現(xiàn)導航B+樹

18、的特點之一是：所有葉子塊都應該在樹的同一層上。這一層稱為索引的高度（height）換句話說，索引是高度平衡的（height balanced）位圖索引位圖索引（bitmap index）是從Oracle 7.3版本開始引入的。目前Oracle企業(yè)版和個人版都支持位圖索引，但是標準版不支持。位圖索引是為數(shù)據(jù)倉庫/即席查詢環(huán)境設計的。位圖索引對于一個位圖索引sex_idx，其存儲的結構可能是 CREATE BITMAP INDEX sex_idx ON student(sex);學號學號姓名姓名性別性別0601001馬韶名女0601003于潤封男男男女女0110位圖索引位圖索引對于相異基數(shù)（dis

19、tinct cardinality）低的數(shù)據(jù)最為適合?；鶖?shù)是指某個列可能擁有的不重復數(shù)值的個數(shù).比如SEX列的基數(shù)為2.創(chuàng)建索引原則:選擇合適的索引類型在適當?shù)谋?適當?shù)牧猩蟿?chuàng)建適當數(shù)量的索引合理設置復合索引中的列順序設置合適的存儲參數(shù)數(shù)據(jù)裝載后再創(chuàng)建索引獲取有關索引的信息查看用戶建立了哪些索引查看用戶所建索引的細節(jié)信息 SELECT * FROM user_indexes WHERE table_name=VIDEO; SELECT * FROM user_ind_columns WHERE table_name=VIDEO;索引管理頁面 索引管理頁面 創(chuàng)建索引創(chuàng)建索引頁面 1輸入索引名稱，

20、選擇方案和表空間3置入列4設置排序和順序2選擇表創(chuàng)建索引索引選項頁面 保存選擇索引類型選擇執(zhí)行選項修改索引 ALTER INDEX語句可以修改索引?！纠吭O置索引index_username不可用：ALTER INDEX index_username UNUSABLERENAME TO子句可以重命名索引?！纠繉⑺饕鹖ndex_username重命名為index_username_1：ALTER INDEX index_username RENAME TO index_username_1分析索引監(jiān)視索引 ALTER INDEX index-name MONITORING USAGE打開索引

21、的監(jiān)視狀態(tài)之后，可以通過動態(tài)性能視圖“V$OBJECT_USAGE”查詢索引的使用情況 如：SELECT * FROM V$OBJECT_USAGEORACLE還提供了一種機制，監(jiān)視索引的空間使用情況。首先需要通過ANALYZE INDEX.VALLIDATE STRUCTURE語句分析索引的結構，再通過數(shù)據(jù)字典視圖INDEX_STATS來監(jiān)視索引的使用情況。如：SQL ANALYZE INDEX index_name VALIDATE STRUCTURE SQLSELECT BR-ROWS,LF_ROWS,DEL_LF_ROWS FROM INDEX_STATS WHERE NAME=ind

22、ex_name;使用ALTER INDEX 關閉索引的監(jiān)視狀態(tài)產看索引的被使用情況在進行數(shù)據(jù)查詢的時候，ORACLE通過優(yōu)化器進行分析判斷，以決定使用索引掃描還是全表掃描方法進行數(shù)據(jù)檢索。一般情況下，用戶要查詢的數(shù)據(jù)量超過表數(shù)據(jù)的5%時，就會進行全表掃描。在數(shù)據(jù)檢索過程中，如果要查看索引是否被使用，可以通過ORACLE提供的AUTOTRACE功能，它可以顯示SQL語句的執(zhí)行路徑。SET AUTOTRACE ON 合并索引合并索引是將B樹索引中葉節(jié)點的存儲碎片合并在一起，但不會改變索引的物理組織結構。當相鄰索引葉塊都存在剩余空間，并且它們的索引入口數(shù)據(jù)可以存放在同一個索引塊時，通過合并索引的方法

23、可以提高索引空間的利用率。ALTER INDEX index_name COALESCE DEALLOCATE UNUSED如果在索引列上頻繁執(zhí)行DELETE或UPDATE 操作，應該定期重建索引，來提高空間利用率。不僅可以消除存儲碎片，還可以改變索引的全部存儲參數(shù)設置。ALTER INDEX index_name REBUILD刪除索引 DROP INDEX語句刪除指定索引。【例】刪除索引index_username：DROP INDEX index_username;單擊此按鈕確認刪除索引視圖視圖的特點虛表, 是從一個或幾個基本表（或視圖）導出的表只存放視圖的定義, 不會出現(xiàn)數(shù)據(jù)冗余基表中

24、的數(shù)據(jù)發(fā)生變化, 從視圖中查詢出的數(shù)據(jù)也隨之改變基于視圖的操作 查詢 刪除 受限更新定義基于該視圖的新視圖一.建立視圖 一般情況下,創(chuàng)建視圖是由表的所有者完成的,如果其他用戶要創(chuàng)建索引,需要具有CREATE VIEW或者CREATE ANY VIEW權限,并且具有操作視圖所涉及的表或其他視圖的權限.語句格式CREATE OR REPLACE FORCE|NOFORCE VIEW ( , )AS WITH CHECK OPTION CONSTRAINT constraint_name ； WITH READ ONLY其中:REPLACE:如果存在同名視圖,則使用新視圖代替已有的視圖.FORCE:

25、強制創(chuàng)建視圖,不考慮基礎表是否存在,也不考慮是否具有基礎表的權限.WITH CHECK OPTION:透過視圖進行增刪改操作時, 不得破壞視圖定義中的謂詞條件（即子查詢中的條件表達式）CONSTRAINT constraint_name :在使用WITH CHECK OPTION選項時指定約束的名稱. WITH READ ONLY:創(chuàng)建的視圖只能用于查詢數(shù)據(jù),而不能用于更改數(shù)據(jù).組成視圖的屬性列名或全部省略或全部指定省略視圖的各個屬性列名, 則該視圖由子查詢中SELECT子句目標列中的諸字段組成. 必須明確指定組成視圖的所有列名的情形(1) 某個目標列不是單純的屬性名, 而是集函數(shù)或列表達式(

26、2) 多表連接時選出了幾個同名列作為視圖的字段(3) 需要在視圖中為某個列啟用新的更合適的名字子查詢不含ORDER BY子句和DISTINCT短語的SELECT語句ORACLE執(zhí)行CREATE VIEW語句時只是把視圖的定義存入數(shù)據(jù)字典, 并不執(zhí)行其中的SELECT語句. 只是在對視圖查詢時, 才按視圖的定義從基本表中將數(shù)據(jù)查出. 創(chuàng)建簡單視圖簡單視圖是基于單個表建立的，不包含任何函數(shù)，表達式和分組數(shù)據(jù)的視圖例：基于emp表創(chuàng)建一個V_staff視圖創(chuàng)建連接視圖連接視圖是基于多個表所創(chuàng)建的視圖，即定義視圖的SELECT子查詢是一個連接查詢。例：創(chuàng)建一個連接dept表，emp表的連接視圖 使用上述連接視圖，可以取得部門編號為10，30的部門及雇員信息。創(chuàng)建復雜視圖復雜視圖是指包含函數(shù)，表達式或分組數(shù)據(jù)的視圖。復雜視圖主要用于執(zhí)行查詢操作，并不用于執(zhí)行DML操作。例：在emp表上創(chuàng)建一個復雜視圖。強制創(chuàng)建視圖正常情況下，如果基本表不存在，創(chuàng)建視圖就會失敗。但是如果創(chuàng)建視圖的語句沒有語法錯誤，只要使用FORCE選項就可以創(chuàng)建視圖。該試圖被稱為帶錯誤的視圖。此時該視圖處于失效狀態(tài)（