MySQL精讲 - 大二学生系统性学习指南
📚 本文档专为大二学生设计，帮助您系统性学习MySQL数据库 🎯 学习目标：掌握MySQL核心概念，具备数据库设计和优化能力 ⏱️ 建议学习时间：6-8周，每周10-15小时
🧑💻 版本说明： 本文档以最新版 MySQL（个人学习：Innovation 26.7 / LTS 9.7）为准编写。 文中出现 🧑💻 个人学习（最新版） 与 🏭 企业生产（8.0 / 8.4 LTS / 存量5.7） 标识处， 表示该功能在企业常见版本中的使用差异，请根据实际环境选择写法。
目录
第一部分：基础入门
第1章 MySQL简介与安装配置
1.1 什么是数据库？
数据库（Database） 是按照数据结构来组织、存储和管理数据的仓库。简单来说，就是一个存放数据的"仓库"。
为什么需要数据库？
- 数据持久化存储（程序关闭后数据不丢失）
- 高效的数据查询和管理
- 数据安全和完整性保障
- 支持多用户并发访问
常见数据库类型：
- 关系型数据库（RDBMS）：MySQL、PostgreSQL、Oracle、SQL Server
- 非关系型数据库（NoSQL）：MongoDB、Redis、Cassandra
1.2 MySQL简介
MySQL 是一个开源的关系型数据库管理系统（RDBMS），由瑞典MySQL AB公司开发，现属于Oracle公司。
MySQL的特点：
- ✅ 开源免费（社区版）
- ✅ 性能卓越，速度快
- ✅ 易于使用和管理
- ✅ 支持多种编程语言
- ✅ 跨平台支持
- ✅ 社区活跃，文档丰富
MySQL 版本与双轨制（重点，面试爱问）：
自 8.1 起 MySQL 采用双轨发布模式：
| 轨道 | 更新频率 | 支持周期 | 适用场景 |
|---|---|---|---|
| Innovation（创新版） | 约每季度一个 | 支持到下一版发布为止 | 开发、学习、追求最新特性 |
| LTS（长期支持版） | 约每两年一个 | 5年Premier + 3年Extended，共8年 | 生产环境 |
- 版本号规律：9.x（如 9.0~9.6）为上一代 Innovation；9.7 是最后一个采用"9.x"顺序命名的 LTS；从 2026-07 起改用日历版本号 YY.M（如 26.7 = 2026年7月发布）。
- 当前最新版（2026-09 时点）：
- 🧑💻 个人学习（绝对最新）：MySQL 26.7（最新 Innovation，2026-07-28 GA；学习语法与 9.7 完全一致）
- 🏭 企业生产（最新 LTS）：MySQL 9.7.x（当前 9.7.2，支持到 2034）
- 企业现实： 多数公司仍在跑 8.0.x（2026-04 起进入 Extended Support，建议向 8.4/9.7 LTS 迁移）；存量系统还有 5.7（已 EOL，强烈不建议新项目使用）。
🧑💻 个人学习（最新版）：直接安装 26.7 或 9.7 均可，本文所有语法两者一致。 🏭 企业生产差异：见各章 🏭 提示块与文末《附录 E 版本兼容速查表》；遇到报错先
SELECT VERSION();确认版本。
1.3 MySQL安装配置
🧑💻 个人学习（最新版）：安装最新 26.7 / 9.7，从官方下载页或
mysql-*-innovation-community仓库源获取。 🏭 企业生产：通过 LTS 仓库源（如mysql-9.7-lts-community，RHEL 系为 Yum/DNF）安装；Innovation 版不用于生产。 ⚠️ 认证兼容提醒：8.4 / 9.x 起默认禁用甚至移除了旧的mysql_native_password认证插件，版本过老的 MySQL Workbench / 驱动可能连不上最新版，务必使用最新客户端工具（详见 12.1 节）。
Windows安装
方法一：使用安装包（推荐初学者）
下载MySQL Installer
- 访问：https://dev.mysql.com/downloads/installer/
- 选择 "MySQL Installer" 下载
安装步骤
1. 运行安装程序 2. 选择 "Developer Default" 或 "Custom" 3. 选择组件：MySQL Server、MySQL Workbench 4. 配置root密码（务必记住！） 5. 设置Windows服务（开机自启） 6. 完成安装验证安装
cmd# 打开命令提示符 mysql -u root -p # 输入密码后，显示 MySQL> 提示符即安装成功
方法二：使用压缩包（推荐有基础的学生）
- 下载ZIP包
- 解压到指定目录（如
C:\mysql） - 配置环境变量
- 创建配置文件
my.ini - 初始化数据目录
- 启动服务
Linux安装（以Ubuntu/Debian为例）
bash
# 更新软件包列表
sudo apt update
# 安装MySQL服务器
sudo apt install mysql-server
# 安装过程中会提示设置root密码
# 启动MySQL服务
sudo systemctl start mysql
# 设置开机自启
sudo systemctl enable mysql
# 安全配置（重要！）
sudo mysql_secure_installation
# 登录MySQL
sudo mysql -u root -pmacOS安装
bash
# 使用Homebrew安装（推荐）
brew install mysql
# 启动服务
brew services start mysql
# 安全配置
mysql_secure_installation
# 登录
mysql -u root -p1.4 MySQL客户端工具
命令行客户端：
- Windows：CMD或PowerShell
- Linux/macOS：Terminal
图形化工具：
- MySQL Workbench（官方工具，推荐）
- Navicat（功能强大，收费）
- phpMyAdmin（Web界面，轻量）
- DBeaver（免费开源，支持多数据库）
1.5 配置文件详解
my.ini / my.cnf 主要配置：
ini
[mysqld]
# 基本设置
port=3306
basedir="C:/mysql"
datadir="C:/mysql/data"
# 字符集设置（重要！）
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
# InnoDB设置
innodb_buffer_pool_size=1G
innodb_log_file_size=256M
# 连接设置
max_connections=100
max_connect_errors=10
# 日志设置
log-error="mysql_error.log"
slow_query_log=1
slow_query_log_file="slow_query.log"
long_query_time=2
[mysql]
default-character-set=utf8mb4
[client]
default-character-set=utf8mb4🏭 版本差异（字符集与排序规则 collation）：
- 🧑💻 个人最新版（8.0+ / 9.x / 26.7）：8.0 起默认排序规则为
utf8mb4_0900_ai_ci（8.0 引入，排序更准、更快），且默认字符集就是 utf8mb4。- 🏭 5.7（企业存量）：没有
0900系列排序规则，只能用utf8mb4_general_ci/utf8mb4_unicode_ci；且默认字符集是 latin1，建库建表务必显式指定 utf8mb4，否则中文乱码。- 数据迁移/备份时若排序规则不一致，排序结果可能不同，详见《附录 E 版本兼容速查表》。
1.6 本章练习
- 练习1：在你的电脑上安装MySQL，记录安装过程中遇到的问题
- 练习2：使用命令行登录MySQL，执行
SELECT VERSION();查看版本 - 练习3：使用MySQL Workbench创建一个连接，测试连接是否成功
- 练习4：修改MySQL配置文件，将字符集设置为utf8mb4
第2章 SQL语言基础
2.1 什么是SQL？
SQL（Structured Query Language） 是用于访问和操作数据库的标准语言。
SQL的特点：
- 不区分大小写（但建议关键字大写）
- 每条语句以分号结尾
- 可以单行书写，也可以多行书写
- 注释方式：sql
-- 单行注释 # 单行注释（MySQL特有） /* 多行注释 */
2.2 SQL分类
| 分类 | 英文全称 | 功能 | 常用命令 |
|---|---|---|---|
| DDL | Data Definition Language | 数据定义 | CREATE, ALTER, DROP |
| DML | Data Manipulation Language | 数据操作 | INSERT, UPDATE, DELETE |
| DQL | Data Query Language | 数据查询 | SELECT |
| DCL | Data Control Language | 数据控制 | GRANT, REVOKE |
2.3 DDL - 数据定义语言
用于定义数据库对象（数据库、表、索引等）
sql
-- 创建数据库
CREATE DATABASE school;
-- 创建数据库（指定字符集）
CREATE DATABASE school CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 查看所有数据库
SHOW DATABASES;
-- 使用数据库
USE school;
-- 查看当前数据库
SELECT DATABASE();
-- 删除数据库
DROP DATABASE IF EXISTS school;
-- 创建表
CREATE TABLE students (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
age INT,
gender VARCHAR(10),
create_time DATETIME DEFAULT CURRENT_TIMESTAMP
);
-- 查看表结构
DESCRIBE students;
-- 或简写
DESC students;
-- 查看建表语句
SHOW CREATE TABLE students;
-- 修改表名
ALTER TABLE students RENAME TO student;
-- 添加字段
ALTER TABLE student ADD email VARCHAR(100);
-- 修改字段类型
ALTER TABLE student MODIFY COLUMN email VARCHAR(150);
-- 修改字段名
ALTER TABLE student CHANGE COLUMN email user_email VARCHAR(150);
-- 删除字段
ALTER TABLE student DROP COLUMN user_email;
-- 删除表
DROP TABLE IF EXISTS student;2.4 DML - 数据操作语言
用于对表中的数据进行增删改操作
sql
-- 创建示例表
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
password VARCHAR(100) NOT NULL,
email VARCHAR(100),
age INT,
create_time DATETIME DEFAULT CURRENT_TIMESTAMP
);
-- 插入数据（完整插入）
INSERT INTO users (username, password, email, age)
VALUES ('zhangsan', '123456', 'zhangsan@example.com', 20);
-- 插入数据（部分插入，需指定字段）
INSERT INTO users (username, password)
VALUES ('lisi', '654321');
-- 批量插入
INSERT INTO users (username, password, email, age)
VALUES
('wangwu', '111111', 'wangwu@example.com', 22),
('zhaoliu', '222222', 'zhaoliu@example.com', 21),
('sunqi', '333333', 'sunqi@example.com', 23);
-- 更新数据
UPDATE users SET age = 21 WHERE username = 'lisi';
-- 更新多个字段
UPDATE users SET
email = 'new_email@example.com',
age = 25
WHERE id = 1;
-- 删除数据
DELETE FROM users WHERE username = 'sunqi';
-- 清空表数据（保留表结构）
TRUNCATE TABLE users;2.5 DQL - 数据查询语言
用于从数据库中查询数据（最常用！）
sql
-- 基础查询
SELECT * FROM users;
-- 查询指定字段
SELECT username, email FROM users;
-- 条件查询
SELECT * FROM users WHERE age > 20;
-- 多条件查询
SELECT * FROM users WHERE age >= 20 AND age <= 25;
-- 模糊查询
SELECT * FROM users WHERE username LIKE 'z%';
-- 排序查询
SELECT * FROM users ORDER BY age DESC;
-- 限制查询数量
SELECT * FROM users LIMIT 5;
-- 分页查询
SELECT * FROM users LIMIT 10 OFFSET 0; -- 第1页
SELECT * FROM users LIMIT 10 OFFSET 10; -- 第2页2.6 DCL - 数据控制语言
用于控制数据库访问权限
sql
-- 创建用户
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password123';
-- 授权
GRANT ALL PRIVILEGES ON school.* TO 'newuser'@'localhost';
-- 刷新权限
FLUSH PRIVILEGES;
-- 查看权限
SHOW GRANTS FOR 'newuser'@'localhost';
-- 撤销权限
REVOKE ALL PRIVILEGES ON school.* FROM 'newuser'@'localhost';
-- 删除用户
DROP USER IF EXISTS 'newuser'@'localhost';2.7 本章练习
- 练习1：创建一个名为
test_db的数据库 - 练习2：在
test_db中创建一个products表，包含：id, name, price, stock - 练习3：向
products表插入5条测试数据 - 练习4：查询价格大于100的产品
- 练习5：更新id=1的产品价格
- 练习6：删除库存为0的产品
第3章 数据类型详解
3.1 数值类型
| 类型 | 字节 | 范围（有符号） | 范围（无符号） | 用途 |
|---|---|---|---|---|
| TINYINT | 1 | -128 ~ 127 | 0 ~ 255 | 小整数值 |
| SMALLINT | 2 | -32768 ~ 32767 | 0 ~ 65535 | 大整数值 |
| MEDIUMINT | 3 | -8388608 ~ 8388607 | 0 ~ 16777215 | 大整数值 |
| INT/INTEGER | 4 | -2147483648 ~ 2147483647 | 0 ~ 4294967295 | 整数值 |
| BIGINT | 8 | -9223372036854775808 ~ 9223372036854775807 | 0 ~ 18446744073709551615 | 超大整数值 |
| FLOAT | 4 | -3.402823466E+38 ~ 3.402823466E+38 | 单精度浮点数 | |
| DOUBLE | 8 | -1.7976931348623157E+308 ~ 1.7976931348623157E+308 | 双精度浮点数 | |
| DECIMAL | M+1 | 依赖于M和D | 定点数（精确） |
使用建议：
- 整数类型：根据数据范围选择，能用TINYINT就不用INT
- 浮点数：金额等精确计算使用DECIMAL
- DECIMAL(10,2)：表示最多10位数字，其中2位小数
sql
-- 示例
CREATE TABLE numeric_demo (
id INT AUTO_INCREMENT PRIMARY KEY,
tiny_val TINYINT,
int_val INT,
bigint_val BIGINT,
float_val FLOAT,
double_val DOUBLE,
decimal_val DECIMAL(10, 2)
);
INSERT INTO numeric_demo VALUES
(NULL, 127, 2147483647, 9223372036854775807, 3.14, 3.141592653589793, 99999999.99);3.2 字符串类型
| 类型 | 最大长度 | 用途 | 特点 |
|---|---|---|---|
| CHAR | 255字符 | 定长字符串 | 速度快，浪费空间 |
| VARCHAR | 65535字节 | 变长字符串 | 节省空间，速度稍慢 |
| TEXT | 65535字节 | 长文本 | 不能有默认值 |
| MEDIUMTEXT | 16777215字节 | 中等长度文本 | |
| LONGTEXT | 4294967295字节 | 长文本 | |
| TINYTEXT | 255字节 | 短文本 | |
| BINARY | 255字节 | 定长二进制 | |
| VARBINARY | 65535字节 | 变长二进制 | |
| BLOB | 65535字节 | 二进制大对象 | 存储图片、文件等 |
注意单位差异： CHAR 的 255 是字符数；VARCHAR 的 65535 是字节数（总长度受行大小 64KB 限制，utf8mb4 下一个汉字占 4 字节，故 VARCHAR 实际最多约 16383 个中文字符）。
CHAR vs VARCHAR 选择：
- 固定长度数据（如身份证号、手机号）：使用CHAR
- 变长数据（如姓名、地址）：使用VARCHAR
- 大文本数据（如文章内容）：使用TEXT
sql
-- 示例
CREATE TABLE string_demo (
id INT AUTO_INCREMENT PRIMARY KEY,
fixed_char CHAR(10), -- 固定10字符
variable_char VARCHAR(50), -- 最大50字符
content TEXT, -- 长文本
avatar BLOB -- 二进制数据（图片等）
);
INSERT INTO string_demo VALUES
(NULL, 'Hello', 'Hello World', '这是一段很长的文本...', NULL);3.3 日期和时间类型
| 类型 | 格式 | 范围 | 用途 |
|---|---|---|---|
| DATE | YYYY-MM-DD | '1000-01-01' to '9999-12-31' | 日期值 |
| TIME | HH:MM:SS | '-838:59:59' to '838:59:59' | 时间值 |
| YEAR | YYYY | 1901 to 2155 | 年份值 |
| DATETIME | YYYY-MM-DD HH:MM:SS | '1000-01-01 00:00:00' to '9999-12-31 23:59:59' | 日期时间值 |
| TIMESTAMP | YYYY-MM-DD HH:MM:SS | '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC | 时间戳 |
DATETIME vs TIMESTAMP：
- DATETIME：范围大，不受时区影响
- TIMESTAMP：范围小，自动转换时区，推荐使用
sql
-- 示例
CREATE TABLE time_demo (
id INT AUTO_INCREMENT PRIMARY KEY,
birth_date DATE,
alarm_time TIME,
birth_year YEAR,
create_datetime DATETIME DEFAULT CURRENT_TIMESTAMP,
update_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
INSERT INTO time_demo (birth_date, alarm_time, birth_year)
VALUES ('2000-01-15', '08:30:00', 2000);3.4 数据类型选择最佳实践
ID字段设计：
sql
-- 自增主键（推荐）
id INT AUTO_INCREMENT PRIMARY KEY
-- UUID（分布式系统推荐）
id CHAR(36) PRIMARY KEY -- 或 BINARY(16)
-- 雪花算法ID（推荐）
id BIGINT PRIMARY KEY金额字段：
sql
-- 使用DECIMAL，避免浮点数精度问题
price DECIMAL(10, 2) -- 最大99999999.99
-- 或使用整数存储分
price INT -- 单位：分布尔值：
sql
-- 使用TINYINT(1)
is_active TINYINT(1) DEFAULT 1 -- 1=真，0=假
-- 或使用ENUM
status ENUM('active', 'inactive') DEFAULT 'active'3.5 本章练习
- 练习1：设计一个学生表，选择合适的数据类型
- 练习2：设计一个订单表，包含金额字段，验证DECIMAL的精度
- 练习3：设计一个用户表，包含创建时间和更新时间
- 练习4：测试CHAR和VARCHAR在存储不同长度字符串时的空间差异
第4章 数据库和表的基本操作
4.1 数据库操作
sql
-- 查看所有数据库
SHOW DATABASES;
-- 创建数据库
CREATE DATABASE IF NOT EXISTS school
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_unicode_ci;
-- 查看数据库创建语句
SHOW CREATE DATABASE school;
-- 修改数据库字符集
ALTER DATABASE school CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 删除数据库
DROP DATABASE IF EXISTS school;
-- 查看当前使用的数据库
SELECT DATABASE();
-- 切换数据库
USE school;4.2 表操作
创建表
sql
USE school;
-- 创建学生表
CREATE TABLE IF NOT EXISTS students (
id INT AUTO_INCREMENT PRIMARY KEY COMMENT '学生ID',
student_no VARCHAR(20) NOT NULL UNIQUE COMMENT '学号',
name VARCHAR(50) NOT NULL COMMENT '姓名',
gender ENUM('男', '女', '其他') DEFAULT '男' COMMENT '性别',
birth_date DATE COMMENT '出生日期',
class_id INT COMMENT '班级ID',
enrollment_date DATE DEFAULT (CURRENT_DATE) COMMENT '入学日期',
status ENUM('在读', '毕业', '休学', '退学') DEFAULT '在读' COMMENT '状态',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
INDEX idx_student_no (student_no),
INDEX idx_class_id (class_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生表';
-- 创建班级表
CREATE TABLE IF NOT EXISTS classes (
id INT AUTO_INCREMENT PRIMARY KEY COMMENT '班级ID',
class_name VARCHAR(50) NOT NULL COMMENT '班级名称',
grade VARCHAR(20) COMMENT '年级',
teacher_name VARCHAR(50) COMMENT '班主任',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
INDEX idx_grade (grade)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='班级表';
-- 创建课程表
CREATE TABLE IF NOT EXISTS courses (
id INT AUTO_INCREMENT PRIMARY KEY COMMENT '课程ID',
course_name VARCHAR(100) NOT NULL COMMENT '课程名称',
credit DECIMAL(3,1) COMMENT '学分',
hours INT COMMENT '课时',
description TEXT COMMENT '课程描述',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程表';
-- 创建选课表（多对多关系）
CREATE TABLE IF NOT EXISTS student_courses (
id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT NOT NULL COMMENT '学生ID',
course_id INT NOT NULL COMMENT '课程ID',
score DECIMAL(5,2) COMMENT '成绩',
choose_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '选课时间',
UNIQUE KEY uk_student_course (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='选课表';查看表信息
sql
-- 查看当前数据库所有表
SHOW TABLES;
-- 查看表结构
DESCRIBE students;
-- 或
DESC students;
-- 查看建表语句
SHOW CREATE TABLE students\G -- \G可以垂直格式显示
-- 查看表状态
SHOW TABLE STATUS LIKE 'students'\G修改表结构
sql
-- 添加字段
ALTER TABLE students ADD COLUMN phone VARCHAR(20) COMMENT '联系电话';
ALTER TABLE students ADD COLUMN address TEXT COMMENT '家庭住址' AFTER email;
-- 修改字段类型
ALTER TABLE students MODIFY COLUMN phone VARCHAR(30);
-- 修改字段名和类型
ALTER TABLE students CHANGE COLUMN phone mobile VARCHAR(30) COMMENT '手机号';
-- 删除字段
ALTER TABLE students DROP COLUMN address;
-- 添加约束
ALTER TABLE students ADD CONSTRAINT uk_mobile UNIQUE (mobile);
ALTER TABLE students ADD INDEX idx_name (name);
-- 删除约束
ALTER TABLE students DROP INDEX idx_name;
-- 修改表名
ALTER TABLE students RENAME TO student;
-- 或
RENAME TABLE student TO students;
-- 修改引擎
ALTER TABLE students ENGINE = InnoDB;
-- 修改字符集
ALTER TABLE students CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;删除表
sql
-- 删除表（如果有）
DROP TABLE IF EXISTS students;
-- 清空表数据（保留结构）
TRUNCATE TABLE students;4.3 约束详解
🏭 版本差异（约束与建表特性）：
- CHECK 约束：8.0.16 起真正生效；5.7 及更早只是"解析但忽略"，并不会校验数据。
- 表达式默认值
DEFAULT (表达式)（如DEFAULT (CURRENT_DATE)）：8.0.13+ 才支持；5.7 会报语法错误，只能写常量默认值。- AUTO_INCREMENT 持久化：8.0+ 通过重做日志保证自增值重启后不回退（5.7 重启后可能重用已删除的最大 id）。
- 其它细节见《附录 E 版本兼容速查表》。
约束类型：
- PRIMARY KEY：主键约束，唯一标识一条记录
- FOREIGN KEY：外键约束，建立表间关系
- UNIQUE：唯一约束，保证数据唯一性
- NOT NULL：非空约束，字段不能为空
- CHECK：检查约束，验证数据是否符合条件
- DEFAULT：默认值约束
sql
-- 约束示例
CREATE TABLE constraint_demo (
id INT AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
age INT CHECK (age >= 0 AND age <= 150),
status ENUM('active', 'inactive') DEFAULT 'active',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uk_username (username),
UNIQUE KEY uk_email (email)
);4.4 外键关系
sql
-- 创建外键
ALTER TABLE students
ADD CONSTRAINT fk_class
FOREIGN KEY (class_id) REFERENCES classes(id)
ON DELETE SET NULL
ON UPDATE CASCADE;
-- 外键选项
-- ON DELETE CASCADE：删除父表记录时，子表对应记录也删除
-- ON DELETE SET NULL：删除父表记录时，子表对应字段设为NULL
-- ON DELETE RESTRICT：删除父表记录时，如果有子表记录则报错
-- ON UPDATE CASCADE：更新父表主键时，子表对应字段也更新4.5 本章练习
- 练习1：创建一个图书馆数据库，包含：图书表、读者表、借阅表
- 练习2：为表添加适当的约束和索引
- 练习3：测试外键约束的效果
- 练习4：修改表结构，添加新字段并测试
第二部分：核心技能
第5章 数据查询（SELECT详解）
5.1 SELECT基础语法
sql
-- 基本查询
SELECT * FROM table_name;
-- 查询指定字段
SELECT column1, column2 FROM table_name;
-- 使用别名
SELECT
name AS '姓名',
age AS '年龄'
FROM students;
-- 去重查询
SELECT DISTINCT class_id FROM students;
-- 运算查询
SELECT
name,
age,
age + 1 AS '明年年龄'
FROM students;5.2 条件查询（WHERE）
sql
-- 比较运算符
SELECT * FROM students WHERE age > 20;
SELECT * FROM students WHERE age >= 20;
SELECT * FROM students WHERE age = 20;
SELECT * FROM students WHERE age != 20;
SELECT * FROM students WHERE age <> 20;
-- 逻辑运算符
SELECT * FROM students WHERE age > 20 AND gender = '男';
SELECT * FROM students WHERE age > 20 OR gender = '女';
SELECT * FROM students WHERE NOT (age > 20);
-- BETWEEN（范围查询）
SELECT * FROM students WHERE age BETWEEN 18 AND 22;
-- IN（集合查询）
SELECT * FROM students WHERE class_id IN (1, 2, 3);
-- LIKE（模糊查询）
SELECT * FROM students WHERE name LIKE '张%'; -- 以"张"开头
SELECT * FROM students WHERE name LIKE '%明'; -- 以"明"结尾
SELECT * FROM students WHERE name LIKE '%三%'; -- 包含"三"
SELECT * FROM students WHERE name LIKE '张_'; -- "张"后面只有一个字符
-- IS NULL（空值查询）
SELECT * FROM students WHERE email IS NULL;
SELECT * FROM students WHERE email IS NOT NULL;5.3 排序查询（ORDER BY）
sql
-- 升序排列（ASC，默认）
SELECT * FROM students ORDER BY age ASC;
-- 降序排列（DESC）
SELECT * FROM students ORDER BY age DESC;
-- 多字段排序
SELECT * FROM students ORDER BY class_id ASC, age DESC;
-- 按字段值排序
SELECT * FROM students
ORDER BY FIELD(status, '在读', '休学', '毕业', '退学');5.4 分页查询（LIMIT）
sql
-- 语法：LIMIT [offset,] row_count
-- offset：起始位置（从0开始）
-- row_count：返回的行数
-- 查询前10条
SELECT * FROM students LIMIT 10;
-- 查询第11-20条（第2页）
SELECT * FROM students LIMIT 10, 10;
-- 查询第21-30条（第3页）
SELECT * FROM students LIMIT 20, 10;
-- 通用分页公式
-- 第N页：LIMIT (N-1)*pageSize, pageSize5.5 聚合函数
sql
-- COUNT：统计行数
SELECT COUNT(*) AS '总人数' FROM students;
SELECT COUNT(DISTINCT class_id) AS '班级数' FROM students;
-- SUM：求和
SELECT SUM(age) AS '年龄总和' FROM students;
-- AVG：平均值
SELECT AVG(age) AS '平均年龄' FROM students;
SELECT ROUND(AVG(age), 2) AS '平均年龄' FROM students; -- 保留2位小数
-- MAX/MIN：最大值/最小值
SELECT MAX(age) AS '最大年龄', MIN(age) AS '最小年龄' FROM students;
-- 综合使用
SELECT
COUNT(*) AS '学生总数',
AVG(age) AS '平均年龄',
MAX(age) AS '最大年龄',
MIN(age) AS '最小年龄'
FROM students;5.6 窗口函数与CTE（MySQL 8.0+ 新特性）
窗口函数与 CTE 是 8.0 才引入的 SQL 语法，也是日常报表与面试里的高频工具，个人最新版（9.x / 26.7）直接可用。
窗口函数（Window Function）： 在不合并行的前提下，为每一行计算一个"窗口"内的聚合或排名值。
sql
-- 行号排名（每个班级内按成绩从高到低编号）
SELECT
name,
class_id,
score,
ROW_NUMBER() OVER (PARTITION BY class_id ORDER BY score DESC) AS rn
FROM student_courses;
-- 排名（允许并列，名次可跳跃）
SELECT
name, score,
RANK() OVER (ORDER BY score DESC) AS rk
FROM student_courses;
-- 密集排名（允许并列，名次连续）
SELECT
name, score,
DENSE_RANK() OVER (ORDER BY score DESC) AS drk
FROM student_courses;
-- 求滑动累计（按课程ID顺序，累加当前行及前2行共3行的学时）
SELECT
course_name,
hours,
SUM(hours) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_sum
FROM courses;
-- 与子查询结合：取出每班成绩第一名
SELECT t.name, t.class_id, t.score
FROM (
SELECT name, class_id, score,
ROW_NUMBER() OVER (PARTITION BY class_id ORDER BY score DESC) AS rn
FROM student_courses
) t
WHERE t.rn = 1;
-- LAG / LEAD：取相邻行的值（按入学时间查看相邻学生的入学间隔）
SELECT
name,
enrollment_date,
LAG(enrollment_date) OVER (ORDER BY enrollment_date) AS prev_date,
DATEDIFF(enrollment_date, LAG(enrollment_date) OVER (ORDER BY enrollment_date)) AS day_diff
FROM students;CTE（公用表表达式，WITH … AS）： 让复杂查询更易读，中间结果可被多次引用，并支持递归。
sql
-- 简单CTE
WITH class_stats AS (
SELECT class_id, COUNT(*) AS cnt
FROM students
GROUP BY class_id
)
SELECT c.class_name, cs.cnt
FROM class_stats cs
JOIN classes c ON c.id = cs.class_id;
-- 多个CTE
WITH
top_class AS (SELECT class_id FROM students GROUP BY class_id HAVING COUNT(*) > 5),
avg_age AS (SELECT AVG(age) AS a FROM students)
SELECT 'top_class_cnt' AS item, (SELECT COUNT(*) FROM top_class) AS val
UNION ALL
SELECT 'avg_age', (SELECT a FROM avg_age);
-- 递归CTE：生成序列
WITH RECURSIVE seq AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM seq WHERE n < 10
)
SELECT * FROM seq;🧑💻 个人最新版（8.0+ / 9.x / 26.7）：以上语法均可直接运行。 🏭 企业版本差异（重要）：
- 5.7 及更早不支持窗口函数与 CTE，执行即报语法错误；若公司仍是 5.7，只能退而求其次——用子查询 + 用户变量模拟排名，或在应用层处理。
- 8.0 / 8.4 / 9.7 / 26.7 均完整支持，用法与本文一致。
5.7 分组查询（GROUP BY）
sql
-- 按班级分组，统计每个班的学生数
SELECT
class_id,
COUNT(*) AS '学生数'
FROM students
GROUP BY class_id;
-- 按性别分组
SELECT
gender,
COUNT(*) AS '人数'
FROM students
GROUP BY gender;
-- 多字段分组
SELECT
class_id,
gender,
COUNT(*) AS '人数'
FROM students
GROUP BY class_id, gender;
-- HAVING：分组后过滤（WHERE在分组前过滤）
SELECT
class_id,
COUNT(*) AS '学生数'
FROM students
GROUP BY class_id
HAVING COUNT(*) > 10;
-- 综合示例：查询学生数大于5的班级，按学生数降序
SELECT
class_id,
COUNT(*) AS '学生数'
FROM students
GROUP BY class_id
HAVING COUNT(*) > 5
ORDER BY COUNT(*) DESC;5.8 连接查询（JOIN）
sql
-- 创建示例数据
INSERT INTO classes (class_name, grade, teacher_name) VALUES
('计算机1班', '大二', '张老师'),
('计算机2班', '大二', '李老师'),
('软件1班', '大二', '王老师');
UPDATE students SET class_id = 1 WHERE id <= 10;
UPDATE students SET class_id = 2 WHERE id > 10 AND id <= 20;
UPDATE students SET class_id = 3 WHERE id > 20;
-- 内连接（INNER JOIN）：只返回两个表中匹配的行
SELECT
s.name AS '学生姓名',
c.class_name AS '班级名称',
c.teacher_name AS '班主任'
FROM students s
INNER JOIN classes c ON s.class_id = c.id;
-- 左连接（LEFT JOIN）：返回左表所有行，右表无匹配则为NULL
SELECT
s.name AS '学生姓名',
c.class_name AS '班级名称'
FROM students s
LEFT JOIN classes c ON s.class_id = c.id;
-- 右连接（RIGHT JOIN）：返回右表所有行，左表无匹配则为NULL
SELECT
s.name AS '学生姓名',
c.class_name AS '班级名称'
FROM students s
RIGHT JOIN classes c ON s.class_id = c.id;
-- 全连接（FULL JOIN）：MySQL不支持，可用UNION模拟
SELECT
s.name AS '学生姓名',
c.class_name AS '班级名称'
FROM students s
LEFT JOIN classes c ON s.class_id = c.id
UNION
SELECT
s.name AS '学生姓名',
c.class_name AS '班级名称'
FROM students s
RIGHT JOIN classes c ON s.class_id = c.id;
-- 自连接：表与自身连接
SELECT
e.name AS '员工',
m.name AS '经理'
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;5.9 子查询
sql
-- WHERE中的子查询
SELECT * FROM students
WHERE class_id IN (
SELECT id FROM classes WHERE grade = '大二'
);
-- FROM中的子查询（派生表）
SELECT
t.class_id,
t.student_count
FROM (
SELECT class_id, COUNT(*) AS student_count
FROM students
GROUP BY class_id
) t
WHERE t.student_count > 5;
-- EXISTS子查询
SELECT * FROM students s
WHERE EXISTS (
SELECT 1 FROM classes c WHERE c.id = s.class_id
);
-- 比较子查询
SELECT * FROM students
WHERE age > (SELECT AVG(age) FROM students);5.10 UNION合并查询
sql
-- UNION：合并结果集，自动去重
SELECT name, age FROM students WHERE gender = '男'
UNION
SELECT name, age FROM students WHERE age > 22;
-- UNION ALL：合并结果集，保留重复
SELECT name, age FROM students WHERE gender = '男'
UNION ALL
SELECT name, age FROM students WHERE age > 22;5.11 本章练习
- 练习1：查询年龄在18-22岁之间的学生，按年龄升序
- 练习2：统计每个班级的平均年龄，只显示平均年龄大于20的班级
- 练习3：查询每个班级的学生人数，显示班级名称和学生数
- 练习4：查询选修了"数据库"课程的学生姓名和成绩
- 练习5：查询年龄最大的前5名学生
第6章 数据操作（INSERT/UPDATE/DELETE）
6.1 INSERT插入数据
sql
-- 基础插入
INSERT INTO students (student_no, name, gender, birth_date, class_id)
VALUES ('2023001', '张三', '男', '2002-01-15', 1);
-- 批量插入
INSERT INTO students (student_no, name, gender, birth_date, class_id)
VALUES
('2023002', '李四', '男', '2002-03-20', 1),
('2023003', '王五', '女', '2001-11-05', 2),
('2023004', '赵六', '男', '2002-07-12', 2);
-- 插入查询结果
INSERT INTO backup_students (student_no, name, class_id)
SELECT student_no, name, class_id
FROM students
WHERE status = '毕业';
-- INSERT IGNORE：忽略重复错误
INSERT IGNORE INTO students (student_no, name, gender, class_id)
VALUES ('2023001', '重复数据', '男', 1);
-- REPLACE：替换已存在的数据
REPLACE INTO students (student_no, name, gender, class_id)
VALUES ('2023001', '张三（更新）', '男', 1);
-- INSERT ... ON DUPLICATE KEY UPDATE：存在则更新
INSERT INTO student_scores (student_id, course_id, score)
VALUES (1, 1, 85)
ON DUPLICATE KEY UPDATE score = 85;6.2 UPDATE更新数据
sql
-- 更新单个字段
UPDATE students SET name = '张三丰' WHERE id = 1;
-- 更新多个字段
UPDATE students
SET
name = '张三丰',
gender = '男',
email = 'zhangsan@example.com'
WHERE id = 1;
-- 使用表达式更新
UPDATE students SET age = age + 1 WHERE class_id = 1;
-- 使用CASE WHEN条件更新
UPDATE students
SET status = CASE
WHEN age > 23 THEN '毕业'
ELSE '在读'
END;
-- 关联更新（UPDATE JOIN）
UPDATE students s
INNER JOIN classes c ON s.class_id = c.id
SET s.grade = c.grade
WHERE c.grade = '大二';
-- 限制更新行数
UPDATE students SET status = '在读' LIMIT 10;
-- 启用 mysql 客户端的"安全更新"模式
-- 注意：SQL_SAFE_UPDATES 是 mysql 命令行客户端的特性（等价于启动参数 --safe-updates），并非服务器变量
-- 开启后，不带 WHERE 或 LIMIT 的 UPDATE/DELETE 会被客户端拒绝执行，防止误操作
-- 注意：它只对使用 mysql 客户端的会话生效，Workbench 或应用程序连接不受此约束；
-- 服务器本身没有该变量，向服务器"开启生产安全模式"属于常见误解
SET SQL_SAFE_UPDATES = 1;
-- 开启后，UPDATE/DELETE必须带WHERE条件或LIMIT6.3 DELETE删除数据
sql
-- 删除单条记录
DELETE FROM students WHERE id = 1;
-- 删除多条记录
DELETE FROM students WHERE class_id = 3;
-- 删除所有记录
DELETE FROM students;
-- 关联删除（DELETE JOIN）
DELETE s FROM students s
INNER JOIN classes c ON s.class_id = c.id
WHERE c.grade = '大二';
-- 使用子查询删除
DELETE FROM students
WHERE class_id IN (
SELECT id FROM classes WHERE grade = '大二'
);
-- 限制删除行数
DELETE FROM students WHERE status = '退学' LIMIT 10;
-- TRUNCATE vs DELETE
-- TRUNCATE：快速清空，不记录日志，重置自增值
-- DELETE：慢，记录日志，可回滚，不重置自增值
TRUNCATE TABLE students;6.4 事务中的数据操作
sql
-- 开启事务
START TRANSACTION;
-- 或
BEGIN;
-- 执行操作
INSERT INTO students (student_no, name, gender, class_id)
VALUES ('2023005', '事务测试', '男', 1);
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
-- 提交事务
COMMIT;
-- 回滚事务（出错时）
ROLLBACK;6.5 本章练习
- 练习1：批量插入10条测试数据
- 练习2：更新所有学生的状态字段
- 练习3：删除年龄小于18岁的学生
- 练习4：测试事务的回滚功能
- 练习5：使用INSERT IGNORE处理重复数据
第7章 索引与查询优化
7.1 什么是索引？
索引（Index） 是帮助MySQL高效获取数据的数据结构。简单来说，索引就是一本书的目录，通过目录可以快速找到需要的内容。
索引的优缺点：
- ✅ 大大加快查询速度
- ✅ 降低IO成本
- ✅ 降低CPU消耗
- ❌ 占用更多空间
- ❌ 降低写入速度
- ❌ 需要维护成本
7.2 索引类型
按数据结构分类：
- B+树索引：最常用，适合大多数场景
- 哈希索引：等值查询快，不支持范围查询
- 全文索引：用于全文搜索
按功能分类：
- 主键索引（PRIMARY KEY）
- 唯一索引（UNIQUE）
- 普通索引（INDEX）
- 全文索引（FULLTEXT）
按字段数量分类：
- 单列索引
- 组合索引（联合索引）
sql
-- 创建索引
CREATE INDEX idx_name ON students(name);
-- 创建唯一索引
CREATE UNIQUE INDEX uk_student_no ON students(student_no);
-- 创建组合索引
CREATE INDEX idx_class_gender ON students(class_id, gender);
-- 创建全文索引
CREATE FULLTEXT INDEX ft_content ON articles(content);
-- 查看索引
SHOW INDEX FROM students;
-- 删除索引
DROP INDEX idx_name ON students;
ALTER TABLE students DROP INDEX idx_name;
-- 删除主键
ALTER TABLE students DROP PRIMARY KEY;
-- 8.0+：倒序索引（配合 ORDER BY ... DESC，让优化器走索引）
CREATE INDEX idx_age_desc ON students(age DESC);
-- 8.0+：不可见索引（仅做优化试验，不立即生效）
CREATE INDEX idx_gender ON students(gender) INVISIBLE;
ALTER TABLE students ALTER INDEX idx_gender VISIBLE;🧑💻 个人最新版（8.0+ / 9.x / 26.7）：上述 8.0 专属语法可直接使用。 🏭 企业版本差异：
- 倒序索引、不可见索引、跳跃扫描（SKIP SCAN，8.0.13+） 均为 8.0 起引入，8.0 / 8.4 / 9.7 支持；5.7 不支持（执行会报语法错误）。
- 索引条件下推（ICP） 5.6+ 即支持；7.6 节中
LIKE '张%三'利用 ICP 的行为在 8.0+ 表现更好。
7.3 B+树索引原理
B+树特点：
- 所有数据都存储在叶子节点
- 叶子节点形成有序链表
- 非叶子节点只存储键值
- 树的高度通常为2-4层
为什么使用B+树？
- 磁盘IO友好：每个节点可以存储多个键值，减少IO次数
- 范围查询高效：叶子节点形成链表，范围查询只需遍历链表
- 排序查询高效：叶子节点有序，ORDER BY可以直接使用索引
7.4 联合索引与最左前缀
联合索引（Composite Index） 是在多个字段上创建的索引。
sql
-- 创建联合索引
CREATE INDEX idx_class_gender_age ON students(class_id, gender, age);
-- 最左前缀原则
-- 索引可以匹配的查询条件：
-- 1. class_id = 1
-- 2. class_id = 1 AND gender = '男'
-- 3. class_id = 1 AND gender = '男' AND age > 20
-- 不能匹配的查询条件：
-- 1. gender = '男'
-- 2. gender = '男' AND age > 20
-- 3. age > 20最左前缀原则详解：
- 联合索引 (a, b, c)
- 查询条件必须从最左列开始匹配
- 可以跳过中间列，但不能跳过左列
7.5 覆盖索引
覆盖索引（Covering Index） 是指查询所需的所有列都在索引中，不需要回表查询。
sql
-- 创建覆盖索引
CREATE INDEX idx_cover ON students(student_no, name, class_id);
-- 使用覆盖索引的查询
SELECT student_no, name, class_id FROM students WHERE student_no = '2023001';
-- EXPLAIN中的Extra列会显示 "Using index"
-- 需要回表的查询
SELECT * FROM students WHERE student_no = '2023001';
-- 因为SELECT *需要查询所有列，而索引中没有所有列7.6 索引失效场景
注意：并不是所有"可能绕过索引"的写法都一定会失效，取决于优化器判断和数据分布。下面分为两类：
A. 铁律（几乎一定会导致全表扫描，务必避免）：
sql
-- 1. 对索引列使用函数或表达式
SELECT * FROM students WHERE LEFT(name, 1) = '张';
-- 优化：改为 WHERE name LIKE '张%'
-- 2. 隐式类型转换（列类型与条件值类型不一致）
SELECT * FROM students WHERE id = '1'; -- id是INT类型，'1'是字符串
-- 优化：改为 WHERE id = 1
-- 3. LIKE以%开头（前导通配符无法定位区间）
SELECT * FROM students WHERE name LIKE '%三';
-- 优化：改为 name LIKE '三%' 或使用全文索引
-- 4. OR两端未同时命中索引（一侧不满足即全表扫描）
SELECT * FROM students WHERE id = 1 OR name = '张三'; -- 假设name无索引
-- 优化：为 name 建索引，或改为 UNION ALL
-- 5. 条件覆盖大多数行时优化器主动放弃索引（选择率过低）
SELECT * FROM students WHERE is_active = 1; -- 若绝大多数行都是1，全表扫更快B. 视情况而定（可能失效，也可能仍走索引，取决于数据分布和优化器判断）：
sql
-- 1. IS NULL / IS NOT NULL
SELECT * FROM students WHERE email IS NULL;
-- 结论：不一定失效。B+树把NULL放在叶子链表的一端，可正常使用索引；
-- 只有当该列绝大多数值为NULL时优化器才可能选择全表扫描
-- 2. NOT IN / NOT EXISTS / !=（优化器可能转为范围扫描）
SELECT * FROM students WHERE id NOT IN (1, 2, 3);
-- 结论：不一定失效。仅当值集合很小且排除的行占比过高时才可能全表扫
-- 3. LIKE把通配符放在中间（如 '张%三'）
SELECT * FROM students WHERE name LIKE '张%三';
-- 结论：MySQL 8.0 支持索引条件下推（ICP），可能部分利用索引，但效率低于 '张%'判断方法： 用 EXPLAIN 查看 type 和 key 列——type 为 ALL（全表扫描）且 key 为 NULL 即索引未生效，详见 7.7 节。
7.7 EXPLAIN执行计划
EXPLAIN 用于分析SQL语句的执行计划。
sql
EXPLAIN SELECT * FROM students WHERE class_id = 1;
-- 关键字段说明：
-- type：访问类型
-- system > const > eq_ref > ref > range > index > ALL
-- key：实际使用的索引
-- rows：预估扫描行数
-- Extra：额外信息
-- Using index：使用覆盖索引
-- Using where：使用WHERE过滤
-- Using temporary：使用临时表
-- Using filesort：使用文件排序EXPLAIN 输出示例（⚠️ 以下为示意输出，实际 key_len / rows / Extra 等数值会随表结构和数据分布变化）：
sql
EXPLAIN SELECT s.name, c.class_name
FROM students s
INNER JOIN classes c ON s.class_id = c.id
WHERE s.class_id = 1;
-- 输出结果解读：
-- +----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
-- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-- +----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
-- | 1 | SIMPLE | s | ref | idx_class_id | idx_class_id | 5 | const | 10 | |
-- | 1 | SIMPLE | c | eq_ref| PRIMARY | PRIMARY | 4 | s.class_id | 1 | |
-- +----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+7.8 查询优化技巧
sql
-- 1. 避免SELECT *
SELECT id, name, email FROM students WHERE id = 1;
-- 2. 使用LIMIT限制结果集
SELECT * FROM students LIMIT 100;
-- 3. 批量插入数据
INSERT INTO students (name, age, class_id) VALUES
('张三', 20, 1),
('李四', 21, 1),
('王五', 22, 2);
-- 4. 使用EXISTS代替IN
-- 慢
SELECT * FROM students WHERE class_id IN (SELECT id FROM classes WHERE grade = '大二');
-- 快
SELECT * FROM students s WHERE EXISTS (
SELECT 1 FROM classes c WHERE c.id = s.class_id AND c.grade = '大二'
);
-- 5. 避免在WHERE中使用函数
-- 慢
SELECT * FROM students WHERE YEAR(create_time) = 2023;
-- 快
SELECT * FROM students WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01';
-- 6. 合理使用JOIN代替子查询
-- 慢
SELECT * FROM students WHERE class_id = (SELECT id FROM classes WHERE class_name = '计算机1班');
-- 快
SELECT s.* FROM students s
INNER JOIN classes c ON s.class_id = c.id
WHERE c.class_name = '计算机1班';7.9 本章练习
- 练习1：为学生表创建合适的索引
- 练习2：使用EXPLAIN分析慢查询
- 练习3：找出索引失效的查询并优化
- 练习4：设计覆盖索引提高查询性能
- 练习5：对比优化前后的查询性能
第8章 事务处理
8.1 什么是事务？
事务（Transaction） 是一组操作序列，这些操作要么全部成功，要么全部失败。
事务的典型场景：
- 银行转账：A向B转账100元
- A账户扣除100元
- B账户增加100元
- 这两步必须同时成功或同时失败
8.2 事务的ACID特性
| 特性 | 英文 | 说明 |
|---|---|---|
| 原子性 | Atomicity | 事务是不可分割的工作单元，要么全部执行，要么全部不执行 |
| 一致性 | Consistency | 事务执行前后，数据库从一个一致性状态变换到另一个一致性状态 |
| 隔离性 | Isolation | 多个并发事务之间相互隔离，互不干扰 |
| 持久性 | Durability | 事务一旦提交，其结果就是永久性的 |
8.3 事务控制语法
sql
-- 开启事务
START TRANSACTION;
-- 或
BEGIN;
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;
-- 设置保存点
SAVEPOINT savepoint_name;
-- 回滚到保存点
ROLLBACK TO SAVEPOINT savepoint_name;
-- 释放保存点
RELEASE SAVEPOINT savepoint_name;8.4 事务隔离级别
下表 "可能" 表示该隔离级别允许/可能发生该类并发问题，"不会" 表示不会发生（按通用 SQL 标准，细节以 MySQL 实现为准）
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 说明 |
|---|---|---|---|---|
| READ UNCOMMITTED | 可能 | 可能 | 可能 | 最低级别，性能最好 |
| READ COMMITTED | 不会 | 可能 | 可能 | Oracle默认级别 |
| REPEATABLE READ | 不会 | 不会 | 不会* | MySQL默认级别 |
| SERIALIZABLE | 不会 | 不会 | 不会 | 最高级别，性能最差 |
问题说明：
- 脏读：读取到其他事务未提交的数据
- 不可重复读：同一事务内两次读取结果不同（其他事务修改了数据）
- 幻读：同一事务内两次查询结果不同（其他事务插入或删除了数据）
⚠️ MySQL 特别说明（重点）：
- 按通用 SQL 标准，REPEATABLE READ 允许出现幻读；但 MySQL InnoDB 存储引擎的 REPEATABLE READ 借助 MVCC（快照读）和 next-key lock（记录锁 + 间隙锁，用于当前读），已彻底解决了幻读。
- 这是面试高频考点：MySQL 默认隔离级别是 REPEATABLE READ，且不会产生幻读（这一点与 Oracle 等数据库不同）。
sql
-- 查看当前隔离级别
SELECT @@transaction_isolation;
-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;8.5 事务使用示例
sql
-- 示例：银行转账
CREATE TABLE accounts (
id INT PRIMARY KEY AUTO_INCREMENT,
user_name VARCHAR(50) NOT NULL,
balance DECIMAL(10,2) DEFAULT 0.00
);
INSERT INTO accounts (user_name, balance) VALUES
('张三', 1000.00),
('李四', 500.00);
-- 开启事务
START TRANSACTION;
-- 检查余额
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- 执行转账
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- 检查余额是否足够
-- 如果余额不足，回滚
-- ROLLBACK;
-- 提交事务
COMMIT;8.6 死锁处理
死锁 是两个或多个事务互相等待对方释放资源的情况。
sql
-- 死锁示例
-- 事务1
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- 等待事务2释放
-- 事务2
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 2;
UPDATE accounts SET balance = balance + 100 WHERE id = 1; -- 等待事务1释放
-- 解决方案：
-- 1. 保持相同的加锁顺序
-- 2. 小事务，减少锁持有时间
-- 3. 使用合理的索引，减少锁范围
-- 4. 设置锁等待超时
SET innodb_lock_wait_timeout = 50;
-- 查看死锁信息
SHOW ENGINE INNODB STATUS;8.7 本章练习
- 练习1：实现一个简单的银行转账事务
- 练习2：测试不同隔离级别下的并发问题
- 练习3：模拟死锁并解决
- 练习4：使用保存点实现部分回滚
- 练习5：分析事务的ACID特性在实际场景中的体现
第三部分：高级特性
第9章 存储过程与函数
9.1 什么是存储过程？
存储过程（Stored Procedure） 是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数来执行它。
存储过程的优点：
- ✅ 预编译，执行效率高
- ✅ 减少网络传输
- ✅ 代码复用
- ✅ 安全性高（可以限制直接访问表）
9.2 创建存储过程
sql
-- 修改语句分隔符（存储过程中需要使用分号）
DELIMITER //
-- 创建无参数的存储过程
CREATE PROCEDURE GetAllStudents()
BEGIN
SELECT * FROM students;
END //
-- 创建带参数的存储过程
CREATE PROCEDURE GetStudentsByClass(IN classId INT)
BEGIN
SELECT * FROM students WHERE class_id = classId;
END //
-- 创建带输出参数的存储过程
CREATE PROCEDURE GetStudentCount(OUT totalCount INT)
BEGIN
SELECT COUNT(*) INTO totalCount FROM students;
END //
-- 创建带输入输出参数的存储过程
CREATE PROCEDURE UpdateStudentStatus(
IN studentId INT,
IN newStatus VARCHAR(20),
OUT result INT
)
BEGIN
DECLARE affectedRows INT;
UPDATE students SET status = newStatus WHERE id = studentId;
SET affectedRows = ROW_COUNT();
IF affectedRows > 0 THEN
SET result = 1; -- 成功
ELSE
SET result = 0; -- 失败
END IF;
END //
-- 恢复分隔符
DELIMITER ;9.3 调用存储过程
sql
-- 调用无参数存储过程
CALL GetAllStudents();
-- 调用带输入参数存储过程
CALL GetStudentsByClass(1);
-- 调用带输出参数存储过程
CALL GetStudentCount(@total);
SELECT @total AS '学生总数';
-- 调用带输入输出参数存储过程
CALL UpdateStudentStatus(1, '毕业', @result);
SELECT @result AS '操作结果';9.4 变量和流程控制
sql
DELIMITER //
CREATE PROCEDURE StudentStatistics()
BEGIN
-- 声明变量
DECLARE totalStudents INT DEFAULT 0;
DECLARE avgAge DECIMAL(5,2);
DECLARE maxAge INT;
DECLARE minAge INT;
DECLARE classCount INT;
-- 赋值
SELECT COUNT(*), AVG(age), MAX(age), MIN(age)
INTO totalStudents, avgAge, maxAge, minAge
FROM students;
SELECT COUNT(DISTINCT class_id) INTO classCount FROM students;
-- IF语句
IF totalStudents > 100 THEN
SELECT '学生数量较多' AS '提示';
ELSEIF totalStudents > 50 THEN
SELECT '学生数量适中' AS '提示';
ELSE
SELECT '学生数量较少' AS '提示';
END IF;
-- CASE语句
SELECT
CASE
WHEN avgAge < 20 THEN '年龄偏小'
WHEN avgAge >= 20 AND avgAge < 22 THEN '年龄适中'
ELSE '年龄偏大'
END AS '年龄分析';
-- WHILE循环
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= classCount DO
-- 处理每个班级
SET i = i + 1;
END WHILE;
END;
-- LOOP循环
BEGIN
DECLARE i INT DEFAULT 1;
loop_label: LOOP
IF i > 5 THEN
LEAVE loop_label;
END IF;
SET i = i + 1;
END LOOP loop_label;
END;
-- REPEAT循环
BEGIN
DECLARE i INT DEFAULT 1;
REPEAT
SET i = i + 1;
UNTIL i > 5
END REPEAT;
END;
END //
DELIMITER ;9.5 创建函数
sql
DELIMITER //
-- 创建函数
CREATE FUNCTION GetAge(birthDate DATE)
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE age INT;
SET age = TIMESTAMPDIFF(YEAR, birthDate, CURDATE());
RETURN age;
END //
-- 创建计算成绩等级的函数
CREATE FUNCTION GetScoreLevel(score DECIMAL(5,2))
RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
DECLARE level VARCHAR(10);
IF score >= 90 THEN
SET level = '优秀';
ELSEIF score >= 80 THEN
SET level = '良好';
ELSEIF score >= 70 THEN
SET level = '中等';
ELSEIF score >= 60 THEN
SET level = '及格';
ELSE
SET level = '不及格';
END IF;
RETURN level;
END //
DELIMITER ;
-- 使用函数
SELECT
name,
birth_date,
GetAge(birth_date) AS age,
GetScoreLevel(85) AS level
FROM students;9.6 异常处理
sql
DELIMITER //
CREATE PROCEDURE SafeTransfer(
IN fromId INT,
IN toId INT,
IN amount DECIMAL(10,2)
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
-- 出错时回滚
ROLLBACK;
SELECT '转账失败：发生错误' AS '结果';
END;
DECLARE EXIT HANDLER FOR 1216 -- 外键约束错误（ERROR 1216）
BEGIN
ROLLBACK;
SELECT '转账失败：账户不存在' AS '结果';
END;
DECLARE EXIT HANDLER FOR 1264 -- 数值超出范围（ERROR 1264，非"余额不足"）
BEGIN
ROLLBACK;
SELECT '转账失败：金额超出范围' AS '结果';
END;
START TRANSACTION;
-- 检查余额：余额不足通过 SIGNAL 抛错（SQLSTATE '45000' 属 SQLEXCEPTION 类），
-- 会被最上面的 SQLEXCEPTION handler 统一捕获
IF (SELECT balance FROM accounts WHERE id = fromId) < amount THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '余额不足';
END IF;
-- 执行转账
UPDATE accounts SET balance = balance - amount WHERE id = fromId;
UPDATE accounts SET balance = balance + amount WHERE id = toId;
COMMIT;
SELECT '转账成功' AS '结果';
END //
DELIMITER ;9.7 本章练习
- 练习1：创建一个存储过程，批量生成学生数据
- 练习2：创建一个函数，计算两个日期之间的天数
- 练习3：创建一个存储过程，实现分页查询
- 练习4：创建一个存储过程，实现数据备份
- 练习5：创建一个函数，格式化手机号显示
第10章 触发器与事件
10.1 什么是触发器？
触发器（Trigger） 是与表相关的数据库对象，在INSERT/UPDATE/DELETE操作前后自动执行。
触发器类型：
- BEFORE INSERT：插入前触发
- AFTER INSERT：插入后触发
- BEFORE UPDATE：更新前触发
- AFTER UPDATE：更新后触发
- BEFORE DELETE：删除前触发
- AFTER DELETE：删除后触发
10.2 创建触发器
sql
DELIMITER //
-- BEFORE INSERT触发器：自动填充字段
CREATE TRIGGER before_student_insert
BEFORE INSERT ON students
FOR EACH ROW
BEGIN
-- 如果创建时间为空，设置为当前时间
IF NEW.create_time IS NULL THEN
SET NEW.create_time = NOW();
END IF;
-- 自动生成学号（⚠️ 本示例仅演示触发器语法，存在并发缺陷：
-- LAST_INSERT_ID() 在 BEFORE INSERT 中取到的是上一次会话插入的自增ID，并非本次；
-- 高并发下会生成重复学号。生产环境应改用独立序号表 / 雪花ID，或在应用层生成唯一业务号）
IF NEW.student_no IS NULL OR NEW.student_no = '' THEN
SET NEW.student_no = CONCAT('STU', DATE_FORMAT(NOW(), '%Y%m%d'), LPAD(LAST_INSERT_ID() + 1, 4, '0'));
END IF;
END //
-- AFTER INSERT触发器：记录操作日志
CREATE TABLE operation_logs (
id INT AUTO_INCREMENT PRIMARY KEY,
table_name VARCHAR(50),
operation VARCHAR(20),
record_id INT,
old_data JSON,
new_data JSON,
create_time DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE TRIGGER after_student_insert
AFTER INSERT ON students
FOR EACH ROW
BEGIN
INSERT INTO operation_logs (table_name, operation, record_id, new_data)
VALUES ('students', 'INSERT', NEW.id, JSON_OBJECT(
'student_no', NEW.student_no,
'name', NEW.name,
'gender', NEW.gender
));
END //
-- UPDATE触发器：直接在 AFTER UPDATE 中使用 OLD/NEW 记录修改前后的数据
-- （注意：无需先建 BEFORE 触发器用 @old_data 用户变量传值——用户变量是会话级的，
-- 容易被其他语句覆盖，产生脏数据；AFTER UPDATE 中即可读取 OLD.* ）
CREATE TRIGGER after_student_update
AFTER UPDATE ON students
FOR EACH ROW
BEGIN
INSERT INTO operation_logs (table_name, operation, record_id, old_data, new_data)
VALUES ('students', 'UPDATE', NEW.id, JSON_OBJECT(
'name', OLD.name,
'age', OLD.age,
'status', OLD.status
), JSON_OBJECT(
'name', NEW.name,
'age', NEW.age,
'status', NEW.status
));
END //
-- BEFORE DELETE触发器：记录删除的数据
CREATE TRIGGER before_student_delete
BEFORE DELETE ON students
FOR EACH ROW
BEGIN
INSERT INTO operation_logs (table_name, operation, record_id, old_data)
VALUES ('students', 'DELETE', OLD.id, JSON_OBJECT(
'student_no', OLD.student_no,
'name', OLD.name
));
END //
DELIMITER ;10.3 查看和删除触发器
sql
-- 查看触发器
SHOW TRIGGERS;
SHOW TRIGGERS LIKE 'students';
-- 查看触发器创建语句
SHOW CREATE TRIGGER after_student_insert;
-- 删除触发器
DROP TRIGGER IF EXISTS after_student_insert;10.4 触发器使用场景
sql
-- 场景1：数据验证
DELIMITER //
CREATE TRIGGER validate_student_age
BEFORE INSERT ON students
FOR EACH ROW
BEGIN
IF NEW.age < 15 OR NEW.age > 30 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = '年龄必须在15-30岁之间';
END IF;
END //
-- 场景2：级联更新
CREATE TRIGGER update_class_student_count
AFTER INSERT ON students
FOR EACH ROW
BEGIN
UPDATE classes
SET student_count = (SELECT COUNT(*) FROM students WHERE class_id = NEW.class_id)
WHERE id = NEW.class_id;
END //
-- 场景3：数据同步
CREATE TABLE student_archive (
id INT PRIMARY KEY,
student_no VARCHAR(20),
name VARCHAR(50),
archived_time DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE TRIGGER archive_graduated_student
AFTER UPDATE ON students
FOR EACH ROW
BEGIN
IF NEW.status = '毕业' AND OLD.status != '毕业' THEN
INSERT INTO student_archive (id, student_no, name)
VALUES (NEW.id, NEW.student_no, NEW.name);
END IF;
END //
DELIMITER ;10.5 事件调度器
事件（Event） 是MySQL中的定时任务，可以在指定时间执行SQL语句。
sql
-- 开启事件调度器
SET GLOBAL event_scheduler = ON;
DELIMITER //
-- 创建一次性事件
CREATE EVENT one_time_event
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
BEGIN
-- 1小时后执行的操作
DELETE FROM operation_logs WHERE create_time < DATE_SUB(NOW(), INTERVAL 30 DAY);
END //
-- 创建重复事件
CREATE EVENT daily_cleanup
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 02:00:00'
DO
BEGIN
-- 每天凌晨2点执行
DELETE FROM operation_logs WHERE create_time < DATE_SUB(NOW(), INTERVAL 90 DAY);
-- 更新统计信息
ANALYZE TABLE students;
END //
-- 创建月度事件
CREATE EVENT monthly_report
ON SCHEDULE EVERY 1 MONTH
STARTS '2024-01-01 08:00:00'
DO
BEGIN
-- 每月1号上午8点执行
INSERT INTO monthly_statistics (stat_date, student_count, class_count)
SELECT
CURDATE(),
(SELECT COUNT(*) FROM students),
(SELECT COUNT(DISTINCT class_id) FROM students);
END //
DELIMITER ;
-- 查看事件
SHOW EVENTS;
SHOW EVENTS LIKE 'daily%';
-- 修改事件
ALTER EVENT daily_cleanup
ON SCHEDULE EVERY 2 DAY;
-- 启用/禁用事件
ALTER EVENT daily_cleanup ENABLE;
ALTER EVENT daily_cleanup DISABLE;
-- 删除事件
DROP EVENT IF EXISTS one_time_event;10.6 本章练习
- 练习1：创建一个触发器，在插入订单时自动更新库存
- 练习2：创建一个触发器，记录所有数据修改历史
- 练习3：创建一个事件，每天自动清理过期数据
- 练习4：创建一个事件，每月生成统计报表
- 练习5：测试触发器的执行顺序
第11章 视图与数据完整性
11.1 什么是视图？
视图（View） 是一个虚拟表，其内容由查询定义。视图并不存储数据，只是存储了查询语句。
视图的优点：
- ✅ 简化复杂查询
- ✅ 提高数据安全性（隐藏敏感字段）
- ✅ 不同用户看到不同数据
- ✅ 逻辑数据独立性
11.2 创建视图
sql
-- 创建简单视图
CREATE VIEW v_student_info AS
SELECT
id,
student_no,
name,
gender,
class_id,
status
FROM students;
-- 使用视图
SELECT * FROM v_student_info WHERE class_id = 1;
-- 创建复杂视图（包含连接和聚合）
CREATE VIEW v_class_statistics AS
SELECT
c.id AS class_id,
c.class_name,
COUNT(s.id) AS student_count,
ROUND(AVG(s.age), 2) AS avg_age,
MAX(s.age) AS max_age,
MIN(s.age) AS min_age
FROM classes c
LEFT JOIN students s ON c.id = s.class_id
GROUP BY c.id, c.class_name;
-- 使用复杂视图
SELECT * FROM v_class_statistics WHERE student_count > 10;
-- 创建包含JOIN的视图
CREATE VIEW v_student_detail AS
SELECT
s.id,
s.student_no,
s.name AS student_name,
s.gender,
s.birth_date,
c.class_name,
c.teacher_name,
s.status
FROM students s
LEFT JOIN classes c ON s.class_id = c.id;11.3 修改和删除视图
sql
-- 修改视图
CREATE OR REPLACE VIEW v_student_info AS
SELECT
id,
student_no,
name,
gender,
class_id,
status,
create_time
FROM students;
-- ALTER VIEW语法
ALTER VIEW v_student_info AS
SELECT
id,
student_no,
name
FROM students;
-- 删除视图
DROP VIEW IF EXISTS v_student_info;
-- 查看视图定义
SHOW CREATE VIEW v_student_info;
SHOW FULL TABLES WHERE Table_type = 'VIEW';11.4 可更新视图
sql
-- 可更新视图的条件：
-- 1. 没有聚合函数（SUM, COUNT, AVG, MAX, MIN）
-- 2. 没有GROUP BY / HAVING / DISTINCT
-- 3. 没有UNION
-- 4. 没有子查询（FROM子句中的子查询除外）
-- 5. 没有JOIN（某些情况下可以）
-- 6. 没有WHERE子句引用其他表
-- 示例：可更新视图
CREATE VIEW v_active_students AS
SELECT id, student_no, name, email
FROM students
WHERE status = '在读';
-- 通过视图更新数据
UPDATE v_active_students SET email = 'new@example.com' WHERE id = 1;
-- 通过视图插入数据
INSERT INTO v_active_students (student_no, name)
VALUES ('2023999', '视图测试');
-- 删除数据
DELETE FROM v_active_students WHERE id = 1;
-- 不可更新视图示例
CREATE VIEW v_student_summary AS
SELECT
class_id,
COUNT(*) AS student_count
FROM students
GROUP BY class_id;
-- 以下操作会失败
-- UPDATE v_student_summary SET student_count = 10 WHERE class_id = 1;11.5 CHECK OPTION
sql
-- WITH CHECK OPTION：确保通过视图的操作仍然满足视图的WHERE条件
CREATE VIEW v_young_students AS
SELECT * FROM students WHERE age < 20
WITH CHECK OPTION;
-- 以下操作会失败（因为age >= 20）
-- INSERT INTO v_young_students (student_no, name, age) VALUES ('2023998', '测试', 25);
-- CASCADED CHECK OPTION（默认）
CREATE VIEW v_class1_students AS
SELECT * FROM students WHERE class_id = 1
WITH CASCADED CHECK OPTION;
-- LOCAL CHECK OPTION
CREATE VIEW v_class2_students AS
SELECT * FROM students WHERE class_id = 2
WITH LOCAL CHECK OPTION;11.6 数据完整性约束
完整性约束确保数据库中数据的准确性和一致性。
sql
-- 实体完整性（主键）
CREATE TABLE students (
id INT PRIMARY KEY,
student_no VARCHAR(20) UNIQUE
);
-- 参照完整性（外键）
CREATE TABLE student_courses (
student_id INT,
course_id INT,
FOREIGN KEY (student_id) REFERENCES students(id),
FOREIGN KEY (course_id) REFERENCES courses(id)
);
-- 用户定义完整性（CHECK约束）
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(100),
price DECIMAL(10,2) CHECK (price >= 0),
stock INT CHECK (stock >= 0)
);
-- 默认值约束
CREATE TABLE orders (
id INT PRIMARY KEY,
status VARCHAR(20) DEFAULT '待处理',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP
);11.7 本章练习
- 练习1：创建一个视图，显示学生详细信息（包含班级名称）
- 练习2：创建一个统计视图，显示每个班级的学生数量和平均年龄
- 练习3：测试可更新视图和不可更新视图的区别
- 练习4：使用WITH CHECK OPTION限制视图操作
- 练习5：设计一个数据库，包含完整的约束
第12章 用户权限管理
12.1 用户管理
⚠️ 认证插件（Authentication Plugin）版本差异： 用户能否登录还取决于用哪个插件校验密码。
| 认证插件 | 5.7 | 8.0 | 8.4 | 9.x / 26.7（最新） |
|---|---|---|---|---|
mysql_native_password | 默认 | 可用 | 默认禁用 | 已移除 |
caching_sha2_password | 可选 | 默认 | 默认 | 默认 |
🧑💻 个人最新版： 全部使用默认的
caching_sha2_password即可，无需关注旧插件。 🏭 企业生产（高频故障点）： 报Authentication plugin ... cannot be loaded通常是——程序/驱动/旧客户端只认mysql_native_password，而数据库已是 8.0+。对策：升级驱动；或对无法升级的旧账户显式创建 native 账户（仅 8.0 / 8.4 可行；9.x 已移除该插件）。5.7 默认就是 native，一般无需处理。 📌 8.0+ 的mysql.user表不再有Password列，统一为authentication_string列，旧脚本查Password会报列不存在。
sql
-- 创建用户（默认认证插件，推荐）
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password123';
-- 显式指定认证插件（兼容老客户端的写法，仅 8.0/8.4 可用）
CREATE USER 'legacy_user'@'localhost'
IDENTIFIED WITH mysql_native_password BY 'password123';– 创建用户（允许远程访问） CREATE USER 'newuser'@'%' IDENTIFIED BY 'password123';
– 创建用户（指定主机） CREATE USER 'newuser'@'192.168.1.%' IDENTIFIED BY 'password123';
– 修改用户密码 ALTER USER 'newuser'@'localhost' IDENTIFIED BY 'newpassword456';
– 重命名用户 RENAME USER 'newuser'@'localhost' TO 'renameduser'@'localhost';
– 删除用户 DROP USER IF EXISTS 'newuser'@'localhost';
– 查看用户 SELECT user, host FROM mysql.user; SHOW USER()\G – 查看当前用户
### 12.2 权限管理
```sql
-- 权限层级
-- 全局层级：*.*
-- 数据层级：database_name.*
-- 表层级：database_name.table_name
-- 列层级：database_name.table_name.column_name
-- 授予权限
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';
GRANT SELECT, INSERT, UPDATE ON school.* TO 'user1'@'localhost';
GRANT SELECT ON school.students TO 'user2'@'localhost';
-- 授予权限（带密码）
CREATE USER 'user3'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, UPDATE, DELETE ON school.* TO 'user3'@'localhost';
-- 撤销权限
REVOKE INSERT ON school.* FROM 'user1'@'localhost';
-- 查看权限
SHOW GRANTS FOR 'user1'@'localhost';
SHOW GRANTS FOR CURRENT_USER();
-- 刷新权限
FLUSH PRIVILEGES;
-- 权限列表
-- ALL PRIVILEGES：所有权限
-- SELECT：查询
-- INSERT：插入
-- UPDATE：更新
-- DELETE：删除
-- CREATE：创建数据库和表
-- DROP：删除数据库和表
-- ALTER：修改表结构
-- INDEX：创建和删除索引
-- REFERENCES：创建外键
-- EXECUTE：执行存储过程和函数
-- CREATE VIEW：创建视图
-- SHOW VIEW：查看视图定义
-- CREATE ROUTINE：创建存储过程和函数
-- ALTER ROUTINE：修改和删除存储过程和函数
-- TRIGGER：创建触发器12.3 角色管理（MySQL 8.0+）
sql
-- 创建角色
CREATE ROLE 'student_role', 'teacher_role', 'admin_role';
-- 给角色授权
GRANT SELECT ON school.* TO 'student_role';
GRANT SELECT, INSERT, UPDATE ON school.* TO 'teacher_role';
GRANT ALL PRIVILEGES ON school.* TO 'admin_role';
-- 将角色赋给用户
GRANT 'student_role' TO 'user1'@'localhost';
GRANT 'teacher_role' TO 'user2'@'localhost';
-- 激活角色（登录后需要激活）
SET DEFAULT ROLE ALL TO 'user1'@'localhost';
-- 或
SET ROLE 'student_role';
-- 查看角色
SHOW GRANTS FOR 'user1'@'localhost';
SELECT * FROM mysql.role_edges;
-- 撤销角色
REVOKE 'student_role' FROM 'user1'@'localhost';
-- 删除角色
DROP ROLE IF EXISTS 'student_role';12.4 密码策略
先决条件：MySQL 8.0 默认未安装 validate_password 组件，直接 SET GLOBAL 会报错，需先安装：
sql
-- MySQL 8.0：安装 validate_password 组件（5.7 是内置插件，无需此步）
INSTALL COMPONENT 'file://component_validate_password';
-- 如需卸载
-- UNINSTALL COMPONENT 'file://component_validate_password';sql
-- 查看密码策略（组件安装后才有这些变量）
SHOW VARIABLES LIKE 'validate_password%';
-- 设置密码策略
SET GLOBAL validate_password.policy = MEDIUM; -- LOW, MEDIUM, STRONG
SET GLOBAL validate_password.length = 8;
-- 密码过期策略
ALTER USER 'user1'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'user1'@'localhost' PASSWORD EXPIRE NEVER;
-- 密码历史策略
SET GLOBAL password_history = 5;
SET GLOBAL password_reuse_interval = 365;
-- 账户锁定
ALTER USER 'user1'@'localhost' ACCOUNT LOCK;
ALTER USER 'user1'@'localhost' ACCOUNT UNLOCK;
-- 连接限制
ALTER USER 'user1'@'localhost' WITH MAX_CONNECTIONS_PER_HOUR 100
MAX_QUERIES_PER_HOUR 1000
MAX_UPDATES_PER_HOUR 100
MAX_USER_CONNECTIONS 10;12.5 安全最佳实践
sql
-- 1. 不要使用root账户进行日常操作
-- 2. 为不同应用创建不同用户
-- 3. 遵循最小权限原则
-- 4. 定期修改密码
-- 5. 禁止远程root登录
-- 6. 启用密码策略
-- 7. 审计用户活动
-- 查看用户活动
SELECT * FROM mysql.user WHERE authentication_string = '';
-- 查看连接信息
SHOW PROCESSLIST;
-- 查看用户统计
SELECT * FROM mysql.user WHERE Super_priv = 'Y';12.6 本章练习
- 练习1：创建一个只读用户，只能查询学生表
- 练习2：创建一个管理员用户，拥有所有权限
- 练习3：创建角色并分配权限
- 练习4：测试权限限制的效果
- 练习5：设计一个安全的用户权限方案
第四部分：实战应用
第13章 备份与恢复
13.1 备份策略
备份类型：
- 完全备份：备份所有数据
- 增量备份：只备份自上次备份后变化的数据
- 差异备份：只备份自上次完全备份后变化的数据
备份方式：
- 热备份：数据库运行时备份（InnoDB支持）
- 温备份：数据库只读时备份
- 冷备份：数据库停止时备份
13.2 mysqldump备份
💡 生产环境必加参数：
--single-transaction使 InnoDB 表做一致性快照备份，备份期间不加锁、不影响线上读写；--default-character-set=utf8mb4避免中文乱码；全库备份可另加--routines --triggers保留存储过程与触发器。
bash
# 备份单个数据库（推荐生产写法）
mysqldump -u root -p --single-transaction --default-character-set=utf8mb4 school > school_backup.sql
# 备份多个数据库
mysqldump -u root -p --databases school test_db > multi_db_backup.sql
# 备份所有数据库
mysqldump -u root -p --all-databases > all_databases_backup.sql
# 备份特定表
mysqldump -u root -p school students classes > tables_backup.sql
# 备份表结构
mysqldump -u root -p --no-data school > schema_backup.sql
# 备份数据
mysqldump -u root -p --no-create-info school > data_backup.sql
# 压缩备份
mysqldump -u root -p school | gzip > school_backup.sql.gz
# 带时间戳的备份
mysqldump -u root -p school > "school_$(date +%Y%m%d_%H%M%S).sql"
# 远程备份
mysqldump -h remote_host -u root -p school > remote_backup.sql13.3 恢复数据
bash
# 恢复数据库
mysql -u root -p school < school_backup.sql
# 恢复压缩备份
gunzip < school_backup.sql.gz | mysql -u root -p school
# 恢复到新数据库
mysql -u root -p -e "CREATE DATABASE school_new;"
mysql -u root -p school_new < school_backup.sql
# 从备份中恢复特定表
mysql -u root -p school < tables_backup.sqlMySQL命令行恢复：
sql
-- 使用SOURCE命令
SOURCE /path/to/school_backup.sql;
-- 使用LOAD DATA
LOAD DATA INFILE '/path/to/data.csv' INTO TABLE students
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';13.4 二进制日志备份
sql
-- 查看二进制日志
SHOW BINARY LOGS;
SHOW BINARY LOG STATUS; -- MySQL 8.0 推荐写法（旧写法 SHOW MASTER STATUS 已弃用）启用二进制日志（my.cnf 配置）：
ini
[mysqld]
log-bin=mysql-bin
binlog_format=ROW使用 mysqlbinlog 工具：
bash
# 查看二进制日志内容
mysqlbinlog mysql-bin.000001
# 使用二进制日志恢复
mysqlbinlog --start-datetime="2024-01-01 00:00:00" \
--stop-datetime="2024-01-01 12:00:00" \
mysql-bin.000001 | mysql -u root -p
# 备份二进制日志
mysqlbinlog --read-from-remote-server --host=remote_host --raw mysql-bin.00000113.5 自动化备份脚本
bash
#!/bin/bash
# MySQL自动备份脚本
# 配置
MYSQL_USER="root"
MYSQL_PASSWORD="your_password"
MYSQL_HOST="localhost"
DATABASE="school"
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="${BACKUP_DIR}/${DATABASE}_${DATE}.sql"
# 创建备份目录
mkdir -p ${BACKUP_DIR}
# 执行备份
mysqldump -u${MYSQL_USER} -p${MYSQL_PASSWORD} -h${MYSQL_HOST} \
${DATABASE} > ${BACKUP_FILE}
# 压缩备份
gzip ${BACKUP_FILE}
# 删除30天前的备份
find ${BACKUP_DIR} -name "*.sql.gz" -mtime +30 -delete
echo "备份完成：${BACKUP_FILE}.gz"设置定时任务：
bash
# 编辑crontab
crontab -e
# 添加定时任务（每天凌晨2点备份）
0 2 * * * /path/to/backup_script.sh13.6 主从复制
主服务器配置（my.cnf）：
ini
[mysqld]
server-id=1
log-bin=mysql-bin
binlog_format=ROW从服务器配置（my.cnf）：
ini
[mysqld]
server-id=2
relay-log=mysql-relay-binSQL 操作：
sql
-- 主服务器创建复制用户
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
-- 查看主服务器状态，记录输出中的 File 和 Position 值
SHOW BINARY LOG STATUS; -- MySQL 8.0 推荐写法
SHOW MASTER STATUS; -- 旧写法（8.0.22 起弃用，8.4 已移除）
-- 从服务器配置主从关系
CHANGE REPLICATION SOURCE TO -- MySQL 8.0.22+ 推荐写法
SOURCE_HOST='master_host',
SOURCE_USER='repl',
SOURCE_PASSWORD='password',
SOURCE_LOG_FILE='mysql-bin.000001',
SOURCE_LOG_POS=0;
-- 旧写法（已弃用，仅作了解）
-- CHANGE MASTER TO
-- MASTER_HOST='master_host',
-- MASTER_USER='repl',
-- MASTER_PASSWORD='password',
-- MASTER_LOG_FILE='mysql-bin.000001',
-- MASTER_LOG_POS=0;
-- 启动复制
START REPLICA; -- MySQL 8.0.22+ 推荐写法
START SLAVE; -- 旧写法（已弃用）
-- 查看复制状态
SHOW REPLICA STATUS\G -- MySQL 8.0.22+ 推荐写法
SHOW SLAVE STATUS\G -- 旧写法（已弃用）🧑💻 个人最新版（9.x / 26.7）：旧写法（
CHANGE MASTER TO/SHOW MASTER STATUS/START SLAVE/SHOW SLAVE STATUS）已完全移除，必须用 REPLICA / SOURCE 新命令，否则报语法错误。 🏭 企业生产版本差异：
- 8.4 LTS / 9.7 LTS：与最新版一致，只有新命令。
- 8.0（8.0.22 起）：新旧命令都可用，但旧命令执行会提示 deprecated 警告。
- 8.0.21 及更早 / 5.7：只有旧命令，写新命令会报错。
13.7 本章练习
- 练习1：使用mysqldump备份school数据库
- 练习2：恢复备份数据到新数据库
- 练习3：编写自动化备份脚本
- 练习4：配置定时备份任务
- 练习5：测试主从复制配置
第14章 性能优化实战
14.1 性能监控
sql
-- 查看服务器状态
SHOW STATUS;
-- 查看连接数
SHOW STATUS LIKE 'Threads%';
-- 查看查询缓存状态（⚠️ 仅5.7及更早有效）
-- 🏭 企业差异：5.7 可看 Qcache%；8.0+ 已彻底移除查询缓存，该语句返回空，配置查询缓存会直接报错
-- （查询缓存在 5.7 也已被官方标记为不再推荐，建议关闭）
SHOW STATUS LIKE 'Qcache%';
-- 查看InnoDB状态
SHOW ENGINE INNODB STATUS\G
-- 查看慢查询日志状态
SHOW STATUS LIKE 'Slow_queries';
-- 查看进程列表
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;
-- 查看锁等待
SELECT * FROM information_schema.INNODB_LOCK_WAITS;
-- 使用Performance Schema
SELECT * FROM performance_schema.events_waits_summary_global_by_event_name;14.2 慢查询优化
sql
-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';
SET GLOBAL long_query_time = 2; -- 超过2秒的查询
-- 使用mysqldumpslow分析慢查询
-- mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log
-- 使用EXPLAIN分析慢查询
EXPLAIN SELECT * FROM students WHERE class_id = 1;
-- 优化步骤
-- 1. 找出慢查询
-- 2. 分析EXPLAIN结果
-- 3. 添加合适的索引
-- 4. 重写查询语句
-- 5. 优化表结构14.3 索引优化
sql
-- 查看索引使用情况
SELECT
t.table_name,
s.index_name,
s.stat_value AS pages,
(s.stat_value * @@innodb_page_size / 1024 / 1024) AS size_mb
FROM mysql.innodb_index_stats s
JOIN information_schema.tables t ON s.table_name = t.table_name
WHERE s.stat_name = 'size'
ORDER BY s.stat_value DESC;
-- 查看未使用的索引
SELECT * FROM sys.schema_unused_indexes;
-- 查看冗余索引
SELECT * FROM sys.schema_redundant_indexes;
-- 优化索引策略
-- 1. 为WHERE、JOIN、ORDER BY字段创建索引
-- 2. 使用覆盖索引减少回表
-- 3. 避免索引失效
-- 4. 定期分析和优化索引14.4 查询优化技巧
sql
-- 1. 避免SELECT *
-- 慢
SELECT * FROM students WHERE class_id = 1;
-- 快
SELECT id, name, email FROM students WHERE class_id = 1;
-- 2. 使用EXISTS代替IN
-- 慢
SELECT * FROM students WHERE class_id IN (SELECT id FROM classes WHERE grade = '大二');
-- 快
SELECT * FROM students s WHERE EXISTS (
SELECT 1 FROM classes c WHERE c.id = s.class_id AND c.grade = '大二'
);
-- 3. 使用JOIN代替子查询
-- 慢
SELECT * FROM students WHERE class_id = (SELECT id FROM classes WHERE class_name = '计算机1班');
-- 快
SELECT s.* FROM students s
INNER JOIN classes c ON s.class_id = c.id
WHERE c.class_name = '计算机1班';
-- 4. 使用LIMIT限制结果集
SELECT * FROM students ORDER BY id LIMIT 100;
-- 5. 批量插入
INSERT INTO students (name, age, class_id) VALUES
('张三', 20, 1),
('李四', 21, 1),
('王五', 22, 2);
-- 6. 使用覆盖索引
CREATE INDEX idx_cover ON students(class_id, name, age);
SELECT class_id, name, age FROM students WHERE class_id = 1;
-- 7. 避免在WHERE中使用函数
-- 慢
SELECT * FROM students WHERE YEAR(create_time) = 2023;
-- 快
SELECT * FROM students WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01';14.5 服务器配置优化
ini
# my.cnf 配置优化
[mysqld]
# 内存配置
innodb_buffer_pool_size = 4G # 建议为物理内存的70-80%
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M
key_buffer_size = 256M
# 连接配置
max_connections = 500
max_connect_errors = 100
wait_timeout = 28800
interactive_timeout = 28800
# 查询缓存（MySQL 8.0已移除）
# query_cache_type = 1
# query_cache_size = 64M
# 排序和临时表
sort_buffer_size = 4M
join_buffer_size = 4M
tmp_table_size = 64M
max_heap_table_size = 64M
# InnoDB配置
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_io_capacity = 200014.6 表优化
sql
-- 分析表
ANALYZE TABLE students;
-- 优化表
OPTIMIZE TABLE students;
-- 修复表
REPAIR TABLE students;
-- 检查表
CHECK TABLE students;
-- 表分区
CREATE TABLE orders (
id INT,
order_date DATE,
amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(order_date)) (
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION pfuture VALUES LESS THAN MAXVALUE
);
-- 查看分区信息
SELECT * FROM information_schema.PARTITIONS
WHERE TABLE_NAME = 'orders';14.7 本章练习
- 练习1：分析并优化一个慢查询
- 练习2：使用EXPLAIN分析查询执行计划
- 练习3：优化表结构和索引
- 练习4：配置MySQL服务器参数
- 练习5：设计表分区策略
第15章 综合项目案例
15.1 项目一：学生信息管理系统
需求分析：
- 管理学生基本信息
- 管理班级信息
- 管理课程信息
- 管理选课和成绩
- 统计报表功能
数据库设计：
sql
-- 创建数据库
CREATE DATABASE student_management
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_unicode_ci;
USE student_management;
-- 班级表
CREATE TABLE classes (
id INT AUTO_INCREMENT PRIMARY KEY,
class_name VARCHAR(50) NOT NULL,
grade VARCHAR(20),
teacher_name VARCHAR(50),
student_count INT DEFAULT 0,
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_grade (grade)
) ENGINE=InnoDB COMMENT='班级表';
-- 学生表
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
student_no VARCHAR(20) NOT NULL UNIQUE,
name VARCHAR(50) NOT NULL,
gender ENUM('男', '女', '其他') DEFAULT '男',
birth_date DATE,
email VARCHAR(100),
phone VARCHAR(20),
class_id INT,
status ENUM('在读', '毕业', '休学', '退学') DEFAULT '在读',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_student_no (student_no),
INDEX idx_class_id (class_id),
FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE SET NULL
) ENGINE=InnoDB COMMENT='学生表';
-- 课程表
CREATE TABLE courses (
id INT AUTO_INCREMENT PRIMARY KEY,
course_name VARCHAR(100) NOT NULL,
credit DECIMAL(3,1),
hours INT,
description TEXT,
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uk_course_name (course_name)
) ENGINE=InnoDB COMMENT='课程表';
-- 选课表
CREATE TABLE student_courses (
id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT NOT NULL,
course_id INT NOT NULL,
score DECIMAL(5,2),
choose_time DATETIME DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uk_student_course (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='选课表';
-- 操作日志表
CREATE TABLE operation_logs (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
user_name VARCHAR(50),
operation VARCHAR(50),
table_name VARCHAR(50),
record_id INT,
old_data JSON,
new_data JSON,
ip_address VARCHAR(50),
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
INDEX idx_create_time (create_time)
) ENGINE=InnoDB COMMENT='操作日志表';功能实现：
sql
-- 1. 创建视图：学生详细信息
CREATE VIEW v_student_detail AS
SELECT
s.id,
s.student_no,
s.name AS student_name,
s.gender,
s.birth_date,
s.email,
s.phone,
c.class_name,
c.teacher_name,
s.status
FROM students s
LEFT JOIN classes c ON s.class_id = c.id;
-- 2. 创建视图：课程统计
CREATE VIEW v_course_statistics AS
SELECT
c.id,
c.course_name,
c.credit,
COUNT(sc.student_id) AS student_count,
ROUND(AVG(sc.score), 2) AS avg_score,
MAX(sc.score) AS max_score,
MIN(sc.score) AS min_score
FROM courses c
LEFT JOIN student_courses sc ON c.id = sc.course_id
GROUP BY c.id, c.course_name, c.credit;
-- 3. 创建存储过程：添加学生
DELIMITER //
CREATE PROCEDURE AddStudent(
IN p_student_no VARCHAR(20),
IN p_name VARCHAR(50),
IN p_gender VARCHAR(10),
IN p_birth_date DATE,
IN p_class_id INT,
OUT p_result INT
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SET p_result = 0;
END;
START TRANSACTION;
-- 检查学号是否已存在
IF EXISTS (SELECT 1 FROM students WHERE student_no = p_student_no) THEN
SET p_result = -1; -- 学号已存在
ROLLBACK;
ELSE
INSERT INTO students (student_no, name, gender, birth_date, class_id)
VALUES (p_student_no, p_name, p_gender, p_birth_date, p_class_id);
-- 更新班级学生数
UPDATE classes SET student_count = student_count + 1 WHERE id = p_class_id;
SET p_result = 1; -- 成功
COMMIT;
END IF;
END //
-- 4. 创建存储过程：添加成绩
CREATE PROCEDURE AddScore(
IN p_student_id INT,
IN p_course_id INT,
IN p_score DECIMAL(5,2),
OUT p_result INT
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SET p_result = 0;
END;
START TRANSACTION;
-- 检查学生和课程是否存在
IF NOT EXISTS (SELECT 1 FROM students WHERE id = p_student_id) THEN
SET p_result = -1; -- 学生不存在
ROLLBACK;
ELSEIF NOT EXISTS (SELECT 1 FROM courses WHERE id = p_course_id) THEN
SET p_result = -2; -- 课程不存在
ROLLBACK;
ELSE
INSERT INTO student_courses (student_id, course_id, score)
VALUES (p_student_id, p_course_id, p_score)
ON DUPLICATE KEY UPDATE score = p_score;
SET p_result = 1; -- 成功
COMMIT;
END IF;
END //
-- 5. 创建触发器：记录操作日志
CREATE TRIGGER after_student_insert
AFTER INSERT ON students
FOR EACH ROW
BEGIN
INSERT INTO operation_logs (operation, table_name, record_id, new_data)
VALUES ('INSERT', 'students', NEW.id, JSON_OBJECT(
'student_no', NEW.student_no,
'name', NEW.name,
'class_id', NEW.class_id
));
END //
CREATE TRIGGER after_student_update
AFTER UPDATE ON students
FOR EACH ROW
BEGIN
INSERT INTO operation_logs (operation, table_name, record_id, old_data, new_data)
VALUES ('UPDATE', 'students', NEW.id, JSON_OBJECT(
'name', OLD.name,
'class_id', OLD.class_id,
'status', OLD.status
), JSON_OBJECT(
'name', NEW.name,
'class_id', NEW.class_id,
'status', NEW.status
));
END //
DELIMITER ;15.2 项目二：电商数据库设计
需求分析：
- 用户管理
- 商品管理
- 订单管理
- 购物车管理
- 支付管理
- 评价管理
数据库设计：
sql
CREATE DATABASE ecommerce DEFAULT CHARACTER SET utf8mb4;
USE ecommerce;
-- 用户表
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
password VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE,
phone VARCHAR(20) UNIQUE,
nickname VARCHAR(50),
avatar VARCHAR(255),
gender ENUM('男', '女', '未知') DEFAULT '未知',
birthday DATE,
status TINYINT DEFAULT 1 COMMENT '0=禁用，1=正常',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_phone (phone),
INDEX idx_email (email)
) ENGINE=InnoDB COMMENT='用户表';
-- 用户地址表
CREATE TABLE user_addresses (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
receiver_name VARCHAR(50) NOT NULL,
receiver_phone VARCHAR(20) NOT NULL,
province VARCHAR(50),
city VARCHAR(50),
district VARCHAR(50),
detail_address VARCHAR(200),
is_default TINYINT DEFAULT 0,
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='用户地址表';
-- 商品分类表
CREATE TABLE categories (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
parent_id INT DEFAULT 0,
level INT DEFAULT 1,
sort_order INT DEFAULT 0,
icon VARCHAR(255),
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
INDEX idx_parent_id (parent_id)
) ENGINE=InnoDB COMMENT='商品分类表';
-- 商品表
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
category_id INT NOT NULL,
name VARCHAR(200) NOT NULL,
subtitle VARCHAR(200),
main_image VARCHAR(255),
price DECIMAL(10,2) NOT NULL,
original_price DECIMAL(10,2),
stock INT NOT NULL DEFAULT 0,
sales INT DEFAULT 0,
status TINYINT DEFAULT 1 COMMENT '0=下架，1=上架',
description TEXT,
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_category_id (category_id),
INDEX idx_status (status),
FOREIGN KEY (category_id) REFERENCES categories(id)
) ENGINE=InnoDB COMMENT='商品表';
-- 商品图片表
CREATE TABLE product_images (
id INT AUTO_INCREMENT PRIMARY KEY,
product_id INT NOT NULL,
image_url VARCHAR(255) NOT NULL,
sort_order INT DEFAULT 0,
is_main TINYINT DEFAULT 0,
FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='商品图片表';
-- 购物车表
CREATE TABLE cart_items (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL DEFAULT 1,
checked TINYINT DEFAULT 1,
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uk_user_product (user_id, product_id),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='购物车表';
-- 订单表
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
order_no VARCHAR(50) NOT NULL UNIQUE,
user_id INT NOT NULL,
total_amount DECIMAL(10,2) NOT NULL,
pay_amount DECIMAL(10,2),
shipping_fee DECIMAL(10,2) DEFAULT 0,
status TINYINT DEFAULT 0 COMMENT '0=待支付，1=已支付，2=已发货，3=已完成，4=已取消',
payment_time DATETIME,
delivery_time DATETIME,
receive_time DATETIME,
receiver_name VARCHAR(50),
receiver_phone VARCHAR(20),
receiver_address VARCHAR(500),
remark VARCHAR(500),
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_user_id (user_id),
INDEX idx_status (status),
INDEX idx_create_time (create_time),
FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB COMMENT='订单表';
-- 订单明细表
CREATE TABLE order_items (
id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
product_id INT NOT NULL,
product_name VARCHAR(200),
product_image VARCHAR(255),
price DECIMAL(10,2) NOT NULL,
quantity INT NOT NULL,
total_amount DECIMAL(10,2) NOT NULL,
FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB COMMENT='订单明细表';
-- 支付记录表
CREATE TABLE payments (
id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
payment_no VARCHAR(100),
payment_type VARCHAR(20),
amount DECIMAL(10,2) NOT NULL,
status TINYINT DEFAULT 0 COMMENT '0=待支付，1=成功，2=失败',
payment_time DATETIME,
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (order_id) REFERENCES orders(id)
) ENGINE=InnoDB COMMENT='支付记录表';
-- 商品评价表
CREATE TABLE product_reviews (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
product_id INT NOT NULL,
order_id INT NOT NULL,
rating TINYINT NOT NULL COMMENT '1-5星',
content TEXT,
images JSON,
is_anonymous TINYINT DEFAULT 0,
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (product_id) REFERENCES products(id),
FOREIGN KEY (order_id) REFERENCES orders(id)
) ENGINE=InnoDB COMMENT='商品评价表';核心业务逻辑：
sql
-- 1. 创建视图：商品详情
CREATE VIEW v_product_detail AS
SELECT
p.id,
p.name,
p.subtitle,
p.price,
p.original_price,
p.stock,
p.sales,
p.status,
c.name AS category_name,
p.description,
p.create_time
FROM products p
LEFT JOIN categories c ON p.category_id = c.id;
-- 2. 创建视图：订单统计
CREATE VIEW v_order_statistics AS
SELECT
DATE(o.create_time) AS order_date,
COUNT(DISTINCT o.id) AS order_count,
SUM(o.total_amount) AS total_sales,
COUNT(DISTINCT o.user_id) AS customer_count
FROM orders o
WHERE o.status != 4
GROUP BY DATE(o.create_time);
-- 3. 创建存储过程：创建订单
DELIMITER //
CREATE PROCEDURE CreateOrder(
IN p_user_id INT,
IN p_address_id INT,
OUT p_order_id INT,
OUT p_result INT
)
BEGIN
DECLARE v_total_amount DECIMAL(10,2) DEFAULT 0;
DECLARE v_order_no VARCHAR(50);
DECLARE v_receiver_name VARCHAR(50);
DECLARE v_receiver_phone VARCHAR(20);
DECLARE v_receiver_address VARCHAR(500);
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SET p_result = 0;
END;
START TRANSACTION;
-- 生成订单号
SET v_order_no = CONCAT(DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'), p_user_id);
-- 获取收货地址
SELECT receiver_name, receiver_phone,
CONCAT(province, city, district, detail_address)
INTO v_receiver_name, v_receiver_phone, v_receiver_address
FROM user_addresses
WHERE id = p_address_id AND user_id = p_user_id;
-- 计算订单总金额
SELECT SUM(ci.quantity * p.price)
INTO v_total_amount
FROM cart_items ci
INNER JOIN products p ON ci.product_id = p.id
WHERE ci.user_id = p_user_id AND ci.checked = 1;
-- 创建订单
INSERT INTO orders (order_no, user_id, total_amount, receiver_name, receiver_phone, receiver_address)
VALUES (v_order_no, p_user_id, v_total_amount, v_receiver_name, v_receiver_phone, v_receiver_address);
SET p_order_id = LAST_INSERT_ID();
-- 创建订单明细
INSERT INTO order_items (order_id, product_id, product_name, product_image, price, quantity, total_amount)
SELECT
p_order_id,
ci.product_id,
p.name,
p.main_image,
p.price,
ci.quantity,
ci.quantity * p.price
FROM cart_items ci
INNER JOIN products p ON ci.product_id = p.id
WHERE ci.user_id = p_user_id AND ci.checked = 1;
-- 扣减库存
UPDATE products p
INNER JOIN cart_items ci ON p.id = ci.product_id
SET p.stock = p.stock - ci.quantity,
p.sales = p.sales + ci.quantity
WHERE ci.user_id = p_user_id AND ci.checked = 1;
-- 清空购物车
DELETE FROM cart_items WHERE user_id = p_user_id AND checked = 1;
SET p_result = 1;
COMMIT;
END //
-- 4. 创建触发器：库存检查
CREATE TRIGGER before_cart_insert
BEFORE INSERT ON cart_items
FOR EACH ROW
BEGIN
DECLARE v_stock INT;
SELECT stock INTO v_stock FROM products WHERE id = NEW.product_id;
IF v_stock < NEW.quantity THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '库存不足';
END IF;
END //
DELIMITER ;15.3 项目三：博客系统
数据库设计：
sql
CREATE DATABASE blog_system DEFAULT CHARACTER SET utf8mb4;
USE blog_system;
-- 用户表
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
password VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE,
nickname VARCHAR(50),
avatar VARCHAR(255),
bio TEXT,
role ENUM('admin', 'editor', 'user') DEFAULT 'user',
status TINYINT DEFAULT 1,
last_login_time DATETIME,
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB COMMENT='用户表';
-- 文章分类表
CREATE TABLE categories (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
slug VARCHAR(50) UNIQUE,
description TEXT,
sort_order INT DEFAULT 0,
create_time DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB COMMENT='文章分类表';
-- 标签表
CREATE TABLE tags (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
slug VARCHAR(50) UNIQUE,
create_time DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB COMMENT='标签表';
-- 文章表
CREATE TABLE posts (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200) NOT NULL,
slug VARCHAR(200) UNIQUE,
content LONGTEXT,
summary TEXT,
user_id INT NOT NULL,
category_id INT,
status ENUM('draft', 'published', 'archived') DEFAULT 'draft',
view_count INT DEFAULT 0,
like_count INT DEFAULT 0,
comment_count INT DEFAULT 0,
is_top TINYINT DEFAULT 0,
published_time DATETIME,
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_user_id (user_id),
INDEX idx_category_id (category_id),
INDEX idx_status (status),
INDEX idx_published_time (published_time),
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (category_id) REFERENCES categories(id)
) ENGINE=InnoDB COMMENT='文章表';
-- 文章标签关联表
CREATE TABLE post_tags (
post_id INT NOT NULL,
tag_id INT NOT NULL,
PRIMARY KEY (post_id, tag_id),
FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='文章标签关联表';
-- 评论表
CREATE TABLE comments (
id INT AUTO_INCREMENT PRIMARY KEY,
post_id INT NOT NULL,
user_id INT NOT NULL,
parent_id INT DEFAULT 0,
content TEXT NOT NULL,
status TINYINT DEFAULT 1,
like_count INT DEFAULT 0,
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
INDEX idx_post_id (post_id),
INDEX idx_user_id (user_id),
FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB COMMENT='评论表';
-- 文章点赞表
CREATE TABLE post_likes (
id INT AUTO_INCREMENT PRIMARY KEY,
post_id INT NOT NULL,
user_id INT NOT NULL,
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uk_post_user (post_id, user_id),
FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB COMMENT='文章点赞表';第16章 学习路径与资源推荐
16.1 学习路径建议
第一阶段：基础入门（1-2周）
- 安装MySQL并熟悉基本操作
- 掌握SQL基础语法（DDL、DML、DQL）
- 理解数据类型选择
- 练习基本的CRUD操作
第二阶段：核心技能（2-3周）
- 深入学习SELECT查询（JOIN、子查询、聚合函数）
- 掌握索引原理和使用
- 理解事务和ACID特性
- 学习查询优化技巧
第三阶段：高级特性（1-2周）
- 掌握存储过程和函数
- 理解触发器和事件
- 学习视图的使用
- 掌握用户权限管理
第四阶段：实战应用（1周）
- 完成至少一个完整项目
- 学习备份与恢复
- 了解性能优化
- 学习主从复制基础
16.2 推荐学习资源
书籍推荐：
- 《MySQL必知必会》- 入门经典
- 《高性能MySQL》- 进阶必读
- 《MySQL技术内幕：InnoDB存储引擎》- 深入理解
- 《MySQL是怎样运行的》- 原理讲解
在线资源：
实践平台：
视频教程：
- B站搜索"MySQL教程"
- 慕课网MySQL课程
- 极客时间MySQL专栏
16.3 面试常见问题
- MySQL中的索引类型有哪些？
- 什么是联合索引的最左前缀原则？
- 事务的ACID特性是什么？
- MySQL的隔离级别有哪些？
- 如何优化慢查询？
- InnoDB和MyISAM的区别？
- 什么是覆盖索引？
- 如何解决死锁问题？
- 主键索引和二级索引的区别？
- MySQL中的锁有哪些类型？
16.4 实战项目建议
初级项目：
- 学生信息管理系统
- 图书管理系统
- 通讯录管理系统
中级项目：
- 博客系统
- 论坛系统
- 在线商城
高级项目：
- 电商系统（完整版）
- 社交网络系统
- 内容管理系统（CMS）
16.5 学习建议
- 多动手实践：SQL是实践性很强的技能，一定要多写多练
- 理解原理：不要只记住语法，要理解背后的原理
- 项目驱动：通过实际项目来巩固所学知识
- 持续学习：MySQL在不断发展，要保持学习的热情
- 参与社区：加入MySQL相关社区，与他人交流学习
附录
A. MySQL常用命令速查
sql
-- 连接数据库
mysql -u root -p
mysql -h host -u user -p dbname
-- 查看状态
STATUS;
SELECT VERSION();
SELECT USER();
SELECT DATABASE();
-- 查看表
SHOW TABLES;
SHOW TABLE STATUS;
DESC table_name;
SHOW CREATE TABLE table_name;
-- 查看索引
SHOW INDEX FROM table_name;
-- 查看进程
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;
-- 查看变量
SHOW VARIABLES LIKE '%timeout%';
SHOW GLOBAL STATUS LIKE 'Threads%';B. SQL语法规范
- 关键字大写：SELECT, FROM, WHERE, GROUP BY, ORDER BY
- 对象名小写：表名、列名使用小写和下划线
- 分号结尾：每条SQL语句以分号结尾
- 缩进对齐：使用空格缩进，保持代码整齐
- 注释规范：使用–或/**/添加注释
C. 数据类型选择速查
| 场景 | 推荐类型 |
|---|---|
| 主键ID | INT/BIGINT AUTO_INCREMENT |
| 用户名 | VARCHAR(50) |
| 密码 | VARCHAR(100) |
| 手机号 | VARCHAR(20) |
| 邮箱 | VARCHAR(100) |
| 金额 | DECIMAL(10,2) |
| 状态 | TINYINT/ENUM |
| 创建时间 | DATETIME DEFAULT CURRENT_TIMESTAMP |
| 内容 | TEXT/LONGTEXT |
| 头像URL | VARCHAR(255) |
D. 常用SQL模板
sql
-- 分页查询
SELECT * FROM table_name
ORDER BY id
LIMIT pageSize OFFSET (pageNum-1)*pageSize;
-- 批量插入
INSERT INTO table_name (col1, col2, col3) VALUES
('val1_1', 'val1_2', 'val1_3'),
('val2_1', 'val2_2', 'val2_3');
-- 软删除
UPDATE table_name SET is_deleted = 1 WHERE id = 1;
SELECT * FROM table_name WHERE is_deleted = 0;
-- 统计查询
SELECT
DATE(create_time) AS date,
COUNT(*) AS count
FROM table_name
GROUP BY DATE(create_time)
ORDER BY date DESC;
-- 联合查询
SELECT col1, col2 FROM table1
UNION
SELECT col1, col2 FROM table2;E. 版本兼容速查表（个人最新版 vs 企业常见版本）
图例：✅ 支持 ❌ 不支持 ⚠️ 有条件/需注意。 "8.0" 列指 8.0.x 全系列，个别功能自 8.0.xx 小版本起（已在前文正文标注）。
| 功能/语法 | 🧑💻 26.7 / 9.7（最新） | 8.4 LTS | 8.0 | 5.7（已EOL） |
|---|---|---|---|---|
| 窗口函数（ROW_NUMBER / RANK 等） | ✅ | ✅ | ✅ | ❌ |
| CTE（WITH … AS） | ✅ | ✅ | ✅ | ❌ |
| 角色（CREATE ROLE） | ✅ | ✅ | ✅ | ❌ |
| CHECK 约束真正生效 | ✅ | ✅ | ✅（8.0.16+） | ❌（仅解析不校验） |
| 表达式默认值 DEFAULT(表达式) | ✅ | ✅ | ✅（8.0.13+） | ❌ |
| AUTO_INCREMENT 重启后持久化 | ✅ | ✅ | ✅ | ❌ |
| 倒序索引 / 不可见索引 | ✅ | ✅ | ✅ | ❌ |
| 跳跃扫描 SKIP SCAN | ✅ | ✅ | ✅（8.0.13+） | ❌ |
| 索引条件下推 ICP | ✅ | ✅ | ✅ | ✅（5.6+） |
| JSON 数据类型 | ✅ | ✅ | ✅ | ✅（5.7.8+） |
| JSON_TABLE 等扩展 JSON 函数 | ✅ | ✅ | ✅ | ❌ |
| 默认字符集 utf8mb4 | ✅ | ✅ | ✅ | ❌（默认 latin1） |
| 默认排序规则 utf8mb4_0900_ai_ci | ✅ | ✅ | ✅ | ❌（只能 general/unicode） |
| 默认认证 caching_sha2_password | ✅ | ✅ | ✅ | ❌（默认 native） |
| mysql_native_password 插件 | ❌（已移除） | ⚠️ 默认禁用 | ⚠️ 可用 | ✅ 默认 |
| 查询缓存 Qcache | ❌（已移除） | ❌ | ❌ | ⚠️ 有但官方不建议 |
| 旧复制命令（MASTER/SLAVE 术语） | ❌（已移除） | ❌（已移除） | ⚠️ 弃用但可用 | ✅（唯一写法） |
| 新复制命令（REPLICA/SOURCE 术语） | ✅ | ✅ | ✅（8.0.22+） | ❌ |
| 账户锁定 / 密码历史等安全策略 | ✅ | ✅ | ✅ | ✅ |
速查口诀： "8.0 是大分水岭"——窗口函数、CTE、角色、CHECK、倒序/不可见索引都要 8.0+ 才有；5.7 存量最易踩坑的是默认字符集 latin1 与默认 native 认证；9.x / 26.7 与 8.4 的主要差别在于已移除旧认证插件与旧复制命令。
更新日志
- v1.0 (2026-09)：初版完成
- v1.1 (2026-09)：修订——补注 MySQL RR 隔离级别幻读说明、索引失效场景分类重写、更正 SQL_SAFE_UPDATES 与主从复制命令（8.0 新旧写法）、修正错误码注释、优化触发器示例与代码块格式
- v1.2 (2026-09)：以最新版（Innovation 26.7 / LTS 9.7）为基准全面更新——新增 5.6 窗口函数与 CTE、双轨制版本说明、认证插件三档差异、8.0+ 索引新特性，各章补充 🧑💻/🏭 个人与企业版本差异提示及《附录 E 版本兼容速查表》
- 后续将根据学习反馈持续更新
祝您学习愉快！ 🎓
如有问题或建议，欢迎反馈！
这是关于赞助的一些描述
- 本文链接：https://zzhitao.site/posts/2026-09-10-mysql
- 版权声明：本博客所有文章除特别声明外，均默认采用 CC BY-NC-SA 许可协议。
Previous Post Python精讲 - 大二学生系统性学习指南
Next Post 离散数学精讲