一、数据库基础概念1.1 什么是数据库数据库Database是按照数据结构组织、存储和管理数据的仓库。它可以长期存储数据支持高效的增删改查操作。数据库的本质 - 数据仓库长期存储数据 - 数据结构化按照特定格式组织 - 高效访问支持快速查询和操作1.2 OLTP vs OLAP数据库应用主要分为两类场景类型全称说明特点OLTP联机事务处理和业务相关对数据库进行增删改查操作实时性高稳定性强行式存储OLAP联机分析处理对数据查询分析如日志分析、留存率分析需要进行列式存储适合大数据分析举例说明 OLTP场景在线交易 - 用户下单INSERT订单记录 - 支付扣款UPDATE账户余额 - 查询订单SELECT订单状态 OLAP场景数据分析 - 统计日活跃用户 - 分析用户留存率 - 报表生成 一个MySQL 一个Web服务器 可以完成OLAP分析日志的任务。二、SQL语言分类SQL结构化查询语言是一个标准化的数据库操作语言。SQL 分类 ├── DQL数据查询语言SELECT ├── DML数据操作语言INSERT、UPDATE、DELETE ├── DDL数据定义语言CREATE、ALTER、DROP ├── DCL数据控制语言GRANT、REVOKE └── TCL事务控制语言BEGIN、COMMIT、ROLLBACK2.1 DQL - 数据查询语言-- 最基本的查询SELECT*FROMusers;-- 带条件查询SELECTname,ageFROMusersWHEREage18;-- 去重查询SELECTDISTINCTcityFROMusers;2.2 DML - 数据操作语言DML操作会引起数据库的变更数据变化。-- 插入数据INSERTINTOusers(name,age)VALUES(张三,20);-- 更新数据UPDATEusersSETage21WHEREid1;-- 删除数据DELETEFROMusersWHEREid1;2.3 DDL - 数据定义语言DDL用于定义和管理数据库对象表、索引等。-- 创建表CREATETABLEorders(idINTPRIMARYKEY,product_nameVARCHAR(100),priceDECIMAL(10,2));-- 修改表结构ALTERTABLEordersADDCOLUMNquantityINT;-- 删除表DROPTABLEorders;2.4 DCL - 数据控制语言DCL用于做权限验证控制用户对数据的访问。-- 授权GRANTSELECT,INSERTONdatabase.tableTOuserhost;-- 撤销权限REVOKEINSERTONdatabase.tableFROMuserhost;-- 刷新权限FLUSHPRIVILEGES;2.5 TCL - 事务控制语言TCL用于管理数据库事务确保数据一致性。-- 开始事务BEGIN;-- 或STARTTRANSACTION;-- 提交事务COMMIT;-- 回滚事务ROLLBACK;三、数据库术语3.1 核心概念术语说明示例主键 (Primary Key)唯一标识表中每一行记录不能为空且唯一user_id 作为用户表的主键外键 (Foreign Key)用来关联两张表的字段orders 表中的 user_id 引用 users 表复合键 (Composite Key)由多个字段组成的主键(order_id, product_id) 作为订单明细的主键索引 (Index)快速访问表的数据结构为 name 字段创建索引加速查询主键特点 - 非空唯一 - 每表只能有一个主键 - 主键值不可修改 外键特点 - 引用另一表的主键 - 实现表间关联 - 保证参照完整性四、MySQL体系结构MySQL采用分层架构主要分为两大部分MySQL 体系结构 ┌─────────────────────────────────────────────────────┐ │ Server 层 │ │ ┌─────────────┐ ┌─────────────┐ ┌────────────┐ │ │ │ Connectors │ │ Server │ │ Optimizer │ │ │ │ (连接器) │ │ (服务层) │ │ (优化器) │ │ │ └─────────────┘ └─────────────┘ └────────────┘ │ │ │ │ │ ┌───────┴───────┐ │ │ │ Cache │ │ │ │ (查询缓存) │ │ │ └───────────────┘ │ └─────────────────────────┬───────────────────────────┘ │ ┌─────────────────────────┴─────────────────────────┐ │ 存储引擎层 │ │ ┌─────────┐ ┌─────────┐ ┌─────────────┐ │ │ │ InnoDB │ │ MyISAM │ │ NDB Cluster │ │ │ └─────────┘ └─────────┘ └─────────────┘ │ │ 可插拔存储引擎 │ └───────────────────────────────────────────────────┘4.1 Server 层组件1. MySQL Connectors - MySQL的驱动包含网络功能 - 实现MySQL协议 - 包含 mysql shell 等工具 2. Server 层核心功能 - 词法分析将SQL语句分解成token - 句法分析生成语法树 - 解析器根据语法树解析SQL - 优化器制定执行计划选择代价最低的方案 3. 查询缓存Query Cache - 5.7 以前存在8.0 以后被取消 - 将SELECT结果缓存起来 - 下次查询时直接命中返回4.2 存储引擎层存储引擎特点 - 可插拔设计 - 默认选择 InnoDB - 提供接口暴露给 Server 层 - 统筹内存和磁盘的数据交互 InnoDB 关键机制 - Buffer Pool内存数据缓存池 - 用来管理内存中的数据页 - 替换内核中的 Page Cache更可控 - Undo Log记录修改前的值方便回滚 - Redo Log记录修改后的值用于恢复 - BinLog主从复制的日志4.3 连接命令# 连接 MySQLmysql-uroot-p# 输入密码后进入# -u: 用户名# -p: 密码会提示输入五、MySQL网络模块5.1 IO多路复用模型MySQL 网络处理流程 1. select/epoll 监听 listenfd 的读事件 2. 检测连接请求是否有客户端连接 3. 处理全连接队列中是否有数据 4. 为每条连接创建连接线程 工作流程 ┌──────────────┐ │ select 监听 │ │ listenfd │ └──────┬───────┘ │ ▼ ┌──────────────┐ │ 检测连接请求 │ │ accept连接 │ └──────┬───────┘ │ ▼ ┌──────────────┐ │ 创建连接线程 │ └──────┬───────┘ │ ▼ ┌──────────────┐ │ 读取SQL语句 │ │ (阻塞IO) │ └──────┬───────┘ │ ▼ ┌──────────────┐ │ 执行SQL │ │ 并发执行 │ └──────┬───────┘ │ ▼ ┌──────────────┐ │ 返回结果给 │ │ 客户端 │ └──────────────┘5.2 连接池机制MySQL 内部连接池 - 包含很多线程 - 可以更高效处理SQL语句 - 减少创建/销毁线程的开销 注意 - MySQL 不使用 Reactor 网络模型 - 只关注处理连接建立的事情 - SQL语句是并发执行的 - 默认最大连接数约 100 多个5.3 查询执行流程完整的查询流程 1. 连接器 - 接收连接、管理连接 - 校验用户信息 2. 查询缓存 - KV 存储结构 - 检查是否命中缓存 - 命中则直接返回结果 3. 分析器 - 词法分析 - 句法分析 - 生成语法树 4. 优化器 - 制定执行计划 - 选择查询成本最小的计划 5. 执行器 - 执行计划 - 从存储引擎获取数据 - 返回结果给客户端六、数据库设计范式6.1 三范式3NF三范式的主要目的是避免数据冗余减少空间占用。第一范式1NF列不可分 - 每个列都是不可分割的原子值 - 避免把多个值放在一个列中 反例 ┌─────────────────────────────────┐ │ address列存储省市街道混在一起 │ └─────────────────────────────────┘ 正确做法 ┌────────┬────────┬────────┐ │province│ city │ street │ └────────┴────────┴────────┘ 第二范式2NF完全依赖主键 - 在满足1NF的基础上 - 非主键列必须完全依赖于主键 - 不能只依赖主键的一部分组合索引的情况 反例 ┌────────┬────────┬────────┐ │order_id│ product│ amount │ └────────┴────────┴────────┘ 如果主键是 (order_id, product_id)amount只依赖order_id就违反2NF 第三范式3NF直接依赖主键 - 在满足2NF的基础上 - 非主键列直接依赖于主键 - 而不是通过其他非主键列间接依赖 反例 ┌────────┬────────┬────────┬────────┐ │user_id│user_name│dept_id │dept_name│ └────────┴────────┴────────┴────────┘ dept_name通过dept_id间接依赖user_id违反3NF6.2 反范式设计为什么需要反范式 均衡选择 - 拆分成多个表会降低查询性能 - 范式化设计会导致多表关联查询 反范式设计 - 允许有一定的数据冗余 - 为了提高查询效率 - 用空间换时间 举例 - 经常一起查询的字段可以冗余存储 - 避免频繁的JOIN操作 - 适合读多写少的场景七、五大约束约束用于保证数据的完整性和一致性。约束说明示例NOT NULL非空约束name VARCHAR(50) NOT NULLAUTO_INCREMENT自增约束id INT AUTO_INCREMENTUNIQUE唯一约束email VARCHAR(100) UNIQUEPRIMARY KEY主键约束非空唯一PRIMARY KEY (id)FOREIGN KEY外键约束表间联动FOREIGN KEY (user_id) REFERENCES users(id)CREATETABLEIFNOTEXISTSOVoice_tbl(idINTUNSIGNEDAUTO_INCREMENTCOMMENT编号,courseVARCHAR(100)NOTNULLCOMMENT课程,teacherVARCHAR(40)NOTNULLCOMMENT教师,priceDECIMAL(8,2)NOTNULLCOMMENT价格,PRIMARYKEY(id),UNIQUE(course))ENGINEInnoDBDEFAULTCHARSETutf8COMMENT课程表;约束详解 NOT NULL - 该列不能为空 - 必须指定具体值 AUTO_INCREMENT - 数值自动增长 - 通常用于主键 - 必须配合索引使用 UNIQUE - 该列不能出现重复值 - 可以有多个UNIQUE约束 PRIMARY KEY - 非空 唯一 - 每表只能有一个主键 - 聚簇索引 FOREIGN KEY - 两张表联动的约束 - 保证参照完整性 - InnoDB引擎支持八、SQL基本操作8.1 数据库操作-- 创建数据库CREATEDATABASE数据库名DEFAULT;-- 查看所有数据库SHOWDATABASES;-- 选择数据库USE数据库名;-- 删除数据库DROPDATABASE数据库名;8.2 表操作-- 查看所有表SHOWTABLES;-- 创建表CREATETABLEtable_name(column_name column_type,...);-- 删除表删除表结构和表数据DROPTABLE表名;-- 清空表TRUNCATETABLE表名;-- 删除数据逐行删除可回滚DELETEFROM表名;三种删除方式的区别 ┌────────────┬────────────┬────────────┬────────────┐ │ 方式 │ 速度 │ 能否回滚 │ 是否重置 │ ├────────────┼────────────┼────────────┼────────────┤ │ DROP │ 最快 │ 不能 │ 是 │ │ TRUNCATE │ 较快 │ 不能 │ 是 │ │ DELETE │ 最慢 │ 能 │ 否 │ └────────────┴────────────┴────────────┴────────────┘ 详细说明 - DROP删除表结构、索引、约束、触发器释放空间 - TRUNCATE以页为单位删除auto_increment重置为初始值 - DELETEDML操作逐行删除支持回滚8.3 增删改查CRUD-- 插入数据INSERTINTOtable_name(field1,field2)VALUES(value1,value2);-- 删除数据DELETEFROMtable_nameWHERE条件;-- 更新数据UPDATEtable_nameSETfield1value1WHERE条件;-- 查询数据SELECT*FROMtable_nameWHERE条件;九、高级查询9.1 基础查询-- 去重查询SELECTDISTINCTcityFROMusers;-- 条件查询SELECT*FROMusersWHEREage18;-- 范围查询SELECT*FROMusersWHEREageBETWEEN18AND30;-- 判空查询SELECT*FROMusersWHEREnameISNOTNULL;-- 模糊查询SELECT*FROMusersWHEREnameLIKE谢%;-- 分页查询SELECT*FROMusersLIMIT10OFFSET20;-- 排序查询SELECT*FROMusersORDERBYageASC;-- 升序SELECT*FROMusersORDERBYageDESC;-- 降序9.2 聚合查询-- 求和SELECTSUM(price)FROMorders;-- 求平均值SELECTAVG(price)FROMorders;-- 求最大值SELECTMAX(price)FROMorders;-- 求最小值SELECTMIN(price)FROMorders;-- 计数SELECTCOUNT(*)FROMorders;-- 分组查询SELECTcategory,COUNT(*)FROMproductsGROUPBYcategory;-- 分组后聚合SELECTdepartment,GROUP_CONCAT(name)FROMemployeesGROUPBYdepartment;-- HAVING 过滤分组SELECTcategory,COUNT(*)ascntFROMproductsGROUPBYcategoryHAVINGcnt5;9.3 联表查询-- 内连接INNER JOIN-- 求两表的交集SELECT*FROMorders oINNERJOINusers uONo.user_idu.id;-- 左连接LEFT JOIN-- 左表全部 右表匹配的SELECT*FROMorders oLEFTJOINusers uONo.user_idu.id;-- 右连接RIGHT JOIN-- 右表全部 左表匹配的SELECT*FROMorders oRIGHTJOINusers uONo.user_idu.id;-- 左连接排除右表差集SELECT*FROMorders oLEFTJOINusers uONo.user_idu.idWHEREu.idISNULL;-- 全外连接MySQL不支持用UNION模拟SELECT*FROMorders oLEFTJOINusers uONo.user_idu.idUNIONSELECT*FROMorders oRIGHTJOINusers uONo.user_idu.id;JOIN 详解 表A: orders (order_id, user_id, amount) 表B: users (id, name) ┌─────────┐ ┌─────────┐ ┌─────────┐ │ INNER │ │ LEFT │ │ RIGHT │ │ JOIN │ │ JOIN │ │ JOIN │ └─────────┘ └─────────┘ └─────────┘ INNER JOIN: 只保留两表都有的记录 LEFT JOIN: 保留A的全部B没有的为NULL RIGHT JOIN: 保留B的全部A没有的为NULL9.4 子查询-- 单行子查询SELECT*FROMusersWHEREage(SELECTAVG(age)FROMusers);-- 多行子查询 INSELECT*FROMproductsWHEREcategory_idIN(SELECTidFROMcategoriesWHEREname电子产品);-- 多行子查询 ANYSELECT*FROMproductsWHEREpriceANY(SELECTpriceFROMproductsWHEREcategory手机);-- 多行子查询 ALLSELECT*FROMproductsWHEREpriceALL(SELECTpriceFROMproductsWHEREcategory低端机);-- EXISTS 子查询SELECT*FROMusers uWHEREEXISTS(SELECT1FROMorders oWHEREo.user_idu.id);9.5 正则表达式-- 匹配以a开头的名字SELECT*FROMusersWHEREnameREGEXP^a;-- 匹配包含b的名字SELECT*FROMusersWHEREnameREGEXPb;-- 匹配以c结尾的名字SELECT*FROMusersWHEREnameREGEXPc$;十、预处理语句预处理语句将SQL语句分成准备和执行两个阶段。PREPAREstmtFROMSELECT * FROM users WHERE id ?;SETid1;EXECUTEstmtUSINGid;DEALLOCATEPREPAREstmt;预处理语句的优点 1. 避免重复解析和编译 - 准备阶段解析、编译、优化生成执行计划并缓存 - 执行阶段直接使用缓存的计划 2. 防止SQL注入 - 参数用占位符 ? 表示 - 用户输入不会当作SQL语句执行 - 避免拼接字符串导致的注入风险十一、视图视图是一张虚拟表基于SQL查询结果。-- 创建视图CREATEVIEWv_user_ordersASSELECTu.name,o.order_id,o.amountFROMusers uINNERJOINorders oONu.ido.user_id;-- 使用视图查询SELECT*FROMv_user_ordersWHEREamount100;视图的应用场景 1. 简化复杂查询 - 用视图代替复杂的JOIN语句 - 减少重复书写 2. 重构利器 - 底层表结构变化时 - 只需修改视图定义 3. 权限管理 - 可以对用户隐藏某些表 - 用户只能通过视图操作数据 4. 限制操作 - 视图通常只用于SELECT - INSERT/UPDATE/DELETE限制较多十二、权限管理-- 查看所有用户SELECTuser,hostFROMmysql.user;-- 授权GRANTSELECT,INSERTONdatabase.tableTOuserhost;-- 刷新权限FLUSHPRIVILEGES;-- 撤销权限REVOKEINSERTONdatabase.tableFROMuserhost;-- 修改用户host允许远程连接UPDATEuserSEThost%WHEREuserroot;十三、总结知识点核心内容OLTP vs OLAPOLTP处理事务OLAP分析数据SQL分类DQL/DML/DDL/DCL/TCLMySQL架构Server层 存储引擎层三范式列不可分 → 完全依赖主键 → 直接依赖主键五大约束NOT NULL/AUTO_INCREMENT/UNIQUE/PRIMARY/FOREIGN删除方式DROP TRUNCATE DELETE速度/回滚高级查询分组/聚合/联表/子查询视图简化查询/权限控制/重构预处理防注入/避免重复解析根据零声教育教学写作https://github.com/0voice
数据库基础知识整理
发布时间：2026/5/30 20:42:58
一、数据库基础概念1.1 什么是数据库数据库Database是按照数据结构组织、存储和管理数据的仓库。它可以长期存储数据支持高效的增删改查操作。数据库的本质 - 数据仓库长期存储数据 - 数据结构化按照特定格式组织 - 高效访问支持快速查询和操作1.2 OLTP vs OLAP数据库应用主要分为两类场景类型全称说明特点OLTP联机事务处理和业务相关对数据库进行增删改查操作实时性高稳定性强行式存储OLAP联机分析处理对数据查询分析如日志分析、留存率分析需要进行列式存储适合大数据分析举例说明 OLTP场景在线交易 - 用户下单INSERT订单记录 - 支付扣款UPDATE账户余额 - 查询订单SELECT订单状态 OLAP场景数据分析 - 统计日活跃用户 - 分析用户留存率 - 报表生成 一个MySQL 一个Web服务器 可以完成OLAP分析日志的任务。二、SQL语言分类SQL结构化查询语言是一个标准化的数据库操作语言。SQL 分类 ├── DQL数据查询语言SELECT ├── DML数据操作语言INSERT、UPDATE、DELETE ├── DDL数据定义语言CREATE、ALTER、DROP ├── DCL数据控制语言GRANT、REVOKE └── TCL事务控制语言BEGIN、COMMIT、ROLLBACK2.1 DQL - 数据查询语言-- 最基本的查询SELECT*FROMusers;-- 带条件查询SELECTname,ageFROMusersWHEREage18;-- 去重查询SELECTDISTINCTcityFROMusers;2.2 DML - 数据操作语言DML操作会引起数据库的变更数据变化。-- 插入数据INSERTINTOusers(name,age)VALUES(张三,20);-- 更新数据UPDATEusersSETage21WHEREid1;-- 删除数据DELETEFROMusersWHEREid1;2.3 DDL - 数据定义语言DDL用于定义和管理数据库对象表、索引等。-- 创建表CREATETABLEorders(idINTPRIMARYKEY,product_nameVARCHAR(100),priceDECIMAL(10,2));-- 修改表结构ALTERTABLEordersADDCOLUMNquantityINT;-- 删除表DROPTABLEorders;2.4 DCL - 数据控制语言DCL用于做权限验证控制用户对数据的访问。-- 授权GRANTSELECT,INSERTONdatabase.tableTOuserhost;-- 撤销权限REVOKEINSERTONdatabase.tableFROMuserhost;-- 刷新权限FLUSHPRIVILEGES;2.5 TCL - 事务控制语言TCL用于管理数据库事务确保数据一致性。-- 开始事务BEGIN;-- 或STARTTRANSACTION;-- 提交事务COMMIT;-- 回滚事务ROLLBACK;三、数据库术语3.1 核心概念术语说明示例主键 (Primary Key)唯一标识表中每一行记录不能为空且唯一user_id 作为用户表的主键外键 (Foreign Key)用来关联两张表的字段orders 表中的 user_id 引用 users 表复合键 (Composite Key)由多个字段组成的主键(order_id, product_id) 作为订单明细的主键索引 (Index)快速访问表的数据结构为 name 字段创建索引加速查询主键特点 - 非空唯一 - 每表只能有一个主键 - 主键值不可修改 外键特点 - 引用另一表的主键 - 实现表间关联 - 保证参照完整性四、MySQL体系结构MySQL采用分层架构主要分为两大部分MySQL 体系结构 ┌─────────────────────────────────────────────────────┐ │ Server 层 │ │ ┌─────────────┐ ┌─────────────┐ ┌────────────┐ │ │ │ Connectors │ │ Server │ │ Optimizer │ │ │ │ (连接器) │ │ (服务层) │ │ (优化器) │ │ │ └─────────────┘ └─────────────┘ └────────────┘ │ │ │ │ │ ┌───────┴───────┐ │ │ │ Cache │ │ │ │ (查询缓存) │ │ │ └───────────────┘ │ └─────────────────────────┬───────────────────────────┘ │ ┌─────────────────────────┴─────────────────────────┐ │ 存储引擎层 │ │ ┌─────────┐ ┌─────────┐ ┌─────────────┐ │ │ │ InnoDB │ │ MyISAM │ │ NDB Cluster │ │ │ └─────────┘ └─────────┘ └─────────────┘ │ │ 可插拔存储引擎 │ └───────────────────────────────────────────────────┘4.1 Server 层组件1. MySQL Connectors - MySQL的驱动包含网络功能 - 实现MySQL协议 - 包含 mysql shell 等工具 2. Server 层核心功能 - 词法分析将SQL语句分解成token - 句法分析生成语法树 - 解析器根据语法树解析SQL - 优化器制定执行计划选择代价最低的方案 3. 查询缓存Query Cache - 5.7 以前存在8.0 以后被取消 - 将SELECT结果缓存起来 - 下次查询时直接命中返回4.2 存储引擎层存储引擎特点 - 可插拔设计 - 默认选择 InnoDB - 提供接口暴露给 Server 层 - 统筹内存和磁盘的数据交互 InnoDB 关键机制 - Buffer Pool内存数据缓存池 - 用来管理内存中的数据页 - 替换内核中的 Page Cache更可控 - Undo Log记录修改前的值方便回滚 - Redo Log记录修改后的值用于恢复 - BinLog主从复制的日志4.3 连接命令# 连接 MySQLmysql-uroot-p# 输入密码后进入# -u: 用户名# -p: 密码会提示输入五、MySQL网络模块5.1 IO多路复用模型MySQL 网络处理流程 1. select/epoll 监听 listenfd 的读事件 2. 检测连接请求是否有客户端连接 3. 处理全连接队列中是否有数据 4. 为每条连接创建连接线程 工作流程 ┌──────────────┐ │ select 监听 │ │ listenfd │ └──────┬───────┘ │ ▼ ┌──────────────┐ │ 检测连接请求 │ │ accept连接 │ └──────┬───────┘ │ ▼ ┌──────────────┐ │ 创建连接线程 │ └──────┬───────┘ │ ▼ ┌──────────────┐ │ 读取SQL语句 │ │ (阻塞IO) │ └──────┬───────┘ │ ▼ ┌──────────────┐ │ 执行SQL │ │ 并发执行 │ └──────┬───────┘ │ ▼ ┌──────────────┐ │ 返回结果给 │ │ 客户端 │ └──────────────┘5.2 连接池机制MySQL 内部连接池 - 包含很多线程 - 可以更高效处理SQL语句 - 减少创建/销毁线程的开销 注意 - MySQL 不使用 Reactor 网络模型 - 只关注处理连接建立的事情 - SQL语句是并发执行的 - 默认最大连接数约 100 多个5.3 查询执行流程完整的查询流程 1. 连接器 - 接收连接、管理连接 - 校验用户信息 2. 查询缓存 - KV 存储结构 - 检查是否命中缓存 - 命中则直接返回结果 3. 分析器 - 词法分析 - 句法分析 - 生成语法树 4. 优化器 - 制定执行计划 - 选择查询成本最小的计划 5. 执行器 - 执行计划 - 从存储引擎获取数据 - 返回结果给客户端六、数据库设计范式6.1 三范式3NF三范式的主要目的是避免数据冗余减少空间占用。第一范式1NF列不可分 - 每个列都是不可分割的原子值 - 避免把多个值放在一个列中 反例 ┌─────────────────────────────────┐ │ address列存储省市街道混在一起 │ └─────────────────────────────────┘ 正确做法 ┌────────┬────────┬────────┐ │province│ city │ street │ └────────┴────────┴────────┘ 第二范式2NF完全依赖主键 - 在满足1NF的基础上 - 非主键列必须完全依赖于主键 - 不能只依赖主键的一部分组合索引的情况 反例 ┌────────┬────────┬────────┐ │order_id│ product│ amount │ └────────┴────────┴────────┘ 如果主键是 (order_id, product_id)amount只依赖order_id就违反2NF 第三范式3NF直接依赖主键 - 在满足2NF的基础上 - 非主键列直接依赖于主键 - 而不是通过其他非主键列间接依赖 反例 ┌────────┬────────┬────────┬────────┐ │user_id│user_name│dept_id │dept_name│ └────────┴────────┴────────┴────────┘ dept_name通过dept_id间接依赖user_id违反3NF6.2 反范式设计为什么需要反范式 均衡选择 - 拆分成多个表会降低查询性能 - 范式化设计会导致多表关联查询 反范式设计 - 允许有一定的数据冗余 - 为了提高查询效率 - 用空间换时间 举例 - 经常一起查询的字段可以冗余存储 - 避免频繁的JOIN操作 - 适合读多写少的场景七、五大约束约束用于保证数据的完整性和一致性。约束说明示例NOT NULL非空约束name VARCHAR(50) NOT NULLAUTO_INCREMENT自增约束id INT AUTO_INCREMENTUNIQUE唯一约束email VARCHAR(100) UNIQUEPRIMARY KEY主键约束非空唯一PRIMARY KEY (id)FOREIGN KEY外键约束表间联动FOREIGN KEY (user_id) REFERENCES users(id)CREATETABLEIFNOTEXISTSOVoice_tbl(idINTUNSIGNEDAUTO_INCREMENTCOMMENT编号,courseVARCHAR(100)NOTNULLCOMMENT课程,teacherVARCHAR(40)NOTNULLCOMMENT教师,priceDECIMAL(8,2)NOTNULLCOMMENT价格,PRIMARYKEY(id),UNIQUE(course))ENGINEInnoDBDEFAULTCHARSETutf8COMMENT课程表;约束详解 NOT NULL - 该列不能为空 - 必须指定具体值 AUTO_INCREMENT - 数值自动增长 - 通常用于主键 - 必须配合索引使用 UNIQUE - 该列不能出现重复值 - 可以有多个UNIQUE约束 PRIMARY KEY - 非空 唯一 - 每表只能有一个主键 - 聚簇索引 FOREIGN KEY - 两张表联动的约束 - 保证参照完整性 - InnoDB引擎支持八、SQL基本操作8.1 数据库操作-- 创建数据库CREATEDATABASE数据库名DEFAULT;-- 查看所有数据库SHOWDATABASES;-- 选择数据库USE数据库名;-- 删除数据库DROPDATABASE数据库名;8.2 表操作-- 查看所有表SHOWTABLES;-- 创建表CREATETABLEtable_name(column_name column_type,...);-- 删除表删除表结构和表数据DROPTABLE表名;-- 清空表TRUNCATETABLE表名;-- 删除数据逐行删除可回滚DELETEFROM表名;三种删除方式的区别 ┌────────────┬────────────┬────────────┬────────────┐ │ 方式 │ 速度 │ 能否回滚 │ 是否重置 │ ├────────────┼────────────┼────────────┼────────────┤ │ DROP │ 最快 │ 不能 │ 是 │ │ TRUNCATE │ 较快 │ 不能 │ 是 │ │ DELETE │ 最慢 │ 能 │ 否 │ └────────────┴────────────┴────────────┴────────────┘ 详细说明 - DROP删除表结构、索引、约束、触发器释放空间 - TRUNCATE以页为单位删除auto_increment重置为初始值 - DELETEDML操作逐行删除支持回滚8.3 增删改查CRUD-- 插入数据INSERTINTOtable_name(field1,field2)VALUES(value1,value2);-- 删除数据DELETEFROMtable_nameWHERE条件;-- 更新数据UPDATEtable_nameSETfield1value1WHERE条件;-- 查询数据SELECT*FROMtable_nameWHERE条件;九、高级查询9.1 基础查询-- 去重查询SELECTDISTINCTcityFROMusers;-- 条件查询SELECT*FROMusersWHEREage18;-- 范围查询SELECT*FROMusersWHEREageBETWEEN18AND30;-- 判空查询SELECT*FROMusersWHEREnameISNOTNULL;-- 模糊查询SELECT*FROMusersWHEREnameLIKE谢%;-- 分页查询SELECT*FROMusersLIMIT10OFFSET20;-- 排序查询SELECT*FROMusersORDERBYageASC;-- 升序SELECT*FROMusersORDERBYageDESC;-- 降序9.2 聚合查询-- 求和SELECTSUM(price)FROMorders;-- 求平均值SELECTAVG(price)FROMorders;-- 求最大值SELECTMAX(price)FROMorders;-- 求最小值SELECTMIN(price)FROMorders;-- 计数SELECTCOUNT(*)FROMorders;-- 分组查询SELECTcategory,COUNT(*)FROMproductsGROUPBYcategory;-- 分组后聚合SELECTdepartment,GROUP_CONCAT(name)FROMemployeesGROUPBYdepartment;-- HAVING 过滤分组SELECTcategory,COUNT(*)ascntFROMproductsGROUPBYcategoryHAVINGcnt5;9.3 联表查询-- 内连接INNER JOIN-- 求两表的交集SELECT*FROMorders oINNERJOINusers uONo.user_idu.id;-- 左连接LEFT JOIN-- 左表全部 右表匹配的SELECT*FROMorders oLEFTJOINusers uONo.user_idu.id;-- 右连接RIGHT JOIN-- 右表全部 左表匹配的SELECT*FROMorders oRIGHTJOINusers uONo.user_idu.id;-- 左连接排除右表差集SELECT*FROMorders oLEFTJOINusers uONo.user_idu.idWHEREu.idISNULL;-- 全外连接MySQL不支持用UNION模拟SELECT*FROMorders oLEFTJOINusers uONo.user_idu.idUNIONSELECT*FROMorders oRIGHTJOINusers uONo.user_idu.id;JOIN 详解 表A: orders (order_id, user_id, amount) 表B: users (id, name) ┌─────────┐ ┌─────────┐ ┌─────────┐ │ INNER │ │ LEFT │ │ RIGHT │ │ JOIN │ │ JOIN │ │ JOIN │ └─────────┘ └─────────┘ └─────────┘ INNER JOIN: 只保留两表都有的记录 LEFT JOIN: 保留A的全部B没有的为NULL RIGHT JOIN: 保留B的全部A没有的为NULL9.4 子查询-- 单行子查询SELECT*FROMusersWHEREage(SELECTAVG(age)FROMusers);-- 多行子查询 INSELECT*FROMproductsWHEREcategory_idIN(SELECTidFROMcategoriesWHEREname电子产品);-- 多行子查询 ANYSELECT*FROMproductsWHEREpriceANY(SELECTpriceFROMproductsWHEREcategory手机);-- 多行子查询 ALLSELECT*FROMproductsWHEREpriceALL(SELECTpriceFROMproductsWHEREcategory低端机);-- EXISTS 子查询SELECT*FROMusers uWHEREEXISTS(SELECT1FROMorders oWHEREo.user_idu.id);9.5 正则表达式-- 匹配以a开头的名字SELECT*FROMusersWHEREnameREGEXP^a;-- 匹配包含b的名字SELECT*FROMusersWHEREnameREGEXPb;-- 匹配以c结尾的名字SELECT*FROMusersWHEREnameREGEXPc$;十、预处理语句预处理语句将SQL语句分成准备和执行两个阶段。PREPAREstmtFROMSELECT * FROM users WHERE id ?;SETid1;EXECUTEstmtUSINGid;DEALLOCATEPREPAREstmt;预处理语句的优点 1. 避免重复解析和编译 - 准备阶段解析、编译、优化生成执行计划并缓存 - 执行阶段直接使用缓存的计划 2. 防止SQL注入 - 参数用占位符 ? 表示 - 用户输入不会当作SQL语句执行 - 避免拼接字符串导致的注入风险十一、视图视图是一张虚拟表基于SQL查询结果。-- 创建视图CREATEVIEWv_user_ordersASSELECTu.name,o.order_id,o.amountFROMusers uINNERJOINorders oONu.ido.user_id;-- 使用视图查询SELECT*FROMv_user_ordersWHEREamount100;视图的应用场景 1. 简化复杂查询 - 用视图代替复杂的JOIN语句 - 减少重复书写 2. 重构利器 - 底层表结构变化时 - 只需修改视图定义 3. 权限管理 - 可以对用户隐藏某些表 - 用户只能通过视图操作数据 4. 限制操作 - 视图通常只用于SELECT - INSERT/UPDATE/DELETE限制较多十二、权限管理-- 查看所有用户SELECTuser,hostFROMmysql.user;-- 授权GRANTSELECT,INSERTONdatabase.tableTOuserhost;-- 刷新权限FLUSHPRIVILEGES;-- 撤销权限REVOKEINSERTONdatabase.tableFROMuserhost;-- 修改用户host允许远程连接UPDATEuserSEThost%WHEREuserroot;十三、总结知识点核心内容OLTP vs OLAPOLTP处理事务OLAP分析数据SQL分类DQL/DML/DDL/DCL/TCLMySQL架构Server层 存储引擎层三范式列不可分 → 完全依赖主键 → 直接依赖主键五大约束NOT NULL/AUTO_INCREMENT/UNIQUE/PRIMARY/FOREIGN删除方式DROP TRUNCATE DELETE速度/回滚高级查询分组/聚合/联表/子查询视图简化查询/权限控制/重构预处理防注入/避免重复解析根据零声教育教学写作https://github.com/0voice
