目錄

• 一.介紹
• 數(shù)據(jù)準(zhǔn)備
• 交叉連接查詢(xún)
• 內(nèi)連接查詢(xún)
• 外連接
• 子查詢(xún)
• 特點(diǎn)
• 子查詢(xún)關(guān)鍵字
• all關(guān)鍵字
• any關(guān)鍵字和some關(guān)鍵字
• in關(guān)鍵字
• exists關(guān)鍵字
• 自關(guān)聯(lián)查詢(xún)
• 總結(jié)

一.介紹

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

• 交叉連接查詢(xún)[產(chǎn)生笛卡爾積，了解]
• 內(nèi)連接查詢(xún)(使用的關(guān)鍵字inner join -- inner可以省略)
• 外連接查詢(xún)(使用的關(guān)鍵字outer join -louter可以省略)
• 子查詢(xún)
• 表自關(guān)聯(lián)

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

-- 創(chuàng)建部門(mén)表create table if not exists dept3(  deptno varchar(20) primary key ,  -- 部門(mén)號(hào)  name varchar(20) -- 部門(mén)名字);-- 創(chuàng)建員工表create table if not exists emp3(  eid varchar(20) primary key , -- 員工編號(hào)  ename varchar(20), -- 員工名字  age int,  -- 員工年齡  dept_id varchar(20)  -- 員工所屬部門(mén));-- 給dept3表添加數(shù)據(jù)insert into dept3 values("1001","研發(fā)部");insert into dept3 values("1002","銷(xiāo)售部");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");

交叉連接查詢(xún)

• 交叉連接查詢(xún)返回被連接的兩個(gè)表所有數(shù)據(jù)行的笛卡爾積
• 笛卡爾積可以理解為一張表的每一行去和另外一張表的任意一行進(jìn)行匹配
• 假如A表有m行數(shù)據(jù),B表有n行數(shù)據(jù),則返回m*n行數(shù)據(jù)
• 笛卡爾積會(huì)產(chǎn)生很多冗余的數(shù)據(jù)，后期的其他查詢(xún)可以在該集合的基礎(chǔ)上進(jìn)行條件篩選

格式

實(shí)現(xiàn)

結(jié)果

內(nèi)連接查詢(xún)

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

格式

操作

inner可以省略

操作

-- 查詢(xún)研發(fā)部門(mén)的所屬員工-- 隱式內(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ā)部"; -- 查詢(xún)研發(fā)部和銷(xiāo)售部的所屬員工select * from dept3 a join emp3 b on a.deptno = b.dept_id and (name = "研發(fā)部" or name = "銷(xiāo)售部") ; select * from dept3 a join emp3 b on a.deptno = b.dept_id and name in("研發(fā)部" ,"銷(xiāo)售部") ;  -- 查詢(xún)每個(gè)部門(mén)的員工數(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;  -- 查詢(xún)?nèi)藬?shù)大于等于3的部門(mén)，并按照人數(shù)降序排序 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)，滿(mǎn)外連接(full outer join)。注意: oracle里面有full join,可是在mysql對(duì)full join支持的不好。我們可以使用unjion來(lái)達(dá)到目的。

格式

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

操作

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

-- 外連接查詢(xún)-- 查詢(xún)哪些部門(mén)有員工，哪些部門(mén)沒(méi)有員工select * from dept3 left outer join emp3 on dept3.deptno = emp3.dept_id;-- 查詢(xún)員工有對(duì)應(yīng)的部門(mén)，哪些沒(méi)有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_idunionselect * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id;

子查詢(xún)

介紹

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

特點(diǎn)

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

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

操作

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

子查詢(xún)關(guān)鍵字

在子查詢(xún)中，有一些常用的邏輯關(guān)鍵字，這些關(guān)鍵字可以給我們提供更豐富的查詢(xú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:與子查詢(xún)返回的所有值比較為true則返回true
• ALL可以與=、>、>=、<、<=、<>結(jié)合是來(lái)使用，分別表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的所有數(shù)據(jù)。
• ALL表示指定列中的值必須要大于子查詢(xún)集的每一個(gè)值，即必須要大于子查詢(xún)集的最大值;如果是小于號(hào)即小于子查詢(xún)集的最小值。同理可以推出其它的比較運(yùn)算符的情況。

操作

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

格式

特點(diǎn)

• ANY:與子查詢(xún)返回的任何值比較為true則返回true
• ANY可以與=、>、>=、<、<=、<>結(jié)合是來(lái)使用，分別表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的任何一個(gè)數(shù)據(jù)。
• 表示制定列中的值要大于子查詢(xún)中的任意一個(gè)值，即必須要大于子查詢(xún)集中的最小值。同理可以推出其它的比較運(yùn)算符的情況。
• SOME和ANY的作用一樣，SOME可以理解為ANY的別名

操作

in關(guān)鍵字

格式

特點(diǎn)

• IN關(guān)鍵字，用于判斷某個(gè)記錄的值,是否在指定的集合中
• 在IN關(guān)鍵字前邊加上not可以將條件反過(guò)來(lái)

操作

exists關(guān)鍵字

格式

特點(diǎn)

• 該子查詢(xún)?nèi)绻?ldquo;有數(shù)據(jù)結(jié)果”(至少返回一行數(shù)據(jù))，則該EXISTS()的結(jié)果為“true"”，外層查詢(xún)執(zhí)行
• 該子查詢(xún)?nèi)绻?ldquo;沒(méi)有數(shù)據(jù)結(jié)果”(沒(méi)有任何數(shù)據(jù)返回），則該EXISTS()的結(jié)果為“false"，外層查詢(xún)不執(zhí)行
• EXISTS后面的子查詢(xún)不返回任何實(shí)際數(shù)據(jù)，只返回真或假，當(dāng)返回真時(shí) where條件成立
• 注意，EXISTS關(guān)鍵字，比IN關(guān)鍵字的運(yùn)算效率高，因此，在實(shí)際開(kāi)發(fā)中，特別是大數(shù)據(jù)量時(shí)，推薦使用EXISTS關(guān)鍵字

操作

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

概念

MySQL有時(shí)在信息查詢(xún)時(shí)需要進(jìn)行對(duì)表自身進(jìn)行關(guān)聯(lián)查詢(xún)，即一張表自己和自己關(guān)聯(lián)，一張表當(dāng)成多張表來(lái)用。注意自關(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)查詢(xún)-- 1.查詢(xún)每個(gè)三國(guó)人物及他的上級(jí)信息，如:關(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.查詢(xún)所有人物及上級(jí)(劉協(xié)（沒(méi)有上級(jí)）也輸出)select a.ename,b.ename from t_sanguo a left join t_sanguo b on a.manager_id = b.eid;-- 3.查詢(xún)所有人物、上級(jí)，上上級(jí)比如:張飛劉備劉協(xié)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;

總結(jié)

到此這篇關(guān)于MySQL中的多表聯(lián)合查詢(xún)的文章就介紹到這了,更多相關(guān)mysql多表聯(lián)合查詢(xún)內(nèi)容請(qǐng)搜索以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持！