我爱MySQL系列——函数

 一、什么是函数  select 函数（参数）

 函数是指一段可以直接被另一段程序调用的程序或代码

1、1字符串函数     

--concat
select concot('hello','word');  #输出  helloword

--lower
select lower('Hello');   #输出  hello 

--upper
select upper('Hello');   #输出 HELLO

--lpad
select lpad('01',5,'-');   #输出 ---01   

--rpad
select rpad('01',5,'-');  #输出01---

--trim
select trim("  hello   word  ");    #输出hello  word   （前面和后面的空格不在了）

--substring
select substring('hello word',1,5);   #输出hello




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

update emp set workno = lapd(workno,5,'0');

1、2 数值函数

-- ceil
select ceil(1.2);    2  向上取整

--floor
select ceil(1.9);    1  向下取整

--mod
select mod(5,4);     1   求模

--rand
select rand();        0-1之间的随机数

--round
select round(2.345,2);   四舍五入 保留两位   2.35


案例：通过数据库的函数，生成一个六位数的随机验证码

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

#rand随机生成0-1的小数 乘以1000000就成为整数  用round 保留整数位 但是呢因为生成的是0-1的小数  会存在0.00156656乘以1000000后保留整数是 1566 这个时候只有四位  所以用一个左填充。如果没有六位数 就在左边填充0 

1.3 日期函数 

--curdate() 当前日期
select  curdate();  2024-4-25

--curtime() 当前时间
select curtime();  15:36:23

--now() 当前时间和日期
select now();   2024-4-25 15:36:23

--year(date) 指定date的年份
select yaer(now()); 2024

--month(date) 指定date的月份
select month(now());  4
 
--day(date)  指定date的日期
select day(now());   25

--date_add()  返回一个日期/时间值加上一个时间间隔expr后的时间值
select date_add(now(),interval 70 day);   2024-7-4 15:38:23
select date_add(now(),interval 70 month);   往后推70个月
select date_add(now(),interval 70 year);   往后推70年

--datediff(date1,date2) 返回date1和date2 之间的天数
select datediff('2021-12-01','2021-11-01');   30
select datediff('2021-11-01','2021-12-01');   -30


案例  查询所有员工的入职天数 并根据入职天数倒序排序

select name, datediff(curdate(),enterdate) as 'enterdays' from emp order by enterdays desc;

1.4 流程控制函数  

在SQL语句中实现条件筛选，从而提高语句的效率

--if（value,t,f）  value为true 返回t，否则返回f
select if(true,'ok','erro');    ok



--ifnull(value1,value2)   value不为空 返回value1，否则返回value2
select ifnull('ok,'dafault');   # ok
select ifnull('','dafault');    #  返回空白    这儿判断的是第一个值是不是null
select ifnull(null,'dafault');   # dafault



--case when  then else  end     如果val1为true 返回res1  否则返回default 默认值
# 查询emp表的员工姓名和工作地址（如果员工的地址是上海/北京 ---->  一线城市 ，其他 --->二线城市）
select 
    name,
    (case wordaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end ) as '工作地址'
from emp ;



--case [expr] when [val1] then [res1]……else [default] end    如果expr的值等于val1 返回res1 #  否则返回default 默认值




# 案例  统计班级各学员的成绩，展示的规则如下:
   #      >= 85,展示优秀    >= 60 展示及格   否则不及格
create table score(
    id  int,
    name varchar(20);
    math int,
    english int,
    chinses int
)comment "学员成绩表";



select 
    id,
    name,
    (case when math >= 85 then "优秀" when math >= 65 then "及格"  else "不及格" end) as "数学成绩",
    (case when english  >= 85 then "优秀" when english >= 65 then "及格"  else "不及格" end) as "英语成绩",,
    (case when chinses >= 85 then "优秀" when chinses >= 65 then "及格"  else "不及格" end) as "语文成绩",
from  score;