📋 知识地图

说明：✅ = 已有覆盖（可继续深化）　⬜ = 待扩充

一、数据库基础概念

• ✅ DB / DBMS / SQL 的定义与关系
• ✅ 数据库持久化 vs 内存存储
• ✅ MySQL 服务启停与连接
• ⬜ 关系型数据库核心：表 / 行 / 列 / 主键 / 外键
• ⬜ ACID 事务特性（原子性 / 一致性 / 隔离性 / 持久性）
• ⬜ CAP 定理简介（一致性 / 可用性 / 分区容错）
• ⬜ 关系型 vs 非关系型数据库的本质区别

二、主流数据库产品对比

• ✅ MySQL / Oracle / DB2 / SQL Server 对比表
• ⬜ PostgreSQL 特点与优势
• ⬜ SQLite 轻量级嵌入式数据库
• ⬜ 选型决策：什么时候用哪个
• ⬜ 各数据库市场份额与生态

三、数据类型与表设计

• ⬜ MySQL 常用数据类型（int / varchar / text / datetime / decimal 等）
• ⬜ 字符集与排序规则（utf8mb4 / utf8 / latin1）
• ⬜ 数据库设计范式（1NF → 2NF → 3NF / 反范式化）
• ⬜ 主键设计（自增 / UUID / 雪花算法）
• ⬜ 约束（NOT NULL / UNIQUE / CHECK / DEFAULT / FOREIGN KEY）
• ⬜ ER 图绘制与实践案例

四、DQL（数据查询语言）

• ✅ SELECT 基础查询
• ✅ WHERE 条件查询
• ✅ 模糊查询（LIKE / % / _ / ESCAPE）
• ✅ BETWEEN AND 区间查询
• ✅ IN 集合查询
• ✅ ORDER BY 排序
• ✅ DISTINCT 去重
• ✅ 常见函数（concat / substr / upper / lower / replace / length / trim / lpad / rpad / instr）
• ✅ 数学函数（ceil / round / mod / floor / truncate / rand）
• ⬜ GROUP BY 分组查询与 HAVING 筛选
• ⬜ 聚合函数（COUNT / SUM / AVG / MAX / MIN）
• ⬜ 多表连接（INNER JOIN / LEFT JOIN / RIGHT JOIN / FULL JOIN / CROSS JOIN / 自连接）
• ⬜ 子查询（标量子查询 / 列子查询 / 行子查询 / EXISTS）
• ⬜ UNION 与 UNION ALL 合并查询
• ⬜ LIMIT 分页查询
• ⬜ 窗口函数（ROW_NUMBER / RANK / DENSE_RANK / LAG / LEAD）

五、DML（数据操作语言）

• ⬜ INSERT（单行 / 多行 / 从查询结果插入）
• ⬜ UPDATE（单表 / 多表联更新）
• ⬜ DELETE（删除 vs TRUNCATE 的区别）
• ⬜ REPLACE 与 INSERT … ON DUPLICATE KEY UPDATE

六、DDL（数据定义语言）

• ⬜ CREATE（建库 / 建表 / 建索引 / 建视图）
• ⬜ ALTER（修改表结构 / 添加删除列 / 修改约束）
• ⬜ DROP / TRUNCATE（删除库表与截断）
• ⬜ RENAME 重命名

七、TCL（事务控制语言）

• ⬜ 事务概念（COMMIT / ROLLBACK / SAVEPOINT）
• ⬜ 事务隔离级别（READ UNCOMMITTED → READ COMMITTED → REPEATABLE READ → SERIALIZABLE）
• ⬜ 并发问题（脏读 / 不可重复读 / 幻读）
• ⬜ MySQL 默认隔离级别与 MVCC 机制
• ⬜ 锁机制（表锁 / 行锁 / 间隙锁 / 意向锁 / 死锁排查）

八、索引

• ⬜ 索引的本质（B+ 树原理）
• ⬜ 索引类型（主键索引 / 唯一索引 / 普通索引 / 复合索引 / 全文索引）
• ⬜ 索引创建与删除
• ⬜ 最左前缀原则
• ⬜ 索引优化策略（覆盖索引 / 索引下推 / 避免索引失效）
• ⬜ EXPLAIN 执行计划分析

九、性能优化

