【示例】MySQL-表字段处理函数

前言

本文主要讲述MySQL中不同类型表字段的常用处理函数。

字符串函数 | 处理字符串类型数据

常见字符串函数	功能
CONCAT(S1,S2,...Sn)	将S1、S2、Sn拼接成一个字符串
LOWER(str)	将字符串str转为小写
UPPER(str)	将字符串str转为大写
LPAD(str,n,pad)	左填充：用pad对字符串str进行填充，使其达到n位
RPAD(str,n,pad)RPAD(str,n,pad)	右填充：用pad对字符串str进行填充，使其达到n位
TRIM(str)	去掉字符串str头部和尾部的空格
SUBSTRING(str,start,len)	截取字符串：从start位置开始，截取长度为len

# 员工id变更，五位字符表示，前边补零
update emp set workno = lpad(workno, 5, '0');

数值函数 | 处理数值类型数据

函数	功能
CEIL(x)	向上取整
FLOOR(x)	向下取整
MOD(x,y)	返回x/y的模
RAND()	返回0-1内的随机数
ROUND(x, y)	求x四舍五入的值，保留y位小数

# 通过数据库的函数，生成一个六位数的随机验证码
select lpad(round(rand()*1000000 , 0), 6, '0');

# 理解
-- rand()*1000000：是一个浮点数。整数部分最多有6位，最少有1位且这1位可以是0；小数部分有多位
-- round(rand()*1000000 , 0)：取上述浮点数的整数部分
-- lpad(round(rand()*1000000 , 0), 6, '0')：将上述浮点数的整数部分作为字符串，用字符串'0'填充至6位

日期函数 | 处理日期类型数据

函数	功能
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前的日期和时间
YEAR(date)	获取指定date的年份
MONTH(date)	获取指定date的月份
DAY(date)	获取指定date的日期
DATE_ADD(date, INTERVAL expr type)	返回一个日期/时间值 加上一个时间间隔expr后的时间值
DATEDIFF(date1,date2)	返回date1和date2之间的天数

# date_add：增加指定的时间间隔
select date_add(now(), INTERVAL 70 YEAR);

# 查询所有员工的入职天数，并根据入职天数倒序排序。
select name, datediff(curdate(), entrydate) as 'entrydays' from emp order by entrydays desc;

流程函数 | 通用字段处理

函数	功能
IF(value , t , f)	如果value为true，则返回t，否则返回f
IFNULL(value1 , value2)	如果value1不为空，返回value1，否则返回value2
CASE WHEN [ val1 ] THEN [res1] ...ELSE [ default ] END	如果val1为true，返回res1，… 否则返回default默认值
CASE [ expr ] WHEN [ val1 ] THEN [res1] ... ELSE [ default ] END	如果expr的值等于val1，返回res1，… 否则返回default默认值

select
	name,
	( case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end ) as '工作地址'
from emp;