MySQL 之函数｜学习笔记

开发者学堂课程【MySQL 实操课程：MySQL 之函数】学习笔记，与课程紧密联系，让用户快速学习知识。

课程地址：https://developer.aliyun.com/learning/course/717/detail/12810

MySQL 之函数

 

目录:

一、MySQL 常用函数之字符串处理

二、其他函数

三、MySQL 常用函数之流程函数

四、MySQL 常用函数之其他函数

五、MySQL 创建自定义函数  

对于第二小节的第二部分内容，会以事例的方式讲解 MySQL 的存储过程，讲解在实战当中应用的场景和注意事项，并且会对存储过程与函数做一个比较。第三部分会讲解 MySQL 的触发器，在什么场景下会用到触发器，触发器的场景和注意事项，首先下面先讲解第一部分内容。

一、MySQL 常用函数之字符串处理

1、概括：

字符串连接函数：concat

大小写转换函数：lower、upper

字符串替换函数：replace

如上的字符串连接，大小，符号函数都是对字符串进行处理的，那么在讲解这些函数之前，先进行说明什么是函数：

函数可以理解为一个加工作坊，其功能为接受调用者传递的参数，对参数进行一系列的加工处理，最终把成品还回给调用者。下面进行演示说明。

首先打开实践环境（dms），找到阿里云数据库，进行连接，可以看到之前创建的数据库中有两个表，而这里还是通过命令窗口进行演示说明。

2、字符串处理连接函数：concat

select、concat 这种系统自带的函数会进行颜色的标识，颜色出现高亮时即函数名字输入正确。如下命令要连接 aaa、bbb、ccc，然后进行执行就会拼接在一起。

（1）连接多个字段演示：

mysql> select、concat（aaa、bbb、ccc）； 

concat（aaa、bbb、ccc）

aaabbbccc

…

可以发现 abc 拼接在一起，而且 concat 中有多个字符串时可以用逗号隔开，不限个数。

（2）在表中连接两个字段演示：

mysql> select * from emp；

id  ename       bir…  sal…

1   zhangsan   200…  2000.00…

2   lisi          201…  3000.00…

3   wangwu    201…   8000.00…

显示表如上，表中包含 ID、姓名、入职日期、薪水、部门的编号。在表中连接两个字段，这个函数可以把姓名 ename 和薪水 sal 两个连接起来，组成一个新的字段，再返回出来，可以通过 concat 函数指定两个字段（ename_sal），为了区分两个字段，所以在两个字段之间加上下划线，并且重命名：

mysql> select concat（ename_sal）as ename_sale from emp；

ename_sale

zhangsan_2000.00

lisi_3000.00

wangwu_8000.00

select concat（ename_sal）as ename_sale from emp；

可以发现在使用这个函数的过程中，函数名+括号，里面就是要传入的参数，这就是函数用法。

3、大小写转换函数：lower、upper

这个函数的功能是将字符串统一转成小写或大写，不管是大小写都可以转换。

（1）lower 转化小写演示：

mysql> sale lower（‘I Love MySQL’）；

lower（‘I Love MySQL’）

I love mysal

结果全部统一转成了小写

（2）upper 转化大写演示：

mysql> sale  upper（‘I love mysal’）；执行

lower（‘I Love MySQL’）

I LOVE MYSQL

如上全部转化为了大写，当然在表中也可以这样使用，如将员工表中姓名全部转成了大写，如：

mysql> select upper(ename) from emp；

upper(ename)

ZHANGSAN

LISI

WANGWU

对字符串进行统一处理时也可以用大小写转换。

4、字符串替换函数：replace

第三个函数可以将某个字符串中的某些字符统一进行替换成新的字符。首字母替换小写演示：

比如现在要将张三替换成首字母小写的张三：

mysql> select replace(ename，‘zhangsan’，‘zs’) as ename from emp；

ename

zs

lisi

wangwu

它只会将 emp 表中 ename 这一列被识别为 zhangsan 的用户替换成 zs，并不会进行其他替换，这种情况一般用于数据表中的数据，例如需要查找或者批量的替换，特别是需要对数据进行统一替换时。某一些特殊字符，例如公司的名称改了，跟公司名称相关的数据统一需要改成新的名称这时就需要 replace 函数，所以 replace能替换所有匹配的值，其他地方还有 zhangsan 这个关键词，它都可以帮你替换。

 

