mysql複雜查詢

2021 年 7 月 8 日
筆記
Mysql學習

1.分組查詢

先分組，然後對每一組的數據進行操作

涉及到兩個字句：group by & having

關鍵字順序：

select ... 
-- 後續執行順序
from  ... 
where ...
group by ... 
having ...
order by ... ;

從某張表查詢數據，經過where篩選信息，對篩選出的信息進行分組，分組後使用having繼續篩選，select查詢出結果後進行排序輸出

1. group by

例：每個崗位工資總和

SELECT JOB,sum(SAL) FROM EMP GROUP BY JOB;
-- 先從EMP表查詢數據，根據job字段進行分組，然後對每一組數據進行求和

在一條SQL語句中，如有group by 語句，select 後只能跟參與分組的字段以及分組函數，其他無關的不加

例：每個部門的最高薪資

SELECT DEPTNO,MAX(SAL) FROM EMP GROUP BY DEPTNO;

例：每個部門，不同工作崗位的最高薪資

技巧：兩個字段聯合成一個字段看，即兩個字段聯合分組

SELECT DEPTNO,JOB,MAX(SAL) FROM EMP GROUP BY DEPTNO,JOB ORDER BY DEPTNO;

2. having

可以用於對分組後的數據進一步過濾

不能單獨使用，也不能代替where

having 必須喝group by聯合使用

例：找出每個部門最高薪資，顯示大魚3000的

1.找出每個部門最高薪資 2.設定條件大於3000

SELECT DEPTNO,MAX(SAL) FROM EMP WHERE SAL >= 3000 GROUP BY DEPTNO; -- where 效率更高
SELECT DEPTNO,MAX(SAL) FROM EMP GROUP BY DEPTNO HAVING max(sal) >= 3000; -- having

例：找出每個部門平均薪資，顯出高於2500的

1.找出平均薪資 2.顯示平均薪資高於2500

SELECT DEPTNO,AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING AVG(SAL)>2500;

例：找出每個崗位的平均薪資，要求顯示大於1500的，除了manage外，按照平均薪資降序排列

SELECT 
	JOB,AVG(SAL) 
from 
	emp 
WHERE 
	job <> 'MANAGER' 
GROUP BY 
	JOB 
HAVING 
	AVG(SAL)>1500 
ORDER BY 
	AVG(SAL) DESC;

2.連接查詢 : 跨表查詢，多張表聯合查詢數據

表的連接方式主要有以下幾點：

1內連接

等值連接

非等值連接

自連接

2外連接

左外連接(左連接)

右外連接(右連接)

3全連接(了解)

*笛卡爾積現象

兩張表連接查詢，但無條件限制的時候，最終查詢結果條數是兩張表條數相乘結果

表的連接次數越多，效率越低

避免笛卡爾積現象：使用條件約束結果，減少連接次數，提高效率

例：查詢每個員工所在部門名稱（EMP，DEPT）

SELECT DNAME,ENAME FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO;
-- 更高效的方法如下
SELECT DEPT.DNAME,EMP.ENAME FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO;
-- 表取別名可提高查詢效率
SELECT d.DNAME,e.ENAME FROM EMP e ,DEPT d WHERE e.DEPTNO = d.DEPTNO; -- SQL92語法

查詢匹配次數並沒有比無條件時候少

1. 內連接

（兩張表連接沒有主次關係。平等的。）

特點：內連接的特點：完成能夠匹配上這個條件的數據查詢出來。

1.1 內連接之等值連接（條件為等量關係）

例：查詢每個員工所在部門名稱，顯示員工名和部門名？

emp表和dept表進行連接，條件：emp.deptno=dept.deptno

