MySQL 入门到精通全解：10+核心知识点/基础/SQL/索引/企业级开发

一、MySQL 入门到精通：基础概念与环境搭建

MySQL是一款开源的关系型数据库管理系统（RDBMS），以轻量、高效、开源免费的特点，成为全球最流行的企业级数据库，也是全栈开发的必备技能。下面整理了3种最常用的环境搭建方式，新手直接抄作业，每一步都有详细说明。

# 1. Docker一键安装（企业/开发首选，环境隔离，无需手动配置）
# --name: 容器名，-p: 端口映射，-e: 设置root密码，-v: 数据持久化
docker run -d \
  --name mysql8 \
  -p 3306:3306 \
  -e MYSQL_ROOT_PASSWORD=123456 \
  -v /data/mysql:/var/lib/mysql \
  mysql:8.0

# 2. Linux系统服务启停命令（服务器运维必备）
systemctl start mysqld    # 启动MySQL服务
systemctl stop mysqld     # 停止MySQL服务
systemctl restart mysqld  # 重启MySQL服务
systemctl status mysqld   # 查看服务状态

# 3. Windows系统服务启停（本地开发常用）
net start mysql80  # 启动MySQL8.0服务
net stop mysql80   # 停止MySQL8.0服务

# 4. 连接MySQL命令（安装后验证是否成功）
mysql -u root -p
# 输入刚才设置的密码123456，即可进入MySQL命令行

# 5. 忘记root密码重置（新手高频场景）
# 第一步：跳过权限验证启动MySQL
mysqld --skip-grant-tables --user=mysql
# 第二步：无密码登录，修改root密码
mysql -u root
UPDATE mysql.user SET authentication_string = PASSWORD('新密码') WHERE User = 'root';
FLUSH PRIVILEGES;
# 第三步：重启正常启动MySQL

# 6. 基础操作：查看所有数据库（新手第一个命令）
SHOW DATABASES;

• Windows/Linux本地安装：适合新手本地开发，官网下载安装包一键安装，图形化操作，零基础友好
• Docker安装：适合企业服务器部署，环境隔离，一键启停，运维方便，不污染本地环境
• 云数据库RDS：适合线上项目，阿里云/腾讯云提供，无需自行运维，高可用，直接连接使用

MySQL常用基础命令速查（新手/企业必备）

-- 1. 查看当前数据库版本
SELECT VERSION();

-- 2. 查看当前登录用户
SELECT USER();

-- 3. 数据库操作（创建/删除/切换）
CREATE DATABASE IF NOT EXISTS test_db DEFAULT CHARACTER SET utf8mb4; -- 创建数据库
DROP DATABASE IF EXISTS test_db; -- 删除数据库（谨慎操作！）
USE test_db; -- 切换到目标数据库

-- 4. 创建项目专用用户并授权（新手必学，避免用root操作）
CREATE USER 'test'@'%' IDENTIFIED BY '123456'; -- %表示允许所有IP访问
GRANT ALL ON student_db.* TO 'test'@'%'; -- 给test用户授权student_db的所有权限
GRANT SELECT, INSERT ON student_db.* TO 'test'@'%'; -- 仅授权查询/插入权限（最小权限原则）
REVOKE ALL ON student_db.* FROM 'test'@'%'; -- 回收用户权限
FLUSH PRIVILEGES; -- 刷新权限使生效
DROP USER 'test'@'%'; -- 删除用户

-- 5. 查看数据库字符集（避免中文乱码，新手必查）
SHOW VARIABLES LIKE 'character_set%';

-- 6. 查看当前数据库
SELECT DATABASE();

-- 7. 查看表结构
DESC student; -- 查看student表的字段、类型、约束

二、MySQL 入门到精通：核心SQL语法（增删改查全场景）

SQL是MySQL的核心操作语言，分为DDL（数据定义）、DML（数据操作）、DQL（数据查询，重点）三大类，是MySQL开发的必备基础，下面每一个语法都加了小白友好的详细注释，直接复制就能用。

1. DDL：建库建表/表结构管理（定义数据结构）

