MySQL

![](/media/202509/2025-09-12_205548_1011870.9149128768110983.png)

## 概述

MySQL 是一款**开源关系型数据库管理系统（RDBMS）**，基于  SQL（结构化查询语言）实现数据存储、管理与操作，广泛应用于 Web  开发、企业系统等场景。

默认开放端口为3306。

其核心优势包括跨平台（Windows/Linux/macOS）、高性能（支持海量数据读写）、高可靠性（InnoDB  引擎支持事务与崩溃恢复）、易用性（简洁的 SQL 语法），兼容性（可轻松迁移其他 SQL 数据库）是中小型项目的首选数据库之一，也是大厂架构中 “读写分离”“分库分表” 的核心组件。

MySQL的官方网址是：https://www.mysql.com/

## **多存储引擎架构**

MySQL 采用 “插件式存储引擎” 设计，允许为不同表选择不同引擎（按需匹配业务场景），**InnoDB 是 MySQL 5.5+ 后的默认存储引擎**，也是唯一支持事务的主流引擎。

| 存储引擎    | 核心特性                               | 适用场景                           |
| ----------- | -------------------------------------- | ---------------------------------- |
| **InnoDB**  | 支持 ACID 事务、行级锁、外键、崩溃恢复 | 电商订单、金融转账（需数据一致性） |
| **MyISAM**  | 不支持事务 / 外键、表级锁、查询速度快  | 日志表、只读数据（如商品分类）     |
| **Memory**  | 数据存于内存、速度极快、重启丢失       | 临时缓存（如会话数据）             |
| **Archive** | 高压缩比、仅支持 INSERT/SELECT         | 归档数据（如历史日志）             |

## 默认数据库

默认数据库是 MySQL 安装后自动创建的 “系统库”，用于存储元数据（如库表结构、用户权限）、性能监控数据等，支撑上述特性运行。核心默认库共 4 个（information_schema、mysql、performance_schema、sys），**仅 test 库存在版本差异**。

| 默认库名称             | 核心作用                                                     | 关键内容 / 使用场景                                          |
| ---------------------- | ------------------------------------------------------------ | ------------------------------------------------------------ |
| **information_schema** | 「元数据信息库」—— 存储所有数据库的结构信息（只读，不能修改） | - 包含 TABLES 表：记录所有库的表名、引擎、行数； - 包含 COLUMNS 表：记录所有表的字段名、类型、注释； - 常用查询：SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA='mydb';（查 mydb 库的所有表） |
| **mysql**              | 「系统核心库」—— 存储 MySQL 服务器的配置信息（用户、权限、参数等） | - 包含 user 表：存储用户账号、密码（加密存储）、主机权限； - 包含 db 表：存储用户对某个库的权限； - 注意：**禁止手动删除此库**，否则 MySQL 无法启动； - 常用操作：SELECT user, host FROM mysql.user;（查看所有用户） |
| **performance_schema** | 「性能监控库」—— 实时收集 MySQL 运行时的性能数据（如锁等待、SQL 执行耗时） | - 包含 events_statements_summary_by_sql_text 表：统计每条 SQL 的执行次数、耗时； - 包含 innodb_lock_waits 表：监控 InnoDB 锁等待（排查死锁）； - 常用查询：SELECT sql_text, exec_count FROM performance_schema.events_statements_summary_by_sql_text WHERE exec_count > 100;（查执行超 100 次的 SQL） |
| **sys**                | 「简化性能库」—— 基于 performance_schema 封装的易用视图（MySQL 5.7+ 新增） | - 提供大量 “即查即用” 的视图，如 sys.schema_unused_indexes（查未使用的索引）； - 避免直接操作复杂的 performance_schema 表，降低性能监控门槛； - 常用查询：SELECT * FROM sys.schema_unused_indexes;（清理无用索引，提升写入性能） |

- **MySQL 5.7 及更早版本**：默认创建 test 库，用于临时测试（如快速执行 CREATE TABLE、INSERT 等操作）。
  注意：test 库默认权限宽松，**匿名用户（无需账号密码）可直接操作**，生产环境需删除（DROP DATABASE IF EXISTS test;）。
- **MySQL 8.0+ 版本**：不再默认创建 test 库，需手动创建（CREATE DATABASE test;），安全性更优。

## 基础概念

| 概念               | 定义与作用                                                   |
| ------------------ | ------------------------------------------------------------ |
| 数据库（DB）       | 存储数据的 “容器”，一个数据库可包含多张表（如 “学校数据库” 包含学生表、课程表） |
| 表（Table）        | 数据库的基本存储单元，按 “行（记录）” 和 “列（字段）” 组织数据（如 “学生表” 每行是 1 个学生的信息） |
| 字段（Column）     | 表的 “列”，定义数据类型（如INT存学号、VARCHAR存姓名）和约束（如非空、唯一） |
| 记录（Row）        | 表的 “行”，对应一条完整数据（如 “张三，男，2005-01-15” 是一条学生记录） |
| 主键（PK）         | 表的唯一标识字段（如学生 ID），确保每条记录不重复，通常用INT AUTO_INCREMENT（自增） |
| 外键（FK）         | 关联两张表的字段（如选课表的 “学生 ID” 关联学生表的 “学生 ID”），确保数据完整性 |
| 约束（Constraint） | 限制字段值的规则（如NOT NULL（非空）、UNIQUE（唯一）、CHECK（值范围）） |

## 安装数据库

使用Docker安装MySQL数据库。

1、从Docker Hub 拉去官方MySQL镜像：

```bash
# 拉取最新版本
docker pull mysql:latest

# 或者指定具体版本，例如8.0
docker pull mysql:8.0
```

```bash
C:\Users\root>docker pull mysql
Using default tag: latest
latest: Pulling from library/mysql
500d7b2546c4: Pull complete
01859c60b4e2: Pull complete
87565b56b57e: Pull complete
9b2f3769f0be: Pull complete
7d70b564625b: Pull complete
1d289f7d1ed9: Pull complete
d210a5b69bfe: Pull complete
95f5cac1a9e9: Pull complete
98045e6cd572: Pull complete
1421f5b704d5: Pull complete
Digest: sha256:94254b456a6db9b56c83525a86bff4c7f1e52335f934cbed686fe1ce763116a0
Status: Downloaded newer image for mysql:latest
docker.io/library/mysql:latest
```

2、创建并启动MySQL容器：

```cmd
docker run -d 
  --name mysql
  -p 13306:3306 
  -e MYSQL_ROOT_PASSWORD=your_root_password 
  -e MYSQL_DATABASE=initial_db 
  -e MYSQL_USER=custom_user 
  -e MYSQL_PASSWORD=custom_password 
  -v mysql-data:/var/lib/mysql 
  mysql:latest
```

参数说明：

- -d：后台运行容器
- --name：指定容器名称
- -p：端口映射，将主机13306端口映射到容器3306端口
- -e：设置环境变量

如果只运行run 则会进入提示，如：
```bash
C:\Users\root>docker run -p 43306:3306 mysql
2025-09-11 02:28:51+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 9.4.0-1.el9 started.
2025-09-11 02:28:52+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2025-09-11 02:28:52+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 9.4.0-1.el9 started.
2025-09-11 02:28:52+00:00 [ERROR] [Entrypoint]: Database is uninitialized and password option is not specified
    You need to specify one of the following as an environment variable:
    - MYSQL_ROOT_PASSWORD
    - MYSQL_ALLOW_EMPTY_PASSWORD
    - MYSQL_RANDOM_ROOT_PASSWORD
```

由于本次是做实验，所以只需要运行：
```bash
docker run -d -p 13306:3306 --name mysql -e MYSQL_ROOT_PASSWORD=123456 mysql
```

```bash
C:\Users\root>docker run -d -p 13306:3306 --name mysql -e MYSQL_ROOT_PASSWORD=123456 mysql
9cfd9ba4dbdd3ec0a3f4cc17bb210cce8153fdf3f62fea4538c0e3f00a433540
```

3、检查容器运行状态：

```bash
# 查看正在运行的容器
docker ps

# 查看所有容器（包括已停止的）
docker ps -a
```

