MySQL數(shù)據(jù)庫查詢之多表查詢總結(jié)
多表關(guān)系
在進(jìn)行數(shù)據(jù)庫表結(jié)構(gòu)的設(shè)計時，會根據(jù)業(yè)務(wù)的需求和業(yè)務(wù)模塊之間的關(guān)系，分析設(shè)計表結(jié)構(gòu)，由于業(yè)務(wù)之間相互關(guān)聯(lián)，所以各個表結(jié)構(gòu)之間也存在各種聯(lián)系
表與表之間的聯(lián)系：
1.一對多(多對一)
2.多對多
3.一對一
一對多(多對一)
例如，一個員工對應(yīng)一個部門，一個部門可以對應(yīng)多個員工
一般在多的一方創(chuàng)建外鍵，指向一的那一方
員工與部門，在員工表上設(shè)置外鍵，指向部門表
多對多
例如，一個學(xué)生可以選修多門課程，一個課程可以被多名學(xué)生選修
一般會建立第三張表，至少包含兩個外鍵，分別指向兩張表的主鍵
一對一
例如，用戶和自己的學(xué)歷信息的關(guān)系，一個人只對應(yīng)一條學(xué)歷信息
可以在任意一方加入外鍵，關(guān)聯(lián)另一方的主鍵，并且設(shè)置外鍵為唯一(unique)
注：可以放在一張表中，但是對其進(jìn)行拆分，一張表放基礎(chǔ)信息，另一張表放詳情，可以提升操作效率
多表查詢
概述：
從多張表中查詢數(shù)據(jù)
笛卡爾積：
笛卡爾積為兩個集合(兩張表)中的每條數(shù)據(jù)進(jìn)行兩兩組合的結(jié)果
在多表查詢時會產(chǎn)生笛卡爾積，要通過添加條件消除笛卡爾積
dept表：
emp表：
查詢產(chǎn)生笛卡爾積的結(jié)果：
select * from emp, dept where emp.dept_id=dept.id;
消除笛卡爾積(添加條件)：
select * from emp, dept where emp.dept_id=dept.id;
多表查詢的分類
1.連接查詢：
內(nèi)連接：
相當(dāng)于查詢AB的交集部分
外連接：
左外連接：
查詢A的所有數(shù)據(jù)，同時拼接上B對應(yīng)的數(shù)據(jù)
右外連接：
查詢B的所有數(shù)據(jù)，同時拼接上A中對應(yīng)的數(shù)據(jù)
自連接：
表與自身連接查詢
自連接必須給表取別名
2.子查詢
數(shù)據(jù)準(zhǔn)備
部門表：
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, '財務(wù)部'),
(4, '銷售部'),
(5, '總經(jīng)辦'),
(6, '人事部');
員工表：
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 '直屬領(lǐng)導(dǎo)id',
dept_id int comment '所在部門id'
) comment '員工表';
insert into emp
values ( 1, '金庸', 66, '總裁', 20000, '2000-01-01', null, 5 ),
( 2, '張無忌', 20, '項目經(jīng)理', 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, '財務(wù)總監(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, '宋遠(yuǎn)橋', 40, '銷售', 4600, '2004-10-12', 14, 4 ),
( 17, '陳友諒', 42, null, 2000, '2011-10-12', 1, null );
內(nèi)連接
語法：
# 隱式內(nèi)連接 select 字段列表 from 表1,表2 where 條件; # 顯示內(nèi)連接 select 字段列表 from 表1 [inner] join 表2 on 連接條件;
內(nèi)連接查詢的是兩張表交集的部分
# 查詢每一個員工的姓名及關(guān)聯(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 條件;
左外連接相當(dāng)于查詢表1的所有數(shù)據(jù)包含表1和表2交集的部分?jǐn)?shù)據(jù)
右外連接相當(dāng)于查詢表2的所有數(shù)據(jù)包含表1和表2交集部分的數(shù)據(jù)
# 查詢emp表的所有數(shù)據(jù)，和應(yīng)于的部門信息(左) 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;
左外連接和右外連接可以進(jìn)行相互轉(zhuǎn)化
自連接
語法：
select 字段列表 from 表a 別名a join 表a 別名b on 條件;
自鏈接查詢可以是內(nèi)連接查詢也可以是外連接查詢
# 查詢員工及其所屬領(lǐng)導(dǎo)的名字 # 自連接可以看成兩張一樣的表進(jìn)行連接查詢 select a.name, b.name from emp a join emp b on a.managerid=b.id;
聯(lián)合查詢
union、union all
對于聯(lián)合查詢就是把多次查詢的結(jié)果合并起來，形成一個新的查詢結(jié)果集
語法：
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重復(fù)滿足條件的只出現(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ù)子查詢的結(jié)構(gòu)不同，分為：
標(biāo)量子查詢：子查詢的結(jié)果為單個值
列子查詢：子查詢的結(jié)果為一列
行子查詢：子查詢的結(jié)果為一行
表子查詢：子查詢的結(jié)果為多行多列
根據(jù)子查詢的位置，分為：
where之后
from之后
select之后
標(biāo)量子查詢
子查詢返回的結(jié)果是單個值(數(shù)字、字符串、日期等)，最簡單的形式，這種子查詢稱為標(biāo)量子查詢
常用符號：=、<>、>、>=、<、<=
# 根據(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='銷售部' );
列子查詢
子查詢的結(jié)果為一列(可以是多行)的，這種子查詢?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='市場部');
行子查詢
子查詢返回的結(jié)果是一行(可以是多列)，這種子查詢?yōu)樾凶硬樵?/p>
常用操作符：=、<>、in、not in
# 查詢與張無忌的薪資及直屬領(lǐng)導(dǎo)相同的員工信息 # 查詢張無忌的薪資和直屬領(lǐng)導(dǎo) select salary, managerid from emp where name='張無忌'; # 查詢與張無忌的薪資及直屬領(lǐng)導(dǎo)相同的員工信息 select * from emp where (salary,managerid)=(select salary, managerid from emp where name='張無忌');
表子查詢
子查詢的結(jié)果是多行多列這種查詢?yōu)楸碜硬樵?/p>
常用操作符：in
# 查詢與鹿杖客和宋遠(yuǎn)橋的職位和薪資相同的員工信息
select * from emp where (job, salary) in ( select job, salary from emp where name in ('鹿杖客', '宋遠(yuǎn)橋'));
表子查詢的子表作為臨時表
# 查詢?nèi)肼毴掌谑?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ù)準(zhǔn)備：
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.查詢員工的姓名，年齡，職位，部門信息(隱式內(nèi)連接)
select e.name, e.age, e.job, d.* from emp e, dept d where e.dept_id=d.id;
2.查詢年齡小于30的員工的姓名、年齡、職位、部門信息(顯示內(nèi)連接)
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;
總結(jié)
到此這篇關(guān)于MySQL數(shù)據(jù)庫查詢之多表查詢的文章就介紹到這了,更多相關(guān)MySQL多表查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
本文通過一個實例給大家用MySQL和mongodb分別寫一個查詢，本文圖片并茂給大家介紹的非常詳細(xì)，感興趣的朋友參考下吧2016-10-10
這篇文章主要為大家詳細(xì)介紹了MySQL5.7.17安裝及配置圖文教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-10-10
阿里云服務(wù)器手動實現(xiàn)mysql雙機熱備的兩種方式
阿里云服務(wù)器由于不支持keepalive虛擬ip，導(dǎo)致無法通過keepalive來實現(xiàn)mysql的雙機熱備。我們這里要實現(xiàn)阿里云的雙機熱備有兩種方式。感興趣的朋友跟隨小編一起看看吧2019-10-10
這篇文章主要給大家介紹了關(guān)于MySQL中執(zhí)行計劃explain命令的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家學(xué)習(xí)或者使用explain命令具有一定的參考學(xué)習(xí)價值，需要的朋友們下面說來一起學(xué)習(xí)學(xué)習(xí)吧2018-05-05
我們都知道數(shù)據(jù)是無價，如果不對數(shù)據(jù)進(jìn)行備份，相當(dāng)是讓數(shù)據(jù)在裸跑，本文就介紹一下如何給mysql定時自動備份數(shù)據(jù)，感興趣的小伙伴們可以參考一下2021-07-07
Mysql5.7忘記root密碼及mysql5.7修改root密碼的方法
這篇文章主要介紹了Mysql5.7忘記root密碼及mysql5.7修改root密碼的方法的相關(guān)資料,需要的朋友可以參考下2016-01-01
安裝MySQL后，配置文件my.cnf在 /MySQL安裝目錄/share/mysql目錄中，該目錄中還包含多個配置文件可供參考，有my-large.cnf ，my-huge.cnf， my-medium.cnf，my-small.cnf，分別對應(yīng)大中小型數(shù)據(jù)庫應(yīng)用的配置。win環(huán)境下即存在于MySQL安裝目錄中的.ini文件2016-05-05
mysql視圖之確保視圖的一致性（with check option）操作詳解
這篇文章主要介紹了mysql視圖之確保視圖的一致性（with check option）操作,結(jié)合實例形式詳細(xì)分析了視圖的一致性操作原理、實現(xiàn)技巧與操作注意事項,需要的朋友可以參考下2019-12-12
mysql中的find_in_set字符串查找函數(shù)解析
這篇文章主要介紹了mysql中的find_in_set字符串查找函數(shù)，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-08-08
這篇文章主要介紹了mysql的事務(wù)隔離級別詳細(xì)解析,事務(wù)是應(yīng)用程序中一系列嚴(yán)密的操作,所有操作必須成功完成,否則在每個操作中所作的所有更改都會被撤消,需要的朋友可以參考下2023-12-12