数据库教程FGMT35‑MySQL数据类型与SQL增删改查实战前言风哥教程本文面向MySQL数据库运维与开发技术人员围绕数据类型选型、数据库对象管理、DML数据操作、DQL查询语法、事务控制开展完整技术阐述。在企业生产环境中数据库的性能隐患、业务逻辑异常、数据损坏很多根源并非复杂架构故障而是字段数据类型选择不合理、SQL语句书写不规范、事务使用不当造成。风哥教程本文实验环境规划两套完全独立的MySQL实例环境两套环境业务互不关联不属于同一台物理主机。第一套实验主机名称为fgedu‑net‑cn1第二套实验主机名称为fgedu‑net‑cn2硬件规格统一为64G内存、8CPU数据库实例名统一为fgedudb业务操作用户为fgedu软件根目录统一为/fgedudb。两套环境可以分别用来完成对比测试一套执行基础语法验证另一套用于复现故障场景避免测试数据互相干扰。风哥教程本文内容分为理论知识与实操演练两大板块理论部分讲解MySQL各类数据类型、字符集、存储引擎、事务隔离级别底层原理实操部分包含大量可直接执行的SQL命令、操作系统层面操作步骤所有命令适配/fgedudb路径、fgedudb实例、fgedu业务用户。风哥针对本文总结部分放在文档末尾用于梳理关键风险点与生产落地注意事项。风哥教程本文覆盖主要知识模块SQL语言基础与数据类型数据库命名规范、字符集与数据库设计规范数值、日期、字符、JSON数据类型存储引擎管理数据库与表对象DDL操作INSERT、UPDATE、DELETE、REPLACE DML操作DELETE/TRUNCATE/DROP三者区别事务基础与隔离级别SELECT查询、多表各类JOIN连接子查询语法。网上搜索风哥教程可以学习全套数据库教程一、MySQL数据类型与SQL语言基础理论知识1.1 SQL语言基础理论SQL结构化查询语言分为DDL数据定义语言、DML数据操纵语言、DQL数据查询语言、TCL事务控制语言、DCL数据控制语言。DDL负责库、表、索引等对象结构定义DML负责表内部数据的插入、修改、删除DQL负责数据查询检索TCL完成事务提交、回滚的事务生命周期管控DCL用于权限账号管理。DDL语句执行会触发元数据锁MDL在生产大并发业务中长时间运行的DDL会阻塞业务DML语句这是MySQL运维中非常经典的风险点。DML语句仅操作行数据不会直接修改表结构InnoDB存储引擎下DML操作会被事务包裹可以执行回滚操作DDL属于非事务语句一旦执行成功无法通过事务回滚撤销生产执行DDL操作需要充分评估业务业务流量窗口。1.2 数据库命名规范理论数据库、数据表、字段对象命名遵循生产通用规范对象名称尽量使用英文语义词汇禁止使用MySQL保留关键字作为库名、表名、字段名库、表名称区分大小写受操作系统底层文件系统影响Linux环境数据库目录对应操作系统文件夹因此库名表名大小写敏感Windows环境文件系统不区分大小写。为保证跨平台兼容性统一全部使用小写命名下划线_作为分隔符不使用中文对象名。对象名称长度控制在64字符以内禁止特殊符号。1.3 字符集与排序规则理论字符集决定数据字节存储编码排序规则collation定义字符串比较、排序逻辑。MySQL历史版本utf8字符集只支持最多3字节无法完整存储emoji表情utf8mb4完整支持4字节unicode字符是生产环境标准推荐字符集。排序规则后缀_ci代表大小写不敏感_cs大小写敏感_bin二进制字节比较。字符集分为实例级别、数据库级别、表级别、字段级别层级优先级字段 表 数据库 实例。如果建库没有显式指定字符集则继承实例全局字符集建表没有指定字符集继承数据库字符集。生产环境建议实例my.cnf配置文件中直接设置全局character‑set‑serverutf8mb4从源头避免中文乱码问题。上51CTO搜索风哥可以学习全套数据库教程1.4 MySQL各类数据类型底层原理1.4.1 数值类型数值类型分为整数类型、定点小数、浮点类型。整数包含TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT不同类型占用存储空间不一样存储范围固定可以增加UNSIGNED修饰符去掉负数区间扩大正数存储上限。定点数DECIMAL用于金额、账务等需要精确计算的业务内部使用二进制字符串存储不会产生浮点精度丢失FLOAT、DOUBLE二进制浮点数存在精度丢失财务业务严禁使用。类型占用字节带符号范围TINYINT1-128 ~127SMALLINT2-32768~32767MEDIUMINT3-8388608~8388607INT4-2147483648~2147483647BIGINT8-9223372036854775808 ~92233720368547758071.4.2 日期时间类型DATE仅存储年月日TIME存储时分秒DATETIME存储完整年月日时分秒时间范围大不受2038时间溢出限制TIMESTAMP底层存储为时间戳受时区影响存在2038上限风险。生产业务优先选用DATETIME记录业务时间。1.4.3 字符字符串类型CHAR为定长字符串定义多少字符物理存储就占用对应空间不足长度会在尾部填充空格读取的时候自动去除尾部空格适合长度固定字段例如身份证号码、手机号。VARCHAR可变长度字符串实际占用存储空间跟随真实数据大小额外增加1‑2字节记录字符串实际长度适合长短变化大的业务文本。TEXT大文本类型存储超长文本TEXT字段不会放在行数据主存储区使用溢出页存放大量使用TEXT会降低表扫描性能大文本业务尽量拆分子表。风哥 itpux‑com1.4.4 JSON数据类型MySQL5.7开始原生支持JSON字段专门存储JSON结构化文档不再使用TEXT/VARCHAR直接存放JSON字符串。JSON字段拥有专用校验机制写入非法JSON文档直接报错提供大量内置JSON函数用于解析、修改、查询JSON内部key8.0版本支持JSON部分原地更新不需要重写完整文档。JSON适合半结构化业务数据但不建议把全部业务都塞进JSON核心过滤条件字段仍然需要设计为独立普通字段JSON适合扩展属性。1.5 MySQL存储引擎理论存储引擎是MySQL底层数据读写组件一张表只能选择一种存储引擎。InnoDB是MySQL8.x版本默认存储引擎完整支持事务ACID、MVCC多版本并发控制、行级锁、外键约束、崩溃恢复是在线业务标准选择。MyISAM不支持事务使用表级锁崩溃后无法保证数据安全现在线上业务已经很少使用。存储引擎可以数据库级别设置默认引擎也可以单张表单独指定ENGINE参数。修改表存储引擎会触发表重建大数据量表执行ALTER修改存储引擎会产生大量IO业务高峰严禁操作。1.6 事务原理与隔离级别理论事务ACID原子性Atomicity、一致性Consistency、隔离性Isolation、持久性Durability。InnoDB依靠undo回滚日志实现原子性redo重做日志保证持久性MVCC多版本实现隔离性。MySQL InnoDB默认隔离级别是REPEATABLE‑READ可重复读。四个隔离级别分别是READ UNCOMMITTED读未提交、READ COMMITTED读已提交、REPEATABLE READ可重复读、SERIALIZABLE串行化。隔离级别越低并发性能越好会出现越多脏读、不可重复读、幻读现象隔离级别越高并发性能下降。风哥教程 113257174二、实验环境基础准备两套独立环境 fgedu‑net‑cn1、fgedu‑net‑cn2两套主机硬件均为64G内存8CPUMySQL软件部署路径/fgedudb实例名fgedudb。下面给出my.cnf核心配置两套主机配置参数保持一致。[mysqld] basedir/fgedudb/mysql-base datadir/fgedudb/mysql-data socket/fgedudb/mysql.sock pid‑file/fgedudb/fgedudb.pid port3306 server‑id1 usermysql character‑set‑serverutf8mb4 collation‑serverutf8mb4_0900_ai_ci #64G内存8CPU参数配置 innodb_buffer_pool_size32G innodb_buffer_pool_instances16 innodb_log_file_size4G innodb_log_files_in_group2 innodb_flush_log_at_trx_commit1 sync_binlog1 max_connections800 max_connect_errors10000 max_allowed_packet64M tmp_table_size2G max_heap_table_size2G log_error/fgedudb/log/fgedudb‑error.log slow_query_logON slow_query_log_file/fgedudb/log/fgedudb‑slow.log2.1 fgedu业务用户创建两套主机分别执行登录mysql客户端分别在fgedu‑net‑cn1与fgedu‑net‑cn2执行两套环境账号互相独立CREATEUSERfgedu%IDENTIFIEDFgEdu2026;GRANTALLPRIVILEGESON*.*TOfgedu%;FLUSHPRIVILEGES;风哥数据库教程 itpux‑com登录数据库示例命令主机分别替换主机名#主机 fgedu‑net‑cn1/fgedudb/mysql-base/bin/mysql-ufgedu-p-S/fgedudb/mysql.sock#主机 fgedu‑net‑cn2/fgedudb/mysql-base/bin/mysql-ufgedu-p-S/fgedudb/mysql.sock三、数据库与数据表DDL实操演练本章节全部操作优先在主机fgedu‑net‑cn1执行fgedu‑net‑cn2主机可以重复同样操作用于做对比测试。3.1 数据库的创建、查看、切换、删除创建业务数据库fgedudb显式指定字符集与排序规则CREATEDATABASEIFNOTEXISTSfgedudbDEFAULTCHARACTERSETutf8mb4DEFAULTCOLLATEutf8mb4_0900_ai_ci;查看实例全部数据库SHOWDATABASES;切换当前会话使用的数据库USEfgedudb;查看当前所在数据库SELECTDATABASE();查看数据库建库完整语句SHOWCREATEDATABASEfgedudb;安全删除数据库IF EXISTS避免库不存在时报错谨慎执行会删除库下面全部表与数据DROPDATABASEIFEXISTSfgedudb;3.2 存储引擎查看与修改实操查看当前实例支持的全部存储引擎SHOWENGINES;查看全局默认存储引擎SHOWVARIABLESLIKEdefault_storage_engine;修改会话级别默认存储引擎当前会话生效SETSESSIONdefault_storage_engineInnoDB;创建测试业务表指定ENGINE存储引擎演示各类数据类型CREATETABLEfgedu_business(idBIGINTAUTO_INCREMENTPRIMARYKEYCOMMENT主键ID,ageTINYINTUNSIGNEDCOMMENT年龄无符号整数,salaryDECIMAL(12,2)COMMENT薪资定点高精度小数,birthDATECOMMENT出生日期,create_timeDATETIMECOMMENT记录创建时间,phoneCHAR(11)COMMENT手机号定长字符,usernameVARCHAR(64)COMMENT用户姓名变长字符,remarkTEXTCOMMENT备注大文本,ext_info JSONCOMMENT扩展JSON属性)ENGINEInnoDBDEFAULTCHARSETutf8mb4COMMENT风哥教程业务测试表;网上搜索风哥教程可以学习全套数据库教程查看数据库下面全部数据表SHOWTABLES;查看表结构字段详情DESCfgedu_business;查看建表完整DDL语句SHOWCREATETABLEfgedu_business;修改表的存储引擎生产大表禁止业务高峰运行ALTERTABLEfgedu_businessENGINEInnoDB;表重命名操作ALTERTABLEfgedu_businessRENAMETOfgedu_bak_business;ALTERTABLEfgedu_bak_businessRENAMETOfgedu_business;截断表TRUNCATE清空全部行数据保留表结构DDL操作不能回滚TRUNCATETABLEfgedu_business;删除整张数据表元数据和数据全部清除DDL不可回滚DROPTABLEIFEXISTSfgedu_business;修改字段定义修改username字段长度ALTERTABLEfgedu_businessMODIFYCOLUMNusernameVARCHAR(128);新增字段ALTERTABLEfgedu_businessADDCOLUMNemailVARCHAR(128);删除字段ALTERTABLEfgedu_businessDROPCOLUMNemail;四、DML增删改replace实战操作4.1 INSERT插入数据插入单行完整字段数据INSERTINTOfgedu_business(age,salary,birth,create_time,phone,username,remark,ext_info)VALUES(28,15800.50,1998‑05‑12,NOW(),13800138000,zhangsan,普通业务用户,{level:normal,tag:[member]});插入多条记录一次values多组括号减少网络交互INSERTINTOfgedu_business(age,salary,birth,create_time,phone,username,remark,ext_info)VALUES(32,22000.00,1994‑03‑22,NOW(),13900139000,lisi,高级付费用户,{level:vip,tag:[vip,member]}),(24,9200.00,2000‑11‑05,NOW(),13700137000,wangwu,新注册用户,{level:new,tag:[new]});不写字段列表按表字段顺序赋值生产不推荐表结构变更语句直接报错INSERTINTOfgedu_businessVALUES(NULL,26,11000,1999‑07‑01,NOW(),13600136000,zhaoliu,测试用户,{level:test});4.2 UPDATE更新数据⚠️重要风险不带WHERE条件UPDATE会更新全表所有行生产环境执行UPDATE前建议先用SELECT校验WHERE条件返回的行数。带条件更新单行部分字段UPDATEfgedu_businessSETsalary16800.50,remark薪资调整后普通用户WHEREid1;多字段同时更新UPDATEfgedu_businessSETage33,salary23500WHEREusernamelisi;表达式运算更新薪资上浮500UPDATEfgedu_businessSETsalarysalary500WHEREid2;4.3 DELETE删除行数据DELETE删除满足where条件的行记录属于DMLInnoDB支持事务回滚会生成undo日志与binlog。删除指定id行DELETEFROMfgedu_businessWHEREid4;⚠️不带WHERE条件会删除全部表数据不会删除表结构。-- 危险语句禁止直接执行-- DELETE FROM fgedu_business;4.4 REPLACE语法实操REPLACE逻辑根据主键或者唯一索引如果记录已经存在先DELETE旧行再INSERT新行不存在则直接INSERT。依赖主键/唯一键没有唯一约束REPLACE等价INSERT。REPLACEINTOfgedu_business(id,age,salary,username)VALUES(1,29,17200,zhangsan_update);4.5 DELETE / TRUNCATE / DROP三者对比实操验证DELETEDML删除行保留表结构可以事务回滚会记录binlog数据量大删除速度慢释放空间不一定还给操作系统。TRUNCATEDDL清空全部行保留表结构无法回滚重置自增主键速度很快直接回收数据页。DROP TABLEDDL删除表定义全部数据释放全部磁盘空间不可回滚。实操验证步骤fgedu‑net‑cn2主机执行隔离测试环境USEfgedudb;CREATETABLEtest_trunc(idINTPRIMARYKEYAUTO_INCREMENT,nameVARCHAR(32));INSERTINTOtest_trunc(name)VALUES(a),(b),(c);BEGIN;DELETEFROMtest_truncWHEREid1;SELECT*FROMtest_trunc;ROLLBACK;SELECT*FROMtest_trunc;-- DELETE支持回滚数据恢复再测试TRUNCATETRUNCATE不受事务回滚保护BEGIN;TRUNCATETABLEtest_trunc;ROLLBACK;SELECT*FROMtest_trunc;执行DROP TABLEDROPTABLEtest_trunc;SHOWTABLES;五、MySQL事务控制实操演练InnoDB引擎支持完整事务MyISAM不支持事务。事务关键字BEGIN / START TRANSACTION开启事务COMMIT提交ROLLBACK回滚。USEfgedudb;BEGIN;UPDATEfgedu_businessSETsalarysalary‑1000WHEREid1;UPDATEfgedu_businessSETsalarysalary1000WHEREid2;-- 此时只在当前会话可见其他会话看不到修改结果SELECT*FROMfgedu_businessWHEREidIN(1,2);ROLLBACK;-- 回滚撤销全部修改SELECT*FROMfgedu_businessWHEREidIN(1,2);提交事务案例STARTTRANSACTION;INSERTINTOfgedu_business(age,username)VALUES(27,chenqi);COMMIT;SELECT*FROMfgedu_businessWHEREusernamechenqi;查看当前会话事务隔离级别SELECTtransaction_isolation;修改当前会话隔离级别为READ‑COMMITTEDSETSESSIONTRANSACTIONISOLATIONLEVELREADCOMMITTED;SELECTtransaction_isolation;六、DQL SELECT查询语言完整实操6.1 SELECT基础查询语法USEfgedudb;-- 查询全部列SELECT*FROMfgedu_business;-- 查询指定列SELECTid,username,salary,create_timeFROMfgedu_business;-- 列别名SELECTidAS用户ID,usernameAS用户姓名FROMfgedu_business;-- where条件过滤SELECT*FROMfgedu_businessWHEREage25ANDsalary10000;-- order by排序SELECTid,username,salaryFROMfgedu_businessORDERBYsalaryDESC;-- group by分组统计SELECTage,COUNT(id)ASuser_countFROMfgedu_businessGROUPBYage;-- limit分页SELECT*FROMfgedu_businessLIMIT0,2;6.2 准备多表用于JOIN连接演示创建部门表fgedu_dept用于多表关联查询CREATETABLEfgedu_dept(dept_idINTPRIMARYKEYAUTO_INCREMENT,dept_nameVARCHAR(64)NOTNULLCOMMENT部门名称)ENGINEInnoDBDEFAULTCHARSETutf8mb4;INSERTINTOfgedu_dept(dept_name)VALUES(研发部),(市场部),(运维部);ALTERTABLEfgedu_businessADDCOLUMNdept_idINT;UPDATEfgedu_businessSETdept_id1WHEREidIN(1,2);UPDATEfgedu_businessSETdept_id2WHEREid3;6.2.1 内连接 INNER JOIN只返回两边匹配上的数据行SELECTb.id,b.username,d.dept_nameFROMfgedu_business bINNERJOINfgedu_dept dONb.dept_idd.dept_id;6.2.2 左外连接 LEFT JOIN左边表全部输出右边没有匹配字段填充NULLSELECTb.id,b.username,d.dept_nameFROMfgedu_business bLEFTJOINfgedu_dept dONb.dept_idd.dept_id;6.2.3 右外连接 RIGHT JOIN右边表全部输出左边无匹配填充NULLSELECTb.id,b.username,d.dept_nameFROMfgedu_business bRIGHTJOINfgedu_dept dONb.dept_idd.dept_id;6.2.4 交叉连接 CROSS JOIN 笛卡尔积不写on条件返回两张表行数乘积业务尽量避免SELECT*FROMfgedu_businessCROSSJOINfgedu_dept;6.2.5 自连接SELF JOIN一张表别名两份自己关联自己适合组织层级、上下级场景。CREATETABLEfgedu_emp(emp_idINTPRIMARYKEYAUTO_INCREMENT,emp_nameVARCHAR(32),mgr_idINTNULL);INSERTINTOfgedu_emp(emp_name,mgr_id)VALUES(boss,NULL),(emp_a,1),(emp_b,1);SELECTe1.emp_nameASemp_name,e2.emp_nameASmanager_nameFROMfgedu_emp e1LEFTJOINfgedu_emp e2ONe1.mgr_ide2.emp_id;6.3 子查询实操简单WHERE标量子查询SELECT*FROMfgedu_businessWHEREdept_id(SELECTdept_idFROMfgedu_deptWHEREdept_name研发部);多行IN子查询SELECT*FROMfgedu_businessWHEREdept_idIN(SELECTdept_idFROMfgedu_deptWHEREdept_id2);EXISTS半连接子查询SELECT*FROMfgedu_business bWHEREEXISTS(SELECT1FROMfgedu_dept dWHEREd.dept_idb.dept_idANDd.dept_name研发部);NOT EXISTS反连接SELECT*FROMfgedu_business bWHERENOTEXISTS(SELECT1FROMfgedu_dept dWHEREd.dept_idb.dept_id);风哥针对本文总结风哥教程本文完整覆盖MySQL数据类型选型、DDL对象管理、DML增删改REPLACE、事务控制、多表JOIN、子查询全部基础知识点两套独立实验主机fgedu‑net‑cn1、fgedu‑net‑cn2硬件规格统一64G内存8CPU配置文件参数按照生产数据库实例调优。生产环境实践的关键风险要点总结如下字符集统一使用utf8mb4禁止旧版utf8防止4字节字符存储异常库表字段命名全部小写规避操作系统大小写兼容问题。数据类型选择遵循够用原则数值不要全部无脑选择BIGINT金额账务业务必须使用DECIMAL拒绝FLOAT/DOUBLE固定长度字段优先CHAR变长业务选择VARCHAR大文本尽量避免频繁使用TEXTJSON字段适合扩展属性核心过滤条件拆为普通字段。InnoDB为业务标准存储引擎DDL属于非事务语句不可回滚业务高峰期禁止执行ALTER、TRUNCATE、DROP执行UPDATE、DELETE操作前先用SELECT验证WHERE条件杜绝不带WHERE条件的DML。DELETE可以回滚TRUNCATE、DROP无法事务回滚高危操作务必确认环境测试环境与生产环境严格隔离。事务优先掌握BEGIN、COMMIT、ROLLBACK理解四个隔离级别差异业务根据并发与数据一致性选择合适隔离级别。JOIN多表查询尽量写显式INNER JOIN / LEFT JOIN语法不使用隐式逗号写法尽量避免笛卡尔积EXISTS半连接、NOT EXISTS反连接适合大数据量过滤场景。所有测试操作优先在独立测试环境完成不要直接在生产数据库执行陌生SQL命令。