Oracle數據基礎(二)

一. SQL基礎查詢

基本查詢語句

    1. FROM子句

          SELECT * FROM TABLE;

    2. 使用別名

    3. WHERE子句

          WHERE子句中使用比較操作符限制查詢結果

    4. SELECT子句

查詢條件

    1. 使用>,<,>=,<=,!=,<>,=

          數據庫中標準的不等于：<>

    2. 使用AND，OR關鍵字

               SELECT * FROM table WHERE sal > 1000 AND job = ‘clerk’;

               SELECT * FROM table WHERE sal > 1000 OR job = ‘clerk’;

          同時使用AND和OR時，AND的優先級高于OR

    3. 使用LIKE條件(模糊查詢)

          LIKE需要借助兩個通配符：

               — %：表示0到多個字符

               — _：標識單個字符

               SELECT name, job FROM amp WHERE name LIKE ‘_A%’;—單詞的第二個字符是A后面未知 

    4. 使用IN和NOT IN

          比較操作符IN(list)用來取出符合列表范圍中的數據

          List表示值列表，當列或表達式匹配于列表中的任何一個值時，條件為TRUE，該條記錄則被顯示出來    

          IN可以理解為一個范圍比較操作符，只不過這個范圍是一個指定的值列表

          NOT IN(list)取出不符合此列表中的數據記錄

    5. BETWEEN…AND...

          BETWEEN…(小的值)AND...(大的值)：操作符用來查詢符合某個值域范圍條件的數據

          最常見的是使用在數字類型的數據范圍上，但對字符類型和日期類型數據也同樣適用

               SELECT name, sal FROM amp WHERE sal BETWEEN 1000 AND 2000;

    6. 使用IS NULL和IS NOT NULL

          NULL比較時不能使用”=“，必須使用IS

    7. 使用ANY和ALL條件

          ALL和ANY不能單獨使用，需要配合單行比較操作符>,<=,<,<=一起使用

               > ANY：大于最小

               < ANY：小于最大

               > ALL：大于最大

               < ALL：小于最小

               eg：SELECT name, job sal FROM amp WHERE sal > ANY (2000,3400,4000);

          與IN的相同之處：給定一組數據進行比較

          區別：IN是與給定的數據進行等值或不等值比較

                    ANY和ALL是與給定數據進行范圍比較

    8. 查詢條件中使用表達式和函數

          查詢條件中可以使用算術表達式：+, -, * ,/。優先級符合四則運算默認優先級，可使用括號改變優先級

    9. 使用DISTINCT過濾重復

          對多列去重，可以達到的效果是，這幾列的組合是不重復的。

          SELECT DISTINCT deptno from table ;—去掉重復值

排序

    1. 使用ORDER BY子句

          對數據按一定規則進行排序操作，使用ORDER BY子句

          必須出現在SELECT中的最后一個子句

    2. ASC和DESC

          ASC：升序

          DESC：降序

    3. 多列排序

          使用多列進行排序時，左邊的列排序優先級高于右面的列

          eg：SELECT name,sal,deptno FROM emp ORDER BY sal,deptno DESC;

          首先按照sal的升序排列，當sal的值相同時，按照deptno的降序排列。若sal的值全表沒有重復值，則第二列的排序會被忽略。

聚合函數

    1. 聚合函數

          多行數據參與運算返回一行結果，也稱作分組函數，多行函數，集合函數。

    2. MAX和MIN

          用來取得列或表達式的最大，最小值

          可用于統計任何數據類型，包括數字，字符和日期

               SELECT MAX(sal) max_sal, MIN(sal) min_sal FROM emp;

          MAX和MIN是不能出現在WHERE語句中的。

    3. AVG和SUM

          聚合函數忽略空值。所以得到的平均值會少數據。

               eg：SELECT AVG(NVL(comm, 0)) FROM amp;—若為空值則使其值為0

    4. COUNT

          用于統計記錄條數

          SELECT COUNT(comm) FROM amp;—忽略空值，若字段comm為kong則不記錄在內

    5. 聚合函數對空值的處理

分組

    1. GROUP BY子句

          SELECT MAX(sal), MIN(sal), SUM(sal), AVG(sal) FROM emp GROUP BY deptno; —根據部分分組，得到每組的最高薪資，最低薪資，平均薪資，總薪資

          根據值相同的記錄作為一組，進行統計；

          一般對部分數據重復的值進行分組，沒有重復的數據分組沒有意義；

          只要在SELECT中使用了分組函數，那么，SELECT中其它非分組函數的列若出現，則必須同時出現在GROUP BY子句中，反過來沒有限制。

          若GROUP BY中出現了多列，那么就按照這幾列組合值相同的記錄看作一組。

    2. 分組查詢

    3. HAVING字句

          WHERE是用于整張表逐行過濾用的；

          HAVING用于在進行分組查詢后，二次過濾數據的；

          HAVING中可以使用分組函數的結果進行過濾；

          HAVING不能獨立存在，必須跟在GROUP BY之后。

          eg：篩選出部門平均工資高于1800的部門：SELECT deptno, AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal) > 1800

    SQL順序：SELECT —> FROM —> WHERE —> GROUP BY —> HAVING —> ORDER BY

查詢語句執行順序

    1. 查詢語句執行順序（可以改善代碼執行效率）

          查詢語句的執行順序依下列子句次序：

          1.1 from子句：執行順序為從后往前，從右到左

               數量較少的表盡量放在后面

          1.2 where子句：執行順序為自下而上，從右到左

               將能過濾掉最大數量記錄的條件寫在where子句的最右

          1.3 group by 執行順序從左往右分組

               最好在GROUP BY前使用WHERE將不需要的記錄在GROUP BY之前過濾掉

          1.4 having子句：消耗資源

               盡量避免使用，HAVING會在檢索出所有記錄之后才對結果進行過濾，需要排序等操作

          1.5 select子句：少用*號，盡量取字段名稱

               ORACLE在解析的過程中，通過查詢數據字典將*號依次轉換成所有的列名，消耗時間

          1.6 order by子句：執行順序為從左到右排序，消耗資源

二. SQL關聯查詢

關聯基礎

    1. 關聯的概念

          查詢兩個或兩個以上數據表或視圖的查詢叫做連接查詢

          連接查詢通常建立在存在相互關心的父子表之間

    2. 笛卡爾積

          笛卡爾積指做關聯操作的每個表的每一行都和其它表的每一行做組合，假設兩個表的記錄條數分別是X和Y，笛卡爾積將返回X*Y條記錄。

          兩張表不加連接條件關聯時會產生笛卡爾積。

    3. 等值連接

          SELECT table1.column, table2.column FROM table1, table2 where table1.column = table2.column;（column分別為table1和table2的主鍵和外鍵）

          NATURAL JOIN：自動尋找兩張表列名相同的字段（且這兩個字段互為主外鍵關系時）做等值連接。注意：兩張表中應當只有一列名字相同才可以使用自然連接

關聯查詢

    1. 內連接

    2. 外連接

          應用場景：A表中某個字段的值在B表中相應該字段的值找不到時使用

          SELECT table1.column, table2.column FROM table1 LEFT | RIGHT | FULL | OUTER  JOIN  table2 ON table1.column = table2.column2;

          LEFT：左邊表為準，右邊表不足補齊；

          RIGHT：右邊表為準，左邊表不足補齊

    3. 全外連接

          左邊表不足補齊，右邊表不足補齊

    4. 自連接

          最常用于員工上下級關系

          將一張表當成兩張表

          eg：SELECT e.name||’的領導是’||m.name FROM amp e, emp m WHERE e.mgr = m.empno;

三. SQL高級查詢

子查詢

當前查詢需要建立在另一個查詢的結果基礎之上，這里就要利用到子查詢

    1. 子查詢在WHERE子句中

          在SELECT查詢中，在WHERE查詢條件中的限制條件不是一個確定的值，而是來自于另一個查詢的結果

          為了給查詢提供數據而首先執行的查詢語句叫做子查詢

          子查詢是嵌入在其它SQL語句中的SELECT語句，大部分時候出現在WHERE子句中

          子查詢嵌入的語句稱作主查詢或父查詢

          主查詢可以是SELECT語句，也可以是其它類型的語句比如：DML或DDL

          根據返回結果不同，子查詢可以分為單行子查詢，多行子查詢及多列子查詢

          單行單列和多行單列子查詢通常用在WHERE子句中作為條件

          多行多列子查詢通常用在FROM子句中

          >, <, >=, <=, =, <>這些比較都只能使用單行單列子查詢

          比較多行單例子查詢時可以使用ANY或ALL或IN

               eg：SELECT name, sal FROM amp WHERE sal > ANY(SELECT sal FROM amp WHERE job = ’SALESMAN’);--大于其中之一

                      SELECT name, sal FROM amp WHERE sal > ALL(SELECT sal FROM amp WHERE job = ’SALESMAN’);--大于最大

                      SELECT name, sal FROM amp WHERE sal > IN(SELECT sal FROM amp WHERE job = ’SALESMAN’);--等于其中之一

          在子查詢中需要引用到主查詢的字段數據，使用EXISTS關鍵字

          EXISTS后邊的子查詢至少返回一行數據，則整個條件返回TRUE

               eg：查詢比本部門平均工資高的那些員工

                         SELECT e.1deptno, e1.name, e1.sal FROM emp e1

                         WHERE e1.sal > (SELECT AVG(sal) FROM emp e2 WHERE e1.deptno = e2.deptno);

    2. 子查詢在HAVING子句中

          查詢列出最低薪水高于部門30的最低薪水的部門信息：

               SELECT deptno, MIN(sal) min_sal FROM emp

               GROUP BY deptno

               HAVING MIN(sal) > (SELECT MIN(sal) FROM emp WHERE deptno = 30);    

    3. 子查詢在FROM部分

          FROM中出現子查詢，則需要將子查詢結果當做一張表看待，再從中查詢想要的結果。此時需注意：子查詢的SELECT語句中，出現了非字段名的字段，通常是表達式或者函數，那么一定要給他們加上別名。

               eg：查詢出薪水比本部門薪水高的員工信息：

               SELECT e.deptno, e.name, e.sal FROM emp e, (SELECT deptno, AVG(sal) avg_sal FROM emp GROUP BY deptno) x

               WHERE e.deptno = x.deptno and e.sal > x.avg_sal ORDER BY e.deptno;

    4. 子查詢在SELECT部分

          外連接的另一種寫法，不常用

               eg：查詢姓名，薪資，部門名

               SELECT name, sal, (SELECT name FROM deptno d WHERE d.deptno = e.deptno) FROM amp e;  

