前言
1.1 什么是 MySQL？
MySQL 是一款开源的关系型数据库管理系统（RDBMS），基于 SQL（结构化查询语言）实现数据管理，广泛应用于 Web 开发（如 PHP+MySQL、Python+MySQL），特点是轻量、高效、跨平台、免费（社区版）。
1.2 学习目标
- 掌握 MySQL 的安装、配置和基础连接方式
- 熟练使用 SQL 语句操作数据库、表、数据
- 理解事务、索引、约束等核心概念
- 能够优化简单的 SQL 查询，解决常见问题
1.3 环境准备
- 操作系统：Windows/Linux/MacOS
- 工具：MySQL Community Server（数据库服务）、Navicat/DBeaver/MySQL Workbench（可视化工具，可选）
MySQL 基础认知
2.1 核心概念
| 概念 | 解释 |
|---|---|
| 数据库（DB） | 存储数据的容器，一个 MySQL 服务可以包含多个数据库（如 test、mysql） |
| 数据表（Table） | 数据库的基本存储单元，由行（记录）和列（字段）组成（如用户表 user） |
| 字段（Column） | 表的列，定义数据类型（如 id int、name varchar） |
| 记录（Row） | 表的行，是一条完整的数据（如 1, "张三", 20） |
| SQL | 操作数据库的标准语言，分为 DQL（查询）、DML（增删改）、DDL（定义）、DCL（权限） |
2.2 MySQL 架构（简化版）
客户端（Navicat/命令行） → MySQL 服务端 → 存储引擎（InnoDB/MyISAM） → 磁盘文件
- InnoDB：默认引擎，支持事务、外键、行级锁（推荐生产环境使用）
- MyISAM：轻量，不支持事务，表级锁（仅适用于只读场景）
MySQL 安装与配置
3.1 Windows 安装
步骤 1：下载安装包
- 官网：https://dev.mysql.com/downloads/mysql/
- 选择「MySQL Community Server」→ 对应 Windows 版本（msi 安装包）
步骤 2：安装流程
- 双击安装包，选择「Custom」自定义安装，勾选「MySQL Server」
- 下一步配置类型：选择「Development Computer」（开发环境）
- 设置 root 密码（务必记住，如
123456） - 配置服务名（默认
MySQL80），设置为「开机自启」 - 完成安装，验证：打开 cmd，输入
mysql -u root -p，输入密码后进入 MySQL 命令行即成功。
3.2 Linux（CentOS7）安装
# 1. 安装依赖
yum install -y wget
# 2. 添加 MySQL 源
wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
rpm -ivh mysql80-community-release-el7-3.noarch.rpm
# 3. 安装 MySQL 服务
yum install -y mysql-community-server
# 4. 启动服务
systemctl start mysqld
# 5. 查看初始密码
grep 'temporary password' /var/log/mysqld.log
# 6. 登录并修改密码（初始密码替换为上面查到的）
mysql -u root -p
ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码（需包含大小写 + 数字 + 特殊字符）';
# 7. 设置开机自启
systemctl enable mysqld
3.3 基础配置（my.cnf）
修改配置文件（Windows：my.ini；Linux：/etc/my.cnf），添加以下常用配置：
[mysqld]
# 数据库默认编码
character-set-server=utf8mb4
# 排序规则
collation-server=utf8mb4_general_ci
# 默认存储引擎
default-storage-engine=InnoDB
# 端口（默认 3306，可修改避免冲突）
port=3306
# 最大连接数
max_connections=1000
# 慢查询阈值（超过 1 秒记录）
long_query_time=1
[mysql]
# 客户端编码
default-character-set=utf8mb4
修改后重启 MySQL 服务生效。
MySQL 核心语法
4.1 数据库操作（DDL）
| 操作 | SQL 语句 |
|---|---|
| 创建数据库 | CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 编码]; |
| 查看所有数据库 | SHOW DATABASES; |
| 使用数据库 | USE 数据库名; |
| 修改数据库编码 | ALTER DATABASE 数据库名 CHARACTER SET utf8mb4; |
| 删除数据库 | DROP DATABASE [IF EXISTS] 数据库名; |
示例：
-- 创建名为 test_db 的数据库，避免重复创建，编码为 utf8mb4
CREATE DATABASE IF NOT EXISTS test_db DEFAULT CHARSET utf8mb4;
-- 使用该数据库
USE test_db;
-- 查看数据库列表
SHOW DATABASES;
-- 删除数据库（谨慎操作！）
DROP DATABASE IF EXISTS test_db;
4.2 数据表操作（DDL）
4.2.1 创建表
语法：
CREATE TABLE [IF NOT EXISTS] 表名 (
字段 1 数据类型 [约束],
字段 2 数据类型 [约束],
...
[PRIMARY KEY(主键字段)]
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
示例：创建用户表 user
CREATE TABLE IF NOT EXISTS `user` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增 ID',
`name` VARCHAR(50) NOT NULL COMMENT '用户名',
`age` TINYINT UNSIGNED DEFAULT 0 COMMENT '年龄',
`gender` ENUM('男','女','未知') DEFAULT '未知' COMMENT '性别',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`id`) -- 主键
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表';
4.2.2 常用表操作
-- 查看表结构
DESC `user`;
-- 查看创建表的 SQL
SHOW CREATE TABLE `user`;
-- 修改表：添加字段
ALTER TABLE `user` ADD `phone` VARCHAR(11) AFTER `name`;
-- 修改表：修改字段类型
ALTER TABLE `user` MODIFY `phone` VARCHAR(20);
-- 修改表：删除字段
ALTER TABLE `user` DROP `phone`;
-- 重命名表
ALTER TABLE `user` RENAME TO `user_info`;
-- 删除表（谨慎！）
DROP TABLE IF EXISTS `user`;
4.3 数据操作（DML）
4.3.1 插入数据（INSERT）
-- 插入单条数据
INSERT INTO `user`(`name`,`age`,`gender`) VALUES('张三',20,'男');
-- 插入多条数据
INSERT INTO `user`(`name`,`age`,`gender`) VALUES('李四',22,'女'),('王五',25,'男');
-- 插入所有字段（按表结构顺序）
INSERT INTO `user` VALUES(NULL,'赵六',18,'未知',NOW());
-- id 自增，传 NULL 即可
4.3.2 更新数据（UPDATE）
-- 修改单条数据（务必加 WHERE，否则修改全表！）
UPDATE `user` SET `age`=21 WHERE `id`=1;
-- 修改多条数据
UPDATE `user` SET `gender`='未知' WHERE `age`<20;
4.3.3 删除数据（DELETE）
-- 删除指定数据（务必加 WHERE！）
DELETE FROM `user` WHERE `id`=3;
-- 清空表（保留表结构，自增 ID 重置）
TRUNCATE TABLE `user`;
4.3.4 查询数据（DQL，核心）
基础语法：
SELECT [DISTINCT] 字段 1, 字段 2,...
FROM 表名
[WHERE 条件]
[ORDER BY 字段 [ASC/DESC]]
[LIMIT 偏移量，条数];
示例：
-- 查询所有字段
SELECT * FROM `user`;
-- 查询指定字段，去重
SELECT DISTINCT `gender` FROM `user`;
-- 条件查询：年龄大于 20 的男性
SELECT `name`,`age` FROM `user` WHERE `age`>20 AND `gender`='男';
-- 排序：按年龄降序
SELECT * FROM `user` ORDER BY `age` DESC;
-- 分页：第 1 页，每页 2 条（偏移量=0，条数=2）
SELECT * FROM `user` LIMIT 0,2;
高级查询技巧
5.1 条件查询进阶
| 运算符/关键字 | 作用 | 示例 |
|---|---|---|
| IN | 匹配多个值 | WHERE age IN (20,22,25) |
| LIKE | 模糊匹配（%任意字符，_单个字符） | WHERE name LIKE '张%' |
| IS NULL | 判断字段为空 | WHERE phone IS NULL |
| BETWEEN … AND … | 区间匹配 | WHERE age BETWEEN 18 AND 30 |
5.2 分组查询（GROUP BY + HAVING）
用于统计数据（结合聚合函数）：
-- 按性别分组，统计每组人数和平均年龄
SELECT `gender`, COUNT(*) AS `total`, AVG(`age`) AS `avg_age`
FROM `user`
GROUP BY `gender`
HAVING `avg_age`>20;
-- 过滤分组后的结果（WHERE 过滤原始数据）
5.3 连接查询（多表关联）
假设有两张表：user（用户表）、order（订单表，含 user_id 关联 user.id）。
| 连接类型 | 作用 | 语法 |
|---|---|---|
| INNER JOIN | 只查两表匹配的数据 | FROM user INNER JOIN order ON user.id = order.user_id |
| LEFT JOIN | 查左表所有数据，右表匹配不上则为 NULL | FROM user LEFT JOIN order ON user.id = order.user_id |
示例：
-- 查询每个用户的姓名和对应的订单数
SELECT u.`name`, COUNT(o.`id`) AS `order_count`
FROM `user` u
LEFT JOIN `order` o ON u.`id` = o.`user_id`
GROUP BY u.`id`;
5.4 子查询
嵌套在其他 SQL 中的查询（分「标量子查询」「行子查询」「表子查询」）：
-- 标量子查询：查询年龄大于平均年龄的用户
SELECT `name`,`age`
FROM `user`
WHERE `age`>(SELECT AVG(`age`) FROM `user`);
-- 表子查询：查询订单数大于 5 的用户
SELECT *
FROM `user`
WHERE `id` IN(
SELECT `user_id`
FROM `order`
GROUP BY `user_id`
HAVING COUNT(*)>5
);
MySQL 函数
6.1 字符串函数
| 函数 | 作用 | 示例 |
|---|---|---|
| CONCAT(a,b) | 拼接字符串 | CONCAT('姓名：', name) |
| LENGTH(str) | 字符串长度（字节） | LENGTH(name) |
| SUBSTR(str,start,len) | 截取字符串 | SUBSTR(name,1,2) |
| TRIM(str) | 去除首尾空格 | TRIM(name) |
6.2 数值函数
| 函数 | 作用 | 示例 |
|---|---|---|
| ROUND(num,n) | 四舍五入（保留 n 位小数） | ROUND(3.1415,2) → 3.14 |
| ABS(num) | 绝对值 | ABS(-5) → 5 |
| CEIL(num) | 向上取整 | CEIL(3.2) → 4 |
| FLOOR(num) | 向下取整 | FLOOR(3.9) → 3 |
6.3 日期时间函数
| 函数 | 作用 | 示例 |
|---|---|---|
| NOW() | 当前时间（日期 + 时间） | NOW() → 2026-01-10 10:00:00 |
| DATE(NOW()) | 提取日期 | DATE(NOW()) → 2026-01-10 |
| YEAR(NOW()) | 提取年份 | YEAR(NOW()) → 2026 |
| DATEDIFF(d1,d2) | 计算 d1-d2 的天数差 | DATEDIFF(NOW(), '2026-01-01') → 9 |
6.4 聚合函数（重点）
| 函数 | 作用 | 示例 |
|---|---|---|
| COUNT(*) | 统计行数（包含 NULL） | COUNT(*) |
| SUM(字段) | 求和 | SUM(age) |
| AVG(字段) | 平均值 | AVG(age) |
| MAX(字段) | 最大值 | MAX(age) |
| MIN(字段) | 最小值 | MIN(age) |
数据约束
约束用于保证数据的完整性和一致性，核心约束如下：
| 约束类型 | 关键字 | 作用 | 示例 |
|---|---|---|---|
| 非空约束 | NOT NULL | 字段不能为空 | name VARCHAR(50) NOT NULL |
| 唯一约束 | UNIQUE | 字段值唯一（可多个 NULL） | phone VARCHAR(11) UNIQUE |
| 主键约束 | PRIMARY KEY | 非空 + 唯一，标识唯一记录 | id INT PRIMARY KEY AUTO_INCREMENT |
| 外键约束 | FOREIGN KEY | 关联另一张表的主键，保证数据关联完整性 | user_id INT FOREIGN KEY REFERENCES user(id) |
| 默认约束 | DEFAULT | 字段默认值 | gender ENUM('男','女') DEFAULT '男' |
外键示例：创建订单表关联用户表
CREATE TABLE `order` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`order_no` VARCHAR(30) NOT NULL UNIQUE COMMENT '订单号',
`user_id` INT UNSIGNED NOT NULL COMMENT '关联用户 ID',
`amount` DECIMAL(10,2) NOT NULL COMMENT '订单金额',
PRIMARY KEY (`id`),
-- 外键约束：user_id 关联 user 表的 id，删除用户时级联删除订单
FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
注意：外键会降低性能，高并发场景可业务层保证数据一致性，不使用外键。
事务管理
8.1 事务的 ACID 特性
- A（原子性）：事务中的操作要么全执行，要么全不执行
- C（一致性）：事务执行前后数据状态一致（如转账：A 减 100，B 必加 100）
- I（隔离性）：多个事务并发执行时，互相不干扰
- D（持久性）：事务提交后，数据永久保存到磁盘
8.2 事务操作语法
-- 开启事务（关闭自动提交）
START TRANSACTION;
-- 执行 SQL 操作（如转账：用户 1 减 100，用户 2 加 100）
UPDATE `user` SET `balance`=`balance`-100 WHERE `id`=1;
UPDATE `user` SET `balance`=`balance`+100 WHERE `id`=2;
-- 提交事务（生效）
COMMIT;
-- 若出错，回滚事务（恢复到事务前状态）
-- ROLLBACK;
8.3 事务隔离级别
MySQL 默认隔离级别为 REPEATABLE READ（可重复读），避免脏读、不可重复读，允许幻读。
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 性能 |
|---|---|---|---|---|
| READ UNCOMMITTED（读未提交） | ✅ | ✅ | ✅ | 最高 |
| READ COMMITTED（读已提交） | ❌ | ✅ | ✅ | 中 |
| REPEATABLE READ（可重复读） | ❌ | ❌ | ✅ | 中 |
| SERIALIZABLE（串行化） | ❌ | ❌ | ❌ | 最低 |
-- 查看当前隔离级别
SELECT @@transaction_isolation;
-- 设置隔离级别（会话级）
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
索引优化
9.1 索引的作用
索引是数据库的「目录」，用于快速定位数据，避免全表扫描，大幅提升查询效率；但会降低插入/更新/删除效率（需维护索引）。
9.2 索引类型
| 索引类型 | 关键字/特点 | 适用场景 |
|---|---|---|
| 主键索引 | PRIMARY KEY | 主键字段（自动创建） |
| 唯一索引 | UNIQUE | 唯一字段（如订单号、手机号） |
| 普通索引 | INDEX | 普通查询字段（如姓名、年龄） |
| 复合索引 | INDEX (字段 1，字段 2) | 多字段联合查询（如 name+age） |
| 全文索引 | FULLTEXT | 文本内容模糊查询（如文章内容） |
9.3 索引操作
-- 创建普通索引
CREATE INDEX idx_user_name ON `user`(`name`);
-- 创建复合索引
CREATE INDEX idx_user_name_age ON `user`(`name`,`age`);
-- 查看索引
SHOW INDEX FROM `user`;
-- 删除索引
DROP INDEX idx_user_name ON `user`;
9.4 索引使用原则（避坑！）
- 适合建索引的字段：查询频繁、区分度高（如手机号）、排序/分组的字段
- 不适合建索引的字段：频繁修改、区分度低（如性别）、数据量小的表
- 复合索引遵循最左匹配原则：
INDEX(a,b,c)仅匹配a、a+b、a+b+c，不匹配b、b+c、c - 避免索引失效：
- WHERE 子句中使用
OR（除非所有字段都有索引） - 对索引字段做函数/运算（如
YEAR(create_time) = 2026） - 使用
LIKE '%xxx'（模糊匹配开头通配符） - 使用
IS NULL/IS NOT NULL（除非是唯一索引）
- WHERE 子句中使用
存储过程与函数
存储过程是预编译的 SQL 集合，可封装复杂逻辑，减少网络交互。
10.1 存储过程创建与调用
-- 创建存储过程：根据性别查询用户
DELIMITER //
CREATE PROCEDURE get_user_by_gender(IN p_gender ENUM('男','女','未知'), OUT p_total INT)
BEGIN
-- 查询符合条件的用户
SELECT * FROM `user` WHERE `gender` = p_gender;
-- 统计数量并赋值给输出参数
SELECT COUNT(*) INTO p_total FROM `user` WHERE `gender` = p_gender;
END //
DELIMITER ;
-- 恢复结束符为;
-- 调用存储过程
CALL get_user_by_gender('男',@total);
-- 查看输出参数
SELECT @total;
10.2 存储函数
-- 创建存储函数：计算用户年龄（根据生日）
DELIMITER //
CREATE FUNCTION calc_age(p_birth DATE) RETURNS INT DETERMINISTIC
BEGIN
RETURN TIMESTAMPDIFF(YEAR, p_birth,NOW());
END //
DELIMITER ;
-- 调用函数
SELECT `name`, calc_age(`birth`) AS `age` FROM `user`;
注意：存储过程/函数维护成本高，简单逻辑建议业务层实现，仅复杂统计场景使用。
用户与权限管理
MySQL 默认有 root 超级用户，生产环境需创建专用用户并分配最小权限。
11.1 用户操作
-- 创建用户：允许 192.168.1.%网段的 user_test 用户访问
CREATE USER 'user_test'@'192.168.1.%' IDENTIFIED BY'Test@123456';
-- 修改用户密码
ALTER USER 'user_test'@'192.168.1.%' IDENTIFIED BY'NewTest@123456';
-- 删除用户
DROP USER IF EXISTS 'user_test'@'192.168.1.%';
-- 查看所有用户
SELECT user, host FROM mysql.user;
11.2 权限操作
| 权限类型 | 关键字 | 说明 |
|---|---|---|
| 数据库权限 | ALL PRIVILEGES | 所有权限 |
| 表权限 | SELECT, INSERT, UPDATE | 查询、插入、更新 |
| 列权限 | SELECT (name,age) | 仅查询指定列 |
-- 授权：给 user_test 分配 test_db 数据库的所有权限
GRANT ALL PRIVILEGES ON test_db.* TO 'user_test'@'192.168.1.%';
-- 授权：仅允许查询 user 表
GRANT SELECT ON test_db.user TO 'user_test'@'192.168.1.%';
-- 回收权限
REVOKE UPDATE ON test_db.user FROM 'user_test'@'192.168.1.%';
-- 刷新权限（授权/回收后必须执行）
FLUSH PRIVILEGES;
-- 查看用户权限
SHOW GRANTS FOR 'user_test'@'192.168.1.%';
性能优化实战
12.1 慢查询日志
开启慢查询日志，定位耗时 SQL：
-- 查看慢查询配置
SHOW VARIABLES LIKE '%slow_query%';
-- 开启慢查询日志（会话级）
SET SESSION slow_query_log = ON;
-- 设置慢查询阈值（1 秒）
SET SESSION long_query_time = 1;
-- 查看慢查询日志路径
SHOW VARIABLES LIKE 'slow_query_log_file';
12.2 EXPLAIN 分析查询
EXPLAIN 用于分析 SQL 执行计划，判断是否使用索引、是否全表扫描：
-- 分析查询语句
EXPLAIN SELECT * FROM `user` WHERE `name`='张三';
EXPLAIN 结果核心字段：
type：访问类型（ALL→全表扫描，最差；ref→索引扫描，较好；const→主键/唯一索引，最好）key：实际使用的索引（NULL 表示未使用索引）rows：预估扫描行数（越小越好）Extra：额外信息（Using filesort→文件排序，需优化；Using index→覆盖索引，最优）
12.3 优化原则
- 索引优化：
- 优先创建复合索引，遵循最左匹配
- 避免在索引字段做函数/运算
- 查询优化：
- 只查需要的字段（避免
SELECT *） - 分页查询用
LIMIT，避免大偏移量（如LIMIT 100000, 10可优化为WHERE id > 100000 LIMIT 10）
- 只查需要的字段（避免
- 表结构优化：
- 用
INT代替VARCHAR存储数字（如手机号可拆分为前缀 + 后缀，或用BIGINT） - 大字段（如文章内容）拆分到单独表
- 避免使用
NULL（可设置默认值）
- 用
常见问题与解决方案
| 问题现象 | 原因 | 解决方案 |
|---|---|---|
| 连接 MySQL 报错 1045 | 密码错误/权限不足 | 重置密码/检查用户 host 授权 |
| 索引失效 | 违反最左匹配/函数操作索引字段 | 调整查询条件/避免索引字段运算 |
| 事务回滚失败 | 表引擎为 MyISAM | 改为 InnoDB 引擎 |
| 插入中文乱码 | 编码不一致 | 统一设置为 utf8mb4（库/表/连接） |
| 锁等待超时 | 事务长时间未提交 | 优化事务逻辑，缩短事务执行时间 |
总结
核心关键点回顾
- 基础操作：掌握数据库/表的 CRUD，重点是
SELECT查询（条件、分组、连接、子查询）。 - 核心特性：事务（ACID）保证数据一致性，索引（合理创建 + 避免失效）提升查询性能，约束保证数据完整性。
- 优化核心：用
EXPLAIN分析 SQL，慢查询日志定位问题，遵循「最左匹配」「避免 SELECT *」等优化原则。 - 生产规范：避免使用
root用户，按最小权限分配账号；优先使用 InnoDB 引擎；外键、存储过程谨慎使用。
