MySQL三表連接

select details.*, department.name, salary.a, salary.b, salary.c from

成都創(chuàng)新互聯(lián)公司長期為數(shù)千家客戶提供的網(wǎng)站建設(shè)服務(wù)，團隊從業(yè)經(jīng)驗10年，關(guān)注不同地域、不同群體，并針對不同對象提供差異化的產(chǎn)品和服務(wù)；打造開放共贏平臺，與合作伙伴共同營造健康的互聯(lián)網(wǎng)生態(tài)環(huán)境。為營口企業(yè)提供專業(yè)的成都網(wǎng)站設(shè)計、網(wǎng)站制作，營口網(wǎng)站改版等技術(shù)服務(wù)。擁有十多年豐富建站經(jīng)驗和眾多成功案例,為您定制開發(fā)。

department join salary on department.id = salary.departmentId join details on salary.id = details.salaryId

MySQL連接查詢

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：查詢員工的工資以及對應(yīng)的工資級別

#案例2：查詢名字中第三個字符為a，第五個字符為e的員工的工資以及對應(yīng)的工資級別

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三表連接查詢

有兩種方式： 關(guān)鍵字where 或嵌入在inner 或left 中：

下面定義3個表A，B，C，字段分別為A：a，b；B:b，c；C:c，d

正常where 使用語句如下：

select A.a,B.b,C.c from A

inner join B on A.b=B.b

inner join C on C.c=B.c

where A.a=10 or B.b=10 or C.c=10

下面的SQL 嵌入到inner 中的使用方式：

select A.a,B.b,C.c from A

inner join B on A.b=B.b and B.b=10

inner join C on C.c=B.c and C.c=10

MySql三張表怎么進行全連接？

select * from A full join B on a.col1=b.col1

left join C on a.col2=c.col2;

是不是你要的效果、C又是什么join呢，是在搞不清你可以把子查詢括號括起來再join你的C表

select * from (

select * from A full join B on a.col1=b.col1

) left join C on a.col2=c.col2;

MySQL中3表join流程分析

常聽說MySQL中3表 join 的執(zhí)行流程并不是前兩張表 join 得出結(jié)果，再與第三張表進行 join；而是3表嵌套的循環(huán)連接。那這個3表嵌套的循環(huán)連接具體又是個什么流程呢？與前兩張表 join 得出結(jié)果再與第三張表進行 join 的執(zhí)行效率相比如何呢？下面通過一個例子來分析分析。

set optimizer_switch='block_nested_loop=off';

關(guān)聯(lián)字段無索引的情況下強制使用索引嵌套循環(huán)連接算法，目的是更好的觀察掃描行數(shù)。

表結(jié)構(gòu)和數(shù)據(jù)如下：

示例SQL：

通過 slow log 得知一共掃描 24100 行：

執(zhí)行計劃顯示用的索引嵌套循環(huán)連接算法：

掃描行數(shù)構(gòu)成：

總行數(shù)=100+4000+20000=24100。

從這個結(jié)果來看，join 過程像是先 t1 和 t3 join 得出 20 行中間結(jié)果，再與 t2 進行 join 得出結(jié)果。這結(jié)論與我們通常認為的 3表 join 實際上是3表嵌套的循環(huán)連接不一樣，接著往下看。

查看執(zhí)行計劃成本：

mysql explain format=json select * from t1 join t2 on t1.b=t2.b join t3 on t1.b=t3.b where t1.a21\G

其他信息：

IO成本= 1*1.0 =1

CPU成本= 100*0.2 =20

t1總成本=21

IO成本= 1*1.0 =1

CPU成本= 200*0.2 =40

t3表總成本= 驅(qū)動表扇出*(IO成本+CPU成本) = 20*(1+40) =820

階段性總成本= 21+820 =841

此處 eval_cost=80，實則為 驅(qū)動表扇出*被驅(qū)動每次掃描行數(shù)*filtered*成本常數(shù) ，即 20*200*10%*0.2 。

