一人即帝国

主题

04 MySQL — 学习指南 ​

学习理念：SQL 是声明式语言——你告诉数据库"要什么"，不用管"怎么找"。 90% 的日常查询 AI 可以直接帮你写，你需要理解的是表怎么设计、数据怎么关联、事务怎么保证正确。

学习路径 ​

建库建表 → 增删改查 → 表关联 → 高级查询 → 约束/事务 → Python 集成

① 🟡 环境搭建（MySQL 安装 + GUI 工具替代方案）
② 🟢 SQL 基础：DDL/DML（建库建表/增删改）
③ 🟢 基本查询与运算符（SELECT 的灵魂）
④ 🟢 数据类型与函数（字符串/日期/数学/聚合）
⑤ 🟡 窗口函数（OVER/PARTITION BY）
⑥ 🟡 连接查询 JOIN（内/外/自连接，最重要的查询）
⑦ 🟢 分组/排序/分页（GROUP BY/ORDER BY/LIMIT）
⑧ 🟡 子查询与 CTE（嵌套查询）
⑨ 🟢 约束与表设计（主键/外键/唯一）
⑩ 🟠 事务与隔离级别（⚠️ ACID 原理）
⑪ 🟢 Python 连接 MySQL（pymysql/SQLAlchemy）

本节 AI 替代率：~95% | 人工干预率：~5%

AI 擅长：写 SQL 查询、优化慢查询、解释执行计划。
人类需决策：表结构设计、事务隔离级别选择、索引策略。

1. 环境搭建 ​

软件工具说明 ​

软件用途是否必须建议
MySQL 8.0.26 installer数据库服务端✅ 需要或用 Docker：docker run --name mysql -e MYSQL_ROOT_PASSWORD=root -d mysql:8.0
Navicat 17（破解版）数据库 GUI 客户端❌⭐ 推荐 DBeaver（免费开源）或 VS Code MySQL 插件
演示数据.sql课程数据✅ 保留用于练习查询
练习数据.sql练习数据✅ 保留用于练习

为什么推荐 DBeaver？

  • 完全免费开源，无需破解
  • 支持 MySQL / PostgreSQL / SQLite 等几乎所有数据库
  • 自动补全、执行计划可视化、ER 图导出

Windows 安装 MySQL ​

bash
# 方式一：Docker（推荐）
docker run --name mysql \
  -e MYSQL_ROOT_PASSWORD=root \
  -e MYSQL_CHARACTER_SET_SERVER=utf8mb4 \
  -e MYSQL_COLLATION_SERVER=utf8mb4_unicode_ci \
  -p 3306:3306 \
  -d mysql:8.0

# 方式二：直接安装
# 双击 mysql-installer-community-8.0.26.0.msi
# 选择 "Developer Default"，一路 Next
# 设置 root 密码，记住它

导入课程数据 ​

bash
mysql -u root -p < 演示数据.sql
mysql -u root -p < 练习数据.sql

2. SQL 基础：DDL / DML ​

李永乐式比喻：数据库就像一个大型 Excel 文件柜——

  • 库 (Database) = 一个文件柜（如"学校管理系统"）
  • 表 (Table) = 文件柜里的一个文件夹（如"学生表"、"成绩表"）
  • 行 (Row) = 文件夹里的一张纸（如"张三的记录"）
  • 列 (Column) = 纸上填写的项目（如"姓名"、"年龄"）

SQL 分类 ​

分类全称作用命令
DDLData Definition Language定义库/表结构CREATE / ALTER / DROP
DMLData Manipulation Language操作数据INSERT / UPDATE / DELETE
DQLData Query Language查询数据SELECT
DCLData Control Language权限控制GRANT / REVOKE

DDL — 库操作 ​

sql
-- 创建库
CREATE DATABASE school DEFAULT CHARACTER SET utf8mb4;

-- 查看所有库
SHOW DATABASES;

-- 使用库
USE school;

-- 删除库（⚠️ 危险！数据全丢）
DROP DATABASE school;

