MySQL从零到精通：基础入门与SQL核心操作详解（含MySQL 5/8差异）

前端时间学习了多线程、Redis相关内容，最近一段时间再把Mysql整体内容梳理一下，作为笔记将这部分重点内容系统串联起来.

一、引言：为什么学习MySQL？

在当今数据驱动的时代，数据库是存储和管理数据的核心工具。MySQL作为全球最流行的开源关系型数据库之一，凭借其高性能、易用性和可靠性，被广泛应用于电商、金融、社交等各类系统。无论是后端开发、数据分析还是运维工程师，掌握MySQL都是必备技能。

本文将从环境搭建到SQL核心操作，带你系统掌握MySQL基础。内容包含MySQL 5与8的关键差异、常见避坑指南，并通过实例+注释让你快速上手。学完本篇，你将能独立创建数据库、设计表结构、完成数据的增删改查（CRUD），并为后续学习索引、事务等高级知识打下坚实基础。

二、环境准备：MySQL安装与版本差异

2.1 安装步骤（Windows/Linux/macOS通用思路）

MySQL支持多种安装方式，推荐优先使用官方安装包或Docker（避免依赖冲突）。以下以MySQL 8.0为例（5.7步骤类似，仅版本号不同）：

1. 下载安装包

• 官网地址：https://dev.mysql.com/downloads/mysql/
• 选择对应系统版本（如Windows的.msi、Linux的.rpm或.deb、macOS的.dmg）。

2. 基础配置（关键！）

安装过程中需设置：

• root用户密码（牢记！后续用mysql -u root -p登录）。
• 端口号（默认3306，若冲突可修改）。
• 服务名（默认MySQL，Windows可在“服务”中查看）。

3. Docker快速部署（推荐开发者）

# 拉取MySQL 8.0镜像
docker pull mysql:8.0
# 运行容器（-e设置环境变量，-v挂载数据卷）
docker run -d --name mysql8 -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql:8.0

2.2 MySQL 5 vs 8 核心差异（必知！）

特性	MySQL 5.7	MySQL 8.0
默认字符集	latin1（需手动改为utf8mb4）	utf8mb4（支持emoji表情）
默认认证插件	mysql_native_password	caching_sha2_password（更安全，但旧客户端可能不兼容）
CTE（公用表表达式）	不支持	支持（WITH子句，简化复杂查询）
JSON支持	基础JSON类型	JSON增强（路径查询、函数优化）
索引特性	不支持降序索引、隐藏索引	支持降序索引（INDEX idx_name (col DESC)）、隐藏索引（ALTER TABLE t ALTER INDEX idx_name INVISIBLE）

2.3 避坑指南：安装后无法登录？

• 问题1：提示“Access denied for user 'root'@'localhost'”
  • 解决：确认密码是否正确；若忘记密码，可通过--skip-grant-tables模式重置（https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html）。
• 问题2：远程连接失败（如Navicat连接Linux服务器）
  • 解决：
    1. 服务器防火墙开放3306端口（ufw allow 3306）；
    2. MySQL授权远程访问：GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY '密码'; FLUSH PRIVILEGES;

三、SQL初识：分类与核心语法

SQL（Structured Query Language）是用于操作数据库的标准语言，分为四大类：

分类	全称	作用	常用命令
DDL	Data Definition Language	定义数据库对象（库、表、索引）	CREATE, DROP, ALTER, TRUNCATE
DML	Data Manipulation Language	操作数据（增删改）	INSERT, UPDATE, DELETE
DQL	Data Query Language	查询数据	SELECT
DCL	Data Control Language	权限控制	GRANT, REVOKE

四、DDL：数据库与表结构操作（含实例+注释）

4.1 数据库操作（库管理）

1. 创建数据库

-- MySQL 5/8通用语法（8.0默认utf8mb4，5.7需显式指定）
CREATE DATABASE IF NOT EXISTS mydb 
  DEFAULT CHARACTER SET utf8mb4  -- 字符集（支持emoji）
  COLLATE utf8mb4_unicode_ci;    -- 排序规则（不区分大小写）

2. 查看所有数据库

SHOW DATABASES;  -- 列出当前MySQL实例的所有数据库

3. 切换数据库

