MySQL学习笔记：3. 函数

__MrHao

已于 2024-09-27 16:50:33 修改

阅读量971

点赞数 11

分类专栏： MySQL 文章标签：笔记 mysql 数据库

于 2024-09-27 16:39:50 首次发布

本文链接：https://blog.csdn.net/Mr_Taurus/article/details/142596754

版权

MySQL 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

一、概述

函数：是指一段可以直接被另一端程序调用的程序或代码。

函数有什么作用？什么时候会用到函数呢？

比如，OA系统数据库表中，存储的是员工入职时间，如2000-11-12，如何快速计算入职天数？

比如，学生成绩数据库表中，存储的是学生的分数值，如98、75，如何快速判定分数的等级呢？

本章内容：

字符串函数
数值函数
日期函数
流程控制函数

二、字符串函数

MySQL函数内置了很多字符串函数，常用的函数如下：

CONCAT(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) 去掉字符串str头部和尾部的空格

SUBSTRING(str, start, len) 返回从字符串str从start位置起的len个长度的字符串 #注意:索引从1开始，而不是0

字符串函数示例

-- ---------- 字符串函数 ---------
# CONCAT
SELECT CONCAT('Hello ', 'MySQL');

# LOWER
SELECT LOWER('Hello');
# 输出: hello

# UPPER
SELECT UPPER('Hello');
# 输出：HELLO

# LPAD
SELECT LPAD('MySQL', 10, '-*');
# 输出：-*-*-MySQL

# RPAD
SELECT RPAD('MySQL', 10, '-*');
# 输出：MySQL-*-*-

# TRIM
SELECT TRIM(' Hello MySQL!  ');
# 输出：Hello MySQL!

# SUBSTRING
SELECT SUBSTRING('Hello MySQL!', 7, 5); #注意:索引从1开始，而不是0
# 输出：MySQL

案例

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

-- 案例
# 由于业务需求变更，企业员工的工号，统一为5位数，目前不足5位数的全部在前面补0。比如1号员工的工号应为00001.
USE my_database;
UPDATE emp SET worker_id = LPAD(worker_id, 5, '0');
SELECT * FROM emp;

三、数值函数

常见的数值函数：

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

数值函数示例

-- ---------- 数值函数 ----------

# CEIL
SELECT CEIL(1.1);
# 输出：2

# FLOOR
SELECT FLOOR(1.8);
# 输出：1

# MOD
SELECT MOD(11, 4);
# 输出：3

# RAND
SELECT RAND();
# 输出一个0至1的随机小数

# ROUND
SELECT ROUND(3.1415926, 2);
# 输出：3.14
SELECT ROUND(3.1415926, 3);
# 输出：3.142

案例

通过数据库函数，生成一个随机6位的验证码。

-- 案例：通过数据库函数，生成一个随机6位的验证码。
SELECT LPAD(ROUND(RAND()*100000), 6, 0);
# 输出：098023（或者其他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之间的天数（第一个时间减第二个时间）

日期函数示例：

-- ---------- 日期函数 ----------
# 当前日期
SELECT CURDATE();

# 当前时间
SELECT CURTIME();

# 获取当前日期和时间
SELECT NOW();

# 获取当前年
SELECT YEAR(NOW());

# 获取当前月
SELECT MONTH(NOW());

# 获取当前日
SELECT DAY(NOW());

# 30天后的日期
SELECT DATE_ADD(CURDATE(), INTERVAL 30 DAY);

# 24个月后的日期
SELECT DATE_ADD(CURDATE(), INTERVAL 24 MONTH );

# 3600秒后的时间
SELECT DATE_ADD(NOW(), INTERVAL 3600 SECOND );

# 计算多少天后，是2030年10月18日
SELECT DATEDIFF(DATE('2030-10-18'), NOW());

# 计算2000年1月1日，距离今天多少天了
SELECT DATEDIFF(CURDATE(), DATE('2000-01-01'));

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

-- 案例：查询所有员工的入职天数，并根据入职天数倒序排序。
USE my_database;
SELECT name, DATEDIFF(CURDATE(), entry_date) AS entry_days FROM emp ORDER BY entry_days DESC;

五、流程控制函数

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默认值

流程函数示例及案例：

-- ---------- 流程控制函数 ----------

-- IF
SELECT IF(TRUE, 'VALUE:TRUE', 'VALUE:FALSE');

-- IFNULL
SELECT IFNULL('Hello', 'Default');
SELECT IFNULL(100, 0);
SELECT IFNULL(null, 'Default');



-- 案例1
-- 需求：在emp表中查找员工的姓名和工作地址，如果如果地址是北上广深，则展示一线城市，否则展示二线城市
USE my_database;

-- CASE WHEN THEN ELSE END
SELECT name, work_address,
       CASE work_address WHEN '北京' THEN '一线城市'
WHEN '上海' THEN '一线城市'
WHEN '广州' THEN '一线城市'
WHEN '深圳' THEN '一线城市'
ELSE '二线城市' END AS '城市等级' FROM emp;

# 也可以用IF条件
SELECT name, work_address,
       IF(work_address IN ('北京', '上海', '广州', '深圳'), '一线城市', '二线城市') AS '城市等级'
FROM emp;

-- 案例2
-- 统计班级各个学员的成绩，展示规则如下：
# * >= 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, 85, 95),
       (2, 'Rose', 44, 66, 90),
       (3, 'Jack', 56, 98, 76);

# 检查
SELECT * FROM score;

SELECT name,
       CONCAT(math, ' ', CASE WHEN math>=85 THEN '优秀' WHEN math>=60 THEN '及格' ELSE '不及格' END) AS '数学',
       CONCAT(english, ' ', CASE WHEN english>=85 THEN '优秀' WHEN english>=60 THEN '及格' ELSE '不及格' END) AS '英语',
       CONCAT(chinese, ' ', CASE WHEN chinese>=85 THEN '优秀' WHEN chinese>=60 THEN '及格' ELSE '不及格' END) AS '语文'
FROM score;