企业官网建设流程全解析

MySQL知识点

一、数据库基础理论（选择/填空/简答高频）

1. 核心概念

• DB（数据库）：长期存储在计算机中、有组织、可共享的数据集合。
• DBMS（数据库管理系统）：操纵和管理数据库的系统软件，MySQL、Oracle、SQL Server均属于DBMS。
• DBS（数据库系统）：由数据库、DBMS、应用程序、数据库管理员（DBA）、用户、硬件平台共同组成的完整系统。
• SQL：结构化查询语言，是关系型数据库的通用标准操作语言。

2. SQL语言五大分类

3. 关系型数据库三大范式（必考）

1. 第一范式（1NF）：字段原子性，每一列不可再拆分，一个单元格只能存一个值。
2. 第二范式（2NF）：满足1NF，消除部分函数依赖；非主键字段必须完全依赖整个主键，不能只依赖主键的一部分（主要针对复合主键）。
3. 第三范式（3NF）：满足2NF，消除传递函数依赖；非主键字段只能依赖主键，不能依赖其他非主键字段。

反范式设计：通过冗余字段减少表连接、提升查询速度，以空间换时间，是实际开发中的常用优化手段。

4. 关系完整性约束

1. 实体完整性：主键约束，主键值必须唯一且非空。
2. 参照完整性：外键约束，子表的外键值必须在主表主键中存在，或为NULL。
3. 域完整性：限制字段的取值范围，如数据类型、长度、非空、默认值、枚举约束。
4. 用户自定义完整性：根据业务自定义规则，如CHECK约束（MySQL 8.0.16后原生支持）。

二、DDL 数据定义语言

1. 数据库操作

-- 创建数据库并指定字符集CREATEDATABASEIFNOTEXISTS库名DEFAULTCHARSETutf8mb4COLLATEutf8mb4_general_ci;-- 查看所有数据库SHOWDATABASES;-- 查看建库语句SHOWCREATEDATABASE库名;-- 切换数据库USE库名;-- 删除数据库DROPDATABASEIFEXISTS库名;

2. 常用数据类型（选择填空高频）

数值类型

字符串类型

日期时间类型

3. 数据表操作

-- 创建表（标准写法）CREATETABLE表名(字段名1数据类型[约束条件],字段名2数据类型[约束条件],...[PRIMARYKEY(主键字段)],[FOREIGNKEY(外键字段)REFERENCES主表(主键字段)])ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;-- 查看表结构DESC表名;DESCRIBE表名;-- 查看建表语句SHOWCREATETABLE表名;-- 修改表结构ALTERTABLE表名ADD字段名 数据类型[约束];-- 新增字段ALTERTABLE表名MODIFY字段名 新数据类型[约束];-- 修改字段类型/约束ALTERTABLE表名 CHANGE 旧字段名 新字段名 数据类型;-- 修改字段名ALTERTABLE表名DROP字段名;-- 删除字段ALTERTABLE表名RENAMETO新表名;-- 修改表名-- 删除表DROPTABLEIFEXISTS表名;

4. 六大约束（核心考点）

三、DML 数据操纵语言

1. 插入数据 INSERT

-- 指定字段插入（推荐写法）INSERTINTO表名(字段1,字段2,...)VALUES(值1,值2,...);-- 全字段插入（省略字段名，值必须和表字段顺序完全对应）INSERTINTO表名VALUES(值1,值2,...);-- 批量插入INSERTINTO表名(字段1,字段2)VALUES(值1,值2),(值3,值4),(值5,值6);

2. 修改数据 UPDATE

UPDATE表名SET字段1=值1,字段2=值2[WHERE条件];

⚠️ 核心注意：不加WHERE条件会修改整张表的所有行，生产环境严禁裸写UPDATE。

3. 删除数据

-- 删除指定行DELETEFROM表名[WHERE条件];-- 清空整张表TRUNCATETABLE表名;

4. DELETE 与 TRUNCATE 核心区别（必考简答）

四、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 条件查询

4. 常用单行函数

• 字符串函数：LENGTH()字节长度、SUBSTRING(str,pos,len)截取、UPPER()/LOWER()大小写
• 数值函数：ROUND(x,d)四舍五入、CEIL()向上取整、FLOOR()向下取整
• 日期函数：NOW()当前日期时间、CURDATE()当前日期、DATEDIFF(date1,date2)日期差
• 流程控制：IF(条件, 成立值, 不成立值)、CASE WHEN 条件 THEN 结果 END

5. 聚合函数（分组专用）

