程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> Oracle數據庫初級學習 2,oracle數據庫

Oracle數據庫初級學習 2,oracle數據庫

編輯:Oracle教程

Oracle數據庫初級學習 2,oracle數據庫


今天我們介紹Oracle數據庫中剩余的查詢方法,今天的查詢方法會比昨天的更為復雜一些(PS:我也是個初學者,請見諒。。)。

一、分組函數

  分組函數是為了區分同一個表中的不同數據而建立,其關鍵字為GROUP BY,其中可以包含任意數目的列。

  舉例:

  SELECT DEPTNO.MAX(SAL)FROM EMP GROUP BY DEPTNO;

  特別注意一旦用GROUP 分組後,前後的數值要保持一致,也就是說GROUP BY 是以DEPTNO為依據進行分組的話,SELECT 後面也必須有DEPTNO。

  同時,組函數例外。也就是說SELECT後面可以跟任意的組函數。

  還記得我們的組函數有哪些嗎?

  •avg()à返回某列的平均值   •min()à返回某列的最小值   •max()à返回某列的最大值   •sum()à返回某列值的和   •count()à返回某列的行數      下面是一些具體實例。   SELECT DEPTNO.SAL FROM EMP GROUP BY DEPTNO,SAL;(錯誤,這樣進行分組將毫無意義,DEPTNO和SAL之間沒有任何練習)


  SELECT DEPTNO,MIN(SAL) FROM EMP GROUP BY DEPTNO;

  SELECT COMM,COUNT(*) FROM EMP GROUP BY COMM ORDER BY COMM;

  SELECT DEPTNO,AVG(SAL)FROM EMP GROUP BY DEPTNO ORDER BY AVG(SAL);

  

  

  

 

  特別注意:GROUP BY  子句必須出現在where子句之後,order by 子句之前,如果GROUP BY 之後還需要進行篩選,則采用關鍵字 HAVING。

   也就是說,SELECT 語句可以寫成  SELECT* FROM * WHERE* GROUP BY * HAVING* ORDER BY;

    程序的具體執行順序為  FROM WHERE GROUP BY HAVING SELECT ORDER BY

     同時考慮到程序的優化,能用WHERE過濾的數據堅決不用HAVING。

 

---分別統計30部門中每個JOB的最高薪資,並按照JOB排列。

SELECT JOB,MAX(SAL) FROM EMP WHERE DEPTNO=30 GROUP BY JOB ORDER BY JOB;

---查詢每個部門,每種工作的平均薪資

SELECT DEPTNO,JOB,AVG(SAL) FROM EMP GROUP BY DEPTNO,JOB ORDER BY DEPTNO;

--先執行WHERE 再執行GROUP

---查詢名字中有A的員工,每個部門 每種工作的平均薪資,並且平均薪資大於1500

SELECT DEPTNO,JOB,AVG(SAL) FROM EMP WHERE ENAME LIKE '%A%'GROUP BY DEPTNO,JOB HAVING AVG(SAL)>1500;

 

二、多表關聯查詢

  目前主流的SQL語句中分為SQL92和SQL99,我們先介紹SQL 92的相關內容。

  

  /*
  SQL92多表關聯查詢
  等值關聯
  非等值關聯
  外連接
  自連接

  */

  -------等值關聯

  

  SELECT * FROM EMP;
  SELECT * FROM DEPT;
  SELECT * FROM EMP,DEPT;
  SELECT * FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO;
  SELECT EMP.ENAME,EMP.DEPTNO,DEPT.DNAME FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO;
  SELECT E.ENAME,E.DEPTNO,D.DNAME,D.LOC FROM EMP E,DEPT D WHERE E.DEPTNO = D.DEPTNO;

 

  SELECT E.ENAME,D.LOC FROM EMP E,DEPT D
  WHERE E.DEPTNO = D.DEPTNO
  AND E.ENAME LIKE '%A%'
  AND D.DEPTNO IN(10,30);

 

  ------非等值關聯

  

  SELECT E.ENAME,E.SAL,S.GRADE FROM EMP E,SALGRADE S
  WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;

  


  為了連接N張表,至少需要N-1個條件。

 

  


  在做等值關聯的時候可能會因為兩表沒有共同的條件導致部分數據丟失,這是就要用到外連接
  ---外連接就是為了解決一張表中有數據,另一張表中沒有數據

  在SQL 92中,外連接分為左外連接和右外連接。

  SELECT * FROM EMP E,DEPT D
  WHERE E.DEPTNO=D.DEPTNO(+);

  此為左外連接,可顯示EMP中有,但DEPT表中無的數據

 

  SELECT * FROM EMP E,DEPT D
  WHERE E.DEPTNO(+)=D.DEPTNO;

  此為右外連接,可顯示EMP中無,DEPT中有的數據

  如果還不好記憶的話,可以記下面的一句話。


  要顯示誰,就在對方後面加一個加號。

   看看下圖中有啥不同?

   

 

 

