MySQL數(shù)據(jù)庫查詢之多表查詢總結

更新時間：2022年08月04日 14:55:57 作者：計科_xiao_cai_ji

最近遇到了多表查詢的需求,也稱為關聯(lián)查詢,指兩個或更多個表一起完成查詢操作,下面這篇文章主要給大家介紹了關于MySQL數(shù)據(jù)庫查詢之多表查詢的相關資料,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下

多表關系

在進行數(shù)據(jù)庫表結構的設計時，會根據(jù)業(yè)務的需求和業(yè)務模塊之間的關系，分析設計表結構，由于業(yè)務之間相互關聯(lián)，所以各個表結構之間也存在各種聯(lián)系

表與表之間的聯(lián)系：

1.一對多(多對一)

2.多對多

3.一對一

一對多(多對一)

例如，一個員工對應一個部門，一個部門可以對應多個員工

一般在多的一方創(chuàng)建外鍵，指向一的那一方

員工與部門，在員工表上設置外鍵，指向部門表

多對多

例如，一個學生可以選修多門課程，一個課程可以被多名學生選修

一般會建立第三張表，至少包含兩個外鍵，分別指向兩張表的主鍵

一對一

例如，用戶和自己的學歷信息的關系，一個人只對應一條學歷信息

可以在任意一方加入外鍵，關聯(lián)另一方的主鍵，并且設置外鍵為唯一(unique)

注：可以放在一張表中，但是對其進行拆分，一張表放基礎信息，另一張表放詳情，可以提升操作效率

多表查詢

概述：

從多張表中查詢數(shù)據(jù)

笛卡爾積：

笛卡爾積為兩個集合(兩張表)中的每條數(shù)據(jù)進行兩兩組合的結果

在多表查詢時會產生笛卡爾積，要通過添加條件消除笛卡爾積

dept表：

請?zhí)砑訄D片描述

emp表：

請?zhí)砑訄D片描述

查詢產生笛卡爾積的結果：

select * from emp, dept where emp.dept_id=dept.id;

消除笛卡爾積(添加條件)：

select * from emp, dept where emp.dept_id=dept.id;

請?zhí)砑訄D片描述

多表查詢的分類

1.連接查詢：

內連接：
   相當于查詢AB的交集部分
外連接：
       左外連接：
           查詢A的所有數(shù)據(jù)，同時拼接上B對應的數(shù)據(jù)
       右外連接：
           查詢B的所有數(shù)據(jù)，同時拼接上A中對應的數(shù)據(jù)
自連接：
   表與自身連接查詢
   自連接必須給表取別名

2.子查詢

數(shù)據(jù)準備

部門表：

請?zhí)砑訄D片描述

create table dept (
    id int auto_increment primary key comment 'id',
    name varchar(50) not null comment '部門名稱'
) comment '部門表';

insert into dept (id, name)
values (1, '研發(fā)部'),
       (2, '市場部'),
       (3, '財務部'),
       (4, '銷售部'),
       (5, '總經辦'),
       (6, '人事部');

員工表：

請?zhí)砑訄D片描述

create table emp(
    id int auto_increment primary key ,
    name varchar(50) not null ,
    age int,
    job varchar(20) comment '職位',
    salary int ,
    entrydate date comment '入職時間',
    managerid int comment '直屬領導id',
    dept_id int comment '所在部門id'
) comment '員工表';

