03-MySQL常见函数

---

1. 单行函数

1.1 字符函数

length：获取字节个数

concat：字符串拼接

SELECT CONCAT(last_name, '_', first_name) 姓名
FROM employees;

upper、lower：字符转换为大小写

SELECT UPPER('john');
SELECT LOWER('john');

# 将姓变大写，名变小写，然后拼接
SELECT CONCAT(UPPER(last_name), LOWER(first_name)) 姓名
FROM employees;		

substr：截取子串（注意：索引从1开始）

# 截取从指定索引处开始的字符
SELECT SUBSTR('ABCDEFGHIGKLMN', 7) out_put     'GHIGKLMN'

# 截取从指定索引处开始指定字符长度的字符
SELECT SUBSTR('ABCDEFGHIGKLMN', 1, 3) out_put   'ABC'

# 姓名中首字母大写，其他字符小写然后用_拼接，显示出来
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_' LOWER(SUBSTR(last_name,2))) 

instr：返回子串第一次出现的索引，如果找不到则返回0

INSTR('HelloWorld','W')         #6

trim：去前后指定的空格和字符

SELECT TRIM('a' FROM 'aaaaa123aaa456aaaaaaa') AS out_put;      #123aaa456  

ltrim：去左边空格

rtrim：去右边空格

replace：替换字符串中指定字符

REPLACE('abcd', 'b', 'm');     #amcd

lpad：用指定的字符实现左填充指定长度

rpad：用指定的字符实现右填充指定长度

LPAD(salary, 10, '*')      #**********2400
RPAD(salary, 10, '*')      #2400**********

1.2 数学函数

round：四舍五入

ROUND(45.926, 2)  #45.93

rand：随机数

floor：向下取整

ceil：向上取整

mod：取余

MOD(a, b)原理：  a-a/b*b
MOD(-10, -3):   -10-(-10)/(-3)*(-3)=-1

# 被除数如果为正，结果为正，被除数为负，结果为负
MOD(-10, -3)      -1
MOD(10, -3)        1

truncate：截断，小数点后保留几位

TRUNCATE(45.926, 2)   # 45.92

1.3 日期函数

now：获取当前系统日期+时间

curdate：获取当前系统日期

curtime：获取当前系统时间

# 可以获取指定的部分，年，月，日，小时，分钟，秒
SELECT YEAR(NOW())  
SELECT YEAR('1998-1-1')

SELECT YEAR(hiredate) 年
FROM employees;

SELECT MONTH(NOW())

str_to_date：将日期格式的字符转换成指定格式的日期

STR_TO_DATE('9-13-1999','%m-%d-%Y')         #1999-09-13

date_format：将日期转换成字符

DATE_FORMAT('2018/6/6','%Y年%m月%d日')       #2018年06月06日

# 查询入职日期为1992-4-3的员工信息
SELECT *
FROM employees
WHERE hiredate='1992-4-3'

SELECT *
FROM employees
WHERE hiredate=STR_TO_DATE('4-3 1992', '%c-%d %Y')

1.4 流程控制函数

​ if 处理双分支

SELECT IF(10 < 5, '大'， '小');

SELECT last_name,commission_pct, IF(commission_pct IS NULL, '没奖金'，'有奖金') 备注
FROM employees;

case语句 处理多分支

（1）情况1：处理等值判断

java中
switch(变量或表达式){
      case 常量1：
           语句1；
           break；
      ...
      default:语句；break；
}

mysql中
case 要判断的字段或表达式
when 常量1 then 语句1；
when 常量2 then 语句2；
...
else 语句n；
end

（2）情况2：处理条件判断

1.5 其他函数

version版本

database当前库

user当前连接用户

2. 分组函数

AVG() 平均值

SUM() 求和

可以对数值型数据使用AVG 和SUM 函数。

SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary)
FROM employees
WHERE job_idLIKE '%REP%';

MAX() 最大值

MIN() 最小值

可以对任意数据类型的数据使用 MIN 和MAX 函数。

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

COUNT() 计数

（1）COUNT(*) 返回表中记录总数，适用于任意数据类型。

SELECT COUNT(*)
FROM employees
WHERE department_id = 50;

（2）COUNT(expr) 返回expr不为空的记录总数。

SELECT COUNT(commission_pct)
FROM employees
WHERE department_id = 50;

特点：

1、以上五个分组函数都忽略null值，除了count(*)

2、count的参数可以支持：字段、常量值，一般放1。建议使用 count(*)

3、都可以搭配distinct使用，用于统计去重后的结果

SELECT SUM(DISTINCT salary), SUM(salary)
FROM employees

SELECT SUM(DISTINCT salary), COUNT(salary)
FROM employees

测验题：

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

# 查询员工表中的最大入职时间和最小入职时间的相差天数
SELECT DATEDIFF(MAX(hiredate), MIN(hiredate)) DIFFRENCE
FROM employees;

# 查询部门编号为90的员工个数
SELECT COUNT(*)
FROM employees
WHERE department_id = 90;