SELECT e.ENAME,d.DNAME FROM EMP e,DEPT d WHERE e.DEPTNO=d.DEPTNO; -- SQL92-- sql92的缺點：結構不清晰，表的連接條件，和後期進一步篩選的條件，都放到了where後面。SELECT e.ENAME,d.DNAME FROM EMP e (INNER) JOIN DEPT d ON e.DEPTNO=d.DEPTNO; -- SQL99-- sql99優點：表連接的條件是獨立的，連接之後，如果還需要進一步篩選，再往後繼續添加whereSELECT e.ENAME,d.DNAME FROM EMP e INNER JOIN DEPT d ON e.DEPTNO=d.DEPTNO; -- 內連接inner可以省略

SQL99語法：
select
…
from
a
join
b
on
a和b的連接條件
where
篩選條件

1.2 內連接之非等值連接(條件並非等量關係)

例：找出每個員工的薪資等級，要求顯示員工名、薪資、薪資等級？

SELECT e.ENAME,e.SAL,s.GRADE from EMP e JOIN SALGRADE s ON e.SAL BETWEEN s.LOSAL AND s.HISAL;

1.3 內連接之自連接

例：查詢員工上級領導，要求顯示員工名字和領導名字

一張表當成兩張表處理，通過不同的別名當成兩張表去進行連接查詢

SELECT a.ENAME,b.ENAME FROM EMP a JOIN EMP b ON a.MGR=b.EMPNO -- on後篩選：員工上級領導的編號等於領導自己的編號

2. 外連接

2.1 右外連接(右連接)RIGHT

例：查詢每個員工所在部門名稱，顯示員工名和所有部門名

select e.ename,d.dname from emp e right join dept d on e.deptno = d.deptno;select e.ename,d.dname from emp e right outer join dept d on e.deptno = d.deptno; -- outer可以省略

right表示將join關鍵字右邊的這張表看成主表，主要是為了將右邊dept這張表的數據全部查詢出來，捎帶着關聯查詢左邊的表emp。
在外連接當中，兩張表連接，產生了主次關係。

任何一個右連接都有左連接的寫法。

2.2 左外連接(左連接)LEFT

select e.ename,d.dname from dept d left OUTER join emp e on e.deptno = d.deptno; -- outer可以省略

任何一個左連接都有右連接的寫法。

*外連接查詢結果條數一定大於等於內連接的查詢結果條數

例：查詢員工上級領導，要求顯示員工名字和領導名字

SELECT e.ENAME,m.ENAME FROM EMP e LEFT JOIN EMP m ON e.MGR=m.EMPNO

3. 全連接union /union all

-- union會自動將完全重複的數據去除掉(select colum1,colum2...columN from tableA ) union (select colum1,colum2...columN from tableB );-- union all會保留那些重複的數據(select colum1,colum2...columN from tableA ) union all (select colum1,colum2...columN from tableB );

注意事項

1.通過union連接的SQL它們分別單獨取出的列數必須相同；2.不要求合併的表列名稱相同時，以第一個sql 表列名為準；3.使用union 時，完全相等的行，將會被合併，由於合併比較耗時，一般不直接使用 union 進行合併，而是通常採用union all 進行合併；4.被union 連接的sql 子句，單個子句中不用寫order by ，因為不會有排序的效果。但可以對最終的結果集進行排序；(select id,name from A order by id) union all (select id,name from B order by id); //沒有排序效果(select id,name from A ) union all (select id,name from B ) order by id; //有排序效果

4. 多張表連接查詢

語法：

select 	... from 	ajoin 	bon 	a和b的連接條件join 	con 	a和c的連接條件right join  -- 內連接和外連接可以混合使用	don 	a和d的連接條件

例：找出每個員工的部門名稱和工資等級，顯示員工名，部門名，薪資，薪資等級

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

例：找出每個員工的部門名稱和工資等級以及其上級領導，顯示員工名，領導名，部門名，薪資，薪資等級

SELECT 	e.ENAME,d.DNAME,e.SAL,s.GRADE ,l.ENAMEFROM 	EMP e JOIN 	DEPT d ON 	e.DEPTNO=d.DEPTNO JOIN 	SALGRADE s ON 	e.SAL BETWEEN s.LOSAL AND s.HISALLEFT JOIN -- 用內連接會缺失一條king的數據，要用外連接	EMP l ON	e.MGR=l.EMPNO

