1.分組查詢
分組函數(shù)和前面講的函數(shù)不同在于，前面的對(duì)內(nèi)容本身的處理，而分組函數(shù)的主要功能是統(tǒng)計(jì)。
主要講的是分組函數(shù)：sum , avg, max, min , count
SELECT SUM(salary) AS 單月所發(fā)放總工資 FROM employees;
SELECT AVG(salary) AS 單月所發(fā)的平均工資 FROM employees;
SELECT MAX(salary) AS 單月所發(fā)放的最大工資 FROM employees;
SELECT MIN(salary) AS 單月所發(fā)放的最小工資 FROM employees;
SELECT COUNT(*) AS 總員工人數(shù) FROM employees;
分組函數(shù)的共同特點(diǎn)：會(huì)忽略null
分組函數(shù)還可以和一個(gè)關(guān)鍵字合用:DISTINCT
SELECT COUNT(DISTINCT salary) FROM employees;
分組查詢:group by關(guān)鍵字實(shí)現(xiàn)分組,group by放在where條件語(yǔ)句之后，order by放置在group by的后面
總體的順序先后為：
where條件，group by 分組語(yǔ)句 ,having 分組的條件 ,order by 排序語(yǔ)句
where條件是針對(duì)所有記錄的，having 條件只是局限的針對(duì)每一組的記錄的
分組查詢語(yǔ)法:
SELECT
列(這個(gè)列要求必須只能是group by的后面字段),分組函數(shù)()
FROM
表名
WHERE
篩選條件
group by
分組字段列表
having(只能配合group by使用)
與分組有關(guān)的篩選條件（針對(duì)分組后的每組內(nèi)記錄）
order by
排序
#ASC 升序排序
# DESC 降序排序
案例：查詢每個(gè)工種的最高工資
SELECT MAX(salary),job_id FROM employees GROUP BY job_id;
案例：查詢每個(gè)地方的部門個(gè)數(shù)
SELECT location_id,COUNT(*) FROM departments GROUP BY location_id;
案例：查詢每個(gè)部門每個(gè)工種的員工的平均工資
SELECT department_id,job_id,AVG(salary) AS 平均工資
FROM employees GROUP BY department_id,job_id;
案例：查詢員工郵箱里包含a字母的，每個(gè)部門的平均工資
SELECT AVG(salary) , department_id FROM employees
WHERE email
LIKE '%a%'
GROUP BY department_id;
案例：查詢有獎(jiǎng)金的每個(gè)領(lǐng)導(dǎo)手下員工的最高工資
SELECT MAX(salary),manager_id FROM employees
WHERE commission_pct IS NOT NULL && commission_pct>0
GROUP BY manager_id;
案例：查詢部門的員工數(shù)>5的所有部門的員工數(shù)
SELECT COUNT(*) AS 員工個(gè)數(shù) ,department_id FROM employees
GROUP BY department_id
HAVING 員工個(gè)數(shù)>5;
案例：查詢沒(méi)有獎(jiǎng)金的每個(gè)領(lǐng)導(dǎo)手下員工的最高工資，且最高工資高于7000
SELECT MAX(salary) AS 最高工資,manager_id FROM employees
WHERE commission_pct is NULL
GROUP BY manager_id
HAVING 最高工資 >7000
ORDER BY 最高工資 ASC;
2.連接查詢
連接查詢：又稱多表查詢，當(dāng)查詢結(jié)果來(lái)自多張數(shù)據(jù)表當(dāng)時(shí)候，就需要連接查詢
CREATE TABLE ta(id int,age int);
CREATE TABLE tb(id int,`name` VARCHAR(4),ta_id int);
INSERT INTO ta(id,age)VALUES(1,12);
INSERT INTO ta(id,age)VALUES(2,22);
INSERT INTO ta(id,age)VALUES(3,32);
INSERT INTO ta(id,age)VALUES(4,42);
INSERT INTO ta(id,age)VALUES(5,52);
INSERT INTO ta(id,age)VALUES(6,62);
INSERT INTO tb(id,`name`,ta_id) VALUES(1,'李白',2);
INSERT INTO tb(id,`name`,ta_id)VALUES(2,'小王',1);
INSERT INTO tb(id,`name`,ta_id)VALUES(3,'小張',3);
INSERT INTO tb(id,`name`,ta_id)VALUES(4,'李紅',8);
INSERT INTO tb(id,`name`,ta_id)VALUES(5,'小白',1);
INSERT INTO tb(id,`name`,ta_id)VALUES(6,'王加',10);
INSERT INTO tb(id,`name`,ta_id)VALUES(7,'趙明',10);
INSERT INTO tb(id,`name`,ta_id)VALUES(8,'趙白',6);
#將兩個(gè)表組合在一起錯(cuò)誤 查詢 出現(xiàn)笛卡爾積的結(jié)果
SELECT `name`,age FROM ta,tb;
#解決辦法添加上限制條件
#在笛卡爾積的結(jié)果上再篩選一次
SELECT `name` , age FROM ta,tb WHERE tb.ta_id=ta.id;
#具體查看連接查詢，先看看連接查詢的分類
# 重要
按照功能分類：
內(nèi)鏈接：
等值連接 *
非等值連接 *
自連接 *
外連接：
左外連接 *
右外連接
全外連接
交叉連接
等值連接：
簡(jiǎn)單說(shuō)明就是：表之間用=連接
分析：SELECT `name` , age FROM ta,tb WHERE tb.ta_id=ta.id;
它的執(zhí)行過(guò)程，和笛卡爾積一樣，只是在結(jié)果集中進(jìn)行了條件篩選，滿足tb.ta_id=ta.id條件的留下，不滿足的丟棄。
案例：查詢員工和對(duì)應(yīng)的部門名
SELECT last_name,department_name FROM employees,departments
WHERE employees.department_id =departments.department_id;
用表名來(lái)限定字段，太長(zhǎng)，不太方方便，在實(shí)際運(yùn)用中，我們一般使用別名來(lái)限定定。
SELECT e.last_name,d.department_name FROM employees e,departments d
WHERE e.department_id =d.department_id;
注意:等值連接查詢出來(lái)的是交集
在等值查詢的基礎(chǔ)上添加查詢條件進(jìn)行篩選
案例：查詢有獎(jiǎng)金定員工名以及所屬部門名：
SELECT last_name,department_name FROM employees e , departments d
WHERE e.department_id = d.department_id
AND e.commission_pct IS NOT NULL;
在等值查詢的基礎(chǔ)上加分組查詢
案例：查詢每個(gè)城市的部門個(gè)數(shù)
SELECT COUNT(*) AS 個(gè)數(shù),city FROM departments d,locations l
WHERE d.location_id=l.location_id
GROUP BY city;
案例：查詢有獎(jiǎng)金的每個(gè)部門的部門名和部門的領(lǐng)導(dǎo)編號(hào)和該部門的最低工資
SELECT d.department_name ,d.manager_id,MIN(e.salary) AS 最低工資
FROM departments d ,employees e
WHERE d.department_id = e.department_id AND e.commission_pct IS NOT NULL
GROUP BY d.department_name,d.manager_id;
案例：查詢每個(gè)工種的工種名和員工個(gè)數(shù)，并且按照員工個(gè)數(shù)排序降序
SELECT j.job_title,COUNT(*) AS 員工個(gè)數(shù) FROM jobs j, employees e
WHERE j.job_id = e.job_id
GROUP BY j.job_title
ORDER BY 員工個(gè)數(shù) DESC;
案例：查詢員工名，部門名和所在城市名
SELECT e.last_name,d.department_name,l.city FROM employees e, departments d,locations l
WHERE e.department_id = d.department_id AND d.location_id = l.location_id;
注意
本文用到的myemployees.sql 文件在MySQL實(shí)戰(zhàn)2 語(yǔ)法、篩選條件和函數(shù) 附件中
