MySQL 高级技巧

一、序言

本文给大家介绍一些 MySQL 的高级技巧，帮助大家提高开发效率。

二、CASE WHEN 表达式

在 MySQL 中，CASE WHEN 表达式是一个灵活的条件判断结构，类似于其他编程语言中的 switch 或 if-else 语句。它允许根据不同的条件执行不同的操作。
假设我们有一个名为 orders 的表，它包含了订单的信息，包括 order_id 和 total_amount 两个字段。

需求：根据订单的总金额，为每个订单打上了不同的标签：‘Low’, ‘Medium’, ‘High’，或者 ‘Unknown’

SELECT 
    order_id,
    total_amount,
    CASE
        WHEN total_amount < 50 THEN 'Low'
        WHEN total_amount >= 50 AND total_amount < 100 THEN 'Medium'
        WHEN total_amount >= 100 THEN 'High'
        ELSE 'Unknown'
    END AS label
FROM 
    orders;

三、UNION

在 MySQL 中，UNION 是用于合并两个或多个 SELECT 语句的操作符。它将两个或多个查询结果集中的行合并成一个结果集，并自动去除重复的行。例如：

SELECT id, name FROM employees1
UNION
SELECT id, name FROM employees2;

以上查询会返回合并后的结果集，其中包含了 employees1 和 employees2 表中所有员工的信息。如果某个员工在两张表中都存在，UNION 会去除重复的行。如果想要保留重复的行，可以使用 UNION ALL。

SELECT id, name FROM employees1
UNION ALL
SELECT id, name FROM employees2;

四、行列转换

4.1 行转列

假设有以下 student_scores 表格：

现在我们有一个需求：将每个学生的数学和科学成绩放在同一行

SELECT
    student_id,
    MAX(CASE WHEN subject = 'Math' THEN score END) AS math_score,
    MAX(CASE WHEN subject = 'Science' THEN score END) AS science_score
FROM
    student_scores
GROUP BY
    student_id;

输出结果：

4.2 列转行

假设有以下 sales 表格：

假设一个场景：我们想要将产品销售额合并到一列中，并包含产品名称和销售额的日期。可以使用列转行操作：

SELECT
    date,
    'product_A' AS product,
    product_A AS sales
FROM
    sales
    
UNION ALL

SELECT
    date,
    'product_B' AS product,
    product_B AS sales
FROM
    sales;

输出结果：

五、LIMIT 提升效率

现假设一个场景：我们有一个 students 表格，想要检查是否存在年龄小于 18 岁的学生
通常我们的写法如下：

SELECT COUNT(1)
FROM students
WHERE age < 18;

上面这种写法是比较耗费性能的，我们可以使用 LIMIT 1 来限制结果集返回一行，然后检查是否存在符合条件的行。

SELECT 1
FROM students
WHERE age < 18
LIMIT 1;

如果存在至少一个年龄小于 18 岁的学生，这个查询将返回一行结果，否则不会返回任何结果。因为我们使用了 LIMIT 1，所以当找到第一个符合条件的行后，查询就会停止，并且不会继续搜索更多的匹配行，这样可以提高效率。

六、WITH AS

假设有两个表：students 表和 grades 表。students 表存储学生信息，包括学生ID（student_id）和姓名（student_name）。grades 表存储学生的成绩，包括学生ID（student_id）和成绩（grade）。
students 表：

grades 表：

需求：计算每个学生的平均成绩

WITH student_avg_grades AS (
  SELECT student_id, AVG(grade) AS avg_grade
  FROM grades
  GROUP BY student_id
)
SELECT s.student_id, s.student_name, g.avg_grade
FROM students s
JOIN student_avg_grades g ON s.student_id = g.student_id
ORDER BY g.avg_grade DESC;

输出结果：

七、窗口函数

6.1 ROW_NUMBER()

假设我们有一个名为 employees 的表，其中包含员工信息，包括员工姓名和工资。

需求：查询每个员工及其工资排名

SELECT
    employee_name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_number
FROM
    employees;

输出结果：

ROW_NUMBER() 为结果集中的每一行分配一个唯一的整数值，不会考虑并列排名。即使有相同的排序值，每一行的排名也是唯一的。如果有多个行具有相同的排序条件，则 ROW_NUMBER() 会为每个行分配不同的排名值，不会合并排名。

6.2 DENSE_RANK()

假设有一个名为 students 的表，其中包含学生的成绩信息，包括学生姓名和考试成绩。

需求：查询每个学生的成绩及其排名（分数相同，排名相同）

SELECT
    student_name,
    score,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM
    students;

输出结果：

DENSE_RANK() 排名如果有多个行具有相同的排序值，则它们会被分配相同的排名，并且下一个排名会紧随其后，不会留下空隙。例如，如果有两行并列排名第 2，下一个排名仍然是第 3，不会跳过任何排名。

6.3 RANK()

假设有一个名为 sales 的表，其中包含销售人员的销售额信息。

需求：每个销售人员的销售额排名

SELECT
    salesperson,
    sales,
    RANK() OVER (ORDER BY sales DESC) AS rank
FROM
    sales;

输出结果：

RANK() 为结果集中的每一行分配一个排名，如果有相同的排序值，则它们会被分配相同的排名，但下一个排名会留下空隙。也就是说，如果有并列排名，则下一个排名会跳过相应的排名数量。例如，如果有两行并列排名第二，下一个排名会是第 4，而不是第 3。

往期推荐

1. JDK 动态代理
2. ThreadLocal
3. HashMap 源码分析（三）
4. Spring 三级缓存
5. RBAC 权限设计（二）