```bash
C:\Users\root>docker ps
CONTAINER ID   IMAGE     COMMAND                   CREATED              STATUS              PORTS                                           NAMES
9cfd9ba4dbdd   mysql     "docker-entrypoint.s…"   About a minute ago   Up About a minute   0.0.0.0:13306->3306/tcp, [::]:13306->3306/tcp   mysql
```

4、连接MySQL容器

**方法一：通过容器内部连接**

```bah
# 进入容器内部
docker exec -it mysql-container bash

# 在容器内连接MySQL
mysql -u root -p
# 输入之前设置的root密码
```

**方法二：通过主机客户端连接**

```bash
mysql -h localhost -P 3306 -u root -p
# 输入之前设置的root密码
```

本次使用【Navicat】数据库连接工具，官网免费版下载地址：https://www.navicat.com.cn/download/navicat-premium-lite

![image-20250911105107061](https://oss.maolin101.com/image-20250911105107061.png-101.webp)

## 数据库基本操作

先从 “数据库” 本身的创建、查看、使用、删除开始，所有表操作都需基于某个数据库。

**1. 创建数据库**

语法：CREATE DATABASE [IF NOT EXISTS] 数据库名 [CHARSET 字符集] [COLLATE 排序规则];

- IF NOT EXISTS：避免数据库已存在时报错（推荐加）；
- CHARSET utf8mb4：指定字符集（utf8mb4支持所有 Unicode 字符，包括表情，替代旧版utf8）；
- COLLATE utf8mb4_general_ci：排序规则（ci表示不区分大小写）。

**示例**：创建 “学校管理数据库”school_db

```sql
CREATE DATABASE IF NOT EXISTS school_db  CHARSET utf8mb4;
```

```mysql
mysql> CREATE DATABASE IF NOT EXISTS school_db  CHARSET utf8mb4;
Query OK, 1 row affected (0.01 sec)

mysql> 
```

**2. 查看数据库**

语法：SHOW DATABASES;（查看所有数据库）或者 SHOW CREATE DATABASE 数据库名;（查看数据库详情）

mysql> show create database school_db;
+-----------+-------------------------------------------------------------------------------------------------------------------------------------+
| Database  | Create Database                                                                                                                     |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------+
| school_db | CREATE DATABASE school_db /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)

mysql> 
```

**3. 使用数据库**

操作表前必须指定 “当前数据库”，语法：USE 数据库名;

```mysql
mysql> use school_db;
Database changed
mysql> 
```

**4. 删除数据库**

语法：DROP DATABASE [IF EXISTS] 数据库名;

```bash
-- 仅在数据库存在时删除（避免报错）
DROP DATABASE IF EXISTS school_db;
```

```mysql
mysql> DROP DATABASE IF EXISTS school_db;
Query OK, 0 rows affected (0.01 sec)