DDL — 表操作 ​

sql
-- 创建表
CREATE TABLE student (
    id       INT PRIMARY KEY AUTO_INCREMENT,
    name     VARCHAR(50) NOT NULL,
    age      INT,
    gender   CHAR(1),
    score    DECIMAL(5,2),
    -- 常用数据类型：INT, VARCHAR(n), DECIMAL(m,n), DATE, DATETIME
    created  DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 查看表结构
DESC student;

-- 修改表（加列）
ALTER TABLE student ADD email VARCHAR(100);

-- 修改表（删列）
ALTER TABLE student DROP COLUMN email;

-- 删除表
DROP TABLE student;

DML — 增删改 ​

sql
-- 插入
INSERT INTO student (name, age, gender, score) VALUES ('张三', 20, '男', 85.5);
INSERT INTO student VALUES (NULL, '李四', 22, '女', 90.0, NOW());

-- 批量插入
INSERT INTO student (name, age) VALUES ('王五', 21), ('赵六', 19);

-- 更新
UPDATE student SET score = 95 WHERE name = '张三';

-- 删除（⚠️ 不带 WHERE 会删全表）
DELETE FROM student WHERE id = 4;

3. 基本查询与运算符 ​

李永乐式比喻：SELECT 查询就像从文件柜里按条件抽文件——

  • SELECT = 你看哪些列（只看姓名和分数）
  • FROM = 从哪个文件夹拿
  • WHERE = 用筛子过滤（只拿分数 > 80 的）
sql
-- 查询所有列
SELECT * FROM student;

-- 查询指定列
SELECT name, score FROM student;

-- 列别名
SELECT name AS 姓名, score AS 分数 FROM student;

-- 去重
SELECT DISTINCT gender FROM student;

WHERE 条件 ​

sql
-- 比较运算符
SELECT * FROM student WHERE score >= 60;
SELECT * FROM student WHERE age != 20;

-- 区间
SELECT * FROM student WHERE score BETWEEN 60 AND 90;

-- 集合
SELECT * FROM student WHERE age IN (18, 20, 22);

-- 模糊匹配
SELECT * FROM student WHERE name LIKE '张%';   -- 以"张"开头
SELECT * FROM student WHERE name LIKE '%三%';  -- 包含"三"

-- 逻辑运算
SELECT * FROM student WHERE score > 80 AND gender = '男';
SELECT * FROM student WHERE score > 90 OR age < 18;

-- 空值处理（NULL 不能用 = 判断）
SELECT * FROM student WHERE score IS NULL;
SELECT * FROM student WHERE score IS NOT NULL;

排序与分页 ​

sql
-- 排序
SELECT * FROM student ORDER BY score DESC;  -- 降序
SELECT * FROM student ORDER BY score ASC;   -- 升序（默认）
SELECT * FROM student ORDER BY score DESC, age ASC;  -- 多字段排序

-- 分页
SELECT * FROM student LIMIT 10;            -- 前 10 条
SELECT * FROM student LIMIT 10 OFFSET 20;  -- 第 21~30 条（第 3 页）
SELECT * FROM student LIMIT 20, 10;        -- 同上（简写）

4. 数据类型与函数 ​

常用数据类型 ​

类型说明占用示例
INT整数4 字节INT, INT UNSIGNED
BIGINT大整数8 字节自增 ID 用这个
DECIMAL(m,d)精确小数不定DECIMAL(10,2) = 99999999.99 最多
VARCHAR(n)变长字符串n+1~2 字节VARCHAR(255)
CHAR(n)定长字符串n 字节CHAR(1) 适合性别、状态
TEXT长文本最大 64KB文章内容
DATE日期3 字节'2026-06-26'
DATETIME日期时间8 字节'2026-06-26 19:00:00'
ENUM枚举1~2 字节ENUM('男', '女')
SET集合1~8 字节SET('A', 'B', 'C')

常用函数 ​

sql
-- 数学函数
SELECT ABS(-10);          -- 10
SELECT ROUND(3.14159, 2); -- 3.14
SELECT CEIL(3.1);         -- 4（向上取整）
SELECT FLOOR(3.9);        -- 3（向下取整）

-- 字符串函数
SELECT CONCAT('Hello', ' ', 'World');  -- Hello World
SELECT UPPER('hello');                 -- HELLO
SELECT LOWER('HELLO');                 -- hello
SELECT LENGTH('hello');                -- 5（字节数）
SELECT CHAR_LENGTH('你好');             -- 2（字符数）
SELECT SUBSTRING('hello', 2, 3);       -- ell
SELECT REPLACE('hello', 'l', 'x');     -- hexxo
SELECT TRIM('  hello  ');              -- hello

-- 日期函数
SELECT NOW();           -- 2026-06-26 19:00:00
SELECT CURDATE();       -- 2026-06-26
SELECT YEAR(NOW());     -- 2026
SELECT DATEDIFF('2026-07-01', '2026-06-26');  -- 5（天数差）
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日');     -- 2026年06月26日

-- 条件判断
SELECT IF(score >= 60, '及格', '不及格') FROM student;
SELECT CASE 
    WHEN score >= 90 THEN '优秀'
    WHEN score >= 60 THEN '及格'
    ELSE '不及格'
END AS 等级 FROM student;

-- 聚合函数（多行函数）
SELECT COUNT(*) FROM student;         -- 总行数
SELECT COUNT(score) FROM student;     -- score 非空的行数
SELECT AVG(score) FROM student;       -- 平均分
SELECT MAX(score) FROM student;       -- 最高分
SELECT MIN(score) FROM student;       -- 最低分
SELECT SUM(score) FROM student;       -- 总分

5. 窗口函数 ​

李永乐式比喻：窗口函数就像给每个组内排座位号—— 全班按班级分组，每个组内按成绩排名。每行数据都看到自己组内的排名，且不改变原始行数。

sql
-- 语法：函数() OVER (PARTITION BY 分组列 ORDER BY 排序列)

-- 按班级分组，每组按成绩排名
SELECT 
    name, 
    class,
    score,
    ROW_NUMBER() OVER (PARTITION BY class ORDER BY score DESC) AS 班级排名,
    RANK()       OVER (PARTITION BY class ORDER BY score DESC) AS 排名_可并列,
    DENSE_RANK() OVER (PARTITION BY class ORDER BY score DESC) AS 排名_不跳号
FROM student;

-- 常用窗口函数
-- ROW_NUMBER()  -- 唯一连续排名（1,2,3,4）
-- RANK()        -- 并列排名，跳跃（1,1,3,4）
-- DENSE_RANK()  -- 并列排名，不跳号（1,1,2,3）
-- SUM/AVG/MIN/MAX OVER()  -- 累计求和、移动平均

6. 连接查询 JOIN（⚠️ 最重要） ​

李永乐式比喻：JOIN 就像把两张 Excel 表通过某一列拼在一起——

  • 内连接 = 取交集，只有两张表都有的数据才出来
  • 左外连接 = 左表全保留，右表没有的填 NULL
  • 右外连接 = 右表全保留，左表没有的填 NULL
  • 自连接 = 一张表自己跟自己比（如员工和上级都在同一张表）
sql
-- 准备数据：学生表 + 成绩表
-- student(id, name)
-- score(id, student_id, subject, score)

-- 内连接：只有两个表都有的数据
SELECT s.name, sc.subject, sc.score
FROM student s
INNER JOIN score sc ON s.id = sc.student_id;

-- 左外连接：左表全部保留，右表没有的填空
SELECT s.name, sc.subject, sc.score
FROM student s
LEFT JOIN score sc ON s.id = sc.student_id;

-- 右外连接：右表全部保留
SELECT s.name, sc.subject, sc.score
FROM student s
RIGHT JOIN score sc ON s.id = sc.student_id;

-- 自连接：同一张表自己连自己
-- 员工表 employee(id, name, manager_id)
SELECT e.name AS 员工, m.name AS 上级
FROM employee e
LEFT JOIN employee m ON e.manager_id = m.id;

7. 分组 / 排序 / 分页 ​

李永乐式比喻：

  • GROUP BY = 按部门统计：销售部多少人、平均工资多少
  • HAVING = 部门统计完了，只留下平均工资 > 5000 的部门（WHERE 是分组前过滤，HAVING 是分组后过滤）
  • ORDER BY = 按工资从高到低排
  • LIMIT = 只看前 10 名
sql
-- 分组统计
SELECT 
    gender,
    COUNT(*) AS 人数,
    AVG(score) AS 平均分,
    MAX(score) AS 最高分,
    MIN(score) AS 最低分
FROM student
GROUP BY gender;

-- HAVING：分组后过滤
SELECT 
    class,
    AVG(score) AS 平均分
FROM student
GROUP BY class
HAVING 平均分 > 80;

-- WHERE vs HAVING 的区别
-- WHERE：分组之前过滤（不能使用聚合函数）
-- HAVING：分组之后过滤（可以使用聚合函数）

-- 完整查询顺序（重要！）
-- SELECT → FROM → JOIN → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT

8. 子查询与 CTE ​

李永乐式比喻：子查询就像套娃——先查出一个结果，拿这个结果再去查另一个。

sql
-- WHERE 中使用子查询
-- 查询分数高于平均分的学生
SELECT * FROM student 
WHERE score > (SELECT AVG(score) FROM student);

-- FROM 中使用子查询（把子查询结果当作临时表）
SELECT AVG(成绩) AS 班级平均分
FROM (
    SELECT class, AVG(score) AS 成绩
    FROM student
    GROUP BY class
) AS 班级统计;

-- EXISTS 子查询
-- 查询有成绩记录的学生
SELECT * FROM student s
WHERE EXISTS (
    SELECT 1 FROM score sc WHERE sc.student_id = s.id
);

-- CTE（Common Table Expression，通用表达式）
-- 让复杂的子查询更易读
WITH 班级统计 AS (
    SELECT class, AVG(score) AS 平均分
    FROM student
    GROUP BY class
)
SELECT * FROM 班级统计 WHERE 平均分 > 80;

-- 递归 CTE（适合树形结构：组织架构、分类）
WITH RECURSIVE 组织树 AS (
    -- 基础情况：顶层
    SELECT id, name, manager_id, 1 AS 层级
    FROM employee WHERE manager_id IS NULL
    UNION ALL
    -- 递归：下一层
    SELECT e.id, e.name, e.manager_id, t.层级 + 1
    FROM employee e
    JOIN 组织树 t ON e.manager_id = t.id
)
SELECT * FROM 组织树;

9. 约束与表设计 ​

李永乐式比喻：约束就是给表的列加规矩——

  • NOT NULL = 这一列不能为空（身份证号必填）
  • UNIQUE = 这一列不能重复（每人一个手机号）
  • PRIMARY KEY = 非空 + 唯一，每条数据的"身份证号"
  • FOREIGN KEY = 绑定另一张表的数据（订单里的用户必须是已存在的用户）
  • DEFAULT = 不给值就用默认的
  • CHECK = 值必须在某个范围内
sql
CREATE TABLE order (
    id        INT PRIMARY KEY AUTO_INCREMENT,    -- 主键，自增
    order_no  VARCHAR(20) UNIQUE NOT NULL,       -- 订单号：唯一+非空
    user_id   INT NOT NULL,                      -- 用户 ID
    amount    DECIMAL(10,2) CHECK (amount > 0),  -- 金额必须 > 0
    status    CHAR(1) DEFAULT '0',               -- 默认为 0
    created   DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    -- 外键约束：确保 user_id 在 user 表中存在
    FOREIGN KEY (user_id) REFERENCES user(id)
);

表关系设计 ​

关系说明设计方式
一对一一个人对应一个身份证号任意一张表加外键 + UNIQUE
一对多一个班级有多个学生"多"的表加外键指向"一"的表
多对多一个学生选多门课，一门课被多个学生选建第三张关联表

10. 事务与隔离级别（⚠️ 人工关注） ​

李永乐式比喻：事务就像银行转账—— 你给朋友转 100 元：你的账户减 100，朋友的账户加 100。 这两个操作必须要么全部成功，要么全部失败。不能出现你扣了钱但朋友没收到的情况。

ACID 四大特性 ​

特性含义比喻
Atomicity 原子性事务中的所有操作要么全做，要么全不做转账：扣钱和加钱是一体的
Consistency 一致性事务前后数据都符合约束规则转账后总金额不变
Isolation 隔离性并发事务互不干扰两个人同时转账不会乱套
Durability 持久性事务提交后数据不会丢失转完账关机重启，钱还在
sql
-- 事务的使用
START TRANSACTION;

UPDATE account SET balance = balance - 100 WHERE name = 'Alice';
UPDATE account SET balance = balance + 100 WHERE name = 'Bob';

-- 都成功了就提交
COMMIT;

-- 如果中间出错了就回滚
ROLLBACK;

隔离级别（⚠️ 面试高频） ​

隔离级别脏读不可重复读幻读性能
READ UNCOMMITTED可能可能可能最高
READ COMMITTED不会可能可能⬇️
REPEATABLE READ (MySQL 默认)不会不会可能⬇️
SERIALIZABLE不会不会不会最低
sql
-- 查看当前隔离级别
SELECT @@transaction_isolation;

-- 设置隔离级别（会话级别）
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

11. Python 连接 MySQL ​

python
# 安装 pymysql
# pip install pymysql

import pymysql

# 连接数据库
conn = pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='root',
    database='school',
    charset='utf8mb4'
)

