MySQL单行函数和聚合函数

2.2.3 流程函数

函数	用法
IF(value,value1,value2)	如果value的值为TRUE，返回value1，否则返回value2
IFNULL(value1, value2)	如果value1不为NULL，返回value1，否则返回value2
CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 .... [ELSE resultn] END	相当于Java的if...else if...else...
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 .... [ELSE 值n] END	相当于Java的switch...case...

​

2.2.3.1 IF

IF(1>0,'正确','错误');
#结果为 正确
SELECT employee_id,commission_pct,IF(commission_pct IS NULL,0,commission_pct)
FROM employees;
#如果奖金是null则返回0，否则返回奖金本身。

2.2.3.2 IFNULL

• IFNULL是IF的特殊情况

SELECT employee_id,commission_pct,IFNULL(0,commission_pct)
FROM employees;
#如果奖金是null则返回0，否则返回奖金本身。

2.2.3.3 CASE WHEN

• CASE WHEN...THEN...WHEN...THEN...ELSE
• 与JAVA中的if...else if ...else类似

SELECT last_name,salary,CASE 
WHEN salary >= 10000 THEN '很好' 
WHEN salary >= 8000 THEN '还行'
WHEN salary >= 6000 THEN '不太行'
ELSE '不行' END "details",department_id
FROM employees;
/*
练习：查询部门号为 10,20, 30 的员工信息, 若部门号为 10, 则打印其工资的 1.1 倍, 20 号部门, 则打印其工资的 1.2 倍, 30 号部门打印其工资的 1.3 倍数。*/
SELECT employee_id,last_name,CASE 
WHEN department_id = 10 THEN salary*1.1
WHEN department_id = 20 THEN salary*1.2
ELSE salary*1.3 END 'details',department_id
FROM employees
WHERE department_id IN(10,20,30);

2.2.3 加密函数

• PASSWORD函数在mysql8.0中弃用
• MD5与SHA加密都是不可逆的
• ENCODE与DECODE是一对加密和解密的函数
• ENCODE与DECODE在8.0中弃用

函数	用法
PASSWORD(str)	返回字符串str的加密版本，41位长的字符串。加密结果不可逆，常用于用户的密码加密
MD5(str)	返回字符串str的md5加密后的值，也是一种加密方式。若参数为NULL，则会返回NULL
SHA(str)	从原明文密码str计算并返回加密后的密码字符串，当参数为NULL时，返回NULL。SHA加密算法比MD5更加安全。
ENCODE(value,password_seed)	返回使用password_seed作为加密密码加密value
DECODE(value,password_seed)	返回使用password_seed作为加密密码解密value

2.2.4 MySQL信息函数

函数	用法
VERSION()	返回当前MySQL的版本号
CONNECTION_ID()	返回当前MySQL服务器的连接数
DATABASE()，SCHEMA()	返回MySQL命令行当前所在的数据库
USER()，CURRENT_USER()、SYSTEM_USER()，SESSION_USER()	返回当前连接MySQL的用户名，返回结果格式为“主机名@用户名”
CHARSET(value)	返回字符串value自变量的字符集
COLLATION(value)	返回字符串value的比较规则

2.2.5 其他函数

函数	用法
FORMAT(value,n)	返回对数字value进行格式化后的结果数据。n表示四舍五入后保留到小数点后n位
CONV(value,from,to)	将value的值进行不同进制之间的转换
INET_ATON(ipvalue)	将以点分隔的IP地址转化为一个数字
INET_NTOA(value)	将数字形式的IP地址转化为以点分隔的IP地址
BENCHMARK(n,expr)	将表达式expr重复执行n次。用于测试MySQL处理expr表达式所耗费的时间
CONVERT(value USING char_code)	将value所使用的字符编码修改为char_code

2.3 课后练习

# 1.显示系统时间(注：日期+时间)
SELECT NOW();
# 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)
FROM employees
ORDER BY last_name; 
# 4.查询员工id,last_name,salary，并作为一个列输出，别名为OUT_PUT
SELECT CONCAT_WS('-',employee_id,last_name,salary)
FROM employees;
# 5.查询公司各员工工作的年数、工作的天数，并按工作年数的降序排序
SELECT TRUNCATE(DATEDIFF(NOW(),hire_date)/365,0) 'worked_years',DATEDIFF(NOW(),hire_date) 'worked_days'
FROM employees;
ORDER BY DESC worked_years 
# 6.查询员工姓名，hire_date , department_id，满足以下条件：雇用时间在1997年之后，department_id
为80 或 90 或110, commission_pct不为空
SELECT last_name,hire_date , department_id
FROM employees
WHERE hire_date>='1997-1-1' 
AND department_id IN(80,90,100) 
AND commission_pct IS NOT NULL;
# date_format(hire_date,'%Y-%m%d')>='1997-1-1'
# 7.查询公司中入职超过10000天的员工姓名、入职时间
SELECT last_name,hire_date
FROM employees
WHERE DATEDIFF(NOW(),hire_date)>10000;

