SQL--

FROM → JOIN → ON → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

• 表连条件 → FROM / JOIN / ON

• 行筛 → WHERE

• 分组 → GROUP BY

• 筛组 → HAVING

• 选列去重 → SELECT / DISTINCT

• 排好序 → ORDER BY

• 截断定长度 → LIMIT / OFFSET

=========================================
SQL 聚合函数与高级关键字完整版总结
=========================================

一、聚合函数（Aggregate Functions）
------------------------------------

1. COUNT()

作用：
统计行数。

写法区别：

1）COUNT(*)
统计总行数（包括NULL）
推荐使用。

2）COUNT(1)
效果等同 COUNT(*)。

3）COUNT(字段)
统计该字段不为 NULL 的行数。

示例：
SELECT COUNT(*) FROM user;
SELECT COUNT(age) FROM user;

注意：
COUNT(字段) 会忽略 NULL。

2. SUM()

作用：
对数值字段求总和。

示例：
SELECT SUM(amount) FROM orders;

特点：
- 忽略 NULL
- 如果全部是 NULL，返回 NULL

安全写法：
SELECT IFNULL(SUM(amount),0) FROM orders;

3. AVG()

作用：
求平均值。

示例：
SELECT AVG(score) FROM student;

特点：
- 忽略 NULL
- 实际等于 SUM / COUNT(非NULL)

4. MAX()

作用：
获取最大值。

示例：
SELECT MAX(age) FROM user;

特点：
- 忽略 NULL
- 可用于字符串

5. MIN()

作用：
获取最小值。

示例：
SELECT MIN(age) FROM user;

特点：
- 忽略 NULL
- 可用于字符串

====================================
二、EXISTS
====================================

作用：
判断子查询是否存在结果。

语法：

SELECT *
FROM user u
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.user_id = u.id
);

特点：
- 只要子查询返回一条记录即为 TRUE
- 存在即停止（短路机制）
- 通常适用于大表

====================================
三、ANY
====================================

作用：
满足子查询结果中的任意一个条件。

示例：

SELECT *
FROM product
WHERE price > ANY (
SELECT price FROM product WHERE category_id = 1
);

等价理解：
> ANY 等价于 > MIN
< ANY 等价于 < MAX

====================================
四、ALL
====================================

作用：
必须满足子查询结果中的所有条件。

示例：

SELECT *
FROM product
WHERE price > ALL (
SELECT price FROM product WHERE category_id = 1
);

等价理解：
> ALL 等价于 > MAX
< ALL 等价于 < MIN

====================================
五、UNION
====================================

作用：
合并多个查询结果，并自动去重。

示例：

SELECT name FROM student
UNION
SELECT name FROM teacher;

特点：
- 自动去重
- 列数必须一致
- 数据类型必须兼容
- 性能比 UNION ALL 低

====================================
六、UNION ALL
====================================

作用：
合并多个查询结果，不去重。

示例：

SELECT name FROM student
UNION ALL
SELECT name FROM teacher;

特点：
- 不去重
- 性能更高
- 推荐优先使用（如果不需要去重）

====================================
七、CASE WHEN THEN ELSE END
====================================

作用：
条件表达式（类似 if-else）。

语法：

SELECT
name,
CASE
WHEN score >= 90 THEN '优秀'
WHEN score >= 60 THEN '及格'
ELSE '不及格'
END AS level
FROM student;

执行顺序：
- 从上到下判断
- 第一个满足条件即返回
- 然后停止

等值写法：

CASE gender
WHEN 'M' THEN '男'
WHEN 'F' THEN '女'
END

条件统计写法：

SELECT
SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS total_paid
FROM orders;

====================================
八、AS —— 起别名
====================================

1）字段别名

SELECT name AS username FROM user;

AS 可以省略：

SELECT name username FROM user;

2）表别名

SELECT u.name
FROM user AS u;

3）子查询必须起别名

SELECT *
FROM (
SELECT user_id, COUNT(*) c
FROM orders
GROUP BY user_id
) AS t;

====================================
九、LEFT JOIN
====================================

作用：
左连接，返回左表所有数据，
右表没有匹配则返回 NULL。

语法：

SELECT *
FROM user u
LEFT JOIN orders o
ON u.id = o.user_id;

特点：
- 左表数据一定全部返回
- 右表无匹配则为 NULL
- 常用于查“有无”关系

示例：查询没有订单的用户

SELECT u.*
FROM user u
LEFT JOIN orders o
ON u.id = o.user_id
WHERE o.id IS NULL;

注意：
- 连接条件必须写在 ON 中
- 过滤右表字段放在 WHERE 会改变结果

====================================
十、GROUP BY 注意事项（重要）
====================================

