MySQL数据库————函数

1.字符串函数

字符串函数有很多，常见的有：

函数	功能
contact(s1,s2,…sn)	字符串拼接函数
lower(str)	将字符串全部转为小写
upper(str)	将字符串str全部转为大写
lpad(str,n,pad)	左填充，用字符串pad的左边进行填充，达到n个字符串长度
rpad(str,n,pad)	右填充，用字符串pad的右边进行填充，达到n个字符串长度
trim(str)	去掉字符串头部和尾部的空格
substring(string,start,len)	返回字符串str从start位置起的len个长度的字符串

演示：

select concat('hello',' world');
select lower('HELLO');
select upper('hello');
select lpad('01',5,'_');
select rpad('01',5,'_');
select trim(' hello world '); 
select substring('hello mysql',1,5);

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

update emp set workno =lpad(workno,7,'0');

原图：

改变后：

2 数值函数

常见的数值函数如下：

函数	功能
ceil(x)	向上取整
floor(x)	向下取整
mod(x,y)	返回x/y取模
rand()	生成0~1的随机数
round(x,y)	求参数x的四舍五入值，保留y位小数

select ceil(1.1);
select floor(1.1);
select mod(5,9);
select rand();
select round(5.56,1);

案例：
通过数据库的函数，生成一个六位数的随机验证码。
思路： 获取随机数可以通过rand()函数，但是获取出来的随机数是在0-1之间的，所以可以在其基础上乘以1000000，然后舍弃小数部分，如果长度不足6位，补0。

select   lpad(round(rand()*1000000,0),6,'0');

3 日期函数
常见的日期函数如下：

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

select curdate();
select curtime();
select now();
select year(now());
select month(now());
select day(now());
select day_add(now(),interval 70 day);
select datediff('2021-10-01', '2023-12-01'));

案例：
查询所有员工的入职天数，并根据入职天数倒序排序。
思路： 入职天数，就是通过当前日期 - 入职日期，所以需要使用datediff函数来完成。

select name,datediff(curdate(),entrydate) as 'entrydays' from emp order by  entrydays desc;

4 流程函数
流程函数也是很常用的一类函数，可以在SQL语句中实现条件筛选，从而提高语句的效率。

函数	功能
if(values,t,f)	如果value为true，则返回t，否则返回f
ifnull(values1,values2)	如果value1不为空，返回value1，否则返回value2
case when [ val1 ] then [res1] else [ default ] end	如果val1为true，返回res1,否则返回default默认值
case expr when vall then res1 else default end	如果expr的值等于val1，返回res1,否则返回default默认值

select if(false,'ok','error');
select ifnull('ok','error');
select ifnull('','error');
select ifnull(null,'error');





//C. 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;