MySQL中的多表聯(lián)合查詢功能怎么使用

本篇內(nèi)容介紹了“MySQL中的多表聯(lián)合查詢功能怎么使用”的有關(guān)知識，在實(shí)際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

一.介紹

多表查詢就是同時(shí)查詢兩個(gè)或兩個(gè)以上的表，因?yàn)橛械臅r(shí)候用戶在查看數(shù)據(jù)的時(shí)候,需要顯示的數(shù)據(jù)來自多張表.多表查詢有以下分類:

• 交叉連接查詢[產(chǎn)生笛卡爾積，了解]

• 內(nèi)連接查詢(使用的關(guān)鍵字inner join -- inner可以省略)

• 外連接查詢(使用的關(guān)鍵字outer join -louter可以省略)

• 子查詢

• 表自關(guān)聯(lián)

數(shù)據(jù)準(zhǔn)備

-- 創(chuàng)建部門表
create table if not exists dept3(
  deptno varchar(20) primary key ,  -- 部門號
  name varchar(20) -- 部門名字
);

-- 創(chuàng)建員工表
create table if not exists emp3(
  eid varchar(20) primary key , -- 員工編號
  ename varchar(20), -- 員工名字
  age int,  -- 員工年齡
  dept_id varchar(20)  -- 員工所屬部門
);

-- 給dept3表添加數(shù)據(jù)
insert into dept3 values('1001','研發(fā)部');
insert into dept3 values('1002','銷售部');
insert into dept3 values('1003','財(cái)務(wù)部');
insert into dept3 values('1004','人事部');

-- 給emp3表添加數(shù)據(jù)
insert into emp3 values('1','喬峰',20, '1001');
insert into emp3 values('2','段譽(yù)',21, '1001');
insert into emp3 values('3','虛竹',23, '1001');
insert into emp3 values('4','阿紫',18, '1001');
insert into emp3 values('5','掃地僧',85, '1002');
insert into emp3 values('6','李秋水',33, '1002');
insert into emp3 values('7','鳩摩智',50, '1002'); 
insert into emp3 values('8','天山童姥',60, '1003');
insert into emp3 values('9','慕容博',58, '1003');
insert into emp3 values('10','丁春秋',71, '1005');

交叉連接查詢

• 交叉連接查詢返回被連接的兩個(gè)表所有數(shù)據(jù)行的笛卡爾積

• 笛卡爾積可以理解為一張表的每一行去和另外一張表的任意一行進(jìn)行匹配

• 假如A表有m行數(shù)據(jù),B表有n行數(shù)據(jù),則返回m*n行數(shù)據(jù)

• 笛卡爾積會產(chǎn)生很多冗余的數(shù)據(jù)，后期的其他查詢可以在該集合的基礎(chǔ)上進(jìn)行條件篩選

格式

實(shí)現(xiàn)

結(jié)果

 內(nèi)連接查詢

內(nèi)連接查詢求多張表的交集

 格式

操作

 inner可以省略

操作

-- 查詢研發(fā)部門的所屬員工
-- 隱式內(nèi)連接
select * from emp3 e ,dept3 d where e.dept_id =d.deptno and name ='研發(fā)部';
-- 顯式內(nèi)連接
select * from dept3 d join emp3 e on d.deptno =e.dept_id and name ='研發(fā)部'; 
-- 查詢研發(fā)部和銷售部的所屬員工
select * from dept3 a join emp3 b on a.deptno = b.dept_id and (name = '研發(fā)部' or name = '銷售部') ; 
select * from dept3 a join emp3 b on a.deptno = b.dept_id and name in('研發(fā)部' ,'銷售部') ; 
 
-- 查詢每個(gè)部門的員工數(shù),并升序排序
 
select 
	a.name,a.deptno,count(1) 
from dept3 a 
   join emp3 b on a.deptno = b.dept_id 
group by 
  a.deptno,name;
 
 
-- 查詢?nèi)藬?shù)大于等于3的部門，并按照人數(shù)降序排序
 
