1. 前言SQL 四大分类整体概述MySQL 中所有 SQL 语句按照功能划分为四大核心类别是本学期课堂全部核心学习内容数据库开发、运维、业务功能实现均围绕四类语句展开下面通过表格清晰区分四类语言核心定位本文整合课堂全部核心知识点、实战标准语法、避坑细节与完整可运行案例适合期末复习、面试背诵、开发查阅全文干货无废话建议收藏。2. DDL 数据定义语言库、表、索引操作DDL 用于修改数据库、数据表、索引的结构执行后会直接修改元数据部分语句无法通过事务回滚线上环境执行需要谨慎。2.1 数据库操作语句1创建数据库标准语法sqlCREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARACTER SET 字符集] [DEFAULT COLLATE 排序规则];实战示例sql-- 不存在则创建库指定utf8mb4字符集支持emoji表情 CREATE DATABASE IF NOT EXISTS student_db DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;使用规范 易错点必须添加IF NOT EXISTS避免数据库已存在抛出异常禁止使用 MySQL 原生utf8仅 3 字节编码无法存储表情符号数据库名称不能以数字开头、不能使用 MySQL 保留关键字如 user、order。2查看、切换、删除数据库sql-- 查询本机所有数据库 SHOW DATABASES; -- 切换至目标数据库后续操作默认作用于该库 USE student_db; -- 查看当前正在使用的数据库 SELECT DATABASE(); -- 删除数据库IF EXISTS防止库不存在报错 DROP DATABASE IF EXISTS student_db;⚠️ 踩坑提醒DROP DATABASE会直接清空库内全部数据表与数据生产环境禁止随意执行。2.2 数据表操作语句1创建数据表 CREATE TABLE完整标准语法sqlCREATE TABLE [IF NOT EXISTS] 表名( 字段1 数据类型 约束条件 COMMENT 字段注释, 字段2 数据类型 约束条件 COMMENT 字段注释, 表级约束 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT表注释;课堂实战学生表示例sqlCREATE 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 学生年龄默认值18, class_id INT COMMENT 所属班级外键ID, create_time DATETIME DEFAULT NOW() COMMENT 数据创建时间 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT学生信息表;规范要求存储引擎统一指定InnoDB唯一支持事务、外键约束MyISAM 无事务能力所有表、字段必须添加COMMENT注释便于后期维护阅读AUTO_INCREMENT自增属性仅支持整数类型字段。2修改表结构 ALTER TABLEsql-- 1.新增字段指定插入在age字段后方 ALTER TABLE student ADD COLUMN email VARCHAR(50) AFTER age; -- 2.仅修改字段数据类型 ALTER TABLE student MODIFY COLUMN age SMALLINT; -- 3.修改字段名称数据类型 ALTER TABLE student CHANGE COLUMN email stu_email VARCHAR(50); -- 4.删除指定字段 ALTER TABLE student DROP COLUMN stu_email; -- 5.修改数据表名称 ALTER TABLE student RENAME TO stu_info;⚠️ 易错点百万级数据大表直接执行 ALTER 会触发全表锁表阻塞业务读写线上需使用在线改表工具。3删除表 清空表数据sql-- 删除整张表表结构内部数据全部清除 DROP TABLE IF EXISTS student; -- 仅清空表内数据重置自增主键ID TRUNCATE TABLE student;TRUNCATE 与 DELETE 核心区别表格对比项TRUNCATEDELETE语句分类DDL 数据定义语言DML 数据操作语言事务支持不支持无法回滚支持事务可 ROLLBACK 恢复自增主键清空后重置为 1保留原有自增数值执行效率速度极快不记录单行日志逐条删除大数据量效率低2.3 索引操作语句索引用于提升查询速度但会降低插入、更新、删除性能频繁修改的字段不建议建索引。sql-- 创建普通索引 CREATE INDEX idx_stu_name ON student(stu_name); -- 创建唯一索引字段值全局不可重复 CREATE UNIQUE INDEX idx_stu_email ON student(email); -- 查看一张表全部索引 SHOW INDEX FROM student; -- 删除指定索引 DROP INDEX idx_stu_name ON student;3. DML 数据操作语言增删改查核心实战DML 是业务开发使用频率最高的语句针对表内数据进行操作支持事务回滚日常开发必须严格规范 WHERE 条件避免全表操作。3.1 INSERT 新增数据sql-- 指定字段单条插入 INSERT INTO student(stu_name,age) VALUES(张三,19); -- 批量插入推荐写法单次IO效率更高 INSERT INTO student(stu_name,age) VALUES(李四,20),(王五,18); -- 查询其他表数据批量插入本表 INSERT INTO stu_back(stu_name,age) SELECT stu_name,age FROM student WHERE age18;易错点字符串、日期类型数值必须包裹英文单引号批量插入单次数据不超过 1000 条避免数据包超限报错自增主键字段无需手动赋值。3.2 SELECT 查询语句课堂重点语法顺序固定不可颠倒完整标准语法顺序考试高频考点sqlSELECT 要查询的字段列表 FROM 数据表 JOIN 关联表 ON 关联匹配条件 WHERE 行数据过滤条件 GROUP BY 分组统计字段 HAVING 分组后聚合过滤条件 ORDER BY 排序字段 [ASC/DESC] LIMIT 偏移量,展示条数;分层实战案例sql-- 基础条件查询 SELECT id,stu_name,age FROM student WHERE age 18 AND class_id 2; -- 分组统计HAVING过滤分组结果 SELECT class_id,COUNT(*) stu_num FROM student GROUP BY class_id HAVING stu_num 5; -- 分页查询第1页每页10条数据 SELECT * FROM student LIMIT 0,10; -- 多表内连接查询学生对应班级名称 SELECT s.stu_name,c.class_name FROM student s INNER JOIN class c ON s.class_id c.id;⚠️ 高频踩坑WHERE 子句不能使用 COUNT、SUM 等聚合函数分组过滤必须写在 HAVING禁止日常开发使用SELECT *仅查询业务所需字段减少 IO 开销LIMIT 偏移量过大时分页性能极差推荐主键分页优化。3.3 UPDATE 修改数据语法规范sqlUPDATE 表名 SET 字段1值1,字段2值2 [WHERE 过滤条件];实战示例sqlUPDATE student SET age20 WHERE id1; 致命易错点省略 WHERE 条件会更新全表所有数据操作前必须先用 SELECT 校验过滤条件。3.4 DELETE 删除数据sqlDELETE FROM student WHERE id1;注意事项无 WHERE 条件会清空全表数据事务内可回滚恢复DELETE 不会重置自增主键大批量数据删除建议分批执行避免长时间锁表。分类全称核心作用核心关键字DDL数据定义语言创建 / 修改 / 删除数据库、数据表、索引定义库表结构CREATE、ALTER、DROP、TRUNCATEDML数据操作语言针对表内数据完成新增、查询、修改、删除业务开发核心INSERT、SELECT、UPDATE、DELETEDCL数据控制语言创建数据库登录用户、分配 / 回收访问权限负责数据库安全CREATE USER、GRANT、REVOKETCL事务控制语言保障多步数据操作一致性管理事务提交、回滚仅 InnoDB 支持BEGIN、COMMIT、ROLLBACK、SAVEPOINT4. DCL 数据控制语言用户与权限管理DCL 用于管理数据库登录账号与访问权限属于运维操作范畴用于管控不同开发人员的数据访问范围。4.1 创建、删除数据库用户sql-- 创建用户%代表任意IP均可登录 CREATE USER school_user% IDENTIFIED BY 123456; -- 删除指定用户 DROP USER IF EXISTS school_user%;4.2 授权与回收权限sql-- 授予school_db库所有表全部操作权限 GRANT ALL ON school_db.* TO school_user%; -- 仅授予查询权限限制修改、删除操作 GRANT SELECT ON school_db.* TO school_user%; -- 回收用户全部权限 REVOKE ALL ON school_db.* FROM school_user%; -- 刷新权限使修改立即生效 FLUSH PRIVILEGES;⚠️ 易错点权限修改后必须执行FLUSH PRIVILEGES否则权限不生效生产环境不建议使用%通配 IP缩小访问范围保障数据库安全。5. TCL 事务控制语言仅 InnoDB 引擎支持事务用于保证一组 DML 操作要么全部执行成功要么全部撤销满足 ACID 四大特性订单、转账、库存扣减等强一致性业务必须使用事务。完整事务语法sql-- 开启事务 BEGIN; -- 执行数据修改操作 UPDATE student SET age21 WHERE id2; -- 创建保存点支持局部回滚 SAVEPOINT save1; DELETE FROM student WHERE id3; -- 回滚到保存点仅撤销删除操作 ROLLBACK TO save1; -- 全部回滚撤销所有未提交操作 ROLLBACK; -- 提交事务数据永久写入数据库 COMMIT;使用规范MyISAM 引擎不支持事务事务内执行 CREATE、ALTER 等 DDL 语句会自动提交事务造成数据错乱会话关闭未执行 COMMIT事务会自动回滚数据丢失。6. 拓展语法子查询、视图、聚合函数6.1 子查询IN/EXISTSsql-- IN子查询查询计算机1班全部学生 SELECT * FROM student WHERE class_id IN (SELECT id FROM class WHERE class_name计算机1班);6.2 视图 VIEW封装常用多表查询sql-- 创建视图封装学生班级关联查询 CREATE VIEW v_stu_class AS SELECT s.*,c.class_name FROM student s LEFT JOIN class c ON s.class_idc.id; -- 调用视图查询 SELECT * FROM v_stu_class; -- 删除视图 DROP VIEW IF EXISTS v_stu_class;6.3 常用聚合函数COUNT()统计行数、SUM()求和、AVG()平均值、MAX()最大值、MIN()最小值sql-- 统计班级学生总数、平均年龄 SELECT class_id,COUNT(*) total,AVG(age) avg_age FROM student GROUP BY class_id;7. 全语句高频易错点汇总个人学习踩坑总结DDL 类踩坑建表忘记指定 InnoDB 引擎后续无法使用事务、外键库名、表名使用中文、关键字直接触发语法报错线上大表直接执行 ALTER长时间锁表阻塞业务读写。DML 类踩坑UPDATE/DELETE 遗漏 WHERE 条件全表数据被批量修改 / 删除查询使用SELECT *读取大量无用字段降低查询速度批量 INSERT 一次性写入上万条数据触发数据包溢出报错。TCL 事务踩坑事务中执行建表、改表 DDL 语句自动提交事务导致数据错乱操作完成忘记 COMMIT关闭会话后数据全部丢失。DCL 权限踩坑用户登录地址设置为%存在数据库入侵安全风险授权后未执行 FLUSH PRIVILEGES新权限不生效。8. 综合实战学生管理系统完整可运行 SQL 脚本整合建库、建表、插入、关联查询、事务完整代码本地 Navicat 可直接复制执行复现sql-- 1.创建数据库 CREATE DATABASE IF NOT EXISTS school_db DEFAULT CHARSET utf8mb4; USE school_db; -- 2.创建班级表 CREATE TABLE class( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 班级id, class_name VARCHAR(30) NOT NULL COMMENT 班级名称 ) ENGINEInnoDB COMMENT班级信息表; -- 3.创建学生表 CREATE TABLE student( id INT PRIMARY KEY AUTO_INCREMENT, stu_name VARCHAR(20) NOT NULL, age TINYINT DEFAULT 18, class_id INT ); -- 4.插入测试数据 INSERT INTO class(class_name) VALUES(计算机1班),(计算机2班); INSERT INTO student(stu_name,age,class_id) VALUES(小明,19,1),(小红,18,2); -- 5.多表关联查询 SELECT s.stu_name,c.class_name FROM student s JOIN class c ON s.class_idc.id; -- 6.事务修改学生年龄 BEGIN; UPDATE student SET age20 WHERE id1; COMMIT;9. 个人学习复盘、现存疑问与提升计划学习收获完整梳理课堂全部 SQL 语句清晰区分 DDL/DML/DCL/TCL 四大类语言核心用途彻底分清 TRUNCATE、DELETE、DROP 三者本质区别规避线上删库删表风险掌握事务 ACID 四大特性能够在订单、转账场景合理使用事务保证数据一致性明确自身薄弱板块复杂多表联查、索引优化、慢 SQL 调优掌握不足后续重点练习 Explain 执行计划。现存学习疑问百万级大数据表分页查询如何优化 LIMIT 大偏移量带来的性能问题联合索引遵循最左匹配原则遇到范围查询时索引失效有什么解决方案线上大表如何无锁执行 ALTER 修改表结构避免阻塞线上业务后续提升计划系统学习 Explain 执行计划分析慢查询根源实操联合索引、覆盖索引优化案例学习 pt-online-schema-change 在线改表工具解决大表 DDL 锁表问题。10. 参考官方文档MySQL 8.0 官方开发文档https://dev.mysql.com/doc/MySQL 中文开发手册CSDN 数据库实训专栏实战教程
