MySQL---多表聯(lián)合查詢（下）（內(nèi)連接查詢、外連接查詢、子查詢（ALL/ANY/SOME/IN/EXISTS關(guān)鍵字）、自關(guān)聯(lián)查詢）

這篇具有很好參考價值的文章主要介紹了MySQL---多表聯(lián)合查詢（下）（內(nèi)連接查詢、外連接查詢、子查詢（ALL/ANY/SOME/IN/EXISTS關(guān)鍵字）、自關(guān)聯(lián)查詢）。希望對大家有所幫助。如果存在錯誤或未考慮完全的地方，請大家不吝賜教，您也可以點擊"舉報違法"按鈕提交疑問。

1. 內(nèi)連接查詢

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

use mydb3;

-- 創(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','人事部');

-- 給emp表添加數(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');

內(nèi)連接查詢語法：

-- 語法：
-- 隱式內(nèi)連接（SQL92標準）：select * from A,B where 條件; 
-- 顯示內(nèi)連接（SQL99標準）：select * from A inner join B on 條件;

-- 查詢每個部門的所屬員工
select * from dept3,emp3 where dept3.deptno = emp3.dept_id;
select * from dept3 inner join emp3 on dept3.deptno = emp3.dept_id;

-- 查詢研發(fā)部和銷售部的所屬員工
select * from dept3,emp3 where dept3.deptno = emp3.dept_id and name in( '研發(fā)部','銷售部');
select * from dept3 join emp3 on dept3.deptno = emp3.dept_id and name in( '研發(fā)部','銷售部');
?
-- 查詢每個部門的員工數(shù),并升序排序
select deptno,count(1) as total_cnt from dept3,emp3 where dept3.deptno = emp3.dept_id group by deptno order by total_cnt;
?
select deptno,count(1) as total_cnt from dept3 join emp3 on dept3.deptno = emp3.dept_id group by deptno order by total_cnt;

-- 查詢研發(fā)部和銷售部的所屬員工
select * from dept3,emp3 where dept3.deptno = emp3.dept_id and name in( '研發(fā)部','銷售部');
select * from dept3 join emp3 on dept3.deptno = emp3.dept_id and name in( '研發(fā)部','銷售部');
?
-- 查詢每個部門的員工數(shù),并升序排序
select deptno,count(1) as total_cnt from dept3,emp3 where dept3.deptno = emp3.dept_id group by deptno order by total_cnt;
?
select deptno,count(1) as total_cnt from dept3 join emp3 on dept3.deptno = emp3.dept_id group by deptno order by total_cnt;

-- 查詢?nèi)藬?shù)大于等于3的部門，并按照人數(shù)降序排序
select deptno,count(1) as total_cnt from dept3,emp3 where dept3.deptno = emp3.dept_id group by deptno having total_cnt >= 3 order by total_cnt desc;
?
select deptno,count(1) as total_cnt from dept3 join emp3 on dept3.deptno = emp3.dept_id group by deptno having total_cnt >= 3 order by total_cnt desc;

2. 外連接查詢

語法：?左外連接：left outer join：

??????????? select * from A left outer join B on 條件;

MySQL---多表聯(lián)合查詢（下）（內(nèi)連接查詢、外連接查詢、子查詢（ALL/ANY/SOME/IN/EXISTS關(guān)鍵字）、自關(guān)聯(lián)查詢）

? ? ? ? ? ??右外連接：right outer join：

??????????? select * from A right outer join B on 條件;

MySQL---多表聯(lián)合查詢（下）（內(nèi)連接查詢、外連接查詢、子查詢（ALL/ANY/SOME/IN/EXISTS關(guān)鍵字）、自關(guān)聯(lián)查詢）

? ? ? ? ? ? 滿外連接: full outer join：

????????????select * from A full outer join B on 條件;

MySQL---多表聯(lián)合查詢（下）（內(nèi)連接查詢、外連接查詢、子查詢（ALL/ANY/SOME/IN/EXISTS關(guān)鍵字）、自關(guān)聯(lián)查詢）