3.子查詢：select語句中嵌套select語句，被嵌套的select語句稱為子查詢。

子查詢可以出現在select，from，where後

3.1 where中的子查詢

例：找出比最低工資高的員工姓名和薪資

先查詢最低工資，然後比較

SELECT ename,SAL FROM EMP WHERE SAL>(SELECT MIN(SAL) FROM EMP)

3.2 from中的子查詢

from後面的子查詢結果可以作為一個臨時表

例：找出每個崗位平均薪資的薪資等級

第一步：找出每個崗位的平均工資（按照崗位分組求平均值）

第二步：把以上的查詢結果就當做一張真實存在的表並取別名來方便調用，其中job和av_sal就是表中可以查詢的字段

SELECT	a.JOB,	a.avg_sal,	sg.GRADE FROM	( SELECT job, AVG( SAL ) avg_sal FROM EMP GROUP BY JOB ) aJOIN 	SALGRADE sg ON 	a.avg_sal BETWEEN sg.LOSAL AND sg.HISAL

3.3 select中的子查詢

例：找出每個員工的部門名稱，要求顯示員工名，部門名？

select 		e.ename,e.deptno,(select d.dname from dept d where e.deptno = d.deptno) as dname 	from 		emp e;

4、union合併查詢結果集

例：查詢工作崗位是MANAGER和SALESMAN的員工？

SELECT ENAME,JOB FROM EMP WHERE job = 'MANAGER' or job = 'SALESMAN';SELECT ENAME,JOB FROM EMP WHERE job IN ('MANAGER','SALESMAN');-- unionSELECT ENAME,JOB FROM EMP WHERE job = 'MANAGER'unionSELECT ENAME,JOB FROM EMP WHERE job = 'SALESMAN';

union可以減少表的連接次數，即減少笛卡爾積現象，一般是完成兩個結果的拼接

union一般是對兩個結果總數進行相加得到最終結果集

注意事項：

	-- 錯誤的：union在進行結果集合併的時候，要求兩個結果集的列數相同。	select ename,job from emp where job = 'MANAGER'	union	select ename from emp where job = 'SALESMAN';	-- MYSQL可以，oracle語法嚴格 ，不可以，報錯。要求：結果集合併時列和列的數據類型也要一致。	select ename,job from emp where job = 'MANAGER'	union	select ename,sal from emp where job = 'SALESMAN';

5.limit

5.1limit作用：將查詢結果集的一部分取出來。通常使用在分頁查詢當中。

5.2用法

例：薪資降序，取前五名員工

SELECT ENAME,SAL FROM EMP ORDER BY SAL DESC LIMIT 5;

完整語法：limit startindex,length

startIndex是起始下標，length是長度。
起始下標從0開始(可不寫，缺省用法：limit 5; — 取前五)。

5.3 mysql中limit是在order by 後執行

例：工資排名3到5的員工信息

SELECT ENAME,SAL FROM EMP ORDER BY SAL DESC LIMIT 2,3-- 2表示下標為2的元素開始，也就是第三個元素

例：工資排名[5-9]的員工信息

SELECT ENAME,SAL FROM EMP ORDER BY SAL DESC LIMIT 4,5;

5.4分頁

每頁顯示3條記錄
第1頁：limit 0,3 [0 1 2]
第2頁：limit 3,3 [3 4 5]
第3頁：limit 6,3 [6 7 8]
第4頁：limit 9,3 [9 10 11]

每頁顯示pageSize條記錄
第pageNo頁：limit (pageNo – 1) * pageSize , pageSize

def pagecut(pageno,pagesize):    startindex = (pageno-1)*pagesize    sql = "SELECT ENAME,SAL FROM EMP ORDER BY SAL DESC LIMIT %d,%d;" %(startindex,pagesize)    return sql

記公式：
limit (pageNo-1)*pageSize , pageSize

語句執行順序大總結

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

Tags: Mysql學習