MySQL 函数

函数

函数是一段已经被编写好，可以直接使用的代码片段。MySQL 中内置了很多的函数，我们可以使用这些函数来完成一些简单的任务。

字符串函数

常用字符串函数

函数	描述
CONCAT(str1, str2, ...)	连接字符串
LOWER(str)	将字符串转换为小写
UPPER(str)	将字符串转换为大写
LPAD(str, len, pad_str)	左填充字符串
RPAD(str, len, pad_str)	右填充字符串
TRIM(str)	去掉字符串两侧的空格
SUBSTRING(str, start, len)	截取字符串
REPLACE(str, old_str, new_str)	替换字符串
LENGTH(str)	返回字符串的长度
LOCATE(substr, str)	返回子字符串在字符串中的位置
INSTR(str, substr)	检测子字符串在字符串中的位置
REVERSE(str)	反转字符串

字符串函数示例

▼
sql
复制代码
-- 1. 字符串拼接
SELECT CONCAT('Hello', 'MySQL');
-- Hello MySQL

-- 2. 转小写
SELECT LOWER('HELLO');
-- hello

-- 3. 转大写
SELECT UPPER('hello');
-- HELLO

-- 4. 左填充
SELECT LPAD('1', 5, '0');
-- 00001

-- 5. 右填充
SELECT RPAD('1', 5, '0');
-- 10000

-- 6. 去除空格
-- 只去除两侧的空格
SELECT TRIM('  Hello MySQL   ');
-- Hello MySQL

-- 7. 截取
-- 索引从 1 开始，从 2 开始，截取 4 个
SELECT SUBSTRING('Hello MySQL', 2, 4);
-- ello

-- 左截取
SELECT LEFT('Hello MySQL', 5);
-- Hello

-- 右截取
SELECT RIGHT('Hello MySQL', 5);
-- MySQL

-- 8. 替换
SELECT REPLACE('Hello MySQL', 'l', 'A');
-- HeAAo MySQL

-- 9. 字符串长度
SELECT LENGTH('Hello MySQL');
-- 11

-- 10. 查询字符在字符串中位置
SELECT LOCATE('l', 'Hello MySQL'); -- 3

SELECT INSTR('Hello MySQL', 'l');
-- 3

-- 11. 反转字符串
SELECT REVERSE('Hello MySQL'); -- LQSyM olleH

字符串函数练习

▼
sql
复制代码
-- 1. 更新员工工号，不足五位用 0 补足
UPDATE tb_employee
SET work_no = LPAD(work_no, 5, '0');

数值函数

常用数值函数

函数	描述
CELL(x)	向上取整
FLOOR(x)	向下取整
ROUND(x, d)	四舍五入
MOD(x, y)	返回 x 除以 y 的余数
RAND()	返回一个 0 ~ 1 的随机数
ABS(x)	返回 x 的绝对值
POW(x, y)	返回 x 的 y 次方
SQRT(x)	返回 x 的平方根
LOG(x)	返回 x 的自然对数

数值函数示例

▼
SQL
复制代码
-- 1. 向上取整
SELECT CEIL(1.1);
-- 2

-- 2. 向下取整
SELECT FLOOR(1.9);
-- 1

-- 3. 四舍五入
SELECT ROUND(1.4); -- 1
SELECT ROUND(1.5);
-- 2

-- 4. 加减乘除取余
SELECT 5 + 3;
SELECT 5 - 3;
SELECT 5 * 3;
SELECT 5 / 3;
SELECT 5 % 3;

SELECT MOD(5, 3);

-- 5. 随机数
SELECT RAND();

-- 6. 绝对值
SELECT ABS(-1);
-- 1

-- 7. 幂次
SELECT POW(3, 2);
-- 9

-- 8. 平方根
SELECT SQRT(9);
-- 3

-- 9. 自然对数
SELECT LOG2(4); -- 2

数值函数练习

▼
sql
复制代码
-- 1. 生成 6 位的随机数值
SELECT LPAD(ROUND(RAND() * 1000000, 0), 6, '0');

日期函数

常用日期函数

函数	描述
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前日期和时间
YEAR(date)	获取日期的年份
MONTH(date)	获取日期的月份
DAY(date)	获取日期的日
HOUR(time)	获取时间中的小时
MINUTE(time)	获取时间中的分钟
SECOND(time)	获取时间中的秒钟
DATE_ADD(date, INTERVAL x UNIT)	添加时间间隔
DATE_SUB(date, INTERVAL x UNIT)	减去时间间隔
DATE_FORMAT(date, format)	格式化日期
DATEDIFF(date1, date2)	计算两个日期之间的天数

日期函数示例

▼
sql
复制代码
-- 1. 当前日期
SELECT CURDATE();
SELECT CURRENT_DATE;

-- 2. 当前时间
SELECT CURTIME();
SELECT CURRENT_TIME;

-- 3. 当前日期+时间
SELECT NOW();

-- 4. 年月日时分秒
SELECT YEAR(CURRENT_DATE);
SELECT MONTH(CURRENT_DATE);
SELECT DAY(CURRENT_DATE);
SELECT HOUR(CURRENT_TIME);
SELECT SECOND(CURRENT_TIME);
SELECT MINUTE(CURRENT_TIME);

