滚雪球学MySQL[3.3讲]：MySQL复杂查询详解：CASE语句、自连接与视图管理-3.3 复杂查询

时间：2024-10-02 22:45:31

在实际数据库应用中，很多查询逻辑需要复杂的条件判断、表内数据关联以及简化查询等功能。MySQL提供了多种复杂查询方式来处理这些需求，包括CASE语句、自连接和视图，这些功能可以帮助开发者解决复杂的数据提取问题。

1. CASE 语句

CASE语句用于在查询中实现条件判断逻辑，它可以根据不同条件返回不同的结果，类似于编程语言中的if-else或switch-case结构。

基本语法
SELECT 列1, 
       CASE 
           WHEN 条件1 THEN 结果1
           WHEN 条件2 THEN 结果2
           ELSE 默认结果
       END AS 别名
FROM 表名;
示例1：根据条件动态计算学生等级

假设我们有一个grades表，记录了学生的成绩，现在我们想根据成绩计算每个学生的等级：

SELECT student_id, score, 
       CASE 
           WHEN score >= 90 THEN 'A'
           WHEN score >= 80 THEN 'B'
           WHEN score >= 70 THEN 'C'
           ELSE 'D'
       END AS grade
FROM grades;

在此查询中，CASE语句根据学生的成绩返回不同的等级。如果成绩高于90分，则返回’A’，以此类推。

示例2：结合聚合函数和CASE

我们可以将CASE语句与聚合函数结合使用。例如，我们想统计每个班级中的学生数量，并按性别分类：

SELECT class_id, 
       SUM(CASE WHEN gender = 'Male' THEN 1 ELSE 0 END) AS male_count,
       SUM(CASE WHEN gender = 'Female' THEN 1 ELSE 0 END) AS female_count
FROM students
GROUP BY class_id;

这个查询通过CASE语句计算每个班级中的男生和女生人数，使用SUM函数进行计数。

2. 自连接（Self JOIN）

自连接（Self JOIN）是指将同一张表自己连接自己，用于处理表内数据之间的关联关系。自连接常用于解决表中需要关联自身的场景，例如员工的上下级关系、产品的组合关系等。

基本语法
SELECT A.列1, B.列2
FROM 表名 A
JOIN 表名 B ON A.列X = B.列Y;

在自连接中，我们通过给同一张表取不同的别名（如A和B），使其在查询中充当不同的角色。

示例1：查询员工的上级信息

假设我们有一个employees表，包含员工的id、name和manager_id（上级ID）。现在我们想查询每个员工及其上级的名字：

SELECT e1.name AS employee_name, e2.name AS manager_name
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

在这个查询中，我们将employees表自连接，通过manager_id字段关联员工和其上级。e1代表员工，e2代表上级，查询返回每个员工及其对应的上级。

示例2：查找同一部门内的产品关联

假设我们有一个products表，记录了产品的id、name和category_id（类别ID），我们希望查找同一类别中的其他产品：

SELECT p1.name AS product_name, p2.name AS related_product
FROM products p1
JOIN products p2 ON p1.category_id = p2.category_id AND p1.id != p2.id;

通过自连接，我们可以查找属于相同类别但ID不同的其他产品，这在推荐系统中非常有用。

3. 视图（VIEW）的使用与管理

**视图（VIEW）**是从数据库表中提取出来的虚拟表，视图并不存储实际数据，而是存储查询的定义。通过视图，用户可以简化复杂的查询操作，提升查询的可读性和复用性。

3.1 创建视图

视图的创建非常简单，只需定义一个SQL查询语句，并将其赋予视图名即可。

基本语法
CREATE VIEW 视图名 AS
SELECT 列1, 列2, ... FROM 表名 WHERE 条件;
示例1：创建学生成绩视图

假设我们经常需要查询学生的名字及其成绩，可以创建一个视图，将学生和成绩表连接起来：

CREATE VIEW student_scores AS
SELECT s.name, g.score
FROM students s
JOIN grades g ON s.id = g.student_id;

这样，未来只需简单地查询视图student_scores，即可获取学生及其成绩数据：

SELECT * FROM student_scores;
3.2 修改视图

如果需要修改视图的定义，可以使用CREATE OR REPLACE语句：

CREATE OR REPLACE VIEW 视图名 AS
SELECT 列1, 列2, ... FROM 表名 WHERE 新条件;
3.3 删除视图

如果不再需要某个视图，可以将其删除：

DROP VIEW 视图名;
3.4 视图的优缺点
优点：
  • 简化复杂查询：通过视图，可以将复杂的SQL查询封装为一个简单的查询调用，提升代码的可读性。
  • 数据安全：视图可以隐藏某些敏感的表结构或数据，只展示需要的数据，从而提升安全性。
  • 代码复用：创建视图后，多个查询可以复用该视图，避免重复编写相同的复杂查询逻辑。
缺点：
  • 性能问题：视图是基于查询定义动态生成的，频繁使用复杂视图可能导致查询性能下降。
  • 非持久化：视图不存储数据，而是实时生成，因此其性能可能不如实际存储的数据表。

高级应用与拓展

复杂查询中的CASE语句、自连接和视图还可以与其他MySQL功能结合使用，例如在子查询中使用CASE语句，或者在视图中结合聚合函数生成复杂的统计报告。

示例：使用视图与自连接结合

假设我们需要查询所有员工及其直接下属的名字，可以创建一个视图来简化这个查询：

CREATE VIEW employee_hierarchy AS
SELECT e1.name AS employee_name, e2.name AS subordinate_name
FROM employees e1
LEFT JOIN employees e2 ON e1.id = e2.manager_id;

这样，以后只需查询employee_hierarchy视图，即可获得所有员工及其下属的关系：

SELECT * FROM employee_hierarchy;

结语与下期预告

通过本期内容的学习，你已经掌握了MySQL的复杂查询技巧，包括CASE语句的条件判断、自连接的表内关联以及视图的创建与管理。掌握这些技巧后，你将能够应对各种复杂的数据库查询场景，提升你的SQL技能。

在下期内容中，我们将进入MySQL索引的基础知识（4.1），探讨如何使用索引提升查询性能，优化数据库的查询效率。索引是数据库性能优化的核心工具之一，敬请期待！

相关文章