MySQL聯(lián)合查詢之輕松實現(xiàn)數(shù)據(jù)關(guān)聯(lián)詳解
1、聯(lián)合查詢
聯(lián)合查詢又稱為多表查詢，它的基本執(zhí)行過程就是笛卡爾積
1.1 認(rèn)識笛卡爾積
那么什么是笛卡爾積呢？
答：笛卡爾積就是將兩張表放在一起進行計算，把第一張表的每一行分別取出來和第二張表的每一行進行連接，得到一個新的行。
舉例說明笛卡爾積：
假設(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)文章
Windows?Server?2019?MySQL數(shù)據(jù)庫的安裝與配置理論+遠(yuǎn)程連接篇
mysql是一款關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，由MySQL?AB公司開發(fā)，目前屬于Oracle旗下產(chǎn)品，MySQL是最流行的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)之一。MySQL也是一款開源的SQL數(shù)據(jù)庫管理系統(tǒng)，是眾多小型網(wǎng)站作為網(wǎng)站數(shù)據(jù)庫的首選數(shù)據(jù)庫2023-05-05
利用MySQL函數(shù)實現(xiàn)判斷視頻擴展名的代碼
MySQL擁有強大的自定義函數(shù)功能，如下，我寫了一個用MySQL函數(shù) 判斷視頻地址是否可以手機端播放2012-02-02
MySQL5.7中自增主鍵在重啟后會重置,而MySQL8.0中通過重做日志持久化自增變量,避免重啟后主鍵沖突,本文介紹MySQL?8.0自增變量的持久化問題小結(jié),感興趣的朋友一起看看吧2024-11-11
這篇文章主要為大家分享了mysql 5.7.14安裝配置方法圖文教程,感興趣的朋友可以參考一下2016-08-08
MySQL中字段類型char、varchar和text的區(qū)別
今天小編就為大家分享一篇關(guān)于MySQL中字段類型char、varchar和text的區(qū)別，小編覺得內(nèi)容挺不錯的，現(xiàn)在分享給大家，具有很好的參考價值，需要的朋友一起跟隨小編來看看吧2019-03-03
mysql建庫時提示Specified key was too long max key length is 1000
本文將詳細(xì)提供mysql建庫時提示Specified key was too long max key length is 1000 bytes的問題的解決方法，有需求的朋友可以參考2012-11-11
MySQL數(shù)據(jù)庫設(shè)計概念及多表查詢和事物操作
數(shù)據(jù)庫設(shè)計就是根據(jù)業(yè)務(wù)系統(tǒng)具體需求，結(jié)合我們所選用的DBMS，為這個業(yè)務(wù)系統(tǒng)構(gòu)造出最優(yōu)的數(shù)據(jù)存儲模型,本文給大家介紹MySQL數(shù)據(jù)庫設(shè)計概念及多表查詢和事物操作，感興趣的朋友一起看看吧2022-05-05
mysql中l(wèi)imit用法詳解及注意事項總結(jié)
mysql數(shù)據(jù)庫中l(wèi)imit子句可以被用于強制select語句返回指定的記錄數(shù),這篇文章主要介紹了mysql中l(wèi)imit用法詳解及注意事項的相關(guān)資料,文中通過代碼介紹的非常詳細(xì),需要的朋友可以參考下2026-01-01
這篇文章主要給大家介紹了關(guān)于MySQL 8忘記密碼的處理方式，文中通過圖文介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧2019-02-02
這篇文章主要為大家詳細(xì)介紹了linux下使用RPM安裝mysql5.7.17的相關(guān)代碼，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-03-03