【数据库相关】MySQL全分类SQL详解（超多数据类型+全约束+实战落地）

MySQL全分类SQL详解超多数据类型全约束实战落地数据库开发工程师进阶版MySQL全分类SQL详解超多数据类型全约束实战落地前置说明一、DDL 数据定义语言建库、建表、改结构、约束核心1.1 建库规范SQL1.2 企业级建表超多数据类型 全约束演示业务场景电商用户详情表 账户资产表融合示范关键知识点拆解① 数据类型选型硬核规范② 约束全覆盖1.3 常用改表DDL加字段/改类型/加约束/加索引1.4 视图/索引进阶DDL二、DML 数据操作语言增删改带严格字段适配2.1 INSERT 插入适配全字段类型2.2 UPDATE 更新强制带WHERE防全表更新2.3 DELETE 删除逻辑删除优先物理删除谨慎三、DQL 数据查询语言高阶多函数关联分组3.1 基础查询字段过滤枚举翻译3.2 分组聚合查询配合数值类型统计3.3 多表JOIN关联查询3.4 JSON字段解析查询3.5 去重分页四、TCL 事务控制语言金融/账户必备保障原子性五、DCL 权限控制语言运维/开发赋权六、补充高频约束避坑数据类型总结6.1 核心约束避坑6.2 全数据类型速记数据库开发工程师进阶版MySQL全分类SQL详解超多数据类型全约束实战落地前置说明全程基于InnoDB、utf8mb4、MySQL 5.7/8.0 兼容覆盖整型/浮点/高精度小数/字符串/日期时间/二进制/枚举/JSON全常用数据类型覆盖主键、自增、非空、唯一、默认值、外键、检查、注释、索引、联合索引全约束按标准五大类 高阶开发SQL拆分每段带业务场景详解。一、DDL 数据定义语言建库、建表、改结构、约束核心1.1 建库规范SQL-- 创建数据库指定字符集排序规则杜绝乱码CREATEDATABASEIFNOTEXISTSdb_tradeDEFAULTCHARACTERSETutf8mb4DEFAULTCOLLATEutf8mb4_unicode_ci;-- 删除库生产严禁随意执行DROPDATABASEIFEXISTSdb_temp;1.2 企业级建表超多数据类型 全约束演示业务场景电商用户详情表 账户资产表融合示范CREATETABLEIFNOTEXISTSt_user_account(-- 1.整型系列tinyint/smallint/int/bigint精准控存储idBIGINTUNSIGNEDNOTNULLAUTO_INCREMENTCOMMENT主键ID无符号大整型,ageTINYINTUNSIGNEDDEFAULT0COMMENT年龄无符号 tinyint 省空间,user_levelSMALLINTNOTNULLDEFAULT1COMMENT会员等级小整型,-- 2.高精度金额必须 decimal禁止 float/doubleaccount_balanceDECIMAL(16,4)NOTNULLDEFAULT0.0000COMMENT账户余额16位总长4位小数金融精准,freeze_amountDECIMAL(16,4)NOTNULLDEFAULT0.0000COMMENT冻结金额,-- 3.字符串系列char/varchar/textuser_codeCHAR(20)NOTNULLCOMMENT用户编码定长char查询更快,phoneVARCHAR(11)NOTNULLCOMMENT手机号变长常用,emailVARCHAR(128)DEFAULTCOMMENT邮箱,user_descTEXTCOMMENT用户长备注大文本不建索引,-- 4.日期时间全品类register_timeDATETIMENOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT注册时间,last_login_dateDATECOMMENT最后登录日期(仅年月日),login_timeTIMECOMMENT常用登录时段(时分秒),update_timestampTIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT自动更新时间戳,-- 5.枚举enum固定业务状态genderENUM(male,female,unknown)DEFAULTunknownCOMMENT性别枚举只能选固定值,-- 6.集合set多选标签user_tagSET(vip,new_user,discount)COMMENT用户标签可多选,-- 7.JSON类型存储灵活扩展字段前端配置、扩展属性extend_info JSONCOMMENT扩展JSON字段头像、第三方绑定信息,-- 8.二进制存小文件/加密密文encrypt_dataBLOBCOMMENT加密二进制密文数据,-- 核心约束区 PRIMARYKEY(id)COMMENT单字段主键,UNIQUEKEYuk_user_code(user_code)COMMENT唯一约束用户编码全局不重复,UNIQUEKEYuk_phone(phone)COMMENT手机号唯一,-- 普通索引/联合索引INDEXidx_register_time(register_time)COMMENT注册时间单列索引,INDEXidx_level_phone(user_level,phone)COMMENT联合索引等级手机号,-- 外键约束InnoDB专用保障关联完整性-- 注实际高并发业务常逻辑外键低并发可用物理外键FOREIGNKEY(user_level)REFERENCESt_level_config(level_id),-- 检查约束MySQL8.0原生支持5.7语法兼容不强制CHECK(age0ANDage150),CHECK(account_balance0))ENGINEInnoDBDEFAULTCHARSETutf8mb4COLLATEutf8mb4_unicode_ciAUTO_INCREMENT10000COMMENT用户账户主表-含全数据类型全约束;关键知识点拆解① 数据类型选型硬核规范整型TINYINT(1字节)/SMALLINT(2)/INT(4)/BIGINT(8)加UNSIGNED扩大正数范围金额强制DECIMAL(m,n)杜绝 Float/Double 精度丢失字符串固定长度用CHAR变长用VARCHAR超长备注用TEXT时间DATETIME存业务时间TIMESTAMP自动更新DATE/TIME拆分场景枚举ENUM限定固定状态防脏数据扩展字段优先JSON无需频繁改表② 约束全覆盖NOT NULL非空业务核心字段必须加AUTO_INCREMENT自增主键仅整型UNIQUE唯一约束防重复手机号/编码DEFAULT默认值杜绝NULL乱存FOREIGN KEY物理关联约束CHECK字段值合法性校验8.0强生效PRIMARY KEY主键非空唯一1.3 常用改表DDL加字段/改类型/加约束/加索引-- 1.加字段指定类型约束注释ALTERTABLEt_user_accountADDCOLUMNreal_nameVARCHAR(64)NOTNULLDEFAULTCOMMENT真实姓名AFTERphone;-- 2.修改字段数据类型约束ALTERTABLEt_user_accountMODIFYCOLUMNemailVARCHAR(192)DEFAULTNOTNULLCOMMENT扩容邮箱长度加固非空;-- 3.添加唯一约束ALTERTABLEt_user_accountADDUNIQUEKEYuk_email(email);-- 4.添加普通索引ALTERTABLEt_user_accountADDINDEXidx_gender(gender);-- 5.删除索引ALTERTABLEt_user_accountDROPINDEXidx_gender;-- 6.删除字段高危生产必须评审ALTERTABLEt_user_accountDROPCOLUMNencrypt_data;1.4 视图/索引进阶DDL-- 创建视图封装复杂关联查询CREATEVIEWv_user_balanceASSELECTid,phone,account_balance,freeze_amount,register_timeFROMt_user_accountWHEREuser_level1;-- 删除视图DROPVIEWIFEXISTSv_user_balance;二、DML 数据操作语言增删改带严格字段适配2.1 INSERT 插入适配全字段类型-- 常规插入覆盖字符串/数字/日期/枚举/JSONINSERTINTOt_user_account(age,user_level,account_balance,user_code,phone,email,gender,extend_info,register_time)VALUES(28,5,999.8800,USER2026001,13800138000,testqq.com,male,{avatar:https://xxx.png,wx_bind:true},2026-04-06 10:00:00);-- 批量插入高性能数据库开发高频INSERTINTOt_user_account(user_code,phone,user_level)VALUES(USER2026002,13900139000,3),(USER2026003,13700137000,2);2.2 UPDATE 更新强制带WHERE防全表更新-- 更新金额状态自动触发update_timeUPDATEt_user_accountSETaccount_balanceaccount_balance50.0000,user_level6WHEREuser_codeUSER2026001;-- JSON字段局部更新MySQL5.7支持UPDATEt_user_accountSETextend_infoJSON_SET(extend_info,$.avatar,https://new.png)WHEREid10000;2.3 DELETE 删除逻辑删除优先物理删除谨慎-- 物理删除仅归档/清理冷数据用DELETEFROMt_user_accountWHEREregister_time2020-01-01;-- 企业规范优先逻辑删除加del_flag字段ALTERTABLEt_user_accountADDCOLUMNdel_flagTINYINTNOTNULLDEFAULT0COMMENT0正常1删除;-- 逻辑删除更新UPDATEt_user_accountSETdel_flag1WHEREuser_codeUSER2026003;三、DQL 数据查询语言高阶多函数关联分组3.1 基础查询字段过滤枚举翻译SELECTid,phone,user_level,-- case when翻译枚举/数字状态CASEgenderWHENmaleTHEN男WHENfemaleTHEN女ELSE未知ENDASgender_name,account_balance,DATE(register_time)ASreg_date--日期函数截取FROMt_user_accountWHEREdel_flag0--屏蔽逻辑删除数据ANDuser_level3ANDaccount_balance100;3.2 分组聚合查询配合数值类型统计-- 按会员等级统计人数、总余额、平均余额SELECTuser_level,COUNT(*)ASuser_count,SUM(account_balance)AStotal_balance,AVG(account_balance)ASavg_balance,MAX(account_balance)ASmax_balanceFROMt_user_accountWHEREdel_flag0GROUPBYuser_levelHAVINGSUM(account_balance)1000;--分组后过滤3.3 多表JOIN关联查询-- 关联用户表订单表LEFT JOIN保留所有用户SELECTu.id,u.phone,o.order_no,o.order_amountFROMt_user_account uLEFTJOINt_order oONu.ido.user_idWHEREu.del_flag0ORDERBYu.idDESC;3.4 JSON字段解析查询-- 提取JSON里的头像地址SELECTid,phone,JSON_UNQUOTE(JSON_EXTRACT(extend_info,$.avatar))ASavatar_urlFROMt_user_accountWHEREextend_info-$.wx_bindtrue;3.5 去重分页-- 去重查询所有会员等级SELECTDISTINCTuser_levelFROMt_user_accountWHEREdel_flag0;-- 分页查询第1页10条SELECT*FROMt_user_accountWHEREdel_flag0ORDERBYidDESCLIMIT0,10;四、TCL 事务控制语言金融/账户必备保障原子性-- 转账业务扣A余额加B余额要么全成要么全回滚STARTTRANSACTION;-- 扣减付款方UPDATEt_user_accountSETaccount_balanceaccount_balance-200.0000WHEREuser_codeUSER2026001ANDaccount_balance200;-- 增加收款方UPDATEt_user_accountSETaccount_balanceaccount_balance200.0000WHEREuser_codeUSER2026002;-- 无异常提交COMMIT;-- 出现异常执行ROLLBACK;五、DCL 权限控制语言运维/开发赋权-- 创建专属数据库开发账号CREATEUSERdb_dev_ops%IDENTIFIEDBYDevMySql2026!;-- 赋权查询增改禁止删表删库GRANTSELECT,INSERT,UPDATEONdb_trade.*TOdb_dev_ops%;-- 回收敏感权限REVOKEDELETE,DROP,ALTERONdb_trade.*FROMdb_dev_ops%;-- 刷新权限生效FLUSHPRIVILEGES;六、补充高频约束避坑数据类型总结6.1 核心约束避坑核心业务字段必加NOT NULL DEFAULT禁止大量NULL手机号/编码必加UNIQUE唯一约束防重复脏数据金额只用DECIMAL绝不浮点状态固定值优先ENUM减少业务乱录入大文本用TEXT不建索引筛选字段必建合适索引/联合索引生产优先逻辑删除少用物理DELETE。6.2 全数据类型速记整型TINYINT/SMALLINT/INT/BIGINT UNSIGNED金额DECIMAL(精度,小数位)字符CHAR(定长) / VARCHAR(变长) / TEXT(大文本)时间DATETIME / DATE / TIME / TIMESTAMP枚举集合ENUM / SET扩展JSON二进制BLOB