MySQL 連接查詢的原理和應(yīng)用
概述
MySQL最強大的功能之一就是能在數(shù)據(jù)檢索的執(zhí)行中連接（join）表。大部分的單表數(shù)據(jù)查詢并不能滿足我們的需求，這時候我們就需要連接一個或者多個表，并通過一些條件過濾篩選出我們需要的數(shù)據(jù)。
了解MySQL連接查詢之前我們先來理解下笛卡爾積的原理。
數(shù)據(jù)準(zhǔn)備
依舊使用上節(jié)的表數(shù)據(jù)（包含classes 班級表和students 學(xué)生表）：
mysql> select * from classes; +---------+-----------+ | classid | classname | +---------+-----------+ | 1 | 初三一班 | | 2 | 初三二班 | | 3 | 初三三班 | | 4 | 初三四班 | +---------+-----------+ 4 rows in set mysql> select * from students; +-----------+-------------+-------+---------+ | studentid | studentname | score | classid | +-----------+-------------+-------+---------+ | 1 | brand | 97.5 | 1 | | 2 | helen | 96.5 | 1 | | 3 | lyn | 96 | 1 | | 4 | sol | 97 | 1 | | 7 | b1 | 81 | 2 | | 8 | b2 | 82 | 2 | | 13 | c1 | 71 | 3 | | 14 | c2 | 72.5 | 3 | | 19 | lala | 51 | 0 | +-----------+-------------+-------+---------+ 9 rows in set
笛卡爾積
笛卡爾積：也就是笛卡爾乘積，假設(shè)兩個集合A和B，笛卡爾積表示A集合中的元素和B集合中的元素任意相互關(guān)聯(lián)產(chǎn)生的所有可能的結(jié)果。
比如A中有m個元素，B中有n個元素，A、B笛卡爾積產(chǎn)生的結(jié)果有m*n個結(jié)果，相當(dāng)于循環(huán)遍歷兩個集合中的元素，任意組合。
笛卡爾積在SQL中的實現(xiàn)方式既是交叉連接(Cross Join)。所有連接方式都會先生成臨時笛卡爾積表，笛卡爾積是關(guān)系代數(shù)里的一個概念，表示兩個表中的每一行數(shù)據(jù)任意組合。
所以上面的表就是 4（班級表）* 9（學(xué)生表） = 36條數(shù)據(jù)；
笛卡爾積語法格式：
select cname1,cname2,... from tname1,tname2,...; or select cname from tname1 join tname2 [join tname...];
圖例表示：
上述兩個表實際執(zhí)行結(jié)果如下：
mysql> select * from classes a,students b order by a.classid,b.studentid; +---------+-----------+-----------+-------------+-------+---------+ | classid | classname | studentid | studentname | score | classid | +---------+-----------+-----------+-------------+-------+---------+ | 1 | 初三一班 | 1 | brand | 97.5 | 1 | | 1 | 初三一班 | 2 | helen | 96.5 | 1 | | 1 | 初三一班 | 3 | lyn | 96 | 1 | | 1 | 初三一班 | 4 | sol | 97 | 1 | | 1 | 初三一班 | 7 | b1 | 81 | 2 | | 1 | 初三一班 | 8 | b2 | 82 | 2 | | 1 | 初三一班 | 13 | c1 | 71 | 3 | | 1 | 初三一班 | 14 | c2 | 72.5 | 3 | | 1 | 初三一班 | 19 | lala | 51 | 0 | | 2 | 初三二班 | 1 | brand | 97.5 | 1 | | 2 | 初三二班 | 2 | helen | 96.5 | 1 | | 2 | 初三二班 | 3 | lyn | 96 | 1 | | 2 | 初三二班 | 4 | sol | 97 | 1 | | 2 | 初三二班 | 7 | b1 | 81 | 2 | | 2 | 初三二班 | 8 | b2 | 82 | 2 | | 2 | 初三二班 | 13 | c1 | 71 | 3 | | 2 | 初三二班 | 14 | c2 | 72.5 | 3 | | 2 | 初三二班 | 19 | lala | 51 | 0 | | 3 | 初三三班 | 1 | brand | 97.5 | 1 | | 3 | 初三三班 | 2 | helen | 96.5 | 1 | | 3 | 初三三班 | 3 | lyn | 96 | 1 | | 3 | 初三三班 | 4 | sol | 97 | 1 | | 3 | 初三三班 | 7 | b1 | 81 | 2 | | 3 | 初三三班 | 8 | b2 | 82 | 2 | | 3 | 初三三班 | 13 | c1 | 71 | 3 | | 3 | 初三三班 | 14 | c2 | 72.5 | 3 | | 3 | 初三三班 | 19 | lala | 51 | 0 | | 4 | 初三四班 | 1 | brand | 97.5 | 1 | | 4 | 初三四班 | 2 | helen | 96.5 | 1 | | 4 | 初三四班 | 3 | lyn | 96 | 1 | | 4 | 初三四班 | 4 | sol | 97 | 1 | | 4 | 初三四班 | 7 | b1 | 81 | 2 | | 4 | 初三四班 | 8 | b2 | 82 | 2 | | 4 | 初三四班 | 13 | c1 | 71 | 3 | | 4 | 初三四班 | 14 | c2 | 72.5 | 3 | | 4 | 初三四班 | 19 | lala | 51 | 0 | +---------+-----------+-----------+-------------+-------+---------+ 36 rows in set
這樣的數(shù)據(jù)肯定不是我們想要的，在實際應(yīng)用中，表連接時要加上限制條件，才能夠篩選出我們真正需要的數(shù)據(jù)。
我們主要的連接查詢有這幾種：內(nèi)連接、左(外)連接、右(外)連接，下面我們一 一來看。
內(nèi)連接查詢 inner join
語法格式：
select cname from tname1 inner join tname2 on join condition; 或者 select cname from tname1 join tname2 on join condition; 或者 select cname from tname1,tname2 [where join condition];
說明：在笛卡爾積的基礎(chǔ)上加上了連接條件，組合兩個表，返回符合連接條件的記錄，也就是返回兩個表的交集（陰影）部分。如果沒有加上這個連接條件，就是上面笛卡爾積的結(jié)果。
mysql> select a.classname,b.studentname,b.score from classes a inner join students b on a.classid = b.classid; +-----------+-------------+-------+ | classname | studentname | score | +-----------+-------------+-------+ | 初三一班 | brand | 97.5 | | 初三一班 | helen | 96.5 | | 初三一班 | lyn | 96 | | 初三一班 | sol | 97 | | 初三二班 | b1 | 81 | | 初三二班 | b2 | 82 | | 初三三班 | c1 | 71 | | 初三三班 | c2 | 72.5 | +-----------+-------------+-------+ 8 rows in set
從上面的數(shù)據(jù)可以看出 ，初三四班 classid = 4，因為沒有關(guān)聯(lián)的學(xué)生，所以被過濾掉了；lala 同學(xué)的classid=0，沒法關(guān)聯(lián)到具體的班級，也被過濾掉了，只取兩表都有的數(shù)據(jù)交集
mysql> select a.classname,b.studentname,b.score from classes a,students b where a.classid = b.classid and a.classid=1; +-----------+-------------+-------+ | classname | studentname | score | +-----------+-------------+-------+ | 初三一班 | brand | 97.5 | | 初三一班 | helen | 96.5 | | 初三一班 | lyn | 96 | | 初三一班 | sol | 97 | +-----------+-------------+-------+ 4 rows in set
查找1班同學(xué)的成績信息，上面語法格式的第三種，這種方式簡潔高效，直接在連接查詢的結(jié)果后面進行Where條件篩選。
左連接查詢 left join
left join on / left outer join on，語法格式：
select cname from tname1 left join tname2 on join condition;
說明： left join 是left outer join的簡寫，全稱是左外連接，外連接中的一種。 左(外)連接，左表(classes)的記錄將會全部出來，而右表(students)只會顯示符合搜索條件的記錄。右表無法關(guān)聯(lián)的內(nèi)容均為null。
mysql> select a.classname,b.studentname,b.score from classes a left join students b on a.classid = b.classid; +-----------+-------------+-------+ | classname | studentname | score | +-----------+-------------+-------+ | 初三一班 | brand | 97.5 | | 初三一班 | helen | 96.5 | | 初三一班 | lyn | 96 | | 初三一班 | sol | 97 | | 初三二班 | b1 | 81 | | 初三二班 | b2 | 82 | | 初三三班 | c1 | 71 | | 初三三班 | c2 | 72.5 | | 初三四班 | NULL | NULL | +-----------+-------------+-------+ 9 rows in set
從上面結(jié)果中可以看出，初三四班無法找到對應(yīng)的學(xué)生，所以后面兩個字段使用null標(biāo)識。
右連接查詢 right join
right join on / right outer join on，語法格式：
select cname from tname1 right join tname2 on join condition;
說明：right join是right outer join的簡寫，全稱是右外連接，外連接中的一種。與左(外)連接相反，右(外)連接，左表(classes)只會顯示符合搜索條件的記錄，而右表(students)的記錄將會全部表示出來。左表記錄不足的地方均為NULL。
mysql> select a.classname,b.studentname,b.score from classes a right join students b on a.classid = b.classid; +-----------+-------------+-------+ | classname | studentname | score | +-----------+-------------+-------+ | 初三一班 | brand | 97.5 | | 初三一班 | helen | 96.5 | | 初三一班 | lyn | 96 | | 初三一班 | sol | 97 | | 初三二班 | b1 | 81 | | 初三二班 | b2 | 82 | | 初三三班 | c1 | 71 | | 初三三班 | c2 | 72.5 | | NULL | lala | 51 | +-----------+-------------+-------+ 9 rows in set
從上面結(jié)果中可以看出，lala同學(xué)無法找到班級，所以班級名稱字段為null。
連接查詢+聚合函數(shù)
使用連接查詢的時候，經(jīng)常會配合使用聚集函數(shù)來進行數(shù)據(jù)匯總。比如在上面的數(shù)據(jù)基礎(chǔ)上查詢出每個班級的人數(shù)和平均分?jǐn)?shù)、班級總分?jǐn)?shù)。
mysql> select a.classname as '班級名稱',count(b.studentid) as '總?cè)藬?shù)',sum(b.score) as '總分',avg(b.score) as '平均分' from classes a inner join students b on a.classid = b.classid group by a.classid,a.classname; +----------+--------+--------+-----------+ | 班級名稱 | 總?cè)藬?shù) | 總分 | 平均分 | +----------+--------+--------+-----------+ | 初三一班 | 4 | 387.00 | 96.750000 | | 初三二班 | 2 | 163.00 | 81.500000 | | 初三三班 | 2 | 143.50 | 71.750000 | +----------+--------+--------+-----------+ 3 rows in set
這邊連表查詢的同時對班級（classid，classname）做了分組，并輸出每個班級的人數(shù)、平均分、班級總分。
連接查詢附加過濾條件
使用連接查詢之后，大概率會對數(shù)據(jù)進行在過濾篩選，所以我們可以在連接查詢之后再加上where條件，比如我們根據(jù)上述的結(jié)果只取出一班的同學(xué)信息。
mysql> select a.classname,b.studentname,b.score from classes a inner join students b on a.classid = b.classid where a.classid=1; +-----------+-------------+-------+ | classname | studentname | score | +-----------+-------------+-------+ | 初三一班 | brand | 97.5 | | 初三一班 | helen | 96.5 | | 初三一班 | lyn | 96 | | 初三一班 | sol | 97 | +-----------+-------------+-------+ 4 rows in set
如上，只輸出一班的同學(xué)，同理，可以附件 limit 限制，order by排序等操作。
總結(jié)
1、連接查詢必然要帶上連接條件，否則會變成笛卡爾乘積數(shù)據(jù)，使用不正確的聯(lián)結(jié)條件，也將返回不正確的數(shù)據(jù)。
2、SQL規(guī)范推薦首選INNER JOIN語法。但是連接的幾種方式本身并沒有明顯的性能差距，性能的差距主要是由數(shù)據(jù)的結(jié)構(gòu)、連接的條件，索引的使用等多種條件綜合決定的。
我們應(yīng)該根據(jù)實際的業(yè)務(wù)場景來決定，比如上述數(shù)據(jù)場景：如果要求返回返回有學(xué)生的班級就使用 inner join；如果必須輸出所有班級則使用left join；如果必須輸出所有學(xué)生，則使用right join。
3、性能上的考慮，MySQL在運行時會根據(jù)關(guān)聯(lián)條件處理連接的表，這種處理可能是非常耗費資源的，連接的表越多，性能下降越厲害。所以要分析去除那些不必要的連接和不需要顯示的字段。
之前我的項目團隊在優(yōu)化舊的業(yè)務(wù)代碼時，發(fā)現(xiàn)隨著業(yè)務(wù)的變更，某些數(shù)據(jù)不需要顯示，對應(yīng)的某個連接也不需要了，去掉之后，性能較大提升。
以上就是MySQL 連接查詢的原理和應(yīng)用的詳細(xì)內(nèi)容，更多關(guān)于MySQL 連接查詢的資料請關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
索引是一個數(shù)據(jù)結(jié)構(gòu),索引是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)(有序),這篇文章主要給大家介紹了關(guān)于MySQLJSON索引用法的相關(guān)資料,需要的朋友可以參考下2024-01-01
在現(xiàn)代應(yīng)用中,數(shù)據(jù)庫是核心組件之一,無論是個人項目還是企業(yè)級應(yīng)用,數(shù)據(jù)的安全性和完整性都至關(guān)重要,為了防止數(shù)據(jù)丟失、損壞或意外刪除,定期備份數(shù)據(jù)庫是必不可少的,本文將詳細(xì)介紹 MySQL 數(shù)據(jù)庫的備份與恢復(fù)方法,需要的朋友可以參考下2024-10-10
mysql 8.0.16 winx64及Linux修改root用戶密碼 的方法
這篇文章主要介紹了mysql 8.0.16 winx64及Linux修改root用戶密碼 的方法，本文給大家介紹的非常詳細(xì)，具有一定的參考借鑒價值,需要的朋友可以參考下2019-07-07
MySQL高級學(xué)習(xí)筆記（三）：Mysql邏輯架構(gòu)介紹、mysql存儲引擎詳解
這篇文章主要介紹了Mysql邏輯架構(gòu)介紹、mysql存儲引擎，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2019-04-04
今天小編就為大家分享一篇關(guān)于B-樹的刪除過程介紹，小編覺得內(nèi)容挺不錯的，現(xiàn)在分享給大家，具有很好的參考價值，需要的朋友一起跟隨小編來看看吧2019-01-01
這篇文章主要給大家介紹了關(guān)于MySQL數(shù)據(jù)庫操作庫的相關(guān)資料,MySQL數(shù)據(jù)庫是一個關(guān)系型數(shù)據(jù)庫管理系統(tǒng),所采用的SQL語言是用于訪問數(shù)據(jù)庫最常用的標(biāo)準(zhǔn)會語言,需要的朋友可以參考下2023-07-07
MySQL自動為查詢數(shù)據(jù)結(jié)果加序號
這篇文章主要給大家介紹了關(guān)于MYSQL如何自動為查詢數(shù)據(jù)的結(jié)果編上序號的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家學(xué)習(xí)或者使用mysql具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起看看吧2022-12-12
MySQL的Replace into 與Insert into on duplicate key update真正的不同
今天聽同事介紹oracle到mysql的數(shù)據(jù)migration，他用了Insert into ..... on duplicate key update ...，我當(dāng)時就想怎么不用Replace呢，于是回來就仔細(xì)查了下，它們果然還是有區(qū)別的2014-02-02
這篇文章主要給大家介紹了關(guān)于MySQL的時間進位問題的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家學(xué)習(xí)或者使用MySQL具有一定的參考學(xué)習(xí)價值，需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧2019-12-12
M1芯片安裝mysql8.0數(shù)據(jù)庫的實現(xiàn)步驟(圖文)
這篇文章主要介紹了M1芯片安裝mysql8.0數(shù)據(jù)庫的實現(xiàn)實現(xiàn)步驟，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-02-02