insert into emp
values ( 1, '金庸', 66, '總裁', 20000, '2000-01-01', null, 5 ),
       ( 2, '張無忌', 20, '項目經理', 12500, '2005-12-05', 1, 1 ),
       ( 3, '楊曉', 33, '開發(fā)', 8400, '2000-11-03', 2, 1 ),
       ( 4, '韋一笑', 48, '開發(fā)', 11000, '2002-02-05', 2, 1 ),
       ( 5, '陳玉存', 43, '開發(fā)', 10500, '2004-09-07', 3, 1 ),
       ( 6, '小昭', 19, '程序員鼓勵師', 6600, '2004-10-12', 2, 1 ),
       ( 7, '滅絕', 60, '財務總監(jiān)', 8500, '2002-09-12', 1, 3 ),
       ( 8, '周芷若', 19, '會計', 48000, '2006-06-02', 7, 3 ),
       ( 9, '丁敏君', 23, '出納', 5250, '2009-05-13', 7, 3 ),
       ( 10, '趙敏', 20, '市場部總監(jiān)', 12500, '2004-10-12', 1, 2 ),
       ( 11, '鹿杖客', 56, '職員', 3750, '2006-10-03', 10, 2 ),
       ( 12, '何碧文', 19, '職員', 3750, '2007-05-09', 10, 2 ),
       ( 13, '東方白', 19, '職員', 5500, '2009-02-12', 10, 2 ),
       ( 14, '張三豐', 88, '銷售總監(jiān)', 14000, '2004-10-12', 1, 4 ),
       ( 15, '魚梁洲', 38, '銷售', 4600, '2004-10-12', 14, 4 ),
       ( 16, '宋遠橋', 40, '銷售', 4600, '2004-10-12', 14, 4 ),
       ( 17, '陳友諒', 42, null, 2000, '2011-10-12', 1, null );

內連接

語法：

# 隱式內連接
select 字段列表 from 表1,表2 where 條件;
# 顯示內連接
select 字段列表 from 表1 [inner] join 表2 on 連接條件;

內連接查詢的是兩張表交集的部分

# 查詢每一個員工的姓名及關聯(lián)的部門的名稱
select emp.name, dept.name from emp, dept where emp.dept_id=dept.id;
select emp.name, dept.name from emp inner join dept on emp.dept_id = dept.id;

外連接

語法：

# 左外連接
select 字段列表 from 表1 left [outer] join 表2 on 條件;
# 右外連接
select 字段列表 from 表1 right [outer] join 表2 on 條件;

左外連接相當于查詢表1的所有數(shù)據(jù)包含表1和表2交集的部分數(shù)據(jù)

右外連接相當于查詢表2的所有數(shù)據(jù)包含表1和表2交集部分的數(shù)據(jù)

# 查詢emp表的所有數(shù)據(jù)，和應于的部門信息(左)
select emp.*, dept.* from emp left outer join dept on emp.dept_id = dept.id;
# 查詢dept表的所有數(shù)據(jù)，和對于的員工信息(右)
select dept.*, emp.* from emp right outer join dept on emp.dept_id = dept.id;

左外連接和右外連接可以進行相互轉化

自連接

語法：

select 字段列表 from 表a 別名a join 表a 別名b on 條件;

自鏈接查詢可以是內連接查詢也可以是外連接查詢

# 查詢員工及其所屬領導的名字
# 自連接可以看成兩張一樣的表進行連接查詢
select a.name, b.name from emp a join emp b on a.managerid=b.id;

聯(lián)合查詢

union、union all

對于聯(lián)合查詢就是把多次查詢的結果合并起來，形成一個新的查詢結果集

語法：

select 字段列表 from 表a
union [all]
select 字段列表 from 表b

# 將薪資低于5000的員工和年齡大于50的員工查詢出來
select * from emp where salary>5000
union all
select * from emp where age>50;

# 沒有all重復滿足條件的只出現(xiàn)一次
# 將薪資低于5000的員工和年齡大于50的員工查詢出來
select * from emp where salary>5000
union
select * from emp where age>50;

對于聯(lián)合查詢的多張表的列數(shù)必須保持一致，字段類型也要保持一致

union all會將全部的數(shù)據(jù)直接合并在一起，union會對合并之后的數(shù)據(jù)去重

子查詢

概念：SQL語句中嵌套select語句為嵌套查詢，又稱子查詢

select * from 表1 where 字段=(select 字段 from 表2);

子查詢外的語句可以是insert、update、delete、select中的一個

根據(jù)子查詢的結構不同，分為：

標量子查詢：子查詢的結果為單個值
列子查詢：子查詢的結果為一列
行子查詢：子查詢的結果為一行
表子查詢：子查詢的結果為多行多列

