MySQL SQL 完全指南：从小白到高手的进阶之路（含面试宝典）

-- 创建数据库
CREATE DATABASE shop DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 查看所有数据库
SHOW DATABASES;

-- 使用数据库
USE shop;

-- 查看当前数据库
SELECT DATABASE();

-- 删除数据库（谨慎操作！）
DROP DATABASE shop;

-- 创建表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
    email VARCHAR(100) NOT NULL COMMENT '邮箱',
    password VARCHAR(255) NOT NULL COMMENT '密码',
    age TINYINT UNSIGNED DEFAULT 0 COMMENT '年龄',
    status TINYINT DEFAULT 1 COMMENT '状态：1正常 0禁用',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

-- 查看表结构
DESC users;
SHOW CREATE TABLE users;

-- 修改表
ALTER TABLE users ADD COLUMN phone VARCHAR(20) COMMENT '手机号';  -- 添加列
ALTER TABLE users MODIFY COLUMN phone VARCHAR(15);                -- 修改列类型
ALTER TABLE users CHANGE phone mobile VARCHAR(15);                -- 重命名列
ALTER TABLE users DROP COLUMN mobile;                             -- 删除列

-- 删除表
DROP TABLE IF EXISTS users;

-- 单行插入
INSERT INTO users (username, email, password, age)
VALUES ('zhangsan', '[email protected]', 'hashed_password', 25);

-- 多行插入（推荐，效率更高）
INSERT INTO users (username, email, password, age) VALUES
('lisi', '[email protected]', 'hashed_password', 30),
('wangwu', '[email protected]', 'hashed_password', 28),
('zhaoliu', '[email protected]', 'hashed_password', 35);

-- 插入或更新（存在则更新）
INSERT INTO users (id, username, email, password)
VALUES (1, 'zhangsan', '[email protected]', 'new_password')
ON DUPLICATE KEY UPDATE email = VALUES(email), password = VALUES(password);

-- 插入忽略重复（存在则跳过）
INSERT IGNORE INTO users (username, email, password)
VALUES ('zhangsan', '[email protected]', 'password');

-- 查询所有列（不推荐，影响性能）
SELECT * FROM users;

-- 查询指定列（推荐）
SELECT id, username, email FROM users;

-- 使用别名
SELECT id AS user_id, username AS name FROM users;

-- 去重
SELECT DISTINCT status FROM users;

-- 限制行数
SELECT * FROM users LIMIT 10;          -- 前 10 行
SELECT * FROM users LIMIT 10 OFFSET 20; -- 跳过 20 行取 10 行
SELECT * FROM users LIMIT 20, 10;       -- 同上，简写形式

-- 比较运算符
SELECT * FROM users WHERE age >= 18;
SELECT * FROM users WHERE status != 0;
SELECT * FROM users WHERE age <> 18;  -- 不等于的另一种写法

-- 逻辑运算符
SELECT * FROM users WHERE age >= 18 AND status = 1;
SELECT * FROM users WHERE age < 18 OR age > 60;
SELECT * FROM users WHERE NOT status = 0;

-- 范围查询
SELECT * FROM users WHERE age BETWEEN 18 AND 30;   -- 包含边界
SELECT * FROM users WHERE age IN (18, 20, 25, 30);

-- 空值判断
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

-- 模糊查询
SELECT * FROM users WHERE username LIKE 'zhang%';  -- 以 zhang 开头
SELECT * FROM users WHERE username LIKE '%san';    -- 以 san 结尾
SELECT * FROM users WHERE username LIKE '%ang%';   -- 包含 ang
SELECT * FROM users WHERE username LIKE 'zhang_';  -- _ 匹配单个字符

-- 升序（默认）
SELECT * FROM users ORDER BY age ASC;

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

-- 多字段排序
SELECT * FROM users ORDER BY status DESC, created_at DESC;

-- NULL 值排序（MySQL 中 NULL 被视为最小值）
SELECT * FROM users ORDER BY age IS NULL, age;  -- NULL 排最后

-- 基本分组
SELECT status, COUNT(*) AS user_count
FROM users
GROUP BY status;

-- 多字段分组
SELECT status, age, COUNT(*) AS count
FROM users
GROUP BY status, age;

-- HAVING 过滤分组（WHERE 在分组前过滤，HAVING 在分组后过滤）
SELECT status, COUNT(*) AS user_count
FROM users
GROUP BY status
HAVING user_count > 10;

-- WITH ROLLUP 添加汇总行
SELECT status, COUNT(*) AS user_count
FROM users
GROUP BY status WITH ROLLUP;

-- 更新单个字段
UPDATE users SET status = 0 WHERE id = 1;

