实战总结MySQL常见函数

公众号：IT蛋记录员

ITEgg的博客：IT蛋的个人博客 - 一个平凡人的编程旅途

前言

学好SQL的重要性不言而喻，即便越来越多的时候，对数据进行筛选操作的任务落给了service层，SQL优化依旧是优化时大家考虑的首选

在聊SQL优化之前，首先得实现需求。比起不断筛选分组连表，合理使用自带的方法显然更方便达到目的

以下是我学习LeetCode上SQL基础50题后，总结的MySQL自带方法。

在了解这些方法后，想必能帮助你更好的学习和通过SQL基础50题，对SQL爱过错过，总得试试才知道，是吧？

日期
- DATE_FORMAT — 格式化日期
- DATE_ADD — 日期加法函数
- DATEDIFF — 日期减法函数
- NOW — 返回当前日期和时间
- CURDATE — 返回当前日期
- YEAR/MONTH/DAY — 返回日期的年/月/日
- HOUR/MINUTE/SECOND — 返回日期的时/分/秒
判断
- IF — 判断
- IFNULL — 判空
- CASE WHEN — 条件执行
数据
- COUNT — 对查询结果计数
- AVG — 取指定列平均值
- SUM — 取指定列的求和值
- ROUND — 取近似（四舍五入）
- MAX / MIN — 取最大/小值
字符串
- UPPER — 字符串转为全大写
- LOWER — 字符串转为全小写
- CONCAT — 连接两个或多个字符串
- SUBSTRING — 返回字符串字串
- LEFT / RIGHT — 从左侧/右侧开始截取字串
- TRIM — 删除首尾空格或指定字符
- CHAR_LENGTH — 判断字符串长度
COUNT OVER 窗口函数

日期

DATE_FORMAT

SELECT DATE_FORMAT(NOW(), '%Y-%d-%m');		-- 修改日期格式：y年m月d日：现在是2024-04-01
-- 输出
2024-01-04

DATE_ADD

SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);		 -- 日期变更 INTERVAL(间隔) 7天 (可以是负数来往前推)
-- 输出
2024-04-08 11:50:25

DATEDIFF

SELECT DATEDIFF("2024-04-16", "2024-04-10");		 -- 日期差值：前者减后者
-- 输出
6

NOW

SELECT NOW();		--返回当前时间
-- 输出
2024-04-01 08:11:54

CURDATE

SELECT CURDATE();		--返回当前日期
-- 输出
2024-04-01

YEAR / MONTH / DAY

SELECT YEAR('2024-04-01 08:13:44');     -- 获取年
SELECT MONTH('2024-04-01 08:13:44');     -- 获取月
SELECT DAY('2024-04-01 08:13:44');     -- 获取日
-- 输出
2024
4
1

HOUR / MINUTE / SECOND

SELECT HOUR('2024-04-01 08:13:44');     -- 获取小时
SELECT MINUTE('2024-04-01 08:13:44');     -- 获取分钟
SELECT SECOND('2024-04-01 08:13:44');     -- 获取秒
-- 输出
8
13
44

判断

SELECT IF(1 = 1, 1, 0);		-- 判断 1=1 是否成立，成立输出前者
SELECT IF(1 = 2, 1, 0);		-- 判断 1=2 是否成立，不成立输出后者
-- 输出
1
0

IFNULL

SELECT IFNULL(null, 0);			-- 判断第一参数是否为空，为空输出第二参数为默认值
SELECT IFNULL('hello', 0);		-- 判断第一参数是否为空，不为空输出第一参数
-- 输出
0
hello

CASE WHEN

SELECT 
       CASE
           WHEN YEAR(NOW()) = 2024 THEN '现在'
           WHEN YEAR(NOW()) < 2024 THEN '过去'
           WHEN YEAR(NOW()) > 2024 THEN '未来'
           ELSE '不及格'
       END AS year			-- 依据条件进行赋值
-- 输出
现在

数据

数据表：num

id (int)	num (int)	min_num (float)
1	10	1.54
2	20	3.72
3	30	5.55

COUNT

SELECT COUNT(*)
FROM　num;		-- 输出数据量（返回几条数据）
-- 输出
3

AVG

SELECT AVG(num)
FROM num;		-- 取目标字段进行求平均值
-- 输出
20

SUM

SELECT SUM(num)
FROM num;		-- 对目标字段进行求和
-- 输出
60

ROUND

SELECT ROUND(min_num, 1)
FROM num;		-- 四舍五入,保留1位小数
-- 输出
1.5
3.7
5.6

MAX / MIN

SELECT MAX(num)
FROM num;		-- 取目标字段最大值

SELECT MIN(num)
FROM num;		-- 取目标字段最小值
-- 输出
30
10

字符串

UPPER

SELECT UPPER('abCD好efgHiJkl');		-- 输出目标字符串的大写
-- 输出
ABCD好EFGHIJKL

LOWER

SELECT LOWER('abCD好efgHiJkl');		-- 输出目标字符串的大写
-- 输出
abcd好efghijkl

CONCAT

SELECT CONCAT('%','name','%');		-- 拼接目标字符串
-- 输出
%name%

SUBSTRING

SELECT SUBSTRING('ABCDEFGHIJKL', 2, 5);		-- 剪切子串：第2个开始，取后面5个
-- 输出
BCDEF

LEFT / RIGHT

SELECT LEFT('ABCDEFGHIJKL', 4);		-- 取左边4个子串
SELECT RIGHT('ABCDEFGHIJKL', 4);	 -- 取右边4个子串
-- 输出
ABCD
IJKL

TRIM

SELECT TRIM('   ABCD EFGHI JKL   ');	-- 删除字符串首尾空格
-- 输出
ABCD EFGHI JKL

CHAR_LENGTH

SELECT CHAR_LENGTH("aaaaaa");		-- 输出字符串长度
-- 输出
6

窗口函数

-- 语法
SELECT 窗口函数名(字段) OVER (窗口定义) FROM table_name;

-- 例子，依据column1累加每一个column1自己的column2,依据column3排序
SELECT 
    column1,
    SUM(column2) OVER (PARTITION BY column1 ORDER BY column3 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM table_name;

-- 比如：查询每个销售人员每天的销售额，并计算每月销售额以及每月销售额的累积总和
CREATE TABLE sales (
    salesperson_id INT, -- 员工id
    sales_date DATE,	-- 日期
    amount DECIMAL(10, 2)	-- 金额
);

SELECT 
    salesperson_id,
    sales_date,
    amount,
    SUM(amount) OVER (PARTITION BY salesperson_id ORDER BY DATE_FORMAT(sales_date, '%Y-%m')) AS monthly_sales,
    SUM(amount) OVER (PARTITION BY salesperson_id ORDER BY DATE_FORMAT(sales_date, '%Y-%m') ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM 
    sales
ORDER BY 
    salesperson_id, sales_date;

尾语

在排查问题和检验测试时，SQL和Excel两边的方法能帮上不小的忙。

所以基础的搭建是很重要的，代码只会越写越熟，多多加油

坚持日复一日编程，打破年复一年造轮

实战总结MySQL常见函数

前言

目录

日期

判断

数据

字符串

窗口函数

尾语