数据库教程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‑server=utf8mb4`，从源头避免中文乱码问题。

> 上51CTO搜索风哥可以学习全套数据库教程

### 1.4 MySQL各类数据类型底层原理
#### 1.4.1 数值类型
数值类型分为整数类型、定点小数、浮点类型。整数包含TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，不同类型占用存储空间不一样，存储范围固定，可以增加`UNSIGNED`修饰符去掉负数区间，扩大正数存储上限。定点数DECIMAL用于金额、账务等需要精确计算的业务，内部使用二进制字符串存储，不会产生浮点精度丢失；FLOAT、DOUBLE二进制浮点数，存在精度丢失，财务业务严禁使用。

|类型|占用字节|带符号范围|
| —- | —- | —- |
|TINYINT|1|-128 ~127|
|SMALLINT|2|-32768~32767|
|MEDIUMINT|3|-8388608~8388607|
|INT|4|-2147483648~2147483647|
|BIGINT|8|-9223372036854775808 ~9223372036854775807|

#### 1.4.2 日期时间类型
DATE仅存储年月日；TIME存储时分秒；DATETIME存储完整年月日时分秒，时间范围大，不受2038时间溢出限制；TIMESTAMP底层存储为时间戳，受时区影响，存在2038上限风险。生产业务优先选用DATETIME记录业务时间。

#### 1.4.3 字符字符串类型
CHAR为定长字符串，定义多少字符，物理存储就占用对应空间，不足长度会在尾部填充空格，读取的时候自动去除尾部空格，适合长度固定字段，例如身份证号码、手机号。VARCHAR可变长度字符串，实际占用存储空间跟随真实数据大小，额外增加1‑2字节记录字符串实际长度，适合长短变化大的业务文本。TEXT大文本类型，存储超长文本，TEXT字段不会放在行数据主存储区，使用溢出页存放，大量使用TEXT会降低表扫描性能，大文本业务尽量拆分子表。

> 风哥 itpux‑com

#### 1.4.4 JSON数据类型
MySQL5.7开始原生支持JSON字段，专门存储JSON结构化文档，不再使用TEXT/VARCHAR直接存放JSON字符串。JSON字段拥有专用校验机制，写入非法JSON文档直接报错；提供大量内置JSON函数用于解析、修改、查询JSON内部key；8.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内存，8CPU，MySQL软件部署路径`/fgedudb`，实例名`fgedudb`。下面给出my.cnf核心配置，两套主机配置参数保持一致。
“`ini
[mysqld]
basedir=/fgedudb/mysql-base
datadir=/fgedudb/mysql-data
socket=/fgedudb/mysql.sock
pid‑file=/fgedudb/fgedudb.pid
port=3306
server‑id=1
user=mysql
character‑set‑server=utf8mb4
collation‑server=utf8mb4_0900_ai_ci
#64G内存8CPU参数配置
innodb_buffer_pool_size=32G
innodb_buffer_pool_instances=16
innodb_log_file_size=4G
innodb_log_files_in_group=2
innodb_flush_log_at_trx_commit=1
sync_binlog=1
max_connections=800
max_connect_errors=10000
max_allowed_packet=64M
tmp_table_size=2G
max_heap_table_size=2G
log_error=/fgedudb/log/fgedudb‑error.log
slow_query_log=ON
slow_query_log_file=/fgedudb/log/fgedudb‑slow.log
“`
### 2.1 fgedu业务用户创建，两套主机分别执行
登录mysql客户端，分别在fgedu‑net‑cn1与fgedu‑net‑cn2执行，两套环境账号互相独立：
“`sql
CREATE USER ‘fgedu’@’%’ IDENTIFIED ‘FgEdu@2026’;
GRANT ALL PRIVILEGES ON *.* TO ‘fgedu’@’%’;
FLUSH PRIVILEGES;
“`
> 风哥数据库教程 itpux‑com

登录数据库示例命令，主机分别替换主机名：
“`bash
#主机 fgedu‑net‑cn1
/fgedudb/mysql-base/bin/mysql -u fgedu -p -S /fgedudb/mysql.sock
#主机 fgedu‑net‑cn2
/fgedudb/mysql-base/bin/mysql -u fgedu -p -S /fgedudb/mysql.sock
“`

## 三、数据库与数据表DDL实操演练
> 本章节全部操作优先在主机**fgedu‑net‑cn1**执行；fgedu‑net‑cn2主机可以重复同样操作，用于做对比测试。
### 3.1 数据库的创建、查看、切换、删除
创建业务数据库`fgedudb`，显式指定字符集与排序规则：
“`sql
CREATE DATABASE IF NOT EXISTS fgedudb
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_0900_ai_ci;
“`
查看实例全部数据库：
“`sql
SHOW DATABASES;
“`
切换当前会话使用的数据库：
“`sql
USE fgedudb;
“`
查看当前所在数据库：
“`sql
SELECT DATABASE();
“`
查看数据库建库完整语句：
“`sql
SHOW CREATE DATABASE fgedudb;
“`
安全删除数据库（IF EXISTS避免库不存在时报错），谨慎执行，会删除库下面全部表与数据：
“`sql
DROP DATABASE IF EXISTS fgedudb;
“`

### 3.2 存储引擎查看与修改实操
查看当前实例支持的全部存储引擎：
“`sql
SHOW ENGINES;
“`
查看全局默认存储引擎：
“`sql
SHOW VARIABLES LIKE ‘default_storage_engine’;
“`
修改会话级别默认存储引擎，当前会话生效：
“`sql
SET SESSION default_storage_engine=InnoDB;
“`
创建测试业务表，指定ENGINE存储引擎，演示各类数据类型：
“`sql
CREATE TABLE fgedu_business(
id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT ‘主键ID’,
age TINYINT UNSIGNED COMMENT ‘年龄，无符号整数’,
salary DECIMAL(12,2) COMMENT ‘薪资，定点高精度小数’,
birth DATE COMMENT ‘出生日期’,
create_time DATETIME COMMENT ‘记录创建时间’,
phone CHAR(11) COMMENT ‘手机号定长字符’,
username VARCHAR(64) COMMENT ‘用户姓名变长字符’,
remark TEXT COMMENT ‘备注大文本’,
ext_info JSON COMMENT ‘扩展JSON属性’
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=’风哥教程业务测试表’;
“`
> 网上搜索风哥教程可以学习全套数据库教程

查看数据库下面全部数据表：
“`sql
SHOW TABLES;
“`
查看表结构字段详情：
“`sql
DESC fgedu_business;
“`
查看建表完整DDL语句：
“`sql
SHOW CREATE TABLE fgedu_business;
“`
修改表的存储引擎，生产大表禁止业务高峰运行：
“`sql
ALTER TABLE fgedu_business ENGINE=InnoDB;
“`
表重命名操作：
“`sql
ALTER TABLE fgedu_business RENAME TO fgedu_bak_business;
ALTER TABLE fgedu_bak_business RENAME TO fgedu_business;
“`
截断表TRUNCATE，清空全部行数据，保留表结构，DDL操作，不能回滚：
“`sql
TRUNCATE TABLE fgedu_business;
“`
删除整张数据表，元数据和数据全部清除，DDL，不可回滚：
“`sql
DROP TABLE IF EXISTS fgedu_business;
“`
修改字段定义，修改username字段长度：
“`sql
ALTER TABLE fgedu_business MODIFY COLUMN username VARCHAR(128);
“`
新增字段：
“`sql
ALTER TABLE fgedu_business ADD COLUMN email VARCHAR(128);
“`
删除字段：
“`sql
ALTER TABLE fgedu_business DROP COLUMN email;
“`

## 四、DML增删改replace实战操作
### 4.1 INSERT插入数据
插入单行完整字段数据：
“`sql
INSERT INTO fgedu_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多组括号，减少网络交互：
“`sql
INSERT INTO fgedu_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”]}’);
“`
不写字段列表，按表字段顺序赋值，生产不推荐，表结构变更语句直接报错：
“`sql
INSERT INTO fgedu_business VALUES(NULL,26,11000,’1999‑07‑01′,NOW(),’13600136000′,’zhaoliu’,’测试用户’,'{“level”:”test”}’);
“`

### 4.2 UPDATE更新数据
⚠️重要风险：不带WHERE条件UPDATE会更新全表所有行，生产环境执行UPDATE前，建议先用SELECT校验WHERE条件返回的行数。
带条件更新单行部分字段：
“`sql
UPDATE fgedu_business SET salary=16800.50,remark=’薪资调整后普通用户’ WHERE id=1;
“`
多字段同时更新：
“`sql
UPDATE fgedu_business SET age=33,salary=23500 WHERE username=’lisi’;
“`
表达式运算更新，薪资上浮500：
“`sql
UPDATE fgedu_business SET salary=salary+500 WHERE id=2;
“`

### 4.3 DELETE删除行数据
DELETE删除满足where条件的行记录，属于DML，InnoDB支持事务回滚，会生成undo日志与binlog。
删除指定id行：
“`sql
DELETE FROM fgedu_business WHERE id=4;
“`
⚠️不带WHERE条件会删除全部表数据，不会删除表结构。
“`sql
— 危险语句，禁止直接执行
— DELETE FROM fgedu_business;
“`

### 4.4 REPLACE语法实操
REPLACE逻辑：根据主键或者唯一索引，如果记录已经存在，先DELETE旧行，再INSERT新行；不存在则直接INSERT。依赖主键/唯一键，没有唯一约束，REPLACE等价INSERT。
“`sql
REPLACE INTO fgedu_business(id,age,salary,username)
VALUES(1,29,17200,’zhangsan_update’);
“`

### 4.5 DELETE / TRUNCATE / DROP三者对比实操验证
1. DELETE：DML，删除行，保留表结构；可以事务回滚；会记录binlog；数据量大删除速度慢；释放空间不一定还给操作系统。
2. TRUNCATE：DDL，清空全部行，保留表结构；无法回滚；重置自增主键；速度很快；直接回收数据页。
3. DROP TABLE：DDL，删除表定义+全部数据，释放全部磁盘空间；不可回滚。

实操验证步骤（fgedu‑net‑cn2主机执行，隔离测试环境）
“`sql
USE fgedudb;
CREATE TABLE test_trunc(id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(32));
INSERT INTO test_trunc(name) VALUES(‘a’),(‘b’),(‘c’);
BEGIN;
DELETE FROM test_trunc WHERE id=1;
SELECT * FROM test_trunc;
ROLLBACK;
SELECT * FROM test_trunc;
— DELETE支持回滚，数据恢复
“`
再测试TRUNCATE，TRUNCATE不受事务回滚保护：
“`sql
BEGIN;
TRUNCATE TABLE test_trunc;
ROLLBACK;
SELECT * FROM test_trunc;
“`
执行DROP TABLE：
“`sql
DROP TABLE test_trunc;
SHOW TABLES;
“`

## 五、MySQL事务控制实操演练
InnoDB引擎支持完整事务，MyISAM不支持事务。事务关键字`BEGIN / START TRANSACTION`开启事务；`COMMIT`提交；`ROLLBACK`回滚。
“`sql
USE fgedudb;
BEGIN;
UPDATE fgedu_business SET salary=salary‑1000 WHERE id=1;
UPDATE fgedu_business SET salary=salary+1000 WHERE id=2;
— 此时只在当前会话可见，其他会话看不到修改结果
SELECT * FROM fgedu_business WHERE id IN (1,2);
ROLLBACK;
— 回滚撤销全部修改
SELECT * FROM fgedu_business WHERE id IN (1,2);
“`
提交事务案例：
“`sql
START TRANSACTION;
INSERT INTO fgedu_business(age,username) VALUES(27,’chenqi’);
COMMIT;
SELECT * FROM fgedu_business WHERE username=’chenqi’;
“`
查看当前会话事务隔离级别：
“`sql
SELECT @@transaction_isolation;
“`
修改当前会话隔离级别为READ‑COMMITTED：
“`sql
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT @@transaction_isolation;
“`

## 六、DQL SELECT查询语言完整实操
### 6.1 SELECT基础查询语法
“`sql
USE fgedudb;
— 查询全部列
SELECT * FROM fgedu_business;
— 查询指定列
SELECT id,username,salary,create_time FROM fgedu_business;
— 列别名
SELECT id AS 用户ID,username AS 用户姓名 FROM fgedu_business;
— where条件过滤
SELECT * FROM fgedu_business WHERE age >=25 AND salary>10000;
— order by排序
SELECT id,username,salary FROM fgedu_business ORDER BY salary DESC;
— group by分组统计
SELECT age,COUNT(id) AS user_count FROM fgedu_business GROUP BY age;
— limit分页
SELECT * FROM fgedu_business LIMIT 0,2;
“`

### 6.2 准备多表用于JOIN连接演示
创建部门表`fgedu_dept`，用于多表关联查询：
“`sql
CREATE TABLE fgedu_dept(
dept_id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(64) NOT NULL COMMENT ‘部门名称’
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO fgedu_dept(dept_name) VALUES(‘研发部’),(‘市场部’),(‘运维部’);

ALTER TABLE fgedu_business ADD COLUMN dept_id INT;
UPDATE fgedu_business SET dept_id=1 WHERE id IN(1,2);
UPDATE fgedu_business SET dept_id=2 WHERE id=3;
“`

#### 6.2.1 内连接 INNER JOIN
只返回两边匹配上的数据行
“`sql
SELECT b.id,b.username,d.dept_name
FROM fgedu_business b
INNER JOIN fgedu_dept d ON b.dept_id=d.dept_id;
“`

#### 6.2.2 左外连接 LEFT JOIN
左边表全部输出，右边没有匹配字段填充NULL
“`sql
SELECT b.id,b.username,d.dept_name
FROM fgedu_business b
LEFT JOIN fgedu_dept d ON b.dept_id=d.dept_id;
“`

#### 6.2.3 右外连接 RIGHT JOIN
右边表全部输出，左边无匹配填充NULL
“`sql
SELECT b.id,b.username,d.dept_name
FROM fgedu_business b
RIGHT JOIN fgedu_dept d ON b.dept_id=d.dept_id;
“`

#### 6.2.4 交叉连接 CROSS JOIN 笛卡尔积
不写on条件，返回两张表行数乘积，业务尽量避免
“`sql
SELECT * FROM fgedu_business CROSS JOIN fgedu_dept;
“`

#### 6.2.5 自连接SELF JOIN
一张表别名两份，自己关联自己，适合组织层级、上下级场景。
“`sql
CREATE TABLE fgedu_emp(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(32),
mgr_id INT NULL
);
INSERT INTO fgedu_emp(emp_name,mgr_id) VALUES(‘boss’,NULL),(’emp_a’,1),(’emp_b’,1);

SELECT e1.emp_name AS emp_name,e2.emp_name AS manager_name
FROM fgedu_emp e1
LEFT JOIN fgedu_emp e2 ON e1.mgr_id=e2.emp_id;
“`

### 6.3 子查询实操
简单WHERE标量子查询
“`sql
SELECT * FROM fgedu_business WHERE dept_id=(SELECT dept_id FROM fgedu_dept WHERE dept_name=’研发部’);
“`
多行IN子查询
“`sql
SELECT * FROM fgedu_business WHERE dept_id IN (SELECT dept_id FROM fgedu_dept WHERE dept_id<=2);
“`
EXISTS半连接子查询
“`sql
SELECT * FROM fgedu_business b
WHERE EXISTS (SELECT 1 FROM fgedu_dept d WHERE d.dept_id=b.dept_id AND d.dept_name=’研发部’);
“`
NOT EXISTS反连接
“`sql
SELECT * FROM fgedu_business b
WHERE NOT EXISTS (SELECT 1 FROM fgedu_dept d WHERE d.dept_id=b.dept_id);
“`

## 风哥针对本文总结
风哥教程本文完整覆盖MySQL数据类型选型、DDL对象管理、DML增删改REPLACE、事务控制、多表JOIN、子查询全部基础知识点，两套独立实验主机fgedu‑net‑cn1、fgedu‑net‑cn2，硬件规格统一64G内存8CPU，配置文件参数按照生产数据库实例调优。

生产环境实践的关键风险要点总结如下：
1. 字符集统一使用utf8mb4，禁止旧版utf8，防止4字节字符存储异常；库表字段命名全部小写，规避操作系统大小写兼容问题。
2. 数据类型选择遵循够用原则，数值不要全部无脑选择BIGINT；金额账务业务必须使用DECIMAL，拒绝FLOAT/DOUBLE；固定长度字段优先CHAR，变长业务选择VARCHAR；大文本尽量避免频繁使用TEXT；JSON字段适合扩展属性，核心过滤条件拆为普通字段。
3. InnoDB为业务标准存储引擎；DDL属于非事务语句，不可回滚，业务高峰期禁止执行ALTER、TRUNCATE、DROP；执行UPDATE、DELETE操作前，先用SELECT验证WHERE条件，杜绝不带WHERE条件的DML。
4. DELETE可以回滚，TRUNCATE、DROP无法事务回滚，高危操作务必确认环境，测试环境与生产环境严格隔离。
5. 事务优先掌握BEGIN、COMMIT、ROLLBACK，理解四个隔离级别差异，业务根据并发与数据一致性选择合适隔离级别。
6. JOIN多表查询尽量写显式INNER JOIN / LEFT JOIN语法，不使用隐式逗号写法；尽量避免笛卡尔积；EXISTS半连接、NOT EXISTS反连接适合大数据量过滤场景。
7. 所有测试操作优先在独立测试环境完成，不要直接在生产数据库执行陌生SQL命令。