前言
MySQL 中的函数是一种重用 SQL 语句逻辑的方法，可以让开发者自定义并封装一些常用的数据处理逻辑，以便在查询、更新或删除等操作过程中进行调用。MySQL 提供了多种类型的函数，包括标量函数（Scalar Functions）、聚合函数（Aggregate Functions）、表值函数（Table-Valued Functions）等。
测试数据
本文使用下面这张表进行测试
CREATE TABLE TB_EMPLOYEE ( ID INT COMMENT '编号', WORK_NO VARCHAR(10) COMMENT '工号', NAME VARCHAR(10) COMMENT '姓名', GENDER CHAR COMMENT '性别', AGE TINYINT UNSIGNED COMMENT '年龄', ID_CARD CHAR(18) COMMENT '身份证号', WORK_ADDRESS VARCHAR(50) COMMENT '工作地址', ENTRY_DATE DATE COMMENT '入职时间' ) COMMENT '员工表' [2024-07-09 11:15:45] 12ms 执行完毕 emps INSERT INTO TB_EMPLOYEE (ID, WORK_NO, NAME, GENDER, AGE, ID_CARD, WORK_ADDRESS, ENTRY_DATE) VALUES (1, '1', '柳岩', '女', 20, '123456789012345678', '北京', '2000-01-01'), (2, '2', '张无忌', '男', 18, '123456789012345670', '北京', '2005-09-01'), (3, '3', '韦一笑', '男', 38, '123456789012345670', '上海', '2005-08-01'), (4, '4', '赵敏', '女', 18, '123456789012345670', '北京', '2009-12-01'), (5, '5', '小昭', '女', 16, '123456789012345678', '上海', '2007-07-01'), (6, '6', '杨逍', '男', 28, '12345678901234567X', '北京', '2006-01-01'), (7, '7', '范瑶', '男', 40, '123456789012345670', '北京', '2005-05-01'), (8, '8', '黛绮丝', '女', 38, '123456789012345670', '天津', '2015-05-01'), (9, '9', '范凉凉', '女', 45, '123456789012345678', '北京', '2010-04-01'), (10, '10', '陈友谅', '男', 53, '123456789012345670', '上海', '2011-01-01'), (11, '11', '张士诚', '男', 55, '123456789012345670', '江苏', '2015-05-01'), (12, '12', '常遇春', '男', 32, '123456789012345670', '北京', '2004-02-01'), (13, '13', '张三丰', '男', 88, '123456789012345678', '江苏', '2020-11-01'), (14, '14', '灭绝', '女', 65, '123456789012345670', '西安', '2019-05-01'), (15, '15', '胡青牛', '男', 70, '12345678901234567X', '西安', '2018-04-01'), (16, '16', '周芷若', '女', 18, NULL, '北京', '2012-06-01')
一、字符串函数
| 函数 | 功能 |
|---|---|
| CONCAT(S1,S,..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个长度的字符串 |
测试
CONCAT(S1,S,..Sn)
三个字符串被拼接为一个字符串
SELECT CONCAT("hello","mysql","test")
LOWER(str)
所有大写字母都被转为了小写
SELECT LOWER("TEst")
UPPER(str)
所有的小写字母都被转为了大写
SELECT UPPER("test")
LPAD(str,n,pad)
左补位了3个'-'，以此达到长度5
SELECT LPAD("01",5,"-")
RPAD(str,n,pad)
右补位了3个'-'，以此达到长度5
SELECT RPAD("01",5,"-")
TRIM(str)
字符串的头尾的空格被去掉
SELECT TRIM(' hello mysql ')
SUBSTRING(str,Start,len)
从第一个字符截取到第五个字符
SELECT SUBSTRING('helloMysql',1,5)
注意：此处截取的索引是从1开始的
练习
由于业务需求变更，企业员工的工号，统一为5位数，目前不足5位数的全部在前面补0。比如：1号员工的工号应该为00001。
UPDATE tb_employee set WORK_NO=LPAD(WORK_NO,5,"0")
二、数值函数
| 函数 | 功能 |
|---|---|
| CEIL(x) | 向上取整 |
| FLOOR(x) | 向下取整 |
| MOD(x,y) | 返回x/y的模 |
| RAND() | 返回0~1内的随机数 |
| ROUND(x,y) | 求参数x的四舍五入的值，保留y位小数 |
测试
CEIL(x)
传入该函数的值都被==向上取整==
SELECT CEIL(1.3),CEIL(1.6)
FLOOR(x)
传入该函数的值都被==向上取整==
SELECT FLOOR(1.3),FLOOR(1.6)
MOD(x,y)
取模
SELECT MOD(3,4),MOD(7,2)
RAND()
返回从0-1的随机数
SELECT RAND()
ROUND(x,y)
保留传入参数的两位小数（四舍五入）
SELECT ROUND(RAND(),2),ROUND(3.167,2)
练习
通过数据库中的函数，生成一个6位数的随机验证码
SELECT LPAD(ROUND(RAND(),6)*1000000,6,"0")
通过随机函数生成一个数并且保留6位小数，然后再乘以1000000，由于随机会得到如0.012345的数，乘以1000000后仍然没有6位数，所以再使用LPAD函数进行左补位，以此得到一个6位的随机数。
三、日期函数
| 函数 | 功能 |
|---|---|
| CURDATE() | 返回当前日期 |
| CURTIME() | 返回当前时间 |
| NOW() | 返回当前日期和时间 |
| YEAR(date) | 获取指定date的年份 |
| MONTH(date) | 获取指定date的月份 |
| DAY(date) | 获取指定date的日期 |
| DATE_ADD(date, INTERVAL expr type) | 返回一个日期/时间值加上一个时间间隔expr后的时间值 |
| DATEDIFF(date1,date2) | 返回起始时间date1和结束时间date2之间的天数 |
测试
CURDATE()
获取当前==日期==
SELECT CURDATE()
CURTIME()
获取当前==时间==
SELECT CURRENT_TIME()
NOW()
获取当前的==日期和时间==
SELECT NOW()
YEAR(date)
获取日期中的年份
SELECT YEAR(CURDATE())
MONTH(date)
获取日期中的月份
SELECT CURDATE(),MONTH(CURDATE())
DAY(date)
获取日期中的日份
SELECT CURDATE(),DAY(CURDATE())
DATE_ADD(date, INTERVAL expr type)
获取间隔日期
SELECT CURDATE(),DATE_ADD(CURDATE(), INTERVAL 5 YEAR),DATE_ADD(CURDATE(), INTERVAL 5 MONTH)
DATEDIFF(date1,date2)
获取两个日期的间隔天数
SELECT CURDATE(),DATE_ADD(CURDATE(), INTERVAL 5 YEAR),DATEDIFF(DATE_ADD(CURDATE(), INTERVAL 5 YEAR),CURDATE())
练习
查询所有员工的入职天数，并根据入职天数倒序排序
SELECT NAME ,DATEDIFF(CURDATE(),ENTRY_DATE) AS ENTRYDAY FROM tb_employee ORDER BY ENTRYDAY DESC
四、流程函数
| 函数 | 功能 |
|---|---|
| IF(value ,t, f) | 如果value为true，则返回t，否则返回f |
| IFNULL(value1 , value2) | 如果value1不为空，返回value1，否则返回value |
| CASE WHEN [ val1 ] THEN [res1] ... ELSE [ default ] END | 如果val1为true，返回res1，...否则返回default默认值 |
| CASE [ expr ] WHEN [val1 ] THEN [res1] ... ELSE [ default ] END | 如果expr的值等于vall，返回res1，...否则返回default默认值 |
测试
IF(value ,t, f)
如果第一个参数为TRUE则返回第二个参数，如果第一个参数为FALSE则返回第三个参数
SELECT IF(TRUE,'a','b'),IF(FALSE,'a','b')
IFNULL(value1 , value2)
如果第一个参数不为空则返回第一个参数，如果第一个参数为空则返回第二个参数
SELECT IFNULL('a','b'),IFNULL(NULL,'c')
CASE WHEN [ val1 ] THEN [res1] ... ELSE [ default ] END
查询emp表的员工姓名和工作地址（北京/上海---->一线城市，其他---->二线城市)
SELECT NAME, CASE WORK_ADDRESS WHEN '北京' THEN '一线城市' WHEN '上海' THEN '一线城市' ELSE '二线城市' END AS '城市' FROM tb_employee
CASE [ expr ] WHEN [val1 ] THEN [res1] ... ELSE [ default ] END
统计班级各个学员的成绩，展示的规则如下：>=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 name, (CASE WHEN math >=85 THEN '优秀' WHEN math>=60 THEN '及格' ELSE '不及格' END) AS '数学', (CASE WHEN english >=85 THEN '优秀' WHEN english>=60 THEN '及格' ELSE '不及格'END) AS '英语', (CASE WHEN chinese >=85 THEN '优秀' WHEN chinese>=60 THEN '及格' ELSE '不及格'END) AS '语文' FROM score
