【第二章】库、表、增删改查操作库的操作Database查看所有库创建库使用库查看建库语句非常常用修改库一般改字符集/排序规则删除库慎重字符集与排序规则查看 MySQL 支持哪些字符集查看排序规则Collation数据类型建表之前先把这个想清楚数值类型整数、浮点、定点怎么选1整数类型TINYINT/SMALLINT/INT/BIGINT2FLOAT/DOUBLE浮点数 vs DECIMAL定点数字符串类型CHAR / VARCHAR / TEXT 的“性格”1CHAR vs VARCHAR2VARCHAR vs TEXT什么时候上 TEXT二进制类型BINARY/VARBINARY/BLOBENUM / SET日期类型DATE / TIME / DATETIME / TIMESTAMP / YEAR表的操作Table查看当前库的所有表创建表带注释、字符集、引擎表在磁盘上的文件了解即可但挺有用查看表结构再多两个常用查看排障很好用修改表ALTER TABLE 常用套路1加字段2改字段类型/属性3删字段4重命名表删除表 vs 清空表这俩经常被混用增删改查CRUD0准备一张演示表1CreateINSERT 新增全列插入不推荐写在业务代码里指定列插入更常用一次插多行效率更好插入时“顺便”查出来INSERT ... SELECT2RetrieveSELECT 查询查所有列调试可用业务慎用只查需要的列业务常用使用AS指定别名DISTINCT最直接的去重单列去重多列去重“组合去重”DISTINCT ORDER BYDISTINCT 遇到 NULLWHERE条件过滤核心中的核心ORDER BY LIMIT排序与分页聚合函数COUNT / SUM / AVG / MAX / MINGROUP BY HAVING分组统计与分组过滤3UpdateUPDATE 修改基本更新一定要带 WHERE同时改多个字段4DeleteDELETE 删除按条件删除一定要带 WHERE批量删除建议带 LIMIT 做保险本章小结后面章节我们默认环境是 MySQL 8.x存储引擎以 InnoDB 为主字符集建议统一用utf8mb4。库的操作Database库database/schema你可以理解成一个“逻辑容器”把相关的表收在一起。一个项目一般一个库或者按业务拆多个库。查看所有库SHOWDATABASES;DATABASES是复数大小写不敏感SQL 关键字在 MySQL 里通常不区分大小写。创建库CREATEDATABASEIFNOTEXISTSdemo_dbDEFAULTCHARACTERSETutf8mb4DEFAULTCOLLATEutf8mb4_0900_ai_ci;我这里强烈建议你养成两个习惯IF NOT EXISTS防止重复执行脚本直接报错明确CHARACTER SET / COLLATE明确编码使用库USEdemo_db;这条命令其实就是告诉 MySQL后面没写库名的表都默认在demo_db里找。查看建库语句非常常用SHOWCREATEDATABASEdemo_db;当你在别人机器上发现“同样的表怎么排序/大小写规则不一样”时这条语句能直接查看底层配置。修改库一般改字符集/排序规则ALTERDATABASEdemo_dbDEFAULTCHARACTERSETutf8mb4DEFAULTCOLLATEutf8mb4_0900_ai_ci;提醒一句改库的默认字符集不等于把库里已有表/字段的字符集全部改了。已经建好的表字段还是得单独处理。删除库慎重DROPDATABASEdemo_db;这条命令的含义很直白库没了里面的表也都没了。一般我建议你在真正DROP前先做两件事先SHOW TABLES;确认你在对的库里做好备份至少把关键表导出来字符集与排序规则很多人第一次被字符集教育基本都是在“存中文乱码/表情插不进去/排序怎么怪怪的”。查看 MySQL 支持哪些字符集SHOWCHARSET;经验点MySQL 8.x 默认字符集一般是utf8mb4MySQL 5.7 默认字符集经常还是latin1这就是乱码的常见起点查看排序规则CollationSHOWCOLLATION;排序规则你可以先粗暴理解成字符串怎么比较、怎么排序。比如utf8mb4_0900_ai_ci这串后缀拆开看0900基于 Unicode Collation Algorithm 9.0.0aiaccent-insensitive口音/重音不敏感cicase-insensitive大小写不敏感你后面会遇到的现象包括但不限于为什么A和a排序/比较时被当成“差不多”为什么有的规则在 MySQL 8 有MySQL 5 没有版本差异数据类型建表之前先把这个想清楚建表这件事很多人一开始会把注意力全放在语法上但其实真正影响后续开发体验的是“字段类型选得对不对”。类型选得对你后面写 CRUD 会很爽类型选错了你会在数据量上来以后天天改表、天天迁移、天天修bug。我这里也按这个思路来按常用程度排序数据值类型整数/浮点/定点字符串类型CHAR/VARCHAR/TEXT二进制类型BINARY/VARBINARY/BLOB日期类型DATE/TIME/DATETIME/TIMESTAMP/YEAR数值类型整数、浮点、定点怎么选先把一个结论放前面业务金额优先DECIMAL别用FLOAT/DOUBLE计数/ID用INT/BIGINT看规模别拿VARCHAR存数字状态/布尔多数场景TINYINT足够BOOL在 MySQL 里本质也是TINYINT(1)1整数类型TINYINT/SMALLINT/INT/BIGINT这里没必要过多强调但需要知道一个方向位数越大、范围越大、占用空间越大。CREATETABLEifnotexistst_int_range(idBIGINTPRIMARYKEYAUTO_INCREMENT,aTINYINT,bTINYINTUNSIGNED,cINT,dBIGINTUNSIGNED)ENGINEInnoDBDEFAULTCHARSETutf8mb4;顺便给你一个很实用的技巧当你不确定某个字段未来会不会增长得很夸张时直接BIGINT让上限留足。真正的性能问题更多来自索引设计和查询方式不是“用 BIGINT 就慢”在数据结构中我们也会使用到时间换取空间的做法。2FLOAT/DOUBLE浮点数 vs DECIMAL定点数浮点数有精度损失定点数不丢精度。CREATETABLEifnotexistst_money_demo(idBIGINTPRIMARYKEYAUTO_INCREMENT,price_floatDOUBLE,price_decimalDECIMAL(10,2));INSERTINTOt_money_demo(price_float,price_decimal)VALUES(0.10.2,0.30);SELECTprice_float,price_decimalFROMt_money_demo;你会发现price_float可能出现一串你不想看到的小数尾巴这就是典型的浮点误差。所以金额、积分、精确统计类字段直接DECIMAL(M, D)别犹豫。课件里也写了DECIMAL的默认值和范围MySQL 的M最大 65D最大 30你做普通业务足够了。字符串类型CHAR / VARCHAR / TEXT 的“性格”字符串类型最大的坑通常不是“能不能存”而是存储方式不一样性能和空间完全不同结尾空格、长度截断、索引支持都不一样1CHAR vs VARCHAR我直接放进来一个例子大家可以跑一下感受CREATETABLEifnotexistsvc(vVARCHAR(4),cCHAR(4));INSERTINTOvcVALUES(ab ,ab );SELECTCONCAT((,v,)),CONCAT((,c,))FROMvc;一般你会看到VARCHAR会保留尾部空格CHAR在取值时会把尾部填充的空格去掉因为它存的时候是“固定长度右侧补空格”再来一个“截断警告”INSERTINTOvcVALUES(ab ,ab );SHOWWARNINGS;这条在写脚本/导数据的时候特别关键很多数据不是“插入失败”而是“插进去了但被截断了”你要学会看WARNINGS。2VARCHAR vs TEXT什么时候上 TEXTVARCHAR适合“短字符串 需要经常检索/建索引”的字段用户名、邮箱、手机号TEXT适合“长内容 不常用来做过滤条件”的字段文章正文、备注、简介一个更像真实业务的示例CREATETABLEifnotexistsarticle(idBIGINTPRIMARYKEYAUTO_INCREMENT,titleVARCHAR(200)NOTNULL,contentLONGTEXTNOTNULL,created_atDATETIMENOTNULLDEFAULTCURRENT_TIMESTAMP)ENGINEInnoDBDEFAULTCHARSETutf8mb4;如果你真的需要对长文本做检索常见做法 1FULLTEXT全文索引常见做法 2前缀索引例如content(100)但这块属于“能用但要谨慎”后面讲索引时再细聊二进制类型BINARY/VARBINARY/BLOB这类字段你平时可能不常写但一旦遇到比如存缩略图、文件 hash、签名、二进制 token就得会选。CREATETABLEifnotexistst_bin(idBIGINTPRIMARYKEYAUTO_INCREMENT,md5CHAR(32)NOTNULL,sha256BINARY(32)NOTNULL,raw_dataBLOBNULL)ENGINEInnoDBDEFAULTCHARSETutf8mb4;简单理解BINARY/VARBINARY按字节存适合“固定/可变长度二进制串”BLOB/TEXT大字段都可能溢出到溢出页别拿它当普通字段乱建索引ENUM / SETENUM从给定值里选一个SET从给定值里选多个CREATETABLEifnotexistst_enum_set(idBIGINTPRIMARYKEYAUTO_INCREMENT,statusENUM(INIT,PAID,CANCEL)NOTNULL,tagsSET(JAVA,MYSQL,LINUX,NETWORK)NULL);这俩在 MySQL 内部会用整数表示空间上不算浪费。但我个人建议你把它当成“比较明确且不怎么变的字段”来用比如性别、支付状态。如果值经常变还是走字典表/配置更稳。日期类型DATE / TIME / DATETIME / TIMESTAMP / YEARDATETIME范围很大1000 ~ 9999存什么就是什么不会因为时区自动变化TIMESTAMP范围相对小从 1970 开始常用于“记录时间点”并且会受时区影响更像“真实时间点”建一个时间字段齐全一点的表CREATETABLEifnotexistst_time(idBIGINTPRIMARYKEYAUTO_INCREMENT,dDATE,tTIME,dtDATETIME(3)DEFAULTCURRENT_TIMESTAMP(3),tsTIMESTAMP(3)DEFAULTCURRENT_TIMESTAMP(3),yYEAR);fsp小数秒精度0~6你做日志、埋点时会很常见DATETIME(3)表示毫秒级。表的操作Table表是你真正放数据的地方。建表这块最核心的其实不是“语法”而是字段类型怎么选这一章已经把常用类型揉进来了约束怎么加保证数据不烂引擎/字符集怎么统一保证后面不折腾查看当前库的所有表SHOWTABLES;创建表带注释、字符集、引擎先建一个简单的users表做演示CREATETABLEIFNOTEXISTSusers(idBIGINTPRIMARYKEYAUTO_INCREMENT,nameVARCHAR(20)NOTNULLCOMMENT用户名,passwordCHAR(32)NOTNULLCOMMENTMD5(32位)示例,birthdayDATENULLCOMMENT生日,created_atDATETIMENOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT创建时间)ENGINEInnoDBDEFAULTCHARACTERSETutf8mb4DEFAULTCOLLATEutf8mb4_0900_ai_ci;这里我顺手做了几件“更像真实项目”的设置PRIMARY KEY主键不是可选项几乎所有业务表都需要AUTO_INCREMENT让 id 自增省心NOT NULL / DEFAULT别让空值随便进来COMMENT你写给未来的自己看的ENGINE/CHARSET/COLLATE把环境统一掉表在磁盘上的文件了解即可但挺有用这块不需要背但理解了以后排障会轻松一点InnoDB通常会有.ibd独立表空间时MyISAM常见是.MYD数据、.MYI索引、.sdi表信息你会发现引擎不一样底层的“存法”也不一样所以能力差异事务/锁/恢复也就不奇怪了。查看表结构DESCusers;或者SHOWCREATETABLEusers;如果你只想“快速看字段”用DESC如果你想“把建表语句完整抄走”用SHOW CREATE TABLE。再多两个常用查看排障很好用SHOWFULLCOLUMNSFROMusers;SHOWINDEXFROMusers;如果你在看别人库的时候发现某个字段怎么“看起来一样但行为不一样”SHOW FULL COLUMNS能把字符集、排序规则、默认值这些信息一次性列出来。修改表ALTER TABLE 常用套路1加字段ALTERTABLEusersADDCOLUMNphoneVARCHAR(20)NULLCOMMENT手机号;2改字段类型/属性ALTERTABLEusersMODIFYCOLUMNnameVARCHAR(50)NOTNULLCOMMENT用户名;3删字段ALTERTABLEusersDROPCOLUMNphone;4重命名表RENAMETABLEusersTOapp_user;删除表 vs 清空表这俩经常被混用删除表DROPTABLEapp_user;清空表更常用也更危险TRUNCATETABLEapp_user;我对这两条的直觉总结是DROP表结构都没了TRUNCATE表还在但数据直接“清零”通常比DELETE快很多后面你写清数据脚本时看到TRUNCATE这词建议你停一下确认是不是你真想要的效果。增删改查CRUDCRUD 的语法不难我一般把 DMLINSERT/UPDATE/DELETE当成带风险的操作。0准备一张演示表CREATETABLEIFNOTEXISTSexam(idBIGINTPRIMARYKEYAUTO_INCREMENT,nameVARCHAR(20)NOTNULLCOMMENT同学姓名,chineseFLOATNULLCOMMENT语文成绩,mathFLOATNULLCOMMENT数学成绩,englishFLOATNULLCOMMENT英语成绩)ENGINEInnoDBDEFAULTCHARSETutf8mb4;插几条测试数据INSERTINTOexam(name,chinese,math,english)VALUES(唐三藏,67,98,56),(孙悟空,87,78,77),(猪悟能,88,98,90),(沙悟净,82,84,67),(刘玄德,55,85,45),(孙权,70,73,78),(宋公明,75,65,30);再补一个“更像业务”的示例表把数据类型融合起来CREATETABLEifnotexistsorders(idBIGINTPRIMARYKEYAUTO_INCREMENT,order_noCHAR(20)NOTNULLCOMMENT订单号定长更合适,user_idBIGINTNOTNULLCOMMENT用户ID,amountDECIMAL(10,2)NOTNULLCOMMENT订单金额,statusENUM(INIT,PAID,CANCEL)NOTNULLDEFAULTINITCOMMENT状态,remarkVARCHAR(200)NULLCOMMENT备注,extra JSONNULLCOMMENT扩展信息,created_atDATETIME(3)NOTNULLDEFAULTCURRENT_TIMESTAMP(3),updated_atDATETIME(3)NOTNULLDEFAULTCURRENT_TIMESTAMP(3)ONUPDATECURRENT_TIMESTAMP(3),UNIQUEKEYuk_order_no(order_no),KEYidx_user_id(user_id),KEYidx_created_at(created_at))ENGINEInnoDBDEFAULTCHARSETutf8mb4;1CreateINSERT 新增全列插入不推荐写在业务代码里INSERTINTOusersVALUES(NULL,张三,e10adc3949ba59abbe56e057f20f883e,2005-01-01,NOW());这种写法的问题是表结构一改多一个字段/字段顺序变了你所有 SQL 都得跟着改。指定列插入更常用INSERTINTOusers(name,password,birthday)VALUES(李四,e10adc3949ba59abbe56e057f20f883e,2004-12-12);一次插多行效率更好INSERTINTOusers(name,password,birthday)VALUES(王五,e10adc3949ba59abbe56e057f20f883e,NULL),(赵六,e10adc3949ba59abbe56e057f20f883e,2003-05-20);插入时“顺便”查出来INSERT … SELECT这种写法在初始化数据、迁移数据时非常常见CREATETABLEifnotexistsexam_copyLIKEexam;INSERTINTOexam_copy(name,chinese,math,english)SELECTname,chinese,math,englishFROMexamWHEREmath80;2RetrieveSELECT 查询查询写得顺不顺关键在于你要记住 SQL 的“逻辑执行顺序”不是你写的顺序同样的针对于select查询我们也有很多不同的查询方式后面再为大家逐一总结。查所有列调试可用业务慎用SELECT*FROMexam;其中*代表的是通配符的意思可以把数据库的所有表都查询出来但是这个操作也需要谨慎要不然就会导致数据库的服务器内存吃满卡死的情况出现。只查需要的列业务常用SELECTid,name,mathFROMexam;也可以是带有表达式的查询比如mathmath10这样的式子这里就不再赘述。使用AS指定别名SELECTid,name,mathASscoreFROMexam;DISTINCT最直接的去重单列去重SELECTDISTINCTnameFROMusers;这个结果只保证name不重复。多列去重“组合去重”SELECTDISTINCTname,birthdayFROMusers;这里去重的单位是(name, birthday)这个组合。也就是说只要两列都一样才算重复有一个不一样都算做不重复。DISTINCT ORDER BYSELECTDISTINCTnameFROMusersORDERBYname;一般没问题。但你要是写成SELECT DISTINCT name FROM users ORDER BY id;很多时候会报错或结果不符合预期因为id不在 select 列表里。DISTINCT 遇到 NULLSELECTDISTINCTbirthdayFROMusers;NULL会被当成“一个值”来处理多个NULL最终只会显示一个NULL,在SQL中它可以表示什么都没填。而关于NULL的运算如果有一列的结果值为NULL那么后续运算的表达式结果都是NULL。WHERE条件过滤核心中的核心SELECTid,name,mathFROMexamWHEREmath80;这里注意表示比较相等而非常见条件你以后会天天用比较 ! 范围BETWEEN a AND b这里是[a,b]的闭区间集合IN (...)模糊LIKE %关键字%这是字符串的模糊匹配空值IS NULL / IS NOT NULL注意不是 NULL示例SELECTid,nameFROMexamWHEREnameLIKE%悟%;再补一个IN的例子很多人写业务筛选会用到SELECTid,name,mathFROMexamWHEREnameIN(孙悟空,猪悟能);还有AND和OR的使用方式本质是与和或运算的区别WHERE执行的时候会根据写的逻辑判断先执行那个如果加括号那么括号内的内容就有最高的优先级否则的话就是按照AND优先级高于OR去执行。ORDER BY LIMIT排序与分页SELECTid,name,totalFROM(SELECTid,name,(chinesemathenglish)AStotalcommentAS在这里是起一个别名FROMexam)tORDERBYtotalDESCLIMIT3;这里的排序是默认升序的ASC如果想要降序那么就使用desc来降序代表的descend。LIMIT 直接从物理层面限制数据传输量。对于包含数十万甚至上亿条记录的表每次只获取固定数量的记录能显著减少网络传输时间、降低应用程序内存占用同时减少数据库缓冲池的压力。LIMIT 必须与明确的 ORDER BY 子句配合才能保证分页结果的正确性和稳定性。没有排序规则的 LIMIT 返回的记录顺序是随机的会导致重复或遗漏数据的问题。聚合函数COUNT / SUM / AVG / MAX / MINSELECTCOUNT(*)AScnt,AVG(math)ASavg_math,MAX(english)ASmax_englishFROMexam;count是用来查询表中的记录数的我们也可以写为COUNT(name)等但是这样写遇见NULL了以后不会进行计数。GROUP BY HAVING分组统计与分组过滤先提醒一句WHERE是“分组前过滤”HAVING是“分组后过滤”。比如按分数段随便举例分组统计SELECTCASEWHENmath90THEN90WHENmath80THEN80-89WHENmath60THEN60-79ELSE0-59ENDASmath_level,COUNT(*)AScntFROMexamGROUPBYmath_levelHAVINGcnt2ORDERBYcntDESC;3UpdateUPDATE 修改基本更新一定要带 WHEREUPDATEexamSETenglish60WHEREname宋公明;同时改多个字段UPDATEexamSETchinesechinese5,mathmath5WHEREname刘玄德;一般会按这个习惯来1.先写SELECT ... WHERE ...确认命中行2.再把SELECT改成UPDATE4DeleteDELETE 删除按条件删除一定要带 WHEREDELETEFROMexamWHEREname宋公明;批量删除建议带 LIMIT 做保险DELETEFROMexamWHEREenglish40LIMIT10;顺便再强调一次DELETE是“按行删”TRUNCATE是“直接清表”。两者的风险级别不是一个量级。本章小结这一章我们为大家大致介绍了下面三个内容总结一下就是库/表的 DDL 会写尤其是SHOW CREATE这种排障利器CRUD 不难但 WHERE、聚合、分组这些是分水岭尤其是 WHERE 写错的后果很真实数据类型要选对整数/定点/字符串/日期的“性格”不一样选错后期基本都要还债字符集和排序规则要统一建议utf8mb4下一章我们会针对于数据库约束、联合查询来详细为大家展开这一章的内容也会有所涉及。