一、基本的查詢回顧練習

前面兩章節整理了許多關于查詢用到的語句和關鍵字，以及MySQL的內置函數，我們先用一些簡單的查詢練習去回顧之前的知識

1.?前提準備

同樣是前面用到的用于測試的表格和數據，一張學生表和三張關于雇員信息表

雇員信息表

2. 測試樣例

2.1 查詢工資高于500或崗位為MANAGER的雇員，同時還要滿足他們的姓名首字母為大寫的J

2.2 按照部門號升序而雇員的工資降序的排序

2.3 顯示工資最高的員工名字和崗位

2.4 顯示工資高于平均工資的人

同樣是利用子查詢，先算到平均工資是多少，然后條件篩選出高于該工資的人

2.5 顯示每個部門的平均工資和最高工資

要不同部門的顯示平均工資和最高工資，也就是要按部門先分組，再聚合

2.6 顯示平均工資低于2000的部門號和它的平均工資

首先，我們要顯示的是部門號和它的平均工資，要求是部門平均工資低于2000，因此還要得到各個部門的平均工資，并且進行條件篩選

2.7 顯示每種崗位的雇員總數，平均工資

根據要求，應該要顯示崗位、崗位雇員總數、平均工資，需要按崗位分組，聚合

二、多表查詢

在實際開發中，往往要描述一些較為復雜的系統時，往往數據不止被記錄在一張表格內，例如我們一直在使用的雇員信息表，就有三張，因此我們還需要多表進行查詢

先介紹一個概念——笛卡爾積

笛卡爾積本質就是一種窮舉的策略，在對兩個表進行聯合查詢的時候，實際就是表一的第一條記錄和表二的所有記錄進行拼接，然后是表一第二條記錄和表二的所有記錄拼接，以此類推形成新表，這種窮舉拼接的策略就是兩個表的笛卡爾積

我們在需要用到多個表的數據時，聯合查詢會得到多個表的笛卡爾積，在其中通過條件篩選出我們需要的數據，就是多表查詢，接下來還是利用雇員信息表來練習多表查詢

1. 顯示雇員名、雇員工資以及所在部門的名字

雇員名字和雇員工資在emp表中，而部門名字在dept表中，因此需要聯合查詢

2. 顯示部門號為10的部門名，員工名和工資

首先我們需要顯示部門名，員工名，還有工資，需要聯合查詢，然后是條件篩選出部門號為10的

3. 顯示各個員工的姓名，工資，及工資級別

需要顯示的是員工名字，工資和工資級別，因此要用到emp和salgrade這兩個表

三、自查詢

自查詢實際就是一個表自己和自己的笛卡爾積，這種查詢方式也有一定的應用場景，要注意，由于兩個表都是同一張，因此命名上會有沖突，需要對表的名字進行重命名

應用場景

顯示員工FORD的上級領導的編號和姓名

我們要找到員工FORD的領導信息，可以通過FORD信息中的領導編號去找到領導的信息，可以使用子查詢的方式先找到FORD的領導編號，再找領導信息，同時也可以使用自查詢的方式，去條件篩選出領導的信息

四、子查詢

子查詢在前面一些例子中已經用到過了，接下來詳細整理和說明子查詢

1.單行子查詢

單行子查詢就是前面用到的，子查詢結果返回單行記錄的情況

例子：顯示SMITH同一部門的員工

首先我們要通過子查詢先找到SMITH所屬的部門，再進行條件篩選

2.多行子查詢

當子查詢返回結果為多行記錄時，條件篩選的條件是一個集合，此時我們針對不同的情況，有三個關鍵字in、all、any去鏈接篩選條件：

in：表示篩選出該條件集合中的數據

all：表示前面的表達式需要滿足和該集合中所有元素都作用且為true才符合篩選條件

any：表示前面的表達式和該集合中的元素任意一個滿足條件即可符合篩選條件

例子：

a.?查詢和10號部門的工作崗位相同的雇員名字、崗位、工資、部門號，但不包含10號本身

分析：要顯示的是ename、job、sal、deptno，篩選條件是和部門號10相同的崗位，需要先找到和十號部門相同的崗位集合，條件是崗位是該集合其中一個即可符合條件，因此使用in，同時要去掉十號本身

b. 顯示工資比部門30的所有員工的工資高的員工的名字、工資和部門號

分析：要顯示的是員工名字、工資、部門號，條件是比部門30所有員工工資要高，我們可以選擇找到該部門工資最高為多少，但這里為了示范all的使用，我們采用另一種思路，我們先找到30號部門所有員工工資的集合，然后篩選條件為大于該集合中的所有元素，因此用all

c. 顯示工資比部門30的任意員工的工資高的員工的姓名、工資和部門號（結果包括部門30的人）

分析：顯示ename、sal、deptno，要求比部門30的任意員工工資都高，先子查詢找到該部門工資的集合，條件篩選是大于該部門的任意一人，因此使用any連接條件

3.多列子查詢

子查詢返回的結果為多列數據時就叫多列子查詢

案例：查詢和SMITH的部門和崗位完全相同的所有雇員，不含SMITH本人

分析：條件是和SMITH的部門和崗位都完全相同，因此需要先找到SMITH的崗位和部門是什么，該子查詢返回結果是兩列的，此時返回的結果的格式要認為是（v1，v2，...）這種類型，在做條件判斷時，左側也應該是這種格式

4.在from子句中使用子查詢

我們可以將子查詢的結果看作一張臨時表，子查詢的結果同樣可以放在from后面去滿足一些查詢要求

案例：

a.顯示每個高于自己部門平均工資的員工的姓名、部門、工資、平均工資

分析：先通過分組子查詢可以查到各個部門的部門平均工資，然后可以對emp和子查詢結果進行笛卡爾積，這樣每個員工信息后面都附加上了其所在部門的平均工資，然后再條件篩選即可

select ename,emp.deptno,sal,avg_sal from emp,(select deptno,avg(sal) as avg_sal from emp group by deptno) as tmp where emp.deptno=tmp.deptno and emp.smp.sal>tmp.avg_sal;

b. 查找每個部門工資最高的人的姓名、工資、部門、最高工資

分析：先子查詢到每個部門的最高工資，然后再和emp笛卡爾積得到新表進行篩選

select ename,sal,deptno,max_sal from emp,(select deptno dt,max(sal) max_sal from emp group by deptno) tmp where emp.deptno=tmp.dt and emp.sal=tmp.max_sal;

c. 顯示每個部門的信息（部門名、編號、地址）和人員數量

分析：我們先將需要的信息都查詢出來，我們需要每個部門的部門名、編號以及地址，這些信息都在dept表上，人員數量則是對emp表格中根據部門不同分組然后聚合

select dept.deptno,dept.dname,dept.loc,num from dept,(select deptno,count(*) as num from emp group by deptno) as tmp where dept.deptno=tmp.deptno;

5.合并查詢

在實際應用中，為了合并多個select的查詢結果，可以使用集合操作符 union 和 union all

（1）union

該操作符用于取得兩個結果集的并集，當使用該操作符時，會自動去掉結果集中的重復行。

案例：將工資大于2500或職位是MANAGER的人找出來

（2）union all（不去重）

該操作符用于取得兩個結果集的并集，當使用該操作符時，不會去掉結果集中的重復行。

案例：將工資大于2500或職位是MANAGER的人找出來

總結

本篇更進一步的整理介紹了查詢的操作，對一些復雜的情況進行各種復合查詢的操作，以及提供了大量的練習和樣例