-- 更新多个字段
UPDATE users SET status = 0, updated_at = NOW() WHERE id = 1;

-- 条件更新
UPDATE users SET status = 0 WHERE last_login < '2024-01-01';

-- 使用 CASE WHEN 批量更新
UPDATE users SET status = CASE
    WHEN age < 18 THEN 0
    WHEN age >= 60 THEN 2
    ELSE 1
END
WHERE id IN (1, 2, 3, 4, 5);

-- 限制更新行数
UPDATE users SET status = 0 ORDER BY created_at LIMIT 100;

-- 条件删除
DELETE FROM users WHERE status = 0;

-- 限制删除行数
DELETE FROM users WHERE status = 0 ORDER BY created_at LIMIT 100;

-- 清空表（TRUNCATE 更快，但不可回滚）
TRUNCATE TABLE users;

-- DELETE vs TRUNCATE
-- DELETE: 逐行删除，可回滚，触发器会执行，自增 ID 继续
-- TRUNCATE: 直接清空，不可回滚，触发器不执行，自增 ID 重置

-- 订单表
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    status TINYINT DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 商品表
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INT DEFAULT 0
);

-- 订单详情表
CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

-- INNER JOIN（内连接）：只返回两表都匹配的行
SELECT u.username, o.id AS order_id, o.total_amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN（左连接）：返回左表所有行，右表无匹配则为 NULL
SELECT u.username, o.id AS order_id, o.total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- RIGHT JOIN（右连接）：返回右表所有行，左表无匹配则为 NULL
SELECT u.username, o.id AS order_id, o.total_amount
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- 查找没有订单的用户
SELECT u.username
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

-- 自连接：表与自身连接
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- 三表关联：查询订单详情
SELECT
    u.username,
    o.id AS order_id,
    p.name AS product_name,
    oi.quantity,
    oi.price
FROM orders o
INNER JOIN users u ON o.user_id = u.id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.status = 1
ORDER BY o.created_at DESC;

-- 标量子查询（返回单个值）
SELECT * FROM users
WHERE age > (SELECT AVG(age) FROM users);

-- 行子查询（返回一行）
SELECT * FROM users
WHERE (age, status) = (SELECT MAX(age), 1 FROM users);

-- 列子查询（返回一列）
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total_amount > 1000);

-- EXISTS 子查询（判断是否存在）
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- NOT EXISTS（找出没有订单的用户）
SELECT * FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- UNION（去重）
SELECT username, email FROM users WHERE status = 1
UNION
SELECT username, email FROM archived_users WHERE status = 1;

-- UNION ALL（不去重，性能更好）
SELECT username, 'active' AS type FROM users WHERE status = 1
UNION ALL
SELECT username, 'inactive' AS type FROM users WHERE status = 0;

-- ROW_NUMBER()：为每行分配唯一序号
SELECT
    username,
    age,
    ROW_NUMBER() OVER (ORDER BY age DESC) AS rank
FROM users;

-- RANK()：相同值同排名，后续跳过
SELECT
    username,
    age,
    RANK() OVER (ORDER BY age DESC) AS rank
FROM users;

-- DENSE_RANK()：相同值同排名，后续不跳过
SELECT
    username,
    age,
    DENSE_RANK() OVER (ORDER BY age DESC) AS rank
FROM users;

-- 分组排名（每个 status 内排名）
SELECT
    username,
    status,
    age,
    ROW_NUMBER() OVER (PARTITION BY status ORDER BY age DESC) AS rank_in_group
FROM users;

-- LAG/LEAD：获取前/后行的值
SELECT
    username,
    created_at,
    LAG(created_at, 1) OVER (ORDER BY created_at) AS prev_created,
    LEAD(created_at, 1) OVER (ORDER BY created_at) AS next_created
FROM users;

-- 累计求和
SELECT
    username,
    total_amount,
    SUM(total_amount) OVER (ORDER BY created_at) AS running_total
FROM orders;

-- 基本 CTE
WITH active_users AS (
    SELECT * FROM users WHERE status = 1
)
SELECT * FROM active_users WHERE age >= 18;

-- 多个 CTE
WITH
    active_users AS (
        SELECT * FROM users WHERE status = 1
    ),
    user_orders AS (
        SELECT user_id, COUNT(*) AS order_count
        FROM orders
        GROUP BY user_id
    )
SELECT u.username, COALESCE(o.order_count, 0) AS orders
FROM active_users u
LEFT JOIN user_orders o ON u.id = o.user_id;

-- 递归 CTE（如组织架构）
WITH RECURSIVE org_tree 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, t.level + 1
    FROM employees e
    INNER JOIN org_tree t ON e.manager_id = t.id
)
SELECT * FROM org_tree;