select
  a.deptno,
  a.name,
  count(1) as total_cnt
from dept3 a
	join emp3 b on a.deptno = b.dept_id
group by 
  a.deptno,a.name
having 
  total_cnt >= 3
order by 
  total_cnt desc;

外連接

外連接分為左外連接（left outer join)、右外連接(right outer join)，滿外連接(full outer join)。注意: oracle里面有full join,可是在mysql對full join支持的不好。我們可以使用unjion來達(dá)到目的。

 格式

左外連接: left outer join
        select* from A left outer join B on條件;
右外連接: right outer join
        select* from A right outer join B on條件;
滿外連接: full outer join
        select * from A full outer join B on 條件;

 操作

 直接用fulljion會報(bào)錯(cuò)

-- 外連接查詢
-- 查詢哪些部門有員工，哪些部門沒有員工
select * from dept3 left outer join emp3 on dept3.deptno = emp3.dept_id;
-- 查詢員工有對應(yīng)的部門，哪些沒有
select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id;
-- 使用union關(guān)鍵字實(shí)現(xiàn)左外連接和右外連接的并集
select * from dept3 left outer join emp3 on dept3. deptno = emp3.dept_id
union
select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id;

子查詢

介紹

子查詢就是指的在一個(gè)完整的查詢語句之中，嵌套若干個(gè)不同功能的小查詢，從而一起完成復(fù)雜查詢的一種編寫形式，通俗一點(diǎn)就是包含select嵌套的查詢。

特點(diǎn)

子查詢可以返回的數(shù)據(jù)類型一共分為四種:

單行單列:返回的是一個(gè)具體列的內(nèi)容，可以理解為一個(gè)單值數(shù)據(jù);單行多列:返回一行數(shù)據(jù)中多個(gè)列的內(nèi)容;多行單列:返回多行記錄之中同一列的內(nèi)容，相當(dāng)于給出了一個(gè)操作范圍;多行多列:查詢返回的結(jié)果是一張臨時(shí)表

操作

-- 查詢年齡最大的員工信息，顯示信息包含員工號、員工名字，員工年齡
select * from emp3 where age=(select max(age) from emp3 );
 
select eid,ename ,age from emp3 where age = (select max (age) from emp3);
-- 查詢年研發(fā)部和銷售部的員工信息,包含員工號、員工名字
select  * from emp3 where emp3.dept_id in (select deptno from dept3 d where d.name='研發(fā)部' or d.name='銷售部');
 
select eid,ename , t.name from emp3 where dept_id in (select deptno, name from dept3where name ='研發(fā)部'or name ='銷售部');
-- 查詢研發(fā)部20歲以下的員工信息，包括員工號、員工名字，部門名字
 
-- 方式一：關(guān)聯(lián)查詢
select * from dept3 d join emp3 e on d.deptno =e.dept_id and (d.name='研發(fā)部' and e.age<20); 
-- 方式二：子查詢
select eid,age ,ename , name from (select * from dept3 where name = '研發(fā)部')t1 , (select * from emp3 where age <20) t2 where  t1.deptno =t2.dept_id ;
 
select eid,age ,ename , name from (select * from dept3 where name = '研發(fā)部')t1 join  (select * from emp3 where age <20) t2 on t1.deptno =t2.dept_id ;

子查詢關(guān)鍵字

在子查詢中，有一些常用的邏輯關(guān)鍵字，這些關(guān)鍵字可以給我們提供更豐富的查詢功能，主要關(guān)鍵字如下:

1.ALL關(guān)鍵字
2.ANY關(guān)鍵字
3.SOME關(guān)鍵字
4.IN關(guān)鍵字
5.EXISTS關(guān)鍵字

all關(guān)鍵字

格式

特點(diǎn)

• ALL:與子查詢返回的所有值比較為true則返回true

• ALL可以與=、>、>=、<、<=、<>結(jié)合是來使用，分別表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的所有數(shù)據(jù)。

