mysql系列III-函数

发表于 更新于 分类于 ， Changyan： 本文字数： 2.4k 阅读时长 ≈ 9 分钟

函数 是指一段可以直接被另一段程序调用的程序或代码。 也就意味着，这一段程序或代码在MySQL中已经给我们提供了，我们要做的就是在合适的业务场景调用对应的函数完成对应的业务需求即可。 那么，函数到底在哪儿使用呢？

Mysql系列文章

  1. mysql系列I-概述

  2. mysql系列II-sql语法

    ①. mysql系列II-sql语法-(1)-概述

    ②. mysql系列II-sql语法-(2)-DDL

    ③. mysql系列II-sql语法-(3)-DML

    ④. mysql系列II-sql语法-(4)-DQL

    ⑤. mysql系列II-sql语法-(5)-DCL

  3. mysql系列III-函数

  4. mysql系列IV-约束

  5. mysql系列V-多表查询

  6. mysql系列VI-事务

  7. mysql系列VII-存储引擎

  8. mysql系列VIII-索引

    ①. mysql系列VIII-索引(1)-概述

    ②. mysql系列VIII-索引(2)-结构

    ③. mysql系列VIII-索引(3)-分类

    ④. mysql系列VIII-索引(4)-语法

    ⑤. mysql系列VIII-索引(5)-SQL性能分析

    ⑥. mysql系列VIII-索引(6)-索引使用

    ⑦. mysql系列VIII-索引(7)-设计原则

  9. mysql系列IX-SQL优化

  10. mysql系列X-视图

  11. mysql系列XI-存储过程

  12. mysql系列XII-存储函数

  13. mysql系列XIII-触发器

  14. mysql系列XIV-锁

我们先来看两个场景：

1). 在企业的OA或其他的人力系统中，经常会提供的有这样一个功能，每一个员工登录上来之后都能够看到当前员工入职的天数。 而在数据库中，存储的都是入职日期，如 2000-11-12，那如果快速计算出天数呢？
2). 在做报表这类的业务需求中,我们要展示出学员的分数等级分布。而在数据库中，存储的是学生的分数值，如98/75，如何快速判定分数的等级呢？
其实，上述的这一类的需求呢，我们通过MySQL中的函数都可以很方便的实现 。
MySQL中的函数主要分为以下四类： 字符串函数、数值函数、日期函数、流程函数。

字符串函数

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个长度的字符串

演示如下：
A. concat : 字符串拼接

1
2
3
4
5
6
7
mysql> select concat('Hello' , ' MySQL');
+----------------------------+
| concat('Hello' , ' MySQL') |
+----------------------------+
| Hello MySQL                |
+----------------------------+
1 row in set (0.00 sec)

B. lower : 全部转小写

1
2
3
4
5
6
7
mysql> select lower('HELLO');
+----------------+
| lower('HELLO') |
+----------------+
| hello          |
+----------------+
1 row in set (0.00 sec)

C. upper : 全部转大写

1
2
3
4
5
6
7
mysql> select upper('hello');
+----------------+
| upper('hello') |
+----------------+
| HELLO          |
+----------------+
1 row in set (0.00 sec)

D. lpad : 左填充

1
2
3
4
5
6
7
mysql> select lpad('01', 5, '-');
+--------------------+
| lpad('01', 5, '-') |
+--------------------+
| ---01              |
+--------------------+
1 row in set (0.00 sec)

E. rpad : 右填充

1
2
3
4
5
6
7
mysql> select rpad('01', 5, '-');
+--------------------+
| rpad('01', 5, '-') |
+--------------------+
| 01---              |
+--------------------+
1 row in set (0.00 sec)

F. trim : 去除空格

1
2
3
4
5
6
7
mysql> select trim(' Hello MySQL ');
+-----------------------+
| trim(' Hello MySQL ') |
+-----------------------+
| Hello MySQL           |
+-----------------------+
1 row in set (0.00 sec)

G. substring : 截取子字符串

1
2
3
4
5
6
7
mysql> select substring('Hello MySQL',1,5);
+------------------------------+
| substring('Hello MySQL',1,5) |
+------------------------------+
| Hello                        |
+------------------------------+
1 row in set (0.00 sec)

案例:

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

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

处理完毕后, 具体的数据为:

数值函数

常见的数值函数如下：

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

演示如下：
A. ceil：向上取整

1
2
3
4
5
6
7
mysql> select ceil(1.1);
+-----------+
| ceil(1.1) |
+-----------+
|         2 |
+-----------+
1 row in set (0.01 sec)

B. floor：向下取整

1
2
3
4
5
6
7
mysql> select floor(1.9);
+------------+
| floor(1.9) |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

C. mod：取模

1
2
3
4
5
6
7
mysql> select mod(7,4);
+----------+
| mod(7,4) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

D. rand：获取随机数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select rand();
+-------------------+
| rand()            |
+-------------------+
| 0.423815661504693 |
+-------------------+
1 row in set (0.00 sec)

mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.8643847721297152 |
+--------------------+
1 row in set (0.00 sec)

E. round：四舍五入

1
2
3
4
5
6
7
mysql> select round(2.344,2);
+----------------+
| round(2.344,2) |
+----------------+
|           2.34 |
+----------------+
1 row in set (0.00 sec)

