MySQL 全套 SQL 语句知识整理｜语法、实战场景、易错点汇总

MySQL全套SQL语句知识整理｜语法、实战场景、易错点汇总

一、前言

本学期我系统学习了MySQL数据库课程，掌握了数据库结构设计、数据操作、权限管理、事务机制等核心知识点。
为了巩固课堂所学，我将本学期所有高频SQL语句进行系统化整理。
本文严格按照 标准语法 + 业务场景 + 高频易错点 + 可运行实战代码 结构编写，所有SQL均可在Navicat直接执行。
同时文末记录了我学习中遇到的真实疑问，并通过自主查阅资料、实操验证得出解决方案，完整满足课程作业考核要求。

二、SQL四大语句分类总览

MySQL的SQL语句根据功能可分为四大类别，覆盖数据库的所有操作场景：

• DDL数据定义语言：用于操作数据库、数据表、索引等结构，不操作具体数据
• DML数据操作语言：用于增删改查业务数据，是日常开发使用最多的语句
• DCL数据控制语言：用于管理数据库账号、分配和回收访问权限
• TCL事务控制语言：用于控制事务提交与回滚，保障业务数据一致性

三、DDL 数据定义语言（库、表、索引）

3.1 数据库操作 CREATE / DROP / ALTER

1. 标准语法规范

-- 创建数据库（规避重复创建报错，标准企业写法）CREATEDATABASEIFNOTEXISTS数据库名DEFAULTCHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ci;-- 删除数据库DROPDATABASEIFEXISTS数据库名;-- 修改数据库字符集ALTERDATABASE数据库名CHARACTERSETutf8mb4;-- 切换当前操作的数据库USE数据库名;

2. 业务应用场景

• 项目初始化：创建独立的业务数据库，隔离不同项目的数据
• 项目下线：测试项目废弃时可删除无用数据库
• 字符集统一：解决中文乱码、Emoji表情无法存储的问题

3. 高频易错点

• 未添加 IF NOT EXISTS：重复创建同名数据库会直接报错
• 字符集选择错误：使用 utf8 字符集无法存储生僻汉字和 Emoji 表情，必须使用 utf8mb4
• 忘记切换数据库：未执行 USE 切换数据库，后续建表、操作数据会默认存入系统库

4. 可直接运行实战案例

-- 创建学生管理专用数据库CREATEDATABASEIFNOTEXISTSstudent_dbDEFAULTCHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ci;-- 切换到新创建的数据库USEstudent_db;-- 修改数据库字符集（如果需要）ALTERDATABASEstudent_dbCHARACTERSETutf8mb4;-- 删除数据库（谨慎操作）-- DROP DATABASE IF EXISTS student_db;

3.2 数据表核心操作（CREATE / DROP / TRUNCATE / ALTER）

1. 标准语法规范

-- 创建数据表CREATETABLEIFNOTEXISTS表名(字段名1数据类型 约束条件,字段名2数据类型 约束条件)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;-- 删除整张数据表（结构+数据全部删除）DROPTABLEIFEXISTS表名;-- 清空表中所有数据，保留表结构TRUNCATETABLE表名;-- 修改数据表结构ALTERTABLE表名ADD/MODIFY/DROP字段信息;

2. 业务应用场景

• CREATE TABLE：根据业务需求创建数据表存储数据
• DROP TABLE：业务废弃时删除数据表
• TRUNCATE TABLE：测试环境需要批量清空冗余数据时使用
• ALTER TABLE：项目迭代更新字段时修改表结构

3. 高频易错点汇总

• VARCHAR类型未指定长度：导致数据存储异常
• 数据表未设置主键：无唯一标识，不符合数据库设计规范
• TRUNCATE属于DDL语句：不支持事务回滚，误操作无法恢复数据
• ALTER修改大表字段会触发锁表：线上业务禁止随意操作

4. 可直接运行实战案例

-- 创建学生信息表CREATETABLEIFNOTEXISTSstudent(idINTPRIMARYKEYAUTO_INCREMENTCOMMENT'学生编号',nameVARCHAR(20)NOTNULLCOMMENT'学生姓名',ageTINYINTCOMMENT'学生年龄',class_idINTCOMMENT'所属班级编号')ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COMMENT'学生信息数据表';

3.3 索引相关操作（CREATE / DROP）

1. 标准语法规范

-- 创建普通索引CREATEINDEX索引名ON表名(字段名);-- 创建唯一索引CREATEUNIQUEINDEX索引名ON表名(字段名);-- 删除索引DROPINDEX索引名ON表名;

2. 业务应用场景

针对项目中高频查询、条件筛选的字段建立索引，大幅降低数据库查询耗时，优化页面数据加载速度。

3. 高频易错点汇总

• 索引并非越多越好：过多索引会降低增删改数据的执行效率
• 前置模糊查询、字段隐式转换：会直接导致索引失效

4. 可直接运行实战案例

-- 为学生姓名字段建立普通索引，优化姓名查询速度CREATEINDEXidx_student_nameONstudent(name);

四、DML 数据操作语言（增删改查核心业务）

4.1 数据新增（INSERT）

1. 标准语法规范

-- 单条数据插入INSERTINTO表名(字段1,字段2)VALUES(值1,值2);-- 批量数据插入（效率远高于单条插入）INSERTINTO表名(字段1,字段2)VALUES(值1,值2),(值3,值4);

2. 业务应用场景

用户注册、表单提交、批量导入Excel数据等场景，用于向数据库写入业务数据。

3. 高频易错点汇总

插入的字段数量、值的数量、数据类型必须一一对应；非空约束字段禁止传入NULL，否则会直接报错。

