MySQL中的多表聯合查詢功能操作

2023-02-03 18:00:47

一.介紹

多表查詢就是同時查詢兩個或兩個以上的表，因為有的時候使用者在檢視資料的時候,需要顯示的資料來自多張表.多表查詢有以下分類:

交叉連線查詢[產生笛卡爾積，瞭解]
內連線查詢(使用的關鍵字inner join -- inner可以省略)
外連線查詢(使用的關鍵字outer join -louter可以省略)
子查詢
表自關聯

資料準備

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

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

-- 給dept3表新增資料
insert into dept3 values('1001','研發部');
insert into dept3 values('1002','銷售部');
insert into dept3 values('1003','財務部');
insert into dept3 values('1004','人事部');

-- 給emp3表新增資料
insert into emp3 values('1','喬峰',20, '1001');
insert into emp3 values('2','段譽',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');

交叉連線查詢

交叉連線查詢返回被連線的兩個表所有資料行的笛卡爾積
笛卡爾積可以理解為一張表的每一行去和另外一張表的任意一行進行匹配
假如A表有m行資料,B表有n行資料,則返回m*n行資料
笛卡爾積會產生很多冗餘的資料，後期的其他查詢可以在該集合的基礎上進行條件篩選

格式

實現

結果

內連線查詢

內連線查詢求多張表的交集

格式

操作

inner可以省略

操作

-- 查詢研發部門的所屬員工
-- 隱式內連線
select * from emp3 e ,dept3 d where e.dept_id =d.deptno and name ='研發部';
-- 顯式內連線
select * from dept3 d join emp3 e on d.deptno =e.dept_id and name ='研發部'; 
-- 查詢研發部和銷售部的所屬員工
select * from dept3 a join emp3 b on a.deptno = b.dept_id and (name = '研發部' or name = '銷售部') ; 
select * from dept3 a join emp3 b on a.deptno = b.dept_id and name in('研發部' ,'銷售部') ; 
 
-- 查詢每個部門的員工數,並升序排序
 
select 
	a.name,a.deptno,count(1) 
from dept3 a 
   join emp3 b on a.deptno = b.dept_id 
group by 
  a.deptno,name;
 
 
-- 查詢人數大於等於3的部門，並按照人數降序排序
 
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來達到目的。

格式

左外連線: 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會報錯

-- 外連線查詢
-- 查詢哪些部門有員工，哪些部門沒有員工
select * from dept3 left outer join emp3 on dept3.deptno = emp3.dept_id;
-- 查詢員工有對應的部門，哪些沒有
select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id;
-- 使用union關鍵字實現左外連線和右外連線的並集
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;

子查詢

介紹

子查詢就是指的在一個完整的查詢語句之中，巢狀若干個不同功能的小查詢，從而一起完成複雜查詢的一種編寫形式，通俗一點就是包含select巢狀的查詢。

特點

子查詢可以返回的資料型別一共分為四種:

單行單列:返回的是一個具體列的內容，可以理解為一個單值資料;單行多列:返回一行資料中多個列的內容;多行單列:返回多行記錄之中同一列的內容，相當於給出了一個操作範圍;多行多列:查詢返回的結果是一張臨時表

操作

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

子查詢關鍵字

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

1.ALL關鍵字
2.ANY關鍵字
3.SOME關鍵字
4.IN關鍵字
5.EXISTS關鍵字

all關鍵字

格式

特點

ALL:與子查詢返回的所有值比較為true則返回true
ALL可以與=、>、>=、<、<=、<>結合是來使用，分別表示等於、大於、大於等於、小於、小於等於、不等於其中的其中的所有資料。
ALL表示指定列中的值必須要大於子查詢集的每一個值，即必須要大於子查詢集的最大值;如果是小於號即小於子查詢集的最小值。同理可以推出其它的比較運運算元的情況。

操作

any關鍵字和some關鍵字

格式

特點

ANY:與子查詢返回的任何值比較為true則返回true
ANY可以與=、>、>=、<、<=、<>結合是來使用，分別表示等於、大於、大於等於、小於、小於等於、不等於其中的其中的任何一個資料。
表示制定列中的值要大於子查詢中的任意一個值，即必須要大於子查詢集中的最小值。同理可以推出其它的比較運運算元的情況。
SOME和ANY的作用一樣，SOME可以理解為ANY的別名

操作

in關鍵字

格式

特點

IN關鍵字，用於判斷某個記錄的值,是否在指定的集合中
在IN關鍵字前邊加上not可以將條件反過來

操作

exists關鍵字

格式

特點

該子查詢如果“有資料結果”(至少返回一行資料)，則該EXISTS()的結果為“true"”，外層查詢執行
該子查詢如果“沒有資料結果”(沒有任何資料返回），則該EXISTS()的結果為“false"，外層查詢不執行
EXISTS後面的子查詢不返回任何實際資料，只返回真或假，當返回真時 where條件成立
注意，EXISTS關鍵字，比IN關鍵字的運算效率高，因此，在實際開發中，特別是巨量資料量時，推薦使用EXISTS關鍵字

操作

自關聯查詢

概念

MySQL有時在資訊查詢時需要進行對錶自身進行關聯查詢，即一張表自己和自己關聯，一張表當成多張表來用。注意自關聯時表必須給表起別名。

格式

操作

資料準備

-- 建立表，並建立自關聯約束
create table t_sanguo (
    eid int primary key ,
    ename varchar (20) ,
    manager_id int,
    foreign key (manager_id) references t_sanguo (eid) -- 新增自關聯約束
);

--新增資料
insert into t_sanguo values (1,'劉協',NULL) ,
                             (2,'劉備',1),
                             (3,'關羽',2),
                            (4,'張飛',2),
                             (5,'曹操',1),
                            (6,'許褚',5),
                             (7,'典韋',5),
                             (8,'孫權',1) ,
                             (9,'周瑜',8),
                            (10,'魯肅',8) ;

-- 進行關聯查詢
-- 1.查詢每個三國人物及他的上級資訊，如:關羽劉備
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.查詢所有人物及上級(劉協（沒有上級）也輸出)
select a.ename,b.ename from t_sanguo a left join t_sanguo b on a.manager_id = b.eid;
-- 3.查詢所有人物、上級，上上級比如:張飛劉備劉協
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;