目录

1. 前言
2. MySQL 基础认知
3. MySQL 安装与配置
4. MySQL 核心语法
5. 高级查询技巧
6. MySQL 函数
7. 数据约束
8. 事务管理
9. 索引优化
10. 存储过程与函数
11. 用户与权限管理
12. 性能优化实战
13. 常见问题与解决方案

---

1. 前言

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（可视化工具，可选）

---

2. MySQL 基础认知

2.1 核心概念

概念	解释
数据库（DB）	存储数据的容器，一个MySQL服务可以包含多个数据库（如test、mysql）
数据表（Table）	数据库的基本存储单元，由行（记录）和列（字段）组成（如用户表user）
字段（Column）	表的列，定义数据类型（如idint、namevarchar）
记录（Row）	表的行，是一条完整的数据（如1, "张三", 20）
SQL	操作数据库的标准语言，分为DQL（查询）、DML（增删改）、DDL（定义）、DCL（权限）

2.2 MySQL 架构（简化版）

客户端（Navicat/命令行） → MySQL服务端 → 存储引擎（InnoDB/MyISAM） → 磁盘文件

• InnoDB：默认引擎，支持事务、外键、行级锁（推荐生产环境使用）
• MyISAM：轻量，不支持事务，表级锁（仅适用于只读场景）

---

3. MySQL 安装与配置

3.1 Windows 安装

步骤1：下载安装包

• 官网：https://dev.mysql.com/downloads/mysql/
• 选择「MySQL Community Server」→ 对应Windows版本（msi安装包）

步骤2：安装流程

1. 双击安装包，选择「Custom」自定义安装，勾选「MySQL Server」
2. 下一步配置类型：选择「Development Computer」（开发环境）
3. 设置root密码（务必记住，如123456）
4. 配置服务名（默认MySQL80），设置为「开机自启」
5. 完成安装，验证：打开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服务生效。

---

4. 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, -- 自增ID
    `name` VARCHAR(50) NOT NULL COMMENT '用户名', -- 非空，最大50字符
    `age` TINYINT UNSIGNED DEFAULT 0 COMMENT '年龄', -- 默认值0
    `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. 高级查询技巧

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);

---

6. 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)

---

7. 数据约束

约束用于保证数据的完整性和一致性，核心约束如下：

约束类型	关键字	作用	示例
非空约束	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. 事务管理

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. 索引优化

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 索引使用原则（避坑！）

1. 适合建索引的字段：查询频繁、区分度高（如手机号）、排序/分组的字段
2. 不适合建索引的字段：频繁修改、区分度低（如性别）、数据量小的表
3. 复合索引遵循最左匹配原则：INDEX(a,b,c) 仅匹配 a、a+b、a+b+c，不匹配 b、b+c、c
4. 避免索引失效：
   • WHERE子句中使用OR（除非所有字段都有索引）
   • 对索引字段做函数/运算（如YEAR(create_time) = 2026）
   • 使用LIKE '%xxx'（模糊匹配开头通配符）
   • 使用IS NULL/IS NOT NULL（除非是唯一索引）

---

10. 存储过程与函数

存储过程是预编译的SQL集合，可封装复杂逻辑，减少网络交互。

10.1 存储过程创建与调用

-- 创建存储过程：根据性别查询用户
DELIMITER // -- 修改结束符为//（避免与SQL的;冲突）
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`;

注意：存储过程/函数维护成本高，简单逻辑建议业务层实现，仅复杂统计场景使用。

---

11. 用户与权限管理

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. 性能优化实战

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 优化原则

1. 索引优化：
   • 优先创建复合索引，遵循最左匹配
   • 避免在索引字段做函数/运算
2. 查询优化：
   • 只查需要的字段（避免SELECT *）
   • 分页查询用LIMIT，避免大偏移量（如LIMIT 100000, 10可优化为WHERE id > 100000 LIMIT 10）
3. 表结构优化：
   • 用INT代替VARCHAR存储数字（如手机号可拆分为前缀+后缀，或用BIGINT）
   • 大字段（如文章内容）拆分到单独表
   • 避免使用NULL（可设置默认值）

---

13. 常见问题与解决方案

问题现象	原因	解决方案
连接MySQL报错1045	密码错误/权限不足	重置密码/检查用户host授权
索引失效	违反最左匹配/函数操作索引字段	调整查询条件/避免索引字段运算
事务回滚失败	表引擎为MyISAM	改为InnoDB引擎
插入中文乱码	编码不一致	统一设置为utf8mb4（库/表/连接）
锁等待超时	事务长时间未提交	优化事务逻辑，缩短事务执行时间

---

三、总结

核心关键点回顾

1. 基础操作：掌握数据库/表的CRUD，重点是SELECT查询（条件、分组、连接、子查询）。
2. 核心特性：事务（ACID）保证数据一致性，索引（合理创建+避免失效）提升查询性能，约束保证数据完整性。
3. 优化核心：用EXPLAIN分析SQL，慢查询日志定位问题，遵循「最左匹配」「避免SELECT *」等优化原则。
4. 生产规范：避免使用root用户，按最小权限分配账号；优先使用InnoDB引擎；外键、存储过程谨慎使用。