-- 1. 创建数据库（判断不存在则创建，避免报错，统一字符集utf8mb4）
CREATE DATABASE IF NOT EXISTS student_db DEFAULT CHARACTER SET utf8mb4;

-- 2. 切换到目标数据库
USE student_db;

-- 3. 创建学生表（实战常用字段类型，加详细注释）
CREATE TABLE IF NOT EXISTS student (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '学生ID（主键自增，唯一标识）',
  name VARCHAR(50) NOT NULL COMMENT '学生姓名（非空，必填）',
  age INT COMMENT '年龄',
  gender ENUM('男','女') DEFAULT '男' COMMENT '性别（枚举类型，限制输入）',
  create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间（自动生成）'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '学生信息表';

-- 4. 修改表结构（企业高频，增/删/改字段）
ALTER TABLE student ADD COLUMN phone VARCHAR(11) COMMENT '手机号'; -- 新增字段
ALTER TABLE student MODIFY COLUMN age TINYINT COMMENT '年龄（修改字段类型）'; -- 修改字段类型
ALTER TABLE student CHANGE COLUMN age stu_age TINYINT COMMENT '学生年龄（重命名字段）'; -- 重命名字段
ALTER TABLE student DROP COLUMN phone; -- 删除字段（谨慎操作！）

-- 5. 复制表结构/数据
CREATE TABLE student_copy LIKE student; -- 仅复制表结构，不复制数据
INSERT INTO student_copy SELECT * FROM student; -- 复制全表数据
CREATE TABLE student_copy2 AS SELECT * FROM student; -- 同时复制结构+数据

-- 6. 清空表数据（TRUNCATE vs DELETE区别）
TRUNCATE TABLE student; -- 清空全表数据，自增重置，不可回滚（速度快）
DELETE FROM student; -- 清空全表数据，自增不重置，可回滚（速度慢）

-- 7. 删除表
DROP TABLE IF EXISTS student; -- 删除表（谨慎操作！无法恢复）

2. DML：增删改（操作数据，企业高频）

-- 1. 新增数据（INSERT）：支持批量插入
INSERT INTO student (name, age, gender) 
VALUES ('张三', 18, '男'), ('李四', 19, '女');

-- 2. 插入忽略（INSERT IGNORE：重复数据不报错，跳过）
INSERT IGNORE INTO student (id, name) VALUES (1, '张三'); -- id重复时跳过，不报错

-- 3. 替换插入（REPLACE INTO：重复数据删除后插入，更新数据）
REPLACE INTO student (id, name) VALUES (1, '张三新名字'); -- id重复时覆盖原数据

-- 4. 批量导入数据（LOAD DATA INFILE：大批量数据导入，比INSERT快10倍）
LOAD DATA INFILE '/data/student.csv' 
INTO TABLE student 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'
IGNORE 1 ROWS; -- 忽略表头行

-- 5. 修改数据（UPDATE，必须加WHERE！否则全表更新，数据灾难）
UPDATE student SET age = 20 WHERE name = '张三';
-- 批量更新：CASE WHEN 实现多条件更新
UPDATE student 
SET age = CASE 
  WHEN name = '张三' THEN 21 
  WHEN name = '李四' THEN 20 
  ELSE age 
END
WHERE name IN ('张三','李四');

-- 6. 删除数据（DELETE，必须加WHERE！否则全表删除，无法恢复）
DELETE FROM student WHERE id = 2;
-- 批量删除
DELETE FROM student WHERE age < 18;

3. DQL：数据查询（MySQL核心，企业高频需求全覆盖）

DQL是MySQL最常用的操作，也是企业项目中数据查询的核心，涵盖条件查询、分组、排序、联表查询、分页等全场景功能。

-- 1. 基础查询（不推荐用SELECT *，只查需要的字段，提升性能）
SELECT id, name, age FROM student;

-- 2. 条件查询（WHERE）：查询年龄大于18的男生
SELECT * FROM student WHERE age > 18 AND gender = '男';
-- 常用条件：IN/BETWEEN/LIKE/IS NULL
SELECT * FROM student WHERE age IN (18,19,20); -- 多条件匹配
SELECT * FROM student WHERE age BETWEEN 18 AND 20; -- 范围查询
SELECT * FROM student WHERE name LIKE '%张%'; -- 模糊查询（%匹配任意字符）
SELECT * FROM student WHERE phone IS NULL; -- 查询手机号为空的数据

-- 3. 去重查询（DISTINCT）
SELECT DISTINCT gender FROM student; -- 查看所有不重复的性别

-- 4. 分页查询（LIMIT：企业列表页必备）
SELECT * FROM student LIMIT 0,10; -- 第1页，每页10条（起始索引从0开始）
SELECT * FROM student LIMIT 10,10; -- 第2页，每页10条
SELECT * FROM student LIMIT 10 OFFSET 10; -- 等价于上面，MySQL8.0+支持

-- 5. 分组查询（GROUP BY）：按性别统计人数
SELECT gender, COUNT(*) AS count FROM student GROUP BY gender;
-- 分组后条件过滤（HAVING：统计后筛选，WHERE是统计前筛选）
SELECT gender, COUNT(*) AS count FROM student GROUP BY gender HAVING count > 5;

-- 6. 排序查询（ORDER BY）：按年龄降序排序
SELECT * FROM student ORDER BY age DESC; -- DESC降序，ASC升序（默认）
-- 多字段排序：先按年龄降序，再按创建时间升序
SELECT * FROM student ORDER BY age DESC, create_time ASC;

-- 7. 联表查询（实战核心，学生表+成绩表联查）
-- LEFT JOIN：左连接，保留左表所有数据，右表匹配不到显示NULL
SELECT s.name, sc.score 
FROM student s
LEFT JOIN score sc ON s.id = sc.student_id
WHERE sc.score > 90;
-- INNER JOIN：内连接，仅返回两张表匹配的数据
SELECT s.name, sc.score FROM student s INNER JOIN score sc ON s.id = sc.student_id;

-- 8. 子查询（嵌套查询，复杂场景用）
-- 查询成绩大于90分的学生信息
SELECT * FROM student WHERE id IN (SELECT student_id FROM score WHERE score > 90);

-- 9. 联合查询（UNION：合并多个查询结果，自动去重；UNION ALL不去重，速度更快）
SELECT name FROM student WHERE gender = '男'
UNION ALL
SELECT name FROM student WHERE gender = '女';

4. 常用函数（字符串/日期/聚合，小白必备）

-- 1. 字符串函数
SELECT CONCAT(name, '(', age, '岁)') AS info FROM student; -- 字符串拼接
SELECT UPPER(name) FROM student; -- 转大写
SELECT LOWER(name) FROM student; -- 转小写
SELECT LENGTH(name) FROM student; -- 字符串长度
SELECT SUBSTRING(name,1,1) FROM student; -- 截取字符串（取姓名首字）

-- 2. 日期函数
SELECT NOW(); -- 当前日期时间
SELECT CURDATE(); -- 当前日期
SELECT CURTIME(); -- 当前时间
SELECT YEAR(create_time) FROM student; -- 提取年份
SELECT MONTH(create_time) FROM student; -- 提取月份
SELECT DATEDIFF(NOW(), create_time) FROM student; -- 计算两个日期的天数差

-- 3. 聚合函数（统计用）
SELECT COUNT(*) FROM student; -- 统计总条数
SELECT SUM(score) FROM score; -- 求和
SELECT AVG(score) FROM score; -- 求平均值
SELECT MAX(score) FROM score; -- 求最大值
SELECT MIN(score) FROM score; -- 求最小值

5. 视图/存储过程（简化复杂操作，企业常用）

视图相当于一张虚拟表，基于真实表的查询结果创建，能简化复杂的联表查询，还能做权限控制；存储过程是预编译的SQL集合，适合批量操作。

-- 1. 视图（View）
-- 创建视图：把学生表和成绩表的联表查询做成视图，新手直接查视图
CREATE VIEW student_score_view AS
SELECT s.id, s.name, s.age, sc.score
FROM student s
LEFT JOIN score sc ON s.id = sc.student_id;

-- 使用视图：像普通表一样查询，不用写复杂的联表语句
SELECT * FROM student_score_view WHERE score > 90;

-- 删除视图
DROP VIEW IF EXISTS student_score_view;

-- 2. 存储过程（Procedure）
-- 创建简单存储过程：给所有学生年龄加1
DELIMITER // -- 把结束符从;改成//，避免和存储过程内的;冲突
CREATE PROCEDURE update_student_age()
BEGIN
  UPDATE student SET age = age + 1; -- 业务逻辑：所有学生年龄+1
END //
DELIMITER ; -- 改回原来的结束符

-- 调用存储过程
CALL update_student_age();

-- 删除存储过程
DROP PROCEDURE IF EXISTS update_student_age;

三、MySQL 入门到精通：高级特性与性能优化（企业级必备）

掌握高级特性和性能优化，才能胜任企业级MySQL项目开发，核心包括索引、事务、执行计划分析三大块，下面每一个都有实战代码，小白能直接上手。

1. 索引（优化查询速度核心，全类型覆盖）

索引相当于书的目录，能大幅提升查询速度，是MySQL性能优化的核心手段，下面覆盖所有常用索引类型+索引失效场景。

-- 1. 普通索引（最常用，提升查询速度）
CREATE INDEX idx_student_name ON student(name); -- 给学生姓名加索引

-- 2. 唯一索引（唯一约束，避免重复数据）
CREATE UNIQUE INDEX idx_student_phone ON student(phone); -- 手机号唯一索引

-- 3. 复合索引（多字段索引，遵循最左前缀原则）
CREATE INDEX idx_student_name_age ON student(name, age); -- 姓名+年龄复合索引
-- 生效场景：WHERE name=? 或 WHERE name=? AND age=?
-- 失效场景：WHERE age=?（不符合最左前缀）

-- 4. 全文索引（MySQL5.6+支持，适合大文本模糊查询，替代LIKE %xxx%）
CREATE FULLTEXT INDEX idx_student_name ON student(name);
-- 使用全文索引查询
SELECT * FROM student WHERE MATCH(name) AGAINST('张三');

-- 5. 查看表中所有索引
SHOW INDEX FROM student;

-- 6. 删除索引
DROP INDEX idx_student_name ON student;

-- 7. 索引失效高频场景（企业必避坑）
-- 1. 索引列使用函数：WHERE YEAR(create_time) = 2024（失效）→ 改为 WHERE create_time BETWEEN '2024-01-01' AND '2024-12-31'
-- 2. 索引列使用%开头的模糊查询：WHERE name LIKE '%张%'（失效）→ 改为 WHERE name LIKE '张%'
-- 3. 隐式类型转换：WHERE phone = 13800138000（phone是varchar类型，失效）→ 改为 WHERE phone = '13800138000'
-- 4. OR条件未全部加索引：WHERE name=? OR age=?（仅name加索引，age未加，全表扫描）
-- 5. 索引列参与运算：WHERE age + 1 = 20（失效）→ 改为 WHERE age = 19

2. 事务（保证数据一致性，企业级核心）

事务用于保证数据操作的原子性，比如转账操作，要么同时成功，要么同时失败，符合ACID特性，下面覆盖事务全场景+隔离级别。

-- 转账实战：A给B转100元（事务标准写法）
START TRANSACTION; -- 开启事务
-- A账户减100
UPDATE account SET balance = balance - 100 WHERE name = 'A';
-- B账户加100
UPDATE account SET balance = balance + 100 WHERE name = 'B';
COMMIT; -- 提交事务（操作成功）
-- 若出错，执行 ROLLBACK; 回滚事务，数据恢复原样
-- ROLLBACK;

-- 事务隔离级别（解决脏读/不可重复读/幻读问题）
-- 查看当前隔离级别
SELECT @@transaction_isolation;
-- 设置隔离级别（MySQL8.0+）
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 读已提交（常用）
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- 可重复读（MySQL默认）

-- 锁表/死锁排查（企业运维必备）
SHOW OPEN TABLES WHERE In_use > 0; -- 查看被锁的表
SHOW PROCESSLIST; -- 查看当前执行的进程，找到锁表进程
KILL 进程ID; -- 杀死锁表进程

3. EXPLAIN执行计划：分析慢查询，优化索引（神器）

EXPLAIN是MySQL性能优化的神器，能查看SQL的执行计划，判断索引是否生效，新手必学，下面是实战演示+关键指标说明。

-- 1. 给name字段加索引（之前学的）
CREATE INDEX idx_student_name ON student(name);

-- 2. 用EXPLAIN分析查询语句，看索引是否生效
EXPLAIN SELECT * FROM student WHERE name = '张三';

-- 3. 关键指标说明（小白必懂）：
-- type: 最好是ref/range，最差是ALL（全表扫描，索引失效）
-- key: 显示实际使用的索引，这里是idx_student_name，说明索引生效
-- rows: 扫描的行数，越少越好
-- Extra: 出现Using filesort/Using temporary说明需要优化

4. 数据备份与恢复（企业运维必备）

-- 1. 全库备份（mysqldump命令，Linux服务器常用）
mysqldump -u root -p --all-databases > /data/all_db_backup.sql

-- 2. 单库备份
mysqldump -u root -p student_db > /data/student_db_backup.sql

-- 3. 单表备份
mysqldump -u root -p student_db student > /data/student_backup.sql

-- 4. 数据恢复
mysql -u root -p student_db < /data/student_db_backup.sql

四、MySQL 入门到精通：完整实战案例（小白直接跑通）

学完语法，我们来做一个完整的学生成绩管理系统实战，从建库、建表、插入数据到联表查询，所有代码都可直接运行，零基础也能跑通。

-- 1. 创建数据库
CREATE DATABASE IF NOT EXISTS student_management DEFAULT CHARACTER SET utf8mb4;
USE student_management;

-- 2. 创建学生表
CREATE TABLE IF NOT EXISTS student (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '学生ID（主键自增）',
  name VARCHAR(50) NOT NULL COMMENT '学生姓名',
  age INT COMMENT '年龄',
  gender ENUM('男','女') DEFAULT '男' COMMENT '性别',
  create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '学生信息表';

-- 3. 创建成绩表
CREATE TABLE IF NOT EXISTS score (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '成绩ID',
  student_id INT NOT NULL COMMENT '关联学生ID',
  subject VARCHAR(50) NOT NULL COMMENT '科目',
  score INT NOT NULL COMMENT '分数',
  FOREIGN KEY (student_id) REFERENCES student(id) ON DELETE CASCADE -- 外键关联，学生删除时成绩同步删除
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '成绩表';

-- 4. 给常用字段加索引（优化查询速度）
CREATE INDEX idx_student_name ON student(name);
CREATE INDEX idx_score_student_id ON score(student_id);

-- 5. 插入测试数据
INSERT INTO student (name, age, gender) 
VALUES ('张三', 18, '男'), ('李四', 19, '女'), ('王五', 20, '男');

INSERT INTO score (student_id, subject, score)
VALUES (1, '语文', 90), (1, '数学', 95), (2, '语文', 88), (2, '数学', 92), (3, '语文', 95), (3, '数学', 85);

-- 6. 联表查询：查询每个学生的总分和平均分
SELECT s.name, SUM(sc.score) AS total_score, AVG(sc.score) AS avg_score
FROM student s
LEFT JOIN score sc ON s.id = sc.student_id
GROUP BY s.id, s.name
ORDER BY total_score DESC;

-- 7. 分页查询：查询学生成绩列表，第1页，每页2条
SELECT s.name, sc.subject, sc.score
FROM student s
LEFT JOIN score sc ON s.id = sc.student_id
ORDER BY sc.score DESC
LIMIT 0,2;

五、MySQL 入门到精通：实战优化与避坑指南（新手100%踩坑）

六、MySQL 入门到精通：学习路线与总结