MySQL 進階查詢（内連接配接、外連接配接、自然連接配接、子查詢）

• 1、關聯查詢,連接配接查詢,内連接配接
• • 1.1、where 子句
  • 1.2、inner join…on…
  • 1.3、inner join…using…
• 2、自然連接配接
• 3、外連接配接
• • 3.1、左外連接配接:left [outer] join…on…
  • 3.2、右外連接配接:right [outer] join…on…
• 4、子查詢
• 5、集合查詢

Test Table emp and dept:

連結：https://pan.baidu.com/s/12p039KI59UDAJ9llXAIk7Q

提取碼：58ry

dept table：

字段名	釋義
deptno	部門編号
dname	部門名
loc	位址

emp table：

字段名	釋義
empno	職工編号
ename	職工姓名
job	工作崗位
mgr	與職工編号的上下級關系
hiredate	聘用日期
sal	工資
comm	傭金
deptno	部門編号

1、關聯查詢,連接配接查詢,内連接配接

笛卡爾積：

emp 表 15 條記錄,dept 表 4 條記錄。

連接配接查詢的笛卡爾積為 60 條記錄。

内連接配接:

不區分主從表，與連接配接順序無關。兩張表均滿足條件則出現結果集中。

1.1、where 子句

select * from emp,dept
where emp.deptno = dept.deptno;
           

1.2、inner join…on…

select * from emp
inner join dept
on emp.deptno = dept.deptno;
           

1.3、inner join…using…

select * from emp
INNER JOIN dept
using(deptno);
           

2、自然連接配接

尋找兩表中字段名稱相等的字段進行連接配接，會自動去重重複列。

3、外連接配接

有主從表之分，與連接配接順序有關。以驅動表為依據，比對表依次進行查詢；比對表中找不到資料，則以null填充。

3.1、左外連接配接:left [outer] join…on…

select * from emp 
left join dept
on emp.deptno = dept.deptno;
           

3.2、右外連接配接:right [outer] join…on…

select * from dept 
right join emp
on emp.deptno = dept.deptno;
           

4、子查詢

即嵌套查詢，将一個查詢結果作為另一個查詢條件或組成部分的查詢。

（1）單行子查詢：

# 查詢工資大于7788号員工的所有員工資訊
select * from emp where sal >(select sal from emp where empno = 7788);
           

（2）多行子查詢：

傳回多值可以使用 any 或 all 來修飾。

=any 相當于 in, <any 小于最大值，>any 大于最小值；

<>all 相當于 not in, >all 大于最大值，<all 小于最小值。

in 和 exists 的差別：

in 先執行子查詢，在執行主查詢; exists 先執行主查詢;

exists 子查詢不傳回具體結果，傳回 true 值出現在結果集，否則不出現。

# 查詢超過所在部門平均工資的員工資訊
select * from emp e1 
where sal > (
select avg(sal) from emp e2 
where e1.deptno = e2.deptno);

# 查詢薪水大于2000的部門名稱
select dname from dept d where deptno in
(select deptno from emp e where sal > 2000);
# 或
select dname from dept d where EXISTS 
(select * from emp e where sal > 2000 and d.deptno=e.deptno);

           

5、集合查詢

UNION：

并集，所有的内容都查詢，重複的顯示一次。

UNION ALL：

并集，所有的内容都顯示，包括重複的。

select * from emp where deptno = 20
UNION ALL
select * from emp where sal <=2000;