學習地址:
創(chuàng)新互聯(lián)憑借在網(wǎng)站建設、網(wǎng)站推廣領(lǐng)域領(lǐng)先的技術(shù)能力和多年的行業(yè)經(jīng)驗，為客戶提供超值的營銷型網(wǎng)站建設服務，我們始終認為：好的營銷型網(wǎng)站就是好的業(yè)務員。我們已成功為企業(yè)單位、個人等客戶提供了成都做網(wǎng)站、網(wǎng)站設計、外貿(mào)營銷網(wǎng)站建設服務，以良好的商業(yè)信譽，完善的服務及深厚的技術(shù)力量處于同行領(lǐng)先地位。
撩課-JavaWeb系列1之基礎語法-前端基礎
撩課-JavaWeb系列2之XML
撩課-JavaWeb系列3之MySQL
撩課-JavaWeb系列4之JDBC
撩課-JavaWeb系列5之web服務器-idea
1.查詢所有員工的姓名，工資
2.查詢所有員工的姓名，工資和所有部門
3.查詢所有員工的姓名，工資和所在部門及工資等級
求7369員工編號、姓名、經(jīng)理編號和經(jīng)理姓名
本文主要介紹 SQL 標準中定義的各種連接的意義和區(qū)別，例如，交叉連接（ CROSS JOIN ）、內(nèi)連接（ INNER JOIN ）、外連接（ OUTER JOIN ）、自然連接（ NATURAL JOIN ）等，并結(jié)合例子講解這些連接在 MySQL 中的語法和表現(xiàn)。
從網(wǎng)上的資料看， JOIN 更多翻譯為連接，本文中凡是出現(xiàn)中文“連接”的地方都是指 JOIN 。
本文中用到的所有例子涉及兩張表—— customers 用戶表和 orders 訂單表，其中訂單表中的 cust_id 字段表示用戶的唯一 ID，也就是用戶表的主鍵 cust_id 。兩張表的數(shù)據(jù)如下：
注：兩張表都經(jīng)過了簡化，實際業(yè)務中這兩張表肯定還包括其他字段。
英文維基百科 JOIN 詞條 對連接的定義如下：
翻譯過來就是，“連接可以根據(jù)一張（自連接）或多張表中的共同值將這些表的列數(shù)據(jù)合并為一個新的結(jié)果集，標準 SQL 定義了五種連接：內(nèi)連接、左外連接、右外連接、全外連接和交叉連接?！?/p>
也就是說，連接是 SQL 標準中定義的一種組合多張表的方式，當然一張表自身也可以和自身組合，稱為自連接。連接后得到的結(jié)果集的每一列其實都來自用于連接的多張表，不同的連接類型只是區(qū)分了這些列具體從哪張表里來，列里填充的是什么數(shù)據(jù)。
其實英文維基百科的 JOIN 詞條已經(jīng)把各種連接的類型解釋地非常清楚了，非常值得去看一下。
我們來看一下 SQL 標準中定義的各種連接類型，理解各種連接最好的方法就是把需要連接的表想象成集合，并畫出可以反映集合的交與并的情況的圖——韋恩圖，例如下圖就畫出了 SQL 中定義的幾種主要連接。
請先仔細查看一下圖中的內(nèi)容，你可以從中歸納出幾種連接類型呢？
雖然圖中畫了 7 種集合的交并情況，但是總結(jié)起來，主要是兩種連接類型在起作用——內(nèi)連接（ INNER JOIN ）和外連接（ OUTER JOIN ），其中外連接又分為了左外連接（ LEFT OUTER JOIN ）、右外連接（ RIGHT OUTER JOIN ）和全外連接（ FULL OUTER JOIN ）。
下面先簡單介紹一下 SQL 標準中各種連接的定義，然后在「MySQL 中的連接」一節(jié)再用例子來演示 MySQL 中支持的各種連接。
連接既然是用來合并多張表的，那么要定義一個連接就必須指定需要連接的表，并指定可選的連接條件。例如，一個典型的 SQL 連接語句如下：
我們用表 A 和表 B 指代需要連接的兩張表，經(jīng)過 內(nèi)連接 后得到的結(jié)果集 僅 包含所有滿足 連接條件 的數(shù)據(jù)；而經(jīng)過 外連接 后得到的數(shù)據(jù)集 不僅 包含滿足 連接條件 的數(shù)據(jù)，還包含其他數(shù)據(jù)，具體的差別是：
在上面「SQL 標準定義的主要連接」一圖中并沒有列出交叉連接，交叉連接會對連接的兩張表做笛卡爾積，也就是連接后的數(shù)據(jù)集中的行是由第一張表中的每一行與第二張表中的每一行配對而成的，而不管它們 邏輯上 是否可以搭配在一起。假設交叉連接的兩張表分別有 m 和 n 行數(shù)據(jù)，那么交叉連接后的數(shù)據(jù)集就包含 m 乘以 n 行數(shù)據(jù)。
連接根據(jù)連接的條件不同，又可以區(qū)分為等值連接和非等值連接，「SQL 標準定義的主要連接」圖中畫出的連接的連接條件都是比較兩個字段是否相等，它們都是等值連接。
自然連接是等值連接的一種特殊形式，自然連接會自動選取需要連接的兩張表中字段名相同的 所有 列做相等比較，而不需要再指定連接條件了。
注：以下內(nèi)容全部基于 MySQL 5.7 版本，所有例子只保證在 MySQL 5.7 上是可以正確執(zhí)行的。
MySQL 中支持的連接類型和關(guān)鍵字如下：
上面的表示方法摘自 MySQL 5.7 版本 官方文檔 ，其中 | 表示兩者皆可出現(xiàn)， [] 表示的是可選的， {} 表示的是必選的，例如 NATURAL LEFT JOIN 和 NATURAL JOIN 都是合法的。
可以看到，除了全外連接（ FULL OUTER JOIN ）以外， MySQL 基本支持了 SQL 標準中定義的各種連接。在 MySQL 中全外連接可以通過 UNION 合并的方式做到，當然前提是你知道自己為什么需要這么做，具體參見： Full Out Join in MySQL 。
MySQL 語法中還支持一個并不在 SQL 標準中的 STRAIGHT_JOIN ，它在 表現(xiàn)上 和內(nèi)連接或者交叉連接并無區(qū)別，只是一種給 MySQL 優(yōu)化器的一個提示， STRAIGHT_JOIN 提示 MySQL 按照語句中表的順序加載表，只有在你明確清楚 MySQL 服務器對你的 JOIN 語句做了負優(yōu)化的時候才可能用到它。
還有一點需要說明的是，根據(jù) 官方文檔 ，在 MySQL 中， JOIN 、 CROSS JOIN 和 INNER JOIN 實現(xiàn)的功能是一致的，它們在語法上是等價的。從語義上來說， CROSS JOIN 特指無條件的連接（沒有指定 ON 條件的 JOIN 或者沒有指定 WHERE 連接條件的多表 SELECT ）， INNER JOIN 特指有條件的連接（指定了 ON 條件的 JOIN 或者指定了 WHERE 連接條件的多表 SELECT ）。當然，如果你非要寫 ... CROSS JOIN ... ON ... 這樣的語法，也是可以執(zhí)行的，雖然寫著交叉連接，實際上執(zhí)行的是內(nèi)連接。
下面我們就用例子來看一看 MySQL 中支持的幾種連接的例子。
注：下面的例子都沒有指定 ORDER BY 子句，返回結(jié)果的順序可能會因為數(shù)據(jù)插入順序的不同而略有不同。
MySQL 的交叉連接或內(nèi)連接有兩種寫法，一種是使用 JOIN 并用 ON 或者 USING 子句指定連接條件的寫法，一種是普通的 SELECT 多表，并且用 WHERE 子句指定連接的鍵的寫法。
下面的例子是一個交叉連接：
上面的寫法等價于：
當然，第二種寫法中如果將 CROSS JOIN 替換成 JOIN 或者 INNER JOIN 也是可以正確執(zhí)行的。上面兩條語句的執(zhí)行結(jié)果如下：
可以看到共返回了 30 行結(jié)果，是兩張表的笛卡爾積。
一個內(nèi)連接的例子如下：
上面的寫法等價于：
在連接條件比較的字段相同的情況下，還可以改用 USING 關(guān)鍵字，上面的寫法等價于：
上面三條語句的返回結(jié)果如下：
可以看到只返回了符合連接條件 customers.cust_id = orders.cust_id 的 6 行結(jié)果，結(jié)果的含義是所有有訂單的用戶和他們的訂單。
左外連接和右外連接的例子如下，其中的 OUTER 關(guān)鍵字可以省略：
其中右外連接的返回與內(nèi)連接的返回是一致的（思考一下為什么），左外連接的返回結(jié)果如下：
可以看到一共返回了 8 行數(shù)據(jù)，其中最后兩行數(shù)據(jù)對應的 order_id 的值為 NULL ，結(jié)果的含義是所有用戶的訂單，不管這些用戶是否已經(jīng)有訂單存在了。
根據(jù)前面介紹的自然連接的定義，自然連接會自動用參與連接的兩張表中 字段名相同 的列做等值比較，由于例子中的 customers 和 orders 表只有一列名稱相同，我們可以用自然連接的語法寫一個與上面的內(nèi)連接的例子表現(xiàn)行為一樣的語句如下：
可以看到，使用自然連接就不能再用 ON 子句指定連接條件了，因為這完全是多余的。
當然，自然連接同樣支持左外連接和右外連接。
下面用一個 customers 表自連接的例子再來說明一下自然連接，語句如下：
因為是自連接，因此必須使用 AS 指定別名，否則 MySQL 無法區(qū)分“兩個” customers 表，運行的結(jié)果如下：
可以看到結(jié)果集和 customers 表完全一致，大家可以思考一下為什么結(jié)果是這樣的。
文章之前也提到了，MySQL 還支持一種 SQL 標準中沒有定義的“方言”， STRAIGHT_JOIN ， STRAIGHT_JOIN 支持帶 ON 子句的內(nèi)連接和不帶 ON 子句的交叉連接，我們來看一個 STRAIGHT_JOIN 版本的內(nèi)連接的例子：
返回結(jié)果與前面內(nèi)連接的例子是一致的，如下：
STRAIGHT_JOIN 的表現(xiàn)和 JOIN 是完全一致的，它只是一種給 MySQL 優(yōu)化器的提示，使得 MySQL 始終按照語句中表的順序讀取表（上面的例子中，MySQL 在執(zhí)行時一定會先讀取 customers 表，再讀取 orders 表），而不會做改變讀取表的順序的優(yōu)化。關(guān)于 MySQL 優(yōu)化器的話題這里不做展開，需要說明的是除非你非常清楚你在做什么，否則不推薦直接使用 STRAIGHT_JOIN 。
你能理解上面的語句是在檢索什么數(shù)據(jù)嗎？
本文主要介紹了 SQL 標準里定義的各種連接的概念，以及 MySQL 中的實現(xiàn)，并通過各種例子來介紹了這些連接的區(qū)別。這些連接不一定都能在實際開發(fā)中用到，但是做到心中有知識也還是很有必要的。
那么，現(xiàn)在再回憶一下，什么是內(nèi)連接、外連接、自連接、等值連接和自然連接？他們的區(qū)別是什么？
最后，給大家留一個思考題，為什么 MySQL 中沒有左外連接或者右外連接版本的 STRAIGHT_JOIN ？
Hello，寫的語言格式有些丑
練習題目：
3、多表連接（等值連接）
①案例1 ：查詢員工名、部門名
②為表起別名
# ③添加篩選條件
# 案例：查詢 工資5000 的工種名和員工名、工資
④添加分組和篩選
#01 案例：查詢每個部門的員工個數(shù)和部門名
⑤排序
#01 案例：查詢每個部門的員工個數(shù)和部門名
⑥ 三表連接
# 案例：查詢員工名、部門名和所在城市
4、多表連接（等值連接）練習
傳統(tǒng)模式的多表連接
1. 顯示所有員工的姓名，部門號和部門名稱。
2. 查詢90 號部門員工的job_id 和90 號部門的location_id
3. 選擇所有有獎金的員工的last_name? , department_name , location_id , city?
----------- 三表連查
4. 選擇city 在Toronto 工作的員工的
last_name? , job_id , department_id , department_name?????----------- 三表連查
5. 查詢每個工種、每個部門的部門名、工種名和最低工資? ----------- 三表連查
6. 查詢每個國家下的部門個數(shù)大于2 的國家編號
5、非等值查詢
2.非等值連接
#案例1：查詢員工的工資以及對應的工資級別
#案例2：查詢名字中第三個字符為a，第五個字符為e的員工的工資以及對應的工資級別
6、內(nèi)連接
#案例1 ：查詢員工名、部門名
案例2：查詢有獎金的員工名、部門名
案例3：查詢城市名、員工名和部門名
9、練習
一、查詢編號3的女神的男朋友信息，如果有則列出詳細，如果沒有，用null填充
#二、查詢哪個城市沒有部門
三、查詢部門名為SAL或IT的員工信息
#四、選擇指定員工的姓名，員工號，以及他的管理者的姓名和員工號，結(jié)果類似于下面的格式
/*
employees?????? Emp#?????? manager? ? Mgr#
kochhar??????????? 101? ???? king??????? ?
100
*/
10、單行子查詢
案例1：誰的工資比Abel高
①查詢Abel的工資
②查詢員工的信息滿足工資①的結(jié)果
案例2：題目：返回job_id與141號員工相同，salary比143號員工多的員工 的姓名，job_id 和工資
①查詢141的job_id
②查詢143的salary
③查詢? 姓名，job_id 和工資，滿足job_id=①并且salary②
案例3：返回公司工資最少的員工的last_name,job_id和salary
①查詢最低工資
②查詢員工的last_name,job_id和salary滿足 salary=①
案例4：查詢最低工資大于50號部門最低工資的部門id和其最低工資
①查詢50號部門的最低工資
②查詢每個部門的最低工資
③篩選最低工資①
11、多行子查詢
二、多行子查詢
案例1：返回location_id是1400或1700的部門中的所有員工姓名
①查詢location_id是1400或1700的部門編號
②查詢department_id滿足①結(jié)果的員工姓名
案例2：返回其它部門中比job_id為‘IT_PROG’部門任意工資低的員工的員
工號、姓名、job_id 以及salary
①查詢job_id為‘IT_PROG’部門工資
②返回其它部門中，工資any ①的結(jié)果
題目：返回其它部門中比job_id為‘IT_PROG’部門所有工資都低的員工
#的員工號、姓名、job_id 以及salary
12、子查詢練習題
#1. 查詢和Zlotkey 相同部門的員工姓名和工資
#2. 查詢工資比公司平均工資高的員工的員工號，姓名和工資。
#①查詢公司平均工資
② 查詢工資①的員工的員工號，姓名和工資。
#3. 查詢各部門中工資比本部門平均工資高的員工的員工號, 姓名和工資
①查詢各部門的平均工資
②查詢員工的員工號, 姓名和工資，滿足本部門并且工資①
4. 查詢姓名中包含字母u 的員工在相同部門的員工的員工號和姓名
①查詢姓名中包含字母u的員工的部門
② 部門=①的員工的員工號和姓名
5. 查詢在部門的location_id 為1700 的部門工作的員工的員工號
①查詢loaction_id =1700的部門編號
② 查詢員工號，滿足部門號=①
#6. 查詢管理者是King 的員工姓名和工資
①查詢員工名是king的編號
#② 查詢員工姓名和工資，領(lǐng)導的編號=①
#7. 查詢工資最高的員工的姓名，要求first_name 和last_name 顯示為一列，列名為 姓. 名
①查詢最高工資
②查詢姓名，工資=①
14、子查詢鞏固練習
# 1 、查詢工資最低的員工信息
#①查詢公司的最低工資
②查詢員工信息，滿足 salary=①
2. 查詢平均工資最低的部門信息
①查詢每個部門的平均工資
②查詢①結(jié)果中avg(salary) 字段中的最低值
# ③查詢部門編號，滿足平均工資= ②結(jié)果
④查詢部門信息，滿足? department_id= ③
3*. 查詢平均工資最低的部門信息和該部門的平均工資
4. 查詢平均工資最高的 job 信息
①查詢每個job的平均工資
②查詢①結(jié)果中的 avg(salary)的最高值
③查詢每個工種的平均工資，滿足 平均工資=②
④工種表和③連接? ， 查詢平均工資最高的 job 信息 ?
# 5. 查詢平均工資高于公司平均工資的部門有哪些?
#①查詢公司的平均工資
②查詢每個部門的平均工資，并且平均工資①
6. 查詢平均工資最高的部門的? manager 的詳細信息:
①查詢平均工資最高的部門編號
②查詢部門編號=①的manager的詳細信息
MySQL中常見的連接查詢有：等值連接，使用=連接兩列數(shù)據(jù)，所有能夠匹配的結(jié)果都會被顯示出來；內(nèi)連接，關(guān)鍵字INNER JOIN ON，連接效果等同于等值連接；左連接，關(guān)鍵字LEFT JOIN ON，關(guān)鍵字左側(cè)的表的所有數(shù)據(jù)均顯示，關(guān)鍵字右側(cè)的表匹配內(nèi)容顯示，無對應內(nèi)容使用NULL填充;右連接，關(guān)鍵字RIGHT JOIN ON，關(guān)鍵字右側(cè)的表的所有數(shù)據(jù)均顯示，關(guān)鍵字左側(cè)的表匹配內(nèi)容顯示，無對應內(nèi)容使用NULL填充；一般情況下，左連接和右連接可以實現(xiàn)相同的連接效果。如果對這部分內(nèi)容感興趣，可以從黑馬程序員獲取測試相關(guān)課程了解一下。
當我們需要用多張表聯(lián)合起來查詢數(shù)據(jù)時，稱為連接查詢。
連接查詢可以分為內(nèi)連接、外連接和全連接。其中內(nèi)連接分為等值連接、非等值連接和自連接。外連接分為左外連接（左連接）和右外連接（右連接）。
笛卡爾積：當兩張表進行連接查詢，沒有任何條件限制的時候，最終查詢結(jié)果條數(shù)是兩張表條數(shù)的乘積，這種現(xiàn)象被稱為笛卡爾積現(xiàn)象。
通過添加條件限制，可以篩選出我們需要的數(shù)據(jù)。
一、內(nèi)連接
在內(nèi)連接中，兩張表是平等的。
dept（部門）表：
emp（員工）表：
salgrade（薪資）表：
1.等值連接
等值連接，即條件是一個等值關(guān)系。
eg.查詢每個員工所在部門名稱，顯示員工名和部門名。
員工表和部門表連接，通過emp.deptno=dept.deptno
//SQL92語法（不推薦）：
mysql select ename,dname
- from dept,emp
- where dept.deptno=emp.deptno;
登錄后復制
//SQL99語法（推薦）：
mysql select ename,dname
- from emp
- (inner) join dept
- on emp.deptno=dept.deptno;
join表示連接兩表，on表示條件,inner表示內(nèi)連接，可省略
登錄后復制
為什么不推薦使用92：
在我們查找數(shù)據(jù)時，連接條件后面可能還要再加篩選條件，如(where dept.deptno=emp.deptno and …(條件)?。
在92語法中，連接條件和篩選條件糅雜在where中，使得結(jié)構(gòu)不清晰。
而99語法中，通過on表示連接條件，后面可以繼續(xù)加where語句進行篩選：
select…
on emp.deptno=dept.deptno
where …
2.非等值連接
非等值連接，即條件不是一個等值關(guān)系。
eg.找出每個員工的薪資等級，要求顯示員工名、薪資、薪資等級。
mysql select ename,sal,grade
- from emp
- (inner) join salgrade
- on emp.sal between losal and hisal;
登錄后復制
3.自連接
將一張表看成兩張表進行查詢。
eg.查詢員工的上級領(lǐng)導，要求顯示員工名和對應的領(lǐng)導名。
mysql select a.ename empname,b.ename leadername
- from emp a join emp b
- on a.mgr=b.empno;
登錄后復制
只有13條記錄，沒有顯示老板KING的領(lǐng)導名，因為為NULL。
二、外連接
在外連接中，兩張表有主次關(guān)系，一主一次。
1.右(外)連接
eg.
select e.ename,d.dname
from emp e
right (outer) join dept d
on e.deptno=d.deptno;
//outer可以省略
登錄后復制
解釋：表示將join關(guān)鍵字右邊的這張表看成主表，主要是為了將這張表的數(shù)據(jù)全部查詢出來，順便關(guān)聯(lián)查詢左邊的表。
2.左(外)連接
將join左邊的表看成主表。
任何一個右連接都有左連接的寫法；
任何一個左連接都有右連接的寫法。
eg.查詢每個員工的上級領(lǐng)導，要求顯示所有員工的名字和領(lǐng)導名（將老板KING的領(lǐng)導名也顯示 出來）。
mysql select a.ename empname,b.ename leadername
- from emp a left join emp b
- on a.mgr=b.empno;
登錄后復制
補充：三張表、四張表如何進行連接？
語法：
select
...
from
a
join
b
on
a和b的連接條件
join
c
on
a和c的連接條件
right join
d
on
a和d的連接條件
登錄后復制
?
一條SQL中內(nèi)連接和外連接可以混合，都可以出現(xiàn)。
eg.找出每個員工的部門名稱以及工資等級，要求顯示員工名、部門名、薪資、薪資等級。
mysql select ename,dname,sal,grade
- from emp
- join dept
- on emp.deptno=dept.deptno
- join salgrade
- on emp.sal between losal and hisal;
登錄后復制
三、全連接
兩張表有主次關(guān)系，但兩張表都是主表。
全連接實際用的比較少，這里就不展開描述了。
重慶分公司
重慶分公司
