06-MySQL函数以及分组查询GROUP BY和HAVING的使用

-- MySQL函数
-- 单行函数
数据准备
链接：https://pan.baidu.com/s/1SlQxvy96xVPkGdg2gTAY8w 
提取码：18n4
# 1.显示系统时间(注：日期+时间)
SELECT NOW()
FROM dual;

# 2.查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）
SELECT employee_id, last_name, salary, salary * 1.2 "new salary"
FROM employees;

# 3.将员工的姓名按首字母排序，并写出姓名的长度（length）
SELECT last_name, LENGTH(last_name) "name len"
FROM employees
ORDER BY last_name;

# 4.查询员工id,last_name,salary，并作为一个列输出，别名为OUT_PUT
SELECT CONCAT(employee_id, '_', last_name, '_', salary) "OUT_PUT"
FROM employees;

# 5.查询公司各员工工作的年数、工作的天数，并按工作年数的降序排序
SELECT employee_id,
       ROUND(DATEDIFF(CURDATE(), hire_date) / 365) "worked_years",
       DATEDIFF(CURDATE(), hire_date)              "worked_days"
FROM employees
ORDER BY worked_years DESC;

# 6.查询员工姓名，hire_date , department_id，
# 满足以下条件：雇用时间在1997年之后，department_id 为80 或 90 或110, commission_pct不为空
SELECT last_name, hire_date, department_id
FROM employees
WHERE department_id IN (80, 90, 110)
  AND commission_pct IS NOT NULL
  -- AND hire_date >= '1997-01-01'   存在隐式转换
  AND DATE_FORMAT(hire_date, '%Y') >= '1997';
-- 显式转换

# 7.查询公司中入职超过10000天的员工姓名、入职时间
SELECT last_name, hire_date
FROM employees
WHERE DATEDIFF(CURDATE(), hire_date) >= 10000;
# 8.做一个查询，产生下面的结果 <last_name> earns <salary> monthly but wants <salary*3>
SELECT CONCAT(last_name, ' earns', TRUNCATE(salary, 0), ' monthly but wants', TRUNCATE(salary * 3, 0)) "Dream salary"
FROM employees;

# 9.使用case-when，按照下面的条件： job grade AD_PRES A ST_MAN B IT_PROG C SA_REP D ST_CLERK E
# 产生下面的结果:
SELECT last_name AS "Last_name",
       job_id    AS "Job_id",
       CASE job_id
           WHEN 'AD_PRES' THEN 'A'
           WHEN 'ST_MAN' THEN 'B'
           WHEN 'IT_PROG' THEN 'C'
           WHEN 'SA_REP' THEN 'D'
           WHEN 'ST_CLERK' THEN 'E'
           END      "Grade"
FROM employees;

-- 聚合函数

-- 1.常见的聚合函数（计算时都会过滤掉空值null）
-- 1.1 AVG / SUM 适用于数值类型的字段
SELECT AVG(salary), SUM(salary)
FROM employees;

-- 1.2 MIN / MAX 数值，字符串，时间
SELECT MAX(salary), MIN(salary)
FROM employees;

SELECT MAX(last_name), MIN(last_name)
FROM employees;

-- 1.3 COUNT 计算指定字段在结果中出现的次数
SELECT COUNT(salary), COUNT(*)
FROM employees;

-- 查询公司的平均奖金率
SELECT SUM(commission_pct) / COUNT(*)
FROM employees;

-- count（*） VS count（1） VS count（字段）
-- 存储引擎 MyISAM 效率一致 O(1);INNODB count(*)=count(1)>count(字段)

-- 2.GROUP BY
-- 查询各个部门的平均工资
SELECT AVG(salary), department_id
FROM employees
GROUP BY department_id;

-- 查询各个job_id的平均工资
SELECT job_id, AVG(salary)
FROM employees
GROUP BY job_id;

-- 查询各个department_id,job_id的平均工资
SELECT department_id, job_id, AVG(salary)
FROM employees
GROUP BY department_id, job_id;

-- 结论：select中出现的非组函数字段，必须声明在Group by 后，group by 后面的字段可以不用声明在select中。
-- 声明在order by的前面（先分组后排序）

-- 3.HAVING 过滤数据
-- 查询各个部门中最高工资比10000高的部门信息
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000;

-- （1）过滤条件中出现了聚合函数，则必须使用having替代where
-- （2）having必须声明到group by的后面
-- （3）使用了分组group by 才能使用having

-- 查询部门id在（10,20,30,40）中最高工资比10000高的部门信息
-- 方式一
SELECT department_id, MAX(salary)
FROM employees
WHERE department_id IN (10, 20, 30, 40)
GROUP BY department_id
HAVING MAX(salary) > 10000; -- 推荐执行效率高于方式二

-- 方式二
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000
   AND department_id IN (10, 20, 30, 40); -- 不推荐
-- 结论：当过滤条件中有聚合函数时则此过滤条件必须声明在having，如果过滤条件没有组函数，声明在where中。
/*
where VS having
1.having的适用范围更广
2.如果过滤条件没有组函数，where的执行效率高于having
*/

-- 4.SQL底层的执行原理
-- 4.1 select 语句的完整结构
/*
SELECT...,...(存在聚合函数)
FROM...，
（left,RIGHT,inner）join...on...(连接条件)
（left,RIGHT,inner）join...on...(连接条件)
WHERE...（不包含聚合函数的过滤条件）
GROUP BY...（分组字段）
HAVING...（聚合函数的过滤条件）
ORDER BY...（ASC/DESC）
LIMIT ...（pageNo，pageSize）
*/
-- 4.2 sql语句的执行过程

课后练习

-- 课后练习
# 1.where子句可否使用组函数进行过滤?
-- 不可以

# 2.查询公司员工工资的最大值，最小值，平均值，总和
SELECT MAX(salary), MIN(salary), SUM(salary) / COUNT(*), COUNT(1)
FROM employees;

# 3.查询各job_id的员工工资的最大值，最小值，平均值，总和
SELECT MAX(salary), MIN(salary), AVG(salary), SUM(salary)
FROM employees
GROUP BY job_id;

# 4.选择各个job_id的员工人数
SELECT job_id, COUNT(1)
FROM employees
GROUP BY job_id;

# 5.查询员工最高工资和最低工资的差距（DIFFERENCE）
SELECT MAX(salary) - MIN(salary) AS `DIFFERENCE`
FROM employees;

# 6.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
SELECT MIN(salary), manager_id
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary) > 6000;

# 7.查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序
SELECT department_name, location_id, COUNT(1), AVG(salary)
FROM departments
         LEFT JOIN employees e ON departments.department_id = e.department_id
GROUP BY department_name, location_id
ORDER BY AVG(salary) DESC;

# 8.查询每个工种、每个部门的部门名、工种名和最低工资
SELECT department_name, job_id, MIN(salary)
FROM departments
         LEFT JOIN employees e ON departments.department_id = e.department_id
GROUP BY department_name, job_id;