06.MySQL 函数

函数 是指一段可以直接被另一段程序调用的程序或代码。 也就意味着，这一段程序或代码在MySQL中已经给我们提供了，我们要做的就是在合适的业务场景调用对应的函数完成对应的业务需求即可。

MySQL中的函数主要分为以下四类： 字符串函数、数值函数、日期函数、流程函数。

1、字符串函数

MySQL中内置了很多字符串函数，常用的几个如下：

#  concat : 字符串拼接
SELECT CONCAT('Hello','MySQL');

# lower : 全部转小写
SELECT LOWER('Lerbon James');

# upper : 全部转大写
SELECT UPPER('sugger');

# lpad：左填充——总共是5个字符，在01左边填充---
SELECT LPAD('01', 5, '-');

# rpad：右填充——总共是5个字符，在01右边填充---
SELECT RPAD('01', 5, '-');

# trim：去除空格——去除左右两端空格
SELECT TRIM(' Hello MySQL ');

# substring：截取子字符串
SELECT SUBSTRING('Hello MySQL', 1, 5); # Hello

案例：

由于业务需求变更，企业员工的工号，统一为5位数，目前不足5位数的全部在前面补0。比如： 1号员工的工号应该为00001。

UPDATE emp SET workno=LPAD(workno, 5, '0')

2、数值函数

常见的数值函数如下：

# ceil：向上取整
SELECT CEIL(1.1);	-- 2

# floor：向下取整
SELECT FLOOR(1.9);	-- 1

# mod：取模
SELECT MOD(7,4);	-- 3

# rand：获取随机数
SELECT RAND();

# round：四舍五入
SELECT ROUND(2.344,2);	-- 2.34

案例：通过数据库的函数，生成一个六位数的随机验证码

SELECT LPAD(FLOOR(RAND() * 1000000), 6, '0');

• 使用 RAND() * 1000000生成整数位有6位的小数。注意：可能第一位为0,那么就是5位。
• 使用 FLOOR()去掉小数位，只保留整数位。
• 使用 LPAD()用0补齐第一步可能出现5位数的情况。

3、日期函数

常见的日期函数如下：

# curdate：获取当前日期
SELECT CURDATE();

# curtime：获取当前时间
SELECT CURTIME();

# now：获取当前日期和时间、
SELECT NOW();

# year：获取当前年份
SELECT YEAR(NOW());

# month：获取当前月份
SELECT MONTH(NOW());

# day：获取当前天数
SELECT DAY(NOW());

# date_add：增加指定的时间间隔
SELECT DATE_ADD(NOW(), INTERVAL 70 YEAR);

# datediff：获取两个日期相差的天数
SELECT DATEDIFF('2023-11-30','1998-10-19');

案例：查询所有员工的入职天数，并根据入职天数倒序排序。

# 查询所有员工的入职天数，并根据入职天数倒序排序。
SELECT `name`, DATEDIFF(CURDATE(), entrydate) AS 'entrydays' FROM emp ORDER BY entrydays DESC;

4、流程控制函数

# if
SELECT IF(FALSE,'Ok', 'Error');

# ifnull
SELECT IFNULL('Ok', 'Error');	# Ok
SELECT IFNULL('', 'Error');	# 
SELECT IFNULL(NULL, 'Error');	# Error

# case when then else end 
# 需求: 查询emp表的员工姓名和工作地址 (北京/上海 ----> 一线城市 , 其他 ----> 二线城市)
SELECT
  `name`,
  (CASE workaddress WHEN '北京' THEN '一线城市' WHEN '上海' THEN '一线城市' ELSE '二线城市' END) AS '工作地址'
FROM emp;

案例：查询成绩表中每个学生的数学、语文、英语的成绩状况。

create table score(
  id int comment 'ID',
  name varchar(20) comment '姓名',
  math int comment '数学',
  english int comment '英语',
  chinese int comment '语文'
) comment '学员成绩表';

insert into score(id, name, math, english, chinese) VALUES (1, 'Tom', 67, 88, 95
), (2, 'Rose' , 23, 66, 90),(3, 'Jack', 56, 98, 76);

SELECT 
	id, `name`, 
	(CASE WHEN math >= 85 THEN '优秀' WHEN math >= 60 THEN '及格' ELSE '不及格' END) AS '数学',
	(CASE WHEN english >= 85 THEN '优秀' WHEN english >= 60 THEN '及格' ELSE '不及格' END) AS '英语',
	(CASE WHEN chinese >= 85 THEN '优秀' WHEN chinese >= 60 THEN '及格' ELSE '不及格' END) AS '语文'
FROM score;