文章作者:当战神遇到编程
文章专栏:MySQL
欢迎大家点赞👍评论📝收藏⭐文章

一: 聚合函数

聚合函数是为了进行聚合查询,聚合查询就是就是进行"行与行之间的运算"

以下这些常用函数都只能接受一个参数

函数	作用
COUNT(expr)	统计数量
SUM(expr)	求和
AVG(expr)	平均值
MAX(expr)	最大值
MIN(expr)	最小值

准备测试表

员工表 emp

CREATE TABLE emp(
    emp_id INT,
    emp_name VARCHAR(20),
    dept_id INT,
    emp_salary DECIMAL(10,2),
    emp_bonus DECIMAL(10,2),
    emp_deduction DECIMAL(10,2)
);

部门表 dept

CREATE TABLE dept(
    dept_id INT,
    dept_name VARCHAR(20)
);

1.计数函数 (COUNT)

COUNT(expr)用于统计结果集中记录的行数。

-- 方式 A：统计总行数（包含 NULL 值）
SELECT COUNT(*) FROM emp;

-- 方式 B：统计指定列的有值行数（忽略 NULL 值）
SELECT COUNT(emp_salary) FROM emp;

2.求和函数 (SUM)

SUM(expr) 用于计算数值列的总和。

(1) 针对单个字段求和

这是最基础的用法，统计某一列的总数。

-- 统计公司所有人的基本工资总和
SELECT SUM(emp_salary) FROM emp;

• 特点：它会自动忽略 NULL 值（不计入总和）。

(2) 针对多个字段（或表达式）求和

有时候我们需要统计的不是某一个列，而是几个列计算后的总结果。

-- 统计公司“实发工资”的总和（基本工资 + 奖金）
SELECT SUM(emp_salary + emp_bonus) FROM emp;

• 核心逻辑
  1.数据库会先计算每一行的 emp_salary + emp_bonus。
  2.然后再将每一行算出的结果进行累加。

• 避坑提醒（NULL 陷阱）：
  ○ 在 SQL 中，任何数值 + NULL 的结果都是 NULL。
  ○ 如果某员工有 emp_salary 但 emp_bonus 是 NULL，那么 emp_salary+ emp_bonus的结果就是 NULL，这条数据就不会被计入总和。

• 非数值处理：只能作用于数值类型（数字）。对字符串求和没有意义。

3.平均值函数 (AVG)

AVG(expr) 用于计算一组数值的平均值。

-- 统计公司所有人的平均工资
SELECT AVG(emp_salary) FROM emp;

-- 统计公司所有人的平均总收入（基本工资 + 奖金）
SELECT AVG(emp_salary + emp_bonus) FROM emp;

深度详细拆解（必看要点）

• 以高精度的小数格式输出结果

• 自动忽略 NULL 值：仅对非 NULL 的数据行计算平均值

• 仅支持数值类型： 对字符串等非数值类型求平均无业务意义

4. 最大值与最小值函数 (MAX / MIN)

MAX(expr) 和 MIN(expr) 分别用于找出一组值中的最高分和最低分。

-- 找出公司里的最高工资和最低工资
SELECT MAX(emp_salary), MIN(emp_salary) FROM emp;

-- 找出最高的“个人净收入”（工资 - 扣款）
SELECT MAX(emp_salary - emp_deduction) FROM emp;

深度详细拆解

• 字符串: 按字母顺序排列

• 日期与时间类型：
  ○ MIN： 代表“最早”的时间（过去）。
  ○ MAX： 代表“最晚”的时间（未来）。

• NULL 的处理：
  ○ 它们都会自动忽略 NULL。
  ○ 如果一列全是 NULL，结果返回 NULL。

二: 分组查询 (GROUP BY) 与 结果筛选 (HAVING)

分组查询是聚合查询的核心环节，它负责将全表数据按指定维度进行“分类汇总”。

1.基础案例：按维度统计

-- 案例 1：按部门统计人数
-- 注意：COUNT(name) 会忽略名字为 NULL 的记录
SELECT dept_id, COUNT(emp_name) FROM emp GROUP BY dept_id;

-- 案例 2：按部门统计平均工资
SELECT dept_id, AVG(emp_salary) FROM emp GROUP BY dept_id;

2.核心铁律：SELECT 的限制

