MySQL知识点一、数据库基础理论选择/填空/简答高频1. 核心概念DB数据库长期存储在计算机中、有组织、可共享的数据集合。DBMS数据库管理系统操纵和管理数据库的系统软件MySQL、Oracle、SQL Server均属于DBMS。DBS数据库系统由数据库、DBMS、应用程序、数据库管理员DBA、用户、硬件平台共同组成的完整系统。SQL结构化查询语言是关系型数据库的通用标准操作语言。2. SQL语言五大分类分类全称作用代表语句DDL数据定义语言定义库/表结构CREATE、ALTER、DROPDML数据操纵语言操作表中数据INSERT、UPDATE、DELETEDQL数据查询语言查询数据SELECTDCL数据控制语言用户与权限管理GRANT、REVOKETCL事务控制语言事务管理COMMIT、ROLLBACK3. 关系型数据库三大范式必考第一范式1NF字段原子性每一列不可再拆分一个单元格只能存一个值。第二范式2NF满足1NF消除部分函数依赖非主键字段必须完全依赖整个主键不能只依赖主键的一部分主要针对复合主键。第三范式3NF满足2NF消除传递函数依赖非主键字段只能依赖主键不能依赖其他非主键字段。反范式设计通过冗余字段减少表连接、提升查询速度以空间换时间是实际开发中的常用优化手段。4. 关系完整性约束实体完整性主键约束主键值必须唯一且非空。参照完整性外键约束子表的外键值必须在主表主键中存在或为NULL。域完整性限制字段的取值范围如数据类型、长度、非空、默认值、枚举约束。用户自定义完整性根据业务自定义规则如CHECK约束MySQL 8.0.16后原生支持。二、DDL 数据定义语言1. 数据库操作-- 创建数据库并指定字符集CREATEDATABASEIFNOTEXISTS库名DEFAULTCHARSETutf8mb4COLLATEutf8mb4_general_ci;-- 查看所有数据库SHOWDATABASES;-- 查看建库语句SHOWCREATEDATABASE库名;-- 切换数据库USE库名;-- 删除数据库DROPDATABASEIFEXISTS库名;2. 常用数据类型选择填空高频数值类型类型说明适用场景TINYINT1字节范围-128~127状态、性别、年龄INT4字节最常用整数编号、数量BIGINT8字节大整数主键ID、超大数值DECIMAL(M,D)定点数无精度丢失金额、财务数据FLOAT/DOUBLE浮点数存在精度误差非精确科学计算字符串类型类型说明适用场景CHAR(n)固定长度字符n为字符数查询快、占空间固定长度字段如手机号、身份证号VARCHAR(n)可变长度字符n为最大字符数省空间姓名、地址等长度不固定字段TEXT长文本最大64KB文章、备注等大文本日期时间类型类型格式说明DATEYYYY-MM-DD仅日期TIMEHH:MM:SS仅时间DATETIMEYYYY-MM-DD HH:MM:SS日期时间范围大与时区无关TIMESTAMPYYYY-MM-DD HH:MM:SS时间戳占空间小受时区影响范围1970-20383. 数据表操作-- 创建表标准写法CREATETABLE表名(字段名1数据类型[约束条件],字段名2数据类型[约束条件],...[PRIMARYKEY(主键字段)],[FOREIGNKEY(外键字段)REFERENCES主表(主键字段)])ENGINEInnoDBDEFAULTCHARSETutf8mb4;-- 查看表结构DESC表名;DESCRIBE表名;-- 查看建表语句SHOWCREATETABLE表名;-- 修改表结构ALTERTABLE表名ADD字段名 数据类型[约束];-- 新增字段ALTERTABLE表名MODIFY字段名 新数据类型[约束];-- 修改字段类型/约束ALTERTABLE表名 CHANGE 旧字段名 新字段名 数据类型;-- 修改字段名ALTERTABLE表名DROP字段名;-- 删除字段ALTERTABLE表名RENAMETO新表名;-- 修改表名-- 删除表DROPTABLEIFEXISTS表名;4. 六大约束核心考点约束名关键字作用特点主键约束PRIMARY KEY唯一标识一行数据唯一且非空一张表只能有一个主键可以是复合主键外键约束FOREIGN KEY关联两张表仅InnoDB支持子表外键类型必须与主表主键完全一致唯一约束UNIQUE字段值不能重复允许出现多个NULLNULL≠NULL一张表可多个唯一约束非空约束NOT NULL字段必须赋值不能为NULL只能作用于列级默认约束DEFAULT未赋值时自动填充默认值插入时不写字段则触发默认值自增约束AUTO_INCREMENT数值自动1每张表只能一个自增字段必须是数值型且为键通常是主键默认从1开始三、DML 数据操纵语言1. 插入数据 INSERT-- 指定字段插入推荐写法INSERTINTO表名(字段1,字段2,...)VALUES(值1,值2,...);-- 全字段插入省略字段名值必须和表字段顺序完全对应INSERTINTO表名VALUES(值1,值2,...);-- 批量插入INSERTINTO表名(字段1,字段2)VALUES(值1,值2),(值3,值4),(值5,值6);2. 修改数据 UPDATEUPDATE表名SET字段1值1,字段2值2[WHERE条件];⚠️ 核心注意不加WHERE条件会修改整张表的所有行生产环境严禁裸写UPDATE。3. 删除数据-- 删除指定行DELETEFROM表名[WHERE条件];-- 清空整张表TRUNCATETABLE表名;4. DELETE 与 TRUNCATE 核心区别必考简答对比项DELETETRUNCATE语言类型DMLDDL条件筛选可加WHERE删除指定行只能清空全表自增计数器不重置继续自增重置为初始值事务回滚支持回滚不支持回滚执行速度慢逐行删除快直接重建表触发器会触发DELETE触发器不会触发触发器四、DQL 数据查询语言分值最高大题必考1. 语法结构与执行顺序必考书写顺序SELECT[DISTINCT]列名/表达式FROM表名[JOIN关联表ON连接条件][WHERE行过滤条件][GROUPBY分组字段][HAVING分组后过滤条件][ORDERBY排序字段ASC/DESC][LIMIT偏移量,条数];执行顺序必背FROM→ON→JOIN→WHERE→GROUP BY→ 聚合函数计算 →HAVING→SELECT→DISTINCT→ORDER BY→LIMIT易错点WHERE中不能使用SELECT定义的别名因为WHERE执行在SELECT之前HAVING同理。2. 基础查询去重SELECT DISTINCT 字段 FROM 表;别名字段名 AS 别名AS可省略别名有空格需加反引号。空值处理函数IFNULL(字段, 默认值)字段为NULL时返回默认值。字符串拼接CONCAT(字段1, 字段2, ...)3. WHERE 条件查询运算符分类语法说明比较运算符、、!、、、、 与 ! 均表示不等于逻辑运算符AND、OR、NOTAND优先级高于OR范围查询BETWEEN 最小值 AND 最大值闭区间包含两端值枚举查询IN (值1, 值2, …)字段值在指定集合内模糊查询LIKE ‘通配符’%匹配任意多个字符_匹配单个字符空值判断IS NULL / IS NOT NULL不能用 NULL 判断空值4. 常用单行函数字符串函数LENGTH()字节长度、SUBSTRING(str,pos,len)截取、UPPER()/LOWER()大小写数值函数ROUND(x,d)四舍五入、CEIL()向上取整、FLOOR()向下取整日期函数NOW()当前日期时间、CURDATE()当前日期、DATEDIFF(date1,date2)日期差流程控制IF(条件, 成立值, 不成立值)、CASE WHEN 条件 THEN 结果 END5. 聚合函数分组专用COUNT(*)统计总行数包含NULL值行性能最优COUNT(字段)统计该字段非空的行数SUM(字段)求和忽略NULLAVG(字段)求平均值忽略NULLMAX(字段)最大值MIN(字段)最小值6. 分组查询 GROUP BYSELECT分组字段,聚合函数FROM表名GROUPBY分组字段;规则SELECT后只能写分组字段 聚合函数SQL标准。WHERE与HAVING的区别必考简答执行阶段不同WHERE在分组前过滤HAVING在分组后过滤过滤对象不同WHERE过滤原始行数据HAVING过滤分组结果聚合函数WHERE后不能跟聚合函数HAVING后可以7. 排序与分页-- 排序ASC升序默认DESC降序支持多字段排序ORDERBY字段1DESC,字段2ASC;-- 分页LIMIT 偏移量, 每页条数LIMIT0,5;-- 第1页5条分页公式LIMIT (当前页-1)*每页条数, 每页条数8. 多表连接查询大题核心分类与语法内连接 INNER JOIN只返回两张表中匹配成功的行不匹配的丢弃SELECT*FROM表AINNERJOIN表BON表A.关联字段表B.关联字段;左外连接 LEFT JOIN左表所有行全部保留右表无匹配数据时显示NULLSELECT*FROM表ALEFTJOIN表BON表A.关联字段表B.关联字段;右外连接 RIGHT JOIN右表所有行全部保留左表无匹配数据时显示NULL易错点左连接中ON是连接时过滤不影响左表保留WHERE是连接后过滤会过滤掉NULL行。自连接一张表自己和自己连接通过别名区分常用于树形结构、上下级关系SELECTe.name,m.nameFROMemployee eLEFTJOINemployee mONe.manager_idm.id;9. 子查询嵌套在SQL语句中的查询语句又称嵌套查询。标量子查询返回单个值配合 等比较运算符使用列子查询返回一列多行配合IN、ANY、ALL使用 ANY(子查询)大于子查询结果中的最小值 ALL(子查询)大于子查询结果中的最大值行子查询返回一行多列用较少表子查询返回多行多列放在FROM后作为派生表必须起别名EXISTS 子查询相关子查询判断子查询是否有结果返回布尔值有匹配数据则保留外查询行10. 合并查询 UNIONSELECT语句1UNION[ALL]SELECT语句2;要求前后两条查询的列数、对应列的数据类型必须一致UNION自动去重速度慢UNION ALL不去重速度快开发优先使用五、事务与ACID特性简答/选择必考1. 基本概念事务是一组不可分割的SQL操作要么全部执行成功要么全部执行失败回滚。仅InnoDB引擎支持事务。2. 事务四大特性 ACID原子性Atomicity事务是最小执行单元不可拆分要么全成功要么全失败回滚。一致性Consistency事务执行前后数据的完整性和业务规则保持一致。隔离性Isolation多个并发事务之间相互隔离互不干扰。持久性Durability事务一旦提交对数据的修改就永久生效断电/宕机不会丢失。3. 事务基本操作STARTTRANSACTION;-- 开启事务或 BEGIN-- 执行DML语句COMMIT;-- 提交事务永久生效ROLLBACK;-- 回滚事务撤销所有操作SAVEPOINT保存点名;-- 设置保存点ROLLBACKTO保存点名;-- 回滚到指定保存点4. 事务并发三大问题脏读一个事务读到了另一个事务未提交的数据。不可重复读同一事务内两次读取同一行数据结果不一致中间被其他事务修改并提交。幻读同一事务内两次执行相同的范围查询行数不一致中间被其他事务插入/删除行并提交。5. 事务隔离级别必考隔离级别脏读不可重复读幻读说明读未提交 READ UNCOMMITTED存在存在存在隔离最低性能最高基本不用读已提交 READ COMMITTED解决存在存在Oracle默认级别可重复读 REPEATABLE READ解决解决理论存在MySQL InnoDB默认级别通过MVCC临键锁解决了幻读串行化 SERIALIZABLE解决解决解决隔离最高性能最差并发极低六、索引选择/简答高频1. 索引本质与优缺点本质帮助MySQL高效查询数据的数据结构InnoDB默认使用B树实现。优点大幅提升查询速度减少IO次数加速排序和分组。缺点占用磁盘空间降低增删改的效率需要同步维护索引。2. 索引分类主键索引主键自动创建唯一且非空一张表只有一个。唯一索引字段值唯一允许多个NULL。普通索引最基础的索引仅用于加速查询无特殊限制。联合索引复合索引多个字段组合创建的索引遵循最左前缀原则。全文索引、空间索引了解即可期末很少考。3. 最左前缀原则联合索引查询时必须从最左侧的字段开始匹配跳过中间字段则后面的字段索引失效。例联合索引idx_a_b_c(a,b,c)生效a、a,b、a,b,c失效b、b,c、a,cc索引失效只有a生效4. 索引失效常见场景简答高频联合索引不满足最左前缀原则索引列上执行运算、函数操作、类型转换使用!、、NOT IN反向查询LIKE模糊查询以%开头前置通配符字符串类型字段不加引号发生隐式类型转换OR连接的条件中有一侧字段没有索引联合索引中范围查询、、between右侧的字段索引失效5. 索引基本操作-- 创建普通索引CREATEINDEX索引名ON表名(字段名);-- 创建唯一索引CREATEUNIQUEINDEX索引名ON表名(字段名);-- 创建联合索引CREATEINDEX索引名ON表名(字段1,字段2,字段3);-- 查看索引SHOWINDEXFROM表名;-- 删除索引DROPINDEX索引名ON表名;七、视图、存储过程、触发器1. 视图 VIEW定义虚拟表本身不存储数据数据来自底层基表存储的是一条SELECT查询语句。语法-- 创建视图CREATEVIEW视图名ASSELECT查询语句;-- 使用视图和普通表一样查询SELECT*FROM视图名;-- 删除视图DROPVIEWIFEXISTS视图名;作用简化复杂多表查询、隐藏敏感字段、统一数据访问口径。注意包含聚合、分组、多表连接的视图一般无法更新。2. 存储过程 PROCEDURE定义封装一组SQL语句预编译后存储在数据库中可重复调用减少网络传输。基础语法-- 修改语句结束符避免与存储过程内的分号冲突DELIMITER//CREATEPROCEDURE存储过程名(IN入参名 类型,OUT出参名 类型)BEGIN-- SQL语句SELECT*FROM表名;END//DELIMITER;-- 恢复默认结束符-- 调用存储过程CALL存储过程名(参数值,变量名);-- 查看输出参数SELECT变量名;参数类型IN输入参数、OUT输出参数、INOUT输入输出参数。3. 触发器 TRIGGER定义在表发生增/删/改操作时自动触发执行的SQL代码。三要素触发时机BEFORE/AFTER触发事件INSERT/UPDATE/DELETE触发对象表每行触发新旧数据INSERT操作只有NEW代表新增的数据DELETE操作只有OLD代表删除前的数据UPDATE操作NEW代表修改后的数据OLD代表修改前的数据八、存储引擎与锁机制1. InnoDB 与 MyISAM 核心区别必考对比项InnoDB默认MyISAM事务支持支持不支持外键支持支持不支持锁粒度行级锁默认、表级锁仅表级锁并发性能高低崩溃恢复支持安全性高不支持易丢数据全文索引5.6后支持原生支持性能更好适用场景业务核心表、需要事务/并发场景静态数据、读多写少的报表2. 锁机制表级锁锁定整张表开销小、加锁快并发度低不会出现死锁。行级锁仅锁定操作的行开销大、加锁慢并发度高可能出现死锁。注意InnoDB的行锁是通过索引实现的没有命中索引会升级为表锁。死锁多个事务互相持有对方需要的锁永久等待。InnoDB会自动检测死锁回滚代价最小的事务。九、DCL 数据控制语言用户与权限1. 用户管理-- 创建用户CREATEUSER用户名登录主机IDENTIFIEDBY密码;-- 登录主机% 表示任意IPlocalhost表示本地192.168.1.% 指定网段-- 删除用户DROPUSER用户名登录主机;-- 修改密码ALTERUSER用户名登录主机IDENTIFIEDBY新密码;2. 权限管理-- 授权GRANT权限1,权限2ON库名.表名TO用户名登录主机;-- 所有权限ALL PRIVILEGES所有库表*.*-- 回收权限REVOKE权限1,权限2ON库名.表名FROM用户名登录主机;-- 刷新权限修改权限后执行FLUSHPRIVILEGES;十、期末高频简答题标准作答WHERE和HAVING的区别答①执行时机不同WHERE在分组前执行HAVING在分组后执行②过滤对象不同WHERE过滤原始表的行数据HAVING过滤分组后的结果集③聚合函数WHERE后不能使用聚合函数HAVING后可以使用聚合函数进行条件过滤。DELETE和TRUNCATE的区别答①语言类型DELETE是DMLTRUNCATE是DDL②删除范围DELETE可加WHERE删除指定行TRUNCATE只能清空全表③自增重置DELETE不重置自增计数器TRUNCATE会重置④事务DELETE支持回滚TRUNCATE不支持⑤执行速度DELETE逐行删除速度慢TRUNCATE直接重建表速度快。事务的四大ACID特性答原子性事务是不可分割的最小单元要么全成功要么全失败一致性事务前后数据完整性和业务规则保持一致隔离性并发事务之间相互隔离互不干扰持久性事务提交后数据修改永久生效故障不会丢失。索引的优缺点答优点大幅提升查询效率减少磁盘IO加速排序和分组操作。缺点占用额外的磁盘空间降低增删改的性能需要同步维护索引结构。内连接、左连接、右连接的区别答内连接只保留两张表匹配成功的行左连接保留左表全部数据右表无匹配时显示NULL右连接保留右表全部数据左表无匹配时显示NULL。十一、SQL大题解题通用步骤建表题先分析实体与字段确定数据类型再标注主键、外键、非空、默认值等约束最后按标准语法书写。单表查询先确定筛选条件WHERE再确定分组/聚合接着排序最后分页。多表查询先找到表之间的关联字段确定连接类型内/左连接写ON连接条件再添加筛选、分组、排序。事务题先开启事务执行DML语句判断业务结果成功则COMMIT失败则ROLLBACK。