-- 外連接查詢
-- 查詢哪些部門有員工，哪些部門沒有員工
use mydb3;
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關(guān)鍵字實現(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;

3. 子查詢

子查詢就是指的在一個完整的查詢語句之中，嵌套若干個不同功能的小查詢。通俗一點就是包含select嵌套的查詢。

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

單行單列：返回的是一個具體列的內(nèi)容，可以理解為一個單值數(shù)據(jù)；

單行多列：返回一行數(shù)據(jù)中多個列的內(nèi)容；

多行單列：返回多行記錄之中同一列的內(nèi)容，相當于給出了一個操作范圍；

多行多列：查詢返回的結(jié)果是一張臨時表。

-- 查詢年齡最大的員工信息，顯示信息包含員工號、員工名字，員工年齡
select eid,ename,age from emp3 where age = (select max(age) from emp3);
?
?
-- 查詢年研發(fā)部和銷售部的員工信息，包含員工號、員工名字
select eid,ename,t.name from emp3 where dept_id in (select deptno,name from dept3 where name = '研發(fā)部' or name = '銷售部') ;
?
?
-- 查詢研發(fā)部20歲以下的員工信息,包括員工號、員工名字，部門名字
select eid,age,ename,name from (select * from dept where name = '研發(fā)部 ')t1,(select * from emp3 where age <20)t2

ALL關(guān)鍵字：

與子查詢返回的所有值比較為true 則返回true。

ALL可以與=、>、>=、<、<=、<>結(jié)合是來使用，分別表示等于、大于、大于等于、小于、小于

等于、不等于其中的所有數(shù)據(jù)。

ALL表示指定列中的值必須要大于子查詢集的每一個值，即必須要大于子查詢集的最大值；如果是

小于號即小于子查詢集的最小值。同理可以推出其它的比較運算符的情況。

-- 語法：
-- select …from …where c > all(查詢語句)
-- 等價于：
-- select ...from ... where c > result1 and?c > result2 and?c > result3

-- 查詢年齡大于‘1003’部門所有年齡的員工信息
select * from emp3 where age > all(select age from emp3 where dept_id = '1003’);

-- 查詢不屬于任何一個部門的員工信息 
select * from emp3 where dept_id != all(select deptno from dept3);

ANY與SOME關(guān)鍵字：

與子查詢返回的任何值比較為true，則返回true。

ANY可以與=、>、>=、<、<=、<>結(jié)合是來使用，分別表示等于、大于、大于等于、小于、小于

等于、不等于其中的任何一個數(shù)據(jù)。

表示指定列中的值要大于子查詢中的任意一個值，即必須要大于子查詢集中的最小值。同理可以推

出其它的比較運算符的情況。

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

-- 語法：
-- select …from …where c > any(查詢語句)
-- 等價于：
-- select ...from ... where c > result1 or?c > result2 or?c > result3

-- 查詢年齡大于‘1003’部門任意一個員工年齡的員工信息
select * from emp3 where age > all(select age from emp3 where dept_id = '1003’);

IN關(guān)鍵字：

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

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

-- 語法：
-- select …from …where c in(查詢語句)
-- 等價于：
-- select ...from ... where c = result1 or?c = result2 or?c = result3

-- 查詢研發(fā)部和銷售部的員工信息，包含員工號、員工名字
select eid,ename,t.name from emp3 where dept_id in (select deptno from dept3 where name = '研發(fā)部' or name = '銷售部') ;

EXISTS關(guā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ù)據(jù)，只返回真或假，當返回真時 where條件成立。

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

薦使用EXISTS關(guān)鍵字。

-- 語法:
-- select …from …where exists(查詢語句)

-- 查詢公司是否有大于60歲的員工，有則輸出
select * from emp3 a where exists(select * from emp3 b where a.age > 60);
?
-- 查詢有所屬部門的員工信息
select * from emp3 a where exists(select * from dept3 b where a.dept_id = b.deptno);

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

MySQL有時在信息查詢時需要進行對表自身進行關(guān)聯(lián)查詢，即一張表自己和自己關(guān)聯(lián)，一張表當

成多張表來用。

注意自關(guān)聯(lián)時表必須給表起別名。

-- 語法：
-- select 字段列表 from 表1 a , 表1 b where 條件;
-- 或者 
-- select 字段列表 from 表1 a [left] join 表1 b on 條件;

-- 創(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);
insert into t_sanguo values(2,'劉備',1);
insert into t_sanguo values(3,'關(guān)羽',2);
insert into t_sanguo values(4,'張飛',2);
insert into t_sanguo values(5,'曹操',1);
insert into t_sanguo values(6,'許褚',5);
insert into t_sanguo values(7,'典韋',5);
insert into t_sanguo values(8,'孫權(quán)',1);
insert into t_sanguo values(9,'周瑜',8);
insert into t_sanguo values(10,'魯肅',8);
?
-- 進行關(guān)聯(lián)查詢
-- 1.查詢每個三國人物及他的上級信息，如:  關(guān)羽  劉備 
select * from t_sanguo a, t_sanguo b where a.manager_id = b.eid;

（日常美圖時間）

MySQL---多表聯(lián)合查詢（下）（內(nèi)連接查詢、外連接查詢、子查詢（ALL/ANY/SOME/IN/EXISTS關(guān)鍵字）、自關(guān)聯(lián)查詢）

?文章來源地址http://www.zghlxwxcb.cn/news/detail-444905.html

到了這里，關(guān)于MySQL---多表聯(lián)合查詢（下）（內(nèi)連接查詢、外連接查詢、子查詢（ALL/ANY/SOME/IN/EXISTS關(guān)鍵字）、自關(guān)聯(lián)查詢）的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！