MySQL 使用指南：从入门到实践

登录 MySQL

1
mysql -u 用户名 -p

数据库操作

1
2
3
4
5
6
7
8
9
10
11
-- 显示所有数据库
SHOW DATABASES;

-- 创建数据库
CREATE DATABASE mydb;

-- 使用数据库
USE mydb;

-- 删除数据库
DROP DATABASE mydb;

表操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 创建表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 显示表结构
DESCRIBE users;

-- 修改表
ALTER TABLE users ADD COLUMN age INT;

-- 删除表
DROP TABLE users;

插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
INSERT INTO users (username, email, age) 
VALUES ('john_doe', 'john@example.com', 30);
## 查询数据
sql
-- 基本查询
SELECT * FROM users;

-- 条件查询
SELECT username, email FROM users WHERE age > 25;

-- 排序
SELECT * FROM users ORDER BY created_at DESC;

-- 分页
SELECT * FROM users LIMIT 10 OFFSET 20;

更新数据

1
2
3
4
UPDATE users SET age = 31 WHERE username = 'john_doe';
## 删除数据
sql
DELETE FROM users WHERE id = 1;

索引优化

1
2
3
4
5
-- 创建索引
CREATE INDEX idx_username ON users(username);

-- 显示索引
SHOW INDEX FROM users;

事务处理

1
2
3
4
5
START TRANSACTION;
INSERT INTO orders (user_id, amount) VALUES (1, 100);
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
COMMIT;
-- 或 ROLLBACK;

存储过程

1
2
3
4
5
6
7
8
9
DELIMITER //
CREATE PROCEDURE GetUserCount()
BEGIN
    SELECT COUNT(*) AS total_users FROM users;
END //
DELIMITER ;

-- 调用存储过程
CALL GetUserCount();

内连接 (INNER JOIN)

内连接返回两个表中连接字段匹配的行。

1
2
3
SELECT a.id, a.name, b.order_date, b.amount
FROM customers a
INNER JOIN orders b ON a.id = b.customer_id;

左连接 (LEFT JOIN)

左连接返回左表的所有记录，即使右表中没有匹配。

1
2
3
SELECT a.id, a.name, b.order_date, b.amount
FROM customers a
LEFT JOIN orders b ON a.id = b.customer_id;

右连接 (RIGHT JOIN)

右连接返回右表的所有记录，即使左表中没有匹配。

1
2
3
SELECT a.id, a.name, b.order_date, b.amount
FROM customers a
RIGHT JOIN orders b ON a.id = b.customer_id;

全连接 (FULL JOIN)

MySQL 不直接支持 FULL JOIN，但可以通过 UNION 实现：

1
2
3
4
5
6
7
8
SELECT a.id, a.name, b.order_date, b.amount
FROM customers a
LEFT JOIN orders b ON a.id = b.customer_id
UNION
SELECT a.id, a.name, b.order_date, b.amount
FROM customers a
RIGHT JOIN orders b ON a.id = b.customer_id
WHERE a.id IS NULL;

交叉连接 (CROSS JOIN)

返回两个表的笛卡尔积。

1
2
3
SELECT a.id, a.name, b.order_date, b.amount
FROM customers a
CROSS JOIN orders b;

自连接 (SELF JOIN)

表与自身连接。

1
2
3
SELECT a.name AS employee, b.name AS manager
FROM employees a
LEFT JOIN employees b ON a.manager_id = b.id;

多表查询的复杂示例

三表连接示例

1
2
3
4
5
6
7
8
9
10
11
12
SELECT 
    c.name AS customer_name,
    p.name AS product_name,
    o.order_date,
    oi.quantity,
    oi.price
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.order_date > '2023-01-01'
ORDER BY o.order_date DESC;

子查询作为表连接

1
2
3
4
5
6
7
8
9
SELECT 
    a.name,
    b.total_orders
FROM customers a
LEFT JOIN (
    SELECT customer_id, COUNT(*) AS total_orders
    FROM orders
    GROUP BY customer_id
) b ON a.id = b.customer_id;

SQL 关键字的执行顺序

理解 SQL 关键字的执行顺序对于编写高效查询和调试复杂 SQL 非常重要。以下是 SQL 语句的逻辑处理顺序（不是书写顺序）：

  • FROM - 确定查询的数据源

  • ON - 应用连接条件

  • JOIN - 执行表连接

  • WHERE - 过滤行数据

  • GROUP BY - 分组数据

  • HAVING - 过滤分组后的数据

  • SELECT - 选择要返回的列

  • DISTINCT - 去除重复行

  • ORDER BY - 排序结果

  • LIMIT/OFFSET - 限制返回行数

执行顺序示例分析

