小蔺的米哈游数据分析师之路——MYSQL函数强化篇

        经过子查询的介绍，我们目前对于MySQL的查询应该是没有什么问题了，那么我们接下来进一步学习下就是他们的函数以及多表查询。

一，函数

        函数即是一段可以直接被另一段程序调用的程序或代码，那么我们主要看的函数分为四大种，字符串函数，数值函数，日期函数，流程函数，接下来我们就分别来学习这四种函数：

1，字符串函数        

        字符串函数的实际功能为对于我们字符串内容的处理，他其中涉及到的函数有：

concat（S1,S2..Sn）	讲S1,S2......Sn拼接成一个字符串
lower(str)	将字符串全部转为小写
upper(str)	将字符串全部转为大写
lpad(str,n,pad)	左填充，用字符串pad对str左边填充，达到n个字符长度
rpad(str,n,pad)	右填充，用字符串pad对str右边填充，达到n个字符长度
trim(str)	去掉字符串头部和尾部的空格
substring(str,start,len)	返回字符串str从strat位置起的len个长度的字符串

        那接下来，我们就给大家来演试一下他们的用法:

        第一个concat语句，我们输入：

select concat('hello','mysql');

        运行后即可将hello和mysql连在一起了，接下来是lower，我们此处选用MYSQL来尝试

SELECT LOWER('MYSQL');

         

        那么同理我们换成upper，也可以得出相同的效果

SELECT UPPER('MYSQL');

        接下来我们先来介绍下trim，这里我们需要注意的是trim删除的空格只是字符串开头和结尾的，不会删除中间的字符串，我们在这里看个例子：

select trim(' hello world ');

         我们在helloworld两个单词的前，中，后分别添加了空格，运行后我们可以看到结果如下

 

        我们生成的结果中只是删除了前后的字符串，并没有删除掉hello world 中间的字符串，在这里我们需要特别注意下的

        那么接下来我们就开始我们比较重要的填充，按照顺序我们从lpad和rpad开始，如果我们需要将01的左侧填充‘-’，并且填充至5位，那么我们可以输入:

select lpad ('01',5,'-');

        运行后我们可以看到填充位数到了五位，且用‘-’进行了填充，同样的如果是右填充的话，我们也是同样的办法

select rpad ('01',5,'-');

        大家可以自行尝试下

        最后一个就是我们讲的substring，他的用法为，如果我们需要单独获取出‘hello python’中的‘hello’字符，我们只需要输入

select substring('hello python',1,5);

        运行后我们即可得出hello的结果，那么1和5就分别代表的我们从左侧第一个字符开始取5位数字

        那么接下来我们对之前学习的内容做一个复习，大家也可以思考下：

        目前由于业务部门的变更，需要将所有员工的工号进行调整，改为‘000001’的六位格式，并且不足六位的补充0使达到6位。

        那我们首先看下这张表， 我们假设sort为目前员工的工号，可以看到大家的工号并不满足我们现在的实际情况，那么我们需要使其右填充

        那么我们需要输入的内容为更新表设置，大家可以会议一下更新表的设置我们需要哪个函数

update department set sort = lpad (sort , 6 , '0');

         因为这里需要修改整张表的值，所以我们这里不需要输入where条件

        但是我们看到这里并没有改变，并且我们的affected rows 也为0，这是为什么呢，我们去检查下表中的数据类型

        可以看出来我们修改的数列数据类型为int，由于int属于整数类型，那我们的修改其实是无法再int中去修改的，那我们换一列来试试，我们在code这一列输入同样内容

update department set code = lpad (code , 6 , '0');

         这回我们成功了，再次查看的时候，发现所有的code已经变成了6位数字，那我们如果确实有需要将我们的sort列改为填充，那我们也是可以的，这里的话我们需要首先先修改我们的列的数值类型，大家还记得公式吗，我们需要输入：

alter table departement modify column sort varchar(50);

         将我们的数据类型先改为varchar，然后再继续进行修改，即可得到我们所要的结果，为了我们数据的完整性，我们再此也不做展示，大家如果好奇，可以自己尝试下。

2，数值函数

        学完了字符串函数，我们接下来开始学习数值函数，目前常见的数值函数有以下几种

函数	功能
CEIL(X)	向上取整
FLOOR（X）	向下取整
MOD（X,Y）	返回X/Y的模
RAND（）	返回0~1内的随机数
ROUND（X,Y）	求参数的四舍五入的值，保留Y位小数

        那么接下来我们就逐一来展示下使用方法，

        首先我们尝试第一种

SELECT CEIL(5.6);

我们看到结果取得了数值6，那么我们再次尝试下，输入5.2呢 

        运行过后我们可以看结果也取了同样的值，所以在这里我们需要注意的是我们这里的向上取整并不是我们平常所说的4舍5入，而是全部向上一位进行取整。

        那么向下取整的话逻辑也是相同的，我们输入

SELECT FLOOR(5.9);

之后计算后我们可以看到无论我们小数点位数取了什么值，结果都是相同的，只会向下取整

        那么接下来就是mod函数了，这里我们需要了解X/Y的模的含义，那么我们在这里用实际距离来猜想下

        输入

select mod（2，2）;

        输出值为0， 那我们继续换成（3,2）

        结果就变成了1，那我们继续输入，当我们输入（4，2）时，可以看到结果又变成了0，我们在这里已经有了初步的猜测，我们接下来在进行几组试验后，可以发现我们的模值其实就是前面的数除以后面数所得的余数，这个大家也可以自己练习下看看。

        随机数这个就更加简单了，我们直接输入：

