MySQL多表關聯(lián)查詢方式及實際應用
更新時間：2024年07月01日 11:09:35 作者：埋頭苦干的小馮
MySQL語句學習的難點和重點就在于多表查詢,同時MySQL也有諸多方法供大家選擇,不論是多表聯(lián)查（聯(lián)結(jié)表、左連接、右連接……）,這篇文章主要給大家介紹了關于MySQL多表關聯(lián)查詢方式及實際應用的相關資料,需要的朋友可以參考下
一、關聯(lián)查詢方式
1、 inner join--內(nèi)連接
代表選擇的是兩個表的交差部分。
內(nèi)連接就是表間的主鍵與外鍵相連，只取得鍵值一致的，可以獲取雙方表中的數(shù)據(jù)連接方式。
基本語法
SELECT 列名1,列名2... FROM 表1 INNER JOIN 表2 ON 表1.外鍵=表2.主鍵 WhERE 條件語句;
2、left join--左連接
代表選擇的是前面一個表的全部。
左連接是以左表為標準，只查詢在左邊表中存在的數(shù)據(jù)，當然需要兩個表中的鍵值一致。
基本語法
SELECT 列名1 FROM 表1 LEFT OUTER JOIN 表2 ON 表1.外鍵=表2.主鍵 WhERE 條件語句;
3、 right join--右連接
代表選擇的是后面一個表的全部
同理，右連接將會以右邊作為基準，進行檢索。
基本語法
SELECT 列名1 FROM 表1 RIGHT OUTER JOIN 表2 ON 表1.外鍵=表2.主鍵 WhERE 條件語句;
4、自連接
自連接顧名思義就是自己跟自己連接，參與連接的表都是同一張表。(通過給表取別名虛擬出)。
基本語法
SELECT 字段名列表 FROM 表1 別名1,表名1 別名2 WHERE 別名1.字段名=別名1.字段名;
5、交叉連接
不適用任何匹配條件。生成笛卡爾積
基本語法
SELECT 列表名 FROM 表名1,表名2;
6、子查詢
1）：子查詢是將一個查詢語句嵌套在另一個查詢語句中。內(nèi)部嵌套其他select語句的查詢，稱為外查詢或主查詢
2）：內(nèi)層查詢語句的查詢結(jié)果，可以為外層查詢語句提供查詢條件。
3）：子查詢中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等關鍵字
4）：還可以包含比較運算符：= 、 !=、> 、<等
基本語法
SELECT 字段列表 FROM 表名 WHERE 字段名=(SELECT 字段名 FROM 表名);
二、關聯(lián)查詢應用
基于這三個表
mysql> select * from dept3; +--------+-----------+ | deptno | name | +--------+-----------+ | 1001 | 研發(fā)部 | | 1002 | 銷售部 | | 1003 | 財務部 | | 1004 | 人事部 | +--------+-----------+ 4 rows in set (0.00 sec) mysql> select * from emp3; +-----+--------------+------+---------+ | eid | ename | age | dept_id | +-----+--------------+------+---------+ | 1 | 喬峰 | 20 | 1001 | | 10 | 丁春秋 | 71 | 1005 | | 2 | 段譽 | 21 | 1001 | | 3 | 虛竹 | 23 | 1001 | | 4 | 阿紫 | 18 | 1001 | | 5 | 掃地僧 | 85 | 1002 | | 6 | 李秋水 | 33 | 1002 | | 7 | 鳩摩智 | 50 | 1002 | | 8 | 天山童姥 | 60 | 1003 | | 9 | 慕容博 | 58 | 1003 | +-----+--------------+------+---------+ 10 rows in set (0.00 sec) ? mysql> select * from emp; +-------+-----------+-----------+------+------------+-------+-------+--------+ | empno | ename | job | mgr | hiredate | sai | comm | deptno | +-------+-----------+-----------+------+------------+-------+-------+--------+ | 1001 | 甘寧 | 文員 | 1013 | 2000-12-17 | 8000 | NULL | 20 | | 1002 | 黛綺絲 | 銷售員 | 1006 | 2001-02-20 | 16000 | 3000 | 30 | | 1003 | 殷天正 | 銷售員 | 1006 | 2001-02-22 | 12500 | 5000 | 30 | | 1004 | 劉備 | 經(jīng)理 | 1009 | 2001-04-02 | 29750 | NULL | 20 | | 1005 | 謝遜 | 銷售員 | 1006 | 2001-09-28 | 12500 | 14000 | 30 | | 1006 | 關羽 | 經(jīng)理 | 1009 | 2001-05-01 | 28500 | NULL | 30 | | 1007 | 張飛 | 經(jīng)理 | 1009 | 2001-09-01 | 24500 | NULL | 10 | | 1008 | 諸葛亮 | 分析師 | 1004 | 2007-04-19 | 30000 | NULL | 20 | | 1009 | 曾阿牛 | 董事長 | NULL | 2001-11-17 | 50000 | NULL | 10 | | 1010 | 韋一笑 | 銷售員 | 1006 | 2001-09-08 | 15000 | 0 | 30 | | 1011 | 周泰 | 文員 | 1006 | 2007-05-23 | 11000 | NULL | 20 | | 1012 | 程普 | 文員 | 1006 | 2001-12-03 | 9500 | NULL | 30 | | 1013 | 龐統(tǒng) | 分析師 | 1004 | 2001-12-03 | 30000 | NULL | 20 | | 1014 | 黃蓋 | 文員 | 1007 | 2002-01-23 | 13000 | NULL | 10 | | 1015 | 張三 | 保潔員 | 1001 | 2013-05-01 | 80000 | 50000 | 50 | +-------+-----------+-----------+------+------------+-------+-------+--------+ 15 rows in set (0.00 sec) ?
1、inner join--內(nèi)連接
查詢銷售部的員工信息。
mysql> select name,emp3.* from dept3 inner join emp3
-> on dept3.deptno=emp3.dept_id
-> where name='銷售部';
+-----------+-----+-----------+------+---------+
| name | eid | ename | age | dept_id |
+-----------+-----+-----------+------+---------+
| 銷售部 | 5 | 掃地僧 | 85 | 1002 |
| 銷售部 | 6 | 李秋水 | 33 | 1002 |
| 銷售部 | 7 | 鳩摩智 | 50 | 1002 |
+-----------+-----+-----------+------+---------+
3 rows in set (0.00 sec)
2、left join--左連接
查看每員工所對應的部門。
mysql> select emp3.*,name from emp3 left join dept3
-> on emp3.dept_id=dept3.deptno;
+-----+--------------+------+---------+-----------+
| eid | ename | age | dept_id | name |
+-----+--------------+------+---------+-----------+
| 1 | 喬峰 | 20 | 1001 | 研發(fā)部 |
| 2 | 段譽 | 21 | 1001 | 研發(fā)部 |
| 3 | 虛竹 | 23 | 1001 | 研發(fā)部 |
| 4 | 阿紫 | 18 | 1001 | 研發(fā)部 |
| 5 | 掃地僧 | 85 | 1002 | 銷售部 |
| 6 | 李秋水 | 33 | 1002 | 銷售部 |
| 7 | 鳩摩智 | 50 | 1002 | 銷售部 |
| 8 | 天山童姥 | 60 | 1003 | 財務部 |
| 9 | 慕容博 | 58 | 1003 | 財務部 |
| 10 | 丁春秋 | 71 | 1005 | NULL |
+-----+--------------+------+---------+-----------+
10 rows in set (0.00 sec)
3、right join--右連接
查看每個部門對應的員工信息。
mysql> select name,emp3.* from dept3 right join emp3
-> on dept3.deptno=emp3.dept_id;
+-----------+-----+--------------+------+---------+
| name | eid | ename | age | dept_id |
+-----------+-----+--------------+------+---------+
| 研發(fā)部 | 1 | 喬峰 | 20 | 1001 |
| 研發(fā)部 | 2 | 段譽 | 21 | 1001 |
| 研發(fā)部 | 3 | 虛竹 | 23 | 1001 |
| 研發(fā)部 | 4 | 阿紫 | 18 | 1001 |
| 銷售部 | 5 | 掃地僧 | 85 | 1002 |
| 銷售部 | 6 | 李秋水 | 33 | 1002 |
| 銷售部 | 7 | 鳩摩智 | 50 | 1002 |
| 財務部 | 8 | 天山童姥 | 60 | 1003 |
| 財務部 | 9 | 慕容博 | 58 | 1003 |
| NULL | 10 | 丁春秋 | 71 | 1005 |
+-----------+-----+--------------+------+---------+
4、自連接
在emp表中查詢姓名的上級領導（mgr）的名字。
mysql> select a.ename,b.ename 領導名字 from emp a,emp b
-> where a.mgr=b.empno;
+-----------+--------------+
| ename | 領導名字 |
+-----------+--------------+
| 甘寧 | 龐統(tǒng) |
| 黛綺絲 | 關羽 |
| 殷天正 | 關羽 |
| 劉備 | 曾阿牛 |
| 謝遜 | 關羽 |
| 關羽 | 曾阿牛 |
| 張飛 | 曾阿牛 |
| 諸葛亮 | 劉備 |
| 韋一笑 | 關羽 |
| 周泰 | 關羽 |
| 程普 | 關羽 |
| 龐統(tǒng) | 劉備 |
| 黃蓋 | 張飛 |
| 張三 | 甘寧 |
+-----------+--------------+
14 rows in set (0.00 sec)
5、交叉連接
mysql> select * from emp3,dept3; +-----+--------------+------+---------+--------+-----------+ | eid | ename | age | dept_id | deptno | name | +-----+--------------+------+---------+--------+-----------+ | 1 | 喬峰 | 20 | 1001 | 1001 | 研發(fā)部 | | 1 | 喬峰 | 20 | 1001 | 1002 | 銷售部 | | 1 | 喬峰 | 20 | 1001 | 1003 | 財務部 | | 1 | 喬峰 | 20 | 1001 | 1004 | 人事部 | | 10 | 丁春秋 | 71 | 1005 | 1001 | 研發(fā)部 | | 10 | 丁春秋 | 71 | 1005 | 1002 | 銷售部 | | 10 | 丁春秋 | 71 | 1005 | 1003 | 財務部 | | 10 | 丁春秋 | 71 | 1005 | 1004 | 人事部 | | 2 | 段譽 | 21 | 1001 | 1001 | 研發(fā)部 | | 2 | 段譽 | 21 | 1001 | 1002 | 銷售部 | | 2 | 段譽 | 21 | 1001 | 1003 | 財務部 | | 2 | 段譽 | 21 | 1001 | 1004 | 人事部 | | 3 | 虛竹 | 23 | 1001 | 1001 | 研發(fā)部 | | 3 | 虛竹 | 23 | 1001 | 1002 | 銷售部 | | 3 | 虛竹 | 23 | 1001 | 1003 | 財務部 | | 3 | 虛竹 | 23 | 1001 | 1004 | 人事部 | | 4 | 阿紫 | 18 | 1001 | 1001 | 研發(fā)部 | | 4 | 阿紫 | 18 | 1001 | 1002 | 銷售部 | | 4 | 阿紫 | 18 | 1001 | 1003 | 財務部 | | 4 | 阿紫 | 18 | 1001 | 1004 | 人事部 | | 5 | 掃地僧 | 85 | 1002 | 1001 | 研發(fā)部 | | 5 | 掃地僧 | 85 | 1002 | 1002 | 銷售部 | | 5 | 掃地僧 | 85 | 1002 | 1003 | 財務部 | | 5 | 掃地僧 | 85 | 1002 | 1004 | 人事部 | | 6 | 李秋水 | 33 | 1002 | 1001 | 研發(fā)部 | | 6 | 李秋水 | 33 | 1002 | 1002 | 銷售部 | | 6 | 李秋水 | 33 | 1002 | 1003 | 財務部 | | 6 | 李秋水 | 33 | 1002 | 1004 | 人事部 | | 7 | 鳩摩智 | 50 | 1002 | 1001 | 研發(fā)部 | | 7 | 鳩摩智 | 50 | 1002 | 1002 | 銷售部 | | 7 | 鳩摩智 | 50 | 1002 | 1003 | 財務部 | | 7 | 鳩摩智 | 50 | 1002 | 1004 | 人事部 | | 8 | 天山童姥 | 60 | 1003 | 1001 | 研發(fā)部 | | 8 | 天山童姥 | 60 | 1003 | 1002 | 銷售部 | | 8 | 天山童姥 | 60 | 1003 | 1003 | 財務部 | | 8 | 天山童姥 | 60 | 1003 | 1004 | 人事部 | | 9 | 慕容博 | 58 | 1003 | 1001 | 研發(fā)部 | | 9 | 慕容博 | 58 | 1003 | 1002 | 銷售部 | | 9 | 慕容博 | 58 | 1003 | 1003 | 財務部 | | 9 | 慕容博 | 58 | 1003 | 1004 | 人事部 | +-----+--------------+------+---------+--------+-----------+ 40 rows in set (0.00 sec)
6、子查詢
在emp3表中年齡大于平均年齡的姓名。
mysql> select ename,age from emp3
-> where age>(select avg(age) from emp3);
+--------------+------+
| ename | age |
+--------------+------+
| 丁春秋 | 71 |
| 掃地僧 | 85 |
| 鳩摩智 | 50 |
| 天山童姥 | 60 |
| 慕容博 | 58 |
+--------------+------+
5 rows in set (0.00 sec)
#驗證是否查詢正確，看看平均年齡是多少
mysql> select avg(age) 平均年齡 from emp3;
+--------------+
| 平均年齡 |
+--------------+
| 43.9000 |
+--------------+
1 row in set (0.00 sec)
7、group_concat（字段名）函數(shù)的使用
查詢每個部門的員工姓名。
emp表
mysql> select job,group_concat(ename) from emp group by job; +-----------+--------------------------------------+ | job | group_concat(ename) | +-----------+--------------------------------------+ | 保潔員 | 張三 | | 分析師 | 諸葛亮,龐統(tǒng) | | 文員 | 甘寧,周泰,程普,黃蓋 | | 經(jīng)理 | 劉備,關羽,張飛 | | 董事長 | 曾阿牛 | | 銷售員 | 黛綺絲,殷天正,謝遜,韋一笑 | +-----------+--------------------------------------+ 6 rows in set (0.00 sec)
emp3和dept3表
mysql> select name,group_concat(ename) from dept3 left join emp3
-> on dept3.deptno=emp3.dept_id
-> group by dept3.name;
+-----------+-------------------------------+
| name | group_concat(ename) |
+-----------+-------------------------------+
| 人事部 | NULL |
| 研發(fā)部 | 段譽,阿紫,喬峰,虛竹 |
| 財務部 | 天山童姥,慕容博 |
| 銷售部 | 李秋水,掃地僧,鳩摩智 |
+-----------+-------------------------------+
4 rows in set (0.00 sec)
注：在使用的過程中根據(jù)需求聯(lián)合使用。
總結(jié)
到此這篇關于MySQL多表關聯(lián)查詢方式及實際應用的文章就介紹到這了,更多相關MySQL多表關聯(lián)查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
實現(xiàn)MySQL與elasticsearch的數(shù)據(jù)同步的代碼示例
MySQL 自身簡單、高效、可靠，是又拍云內(nèi)部使用最廣泛的數(shù)據(jù)庫，但是當數(shù)據(jù)量達到一定程度的時候，對整個 MySQL 的操作會變得非常遲緩，這個時候我們就需要MySQL與elasticsearch數(shù)據(jù)同步，接下來就給大家介紹如何實現(xiàn)數(shù)據(jù)同步2023-07-07
這篇文章主要介紹了Ubuntu16.04安裝mysql5.7.22的圖文教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2018-07-07
這篇文章主要介紹了Mysql select語句設置默認值的方法,需要的朋友可以參考下2014-05-05
在MySQL 5.7及更高版本中,虛擬列是一個非常有用的特性,本文主要介紹了MySQL 虛擬列和虛擬索引的實現(xiàn)示例,具有一定的參考價值,感興趣的可以了解一下2025-03-03
當連接數(shù)據(jù)庫失敗次數(shù)過多時，MySQL 是否會限制登錄呢？數(shù)據(jù)庫服務端應該怎么應對暴力破解呢？本篇文章介紹下 MySQL 中的連接控制插件，一起來學習下此插件的作用,需要的朋友可以參考下面文章內(nèi)容2021-09-09
mysql臨時表(temporary?table)使用方法詳解
MySQL臨時表在很多場景中都會用到,MySQL內(nèi)部在執(zhí)行復雜SQL時,需要借助臨時表進行分組、排序、去重等操作,下面這篇文章主要給大家介紹了關于mysql臨時表(temporary?table)使用方法的相關資料,需要的朋友可以參考下2024-01-01
MySQL性能優(yōu)化之Open_Table配置參數(shù)的合理配置建議
這篇文章主要介紹了MySQL性能優(yōu)化之Open_Table配置參數(shù)的合理配置建議,在MySQL數(shù)據(jù)庫中，Opened_tables表示打開過的表數(shù)量,需要的朋友可以參考下2014-07-07
MySQL索引是加速數(shù)據(jù)檢索的特殊結(jié)構(gòu),用于存儲列值與位置信息,常見的索引類型包括：主鍵索引、唯一索引、普通索引、復合索引、全文索引和空間索引等,本文介紹MySQL索引簡介及常見的索引類型有哪些,感興趣的朋友一起看看吧2025-08-08
這篇文章主要介紹了關于Mysql的InnoDB鎖概述,對于更新操作（UPDATE、INSERT、DELETE），InnoDB會自動給涉及到的數(shù)據(jù)集加排他鎖,需要的朋友可以參考下2023-05-05
本文主要介紹了MySQL清空所有表的數(shù)據(jù)方法示例,要清空MySQL數(shù)據(jù)庫中所有表的數(shù)據(jù),但保留表結(jié)構(gòu),下面就介紹了幾種常用的方法,具有一定的參考價值,感興趣的可以了解一下2024-07-07