MySQL8 常用命令之DQL（多表聯查）-有解無憂

DQL：（Data Query Language）資料查詢語言

一、去除重復記錄

使用distinct關鍵字

select distinct 欄位名 from表名：

注：

distinct 只對查詢結果進行去重，原表資料不會被修改，
distinct 后面可以跟多個欄位，表示多個欄位聯合去重，

mysql> select distinct job,deptno from emp order by deptno;
+-----------+--------+
| job       | deptno |
+-----------+--------+
| CLERK     |     10 |
| MANAGER   |     10 |
| PRESIDENT |     10 |
| ANALYST   |     20 |
| CLERK     |     20 |
| MANAGER   |     20 |
| CLERK     |     30 |
| MANAGER   |     30 |
| SALESMAN  |     30 |
+-----------+--------+
9 rows in set (0.01 sec)

二、連接查詢

1. 笛卡爾積現象

當兩張表進行連接查詢時，如果沒有任何條件的限制會發生笛卡爾積現象，如emp表有14條記錄，dept表有4條記錄，不加任何條件直接在兩表查詢最侄訓顯示56條資料，

mysql> select ename,dname from emp,dept;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | OPERATIONS |
| SMITH  | SALES      |
| SMITH  | RESEARCH   |
| SMITH  | ACCOUNTING |
| ALLEN  | OPERATIONS |
......
| MILLER | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+
56 rows in set (0.00 sec)

可以通過添加條件避免笛卡爾積現象的發生，

mysql> select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
......
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.01 sec)

2. 內連接

select ... from A inner join B on A和B的連接條件 where 篩選條件;

說明：

inner可以省略，帶著inner可讀性更好
內連接的特點：AB兩表之間沒有主次關系，將完全能夠滿足連接條件的資料查詢出來，

2.1 等值連接
on 后面的連接條件是等量關系，

案例：查詢員工的部門名稱，

mysql> select e.ename,d.dname from emp e inner join dept d on e.deptno=d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
......
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.01 sec)

2.2 非等值連接
連接條件不是等量關系，

案例：查詢每個員工的薪資等級，

mysql> select e.ename,e.sal,s.grade from emp e 
	 > join salgrade s on e.sal between s.losal and s.hisal;
+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| SMITH  |  800.00 |     1 |
| ALLEN  | 1600.00 |     3 |
| WARD   | 1250.00 |     2 |
| JONES  | 2975.00 |     4 |
......
| FORD   | 3000.00 |     4 |
| MILLER | 1300.00 |     2 |
+--------+---------+-------+
14 rows in set (0.01 sec)

2.3 自連接
將一張表看做是兩張表，

案例：查詢員工的上級領導并顯示，

mysql> select e.ename,m.ename from 
	 > (select empno,ename from emp) as m 
	 > join emp e on e.mgr=m.empno;
+--------+-------+
| ename  | ename |
+--------+-------+
| SMITH  | FORD  |
| ALLEN  | BLAKE |
| WARD   | BLAKE |
| JONES  | KING  |
| MARTIN | BLAKE |
| BLAKE  | KING  |
| CLARK  | KING  |
| SCOTT  | JONES |
| TURNER | BLAKE |
| ADAMS  | SCOTT |
| JAMES  | BLAKE |
| FORD   | JONES |
| MILLER | CLARK |
+--------+-------+
13 rows in set (0.00 sec)

3. 外連接

兩張表具有主次關系，陳述句中的outer可以省略，
3.1 左外連接

select ... from A left outer join B on 連接條件;

說明：將join關鍵字左邊的表看做是主表，左表中的資料將被全部查詢出來，右表中不滿足條件的資料使用NULL填充，

mysql> select e.ename,d.dname from dept d 
	 > left join emp e on e.deptno=d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| MILLER | ACCOUNTING |
| KING   | ACCOUNTING |
| CLARK  | ACCOUNTING |
| FORD   | RESEARCH   |
| ADAMS  | RESEARCH   |
| SCOTT  | RESEARCH   |
| JONES  | RESEARCH   |
| SMITH  | RESEARCH   |
| JAMES  | SALES      |
| TURNER | SALES      |
| BLAKE  | SALES      |
| MARTIN | SALES      |
| WARD   | SALES      |
| ALLEN  | SALES      |
| NULL   | OPERATIONS |
+--------+------------+
15 rows in set (0.00 sec)

