数据库概述
MySQL安装与配置
数据库基础操作
数据类型
表结构操作
数据插入更新删除
基础查询
多表查询基础
高级查询
多表查询进阶
索引与优化
事务与锁
存储过程与函数基础
触发器与事件
存储过程与函数进阶
视图
用户权限管理
数据备份与恢复
性能优化
  1. SQL语法
  2. 高级查询

高级查询

一、GROUP BY高级用法

1.1 GROUP BY基础

1.1.1 基本分组

-- 创建销售表
CREATE TABLE sales (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(50),
    category VARCHAR(50),
    amount DECIMAL(10, 2),
    sale_date DATE,
    region VARCHAR(50)
);

INSERT INTO sales (product_name, category, amount, sale_date, region) VALUES
('iPhone', '手机', 6999.00, '2024-01-01', '北京'),
('iPhone', '手机', 6999.00, '2024-01-02', '上海'),
('iPad', '平板', 4999.00, '2024-01-01', '北京'),
('iPad', '平板', 4999.00, '2024-01-03', '广州'),
('MacBook', '电脑', 12999.00, '2024-01-02', '上海'),
('MacBook', '电脑', 12999.00, '2024-01-04', '深圳'),
('iPhone', '手机', 6999.00, '2024-01-03', '北京'),
('iPad', '平板', 4999.00, '2024-01-04', '上海');

-- 按类别分组统计
SELECT category, SUM(amount) AS total_amount
FROM sales
GROUP BY category;

1.2 GROUP BY WITH ROLLUP

1.2.1 分组汇总

语法格式：

SELECT 列名, 聚合函数()
FROM 表名
GROUP BY 列名 WITH ROLLUP;

示例：

-- 按类别和地区分组，并汇总
SELECT 
    category,
    region,
    SUM(amount) AS total_amount
FROM sales
GROUP BY category, region WITH ROLLUP;

输出：

+-----------+--------+--------------+
| category  | region | total_amount |
+-----------+--------+--------------+
| 手机      | 上海   |      6999.00 |
| 手机      | 北京   |     13998.00 |
| 手机      | NULL   |     20997.00 |
| 平板      | 北京   |      4999.00 |
| 平板      | 广州   |      4999.00 |
| 平板      | 上海   |      4999.00 |
| 平板      | NULL   |     14997.00 |
| 电脑      | 上海   |     12999.00 |
| 电脑      | 深圳   |     12999.00 |
| 电脑      | NULL   |     25998.00 |
| NULL      | NULL   |     61992.00 |
+-----------+--------+--------------+

1.3 GROUP BY WITH CUBE

1.3.1 MySQL 8.0实现

MySQL不支持WITH CUBE，使用UNION ALL模拟：

-- 模拟WITH CUBE
SELECT category, region, SUM(amount) AS total_amount
FROM sales
GROUP BY category, region

UNION ALL

SELECT category, NULL, SUM(amount)
FROM sales
GROUP BY category

UNION ALL

SELECT NULL, region, SUM(amount)
FROM sales
GROUP BY region

UNION ALL

SELECT NULL, NULL, SUM(amount)
FROM sales;

1.4 GROUPING函数

1.4.1 判断是否为汇总行

语法格式：

GROUPING(列名)

返回值：

  • 1：汇总行
  • 0：非汇总行

示例：

SELECT 
    category,
    region,
    SUM(amount) AS total_amount,
    GROUPING(category) AS is_category_total,
    GROUPING(region) AS is_region_total
FROM sales
GROUP BY category, region WITH ROLLUP;

输出：

+-----------+--------+--------------+-------------------+----------------+
| category  | region | total_amount | is_category_total | is_region_total |
+-----------+--------+--------------+-------------------+----------------+
| 手机      | 上海   |      6999.00 |                 0 |              0 |
| 手机      | 北京   |     13998.00 |                 0 |              0 |
| 手机      | NULL   |     20997.00 |                 0 |              1 |
| 平板      | 北京   |      4999.00 |                 0 |              0 |
| 平板      | 广州   |      4999.00 |                 0 |              0 |
| 平板      | 上海   |      4999.00 |                 0 |              0 |
| 平板      | NULL   |     14997.00 |                 0 |              1 |
| 电脑      | 上海   |     12999.00 |                 0 |              0 |
| 电脑      | 深圳   |     12999.00 |                 0 |              0 |
| 电脑      | NULL   |     25998.00 |                 0 |              1 |
| NULL      | NULL   |     61992.00 |                 1 |              1 |
+-----------+--------+--------------+-------------------+----------------+

二、窗口函数

2.1 窗口函数概念

2.1.1 基本语法

语法格式：