根據(jù)子查詢的位置，分為：

where之后
from之后
select之后

標量子查詢

子查詢返回的結果是單個值(數(shù)字、字符串、日期等)，最簡單的形式，這種子查詢稱為標量子查詢
常用符號：=、<>、>、>=、<、<=

# 根據(jù)銷售部門的id查詢員工信息
# 先分開查詢
# 查詢銷售部門的id
select id from dept where name='銷售部'; #id為4
# 查詢銷售部門中員工的信息
select * from emp where dept_id=4;
# 合并為一個查詢
select * from emp where dept_id=(select dept.id from dept where dept.name='銷售部' );

列子查詢

子查詢的結果為一列(可以是多行)的，這種子查詢?yōu)榱凶硬樵?/p>

常用操作符：

# 列子查詢
# 查詢銷售部和市場部的所有員工信息
# 查詢銷售部和市場部的id
select id from dept where name='銷售部' or name='市場部'; #id為2 4
# 查詢兩個部門的所有員工
select * from emp where dept_id in (2,4);
# 合并
select * from emp where dept_id in (select id from dept where name='銷售部' or name='市場部');

行子查詢

子查詢返回的結果是一行(可以是多列)，這種子查詢?yōu)樾凶硬樵?/p>

常用操作符：=、<>、in、not in

# 查詢與張無忌的薪資及直屬領導相同的員工信息
# 查詢張無忌的薪資和直屬領導
select salary, managerid from emp where name='張無忌';
# 查詢與張無忌的薪資及直屬領導相同的員工信息
select * from emp where (salary,managerid)=(select salary, managerid from emp where name='張無忌');

表子查詢

子查詢的結果是多行多列這種查詢?yōu)楸碜硬樵?/p>

常用操作符：in

# 查詢與鹿杖客和宋遠橋的職位和薪資相同的員工信息
select * from emp where (job, salary) in ( select job, salary from emp where name in ('鹿杖客', '宋遠橋'));

表子查詢的子表作為臨時表

# 查詢入職日期是'2006-01-01‘之后的員工信息和部門信息
# 先查詢出入職在'2006-01-01‘之后員工的所有信息
# 與部門表左連接
select e.*, dept.* from (select * from emp where entrydate>'2006-01-01') e left outer join dept on e.dept_id=dept.id;

多表查詢案例

數(shù)據(jù)準備：

create table salgrade (
    grade int,
    losal int comment '本薪資等級的最低界限',
    hisal int comment '最高界限'
) comment '薪資等級表';
insert into salgrade values (1,0,3000);
insert into salgrade values (2,3001,5000);
insert into salgrade values (3,5001,8000);
insert into salgrade values (4,8001,10000);
insert into salgrade values (5,10001,15000);
insert into salgrade values (6,15001,20000);
insert into salgrade values (7,20001,25000);
insert into salgrade values (8,025001,30000);

1.查詢員工的姓名，年齡，職位，部門信息(隱式內連接)

select e.name, e.age, e.job, d.* 
from emp e, dept d 
where e.dept_id=d.id;

2.查詢年齡小于30的員工的姓名、年齡、職位、部門信息(顯示內連接)

select e.name,e.age,e.job,d.*
from emp e
inner join dept d on e.dept_id = d.id
where e.age<30;

3.查詢擁有員工的部門id，部門名稱

select distinct d.id,d.name
from emp e, dept d
where d.id=e.dept_id;

4.查詢所有年齡大于40的員工，及其歸屬部門名稱，如果員工沒有分配部門也要顯示

select e.*,d.name
from emp e
left outer join dept d on e.dept_id = d.id
where e.age>40;

5.查詢所有員工的工資等級

select e.*,s.grade
from emp e, salgrade s
where e.salary between s.losal and s.hisal;

6.查詢研發(fā)部所有員工的信息即工資等級

select e.*,s.grade
from emp e,dept d,salgrade s
where (e.dept_id=d.id) and (d.name='研發(fā)部') and (e.salary between s.losal and s.hisal);