• COUNT(*)：统计总行数，包含NULL值行，性能最优
• COUNT(字段)：统计该字段非空的行数
• SUM(字段)：求和，忽略NULL
• AVG(字段)：求平均值，忽略NULL
• MAX(字段)：最大值
• MIN(字段)：最小值

6. 分组查询 GROUP BY

SELECT分组字段,聚合函数FROM表名GROUPBY分组字段;

• 规则：SELECT后只能写分组字段 + 聚合函数（SQL标准）。
• WHERE与HAVING的区别（必考简答）：
  1. 执行阶段不同：WHERE在分组前过滤，HAVING在分组后过滤
  2. 过滤对象不同：WHERE过滤原始行数据，HAVING过滤分组结果
  3. 聚合函数：WHERE后不能跟聚合函数，HAVING后可以

7. 排序与分页

-- 排序：ASC升序（默认），DESC降序；支持多字段排序ORDERBY字段1DESC,字段2ASC;-- 分页：LIMIT 偏移量, 每页条数LIMIT0,5;-- 第1页，5条

分页公式：LIMIT (当前页-1)*每页条数, 每页条数

8. 多表连接查询（大题核心）

分类与语法

1. 内连接 INNER JOIN：只返回两张表中匹配成功的行，不匹配的丢弃

SELECT*FROM表AINNERJOIN表BON表A.关联字段=表B.关联字段;

2. 左外连接 LEFT JOIN：左表所有行全部保留，右表无匹配数据时显示NULL

SELECT*FROM表ALEFTJOIN表BON表A.关联字段=表B.关联字段;

3. 右外连接 RIGHT JOIN：右表所有行全部保留，左表无匹配数据时显示NULL

易错点：左连接中，ON是连接时过滤，不影响左表保留；WHERE是连接后过滤，会过滤掉NULL行。

4. 自连接：一张表自己和自己连接，通过别名区分，常用于树形结构、上下级关系

SELECTe.name,m.nameFROMemployee eLEFTJOINemployee mONe.manager_id=m.id;

9. 子查询

嵌套在SQL语句中的查询语句，又称嵌套查询。

1. 标量子查询：返回单个值，配合= > <等比较运算符使用
2. 列子查询：返回一列多行，配合IN、ANY、ALL使用
   • > ANY(子查询)：大于子查询结果中的最小值
   • > ALL(子查询)：大于子查询结果中的最大值
3. 行子查询：返回一行多列，用较少
4. 表子查询：返回多行多列，放在FROM后作为派生表，必须起别名
5. EXISTS 子查询：相关子查询，判断子查询是否有结果，返回布尔值；有匹配数据则保留外查询行

10. 合并查询 UNION

SELECT语句1UNION[ALL]SELECT语句2;

• 要求：前后两条查询的列数、对应列的数据类型必须一致
• UNION：自动去重，速度慢
• UNION ALL：不去重，速度快，开发优先使用

五、事务与ACID特性（简答/选择必考）

1. 基本概念

事务是一组不可分割的SQL操作，要么全部执行成功，要么全部执行失败回滚。仅InnoDB引擎支持事务。

2. 事务四大特性 ACID

1. 原子性（Atomicity）：事务是最小执行单元，不可拆分；要么全成功，要么全失败回滚。
2. 一致性（Consistency）：事务执行前后，数据的完整性和业务规则保持一致。
3. 隔离性（Isolation）：多个并发事务之间相互隔离，互不干扰。
4. 持久性（Durability）：事务一旦提交，对数据的修改就永久生效，断电/宕机不会丢失。

3. 事务基本操作

STARTTRANSACTION;-- 开启事务（或 BEGIN）-- 执行DML语句COMMIT;-- 提交事务，永久生效ROLLBACK;-- 回滚事务，撤销所有操作SAVEPOINT保存点名;-- 设置保存点ROLLBACKTO保存点名;-- 回滚到指定保存点

4. 事务并发三大问题

1. 脏读：一个事务读到了另一个事务未提交的数据。
2. 不可重复读：同一事务内，两次读取同一行数据，结果不一致（中间被其他事务修改并提交）。
3. 幻读：同一事务内，两次执行相同的范围查询，行数不一致（中间被其他事务插入/删除行并提交）。

5. 事务隔离级别（必考）

六、索引（选择/简答高频）

1. 索引本质与优缺点

• 本质：帮助MySQL高效查询数据的数据结构，InnoDB默认使用B+树实现。
• 优点：大幅提升查询速度，减少IO次数；加速排序和分组。
• 缺点：占用磁盘空间；降低增删改的效率（需要同步维护索引）。

