中间件
MySQL
基础教程

MySQL的函数

# MySQL的函数

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

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 : 字符串拼接

MariaDB [(none)]> select concat('Hello','MySQL');
+-------------------------+
| concat('Hello','MySQL') |
+-------------------------+
| HelloMySQL              |
+-------------------------+
1 row in set (0.000 sec)

B. lower : 全部转小写

MariaDB [(none)]> select lower('Hello');
+----------------+
| lower('Hello') |
+----------------+
| hello          |
+----------------+
1 row in set (0.000 sec)

C. upper : 全部转大写

MariaDB [(none)]> select upper('Hello');
+----------------+
| upper('Hello') |
+----------------+
| HELLO          |
+----------------+
1 row in set (0.000 sec)

D. lpad : 左填充

MariaDB [(none)]> select lpad('01',5,'-');
+------------------+
| lpad('01',5,'-') |
+------------------+
| ---01            |
+------------------+
1 row in set (0.000 sec)

E. rpad : 右填充

MariaDB [(none)]> select rpad('01',5,'---');
+--------------------+
| rpad('01',5,'---') |
+--------------------+
| 01---              |
+--------------------+
1 row in set (0.000 sec)

F. trim : 去除空格

MariaDB [(none)]> select trim(' hello MySQL');
+----------------------+
| trim(' hello MySQL') |
+----------------------+
| hello MySQL          |
+----------------------+
1 row in set (0.000 sec)

G. substring : 截取子字符串

MariaDB [(none)]> select substring('hello MySQL',1,5);
+------------------------------+
| substring('hello MySQL',1,5) |
+------------------------------+
| hello                        |
+------------------------------+
1 row in set (0.000 sec)

案例:

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

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

MariaDB [test]> update emp set workno = lpad(workno,5,'0');
Query OK, 0 rows affected (0.000 sec)
Rows matched: 16  Changed: 0  Warnings: 0

MariaDB [test]> select * from emp;
+------+--------+-----------+--------+------+--------------------+-------------+------------+
| id   | workno | name      | gender | age  | idcard             | workaddress | entrydate  |
+------+--------+-----------+--------+------+--------------------+-------------+------------+
|    1 | 00001  | 柳岩666   | 女     |   20 | 123456789012345678 | 北京        | 2000-01-01 |
|    2 | 00002  | 张无忌    | 男     |   18 | 123456789012345670 | 北京        | 2005-09-01 |
|    3 | 00003  | 韦一笑    | 男     |   38 | 123456789712345670 | 上海        | 2005-08-01 |
|    4 | 00004  | 赵敏      | 女     |   18 | 123456757123845670 | 北京        | 2009-12-01 |
|    5 | 00005  | 小昭      | 女     |   16 | 123456769012345678 | 上海        | 2007-07-01 |
|    6 | 00006  | 杨逍      | 男     |   28 | 12345678931234567X | 北京        | 2006-01-01 |
|    7 | 00007  | 范瑶      | 男     |   40 | 123456789212345670 | 北京        | 2005-05-01 |
|    8 | 00008  | 黛绮丝    | 女     |   38 | 123456157123645670 | 天津        | 2015-05-01 |
|    9 | 00009  | 范凉凉    | 女     |   45 | 123156789012345678 | 北京        | 2010-04-01 |
|   10 | 00010  | 陈友谅    | 男     |   53 | 123456789012345670 | 上海        | 2011-01-01 |
|   11 | 00011  | 张士诚    | 男     |   55 | 123567897123465670 | 江苏        | 2015-05-01 |
|   12 | 00012  | 常遇春    | 男     |   32 | 123446757152345670 | 北京        | 2004-02-01 |
|   13 | 00013  | 张三丰    | 男     |   88 | 123656789012345678 | 江苏        | 2020-11-01 |
|   14 | 00014  | 灭绝      | 女     |   65 | 123456719012345670 | 西安        | 2019-05-01 |
|   15 | 00015  | 胡青牛    | 男     |   70 | 12345674971234567X | 西安        | 2018-04-01 |
|   16 | 00016  | 周芷若    | 女     |   18 | NULL               | 北京        | 2012-06-01 |
+------+--------+-----------+--------+------+--------------------+-------------+------------+
16 rows in set (0.000 sec)

