Oracle與Mysql的高階查詢與難點sql

y_keven發表於2013-09-23
OracleMySql

一、連線查詢 
1.內連線 
    內連線用於返回滿足連線條件的所有記錄。預設情況下，在執行連線查詢時如果沒有指定任何連線操作符，那麼這些連線查詢都屬於內連線。 

Sql程式碼  

1.   SELECT a.dname,b.ename from dept a,emp b where a.deptno=b.deptno and a.deptno=10;  


   在FROM子句中指定INNER JOIN選項，也可以指定內連線。 

Sql程式碼  

1.   SELECT a.dname,b.ename from dept a INNER JOIN emp b where a.deptno=b.deptno and a.deptno=10;  


   從Oracle9i開始，如果主表的主鍵列和從表的外部鍵列名稱相同，那麼可以使用NATURAL JOIN 關鍵字自動執行內連線操作。 

Sql程式碼  

1.   SELECT dname,ename FROM dept NATURAL JOIN emp;  


2.左外連線 
    左外連線通過指定LEFT［OUTER］ JOIN選項來實現。當使用左外連線時，不僅會返回滿足連線條件的所有記錄，而且還會返回不滿足連線條件的連線操作符左邊表的其他行。 

Sql程式碼  

1.   SELECT a.dname,b.ename FROM dept a LEFT JOIN emp b ON a.deptno=b.deptno AND a.deptno=10;  


3.右外連線 
    右外連線通過指定RIGHT［OUTER］ JOIN選項來實現的。當使用右外連線時，不僅會返回滿足連線條件的所有行，而且還會返回不滿足連線條件操作符右邊表的其他行。 

Sql程式碼  

1.   SELECT a.dname,b.ename FROM dept a RIGHT JOIN emp b ON a.deptno=b.deptno AND a.deptno=10;  


4.完全外連線 
    完全外連線是指定FULL［OUTER］ JOIN 選項來實現的。當使用完全外連線時，不僅會返回滿足連線條件的所有行，而且還會返回不滿足連線條件的所有其他行。 

Sql程式碼  

1.   SELECT a.dname,b.ename FROM dept a FULL JOIN emp b ON a.deptno=b.deptno AND a.deptno=10;  

相當於沒連線去掉where條件

Sql程式碼  

1.   SELECT a.dname,b.ename from dept a,emp;  


5.使用（＋）操作符 
    在Oracle9i之前，當執行外連線時，都是使用連線操作符（＋）來完成的。儘管可以使用操作符（＋）執行外連線操作。但Oralce9i開始Oracle建議使用OUTER JOIN執行外連線。語法如下： 
    SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1(+) = table2.column2; 
    當使用（＋）操作符執行外連線時，應該將該操作符放在顯示較少行（完全滿足連線條件行）的一端。 
     一句話，左外連線時，（＋）要放在右邊表的一端。右外連線時，（＋）放在左邊表的一端。 
    左外連線： 

Sql程式碼  

1.   SELECT a.dname,b.ename FROM dept a, emp b where a.deptno=b.deptno(+) AND b.deptno(+)=10;  


   右外連線 

Sql程式碼  

1.   SELECT a.dname,b.ename FROM dept a, emp b where a.deptno(+)=b.deptno AND a.deptno(+)=10;  


二、遞迴查詢 
1.語法 

Sql程式碼  

1.   Select * from …. Where [結果過濾條件語句]  

2.   Start with  [起始條件過濾語句]  

3.   Connect by prior [中間記錄過濾條件語句]   


2.例子 

Sql程式碼  

1.   Select * from company t Where t.flag=1  

2.   Start with  t.company_id=50500000  

3.   Connect by prior t.company_id=t.parent_id  


說明： 

Sql程式碼  

1.   select [level],column,expr from table [where condition]  

2.   [start with] //[起點]  

3.   [connect by prior + 主鍵=外來鍵 或 外來鍵=主鍵]  


a.自頂向下: 左邊放主鍵，右邊放外來鍵。 
b.自底向上: 右邊放主鍵，左邊放外來鍵。 
c.level（偽列）層次的級別,不固定值。 

三、更改資料庫的時間格式 

1、臨時更改 
alter  session  set  nls_date_format='YYYY-MM-DD HH24:MI:SS' 

2.永久更改 
    在登錄檔[HKEY_CODE_MACHINE\SOFTWARE\ORACLE]裡面，加入NLS_DATE_FORMAT，值為YYYY-MM-DD HH24:MI:SS。 

3、Unix下，在使用者的.profile檔案中增加以下內容：  
  NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS 
  export  NLS_DATE_FORMAT 

附：在SQLPLUS下，實現中-英字符集轉換 

Sql程式碼  

1.   alter session set nls_language='AMERICAN';  

2.   alter session set nls_language='SIMPLIFIED CHINESE';   


四、複製表和資料 
1)建表 

Sql程式碼  