• ⬜ 慢查询日志与分析
• ⬜ SQL 语句优化实战（SELECT * 的害处 / LIMIT 优化 / JOIN 优化）
• ⬜ 表结构优化（垂直拆分 / 水平拆分）
• ⬜ 读写分离与主从复制
• ⬜ 分库分表策略（ShardingSphere / MyCat）
• ⬜ 缓存策略（Redis 缓存 / 查询缓存）

十、NoSQL 数据库入门

• ⬜ Redis（键值存储 / 数据结构 / 缓存 / 消息队列 / 持久化）
• ⬜ MongoDB（文档数据库 / 灵活 Schema / 适用场景）
• ⬜ Elasticsearch（搜索引擎 / 倒排索引 / 日志分析）
• ⬜ 关系型 vs NoSQL 的技术选型指南

十一、备份与恢复

• ⬜ mysqldump 逻辑备份
• ⬜ 物理备份（xtrabackup）
• ⬜ binlog 日志与时间点恢复
• ⬜ 备份策略（全量 + 增量 / 冷备 vs 热备）

十二、安全管理

• ⬜ 用户与权限管理（GRANT / REVOKE）
• ⬜ SQL 注入原理与防范（参数化查询）
• ⬜ 数据加密（传输层 SSL / 存储加密）
• ⬜ 审计日志

十三、实战案例设计

• ⬜ 博客系统的数据库设计
• ⬜ 电商订单系统的数据库设计
• ⬜ 用户权限系统的数据库设计

常见数据库产品及区别

使用数据库存储数据相对内存等具有能够将数据持久化到本地保存的特点，同时数据库可实现对数据的结构化查询，方便进一步管理海量的数据。

DB:数据库（database），存储数据的仓库，保存一系列有组织的数据。
DBMS：数据库管理系统（DatabaseManagementSystem），数据库是通过DBMS创建和操作的容器，MySQL等即为数据库管理软件。（分为基于共享文件系统的DBMS，如Access，和基于客户机，即服务器的DBMS，MySQL等常见数据库属于此类，一般安装数据库是指安装服务端。）
SQL:机构化查询语言（StructureQueryLanguage），用于与数据库通信的语言。一般被DBMS均支持。

开启或关闭服务
可在计算机管理的服务中对设置的MySQL服务进行停止或关闭操作。也可通过命令行net start 服务名与net stop 服务名 操作开启或停止。
非root用户连接数据库： mysql - h 主机 -P 端口 -u 用户名 -p密码，若为本地则localhost

图形化客户端：SQLyog
sql文件用于保存命令。

数据库的使用

数据库MySQL语句规范：

1. 不区分大小写，但一般关键字大写，表名、列名小写。
2. 每条命令需要分号结尾。
3. 可根据需要缩进或换行编写。
4. 注释： #注释文字 - - 注释文字 /* 多行注释 */

常见命令

show databases; 查看所以数据库
use 库名 打开指定库
show tables; 查看当前库所以表（table）
show tables from 库名; 查看其他库所以表
create table 表名(
列名 列类型,
列名 列类型
……
)；
desc 表名; 查看表结构

SQL语言

DQL语言（数据查询语言）

查询命令

1
2
3
4

SELECT 查询内容 FROM 表名;  //可使用*通史符查询，但查询内容将按原表顺序。若查询常量，则不需要from。也可用来查询函数。如 `SELECT VERSION();`
SELECT 查询内容 AS 别名;  //可将查询到的结果按新的首行名称显示。如 `SELECT last_name AS姓,first_name AS 名 FROM employees;` AS可以省略。

当字段名与关键字冲突时，可通过``标识字段。

查询结果约束，可通过关键词 DISTINCT 去掉重复的查询结果。
+ 号在MySQL中仅运算符。若两个量为数值则做加法；若其中一个为字符，则将字符转为
数值进行加法。若字符转换失败者将该字符型转换为0。

条件查询 where
SELECT 查询列表 FROM 表名 where 筛选条件;

模糊查询。
通配符：%任意多个字符，_任意单个字符。escape用于告知某字符后不作为通配符，而作为字符查询。select * from user where username like '/_nihao' escape '/';
between and 用于查询区间
in 用于判断某字段值是否为列表中的一项。相比于where后添加多个or更加简洁。

排序查询：
order by 排序列表 【asc，默认升序，desc】 可以同时按多个元素排序，按在by之后的参数的先后顺序判定优先级。