函数名() OVER (
    [PARTITION BY 分组列]
    [ORDER BY 排序列]
    [frame子句]
)

参数说明：

参数 说明
PARTITION BY 分组
ORDER BY 排序
frame子句 窗口范围

2.2 排序函数

2.2.1 ROW_NUMBER

特点：连续编号，不重复

-- 按成绩排名
SELECT 
    name,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS rank
FROM students s
INNER JOIN scores sc ON s.id = sc.student_id;

输出：

+--------+-------+------+
| name   | score | rank |
+--------+-------+------+
| 王五   | 95.50 |    1 |
| 张三   | 92.00 |    2 |
| 赵六   | 90.00 |    3 |
| 孙七   | 88.00 |    4 |
| 李四   | 88.00 |    5 |
| 张三   | 85.50 |    6 |
| 王五   | 82.00 |    7 |
| 李四   | 78.50 |    8 |
+--------+-------+------+

2.2.2 RANK

特点：相同值排名相同，跳过后续排名

-- 按成绩排名（相同成绩排名相同）
SELECT 
    name,
    score,
    RANK() OVER (ORDER BY score DESC) AS rank
FROM students s
INNER JOIN scores sc ON s.id = sc.student_id;

输出：

+--------+-------+------+
| name   | score | rank |
+--------+-------+------+
| 王五   | 95.50 |    1 |
| 张三   | 92.00 |    2 |
| 赵六   | 90.00 |    3 |
| 孙七   | 88.00 |    4 |
| 李四   | 88.00 |    4 |
| 张三   | 85.50 |    6 |
| 王五   | 82.00 |    7 |
| 李四   | 78.50 |    8 |
+--------+-------+------+

2.2.3 DENSE_RANK

特点：相同值排名相同，不跳过后续排名

-- 按成绩排名（相同成绩排名相同，不跳过）
SELECT 
    name,
    score,
    DENSE_RANK() OVER (ORDER BY score DESC) AS rank
FROM students s
INNER JOIN scores sc ON s.id = sc.student_id;

输出：

+--------+-------+------+
| name   | score | rank |
+--------+-------+------+
| 王五   | 95.50 |    1 |
| 张三   | 92.00 |    2 |
| 赵六   | 90.00 |    3 |
| 孙七   | 88.00 |    4 |
| 李四   | 88.00 |    4 |
| 张三   | 85.50 |    5 |
| 王五   | 82.00 |    6 |
| 李四   | 78.50 |    7 |
+--------+-------+------+

2.3 分组排序

2.3.1 PARTITION BY

示例：

-- 按课程分组排名
SELECT 
    c.course_name,
    s.name,
    sc.score,
    RANK() OVER (PARTITION BY c.id ORDER BY sc.score DESC) AS rank
FROM students s
INNER JOIN scores sc ON s.id = sc.student_id
INNER JOIN courses c ON sc.course_id = c.id;

输出：

+-----------------+--------+-------+------+
| course_name     | name   | score | rank |
+-----------------+--------+-------+------+
| 数据库原理      | 王五   | 95.50 |    1 |
| 数据库原理      | 孙七   | 88.00 |    2 |
| 数据库原理      | 张三   | 85.50 |    3 |
| 数据库原理      | 李四   | 78.50 |    4 |
| 数据结构        | 张三   | 92.00 |    1 |
| 数据结构        | 赵六   | 90.00 |    2 |
| 数据结构        | 李四   | 88.00 |    3 |
| 操作系统        | 王五   | 82.00 |    1 |
+-----------------+--------+-------+------+

2.4 聚合窗口函数

2.4.1 累计聚合

示例：

-- 累计销售额
SELECT 
    sale_date,
    amount,
    SUM(amount) OVER (ORDER BY sale_date) AS cumulative_amount
FROM sales
ORDER BY sale_date;

输出：

+------------+----------+-------------------+
| sale_date  | amount   | cumulative_amount |
+------------+----------+-------------------+
| 2024-01-01 |  6999.00 |          11998.00 |
| 2024-01-01 |  4999.00 |          11998.00 |
| 2024-01-02 |  6999.00 |          31996.00 |
| 2024-01-02 | 12999.00 |          31996.00 |
| 2024-01-03 |  6999.00 |          43994.00 |
| 2024-01-03 |  4999.00 |          43994.00 |
| 2024-01-04 |  4999.00 |          61992.00 |
| 2024-01-04 | 12999.00 |          61992.00 |
+------------+----------+-------------------+

2.4.2 移动平均

示例：

-- 3天移动平均
SELECT 
    sale_date,
    amount,
    AVG(amount) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg
FROM sales
ORDER BY sale_date;

2.5 偏移函数

2.5.1 LAG和LEAD

LAG：访问前一行

LEAD：访问后一行

示例：

-- 查询当天与前一天的销售额对比
SELECT 
    sale_date,
    amount,
    LAG(amount) OVER (ORDER BY sale_date) AS prev_amount,
    amount - LAG(amount) OVER (ORDER BY sale_date) AS diff
FROM sales
ORDER BY sale_date;

2.5.2 FIRST_VALUE和LAST_VALUE

示例：

-- 查询每个类别的第一个和最后一个产品
SELECT 
    category,
    product_name,
    amount,
    FIRST_VALUE(product_name) OVER (PARTITION BY category ORDER BY amount) AS first_product,
    LAST_VALUE(product_name) OVER (
        PARTITION BY category 
        ORDER BY amount
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_product
FROM sales;

三、公用表表达式

3.1 CTE基础

3.1.1 基本语法

语法格式：

WITH cte_name AS (
    SELECT语句
)
SELECT * FROM cte_name;

示例：

-- 查询成绩高于平均分的学生
WITH avg_score AS (
    SELECT AVG(score) AS avg FROM scores
)
SELECT s.name, sc.score, a.avg
FROM students s
INNER JOIN scores sc ON s.id = sc.student_id
INNER JOIN avg_score a ON sc.score > a.avg;

3.2 多个CTE

3.2.1 定义多个CTE

语法格式：

WITH 
cte1 AS (SELECT语句),
cte2 AS (SELECT语句),
cte3 AS (SELECT语句)
SELECT * FROM cte1 JOIN cte2 ON ...;

示例：

-- 统计每个班级的平均成绩和最高成绩
WITH 
class_avg AS (
    SELECT s.class_id, AVG(sc.score) AS avg_score
    FROM students s
    INNER JOIN scores sc ON s.id = sc.student_id
    GROUP BY s.class_id
),
class_max AS (
    SELECT s.class_id, MAX(sc.score) AS max_score
    FROM students s
    INNER JOIN scores sc ON s.id = sc.student_id
    GROUP BY s.class_id
)
SELECT 
    c.class_name,
    ca.avg_score,
    cm.max_score
FROM classes c
LEFT JOIN class_avg ca ON c.id = ca.class_id
LEFT JOIN class_max cm ON c.id = cm.class_id;

3.3 递归CTE

3.3.1 递归语法

语法格式：

WITH RECURSIVE cte_name AS (
    -- 初始查询（锚点）
    SELECT语句
    
    UNION ALL
    
    -- 递归查询
    SELECT语句
    FROM cte_name
    WHERE 终止条件
)
SELECT * FROM cte_name;

3.3.2 组织结构查询

示例：

-- 创建组织结构表
CREATE TABLE org (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    parent_id INT
);

INSERT INTO org VALUES
(1, 'CEO', NULL),
(2, 'CTO', 1),
(3, 'CFO', 1),
(4, '工程经理', 2),
(5, '技术经理', 2),
(6, '财务经理', 3),
(7, '工程师', 4),
(8, '技术员', 5);

-- 查询组织层级
WITH RECURSIVE org_tree AS (
    -- 锚点：顶级节点
    SELECT id, name, parent_id, 1 AS level
    FROM org
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- 递归：子节点
    SELECT o.id, o.name, o.parent_id, ot.level + 1
    FROM org o
    INNER JOIN org_tree ot ON o.parent_id = ot.id
)
SELECT * FROM org_tree ORDER BY level;

输出：

+----+--------------+-----------+-------+
| id | name         | parent_id | level |
+----+--------------+-----------+-------+
|  1 | CEO          |      NULL |     1 |
|  2 | CTO          |         1 |     2 |
|  3 | CFO          |         1 |     2 |
|  4 | 工程经理     |         2 |     3 |
|  5 | 技术经理     |         2 |     3 |
|  6 | 财务经理     |         3 |     3 |
|  7 | 工程师       |         4 |     4 |
|  8 | 技术员       |         5 |     4 |
+----+--------------+-----------+-------+

3.3.3 生成序列

示例：

-- 生成1到10的序列
WITH RECURSIVE numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT * FROM numbers;

输出：

+----+
| n  |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |
+----+

四、分页优化

4.1 传统分页

4.1.1 LIMIT OFFSET

语法格式：

SELECT * FROM 表名 LIMIT 偏移量, 数量;

示例：

-- 第1页
SELECT * FROM students LIMIT 0, 10;

-- 第2页
SELECT * FROM students LIMIT 10, 10;

-- 第100页
SELECT * FROM students LIMIT 990, 10;

问题：

  • 偏移量大时性能差
  • 需要扫描大量行

4.2 覆盖索引分页

4.2.1 使用子查询

示例：

-- 传统方式
SELECT * FROM students ORDER BY id LIMIT 100000, 10;

-- 优化方式
SELECT * FROM students s
INNER JOIN (
    SELECT id FROM students ORDER BY id LIMIT 100000, 10
) tmp ON s.id = tmp.id;

原理：

  • 子查询使用覆盖索引
  • 只扫描索引，不扫描数据
  • 大幅提高性能

4.3 游标分页

4.3.1 基于游标

语法格式：

SELECT * FROM 表名 WHERE id > 上次最大id ORDER BY id LIMIT 数量;

示例：

-- 第1页
SELECT * FROM students ORDER BY id LIMIT 10;

-- 假设第1页最后一条id=10
-- 第2页
SELECT * FROM students WHERE id > 10 ORDER BY id LIMIT 10;

-- 第3页
SELECT * FROM students WHERE id > 20 ORDER BY id LIMIT 10;

优点：

  • 性能稳定
  • 不受偏移量影响
  • 适合无限滚动

缺点：

  • 不能跳页
  • 需要记录位置

4.4 分页性能对比

方式 性能 适用场景
LIMIT OFFSET 差 小数据量
覆盖索引 好 大数据量
游标分页 最好 无限滚动

五、全文搜索

5.1 全文索引

5.1.1 创建全文索引

语法格式：

CREATE FULLTEXT INDEX 索引名 ON 表名(列名);

示例：

-- 创建文章表
CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT(title, content)
);

