Mysql

一、数据库基础操作（创建 / 修改 / 删除 / 查看） 1.1 字符集 & 校对规则 字符集 ：决定 “文字怎么存”，比如： GB2312：简体中文 UTF-8 ：全世界语言（最推荐） 校对规则 ：决定 “怎么排序、怎么比较”，比如中文不区分大小写、按拼音排。

常用字符集： 1. ASCII：7位二进制，仅支持英文、数字、基础符号，共128个字符。

2. GB2312：简体中文国家标准，收录7445个汉字及符号。

3. UTF\-8：通用Unicode字符集，支持全球语言，MySQL推荐使用。

一句话：字符集管存，校对规则管比。

1.2 创建数据库 基本结构： CREATE DATABASE 数据库名 DEFAULT CHARACTER SET 字符集名 COLLATE 校对规则名;

CREATE DATABASE Bookstore

DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci; 1.3 修改数据库 基本结构： ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 字符集名 DEFAULT COLLATE 校对规则名;

ALTER DATABASE Bookstore

DEFAULT CHARACTER SET utf8;

改字符集 / 排序规则。

1.4 删除数据库 基本规则： DROP DATABASE [IF EXISTS] 数据库名;

区别：如果删除一个不存在的表不加if exists,会报错，如果不想报错，可以加上

1.5 查看 / 切换 基本结构： SHOW DATABASES; -- 查看所有数据库 USE 数据库名; -- 切换当前数据库

SHOW DATABASES; -- 看所有库

USE Bookstore; -- 进入这个库 二、数据表操作（表结构） 2.1 数据类型（很关键） 数据表的核心是“字段\+数据类型”，MySQL数据类型分为三大类： 数值

INT：整数（年龄、数量） FLOAT(M,D)：小数（单价） DECIMAL(M,D)：精确小数（钱、金额）

字符串

CHAR(n)：固定长度，不足补空格（编号、身份证） VARCHAR(n)：变长，省空间（书名、地址） TEXT：大段文字 BLOB：图片、文件

日期

DATE：2026-05-23 DATETIME：2026-05-23 14:30:00

2.2 创建表 基本结构： CREATE TABLE [IF NOT EXISTS] 表名( 字段名1 数据类型 [约束条件], 字段名2 数据类型 [约束条件], ... ) ENGINE=存储引擎;

CREATE TABLE book (

图书编号 CHAR(20) NOT NULL PRIMARY KEY, 书名 VARCHAR(40) NOT NULL, 单价 FLOAT(5,2) ) ENGINE=InnoDB; FLOAT(5,2)：最多 5 位数字 ，其中小数点后占 2 位 ps: 如果是存金额、价格，用

DECIMAL(5,2)

MySQL 常用表约束总结 1. 主键约束 PRIMARY KEY 唯一且非空，一张表 只能一个主键

id INT PRIMARY KEY

2. 自增约束 AUTO_INCREMENT 数值自动递增，搭配主键使用

id INT PRIMARY KEY AUTO_INCREMENT

3. 非空约束 NOT NULL 字段 必须填值 ，不能为空

name VARCHAR(20) NOT NULL

4. 唯一约束 UNIQUE 字段值 不能重复 ，允许为空

phone VARCHAR(11) UNIQUE

5. 默认约束 DEFAULT 不赋值时自动填充默认值

sex CHAR(2) DEFAULT '男'

6. 外键约束 FOREIGN KEY 关联两张表，保证数据参照完整性

FOREIGN KEY(uid) REFERENCES user(id)

7. 枚举约束 ENUM 只能取指定范围内的值

status ENUM('正常','禁用')

8. 检查约束 CHECK 限定字段取值范围

age INT CHECK(age>0 && age<150)

9. 零填充 ZEROFILL 数值不足位数左侧补 0，默认搭配无符号

num INT(4) ZEROFILL

10. 无符号 UNSIGNED 数值只存非负数，数值范围翻倍

age INT UNSIGNED

11. 注释约束 COMMENT 给字段 / 表添加备注说明

name VARCHAR(20) COMMENT '用户姓名'

12. 索引约束 INDEX 加速查询，可重复可空

INDEX idx_name(name)

13. 全文索引 FULLTEXT 适配大文本模糊检索

FULLTEXT INDEX ft_content(content)

14.CURRENT_TIMESTAMP 时间戳关键字 获取 当前系统时间 ，格式：

yyyy-MM-dd HH:mm:ss

插入数据自动取当前时间

create table test(

id int primary key auto_increment, create_time datetime default CURRENT_TIMESTAMP );

修改数据自动更新时间

update_time datetime default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP

数据改动时，自动刷新为最新时间。

MyBatis-Plus 框架

fill = FieldFill.INSERT

：表示这个字段 在插入数据时自动填充 。

fill = FieldFill.INSERT_UPDATE

：表示这个字段 在插入和更新数据时都自动填充 。

如何在实际项目中选择合适的约束？

一、快速选型判断 唯一标识每条数据 → 用 主键 PRIMARY KEY 用户 ID、订单 ID 必设主键，搭配自增。

字段不能为空 → NOT NULL 用户名、手机号、订单金额必填项。

数据不能重复 → UNIQUE 手机号、邮箱、身份证、账号。

不填就给默认值 → DEFAULT 状态默认正常、创建时间默认当前时间。

数值不能负数 → UNSIGNED 年龄、数量、积分、金额。

限定固定选项 → ENUM/CHECK 性别、订单状态、账号状态。

多张表关联数据 → 外键 FOREIGN KEY 订单关联用户、商品关联分类； 互联网项目大多禁用外键 ，代码层控制关联。

查询慢、经常搜索 → INDEX 索引 搜索字段、查询条件字段建索引。

二、业务场景直接套用 1. 用户表 id：主键 + 自增 username：唯一 + 非空 phone：唯一 + 非空 age：无符号 sex：枚举 status：默认正常 2. 订单表 order_id：主键自增 user_id：关联用户，代码维护关系 pay_status：枚举状态 create_time：默认当前时间 money：无符号 3. 分类表 分类 ID 主键 分类名唯一非空 三、避坑选型规则 主键必加 ，每张表都要有主键 必填字段一律加 NOT NULL ，减少脏数据 账号、手机号这类唯一数据加 UNIQUE 防重复注册 金额、数量用 UNSIGNED 杜绝负数 固定状态用枚举，不用零散数字 高并发项目不用外键 ，业务代码做关联校验 查询频繁字段建索引，别乱加索引影响写入速度 2.3 修改表 基本规则：都是用alter (增加add ,删除drop column,修改modify,重命名to) 1. 添加字段 ALTER TABLE 表名 ADD 字段名 数据类型; 2. 删除字段 ALTER TABLE 表名 DROP COLUMN 字段名; 3. 修改字段类型 ALTER TABLE 表名 MODIFY 字段名 新数据类型; 4. 重命名表 ALTER TABLE 旧表名 RENAME TO 新表名; -- 或 RENAME TABLE 旧表名 TO 新表名; 2.4复制表 基本结构： CREATE TABLE 新表 LIKE 原表;测试表结构，不复制数据

CREATE TABLE 新表 AS (SELECT * FROM 原表);

数据备份

2.5删除表 基本结构： DROP TABLE [IF EXISTS] 表名;

2.6查看数据表 基本结构：DESC 表名;

查看当前数据库所有表：SHOW TABLES；

三、 数据操纵（插入、修改、删除） 3.1 插入数据（INSERT） 基本结构： -- 全字段插入 INSERT INTO 表名 VALUES(值1,值2,...); -- 指定字段插入 INSERT INTO 表名(字段1,字段2,...) VALUES(值1,值2,...); -- 插入多条数据 INSERT INTO 表名 VALUES(值1,值2),(值3,值4),...; 核心规则： 字符 / 日期值必须用 单引号 包裹（例：

'2010-08-16'

）； 主键值必须唯一（重复会报错）； 可指定字段插入（不用按顺序，缺省字段取默认值 / NULL）

3.2 修改数据（UPDATE） 必加 WHERE 条件 ！

否则会修改全表数据（生产环境大忌） 基本结构： UPDATE 表名 SET 字段名=新值 [,字段名=新值...] [WHERE 条件]; -- 无WHERE则修改全表数据 有where

无where

3.3 删除数据（DELETE/TRUNCATE） 基本结构： DELETE FROM 表名 [WHERE 条件]; -- 无WHERE则清空全表（不重置自增 ID） TRUNCATE TABLE 表名;（直接清表，速度快，重置自增 ID） 四 、数据查询 语法骨架（按执行顺序记）：

SELECT 字段 -- 5.选字段

FROM 表名 -- 1.选表 WHERE 条件 -- 2.行过滤 GROUP BY 字段 -- 3.分组 HAVING 条件 -- 4.分组过滤 ORDER BY 字段 -- 6.排序 LIMIT 行数; -- 7.限制结果 4.1 单表查询基础 基本结构： SELECT [DISTINCT] 字段1,字段2,... | * FROM 表名 [WHERE 条件] [GROUP BY 分组字段] [HAVING 分组过滤条件] [ORDER BY 排序字段 ASC/DESC] [LIMIT 偏移量,行数]; 不能直接写普通字段（book_id、book_name），否则一定报错！