• ALL表示指定列中的值必須要大于子查詢集的每一個(gè)值，即必須要大于子查詢集的最大值;如果是小于號即小于子查詢集的最小值。同理可以推出其它的比較運(yùn)算符的情況。

操作

any關(guān)鍵字和some關(guān)鍵字

格式

特點(diǎn)

• ANY:與子查詢返回的任何值比較為true則返回true

• ANY可以與=、>、>=、<、<=、<>結(jié)合是來使用，分別表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的任何一個(gè)數(shù)據(jù)。

• 表示制定列中的值要大于子查詢中的任意一個(gè)值，即必須要大于子查詢集中的最小值。同理可以推出其它的比較運(yùn)算符的情況。

• SOME和ANY的作用一樣，SOME可以理解為ANY的別名

操作

in關(guān)鍵字

格式

 特點(diǎn)

• IN關(guān)鍵字，用于判斷某個(gè)記錄的值,是否在指定的集合中

• 在IN關(guān)鍵字前邊加上not可以將條件反過來

操作

exists關(guān)鍵字

格式

特點(diǎn)

• 該子查詢?nèi)绻坝袛?shù)據(jù)結(jié)果”(至少返回一行數(shù)據(jù))，則該EXISTS()的結(jié)果為“true"”，外層查詢執(zhí)行

• 該子查詢?nèi)绻皼]有數(shù)據(jù)結(jié)果”(沒有任何數(shù)據(jù)返回），則該EXISTS()的結(jié)果為“false"，外層查詢不執(zhí)行

• EXISTS后面的子查詢不返回任何實(shí)際數(shù)據(jù)，只返回真或假，當(dāng)返回真時(shí) where條件成立

• 注意，EXISTS關(guān)鍵字，比IN關(guān)鍵字的運(yùn)算效率高，因此，在實(shí)際開發(fā)中，特別是大數(shù)據(jù)量時(shí)，推薦使用EXISTS關(guān)鍵字

操作

 自關(guān)聯(lián)查詢

概念

MySQL有時(shí)在信息查詢時(shí)需要進(jìn)行對表自身進(jìn)行關(guān)聯(lián)查詢，即一張表自己和自己關(guān)聯(lián)，一張表當(dāng)成多張表來用。注意自關(guān)聯(lián)時(shí)表必須給表起別名。

格式

操作

數(shù)據(jù)準(zhǔn)備

-- 創(chuàng)建表，并建立自關(guān)聯(lián)約束
create table t_sanguo (
    eid int primary key ,
    ename varchar (20) ,
    manager_id int,
    foreign key (manager_id) references t_sanguo (eid) -- 添加自關(guān)聯(lián)約束
);

--添加數(shù)據(jù)
insert into t_sanguo values (1,'劉協(xié)',NULL) ,
                             (2,'劉備',1),
                             (3,'關(guān)羽',2),
                            (4,'張飛',2),
                             (5,'曹操',1),
                            (6,'許褚',5),
                             (7,'典韋',5),
                             (8,'孫權(quán)',1) ,
                             (9,'周瑜',8),
                            (10,'魯肅',8) ;

-- 進(jìn)行關(guān)聯(lián)查詢
-- 1.查詢每個(gè)三國人物及他的上級信息，如:關(guān)羽劉備
select * from t_sanguo ts ,t_sanguo ts2 where ts .manager_id  =ts2.eid  ;						
select ts.ename ,ts2.ename  from t_sanguo ts ,t_sanguo ts2 where ts .manager_id  =ts2.eid  ;						
-- 2.查詢所有人物及上級(劉協(xié)（沒有上級）也輸出)
select a.ename,b.ename from t_sanguo a left join t_sanguo b on a.manager_id = b.eid;
-- 3.查詢所有人物、上級，上上級比如:張飛劉備劉協(xié)
select
	a.ename,b.ename,c.ename
from t_sanguo a
left join t_sanguo b on a.manager_id = b.eid
left join t_sanguo c on b.manager_id = c.eid;

“MySQL中的多表聯(lián)合查詢功能怎么使用”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識可以關(guān)注億速云網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！