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

登录 MySQL

mysql -u 用户名 -p

数据库操作

-- 显示所有数据库
SHOW DATABASES;

-- 创建数据库
CREATE DATABASE mydb;

-- 使用数据库
USE mydb;

-- 删除数据库
DROP DATABASE mydb;

表操作

-- 创建表
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;

插入数据

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;

更新数据

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

索引优化

-- 创建索引
CREATE INDEX idx_username ON users(username);

-- 显示索引
SHOW INDEX FROM users;

事务处理

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

存储过程

DELIMITER //
CREATE PROCEDURE GetUserCount()
BEGIN
    SELECT COUNT(*) AS total_users FROM users;
END //
DELIMITER ;

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

内连接 (INNER JOIN)

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

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)

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

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)

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

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 实现：

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)

返回两个表的笛卡尔积。

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

自连接 (SELF JOIN)

表与自身连接。

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

多表查询的复杂示例

三表连接示例

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;

子查询作为表连接

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 - 限制返回行数

执行顺序示例分析

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语句

一、基本语法

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

二、CTE 的主要类型

非递归 CTE

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;

递归 CTE
递归 CTE 可以引用自身，常用于处理层次结构数据（如组织结构、评论树等）。

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：

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 深度控制

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

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

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;