规则：
SELECT 中出现的字段：

1）要么是聚合函数
2）要么必须出现在 GROUP BY 中

错误写法：

SELECT user_id, amount
FROM orders
GROUP BY user_id;

原因：
amount 既不是聚合函数，也不在 GROUP BY 中。

正确写法：

SELECT user_id, SUM(amount)
FROM orders
GROUP BY user_id;

或者：

SELECT user_id, amount
FROM orders
GROUP BY user_id, amount;

说明：
- 标准SQL必须严格遵守
- MySQL某些模式下允许，但不规范
- 面试中必须回答为错误

====================================
总结
====================================

聚合函数：
COUNT
SUM
AVG
MAX
MIN

子查询判断：
EXISTS
ANY
ALL

结果合并：
UNION
UNION ALL

条件表达式：
CASE
WHEN
THEN
ELSE
END

连接：
LEFT JOIN

分组规则：
GROUP BY 中不能出现非聚合字段

别名：
AS

====================================

====================================
MySQL 常用日期函数总结
====================================

一、获取当前时间
------------------------------------

1. NOW()
返回当前日期 + 时间（年月日 时分秒）

示例：
SELECT NOW();

返回示例：
2026-02-21 15:30:00

2. CURDATE()
返回当前日期（只有年月日）

示例：
SELECT CURDATE();

返回示例：
2026-02-21

3. CURTIME()
返回当前时间（只有时分秒）

示例：
SELECT CURTIME();

返回示例：
15:30:00

====================================
二、提取日期部分
====================================

1. YEAR(date)
提取年份

2. MONTH(date)
提取月份

3. DAY(date)
提取日期

4. HOUR(datetime)
提取小时

5. MINUTE(datetime)
提取分钟

6. SECOND(datetime)
提取秒

示例：
SELECT YEAR(create_time) FROM orders;
SELECT MONTH(create_time) FROM orders;

====================================
三、日期加减
====================================

1. DATE_ADD(date, INTERVAL 数值 单位)
日期增加

示例：
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);

2. DATE_SUB(date, INTERVAL 数值 单位)
日期减少

示例：
SELECT DATE_SUB(NOW(), INTERVAL 7 DAY);

常用单位：

SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
YEAR

====================================
四、日期差值计算
====================================

1. DATEDIFF(date1, date2)
返回两个日期之间相差的天数
忽略时分秒

示例：
SELECT DATEDIFF('2026-02-21', '2026-02-18');
结果：3

2. TIMESTAMPDIFF(单位, start, end)
可指定单位计算差值

示例：
SELECT TIMESTAMPDIFF(DAY, start_time, end_time);
SELECT TIMESTAMPDIFF(HOUR, start_time, end_time);

常用单位：

SECOND
MINUTE
HOUR
DAY
MONTH
YEAR

====================================
五、日期格式化
====================================

1. DATE_FORMAT(date, 格式)

示例：
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');

常用格式符：

%Y 四位年份
%y 两位年份
%m 月份
%d 日期
%H 小时(24小时制)
%i 分钟
%s 秒

====================================
六、字符串转日期
====================================

1. STR_TO_DATE(str, 格式)

示例：
SELECT STR_TO_DATE('2026-02-21', '%Y-%m-%d');

====================================
七、时间戳相关
====================================

1. UNIX_TIMESTAMP()
返回时间戳

示例：
SELECT UNIX_TIMESTAMP(NOW());

2. FROM_UNIXTIME(timestamp)
时间戳转日期

示例：
SELECT FROM_UNIXTIME(1708500000);

====================================
八、企业推荐写法（重要）
====================================

查询今天数据（DATETIME 字段）：

正确写法（推荐）：

WHERE create_time >= CURDATE()
AND create_time < DATE_ADD(CURDATE(), INTERVAL 1 DAY);

不推荐写法（会导致索引失效）：

WHERE DATE(create_time) = CURDATE();

查询最近7天：

WHERE create_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY);

====================================
九、最常用函数（必须掌握）
====================================

NOW()
CURDATE()
DATE_ADD()
DATE_SUB()
DATEDIFF()
TIMESTAMPDIFF()
DATE_FORMAT()

====================================

====================================
MySQL WITH（CTE）用法总结
====================================

一、什么是 WITH

WITH 是公共表表达式（Common Table Expression，简称 CTE）。

作用：
给一个子查询起名字，使 SQL 更清晰、可读性更强。

基本语法：

WITH 表名 AS (
子查询
)
SELECT * FROM 表名;

====================================
二、基本示例
====================================

WITH tmp AS (
SELECT uid, COUNT(*) AS cnt
FROM tb_user_video_log
GROUP BY uid
)
SELECT *
FROM tmp;