二、其他函数

1、获取其他字符串长度

mysql> select char_length（‘aliyun’）；

char_length（‘aliyun’）

6

如上它会帮你获取‘aliyun’这个组字符串包含6个字符长度。

2、获取所占字节长度

mysql> select length（‘aliyun’）； 

length（‘aliyun’）

6

它跟 char_length 的区别：后面的 length 是用来获取 aliyun 所占的字节数，前面的length 是用来获取 aliyun 所占的长度。

3、加密字符串

(1) password 加密演示：

mysql> select password（‘aliyun’）；

password（‘aliyun’）

*2411E586C2C7E14C69467806190482802887A413

它会生成一个41位的加密字符串，它是直接对参数 x（aliyun）进行加密。

（2）md5 加密演示：

mysql> select md5（‘aliyun’）；

& 418265807c*339038-4213f7**19f510

它会直接返回一个32位的加密字符串，这是关于加密。

4、查找字符串子字符串

如果要查询字符串里面指定位置的一个字符串函数，通过字符串子字符串查找

mysql> select substring（‘aliyun’1，6）；

substring（‘aliyun’1，6）

aliyun

从第一个字符开始找6个长度的 aliyun；去找三个长度的 aliyun。

mysql> select substring（‘aliyun’1，3）；

ali

查找第一个到第三个字符如上，这就是查找子串的方法。

一个用的比较多的是对一个字符串去进行一个逆序的解释：

mysql> select reverse（‘123456’）；

reverse（‘123456’）

654321

对某个字符串通过指定的 key 进行加密生成了一串加密字符串，反向操作即可解密：

mysql> select encode（‘123456’，‘aliyun’）；

encode（‘123456’，‘aliyun’）

???