-- 插入测试数据
INSERT INTO articles (title, content) VALUES
('MySQL数据库教程', 'MySQL是最流行的关系型数据库管理系统'),
('Python编程入门', 'Python是一种简单易学的编程语言'),
('Linux系统管理', 'Linux是开源的操作系统，广泛应用于服务器'),
('数据库优化技巧', '数据库优化可以提高查询性能，包括索引优化和查询优化');

-- 创建全文索引
CREATE FULLTEXT INDEX idx_content ON articles(content);

5.2 全文搜索语法

5.2.1 MATCH AGAINST

语法格式：

SELECT * FROM 表名
WHERE MATCH(列名) AGAINST('搜索词');

示例：

-- 搜索包含"数据库"的文章
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('数据库');

5.2.2 搜索模式

自然语言模式（默认）：

SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('数据库' IN NATURAL LANGUAGE MODE);

布尔模式：

-- 必须包含"数据库"
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('+数据库' IN BOOLEAN MODE);

-- 包含"数据库"或"优化"
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('数据库 优化' IN BOOLEAN MODE);

-- 包含"数据库"但不包含"MySQL"
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('+数据库 -MySQL' IN BOOLEAN MODE);

-- 包含完整短语"数据库优化"
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('"数据库优化"' IN BOOLEAN MODE);

查询扩展模式：

SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('数据库' WITH QUERY EXPANSION);

5.3 相关性排序

5.3.1 相关性得分

-- 查询相关性得分
SELECT 
    title,
    MATCH(title, content) AGAINST('数据库') AS relevance
FROM articles
WHERE MATCH(title, content) AGAINST('数据库')
ORDER BY relevance DESC;

输出：

+-----------------------+--------------------+
| title                 | relevance          |
+-----------------------+--------------------+
| MySQL数据库教程       | 0.2276446968317032 |
| 数据库优化技巧        | 0.2276446968317032 |
+-----------------------+--------------------+

5.4 中文全文搜索

5.4.1 中文分词

MySQL默认不支持中文分词，解决方案：

  1. 使用ngram分词器（MySQL 5.7.6+）
  2. 使用第三方搜索引擎（Elasticsearch、Sphinx）

ngram分词器：

-- 创建使用ngram的全文索引
CREATE TABLE articles_cn (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT INDEX ft_idx(title, content) WITH PARSER ngram
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入中文数据
INSERT INTO articles_cn (title, content) VALUES
('数据库教程', '这是一个关于数据库的教程'),
('编程入门', '学习编程从基础开始');

-- 搜索
SELECT * FROM articles_cn
WHERE MATCH(title, content) AGAINST('数据库' IN BOOLEAN MODE);

六、本章小结

6.1 核心要点

✅ GROUP BY WITH ROLLUP生成分组汇总 ✅ 窗口函数实现排名和累计计算 ✅ CTE简化复杂查询，支持递归 ✅ 分页优化提高查询性能 ✅ 全文搜索支持文本检索

6.2 验证清单

完成本章学习后，请确认您能够：

  • 使用GROUP BY WITH ROLLUP生成分组汇总
  • 使用窗口函数进行排名和累计计算
  • 使用CTE简化复杂查询
  • 优化分页查询性能
  • 使用全文搜索检索文本
上一章：多表查询基础
下一章：多表查询进阶