函数

指一段可以被直接调用的程序或代码，mysql已经内置了很多函数，直接调用即可。

常用函数分类

• 字符串函数
• 数值函数
• 日期函数
• 流程函数

字符串函数

CONCAT(S1,S2),字符串拼接，将S1,S2拼接成一个字符串

SELECT CONCAT('Hello',' ','World !') AS '问候';

LOWER(str),将字符串全部转为小写

SELECT LOWER('HELLO');

UPPER(str),将字符串全部转为大写

SELECT UPPER('hello');

LPAD(str,n,pad),左填充，用字符串pad对str左边进行填充，达到n个字符串长度

SELECT LPAD('joan',7,'-');

RPAD(str,n,pad),右填充，用字符串pad对str右边进行填充，达到n个字符串长度

SELECT RPAD('joan',7,'-');

TRIM(str),去掉字符串头部和尾部的空格

SELECT TRIM('  My name is chuck  ');

SUBSTRING(str,start,len),返回从字符串str从start位置起的len个长度的字符串,截取

SELECT SUBSTRING('ABCDEFGHIJKLMN',3,6); # 注意是从3开始数6个字符，而不是第三到第六个字符

示例：

-- 示例，使用0填充工号到5位数，工号字段需要为字符串类型
UPDATE employee SET workno = LPAD(workno,5,'0') WHERE id =3;

数值函数

CEIL(X),向上取整

-- CEIL
SELECT CEIL(1.5);

FLOOR(X),向下取整

-- FLOOR
SELECT FLOOR(3.14);

MOD(X,Y),返回x/y的模

-- MOD
SELECT MOD(6,4);

RAND(),返回0-1内的随机数

-- RAND
SELECT RAND();

ROUND(X,Y),求参数x的四舍五入的值，保留y位小数

-- ROUND
SELECT ROUND(3.14159,4);

示例：使用函数生成随机六位验证码

# 使用RAND()生成随机0-1的数乘以100万，再使用ROUND四舍五入取整,使用LPAD处理不足6位补0的操作
SELECT LPAD(ROUND(RAND()*1000000,0),6,0);

示例：生成随机六位大写字母验证码

# 使用RAND()生成随机数乘以26，再加上65使结果为65-90，通过CHAR将数字转换成ASCII中的大写字母，使用CONCAT连接6位随机字母
SELECT CONCAT(CHAR(ROUND((rand() * 26)+65,0)),CHAR(ROUND((rand() * 26)+65,0)),CHAR(ROUND((rand() * 26)+65,0)),CHAR(ROUND((rand() * 26)+65,0)),CHAR(ROUND((rand() * 26)+65,0)),CHAR(ROUND((rand() * 26)+65,0)));

日期函数

CURDATE(),返回当前日期

SELECT CURDATE();

CURTIME(),返回当前时间

SELECT CURTIME();

NOW(),返回当前日期和时间

SELECT NOW();

YEAR(date),获取指定date的年份

SELECT YEAR(CURDATE());

MONTH(date),获取指定date的月份

SELECT MONTH(CURDATE());

DAY(date),获取指定date的日期

SELECT DAY(CURDATE());

DATE_ADD(date,INTERVAL expr type),获取一个日期/时间值加上一个时间间隔expr后的时间值

# type支持多种格式，YEAR,MONTH,DAY,或者组合 YEAR_MONTH,中间的值需要'1,2,3'格式
SELECT DATE_ADD(NOW(),INTERVAL 70 DAY);
# 修改日期
UPDATE employee SET date = DATE_ADD(date,INTERVAL 10 DAY) WHERE name = 'joan';

DATEDIFF(date1,date2),返回起始时间date1和结束时间date2之间的天数

# 将date1-date2计算天数
SELECT DATEDIFF(curdate(),'2019-10-07');
# 计算出生天数
SELECT DATEDIFF(curdate(),'1998-08-24') AS '出生天数';

示例：

# 查询员工的入职天数并排序
SELECT name '姓名',date AS '入职日期', DATEDIFF(CURDATE(),date) AS '入职天数' FROM employee ORDER BY DATEDIFF(CURDATE(),date) DESC LIMIT 10;

流程函数

IF(value,t,f),如果value为true则返回t,否则返回f

SELECT IF(CURDATE()='2023-05-07','OK','ERROR');

IFNULL(value1,value2),如果value1不为空返回value1,否则返回value2

# 判断第一个value是否为空
SELECT IFNULL((SELECT workno FROM employee WHERE name='joan'),'FALSE');

CASE WHEN [VAL1] THEN [RES1] ... ELSE [Default] END,如果val1为true返回res1,否则返回default

# 格式，简单的 CASE 语句就是指使用简单 CASE 语句来检查表达式的值与一组唯一值的匹配。
CASE  <表达式>
   WHEN <值1> THEN <操作>
   WHEN <值2> THEN <操作>
   ...
   ELSE <操作>
END CASE;

# 示例
SELECT 
CASE WEEKDAY(NOW()) 
WHEN 0 THEN '星期一' 
WHEN 1 THEN '星期二'
WHEN 2 THEN '星期三' 
WHEN 3 THEN '星期四'
WHEN 4 THEN '星期五'
WHEN 5 THEN '星期六'
ELSE '星期天' END AS COLUMN1,NOW(),WEEKDAY(NOW()),DAYNAME(NOW());

CASE [expr] when [val1] then [res1] else [default] END,如果expr等于val1，返回res1,否则返回default

# 格式，为了执行更复杂的匹配，如范围，则可以使用可搜索 CASE 语句。 可搜索 CASE 语句等同于 IF 语句，但是它的构造更加可读。
CASE
    WHEN <条件1> THEN <命令>
    WHEN <条件2> THEN <命令>
    ...
    ELSE commands
END CASE;

# 示例
SELECT
name,date,
(CASE WHEN date <='2000-01-01' THEN '老员工'
WHEN date BETWEEN '2000-01-01' AND '2022-12-31' THEN '骨干员工'
ELSE '新入职员工' END) AS '员工属性'
FROM employee ORDER BY date;

其他

MD5(),将一个数字或字符进行md5运算，生成唯一32位字符串

SELECT MD5(1);

CHAR(),将数字转换为ASCII对照的字符

SELECT CHAR(65);# A