MySQL 多表查询技巧和高阶操作实例1

MySQL 多表查询技巧和高阶操作实例1

一、多表查询基础技巧

1. JOIN 类型与场景

• INNER JOIN
  获取两表交集数据：

  SELECT orders.order_id, customers.name 
  FROM orders 
  INNER JOIN customers ON orders.customer_id = customers.id;

• LEFT JOIN / RIGHT JOIN
  保留左表或右表全部数据，未匹配字段为 NULL：

  SELECT customers.name, orders.order_id
  FROM customers
  LEFT JOIN orders ON customers.id = orders.customer_id;

• CROSS JOIN
  笛卡尔积（慎用）：

  SELECT * FROM employees CROSS JOIN departments;

2. 子查询 (Subqueries)

• 标量子查询（返回单个值）：

  SELECT name, (SELECT COUNT(*) FROM orders WHERE customer_id = c.id) AS order_count
  FROM customers c;

• EXISTS / NOT EXISTS
  检查子查询是否存在结果：

  SELECT name FROM customers c
  WHERE EXISTS (
    SELECT 1 FROM orders WHERE customer_id = c.id AND total > 1000
  );

3. UNION 联合查询

合并多个查询结果（列结构需一致）：

SELECT product_name FROM products WHERE category = 'Electronics'
UNION ALL
SELECT product_name FROM archived_products WHERE category = 'Electronics';

二、高阶操作实例

1. 多级 JOIN 嵌套

关联客户、订单、商品信息：

SELECT 
    c.name AS customer_name,
    o.order_date,
    p.product_name,
    od.quantity
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
LEFT JOIN order_details od ON o.id = od.order_id
LEFT JOIN products p ON od.product_id = p.id;

2. 窗口函数 (Window Functions)

为每个部门的员工薪水排名（MySQL 8.0+）：

SELECT 
    name, 
    department, 
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

3. CTE (公共表表达式)

递归查询层级结构（如组织架构）：

WITH RECURSIVE org_tree AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE manager_id IS NULL  -- 根节点
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    INNER JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree;

4. 动态条件筛选

使用 CASE 和 COALESCE 动态调整查询：

SELECT 
    product_id,
    SUM(CASE WHEN YEAR(order_date) = 2023 THEN quantity ELSE 0 END) AS sales_2023,
    COALESCE(SUM(quantity), 0) AS total_sales
FROM order_details
GROUP BY product_id;

三、性能优化技巧

1. 索引优化

   • 为 JOIN 字段（如 customer_id）和 WHERE 条件字段创建索引。

   • 复合索引优先覆盖高频查询条件。

2. 减少子查询嵌套
   将部分子查询改写为 JOIN：

   -- 原查询（子查询）
   SELECT name 
   FROM customers 
   WHERE id IN (SELECT customer_id FROM orders WHERE total > 1000);
   
   -- 优化后（JOIN）
   SELECT DISTINCT c.name 
   FROM customers c
   JOIN orders o ON c.id = o.customer_id
   WHERE o.total > 1000;

3. 分页优化
   使用 LIMIT 和游标分页，避免 OFFSET 性能问题：

   SELECT * FROM orders 
   WHERE id > 1000  -- 上一页最后一条记录的ID
   ORDER BY id 
   LIMIT 10;

四、常见问题解决

1. 重复数据

使用 DISTINCT 或 GROUP BY 去重：

SELECT DISTINCT customer_id FROM orders;

2. NULL 值处理

使用 COALESCE 设置默认值：

SELECT 
    name, 
    COALESCE(email, 'N/A') AS email 
FROM customers;

掌握多表查询技巧可大幅提升复杂数据分析效率，重点在于理解表关系、选择合适连接方式，并持续优化查询性能。