MySQL多表查詢示例詳解
更新時間：2025年09月09日 08:58:55 作者：xclic
本文系統(tǒng)講解了MySQL多表查詢的核心概念與分類,涵蓋內(nèi)連接、外連接（左/右）、自連接、聯(lián)合查詢及子查詢（標量/列/行/表）等類型,重點介紹了各查詢方式的語法、應用場景及注意事項,適用于關系型數(shù)據(jù)庫中數(shù)據(jù)關聯(lián)與整合的開發(fā)實踐,感興趣的朋友跟隨小編一起看看吧
什么是多表查詢
多表查詢，也稱為關聯(lián)查詢，指兩個或更多個表一起完成查詢操作。
可進行多表查詢的前提條件： 這些一起查詢的表之間是有關系的（一對一、一對多），它們之間一定是有關聯(lián)字段，如下圖：員工表和部門表，這兩個表依靠“部門編號”進行關聯(lián)，因此符合多表查詢的條件。
MySQL 多表查詢是指同時從兩個或多個關聯(lián)表中檢索數(shù)據(jù)，通過表之間的關系（如外鍵關聯(lián)）將分散的數(shù)據(jù)整合起來。這是實際開發(fā)中最常用的查詢方式之一，尤其在關系型數(shù)據(jù)庫中，數(shù)據(jù)通常按業(yè)務邏輯拆分到不同表中存儲。
1、核心概念
1.1 多表關系
- 一對多(多對一)
- 多對多
- 一對一
1.2 多表查詢分類
- 連接查詢
- 內(nèi)連接：相當于查詢A、B交集部分數(shù)據(jù)
- 外連接：
- 左外連接：查詢左表所有數(shù)據(jù)，以及兩張表交集部分數(shù)據(jù)
- 右外連接：查詢右表所有數(shù)據(jù)，以及兩張表交集部分數(shù)據(jù)
- 自連接：當前表與自身的連接查詢，自連接必須使用表別名
- 子查詢
- 標量子查詢（子查詢結(jié)果為單個值）
- 列子查詢(子查詢結(jié)果為一列)
- 行子查詢(子查詢結(jié)果為一行)
- 表子查詢(子查詢結(jié)果為多行多列)
1.3 表結(jié)構準備
-- 創(chuàng)建dept表
create table dept(
id int auto_increment comment 'ID' primary key,
name varchar(50) not null comment '部門名稱'
)comment '部門表';
-- 創(chuàng)建emp表
create table emp(
id int auto_increment comment 'ID' primary key,
name varchar(50) not null comment '姓名',
age int comment '年齡',
job varchar(20) comment '職位',
salary int comment '薪資',
entrydate date comment '入職時間',
managerid int comment '直屬領導ID',
dept_id int comment '部門ID'
)comment '員工表';
-- 添加外鍵
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);2、內(nèi)連接
內(nèi)連接查詢的是兩張表交集部分的數(shù)據(jù)。
內(nèi)連接的語法分為兩種: 隱式內(nèi)連接、顯式內(nèi)連接。
隱式內(nèi)連接
SELECT 字段列表 FROM 表1 , 表2 WHERE 條件 ... ;
顯式內(nèi)連接
SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 連接條件 ... ;
示例
-- 查詢每一個員工的姓名 , 及關聯(lián)的部門的名稱 (隱式內(nèi)連接實現(xiàn)) select emp.name , dept.name from emp , dept where emp.dept_id = dept.id ; -- 為每一張表起別名,簡化SQL編寫 select e.name,d.name from emp e , dept d where e.dept_id = d.id; -- 查詢每一個員工的姓名 , 及關聯(lián)的部門的名稱 (顯式內(nèi)連接實現(xiàn)) select e.name, d.name from emp e inner join dept d on e.dept_id = d.id; -- 為每一張表起別名,簡化SQL編寫 select e.name, d.name from emp e join dept d on e.dept_id = d.id;
表的別名:
- tablea as 別名1 , tableb as 別名2 ;
- tablea 別名1 , tableb 別名2 ;
注意事項:一旦為表起了別名，就不能再使用表名來指定對應的字段了，此時只能夠使用別名來指定字段。
3、外連接
外連接分為兩種，分別是：左外連接 和 右外連接。
3.1 語法
左外連接
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 條件 ... ;
左外連接相當于查詢表1(左表)的所有數(shù)據(jù)，當然也包含表1和表2交集部分的數(shù)據(jù)。
右外連接
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 條件 ... ;
右外連接相當于查詢表2(右表)的所有數(shù)據(jù)，當然也包含表1和表2交集部分的數(shù)據(jù)。
3.2 示例
-- 查詢emp表的所有數(shù)據(jù), 和對應的部門信息 select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id; select e.*, d.name from emp e left join dept d on e.dept_id = d.id; -- 查詢dept表的所有數(shù)據(jù), 和對應的員工信息(右外連接) select d.*, e.* from emp e right outer join dept d on e.dept_id = d.id; select d.*, e.* from dept d left outer join emp e on e.dept_id = d.id;
左外連接和右外連接是可以相互替換的，只需要調(diào)整在連接查詢時SQL中，表結(jié)構的先后順序就可以了。而我們在日常開發(fā)使用時，更偏向于左外連接。
4、自連接
自連接查詢，顧名思義，就是自己連接自己，也就是把一張表連接查詢多次。而對于自連接查詢，可以是內(nèi)連接查詢，也可以是外連接查詢。
4.1 語法
SELECT 字段列表 FROM 表A 別名A JOIN 表A 別名B ON 條件 ... ;
4.2 示例
-- 查詢員工 及其 所屬領導的名字 select a.name , b.name from emp a , emp b where a.managerid = b.id; -- 查詢所有員工 emp 及其領導的名字 emp , 如果員工沒有領導, 也需要查詢出來 select a.name '員工', b.name '領導' from emp a left join emp b on a.managerid = b.id;
注意：在自連接查詢中，必須要為表起別名，要不然我們不清楚所指定的條件、返回的字段，到底是哪一張表的字段。
5、聯(lián)合查詢
對于union查詢，就是把多次查詢的結(jié)果合并起來，形成一個新的查詢結(jié)果集。
5.1 語法
SELECT 字段列表 FROM 表A ... UNION [ ALL ] SELECT 字段列表 FROM 表B ....;
- 對于聯(lián)合查詢的多張表的列數(shù)必須保持一致，字段類型也需要保持一致。
- union all 會將全部的數(shù)據(jù)直接合并在一起，union 會對合并之后的數(shù)據(jù)去重。
5.2 示例
-- 將薪資低于 5000 的員工 , 和 年齡大于 50 歲的員工全部查詢出來. select * from emp where salary < 5000 union all select * from emp where age > 50;
注意：如果多條查詢語句查詢出來的結(jié)果，字段數(shù)量不一致，在進行union/union all聯(lián)合查詢時，將會報錯。
6、子查詢
SQL語句中嵌套SELECT語句，稱為嵌套查詢，又稱子查詢。
6.1 語法
SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );
子查詢外部的語句可以是INSERT / UPDATE / DELETE / SELECT 的任何一個。
6.2 分類
根據(jù)子查詢結(jié)果不同，分為：
- 標量子查詢（子查詢結(jié)果為單個值）
- 列子查詢(子查詢結(jié)果為一列)
- 行子查詢(子查詢結(jié)果為一行)
- 表子查詢(子查詢結(jié)果為多行多列)
根據(jù)子查詢位置，分為：
- WHERE之后
- FROM之后
- SELECT之后
6.3 標量子查詢
子查詢返回的結(jié)果是單個值（數(shù)字、字符串、日期等），最簡單的形式，這種子查詢稱為標量子查詢。
常用的操作符：= <> > >= < <=
示例
-- 查詢 "銷售部" 的所有員工信息 select * from emp where dept_id = (select id from dept where name = '銷售部'); -- 查詢在 "方東白" 入職之后的員工信息 select * from emp where entrydate > (select entrydate from emp where name = '方東 白')；
6.4 列子查詢
子查詢返回的結(jié)果是一列（可以是多行），這種子查詢稱為列子查詢。
常用的操作符：IN 、NOT IN 、 ANY 、SOME 、 ALL
示例
-- 查詢 "銷售部" 和 "市場部" 的所有員工信息 select * from emp where dept_id in (select id from dept where name = '銷售部' or name = '市場部'); -- 查詢比 財務部 所有人工資都高的員工信息 select * from emp where salary > all ( select salary from emp where dept_id = (select id from dept where name = '財務部') ); -- 查詢比研發(fā)部其中任意一人工資高的員工信息 select * from emp where salary > any ( select salary from emp where dept_id = (select id from dept where name = '研發(fā)部') );
6.5 行子查詢
子查詢返回的結(jié)果是一行（可以是多列），這種子查詢稱為行子查詢。
常用的操作符：= 、<> 、IN 、NOT IN
示例
-- 查詢與 "張無忌" 的薪資及直屬領導相同的員工信息 ; select * from emp where (salary,managerid) = (select salary, managerid from emp where name = '張無忌');
6.6 表子查詢
子查詢返回的結(jié)果是多行多列，這種子查詢稱為表子查詢。
常用的操作符：IN
示例
-- 查詢與 "鹿杖客" , "宋遠橋" 的職位和薪資相同的員工信息 select * from emp where (job,salary) in ( select job, salary from emp where name = '鹿杖客' or name = '宋遠橋' ); -- 查詢?nèi)肼毴掌谑?"2006-01-01" 之后的員工信息 , 及其部門信息 select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id ;
7、綜合練習
7.1 表結(jié)構準備
新增薪資等級表
create table salgrade(
grade int,
losal int,
hisal int
) comment '薪資等級表';7.2 示例
-- 查詢員工的姓名、年齡、職位、部門信息 （隱式內(nèi)連接） -- 連接條件: emp.dept_id = dept.id select e.name , e.age , e.job , d.name from emp e , dept d where e.dept_id = d.id; -- 查詢年齡小于30歲的員工的姓名、年齡、職位、部門信息（顯式內(nèi)連接） -- 連接條件: emp.dept_id = dept.id select e.name , e.age , e.job , d.name from emp e inner join dept d on e.dept_id = d.id where e.age < 30; -- 查詢擁有員工的部門ID、部門名稱 -- 連接條件: emp.dept_id = dept.id select distinct d.id , d.name from emp e , dept d where e.dept_id = d.id; -- 查詢所有年齡大于40歲的員工, 及其歸屬的部門名稱; 如果員工沒有分配部門, 也需要展示出來(外連接) -- 連接條件: emp.dept_id = dept.id select e.*, d.name from emp e left join dept d on e.dept_id = d.id where e.age > 40 ; -- 查詢所有員工的工資等級 -- 連接條件 : emp.salary >= salgrade.losal and emp.salary <= salgrade.hisal -- 方式一 select e.* , s.grade , s.losal, s.hisal from emp e , salgrade s where e.salary >= s.losal and e.salary <= s.hisal; -- 方式二 select e.* , s.grade , s.losal, s.hisal from emp e , salgrade s where e.salary between s.losal and s.hisal; -- 查詢 "研發(fā)部" 所有員工的信息及 工資等級 -- 連接條件 : emp.salary between salgrade.losal and salgrade.hisal , emp.dept_id = dept.id select e.* , s.grade from emp e , dept d , salgrade s where e.dept_id = d.id and (e.salary between s.losal and s.hisal ) and d.name = '研發(fā)部'; -- 查詢 "研發(fā)部" 員工的平均工資 -- 連接條件 : emp.dept_id = dept.id select avg(e.salary) from emp e, dept d where e.dept_id = d.id and d.name = '研發(fā)部'; -- 查詢工資比 "滅絕" 高的員工信息。 select * from emp where salary > ( select salary from emp where name = '滅絕' ); -- 查詢比平均薪資高的員工信息 select * from emp where salary > ( select avg(salary) from emp ); -- 查詢低于本部門平均工資的員工信息 select * from emp e2 where e2.salary < ( select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id ); -- 查詢所有的部門信息, 并統(tǒng)計部門的員工人數(shù) select d.id, d.name , ( select count(*) from emp e where e.dept_id = d.id ) '人數(shù)' from dept d; -- 查詢所有學生的選課情況, 展示出學生名稱, 學號, 課程名稱 -- 連接條件: student.id = student_course.studentid , course.id = student_course.courseid select s.name , s.no , c.name from student s , student_course sc , course c where s.id = sc.studentid and sc.courseid = c.id ;
到此這篇關于MySQL多表查詢的文章就介紹到這了,更多相關MySQL多表查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
這篇文章主要介紹了MySQL數(shù)據(jù)庫備份過程的注意事項，幫助大家更好的理解和維護MySQL，感興趣的朋友可以了解下2020-11-11
mysql高級學習之索引的優(yōu)劣勢及規(guī)則使用
這篇文章主要給大家介紹了關于mysql高級學習之索引的優(yōu)劣勢及規(guī)則使用的相關資料，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2021-03-03
這篇文章主要給大家介紹了關于Mysql事務中Update是否會鎖表的相關資料，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2020-12-12
這篇文章主要介紹了查看mysql當前連接數(shù)的方法詳解，本文給大家介紹的非常詳細，具有一定的參考借鑒價值,需要的朋友可以參考下2019-06-06
由于安裝MySQL的時候，疏忽沒有選擇底層編碼方式，采用默認的ASCII的編碼格式，于是接二連三的中文轉(zhuǎn)換問題隨之而來，就想卸載了重新安裝MYSQL，這一卸載倒是出了問題，導致安裝的時候安裝不上，在網(wǎng)上找了一個多小時也沒解決。2011-08-08
MySQL中ONLY_FULL_GROUP_BY的使用小結(jié)
ONLY_FULL_GROUP_BY是MySQL中的一個重要SQL模式,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧2024-12-12
這篇文章主要介紹了mysql如何查詢重復數(shù)據(jù)并刪除問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-12-12
這篇文章主要為大家詳細介紹了mysql5.5安裝配置方法的圖文教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-05-05
Navicat連接MySQL時出現(xiàn)的連接失敗問題及解決
這篇文章主要介紹了Navicat連接MySQL時出現(xiàn)的連接失敗問題及解決方案，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-05-05
varchar和char是MySQL中的兩種數(shù)據(jù)類型,都是用來存儲字符串的,下面這篇文章主要給大家介紹了關于MySQL字符之char、varchar類型的相關資料,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下2022-09-09