mysql> 
```

## 表操作

表是数据的 “载体”，需先设计表结构（字段名、类型、约束），再创建表。

**1. 表类型**

设计表时需为每个字段指定数据类型，确保存储效率与数据合法性：

- **数值型**：INT（整数，如学号）、DECIMAL(总位数, 小数位)（小数，如成绩DECIMAL(5,2)表示最大 999.99）、TINYINT（小整数，如学分 1-5）；
- **字符串型**：VARCHAR(长度)（变长字符串，如姓名VARCHAR(50)）、CHAR(长度)（定长字符串，如性别CHAR(1)）、ENUM('值1','值2')（枚举，如性别ENUM('男','女','未知')）；
- **日期时间型**：DATE（日期，如生日2005-01-15）、DATETIME（日期 + 时间，如创建时间2024-05-20 14:30:00）、TIMESTAMP（自动更新时间戳）。

**2. 创建表**

语法：

```mysql
CREATE TABLE [IF NOT EXISTS] 表名 (
    字段名1 数据类型 [约束1] [约束2] COMMENT '字段说明',
    字段名2 数据类型 [约束1] [约束2] COMMENT '字段说明',
    -- 主键约束（可单独定义，也可在字段后加PRIMARY KEY）
    PRIMARY KEY (字段名),
    -- 外键约束（关联其他表）
    FOREIGN KEY (当前表字段) REFERENCES 关联表(关联字段) [ON DELETE/UPDATE 规则],
    -- 唯一约束（非主键的唯一字段）
    UNIQUE KEY (字段名)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '表说明';
```

- ENGINE=InnoDB：指定存储引擎（InnoDB 支持事务、外键、行锁，是默认推荐引擎；MyISAM 不支持外键，已淘汰）；
- 外键规则：ON DELETE CASCADE（关联表记录删除时，当前表关联记录也删除）、ON UPDATE CASCADE（关联表主键更新时，当前表外键同步更新）。

创建两张表，分别是学生表（students）-存储学生基本信息，主键为student_id（自增），课程表（courses）-存储课程信息，主键为course_id，课程名唯一（避免重复）：
```bash
CREATE TABLE IF NOT EXISTS students (
    student_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '学生ID（主键，自增，唯一标识学生）',
    student_name VARCHAR(50) NOT NULL COMMENT '学生姓名（非空，不可缺）',
    gender ENUM('男', '女', '未知') DEFAULT '未知' COMMENT '性别（仅允许3个值，默认“未知”）',
    birthday DATE COMMENT '出生日期（如2005-01-15）',
    class VARCHAR(20) COMMENT '班级（如高一(1)班）',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间（自动填充当前时间）'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '学生表（存储学生基本信息）';
```

```bash
CREATE TABLE IF NOT EXISTS courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '课程ID（主键，自增）',
    course_name VARCHAR(100) NOT NULL UNIQUE COMMENT '课程名称（非空且唯一，避免重复课程）',
    credit TINYINT NOT NULL CHECK (credit > 0) COMMENT '学分（非空，且必须大于0，如1-5分）',
    teacher_name VARCHAR(50) COMMENT '授课教师姓名',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '课程表（存储课程信息）';
```

```mysql
mysql> CREATE TABLE IF NOT EXISTS students (
    student_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '学生ID（主键，自增，唯一标识学生）',
    student_name VARCHAR(50) NOT NULL COMMENT '学生姓名（非空，不可缺）',
    gender ENUM('男', '女', '未知') DEFAULT '未知' COMMENT '性别（仅允许3个值，默认“未知”）',
    birthday DATE COMMENT '出生日期（如2005-01-15）',
    class VARCHAR(20) COMMENT '班级（如高一(1)班）',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间（自动填充当前时间）'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '学生表（存储学生基本信息）';
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE IF NOT EXISTS courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '课程ID（主键，自增）',
    course_name VARCHAR(100) NOT NULL UNIQUE COMMENT '课程名称（非空且唯一，避免重复课程）',
    credit TINYINT NOT NULL CHECK (credit > 0) COMMENT '学分（非空，且必须大于0，如1-5分）',
    teacher_name VARCHAR(50) COMMENT '授课教师姓名',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '课程表（存储课程信息）';
Query OK, 0 rows affected (0.01 sec)

mysql> 
```

再创建一个学生选课表（student_courses），学生与课程是 “多对多” 关系（1 个学生可选多门课，1 门课可被多学生选），需中间表关联，主键为**联合主键**（student_id + course_id）：
```mysql
CREATE TABLE IF NOT EXISTS student_courses (
    student_id INT NOT NULL COMMENT '学生ID（关联学生表的student_id）',
    course_id INT NOT NULL COMMENT '课程ID（关联课程表的course_id）',
    score DECIMAL(5, 2) COMMENT '考试成绩（如89.50，允许NULL表示未考试）',
    select_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '选课时间',
    -- 联合主键：确保1个学生不能重复选同一门课
    PRIMARY KEY (student_id, course_id),
    -- 外键约束：关联学生表，级联删除/更新
    FOREIGN KEY (student_id) 
        REFERENCES students(student_id) 
        ON DELETE CASCADE  -- 学生表删除某学生，选课表关联记录也删除
        ON UPDATE CASCADE, -- 学生表student_id更新，选课表同步更新
    -- 外键约束：关联课程表
    FOREIGN KEY (course_id) 
        REFERENCES courses(course_id) 
        ON DELETE CASCADE 
        ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '学生选课表（中间表，关联学生与课程）';
```

```mysql
mysql> CREATE TABLE IF NOT EXISTS student_courses (
    student_id INT NOT NULL COMMENT '学生ID（关联学生表的student_id）',
    course_id INT NOT NULL COMMENT '课程ID（关联课程表的course_id）',
    score DECIMAL(5, 2) COMMENT '考试成绩（如89.50，允许NULL表示未考试）',
    select_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '选课时间',
    -- 联合主键：确保1个学生不能重复选同一门课
    PRIMARY KEY (student_id, course_id),
    -- 外键约束：关联学生表，级联删除/更新
    FOREIGN KEY (student_id) 
        REFERENCES students(student_id) 
        ON DELETE CASCADE  -- 学生表删除某学生，选课表关联记录也删除
        ON UPDATE CASCADE, -- 学生表student_id更新，选课表同步更新
    -- 外键约束：关联课程表
    FOREIGN KEY (course_id) 
        REFERENCES courses(course_id) 
        ON DELETE CASCADE 
        ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '学生选课表（中间表，关联学生与课程）';
Query OK, 0 rows affected (0.02 sec)
```

**3. 查看表**

查看数据库内所有表：SHOW TABLES;

查看表结构（字段、类型、约束）：DESC 表名; 或 SHOW COLUMNS FROM 表名;

查看表创建语句：SHOW CREATE TABLE 表名;

mysql> show create table courses;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| courses | CREATE TABLE courses (
  course_id int NOT NULL AUTO_INCREMENT COMMENT '课程ID（主键，自增）',
  course_name varchar(100) NOT NULL COMMENT '课程名称（非空且唯一，避免重复课程）',
  credit tinyint NOT NULL COMMENT '学分（非空，且必须大于0，如1-5分）',
  teacher_name varchar(50) DEFAULT NULL COMMENT '授课教师姓名',
  create_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
  PRIMARY KEY (course_id),
  UNIQUE KEY course_name (course_name),
  CONSTRAINT courses_chk_1 CHECK ((credit > 0))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='课程表（存储课程信息）' |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.07 sec)

mysql> 
```

**4. 修改表结构**

表创建后需调整结构（如加字段、改类型），用ALTER TABLE，常见操作如下：

| 需求                | 语法示例                                                     |
| ------------------- | ------------------------------------------------------------ |
| 添加字段            | ALTER TABLE students ADD COLUMN email VARCHAR(100) UNIQUE COMMENT '学生邮箱'; |
| 修改字段类型 / 约束 | ALTER TABLE courses MODIFY COLUMN credit INT NOT NULL CHECK (credit BETWEEN 1 AND 10);（学分改为 1-10） |
| 修改字段名          | ALTER TABLE students CHANGE COLUMN class class_name VARCHAR(20) COMMENT '班级名称'; |
| 删除字段            | ALTER TABLE students DROP COLUMN email;                      |
| 修改表名            | ALTER TABLE student_courses RENAME TO stu_courses;           |

例如：给学生表加 “联系电话” 字段。

```mysql
ALTER TABLE students 
ADD COLUMN phone CHAR(11) UNIQUE COMMENT '学生手机号（11位，唯一）' 
AFTER birthday; 
# AFTER 指定字段插入位置（默认在最后）
```

```mysql
mysql> ALTER TABLE students 
ADD COLUMN phone CHAR(11) UNIQUE COMMENT '学生手机号（11位，唯一）' 
AFTER birthday; 
Query OK, 0 rows affected (0.03 sec)
mysql> 
```

**5. 删除表**

语法：DROP TABLE [IF EXISTS] 表名1, 表名2...;

```mysql
DROP TABLE IF EXISTS student_courses;
```

## 数据操作（CRUD）

CRUD 是数据库核心操作，即**插入（Create）、查询（Read）、更新（Update）、删除（Delete）**，基于上述 3 张表实操。

**1. 插入数据**

语法：

- 插入单条 / 多条：INSERT INTO 表名 (字段1, 字段2...) VALUES (值1, 值2...), (值1, 值2...);
- 插入所有字段（不推荐，字段顺序变化会报错）：INSERT INTO 表名 VALUES (值1, 值2...);

值得注意的是：

- 字符串值需用单引号（'张三'），日期值格式为'YYYY-MM-DD'（如'2005-01-15'）；
- 字段若有DEFAULT或AUTO_INCREMENT，可省略不写（自动填充）。

插入学生数据：
```mysql
INSERT INTO students (student_name, gender, birthday, class, phone)
VALUES 
('张三', '男', '2005-01-15', '高一(1)班', '13800138001'),
('李四', '女', '2005-03-20', '高一(2)班', '13900139001'),
('王五', '男', '2005-02-10', '高一(1)班', '13700137001');
```

课程数据：

```mysql
INSERT INTO courses (course_name, credit, teacher_name)
VALUES 
('数学', 5, '李老师'),
('英语', 4, '王老师'),
('数据库原理', 3, '张老师');
```

选课数据：

```mysql
INSERT INTO student_courses (student_id, course_id, score)
VALUES 
(1, 1, 92.50), 
(1, 3, 88.00),
(2, 2, 95.00),
(3, 1, 79.50);
### 依次为：
张三选数学，成绩92.5
张三选数据库原理，成绩88
李四选英语，成绩95
王五选数学，成绩79.5
```

**2. 查询数据**

查询是最常用操作，支持基础查询、条件筛选、排序、分页、关联查询、子查询等，语法核心：

SELECT 字段1, 字段2... FROM 表名 [WHERE 条件] [GROUP BY 字段] [HAVING 条件] [ORDER BY 字段] [LIMIT 数量];

**2.1 基础查询**

- 查询所有字段：SELECT * FROM 表名;（*表示所有字段，开发中不推荐，效率低）；
- 查询指定字段：SELECT 字段1, 字段2 FROM 表名;；
- 去重查询：SELECT DISTINCT 字段 FROM 表名;（去除重复值）；
- 别名查询：SELECT student_name AS 姓名, class AS 班级 FROM students;（AS可省略，用空格分隔）。

例如：查询高一 (1) 班的学生姓名、性别、手机号（别名显示）。

```mysql
mysql> use school_db;
Database changed
mysql> SELECT student_name,  gender,  phone  FROM students  WHERE class = '高一(1)班';
+--------------+--------+-------------+
| student_name | gender | phone       |
+--------------+--------+-------------+
| 张三          | 男     | 13800138001 |
| 王五          | 男     | 13700137001 |
+--------------+--------+-------------+
2 rows in set (0.02 sec)

mysql> 
```

**2.2 条件查询**

用WHERE指定筛选条件，支持以下运算符：

- 比较：=（等于）、!=（不等于）、>（大于）、<（小于）、BETWEEN...AND（范围）；
- 逻辑：AND（且）、OR（或）、NOT（非）；
- 模糊：LIKE（匹配，%表示任意字符，_表示单个字符）；
- 空值：IS NULL（为空）、IS NOT NULL（不为空）。

**示例 1**：查询成绩在 80-90 分之间的选课记录（学生 ID、课程 ID、成绩）。

```mysql
mysql> SELECT student_id, course_id, score FROM student_courses WHERE score BETWEEN 80 AND 90;
+------------+-----------+-------+
| student_id | course_id | score |
+------------+-----------+-------+
|          1 |         3 | 88.00 |
+------------+-----------+-------+
1 row in set (0.02 sec)

mysql> 
```

**示例 2**：查询姓名含 “张” 的学生信息。

```mysql
mysql> SELECT * FROM students WHERE student_name LIKE '%张%';
+------------+--------------+--------+------------+-------------+---------+---------------------+
| student_id | student_name | gender | birthday   | phone       | class   | create_time         |
+------------+--------------+--------+------------+-------------+---------+---------------------+
|          1 | 张三          | 男     | 2005-01-15 | 13800138001 | 高一(1)班 | 2025-09-11 03:16:51 |
+------------+--------------+--------+------------+-------------+---------+---------------------+
1 row in set (0.02 sec)

mysql> 
```

**示例 3**：查询未考试（成绩为空）的选课记录。

```sql
mysql> SELECT student_id, course_id 
FROM student_courses 
WHERE score IS NULL;
Empty set

mysql> select * from student_courses;
+------------+-----------+-------+---------------------+
| student_id | course_id | score | select_time         |
+------------+-----------+-------+---------------------+
|          1 |         1 | 92.50 | 2025-09-11 03:17:02 |
|          1 |         3 | 88.00 | 2025-09-11 03:17:02 |
|          2 |         2 | 95.00 | 2025-09-11 03:17:02 |
|          3 |         1 | 79.50 | 2025-09-11 03:17:02 |
+------------+-----------+-------+---------------------+
4 rows in set (0.03 sec)

mysql> 
```

**2.3 关联查询**

当需要从多张关联表中获取数据时，用JOIN（核心场景：多表联动），常见类型：

- INNER JOIN：只返回两张表中**匹配的记录**（无匹配则不显示）；
- LEFT JOIN：返回左表所有记录，右表匹配的记录显示，无匹配则右表字段为NULL；
- RIGHT JOIN：与LEFT JOIN相反，返回右表所有记录。

**示例**：查询学生姓名、所选课程名、成绩（3 表关联，只显示有成绩的记录）。

```mysql
mysql> SELECT s.student_name 学生姓名, c.course_name 课程名, sc.score 成绩 FROM students s INNER JOIN student_courses sc ON s.student_id = sc.student_id INNER JOIN courses c ON sc.course_id = c.course_id;
+-------+---------+-------+
| 学生姓名 | 课程名    | 成绩   |
+-------+---------+-------+
| 张三   | 数学     | 92.50 |
| 张三   | 数据库原理 | 88.00 |
| 李四   | 英语     | 95.00 |
| 王五   | 数学     | 79.50 |
+-------+---------+-------+
4 rows in set (0.04 sec)

mysql> 
```

```mysql
SELECT 
    s.student_name 学生姓名, 
    c.course_name 课程名, 
    sc.score 成绩 
FROM students s  -- s是students的别名（简化写法）
INNER JOIN student_courses sc 
    ON s.student_id = sc.student_id  -- 关联学生表与选课表
INNER JOIN courses c 
    ON sc.course_id = c.course_id;  -- 关联选课表与课程表
```

**2.4 子查询**

子查询是 “查询中的查询”，即把一个查询结果作为另一个查询的条件或数据源，分为**非相关子查询**（子查询可独立执行）和**相关子查询**（子查询依赖主查询的字段）。

**示例 1**：非相关子查询（查询选了 “数据库原理” 的学生姓名）。

先查 “数据库原理” 的course_id，再查选了该course_id的学生 ID，最后查学生姓名。

```mysql
mysql> SELECT student_name 选数据库原理的学生
FROM students
WHERE student_id IN (
    SELECT student_id 
    FROM student_courses 
    WHERE course_id = (
        SELECT course_id 
        FROM courses 
        WHERE course_name = '数据库原理'
    )
);
+--------------+
| 选数据库原理的学生 |
+--------------+
| 张三          |
+--------------+
1 row in set (0.02 sec)

mysql> 
```

```mysql
SELECT student_name 选数据库原理的学生
FROM students
WHERE student_id IN (
    -- 子查询1：查选了“数据库原理”的学生ID
    SELECT student_id 
    FROM student_courses 
    WHERE course_id = (
        -- 子查询2：查“数据库原理”的course_id（结果唯一，用=）
        SELECT course_id 
        FROM courses 
        WHERE course_name = '数据库原理'
    )
);

```

**示例 2**：相关子查询（查询每门课程的最高分及对应学生姓名）。

思路：主查询遍历每门课程，子查询针对当前课程查最高分，再匹配该课程下成绩等于最高分的学生。

```mysql
mysql> SELECT 
    c.course_name 课程名,
    s.student_name 学生姓名,
    sc.score 最高分
FROM courses c
INNER JOIN student_courses sc 
    ON c.course_id = sc.course_id
INNER JOIN students s 
    ON sc.student_id = s.student_id
WHERE sc.score = (
    SELECT MAX(score) 
    FROM student_courses 
    WHERE course_id = c.course_id
)
ORDER BY c.course_name;
+---------+-------+-------+
| 课程名    | 学生姓名 | 最高分  |
+---------+-------+-------+
| 数学     | 张三   | 92.50 |
| 数据库原理 | 张三   | 88.00 |
| 英语     | 李四   | 95.00 |
+---------+-------+-------+
3 rows in set (0.04 sec)

mysql> 
```

```mysql
SELECT 
    c.course_name 课程名,
    s.student_name 学生姓名,
    sc.score 最高分
FROM courses c
INNER JOIN student_courses sc 
    ON c.course_id = sc.course_id
INNER JOIN students s 
    ON sc.student_id = s.student_id
WHERE sc.score = (
    -- 相关子查询：针对主查询的当前课程（c.course_id）查最高分
    SELECT MAX(score) 
    FROM student_courses 
    WHERE course_id = c.course_id
)
ORDER BY c.course_name;

```

**2.5 排序与分页查询**

- ORDER BY 字段 [ASC/DESC]：排序（ASC升序，默认；DESC降序）；
- LIMIT 起始位置, 条数：分页（起始位置从 0 开始，如LIMIT 0,2表示第 1-2 条）。

**示例**：查询高一学生，按生日降序排序，分页显示第 1-2 条。

```mysql
mysql> SELECT student_name 姓名, birthday 生日, class 班级
FROM students
WHERE class LIKE '高一%'
ORDER BY birthday DESC
LIMIT 0, 2;
+-----+------------+---------+
| 姓名 | 生日        | 班级     |
+-----+------------+---------+
| 李四 | 2005-03-20 | 高一(2)班 |
| 王五 | 2005-02-10 | 高一(1)班 |
+-----+------------+---------+
2 rows in set (0.02 sec)

mysql> 
```

**3. 更新数据**

语法：UPDATE 表名 SET 字段1=值1, 字段2=值2... [WHERE 条件];

**示例**：将张三的手机号改为13800138002。

```mysql
mysql> UPDATE students
SET phone = '13800138002'
WHERE student_name = '张三';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from students;
+------------+--------------+--------+------------+-------------+---------+---------------------+
| student_id | student_name | gender | birthday   | phone       | class   | create_time         |
+------------+--------------+--------+------------+-------------+---------+---------------------+
|          1 | 张三          | 男     | 2005-01-15 | 13800138002 | 高一(1)班 | 2025-09-11 03:16:51 |
|          2 | 李四          | 女     | 2005-03-20 | 13900139001 | 高一(2)班 | 2025-09-11 03:16:51 |
|          3 | 王五          | 男     | 2005-02-10 | 13700137001 | 高一(1)班 | 2025-09-11 03:16:51 |
+------------+--------------+--------+------------+-------------+---------+---------------------+
3 rows in set (0.03 sec)

mysql> 
```

**4. 删除数据**

语法：DELETE FROM 表名 [WHERE 条件];

**示例**：删除王五的 “数学” 选课记录。

```mysql
mysql> SELECT * FROM student_courses 
WHERE student_id = (SELECT student_id FROM students WHERE student_name = '王五')
  AND course_id = (SELECT course_id FROM courses WHERE course_name = '数学');
+------------+-----------+-------+---------------------+
| student_id | course_id | score | select_time         |
+------------+-----------+-------+---------------------+
|          3 |         1 | 79.50 | 2025-09-11 03:17:02 |
+------------+-----------+-------+---------------------+
1 row in set (0.04 sec)

mysql> DELETE FROM student_courses
WHERE student_id = (SELECT student_id FROM students WHERE student_name = '王五')
  AND course_id = (SELECT course_id FROM courses WHERE course_name = '数学');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student_courses;
+------------+-----------+-------+---------------------+
| student_id | course_id | score | select_time         |
+------------+-----------+-------+---------------------+
|          1 |         1 | 92.50 | 2025-09-11 03:17:02 |
|          1 |         3 | 88.00 | 2025-09-11 03:17:02 |
|          2 |         2 | 95.00 | 2025-09-11 03:17:02 |
+------------+-----------+-------+---------------------+
3 rows in set (0.05 sec)

mysql> 
```

## 事务

在数据库领域，**事务（Transaction）** 是指一组数据库操作的**不可分割的工作单元**—— 这组操作要么**全部成功执行**，要么**全部失败回滚**，不会出现 “部分执行” 的中间状态。其核心目的是保证数据在多步操作中的**一致性和完整性**。

最经典的场景是**银行转账**：

假设用户 A 向用户 B 转账 100 元，需要执行两个操作：

1. 从 A 的账户余额中减去 100 元；
2. 向 B 的账户余额中增加 100 元。

这两个操作必须作为一个 “事务” 执行：

- 若两步都成功，事务 “提交”，最终 A 少 100 元、B 多 100 元（数据一致）；
- 若第一步成功但第二步失败（如网络中断），事务必须 “回滚”，A 的余额恢复到原始状态（避免 A 的钱平白消失，数据仍一致）。

**四大核心特性（ACID）**

事务必须满足 **ACID 四大特性**，这是判断事务是否可靠的标准：

| 特性       | 英文        | 含义说明                                                     | 例子（转账场景）                                             |
| ---------- | ----------- | ------------------------------------------------------------ | ------------------------------------------------------------ |
| **原子性** | Atomicity   | 事务中的所有操作是一个 “原子”（不可分割），要么全做，要么全不做。 | 转账时，“扣 A 的钱” 和 “加 B 的钱” 必须同时成功或同时失败，不能只执行其中一步。 |
| **一致性** | Consistency | 事务执行前后，数据从一个 “合法状态” 变为另一个 “合法状态”（满足业务规则）。 | 转账前 A 有 500 元、B 有 300 元（总 800 元）；转账后 A 有 400 元、B 有 400 元（总仍 800 元），符合 “总金额不变” 的规则。 |
| **隔离性** | Isolation   | 多个事务同时执行时，彼此的操作互不干扰，仿佛各自独立运行。   | 当 A 向 B 转账时，若同时有 C 向 A 转账，两个事务的结果不会相互覆盖或混乱。 |
| **持久性** | Durability  | 事务一旦 “提交”，其结果会永久保存到数据库（即使断电、崩溃也不会丢失）。 | 转账成功并提交后，即使数据库服务器突然断电，重启后 A 和 B 的余额仍保持转账后的状态。 |

在 MySQL 中，只有 **InnoDB 存储引擎** 支持事务（MyISAM 等引擎不支持）。默认情况下，MySQL 是 “自动提交” 模式（每条 SQL 语句单独作为一个事务，执行后自动提交），若需手动控制事务，需用以下命令：

| 操作         | 语法                         | 说明                                                         |
| ------------ | ---------------------------- | ------------------------------------------------------------ |
| 开启事务     | BEGIN; 或 START TRANSACTION; | 之后执行的 SQL 语句将属于当前事务，不会自动提交。            |
| 提交事务     | COMMIT;                      | 确认事务中的所有操作，将结果永久保存到数据库（事务结束）。   |
| 回滚事务     | ROLLBACK;                    | 取消事务中的所有操作，数据恢复到事务开始前的状态（事务结束）。 |
| 设置保存点   | SAVEPOINT 保存点名称;        | 在事务中设置一个 “checkpoint”，可回滚到该点（而非事务开始）。 |
| 回滚到保存点 | ROLLBACK TO 保存点名称;      | 仅取消从保存点到当前的操作，保存点之前的操作仍保留在事务中。 |

**示例：用事务处理转账**。

假设数据库中有 accounts 表（存储用户账户）：

```mysql
-- 创建账户表（InnoDB引擎，支持事务）
CREATE TABLE accounts (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    balance DECIMAL(10,2) NOT NULL CHECK (balance >= 0) -- 余额不能为负（一致性约束）
) ENGINE=InnoDB;

-- 插入测试数据
INSERT INTO accounts VALUES (1, 'A', 500.00), (2, 'B', 300.00);
```

执行转账事务（A 向 B 转 100 元）：

```mysql
-- 1. 开启事务
BEGIN;

-- 2. 执行转账操作
UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- A扣100
UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- B加100

-- 3. 检查操作是否正确（可选，实际开发中可通过代码判断）
SELECT * FROM accounts; -- 此时A=400，B=400（但未提交，仅当前会话可见）

-- 4. 若没问题，提交事务（结果永久生效）
COMMIT;

-- 若中间出错（如B的账户不存在），则回滚
-- ROLLBACK;
```

## 函数

MySQL 提供了丰富的内置函数，用于在数据查询和操作中进行计算、处理字符串、格式化日期等。

**元数据函数**

**元数据函数（Metadata Functions）** 用于获取数据库、表、列、用户、版本等系统或对象的 “元信息”（描述数据的数据）。

| 类别                  | 函数 / 操作            | 作用描述                               | 示例（输出）                                                 |
| --------------------- | ---------------------- | -------------------------------------- | ------------------------------------------------------------ |
| 数据库与版本信息      | DATABASE()/SCHEMA()    | 返回当前连接的数据库名                 | SELECT DATABASE(); → test_db                                 |
|                       | VERSION()              | 返回 MySQL 服务器版本                  | SELECT VERSION(); → 8.0.32                                   |
| 用户与权限信息        | USER()                 | 返回当前登录用户（格式：用户@主机）    | SELECT USER(); → root@localhost                              |
|                       | CURRENT_USER()         | 返回当前有效用户（用于权限验证）       | SELECT CURRENT_USER(); → admin@%                             |
| 字符集相关            | CHARSET(str)           | 返回字符串str的字符集                  | SELECT CHARSET('abc'); → utf8mb4                             |
|                       | COLLATION(str)         | 返回字符串str的排序规则                | SELECT COLLATION('abc'); → utf8mb4_general_ci                |
| 数据操作元信息        | LAST_INSERT_ID()       | 返回最后插入的自增 ID                  | 插入后执行 → 1001                                            |
|                       | ROW_COUNT()            | 返回上条 DML（增 / 删 / 改）影响的行数 | 更新后执行 → 5（表示 5 行被更新）                            |
| 表 / 列元信息（补充） | 查询INFORMATION_SCHEMA | 获取表结构、列类型等详细信息           | SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='emp';（返回表emp的列名） |

**1. 字符串函数**

字符串函数用于处理文本数据。

| 函数                     | 描述           | 示例                                                         |
| ------------------------ | -------------- | ------------------------------------------------------------ |
| CONCAT(str1, str2, ...)  | 连接字符串     | SELECT CONCAT('Hello', ' ', 'World'); → 'Hello World'        |
| SUBSTRING(str, pos, len) | 提取子字符串   | SELECT SUBSTRING('MySQL', 3, 2); → 'SQ'                      |
| LENGTH(str)              | 返回字符串长度 | SELECT LENGTH('MySQL'); → 5                                  |
| UPPER(str)               | 转换为大写     | SELECT UPPER('mysql'); → 'MYSQL'                             |
| LOWER(str)               | 转换为小写     | SELECT LOWER('MySQL'); → 'mysql'                             |
| TRIM(str)                | 去除两端空格   | SELECT TRIM('  MySQL  '); → 'MySQL'                          |
| REPLACE(str, from, to)   | 替换字符串     | SELECT REPLACE('I like apples', 'apples', 'oranges'); → 'I like oranges' |
| LEFT(str, len)           | 返回左侧字符   | SELECT LEFT('MySQL', 2); → 'My'                              |
| RIGHT(str, len)          | 返回右侧字符   | SELECT RIGHT('MySQL', 3); → 'SQL'                            |

**2. 数值函数**

数值函数用于数学计算。

| 函数                 | 描述        | 示例                                |
| -------------------- | ----------- | ----------------------------------- |
| ROUND(num, decimals) | 四舍五入    | SELECT ROUND(123.4567, 2); → 123.46 |
| CEILING(num)         | 向上取整    | SELECT CEILING(123.45); → 124       |
| FLOOR(num)           | 向下取整    | SELECT FLOOR(123.75); → 123         |
| ABS(num)             | 绝对值      | SELECT ABS(-123); → 123             |
| POW(x, y)            | 幂运算      | SELECT POW(2, 3); → 8               |
| SQRT(num)            | 平方根      | SELECT SQRT(25); → 5                |
| RAND()               | 随机数(0-1) | SELECT RAND(); → 0.123456...        |
| MOD(x, y)            | 取余数      | SELECT MOD(10, 3); → 1              |

**3. 日期和时间函数**

日期和时间函数用于处理日期和时间值。

| 函数                      | 描述         | 示例                                                         |
| ------------------------- | ------------ | ------------------------------------------------------------ |
| NOW()                     | 当前日期时间 | SELECT NOW(); → '2023-10-15 14:30:45'                        |
| CURDATE()                 | 当前日期     | SELECT CURDATE(); → '2023-10-15'                             |
| CURTIME()                 | 当前时间     | SELECT CURTIME(); → '14:30:45'                               |
| DATE(date)                | 提取日期部分 | SELECT DATE('2023-10-15 14:30:45'); → '2023-10-15'           |
| YEAR(date)                | 提取年份     | SELECT YEAR('2023-10-15'); → 2023                            |
| MONTH(date)               | 提取月份     | SELECT MONTH('2023-10-15'); → 10                             |
| DAY(date)                 | 提取日       | SELECT DAY('2023-10-15'); → 15                               |
| DATEDIFF(date1, date2)    | 日期差(天数) | SELECT DATEDIFF('2023-10-20', '2023-10-15'); → 5             |
| DATE_ADD(date, INTERVAL)  | 日期加法     | SELECT DATE_ADD('2023-10-15', INTERVAL 7 DAY); → '2023-10-22' |
| DATE_FORMAT(date, format) | 日期格式化   | SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日'); → '2023年10月15日' |

**4. 聚合函数**

聚合函数对一组值执行计算并返回单个值。

| 函数           | 描述           | 示例                                                      |
| -------------- | -------------- | --------------------------------------------------------- |
| COUNT()        | 计数           | SELECT COUNT(*) FROM users;                               |
| SUM()          | 求和           | SELECT SUM(salary) FROM employees;                        |
| AVG()          | 平均值         | SELECT AVG(price) FROM products;                          |
| MAX()          | 最大值         | SELECT MAX(age) FROM users;                               |
| MIN()          | 最小值         | SELECT MIN(price) FROM products;                          |
| GROUP_CONCAT() | 连接组内字符串 | SELECT GROUP_CONCAT(name) FROM users GROUP BY department; |

**5. 条件函数**

条件函数用于实现条件逻辑。

| 函数                          | 描述               | 示例                                                        |
| ----------------------------- | ------------------ | ----------------------------------------------------------- |
| IF(expr, true_val, false_val) | 简单条件判断       | SELECT IF(score >= 60, '及格', '不及格') FROM scores;       |
| CASE                          | 多条件判断         | 见下方示例                                                  |
| IFNULL(expr, fallback)        | 空值替换           | SELECT IFNULL(address, '未知地址') FROM users;              |
| COALESCE(val1, val2, ...)     | 返回第一个非NULL值 | SELECT COALESCE(NULL, NULL, '第三值', '第四值'); → '第三值' |

**6. 类型转换函数**

| 函数                | 描述     | 示例                                              |
| ------------------- | -------- | ------------------------------------------------- |
| CAST(expr AS type)  | 类型转换 | SELECT CAST('123' AS UNSIGNED); → 123             |
| CONVERT(expr, type) | 类型转换 | SELECT CONVERT('123.45', DECIMAL(10,2)); → 123.45 |

**7. JSON函数（5.7+）**

| 函数           | 描述         | 示例                                                         |
| -------------- | ------------ | ------------------------------------------------------------ |
| JSON_EXTRACT() | 提取JSON值   | SELECT JSON_EXTRACT('{"name": "John"}', '$.name'); → "John"  |
| JSON_OBJECT()  | 创建JSON对象 | SELECT JSON_OBJECT('name', 'John', 'age', 30); → {"name": "John", "age": 30} |
| JSON_ARRAY()   | 创建JSON数组 | SELECT JSON_ARRAY(1, 2, 3); → [1, 2, 3]                      |

## 用户与权限管理核心概念

其核心逻辑是：**通过 “用户（User@Host）” 作为权限载体，将 “系统权限” 或 “对象权限” 精准授予用户，并支持通过 “角色” 聚合权限以简化管理**。MySQL 权限体系覆盖从 “服务器全局” 到 “列级” 的细粒度控制，适用于从个人开发到企业级多用户协作的场景。

**用户标识（User@Host）**
MySQL 用户由 “用户名（User）” 和 “主机（Host）” 共同唯一标识，例如 'admin'@'localhost'（仅允许本地登录）与 'admin'@'%'（允许所有主机登录）是两个不同用户。

- Host 取值：localhost（本地）、192.168.1.%（网段）、%（所有主机，生产环境慎用）、具体 IP（如 192.168.1.100）。

**权限分类**
MySQL 权限按 “作用范围” 分为两类：

- 系统权限：作用于整个 MySQL 服务器或特定数据库，如 CREATE DATABASE（创建数据库）、SUPER（超级权限）。
- 对象权限：作用于具体数据库对象（表、视图、存储过程等），如 SELECT（查询表）、INSERT（插入数据）。

**权限存储**
权限信息存储在 MySQL 系统数据库 mysql 的表中，核心表包括：

- user：存储用户账号、密码、全局权限（如 SELECT *.*）。
- db：存储用户的数据库级权限（如 SELECT db1.*）。
- tables_priv：存储表级权限（如 SELECT db1.tbl1）。
- columns_priv：存储列级权限（如 UPDATE db1.tbl1 (name, age)）。

## 用户管理

**创建用户**

```sql
CREATE USER [IF NOT EXISTS] '用户名'@'主机'
IDENTIFIED BY '密码'  -- 设置密码
[IDENTIFIED WITH 认证插件]  -- 指定认证插件（可选，如 mysql_native_password）
[DEFAULT DATABASE 数据库名]  -- 默认数据库（可选）
[COMMENT '用户描述']  -- 备注（可选）;
```

- **认证插件**：MySQL 5.7 默认 mysql_native_password，MySQL 8.0 默认 caching_sha2_password（兼容性稍差，若需兼容旧客户端，可指定 mysql_native_password）。
- **密码强度**：生产环境需符合密码策略（如长度≥8、包含大小写 / 数字 / 特殊字符），可通过 show variables like 'validate_password%' 查看密码策略。

```sql
-- 示例1：创建本地用户（仅允许 localhost 登录），MySQL 5.7/8.0 通用
CREATE USER IF NOT EXISTS 'dev'@'localhost'
IDENTIFIED BY 'Dev@123456'  -- 符合密码策略
COMMENT '开发环境本地用户';

-- 示例2：创建远程用户（允许 192.168.1 网段登录），指定认证插件（兼容旧客户端）
CREATE USER IF NOT EXISTS 'app'@'192.168.1.%'
IDENTIFIED WITH mysql_native_password BY 'App@67890'
DEFAULT DATABASE shop_db  -- 默认数据库为 shop_db
COMMENT '应用服务器用户';

-- 示例3：创建允许所有主机登录的用户（生产环境慎用 %）
CREATE USER IF NOT EXISTS 'read_user'@'%'
IDENTIFIED BY 'Read@111222'
COMMENT '只读用户（所有主机可登录）';
```

**修改用户**

用于修改用户的密码、认证插件、锁定状态等。

```sql
ALTER USER [IF EXISTS] '用户名'@'主机'
[IDENTIFIED BY '新密码']  -- 修改密码
[IDENTIFIED WITH 新认证插件]  -- 修改认证插件
[ACCOUNT LOCK | UNLOCK]  -- 锁定/解锁用户
[DEFAULT DATABASE 新默认数据库];  -- 修改默认数据库
```

```sql
-- 示例1：修改用户密码
ALTER USER 'dev'@'localhost' IDENTIFIED BY 'NewDev@54321';

-- 示例2：锁定用户（禁止登录，用于离职员工等场景）
ALTER USER 'app'@'192.168.1.%' ACCOUNT LOCK;

-- 示例3：解锁用户并修改认证插件（从 caching_sha2_password 改为 mysql_native_password）
ALTER USER 'read_user'@'%' 
ACCOUNT UNLOCK
IDENTIFIED WITH mysql_native_password BY 'Read@111222';
```

**删除用户**

删除用户及其所有权限（谨慎操作！）。

```sql
DROP USER [IF EXISTS] '用户名'@'主机' [, '用户名2'@'主机2' ...];
```

```sql
-- 删除单个用户
DROP USER IF EXISTS 'dev'@'localhost';

-- 批量删除用户
DROP USER IF EXISTS 'app'@'192.168.1.%', 'read_user'@'%';

```

**查询用户信息**

```sql
-- 1. 查看所有用户（从 mysql.user 表）
SELECT user, host, authentication_string, account_locked 
FROM mysql.user;

-- 2. 查看用户的详细配置（如默认数据库、备注）
SELECT user, host, default_database_name, comment 
FROM mysql.user 
WHERE user = 'app' AND host = '192.168.1.%';

-- 3. 查看用户的权限（后续“权限查询”会详细讲）
SHOW GRANTS FOR 'app'@'192.168.1.%';

```

## 权限管理

**权限分类**

| 权限级别          | 作用范围             | 示例权限                       | 授权语法示例                                                 |
| ----------------- | -------------------- | ------------------------------ | ------------------------------------------------------------ |
| 全局级（Global）  | 整个 MySQL 服务器    | CREATE USER、SUPER、SELECT *.* | GRANT SELECT, INSERT ON *.* TO 'user'@'host'                 |
| 数据库级（DB）    | 单个或多个数据库     | CREATE、SELECT db1.*           | GRANT CREATE, SELECT ON shop_db.* TO 'user'@'host'           |
| 表级（Table）     | 单个或多个表         | SELECT、UPDATE db1.tbl1        | GRANT SELECT, UPDATE ON shop_db.users TO 'user'@'host'       |
| 列级（Column）    | 表中的单个或多个列   | UPDATE db1.tbl1 (name)         | GRANT UPDATE (name, age) ON shop_db.users TO 'user'@'host'   |
| 程序级（Program） | 存储过程、函数、事件 | EXECUTE 存储过程               | GRANT EXECUTE ON PROCEDURE shop_db.get_user TO 'user'@'host' |

**常用权限说明**

| 权限名称             | 作用                                  | 适用级别           |
| -------------------- | ------------------------------------- | ------------------ |
| ALL PRIVILEGES       | 所有权限（除 GRANT OPTION 外）        | 全局 / 数据库 / 表 |
| CREATE               | 创建数据库、表、视图                  | 全局 / 数据库      |
| SELECT               | 查询表或视图的数据                    | 数据库 / 表 / 列   |
| INSERT/UPDATE/DELETE | 插入 / 更新 / 删除表数据              | 数据库 / 表 / 列   |
| DROP                 | 删除数据库、表、视图                  | 全局 / 数据库 / 表 |
| GRANT OPTION         | 允许用户将自己的权限转授给其他用户    | 所有级别           |
| SUPER                | 超级权限（如修改全局参数、kill 进程） | 全局               |
| EXECUTE              | 执行存储过程或函数                    | 数据库 / 程序      |

**授予权限**

```sql
GRANT 权限1, 权限2, ... 
ON 权限范围  -- 如 *.*、db.*、db.tbl
TO '用户名'@'主机'
[WITH GRANT OPTION]  -- 可选：允许用户转授自己的权限
[WITH MAX_QUERIES_PER_HOUR 次数]  -- 可选：限制每小时查询次数（资源控制）;
```

```sql
-- 示例1：授予全局权限（仅给管理员，谨慎！）
-- 给 'admin'@'localhost' 授予所有权限，并允许转授
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

-- 示例2：授予数据库级权限（应用用户常用）
-- 给 'app'@'192.168.1.%' 授予 shop_db 数据库的 SELECT/INSERT/UPDATE/DELETE 权限
GRANT SELECT, INSERT, UPDATE, DELETE ON shop_db.* TO 'app'@'192.168.1.%';

-- 示例3：授予表级权限（只读用户常用）
-- 给 'read_user'@'%' 授予 shop_db.users 表的 SELECT 权限
GRANT SELECT ON shop_db.users TO 'read_user'@'%';

-- 示例4：授予列级权限（精细控制，如仅允许修改用户年龄）
-- 给 'dev'@'localhost' 授予 shop_db.users 表的 name 和 age 列的 UPDATE 权限
GRANT UPDATE (name, age) ON shop_db.users TO 'dev'@'localhost';

-- 示例5：授予程序级权限（执行存储过程）
-- 给 'app'@'192.168.1.%' 授予执行 shop_db.get_user 存储过程的权限
GRANT EXECUTE ON PROCEDURE shop_db.get_user TO 'app'@'192.168.1.%';

```

**WITH GRANT OPTION 风险**：若授予此权限，用户可能将权限转授给他人，生产环境仅给管理员使用。

**权限生效时机**：

- 新授予的权限对**已登录用户**需重新登录才生效；
- 若手动修改了 mysql 系统表（如 update mysql.user set ...），需执行 FLUSH PRIVILEGES 刷新权限缓存。

**回收权限（REVOKE）**

当用户不再需要某权限时，需及时回收，避免权限泄露。

```sql
REVOKE 权限1, 权限2, ... 
ON 权限范围 
FROM '用户名'@'主机';

-- 回收转授权限（仅回收 WITH GRANT OPTION，保留原权限）
REVOKE GRANT OPTION ON 权限范围 FROM '用户名'@'主机';

```

```sql
-- 示例1：回收数据库级权限（收回 app 用户的 shop_db 数据库 DELETE 权限）
REVOKE DELETE ON shop_db.* FROM 'app'@'192.168.1.%';

-- 示例2：回收表级权限（收回 read_user 的 shop_db.users 表 SELECT 权限）
REVOKE SELECT ON shop_db.users FROM 'read_user'@'%';

-- 示例3：回收全局权限（收回 admin 用户的 SUPER 权限）
REVOKE SUPER ON *.* FROM 'admin'@'localhost';

-- 示例4：回收转授权限（保留 app 用户的权限，但禁止其转授）
REVOKE GRANT OPTION ON shop_db.* FROM 'app'@'192.168.1.%';

```

## 角色管理

角色是 “权限的集合”，可将多个权限打包为一个角色，再将角色分配给用户，避免重复授权（尤其适合多用户管理场景）。

**创建角色**

```sql
-- 创建角色（角色名格式与用户类似，但 Host 可省略，默认 %）
CREATE ROLE IF NOT EXISTS 'read_only'@'%', 'app_write'@'%';

```

**给角色授予权限**

```sql
-- 给 'read_only' 角色授予 shop_db 所有表的 SELECT 权限
GRANT SELECT ON shop_db.* TO 'read_only'@'%';

-- 给 'app_write' 角色授予 shop_db 所有表的 SELECT/INSERT/UPDATE/DELETE 权限
GRANT SELECT, INSERT, UPDATE, DELETE ON shop_db.* TO 'app_write'@'%';

```

**将角色分配给用户**

```sql
-- 给 'read_only' 角色授予 shop_db 所有表的 SELECT 权限
GRANT SELECT ON shop_db.* TO 'read_only'@'%';

-- 给 'app_write' 角色授予 shop_db 所有表的 SELECT/INSERT/UPDATE/DELETE 权限
GRANT SELECT, INSERT, UPDATE, DELETE ON shop_db.* TO 'app_write'@'%';

```

**激活角色**

用户登录后，需要激活角色才能使用权限

```sql
-- 激活当前用户的所有已分配角色
SET DEFAULT ROLE ALL TO CURRENT_USER;

-- 给 'app1'@'192.168.1.10' 设置默认角色为 'app_write'
ALTER USER 'app1'@'192.168.1.10' DEFAULT ROLE 'app_write'@'%';

```

**查询角色与权限**

```sql
-- 1. 查看所有角色
SELECT role_name, is_default 
FROM mysql.default_roles;

-- 2. 查看角色的权限
SHOW GRANTS FOR 'read_only'@'%';

-- 3. 查看用户已分配的角色
SHOW GRANTS FOR 'app1'@'192.168.1.10' USING 'app_write'@'%';

```

**删除角色**

```sql
-- 删除角色（同时会收回所有用户的该角色权限）
DROP ROLE IF EXISTS 'read_only'@'%';
```

## 权限查询

MySQL 采用层级化的权限管理模型，权限覆盖从全局到列级的多个层级，同时提供了日志和审计工具追踪数据库活动。

权限查询用于确认 “谁拥有什么权限”，审计则用于记录 “谁执行了什么操作”，二者结合是 MySQL 安全管理的核心。

权限层级：从高到低分为全局权限（影响所有数据库）、数据库权限（仅影响特定数据库）、表权限（仅影响特定表）、列权限（仅影响表中特定列）、子程序权限（存储过程 / 函数）。

权限存储：权限信息存储在 mysql 系统数据库的表中（如 user、db、tables_priv、columns_priv 等）。

用户标识：MySQL 用户以 “用户名@主机” 形式唯一标识（如 app_user@'192.168.1.%' 表示允许从 192.168.1 网段登录的 app_user）。

角色支持：MySQL 8.0 及以上引入角色（Role），可批量管理权限（类似 “用户组”）

MySQL 提供 SHOW GRANTS 命令和系统表查询两种方式，可按需获取不同层级的权限信息。

**SHOW GRANTS 命令**

SHOW GRANTS 是查询权限的快捷方式，直接返回用户 / 角色的权限明细，适合快速验证。

查询用户的所有权限

```sql
-- 语法：SHOW GRANTS FOR '用户名'@'主机';
-- 示例：查询 app_user@'localhost' 的所有权限
SHOW GRANTS FOR 'app_user'@'localhost';

```

输出说明：返回类似 GRANT SELECT, INSERT ON db1.* TO app_user@localhost 的语句，表示该用户在 db1 数据库的所有表上有 SELECT 和 INSERT 权限。

查询当前登录用户的权限

```sql
-- 查看当前用户的权限
SHOW GRANTS;
-- 或等价于
SHOW GRANTS FOR CURRENT_USER;

```

查询角色的权限（MySQL 8.0+）

```sql
-- 1. 创建角色（示例）
CREATE ROLE 'read_only_role';
GRANT SELECT ON db1.* TO 'read_only_role';

-- 2. 查询角色的权限
SHOW GRANTS FOR 'read_only_role';
```

**系统表查询**

若需自定义筛选条件（如 “查询所有有 DELETE 权限的用户”），需直接查询 mysql 系统库的权限表。

| 表名               | 作用范围                 | 关键字段说明                                                 |
| ------------------ | ------------------------ | ------------------------------------------------------------ |
| mysql.user         | 全局权限                 | User（用户名）、Host（主机）、Select_priv（查询权限）等      |
| mysql.db           | 数据库级权限             | Db（数据库名）、User、Host、Insert_priv 等                   |
| mysql.tables_priv  | 表级权限                 | Db、Table_name（表名）、User、Host、Table_priv（表权限）等   |
| mysql.columns_priv | 列级权限                 | Db、Table_name、Column_name（列名）、User、Host、Column_priv 等 |
| mysql.role_edges   | 角色与用户的关联（8.0+） | FROM_USER（角色）、TO_USER（用户）                           |

查询所有用户及全局权限

```sql
-- 查询所有用户、主机及全局权限（简化版）
SELECT 
  User AS 用户名,
  Host AS 主机,
  Select_priv AS 全局查询权限,
  Insert_priv AS 全局插入权限,
  Super_priv AS 超级用户权限,
  Create_user_priv AS 创建用户权限
FROM mysql.user
ORDER BY User, Host;
```

说明：

- 字段值为 Y 表示拥有权限，N 表示无权限；
- Super_priv = Y 表示该用户是超级用户（拥有所有权限）。

查询数据库级权限（某用户对特定数据库的权限）

```sql
-- 查询 app_user@'192.168.1.%' 对 db1 数据库的权限
SELECT 
  Db AS 数据库名,
  User AS 用户名,
  Host AS 主机,
  Select_priv AS 查询权限,
  Insert_priv AS 插入权限,
  Update_priv AS 更新权限,
  Delete_priv AS 删除权限
FROM mysql.db
WHERE 
  User = 'app_user' 
  AND Host = '192.168.1.%'
  AND Db = 'db1';
```

查询表级权限（某用户对特定表的权限）

```sql
-- 查询 app_user@'localhost' 对 db1.orders 表的权限
SELECT 
  Db AS 数据库名,
  Table_name AS 表名,
  User AS 用户名,
  Host AS 主机,
  Table_priv AS 表权限  -- 如 'Select,Insert,Update'
FROM mysql.tables_priv
WHERE 
  User = 'app_user' 
  AND Host = 'localhost'
  AND Db = 'db1'
  AND Table_name = 'orders';
```

查询角色与用户的关联（MySQL 8.0+）

```sql
-- 查看哪些用户被授予了 'read_only_role' 角色
SELECT 
  FROM_USER AS 角色名,
  FROM_HOST AS 角色主机,
  TO_USER AS 用户名,
  TO_HOST AS 用户主机
FROM mysql.role_edges
WHERE FROM_USER = 'read_only_role';
```

## 权限审计

MySQL 的审计主要通过**日志系统**实现，核心目标是记录权限变更（如 GRANT/REVOKE）、用户登录、敏感操作（如 DROP TABLE）等，常用工具有：通用日志、二进制日志、审计插件。

**通用日志（General Log）**

通用日志记录 所有 SQL 语句（包括连接、权限操作、数据操作），适合全面审计，但日志量较大，不建议长期开启。

临时开启（无需重启 MySQL）：

```sql
-- 开启通用日志
SET GLOBAL general_log = ON;
-- 指定日志存储方式（文件或表）
SET GLOBAL log_output = 'FILE';  -- 存储为文件（默认）
-- 或存储到 mysql.general_log 表（便于查询）
SET GLOBAL log_output = 'TABLE';

```

永久配置（my.cnf 或 my.ini）：

```ini
[mysqld]
general_log = 1
general_log_file = /var/log/mysql/mysql-general.log  # 日志路径（需MySQL有权限写入）
log_output = FILE,TABLE  # 同时输出到文件和表

```

查看日志：

- 若存储为文件：直接查看 general_log_file 指定的文件；
- 若存储为表：查询 mysql.general_log 表：

```sql
SELECT event_time AS 时间, user_host AS 用户, argument AS SQL语句
FROM mysql.general_log
WHERE argument LIKE '%GRANT%'  # 筛选权限变更操作
ORDER BY event_time DESC;

```

**二进制日志（Binary Log）**

二进制日志记录 数据变更操作（INSERT/UPDATE/DELETE）和 DDL 语句（如 CREATE/ALTER/DROP、GRANT/REVOKE），不记录查询（SELECT），主要用于 replication 和数据恢复，也可辅助审计。

开启二进制日志（my.cnf）：

```ini
[mysqld]
log_bin = /var/log/mysql/mysql-bin.log  # 日志路径
server-id = 1  # 必须设置，用于区分不同实例
binlog_do_db = db1  # 可选：仅记录 db1 数据库的操作

```

查看二进制日志：使用 mysqlbinlog 工具解析日志（二进制格式，需转换为文本）。

```bash
# 查看最新日志中的权限变更
mysqlbinlog --base64-output=DECODE-ROWS /var/log/mysql/mysql-bin.000001 | grep -i "GRANT"
```

**审计插件（企业级审计）**

MySQL 企业版提供 MySQL Enterprise Audit 插件，社区版可使用第三方插件（如 Percona Audit Log Plugin），支持细粒度审计策略（如仅审计特定用户 / 操作）。

Percona Audit Log Plugin 示例（社区版可用）,安装插件：
```sql
-- 安装插件（需提前将插件文件放入 MySQL 插件目录）
INSTALL PLUGIN audit_log SONAME 'audit_log.so';

```

配置审计策略（my.cnf）：

```ini
[mysqld]
audit_log = ON
audit_log_file = /var/log/mysql/audit.log
audit_log_format = JSON  # 日志格式（JSON 或 OLD）
audit_log_policy = ALL  # 审计所有操作；可选 LOGINS（仅登录）、QUERIES（仅查询）等

```

查看审计日志：日志记录包含时间、用户、操作类型、SQL 语句等信息。

```json
{
  "audit_record": {
    "name": "Query",
    "timestamp": "2025-09-11T10:30:00",
    "user": "root@localhost",
    "host": "localhost",
    "query": "GRANT SELECT ON db1.* TO 'app_user'@'192.168.1.%'"
  }
}

```

**慢查询日志（辅助审计）**

慢查询日志记录执行时间超过阈值的 SQL 语句（默认 10 秒），可用于审计低效查询，间接发现异常操作（如恶意扫描表）。

```ini
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2  # 阈值设为 2 秒（记录执行≥2秒的语句）
log_queries_not_using_indexes = 1  # 记录未使用索引的语句
```