文章目录MySQL表操作入门玩转你的数据库Excel小白也能看懂 前言一、创建表 CREATE TABLE绘制你的表头1.1 基础语法1.2 核心参数大白话拆解1.3 新手必知常见数据类型选型1.4 字段约束建表核心新手必学标准建表案例带约束接近真实开发1.5 存储引擎对应的磁盘文件面试高频考点MyISAM引擎老版本遗留新项目不推荐InnoDB引擎生产环境默认首选1.6 建表命名规范好习惯从入门开始二、查看表结构 DESC看看表头长啥样2.1 基础语法2.2 输出字段说明2.3 ✨ 拓展查看技巧三、修改表 ALTER TABLE —— 线上大表千万谨慎3.1 ADD新增字段新增一列单字段写法最常用可指定位置批量新增写法括号形式讲义课件版3.2 MODIFY修改字段属性不能修改列名单字段写法批量修改正确写法不要括号逗号分隔多条modify3.3 DROP删除字段极度高危3.4 RENAME修改整张表名字3.5 CHANGE改列名修改字段属性功能最强3.6 修改表级属性字符集、存储引擎3.7 ALTER写法速查表新手必背区分讲义伪语法和真实MySQL语法3.8 MODIFY vs CHANGE 核心对比四、删除相关操作行、全表、清空4.1 delete删除某一行 / 部分行DML4.2 truncate清空整张表保留表结构DDL4.3 DROP TABLE一键删除整张表4.4 ⚠️ 三个易混删除命令对比面试常考五、复制表快速备份/复刻表5.1 LIKE只复制表结构不复制数据5.2 CREATE ... SELECT复制结构 数据5.3 组合复制结构全部数据约束六、 初学者常见报错速查表 初学者高频踩坑总结✍️ 手敲实操测试完整练习脚本复制执行 结尾 拓展思考面试可以思考 补充阅读知识点拓展MySQL表操作入门玩转你的数据库Excel小白也能看懂 摘要数据库就像超级加强版Excel。数据库Excel工作簿数据表工作表。本文手把手带你吃透建表、查表、改表、删表、复制表搞懂存储引擎、磁盘文件、字段约束、容易混淆的ALTER语法。新增「手敲实操测试」「常见报错速查表」把真实踩坑经验写进文章适合MySQL零基础小伙伴阅读。⚠️ 重要提醒MySQL没有CtrlZ撤销DDL操作建表改表删表一旦执行手滑就可能数据跑路测试环境随便造线上务必谨慎环境说明核心语法兼容MySQL 5.7 / 8.0版本差异处会专门标注。 前言刚学数据库的小伙伴经常分不清数据库和表。记住这个生活化比喻Database数据库整个Excel文件工作簿里面可以放很多张表Table数据表Excel里面的某一张工作表真正用来存业务数据我们建好数据库之后重头戏就是操作表。写SQL操作表等价于Excel新增工作表、修改表头、删除某一列。但Excel错了可以按CtrlZ回退MySQL的表结构操作大多属于DDL敲完回车直接生效没有系统回收站敲命令前脑子先过一遍 实操小提示MySQL命令行常见提示符提示符含义处理方式mysql命令就绪可以输入SQL正常编写执行-语句未写完等待继续输入输入\c回车取消当前语句重写单引号没有闭合\c取消检查引号配对一、创建表 CREATE TABLE绘制你的表头1.1 基础语法建表语句有两种完全等价的书写形式讲义课件常用「空格分隔版」工具自动导出SQL常用「等号简写版」。-- 形式1空格分隔版教材/讲义原版CREATETABLE表名(字段名1数据类型[约束][comment注释],字段名2数据类型[约束][comment注释])characterset字符集collate校验规则engine存储引擎;-- 形式2等号简写版工具导出/项目常用CREATETABLE表名(字段名1数据类型[约束][comment注释],字段名2数据类型[约束][comment注释])charset字符集collate校验规则engine存储引擎;1.2 核心参数大白话拆解参数通俗解释field字段名Excel表头列的名字datatype数据类型规定该列存什么int存数字varchar存字符串date存日期character set / charset字符编码不指定就继承数据库的字符集优先使用utf8mb4MySQL的utf8是残缺版本存不了emoji表情与部分生僻字collate 校验规则控制字符串排序、比较逻辑一般不需要手动修改engine 存储引擎决定数据怎么在硬盘存储MySQL 5.5默认InnoDBcomment字段注释写注释是救命好习惯半年后看自己写的SQL不至于一脸懵 关键说明character set是完整关键字后面直接跟字符集名不能加等号想用等号写法必须用它的别名charset。collate和engine两种写法都支持空格和等号效果完全一致。❌ 错误写法character setutf8语法直接报错✅ 正确写法charsetutf8或character set utf81.3 新手必知常见数据类型选型不用记全所有类型掌握这几个覆盖90%场景类型用途注意事项int整数存年龄、编号、数量普通业务int足够范围-21亿~21亿bigint大整数存主键ID、雪花算法ID数据量极大的表用varchar(n)可变长度字符串存姓名、地址、简介n是最大字符数省空间最常用char(n)固定长度字符串存密码、MD5、UUID长度固定性能略高不足长度补空格date日期存生日、入职日期格式YYYY-MM-DDdatetime日期时间存创建时间、下单时间格式YYYY-MM-DD HH:MM:SStinyint小整数存状态、性别范围0~255省空间 char vs varchar 怎么选长度确定用char比如MD5密码固定32位用char(32)长度不确定用varchar比如用户名、地址用varchar(n)同样长度char性能略好varchar更省空间1.4 字段约束建表核心新手必学约束就是给字段定规矩不符合规矩的数据插不进去。约束关键字作用NOT NULL非空该字段必须填不能为NULLDEFAULT 值默认值不填就自动用默认值填充PRIMARY KEY主键唯一标识一行一张表只能一个主键自动非空唯一AUTO_INCREMENT自增配合主键用插入数据自动1不用手动填IDUNIQUE唯一约束该列值不能重复标准建表案例带约束接近真实开发createtableusers(idintprimarykeyauto_incrementcomment主键ID,namevarchar(20)notnullcomment用户名,passwordchar(32)notnullcomment32位MD5密码,agetinyintdefault18comment年龄默认18,birthdaydatecomment生日)charsetutf8mb4engineInnoDB;主键自增是业务表标配id不用手动传插入自动从1开始递增。1.5 存储引擎对应的磁盘文件面试高频考点MySQL的表不是虚拟的实实在在保存在磁盘的data目录下不同存储引擎生成的文件完全不一样。MyISAM引擎老版本遗留新项目不推荐一张表会生成3个独立文件users.frm保存表结构表头5.7所有引擎都会生成这个文件users.MYDMY Data存放真实表数据users.MYIMY Index存放索引特点数据、索引、表结构分开存放不支持事务、只支持表锁数据库异常崩溃有概率发生表损坏现在业务很少使用。InnoDB引擎生产环境默认首选xxx.frm表结构元数据文件MySQL 8.0彻底取消frm文件元数据存入内置数据字典磁盘上再也看不到frm文件xxx.ibd一个文件包揽全部表数据索引全部放在这个文件特点支持事务ACID、行级锁、崩溃恢复、外键约束是现在业务开发首选引擎。 小实验分别创建MyISAM和InnoDB两张表打开MySQL的data文件夹对比文件数量直观感受两者差异。1.6 建表命名规范好习惯从入门开始用小写字母下划线不要大写、不要中文、不要空格表名见名知意用户表user/users订单表order/orders避免用MySQL关键字做表名/字段名比如name、order、condition容易报错主键统一叫id字段名统一风格比如create_time、update_time二、查看表结构 DESC看看表头长啥样建完表不要跑到磁盘扒frm文件一条命令查看表字段、类型、约束。2.1 基础语法desc表名;-- 完整写法describe表名;2.2 输出字段说明执行desc users;输出各列含义列名含义Field字段名Type字段数据类型Null是否允许为空YES/NOKey索引标记PRI主键UNI唯一索引MUL普通索引Default字段默认值Extra额外属性如auto_increment自增2.3 ✨ 拓展查看技巧普通desc看不到完整字段注释想看注释、字符集、引擎用这两条-- 查看完整字段信息含注释showfullcolumnsfromusers;-- 查看完整建表语句字符集、引擎、约束一目了然showcreatetableusers; 易混命令区分show tables;查看当前库下所有表名字desc 表名;查看某一张表内部字段结构千万不要搞混三、修改表 ALTER TABLE —— 线上大表千万谨慎需求永远会变建表没考虑周全需要加列、改字段长度、改列名、删除列、改字符集全部靠ALTER TABLE。⚠️ 高危警告MySQL 5.7原生ALTER大表会锁表整个表读写被阻塞生产环境尽量业务低峰期执行大表优先使用在线DDL工具不要随便操作先插入两条测试数据观察修改前后数据变化insertintousers(name,password,age,birthday)values(张三,e10adc3949ba59abbe56e057f20f883e,20,2004-05-10),(李四,e10adc3949ba59abbe56e057f20f883e,21,2003-09-15);3.1 ADD新增字段新增一列单字段写法最常用可指定位置需求users表增加手机号tel放在name字段的后面。altertableusersaddtelchar(11)comment手机号aftername;after name指定新字段放在哪个列后面写first代表放到表的最开头省略则默认放在表末尾。✨ 效果原有数据完全保留老数据新增字段自动填充NULL如果有default则填充默认值。批量新增写法括号形式讲义课件版一次性新增多个字段用括号包裹逗号分隔。altertableusersadd(emailvarchar(50)comment邮箱,addressvarchar(100)comment家庭住址); 批量ADD大坑一旦使用括号批量新增不能写after/first指定字段位置所有新字段只能追加到表的末尾。需要控制列顺序必须去掉括号单条写。3.2 MODIFY修改字段属性不能修改列名⚠️ 重要踩坑讲义教材写的MODIFY(字段1,字段2)是伪语法MySQL实际不支持括号批量MODIFY会报1064语法错误单字段写法需求将name字段从varchar(20)扩容到varchar(50)列名保持不变。altertableusersmodifynamevarchar(50)notnull;批量修改正确写法不要括号逗号分隔多条modifyaltertableusersmodifyemailvarchar(80)comment企业邮箱,modifyaddressvarchar(200);划重点MODIFY只能改类型、长度、是否为空、默认值不能修改列的名字MODIFY如果漏写了原有的约束比如not null、comment执行后会丢失建议完整重写。3.3 DROP删除字段极度高危需求删除password密码这一列altertableusersdroppassword; 血泪提醒1执行DROP字段该列全部数据直接永久消失MySQL没有回收站。测试环境随便玩线上操作前务必备份 血泪提醒2DROP不支持括号批量删除多列删除多列必须分开写多个DROP-- 错误写法altertableusersdrop(password,tel);-- 正确写法altertableusersdroppassword,droptel;3.4 RENAME修改整张表名字将users表重命名为employeeto关键字可以省略。altertableusersrenametoemployee;-- 简写效果完全一致altertableusersrenameemployee;注意RENAME没有批量括号写法一次只能改一张表名。3.5 CHANGE改列名修改字段属性功能最强需求把employee表的name列改名为username字段类型varchar(50)不变。altertableemployee change name usernamevarchar(50)notnull;⚡ 超级大坑CHANGE命令哪怕字段类型不变也必须完整写一遍字段定义否则直接报语法错误注意CHANGE也没有批量括号写法一次只能改一列。 小补充MySQL 8.0新增RENAME COLUMN语法专门只改列名不用重写类型更方便altertableemployeerenamecolumnnametousername;3.6 修改表级属性字符集、存储引擎不止字段能改整张表的字符集、引擎也能改-- 修改表字符集为utf8mb4altertableemployeeconverttocharsetutf8mb4;-- 修改存储引擎为InnoDBaltertableemployeeengineInnoDB;3.7 ALTER写法速查表新手必背区分讲义伪语法和真实MySQL语法命令单字段写法是否支持括号批量(MySQL真实)能不能改列名能不能指定位置ADD新增字段✅✅❌单字段支持批量不支持MODIFY修改属性✅❌❌❌CHANGE改列名属性✅❌✅❌DROP删除字段✅❌❌-RENAME改表名✅❌改表名-考试做题可以记忆讲义模板实操敲代码MODIFY、DROP、CHANGE不要加括号。3.8 MODIFY vs CHANGE 核心对比命令能不能改列名典型使用场景MODIFY❌只改字段类型、长度、约束列名不动CHANGE✅需要修改列名或者同时改名改属性四、删除相关操作行、全表、清空4.1 delete删除某一行 / 部分行DML只删除行数据表结构保留可以加where条件事务内可以回滚。-- 删除id1单独一行deletefromuserswhereid1;⚠️千万不能省略where否则删除表里全部数据4.2 truncate清空整张表保留表结构DDLtruncatetableusers;特点速度极快重置自增主键不能加where无法回滚。4.3 DROP TABLE一键删除整张表droptableifexistsusers;IF EXISTS容错表不存在也不会报错只会警告写自动化脚本强烈建议带上。4.4 ⚠️ 三个易混删除命令对比面试常考命令类型作用能不能加where可以回滚drop table 表名DDL整张表直接干掉结构数据索引全部删除❌❌delete from 表名DML删除行数据保留表结构✅✅事务内truncate table 表名DDL清空全部行保留表结构重置自增❌❌口诀删表用drop删部分数据用deletewhere清空全表保留表头可以用truncate。五、复制表快速备份/复刻表新手经常需要复制表做测试这两条命令超实用5.1 LIKE只复制表结构不复制数据createtableuser_baklikeusers;完美复制表结构、主键、索引、约束表里没有数据。5.2 CREATE … SELECT复制结构 数据createtableuser_bak2select*fromusers;数据和字段都复制过来缺点主键、索引、约束不会复制只复制字段和数据。5.3 组合复制结构全部数据约束-- 先复制结构createtableuser_baklikeusers;-- 再插入全部数据insertintouser_bakselect*fromusers;六、 初学者常见报错速查表错误码典型报错原因解决办法1146Table ‘shturl.’ doesn’t exist表名拼写错误 / 没切换到正确的数据库执行select database();确认库show tables;复制真实表名1064You have an error in your SQL syntax语法错误括号、引号、分号、关键字拼写检查符号是否英文半角MODIFY不要套括号检查逗号1054Unknown column ‘xxx’ in ‘field list’字段名写错 / 表中没有这个字段desc 表名;查看真实字段名1366Incorrect string value字符集不支持中文/emoji表/字段字符集改成utf8mb41067Invalid default value默认值和字段类型不匹配检查default后面的值类型-提示符变成-语句没写完 / 引号没闭合输入\c回车取消重写语句 初学者高频踩坑总结SQL末尾分号不要丢不写分号MySQL认为语句没有结束卡在-等待输入。character set是完整关键字后面不能加等号想用等号必须写别名charset。使用CHANGE改名千万不要漏写完整字段类型否则直接语法报错。批量ADD(...)带括号语法不能写after/first指定字段位置新字段只能追加到表末尾。❗重点MODIFY、DROP、CHANGE不支持括号批量语法讲义模板不等于可运行代码。DROP不支持括号批量删多列删除多列必须写多个DROP 列名用逗号分隔。delete删除行切记不要漏写where条件否则清空全表。删除字段、删除表三思而后行MySQL没有回收站数据删了很难救。字符集大坑拒绝直接用utf8优先使用utf8mb4支持emoji、生僻字。MyISAM已经逐步淘汰新项目一律默认InnoDB支持事务、崩溃恢复。MySQL 8.0已经没有frm文件表元数据存入内部数据字典。大表执行ALTER TABLE避开业务高峰锁表会导致业务卡死。MODIFY修改字段属性时原有的not null、comment、default最好一起重写否则会丢失。✍️ 手敲实操测试完整练习脚本复制执行实操目标完整走一遍建表→查看→新增字段→修改字段→改列名→删字段→改表名→删除行→复制表→清空表→删除表巩固全部知识点。先切换到你的测试数据库例如use test1;一条一条执行每执行完建议执行desc student;观察表变化。-- 1. 创建练习表 student带主键、自增、非空、默认值createtablestudent(idintprimarykeyauto_incrementcomment主键ID,stu_namevarchar(30)notnullcomment学生姓名,agetinyintdefault18comment年龄,birthdaydatecomment出生日期)charsetutf8mb4engineInnoDB;-- 查看表结构、建表语句descstudent;showcreatetablestudent;-- 2. 插入两行测试数据主键自增不用传idinsertintostudent(stu_name,age,birthday)values(张三,19,2007-05-10),(李四,20,2006-09-15);select*fromstudent;-- 3. 单字段新增指定位置tel放在stu_name后面altertablestudentaddtelchar(11)comment手机号afterstu_name;descstudent;-- 4. ADD括号批量新增两列只能追加末尾altertablestudentadd(emailvarchar(50)comment邮箱,addressvarchar(100)comment家庭住址);descstudent;-- 5. MODIFY单字段修改长度保留非空altertablestudentmodifystu_namevarchar(50)notnull;descstudent;-- 6. MODIFY批量修改禁止括号逗号分隔多条modifyaltertablestudentmodifyemailvarchar(80)comment校园邮箱,modifyaddressvarchar(200);descstudent;-- 7. CHANGE修改列名 tel → phone必须完整写字段类型altertablestudent change tel phonechar(11);descstudent;-- 8. DROP删除多列不能括号多个DROPaltertablestudentdropage,dropaddress;descstudent;select*fromstudent;-- 9. delete删除【某一行】务必带wheredeletefromstudentwhereid1;select*fromstudent;--10. 修改表名 student → t_studentaltertablestudentrenametot_student;showtables;desct_student;--11. 复制表备份结构数据createtablestudent_bakliket_student;insertintostudent_bakselect*fromt_student;select*fromstudent_bak;--12. truncate清空全部数据保留表结构truncatetablet_student;desct_student;select*fromt_student;--13. drop删除测试表收尾清理环境droptableifexistst_student;droptableifexistsstudent_bak;showtables;实操排错小提示出现ERROR 1146表名拼写错误复制show tables;输出的真实表名出现ERROR 1064语法错误检查括号、逗号、分号MODIFY不要套括号提示符变成-输入\c回车放弃当前语句重新写。 结尾表操作是MySQL的地基后续的增删改查DML、索引、约束、事务全部建立在表结构之上。记住口诀CREATE建DESC看ALTER改DROP删分清讲义伪语法与MySQL真实语法牢记DDL高危操作MySQL入门直接跨过一大半坎。 拓展思考面试可以思考MyISAM三张磁盘文件和InnoDB ibd文件各自优缺点什么场景会考虑MyISAM为什么线上环境大表执行ALTER TABLE要格外谨慎有什么优化方案COMMENT注释除了desc还有哪些SQL方式读取字段注释批量ADD字段为什么不支持指定位置从MySQL底层存储的角度可以怎么理解delete和truncate都能清空表为什么truncate速度快很多 补充阅读知识点拓展MyISAM只支持表级锁写操作会锁整张表InnoDB支持行锁并发读写性能更强。MyISAM没有事务数据库宕机容易损坏表。delete属于DML语句可以回滚drop、truncate属于DDL语句执行隐式提交无法回滚。MySQL 8.0支持事务型DDL部分DDL操作支持原子性是和5.7的重大底层差异。生产环境改大表结构通常使用pt-online-schema-change、gh-ost等在线DDL工具避免锁表影响业务。InnoDB主键是聚簇索引数据就挂在主键树上所以每张表必须有主键。