分頁查詢

    1. ROWNUM

          被稱作偽列，用于返回標識行數據順序的數字；

          只能從1計數，不能從結果集中直接截取

          ROWNUM是當oracle進行select進行select當查詢表數據時，確定一條數據需要時，才會對其進行編號（偽劣的值），這就導致，沒有數據被查出前，rownum永遠沒有值。所以rownum不能在第一次查詢時作為where條件。

          rownum：有數據了才有值

          where里出現：有值才有數據

               eg：SELECT ROWUM,empno,name,sal FROM emp WHERE rownum > 3;--查詢不到結果

    2. 使用子查詢進行分頁

         分頁三步：

          a. 排序

          b. 編號

          c. 取范圍

               eg：

                    a. SELECT empno, name, sal FROM emp ORDER BY empno;

                    b. SELECT ROWNUM rw, e.* FROM () e;

                    c. SELECT * FROM () WHERE rw BETWEEN 1 AND 3;

                     SELECT * FROM (SELECT ROWNUM rw, e.* FROM (SELECT empno, name, sal FROM emp ORDER BY empno) e) WHERE rw BETWEEN 1 AND 3;

                    分頁算法：page頁數，pagesize一頁的條數

                    起始位置：(page-1)*pagesize+1

                    結束位置：page*pagesize

                    MYSQL：limit(1,3)

    3. 分頁與ORDER BY

DECODE函數

    1. DECODE函數基本語法

          DECODE(expr, search2, result1[, search3, result2…][,default])

          DECODE用于比較參數expr的值，如果匹配到哪一個search條件，就返回對應的result結果

          可以有多組search和result對應關系，如果任何一個search條件都沒有匹配到，則返回最后default的值

          default參數是可選的，如果沒有提供default參數值，當沒有匹配到時，將返回NULL

    2. DECODE函數在分組查詢中的應用

          eg：查詢職員表，根據職員的職位計算獎金金額，當職位為MANAGER，ANALYST，SALESMAN時，獎勵金額分別是薪水的1.2倍，1.1倍，1.05倍，如果不是這三個職位，則獎勵金額取薪水值

          SELECT name, job, sal, DECODE(job, ‘MANAGER’, sal * 1.2,

                                                                        ‘ANALYST’, sal * 1.1,

                                                                        ’SALESMAN’, sal * 1.05,

                                                                         sal) bonus 

          FROM emp;

          和DECODE函數功能相似的有CASE語句，實現類似于if-else的操作：

          SELECT name, job, sal, CASE job WHEN ‘MANAGER’ THEN sal * 1.2

                                                                  WHEN  ‘ANALYST’ THEN sal * 1.1

                                                                  WHEN  ’SALESMAN’ THEN sal * 1.05

                                                                   ELSE  sal END bonus 

          FROM emp;

          DECODE用在ORDER BY時，可以自定義排序規則：

          SELECT * FROM dept ORDER BY DECODE(name, ‘OPERATIONS’, 0, ‘ACCOUNTING’, 1, ’SALES’, 2, 3);

