Oracle 查詢的十個小技巧

Oracle資料庫查詢十個小技巧 資料查詢，是資料庫操作中最主要的功能之一；有時候資料庫查詢效能的好壞，直接關係到資料庫的運行效率，關係到資料庫的選型。下面筆者不談大道理，只是對其中對一些平時大家容易忽略的查詢小技巧做一些總結。或許大家可能正在為此犯愁呢?

　　第一個技巧：利用串連符串連多個欄位。
　　如在員工基本資料表中，有員工姓名、員工職位、出身日期等等。如果現在視圖中這三個欄位顯示在同一個欄位中，並且中間有分割符。如我現在想顯示的結果為“經理Victor出身於1976年5月3日”。這該如何處理呢?其實，這是比較簡單的，我們可以在Select查詢語句中，利用串連符把這些欄位串連起來。
　　如可以這麼寫查詢語句：
　　SELECT員工職位 ||’ ’ ||員工姓名||’出身於’||出身日期 as 員工出身資訊 FROM 員工基本資料表;
　　通過這條語句就可以實現如上的需求。也就是說，我們在平時查詢中，可以利用||串連符把一些相關的欄位串連起來。這在報表視圖中非常的有用。如筆者以前在設計圖書館管理系統的時候，在書的基本資料處有圖書的出版社、出版序號等等內容。但是，有時會在列印報表的時候，需要把這些欄位合并成一個欄位列印。為此，就需要利用這個串連符把這些欄位串連起來。而且，利用串連符還可以在欄位中間加入一些說明性的文字，以方便大家閱讀。如上面我在員工職位與員工姓名之間加入了空格;並且在員工姓名與出身日期之間加入了出身於幾個注釋性的文字。這些功能看起來比較小，但是卻可以大大的提高內容的可讀性。這也是我們在資料庫設計過程中需要關注的一個內容。
　　總之，令後採用串連符，可以提高我們報表的可讀性於靈活性。
　　第二個技巧：取消重複的行。
　　如在人事管理系統中，有員工基本資料基本表。在這張表中，可能會有部門、職位、員工姓名、身份證件號碼等欄位。若查詢這些內容，可能不會有重複的行。但是，我若想知道，在公司內部設定了哪些部門與職位的時候，並且這些部門與職位配置了相關人員。此時，又該如何查詢呢?
　　若我現在直接查詢部門表，其可以知道系統中具體設定了哪些部門與職位。但是，很有可能這些部門或者職位由於人事變動的關係，現在已經沒有人了。所以，這裡查詢出來的是所有的部門與職位資訊，而不能夠保證這個部門或者職位一定有職員存在。也就是說，這不能夠滿足於我們上面的要求。
　　若我現在直接從員工資訊表中查詢，雖然可以保證所查詢出來的部門與職位資訊，一定有員工資訊的存在。但是，此時查詢出來的部門與職位資訊會有重複的行。如採購部門分工合作，可能會有採購採購小組長。此時，在查詢出來的部門與職位的資訊中，就會有三條重複的記錄。
　　所以，以上兩種處理方式，都不能夠百分之百的滿足企業使用者的需求。此時，我們其實可以利用一個DISTINCT函數，來消除其中查詢出來的重複行。
　　如我們可以利用SELECT DISTINCT 部門資訊，職位資訊 FROM 員工基本資料表。通過這條加了DISTINCT約束的查詢語句，不但可以查詢出所有有員工的職位與部門資訊，而且，會把重複的記錄過濾掉，從而提高可閱讀性。
　　所以，在資料庫設計過程中，特別是在查詢語句的使用中，這個函數特別有用。
　　第三個技巧：勤用WHERE語句。
　　我們都知道，資料庫查詢效率高不高，是我們評價資料庫設計好壞的一個重要標準。毋庸置疑，在資料庫查詢中勤用Where條件陳述式，是提高資料庫查詢效能的一個很重要的手段之一。特別是在設計到比較大的表中查詢合格記錄過程中，利用WHERE條件陳述式加以限制，可以大幅度的提高查詢的響應速度。
　　如在圖書館管理系統中，現在有人想查詢“註冊會計師”輔導用書的時候，雖然不在書的類別或者名稱中輸入“註冊會計師”，先查詢出全部的紀錄，然後再一條條的看是否有相關的書籍資訊，也是可行的。但是，這麼處理的話，一方面系統響應的速度會非常的慢，因為裡面記錄很多。另一方面，查詢的結果看起來也會非常的頭疼。
　　其實，我們只需要在查詢中加入一些查詢的參數，利用Where條件陳述式加以限制，則即可以提高資料庫響應的速度，也可以找出最符合使用者需求的資料。
　　另外，我也接觸過一些在Oracle資料庫上設計的平台型管理軟體，他們可以自訂相關的報表。在報表設計中，只要使用者在前台設計平台中，選中“大表查詢”的話，則這個平台會在產生報表的時候，自動應用Where條件陳述式，以提高前台系統從資料庫查詢資料的效率。
　　所以，筆者認為在Oracle資料庫系統設計中，要勤於使用Where語句。利用Where語句來提高資料庫查詢的效率。
第四個技巧：靈活使用COUNT函數。
　　在查詢處理的時候，COUNT函數可以說是我們應用的比較多的函數之一。如我們有時候需要統計員工的人數、統計圖書的種類數的時候，都需要使用到這個函數。不過，這個函數很多人可能會用，但是到靈活應用的地步，還是有一點差距。
　　下面筆者就COUNT函數的一些應用技巧談談自己的心得。
　　一是要靈活放置COUNT函數的位置，因為利用COUNT函數統計記錄數的時候，是會考慮空行的記錄的。如在資料表中一般有序欄欄位與其它的有意義欄位兩類。有時候可能序欄欄位中有內容而其它欄位中沒有內容，則在利用COUNT函數統計記錄數量的時候，會把這個空記錄也考慮進去。很明顯，則就會發生統計的錯誤。所以，這個COUNT函數該放在哪個位置上，還是比較講究的。一般的話，筆者試建議不要放在序號欄位上，而要放在一些關鍵的實體欄位中。如統計員工人數的時候，則就可以放在員工姓名或者編號上等等。
　　二是靈活跟其它函數搭配使用。如在上面的例子中，筆者談到有時候使用者需要知道現在有員工編製的部門與職位有哪一些，我們可以利用DISTINCT函數來找出具體的部門。但是，我現在只想知道有編製的部門與職位具體有多少，此時，我們也可以利用COUNT 與DISTINCT函數結合應用，找出我們所需要的資料。在COUNT函數中，可以指定ALL與DISTINCT選項。預設的情況下，是ALL選項，表示統計所有的行，其中也包括重複的行。而DISTINCT就表示只統計不重複的行。可見，COUNT函數跟其它函數搭配使用的話，可以簡化我們的查詢語句，提高查詢效率。
　　第五個技巧：只查詢時必須的欄位。
　　有時候，使用者不同的查詢需求都要用到同一張表。如在員工資訊表中包含了很多內容。有時候使用者想要知道正式員工有多少;管理層員工有多少;生產線員工又有哪些;或者想知道合約即將到期的員工有哪些。為此，就遇到一個問題，因為這些內容基本上都是在同一張表中，那是在同一個視圖中實現，而是根據需求不同，設計不同的視圖呢?
　　若單從技術上考慮，兩這都是可以實現的，不會有多大的難度。但是，若是從資料庫效能上考慮在，則還是採用不同的視圖來實現不同的需求為好。
　　一方面，若從安全方面講，則可以根據不同的視圖來控制相關的存取權限。可見，把視圖細化，在許可權控制上則會更加的靈活。
　　另一方面，資料的查詢效率，跟資料內容的多少也有非常密切的關係。如在查詢員工合約到期資訊的時候，一般不需要員工的地址資訊等等。若把這個資訊也查詢出來的話，由於這個欄位比較長，就會花費比較長的時間。所以，在資料庫設計中，我們要學會根據使用者不同的需求，設計不同的視圖。雖然可能這在設計的時候會比較花時間，但是，在確可以提高資料庫的效能與安全性。這筆生意還是划得來的。
第六個技巧：合理處理NULL欄位。
　　Null欄位在資料庫中是一個比較特殊的欄位。Null欄位表示未知值或者說缺少資料，注意若某個欄位的值為Null，則這個欄位即不是空格，也不是0。當插入記錄的時候，若這個欄位沒有被賦值，而且也沒有預設值的話，則這個欄位系統預設給他的值就是“Null”。
　　由於這個值比較特殊，在查詢的時候，及時經驗豐富的資料庫管理員，有時候在處理起來的時候，也會發生錯誤。為此，筆者在這裡總結一些，在資料庫查詢的時候，關於這個空欄位查詢的一些需要注意的地方。
　　一是要注意NULL欄位的數字運算問題。
　　如現在在一個薪資管理系統中，有一張薪資表，其中有基本工資與加班工資兩個欄位。若某個使用者的基本工資為2000，而其加班工資沒有。在輸入這條記錄的時候，由於加班工資這個欄位中，沒有輸入資料，而且在資料庫設計的時候，也沒有個這個欄位設定0的預設值。所以，當這條記錄儲存的時候，資料庫系統會給這個欄位自動賦值，這個欄位的值就為NULL。
　　若我們用Select語句查詢這條記錄的時候，其加班工資這個欄位顯示的資料是空的。看起來好像是空格，而實際上其儲存的不是空格。此時，我們若利用查詢語句想知道，這個員工的總的工資(即加班工資加上基本工資)為多少的時候會有什麼結果呢?
　　我們可以利用Select 員工姓名,基本工資,加班工資,基本工資+加班工資 as 總工資 FROM 員工薪資表; 我們可以通過這條語句來查詢這個員工總的工資是多少。但是，這條語句會查詢出我們想要的結果嗎?我們執行一下這條語句，結果我們會發現，得出的結果跟我們想象的大相徑庭。最後顯示的總工資一欄中，為空白格。
　　原來，Oracle資料庫設計中，若一個NULL欄位跟其他欄位進行四則運算時，其顯示的結果都為空白。所以，若一個欄位為NUU，則無論加減乘除，最後其結果都返回的施NULL值。這顯然跟我們想象的不同。
　　針對這種情況，我們該如何處理呢?在資料庫設計過程中，主要有兩種處理方法。
　　一是在設計表的時候，對於這些需要參與運算的欄位，要設定預設值。如可以把這個欄位的預設值設定為0。則當添加這條記錄的時候，即使前台使用者沒有給其設定值。在儲存資料的時候，系統也會給其預設值0。如此的話，在進行四則運算的時候，才可能得到我們想要的值。
　　二是在查詢的時候，需要考慮到這個NULL值的影響。有時候，若資料庫中已經有記錄，則不能夠改變資料庫欄位的預設值。遇到這種情況，若我們需要對NULL欄位與數字欄位進行四則運算的時候，又該如何處理呢?此時，我們就需要在查詢的時候，給NULL欄位賦0的值。具體我們可以在查詢語句中，如此定義。Select 員工姓名,基本工資,加班工資,基本工資+NVL(加班工資,0) as 總工資 FROM 員工薪資表;如此的話，當加班工資的值為NULL的時候，則系統在運算的時候，會把其當作0來處理。這麼處理，我們就可以得到我們所想要的結果。不過一般情況下，這一種處理方式是不得已而為之的。最好的是，在資料庫表設計的時候，就給相關的欄位設定0的預設值。
　　另外，還有一個函數NVL2跟NVL函數功能類似，只是其多了一個參數而已，其運算式為NVL2(參數1，參數2，參數3)。它的含義是，當參數1不為空白值時，則返回的值為參數2;當參數1是空值時，則範圍的是參數3。若用這個函數實現NVL函數的目的時，則就需要如此改寫上面這個案例的函數參數寫法：NAV2 (加班工資，基本工資+加班工資，基本工資)。可見，兩個函數有異曲同工之妙。具體採用哪種函數為好，則就需要根據資料庫管理員的愛好來選擇了。
　　二是如何查詢NULL欄位。
　　如果現在有一張員工基本資料表，其中有一個社會安全號碼碼的欄位。現在若使用者想知道，有哪些員工還沒有記錄社會安全號碼碼資訊，該如何做呢?由於這個NULL欄位不為空白格或者0。若我們在查詢條件陳述式中，利用’0’ 或者’’(空格)作為查詢條件的話，是查不到我們所需要的結果的。此時，在資料庫中，提供了一個專門使用者查詢NULL欄位記錄的函數IS NULL。若我們現在想知道哪些員工沒有註明身份證資訊時，就可以利用如下的語句來實現。
　　Select 員工姓名,社會安全號碼碼 from 員工基本資料表 where 社會安全號碼碼 is not null;
　　通過以上這條語句就可以實現尋找身份證件為空白的員工資訊的目的。
　　第七個技巧：多多利用模糊查詢。
　　在應用系統設計的時候，若讓使用者完整的輸入全部查詢條件，這個要求對於普通使用者來說，過於苛刻。做過軟體項目培訓或者實施的時候，出於種種原因，使用者在查詢的時候，往往只輸入部分的輸入條件。遇到這種情況的時候，就需要在查詢設計的時候，實現模糊查詢。如此的話，即使使用者輸入的查詢條件不全，也可以查詢出相關的內容。
　　如使用者在查詢某個產品資訊的時候，其可能不記得某個產品的具體名稱，只知道其叫做端子，而其他具體的資訊不清楚。此時，只需要在名稱欄位或者規格欄位處輸入端子，就可以從系統中查詢中這兩個欄位中含有“端子”的紀錄資訊。如此的話，使用者只需要在查詢出的結果中繼續尋找即可。
　　這就告誡我們資料庫管理員，在資料庫系統設計的時候，需要多用用模糊查詢的功能。
　　具體的來說，需要注意以下幾方面內容。
　　一是大小寫不要進行區分。預設情況下，在資料庫查詢的時候，大小寫是區分的。也就是說，現在有個欄位內容為ABC，則我們查詢abc的時候，就查不到這條記錄。因為其大小寫不同。而作為前段應用程式的使用者來說，往往其在輸入查詢條件的時候，其並不會區分英文的大小寫。雖然，不區分大小寫也可以在前端應用程式中實現，不過，一般來說，在資料庫中實現要比在前端系統中實現簡單的多。故筆者是建議在後台資料庫中實現這個大小寫自動轉換功能。在查詢的時候，不要區分大小寫。這可能就是國內的特有國情吧。
　　二是要實現前後模糊查詢。如現在有個欄位的內容為“好好學習”，若我現在輸入查詢條件為學習、好好、好學等，都可以查到這條件記錄。此時該如何設計查詢的條件陳述式呢?這就是前後模糊查詢的概念。其實，要實現這個功能也很簡單。在前台查詢語句設計的時候，當把查詢條件傳遞給後台資料庫系統的時候，在查詢參數的前後，分別加入模糊查詢的參數%即可。即我們若輸入的查詢條件陳述式為“好學”，則其傳遞給資料庫的參數為“%好學%”。如此，就可以實現我們所需要的模糊查詢。一般來說，模糊查詢需要前台應用程式與後台資料庫之間共同實現，這麼處理起來，工作量會少許多;也會提高資料庫的運行效率。
　　三是通過Beteen函數實現模糊查詢。如現在有一個考勤系統，某個員工想知道自己在9月份自己的加班情況時，只需要在查詢條件中，輸入時間為9月1日到9月30日時，就可以查詢到自己所需要的資訊。這就是通過Beteen函數來實現模糊查詢的。筆者以前碰到過一個平台型的ERP系統，他在這方面作的不錯。這個產品就可以自己設計相關的報表。在報表設計中，在基於日期的查詢條件，其即可以查詢單個日期，而且還可以查詢某個範圍內的紀錄。這就使利用了這個函數。另外，可以實現模糊查詢的函數還有IN函數等等。作為資料庫管理員，需要從提高資料庫的查詢效能角度出發，合理選擇這些模糊查詢函數。並且，還需要配合前台應用程式設計，處理好模糊查詢的功能。
第八個技巧：慎用Like等萬用字元。
　　Like關鍵字，從技術上來說，是一個非常友善的萬用字元。利用這個萬用字元，我們可以實現很多模糊查詢。如現在在一個人事檔案系統中，使用者想知道社會安全號碼碼以“339005”開頭的人事資訊，此時，就可以利用Like語句實現。我們可以利用下面的條件陳述式，實現我們的需求，“where 社會安全號碼碼 like ‘339005%’”。通過這個條件陳述式，可以查到所有身份證以339005開頭的號碼。
　　但是，當使用者在一張大表中採用這個LIKE語句的話，就會發現這個查詢語句的運行效率非常的慢。這是什麼原因造成的呢?其實，不管是Like 關鍵字，若採用MATCHES關鍵字的話，若在大量資料中尋找合格記錄，則其運行效率也比較低。這主要是其技術特性所造成的。
　　Like與Matches兩個關鍵字，其支援萬用字元匹配。在有些專業書籍上把這個叫做“正規運算式”。不過由於在利用這些關鍵字查詢的時候， 資料庫系統不是通過索引來查詢，而是採用順序掃描的方式來查詢。顯然，真是這種技術特性，造成了Like與Mateches兩個關鍵字查詢效率的低下。特別是在複雜查詢或者大表查詢中，使用者可以明顯感覺到速度比較慢。
　　索引是資料庫中的一個重要的資料結構。索引如果利用的合理的話，可以大幅度的提升資料庫的查詢效能。一般情況下，我們在資料庫設計的時候，要充分的利用索引，來提高資料庫的運行效率。如對於一些經常需要用到的查詢功能，我們需要為沒有指定外鍵的列建立索引;如有查詢大表資料，而且又需根據好幾個欄位的值對其進行排序，也需要在這些列上建立複合索引。特別是在一些應用系統上，往往可以按以下欄位的名稱，就會對這個欄位進行排序。遇到這種情況的話，更加需要在這些頻繁進行排序的列上建立索引，以提高重新排序的效率。可見，若在查詢的時候，若不能利用索引提高查詢效率的話，則就好像跑車失去四輪驅動，速度會大受影響。
　　所以，在資料庫系統設計中，要盡量避免採用Like或者Matche關鍵字。有時候，我們可以利用其他運算子號來代替。如我們可以利用〉(大於)或者<(小於)符號來達到類似的需求。若真的要採用這兩個關鍵字的話，則就需要做好查詢最佳化方面的工作。如不要在基礎資料表中直接利用這個兩個關鍵字，而是通過報表視圖、或者暫存資料表等來查詢，以減少其不良影響。
　　第九個技巧：利用注釋提高查詢語句的可讀性。
　　在資料庫設計中，有一個非常奇怪的現象。一些專家級的資料庫設計人員，在寫查詢語句的時候，非對語句進行詳細的注釋。有時會，注釋的內容大大超過了查詢代碼本身的篇幅。可是，往往一些入門不久的資料庫設計人員，不喜歡寫備註陳述式。這是一個很反常的現象。
　　筆者剛開始接觸資料庫的時候，也不喜歡寫備註陳述式。覺得寫備註陳述式太浪費時間。但是，一個偶然的事件讓筆者改掉了這個壞習慣。那時筆者在觀摩一個專家設計資料庫的時候，被其密密麻麻的注釋驚呆了。看了其代碼之後，筆者可以非常輕鬆的閱讀完其所有的代碼。不愧為是專家級的人物。看了他的注釋之後，在看看自己編寫的代碼注視，那真是大巫見小巫了。從此之後，筆者也在慢慢培養自己編寫代碼的習慣。現在筆者在資料庫設計的時候，注釋已經寫的很詳細了。至少筆者的同事在看到我的注釋之後，不用看原始碼就知道筆者要實現的目的了。
　　在編寫注釋的時候，要注意幾個問題。
　　一是注釋越詳細越好。其實，注釋最多犧牲一點磁碟空間，而不會對資料庫的效能產生任何不良的影響。相反，注釋詳細的話，對於後續資料庫維護與管理、系統二次開發的等等，都會提供很大的協助。
　　二是最好採用英文注釋。若採用中文注釋的話，有時候資料庫語言設定不當，在資料庫執行個體安裝的時候，不會把中文的注釋帶過去。所以，作為資料庫管理員，要有一定的英語基礎，學會利用英語寫注釋。其實，這也不是很難。只要多看看別人設計的資料庫注釋，把他們常用的注釋複製下來。通過選擇合適的進行複製、粘貼就可以完成任務。
　　三是不僅在寫查詢語句的時候，要做好代碼的注釋工作。在編寫其他代碼的時候，如過程、函數等等，也要添加詳細的代碼。以增加這些複雜功能的可讀性。
　　四是注釋的內容。一般注釋應該包含如下幾方面的內容。一是這段代碼要實現的功能;二是這段代碼需要調用的參數;三是這段代碼輸出的結果。若是多表關聯查詢的話，最好能夠說明表之間的對應關係。若在查詢語句中，直接調用了函數的話，則最好能夠註明這個函數的功能;等等。終止一個原則就是，讓其他人看到這個注釋，不再需要去查詢其他的資料，就可以明白這段代碼的含義。
　　詳細的注釋內容，不僅不會降低資料庫的運行效能，而且還可以提高資料庫的管理與維護的效率;同時也可以加快前台應用程式開發設計的速度。又因為查詢語句是系統中利用的最多的語句，也是引用的最頻繁的語句。故在查詢語句中，更加需要做好相關的注釋。
　　第十個技巧：必要的時候，限制使用者所使用的行。
　　在使用一些大型管理系統的時候，如ERP系統。我們若查詢產品資訊，預設的情況下，若記錄比較多的時候，其不會把所有的記錄都查詢出來。如在ERP系統的資料庫中，其有20000個產品資訊。而端子類的產品資訊就佔據到1000個。此時，我們在查詢條件中，若產品類別限制為“端子”的話，則其查詢出來的結果也可能不是所有的端子。預設顯示的話，可能只有前面的100個端子類產品。若使用者需要看到全部的產品資訊，就需要點擊“顯示全部”按鈕，才可以顯示出全部的資訊。
　　其實，不管是一些應用程式如此設計，在Oracle資料庫中，本身也有這方面的限制。如直接在PL/SQL用戶端中查詢資料的話，其顯示的記錄預設情況下也是有限制的，而不會把所有合格語句查詢出來。若使用者需要查詢所有合格記錄，則需要點擊“繼續”按鈕，以讓資料庫顯示所有的記錄。
　　為什麼要做類似的限制呢?這主要就是為了提高資料庫查詢的效能。我們直接在資料庫伺服器上，在幾百萬條記錄中查詢的話，顯示幾百條記錄跟現實幾千條記錄所花費的時間明顯不同。前者可能只需要3秒即可。而後者可能需要1分鐘。所以，為了減少使用者等待的時間，我們往往需要限制首次查詢預設顯示的記錄數字。
　　如我們往往在查詢語句中，利用top 100 來讓資料庫只顯示前100條記錄。如此的話，可以明顯的縮短使用者的等待時間。預設情況下，是根據記錄建立的時間順序，來顯示記錄的。最遲建立的記錄，其顯示在最前。以此類推。
　　當使用者需要的資料在前面100條之內，則就不需要再查看其他記錄了。相反，若不在的話，則就需要查詢全部記錄資訊了。