# 8.做一个查询，产生下面的结果
#<last_name> earns <salary> monthly but wants <salary*3>
SELECT CONCAT_WS(' ',last_name,'earns',salary,'monthly but wants',salary*3)
FROM employees;
# 9.使用case-when，按照下面的条件：
SELECT last_name,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 'grades'
FROM employees;

3 聚合函数

3.1 什么是聚合函数

• 聚合函数作用于一族数据，并对一组数据返回一个值
• 如员工最大的工资是多少。

3.2 常见的几个聚合函数

3.2.1 AVG&SUM

• 数字类型外进行求和或是求平均数的运算是没有意义的。
• MySQL中对字符串求和或是求平均数返回0

SELECT AVG(salary)
FROM employees;
#计算平均工资
SELECT SUM(salary)
FROM employees;
#计算总工资

3.2.2 MAX&MIN

• 可以对非数字的数据进行比较最大和最小
• 与编码有关

SELECT MAX(salary)
FROM employees;
#计算最高工资
SELECT MIN(salary)
FROM employees;
#计算最低工资

3.2.3 COUNT

• 计算指定字段在查询结果中出现的个数

SELECT COUNT(employee_id),COUNT(salary)
WHERE employees;
#结果为107

• 计算表中有多少数据

#方式一
COUNT(*)
#方式二
COUNT(1)
#方式三
COUNT(employee_id) #不一定对！（所以不要用）

• 计算指定字段时，不计算空值
• AVG()=SUM()/COUNT()
• AVG和SUM都会忽略空值

#查询公司的平均奖金率
SELECT SUM(commission_pct)/COUNT(IFNULL(commission_pct,0))
FROM employees;
#执行COUNT时，如果内部的值是NULL则改为0，否则就按原来的值
#COUNT会忽略NULL，通过IFNULL将内部的NULL改为0，则不会忽略
#或者
SELECT AVG(IFNULL(commission_pct,0))
#AVG会忽略NULL，通过IFNULL将NULL改为0，就不会忽略了。

• 效率：COUNT(1)=COUNT(*)>COUNT(字段)

3.2.4 GROUP BY

• 分组

#需求：查询各个部门的平均工资和最高工资
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id;
#需求：二级分组？
SELECT department_id,job_id,department_id,AVG(salary)
FROM employees
GROUP BY department_id,job_id;

• SELECT中出现的非组函数的字段必须出现在GROUP BY中。反之，GROUP中出现的字段，可以不出现在SELECT中
• GROUP BY声明在FROM、WHERE的后面，ORDER BY、LIMIT的前面。
• 使用WITH ROLL UP会在最后增加一个全部的平均值。
• WITH ROLLUP不得与ORDER连用（其实8.0可以）

3.2.5 HAVING

• 要求1：过滤条件中含有组函数，则WHERE必须使用HAVING替换。
• 要求2：HAVING必须声明在GROUP BY后面
• 要求3：开发中，使用HAVING的前提是使用了GROUP BY

#练习：查询各个部门中最高工资比10000高的部门信息
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000
#练习：查询部门号位10,20,30,40中最高工资比10000高的部门信息
#方式一
SELECT department_id,MAX(salary)
FROM employees
WHERE depatrment_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中
• 当过滤条件时没有聚合函数时，可以声明在HAVING和WHERE中，建议写在WHERE中。

---

WHERE和HAVING的对比

1. 从使用范围上来讲，HAVING的适用范围更广
2. 如果从过滤条件中没有集合函数时：WHERE的效率要高于HAVING

---

3.3 课后练习

#1.where子句可否使用组函数进行过滤?
#不可以
#2.查询公司员工工资的最大值，最小值，平均值，总和
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees;
#3.查询各job_id的员工工资的最大值，最小值，平均值，总和
SELECT job_id,MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees
GROUP BY job_id;
#4.选择具有各个job_id的员工人数
SELECT job_id,COUNT(*)
FROM employees
GROUP BY job_id;
# 5.查询员工最高工资和最低工资的差距（DIFFERENCE）
SELECT MAX(salary)-MIN(salary) 'DIFFERENCE'
FROM employees;
# 6.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
SELECT manager_id,MIN(salary)
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(employee_id),AVG(salary)
FROM departments d LEFT JOIN employees e
ON d.department_id = e.department_id
GROUP BY department_name,location_id
ORDER BY AVG(salary)DESC;
# 8.查询每个工种、每个部门的部门名、工种名和最低工资
SELECT d.job_id,e.department_name,MIN(salary)
FROM departments d LEFT JOIN employees e
ON d.department_id = e.department_id
GROUP BY job_id,department_name