MySQL数据库SQL查询封神之路：从基础到高阶的完整指南

MySQL作为最流行的开源关系型数据库，掌握其查询技巧是每个开发者的必备技能。本文将系统梳理MySQL查询的核心操作与高阶玩法，帮助你从入门到精通。

一、基础查询操作

1.1 基本查询语法

-- 查询所有字段
SELECT * FROM table_name;

-- 查询指定字段
SELECT column1, column2 FROM table_name;

-- 条件筛选
SELECT * FROM table_name WHERE condition;

-- 去重查询
SELECT DISTINCT column FROM table_name;

1.2 条件查询进阶

-- 比较运算符
SELECT * FROM users WHERE age > 18 AND gender = '男';

-- 模糊查询
SELECT * FROM users WHERE name LIKE '张%';

-- 范围查询
SELECT * FROM products WHERE price BETWEEN 100 AND 500;

-- IN查询
SELECT * FROM users WHERE city IN ('北京', '上海', '广州');

-- NULL值处理
SELECT * FROM users WHERE email IS NULL;

1.3 排序与分页

-- 单字段排序
SELECT * FROM users ORDER BY age DESC;

-- 多字段排序
SELECT * FROM users ORDER BY department ASC, salary DESC;

-- 分页查询
SELECT * FROM users LIMIT 10 OFFSET 20;  -- 第3页，每页10条
SELECT * FROM users LIMIT 20, 10;       -- 从第21条开始，取10条

二、聚合函数与分组统计

2.1 常用聚合函数

-- 统计总数
SELECT COUNT(*) FROM users;

-- 求和
SELECT SUM(salary) FROM employees;

-- 平均值
SELECT AVG(score) FROM students;

-- 最大值/最小值
SELECT MAX(price), MIN(price) FROM products;

2.2 分组统计

-- 按部门分组统计人数
SELECT department, COUNT(*) FROM employees GROUP BY department;

-- 按城市分组统计平均工资
SELECT city, AVG(salary) FROM employees GROUP BY city;

-- 分组后筛选（HAVING）
SELECT department, AVG(salary) FROM employees 
GROUP BY department 
HAVING AVG(salary) > 10000;

三、多表连接查询

3.1 内连接（INNER JOIN）

-- 查询员工及其部门信息
SELECT e.name, d.department_name 
FROM employees e 
INNER JOIN departments d ON e.department_id = d.id;

3.2 左连接（LEFT JOIN）

-- 查询所有员工，即使没有部门信息
SELECT e.name, d.department_name 
FROM employees e 
LEFT JOIN departments d ON e.department_id = d.id;

3.3 右连接（RIGHT JOIN）

-- 查询所有部门，即使没有员工
SELECT e.name, d.department_name 
FROM employees e 
RIGHT JOIN departments d ON e.department_id = d.id;

3.4 多表连接

-- 三表连接：员工-部门-职位
SELECT e.name, d.department_name, p.position_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
INNER JOIN positions p ON e.position_id = p.id;

四、子查询与嵌套查询

4.1 WHERE子句中的子查询

-- 查询工资高于平均工资的员工
SELECT * FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);

-- 查询与SMITH同部门的员工
SELECT * FROM employees 
WHERE department_id = (
    SELECT department_id FROM employees WHERE name = 'SMITH'
);

4.2 FROM子句中的子查询

-- 查询每个部门工资最高的员工
SELECT e.name, e.salary, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
WHERE e.salary = (
    SELECT MAX(salary) FROM employees WHERE department_id = e.department_id
);

4.3 EXISTS子查询

-- 查询有订单的客户
SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

五、高级查询技巧

5.1 CASE表达式

-- 根据工资等级分类
SELECT name, salary,
    CASE 
        WHEN salary > 10000 THEN '高薪'
        WHEN salary > 5000 THEN '中薪'
        ELSE '低薪'
    END AS salary_level
FROM employees;

5.2 窗口函数（MySQL 8.0+）

-- 按部门分组，计算工资排名
SELECT name, department, salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

-- 计算累计工资
SELECT name, salary,
    SUM(salary) OVER (ORDER BY hire_date) AS cumulative_salary
FROM employees;

5.3 公用表表达式（CTE）

-- 递归查询组织架构
WITH RECURSIVE org_tree AS (
    -- 基础查询：顶级部门
    SELECT id, name, parent_id, 1 AS level
    FROM departments
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- 递归部分：子部门
    SELECT d.id, d.name, d.parent_id, ot.level + 1
    FROM departments d
    JOIN org_tree ot ON d.parent_id = ot.id
)
SELECT * FROM org_tree ORDER BY level, id;

5.4 UNION查询

-- 合并两个查询结果（去重）
SELECT name FROM table1
UNION
SELECT name FROM table2;

-- 合并两个查询结果（包含重复）
SELECT name FROM table1
UNION ALL
SELECT name FROM table2;

六、查询优化策略

6.1 索引优化

-- 创建单列索引
CREATE INDEX idx_email ON users(email);

-- 创建复合索引（注意最左前缀原则）
CREATE INDEX idx_name_age ON users(name, age);

-- 覆盖索引
CREATE INDEX idx_covering ON orders(user_id, status, created_at);

6.2 查询语句优化

-- 避免SELECT *
SELECT id, name, email FROM users;  -- 只查询需要的列

-- 避免在索引列上使用函数
SELECT * FROM users WHERE YEAR(created_at) = 2024;  -- 错误写法
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';  -- 正确写法

-- 优化分页查询（深分页问题）
SELECT * FROM orders 
WHERE id > (SELECT id FROM orders ORDER BY created_at DESC LIMIT 1000000, 1)
ORDER BY created_at DESC LIMIT 10;

6.3 使用EXPLAIN分析查询计划

-- 分析查询执行计划
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- 详细格式
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE email = 'test@example.com';

七、实战案例

7.1 电商场景：查询用户订单详情

SELECT 
    u.name AS user_name,
    o.order_id,
    o.order_date,
    p.product_name,
    oi.quantity,
    oi.price,
    oi.quantity * oi.price AS total_amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE u.id = 1001
ORDER BY o.order_date DESC;

7.2 统计场景：按月统计销售额

SELECT 
    YEAR(order_date) AS year,
    MONTH(order_date) AS month,
    SUM(quantity * price) AS total_sales,
    COUNT(DISTINCT user_id) AS active_users
FROM orders o
INNER JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY year DESC, month DESC;

7.3 复杂场景：查询每个部门工资最高的员工

SELECT 
    d.department_name,
    e.name,
    e.salary
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
WHERE (e.department_id, e.salary) IN (
    SELECT department_id, MAX(salary)
    FROM employees
    GROUP BY department_id
);

八、性能监控与维护

8.1 开启慢查询日志

-- 查看当前设置
SHOW VARIABLES LIKE '%slow_query%';
SHOW VARIABLES LIKE '%long_query_time%';

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;  -- 单位：秒
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

8.2 表维护

-- 分析表统计信息
ANALYZE TABLE users;

-- 优化表（整理碎片）
OPTIMIZE TABLE orders;

-- 查看索引使用情况
SELECT * FROM sys.schema_index_statistics;
SELECT * FROM sys.schema_redundant_indexes;

总结

MySQL查询优化是一个持续的过程，需要从索引设计、查询语句编写、表结构优化等多个维度进行综合考虑。掌握以上核心操作和高阶技巧，能够显著提升数据库查询性能，为业务系统提供更好的数据支撑能力。建议在实际工作中不断实践和总结，形成自己的优化方法论。