案例：
通过数据库的函数，生成一个六位数的随机验证码。
思路： 获取随机数可以通过rand()函数，但是获取出来的随机数是在0-1之间的，所以可以在其基础上乘以1000000，然后舍弃小数部分，如果长度不足6位，补0

1
2
3
4
5
6
7
mysql> select lpad(round(rand()*1000000 , 0), 6, '0');
+-----------------------------------------+
| lpad(round(rand()*1000000 , 0), 6, '0') |
+-----------------------------------------+
| 659230                                  |
+-----------------------------------------+
1 row in set (0.00 sec)

日期函数

常见的日期函数如下：

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

演示如下：
A. curdate：当前日期

1
2
3
4
5
6
7
mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2022-07-07 |
+------------+
1 row in set (0.00 sec)

B. curtime：当前时间（UTC时间）

1
2
3
4
5
6
7
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 04:55:17  |
+-----------+
1 row in set (0.00 sec)

C. now：当前日期和时间（UTC时间）

1
2
3
4
5
6
7
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2022-07-07 04:56:10 |
+---------------------+
1 row in set (0.00 sec)

D. YEAR , MONTH , DAY：当前年、月、日

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> select YEAR(now());
+-------------+
| YEAR(now()) |
+-------------+
|        2022 |
+-------------+
1 row in set (0.00 sec)

mysql> select MONTH(now());
+--------------+
| MONTH(now()) |
+--------------+
|            7 |
+--------------+
1 row in set (0.00 sec)

mysql> select DAY(now());
+------------+
| DAY(now()) |
+------------+
|          7 |
+------------+
1 row in set (0.00 sec)

E. date_add：增加指定的时间间隔

1
2
3
4
5
6
7
mysql> select date_add(now(), INTERVAL 70 YEAR );
+------------------------------------+
| date_add(now(), INTERVAL 70 YEAR ) |
+------------------------------------+
| 2092-07-07 04:58:40                |
+------------------------------------+
1 row in set (0.00 sec)

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

1
2
3
4
5
6
7
mysql> select datediff('2021-10-01', '2021-12-01');
+--------------------------------------+
| datediff('2021-10-01', '2021-12-01') |
+--------------------------------------+
|                                  -61 |
+--------------------------------------+
1 row in set (0.00 sec)

案例：
查询所有员工的入职天数，并根据入职天数倒序排序。
思路： 入职天数，就是通过当前日期 - 入职日期，所以需要使用datediff函数来完成。

1
select name, datediff(curdate(), entrydate) as 'entrydays' from emp order by entrydays desc;

流程函数

流程函数也是很常用的一类函数，可以在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默认值

演示如下：
A. if

1
2
3
4
5
6
7
mysql> select if(false, 'Ok', 'Error');
+--------------------------+
| if(false, 'Ok', 'Error') |
+--------------------------+
| Error                    |
+--------------------------+
1 row in set (0.01 sec)

B. ifnull

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> select ifnull('Ok','Default');
+------------------------+
| ifnull('Ok','Default') |
+------------------------+
| Ok                     |
+------------------------+
1 row in set (0.00 sec)

mysql> select ifnull('','Default');
+----------------------+
| ifnull('','Default') |
+----------------------+
|                      |
+----------------------+
1 row in set (0.00 sec)

mysql> select ifnull(null,'Default');
+------------------------+
| ifnull(null,'Default') |
+------------------------+
| Default                |
+------------------------+
1 row in set (0.00 sec)

C. case when then else end
需求: 查询emp表的员工姓名和工作地址 (北京/上海 —-> 一线城市 , 其他 —-> 二线城市)

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

案例:

各科考试成绩转换为优秀、及格、不及格来表示

1
2
3
4
5
6
7
8
9
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);

具体的SQL语句如下:

1
2
3
4
5
6
7
8
9
10
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;

思考：

1). 数据库中，存储的是入职日期，如 2000-01-01，如何快速计算出入职天数呢？ ——–>
答案: datediff
2). 数据库中，存储的是学生的分数值，如98、75，如何快速判定分数的等级呢？ ———->
答案: case ... when ...

Mysql系列文章

  1. mysql系列I-概述

  2. mysql系列II-sql语法

    ①. mysql系列II-sql语法-(1)-概述

    ②. mysql系列II-sql语法-(2)-DDL

    ③. mysql系列II-sql语法-(3)-DML

    ④. mysql系列II-sql语法-(4)-DQL

    ⑤. mysql系列II-sql语法-(5)-DCL

  3. mysql系列III-函数

  4. mysql系列IV-约束

  5. mysql系列V-多表查询

  6. mysql系列VI-事务

  7. mysql系列VII-存储引擎

  8. mysql系列VIII-索引

    ①. mysql系列VIII-索引(1)-概述

    ②. mysql系列VIII-索引(2)-结构

    ③. mysql系列VIII-索引(3)-分类

    ④. mysql系列VIII-索引(4)-语法

    ⑤. mysql系列VIII-索引(5)-SQL性能分析

    ⑥. mysql系列VIII-索引(6)-索引使用

    ⑦. mysql系列VIII-索引(7)-设计原则

  9. mysql系列IX-SQL优化

  10. mysql系列X-视图

  11. mysql系列XI-存储过程

  12. mysql系列XII-存储函数

  13. mysql系列XIII-触发器

  14. mysql系列XIV-锁