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