USE mydb;  -- 切换到mydb数据库（后续操作默认在该库执行）

4. 删除数据库（⚠️危险操作！）

DROP DATABASE IF EXISTS mydb;  -- 删除mydb数据库（谨慎使用！）

4.2 表操作（核心！）

1. 创建表（CREATE TABLE）

实例：创建一个学生表（student），包含学号、姓名、年龄、性别、入学时间。

CREATE TABLE IF NOT EXISTS student (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '学号（主键，自增）',
  name VARCHAR(50) NOT NULL COMMENT '姓名（非空）',
  age TINYINT UNSIGNED COMMENT '年龄（无符号，0-255）',  -- MySQL 5/8通用
  gender ENUM('男', '女', '未知') DEFAULT '未知' COMMENT '性别（枚举类型）',  -- 5/8通用
  enroll_date DATE COMMENT '入学时间',
  -- MySQL 8.0特有：JSON类型存储额外信息（如爱好）
  extra_info JSON COMMENT '额外信息（JSON格式，8.0新增支持）'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '学生表';

字段类型说明（常用）：

• INT：整数（4字节，范围-21亿~21亿）；BIGINT：长整数（8字节）。
• VARCHAR(n)：可变长度字符串（n最大65535，需指定长度）；CHAR(n)：固定长度字符串（适合短文本）。
• DATE：日期（YYYY-MM-DD）；DATETIME：日期时间（YYYY-MM-DD HH:MM:SS）；TIMESTAMP：时间戳（自动更新）。
• TINYINT：小整数（1字节，范围-128~127）；DECIMAL(m,n)：精确小数（如DECIMAL(10,2)表示共10位，2位小数）。

约束说明：

• PRIMARY KEY：主键（唯一标识一条记录，非空且唯一）。
• AUTO_INCREMENT：自增（仅适用于数值类型主键，MySQL自动维护）。
• NOT NULL：字段值不能为空。
• UNIQUE：字段值唯一（允许NULL，但NULL只能出现一次）。
• DEFAULT：默认值（如gender默认“未知”）。

2. 查看表结构

DESC student;  -- 简洁查看表字段信息（5/8通用）
-- 或
SHOW CREATE TABLE student;  -- 详细查看建表语句（含引擎、字符集等）

3. 修改表结构（ALTER TABLE）

场景1：添加字段

-- 给学生表添加“邮箱”字段（允许为空）
ALTER TABLE student ADD COLUMN email VARCHAR(100) COMMENT '邮箱';

场景2：修改字段类型/约束

-- 将age字段类型从TINYINT改为SMALLINT（2字节，范围更大）
ALTER TABLE student MODIFY COLUMN age SMALLINT UNSIGNED;

场景3：删除字段

-- 删除extra_info字段（MySQL 8.0新增的JSON字段）
ALTER TABLE student DROP COLUMN extra_info;

场景4：添加索引（后续“索引原理”会详述，此处先了解语法）

-- 为name字段添加普通索引（加速查询）
ALTER TABLE student ADD INDEX idx_name (name);

4. 删除表（DROP TABLE）

DROP TABLE IF EXISTS student;  -- 删除学生表（⚠️危险！先备份数据）

4.3 避坑指南：DDL常见错误

• 坑1：字段名用关键字（如order、user）导致报错。
  • 解决：用反引号包裹字段名，如`order` INT。
• 坑2：修改表结构时锁表（MySQL 5.7前ALTER TABLE会锁表，影响业务）。
  • 解决：MySQL 8.0支持在线DDL（如ALGORITHM=INPLACE），减少锁表时间。

五、DML：数据增删改（CRUD核心）

5.1 插入数据（INSERT）

1. 单行插入

-- 插入一条学生记录（字段顺序与表结构一致时可省略字段名）
INSERT INTO student (name, age, gender, enroll_date) 
VALUES ('张三', 20, '男', '2023-09-01');

-- 插入一条带JSON字段的记录（仅MySQL 8.0支持）
INSERT INTO student (name, age, extra_info) 
VALUES ('李四', 22, '{"hobby": ["篮球", "阅读"], "score": 95}');

2. 多行插入（效率更高）

INSERT INTO student (name, age, gender) VALUES 
  ('王五', 21, '男'),
  ('赵六', 20, '女'),
  ('钱七', 23, '未知');

3. 避坑指南

• 坑：插入数据与字段类型不匹配（如字符串插入INT字段）。
  • 解决：确保数据类型一致，或用CAST()函数转换（如CAST('20' AS INT)）。

5.2 查询数据（SELECT，DQL核心）

1. 基础查询（全表/指定字段）

-- 查询所有字段（*慎用！效率低，尤其大表）
SELECT * FROM student;

-- 查询指定字段（推荐！明确所需数据）
SELECT name, age, enroll_date FROM student;

2. 条件查询（WHERE子句）

-- 查询年龄>20的学生（比较运算符：>, <, >=, <=, =, !=/<>
SELECT name, age FROM student WHERE age > 20;

-- 查询性别为“女”且年龄<22的学生（逻辑运算符：AND, OR, NOT）
SELECT * FROM student WHERE gender = '女' AND age < 22;

-- 查询姓名包含“张”的学生（模糊查询：LIKE + %通配符，%匹配任意字符）
SELECT * FROM student WHERE name LIKE '张%';  -- 姓张的（张XX）
SELECT * FROM student WHERE name LIKE '%三';  -- 名含三的（X三）

3. 排序（ORDER BY）

-- 按年龄升序（ASC，默认）/降序（DESC）排列
SELECT name, age FROM student ORDER BY age ASC;  -- 从小到大
SELECT name, age FROM student ORDER BY age DESC; -- 从大到小

4. 限制返回行数（LIMIT，分页查询核心）

-- 查询前3条记录（MySQL 5/8通用）
SELECT * FROM student LIMIT 3;

-- 分页查询：第2页，每页2条（公式：(页码-1)*每页条数）
SELECT * FROM student LIMIT 2 OFFSET 2;  -- OFFSET后接偏移量（跳过前2条）
-- 等价于（MySQL特有简写）
SELECT * FROM student LIMIT 2, 2;  -- 第一个数字是偏移量，第二个是条数

5. 聚合查询（GROUP BY + 聚合函数）

常用聚合函数：COUNT()（计数）、SUM()（求和）、AVG()（平均）、MAX()（最大）、MIN()（最小）。

-- 统计学生总数（COUNT(*)统计所有行，包括NULL；COUNT(字段)忽略NULL）
SELECT COUNT(*) AS total_students FROM student;

-- 按性别分组，统计每组人数和平均年龄
SELECT gender, 
       COUNT(*) AS count, 
       AVG(age) AS avg_age 
FROM student 
GROUP BY gender;  -- 按gender字段分组

6. 避坑指南

• 坑1：WHERE与HAVING混淆。WHERE过滤行（分组前），HAVING过滤组（分组后）。

  -- 错误：WHERE不能直接用聚合函数
  SELECT gender, AVG(age) FROM student WHERE AVG(age) > 20 GROUP BY gender;
  
  -- 正确：用HAVING过滤分组结果
  SELECT gender, AVG(age) AS avg_age FROM student GROUP BY gender HAVING avg_age > 20;
  

• 坑2：LIMIT大偏移量效率低（如LIMIT 10000, 10）。
  • 解决：用“书签查询”（记录上一页最后一条记录的ID），如WHERE id > 10000 LIMIT 10。

5.3 更新数据（UPDATE）

-- 将“张三”的年龄改为21（⚠️务必加WHERE条件！否则全表更新）
UPDATE student SET age = 21 WHERE name = '张三';

-- 同时更新多个字段（用逗号分隔）
UPDATE student SET age = age + 1, enroll_date = '2023-09-02' WHERE id = 1;

5.4 删除数据（DELETE/TRUNCATE）

1. 删除指定记录（DELETE）

-- 删除id=3的学生记录（⚠️务必加WHERE条件！）
DELETE FROM student WHERE id = 3;

2. 清空表（TRUNCATE）

TRUNCATE TABLE student;  -- 清空表中所有数据（保留表结构，自增ID重置）

DELETE vs TRUNCATE：

• DELETE逐行删除（可回滚，日志记录详细），TRUNCATE直接删除数据页（速度快，不可回滚）。
• 大表清空优先用TRUNCATE（效率高），但需注意数据备份。

六、多表关联查询（JOIN）

实际业务中，数据通常分散在多个表中（如学生表、课程表、成绩表），需通过关联查询整合数据。

6.1 表关系与ER图

• 一对一：如“学生”与“学生证”（一个学生一个证）。
• 一对多：如“班级”与“学生”（一个班级多个学生）——最常见。
• 多对多：如“学生”与“课程”（一个学生选多门课，一门课多个学生）——需中间表（如“选课表”）。

ER图（实体-关系图）：可视化表关系的工具（可用Draw.io绘制），例如：

班级表（class）      学生表（student）      课程表（course）
+----+--------+      +----+------+--------+  +----+--------+
| id | name   | ---| id | name | class_id|  | id | name   |
+----+--------+      +----+------+--------+  +----+--------+
                           |                |
                           |                |
                    选课表（sc）          |
                  +----+---------+-------+
                  | id | stu_id  | course_id |
                  +----+---------+-------+

6.2 JOIN语法与实例

核心JOIN类型：

• INNER JOIN（内连接）：只返回两表中匹配的记录（交集）。
• LEFT JOIN（左连接）：返回左表所有记录，右表无匹配则显示NULL（左表全集）。
• RIGHT JOIN（右连接）：返回右表所有记录，左表无匹配则显示NULL（右表全集）。

实例：查询学生及其所在班级名称（假设有班级表class，学生表student通过class_id关联）。

-- 内连接：只返回有班级的学生
SELECT s.name AS student_name, c.name AS class_name
FROM student s
INNER JOIN class c ON s.class_id = c.id;  -- ON指定关联条件

-- 左连接：返回所有学生（即使未分配班级）
SELECT s.name AS student_name, c.name AS class_name
FROM student s
LEFT JOIN class c ON s.class_id = c.id;

七、数据库设计基础：范式与反范式（扩展详解+比喻）

7.1 为什么要学范式？

想象你要整理一个杂乱的仓库：货物乱堆、重复存放、找一件东西要翻遍整个仓库——这就是“非规范化”的数据表。而范式就是一套“仓库整理手册”，通过规范数据存储方式，减少冗余、避免混乱，让数据像图书馆的书架一样整齐有序。

范式的核心是“一事一地”：每个数据只存一份，每个字段只描述一件事。下面用生活比喻+实例，详解三大范式。

7.2 第一范式（1NF）：原子性——“每个格子只放一种东西”

定义

字段不可再分，即每个字段必须是“原子值”（不可拆分的单一数据），不能有多个值或复合结构。

比喻

把数据库表比作衣柜，1NF要求：每个抽屉（字段）只能放一种物品，不能把“上衣+裤子+袜子”塞进同一个抽屉。

反例（违反1NF）

假设有一个“学生信息表”，其中“联系方式”字段同时存电话和邮箱：

学号	姓名	联系方式
1	张三	13800138000, zhang@xx.com

问题：想单独查“电话”或“邮箱”时，需要拆分字符串（如用SUBSTRING_INDEX），效率低且易出错。

正例（满足1NF）

拆分“联系方式”为两个独立字段：

学号	姓名	电话	邮箱
1	张三	13800138000	zhang@xx.com

7.3 第二范式（2NF）：完全依赖——“零件不能只靠半个框架”

定义

在满足1NF的基础上，非主键字段必须完全依赖于主键（不能只依赖主键的一部分）。

比喻

把“订单详情表”比作组装电脑：主键是“订单ID+商品ID”（相当于“机箱+主板”的组合框架），非主键字段（如商品名称、价格）必须同时依赖“订单ID”和“商品ID”——就像CPU必须同时装在机箱和主板上才能工作，不能只靠机箱或只靠主板。

反例（违反2NF）

假设有一个“订单详情表”，主键是“订单ID+商品ID”，但“商品名称”只依赖“商品ID”（不依赖订单ID）：

订单ID	商品ID	商品名称	单价	数量
1001	A001	键盘	99	2
1001	A002	鼠标	59	1
1002	A001	键盘	99	1

问题：

• 冗余：“键盘”“鼠标”的名称和单价重复存储（订单1001和1002都存了“键盘”）。
• 更新异常：若“键盘”涨价到129，需修改所有含A001的行，漏改则数据不一致。
• 插入异常：新商品未下单时，无法录入其名称和单价（因缺少订单ID）。

正例（满足2NF）

拆分表：

• 订单表（主键：订单ID）：存订单总金额、下单时间等（依赖整个主键）。
• 商品表（主键：商品ID）：存商品名称、单价等（依赖商品ID，与订单无关）。
• 订单详情表（主键：订单ID+商品ID）：仅存数量（完全依赖整个主键）。

订单表		商品表		订单详情表
订单ID	总金额	下单时间	商品ID	名称	单价
1001	257	2023-09-01	A001	键盘	99

7.4 第三范式（3NF）：无传递依赖——“别让员工通过部门问领导”

定义

在满足2NF的基础上，非主键字段不能传递依赖于主键（即不能通过其他非主键字段间接依赖主键）。

比喻

把“员工表”比作公司通讯录：主键是“员工ID”，非主键字段（如部门名称、部门经理）中，“部门经理”依赖“部门名称”，“部门名称”依赖“员工ID”——这就像员工想知道领导是谁，得先问“我在哪个部门”，再通过部门查领导，多绕了一层。3NF要求：员工直接存领导ID，不用通过部门中转。

反例（违反3NF）

假设“学生表”中，“班主任姓名”依赖“班级名称”，“班级名称”依赖“学号”（主键）：

学号	姓名	班级名称	班主任姓名
1	张三	一班	李老师
2	李四	一班	李老师

问题：

• 冗余：“一班”的班主任“李老师”重复存储（所有一班学生都存一遍）。
• 更新异常：若李老师离职，需修改所有一班学生的“班主任姓名”。

正例（满足3NF）

拆分表：

• 学生表（主键：学号）：存姓名、班级ID（直接依赖学号）。
• 班级表（主键：班级ID）：存班级名称、班主任姓名（班主任姓名直接依赖班级ID，不依赖学生）。

学生表		班级表
学号	姓名	班级ID	班级ID
1	张三	C01	C01

7.5 反范式设计：“偶尔的冗余是为了更快找到书”

范式虽好，但过度规范化会导致查询时需关联多表（如查“学生+班级+班主任”需关联3张表），效率下降。此时可采用反范式：适当冗余字段，减少关联。

比喻

图书馆的书架按“作者+书名”严格分类（范式），但热门书会在“新书区”再放一本（冗余）——读者不用跑遍整个图书馆就能找到。

实例

在“学生表”中冗余“班级名称”（原本需关联班级表），查询时直接取：

学号	姓名	班级ID	班级名称（冗余）	班主任姓名（冗余）

注意：冗余字段需通过触发器或代码保证一致性（如班级名称修改时，同步更新所有学生的冗余字段）。

八、总结与展望

本文从环境搭建到SQL核心操作，详细讲解了MySQL基础：

• 掌握DDL（库表创建/修改）、DML（CRUD）、DQL（查询优化），能独立完成数据管理；
• 理解MySQL 5与8的差异（字符集、认证插件、JSON支持等），避开常见坑点；
• 学会多表关联查询和数据库设计范式（用“衣柜整理”“电脑组装”“通讯录”等比喻轻松理解），为业务逻辑实现打基础。

但这只是MySQL的“冰山一角”！后续我们将深入学习索引原理（B+树、索引优化）、事务与锁机制（ACID、隔离级别）、高可用架构（主从复制、读写分离）等高级内容，助你成为MySQL专家。

下一篇预告：《MySQL索引原理与查询优化实战——从B+树到执行计划分析》，敬请期待！

附录：常用命令速查表

类别	命令	作用
登录	mysql -u root -p	登录MySQL（输入密码）
退出	exit 或 \q	退出MySQL命令行
查看库	SHOW DATABASES;	列出所有数据库
查看表	SHOW TABLES;	列出当前库所有表
查看进程	SHOW PROCESSLIST;	查看当前数据库连接
备份	mysqldump -u root -p dbname > backup.sql	逻辑备份数据库

通过本文的学习，相信你已对MySQL基础有了扎实掌握。动手实践是巩固知识的最佳方式，快创建自己的数据库，尝试用SQL语句管理数据吧！ 🚀