----自連接就是為了解決兩個要匹配的列都在一張表上面

SELECT E.ENAME,M.ENAME FROM EMP E,EMP M
WHERE E.MGR=M.EMPNO;

 

  99語法
    笛卡爾積
    等值連接
    自然:會將名字,類型相同的類自動做等值連接
        簡便
        有可能存在隱患(表的結構有可能被修改)
    字段
      USING
    非等值連接
      ON
    外連接
      左外
      右外
      全外

  


SELECET * FROM EMP E,DEPT D;(92式)

SELECT * FROM EMP E CROSS JOIN DEPT D;(99式)

用CROSS JOIN 代替 ,

 

SELECT* FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO;(92式)

SELECT* FROM EMP E NATURAL JOIN DEPT D;(99式)

NATURAL JOIN 又成為自然連接,會將名字,類型相同的類自動做等值連接,缺點是有可能存在隱患(表的結構有可能被修改)

所以,我們經常可以采用下面一種方式

 


SELECT DEPTNO E.ENAME,D.DNAME FROM EMP E JOIN DEPT D USING(DEPTNO);(99式)(自動用DEPTNO做關聯,並且不能加限定詞,就是不能加E.或者D)

SLEECT* FROM EMP E, DEPT D WHERE E.DEPTNO=D.D.DEPTNO;(92式)

 

 

SELECT * FROM EMP E,SALGRADE S WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;


SELECT * FROM EMP E JOIN SALGRADE S ON ( E.SAL BETWEEN S.LOSAL AND S.HISAL);
(關鍵字JOIN ON代替了BETWEEN)

 

------外連接

---左外
SELECT * FROM EMP E,DEPT D
WHERE E.DEPTNO=D.DEPTNO(+);

SELECT * FROM EMP E LEFT OUTER JOIN DEPT D USING(DEPTNO);
(關鍵字 OUTJOIN USING)

---右外
SELECT * FROM EMP E,DEPT D
WHERE E.DEPTNO(+)=D.DEPTNO;

SELECT * FROM EMP E RIGHT OUTER JOIN DEPT D USING(DEPTNO);
(關鍵字 OUTER JOIN USING)

---全外 99特點,92沒有

SELECT * FROM EMP E FULL OUTER JOIN DEPT D USING(DEPTNO);

 

---自連接

SELECT E.ENAME,M.ENAME FROM EMP E JOIN EMP M ON(E.MGR = M.EMPNO);(99式)
(關鍵字 JOIN ON)代替了,where


SELECT E.ENAME,M.ENAME FROM EMP E,EMP M
WHERE E.MGR=M.EMPNO;(92式)

 

/*

子查詢
單行子查詢 返回一行
多行子查詢

只能運用三個運算符 IN SOME ALL

將查詢的結果抽象成一張表
*/

SELECT MAX(SAL) FROM EMP;
SELECT ENAME FROM EMP WHERE SAL=(SELECT MAX(SAL) FROM EMP);
理解子查詢的關鍵在於把子查詢當作一張表來看待。外層的語句可以把內嵌的子查詢返回的結果當成一張表使用。
子查詢要用括號括起來
將子查詢放在比較運算符的右邊(增強可讀性)

SELECT ENAME FROM EMP WHERE SAL>=(SELECT AVG(SAL) FROM EMP);

SELECT * FROM EMP WHERE DEPTNO = 30;

SELECT * FROM EMP WHERE DEPTNO <> 30 AND SAL IN(SELECT SAL FROM EMP WHERE DEPTNO = 30);

SELECT * FROM EMP WHERE DEPTNO <> 30 AND >=SAL SOME(SELECT SAL FROM EMP WHERE DEPTNO = 30);

SELECT * FROM EMP WHERE DEPTNO <> 30 AND SAL>= ALL(SELECT SAL FROM EMP WHERE DEPTNO=30);

some 只要大於最小的選項
ALL 則要大於最大的


SELECT * FROM EMP WHERE SAL>= ALL(SELECT SAL FROM EMP WHERE DEPTNO=20)AND DEPTNO=20;(最大)

SELECT * FROM EMP WHERE SAL>= some(SELECT SAL FROM EMP WHERE DEPTNO=20)AND DEPTNO=20;(最小)


SELECT DEPTNO,AVG(SAL) FROM EMP WHERE DEPTNO IS NOT NULL GROUP BY DEPTNO;

SELECT DA.DEPTNO,S.GRADE FROM SALGRADE S(
SELECT DEPTNO,AVG(SAL) FROM EMP WHERE DEPTNO IS NOT NULL GROUP BY DEPTNO) DA
WHERE DA.AVGSAL BETWEEN S. LOSAL AND S. HISAL;

 

 

 

 

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved