SQL查詢總結

coding_1994發表於2018-07-12
SQL
  1.  

1. SQL(基礎查詢)

1.1. 基本查詢語句

1.1.1. FROM子句

SQL查詢語句的語法如下：

  1. SELECT <*, column [alias], …> FROM table;

其中：SELECT用於指定要查詢的列，FROM指定要從哪個表中查詢。如果要查詢所有列，可以在SELECT後面使用*號，如果只查詢特定的列，可以直接在SELECT後面指定列名，列名之間用逗號隔開。例句如下，查詢dept表中的所有記錄：

  1. SELECT * FROM dept;

1.1.2. 使用別名

在SQL語句中可以通過使用列的別名改變標題的顯示樣式，或者表示計算結果的含義，使用語法是列的別名跟在列名後，中間可以加或不加一個“AS”關鍵字。例如：

  1. SELECT empno AS id ,ename "Name", sal * 12 "Annual Salary" FROM emp;

別名可以直接寫，不必用雙引號引起來。但是如果希望別名中區分大小寫字元，或者別名中包含字元或空格，則必須用雙引號引起來。

1.1.3. WHERE子句

在SELECT語句中，可以在WHERE子句中使用比較操作符限制查詢結果，是可選的。

當查詢條件中和數字比較，可以使用單引號引起，也可以不用，當和字元及日期型別的資料比較，則必須用單引號引起。例如查詢部門10下的員工資訊：

  1. SELECT * FROM empWHERE deptno = 10;

查詢職員表中職位是’SALESMAN’的職員：

  1. SELECT ename, sal, job FROM emp WHERE job = 'SALESMAN';

圖-1 用where子句定義查詢條件

1.1.4. SELECT子句

如果只查詢表的部分列，需要在SELECT後指定列名，例如：

  1. SELECT empno, ename, sal, job FROM emp;

圖-2查詢指定的列

1.2. 查詢條件

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

在WHERE子句中的查詢條件，可以使用比較運算子來做查詢。比如：查詢職員表中薪水低於2000元的職員資訊：

  1. SELECT ename, sal FROM emp WHERE sal< 2000;

查詢職員表中不屬於部門10的員工資訊（!=等價於<>）：

  1. SELECT ename, sal, job FROM emp WHERE deptno != 10;

查詢職員表中在2002年1月1號以後入職的職員資訊，比較日期型別資料：

  1. SELECT ename, sal, hiredate FROM emp
  2. WHERE hiredate>to_date('2002-1-1','YYYY-MM-DD');

1.2.2. 使用AND，OR關鍵字

在SQL操作中，如果希望返回的結果必須滿足多個條件，應該使用AND邏輯操作符連線這些條件，如果希望返回的結果滿足多個條件之一即可，應該使用OR邏輯操作符連線這些條件。例如：查詢薪水大於1000並且職位是’CLERK’的職員資訊：

  1. SELECT ename, sal, job FROM emp
  2. WHERE sal> 1000 AND job = 'CLERK';

查詢薪水大於1000或者職位是’CLERK’的職員資訊：

  1. SELECT ename, sal, job FROM emp
  2. WHERE sal> 1000 OR job = 'CLERK';

1.2.3. 使用LIKE條件（模糊查詢）

當使用者在執行查詢時，不能完全確定某些資訊的查詢條件，或者只知道資訊的一部分，可以藉助LIKE來實現模糊查詢。LIKE需要藉助兩個萬用字元：

  • %：表示0到多個字元 
  • _：標識單個字元 

這兩個萬用字元可以配合使用，構造靈活的匹配條件。例如查詢職員姓名中第二個字元是‘A’的員工資訊：

  1. SELECT ename, job FROM emp WHERE ename LIKE '_A%';

圖-3 模糊查詢的結果

1.2.4. 使用IN和NOT IN

在WHERE子句中可以用比較操作符IN(list)來取出符合列表範圍中的資料。其中的引數list表示值列表，當列或表示式匹配於列表中的任何一個值時，條件為TRUE，該條記錄則被顯示出來。

IN頁可以理解為一個範圍比較操作符，只不過這個範圍是一個指定的值列表，NOT IN(list) 取出不符合此列表中的資料記錄。例如查詢職位是MANAGER或者CLERK的員工：

  1. SELECT ename, job FROM emp WHERE job IN ('MANAGER', 'CLERK');

查詢不是部門10或20的員工：

  1. SELECT ename, job FROM emp WHERE deptno NOT IN (10, 20);

1.2.5. BETWEEN…AND…

BETWEEN…AND…操作符用來查詢符合某個值域範圍條件的資料，最常見的是使用在數字型別的資料範圍上，但對字元型別和日期型別資料也同樣適用。例如查詢薪水在1500-3000之間的職員資訊：

  1. SELECT ename, sal FROM emp
  2. WHERE sal BETWEEN 1500 AND 3000;

1.2.6. 使用IS NULL和IS NOT NULL

空值NULL是一個特殊的值，比較的時候不能使用”=”號，必須使用IS NULL，否則不能得到正確的結果。例如查詢哪些職員的獎金資料為NULL：

  1. SELECT ename, sal, comm FROM emp
  2. WHERE comm IS NULL;

1.2.7. 使用ANY和ALL條件

在比較運算子中，可以出現ALL和ANY，表示“全部”和“任一”，但是ALL和ANY不能單獨使用，需要配合單行比較操作符>、>=、<、<=一起使用。其中：

  • > ANY : 大於最小 
  • < ANY：小於最大 
  • > ALL：大於最大 
  • < ALL：小於最小 

例如，查詢薪水比職位是“SALESMAN”的人高的員工資訊，比任意一個SALESMAN高都行：

  1. SELECT empno, ename, job, sal, deptno
  2. FROM emp
  3. WHERE sal> ANY (
  4. SELECT sal FROM emp WHERE job = 'SALESMAN');

1.2.8. 查詢條件中使用表示式和函式

當查詢需要對選出的欄位進行進一步計算，可以在數字列上使用算術表示式(+、-、*、/)。表示式符合四則運算的預設優先順序，如果要改變優先順序可以使用括號。

算術運算主要是針對數字型別的資料，對日期型別的資料可以做加減操作，表示在一個日期值上加或減一個天數。

查詢條件中使用字串函式UPPER，將條件中的字串變大寫後再參與比較：

  1. SELECT ename, sal, job FROMempWHERE ename = UPPER('rose');

查詢條件中使用算數表示式，查詢年薪大於10w元的員工記錄：

  1. SELECT ename, sal, job FROM empWHERE sal * 12 >100000;

1.2.9. 使用DISTINCT過濾重複

資料表中有可能儲存相同資料的行，當執行查詢操作時，預設情況會顯示所有行，不管查詢結果是否有重複的資料。當重複資料沒有實際意義，經常會需要去掉重複值，使用DISTINCT實現。例如查詢員工的部門編碼，包含所有重複值：

  1. SELECT deptno FROM emp;

查詢員工的部門編碼，去掉重複值：

  1. SELECT DISTINCT deptno FROM emp;

DISTINCT後面的列可以組合查詢，下例查詢每個部門的職位，去掉重複值。注意是deptno和job聯合起來不重複：

  1. SELECT DISTINCT deptno, job FROM emp;

圖-4用distinct去掉重複的列

1.3. 排序

1.3.1. 使用ORDER BY字句

對查詢出的資料按一定規則進行排序操作，使用ORDER BY子句。語法如下：

  1. SELECT <*, column [alias], …>
  2. FROM table 
  3. [WHERE condition(s)]
  4. [ORDER BY column [ASC | DESC]] ;

注意，ORDER BY必須出現在SELECT中的最後一個子句。下例對職員表按薪水排序：

  1. SELECT ename, sal
  2. FROM emp
  3. ORDER BY sal;

圖-5用ORDER BY將查詢結果排序

1.3.2. ASC和DESC

排序時預設按升序排列，即由小及大，ASC用來指定升序排序，DESC用來指定降序排序。

因為NULL值視作最大，則升序排列時，排在最後，降序排列時，排在最前。如果不寫ASC或DESC，預設是ASC，升序排列。例如，按員工的經理升序排序：

  1. SELECT empno, ename, mgr FROM emp
  2. WHERE deptno = 10 ORDER BY mgr;

降序排列，必須指明，按員工的薪水倒序排序：

  1. SELECT ename, sal FROM emp
  2. ORDER BY sal DESC;

1.3.3. 多個列排序

當以多列作為排序標準時，首先按照第一列進行排序，如果第一列資料相同，再以第二列排序，以此類推。多列排序時，不管正序還是倒序，每個列需要單獨設定排序方式。

下例對職員表中的職員排序，先按照部門編碼正序排列，再按照薪水降序排列：

  1. SELECT ename, deptno, sal FROM emp
  2. ORDER BY deptno ASC, sal DESC;

1.4. 聚合函式

1.4.1. 什麼是聚合函式

查詢時需要做一些資料統計，比如：查詢職員表中各部門職員的平均薪水，各部門的員工人數。當需要統計的資料並不能在職員表裡直觀列出，而是需要根據現有的資料計算得到結果，這種功能可以使用聚合函式來實現，即：將表的全部資料劃分為幾組資料，每組資料統計出一個結果。

因為是多行資料參與運算返回一行結果，也稱作分組函式、多行函式、集合函式。用到的關鍵字：

  • GOURP BY 按什麼分組 
  • HAVING 進一步限制分組結果 

1.4.2. MAX和MIN

用來取得列或表示式的最大、最小值，可以用來統計任何資料型別，包括數字、字元和日期。例如獲取機構下的最高薪水和最低薪水，引數是數字：

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

計算最早和最晚的入職時間，引數是日期：

  1. SELECT MAX(hiredate) max_hire, MIN(hiredate) min_hire
  2. FROM emp;

1.4.3. AVG和SUM

AVG和SUM函式用來統計列或表示式的平均值和和值，這兩個函式只能運算元字型別，並忽略NULL值。例如獲得機構下全部職員的平均薪水和薪水總和：

  1. SELECT AVG(sal) avg_sal, SUM(sal) sum_sal  FROM emp;

1.4.4. COUNT

COUNT函式用來計算表中的記錄條數，同樣忽略NULL值。例如獲取職員表中一共有多少名職員記錄：

  1. SELECT COUNT(*) total_num  FROM emp;

獲得職員表中有多少人是有職位的（忽略沒有職位的員工記錄）

  1. SELECT COUNT(job) total_job  FROM emp;

1.4.5. 聚合函式對空值的處理

聚合函式忽略NULL值。即當emp表中的某列有NULL值，比如某新入職員工沒有薪水，比較兩條語句的結果：

  1. SELECT AVG(sal) avg_sal FROM emp;
  2. SELECT AVG(NVL(sal,0)) avg_sal FROM emp;

1.5. 分組

1.5.1. GROUP BY子句

上面的例子都是以整個表作為一組。如果希望得到每個部門的平均薪水，而不是整個機構的平均薪水，需要把整個資料表按部門劃分成一個個小組，每個小組中包含一行或多行資料，在每個小組中再使用分組函式進行計算，每組返回一個結果。語法如下：

  1. SELECT <*, column [alias], …>
  2. FROM table [WHERE condition(s)]
  3. [GROUP BY group_by_expression]
  4. [HAVING group_condition]
  5. [ORDER BY column [ASC | DESC]] ;

其中劃分的小組有多少，最終的結果集行數就有多少。

1.5.2. 分組查詢

圖-6分組查詢

1.5.3. HAVING字句

HAVING子句用來對分組後的結果進一步限制，比如按部門分組後，得到每個部門的最高薪水，可以繼續限制輸出結果。必須跟在GROUP BY後面，不能單獨存在。例如查詢每個部門的最高薪水，只有最高薪水大於4000的記錄才被輸出顯示：

  1. SELECT deptno, MAX(sal) max_sal FROM emp
  2. GROUP BY deptno HAVING MAX(sal) >4000;

1.6. 查詢語句的執行順序

當一條查詢語句中包含所有的子句，執行順序依下列子句次序：

  1. FROM 子句：執行順序為從後往前、從右到左。資料量較少的表儘量放在後面。 
  2. WHERE子句：執行順序為自下而上、從右到左。將能過濾掉最大數量記錄的條件寫在WHERE 子句的最右。 
  3. GROUP BY：執行順序從左往右分組，最好在GROUP BY前使用WHERE將不需要的記錄在GROUP BY之前過濾掉。 
  4. HAVING 子句：消耗資源。儘量避免使用，HAVING 會在檢索出所有記錄之後才對結果集進行過濾，需要排序等操作。 
  5. SELECT子句：少用*號，儘量取欄位名稱。ORACLE 在解析的過程中, 通過查詢資料字典將*號依次轉換成所有的列名, 消耗時間。 
  6. ORDER BY子句：執行順序為從左到右排序，消耗資源。 

2. SQL（關聯查詢）

2.1. 關聯基礎

2.1.1. 關聯的概念

實際應用中所需要的資料，經常會需要查詢兩個或兩個以上的表。這種查詢兩個或兩個以上資料表或檢視的查詢叫做連線查詢，連線查詢通常建立在存在相互關係的父子表之間。語法如下：

  1. SELECT table1.column, table2.column
  2. FROM table1, table2
  3. WHERE table1.column1 = table2.column2;

或者：

  1. SELECT table1.column, table2.column
  2. FROM table1JOIN table2
  3. ON(table1.column1 = table2.column2);

2.1.2. 笛卡爾積

笛卡爾積指做關聯操作的每個表的每一行都和其它表的每一行做組合，假設兩個表的記錄條數分別是X和Y，笛卡爾積將返回X * Y條記錄。當兩個表關聯查詢時，不寫連線條件，得到的結果即是笛卡爾積。例如：

  1. SELECT COUNT(*) FROM emp; --14條記錄
  2. SELECT COUNT(*) FROM dept; --4條記錄
  3. SELECT emp.ename, dept.dnameFROM emp, dept;--56條記錄

2.1.3. 等值連線

等值連線是連線查詢中最常見的一種，通常是在有主外來鍵關聯關係的表間建立，並將連線條件設定為有關係的列，使用等號”=”連線相關的表。例如查詢職員的姓名、職位以及所在部門的名字和所在城市，使用兩個相關的列做等值操作：

  1. SELECT e.ename, e.job, d.dname, d.loc
  2. FROM emp e, dept d
  3. WHERE e.deptno = d.deptno;

2.2. 關聯查詢

2.2.1. 內連線

內連線返回兩個關聯表中所有滿足連線條件的記錄。例如查詢員工的名字和所在部門的名字：

  1. SELECT e.ename, d.dname
  2. FROM emp e, dept d
  3. WHERE e.deptno = d.deptno

上面的語法也可以寫為：

  1. SELECT e.ename, d.dname
  2. FROM emp e JOIN dept d
  3. ON(e.deptno = d.deptno);

2.2.2. 外連線

內連線返回兩個表中所有滿足連線條件的資料記錄，在有些情況下，需要返回那些不滿足連線條件的記錄，需要使用外連線，即不僅返回滿足連線條件的記錄，還將返回不滿足連線條件的記錄。比如把沒有職員的部門和沒有部門的職員查出來。外連線的語法如下：

  1. SELECT table1.column, table2.column
  2. FROM table1 [LEFT | RIGHT | FULL] JOIN table2
  3. ON table1.column1 = table2.column2;

瞭解驅動表的概念。

圖-7左外連線

圖-8右外連線

外連線查詢的例子,Emp表做驅動表:

  1. SELECT e.ename, d.dname
  2. FROM emp e LEFT OUTER JOIN dept d
  3. ON e.deptno = d.deptno;

Dept表做驅動表:

  1. SELECT e.ename, d.dname
  2. FROM emp e RIGHT OUTER JOIN dept d
  3. ON e.deptno = d.deptno;

2.2.3. 全連線

全外連線是指除了返回兩個表中滿足連線條件的記錄，還會返回不滿足連線條件的所有其它行。即是左外連線和右外連線查詢結果的總和。例如：

  1. SELECT e.ename, d.dname
  2. FROM emp e FULL OUTER JOIN dept d
  3. ON e.deptno = d.deptno;

圖-9 全外連線

2.2.4. 自連線

自連線是一種特殊的連線查詢，資料的來源是一個表，即關聯關係來自於單表中的多個列。表中的列參照同一個表中的其它列的情況稱作自參照表。

自連線是通過將表用別名虛擬成兩個表的方式實現，可以是等值或不等值連線。例如查出每個職員的經理名字，以及他們的職員編碼：

  1. SELECT worker.empnow_empno, worker.enamew_ename, manager.empnom_empno, manager.enamem_ename
  2. FROM emp worker join emp manager
  3. ON worker.mgr = manager.empno;

相關文章