# 创建游标
cursor = conn.cursor()

# 查询
cursor.execute('SELECT * FROM student WHERE score > %s', (80,))
results = cursor.fetchall()   # 所有结果
for row in results:
    print(row)

# 插入/更新/删除
cursor.execute(
    'INSERT INTO student (name, age, score) VALUES (%s, %s, %s)',
    ('新同学', 20, 88)
)
conn.commit()  # 提交事务

# 关闭
cursor.close()
conn.close()

使用 SQLAlchemy（推荐用于项目） ​

python
# pip install sqlalchemy pymysql

from sqlalchemy import create_engine, text

engine = create_engine('mysql+pymysql://root:root@localhost:3306/school')

with engine.connect() as conn:
    result = conn.execute(text('SELECT * FROM student WHERE score > :score'), 
                          {'score': 80})
    for row in result:
        print(row)

AI 协作指南 ​

AI 能做的：
  - 写任何复杂 SQL 查询（多表 JOIN、子查询、窗口函数）
  - 优化慢查询（分析 EXPLAIN 输出、推荐索引）
  - 设计数据库表结构根据需求描述
  - 生成 Python 连接 MySQL 的代码

人类需决策的：
  - 表关系设计（一对多还是多对多？）
  - 事务隔离级别选哪个？
  - 索引策略（哪些列需要索引？）
  - 数据量大时的分库分表方案

最高效的学习方式：
  - 遇到查数据的任务，直接描述给 AI，让 AI 写 SQL
  - 遇到慢查询，把 EXPLAIN 结果给 AI 分析
  - 把重点放在理解事务、索引、表设计上
  - SQL 语法本身不值得花时间记忆

附录：原始资料处理说明 ​

原始文件处理方式
尚硅谷大模型技术之MySQL1.0.docx内容已整合到本文档各章节
mysql-installer-community-8.0.26.0.msi建议用 Docker 替代，详见第 1 章
Navicatls_17.rar不推荐安装，建议用 DBeaver
演示数据.sql / 练习数据.sql保留，导入后配合练习
视频（67 个）跳过

OPC 超级个体实战指南

© 2026 OPC Guide