MySQL常见函数

对外仅暴露方法名，供外部调用

1、自定义方法（函数）

2、调用方法（函数）

叫什么：函数名

干什么：函数功能

单行函数

 字符函数
     concat
     substr
     length（str）
     char_length
     upper
     lower
     trim
     left
     right
     lpad
     rpad
     instr
     strcmp
 数学函数
     abs
     ceil
     floor
     round
     truncate
     mod
     
 日期函数
     now 
     curtime
     curdate
     datediff
     date_format
     str_to_date
     
 流程控制函数
     if
     case

一、字符函数

1、CONCAT拼接字符

 SELECT CONCAT('hello,',first_name,last_name)  备注 FROM employees;

2、LENGTH 获取字节长度

 SELECT LENGTH('hello,郭襄');

3、CHAR_LENGTH 获取字符个数

 SELECT CHAR_LENGTH('hello,郭襄');

4、SUBSTRING 截取子串

注意：起始索引从1开始！

substr(str,起始索引，截取的字符长度) substr(str,起始索引)

 SELECT SUBSTR('张三丰爱上了郭襄',1,3);
 SELECT SUBSTR('张三丰爱上了郭襄',7);

5、INSTR获取字符第一次出现的索引

返回的索引也是从1开始的。

 SELECT INSTR('三打白骨精aaa白骨精bb白骨精','白骨精');

6、TRIM去前后指定的字符，默认是去空格

去除前后的x。

 SELECT TRIM(' 虚  竹    ')  AS a;
 SELECT TRIM('x' FROM 'xxxxxx虚xxx竹xxxxxxxxxxxxxxxxxx')  AS a;

7、LPAD/RPAD 左填充/右填充

LAPD(字符串，长度，填充的串) 左填充

RPAD(字符串，长度，填充的串)右填充

 SELECT LPAD('木婉清',10,'a');
 SELECT RPAD('木婉清',10,'a');

8、UPPER/LOWER 变大写/变小写

案例：查询员工表的姓名，要求格式：姓首字符大写，其他字符小写，名所有字符大写，且姓和名之间用_分割，最后起别名“OUTPUT”

 SELECT CONCAT(UPPER(SUBSTR(first_name,1)),LOWER(SUBSTR(first_name,2)),'_',UPPER(last_name)) 'OUTPUT'
 FROM employees;

9、STRCMP 比较两个字符大小

相等为0，前面大于后面为1，前面小于后面为-1

 SELECT STRCMP('aec','aec');

10、LEFT/RIGHT 截取字串

 SELECT LEFT('鸠摩智',1); 结果为：鸠
 SELECT RIGHT('鸠摩智',1); 结果为：智

二、数学函数

1、ABS绝对值

 SELECT ABS(-2.4)

2、CEIL向上取整 返回>=该参数的最小整数

 SELECT CEIL(-1.09);
 SELECT CEIL(0.09);
 SELECT CEIL(1.00);

3、FLOOR 向下取整，返回<=该参数的最大整数

 SELECT FLOOR(-1.09);
 SELECT FLOOR(0.09);
 SELECT FLOOR(1.00);

4、四舍五入

 SELECT ROUND(1.8712345);
 SELECT ROUND(1.8712345,2);

5、TRUNCATE 截断

SELECT TRUNCATE(1.8712345,1);结果为：1.8 截取一位小数

6、MOD取余

 SELECT MOD(-10,3)
 a%b = a-(INT)a/b*b
 SELECT 10%3;

三、日期函数

1、NOW

 SELECT NOW(); 结果为：2020-12-09 14:27:53

2、CURDATE

 SELECT CURDATE(); 结果为：2020-12-09

3、CURTIME

 SELECT CURTIME(); 结果为：14:29:31

4、DATEDIFF

DATEDIFF() 函数返回两个日期之间的时间。

 SELECT DATEDIFF('1998-7-16','2019-7-13');

5、DATE_FORMAT

 SELECT DATE_FORMAT('1998-7-16','%Y年%m月%d日 %H小时%i分钟%s秒') 出生日期;
 SELECT DATE_FORMAT(hiredate,'%Y年%M月%d日 %H小时%i分钟%s秒')入职日期 
 FROM employees;

6、STR_TO_DATE 按指定格式解析字符串为日期类型

 SELECT STR_TO_DATE('3/15 1998','%m/%d %Y'); 
 结果为：1998-03-15

四、流程控制函数

1、IF函数

类似于java中的三元表达式

 SELECT IF(100>9,'1','0');

需求：如果右奖金，则显示最终奖金，如果没有，则显示0

 SELECT IF(commission_pct IS NULL,0,commission_pct*12*salary) 奖金，commission_pct
 FROM employees;

2、CASE函数

情况1：类似switch语句，可以实现等值判断

CASE 表达式 WHEN 值1 THEN 结果1 WHEN 值2 THEN 结果2 ... ELSE 结果n END

案例： 部门编号是30，工资显示为2倍 部门编号是50，工资显示为3倍 部门编号是60，工资显示为4倍 否则不变

 SELECT department_id,salary,
 CASE department_id
 WHEN 30 THEN salary*2
 WHEN 50 THEN salary*3
 WHEN 60 THEN salary*4
 ELSE salary
 END newSalary
 From employees;

情况2：类似于多重IF语句，实现区间判断

CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 ...

ELSE 结果n

END

案例：如果工资>20000,显示级别A 工资>15000,显示级别B 工资>10000,显示级别C 否则，显示D

SELECT salary,
 CASE
 WHEN salary>20000 THEN 'A'
 WHEN salary>15000 THEN 'B'
 WHEN salary>10000 THEN 'C'
 ELSE 'D'
 END level
 FROM employees

五、分组函数

清单：

 sum(字段名)：求和
 avg(字段名)：求平均数
 max(字段名)：求最大值
 min(字段名)：求最小值
 count(字段名)：计算非空字段值的个数

count的补充：

统计结果集的行数，推荐使用count(*)

SELECT COUNT(*) FROM employees;

搭配distinct实现去重的统计

 #查询有员工的部门的个数
 SELECT COUNT(DISTINCT department_id) FROM employees;