排序函數

    1. ROW_NUMBER

          ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY col2)

          表示根據col1分組，在分組內部根據col2排序

          此函數計算的值就表示每組內部排序后的順序編號，組內連續且唯一

          Rownum是偽列，ROW_NUMBER功能更強，可以直接從結果集中取出子集

          eg：根據部門分組，部門內薪資倒序，部門內生成連續且唯一編號

               SELECT name, sal, ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY sal DESC) rank FROM emp;

    2. RANK

          生成組內不連續且不唯一的編號，排序的列若值相同，會得到相同的編號

          不連續排序，如果有并列第二，下一個排序將是四

          eg：根據部門分組，部門內薪資倒序，部門內生成不連續且不唯一編號

               SELECT name,sal,deptno RANK() OVER(PARTITION BY deptno ORDER BY sal DESC) rank FROM emp;

    3. DENSE_RANK

          生成組內連續但不唯一的編號，排序的列若值相同，會得到相同的編號

          連續排序，如果有并列第二，下一個排序將是三，這一點和RANK的不同，RANK是跳躍排序

          eg：根據部門分組，部門內薪資倒序，部門內生成連續但不唯一編號

               SELECT name,sal,deptno DENSE_RANK() OVER(PARTITION BY deptno ORDER BY sal DESC) rank FROM emp;

集合操作

    1. UNION，UNION ALL

          為了合并多個SELECT語句的結果，可以使用集合操作符，實現集合的并，交，差

          集合操作符包括UNION，UNION ALL，INTERSECT和MINUS

          多條作集合的SELECT語句的列的個數和數據類型必須匹配

          ORDER BY子句只能放在最后的一個查詢語句中

          集合操作的語法：

               SELECT statement1 [UNION | UNION ALL |INTERSECT | MINUS] SELECT statement2;

          UNION:statement1和statement2重復的結果合并，重復的結果出現一次

          UNION:statement1和statement2重復的結果不合并，重復的結果有幾個就出現幾次

    2. INTERSECT

          獲得兩個結果的交集，只有同時存在于兩個結果集中的數據才被顯示輸出

          使用INTERSECT操作符后的結果集會以第一列的數據作升序排序

    3. MINUS

          獲取兩個結果集的差集

          只有在第一個結果集中存在，在第二結果集中不存在的數據才會被顯示出來，也就是結果集一減去結果集二的結果

高級分組函數

    1. ROLLUP

          eg：計算出每月總和，每年總和

          SELECT year_id, month_id, SUM(sales_value) AS sales_value FROM sales_tab GROUP BY ROLLUP(year_id, month_id) ORDER BY year_id, month_id;

    2. CUBE

          GROUP BY CUBE(a,b,c)

          對CUBE的每個參數，都可以理解為取值為參與分組和不參與分組兩個值的一個維度，所有維度取值組合的集合就是分組后的集合

          對于n個參數的CUBE，組合形式有2^n次，如果是a，b，c則是2^3種組合

          GROUP BY CUBE(a, b, c)，首先是對(a,b,c)進行GROUP BY，然后依次是(a,b)，(a,c)，(a)，(b,c)，(b)，(c)，最后對全表進行GROUP BY操作，一共是2^3=8次分組

          eg：SELECT a,b,c,SUM(D) FROM test GROUP BY CUBE(a,b,c)等價于：(等價于，但是內部運行機制不同于UNION ALL，其效率遠高于UNION ALL)

                  SELECT a,b,c,SUM(D) FROM test GROUP BY a,b,c

                  UNION ALL

                  SELECT a,b,NULL,SUM(D) FROM test GROUP BY a,b

                  UNION ALL

                  SELECT a,NULL,c,SUM(D) FROM test GROUP BY a,c

                  UNION ALL

                  SELECT a,NULL,NULL,SUM(D) FROM test GROUP BY a

                  UNION ALL

                  SELECT NULL,b,c,SUM(D) FROM test GROUP BY b,c

                  UNION ALL

                  SELECT NULL,b,NULL,SUM(D) FROM test GROUP BY b

                  UNION ALL

                  SELECT NULL,NULL,c,SUM(D) FROM test GROUP BY c

                  UNION ALL

                  SELECT NULL,NULL,NULL,SUM(D) FROM test ;

    3. GROUPING SETS

          GROUPING SETS運算符可以生成與使用單個GROUP BY，ROLLUP或CUBE運算符所生成的結果集相同的結果集

          如果不需要獲得由完備的ROLLUP或CUBE運算符生成的全部分組，則可以使用GROUPING SETS僅指定所需的分組

          GROUPING SETS列表可以包含重復的分組，即允許參數重復

          eg：GROUP BY GROUPING SETS(a,a)，則對a進行2次GROUP BY

          eg：計算出每年每月，每年每月每日的統計

               SELECT year_id, month_id, SUM(sales_value) AS sales_value FROM sales_tab GROUP BY GROUPING SETS((year_id,month_id,day_id),(year_id, month_id)) ORDER BY year_id, month_id;