基础查询 ：

如果要去重后返回所有字段可以用group+ANY_VALUE()

或者 窗口函数（有点不能理解）

SELECT *

：查所有字段（测试用，生产避免，效率低）；

DISTINCT

：去重（例：

SELECT DISTINCT 图书类别 FROM book;

）； 别名：

AS

（例：

SELECT 书名 AS 图书名称 FROM book;

）。

条件查询（WHERE） ： 条件类型运算符案例 比较>、<、=、!=单价>30逻辑AND、OR、NOT单价>30 AND 类别='计算机'模糊查询LIKE作者 LIKE '张%'（姓张的作者）范围BETWEEN...AND出版时间 BETWEEN '2010-01-01' AND '2010-12-31' 聚合函数（统计） ： 把多行数据，合并计算成一个结果 （求和、统计、最大、最小、平均...）

COUNT：数个数 SUM：求和 AVG：求平均 MAX：最大 MIN：最小 SELECT

COUNT(*) AS 总图书数, AVG(单价) AS 平均单价, MAX(单价) AS 最贵图书 FROM book;

4.3 分组查询（GROUP BY \+ HAVING）

GROUP BY

：按字段分组（例：按图书类别分组）；

HAVING

：过滤分组结果（只能跟 GROUP BY，区别于 WHERE）。

在

GROUP BY

查询里： SELECT 后面只能写 2 种东西： 分组字段 （比如

book_type

） 聚合函数 （

COUNT、SUM、MAX、ANY_VALUE

）

-- 按类别统计，只显示库存>10的类别

SELECT 图书类别, COUNT(*) AS 库存数 FROM book GROUP BY 图书类别 HAVING 库存数>10;

4.4排序查询（ORDER BY） 基本结构： `ASC`：升序（默认） `DESC`：降序

4.5 限制查询结果（LIMIT）

4.6 多表查询 等值连接（WHERE）

SELECT book.书名, sell.订购册数

FROM book, sell WHERE book.图书编号=sell.图书编号; INNER JOIN（内连接）

SELECT book.书名, sell.订购册数

FROM book INNER JOIN sell ON book.图书编号=sell.图书编号; 左连接（LEFT JOIN）显示左表所有数据，右表无匹配则为`NULL`：

SELECT book.书名, sell.订购册数

FROM book LEFT JOIN sell ON book.图书编号=sell.图书编号; 左连接，右连接，等值连接，内连接有什么区别，使用场景 内连接（INNER JOIN）= 等值连接（=）： 只查两边都能匹配上的数据！

左连接（LEFT JOIN）： 左边表（user）全部保留 右边表能匹配就显示，匹配不上 → 填 NULL

SELECT * FROM user

LEFT JOIN order ON user.id = order.user_id;

使用场景 要显示所有用户 ，不管有没有订单 统计每个用户的订单数（包括 0 单） 最常用！

90% 连表都用左连接 3. 右连接（RIGHT JOIN）： 右边表（order）全部保留 左边表匹配不上 → 填 NULL

SELECT * FROM user

RIGHT JOIN order ON user.id = order.user_id;

使用场景 要 所有订单 ，哪怕订单对应的用户被删了 实际开发很少用 ，因为可以换成左连接更直观 4.7 子查询 把一个查询结果当条件用（“嵌套查询”）：

-- 查买过书的会员姓名

SELECT 会员姓名 FROM members WHERE 身份证号 IN (SELECT 身份证号 FROM sell); 五、 视图（VIEW） 核心本质：虚拟表（存储的是查询语句，不是数据） 优点 ：简化复杂查询、屏蔽敏感字段（例：只给用户看 “书名 + 单价”，隐藏 “成本”）； 创建语法 ：

CREATE VIEW 视图名 AS

SELECT 字段1,字段2 FROM 表名 WHERE 条件;

使用 ：和查普通表一样（

SELECT * FROM 视图名;

）； 注意 ：视图可更新的前提 —— 无聚合函数、GROUP BY、DISTINCT，且是 单表查询 。

视图和表在实际开发中的应用场景有哪些？

表 = 存真实业务数据的地方所有需要增删改查的业务，都必须用表。

视图 = 封装复杂查询，简化查询，不存数据适合只查不改、经常重复查询的场景。

六、索引与数据完整性（性能 + 数据合法性） 6.1 索引作用 - 加快查询速度，避免全表扫描； - 索引是独立的文件，会占用磁盘空间，仅在查询频繁的字段创建。

