文章詳情頁

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

瀏覽：343日期：2023-02-18 16:43:17

一.介紹

多表查詢就是同時查詢兩個或兩個以上的表，因為有的時候用戶在查看數據的時候,需要顯示的數據來自多張表.多表查詢有以下分類:

交叉連接查詢[產生笛卡爾積，了解]
內連接查詢(使用的關鍵字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_cntfrom dept3 a	join emp3 b on a.deptno = b.dept_idgroup by   a.deptno,a.namehaving   total_cnt >= 3order 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_idunionselect * 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.enamefrom t_sanguo aleft join t_sanguo b on a.manager_id = b.eidleft join t_sanguo c on b.manager_id = c.eid;