主頁 > 知識庫 > MySQL 連接查詢的原理和應(yīng)用

MySQL 連接查詢的原理和應(yīng)用

熱門標(biāo)簽:芒果電話機器人自動化 申請外呼電話線路 信陽穩(wěn)定外呼系統(tǒng)運營商 湖南人工外呼系統(tǒng)多少錢 廣東人工電話機器人 百度地圖圖標(biāo)標(biāo)注中心 日照旅游地圖標(biāo)注 石家莊電商外呼系統(tǒng) 南通自動外呼系統(tǒ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é)果后面進(jìn)行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ù)來進(jìn)行數(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ù)進(jìn)行在過濾篩選,所以我們可以在連接查詢之后再加上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)條件處理連接的表,這種處理可能是非常耗費資源的,連接的表越多,性能下降越厲害。所以要分析去除那些不必要的連接和不需要顯示的字段。

之前我的項目團(tuá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)文章!

您可能感興趣的文章:
  • php7連接MySQL實現(xiàn)簡易查詢程序的方法
  • Mysql join連接查詢的語法與示例
  • mysql連接查詢、聯(lián)合查詢、子查詢原理與用法實例詳解
  • MySQL利用UNION連接2個查詢排序失效詳解
  • python針對mysql數(shù)據(jù)庫的連接、查詢、更新、刪除操作示例
  • Mysql自連接查詢實例詳解
  • 詳解MySQL數(shù)據(jù)庫--多表查詢--內(nèi)連接,外連接,子查詢,相關(guān)子查詢
  • mysql多表連接查詢實例講解
  • mysql多個left join連接查詢用法分析
  • JDBC連接MySql數(shù)據(jù)庫步驟 以及查詢、插入、刪除、更新等
  • mysql連接查詢(左連接,右連接,內(nèi)連接)

標(biāo)簽:牡丹江 公主嶺 惠州 合肥 天津 阿里 沈陽 呼和浩特

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《MySQL 連接查詢的原理和應(yīng)用》,本文關(guān)鍵詞  MySQL,連接,查詢,的,原理,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請?zhí)峁┫嚓P(guān)信息告之我們,我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《MySQL 連接查詢的原理和應(yīng)用》相關(guān)的同類信息!
  • 本頁收集關(guān)于MySQL 連接查詢的原理和應(yīng)用的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章