3.2 右外連接

select ... from A right outer join B on 連接條件;

說明：將join關鍵字右邊的表看做是主表，右表中的資料將被全部查詢出來，左表中不滿足條件的資料使用NULL填充，

mysql> select e.ename,d.dname from emp e 
	 > right join dept d on e.deptno=d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| MILLER | ACCOUNTING |
| KING   | ACCOUNTING |
| CLARK  | ACCOUNTING |
| FORD   | RESEARCH   |
| ADAMS  | RESEARCH   |
| SCOTT  | RESEARCH   |
| JONES  | RESEARCH   |
| SMITH  | RESEARCH   |
| JAMES  | SALES      |
| TURNER | SALES      |
| BLAKE  | SALES      |
| MARTIN | SALES      |
| WARD   | SALES      |
| ALLEN  | SALES      |
| NULL   | OPERATIONS |
+--------+------------+
15 rows in set (0.01 sec)

案例：查詢所有員工（包括KING）的上級領導并顯示員工名和領導名，

mysql> select e.ename,m.ename from emp e
    -> left join (select empno,ename from emp) m
    -> on e.mgr=m.empno;
+--------+-------+
| ename  | ename |
+--------+-------+
| SMITH  | FORD  |
| ALLEN  | BLAKE |
| WARD   | BLAKE |
| JONES  | KING  |
| MARTIN | BLAKE |
| BLAKE  | KING  |
| CLARK  | KING  |
| SCOTT  | JONES |
| KING   | NULL  |
| TURNER | BLAKE |
| ADAMS  | SCOTT |
| JAMES  | BLAKE |
| FORD   | JONES |
| MILLER | CLARK |
+--------+-------+
14 rows in set (0.00 sec)

4.三表聯查，四表聯查…

select ... from A join B on AB連接條件 join C on AC連接條件 right join D on AD連接條件...;

案例1：查詢每個員工的部門名稱以及工資等級，要求顯示員工名、部門名、薪資、薪資等級，

mysql> select e.ename,d.dname,e.sal,s.grade from emp e
    -> join dept d on e.deptno=d.deptno
    -> join salgrade s on e.sal between s.losal and s.hisal;
+--------+------------+---------+-------+
| ename  | dname      | sal     | grade |
+--------+------------+---------+-------+
| SMITH  | RESEARCH   |  800.00 |     1 |
| ALLEN  | SALES      | 1600.00 |     3 |
| WARD   | SALES      | 1250.00 |     2 |
| JONES  | RESEARCH   | 2975.00 |     4 |
| MARTIN | SALES      | 1250.00 |     2 |
| BLAKE  | SALES      | 2850.00 |     4 |
| CLARK  | ACCOUNTING | 2450.00 |     4 |
| SCOTT  | RESEARCH   | 3000.00 |     4 |
| KING   | ACCOUNTING | 5000.00 |     5 |
| TURNER | SALES      | 1500.00 |     3 |
| ADAMS  | RESEARCH   | 1100.00 |     1 |
| JAMES  | SALES      |  950.00 |     1 |
| FORD   | RESEARCH   | 3000.00 |     4 |
| MILLER | ACCOUNTING | 1300.00 |     2 |
+--------+------------+---------+-------+
14 rows in set (0.01 sec)

案例2：查詢每個員工的領導名，部門名稱，工資以及工資等級，

mysql> select e.ename,m.ename mname,d.dname,e.sal,s.grade from emp e
    -> left join (select empno,ename from emp) as m on e.mgr=m.empno
    -> join dept d on e.deptno=d.deptno
    -> join salgrade s on e.sal between s.losal and s.hisal;