7.查詢研發(fā)部員工的平均工資

select avg(e.salary)
from emp e, dept d
where e.dept_id=d.id and d.name='研發(fā)部';

8.查詢工資比滅絕高的員工信息

select *
from emp
where emp.salary > (
                      select e.salary
                      from emp e
                      where e.name='滅絕'
                      );

9.查詢比平均薪資高的員工信息

select *
from emp
where salary> (
    select avg(e.salary)
    from emp e
    );

10.查詢低于本部門平均工資的員工信息

select *
from emp
where emp.salary<(
    select avg(salary)
    from emp e
    where e.dept_id=emp.dept_id
    );

11.查詢所有部門信息，并統(tǒng)計部門的員工人數(shù)

select d.*, (
    select count(*)
    from emp
    where emp.dept_id=d.id
    )
from dept d;

總結

到此這篇關于MySQL數(shù)據(jù)庫查詢之多表查詢的文章就介紹到這了,更多相關MySQL多表查詢內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

MySQL與Mongo簡單的查詢實例代碼
本文通過一個實例給大家用MySQL和mongodb分別寫一個查詢，本文圖片并茂給大家介紹的非常詳細，感興趣的朋友參考下吧
2016-10-10
MySQL5.7.17安裝及配置圖文教程
這篇文章主要為大家詳細介紹了MySQL5.7.17安裝及配置圖文教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下
2017-10-10
阿里云服務器手動實現(xiàn)mysql雙機熱備的兩種方式
阿里云服務器由于不支持keepalive虛擬ip，導致無法通過keepalive來實現(xiàn)mysql的雙機熱備。我們這里要實現(xiàn)阿里云的雙機熱備有兩種方式。感興趣的朋友跟隨小編一起看看吧
2019-10-10
MySQL中執(zhí)行計劃explain命令示例詳解
這篇文章主要給大家介紹了關于MySQL中執(zhí)行計劃explain命令的相關資料，文中通過示例代碼介紹的非常詳細，對大家學習或者使用explain命令具有一定的參考學習價值，需要的朋友們下面說來一起學習學習吧
2018-05-05
mysql定時自動備份數(shù)據(jù)庫的方法步驟
我們都知道數(shù)據(jù)是無價，如果不對數(shù)據(jù)進行備份，相當是讓數(shù)據(jù)在裸跑，本文就介紹一下如何給mysql定時自動備份數(shù)據(jù)，感興趣的小伙伴們可以參考一下
2021-07-07
Mysql5.7忘記root密碼及mysql5.7修改root密碼的方法
這篇文章主要介紹了Mysql5.7忘記root密碼及mysql5.7修改root密碼的方法的相關資料,需要的朋友可以參考下
2016-01-01
MySQL 配置優(yōu)化(多個參數(shù))
安裝MySQL后，配置文件my.cnf在 /MySQL安裝目錄/share/mysql目錄中，該目錄中還包含多個配置文件可供參考，有my-large.cnf ，my-huge.cnf， my-medium.cnf，my-small.cnf，分別對應大中小型數(shù)據(jù)庫應用的配置。win環(huán)境下即存在于MySQL安裝目錄中的.ini文件
2016-05-05
mysql視圖之確保視圖的一致性（with check option）操作詳解
這篇文章主要介紹了mysql視圖之確保視圖的一致性（with check option）操作,結合實例形式詳細分析了視圖的一致性操作原理、實現(xiàn)技巧與操作注意事項,需要的朋友可以參考下
2019-12-12
mysql中的find_in_set字符串查找函數(shù)解析
這篇文章主要介紹了mysql中的find_in_set字符串查找函數(shù)，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教
2022-08-08
mysql的事務隔離級別詳細解析
這篇文章主要介紹了mysql的事務隔離級別詳細解析,事務是應用程序中一系列嚴密的操作,所有操作必須成功完成,否則在每個操作中所作的所有更改都會被撤消,需要的朋友可以參考下
2023-12-12