4. 可直接运行实战案例

-- 批量插入学生数据INSERTINTOstudent(name,age,class_id)VALUES('张三',18,101),('李四',19,101);

4.2 数据修改（UPDATE）

1. 标准语法规范

UPDATE表名SET字段1=值1,字段2=值2WHERE筛选条件;

2. 业务应用场景

用户修改个人资料、更新商品库存、调整订单状态、修改业务数据等场景。

3. 高频易错点汇总

绝对不能省略WHERE条件，无条件UPDATE会更新整张表所有数据，造成严重数据事故。

4. 可直接运行实战案例

-- 修改张三的年龄信息UPDATEstudentSETage=20WHEREname='张三';

4.3 数据删除（DELETE）

1. 标准语法规范

DELETEFROM表名WHERE筛选条件;

2. 业务应用场景

删除过期数据、无效数据、用户注销数据等精细化数据删除场景。

3. 高频易错点汇总

省略WHERE条件会清空全表数据；DELETE删除不会重置自增主键，支持事务回滚，安全性更高。

4. 可直接运行实战案例

-- 删除指定编号的学生数据DELETEFROMstudentWHEREid=2;

4.4 数据查询（SELECT）

1. 完整语法框架

SELECT字段列表FROM表名WHERE行数据筛选条件GROUPBY分组字段HAVING分组后筛选ORDERBY排序字段ASC/DESCLIMIT偏移量,展示条数;

2. 业务应用场景

后台列表展示、前端页面数据渲染、数据统计报表、条件筛选查询等所有数据读取场景。

3. 高频易错点汇总

• WHERE与HAVING混用：WHERE筛选原始数据，HAVING仅能筛选分组后的聚合数据，二者不可混用
• 大数据分页效率低：偏移量过大会导致查询效率极低
• 多表联查未加关联条件：会产生笛卡尔积，造成数据冗余、查询卡顿

4. 可直接运行实战案例

-- 统计各班学生人数，筛选人数大于1的班级，按人数降序排序，取前5条数据SELECTclass_id,COUNT(id)student_countFROMstudentGROUPBYclass_idHAVINGCOUNT(id)>1ORDERBYstudent_countDESCLIMIT0,5;

五、DCL 数据控制语言（权限管理）

1. 标准语法规范

-- 授予用户权限GRANT权限1,权限2ON数据库.表TO'账号'@'访问地址'IDENTIFIEDBY'密码';-- 回收用户权限REVOKE权限ON数据库.表FROM'账号'@'访问地址';

2. 业务应用场景

为开发人员分配测试库读写权限，为运维人员分配只读权限，实现数据库权限分级管理，保障数据安全。

3. 高频易错点汇总

权限修改后必须执行FLUSH PRIVILEGES;刷新权限，修改才能生效。

4. 可直接运行实战案例

-- 创建测试账号，赋予学生数据库查询权限GRANTSELECTONstudent_db.*TO'test'@'%'IDENTIFIEDBY'123456';FLUSHPRIVILEGES;

六、TCL 事务控制语言（数据一致性保障）

1. 核心语法规范

STARTTRANSACTION;-- 手动开启事务-- 执行多条关联DML操作COMMIT;-- 提交事务，数据永久生效ROLLBACK;-- 回滚事务，撤销所有操作

2. 业务应用场景

转账、订单扣库存、多表联动修改等关联性极强的业务，保证所有操作要么全部成功、要么全部失败，杜绝数据错乱。

3. 高频易错点汇总

DDL语句会自动提交事务，无法回滚；MyISAM引擎不支持事务，必须使用InnoDB引擎。

4. 可直接运行实战案例

-- 事务实操演示STARTTRANSACTION;UPDATEstudentSETage=21WHEREid=1;COMMIT;

七、学习疑问与实操解决方案（作业必填）

7.1 疑问一：DELETE 与 TRUNCATE 清空数据的核心区别

我的疑惑：两种语句都能清空表数据，不清楚实际开发中该如何选择。

查阅渠道：MySQL官方文档、数据库实操教程、技术博客。

实操测试代码

CREATETABLEtest(idINTPRIMARYKEYAUTO_INCREMENT,nameVARCHAR(10));INSERTINTOtest(name)VALUES('A');DELETEFROMtest;INSERTINTOtest(name)VALUES('B');TRUNCATETABLEtest;INSERTINTOtest(name)VALUES('C');

最终总结：DELETE是DML语句，逐行删除数据、不重置自增主键、支持事务回滚，适合删除部分数据；TRUNCATE是DDL语句，清空数据页、重置自增主键、无法回滚，适合批量清空全表测试数据。

7.2 疑问二：MySQL索引失效的常见场景与优化方案

我的疑惑：建立索引后查询依然缓慢，不清楚索引失效的原因。

查阅渠道：MySQL性能优化手册、实战踩坑案例。

失效场景总结：前置模糊查询、字段隐式类型转换、OR无索引字段、索引字段使用函数计算，都会导致索引失效。

优化方案：改写查询语句，避免对索引字段进行加工处理，保证索引正常生效。

八、全文学习总结与心得

通过本次MySQL SQL语句系统整理，我完整掌握了DDL、DML、DCL、TCL四大类核心语句的使用方法。在所有SQL操作中，DML增删改查是开发核心，编写语句必须规范严谨，尤其是UPDATE和DELETE必须添加WHERE条件，杜绝全表操作事故。同时我掌握了索引优化、事务使用、权限管理的核心要点，理解了开发中的高频踩坑点。后续我会继续加强SQL实操练习，提升代码规范性和问题排查能力。