+--------+-------+------------+---------+-------+
| ename  | mname | dname      | sal     | grade |
+--------+-------+------------+---------+-------+
| SMITH  | FORD  | RESEARCH   |  800.00 |     1 |
| ALLEN  | BLAKE | SALES      | 1600.00 |     3 |
| WARD   | BLAKE | SALES      | 1250.00 |     2 |
| JONES  | KING  | RESEARCH   | 2975.00 |     4 |
| MARTIN | BLAKE | SALES      | 1250.00 |     2 |
| BLAKE  | KING  | SALES      | 2850.00 |     4 |
| CLARK  | KING  | ACCOUNTING | 2450.00 |     4 |
| SCOTT  | JONES | RESEARCH   | 3000.00 |     4 |
| KING   | NULL  | ACCOUNTING | 5000.00 |     5 |
| TURNER | BLAKE | SALES      | 1500.00 |     3 |
| ADAMS  | SCOTT | RESEARCH   | 1100.00 |     1 |
| JAMES  | BLAKE | SALES      |  950.00 |     1 |
| FORD   | JONES | RESEARCH   | 3000.00 |     4 |
| MILLER | CLARK | ACCOUNTING | 1300.00 |     2 |
+--------+-------+------------+---------+-------+
14 rows in set (0.00 sec)

三、子查詢

select陳述句中嵌套select陳述句，稱為子查詢，

1. where中的子查詢

上一篇文章中提到where子句中不能直接使用分組函式，可以使用子查詢解決這個問題，

案例：查詢比最低工資高的員工姓名和工資，

mysql> select ename,sal from emp
    -> where sal>(select min(sal) from emp);
+--------+---------+
| ename  | sal     |
+--------+---------+
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
13 rows in set (0.01 sec)

2. from中的子查詢

注：from后面的子查詢，可以將子查詢結果當作一張臨時表，

案例：查詢每個崗位的平均工資的薪資等級，

mysql> select a.job,a.avgsal,s.grade from
    -> (select job,round(avg(sal),2) avgsal from emp group by job) a
    -> join salgrade s on a.avgsal between s.losal and s.hisal;
+-----------+---------+-------+
| job       | avgsal  | grade |
+-----------+---------+-------+
| CLERK     |  1037.5 |     1 |
| SALESMAN  |    1400 |     2 |
| MANAGER   | 2758.33 |     4 |
| ANALYST   |    3000 |     4 |
| PRESIDENT |    5000 |     5 |
+-----------+---------+-------+
5 rows in set (0.00 sec)

3. select后的子查詢（了解）

案例：查詢員工的所屬部門，

mysql> select e.ename,(select d.dname from dept d where e.deptno=d.deptno) dname from emp e;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)

注：對于select后的子查詢來說，一次回傳一條資料，多于一潭訓報錯，

四、union

union合并查詢結果集合（相加）

有ABC三張表，各有10條記錄
A 連接 B 連接 C ----- 匹配次數：10*10*10 = 1000次
------------------------------------------------------------------
A 連接 B ----- 匹配次數： 10*10 = 100次
A 連接 C ----- 匹配次數： 10*10 = 100次
使用union合并：100+100 = 200次

案例：查詢作業崗位是MANAGER和SALESMAN的員工，

mysql> select ename,job from emp where job='manager' or job='salesman';
mysql> select ename,job from emp where job in ('manager','salesman');

以上兩條陳述句是利用之前提到的知識寫出來的，結果相同，

mysql> select ename,job from emp where job='manager'
    -> union
    -> select ename,job from emp where job='salesman';

注：union在進行結果合并時，要求兩個結果集的列數相同，在書寫陳述句時查詢欄位順序書寫要保持一致，

union的查詢效率更高，對于表連接來說，每連接一次新表，匹配的次數滿足笛卡爾積，也就是說查詢次數會成倍增長，但是union會減少查詢次數，同時將查詢結果集進行拼接輸出，

五、分頁查詢

1. limit(start,len)

說明：

將查詢結果的一部分取出來，
start：起始下標，可以省略，默認從0開始，
len：截取長度，
mysql中limit在order by后面執行

案例：查詢工資排名前3-5的員工，

mysql> select ename,sal from emp order by sal desc limit 2,3;
+-------+---------+
| ename | sal     |
+-------+---------+
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
3 rows in set (0.00 sec)

2. 通用分頁

每頁顯示pageSize條記錄，第pageNo頁
公式：limit (pageNo-1) * pageSize,pageSize

DQL總結

1. 查詢陳述句書寫順序

select ...from ...
where ...
group by ... having ...
order by ... limit...

2. 執行順序

from
where
group by
having
select
order by
limit

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/267111.html

標籤：其他

上一篇：Spring Boot 事務傳播機制級別/型別/行為、嵌套事務、事務隔離機制

下一篇：【MySQL學習】分組資料，使用子查詢

MySQL8 常用命令之DQL（多表聯查）