# 数值函数

常见的数值函数如下：

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

演示如下：

A. ceil：向上取整

MariaDB [test]> select ceil(1.1);
+-----------+
| ceil(1.1) |
+-----------+
|         2 |
+-----------+
1 row in set (0.000 sec)

B. floor：向下取整

MariaDB [test]> select floor(1.9);
+------------+
| floor(1.9) |
+------------+
|          1 |
+------------+
1 row in set (0.000 sec)

C. mod：取模

MariaDB [test]> select mod(7,4);
+----------+
| mod(7,4) |
+----------+
|        3 |
+----------+
1 row in set (0.000 sec)

D. rand：获取随机数

MariaDB [test]> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.5543058836407083 |
+--------------------+
1 row in set (0.000 sec)

E. round：四舍五入

MariaDB [test]> select round(2.345,2);
+----------------+
| round(2.345,2) |
+----------------+
|           2.35 |
+----------------+
1 row in set (0.000 sec)

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

思路：获取随机数可以通过rand()函数，但是获取出来的随机数是在0-1之间的，所以可以在其基础上乘以1000000，然后舍弃小数部分，如果长度不足6位，补0

MariaDB [test]> select lpad(round(rand()*1000000,0),6,'0');
+-------------------------------------+
| lpad(round(rand()*1000000,0),6,'0') |
+-------------------------------------+
| 091292                              |
+-------------------------------------+
1 row in set (0.000 sec)

# 日期函数

常见的日期函数如下：

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

演示如下：

A. curdate：当前日期

MariaDB [test]> select curdate();
+------------+
| curdate()  |
+------------+
| 2022-06-10 |
+------------+
1 row in set (0.000 sec)

B. curtime：当前时间

MariaDB [test]> select curtime();
+-----------+
| curtime() |
+-----------+
| 15:17:05  |
+-----------+
1 row in set (0.000 sec)

C. now：当前日期和时间

