1.分組查詢
1.1什么是分組:
針對于班上所有的同學(xué):
分組情況1-按照性別分組:男生一組痕支，女生一組颁虐，之后可以統(tǒng)計男生和女生的數(shù)量；
分組情況2-按照年齡段分組:80后一組卧须，90后一組另绩；
分組情況3-按照籍貫分組:廣東一組，湖南一組花嘶，江西一組笋籽；
1.2語法:
SELECT [DISTINCT] *|分組字段1 [別名] [,分組字段2 [別名] ,…] | 統(tǒng)計函數(shù)
FROM 表名稱 [別名], [表名稱 [別名] ,…]
[WHERE 條件(s)]
[GROUP BY 分組字段1 [,分組字段2 ,…]]
[ORDER BY 排序字段 ASC | DESC [,排序字段 ASC | DESC]];
注意：
- 使用GROUP BY子句將表分成小組
- 組函數(shù)忽略空值,可以使用ifnull
- 結(jié)果集隱式按升序排列,如果需要改變排序方式可以使用order by 子句
1.3練習(xí)：
-- 1，按照職位分組椭员，求出每個職位的最高和最低工資
SELECT MAX(SAL),JOB FROM emp GROUP BY JOB;
-- 2车海，查詢出每一個部門員工的平均工資
SELECT AVG(SAL),DEPTNO FROM emp GROUP BY DEPTNO;
-- 3，查詢平均工資高于2000的部門和其平均工資
SELECT AVG(SAL),DEPTNO FROM emp GROUP BY DEPTNO HAVING AVG(SAL) >=2000;
-- 4隘击，查詢各個部門和崗位的平均工資
SELECT AVG(SAL),DEPTNO,JOB FROM emp GROUP BY DEPTNO,JOB;
-- 5侍芝，查詢各個管理人員下員工的平均工資，其中最低工資不能低于1300埋同，不計算老板
SELECT AVG(SAL),DEPTNO,JOB FROM emp GROUP BY job HAVING AVG(SAL) >1300;
-- 6州叠，查詢在80,81,82,83年各進公司多少人
SELECT COUNT(*),YEAR(HIREDATE) FROM emp WHERE YEAR(HIREDATE) IN ('1980','1981','1982','1983') GROUP BY YEAR(HIREDATE);
1.4分組函數(shù)使用注意:
1，出現(xiàn)在SELECT列表中的字段莺禁，如果出現(xiàn)的位置不是在分組函數(shù)中留量，那么必須出現(xiàn)在GROUP BY子句中
2，在GROUP BY 子句中出現(xiàn)的字段哟冬，可以不出現(xiàn)在SELECT列表中
3楼熄，如果沒有GROUP BY子句,SELECT列表中的任何列或表達式不能使用統(tǒng)計函數(shù)(單獨使用統(tǒng)計函數(shù)除外)：
分組函數(shù)單獨使用：
SELECT COUNT(empno) FROM emp;
錯誤的使用，出現(xiàn)了其他字段：
SELECT empno,COUNT(empno) FROM emp;
正確做法:
SELECT empno,COUNT(empno) FROM emp GROUP BY empno,job;
如果現(xiàn)在要進行分組的話浩峡，則SELECT子句之后可岂，只能出現(xiàn)分組的字段和統(tǒng)計函數(shù)，其他的字段不能出現(xiàn)：
正確做法：
SELECT job,COUNT(empno),AVG(sal)
FROM emp
GROUP BY job;
錯誤的做法：
SELECT deptno,job,COUNT(empno),AVG(sal)
FROM emp
GROUP BY job;
正確做法:
SELECT deptno,job,COUNT(empno),AVG(sal)
FROM emp
GROUP BY deptno,job;
在group by 子句中翰灾，可以按單列進行分組缕粹，也可以在多列上進行分組稚茅，多列分組就是按照多個字段的組合進行分組，最終的結(jié)果也會按照分組字段進行排序顯示平斩。
1.5分組函數(shù)執(zhí)行流程
查詢在80,81,82,83年各進公司多少人
SELECT COUNT(empno), YEAR(hiredate) FROM emp WHERE YEAR(hiredate) IN ('1980','1981','1982','1983') GROUP BY YEAR(hiredate)
在整個語句執(zhí)行的過程中亚享，最先執(zhí)行的是WHERE子句，在對表數(shù)據(jù)進行過濾后绘面，符合條件的數(shù)據(jù)通過Group by進行分組欺税，分組數(shù)據(jù)通過Having子句進行組函數(shù)過濾，最終的結(jié)果通過order by子句進行排序揭璃，排序的結(jié)果被返回給用戶晚凿。
注意點：WHERE和HAVING的區(qū)別
WHERE：是在執(zhí)行GROUP BY操作之前進行的過濾，表示從全部數(shù)據(jù)之中篩選出部分的數(shù)據(jù)瘦馍，在WHERE之中不能使用統(tǒng)計函數(shù)歼秽；
HAVING：是在GROUP BY分組之后的再次過濾，可以在HAVING子句中使用統(tǒng)計函數(shù)情组；
2.多表查詢
2.1笛卡爾積:
沒有連接條件的表關(guān)系返回的結(jié)果燥筷。
多表查詢會產(chǎn)生笛卡爾積:
假設(shè)集合A={a,b}，集合B={0,1,2}呻惕，則兩個集合的笛卡爾積為{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}荆责。
實際運行環(huán)境下，應(yīng)避免使用全笛卡爾集亚脆。
select * from emp,dept
解決方案: 在WHERE加入有效的連接條件---->等值連接/不等值連接
注意:連接 n張表,至少需要 n-1個連接條件做院。
2.2隱式連接
沒有join，通過Where的條件進行連接
2.2.1語法
SELECT [DISTINCT] * | 字段 [別名] [,字段 [別名] ,…]
FROM 表名稱 [別名], [表名稱 [別名] ,…]
[WHERE 條件(S)]
[ORDER BY 排序字段 [ASC|DESC] [,排序字段 [ASC|DESC] ,…]];
2.2.2隱式等值連接
使用表連接從多個表中查詢數(shù)據(jù)
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;
在 WHERE 子句中寫入連接條件
當(dāng)多個表中有重名列時濒持，必須在列的名字前加上表名作為前綴
等值連接是連接操作中最常見的一種键耕，通常是在存在主外鍵約束條件的多表上建立的，連接條件中的兩個字段通過等號建立等值關(guān)系柑营。
需求:查詢員工編號屈雄，員工名稱，員工所屬部門的編號和名稱.
2.2.3使用別名
使用表的別名簡化了查詢官套，提高了查詢的性能
SELECT e.empno,e.ename,d.deptno,d.dname FROM emp e,dept d WHERE e.deptno = d.deptno;
2.2.4對多表做等值連接
為了連接n個表酒奶，至少需要n-1個連接條件。例如奶赔，為了連接三個表惋嚎，至少需要兩個連接條件
2.2.5非等值連接
查詢員工的姓名,工資,所在部門的名稱,以及工資的等級.
輸出：
SELECT e.ename, e.sal,d.dname,s.grade FROM emp e,dept d,salgrade s WHERE e.deptno = d.deptno AND e.sal BETWEEN s.losal AND s.hisal
2.3顯示連接
隱式連接的問題在于：
1，需要在where條件中寫連接條件站刑，如果忘記寫另伍，代碼不會出錯，產(chǎn)生笛卡爾乘積绞旅；
2摆尝，隱式連接只能做內(nèi)連接温艇；
2.3.1內(nèi)連接
SELECT table1.column, table2.column
FROM table1 JOIN table2 ON(table1.column_name = table2.column_name)
自然連接的條件是基于表中所有同名列的等值連接
為了設(shè)置任意的連接條件或者指定連接的列，需要使用ON子句
連接條件與其它的查詢條件分開書寫
使用ON 子句使查詢語句更容易理解
先執(zhí)行join再執(zhí)行過濾
如果要先過濾再join堕汞，那么在join中可以通過join on ..and ..and來先過濾再連接
練習(xí)勺爱，使用顯式內(nèi)連接查詢：
需求:查詢員工編號，員工名稱臼朗，員工所屬部門的編號和名稱.
SELECT e.empno,e.ename,d.deptno,d.dname FROM emp e JOIN dept d ON e.deptno = d.deptno
SELECT e.empno,e.ename,d.deptno,d.dname FROM emp e JOIN dept d USING(deptno)
2.3.2外連接
需求:查詢出至少有一個員工的所有部門編號邻寿、名稱，并統(tǒng)計出這些部門的平均工資视哑、最低工資、最高工資誊涯。
需求:查詢每一個部門的總?cè)藬?shù).
| deptno | dname | COUNT(empno) |
|---|---|---|
| 10 | ACCOUNTING | 3 |
| 20 | RESEARCH | 5 |
| 30 | SALES | 6 |
| 40 | OPERATIONS | 0 |
外連接查詢:
左外連接:查詢出JOIN左邊表的全部數(shù)據(jù)查詢出來,JOIN右邊的表不匹配的數(shù)據(jù)使用NULL來填充數(shù)據(jù).
右外連接:查詢出JOIN右邊表的全部數(shù)據(jù)查詢出來,JOIN左邊的表不匹配的數(shù)據(jù)使用NULL來填充數(shù)據(jù).
SELECT dname,COUNT(empno) FROM emp JOIN dept USING (deptno) GROUP BY deptno;
SELECT deptno,dname,COUNT(empno) FROM emp RIGHT JOIN dept USING (deptno) GROUP BY deptno,deptno;
左外連接
在LEFT OUTER JOIN中挡毅，會返回所有左邊表中的行，即使在右邊的表中沒有可對應(yīng)的列值暴构。即對連接中左邊的表中的記錄不加限制
SELECT table1.column, table2.column
FROM table1 LEFT [OUTER] JOIN table2 ON (table1.column_name = table2.column_name);
右邊外連接
RIGHT OUTER JOIN中會返回所有右邊表中的行跪呈，即使在左邊的表中沒有可對應(yīng)的列值。即對連接中右邊的表中的記錄不加限制
SELECT table1.column, table2.column
FROM table1 RIGHT [OUTER] JOIN table2 ON (table1.column_name = table2.column_name);
全外連接
FULL OUTER JOIN中會返回所有右邊表中的行和所有左邊表中的行取逾，即使在左邊的表中沒有可對應(yīng)的列值或者右邊的表中沒有可對應(yīng)的列
SELECT table1.column, table2.column
FROM table1 FULL [OUTER] JOIN table2 ON (table1.column_name = table2.column_name);
MYSQL中暫時不支持全連接,可以通過union +左右連接來完成耗绿；
2.3.3自連接
在查詢語句中，一張表可以重復(fù)使用多次砾隅，完成多次連接的需要误阻；
需求:查詢員工名稱和其對應(yīng)經(jīng)理的名稱.
SELECT e.empno,e.ename,m.ename FROM emp e LEFT JOIN emp m ON e.mgr = m.empno;
3.子查詢
3.1子查詢
子查詢指的就是在一個查詢之中嵌套了其他的若干查詢.
在使用select語句查詢數(shù)據(jù)時,有時候會遇到這樣的情況，在where查詢條件中的限制條件不是一個確定的值晴埂，而是一個來自于另一個查詢的結(jié)果究反。
子查詢一般出現(xiàn)在FROM和WHERE子句中.
SELECT <select_list>
FROM table
WHERE expr operator
(SELECT select_list
FROM table);
- 1、子查詢在主查詢前執(zhí)行一次
- 2儒洛、主查詢使用子查詢的結(jié)果
練習(xí)
1精耐、查詢大于公司平均工資的員工姓名
SELECT ename,sal FROM emp WHERE sal >
(SELECT AVG(sal) FROM emp)
2、查詢出工資比MARTIN還要高的全部雇員信息
SELECT * FROM emp WHERE sal >
(SELECT sal FROM emp WHERE ename = 'MARTIN')
使用子查詢的注意事項：
- 1琅锻、子查詢要用括號括起來
- 2卦停、將子查詢放在比較運算符的右邊(增強可讀性)
- 3、對單行子查詢使用單行運算符
- 4恼蓬、對多行子查詢使用多行運算符
3.2子查詢種類
3.2.1單行單列子查詢：
只包含一個字段的查詢惊完，返回的查詢結(jié)果也只包含一行數(shù)據(jù)
1、返回一行記錄
2滚秩、使用單行記錄比較運算符：=专执；>；>=郁油；<本股；<=攀痊；<>
練習(xí)：
1、查詢大于公司平均工資的員工姓名
SELECT ename,sal FROM emp WHERE sal >
(SELECT AVG(sal) FROM emp)
2拄显、查詢出工資比MARTIN還要高的全部雇員信息
SELECT * FROM emp WHERE sal >
(SELECT sal FROM emp WHERE ename = 'MARTIN')
3.2.2多行單列子查詢：
只包含了一個字段苟径，但返回的查詢結(jié)果可能多行或者零行(多行子查詢返回多行單列)
1、返回多行
2躬审、使用多行比較運算符
IN：與列表中的任意一個值相等 :需求:查詢工資等于部門經(jīng)理的員工信息.
ANY：與子查詢返回的任意一個值比較
1): = ANY:此時和IN操作符相同. :需求:查詢工資等于任意部門經(jīng)理的員工信息.
2): > ANY:大于子查詢中最小的數(shù)據(jù). :需求:查詢工資大于任意部門經(jīng)理的員工信息.
3): < ANY:大于子查詢中最大的數(shù)據(jù). :需求:查詢工資小于任意部門經(jīng)理的員工信息.
ALL：與子查詢返回的每一個值比較
1): > ALL:大于子查詢中最大的數(shù)據(jù).
2): < ALL:小于子查詢中最小的數(shù)據(jù).
練習(xí)
查詢平均工資高于公司平均工資的部門信息
SELECT deptno,AVG(sal) FROM dept JOIN emp USING (deptno) GROUP BY deptno HAVING AVG(sal) >
(
SELECT AVG(sal) FROM emp
);
select * from departments where department_id in
(select department_id from employees
group by department_id having avg(salary)>
(select avg(salary) from employees));
3.2.3多列子查詢：
包含多個字段的返回棘街，查詢結(jié)構(gòu)可能是單行或者多行。(子查詢返回的結(jié)果是多行多列)
一般會把子查詢返回的結(jié)果當(dāng)成一個臨時表承边，接著在臨時表上繼續(xù)查詢或者連接查詢遭殉；
注意，多行多列的子查詢返回的結(jié)果必須要設(shè)置一個臨時表名博助；
查詢出每個部門的編號险污、名稱、部門人數(shù)富岳、平均工資:
SELECT d.deptno,d.dname,COUNT(e.empno),IFNULL(AVG(e.sal),0)
FROM dept d JOIN emp e USING (deptno)
GROUP BY d.deptno,d.dname
分析性能:笛卡爾積數(shù)量:
可以先把每一個部門的編號,總?cè)藬?shù),平均工資先查詢出來.
SELECT deptno dno,COUNT(empno) count ,AVG(sal) avg FROM emp GROUP BY dno
再和dept表聯(lián)合查詢部門名稱.
SELECT dept.deptno,temp.count,temp.avg FROM dept JOIN (SELECT deptno dno,COUNT(empno) count ,AVG(sal) avg FROM emp GROUP BY deptno) temp ON dept.deptno = temp.dno
3.3UNION/UNION ALL
JOIN是用于把表橫向連接蛔糯，UNION/UNION ALL是用于把表縱向連接(一般用于做查詢的臨時表)
UNION 操作符用于合并兩個或多個 SELECT 語句的結(jié)果集。
3.3.1注意
1窖式，UNION 內(nèi)部的 SELECT 語句必須擁有相同數(shù)量的列蚁飒。
2，列也必須擁有兼容的數(shù)據(jù)類型萝喘。
3淮逻，每條 SELECT 語句中的列的順序必須相同。
4蜒灰，UNION 結(jié)果集中的列名總是等于 UNION 中第一個 SELECT 語句中的列名
5弦蹂，UNION 操作符選取不同的值。如果允許重復(fù)的值强窖，請使用 UNION ALL(性能高).
3.3.2語法：
SELECT column_name(s) FROM table_name1
UNION|UNION ALL
SELECT column_name(s) FROM table_name2
3.3.3在MYSQL 中實現(xiàn)FULL JOIN:
查詢員工的編號,名稱和部門名稱.
1:先在emp表中插入一條數(shù)據(jù),并設(shè)置depto為NULL.
2:查詢
SELECT empno,ename,dname FROM emp LEFT JOIN dept USING (deptno)
UNION
SELECT empno,ename,dname FROM emp RIGHT JOIN dept USING (deptno)