在使用 GROUP BY 时，SELECT 后面的普通字段必须出现在 GROUP BY 子句中。

3.分组后的筛选：HAVING

当我们需要对聚合后的统计结果进行过滤时（比如：只看人数 > 1 的部门），必须使用 HAVING。

-- 查询人数大于 1 的部门
SELECT dept_id, COUNT(*) AS total FROM emp GROUP BY dept_id HAVING COUNT(*) > 1;

WHERE 与 HAVING 的本质区别

子句	执行时机	作用对象	备注
WHERE	分组前	筛选原始数据	决定哪些行能进入分组环节
HAVING	分组后	筛选分组结果	决定哪些组能出现在最终结果中

4.综合实战：先筛选，后分组

在实际业务中，我们通常需要先排除不符合条件的原始数据，再进行统计。

-- 需求：先筛选工资 > 6000 的员工，再按部门统计人数
SELECT dept_id, COUNT(*) FROM emp WHERE emp_salary > 6000 GROUP BY dept_id;

5.SQL 逻辑执行顺序

• FROM：定位表数据。
• WHERE：对原始行进行初次过滤。
• GROUP BY：将剩下的行分堆。
• HAVING：对分好的堆进行过滤。
• SELECT：提取字段、计算表达式、计算聚合函数、起别名。
• ORDER BY：对最终结果进行排序。
• LIMIT：最后一切两半，取指定行数。

三: 日期函数

1.获取当前时间

这类函数不需要参数，直接调用即可获取数据库服务器的当前时钟。

函数	返回值示例	说明
CURDATE( )	2023-10-27	仅日期 (Current Date)
CURTIME( )	14:30:05	仅时间 (Current Time)
NOW( )	2023-10-27 14:30:05	日期 + 时间（最常用）

场景一：直接查看系统时间

-- 一次性查看日期、时间、以及完整的时间戳
SELECT CURDATE(), CURTIME(), NOW();

场景二：插入数据时自动记录时间

这是最常用的场景。比如有一个新用户注册，你需要记录他注册的精确瞬间。

-- 假设表名叫 users，有一个字段叫 registration_time
INSERT INTO users (username, registration_time) VALUES ('张三', NOW());

• 意义： 使用 NOW( ) 可以确保记录下的是数据库服务器接收到请求的那一秒，非常精准。

2.日期提取与转换

当你有一个完整的“日期时间（Datetime）”数据，但只需要其中日期部分进行比较或展示时使用。

• 函数原型： DATE(expr)

• 参数说明： expr 是必需的。它可以是一个日期时间字符串、数据库中的字段名（如 create_time）。

3.日期的加减运算（实战核心）

这是日期处理中最难也最强大的部分，使用了特殊的 INTERVAL（间隔）语法。

• ADDDATE(date, INTERVAL expr unit)（加法）：
  ○ ELECT ADDDATE(NOW(), INTERVAL 7 DAY); —— 获取 7 天后 的时间。
• SUBDATE(date, INTERVAL expr unit)（减法）：
  ○ SELECT SUBDATE(NOW(), INTERVAL 1 MONTH); —— 获取 1 个月前 的时间（常用于统计上月报表）。

注意: unit 可以是 DAY, MONTH, YEAR, HOUR 等。

4.计算日期差

• 函数原型： DATEDIFF(expr1, expr2)
• 计算逻辑：expr1 - expr2。
• 单位：固定返回天数。
• 注意顺序：
  ○ DATEDIFF(‘2023-10-01’, ‘2023-10-05’) 结果是 -4。
  ○ 如果第一个日期比第二个早，结果就是负数。

四: 字符串处理函数

1.获取长度

(1) CHAR_LENGTH(str)

返回字符串的字符个数（一个汉字算 1 个字符）

SELECT CHAR_LENGTH('hello');   -- 5
SELECT CHAR_LENGTH('你好');    -- 2

(2) LENGTH(str)

返回字符串占用的字节数

SELECT LENGTH('hello'); -- 结果：5
SELECT LENGTH('你好');  -- 结果：6 (3 + 3)
SELECT LENGTH('🚀');    -- 结果：4 (表情包通常占4个字节)

2.字符串拼接

(1) CONCAT(str1,str2,…)

直接拼接多个字符串

-- 1. 拼接常量
SELECT CONCAT('张', '三'); -- 结果：'张三'

