MySQL聯(lián)合查詢之輕松實現(xiàn)數(shù)據(jù)關(guān)聯(lián)詳解
1、聯(lián)合查詢
聯(lián)合查詢又稱為多表查詢，它的基本執(zhí)行過程就是笛卡爾積
1.1 認識笛卡爾積
那么什么是笛卡爾積呢？
答：笛卡爾積就是將兩張表放在一起進行計算，把第一張表的每一行分別取出來和第二張表的每一行進行連接，得到一個新的行。
舉例說明笛卡爾積：
假設(shè)我們現(xiàn)在有兩張表分別為學(xué)生信息表和班級信息表
現(xiàn)在我們把這兩張表進行笛卡爾積操作：
笛卡爾積相當(dāng)于乘法運算，列數(shù)是兩個表列數(shù)之和，行數(shù)是兩個表行數(shù)之積
注：笛卡爾積執(zhí)行后產(chǎn)生的結(jié)果大多是無效的，此時我們就可以采用條件進行篩選
1.2 笛卡爾積進行多表查詢
接下來我們就來試一下如何進行笛卡爾積操作：
首先創(chuàng)建兩張表，分別為上述的學(xué)生信息表和班級信息表，創(chuàng)建完兩張表后并給它們添加上述表中的內(nèi)容
select * from student; +----+------+---------+ | id | name | classid | +----+------+---------+ | 1 | 張三 | 1 | | 2 | 李四 | 2 | | 3 | 王五 | 1 | +----+------+---------+
select * from class; +---------+-----------+ | classid | classname | +---------+-----------+ | 1 | 舞蹈班 | | 2 | 跆拳道班 | +---------+-----------+
然后對這兩張表進行笛卡爾積操作
select * from student,class; +----+------+---------+---------+-----------+ | id | name | classid | classid | classname | +----+------+---------+---------+-----------+ | 1 | 張三 | 1 | 1 | 舞蹈班 | | 1 | 張三 | 1 | 2 | 跆拳道班 | | 2 | 李四 | 2 | 1 | 舞蹈班 | | 2 | 李四 | 2 | 2 | 跆拳道班 | | 3 | 王五 | 1 | 1 | 舞蹈班 | | 3 | 王五 | 1 | 2 | 跆拳道班 | +----+------+---------+---------+-----------+
上述的笛卡爾積執(zhí)行后產(chǎn)生的結(jié)果大多數(shù)是無效的，此時我們就可以采用條件進行篩選
當(dāng) student 表中的 classid 和 class 表中的 classid 相等時，則這條數(shù)據(jù)是有效的數(shù)據(jù)
select * from student,class where classid = classid; ERROR 1052 (23000): Column 'classid' in where clause is ambiguous
如果直接用 classid = classid 則會報錯，因為它無法分辨哪個classid 和 哪個 classid 進行比較
select * from student,class where student.classid = class.classid; +----+------+---------+---------+-----------+ | id | name | classid | classid | classname | +----+------+---------+---------+-----------+ | 1 | 張三 | 1 | 1 | 舞蹈班 | | 2 | 李四 | 2 | 2 | 跆拳道班 | | 3 | 王五 | 1 | 1 | 舞蹈班 | +----+------+---------+---------+-----------+
那么此時就可以采用 表名.列名 的方式進行區(qū)分
注：多表查詢除了可以加上連接條件外，還可以加上其他條件
多表查詢出來用 from 多個表加逗號分隔 來連接多個表外，還可以用 join on 來連接，還可以是 inner join on
join on 實現(xiàn)多表查詢：
select * from student join class on student.classid = class.classid; +----+------+---------+---------+-----------+ | id | name | classid | classid | classname | +----+------+---------+---------+-----------+ | 1 | 張三 | 1 | 1 | 舞蹈班 | | 2 | 李四 | 2 | 2 | 跆拳道班 | | 3 | 王五 | 1 | 1 | 舞蹈班 | +----+------+---------+---------+-----------+
join 連接的是兩個表，on 后面跟的是連接條件
inner join on 實現(xiàn)多表查詢：
select * from student inner join class on student.classid = class.classid; +----+------+---------+---------+-----------+ | id | name | classid | classid | classname | +----+------+---------+---------+-----------+ | 1 | 張三 | 1 | 1 | 舞蹈班 | | 2 | 李四 | 2 | 2 | 跆拳道班 | | 3 | 王五 | 1 | 1 | 舞蹈班 | +----+------+---------+---------+-----------+
inner join on 其實跟 join on 一樣，此處不做過多解釋
from 多個表 和 join on 的主要區(qū)別：
- from 多個表只能實現(xiàn)內(nèi)連接
- join on 既可以實現(xiàn)內(nèi)連接也可以實現(xiàn)外連接
1.3 內(nèi)連接和外連接
內(nèi)連接 和 外連接的主要區(qū)別：
- 當(dāng)連接的兩個表里面的數(shù)據(jù)是一一對應(yīng)的時候，內(nèi)連接和外連接其實就沒啥區(qū)別
- 當(dāng)連接的兩個表里面的數(shù)據(jù)不是一一對應(yīng)的時候，內(nèi)連接和外連接就有區(qū)別了
1.3.1 兩張表一一對應(yīng)
現(xiàn)在有兩張表，分別為 student 學(xué)生表 和 score 成績表：
select * from student; +----+------+ | id | name | +----+------+ | 1 | 張三 | | 2 | 李四 | | 3 | 王五 | +----+------+
select * from scoretable; +-----------+-------+ | studentId | score | +-----------+-------+ | 1 | 97 | | 2 | 86 | | 3 | 73 | +-----------+-------+
id 和 studentId 是一一對應(yīng)的，所有內(nèi)連接和外連接沒什么區(qū)別
內(nèi)連接：
select * from student,scoreTable where student.id = scoretable.studentId; +----+------+-----------+-------+ | id | name | studentId | score | +----+------+-----------+-------+ | 1 | 張三 | 1 | 97 | | 2 | 李四 | 2 | 86 | | 3 | 王五 | 3 | 73 | +----+------+-----------+-------+
外連接：
select * from student join scoreTable on student.id = scoretable.studentId; +----+------+-----------+-------+ | id | name | studentId | score | +----+------+-----------+-------+ | 1 | 張三 | 1 | 97 | | 2 | 李四 | 2 | 86 | | 3 | 王五 | 3 | 73 | +----+------+-----------+-------+
1.3.2 兩張表不一一對應(yīng)
現(xiàn)在有兩張表，分別為 student 學(xué)生表 和 score 成績表：
select * from student; +----+------+ | id | name | +----+------+ | 1 | 張三 | | 2 | 李四 | | 3 | 王五 | +----+------+
select * from scoretable; +-----------+-------+ | studentId | score | +-----------+-------+ | 1 | 97 | | 2 | 86 | | 6 | 73 | +-----------+-------+
現(xiàn)在我們可以看到 student 中的 id 為 3 的在 scoretable 中沒有對應(yīng)的 studentId
內(nèi)連接：
select * from student,scoreTable where student.id = scoretable.studentId; +----+------+-----------+-------+ | id | name | studentId | score | +----+------+-----------+-------+ | 1 | 張三 | 1 | 97 | | 2 | 李四 | 2 | 86 | +----+------+-----------+-------+
當(dāng)進行內(nèi)連接的時候因為student中的id為3 的和scoretable中的studentId為6的不匹配所以就被篩除了沒有查詢出來
外連接：
當(dāng)兩張表不是一一對應(yīng)的時候，外連接又可以分為 左外連接 和 右外連接
- 左外連接：left join on
select * from student left join scoreTable on student.id = scoretable.studentId; +----+------+-----------+-------+ | id | name | studentId | score | +----+------+-----------+-------+ | 1 | 張三 | 1 | 97 | | 2 | 李四 | 2 | 86 | | 3 | 王五 | NULL | NULL | +----+------+-----------+-------+
左外連接會把左表的結(jié)果盡量顯示出來，如果右表中沒有對應(yīng)的記錄，就使用NULL填充
- 右外連接：right join on
select * from student right join scoreTable on student.id = scoretable.studentId; +------+------+-----------+-------+ | id | name | studentId | score | +------+------+-----------+-------+ | 1 | 張三 | 1 | 97 | | 2 | 李四 | 2 | 86 | | NULL | NULL | 6 | 73 | +------+------+-----------+-------+
右外連接會把右表的結(jié)果盡量顯示出來，如果左表中沒有對應(yīng)的記錄，就使用NULL填充
1.4 自連接
自連接：自己和自己進行笛卡爾積
自連接使用場景：當(dāng)行與行進行比較時，就可以使用自連接，將行轉(zhuǎn)成列進行比較
現(xiàn)在有兩張表，分別為 scoretable 和 course
scoretable 表：
select * from scoretable; +-------+------------+-----------+ | score | student_id | course_id | +-------+------------+-----------+ | 70 | 1 | 1 | | 96 | 1 | 2 | | 97 | 1 | 3 | | 80 | 2 | 1 | | 92 | 2 | 2 | | 86 | 2 | 3 | | 91 | 3 | 1 | | 76 | 3 | 2 | | 77 | 3 | 3 | +-------+------------+-----------+
course 表：
select * from course; +----+------+ | id | name | +----+------+ | 1 | 語文 | | 2 | 數(shù)學(xué) | | 3 | 英語 | +----+------+
現(xiàn)在我們要查詢哪些同學(xué)的語文成績比英語成績低
首先自連接，將行轉(zhuǎn)換成列：
select * from scoretable,scoretable; ERROR 1066 (42000): Not unique table/alias: 'scoretable'
自己跟自己連接名字不能重復(fù)
那表名不能重復(fù)，那還如何自連接呢？
答：可以起別名，起別名不光可以對列，還可以對表
select * from scoretable as s1,scoretable as s2;
自連接排列組合的時候會產(chǎn)生大量無效的數(shù)據(jù)，所有就需要指定連接條件
指定連接條件，將有效數(shù)據(jù)篩選出來：
select * from scoretable as s1,scoretable as s2 where s1.student_id = s2.student_id;
自連接的時候只有當(dāng) student_id 相等時才表示有效數(shù)據(jù)
添加條件，將左邊表的語文成績和右邊表的英語成績查詢出來：
有效成績查詢出來后，就需要加上條件查詢出左邊的語文成績和右邊的英語成績
select * from scoretable as s1,scoretable as s2 where s1.student_id = s2.student_id and s1.course_id = 1 and s2.course_id = 3; +-------+------------+-----------+-------+------------+-----------+ | score | student_id | course_id | score | student_id | course_id | +-------+------------+-----------+-------+------------+-----------+ | 70 | 1 | 1 | 97 | 1 | 3 | | 80 | 2 | 1 | 86 | 2 | 3 | | 91 | 3 | 1 | 77 | 3 | 3 | +-------+------------+-----------+-------+------------+-----------+
這樣就將左側(cè)的語文成績查詢出來了，右側(cè)的英語成績查詢出來了
添加條件，將語文成績比英語成績低的同學(xué)查詢出來：
接下來就要查詢哪些同學(xué)的語文成績比英語成績低
select * from scoretable as s1,scoretable as s2 where s1.student_id = s2.student_id and s1.course_id = 1 and s2.course_id = 3 and s1.score < s2.score; +-------+------------+-----------+-------+------------+-----------+ | score | student_id | course_id | score | student_id | course_id | +-------+------------+-----------+-------+------------+-----------+ | 70 | 1 | 1 | 97 | 1 | 3 | | 80 | 2 | 1 | 86 | 2 | 3 | +-------+------------+-----------+-------+------------+-----------+ 2 rows in set (0.00 sec)
這樣就把語文成績比英語成績低的學(xué)生信息查詢出來了
1.5 子查詢
子查詢：把多個 SQL 組合成一個
在實際開發(fā)中，子查詢得慎用。因為子查詢可能會構(gòu)造出非常復(fù)雜，非常不好理解的 SQL。
寫代碼一般要么追求可讀性和可維護性，要么追求程序的運行速度
1.5.1 單行子查詢
單行子查詢：返回一行記錄的子查詢
student 表：
select * from student; +----+----------+------+ | id | class_id | name | +----+----------+------+ | 1 | 1 | 張三 | | 2 | 1 | 李四 | | 3 | 2 | 王五 | | 4 | 3 | 趙六 | | 5 | 2 | 王七 | +----+----------+------+
現(xiàn)在查詢“張三”的同班同學(xué)，根據(jù) class_id 進行查詢
分開查詢：
//查詢出張三的class_id select class_id from student where name = '張三'; +----------+ | class_id | +----------+ | 1 | +----------+
//查詢出來的張三的class_id為 1,再查詢除了張三以外的class_id 為1的同學(xué) select * from student where class_id = 1 and name != '張三'; +----+----------+------+ | id | class_id | name | +----+----------+------+ | 2 | 1 | 李四 | +----+----------+------+
單行子查詢：
select * from student where class_id = ( select class_id from student where name = '張三') and name != '張三'; +----+----------+------+ | id | class_id | name | +----+----------+------+ | 2 | 1 | 李四 | +----+----------+------+
1.5.2 多行子查詢
多行子查詢：返回多行記錄的子查詢
scoretable 表：
select * from scoretable; +-------+------------+-----------+ | score | student_id | course_id | +-------+------------+-----------+ | 70 | 1 | 1 | | 96 | 1 | 2 | | 97 | 1 | 3 | | 80 | 2 | 1 | | 92 | 2 | 2 | | 86 | 2 | 3 | | 91 | 3 | 1 | | 76 | 3 | 2 | | 77 | 3 | 3 | +-------+------------+-----------+
course 表：
select * from course; +----+------+ | id | name | +----+------+ | 1 | 語文 | | 2 | 數(shù)學(xué) | | 3 | 英語 | +----+------+
查詢每位同學(xué)的“語文”“英語”課程的成績信息
普通查詢：
//首先查詢出語文和英語成績對應(yīng)的id select id from course where name = '語文' or name = '英語'; +----+ | id | +----+ | 1 | | 3 | +----+
//再根據(jù)查詢出來的語文英語對應(yīng)的id，在 scoretable表中查詢 select * from scoretable where course_id = 1 or course_id = 3; +-------+------------+-----------+ | score | student_id | course_id | +-------+------------+-----------+ | 70 | 1 | 1 | | 97 | 1 | 3 | | 80 | 2 | 1 | | 86 | 2 | 3 | | 91 | 3 | 1 | | 77 | 3 | 3 | +-------+------------+-----------+
多行子查詢：
select * from scoretable where course_id in(select id from course where name = '語文' or name = '英語'); +-------+------------+-----------+ | score | student_id | course_id | +-------+------------+-----------+ | 70 | 1 | 1 | | 97 | 1 | 3 | | 80 | 2 | 1 | | 86 | 2 | 3 | | 91 | 3 | 1 | | 77 | 3 | 3 | +-------+------------+-----------+
1.5.3 合并查詢
合并查詢：就是將兩個查詢結(jié)果集，合并成一個
在實際應(yīng)用中，為了合并多個查詢的執(zhí)行結(jié)果，可以使用集合操作符 union，union all。使用UNION 和UNION ALL時，前后查詢的結(jié)果集中，字段需要一致
- union操作符
union操作符用于取得兩個結(jié)果集的并集，當(dāng)使用該操作符時，會自動去掉結(jié)果集中的重復(fù)行
course 表：
select * from course; +----+------+ | id | name | +----+------+ | 1 | 語文 | | 2 | 數(shù)學(xué) | | 3 | 英語 | | 6 | 化學(xué) | | 7 | 物理 | +----+------+
現(xiàn)在查詢 id 小于等于 2 或者 name 為 "英文" 的課程信息
select * from course where id <= 2 union select * from course where name = '英語'; +----+------+ | id | name | +----+------+ | 1 | 語文 | | 2 | 數(shù)學(xué) | | 3 | 英語 | +----+------+
看到這里大家可能有個疑問，明明可以用 or 也能實現(xiàn)，為什么還要用 union？
答：用 or 查詢只能時來自于同一個表，如果用 union 查詢可以時來自于不同的表，子要查詢的結(jié)果列匹配即可，匹配就是列的類型一樣、列的一樣、列的名字一樣
- union all 操作符
union all 操作符用于取得兩個結(jié)果集的并集，當(dāng)使用該操作符時，不會去掉結(jié)果集中的重復(fù)行
select * from course where id < 3 union all select * from course where name = '數(shù)學(xué)'; +----+------+ | id | name | +----+------+ | 1 | 語文 | | 2 | 數(shù)學(xué) | | 2 | 數(shù)學(xué) | +----+------+
總結(jié)
到此這篇關(guān)于MySQL聯(lián)合查詢之輕松實現(xiàn)數(shù)據(jù)關(guān)聯(lián)的文章就介紹到這了,更多相關(guān)MySQL實現(xiàn)數(shù)據(jù)關(guān)聯(lián)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要介紹了MySQL建表設(shè)置默認值的取值范圍,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-10-10
Mysql將一個表中的某一列數(shù)據(jù)復(fù)制到另一個表中某一列里的方法
今天小編就為大家分享一篇關(guān)于Mysql將一個表中的某一列數(shù)據(jù)復(fù)制到另一個表中某一列里的方法，小編覺得內(nèi)容挺不錯的，現(xiàn)在分享給大家，具有很好的參考價值，需要的朋友一起跟隨小編來看看吧2019-03-03
這篇文章主要介紹了MySQL字段定義時的屬性設(shè)置方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-04-04
mysql數(shù)據(jù)備份與恢復(fù)實現(xiàn)方法分析
這篇文章主要介紹了mysql數(shù)據(jù)備份與恢復(fù)實現(xiàn)方法,結(jié)合實例形式分析了mysql數(shù)據(jù)備份與恢復(fù)常見實現(xiàn)方法與相關(guān)操作注意事項,需要的朋友可以參考下2020-04-04
這篇文章主要介紹了Mysql如何查詢字符串開頭的數(shù)據(jù)問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-07-07
master and slave have equal MySQL server ids
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids2013-07-07
這篇文章主要為大家詳細介紹了Win10下mysql 8.0.15 安裝配置方法圖文教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2019-02-02
這篇文章主要介紹了mysql的集群模式 galera-cluster部署詳解，小編覺得挺不錯的，現(xiàn)在分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2019-02-02
count(1)、count(*)與count(列名)的執(zhí)行區(qū)別詳解
這篇文章主要介紹了count(1)、count(*)與count(列名)的執(zhí)行區(qū)別詳解，文中通過示例代碼介紹的非常詳細，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-11-11
這篇文章主要介紹了MySQL DDL 引發(fā)的同步延遲該如何解決，幫助大家更好的理解和學(xué)習(xí)使用MySQL數(shù)據(jù)庫，感興趣的朋友可以了解下2021-05-05