1.   create table test as select * from dept; --從已知表複製資料和結構  

2.   create table test as select * from dept where 1=2; --從已知表複製結構但不包括資料  


   建立檢視： 

Sql程式碼  

1.   CREATE OR REPLACE VIEW dept_10 AS SELECT empno,ename,job,sal,deptno FROM emp WHERE deptno=10 ORDER BY empno;  


   建立實體化檢視： 

Sql程式碼  

1.   CREATE MATERIALLIZED VIEW summary_emp AS SELECT deptno,job,avg(sal) avgsal, sum(sal) sumsal FROM emp GROUP BY cube(deptno,job);  


   實體化檢視管理是用於彙總,預計算,複製或分發資料的物件, 在大型的資料庫中使用它可以提高涉及到的SUM,COUNT,AVG,MIN,MAX等的表的查詢的速度,只要在實體化檢視管理上建立了統計,查詢優化器將自動的使用實體化檢視管理,這特性被稱為QUERY REWRITE(查詢重寫).與普通的檢視不同的是實體化檢視管理儲存資料,佔據資料庫的物理空間的. 
2)不使用列的列表插入資料： 

Sql程式碼  

1.   insert into test select * from dept;  


3)使用DEFAULT插入資料:如果列存在預設值，則會使用其預設值；如果列不存在預設值，則自動使用NULL. 

Sql程式碼  

1.   insert into dept values(10,'市場部',default);  


4)使用子查詢插入資料 

   將查出來的內容全部插入

Sql程式碼  

1.   insert into employee(empno,ename,sql,deptno)  

2.   select empno,ename,sal,deptno from emp  

3.   where deptno = 10;  

將查出來的內容部分插入

Sql程式碼  

1.   insert into employee(empno,ename,sql,deptno)

2.   select empno,’楊凱’,sal,deptno from emp  

3.   where deptno = 10;  

    當要裝載大批量資料時，用直接裝載方式: 

Sql程式碼  

1.   insert /*+APPEND */ into employee(empno,ename,sql,deptno)  

2.   select empno,ename,sal,deptno from emp  

3.   where deptno = 10;  


5)使用多表插入資料。從Oracle9i開始，使用INSERT語句可以將某張表的資料同時插入到多張表中。 
    使用ALL操作符，在每個條件子句上都要執行INTO子句後的子查詢。 

Sql程式碼  

1.   insert all when deptno=10 then into t_dept10  

2.   when deptno=20 then into t_dept20  

3.   when deptno=30 then into t_dept30  

4.   when job='clerk' then into t_clerk  

5.   else into t_other  

6.   select * from emp;  


     使用FIRST操作符執行多表插入，如果資料已經滿足了先前條件，並且已經被插入到某表，那麼該行資料在後續插入中將不會被再次使用。 

Sql程式碼  

1.   insert first when deptno=10 then into t_dept10  

2.   when deptno=20 then into t_dept20  

3.   when deptno=30 then into t_dept30  

4.   when job='clerk' then into t_clerk  

5.   else into t_other  

6.   select * from emp;  


五、多列子查詢 
    多列子查詢是指返回多列資料的子查詢語句。當多列子查詢返回單行資料時，在WHERE子句中可以使用單行比較符；當多列子查詢返回多行資料時，在WHERE子句中必須使用多行比較符（IN，ANY，ALL）。 
    如顯示與Id為10000的人員部門和崗位完全相同的所有僱員： 

Sql程式碼  

1.   SELECT ename,job,sal,deptno FROM emp WHERE (deptno,job)=(SELECT deptno,job FROM emp WHERE id=10000);  


    當執行成對比較是時，因為要求多個列的資料必須同時匹配，所以必須要使用多列子查詢實現。 
     如顯示工資和補助與部門30僱員的工資和補助完全匹配的所有僱員： 

Sql程式碼  

1.   SELECT ename,sal,comm,dptno FROM emp WHERE (sal, nvl(comm,-1)) IN (SELECT sal, nvl(comm,-1) FROM emp WHERE deptno=30);  


    執行非成對比較時，應該使用多個多行子查詢來實現。 
     如顯示工資匹配於部門30工資列表、補助匹配於部門30補助列表的所有僱員： 

Sql程式碼  

1.   SELECT ename,sal,comm,deptno FROM emp WHERE sal IN (SELECT sal FROM emp WHERE deptno=30) AND nvl(comm,-1) IN (SELECT nvl(comm,-1) FROM emp WHERE deptno=30);  


六、相關子查詢與FROM子句中子查詢 
     相關子查詢是指需要引用主查詢表列的子查詢語句，是通過EXISTS謂詞來實現的。 
      如顯示工作在“NEW YORK”的所有僱員： 

Sql程式碼  