2. 索引分类

1. 主键索引：主键自动创建，唯一且非空，一张表只有一个。
2. 唯一索引：字段值唯一，允许多个NULL。
3. 普通索引：最基础的索引，仅用于加速查询，无特殊限制。
4. 联合索引（复合索引）：多个字段组合创建的索引，遵循最左前缀原则。
5. 全文索引、空间索引：了解即可，期末很少考。

3. 最左前缀原则

联合索引查询时，必须从最左侧的字段开始匹配，跳过中间字段则后面的字段索引失效。
例：联合索引idx_a_b_c(a,b,c)

• 生效：a、a,b、a,b,c
• 失效：b、b,c、a,c（c索引失效，只有a生效）

4. 索引失效常见场景（简答高频）

1. 联合索引不满足最左前缀原则
2. 索引列上执行运算、函数操作、类型转换
3. 使用!=、<>、NOT IN反向查询
4. LIKE模糊查询以%开头（前置通配符）
5. 字符串类型字段不加引号，发生隐式类型转换
6. OR连接的条件中，有一侧字段没有索引
7. 联合索引中，范围查询（>、<、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 核心区别（必考）

2. 锁机制

1. 表级锁：锁定整张表，开销小、加锁快，并发度低，不会出现死锁。
2. 行级锁：仅锁定操作的行，开销大、加锁慢，并发度高，可能出现死锁。
   • 注意：InnoDB的行锁是通过索引实现的，没有命中索引会升级为表锁。
3. 死锁：多个事务互相持有对方需要的锁，永久等待。InnoDB会自动检测死锁，回滚代价最小的事务。

九、DCL 数据控制语言（用户与权限）

1. 用户管理

-- 创建用户CREATEUSER'用户名'@'登录主机'IDENTIFIEDBY'密码';-- 登录主机：% 表示任意IP，localhost表示本地，192.168.1.% 指定网段-- 删除用户DROPUSER'用户名'@'登录主机';-- 修改密码ALTERUSER'用户名'@'登录主机'IDENTIFIEDBY'新密码';

2. 权限管理

-- 授权GRANT权限1,权限2ON库名.表名TO'用户名'@'登录主机';-- 所有权限：ALL PRIVILEGES；所有库表：*.*-- 回收权限REVOKE权限1,权限2ON库名.表名FROM'用户名'@'登录主机';-- 刷新权限（修改权限后执行）FLUSHPRIVILEGES;

十、期末高频简答题标准作答

1. WHERE和HAVING的区别
   答：①执行时机不同：WHERE在分组前执行，HAVING在分组后执行；②过滤对象不同：WHERE过滤原始表的行数据，HAVING过滤分组后的结果集；③聚合函数：WHERE后不能使用聚合函数，HAVING后可以使用聚合函数进行条件过滤。

2. DELETE和TRUNCATE的区别
   答：①语言类型：DELETE是DML，TRUNCATE是DDL；②删除范围：DELETE可加WHERE删除指定行，TRUNCATE只能清空全表；③自增重置：DELETE不重置自增计数器，TRUNCATE会重置；④事务：DELETE支持回滚，TRUNCATE不支持；⑤执行速度：DELETE逐行删除速度慢，TRUNCATE直接重建表速度快。

3. 事务的四大ACID特性
   答：原子性：事务是不可分割的最小单元，要么全成功要么全失败；一致性：事务前后数据完整性和业务规则保持一致；隔离性：并发事务之间相互隔离互不干扰；持久性：事务提交后数据修改永久生效，故障不会丢失。

4. 索引的优缺点
   答：优点：大幅提升查询效率，减少磁盘IO；加速排序和分组操作。缺点：占用额外的磁盘空间；降低增删改的性能，需要同步维护索引结构。

5. 内连接、左连接、右连接的区别
   答：内连接只保留两张表匹配成功的行；左连接保留左表全部数据，右表无匹配时显示NULL；右连接保留右表全部数据，左表无匹配时显示NULL。

十一、SQL大题解题通用步骤

1. 建表题：先分析实体与字段，确定数据类型，再标注主键、外键、非空、默认值等约束，最后按标准语法书写。
2. 单表查询：先确定筛选条件（WHERE），再确定分组/聚合，接着排序，最后分页。
3. 多表查询：先找到表之间的关联字段，确定连接类型（内/左连接），写ON连接条件，再添加筛选、分组、排序。
4. 事务题：先开启事务，执行DML语句，判断业务结果，成功则COMMIT，失败则ROLLBACK。