MySQL 高级技巧

一、序言

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

二、CASE WHEN 表达式

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

order_id (INT)	total_amount (DECIMAL)
1	25.50
2	75.00
3	110.25
4	40.75
5	95.20

需求：根据订单的总金额，为每个订单打上了不同的标签：‘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 表格：

student_id	subject	score
1	Math	85
1	Science	90
2	Math	75
2	Science	80

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

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;

输出结果：

student_id	math_score	science_score
1	85	90
2	75	80

4.2 列转行

假设有以下 sales 表格：

date	product_A	product_B
2024-01-01	100	120
2024-01-02	150	130

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

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;

输出结果：

date	product	sales
2024-01-01	product_A	100
2024-01-01	product_B	120
2024-01-02	product_A	150
2024-01-02	product_B	130

五、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 表：

student_id	student_name
1	Alice
2	Bob
3	Charlie

grades 表：

grade_id	student_id	grade
1	1	85.0
2	1	90.0
3	2	88.0
4	2	92.0
5	3	75.0
6	3	80.0

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

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;

输出结果：

student_id	student_name	avg_grade
2	Bob	90.0
1	Alice	87.5
3	Charlie	77.5

七、窗口函数

6.1 ROW_NUMBER()

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

employee_name	salary
Alice	5000.00
Bob	5500.00
Charlie	5500.00
David	6500.00
Eva	5500.00

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

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

输出结果：

employee_name	salary	row_number
David	6500.00	1
Bob	5500.00	2
Charlie	5500.00	3
Eva	5500.00	4
Alice	5000.00	5

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

6.2 DENSE_RANK()

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

student_name	score
Alice	85.50
Bob	78.00
Charlie	92.75
David	85.50
Eva	78.00

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

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

输出结果：

student_name	score	dense_rank
Charlie	92.75	1
Alice	85.50	2
David	85.50	2
Bob	78.00	3
Eva	78.00	3

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

6.3 RANK()

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

salesperson	sales
Alice	15000.00
Bob	12000.00
Charlie	18000.00
David	15000.00
Eva	12000.00

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

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

输出结果：

salesperson	sales	rank
Charlie	18000.00	1
Alice	15000.00	2
David	15000.00	2
Bob	12000.00	4
Eva	12000.00	4

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

往期推荐

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