尧图网站建设 
上午后两节课讲MySQL从数据定义语言DDL讲到数据操作语言DML最后还扯到了数据控制语言DCL。下课的时候有同学盯着笔记问我这三类命令到底按什么标准分的为什么CREATE TABLE有的机器能回滚、有的机器不能回滚GRANT授权之后为什么还要刷新这些问题非常典型。今天干脆把这三块内容完整拆一遍把常见的坑和容易混淆的命令全说清楚不管是准备考试的计算机基础课学员还是刚接触数据库的开发者看完应该都能在自己机器上跑一遍。1. SQL语言分类的底层逻辑别只会背名字1.1 为什么数据库命令要分成这几类SQL不是一门单纯的计算语言它朝着管理数据这个目标拆成了几个职责完全不同的阵营。DDLData Definition Language管的是表长什么样DMLData Manipulation Language管的是表里的数据怎么变DCLData Control Language管的是谁能动这些数据。这三类命令在底层执行逻辑上有非常明显的区别区分它们的第一个标准是操作的是结构还是数据。比如CREATE TABLE、ALTER TABLE这种命令操作的是数据库的元数据——表结构、字段定义、约束条件它是盖房子。而INSERT、UPDATE、DELETE操作的是表里的实际记录它是房子里住的人怎么换。如果理解不了这一点就很容易在需要改字段类型的时候去删数据重建表或者在需要删数据的时候去DROP TABLE把整个表结构都干掉。第二个区分标准是事务特性。在InnoDB存储引擎下DML默认是支持事务的可以COMMIT也可以ROLLBACK而DDL绝大多数是隐式提交的执行完直接生效没有后悔药。平时课堂上很多同学问为什么我的DELETE可以回滚DROP却不能回滚根源就在这个分类逻辑上。DCL也有自己独特的行为授权和回收权限之后通常需要刷新权限缓存才能让会话感知到变化。1.2 一张表看懂四大语言阵营在这里我直接列一张对照表把常见的SQL命令按阵营分好便于记忆也便于查阅。除了标题里提到的DDL、DML、DCL之外我建议顺手加上TCL事务控制语言。虽然很多教材不单独提但实操中COMMIT、ROLLBACK几乎天天用和DML绑定极深。语言类型英文全称作用对象常见命令是否隐式提交DDLData Definition Language数据库、表、索引等结构CREATE, ALTER, DROP, TRUNCATE, RENAME是DMLData Manipulation Language表内的数据行INSERT, UPDATE, DELETE, SELECT默认手动提交取决于autocommitDCLData Control Language用户、权限GRANT, REVOKE, CREATE USER, DROP USER是TCLTransaction Control Language事务COMMIT, ROLLBACK, SAVEPOINT控制事务边界这里尤其要提的是SELECT命令。标准SQL里其实有专门的一类DQLData Query Language来放SELECT但在实际教学中SELECT因为和INSERT、UPDATE、DELETE经常一起出现在增删改查的场景里所以普遍划在DML里讲。我不会去纠结这个分类是否绝对规范重要的是你心里清楚查询不改变数据但查询结果是所有操作的基础。2. DDL数据定义语言实操拆解2.1 建库建表字符集与字段类型选择是关键DDL的第一件大事就是建库建表。很多新手在建表时要么不指定字符集要么随手用默认类型后面插入中文就乱码。这里我建议从一开始就养成习惯数据库和表都显式指定utf8mb4这样能存中文、特殊符号甚至emoji。注意老版本的utf8是够用的但utf8mb4才是完整的UTF-8实现尤其是在MySQL 5.7之后的版本里utf8mb4已经是标配。建库和建表的基本语法如下-- 建库指定默认字符集 CREATE DATABASE school DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; -- 切库 USE school; -- 建表指定引擎和自增起始值 CREATE TABLE student ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键, stu_no VARCHAR(20) NOT NULL UNIQUE COMMENT 学号, name VARCHAR(50) NOT NULL COMMENT 姓名, gender TINYINT NOT NULL DEFAULT 1 COMMENT 性别 1男 0女, birthday DATE DEFAULT NULL COMMENT 出生日期, score DECIMAL(5,2) DEFAULT 0.00 COMMENT 综合成绩, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT学生信息表;字段类型的选择上我个人给出了几个实用建议整数优先用INT超过21亿条数据再考虑BIGINT短文本用VARCHAR而不是CHARVARCHAR会根据内容动态分配空间CHAR适合长度固定且访问频繁的字段比如身份证号、学号金额和成绩用DECIMAL而不是FLOAT或DOUBLE否则很容易出现精度失真。日期类型里只存年月日用DATE要存时分秒就用DATETIME除非明确知道需要跟时区打交道否则不建议用TIMESTAMP。COMMENT注释看起来不起眼但后续维护表结构时省太多事。我有一次接手老项目一张表十几个字段没有一个注释全靠猜每个字段的意思那种痛苦经历过的人都懂。所以从建表第一天开始就养成写注释的习惯这是值得长期坚持的。2.2 ALTER TABLE修改表结构常用操作全集ALTER TABLE是DDL里用的最频繁、也最容易操作失误的命令。它主要干五种事加字段、删字段、改字段类型、改字段名、改表名。每种操作都有对应语法我分别拆一下。-- 追加字段 ALTER TABLE student ADD COLUMN phone VARCHAR(20) DEFAULT NULL COMMENT 手机号; -- 在指定字段之后加字段 ALTER TABLE student ADD COLUMN address VARCHAR(255) DEFAULT NULL COMMENT 住址 AFTER birthday; -- 修改字段类型注意字段名不变 ALTER TABLE student MODIFY COLUMN phone VARCHAR(15) DEFAULT NULL COMMENT 联系电话; -- 修改字段名和类型 ALTER TABLE student CHANGE COLUMN phone mobile VARCHAR(20) DEFAULT NULL COMMENT 手机号; -- 删除字段 ALTER TABLE student DROP COLUMN address; -- 修改表名 ALTER TABLE student RENAME TO student_info; -- 或者 RENAME TABLE student TO student_info;MODIFY和CHANGE的区别是个高频考点。MODIFY只改类型、约束、注释不改字段名CHANGE可以同时改字段名所以CHANGE后面要写两次字段名旧名和新名。新手很容易写成ALTER TABLE test CHANGE new_name INT结果报错就是因为漏了旧字段名这一步。在课上反复强调的一点是ALTER TABLE在大表上执行时可能会长时间锁表。如果一张线上表有几百万行数据执行ALTER TABLE去修改字段长度很多时候不能在线完成。MySQL 5.6之后引入了在线DDLALGORITHMINPLACE但也不是所有操作都能支持。日常开发直接跑问题不大生产环境改大表结构时还是要用pt-online-schema-change这类工具或者在业务低峰期操作。2.3 DROP、TRUNCATE、DELETE三个删除命令的分工这三个命令是DDL和DML交叉区域最大的混淆点。我建议用一句话总结DROP是把整张表扔掉TRUNCATE是把表里的数据清空并重置自增计数DELETE是逐行删除满足条件的数据。三者的区别在课堂上演示非常直观操作类型是否走事务是否能回滚是否保留表结构自增是否重置DELETE FROM studentDML是可以在事务内保留不重置TRUNCATE TABLE studentDDL否不可以保留重置DROP TABLE studentDDL否不可以删除无重点提醒一下TRUNCATE很多人以为它和DELETE FROM不加WHERE的效果一样其实差很远。TRUNCATE在绝大多数MySQL版本下是隐式提交的一旦执行就结束事务不能回滚。它还有一个特点就是重置AUTO_INCREMENT计数下一次插入的主键会重新从1开始。DELETE则不会重置计数哪怕你把所有数据全部删除再插入新数据时自增ID也还是接着原来的值走。我记得有一次课堂上一个同学在测试环境执行了TRUNCATE TABLE随后立刻后悔了跑过来问能不能恢复。我告诉他测试环境还好生产环境如果没做备份TRUNCATE之后基本只能靠之前的备份来恢复binlog虽然能记录但恢复成本极高。所以这三个删除命令在动手前一定要先想清楚我到底要删结构、清空数据还是删部分符合条件的行3. DML数据操作语言细节剖析3.1 INSERT插入数据单行、多行、批量从他表导入INSERT在DML里是最温柔的操作只会增加数据不会误伤。但依然有很多细节值得注意。最基础的是单行插入与多行插入实际开发中更推荐多行VALUES一次插入因为一次IO就能搞定性能比循环单行INSERT好得多。-- 单行 INSERT INTO student (stu_no, name, gender, birthday, score) VALUES (2026001, 张三, 1, 2008-03-15, 88.50); -- 多行批量 INSERT INTO student (stu_no, name, gender, birthday, score) VALUES (2026002, 李四, 0, 2008-07-26, 91.00), (2026003, 王五, 1, 2007-11-05, 76.50), (2026004, 赵六, 0, 2008-01-18, 83.20);这里有两个非常实用的场景。第一个是INSERT IGNORE它可以在主键或唯一键冲突时跳过插入不报错。第二个是ON DUPLICATE KEY UPDATE冲突时改成更新操作。比如在业务里维护一张统计表每次来一条新数据就想累计某个字段用这个语法特别顺手。-- 有学号冲突就忽略不报错 INSERT IGNORE INTO student (stu_no, name, score) VALUES (2026001, 张三, 90); -- 有学号冲突就更新分数 INSERT INTO student (stu_no, name, score) VALUES (2026001, 张三, 95) ON DUPLICATE KEY UPDATE score VALUES(score);顺便说一下从另外一张表批量导入数据最常用的写法是INSERT INTO ... SELECT不需要一条条拼INSERT。这在校验数据、做数据迁移的时候非常有用。INSERT INTO student_copy (stu_no, name, score) SELECT stu_no, name, score FROM student WHERE score 80;实操中最常见的INSERT报错是字段数不匹配、字符串忘了加引号、字段值超过了字段定义的范围。尤其是字符串忘加引号这个几乎是零基础学员的必踩坑。比如上面VALUES里的张三必须写成张三少一层引号MySQL就会当成字段名去解析直接抛Unknown column错误。3.2 UPDATE与DELETEWHERE条件决定生死UPDATE和DELETE是整个SQL语言中最危险的操作因为它们都有不带WHERE就全表遭殃的属性。我在课堂上做演示时一定会先把WHERE写上再用SELECT查一遍确认条件范围最后才执行更新或删除。这个习惯放进生产环境实在能避免很多事故。-- 更新前先查一遍确认影响范围 SELECT * FROM student WHERE stu_no 2026001; -- 执行更新 UPDATE student SET score 95.50 WHERE stu_no 2026001; -- 删除前同样先查 SELECT * FROM student WHERE stu_no 2026004; -- 执行删除 DELETE FROM student WHERE stu_no 2026004;UPDATE的底层逻辑是找到满足WHERE的记录然后修改对应字段。这里有一个容易忽略的现象如果你UPDATE的值和原值一模一样MySQL默认不会修改记录并且受影响行数会显示为0。这一点不影响正确性但很多人看到0 rows affected就以为自己没更新成功其实数据本身就是目标值不用虚惊。DELETE删除数据后表文件在磁盘上并不会立刻缩小只是把记录标记为删除空间由后续新插入的数据复用。有些人发现删了几万行数据表空间一点没变就是这个原因。真要让磁盘空间立刻释放得用OPTIMIZE TABLE或重建表日常维护里不是每删一次数据都需要做。事务内DELETE之后如果发现删错了只要还没COMMIT就可以ROLLBACK恢复。这也是DML和DDL最大的区别。下面这个例子非常直观建议在本地亲手操作一遍START TRANSACTION; DELETE FROM student WHERE score 60; -- 查一下发现误删了立刻回滚 ROLLBACK;3.3 SELECT查询不只是SELECT *那么简单SELECT虽然是查询但在数据操作里它的地位最高。可以说写SQL的百分之七十时间都花在SELECT身上。基础的正常查询顺序是FROM - WHERE - GROUP BY - HAVING - ORDER BY - LIMIT。很多学员刚接触时会把WHERE和HAVING搞混简单理解就是WHERE是给原始数据行过滤HAVING是给分组后的结果过滤。-- 条件查询 SELECT stu_no, name, score FROM student WHERE score 60 ORDER BY score DESC; -- 分页查询跳过0条取10条 SELECT * FROM student LIMIT 10 OFFSET 0; -- 等价写法 SELECT * FROM student LIMIT 0, 10; -- 模糊查询注意%的位置 SELECT * FROM student WHERE name LIKE 张%;分页性能的坑必须要讲。LIMIT 100000, 10这种深分页在百万级数据上性能极其糟糕因为MySQL要先扫出前面十万条再扔掉。数据量大时可以改成基于主键定位的方式例如记住上一页最后一条记录的主键然后WHERE id 上一页最大id LIMIT 10效果提升非常明显。ORDER BY排序时如果不想自己写字段名可以用字段序号。SELECT name, score FROM student ORDER BY 2 DESC表示按第二列score排序。这个技巧写复杂查询的时候挺方便但可读性较差我建议关键SQL还是老老实实写字段名。还有一个常被忽略的细节SELECT *会把所有列都查出来在开发初期图省事没问题但生产环境尤其是对接接口时尽量明确列出需要的字段。这不仅仅是性能问题更关乎代码可读性和后续维护数据量大之后多几列不用但传输的字段差距会非常明显。4. DCL数据控制语言权限分配与安全问题4.1 用户管理与登录权限DCL的核心是管人、管权限。第一步是用户管理建议每个应用都单独建账号别用root去跑业务。MySQL创建用户的语法如下-- 创建用户host指定允许登录的地址 CREATE USER app_userlocalhost IDENTIFIED BY your_password; -- 允许所有地址登录不太安全按需设置 CREATE USER app_user% IDENTIFIED BY your_password;这里的host字段是很多人容易忽略的点。app_user%表示从任何IP都能连app_userlocalhost表示只能本机连app_user192.168.1.%表示只能从指定网段连。如果是生产数据库账号尽量限制到具体IP可以大幅降低被爆破的风险。-- 修改密码5.7之后推荐写法 ALTER USER app_userlocalhost IDENTIFIED BY new_password; -- 删除用户 DROP USER app_userlocalhost;4.2 GRANT授权权限粒度决定安全边界创建完用户以后还需要授权不然用户什么都做不了。GRANT的授权粒度可以从全局到库再到表甚至细化到字段级别。申请权限的时候就按最小权限原则来只用SELECT权限就别顺手给UPDATE否则一旦应用被注入SQL损失会被放大很多倍。-- 只授一个库的所有权限 GRANT SELECT, INSERT, UPDATE, DELETE ON school.* TO app_userlocalhost; -- 只授一张表 GRANT SELECT ON school.student TO app_userlocalhost; -- 授所有库所有权限相当于超级管理员慎重 GRANT ALL PRIVILEGES ON *.* TO app_userlocalhost; -- 真正生效 FLUSH PRIVILEGES;权限层级从小到大是表权限、库权限、全局权限。当多个层级权限同时存在时MySQL会把它们合并不是覆盖关系。什么意思呢比如A用户有全局SELECT权限又在某张表上被REVOKE了SELECT那他在那张表上依然不能查但其他表能查。实际场景里很少需要这么精细的控制但理解合并逻辑对排查权限问题很有帮助。有一次我在生产环境排查一个诡异的问题新创建的用户怎么都连接不上数据库最后发现CREATE USER确实成功但忘记GRANT任何权限。MySQL默认新建用户一个权限都没有连登录校验通过之后执行任何查询都会报SELECT command denied。这不是密码错而是授权漏了。4.3 REVOKE回收权限与查看授权权限回收用REVOKE语法和GRANT是反着的-- 撤销删除权限 REVOKE DELETE ON school.* FROM app_userlocalhost; -- 撤销所有权限 REVOKE ALL PRIVILEGES ON school.* FROM app_userlocalhost;排查用户到底有什么权限使用SHOW GRANTSSHOW GRANTS FOR app_userlocalhost;实操经验里有个坑修改了权限表之后某些已存在的连接不会立刻生效必须得等会话重建或者在执行完GRANT/REVOKE后执行FLUSH PRIVILEGES。尤其是使用ALTER USER修改密码已经登录的会话不会马上断开新密码要等下次连接才生效。上课时有同学问为什么刚改了密码同事那边还能继续连就是因为他那台机器上保留着旧的连接池重新释放重连才会用新密码。DCL这块在计算机基础课程里通常不会讲得太深但我觉得每个开发者都应该掌握最基本的用户和授权操作。因为实际项目中几乎不会让你用root跑业务应用迟早得自己创建账号、分配权限。把GRANT和REVOKE练熟了等于给自己的数据库装了一道安全气囊。5. 事务控制语言TCL这里藏着控制的深层含义5.1 为什么DML离不开事务说到数据控制语言很多人第一反应是DCL的权限控制但课堂后段我会额外补充事务控制这一层。因为DML操作和事务控制绑定最紧日常写增删改如果不懂COMMIT、ROLLBACK极易造成数据脏乱。事务要解决的核心问题其实可以用一个场景解释银行转账。A账户扣1000元B账户加1000元两条UPDATE必须打包成一个整体。如果第一条成功、第二条失败数据库应该回到转账前的状态谁的钱都不少。这就是事务的原子性。引申出的四种特性合起来就是ACID原子性Atomicity、一致性Consistency、隔离性Isolation、持久性Durability。InnoDB是默认支持事务的存储引擎MyISAM不支持。如果你发现某个表执行START TRANSACTION和ROLLBACK完全无效先检查一下存储引擎大概率是MyISAM。这也是为什么我在建表时总强调ENGINEInnoDB。5.2 COMMIT、ROLLBACK、SAVEPOINT的实战用法实操中事务的基本写法如下-- 开启事务 START TRANSACTION; UPDATE student SET score score - 5 WHERE stu_no 2026001; UPDATE student SET score score 5 WHERE stu_no 2026002; -- 确认无误后提交 COMMIT; -- 如果发现异常可以回滚 ROLLBACK;SAVEPOINT是事务中间的存档点适合长事务中分段回滚。比如一个大事务里做了很多操作只想回滚到中间某一步而不是全部回滚就能用SAVEPOINT。START TRANSACTION; INSERT INTO student (stu_no, name) VALUES (2026100, 测试); SAVEPOINT sp1; INSERT INTO student (stu_no, name) VALUES (2026101, 临时); -- 出了点问题回滚到sp1只撤销后面这次插入 ROLLBACK TO sp1; COMMIT;关于MySQL的自动提交课堂上被问得也很多。默认情况autocommit1你执行一条INSERT或UPDATE后自动生效不需要手动COMMIT。但当你手动START TRANSACTION之后autocommit就暂时失效了必须显式COMMIT或ROLLBACK结束事务。理解这一点对排查为什么手动开了事务后别的会话看不到数据变化很有帮助。5.3 事务隔离级别与并发问题既然控制语言谈到了事务就绕不开隔离级别。MySQL默认的隔离级别是REPEATABLE READ可重复读它通过MVCC机制解决了大部分并发问题。四个隔离级别从松到严分别是READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE。对应的三个经典并发问题分别是脏读、不可重复读、幻读。隔离级别脏读不可重复读幻读READ UNCOMMITTED可能可能可能READ COMMITTED不可能可能可能REPEATABLE READ不可能不可能InnoDB下一般不发生SERIALIZABLE不可能不可能不可能课堂演示时我经常用一个例子解释不可重复读和幻读的区别事务A查成绩看到张三80分事务B这时候把张三改成90分提交事务A再查一次如果读到90分就是不可重复读同一行数据前后不一致。幻读则是A查询成绩在60到100分的记录总数B插入了一条新记录并提交A再查一次总数变了。隔离级别就是解决这类并发矛盾的手段。串行化级别最安全但性能最差实际生产环境很少用。默认的REPEATABLE READ在绝大多数场景下都够用这也是为什么我不建议随意修改事务隔离级别的原因。很多初学者听说SERIALIZABLE最安全就跑去改结果数据库吞吐量直线下降得不偿失。合理设置索引、把大事务拆小、控制好锁的粒度远比调隔离级别更高效。6. 课堂常见翻车现场与排查速查6.1 大小写敏感导致的表不存在MySQL在同一套系统下大小写规则还算稳定但在Windows和Linux上的表现完全不同。Windows系统默认对表名不敏感Linux则敏感。在Linux上创建了一张student表写SELECT FROM Student就报Table doesnt exist。这种问题在开发环境Windows上发现不了一上线到Linux测试环境就暴露。规避方法很简单库名、表名、字段名全部统一小写下划线风格不要一会Student一会student。6.2 中文乱码和字符集不一致乱码问题的排查顺序是连接层、库表层、客户端。连接层表现为客户端设置了SET NAMES utf8mb4但库表字符集是utf8或latin1。库表层表现为建表时忘写CHARSET沿用默认latin1。客户端表现为终端工具本身编码不对命令行窗口没切成UTF-8。排查的时候用一个命令SHOW FULL COLUMNS FROM student可以看到每列的字符集再用SHOW CREATE TABLE student看整个表的定义基本能定位。6.3 GRANT之后权限不生效很多人执行完GRANT后直接去连数据库发现新权限没生效。首先要确认当前会话是不是新建的权限受连接池缓存影响其次执行FLUSH PRIVILEGES刷新权限缓存最后检查授权对象是否匹配比如你授权给app_userlocalhost但应用通过IP连接实际匹配的可能是app_user%或app_user具体IP的规则权限自然对不上。这种用户看着对、权限却完全错位的问题用SHOW GRANTS FOR对应host一查就明白了。6.4 其他几个高频错误缺少WHERE条件的UPDATE和DELETE是生产环境最惨烈的事故类型没有之一。课堂上为了加深印象我专门演示过不带WHERE删除整张表数据的后果好在测试环境可以ROLLBACK。但在生产环境一旦走了隐式提交就没救了。所以我在课上一再强调写完UPDATE或DELETE先看一眼有没有WHERE再瞄一眼WHERE里的条件是不是可能匹配到多余数据。字符串不写引号、日期不按格式写、字段类型不一致时MySQL会尝试隐式转换这也会导致意想不到的结果。比如WHERE phone 123456根据索引规则有时候会放弃索引而全表扫描。外键和约束相关的报错往下看错误码信息通常写得很清楚07xx开头的多数是操作违反约束。不要太依赖MySQL的报错提示去猜而是把表结构约束先梳理一遍大多数INSERT和UPDATE异常都能迎刃而解。7. 课后复盘我建议你亲手做一遍的操作课堂上的知识点再多不动手很容易忘。我每次带课都会让学员在本机完成一个训练几步下来基本能把DDL、DML、DCL串起来第一步创建数据库test并创建两张表一张用户表user一张订单表orderuser和order有主外键关联。第二步用INSERT插入至少3条用户数据和5条订单数据。第三步用UPDATE把某个用户的名字改掉再用DELETE删除一条订单记录。第四步用SELECT查询每个用户的订单总量按订单数量降序排序。第五步创建只拥有SELECT、INSERT、UPDATE权限的应用账号测试用这个账号执行DELETE时能不能成功。第六步开一个事务INSERT一条数据后ROLLBACK确认表里没有这条数据。我自己这几年带项目的体会是数据库基础学得扎实不扎实不是看会不会写CREATE TABLE而是看遇到删错了怎么办权限不够怎么办数据乱码怎么办的时候能不能在最短时间内定位原因并恢复。这三类语言的分工看起来很死板但理解透了之后你会发现日常开发中几乎所有数据库交互都有章可循不再是一遇到问题就百度。最后分享一个小技巧无论课上还是实际项目里每次执行有风险的DML或DDL前先把当前SQL语句放到事务里跑一遍测试或者先SELECT COUNT(*)看一眼影响范围这是个成本极低但收益极高的习惯。这个习惯养成之后可以替你挡掉大多数后悔都来不及的误操作。 一文搞懂MySQL三大语言：DDL、DML与DCL的分类逻辑与实战避坑
上午后两节课讲MySQL从数据定义语言DDL讲到数据操作语言DML最后还扯到了数据控制语言DCL。下课的时候有同学盯着笔记问我这三类命令到底按什么标准分的为什么CREATE TABLE有的机器能回滚、有的机器不能回滚GRANT授权之后为什么还要刷新这些问题非常典型。今天干脆把这三块内容完整拆一遍把常见的坑和容易混淆的命令全说清楚不管是准备考试的计算机基础课学员还是刚接触数据库的开发者看完应该都能在自己机器上跑一遍。1. SQL语言分类的底层逻辑别只会背名字1.1 为什么数据库命令要分成这几类SQL不是一门单纯的计算语言它朝着管理数据这个目标拆成了几个职责完全不同的阵营。DDLData Definition Language管的是表长什么样DMLData Manipulation Language管的是表里的数据怎么变DCLData Control Language管的是谁能动这些数据。这三类命令在底层执行逻辑上有非常明显的区别区分它们的第一个标准是操作的是结构还是数据。比如CREATE TABLE、ALTER TABLE这种命令操作的是数据库的元数据——表结构、字段定义、约束条件它是盖房子。而INSERT、UPDATE、DELETE操作的是表里的实际记录它是房子里住的人怎么换。如果理解不了这一点就很容易在需要改字段类型的时候去删数据重建表或者在需要删数据的时候去DROP TABLE把整个表结构都干掉。第二个区分标准是事务特性。在InnoDB存储引擎下DML默认是支持事务的可以COMMIT也可以ROLLBACK而DDL绝大多数是隐式提交的执行完直接生效没有后悔药。平时课堂上很多同学问为什么我的DELETE可以回滚DROP却不能回滚根源就在这个分类逻辑上。DCL也有自己独特的行为授权和回收权限之后通常需要刷新权限缓存才能让会话感知到变化。1.2 一张表看懂四大语言阵营在这里我直接列一张对照表把常见的SQL命令按阵营分好便于记忆也便于查阅。除了标题里提到的DDL、DML、DCL之外我建议顺手加上TCL事务控制语言。虽然很多教材不单独提但实操中COMMIT、ROLLBACK几乎天天用和DML绑定极深。语言类型英文全称作用对象常见命令是否隐式提交DDLData Definition Language数据库、表、索引等结构CREATE, ALTER, DROP, TRUNCATE, RENAME是DMLData Manipulation Language表内的数据行INSERT, UPDATE, DELETE, SELECT默认手动提交取决于autocommitDCLData Control Language用户、权限GRANT, REVOKE, CREATE USER, DROP USER是TCLTransaction Control Language事务COMMIT, ROLLBACK, SAVEPOINT控制事务边界这里尤其要提的是SELECT命令。标准SQL里其实有专门的一类DQLData Query Language来放SELECT但在实际教学中SELECT因为和INSERT、UPDATE、DELETE经常一起出现在增删改查的场景里所以普遍划在DML里讲。我不会去纠结这个分类是否绝对规范重要的是你心里清楚查询不改变数据但查询结果是所有操作的基础。2. DDL数据定义语言实操拆解2.1 建库建表字符集与字段类型选择是关键DDL的第一件大事就是建库建表。很多新手在建表时要么不指定字符集要么随手用默认类型后面插入中文就乱码。这里我建议从一开始就养成习惯数据库和表都显式指定utf8mb4这样能存中文、特殊符号甚至emoji。注意老版本的utf8是够用的但utf8mb4才是完整的UTF-8实现尤其是在MySQL 5.7之后的版本里utf8mb4已经是标配。建库和建表的基本语法如下-- 建库指定默认字符集 CREATE DATABASE school DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; -- 切库 USE school; -- 建表指定引擎和自增起始值 CREATE TABLE student ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键, stu_no VARCHAR(20) NOT NULL UNIQUE COMMENT 学号, name VARCHAR(50) NOT NULL COMMENT 姓名, gender TINYINT NOT NULL DEFAULT 1 COMMENT 性别 1男 0女, birthday DATE DEFAULT NULL COMMENT 出生日期, score DECIMAL(5,2) DEFAULT 0.00 COMMENT 综合成绩, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT学生信息表;字段类型的选择上我个人给出了几个实用建议整数优先用INT超过21亿条数据再考虑BIGINT短文本用VARCHAR而不是CHARVARCHAR会根据内容动态分配空间CHAR适合长度固定且访问频繁的字段比如身份证号、学号金额和成绩用DECIMAL而不是FLOAT或DOUBLE否则很容易出现精度失真。日期类型里只存年月日用DATE要存时分秒就用DATETIME除非明确知道需要跟时区打交道否则不建议用TIMESTAMP。COMMENT注释看起来不起眼但后续维护表结构时省太多事。我有一次接手老项目一张表十几个字段没有一个注释全靠猜每个字段的意思那种痛苦经历过的人都懂。所以从建表第一天开始就养成写注释的习惯这是值得长期坚持的。2.2 ALTER TABLE修改表结构常用操作全集ALTER TABLE是DDL里用的最频繁、也最容易操作失误的命令。它主要干五种事加字段、删字段、改字段类型、改字段名、改表名。每种操作都有对应语法我分别拆一下。-- 追加字段 ALTER TABLE student ADD COLUMN phone VARCHAR(20) DEFAULT NULL COMMENT 手机号; -- 在指定字段之后加字段 ALTER TABLE student ADD COLUMN address VARCHAR(255) DEFAULT NULL COMMENT 住址 AFTER birthday; -- 修改字段类型注意字段名不变 ALTER TABLE student MODIFY COLUMN phone VARCHAR(15) DEFAULT NULL COMMENT 联系电话; -- 修改字段名和类型 ALTER TABLE student CHANGE COLUMN phone mobile VARCHAR(20) DEFAULT NULL COMMENT 手机号; -- 删除字段 ALTER TABLE student DROP COLUMN address; -- 修改表名 ALTER TABLE student RENAME TO student_info; -- 或者 RENAME TABLE student TO student_info;MODIFY和CHANGE的区别是个高频考点。MODIFY只改类型、约束、注释不改字段名CHANGE可以同时改字段名所以CHANGE后面要写两次字段名旧名和新名。新手很容易写成ALTER TABLE test CHANGE new_name INT结果报错就是因为漏了旧字段名这一步。在课上反复强调的一点是ALTER TABLE在大表上执行时可能会长时间锁表。如果一张线上表有几百万行数据执行ALTER TABLE去修改字段长度很多时候不能在线完成。MySQL 5.6之后引入了在线DDLALGORITHMINPLACE但也不是所有操作都能支持。日常开发直接跑问题不大生产环境改大表结构时还是要用pt-online-schema-change这类工具或者在业务低峰期操作。2.3 DROP、TRUNCATE、DELETE三个删除命令的分工这三个命令是DDL和DML交叉区域最大的混淆点。我建议用一句话总结DROP是把整张表扔掉TRUNCATE是把表里的数据清空并重置自增计数DELETE是逐行删除满足条件的数据。三者的区别在课堂上演示非常直观操作类型是否走事务是否能回滚是否保留表结构自增是否重置DELETE FROM studentDML是可以在事务内保留不重置TRUNCATE TABLE studentDDL否不可以保留重置DROP TABLE studentDDL否不可以删除无重点提醒一下TRUNCATE很多人以为它和DELETE FROM不加WHERE的效果一样其实差很远。TRUNCATE在绝大多数MySQL版本下是隐式提交的一旦执行就结束事务不能回滚。它还有一个特点就是重置AUTO_INCREMENT计数下一次插入的主键会重新从1开始。DELETE则不会重置计数哪怕你把所有数据全部删除再插入新数据时自增ID也还是接着原来的值走。我记得有一次课堂上一个同学在测试环境执行了TRUNCATE TABLE随后立刻后悔了跑过来问能不能恢复。我告诉他测试环境还好生产环境如果没做备份TRUNCATE之后基本只能靠之前的备份来恢复binlog虽然能记录但恢复成本极高。所以这三个删除命令在动手前一定要先想清楚我到底要删结构、清空数据还是删部分符合条件的行3. DML数据操作语言细节剖析3.1 INSERT插入数据单行、多行、批量从他表导入INSERT在DML里是最温柔的操作只会增加数据不会误伤。但依然有很多细节值得注意。最基础的是单行插入与多行插入实际开发中更推荐多行VALUES一次插入因为一次IO就能搞定性能比循环单行INSERT好得多。-- 单行 INSERT INTO student (stu_no, name, gender, birthday, score) VALUES (2026001, 张三, 1, 2008-03-15, 88.50); -- 多行批量 INSERT INTO student (stu_no, name, gender, birthday, score) VALUES (2026002, 李四, 0, 2008-07-26, 91.00), (2026003, 王五, 1, 2007-11-05, 76.50), (2026004, 赵六, 0, 2008-01-18, 83.20);这里有两个非常实用的场景。第一个是INSERT IGNORE它可以在主键或唯一键冲突时跳过插入不报错。第二个是ON DUPLICATE KEY UPDATE冲突时改成更新操作。比如在业务里维护一张统计表每次来一条新数据就想累计某个字段用这个语法特别顺手。-- 有学号冲突就忽略不报错 INSERT IGNORE INTO student (stu_no, name, score) VALUES (2026001, 张三, 90); -- 有学号冲突就更新分数 INSERT INTO student (stu_no, name, score) VALUES (2026001, 张三, 95) ON DUPLICATE KEY UPDATE score VALUES(score);顺便说一下从另外一张表批量导入数据最常用的写法是INSERT INTO ... SELECT不需要一条条拼INSERT。这在校验数据、做数据迁移的时候非常有用。INSERT INTO student_copy (stu_no, name, score) SELECT stu_no, name, score FROM student WHERE score 80;实操中最常见的INSERT报错是字段数不匹配、字符串忘了加引号、字段值超过了字段定义的范围。尤其是字符串忘加引号这个几乎是零基础学员的必踩坑。比如上面VALUES里的张三必须写成张三少一层引号MySQL就会当成字段名去解析直接抛Unknown column错误。3.2 UPDATE与DELETEWHERE条件决定生死UPDATE和DELETE是整个SQL语言中最危险的操作因为它们都有不带WHERE就全表遭殃的属性。我在课堂上做演示时一定会先把WHERE写上再用SELECT查一遍确认条件范围最后才执行更新或删除。这个习惯放进生产环境实在能避免很多事故。-- 更新前先查一遍确认影响范围 SELECT * FROM student WHERE stu_no 2026001; -- 执行更新 UPDATE student SET score 95.50 WHERE stu_no 2026001; -- 删除前同样先查 SELECT * FROM student WHERE stu_no 2026004; -- 执行删除 DELETE FROM student WHERE stu_no 2026004;UPDATE的底层逻辑是找到满足WHERE的记录然后修改对应字段。这里有一个容易忽略的现象如果你UPDATE的值和原值一模一样MySQL默认不会修改记录并且受影响行数会显示为0。这一点不影响正确性但很多人看到0 rows affected就以为自己没更新成功其实数据本身就是目标值不用虚惊。DELETE删除数据后表文件在磁盘上并不会立刻缩小只是把记录标记为删除空间由后续新插入的数据复用。有些人发现删了几万行数据表空间一点没变就是这个原因。真要让磁盘空间立刻释放得用OPTIMIZE TABLE或重建表日常维护里不是每删一次数据都需要做。事务内DELETE之后如果发现删错了只要还没COMMIT就可以ROLLBACK恢复。这也是DML和DDL最大的区别。下面这个例子非常直观建议在本地亲手操作一遍START TRANSACTION; DELETE FROM student WHERE score 60; -- 查一下发现误删了立刻回滚 ROLLBACK;3.3 SELECT查询不只是SELECT *那么简单SELECT虽然是查询但在数据操作里它的地位最高。可以说写SQL的百分之七十时间都花在SELECT身上。基础的正常查询顺序是FROM - WHERE - GROUP BY - HAVING - ORDER BY - LIMIT。很多学员刚接触时会把WHERE和HAVING搞混简单理解就是WHERE是给原始数据行过滤HAVING是给分组后的结果过滤。-- 条件查询 SELECT stu_no, name, score FROM student WHERE score 60 ORDER BY score DESC; -- 分页查询跳过0条取10条 SELECT * FROM student LIMIT 10 OFFSET 0; -- 等价写法 SELECT * FROM student LIMIT 0, 10; -- 模糊查询注意%的位置 SELECT * FROM student WHERE name LIKE 张%;分页性能的坑必须要讲。LIMIT 100000, 10这种深分页在百万级数据上性能极其糟糕因为MySQL要先扫出前面十万条再扔掉。数据量大时可以改成基于主键定位的方式例如记住上一页最后一条记录的主键然后WHERE id 上一页最大id LIMIT 10效果提升非常明显。ORDER BY排序时如果不想自己写字段名可以用字段序号。SELECT name, score FROM student ORDER BY 2 DESC表示按第二列score排序。这个技巧写复杂查询的时候挺方便但可读性较差我建议关键SQL还是老老实实写字段名。还有一个常被忽略的细节SELECT *会把所有列都查出来在开发初期图省事没问题但生产环境尤其是对接接口时尽量明确列出需要的字段。这不仅仅是性能问题更关乎代码可读性和后续维护数据量大之后多几列不用但传输的字段差距会非常明显。4. DCL数据控制语言权限分配与安全问题4.1 用户管理与登录权限DCL的核心是管人、管权限。第一步是用户管理建议每个应用都单独建账号别用root去跑业务。MySQL创建用户的语法如下-- 创建用户host指定允许登录的地址 CREATE USER app_userlocalhost IDENTIFIED BY your_password; -- 允许所有地址登录不太安全按需设置 CREATE USER app_user% IDENTIFIED BY your_password;这里的host字段是很多人容易忽略的点。app_user%表示从任何IP都能连app_userlocalhost表示只能本机连app_user192.168.1.%表示只能从指定网段连。如果是生产数据库账号尽量限制到具体IP可以大幅降低被爆破的风险。-- 修改密码5.7之后推荐写法 ALTER USER app_userlocalhost IDENTIFIED BY new_password; -- 删除用户 DROP USER app_userlocalhost;4.2 GRANT授权权限粒度决定安全边界创建完用户以后还需要授权不然用户什么都做不了。GRANT的授权粒度可以从全局到库再到表甚至细化到字段级别。申请权限的时候就按最小权限原则来只用SELECT权限就别顺手给UPDATE否则一旦应用被注入SQL损失会被放大很多倍。-- 只授一个库的所有权限 GRANT SELECT, INSERT, UPDATE, DELETE ON school.* TO app_userlocalhost; -- 只授一张表 GRANT SELECT ON school.student TO app_userlocalhost; -- 授所有库所有权限相当于超级管理员慎重 GRANT ALL PRIVILEGES ON *.* TO app_userlocalhost; -- 真正生效 FLUSH PRIVILEGES;权限层级从小到大是表权限、库权限、全局权限。当多个层级权限同时存在时MySQL会把它们合并不是覆盖关系。什么意思呢比如A用户有全局SELECT权限又在某张表上被REVOKE了SELECT那他在那张表上依然不能查但其他表能查。实际场景里很少需要这么精细的控制但理解合并逻辑对排查权限问题很有帮助。有一次我在生产环境排查一个诡异的问题新创建的用户怎么都连接不上数据库最后发现CREATE USER确实成功但忘记GRANT任何权限。MySQL默认新建用户一个权限都没有连登录校验通过之后执行任何查询都会报SELECT command denied。这不是密码错而是授权漏了。4.3 REVOKE回收权限与查看授权权限回收用REVOKE语法和GRANT是反着的-- 撤销删除权限 REVOKE DELETE ON school.* FROM app_userlocalhost; -- 撤销所有权限 REVOKE ALL PRIVILEGES ON school.* FROM app_userlocalhost;排查用户到底有什么权限使用SHOW GRANTSSHOW GRANTS FOR app_userlocalhost;实操经验里有个坑修改了权限表之后某些已存在的连接不会立刻生效必须得等会话重建或者在执行完GRANT/REVOKE后执行FLUSH PRIVILEGES。尤其是使用ALTER USER修改密码已经登录的会话不会马上断开新密码要等下次连接才生效。上课时有同学问为什么刚改了密码同事那边还能继续连就是因为他那台机器上保留着旧的连接池重新释放重连才会用新密码。DCL这块在计算机基础课程里通常不会讲得太深但我觉得每个开发者都应该掌握最基本的用户和授权操作。因为实际项目中几乎不会让你用root跑业务应用迟早得自己创建账号、分配权限。把GRANT和REVOKE练熟了等于给自己的数据库装了一道安全气囊。5. 事务控制语言TCL这里藏着控制的深层含义5.1 为什么DML离不开事务说到数据控制语言很多人第一反应是DCL的权限控制但课堂后段我会额外补充事务控制这一层。因为DML操作和事务控制绑定最紧日常写增删改如果不懂COMMIT、ROLLBACK极易造成数据脏乱。事务要解决的核心问题其实可以用一个场景解释银行转账。A账户扣1000元B账户加1000元两条UPDATE必须打包成一个整体。如果第一条成功、第二条失败数据库应该回到转账前的状态谁的钱都不少。这就是事务的原子性。引申出的四种特性合起来就是ACID原子性Atomicity、一致性Consistency、隔离性Isolation、持久性Durability。InnoDB是默认支持事务的存储引擎MyISAM不支持。如果你发现某个表执行START TRANSACTION和ROLLBACK完全无效先检查一下存储引擎大概率是MyISAM。这也是为什么我在建表时总强调ENGINEInnoDB。5.2 COMMIT、ROLLBACK、SAVEPOINT的实战用法实操中事务的基本写法如下-- 开启事务 START TRANSACTION; UPDATE student SET score score - 5 WHERE stu_no 2026001; UPDATE student SET score score 5 WHERE stu_no 2026002; -- 确认无误后提交 COMMIT; -- 如果发现异常可以回滚 ROLLBACK;SAVEPOINT是事务中间的存档点适合长事务中分段回滚。比如一个大事务里做了很多操作只想回滚到中间某一步而不是全部回滚就能用SAVEPOINT。START TRANSACTION; INSERT INTO student (stu_no, name) VALUES (2026100, 测试); SAVEPOINT sp1; INSERT INTO student (stu_no, name) VALUES (2026101, 临时); -- 出了点问题回滚到sp1只撤销后面这次插入 ROLLBACK TO sp1; COMMIT;关于MySQL的自动提交课堂上被问得也很多。默认情况autocommit1你执行一条INSERT或UPDATE后自动生效不需要手动COMMIT。但当你手动START TRANSACTION之后autocommit就暂时失效了必须显式COMMIT或ROLLBACK结束事务。理解这一点对排查为什么手动开了事务后别的会话看不到数据变化很有帮助。5.3 事务隔离级别与并发问题既然控制语言谈到了事务就绕不开隔离级别。MySQL默认的隔离级别是REPEATABLE READ可重复读它通过MVCC机制解决了大部分并发问题。四个隔离级别从松到严分别是READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE。对应的三个经典并发问题分别是脏读、不可重复读、幻读。隔离级别脏读不可重复读幻读READ UNCOMMITTED可能可能可能READ COMMITTED不可能可能可能REPEATABLE READ不可能不可能InnoDB下一般不发生SERIALIZABLE不可能不可能不可能课堂演示时我经常用一个例子解释不可重复读和幻读的区别事务A查成绩看到张三80分事务B这时候把张三改成90分提交事务A再查一次如果读到90分就是不可重复读同一行数据前后不一致。幻读则是A查询成绩在60到100分的记录总数B插入了一条新记录并提交A再查一次总数变了。隔离级别就是解决这类并发矛盾的手段。串行化级别最安全但性能最差实际生产环境很少用。默认的REPEATABLE READ在绝大多数场景下都够用这也是为什么我不建议随意修改事务隔离级别的原因。很多初学者听说SERIALIZABLE最安全就跑去改结果数据库吞吐量直线下降得不偿失。合理设置索引、把大事务拆小、控制好锁的粒度远比调隔离级别更高效。6. 课堂常见翻车现场与排查速查6.1 大小写敏感导致的表不存在MySQL在同一套系统下大小写规则还算稳定但在Windows和Linux上的表现完全不同。Windows系统默认对表名不敏感Linux则敏感。在Linux上创建了一张student表写SELECT FROM Student就报Table doesnt exist。这种问题在开发环境Windows上发现不了一上线到Linux测试环境就暴露。规避方法很简单库名、表名、字段名全部统一小写下划线风格不要一会Student一会student。6.2 中文乱码和字符集不一致乱码问题的排查顺序是连接层、库表层、客户端。连接层表现为客户端设置了SET NAMES utf8mb4但库表字符集是utf8或latin1。库表层表现为建表时忘写CHARSET沿用默认latin1。客户端表现为终端工具本身编码不对命令行窗口没切成UTF-8。排查的时候用一个命令SHOW FULL COLUMNS FROM student可以看到每列的字符集再用SHOW CREATE TABLE student看整个表的定义基本能定位。6.3 GRANT之后权限不生效很多人执行完GRANT后直接去连数据库发现新权限没生效。首先要确认当前会话是不是新建的权限受连接池缓存影响其次执行FLUSH PRIVILEGES刷新权限缓存最后检查授权对象是否匹配比如你授权给app_userlocalhost但应用通过IP连接实际匹配的可能是app_user%或app_user具体IP的规则权限自然对不上。这种用户看着对、权限却完全错位的问题用SHOW GRANTS FOR对应host一查就明白了。6.4 其他几个高频错误缺少WHERE条件的UPDATE和DELETE是生产环境最惨烈的事故类型没有之一。课堂上为了加深印象我专门演示过不带WHERE删除整张表数据的后果好在测试环境可以ROLLBACK。但在生产环境一旦走了隐式提交就没救了。所以我在课上一再强调写完UPDATE或DELETE先看一眼有没有WHERE再瞄一眼WHERE里的条件是不是可能匹配到多余数据。字符串不写引号、日期不按格式写、字段类型不一致时MySQL会尝试隐式转换这也会导致意想不到的结果。比如WHERE phone 123456根据索引规则有时候会放弃索引而全表扫描。外键和约束相关的报错往下看错误码信息通常写得很清楚07xx开头的多数是操作违反约束。不要太依赖MySQL的报错提示去猜而是把表结构约束先梳理一遍大多数INSERT和UPDATE异常都能迎刃而解。7. 课后复盘我建议你亲手做一遍的操作课堂上的知识点再多不动手很容易忘。我每次带课都会让学员在本机完成一个训练几步下来基本能把DDL、DML、DCL串起来第一步创建数据库test并创建两张表一张用户表user一张订单表orderuser和order有主外键关联。第二步用INSERT插入至少3条用户数据和5条订单数据。第三步用UPDATE把某个用户的名字改掉再用DELETE删除一条订单记录。第四步用SELECT查询每个用户的订单总量按订单数量降序排序。第五步创建只拥有SELECT、INSERT、UPDATE权限的应用账号测试用这个账号执行DELETE时能不能成功。第六步开一个事务INSERT一条数据后ROLLBACK确认表里没有这条数据。我自己这几年带项目的体会是数据库基础学得扎实不扎实不是看会不会写CREATE TABLE而是看遇到删错了怎么办权限不够怎么办数据乱码怎么办的时候能不能在最短时间内定位原因并恢复。这三类语言的分工看起来很死板但理解透了之后你会发现日常开发中几乎所有数据库交互都有章可循不再是一遇到问题就百度。最后分享一个小技巧无论课上还是实际项目里每次执行有风险的DML或DDL前先把当前SQL语句放到事务里跑一遍测试或者先SELECT COUNT(*)看一眼影响范围这是个成本极低但收益极高的习惯。这个习惯养成之后可以替你挡掉大多数后悔都来不及的误操作。