2020/5/6
分組函式：(分組函式用作統計使用，又稱聚合函式、統計函式或組函式)
#sum（求和）、avg（平均值）、max（最大值）、min（最小值）、count（計數）
特點：
1. 以上分組函式中都是可以忽略null值 （其中count本身就是計算非null值得個數）
2. sum和avg函式的引數一般只能處理數值型，而max、min以及count可針對任意型別的引數
SELECT SUM(salary) FROM employees;-> 691400.00
SELECT AVG(salary) FROM employees;-> 6461.682243
SELECT MAX(salary) FROM employees;-> 24000.00
SELECT MIN(salary) FROM employees;-> 2100.00
SELECT COUNT(salary) FROM employees;-> 107
#組合使用:
SELECT
SUM(salary) 和,
ROUND(AVG(salary),2) 平均, #嵌套使用round()函式，將值保留至小數點后面2位
MAX(salary) 最大值,
MIN(salary) 最小值,
COUNT(salary) 總數
FROM
employees;
關于分組函式忽略nul值，舉例：
SELECT
AVG(commission_pct),
SUM(commission_pct) / COUNT(commission_pct),
SUM(commission_pct) / COUNT(*)
FROM
employees;
這里可以看出avg(commissom_pct)的值等于sum(commission_pct)/ count(commission_pct)(非空的總數)，而不是總體的個數(count(*))
#與DISTINCT（去重）關鍵字搭配使用
SELECT SUM(DISTINCT salary), SUM(salary) FROM employees;
去重之后，在統計工資之和
SELECT SUM(DISTINCT salary), SUM(salary) FROM employees;
統計工資的種類
#count函式詳細介紹
select count(*) from 表名; ->統計表的總行數
select count(1) from 表名; ->相當于在表中多了一列，這一列中根據表內的行數加了相應個數的1，統計1的個數，并回傳
效率比較：
MYISAM存盤引擎下，count(*)的效率高
INNODB存盤引擎下，count(*)和count(1)的效率差不多，比count(欄位)(有個判斷欄位是否為null的程序)要高
注意：和分組函式一同查詢的欄位要求是group by 后的欄位
十六、分組查詢
語法：(group by 子句語法)
注意：查詢串列必須特殊，要求是分組函式或group by后出現的欄位
SELECT
分組函式，列（要求要出現在group by 之后）
FROM
表名
[WHERE
篩選條件]
GROUP BY
分組的串列
[ORDER BY
子句]
特點：
- 分組查詢中的篩選條件分為兩類
資料源 位置 關鍵字
分組前篩選 原始表 group by子句前 where
分組后篩選 分組后的結果 group by子句后 having
1.若分組函式做篩選條件則肯定放在having子句中
2.能用分組前篩選的，就優先考慮使用分組前篩選（考慮效率問題）
2. group by 子句中支持單個欄位分組，多個欄位分組（多個欄位用逗號隔開，沒有順序要求，還支持運算式和函式分組（用的較少））
3. 也可以添加排序（排序放在整個分組查詢陳述句的最后）
----------------------------------簡單分組查詢------------------------
#案例一：查詢每個部門的平均工資
SELECT
AVG(salary) 平均工資,
department_id
FROM
employees
GROUP BY
department_id;
#案例二：查詢每個工種的最高工資
SELECT
MAX(salary),
job_id
FROM
employees
GROUP BY
job_id;
#案例三：查詢每個位置上的部門個數
SELECT
COUNT(*),
location_id FROM
departments
GROUP BY
location_id;
-----------------------------添加篩選條件的分組查詢-------------------
1.分組前篩選
#案例1：查詢郵箱中包含a字符的，每個部門的平均工資
SELECT
AVG(salary) 平均工資,
department_id 部門編號
FROM
employees
WHERE
email LIKE '%a%'
GROUP BY
department_id;
#案例2：查詢有獎金的每個領導手下員工的最高工資
SELECT
MAX(salary) 最高工資,
manager_id 領導編號
FROM
employees
WHERE
commission_pct IS NOT NULL
GROUP BY
manager_id;
2.分組后篩選
#案例1：查詢哪個部門的員工個數>2
SELECT
count(*) 員工個數,
department_id 部門編號
FROM
employees
GROUP BY
department_id
HAVING #根據GROUP by 執行后的結果再篩選
count(*) > 2;
SELECT
count(*) 員工個數,
department_id 部門編號
FROM
employees
GROUP BY
department_id
HAVING
員工個數 > 2;#可使用別名
#案例2：查詢每個工種有獎金的員工的最高工資>12000
SELECT
MAX(salary) 最高工資,
job_id 工種編號
FROM
employees
WHERE
commission_pct IS NOT NULL
GROUP BY
job_id
HAVING
MAX(salary) > 12000;
-------------------------------------------------
SELECT
MAX(salary) 最高工資,
job_id 工種編號
FROM
employees
WHERE
commission_pct IS NOT NULL
GROUP BY
工種編號
HAVING
MAX(salary) > 12000;
注意：ORDER BY以及GROUP BY子句后都可以使用別名，注意！！！WHERE子句后不可以！！！
#案例3：查詢領導編號>102的每個領導手下的最低工資>5000的領導編號是哪個，以及其最低工資
SELECT
MIN(salary) 最低工資,
manager_id 領導編號
FROM
employees
WHERE
manager_id > 102
GROUP BY
manager_id
HAVING
MIN(salary) > 5000;
對比分組前篩選與分組后篩選：
資料源 位置 關鍵字
分組前篩選 原始表 group by子句前 where
分組后篩選 分組后的結果 group by子句后 having
注意：
- 若分組函式做篩選條件則肯定放在having子句中
- 能用分組前篩選的，就優先考慮使用分組前篩選（考慮效率問題）
---------------------按運算式或函式分組查詢(用的較少)--------------------
#案例：按員工姓名的長度分組，查詢每一組的員工個數，篩選員工個數>5的有哪些
SELECT
COUNT(*) 員工個數,
LENGTH(last_name) len_name
FROM
employees
GROUP BY
LENGTH(last_name)
HAVING
COUNT(*) > 5;
-----------------------------多個欄位的分組查詢----------------------------
#案例：每個部門每個工種的平均工資
SELECT
AVG(salary) 平均工資,
department_id,
job_id
FROM
employees
GROUP BY #department_id與job_id一致的分為一個小組（與順序無關）
department_id,
job_id;
----------------------------添加排序條件的分組查詢-------------------------
#案例：每個部門每個工種的獎金存在的并且平均工資大于1000的平均工資，并且按平均工資的高低顯示
SELECT
AVG(salary) 平均工資,
department_id,
job_id
FROM
employees
WHERE
department_id IS NOT NULL
GROUP BY #department_id與job_id一致的分為一個小組（與順序無關）
department_id,
job_id
HAVING
AVG(salary)>10000
ORDER BY
AVG(salary) DESC;
十七、連接查詢
含義：又稱多表查詢，當查詢的欄位來自于多個表時，就會用到
笛卡爾乘積現象：表1 有m行，表2 有n行，結果=m*n行
發生原因：沒有有效的連接條件
如何避免：添加上有效的連接條件
連接查詢分類：
按年代分類：
sq92標準：僅僅支持內連接（對MySQL而言）
sq99標準（推薦）：支持內連接+外連接（左外、右外）+交叉連接
按功能分類：
內連接：
等值連接
非等值連接
自連接
外連接：
左外連接
右外連接
全外連接
交叉連接
（sq92標準）
#等值連接
特點：
- 多表連接的結果為多表的交集部門
- n表連接，至少需要n-1個連接條件
- 多表的順序沒有要求
- 一般需要為表取別名
- 可以搭配前面介紹的所有子句
#案例1：查詢女神名和對應的男神名
SELECT
NAME,
boyname
FROM
beauty,
boys
WHERE
beauty.boyfriend_id = boys.id; #在兩個表之間添加了一個連接的條件
#案例2：查詢員工名和對應的部門名
SELECT
last_name,
department_name
FROM
employees,
departments
WHERE
employees.department_id = departments.department_id;
#案例3：查詢員工名、工種號、工種名
SELECT
last_name,
employees.job_id, #要用表名去限定，否則識別不出來是哪個表中的job_id
job_title
FROM
employees,
jobs #兩個表的順序可調換
WHERE
employees.job_id = jobs.job_id;
------------為表取別名----------------
- 提高陳述句的簡潔度
- 區分多個重名的欄位（限定欄位）
- 若為表取了別名，則查詢的欄位就不能使用原來的表名取限定
SELECT
e.last_name,
e.job_id,#用表名去限定
j.job_title
FROM
employees e,
jobs j
WHERE
e.job_id = j.job_id;
#案例4：查詢有獎金的員工名、部門名、獎金率【增加篩選條件】
SELECT
last_name,
department_name,
commission_pct
FROM
employees e,
departments d
WHERE
e.department_id = d.department_id
AND e.commission_pct IS NOT NULL;
#案例5：查詢城市名中第二個字符為'o'的部門名和城市名【增加篩選條件】
SELECT
department_name,
city
FROM
departments d,
locations l
WHERE
d.location_id = l.location_id
AND city LIKE '_o%';
#案例6：查詢每個城市的部門個數【與group by子句搭配使用】
SELECT
count(*) 個數,
city
FROM
departments d,
locations l
WHERE
d.location_id = l.location_id
GROUP BY
city;
#案例7：查詢有獎金的每個部門的部門名和部門的領導編號和該部門的最低工資
【與group by子句搭配使用】
SELECT
department_name,
e.manager_id,
MIN(salary)
FROM
departments d,
employees e
WHERE
e.department_id = d.department_id
AND e.commission_pct IS NOT NULL
GROUP BY
department_name,manager_id;
#案例8：查詢每個工種的工種名，和員工個數，并按員工個數降序【與order by 子句搭配使用】
SELECT
job_title,
COUNT(*)
FROM
jobs j,
employees e
WHERE
j.job_id = e.job_id
GROUP BY
job_title
ORDER BY
COUNT(*) DESC;
#案例9：查詢員工名、部門名和所在的城市【多表聯合查詢】
SELECT
last_name,
department_name,
city
FROM
employees e,
departments d,
locations l
WHERE
e.department_id = d.department_id
AND d.location_id = l.location_id;
#非等值連接
#案例1：查詢員工的工資和工資級別
SELECT
salary,
grade_level
FROM
employees e,
job_grades j
WHERE
salary BETWEEN lowest_sal #salary在這個范圍內就顯示出來（不是等值的形式，而是一個范圍的判斷）
AND highest_sal;
#自連接（當前表要要連接當前表，為了不模糊，則需各取別名進行限定！）
#案例：查詢員工名和上級的名稱
SELECT
e.last_name 員工名,
m.last_name 上級名稱
FROM
employees e,
employees m
WHERE
e.manager_id = m.employee_id;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/46579.html
標籤：MySQL