mysql> select decode(encode（‘123456’，‘aliyun’）,‘aliyun’）；

decode(encode（‘123456’，‘aliyun’）,‘aliyun’）

123456

还有常见的修剪函数：

mysql> select ltrin（‘ a li y un ’）；

ltrin（‘ a li y un ’）

a li y un

如上它会将字符串左边的空格去除，右边的空格依然存在，你可以通过 rtrin 去除右边的空格，左边的空格仍然存在，直接通过trin可以将两边的空格都去除。这称之为字符串的修剪函数，对字符串进行修剪处理。

 

三、MySQL 常用函数之流程函数

Mysql 的流程函数，在存储过程中或者在 sal 语句中对某一些字段的值、变量做一个判断，直接通过流程函数进行处理。

1、例子:查询数据、判断员工薪资，薪资大于2000就显示级别为 high 否则为 low。

2、演示:

mysql> select from emp；

id  ename       bir…  sal…

1   zhangsan   200…  2000.00…

2   lisi          201…  3000.00…

3   wangwu    201…   8000.00…

mysql> select ename,sal,if（sal>2000,’high’,‘low’）as sal_level from emp;

id  ename       sal        sal_level

1   zhangsan    2000.00   low

2   lisi           3000.00   high

3   wangwu     8000.00   high

mysql> select ifnull（a，‘ok’）；

报错 a 不存在

（1）mysql> select ename，sal，ifnull（ename） as ename_null from emp；

此时报错，因为函数少了一个东西，需要加一个参数如下。

（2）mysql> select  ename，sal，ifnull（ename，‘空’） as ename_null  from emp；

ename       sal        ename_null

zhangsan    2000.00   zhangsan

lisi           3000.00   lisi

wangwu     8000.00   wangwu

指令表示如果 ename 的值为空 (null)，那么就给它赋值为空，否则就显示 ename的值。但是没有为空的数据看不出对比，所以可以插入一条数据便于观察。

插入一条数据：

mysql> insert into emp（hiredate，sal，deptnd）values（‘2020-05-12’，5800，1）；执行

mysql> select  ename，sal，ifnull（ename，‘空’） as ename_null  from emp；

ename       sal        ename_null

zhangsan    2000.00   zhangsan

lisi           3000.00   lisi

wangwu     8000.00   wangwu

5000.00   空

如上这里是一条数据，查询时它给你打印为空，这是 ifnull 的用法，实际运用中，某些字段的值允许为空，有可能这个字段的值为空，不想显示为空，就按照这样的方式去使用。

 

四、MySQL 常用函数之其他函数

简单快速演示如下。

1、获取数据库，版本，当前用户

mysql> select datebase（）；//获取当前数据库

datebase（）

aliyun

mysql> select version（）；//获取当前数据库版本;

select version（）

5.7.28-log

mysql> select user（）； //获取当前用户

user（）

root@100.104.5.54

如上获取当前的数据库，当前是在阿里云的数据库首先返回阿里云，然后获取当前数据库的版本，这是一个系统函数，获取当前用户时前面讲过把用户名以及当前的ip 地址都会打印出来。

2、包括获取当前的一些系统时间：

mysql> select CURRENT_DATE( )；

CURRENT_DATE( )

2020-08-17

3、还有获取一些数学函数，比如打印 pi 的值：

mysql> select pi（）；

3.141592

4、获取一些将 ip 转化的函数，它可以将 ip 地址字符串转化成整数

mysql> select inet_aton（‘192.168.0.1’）；

inet_aton（‘192.168.0.1’）

3232235521

mysql> select inet_ntoa（3232235521）；

inet_ntoa（3232235521）

192.168.0.1

而通过把 ip 转成整数存到数据表中，存取的效率会高一些。

5、还有 uuid 会生成一个唯一的字符序列：

mysql> select uuid（）；

还有一些其他函数就这里不一一演示了，可以参考 MySQL 官方文档或者阿里云相关文档进行学习。

 

五、MySQL 创建自定义函数  

1、用法：自定义函数在实际开发当中运用的比较多，那么自定义函数当中就会涉及到很多的用法，比如计算或者统计某一个部门的员工编号，通过传入员工部门的编号去返回对应部门人数的统计结果，在这里创建一个函数进行演示。

2、演示

delimiter $$; //首先设置指定$$进行结束

create function ‘get_emp_number_fn’（dept_no int）returns int（11）//创建函数，传入参为整型，返回也是一个整型

reads sal date //然后进行执行，因为要读数据库所以要加上这样的语句。

begin

declare dept_number int；//定义返回员工数量的参数 dept_number。

select count（1）into dept_number from emp where deptno_dept_no;

return dept_number;

end

$$

如上函数就定义完毕了，然后可以进行执行，但是会出现报错，因为创建函数时语法有要求，所以可以在客户端中进行操作。首先需要通过 cloud shell 连接阿里云，阿里云中有可编程的对象，并进入 mysql：

mysql> deliniter $$

mysql-> create function ‘get_emp_number_fn’（dept_no int）returns int（11）

->reads sal date

…

回车执行可以看到因为在复制时出现复制出了特殊字符所以函数会报错，所以再次回到命令窗口重新执行：

delimiter $$; //首先设置指定$$进行结束

create function ‘get_emp_number_fn’（dept_no int）returns int（11）

reads sal date  

begin

declare dept_number int；

select count（1）into dept_number from emp where deptno_dept_no;

return dept_number;

end

$$

如上执行完毕，然后在当前页面左侧点击可编程对象中的函数查看是否执行成功，演示案例中可查看到成功生成函数 get_emp_number_fn，说明创建成功。

3、创建后进行函数的调动：

直接通过 select get_emp_number_fn（1）进行调用，返回3：

mysql> select get_emp number_fn（1）;

get_emp number_fn（1）

3

此时查看表中数据可以发现确实是三条数据:

id  ename …      sal        deptno

1  zhangsan…    2000.00    1

2  lisi    …       3000.00    1

3  wangwu…      8000.00    2

4          …      5000.00    1

也可以统计每个部门员工的数量：

mysql> select deptno，deptname，get_emp_number_fn（deptno） as dept_number from dept；

deptno   deptname    dept_number

1          tech          3

2          sal           1

3          fim           0

如上就快速的查询出了每一个部门对应的员工数量，不会像原来用分组还需要做多表的关联，通过部门表关联员工表。这就是自定义函数的应用。