1
2
3
4
5
6
7
8
9
SELECT 
    department_id,
    AVG(salary) AS avg_salary
FROM employees
WHERE hire_date > '2020-01-01'
GROUP BY department_id
HAVING AVG(salary) > 5000
ORDER BY avg_salary DESC
LIMIT 10;

实际执行顺序：

  • FROM employees - 从employees表获取数据

  • WHERE hire_date > ‘2020-01-01’ - 过滤入职日期

  • GROUP BY department_id - 按部门分组

  • HAVING AVG(salary) > 5000 - 过滤平均工资>5000的部门

  • SELECT department_id, AVG(salary) - 选择要显示的列

  • ORDER BY avg_salary DESC - 按平均工资降序排序

  • LIMIT 10 - 只返回前10条记录

备注：8.0以后的with语句

一、基本语法

1
2
3
4
WITH cte_name AS (
    SELECT ...  -- 定义CTE的查询
)
SELECT * FROM cte_name;  -- 使用CTE

二、CTE 的主要类型

  1. 非递归 CTE
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    WITH department_stats AS (
        SELECT 
            department_id,
            COUNT(*) AS employee_count,
            AVG(salary) AS avg_salary
        FROM employees
        GROUP BY department_id
    )
    SELECT 
        d.department_name,
        ds.employee_count,
        ds.avg_salary
    FROM departments d
    JOIN department_stats ds ON d.department_id = ds.department_id
    ORDER BY ds.avg_salary DESC;
  2. 递归 CTE
    递归 CTE 可以引用自身，常用于处理层次结构数据（如组织结构、评论树等）。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
WITH RECURSIVE employee_hierarchy AS (
    -- 基础查询（锚成员）
    SELECT 
        id,
        name,
        manager_id,
        1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 递归查询（递归成员）
    SELECT 
        e.id,
        e.name,
        e.manager_id,
        eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy ORDER BY level, name;

三、CTE 的执行顺序

在包含 WITH 子句的查询中，执行顺序如下：

首先评估所有 CTE：按照它们在查询中出现的顺序

然后执行主查询：可以引用已定义的 CTE

CTE 只在当前查询中有效：不会影响其他查询

四、多 CTE 示例

可以在一个查询中定义多个 CTE：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
WITH 
high_salary_employees AS (
    SELECT * FROM employees WHERE salary > 10000
),
department_budgets AS (
    SELECT 
        department_id,
        SUM(salary) AS total_salary
    FROM employees
    GROUP BY department_id
)
SELECT 
    d.department_name,
    COUNT(hse.id) AS high_salary_count,
    db.total_salary
FROM departments d
LEFT JOIN high_salary_employees hse ON d.department_id = hse.department_id
JOIN department_budgets db ON d.department_id = db.department_id
GROUP BY d.department_id, d.department_name, db.total_salary;

五、CTE 与子查询的比较

特性CTE子查询
可读性高，可以命名和模块化低，嵌套层次多时难以理解
重用性可在同一查询中多次引用每次需要重复定义
递归能力支持递归查询
性能通常与子查询相当取决于具体实现

六、CTE 的实际应用场景

简化复杂查询：将复杂逻辑分解为多个逻辑块

递归查询：处理树形结构数据

数据准备：预先计算中间结果

替代视图：当只需要一次性使用时

分步调试：可以逐步构建和测试查询部分

七、性能考虑

物化：MySQL 可能会将 CTE 物化为临时表

优化器限制：某些优化可能不如直接使用子查询

递归深度：递归 CTE 有默认 1000 的深度限制，可通过 cte_max_recursion_depth 调整

八、递归 CTE 深度控制

1
2
3
4
5
6
7
8
9
10
11
12
SET SESSION cte_max_recursion_depth = 2000;  -- 增加递归深度限制

WITH RECURSIVE cte AS (
    -- 基础查询
    SELECT 1 AS n
    
    UNION ALL
    
    -- 递归部分
    SELECT n + 1 FROM cte WHERE n < 1500
)
SELECT * FROM cte;

九、CTE 与临时表的比较

特性CTE临时表
生命周期仅当前查询会话结束或显式删除
可见性仅当前查询会话中的所有查询
索引不能直接创建索引可以创建索引
存储通常内存中可以存储在磁盘

十、实际案例：员工层级与路径查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
WITH RECURSIVE emp_path AS (
    -- 基础查询：顶级管理者
    SELECT 
        id,
        name,
        manager_id,
        name AS path
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 递归查询：下属员工
    SELECT 
        e.id,
        e.name,
        e.manager_id,
        CONCAT(ep.path, ' > ', e.name) AS path
    FROM employees e
    JOIN emp_path ep ON e.manager_id = ep.id
)
SELECT * FROM emp_path ORDER BY path;