-- 5. 时间增减
SELECT DATE_ADD(CURRENT_DATE, INTERVAL 30 DAY);
SELECT DATE_ADD(CURRENT_DATE, INTERVAL 1 MONTH);

SELECT DATE_SUB(CURRENT_TIME, INTERVAL 20 HOUR);
SELECT DATE_SUB(CURRENT_DATE, INTERVAL 20 YEAR);

-- 6. 格式化
SELECT DATE_FORMAT(NOW(), '%Y/%m/%d %H:%i:%s');

-- 7. 想差天数
SELECT DATEDIFF(CURRENT_DATE, '2000-11-28');

日期函数练习

▼
sql
复制代码
-- 1. 根据入职时间计算天数，并按天数降序排序
SELECT name, DATEDIFF(CURRENT_DATE, join_date) AS days
FROM tb_employee
ORDER BY days DESC;

流程函数

常用流程函数

函数	功能
IF(expr1, expr2, expr3)	如果expr1为真，则返回expr2，否则返回expr3
IFNULL(expr1, expr2)	如果expr1为NULL，则返回expr2，否则返回expr1
CASE WHEN expr1 THEN result1 [WHEN expr2 THEN result2 ...] [ELSE result_final] END	根据条件返回相应的结果，相当于 if...else...
CASE expr [WHEN value THEN result [WHEN value THEN result ...]] [ELSE result_final] END	根据表达式的值返回相应的结果，相当于 switch...case...

常用流程函数示例

▼
sql
复制代码
-- 1, IF
SELECT IF(age > 18, '成年', '未成年')
FROM tb_employee;

-- 2. IFNULL
SELECT IFNULL(id_card, '没有身份证')
FROM tb_employee;

-- 3. CASE
SELECT CASE
           WHEN work_address = '北京' THEN CONCAT(work_address, '-首都')
           WHEN work_address IN ('上海', '深圳') THEN CONCAT(work_address, '-一线城市')
           ELSE work_address
           END
FROM tb_employee;


SELECT CASE work_address
           WHEN '北京' THEN '首都'
           WHEN '上海' THEN '一线城市'
           WHEN '深圳' THEN '一线城市'
           ELSE work_address
           END
FROM tb_employee;

流程函数练习

▼
sql
复制代码
INSERT INTO score VALUES (1, '林洁', CEIL(RAND() * 100), CEIL(RAND() * 100), CEIL(RAND() * 100));
INSERT INTO score VALUES (2, '邱慧', CEIL(RAND() * 100), CEIL(RAND() * 100), CEIL(RAND() * 100));
INSERT INTO score VALUES (3, '蔡淑', CEIL(RAND() * 100), CEIL(RAND() * 100), CEIL(RAND() * 100));
INSERT INTO score VALUES (4, '孟巧', CEIL(RAND() * 100), CEIL(RAND() * 100), CEIL(RAND() * 100));
INSERT INTO score VALUES (5, '贺云', CEIL(RAND() * 100), CEIL(RAND() * 100), CEIL(RAND() * 100));
INSERT INTO score VALUES (6, '程良', CEIL(RAND() * 100), CEIL(RAND() * 100), CEIL(RAND() * 100));
INSERT INTO score VALUES (7, '白兰', CEIL(RAND() * 100), CEIL(RAND() * 100), CEIL(RAND() * 100));
INSERT INTO score VALUES (8, '贾保', CEIL(RAND() * 100), CEIL(RAND() * 100), CEIL(RAND() * 100));
INSERT INTO score VALUES (9, '韩欣霞', CEIL(RAND() * 100), CEIL(RAND() * 100), CEIL(RAND() * 100));
INSERT INTO score VALUES (10, '汤静珍', CEIL(RAND() * 100), CEIL(RAND() * 100), CEIL(RAND() * 100));

-- 1, 根据学生成绩对学生进行评价
-- x > 90 优秀
-- 90 > x > 80 良好
-- 80 > x > 70 一般
-- 70 > x > 60 合格
-- 60 > x 不合格

SELECT name,
       CASE
           WHEN math > 90 THEN '优秀'
           WHEN math BETWEEN 80 AND 90 THEN '良好'
           WHEN math BETWEEN 70 AND 80 THEN '一般'
           WHEN math BETWEEN 60 AND 70 THEN '合格'
           ELSE '不合格'
           END AS '数学成绩',
       CASE
           WHEN english > 90 THEN '优秀'
           WHEN english BETWEEN 80 AND 90 THEN '良好'
           WHEN english BETWEEN 70 AND 80 THEN '一般'
           WHEN english BETWEEN 60 AND 70 THEN '合格'
           ELSE '不合格'
           END AS '英语成绩',
       CASE
           WHEN chinese > 90 THEN '优秀'
           WHEN chinese BETWEEN 80 AND 90 THEN '良好'
           WHEN chinese BETWEEN 70 AND 80 THEN '一般'
           WHEN chinese BETWEEN 60 AND 70 THEN '合格'
           ELSE '不合格'
           END AS '语文成绩'
FROM score;