SQL 知识速查表
本速查表汇总了 LeetCode SQL 50 题涉及的核心语法和函数，方便快速查阅。
基础查询
SELECT 基本语法
-- 基本查询
SELECT column1, column2 FROM table_name;
-- 查询所有列
SELECT * FROM table_name;
-- 别名
SELECT column_name AS alias_name FROM table_name;
-- 去重
SELECT DISTINCT column_name FROM table_name;
-- 条件过滤
SELECT * FROM table_name WHERE condition;
WHERE 条件运算符
| 运算符 | 说明 | 示例 |
|---|---|---|
= | 等于 | WHERE id = 1 |
<> 或 != | 不等于 | WHERE status <> 'active' |
> / < | 大于 / 小于 | WHERE age > 18 |
>= / <= | 大于等于 / 小于等于 | WHERE score >= 60 |
BETWEEN | 范围查询 | WHERE age BETWEEN 18 AND 30 |
IN | 列表匹配 | WHERE id IN (1, 2, 3) |
LIKE | 模糊匹配 | WHERE name LIKE '张%' |
IS NULL | 空值判断 | WHERE bonus IS NULL |
AND | 逻辑与 | WHERE age > 18 AND status = 'active' |
OR | 逻辑或 | WHERE type = 'A' OR type = 'B' |
NOT | 逻辑非 | WHERE NOT deleted |
LIKE 通配符
| 通配符 | 说明 | 示例 |
|---|---|---|
% | 任意多个字符 | LIKE '张%'（以张开头） |
_ | 单个字符 | LIKE '_明'（第二个字是明） |
-- 以"张"开头
WHERE name LIKE '张%';
-- 以"明"结尾
WHERE name LIKE '%明';
-- 包含"小"
WHERE name LIKE '%小%';
-- 第二个字是"明"
WHERE name LIKE '_明%';
ORDER BY 排序
-- 升序（默认）
SELECT * FROM table_name ORDER BY column_name ASC;
-- 降序
SELECT * FROM table_name ORDER BY column_name DESC;
-- 多字段排序
SELECT * FROM table_name ORDER BY column1 DESC, column2 ASC;
-- 按表达式排序
SELECT * FROM products ORDER BY price * quantity DESC;
聚合函数
常用聚合函数
| 函数 | 说明 | 示例 |
|---|---|---|
COUNT(*) | 统计行数 | COUNT(*) AS total |
COUNT(column) | 统计非空值数量 | COUNT(bonus) |
COUNT(DISTINCT column) | 统计去重后的数量 | COUNT(DISTINCT user_id) |
SUM(column) | 求和 | SUM(amount) |
AVG(column) | 求平均值 | AVG(score) |
MAX(column) | 最大值 | MAX(price) |
MIN(column) | 最小值 | MIN(created_at) |
GROUP BY 分组
-- 基本分组
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;
-- 多字段分组
SELECT department, job_title, AVG(salary) AS avg_salary
FROM employees
GROUP BY department, job_title;
-- 分组后过滤（HAVING）
SELECT department, COUNT(*) AS count
FROM employees
GROUP BY department
HAVING COUNT(*) >= 5;
-- WHERE vs HAVING
-- WHERE：分组前过滤
-- HAVING：分组后过滤
SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE status = 'active' -- 先过滤在职员工
GROUP BY department
HAVING AVG(salary) > 10000; -- 再筛选平均工资 > 10000 的部门
条件聚合技巧
-- 条件计数
SELECT
COUNT(*) AS total,
SUM(CASE WHEN status = 'approved' THEN 1 ELSE 0 END) AS approved_count,
SUM(status = 'approved') AS approved_count_mysql -- MySQL 简写
FROM orders;
-- 条件求和
SELECT
SUM(CASE WHEN status = 'approved' THEN amount ELSE 0 END) AS approved_amount,
SUM(IF(status = 'approved', amount, 0)) AS approved_amount_mysql -- MySQL 简写
FROM orders;
-- 多条件统计
SELECT
COUNT(*) AS total,
SUM(rating >= 3) AS good_count,
SUM(rating < 3) AS poor_count,
ROUND(AVG(rating >= 3) * 100, 2) AS good_percentage
FROM reviews;
连接查询 JOIN
JOIN 类型对比
┌─────────────────────────────────────────────────────────────────┐
│ INNER JOIN │
│ ┌─────────────┐ ┌─────────────┐ │
│ │ A │ │ B │ │
│ │ ┌───────┐ │ │ ┌───────┐ │ │
│ │ │ │ │ │ │ │ │ │
│ │ │ ┌──┐ │ │ 只返回匹配的行 │ │ ┌──┐ │ │ │
│ │ │ │██│ │ │ ─────────────────► │ │ │██│ │ │ │
│ │ │ └──┘ │ │ │ │ └──┘ │ │ │
│ │ │ │ │ │ │ │ │ │
│ │ └───────┘ │ │ └───────┘ │ │
│ └─────────────┘ └─────────────┘ │
└─────────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────┐
│ LEFT JOIN │
│ ┌─────────────┐ ┌─────────────┐ │
│ │ A │ │ B │ │
│ │ ┌───────┐ │ │ ┌───────┐ │ │
│ │ │ ┌──┐ │ │ 返回左表所有行 │ │ ┌──┐ │ │ │
│ │ │██│██│ │ │ ─────────────────► │ │ │██│ │ │ │
│ │ │ └──┘ │ │ 右表无匹配则为NULL│ │ └──┘ │ │ │
│ │ └───────┘ │ │ └───────┘ │ │
│ └─────────────┘ └─────────────┘ │
└─────────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────┐
│ CROSS JOIN │
│ ┌─────────────┐ ┌─────────────┐ │
│ │ A │ │ B │ │
│ │ a1 a2 a3 │ 笛卡尔积 │ b1 b2 b3 │ │
│ │ │ ─────────────────► │ │ │
│ │ │ m × n 行 │ │ │
│ └─────────────┘ └─────────────┘ │
└─────────────────────────────────────────────────────────────────┘
JOIN 语法
-- INNER JOIN（内连接）
SELECT a.name, b.order_id
FROM customers a
INNER JOIN orders b ON a.id = b.customer_id;
-- LEFT JOIN（左连接）
SELECT a.name, b.order_id
FROM customers a
LEFT JOIN orders b ON a.id = b.customer_id;
-- 查找没有匹配的行
SELECT a.name
FROM customers a
LEFT JOIN orders b ON a.id = b.customer_id
WHERE b.order_id IS NULL; -- 关键：用 IS NULL 判断
-- RIGHT JOIN（右连接）
SELECT a.name, b.order_id
FROM customers a
RIGHT JOIN orders b ON a.id = b.customer_id;
-- 自连接
SELECT a.name AS employee, b.name AS manager
FROM employees a
JOIN employees b ON a.manager_id = b.id;
-- CROSS JOIN（笛卡尔积）
SELECT s.student_name, sub.subject_name
FROM students s
CROSS JOIN subjects sub;
-- USING 语法（当连接字段名相同时）
SELECT * FROM table1
JOIN table2 USING(id);
-- 多表连接
SELECT a.name, b.order_id, c.product_name
FROM customers a
JOIN orders b ON a.id = b.customer_id
JOIN order_items c ON b.id = c.order_id;
子查询
子查询类型
-- 标量子查询（返回单个值）
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- 列子查询（返回一列）
SELECT name
FROM employees
WHERE department_id IN (SELECT id FROM departments WHERE location = '北京');
-- 行子查询（返回一行）
SELECT *
FROM employees
WHERE (department_id, salary) = (
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
);
-- 表子查询（返回一个表）
SELECT e.name, e.salary
FROM (
SELECT name, salary, department_id
FROM employees
WHERE status = 'active'
) e;
-- EXISTS 子查询
SELECT name
FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id
);
-- NOT EXISTS 子查询
SELECT name
FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id
);
相关子查询 vs 非相关子查询
-- 非相关子查询：独立执行，只执行一次
SELECT name FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- 相关子查询：依赖外层查询，每行执行一次
SELECT name, salary
FROM employees e1
WHERE salary > (
SELECT AVG(salary)
FROM employees e2
WHERE e2.department_id = e1.department_id -- 引用外层表
);
窗口函数
窗口函数是 SQL 中强大的分析工具，可以在不减少行数的情况下进行聚合计算。
基本语法
function_name() OVER (
PARTITION BY column_name -- 分组
ORDER BY column_name -- 排序
frame_clause -- 窗口范围
)
排名函数
-- ROW_NUMBER：连续编号，无并列
SELECT name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;
-- 结果：1, 2, 3, 4, 5...
-- RANK：并列跳号
SELECT name, salary,
RANK() OVER (ORDER BY salary DESC) AS rank_num
FROM employees;
-- 结果：1, 2, 2, 4, 5...（有两个第2名，第3名被跳过）
-- DENSE_RANK：并列不跳号
SELECT name, salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;
-- 结果：1, 2, 2, 3, 4...（有两个第2名，第3名不跳过）
排名函数对比：
| 函数 | 相同值处理 | 示例结果 |
|---|---|---|
ROW_NUMBER() | 不同编号 | 1, 2, 3, 4, 5 |
RANK() | 相同编号，跳过后续 | 1, 2, 2, 4, 5 |
DENSE_RANK() | 相同编号，不跳过 | 1, 2, 2, 3, 4 |
分组内排名
-- 每个部门的工资排名
SELECT name, department_id, salary,
RANK() OVER (
PARTITION BY department_id
ORDER BY salary DESC
) AS dept_rank
FROM employees;
-- 每个部门工资前三名
WITH ranked AS (
SELECT name, department_id, salary,
DENSE_RANK() OVER (
PARTITION BY department_id
ORDER BY salary DESC
) AS rnk
FROM employees
)
SELECT * FROM ranked WHERE rnk <= 3;
LEAD 和 LAG
-- LAG：获取前 N 行的值
SELECT date, revenue,
LAG(revenue, 1) OVER (ORDER BY date) AS prev_revenue,
revenue - LAG(revenue, 1) OVER (ORDER BY date) AS growth
FROM daily_sales;
-- LEAD：获取后 N 行的值
SELECT date, revenue,
LEAD(revenue, 1) OVER (ORDER BY date) AS next_revenue
FROM daily_sales;
-- 带默认值
SELECT date, revenue,
LAG(revenue, 1, 0) OVER (ORDER BY date) AS prev_revenue
FROM daily_sales;
聚合窗口函数
-- 累计求和
SELECT date, amount,
SUM(amount) OVER (ORDER BY date) AS running_total
FROM sales;
-- 移动平均
SELECT date, amount,
AVG(amount) OVER (
ORDER BY date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg
FROM sales;
-- 分组内累计
SELECT department, month, revenue,
SUM(revenue) OVER (
PARTITION BY department
ORDER BY month
) AS dept_running_total
FROM monthly_revenue;
窗口范围 frame_clause
-- 语法
ROWS BETWEEN start AND end
-- 范围关键字
UNBOUNDED PRECEDING -- 从第一行开始
N PRECEDING -- 前 N 行
CURRENT ROW -- 当前行
N FOLLOWING -- 后 N 行
UNBOUNDED FOLLOWING -- 到最后一行
-- 示例
-- 从开头到当前行（累计）
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
-- 前2行到后2行
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
-- 当前行到末尾
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
字符串函数
常用字符串函数
| 函数 | 说明 | 示例 |
|---|---|---|
CONCAT(s1, s2, ...) | 连接字符串 | CONCAT(first_name, ' ', last_name) |
CONCAT_WS(sep, s1, s2, ...) | 用分隔符连接 | CONCAT_WS('-', '2024', '01', '15') |
LENGTH(s) / CHAR_LENGTH(s) | 字符串长度 | LENGTH('Hello') → 5 |
UPPER(s) / LOWER(s) | 大小写转换 | UPPER('hello') → 'HELLO' |
TRIM(s) | 去除两端空格 | TRIM(' hello ') → 'hello' |
LTRIM(s) / RTRIM(s) | 去除左/右空格 | LTRIM(' hello') → 'hello' |
LEFT(s, n) / RIGHT(s, n) | 取左/右 n 个字符 | LEFT('Hello', 2) → 'He' |
SUBSTRING(s, pos, len) | 子字符串 | SUBSTRING('Hello', 2, 3) → 'ell' |
REPLACE(s, old, new) | 替换 | REPLACE('Hello', 'l', 'L') → 'HeLLo' |
REVERSE(s) | 反转 | REVERSE('Hello') → 'olleH' |
LOCATE(sub, s) | 查找位置 | LOCATE('l', 'Hello') → 3 |
INSTR(s, sub) | 查找位置 | INSTR('Hello', 'l') → 3 |
LPAD(s, len, pad) | 左填充 | LPAD('5', 3, '0') → '005' |
RPAD(s, len, pad) | 右填充 | RPAD('5', 3, '0') → '500' |
REPEAT(s, n) | 重复 n 次 | REPEAT('ab', 3) → 'ababab' |
正则表达式
-- REGEXP：正则匹配
WHERE name REGEXP '^张'; -- 以"张"开头
WHERE email REGEXP '@gmail\.com$'; -- 以 @gmail.com 结尾
WHERE phone REGEXP '^[0-9]{11}$'; -- 11位数字
-- REGEXP_REPLACE：正则替换（MySQL 8.0+）
SELECT REGEXP_REPLACE('Hello123World', '[0-9]', '-');
-- 结果：Hello---World
-- REGEXP_SUBSTR：提取匹配子串（MySQL 8.0+）
SELECT REGEXP_SUBSTR('abc123def', '[0-9]+');
-- 结果：123
-- 正则元字符
-- ^ 字符串开头
-- $ 字符串结尾
-- . 任意单个字符
-- * 前一个字符出现 0 次或多次
-- + 前一个字符出现 1 次或多次
-- ? 前一个字符出现 0 次或 1 次
-- [abc] 匹配 a、b 或 c
-- [^abc] 不匹配 a、b、c
-- [a-z] 匹配 a 到 z
-- {n} 恰好 n 次
-- {n,} 至少 n 次
-- {n,m} n 到 m 次
日期时间函数
日期获取函数
| 函数 | 说明 | 示例 |
|---|---|---|
NOW() | 当前日期时间 | 2024-01-15 14:30:00 |
CURDATE() | 当前日期 | 2024-01-15 |
CURTIME() | 当前时间 | 14:30:00 |
YEAR(date) | 年份 | YEAR('2024-01-15') → 2024 |
MONTH(date) | 月份 | MONTH('2024-01-15') → 1 |
DAY(date) | 日 | DAY('2024-01-15') → 15 |
HOUR(time) | 小时 | HOUR('14:30:00') → 14 |
MINUTE(time) | 分钟 | MINUTE('14:30:00') → 30 |
WEEKDAY(date) | 星期几（0=周一） | WEEKDAY('2024-01-15') → 0 |
DAYOFWEEK(date) | 星期几（1=周日） | DAYOFWEEK('2024-01-15') → 2 |
日期计算函数
-- DATEDIFF：计算两个日期相差的天数
SELECT DATEDIFF('2024-01-20', '2024-01-15'); -- 5
-- TIMESTAMPDIFF：计算时间差
SELECT TIMESTAMPDIFF(DAY, '2024-01-01', '2024-01-15'); -- 14 天
SELECT TIMESTAMPDIFF(MONTH, '2024-01-01', '2024-03-15'); -- 2 个月
SELECT TIMESTAMPDIFF(YEAR, '2020-01-01', '2024-01-15'); -- 4 年
-- DATE_ADD / DATE_SUB：日期加减
SELECT DATE_ADD('2024-01-15', INTERVAL 7 DAY); -- 2024-01-22
SELECT DATE_ADD('2024-01-15', INTERVAL 1 MONTH); -- 2024-02-15
SELECT DATE_SUB('2024-01-15', INTERVAL 7 DAY); -- 2024-01-08
-- 日期部分加减
SELECT '2024-01-15' + INTERVAL 1 DAY; -- 2024-01-16
SELECT '2024-01-15' - INTERVAL 1 WEEK; -- 2024-01-08
日期格式化
-- DATE_FORMAT：日期格式化
SELECT DATE_FORMAT('2024-01-15', '%Y年%m月%d日'); -- 2024年01月15日
SELECT DATE_FORMAT(NOW(), '%Y-%m'); -- 2024-01
-- 常用格式符
-- %Y 四位年份 (2024)
-- %y 两位年份 (24)
-- %m 两位月份 (01-12)
-- %c 月份 (1-12)
-- %d 两位日期 (01-31)
-- %e 日期 (1-31)
-- %H 24小时制小时 (00-23)
-- %h 12小时制小时 (01-12)
-- %i 分钟 (00-59)
-- %s 秒 (00-59)
-- %W 星期名称 (Monday)
-- %w 星期数字 (0=Sunday)
-- %M 月份名称 (January)
数值函数
| 函数 | 说明 | 示例 |
|---|---|---|
ROUND(x, d) | 四舍五入保留 d 位小数 | ROUND(3.456, 2) → 3.46 |
CEIL(x) / CEILING(x) | 向上取整 | CEIL(3.2) → 4 |
FLOOR(x) | 向下取整 | FLOOR(3.8) → 3 |
ABS(x) | 绝对值 | ABS(-5) → 5 |
MOD(n, m) | 取模 | MOD(10, 3) → 1 |
POWER(x, n) | 幂运算 | POWER(2, 3) → 8 |
SQRT(x) | 平方根 | SQRT(16) → 4 |
RAND() | 随机数 [0, 1) | RAND() → 0.123... |
NULL 处理
-- IFNULL：NULL 替换
SELECT IFNULL(bonus, 0) FROM employees;
-- 如果 bonus 为 NULL，返回 0
-- COALESCE：返回第一个非 NULL 值
SELECT COALESCE(phone, mobile, email, 'N/A') AS contact
FROM customers;
-- NULLIF：如果相等返回 NULL
SELECT NULLIF(a, b); -- 如果 a = b，返回 NULL，否则返回 a
-- CASE WHEN 处理 NULL
SELECT
CASE
WHEN bonus IS NULL THEN '无奖金'
WHEN bonus < 1000 THEN '低'
ELSE '高'
END AS bonus_level
FROM employees;
条件表达式
-- CASE WHEN（搜索形式）
SELECT name, salary,
CASE
WHEN salary >= 20000 THEN '高薪'
WHEN salary >= 10000 THEN '中等'
ELSE '低薪'
END AS level
FROM employees;
-- CASE WHEN（简单形式）
SELECT name,
CASE department_id
WHEN 1 THEN '技术部'
WHEN 2 THEN '销售部'
ELSE '其他'
END AS department
FROM employees;
-- IF 函数（MySQL 特有）
SELECT name, IF(salary > 10000, '高薪', '低薪') AS level
FROM employees;
-- IFNULL 函数
SELECT name, IFNULL(bonus, 0) AS bonus
FROM employees;
数学计算技巧
-- 取模判断奇偶
WHERE id % 2 = 1 -- 奇数
WHERE id % 2 = 0 -- 偶数
-- MOD 函数
WHERE MOD(id, 2) = 1
-- 除法保留小数
SELECT ROUND(10.0 / 3, 2); -- 3.33
-- 百分比计算
SELECT
COUNT(*) AS total,
SUM(status = 'success') AS success_count,
ROUND(SUM(status = 'success') / COUNT(*) * 100, 2) AS success_rate
FROM orders;
-- 注意整数除法
SELECT 10 / 3; -- 3.3333（MySQL 默认浮点除法）
SELECT 10 DIV 3; -- 3（整数除法）
常见面试题型
1. 第 N 高的值
-- 方法一：LIMIT OFFSET
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET N-1; -- 第 N 高
-- 方法二：窗口函数
WITH ranked AS (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
)
SELECT MAX(salary) FROM ranked WHERE rnk = N;
2. 连续 N 天登录
-- 使用日期差 + 分组
SELECT user_id, COUNT(DISTINCT DATE(login_time)) AS consecutive_days
FROM (
SELECT user_id, login_time,
DATE(login_time) - ROW_NUMBER() OVER (
PARTITION BY user_id ORDER BY login_time
) AS grp
FROM login_log
) t
GROUP BY user_id, grp
HAVING consecutive_days >= N;
3. 部门工资排名
-- 每个部门工资前三名
WITH ranked AS (
SELECT name, department_id, salary,
DENSE_RANK() OVER (
PARTITION BY department_id
ORDER BY salary DESC
) AS rnk
FROM employees
)
SELECT name, department_id, salary
FROM ranked
WHERE rnk <= 3;
4. 用户留存率
-- 次日留存率
SELECT
DATE(first_login) AS login_date,
COUNT(DISTINCT user_id) AS new_users,
COUNT(DISTINCT CASE WHEN next_login IS NOT NULL THEN user_id END) AS retained,
ROUND(COUNT(DISTINCT CASE WHEN next_login IS NOT NULL THEN user_id END)
/ COUNT(DISTINCT user_id) * 100, 2) AS retention_rate
FROM (
SELECT user_id,
MIN(login_time) AS first_login,
MIN(CASE WHEN login_time > DATE_ADD(MIN(login_time), INTERVAL 1 DAY)
THEN login_time END) AS next_login
FROM login_log
GROUP BY user_id
) t
GROUP BY login_date;
5. 查找重复数据
-- 查找重复记录
SELECT email, COUNT(*) AS count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
-- 删除重复记录（保留 ID 最小的）
DELETE FROM users
WHERE id NOT IN (
SELECT MIN(id) FROM users GROUP BY email
);
性能优化建议
1. 索引使用
-- 为常用查询条件创建索引
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_status_date ON orders(status, created_at);
-- 复合索引遵循最左前缀原则
-- 索引 (a, b, c) 可用于：
-- WHERE a = 1
-- WHERE a = 1 AND b = 2
-- WHERE a = 1 AND b = 2 AND c = 3
-- 不可用于：
-- WHERE b = 2
-- WHERE c = 3
2. 避免全表扫描
-- 避免：LIKE 以通配符开头
WHERE name LIKE '%张%'; -- 不走索引
-- 推荐：LIKE 以具体字符开头
WHERE name LIKE '张%'; -- 走索引
-- 避免：在索引列上使用函数
WHERE YEAR(created_at) = 2024; -- 不走索引
-- 推荐：范围查询
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
-- 避免：OR 连接不同字段
WHERE a = 1 OR b = 2; -- 可能不走索引
-- 推荐：UNION ALL
SELECT * FROM table WHERE a = 1
UNION ALL
SELECT * FROM table WHERE b = 2;
3. 子查询优化
-- 避免：相关子查询
SELECT * FROM orders o
WHERE customer_id IN (
SELECT id FROM customers WHERE region = '华东'
);
-- 推荐：JOIN
SELECT o.* FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.region = '华东';
-- 避免：SELECT *
SELECT * FROM large_table;
-- 推荐：只查需要的列
SELECT id, name FROM large_table;
SQL 执行顺序
了解 SQL 的执行顺序有助于理解查询的工作原理：
1. FROM -- 确定表
2. ON -- 连接条件
3. JOIN -- 执行连接
4. WHERE -- 过滤条件
5. GROUP BY -- 分组
6. HAVING -- 分组后过滤
7. SELECT -- 选择列
8. DISTINCT -- 去重
9. ORDER BY -- 排序
10. LIMIT -- 限制行数
-- 示例：理解执行顺序
SELECT department_id, AVG(salary) AS avg_salary -- 7. SELECT
FROM employees -- 1. FROM
WHERE status = 'active' -- 4. WHERE
GROUP BY department_id -- 5. GROUP BY
HAVING AVG(salary) > 10000 -- 6. HAVING
ORDER BY avg_salary DESC -- 9. ORDER BY
LIMIT 5; -- 10. LIMIT