SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT COMMIT(salary) FROM employees;
SELECT SUM(salary) 和，AVG(salary) 平均，MAX(salary) 最大，MIN(salary) 最小 FROM employees;

#1.count 函數(shù)的詳細(xì)介紹
//count:
//MYISAM 存儲(chǔ)引擎下,COUNT() 的效率高
//INNODB 存儲(chǔ)引擎下,COUNT()和 COUNT(1)的效率差不多，比COUNT(字段)要高一些
SELECT COUNT(salary) FROM employees;
SELECT COUNT(*) FROM employees;
SELECT COUNT(1) FROM employees;

#和分組函數(shù)一同查詢的字段有限制
#案例1.查詢公司員工的工資最大值，最小值，平均值，總和
SELECT MAX(salary) mx_sal,MIN(salary) mi_sal,AVG(salary) ag_sal,SUM(salary) sm_sal FROM employees;
#案列2. 查詢員工表中的最大入職時(shí)間和最小入職時(shí)間的相差天數(shù)
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) DIFFRENCE FROM employees;
#案例3 查詢部門編號(hào)為90的員工個(gè)數(shù)
SELECT COUNT(*) FROM employees WHERE department_id =90;

#案列1:查詢每個(gè)部門的平均工資
SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id;
#案列2: 查詢每個(gè)工種的最高工資
SELECT MAX(salary),job_id
FROM employees;
GROUP BY job_id;
#案列3 查詢每個(gè)位置上的部門個(gè)數(shù)
SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;

#添篩選條件
#案例1:查詢郵箱中包含a字符的，每個(gè)部門的平均工資
SELECT AVG(salary),department_id
FROM employees;
WHERE email LIKE '%a%'
GROUP BY department_id;
#案例2 查尋有獎(jiǎng)金的每個(gè)領(lǐng)導(dǎo)手下員工的最高工資
SELECT MAX(salary),manager_id
FROM employees;
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;
#案例3 查詢那個(gè)部門的員工個(gè)數(shù)>2
    #1.查詢每個(gè)部門的員工個(gè)數(shù)
SELECT COUNT(*),department_id
FROM employees;
GROUP BY department_id;
#2.根據(jù)1的結(jié)果進(jìn)行帥選，查詢那個(gè)部門的員工個(gè)數(shù)>2
SELECT COUNT(*),department_id
FROM employees;
GROUP BY department_id
HAVING COUNT(*)>2;
#案例4；查詢每個(gè)工種有獎(jiǎng)金的員工的最高工資>12000的工種編號(hào)和最高工資
# 1.查詢每個(gè)工種有獎(jiǎng)金的員工的最高工資
SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id;
# 2.根據(jù)1結(jié)果繼續(xù)帥選，最高工資>12000
SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;