SELECT RAND();

        

        运行后我们就可以得到一个随机生成的小数了

        那接下来就是我们的最后一个函数了，我们这里也是继续演示下round

        如果我们想要我们的5.234保留两位小数输出，那我们就可以使用round函数，输入

select round(5.234,2);

        这样我们输出的就是我们的保留两位小数后的值了 

       学习了这些以后，那么我们这里就有一个需求了，我们平时需要生成6位数验证码的时候，该如何解决呢

        那么在这里我们的底层逻辑也很简单，就是用我们的rand函数取随机数后给他扩大1000000倍，然后进行取整就可以了，那么我们来尝试下，输入

SELECT ROUND(RAND() * 1000000, 0 );

        我们进行运行后发现，已经取到了我们想要的六位数，但是我们多运行几次尝试下 

        确有取到了五位数的结果，那么这种情况为什么会出现呢，就是因为rand是取0-1之间的随机数，那我们如果取到的值是0.036139，就会出现这种只有五位数的情况，那么这时候我们就只用用我们的lpad或者rpad来进行填充就可以了，那我们的公式变成了

SELECT LPAD(ROUND(RAND()*1000000,0),6,0);

        这样我们多尝试几次，就可以看到输入的值全都变成了6位了

        当然这个并不是我们的唯一解法，这里用这个需求也是让大家来熟悉一下我们刚刚学过的函数。

3，日期函数

        日期函数就比较简单了，我们常用的日期函数涉及到以下几种：

函数	功能
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前日期和时间
YERA(date)	获取指定date的年份
MONTH(date)	获取指定date的月份
DAY（date）	获取指定date的日期
DATE_ADD(date,INTERVAL expr type)	返回一个日期/时间值加上一个时间间隔后的时间
DATEDIFF（date1，date2）	返回起始时间date1和结束时间date2之间的天数

         那么同上，在前三个函数中，我们直接用我们的select    语句进行查找即可，而year，month和day三个函数只需要我们在后面括号内跟上时间例如今天是2023年9月24号就可以输入20230924，那这几个函数我们就不过多赘述，我们直接看date_add和datediff 

        那么假设我们需要获取70天后的时间，我们就可以输入一个代码：

SELECT DATE_ADD(NOW(), INTERVAL 70 DAY );

        运行之后便得出了我们70天后的数据了，那么difftime也是一个很好理解的概念，我们就是求出两个时间的插值例如我们想知道今天距离2023年4月8号的差值，我们就可以输入

 SELECT DATEDIFF(20230924,20230408);

        当然我们这里输入now来代替今天，得到的结果也是一样的了，当然我们时间的表达式也可以用'2023-09-24'来表达，我们效果也是相同的。

        那学完了我们的所有的时间函数之后，我们来尝试下解决一个我们的需求，现在我们需要查询所有员工的入职天数并且按照时间从长到短开始排序这里我们输入函数

select name,DATEDIFF(curdate(),entrydate) from emp ;

         

        这样我们就实现了我们的时间排序了

4，流程函数

函数	功能
IF（value，t，f）	如果value为真则，返回t，否则返回f
ifunll(value1,value2)	如果value1不为空，返回value1，否则返回value2
case when（val1）then （res1）  ....else（default）end	如果val1为真，返回res1，否则返回default默认值
case（expr）when（val1）then（res1）...else（default）  end	如果expr的值等于val1，返回res1，否则返回default默认值

        那我们来演试下这些函数，

        首先我们输入

SELECT IF(true,'OK','ERROR');

        我们输入true之后输出了OK，同样，当我们换成了 false的时候就会输出error，当然我们这里的true和false也可以换成任何的条件

        接下来就是ifnull了，我们输入

SELECT IFNULL('OK','DEFAULT');

        如果我们将'OK'换成' ' ，那我们输出的值会是空值，只有我们在前面的值输入的是null的时候，我们才会输出default值，例如我们输入

SELECT IFNULL(null,'DEFAULT');

        那我们就知道了只有前面的条件为null的时候才会显示出default值，这样我们就可以判断我们的条件是否为空

        接下来是case when，case when是一个比较重要的函数，我们这里按照一个需求来进行操作，我们现在需要查询emp表的员工的姓名和工作地址，并且当输出的城市是北京/上海时，我们需要将其输出为一线城市，如果是其他城市，我们则输出二线城市

        那我们开始编写代码

select 
    name,
    (case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end)  as '工作地址'
from 
    emp;
    

        这样之后我们就可以得到我们想要的结果了，由于这里我们没有数据，就不过多演示了，

        最后一个需求就是我们需要统计班里各个同学的成绩，展示的规则如下：

        >=85 展示优秀

        >=60 展示及格

        否则 展示不及格

         我们这里首先要插入一张表

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)  '数学' , 
  (case when english >= 85 then '优秀' when english >= 60 then '及格' else '不及格' END) '英语',
  (case when chinese >= 85 then '优秀' when chinese >= 60 then '及格' else '不及格' END) '语文'
from score;

        运行后我们就可以得到我们的结果了，那么到这里我们日常中需要用到的函数基本上就都在这里了，大家可以多多巩固下，我们这次就先学到这里啦！