6.2 索引分类 1. 普通索引（INDEX）：最基础索引，无唯一性限制； 2. 唯一索引（UNIQUE）：索引列值唯一，可空； 3. 主键索引（PRIMARY KEY）：唯一\+非空，一张表仅一个； 4. 复合索引：多个字段组合的索引。

一张表记住所有索引使用场景（必背） 索引类型特点使用场景 普通索引 INDEX无限制、加速查询经常查询的普通字段（分类、价格）唯一索引 UNIQUE唯一、可空用户名、手机号、订单号（业务唯一）主键索引 PRIMARY KEY唯一 + 非空表 ID，每张表必有一个复合索引多字段组合多条件组合查询、列表筛选、联合查询 6.3创建索引 1. 创建表时创建 CREATE TABLE book( 图书编号 CHAR(20) PRIMARY KEY, -- 主键索引 书名 VARCHAR(40), INDEX name_book(书名(6)) -- 普通索引（前6个字符） ); 2. 已存在表创建索引(常用） CREATE INDEX 索引名 ON 表名(字段名); 3. ALTER TABLE添加索引 ALTER TABLE book ADD INDEX sm_book(书名); 6.4 删除索引 DROP INDEX 索引名 ON 表名; ALTER TABLE 表名 DROP PRIMARY KEY; -- 删除主键 6.5索引命名规则 索引类型 + 表名 + 字段名 idx_ + 字段名（多字段用 _ 连接） 1. 普通索引（最常用） 前缀：idx_

idx_字段名

idx_字段1_字段2_字段3

2. 唯一索引 前缀：uk_= unique key

uk_字段名

constraint=约束 约束名称英文作用例子 主键约束PRIMARY KEY唯一 + 非空，每张表只能一个id唯一约束UNIQUE字段值不能重复email, username, phone非空约束NOT NULL字段不能为空password, username外键约束FOREIGN KEY确保关联表数据存在product.category_id → category.id

保证username /email/phone 不能重复 自动创建唯一索引 索引名系统自动生成（不用你管） 约束 vs 索引 约束 = 规矩（限制数据） 索引 = 提速（优化查询） 但注意： UNIQUE 约束 = 会自动创建唯一索引 PRIMARY KEY 约束 = 自动创建主键索引 FOREIGN KEY 约束 = 自动创建普通索引 3. 主键索引 系统自动命名：

PRIMARY

4. 外键索引 前缀：fk_

fk_子表字段_父表

为什么不用外键？

太严格，影响业务灵活性 高并发下性能差 删数据会报错、连锁报错 分库分表不支持外键 索引命名3 条黄金法则 全部小写 单词之间用下划线分隔 见名知意 七、 数据库编程（存储过程、函数、触发器） 7.1 存储过程（PROCEDURE） 核心概念： 预编译的SQL语句集合，存储在数据库中，一次编译多次执行，提升效率。

7.1.1 创建存储过程 DELIMITER $$ -- 修改结束符为$$ CREATE PROCEDURE 存储过程名 (IN 输入参数, OUT 输出参数) BEGIN -- SQL语句 END $$ DELIMITER ; -- 恢复结束符为; - `IN`：输入参数；`OUT`：输出参数；`INOUT`：输入输出参数。

创建删除会员的存储过程

-- 创建：删除指定身份证号的会员

DELIMITER $$ -- 临时修改结束符（避免和SQL的;冲突） CREATE PROCEDURE del_member(IN sfz CHAR(18)) BEGIN DELETE FROM members WHERE 身份证号=sfz; END $$ DELIMITER ; -- 恢复结束符 7.1.2 调用存储过程 -- 调用 CALL del_member('4301031962010101388'); 7.1.3 删除存储过程 DROP PROCEDURE 存储过程名; 7.2 存储函数（FUNCTION） - 必须有返回值 ，不能用CALL调用，直接在 SELECT 中使用。

CREATE FUNCTION 函数名(参数) RETURNS 返回类型 BEGIN RETURN 返回值; END 案例：统计图书总数的函数

-- 创建：统计图书总数

CREATE FUNCTION num_book() RETURNS INT BEGIN RETURN (SELECT COUNT(*) FROM book); END;

-- 调用 SELECT num_book(); - 7.3 触发器（TRIGGER） 核心概念 - 表发生` INSERT/UPDATE/DELETE `时 自动执行的SQL 程序。

- 触发时间：`BEFORE`（执行前）、`AFTER`（执行后）。

CREATE TRIGGER 触发器名 触发时间 触发事件 ON 表名 FOR EACH ROW BEGIN -- 执行语句 END 案例：删除图书时，级联删除销售记录

CREATE TRIGGER book_del AFTER DELETE

ON book FOR EACH ROW BEGIN DELETE FROM sell WHERE 图书编号=OLD.图书编号; -- OLD=删除前的图书编号 END; - `OLD`：触发前的数据；`NEW`：触发后的数据。

什么时候使用？

大数据量计算、统计、报表 自动触发、批量处理 1. 复杂统计、报表、大数据量计算（最常用！

） 比如： 统计每月销售额 统计用户消费排行 统计商品销量报表 2. 批量处理大量数据 比如： 批量更新 10 万条商品价格 批量给用户发优惠券 月底批量结算佣金 3. 自动化操作（触发器 Trigger） 比如： 用户下单 → 自动扣库存 商品被删除 → 自动记录日志 订单支付 → 自动生成物流单 4.重复使用的公共逻辑（函数 Function） 比如： 计算商品折扣价 格式化手机号、身份证 通用状态转换 5. 定时任务（定时 SQL） 比如： 每天凌晨统计昨日订单 每周清理无效购物车 每月自动结算佣金 八、数据库管理（安全 + 备份 + 事务） 8.1 用户管理 8.1.1 创建用户 CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'; - 主机名：`localhost`（本地）、`%`（所有主机）。

8.1.2 删除用户 DROP USER '用户名'@'主机名'; 8.1.3 修改密码 SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码'); 8.2 权限管理 8.2.1 授予权限（GRANT） GRANT 权限 ON 数据库.表 TO '用户名'@'主机名'; - 常用权限：`SELECT`、`INSERT`、`UPDATE`、`DELETE`、`ALL`（所有权限）。

案例 授予`user1`查询`book`表的权限： GRANT SELECT ON Bookstore.book TO 'user1'@'localhost'; 8.2.2 回收权限（REVOKE） REVOKE 权限 ON 数据库.表 FROM '用户名'@'主机名'; 8.3 数据库备份与恢复 8.3.1 数据导出（备份） SELECT * FROM book INTO OUTFILE 'D:/book_backup.txt'; 8.3.2 数据导入（恢复） LOAD DATA INFILE 'D:/book_backup.txt' INTO TABLE book; 数据库备份，既可以备份整个库，也可以只备份单张表！

一、3 种备份级别（你必须知道） 1.全库备份（最常用） 备份这个数据库里 所有表 + 所有数据

mysqldump -uroot -p shopping > all.sql

✅ 适合：整库迁移、整库恢复、日常定时备份 2. 单表备份（只备份某一张表） 只备份

z_user

这张表

mysqldump -uroot -p shopping z_user > user.sql

✅ 适合：只需要恢复 / 迁移某一张表，不影响其他表 3. 整台服务器备份（所有库） 备份 MySQL 里 所有数据库

mysqldump -uroot -p --all-databases > all_dbs.sql

备份（导出）

mysqldump -uroot -p 库名 > 备份文件.sql

恢复（导入）

mysql -uroot -p 库名 < 备份文件.sql

最核心区别（一张表看懂） 方式导出内容能否恢复表结构使用场景 SELECT INTO OUTFILE只有纯数据❌ 不能只需要快速导出 / 导入数据mysqldump表结构 + 数据 + 索引 + 约束✅ 能数据库备份、迁移、恢复 8.4 事务（TRANSACTION） 8.4.1 事务特性（ACID） - 原子性：要么全执行，要么全不执行； - 一致性：数据前后状态一致； - 隔离性：并发事务互不干扰； - 持久性：提交后数据永久保存。

8.4.2 事务操作 START TRANSACTION; -- 开启事务 -- SQL增删改语句 COMMIT; -- 提交事务 ROLLBACK; -- 回滚事务（撤销操作） 外键和连表查询有什么区别？

外键 = 数据库里的【约束 / 规矩】 （用来保证数据不乱、不无效） 连表查询 = 拿数据时的【查询方式】 （用来把多张表的数据一起查出来） 虚拟机操作数据库遇到的问题

服务可能没有启动 检查服务是否启动

systemctl status mysqld

# 或 systemctl status mysql

启动服务

systemctl start mysqld

3. 开机自启（可选）

systemctl enable mysqld

4. 启动后重新登录

mysql -u root -p

成功登录

补充排查 若启动失败，查看日志：

journalctl -xeu mysqld

语法有误 我这里是出现了结束没有写 ；

MySQL 8.0 / 5.7 的默认严格模式ONLY_FULL_GROUP_BY导致的 ，它不允许

GROUP BY

后直接写

*

。

MySQL 是关闭状态 → 没运行 → 项目连不上数据库