常见函数

concat:连接。连接不同的字段为一个字符串。需要注意，若有一个值为null，则与该值衔接的值均为null。
substr:截取子串
upper:变大写
lower：变小写
replace：替换
length：获取字节长度
trim:去前后空格
lpad：左填充
rpad：右填充
instr:获取子串第一次出现的索引
2、数学函数
ceil:向上取整
round：四舍五入
mod:取模
floor：向下取整
truncate:截断
rand:获取随机数，返回0-1之间的小数

1
2
3

SELECT COUNT(*) 
FROM ECOLOGY_CLOUD_PRO.sys_login_log 
WHERE create_time < '2026-09-01 00:00:00';

DML语言（数据操作管理语言，增删改）

DDL语言（数据定义语言）

TCL语言（事务控制语言）

双层查询

这个 SQL 的作用是统计某数据在指定时间段的合计值，并同时计算它与“去年同期”或“基期”的差值及增长率。下面我分步解释：

1. 整体功能

假设有一张表 EDU_CLOUD_PRO.cst_data_consume，记录着每日的消费/使用数据。
你想看 2025年3月 这个“当前期间”的数据总和，同时与“基期”（很可能是2024年3月，也就是去年同月）的数据总和进行比较，最后计算出：

• 增长量 = 当前期 – 基期
• 增长率 = 增长量 ÷ 基期（如果基期不为0）

2. 子查询（内层 FROM 部分）

1
2
3
4
5

SELECT SUM(b.all_day_num) AS current_period, 
       SUM(b.pre_year_num) AS base_period
FROM EDU_CLOUD_PRO.cst_data_consume b
WHERE b.del_flag = '0'
  AND b.statistic_time BETWEEN TO_DATE('2025-03-01', 'YYYY-MM-DD') AND TO_DATE('2025-03-31', 'YYYY-MM-DD')

• cst_data_consume 表中，all_day_num 应该是当前期间某个指标的值（比如当日用量、消费金额等）。
• pre_year_num 应该是去年同期对应的值（比如去年同一日期的数据）。
• 过滤条件：
  • del_flag = '0'：只统计未删除的有效记录。
  • statistic_time BETWEEN '2025-03-01' AND '2025-03-31'：筛选出2025年3月内的所有记录。
• 然后分别对 all_day_num 和 pre_year_num 求和：
  • current_period = 2025年3月所有 all_day_num 的合计（当前期间总和）
  • base_period = 这些记录里对应的 pre_year_num 的合计（基期总和，通常代表去年同期的数据）

因为子查询只返回一行（两个汇总值），所以外层可以直接作为一个单行结果引用。

3. 外层查询（选择与计算）

1
2
3
4
5

SELECT a.current_period,
       a.base_period,
       (a.current_period - a.base_period) AS "growth",
       ((a.current_period - a.base_period) / NULLIF(a.base_period, 0)) AS "rate"
FROM ( ... ) a;

• a.current_period：直接显示当前期间总和。
• a.base_period：显示基期总和。
• growth = 当前期 - 基期，即增长量。
• rate = 增长量 ÷ 基期，即增长率。
  • NULLIF(a.base_period, 0)：如果基期总和为0，则 NULLIF 会把0变成 NULL，这样除法结果就是 NULL 而不是报错，避免除数为0的异常。

4. 注意事项

• BETWEEN 是闭区间，会包含 '2025-03-01' 和 '2025-03-31'。但如果 statistic_time 是带时分秒的时间类型，3月31日当天 23:59:59 之前的数据都会包含，没问题；不过严格来说若存在3月31日 00:00:00 之后的数据，BETWEEN 到 '2025-03-31'（默认时间可能是00:00:00）会漏掉当天除零点之后的数据。如果想覆盖整天，建议写成 < TO_DATE('2025-04-01', 'YYYY-MM-DD') 更安全。
• 如果 pre_year_num 并不是字面意义的“去年同期值”，而只是某个固定的对比基准值，那么 base_period 就是那个基准值，逻辑依旧成立。
• 最后结果中 growth 和 rate 列名加了双引号，表示区分大小写或保留原样，在Oracle等数据库中双引号表示该列名的大小写敏感。

简单总结：这个SQL用2025年3月的数据与去年同月对比，计算出增长量和增长率，方便做同比分析。 如果还有疑问，可以继续问我。