博客

三、MySQL——函数类型

  • Post author:
  • Post category:mysql



目录


1、字符串函数


2、数值函数


3、日期函数


4、流程函数


窗口函数


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


也就意味着，这一段程序或代码在


MySQL


中


已经给我们提供了，我们要做的就是在合适的业务场景调用对应的函数完成对应的业务需求即可。


那么，函数到底在哪儿使用呢？


我们先来看两个场景：



  1. 在企业的


    OA


    或其他的人力系统中，经常会提供的有这样一个功能，每一个员工登录上来之后都能够看到当前员工入职的天数。


    而在数据库中，存储的都是入职日期，如


    2000-11-12


    ，那如果快速计


    算出天数呢？

  2. 在做报表这类的业务需求中


    ,


    我们要展示出学员的分数等级分布。而在数据库中，存储的是学生的


    分数值，如


    98/75


    ，如何快速判定分数的等级呢？


其实，上述的这一类的需求呢，我们通过


MySQL


中的函数都可以很方便的实现  。


MySQL


中的函数主要分为以下四类：  字符串函数、数值函数、日期函数、流程函数。

1、

字符串函数


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


个长度的字符串


演示如下：


1、concat : 字符串拼接


1


select


concat(


‘Hello’


,


‘ MySQL’


);


2、lower :


全部转小写


1


select


lower(


‘Hello’


);


3、upper :


全部转大写


1


select


upper(


‘Hello’


);


4、lpad :


左填充


1


select


lpad(


’01’


,


5


,


‘-‘


);


5、rpad :


右填充


1


select


rpad(


’01’


,


5


,


‘-‘


);


6、trim :


去除空格


1


select


trim(


‘ Hello


MySQL ‘


);


7、substring :


截取子字符串


1


select


substring(


‘Hello MySQL’


,


1


,


5


);


案例


:


由于业务需求变更，企业员工的工号，统一为


5


位数，目前不足


5


位数的全部在前面补


0


。比如：


1


号员工的工号应该为


00001


。


1


update


emp


set


workno = lpad(workno,


5


,


‘0’


);


处理完毕后


,


具体的数据为


:

​​​​​​​2、

数值函数


常见的数值函数如下：



函数



功能


CEIL(x)


向上取整


FLOOR(x)


向下取整


MOD(x,y)


返回


x/y


的模


RAND()


返回


0~1


内的随机数


ROUND(x,y)


求参数


x


的四舍五入的值，保留


y


位小数


演示如下：


1、ceil


：向上取整


1


select


ceil(


1.1


);


2、floor


：向下取整


1


select


floor(


1.9


);


3、mod


：取模


1


select


mod(


7


,


4


);


4、rand


：获取随机数


1


select


rand();


5、round：四舍五入


1


select


round(


2.344


,


2


);


案例：


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


思路：  获取随机数可以通过


rand()


函数，但是获取出来的随机数是在


0-1


之间的，所以可以在其基础上乘以


1000000


，然后舍弃小数部分，如果长度不足


6


位，补


0


1


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


之间的天数


演示如下：


1、curdate


：当前日期


1


select


curdate();


2、curtime


：当前时间


1


select


curtime();


3、now


：当前日期和时间


1


select


now();


4、YEAR , MONTH , DAY


：当前年、月、日


  1. select


    YEAR


    (now());

  1. select


    MONTH(now());

  1. select


    DAY(now());


5、date_add


：增加指定的时间间隔


1


select


date_add(now(), INTERVAL


70


YEAR


);


6、datediff


：获取两个日期相差的天数


1


select


datediff(


‘2021-10-01’


,


‘2021-12-01’


);


案例：


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


思路：  入职天数，就是通过当前日期


–


入职日期，所以需要使用


datediff


函数来完成。


1


select


name, datediff(curdate(), entrydate)


as


‘entrydays’


from


emp


order by


entrydays


desc


;

​​​​​​​4、

流程函数


流程函数也是很常用的一类函数，可以在


SQL


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



函数



功能


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


默认值


演示如下：


1、if


1


select


if(


false


,


‘Ok’


,


‘Error’


);


2、ifnull


1


select


ifnull(


‘Ok’


,


‘Default’


);


3


select


ifnull(


”


,


‘Default’


);


5


select


ifnull(


null


,


‘Default’


);


3、case when then else end


需求


:


查询


emp


表的员工姓名和工作地址


(


北京


/


上海


—->


一线城市


,


其他


—->


二线城市


)


1


selec




name,


(


case workaddress when


‘


北京


‘


then


‘


一线城市


‘


when


‘


上海


‘


then


‘


一线城市


‘


else


‘


二线城市


‘


end )


as


‘


工作地址


‘


from


emp;

​​​​​​​


案例


:

1、

create table


score(


id


int


comment


‘ID’


,


name


varchar


(


20


)


comment


‘


姓名


‘


,


math


int


comment


‘


数学


‘


,


english


int


comment


‘


英语


‘


,


chinese


int


comment


‘


语文


‘


comment


‘


学员成绩表


‘


;


2、insert into


score(id, name, math, english, chinese)


VALUES


(


1


,


‘Tom’


,


67


,


88


,


95


), (


2


,


‘Rose’


,


23


,


66


,


90


),(


3


,


‘Jack’


,


56


,


98


,


76


);


具体的


SQL


语句如下


:


1


select




id,


name,


(case when math >=


85


then


‘


优秀


‘


when math >=


60


then


‘


及格


‘


else


‘


不及格


‘


end )


‘


数学


‘


,


(case when english >=


85


then


‘


优秀


‘


when english >=


60


then


‘


及格


‘


else


‘


不及格


‘


end )


‘


英语


‘


,


(case when chinese >=


85


then


‘


优秀


‘


when chinese >=


60


then


‘


及格


‘


else


‘


不及格


‘


end )


‘


语文


‘




from


score;


MySQL


的常见函数我们学习完了，那接下来，我们就来分析一下，在前面讲到的两个函数的案例场景，


思考一下需要用到什么样的函数来实现


?


  1. 数据库中，存储的是入职日期，如


    2000-01-01


    ，如何快速计算出入职天数呢？


    ————–


    >


答案


:


datediff


  1. 数据库中，存储的是学生的分数值，如


    98


    、


    75


    ，如何快速判定分数的等级呢？


    ————————


    >


答案


:


case … when …


版权声明：本文为weixin_53330986原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
Tags: