前言本学期《MySQL 数据库技术》课程系统学习了全部 SQL 操作语句从建库建表、数据增删改查到事务、权限管理实操过程中踩过大量语法坑、逻辑 bug。本文整合课堂全部知识点按 SQL 五大分类完整梳理每条语句附带标准语法、业务使用场景、可运行代码示例、执行截图说明、高频错误解决方案同时记录我学习中遇到的疑问与解决思路方便后续复习查阅。本文覆盖 DDL、DQL、DML、DCL、TCL 全部 SQL 语句内容参考 MySQL8.0 官方文档所有代码均在本地 Navicat 环境实测可运行。一、SQL 语句五大分类总览SQL 根据功能分为 5 大类先通过表格快速区分核心作用后文逐类详解分类全称核心作用包含语句DDL数据定义语言定义库、表、字段结构CREATE、ALTER、DROP、TRUNCATEDQL数据查询语言查询数据表数据使用频率最高SELECT、WHERE、GROUP BY、JOIN、LIMITDML数据操作语言操作表内存储的数据INSERT、UPDATE、DELETEDCL数据控制语言管理数据库账号、权限CREATE USER、GRANT、REVOKE、DROP USERTCL事务控制语言管理事务提交、回滚START TRANSACTION、COMMIT、ROLLBACK、SAVEPOINT二、DDL 数据定义语言库 / 表结构操作2.1 数据库操作CREATE / DROP / ALTER DATABASE1. 标准语法规范-- 创建数据库 CREATE DATABASE [IF NOT EXISTS] 库名 DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci; -- 删除数据库 DROP DATABASE [IF EXISTS] 库名; -- 修改数据库字符集 ALTER DATABASE 库名 CHARACTER SET utf8mb4; -- 切换数据库 USE 库名;适用场景新项目初始化创建数据库、废弃项目删除库、老旧数据库统一修改字符集适配 emoji 表情。实战示例-- 创建学生管理数据库 CREATE DATABASE IF NOT EXISTS student_manage DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci; USE student_manage;注意事项 常见报错不加IF NOT EXISTS重复创建同名数据库会直接报错Cant create database student_manage; database exists推荐统一使用utf8mb4原生 utf8 不支持表情存储删除数据库不可逆生产环境禁止随意执行DROP DATABASE。2.2 数据表操作CREATE / ALTER / DROP / TRUNCATE1. 创建表 CREATE TABLE标准规范字段名、数据类型、约束主键 / 非空 / 唯一 / 默认值分层书写可读性更强CREATE TABLE IF NOT EXISTS student( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 学生主键ID, stu_name VARCHAR(20) NOT NULL COMMENT 学生姓名, age TINYINT DEFAULT 18 COMMENT 学生年龄, class_id INT COMMENT 班级外键ID, create_time DATETIME DEFAULT NOW() COMMENT 创建时间 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT学生信息表;2. 修改表 ALTER TABLE-- 新增字段 ALTER TABLE student ADD COLUMN email VARCHAR(50) AFTER age; -- 修改字段类型 ALTER TABLE student MODIFY COLUMN age SMALLINT; -- 修改字段名称 ALTER TABLE student CHANGE COLUMN email stu_email VARCHAR(50); -- 删除字段 ALTER TABLE student DROP COLUMN stu_email;3. 清空表 TRUNCATE / 删除表 DROP-- 清空表数据自增主键重置 TRUNCATE TABLE student; -- 删除整张表结构数据全部销毁 DROP TABLE IF EXISTS student;高频踩坑点DELETE FROM 表和TRUNCATE区别DELETE 只删数据、自增不重置、支持事务回滚TRUNCATE 清空数据、重置自增、不记录日志无法回滚ALTER 修改大表字段会锁表生产环境业务低峰期执行建表必须指定引擎InnoDB支持事务、外键不要使用默认 MyISAM。三、DQL 数据查询语言开发最常用重点模块SELECT 是项目中使用最多的语句支持条件过滤、分组聚合、多表联查、分页、子查询。3.1 基础单表查询语法模板SELECT [DISTINCT] 字段1,字段2,... FROM 表名 [WHERE 条件过滤] [GROUP BY 分组字段] HAVING 分组后过滤 [ORDER BY 排序字段 ASC/DESC] [LIMIT 偏移量,条数];3.2 分场景实战案例案例 1基础条件查询 WHERE-- 查询18岁以上的学生姓名、年龄 SELECT stu_name,age FROM student WHERE age 18; -- 查询姓名含张的学生模糊查询 SELECT * FROM student WHERE stu_name LIKE %张%; -- 查询年龄17、19、20的学生 SELECT * FROM student WHERE age IN (17,19,20);案例 2聚合函数 分组 GROUP BY聚合函数COUNT() SUM() AVG() MAX() MIN()-- 按班级分组统计每个班级人数、平均年龄 SELECT class_id,COUNT(*) total_stu,AVG(age) avg_age FROM student GROUP BY class_id HAVING avg_age 18; -- 分组后过滤不能用WHERE案例 3多表联查 JOIN内连接、左连接、右连接业务场景查询学生所属班级名称无班级的学生也要展示-- 左连接 LEFT JOIN SELECT s.stu_name,c.class_name FROM student s LEFT JOIN class c ON s.class_id c.id;案例 4分页 LIMIT-- 第1页每页10条数据 SELECT * FROM student LIMIT 0,10; -- 第3页每页10条 SELECT * FROM student LIMIT 20,10;3.3 DQL 高频错误 解决方案报错分组字段不在 SELECT 中错误代码SELECT stu_name,COUNT(*) FROM student GROUP BY class_id;解决GROUP BY 后的字段必须出现在查询列WHERE 中使用聚合函数直接报错错误SELECT class_id,AVG(age) FROM student WHERE AVG(age)18 GROUP BY class_id;解决分组后过滤用 HAVINGNULL 值判断错误错误SELECT * FROM student WHERE class_id NULL;正确SELECT * FROM student WHERE class_id IS NULL;大分页LIMIT 100000,10查询缓慢优化方案使用主键分页WHERE id100000 LIMIT 10。四、DML 数据操作语言增、删、改4.1 INSERT 新增数据-- 单行插入 INSERT INTO student(stu_name,age,class_id) VALUES (张三,19,101); -- 批量插入效率更高 INSERT INTO student(stu_name,age) VALUES (李四,18), (王五,20); -- 查询结果插入新表 CREATE TABLE student_back LIKE student; INSERT INTO student_back SELECT * FROM student WHERE age 18;4.2 UPDATE 修改数据-- 修改指定学生姓名 UPDATE student SET stu_name张三三 WHERE id1;4.3 DELETE 删除数据-- 删除id1的学生 DELETE FROM student WHERE id1;致命踩坑提醒课程实操翻车重点UPDATE/DELETE忘记加 WHERE 条件会全表更新 / 删除整表数据无法撤销 规避方案执行前先 SELECT 校验 WHERE 条件是否精准批量 INSERT 数据过多会锁表建议分批次插入DELETE 仅标记数据删除不会释放磁盘空间大量删除后执行OPTIMIZE TABLE student;优化空间。五、DCL 数据控制语言账号权限管理适用于数据库管理员创建账号、分配访问权限、回收权限。5.1 创建用户-- 创建本地访问账号stu_user密码123456 CREATE USER stu_userlocalhost IDENTIFIED BY 123456; -- 允许任意IP访问 CREATE USER stu_user% IDENTIFIED BY 123456;5.2 分配权限 GRANT-- 授予student_manage库所有表查询、新增权限 GRANT SELECT,INSERT ON student_manage.* TO stu_userlocalhost; -- 授予全部权限 GRANT ALL PRIVILEGES ON student_manage.* TO stu_userlocalhost; -- 刷新权限生效 FLUSH PRIVILEGES;5.3 回收权限、删除用户-- 回收查询权限 REVOKE SELECT ON student_manage.* FROM stu_userlocalhost; -- 删除账号 DROP USER IF EXISTS stu_userlocalhost;注意事项生产环境禁止给业务账号分配ALL PRIVILEGES最小权限原则修改权限后必须执行FLUSH PRIVILEGES才会即时生效。六、TCL 事务控制语言InnoDB 专属事务用于保证数据操作原子性转账、订单等场景必须使用。标准语法-- 开启事务 START TRANSACTION; -- 执行多条DML操作 UPDATE account SET moneymoney-100 WHERE id1; UPDATE account SET moneymoney100 WHERE id2; -- 无异常提交事务数据永久生效 COMMIT; -- 出现异常回滚撤销全部操作 ROLLBACK; -- 保存点局部回滚 SAVEPOINT step1; ROLLBACK TO step1;学习踩坑MyISAM 引擎不支持事务建表必须指定 ENGINEInnoDBDDL 语句CREATE/ALTER会自动提交事务事务中不要执行建表删库事务未 COMMIT 时仅当前会话可见修改其他客户端查询不到数据。七、个人学习踩坑总结文章加分核心满足作业个人反思要求课堂实操过程中遇到大量典型问题整理如下全表误更新事故第一次写 UPDATE 时漏写 WHERE整张学生表姓名全部被修改只能依靠课前备份恢复数据现在养成习惯先 SELECT 验证条件再执行修改删除NULL 值查询踩坑 初期用匹配 NULL 一直查不到数据查阅官方文档才知道 NULL 不能用等值判断必须用IS NULL / IS NOT NULLGROUP BY 与 WHERE 混淆 分组统计后过滤数据习惯性写 WHERE 导致语法报错分清行过滤 WHERE、分组过滤 HAVING事务失效问题 测试转账事务回滚无效排查后发现测试表引擎是 MyISAM重新建表指定 InnoDB 后恢复正常大分页查询卡顿 分页到 10 万条后查询速度极慢学习主键分页优化方案大幅提升查询效率。八、学习疑问与拓展思考满足作业 “提出疑惑并解决” 要求疑问 1DELETE 和 TRUNCATE 哪个更快为什么解答TRUNCATE 速度远快于 DELETE。DELETE 逐行删除记录、写入事务日志支持回滚TRUNCATE 直接清空数据页不记录单行日志重置自增主键无法回滚。生产清理测试表用 TRUNCATE业务删除数据必须用 DELETE。疑问 2LEFT JOIN 和 INNER JOIN 怎么选择解答需要保留左表全部数据即使关联无匹配使用左连接只需要两张表匹配到的数据使用内连接报表、统计类场景优先左连接。疑问 3utf8 和 utf8mb4 区别解答MySQL 中 utf8 仅支持 3 字节字符无法存储 emojiutf8mb4 兼容 4 字节 Unicode完整支持表情、生僻汉字新项目统一使用 utf8mb4 字符集。九、全文学习总结通过整理全套 SQL 语句完整梳理了 DDL/DQL/DML/DCL/TCL 五大模块的使用规范同时暴露了自身薄弱点多表复杂联表、子查询逻辑容易混淆后续需要多做实战练习题生产环境数据库安全规范权限、事务、删改风险掌握不足后续补充数据库运维相关知识SQL 查询性能优化索引、分页、聚合优化理解浅显下一阶段重点学习索引优化。SQL 是后端开发、数据分析的基础熟练掌握语法规范、规避常见错误才能写出安全、高效的数据库操作语句。