MariaDB [test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2022-06-10 15:17:10 |
+---------------------+
1 row in set (0.000 sec)

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

MariaDB [test]> select year(now());
+-------------+
| year(now()) |
+-------------+
|        2022 |
+-------------+
1 row in set (0.000 sec)

MariaDB [test]> select month(now());
+--------------+
| month(now()) |
+--------------+
|            6 |
+--------------+
1 row in set (0.000 sec)

MariaDB [test]> select day(now());
+------------+
| day(now()) |
+------------+
|         10 |
+------------+
1 row in set (0.000 sec)

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

MariaDB [test]> select date_add(now(), interval 70 day);
+----------------------------------+
| date_add(now(), interval 70 day) |
+----------------------------------+
| 2022-08-19 15:20:46              |
+----------------------------------+
1 row in set (0.000 sec)

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

MariaDB [test]> select datediff('2021-10-01','2021-12-30');
+-------------------------------------+
| datediff('2021-10-01','2021-12-30') |
+-------------------------------------+
|                                 -90 |
+-------------------------------------+
1 row in set (0.000 sec)

案例：

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

思路：入职天数，就是通过当前日期 - 入职日期，所以需要使用datediff函数来完成。

MariaDB [test]> select name,datediff(curdate(),entrydate) as 'entrydays' from emp order by entrydays desc; 
+-----------+-----------+
| name      | entrydays |
+-----------+-----------+
| 柳岩666   |      8196 |
| 常遇春    |      6704 |
| 范瑶      |      6249 |
| 韦一笑    |      6157 |
| 张无忌    |      6126 |
| 杨逍      |      6004 |
| 小昭      |      5458 |
| 赵敏      |      4574 |
| 范凉凉    |      4453 |
| 陈友谅    |      4178 |
| 周芷若    |      3661 |
| 张士诚    |      2597 |
| 黛绮丝    |      2597 |
| 胡青牛    |      1531 |
| 灭绝      |      1136 |
| 张三丰    |       586 |
+-----------+-----------+
16 rows in set (0.000 sec)

# 流程函数

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

MariaDB [test]> select if(true,'OK','Error');
+-----------------------+
| if(true,'OK','Error') |
+-----------------------+
| OK                    |
+-----------------------+
1 row in set (0.000 sec)

B. ifnull

MariaDB [test]> select ifnull('OK','Default');
+------------------------+
| ifnull('OK','Default') |
+------------------------+
| OK                     |
+------------------------+
1 row in set (0.000 sec)

MariaDB [test]> select ifnull('','Default');
+----------------------+
| ifnull('','Default') |
+----------------------+
|                      |
+----------------------+
1 row in set (0.000 sec)

MariaDB [test]> select ifnull(null,'Default');
+------------------------+
| ifnull(null,'Default') |
+------------------------+
| Default                |
+------------------------+
1 row in set (0.000 sec)

C. case when then else end

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

案例:

MariaDB [test]> select 
    -> name,
    -> (case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '工作地址' 
    -> from emp;
+-----------+--------------+
| name      | 工作地址     |
+-----------+--------------+
| 柳岩666   | 一线城市     |
| 张无忌    | 一线城市     |
| 韦一笑    | 一线城市     |
| 赵敏      | 一线城市     |
| 小昭      | 一线城市     |
| 杨逍      | 一线城市     |
| 范瑶      | 一线城市     |
| 黛绮丝    | 二线城市     |
| 范凉凉    | 一线城市     |
| 陈友谅    | 一线城市     |
| 张士诚    | 二线城市     |
| 常遇春    | 一线城市     |
| 张三丰    | 二线城市     |
| 灭绝      | 二线城市     |
| 胡青牛    | 二线城市     |
| 周芷若    | 一线城市     |
+-----------+--------------+
16 rows in set (0.000 sec)

创建学员成绩表：

MariaDB [test]> create table score(
    id int comment 'ID',
    name varchar(20) comment '姓名',
    math int comment '数学',
    english int comment '英语',
    chinese int comment '语文'
) comment '学员成绩表';
Query OK, 0 rows affected (0.004 sec)

MariaDB [test]> insert into score(id,name,math,english,chinese) values(1,'Tom',67,88,95),(2,'Rose',23,66,90),(3,'Jack',56,98,76);
Query OK, 3 rows affected (0.001 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [test]> select * from score;
+------+------+------+---------+---------+
| id   | name | math | english | chinese |
+------+------+------+---------+---------+
|    1 | Tom  |   67 |      88 |      95 |
|    2 | Rose |   23 |      66 |      90 |
|    3 | Jack |   56 |      98 |      76 |
+------+------+------+---------+---------+
3 rows in set (0.000 sec)

统计班级各个学院的成绩，展示的规则如下：

>=85 展示优秀

>=60 展示及格

否则，展示不及格

MariaDB [test]> 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;
+------+------+-----------+--------+--------+
| id   | name | 数学      | 英语   | 语文   |
+------+------+-----------+--------+--------+
|    1 | Tom  | 及格      | 优秀   | 优秀   |
|    2 | Rose | 不及格    | 及格   | 优秀   |
|    3 | Jack | 不及格    | 优秀   | 及格   |
+------+------+-----------+--------+--------+
3 rows in set (0.000 sec)

MySQL的常见函数我们学习完了，那接下来，我们就来分析一下，在前面讲到的两个函数的案例场景，思考一下需要用到什么样的函数来实现?

1). 数据库中，存储的是入职日期，如 2000-01-01，如何快速计算出入职天数呢？ --------> 答案: datediff

2). 数据库中，存储的是学生的分数值，如98、75，如何快速判定分数的等级呢？ ---------->

答案: case ... when ...

上次更新: 2023/11/28, 22:03:59

← MySQL的SQL详解 MySQL的约束→