1.   SELECT ename,job,sal,deptno FROM emp WHERE EXISTS (SELECT 1 FROM dept WHERE dept.deptno=emp.deptno AND dept.loc='NEW YORK');  


     在FROM子句中使用子查詢時，該子查詢會被作為檢視對待。因此也被稱為內嵌檢視。 
      如顯示高於部門平均工資的僱員資訊： 

Sql程式碼  

1.   SELECT ename,job,sal FROM emp, (SELECT deptno,avg(sal) avgsal FROM emp GROUP BY deptno) dept WHERE emp.deptno=dept.deptno AND sal>dept.avgsal;  


七、合併查詢 
    集合操作符有UNION，UNION ALL， INTERSECT和MINUS。當使用集合操作符時，必須確保不同查詢的列個數和資料型別都要匹配。 
     有以下一些限制： 
1.對於LOB，VARRAY和巢狀表列來說，集合操作符是無效的。 
2.對於LONG列來說，UNION，INTERSECT，MINUS操作符是無效的。 
3.如果選擇列表包含了表示式，則必須為為其指定列別名。 
(一)UNION 
    UNION操作符用於獲取兩個結果集的並集。當使用該操作符時，會自動去掉結果集中的重複行。並且會以第一列的結果進行排序。 

Sql程式碼  

1.   SELECT ename,sal,job FROM emp WHERE sal>2500 UNION SELECT ename,sal,job FROM emp WHERE job='MANAGER';  


(二)UNION ALL 
    UNION ALL操作符獲取兩個結果集的並集，但不會取消重複值，而且也不會以任何列進行排序。 

Sql程式碼  

1.   SELECT ename,sal,job FROM emp WHERE sal>2500 UNION ALL SELECT ename,sal,job FROM emp WHERE job='MANAGER';  


(三)INTERSECT 
    INTERSECT操作符用於獲取兩個結果集的交集。當使用該操作符時，只會顯示同時存在於兩個結果集中的資料，並且會以第一列進行排序。 

Sql程式碼  

1.   SELECT ename,sal,job FROM emp WHERE sal>2500 INTERSECT SELECT ename,sal,job FROM emp WHERE job='MANAGER';  


(四)MINUS 
    MINUS用於獲取兩個結果集的差集。以第一列進行排序。 

Sql程式碼  

1.   SELECT ename,sal,job FROM emp WHERE sal>2500 MINUS SELECT ename,sal,job FROM emp WHERE job='MANAGER';  


八、CASE表示式和倒敘查詢 
     通過CASE表示式，可以避免呼叫過程來完成條件分支操作。 

Sql程式碼  

1.   SELECT ename,sal,CASE WHEN sal>3000 THEN 3 WHEN sal>2000 THEN 2 ELSE 1 END grade FROM emp WHERE deptno=10;  


    預設情況下，當執行查詢操作時，只能看到最近提交的資料。從Oracle9i開始，通過使用倒敘查詢(Flashback Query)特徵，可以檢視到過去某個時間點所提交的資料。注意，如果使用倒敘查詢，那麼要示資料庫必須彩UNDO管理方式，並且初始化引數undo_retention限制了UNDO資料的保留時間。 

Sql程式碼  

1.   SELECT ename,sal FROM emp AS OF TIMESTAMP to_timestamp('2009-01-01 12:12:00','YYYY-MM-DD HH24:MI:SS') WHERE ename='CLARK';  


九、使用WITH子句重用子查詢 
     從Oracle9i開始，通過WITH子句可以給子查詢指定一個名稱，並且使得在一條語句中可以完成所有任務，從而避免使用臨時表。 
      如顯示部門工資總和高於僱員工資總和三分之一的部門名稱及工資總和。 

Sql程式碼  

1.   WITH summary AS (SELECT dname,SUM(sal) AS dept_total FROM emp,dept WHERE emp.deptno=dept.deptno GROUP BY dname)  

2.   SELECT dname, dept_total FROM summary WHERE dept_total>(  

3.   SELECT SUM(dept_total) * 1/3 FROM summary);  


十、oracle對記錄進行去重sql 

Sql程式碼  

1.   DELETE FROM [TABLE_NAME]   

2.   WHERE ROWID NOT IN (  

3.      SELECT MIN(ROWID) FROM [TABLE_NAME]   

4.      GROUP BY [COL1,COL2,COL3...]  

5.   );   

 

統計酒店名不重複的總數

Sql程式碼  

1.   SELECT COUNT(DISTINCT(hotel_id)) FROM t_app_travel_hotel_comment; 


十一、SQL排序後的NULL位置 
      可以把那些NULL值假想為所有內容中值是最大的，因此，升序排序後NULL值在最後，倒序排序後NULL值在最前！ 
       當指定"NULLS  FIRST"時，無論是升序排序還是倒序排序，NULL值都會排列在最前面；當指定“NULLS  LAST”時，無論是升序排序還是倒序排序，NULL值都會排列在最後面。 

Sql程式碼  

1.   SELECT * FROM t ORDER BY x DESC NULLS LAST;  

 

 

相關文章