-- 2. 拼接字段与符号
SELECT CONCAT(name, '(', age, ')') FROM student; -- 示例结果：'张三(20)'

• 只要有一个参数是 NULL，整个结果都会变成 NULL！

(2) CONCAT_WS(sep,str1,str2,…)

按指定分隔符拼接

-- 自动用 "-" 连接，且不怕 NULL
SELECT CONCAT_WS('-', '2023', '10', '27'); -- 结果：'2023-10-27'

• 自动跳过 NULL： 它会忽略 NULL 值，而不会让整个结果变 NULL。
• 省时省力： 不需要重复写横杠或逗号。

3.大小写转换

(1) LOWER(str)

转小写

SELECT LOWER('HELLO');
-- hello

(2) UPPER(str)

转大写

SELECT UPPER('hello');
-- HELLO

4.截取字符串（重点）

(1) SUBSTR(str,pos,len)

从指定位置开始截取 len 个字符

SELECT SUBSTR('abcdef',2,3);
-- bcd

说明：

• 下标从 1 开始
• pos = 起始位置

常见场景：手机号隐藏

SELECT CONCAT(SUBSTR(phone,1,3),'****',SUBSTR(phone,8,4)) FROM user;

5.替换字符串（重点）

(1) REPLACE(str,old,new)

SELECT REPLACE('I like java','java','mysql');
-- I like mysql

6.左右截取

(1) LEFT(str,len)

取左边 len 个字符

SELECT LEFT('abcdef',3);
-- abc

(2) RIGHT(str,len)

取右边 len 个字符

SELECT RIGHT('abcdef',2);
-- ef

7.去空格（高频）

(1) TRIM(str)

去除两边空格

SELECT TRIM('   hello   ');
-- hello

(2) LTRIM(str)

去左边空格

SELECT LTRIM('   hello');

(3) RTRIM(str)

去右边空格

SELECT RTRIM('hello   ');

8.查找字符串位置

INSTR(str,substr)

返回子串第一次出现的位置（从1开始）

SELECT INSTR('abcdef','cd');
-- 3

找不到返回：

0

五: 数学函数

1.ABS(X)

返回 X 的绝对值。

SELECT ABS(-10);
-- 10

2.CEILING(X)

向上取整（返回不小于 X 的最小整数）

SELECT CEIL(3.2);
-- 4

3.FLOOR(X)

向下取整（返回不大于 X 的最大整数）

SELECT FLOOR(3.9);
-- 3

4.ROUND(X,D)

用于对数值 X 进行四舍五入。

• 参数说明：
  ○ X：要处理的数字。
  ○ D：保留的小数位数。如果不写，默认为 0（即取整）。

SELECT ROUND(3.14159, 2); -- 结果：3.14
SELECT ROUND(3.56);       -- 结果：4 （不传D，直接取整）

• 进阶技巧（负数位数）：
  ○ D 可以是负数，表示对整数位进行四舍五入。
  ○ SELECT ROUND(1234.56, -1); → 结果：1230
  ○ SELECT ROUND(1234.56, -2); → 结果：1200

5.RAND()

返回一个 [0,1) 之间的随机小数（前闭后开，包含 0 但不包含 1）。

SELECT RAND();

随机抽取一条数据

SELECT * FROM student ORDER BY RAND() LIMIT 1;

六: 其他常用函数

1.VERSION( )

查看当前 MySQL 版本。

SELECT VERSION();

2.DATABASE( )

查看当前正在使用的数据库。

SELECT DATABASE();

3.USER( )

查看当前登录用户。

SELECT USER();

4.IFNULL(expression, default_value)

IFNULL(expression, default_value) 用于为 NULL 值设置一个默认的“备选方案”。

• 逻辑：
  ○ 如果第一个参数 expression 不是 NULL，则返回它本身。
  ○ 如果第一个参数 是 NULL，则返回第二个参数 default_value。

-- 1. 基础演示
SELECT IFNULL(NULL, '暂无数据'); -- 结果：'暂无数据'
SELECT IFNULL('Hello', '备选');  -- 结果：'Hello'

-- 2. 实际场景：处理奖金字段
-- 很多员工没有奖金（为 NULL），直接计算会出问题
SELECT emp_name, (emp_salary + IFNULL(emp_bonus, 0)) AS total_income FROM emp;