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)	右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
trim(str)	去掉字符串头部和尾部的空格
substring(str,start,len)	返回从字符串str从start位置起的len个长度的字符串

#concat
select concat('hello',' everyone！');
#lower
select lower('MYSQL');
#upper
select upper('mysql');
#lpad
select lpad('syt',5,'s');
#rpad
select rpad('syt',5,'t');
#trim
select trim(' s y t ');
#substring
select substring('sytttttttttt',1,3);

例题：

#1.由于业务需求变更，企业员工的工号，同一为5位数，
#目前不足5位数的全部在前面补0.比如：1号员工的工号应该为00001
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位小数

#ceil
select ceil(1.1);#2
#floor
select floor(1.9);#1
#mod
select mod(3,4);#3
#rand
select rand();
#round
select round(3.1415926,3);#3.142

 例题：

#通过数据库的函数，随机生成一个六位数的随机验证码
select lpad(round(rand()*1000000,0),6,0);
#思考为什么需要lpad()?
#假设rand()产生0.0123456789，round()后为12345，因此需要左边补0;

三.日期函数

函数	功能
curdate() 注：current date	返回当前日期
curtime()	返回当前时间
now()	返回当前日期和时间
year(date)	获取指定date的年份
month(date)	获取指定date的月份
day(date)	获取指定date的日期
date_add(date,interval expr type)	返回一个日期/时间值加上一个时间间隔expr后的时间值
datediff(date1,date2)	返回起始时间date1和结束时间date2之间的天数

#curdate()
select curdate();#2023-01-12
#curtime()
select curtime();#10:42:17
#now()
select now();#2023-01-12 10:42:22
#year(),month(),day()
select year(now());#2023
select month(now());#1
select day(now());#12
#date_add
select date_add(now(),interval 70 month );#2028-11-12 11:45:01
#datediff
select datediff('2021-12-01','2021-10-01');#61
select datediff('2021-10-01','2021-12-01');#-61

例题： 

#查询所有员工的入职天数，并根据入职天数倒数排序
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默认值

#if
select if(false,'Ok','Error');#Error
#ifnull
select ifnull('ok','default');#ok
select ifnull(null,'default');
#case when then else end
#查询emp表的员工姓名和工作地址（北京、上海：一线城市，其他：二线城市）
select
    name,
       (case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '工作地址'
from emp;

#案例：统计班级各个学员的成绩，展示规则如下：
#>=85，优秀
#>=60，及格
#否则，不及格
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;