说明：
tmp 是一个临时结果集，
仅在当前 SQL 语句中有效。

====================================
三、多个 WITH
====================================

WITH t1 AS (
SELECT ...
),
t2 AS (
SELECT ...
)
SELECT *
FROM t1
JOIN t2 ON ...;

多个 CTE 用逗号分隔。

====================================
四、使用场景
====================================

1）复杂查询分步骤写
2）替代嵌套子查询
3）滑动窗口计算
4）数据预处理
5）分层统计

====================================
五、滑动窗口示例（常见面试题）
====================================

WITH daily_stat AS (
SELECT
v.tag,
DATE(l.start_time) AS dt,
SUM(l.if_like) AS like_cnt,
SUM(l.if_retweet) AS retweet_cnt
FROM tb_user_video_log l
JOIN tb_video_info v
ON l.video_id = v.video_id
GROUP BY v.tag, DATE(l.start_time)
)

SELECT
a.tag,
a.dt,
SUM(b.like_cnt) AS week_like_cnt,
MAX(b.retweet_cnt) AS week_max_retweet
FROM daily_stat a
JOIN daily_stat b
ON a.tag = b.tag
AND b.dt BETWEEN DATE_SUB(a.dt, INTERVAL 6 DAY) AND a.dt
GROUP BY a.tag, a.dt;

====================================
六、WITH 和子查询区别
====================================

普通子查询：

SELECT *
FROM (
SELECT ...
) t;

WITH 写法：

WITH t AS (
SELECT ...
)
SELECT *
FROM t;

区别：

1）WITH 可读性更强
2）复杂逻辑更清晰
3）可以定义多个临时结果集

====================================
七、递归 WITH（高级）
====================================

WITH RECURSIVE t AS (
初始查询
UNION ALL
递归查询
)
SELECT * FROM t;

常用于：

1）树结构查询
2）层级数据
3）连续日期生成

====================================
八、注意事项
====================================

1）MySQL 8.0 以上才支持 WITH
2）CTE 只在当前语句中有效
3）性能上通常等同于子查询
4）CTE 必须写在最前面

====================================
九、总结
====================================

WITH = 给子查询起名字

优势：
- 结构清晰
- 可读性强
- 适合复杂统计
- 面试高频考点

====================================

MySQL 窗口函数介绍

窗口函数是在不改变行数的情况下，对一组行计算聚合、排名或累积等指标。它和普通聚合函数的区别如下：

基本语法：

聚合函数/排名函数() OVER (
[PARTITION BY 分组字段]
[ORDER BY 排序字段 [ASC|DESC]]
[窗口帧定义]
)

窗口函数使用规则：

1. 每个窗口函数必须使用 OVER() 语法。

2. 可以使用 PARTITION BY 分组，但不合并行，只是独立计算。

3. ORDER BY 定义累积或排名顺序，顺序对结果有直接影响。

4. 窗口帧可选，用于定义累积范围，如 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。

5. 同一行可以同时使用多个窗口函数。

6. 窗口函数不能用于 WHERE 子句，但可用于 SELECT、ORDER BY、HAVING 等。

窗口帧使用及场景：

1. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

   • 当前行 = 窗口函数正在计算的这一行

   • 含义：从分组第一行累加到当前行

   • 返回值：窗口范围内的累积值

   • 场景：累计销售额、文章最大同时在线人数

2. ROWS BETWEEN N PRECEDING AND CURRENT ROW

   • 含义：当前行及之前 N 行

   • 返回值：最近 N+1 行的聚合值

   • 场景：滑动平均值、最近 N 条记录总和

3. ROWS BETWEEN CURRENT ROW AND N FOLLOWING

   • 含义：当前行及之后 N 行

   • 返回值：当前行及未来 N 行的聚合值

   • 场景：预测未来 N 条数据累计值

4. ROWS BETWEEN N PRECEDING AND M FOLLOWING

   • 含义：当前行前 N 行 + 当前行 + 当前行后 M 行

   • 返回值：前后 N+M+1 行的聚合值

   • 场景：前后各几条记录平均值或总和

示例 1：文章最大同时在线人数
表 events：

SQL：
SELECT event_time, delta, SUM(delta) OVER(PARTITION BY artical_id ORDER BY event_time, delta DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS current_uv FROM events;

输出：

示例 2：滑动平均
SQL：
SELECT sale_id, sale_date, amount, AVG(amount) OVER(ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM sales;

常用窗口函数类型：

总结：

• 窗口函数对每一行返回一个值，该值根据窗口帧定义计算

• current row = 窗口函数正在处理的那一行

• ROWS 类型窗口帧适合累积和滑动统计，RANGE 类型适合按值范围累加

• 使用 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 可以保证累积值精确且顺序正确