sql最佳化（oracle）- 第二部分 常用sql用法和注意事項

標籤：having   多列   ges   重複   返回   表達   常用   nbsp   gen   

第二部分 常用sql用法和注意事項             
  1. exists 和 in                          
  2. union 和 union all                    
  3. with as
  4. order by
  5. group by
  6. where 和 having
  7. case when 和 decode

1.exits和in用法
1）說明：
　　1. exists先對外表做迴圈，每次迴圈對內表查詢；in將內表和外表做hash串連
　　2. 使用exists oracle會先檢查主查詢； 使用in，首先執行子查詢，並將結果儲存在暫存資料表中

1. 使用exists和not exists
 select name, classno from student where exists (select * from class where student.classno= class.classno);　　
 select name, classno from student where not exists (select * from class where student.classno= class.classno);
　　
2. 使用in 和not in
 select name, classno  from student where classno  in (select classno from class);
 select name, classno  from student where classno not in (select classno from class);
 
2）比較
　　1. 如果兩個表大小相當，in和exists差別不大
　　2. 如果兩個表大小相差較大則子查詢表大的用exists，子查詢表小的用in
　　3.盡量不要使用not in

2.union和union all
1）說明：
　　1. 使用情境：需要將兩個select語句結果整體顯示時，可以使用union和union all
　　2. union對兩個結果集取並集不包含重複結果同時進行預設規則的排序；而union all對兩個結果集去並集，包括重複行，不進行排序
　　3. union需要進行重複值掃描，效率低，如果沒有要重複資料刪除行，應該使用union all
　　4. insersect和minus分別交集和差集，都不包括重複行，並且進行預設規則的排序
2）使用注意事項
　　1.可以將多個結果集合并
　　2. 必須保證select集合的結果有相同個數的列，並且每個列的類型是一樣的（列名不一定要相同，會預設將第一個結果的列名作為結果集的列名）

3.with as
1）說明：
　　1. with table as 可以建立暫存資料表，一次分析，多次使用
　　2. 對於複雜查詢，使用with table as可以抽取公用查詢部分，多次查詢時可以提高效率
　　3. 增強了易讀性
2）文法：
with tabName as （select ...）

 

4. order by
1）說明：
　　1. order by 決定oracle如何將查詢結果排序
　　2. 不指定asc或者desc時預設asc
2）使用：
　　1. 單列升序（可以去掉asc）
  　　select * from student order by score asc;
　　2. 多列升序
  　　select * from student order by score,deptno;
 　　3. 多列降序
  　　select * from student order by score desc,deptno  desc;
　　4. 混合
 　　 select * from student order by score asc,deptno  desc;
3）對NULL的處理
　　1. oracle在order by 時認為null是最大值，asc時排在最後，desc時排在最前  
 　　2. 使用 nulls first （不管asc或者desc，null記錄排在最前）或者nulls last 可以控制null的位置 
4）將某行資料置頂（decode）
 select * from student order by decode(score,100,1,2);  
 select * from student order by decode(score,100,1,2), score;  //（某一行置頂，其他的升序）
 
5）注意事項
　　1. 任何在order by 語句的非索引項目都將降低查詢速度
　　2. 避免在order by 子句中使用運算式

 

5. group by
1）說明：
　　1.用於對where執行結果進行分組
 eg1:select sum(score), deptno from student group by deptno;
 eg2:select deptno,sum(score) from student where deptno>1  group by deptno; 
 
 
6.where和having
1）說明：
　　1. where和having都是用來篩選資料，但是執行的順序不同 where --group by--having（即分組計算前計算where語句，分組計算後計算having‘語句）
　　2. having一般用來對分組後的資料進行篩選
　　3. where中不能使用聚組函數如sum，count，max等
2）例子
eg1：
 select deptno,sum(score) from student where deptno>1  group by deptno having sum(score)>100;

7. case when 和decode
1）說明：
　　1. decode更簡潔
　　2. decode只能做等值的條件區分，case when可以使用區間的做判斷
2）文法：
 　　decode(條件,值1,傳回值1,值2,傳回值2,...值n,傳回值n,預設值)

 　　--等價於：

　　 IF 條件=值1 THEN
 　　　　　　RETURN(翻譯值1)
 　　ELSIF 條件=值2 THEN
 　　　　　　RETURN(翻譯值2)
 　　　　　　......
 　　ELSIF 條件=值n THEN
 　　　　　　RETURN(翻譯值n)
　　 ELSE
 　　　　　　RETURN(預設值)
 　　END IF 
 
 
 　　CASE expr WHEN comparison_expr1 THEN return_expr1
 　　        [WHEN comparison_expr2 THEN return_expr2
   　　       WHEN comparison_exprn THEN return_exprn
    　　      ELSE else_expr]
 　　END
  

 　　CASE
 　　   WHEN comparison_expr1 THEN return_expr1
 　　   [WHEN comparison_expr2 THEN return_expr2
   　　  WHEN comparison_exprn THEN return_exprn
   　　  ELSE else_expr]
 　　END
 
3）例子：
eg1：
　　 方式一：
　　 select name, score,gender,
   　　case gender when ‘1‘ then ‘女‘
  　　  　　　　　  when ‘2‘ then ‘男‘
   　　   　　　　  else ‘未說明‘
   　　end gender_t
　　 from student; 
 
 方式二：
 　　select name, score,gender,
   　　case  when gender=‘1‘ then ‘女‘
 　　　　   when  gender=‘2‘ then ‘男‘
                   else ‘未說明‘
  　　 end gender_t
 　　from student;

 方式三：
　　 select name,gender,decode(gender,‘1‘,‘女‘,‘2‘,‘男‘,‘未說明‘)gender_t from student; 
 
 結果：
      
 
eg2：
　　 select name,score,
　　  case  when score >80 then‘優秀‘
  　　　　   when score>=60 and score <=80 then ‘良好‘
  　　　　   when score<60 then ‘不及格‘
    　　end  evalution
　　 from student;  
 結果：

  

 設定預設值，將null置為沒成績：
 　select name,score,
 　　 case  when score >80 then‘優秀‘
  　　　　  when score>=60 and score <=80 then ‘良好‘
  　　　 　 when score<60 then ‘不及格‘
　　　　    else ‘沒成績‘
　　  end  evalution
 from student;
 結果：
 
4）注意：
　　1.case有兩種形式，其中case 運算式 when then方式效率高於case when 運算式效率
　　2.使用decode函數可以避免重複掃描相同記錄或者重複串連相同的表，因而某些情況可以減少處理時間

sql最佳化（oracle）- 第二部分 常用sql用法和注意事項