目錄

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

一.介紹

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

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

數(shù)據(jù)準備

-- 創(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","財務部");insert into dept3 values("1004","人事部");-- 給emp3表添加數(shù)據(jù)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");

交叉連接查詢

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

格式

實現(xiàn)

結果

內(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ā)部" ,"銷售部") ;  -- 查詢每個部門的員工數(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_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關鍵字實現(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;

子查詢

介紹

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

特點

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

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

操作

-- 查詢年齡最大的員工信息，顯示信息包含員工號、員工名字，員工年齡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歲以下的員工信息，包括員工號、員工名字，部門名字 -- 方式一：關聯(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 ;

子查詢關鍵字

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

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

all關鍵字

格式

特點

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

操作

any關鍵字和some關鍵字

格式

特點

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

操作

in關鍵字

格式

特點

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

操作

exists關鍵字

格式

特點

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

操作

自關聯(lián)查詢

概念

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

格式

操作

數(shù)據(jù)準備

-- 創(chuàng)建表，并建立自關聯(lián)約束create table t_sanguo (    eid int primary key ,    ename varchar (20) ,    manager_id int,    foreign key (manager_id) references t_sanguo (eid) -- 添加自關聯(lián)約束);--添加數(shù)據(jù)insert into t_sanguo values (1,"劉協(xié)",NULL) ,     (2,"劉備",1),     (3,"關羽",2),    (4,"張飛",2),     (5,"曹操",1),    (6,"許褚",5),     (7,"典韋",5),     (8,"孫權",1) ,     (9,"周瑜",8),    (10,"魯肅",8) ;

-- 進行關聯(lián)查詢-- 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.查詢所有人物及上級(劉協(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.enamefrom t_sanguo aleft join t_sanguo b on a.manager_id = b.eidleft join t_sanguo c on b.manager_id = c.eid;

總結

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