-- 主键索引（PRIMARY KEY）
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT
);

-- 唯一索引（UNIQUE）
CREATE UNIQUE INDEX idx_email ON users(email);
-- 或
ALTER TABLE users ADD UNIQUE INDEX idx_email(email);

-- 普通索引（INDEX）
CREATE INDEX idx_username ON users(username);

-- 复合索引（多列索引）
CREATE INDEX idx_status_created ON users(status, created_at);

-- 前缀索引（长字符串字段）
CREATE INDEX idx_email_prefix ON users(email(10));

-- 全文索引（FULLTEXT）
CREATE FULLTEXT INDEX idx_content ON articles(content);

-- 示例：假设 users 表有主键 id 和索引 idx_username

-- 这个查询需要回表
SELECT * FROM users WHERE username = 'zhangsan';
-- 1. 通过 idx_username 找到 username='zhangsan' 的主键 id
-- 2. 再通过主键 id 查询聚簇索引获取完整数据

-- 这个查询不需要回表（覆盖索引）
SELECT id, username FROM users WHERE username = 'zhangsan';
-- 二级索引已包含 id 和 username，直接返回

-- 1. 对索引列使用函数
SELECT * FROM users WHERE YEAR(created_at) = 2024;  -- ❌ 索引失效
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';  -- ✅ 可以用索引

-- 2. 隐式类型转换
-- 假设 phone 是 VARCHAR 类型
SELECT * FROM users WHERE phone = 13800138000;  -- ❌ 数字比较，索引失效
SELECT * FROM users WHERE phone = '13800138000';  -- ✅ 字符串比较

-- 3. LIKE 左模糊
SELECT * FROM users WHERE username LIKE '%zhang';  -- ❌ 索引失效
SELECT * FROM users WHERE username LIKE 'zhang%';  -- ✅ 可以用索引

-- 4. OR 连接非索引列
SELECT * FROM users WHERE username = 'zhang' OR age = 25;  -- 如果 age 没有索引，整个查询不走索引

-- 5. 联合索引不满足最左前缀
-- 假设有索引 idx_a_b_c(a, b, c)
SELECT * FROM users WHERE a = 1 AND b = 2;  -- ✅ 使用索引
SELECT * FROM users WHERE b = 2 AND c = 3;  -- ❌ 不满足最左前缀，索引失效
SELECT * FROM users WHERE a = 1 AND c = 3;  -- ⚠️ 只能使用 a 列索引

-- 6. 使用不等于
SELECT * FROM users WHERE status != 1;  -- ❌ 大概率全表扫描

-- 7. IS NOT NULL（视情况）
SELECT * FROM users WHERE email IS NOT NULL;  -- 如果大部分都不为 NULL，可能全表扫描更快

-- 复合索引 idx_a_b_c(a, b, c)

-- ✅ 可以使用索引
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3
WHERE a = 1 AND c = 3  -- 只能用到 a

-- ❌ 无法使用索引
WHERE b = 2
WHERE c = 3
WHERE b = 2 AND c = 3

-- 假设有索引 idx_name_age(name, age)

-- 覆盖索引：查询列都在索引中
SELECT name, age FROM users WHERE name = 'zhang';  -- ✅ Using index

-- 需要回表：查询列不全在索引中
SELECT name, age, email FROM users WHERE name = 'zhang';  -- 需要回表查 email

-- 开始事务
START TRANSACTION;
-- 或
BEGIN;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

-- 设置保存点
SAVEPOINT sp1;
ROLLBACK TO sp1;

-- 示例：转账事务
START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

-- 检查约束
SELECT balance FROM accounts WHERE user_id = 1;
-- 如果余额为负，回滚
-- ROLLBACK;

COMMIT;

-- 查看当前隔离级别
SELECT @@transaction_isolation;

-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 共享锁（S Lock）：允许多个事务同时读
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;
-- MySQL 8.0+ 新语法
SELECT * FROM users WHERE id = 1 FOR SHARE;

-- 排他锁（X Lock）：只允许一个事务读写
SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- 间隙锁（Gap Lock）：锁定索引记录之间的间隙
-- 在 RR 隔离级别下，范围查询会产生间隙锁
SELECT * FROM users WHERE age > 20 FOR UPDATE;  -- 锁定 age > 20 的间隙

-- 临键锁（Next-Key Lock）= 记录锁 + 间隙锁
-- InnoDB 默认的行锁算法

-- 事务 A
START TRANSACTION;
UPDATE users SET status = 1 WHERE id = 1;  -- 锁定 id=1
UPDATE users SET status = 1 WHERE id = 2;  -- 等待 id=2 的锁...

