1、基本查詢回顧

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

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

1.3、使用年薪進行降序排序

1.4、顯示工資最高的員工的名字和工作崗位

我們把select聚合統計出來的最高薪資作為另一個select的子句。

1.5、顯示工資高于平均工資的員工信息

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

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

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

---

2、多表查詢

實際開發中往往數據來自不同的表，所以需要多表查詢。本節我們用一個簡單的公司管理系統，有三張表EMP、DEPT、SALGRADE來演示如何進行多表查詢。

2.1、顯示雇員名、雇員工資以及所在部門的名字。因為上面的數據來自EMP和DEPT表，因此要聯合查詢。

首先我們來看在from后面跟兩個表名emp和dept獲取的結果是怎么得到的。
我們可以發現是拿著emp的第一行跟dept的每一行進行組合，接著再拿emp的第二行給dept的每一行進行組合，以此類推。也就是將數據進行窮舉組合，我們稱之為笛卡爾積。
這就是將兩張表組合成一張表，所以本質上還是單表查詢。

由于會進行笛卡爾積組合，但是如果部門編號不同的組合就沒有意義，所以加上where子句讓它們兩張表中的部門編號相等。最后我們只需要獲取雇員姓名、雇員薪資、部門名稱即可，如果有沖突需要在前面加上表明.的方式獲取，沒有直接寫屬性名即可。

2.2、顯示部門號為10的部門名，員工名和工資。

2.3、顯示各個員工的姓名，工資，及工資級別。

---

3、自連接

自連接是指在同一張表連接查詢。

直接對兩張同樣的表做笛卡爾積是不行的，需要進行重命名否則無法辨認。同時可以發現兩張表的組合跟上面不同表的組合一樣。并且注意到，這里是后面的表和前面的表進行組合的，在老版本MySQL中是前面的表和后面的表進行組合。

3.1、顯示員工FORD的上級領導的編號和姓名（mgr是員工領導的編號–empno）
有兩種做法，第一種做法使用子查詢：

第二種做法，使用多表查詢：

雖然根據上面查詢結果我們發現組合方式發生了變化，但是只要SQL語句是正確的，照樣可以返回結果。

---

4、子查詢

子查詢是指嵌入在其他sql語句中的select語句，也叫嵌套查詢。

4.1、單行子查詢

單行子查詢：返回一行記錄的子查詢。
如：顯示SMITH同一部門的員工

4.2、多行子查詢

多行子查詢：返回一行記錄的子查詢。

4.2.1、in關鍵字；查詢和10號部門的工作崗位相同的雇員的名字，崗位，工資，部門號，但是不包含10自己的。

使用in來判斷，job是否在返回的多行子查詢中。
如果我們還需要知道部門的名字呢？

MySQL一切皆表，我們上面的查詢結果也是一個表結構，我們把上面的查詢結果另一個查詢的表，并與表dept進行笛卡爾積。
子查詢不僅可以出現在where子句中，還可以出現在from后面。

4.2.2、all關鍵字；顯示工資比部門30的所有員工的工資高的員工的姓名、工資和部門號。
可以查詢部門30最高工資是多少，然后將其結果作為另一個查詢的子查詢：

也可以使用下面這種做法：

all在這里表示，sal要大于子查詢結果里的所有sal。

4.2.3、any關鍵字；顯示工資比部門30的任意員工的工資高的員工的姓名、工資和部門號（包含自己部門的員工）

4.3、多列子查詢

單行子查詢是指子查詢只返回單行單列數據；多行子查詢是指返回多行單列數據，都是針對單列而言的，而多列子查詢則是指查詢返回多個列數據的子查詢語句。

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

4.4、在from子句中使用子查詢

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

首先分組聚合計算出每個部門的平均薪資，然后讓emp和該表進行笛卡爾積。

拓展：我要的是這些人辦公地點在哪里

4.4.2、查找每個部門工資最高的人的姓名、工資、部門、最高工資
思路：先分組聚合統計出每個部門最高薪資，然后將該表和emp表進行笛卡爾積，篩選出部門相等和薪資相等的信息，最后需要什么數據拿什么數據即可。

4.4.3、顯示每個部門的信息（部門名，編號，地址）和人員數量
多表查詢，首先根據部門號聚合統計出每個部門的人數，然后將臨時表和dept表做笛卡爾積，篩選條件為部門編號相等，然后將需要的信息拿出來即可。

另一種做法，不推薦！

解決多表問題的本質：想辦法將多表轉換成單表，MySQL中所有select的問題都可以轉換成單表問題。

4.5、合并查詢

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

4.5.1、union該操作符用于取得兩個結果集的并集。當使用該操作符時，會自動去掉結果集中的重復行。
案例：將工資大于2500或職位是MANAGER的人找出來。

4.5.2、union all該操作符用于取得兩個結果集的并集。當使用該操作符時，不會去掉結果集中的重復行。
案例：將工資大于2500或職位是MANAGER的人找出來。

注意：使用union/union all必須保證兩個查詢的列數是相同的。

---

5、表的內連和外連

5.1、內連接

表的連接分為內連和外連。
內連接實際上就是利用where子句對兩種表形成的笛卡兒積進行篩選，我們前面學習的查詢都是內連接，也是在開發過程中使用的最多的連接查詢。

案例：顯示SMITH的名字和部門名稱。
采用之前的寫法：

采用上面的語法：

5.2、外連接

外連接分為左外連接和右外連接。

5.2.1、左外連接

如果聯合查詢，左側的表完全顯示我們就說是左外連接。

首先創建兩張表方便我們進行測試：

-- 建兩張表
create table stu (id int, name varchar(30)); -- 學生表
insert into stu values(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');
create table exam (id int, grade int); -- 成績表
insert into exam values(1, 56),(2,76),(11, 8);

查詢所有學生的成績，如果這個學生沒有成績，也要將學生的個人信息顯示出來。

如圖，左側全部顯示，右側如果沒有匹配的就會補NULL。

5.2.2、右外連接

如果聯合查詢，右側的表完全顯示我們就說是右外連接。

對stu表和exam表聯合查詢，把所有的成績都顯示出來，即使這個成績沒有學生與它對應，也要顯示出來

列出部門名稱和這些部門的員工信息，同時列出沒有員工的部門。

或者采用右外連接：