-- 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.做一个查询，产生下面的结果 earns monthly but wants
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中。
-- 4.SQL底层的执行原理
-- 4.1 select 语句的完整结构
-- 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;