簡化公式為： eval_cost=rows_produced_per_json*成本常數(shù)

IO成本= 4*1.0 =4

CPU成本= 1000*0.2 =200

t2表總成本= 前2表join的扇出*(IO成本+CPU成本) = 400*(4+200) =81600

階段性總成本= 841+81600 =82441

此處 eval_cost=8000，即 rows_produced_per_json*成本常數(shù) ，即 40000*0.2

根據(jù)執(zhí)行計劃成本分析：

這樣看，3表 join 流程是：

注意，由于造的數(shù)據(jù)比較特殊，所以第 3 步得出的中間結(jié)果集實際上只有 1行，所以最終 t2 表的查找次數(shù)是 20*1=20 ，所以掃描總行數(shù)是 20*1000 。所以單看 slow log 中顯示的 24100 行，會誤認為是先得出 t1 和 t3 join 的結(jié)果，再去和 t2 進行 join。

當我調(diào)整 t3 的數(shù)據(jù)，刪除20行，再插入20行，使?jié)M足 b21 的數(shù)據(jù)翻倍，這樣“第 3 步得出的中間結(jié)果集”變成 2 行：

再來看slow log 中掃描的總行數(shù)為44100，t1、t3的掃描行數(shù)不變，t2 的掃描行數(shù)變?yōu)? 20*2*1000=40000 ：

為什么執(zhí)行計劃中分析得到的是 t2 表查找 400 次呢？

因為執(zhí)行計劃對t1 join t3 的扇出是個估算值，不準確。而 slow log 是真實執(zhí)行后統(tǒng)計的，是個準確值。

為什么執(zhí)行計劃中，t2表的執(zhí)行次數(shù)是用“t1 join t3 的扇出”表示的？這不是說明 t1 先和 t3 join，結(jié)果再和 t2 join？

其實拆解來看，“3表嵌套循環(huán)” 和 “前2表 join 的結(jié)果和第3張表 join” 兩種算法，成本是一樣的，而且如果要按3表嵌套循環(huán)的方式展示每張表的成本將非常復雜，可讀性不強。所以執(zhí)行計劃中這么表示沒有問題。

總的來說，對于3表join或者多表join 來說，“3表嵌套循環(huán)” 和 “先2表 join，結(jié)果和第3張表join” 兩種算法，成本是一樣的。要注意的一點是3表嵌套循環(huán)成本并非如下圖寫的：n m x，而是 n (m+a x)，其中 a 為 t2 滿足單個等值條件的平均值。

當被驅(qū)動表的關(guān)聯(lián)字段不是唯一索引，或者沒有索引，每次掃描行數(shù)會大于1時，其扇出誤差會非常大。比如在上面的示例中：

t3 實際的扇出只有 20，但優(yōu)化器估算值是 總掃描行數(shù)的 10%，由于t3表的關(guān)聯(lián)字段沒有索引，所以每次都要全表掃描200行，總的掃描行數(shù)= 20*200 =4000，扇出= 4000*10% =400，比實際的20大了20倍。尤其對于后續(xù)表的 join 來說，成本估算會產(chǎn)生更嚴重的偏差。

如果是 left join，每個被驅(qū)動表的 filtered 都會被優(yōu)化器認定為 100%，誤差更大！

通常建議join不超過2表，就是因為優(yōu)化器估算成本誤差大導致選擇不好的執(zhí)行計劃，如果要用，一定要記?。宏P(guān)聯(lián)字段必須要有索引，最好有唯一性或者基數(shù)大。

SQL/spark SQL三表左外連接

近期有一個場景需要用到三表左外連接，以mysql為例，將實現(xiàn)方法記錄如下。

假設(shè)有如下三張表：

實現(xiàn)三表左外連接：

當前名稱：mysql怎么三表鏈接 sql如何三個表連接
網(wǎng)站鏈接：http://jinyejixie.com/article6/hpccog.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供用戶體驗、關(guān)鍵詞優(yōu)化、網(wǎng)站排名、Google、品牌網(wǎng)站設(shè)計、定制網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)