【七，MySQL】基础篇--函数（MySQL常用内置函数：字符串函数、数值函数、日期函数、流程函数）

一，字符串函数

#======================================函数演示==================================
#1，concat 拼接字符串
select concat('hello',' mysql'); #hello mysql
#2，lower转换小写
select lower('HEllo'); #hello
#3，upper转换大写
select upper('helLO'); #HELLO
#4，lpad 左填充
select lpad('hello',8,'_'); #___hello 本身'hello'就是5个长度，所以往左就只有3个'_'
#5，rpad右填充
select rpad('hello',8,'_'); #hello___ 本身'hello'就是5个长度，所以往右就只有3个'_'
#6，trim去除首尾空格
select trim('  hello mysql   '); #hello mysql
#7，substring截取字符
select substring('hello mysql',1,5); #索引值从1开始的   hello

#练习：员工号统一为5位数，不足5位的往前补0，如1员工为00001
update emp set workno = lpad(workno,5,'0');

二，数值函数

#======================================数值函数==================================
#1，ceil(x)向上取整
select ceil(1.2);# 2
#2，floor(x)向下取整
select floor(1.2);# 1
#3，mod(x/y)返回x/y的模
select mod(11,3);# 2
#4，rand返回0~1内的随机数
select rand();
#5，round(x,y)求x的四舍五入的值，保留y位
select round(4.5579,3);# 4.558

#练习：通过数据库的函数，生成一个6位的随机验证码
select lpad(floor(rand()*1000000),6,'0'); # 当返回的随机数为这样的情况（如0.087632）的时候，得到的随机数不会满足6位，所以需要补0
select lpad(round(rand()*1000000,0),6,'0'); # 当返回的随机数为这样的情况（如0.087632）的时候，得到的随机数不会满足6位，所以需要补0

三，日期函数

#======================================日期函数==================================
#1，curdate()当前日期
select curdate(); #2022-09-12
#2，curtime()当前时间
select curtime(); #22:14:28
#3，now()当前日期 + 时间
select now(); #2022-09-12 22:14:43
#4，YEAR(date),MONTH(date),DAY(date)获取date的年、月、日
select YEAR(now());#2022
select MONTH(now());#9
select DAY(now());#12
#5，DATE_ADD(date,INTERVAL expr type)在指定日期(date)的基础上 增加一个时间间隔(expr)之后的时间值 type:时间单位
select DATE_ADD(now(),interval 70 day); #当前时间(2022-09-12 22:20:32)往后推70天 2022-11-21 22:20:32
select DATE_ADD(now(),interval 7 month ); #当前时间(2022-09-12 22:20:32)往后推7个月 2023-04-12 22:20:32
select DATE_ADD(now(),interval 2 year ); #当前时间(2022-09-12 22:20:32)往后推2年 2024-09-12 22:20:32
#6，datediff(date1,date2)返回起始时间（date1）和结束时间（date2）之间的天数
select datediff('2022-9-5','2022-4-11'); #date1-date2 147
#练习：查询所有员工的入职天数，并根据入职天数倒序排序
select name as 姓名, datediff(curdate(),entrydate) as 'entrydays' from emp order by entrydays desc;

四、流程控制函数

#======================================流程控制函数==================================
#1，if(value,t,f)
select if(true,'OK','不OK');# OK
select if(false,'OK','不OK');# 不OK

#2，ifnull(v1,v2)
select ifnull('v1','default'); # v1
select ifnull('','default'); #  (备注：返回了一个空串)
select ifnull(null,'default'); # default

#3，case when then else end
#需求：查询emp表的员工姓名和工作地址（如果地址是‘河池‘或者’玉林‘，则展示一线城市，如果是其它则展示二线城市）
select
       name as 姓名,
       case when(workaddress='河池' or workaddress='玉林') then('一线城市') else('二线城市') end as 工作地址
from emp;

select
       name as 姓名,
       case when(workaddress='河池') then('一线城市') when(workaddress='玉林') then('一线城市') else('二线城市') end as 工作地址
from emp;

#练习：统计班级各个学员的成绩，规则如下：>=85展示优秀、>=60 展示及格，否则展示不及格
create table score(
    id int comment 'ID',
    name varchar(20) comment '姓名',
    chinese int comment '语文成绩',
    math int comment '数学成绩',
    english int comment '英语成绩'
)comment '学员成绩表',charset=utf8;
drop table score;

insert into score(id, name, chinese, math, english) values (1,'波波',70,50,20);
insert into score(id, name, chinese, math, english) values (2,'文辉',85,40,30);
insert into score(id, name, chinese, math, english) values (3,'老杨',70,50,40);
insert into score(id, name, chinese, math, english) values (4,'粥磊',70,50,50);
insert into score(id, name, chinese, math, english) values (5,'学委',90,90,60);
insert into score(id, name, chinese, math, english) values (6,'阿欢',95,60,70);
insert into score(id, name, chinese, math, english) values (7,'萌才',96,70,80);

select * from score;

select
       id as 编号,
       name as 姓名,
       case when chinese >= 85 then '优秀' when chinese >= 60 then '及格' else '不及格' end  语文,
       case when math >= 85 then '优秀' when math >= 60 then '及格' else '不及格' end  数学,
       case when english >= 85 then '优秀' when english >= 60 then '及格' else '不及格' end  英语
from score;