-- 事务 B
START TRANSACTION;
UPDATE users SET status = 2 WHERE id = 2;  -- 锁定 id=2
UPDATE users SET status = 2 WHERE id = 1;  -- 等待 id=1 的锁...

-- 死锁！InnoDB 会检测到并回滚其中一个事务

-- 查看死锁日志
SHOW ENGINE INNODB STATUS;

-- 查看当前锁等待
SELECT * FROM performance_schema.data_lock_waits;

EXPLAIN SELECT * FROM users WHERE username = 'zhangsan';

-- 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 超过 1 秒记录

-- 分析慢查询日志
-- 使用 mysqldumpslow 工具
-- mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log

-- 问题：深分页性能很差
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;  -- 需要扫描 100 万行

-- 优化方案1：延迟关联
SELECT * FROM orders o
INNER JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 10) t
ON o.id = t.id;

-- 优化方案2：游标分页（需要知道上一页最后一条的 id）
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 10;

-- 问题：一次更新太多行，锁等待严重
UPDATE orders SET status = 2 WHERE created_at < '2024-01-01';

-- 优化：分批更新
UPDATE orders SET status = 2
WHERE created_at < '2024-01-01' AND status != 2
LIMIT 1000;

-- 循环执行直到影响行数为 0

-- COUNT(*) vs COUNT(1) vs COUNT(列名)
-- MySQL 官方优化：COUNT(*) 和 COUNT(1) 效率相同，都统计所有行
-- COUNT(列名) 只统计该列非 NULL 的行

-- 对于 InnoDB，COUNT(*) 需要全表扫描
-- 优化方案：
-- 1. 使用近似值：SHOW TABLE STATUS LIKE 'orders';
-- 2. 使用缓存（Redis）维护计数
-- 3. 使用汇总表

-- 问题：filesort 性能差
SELECT * FROM orders WHERE status = 1 ORDER BY created_at DESC LIMIT 10;

-- 优化：创建复合索引
CREATE INDEX idx_status_created ON orders(status, created_at);
-- 索引已经有序，无需额外排序

-- 延迟关联
SELECT * FROM t
INNER JOIN (SELECT id FROM t ORDER BY id LIMIT 1000000, 10) t2
ON t.id = t2.id;

-- 游标分页
SELECT * FROM t WHERE id > last_id ORDER BY id LIMIT 10;

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    class_id INT,
    score INT
);

CREATE TABLE classes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);

-- 1. 查询每个班级的平均分，并按平均分降序排列
SELECT c.name AS class_name, AVG(s.score) AS avg_score
FROM classes c
LEFT JOIN students s ON c.id = s.class_id
GROUP BY c.id
ORDER BY avg_score DESC;

-- 2. 查询每个班级中分数最高的学生
SELECT s.*
FROM students s
INNER JOIN (
    SELECT class_id, MAX(score) AS max_score
    FROM students
    GROUP BY class_id
) t ON s.class_id = t.class_id AND s.score = t.max_score;

-- 3. 查询分数高于平均分的学生
SELECT *
FROM students
WHERE score > (SELECT AVG(score) FROM students);

-- 4. 使用窗口函数查询每个班级的学生排名
SELECT
    name,
    class_id,
    score,
    RANK() OVER (PARTITION BY class_id ORDER BY score DESC) AS rank_in_class
FROM students;

-- 5. 查询连续三天都有登录记录的用户
WITH ranked_logins AS (
    SELECT
        user_id,
        login_date,
        DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) AS grp
    FROM login_logs
)
SELECT DISTINCT user_id
FROM ranked_logins
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;

-- 1. 统计每天的订单数和销售额
SELECT
    DATE(created_at) AS order_date,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_sales
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY DATE(created_at)
ORDER BY order_date;

-- 2. 统计每个用户的首单时间和总消费
SELECT
    user_id,
    MIN(created_at) AS first_order_time,
    SUM(total_amount) AS total_spent,
    COUNT(*) AS order_count
FROM orders
GROUP BY user_id;

-- 3. 查询消费金额排名前 10 的用户
SELECT
    u.id,
    u.username,
    COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id
ORDER BY total_spent DESC
LIMIT 10;

-- 4. 统计商品销量排行（带月份维度）
SELECT
    DATE_FORMAT(o.created_at, '%Y-%m') AS month,
    p.name AS product_name,
    SUM(oi.quantity) AS total_quantity
FROM order_items oi
JOIN orders o ON oi.order_id = o.id
JOIN products p ON oi.product_id = p.id
GROUP BY month, p.id
ORDER BY month, total_quantity DESC;