【第二章】库、表、增删改查操作-171主机测评

【第二章】库、表、增删改查操作

库的操作（Database）
- 查看所有库
- 创建库（顺便把字符集写清楚）
- 使用库
- 查看建库语句（非常常用）
- 修改库（一般改字符集/排序规则）
- 删除库（慎重）
字符集与排序规则
- 查看 MySQL 支持哪些字符集
- 查看排序规则（Collation）
数据类型（建表之前先把这个想清楚）
- 数值类型：整数、浮点、定点怎么选
- - 1）整数类型（TINYINT/SMALLINT/INT/BIGINT）
  - 2）FLOAT/DOUBLE（浮点数） vs DECIMAL（定点数）
- 字符串类型：CHAR / VARCHAR / TEXT 的“性格”
- - 1）CHAR vs VARCHAR
  - 2）VARCHAR vs TEXT（什么时候上 TEXT）
- 二进制类型：BINARY/VARBINARY/BLOB
- ENUM / SET
- 日期类型：DATE / TIME / DATETIME / TIMESTAMP / YEAR
表的操作（Table）
- 查看当前库的所有表
- 创建表（带注释、字符集、引擎）
- 表在磁盘上的文件（了解即可，但挺有用）
- 查看表结构
- 再多两个常用查看（排障很好用）
- 修改表（ALTER TABLE 常用套路）
- - 1）加字段
  - 2）改字段类型/属性
  - 3）删字段
  - 4）重命名表
- 删除表 vs 清空表（这俩经常被混用）
增删改查（CRUD）
- 0）准备一张演示表
- 1）Create：INSERT 新增
- - 全列插入（不推荐写在业务代码里）
  - 指定列插入（更常用）
  - 一次插多行（效率更好）
  - 插入时“顺便”查出来（INSERT … SELECT）
- 2）Retrieve：SELECT 查询
- - 查所有列（调试可用，业务慎用）
  - 只查需要的列（业务常用）
  - 使用AS指定别名
  - DISTINCT：最直接的去重
  - - 单列去重
- 多列去重（“组合去重”）
- DISTINCT + ORDER BY
- DISTINCT 遇到 NULL
- - WHERE：条件过滤（核心中的核心）
  - ORDER BY + LIMIT：排序与分页
  - 聚合函数：COUNT / SUM / AVG / MAX / MIN
  - GROUP BY + HAVING：分组统计与分组过滤
- 3）Update：UPDATE 修改
- - 基本更新（一定要带 WHERE）
  - 同时改多个字段
- 4）Delete：DELETE 删除
- - 按条件删除（一定要带 WHERE）
  - 批量删除（建议带 LIMIT 做保险）
本章小结

后面章节我们默认环境是 MySQL 8.x，存储引擎以 InnoDB 为主，字符集建议统一用 utf8mb4。

库的操作（Database）

库（database/schema）你可以理解成一个“逻辑容器”，把相关的表收在一起。一个项目一般一个库，或者按业务拆多个库。

查看所有库

SHOW DATABASES;

DATABASES 是复数，大小写不敏感（SQL 关键字在 MySQL 里通常不区分大小写）。

创建库（顺便把字符集写清楚）

CREATE DATABASE IF NOT EXISTS demo_db
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_0900_ai_ci;

我这里强烈建议你养成两个习惯：

IF NOT EXISTS：防止重复执行脚本直接报错
明确 CHARACTER SET / COLLATE：明确编码

使用库

USE demo_db;

这条命令其实就是告诉 MySQL：后面没写库名的表，都默认在 demo_db 里找。

查看建库语句（非常常用）

SHOW CREATE DATABASE demo_db;

当你在别人机器上发现“同样的表，怎么排序/大小写规则不一样”时，这条语句能直接查看底层配置。

修改库（一般改字符集/排序规则）

ALTER DATABASE demo_db
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_0900_ai_ci;

提醒一句：改库的默认字符集，不等于把库里已有表/字段的字符集全部改了。已经建好的表字段，还是得单独处理。

删除库（慎重）

DROP DATABASE demo_db;

这条命令的含义很直白：库没了，里面的表也都没了。一般我建议你在真正 DROP 前先做两件事：

先 SHOW TABLES; 确认你在对的库里
做好备份（至少把关键表导出来）

字符集与排序规则

很多人第一次被字符集教育，基本都是在“存中文乱码/表情插不进去/排序怎么怪怪的”。

查看 MySQL 支持哪些字符集

SHOW CHARSET;

经验点：

MySQL 8.x 默认字符集一般是 utf8mb4
MySQL 5.7 默认字符集经常还是 latin1（这就是乱码的常见起点）

查看排序规则（Collation）

SHOW COLLATION;

排序规则你可以先粗暴理解成：字符串怎么比较、怎么排序。

比如 utf8mb4_0900_ai_ci 这串后缀拆开看：

0900：基于 Unicode Collation Algorithm 9.0.0
ai：accent-insensitive（口音/重音不敏感）
ci：case-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）

这里没必要过多强调，但需要知道一个方向：位数越大、范围越大、占用空间越大。

CREATE TABLE if not exists t_int_range (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
a TINYINT,
b TINYINT UNSIGNED,
c INT,
d BIGINT UNSIGNED
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

顺便给你一个很实用的技巧：当你不确定某个字段未来会不会增长得很夸张时，直接 BIGINT，让上限留足。真正的性能问题更多来自索引设计和查询方式，不是“用 BIGINT 就慢”，在数据结构中，我们也会使用到时间换取空间的做法。

2）FLOAT/DOUBLE（浮点数） vs DECIMAL（定点数）

浮点数有精度损失，定点数不丢精度。

CREATE TABLE if not exists t_money_demo (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
price_float DOUBLE,
price_decimal DECIMAL(10, 2)
);

INSERT INTO t_money_demo(price_float, price_decimal)
VALUES (0.1 + 0.2, 0.30);

SELECT price_float, price_decimal FROM t_money_demo;

你会发现 price_float 可能出现一串你不想看到的小数尾巴，这就是典型的浮点误差。所以金额、积分、精确统计类字段，直接 DECIMAL(M, D)，别犹豫。

课件里也写了 DECIMAL 的默认值和范围（MySQL 的 M 最大 65，D 最大 30），你做普通业务足够了。

字符串类型：CHAR / VARCHAR / TEXT 的“性格”

字符串类型最大的坑，通常不是“能不能存”，而是：

存储方式不一样，性能和空间完全不同
结尾空格、长度截断、索引支持都不一样

1）CHAR vs VARCHAR

我直接放进来一个例子，大家可以跑一下感受：

CREATE TABLE if not exists vc (
v VARCHAR(4),
c CHAR(4)
);

INSERT INTO vc VALUES ('ab ', 'ab ');
SELECT CONCAT('(', v, ')'), CONCAT('(', c, ')') FROM vc;

一般你会看到：

VARCHAR 会保留尾部空格
CHAR 在取值时会把尾部填充的空格去掉（因为它存的时候是“固定长度右侧补空格”）

再来一个“截断警告”：

INSERT INTO vc VALUES ('ab ', 'ab ');
SHOW WARNINGS;

这条在写脚本/导数据的时候特别关键：很多数据不是“插入失败”，而是“插进去了但被截断了”，你要学会看 WARNINGS。

2）VARCHAR vs TEXT（什么时候上 TEXT）

VARCHAR：适合“短字符串 + 需要经常检索/建索引”的字段（用户名、邮箱、手机号）
TEXT：适合“长内容 + 不常用来做过滤条件”的字段（文章正文、备注、简介）

一个更像真实业务的示例：

CREATE TABLE if not exists article (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(200) NOT NULL,
content LONGTEXT NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

如果你真的需要对长文本做检索：

常见做法 1：FULLTEXT（全文索引）
常见做法 2：前缀索引（例如 content(100)），但这块属于“能用但要谨慎”，后面讲索引时再细聊

二进制类型：BINARY/VARBINARY/BLOB

这类字段你平时可能不常写，但一旦遇到（比如存缩略图、文件 hash、签名、二进制 token），就得会选。

CREATE TABLE if not exists t_bin (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
md5 CHAR(32) NOT NULL,
sha256 BINARY(32) NOT NULL,
raw_data BLOB NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

简单理解：

BINARY/VARBINARY：按字节存，适合“固定/可变长度二进制串”
BLOB/TEXT：大字段（都可能溢出到溢出页），别拿它当普通字段乱建索引

ENUM / SET

ENUM：从给定值里选一个
SET：从给定值里选多个

CREATE TABLE if not exists t_enum_set (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
status ENUM('INIT', 'PAID', 'CANCEL') NOT NULL,
tags SET('JAVA', 'MYSQL', 'LINUX', 'NETWORK') NULL
);

这俩在 MySQL 内部会用整数表示，空间上不算浪费。但我个人建议你把它当成“比较明确且不怎么变的字段”来用（比如性别、支付状态）。如果值经常变，还是走字典表/配置更稳。

日期类型：DATE / TIME / DATETIME / TIMESTAMP / YEAR

DATETIME：范围很大（1000 ~ 9999），存什么就是什么，不会因为时区自动变化
TIMESTAMP：范围相对小（从 1970 开始），常用于“记录时间点”，并且会受时区影响（更像“真实时间点”）

建一个时间字段齐全一点的表：

CREATE TABLE if not exists t_time (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
d DATE,
t TIME,
dt DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3),
ts TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3),
y YEAR
);

fsp（小数秒精度，0~6），你做日志、埋点时会很常见：DATETIME(3) 表示毫秒级。

表的操作（Table）

表是你真正放数据的地方。建表这块最核心的其实不是“语法”，而是：

字段类型怎么选（这一章已经把常用类型揉进来了）
约束怎么加（保证数据不烂）
引擎/字符集怎么统一（保证后面不折腾）

查看当前库的所有表

SHOW TABLES;

创建表（带注释、字符集、引擎）

先建一个简单的 users 表做演示：

CREATE TABLE IF NOT EXISTS users (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL COMMENT '用户名',
password CHAR(32) NOT NULL COMMENT 'MD5(32位)示例',
birthday DATE NULL COMMENT '生日',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) ENGINE=InnoDB
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_0900_ai_ci;

这里我顺手做了几件“更像真实项目”的设置：

PRIMARY KEY：主键不是可选项，几乎所有业务表都需要
AUTO_INCREMENT：让 id 自增（省心）
NOT NULL / DEFAULT：别让空值随便进来
COMMENT：你写给未来的自己看的
ENGINE/CHARSET/COLLATE：把环境统一掉

表在磁盘上的文件（了解即可，但挺有用）

这块不需要背，但理解了以后排障会轻松一点：

InnoDB：通常会有 .ibd（独立表空间时）
MyISAM：常见是 .MYD（数据）、.MYI（索引）、.sdi（表信息）

你会发现：引擎不一样，底层的“存法”也不一样，所以能力差异（事务/锁/恢复）也就不奇怪了。

查看表结构

DESC users;

或者：

SHOW CREATE TABLE users;

如果你只想“快速看字段”，用 DESC；如果你想“把建表语句完整抄走”，用 SHOW CREATE TABLE。

再多两个常用查看（排障很好用）

SHOW FULL COLUMNS FROM users;
SHOW INDEX FROM users;

如果你在看别人库的时候发现某个字段怎么“看起来一样但行为不一样”，SHOW FULL COLUMNS 能把字符集、排序规则、默认值这些信息一次性列出来。

修改表（ALTER TABLE 常用套路）

1）加字段

ALTER TABLE users
ADD COLUMN phone VARCHAR(20) NULL COMMENT '手机号';

2）改字段类型/属性

ALTER TABLE users
MODIFY COLUMN name VARCHAR(50) NOT NULL COMMENT '用户名';

3）删字段

ALTER TABLE users
DROP COLUMN phone;

4）重命名表

RENAME TABLE users TO app_user;

删除表 vs 清空表（这俩经常被混用）

删除表：

DROP TABLE app_user;

清空表（更常用也更危险）：

TRUNCATE TABLE app_user;

我对这两条的直觉总结是：

DROP：表结构都没了
TRUNCATE：表还在，但数据直接“清零”（通常比 DELETE 快很多）

后面你写清数据脚本时，看到 TRUNCATE 这词，建议你停一下，确认是不是你真想要的效果。

增删改查（CRUD）

CRUD 的语法不难，我一般把 DML（INSERT/UPDATE/DELETE）当成带风险的操作。

0）准备一张演示表

CREATE TABLE IF NOT EXISTS exam (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL COMMENT '同学姓名',
chinese FLOAT NULL COMMENT '语文成绩',
math FLOAT NULL COMMENT '数学成绩',
english FLOAT NULL COMMENT '英语成绩'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

插几条测试数据：

INSERT INTO exam (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);

再补一个“更像业务”的示例表（把数据类型融合起来）：

CREATE TABLE if not exists orders (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
order_no CHAR(20) NOT NULL COMMENT '订单号（定长更合适）',
user_id BIGINT NOT NULL COMMENT '用户ID',
amount DECIMAL(10, 2) NOT NULL COMMENT '订单金额',
status ENUM('INIT', 'PAID', 'CANCEL') NOT NULL DEFAULT 'INIT' COMMENT '状态',
remark VARCHAR(200) NULL COMMENT '备注',
extra JSON NULL COMMENT '扩展信息',
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
UNIQUE KEY uk_order_no(order_no),
KEY idx_user_id(user_id),
KEY idx_created_at(created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

1）Create：INSERT 新增

全列插入（不推荐写在业务代码里）

INSERT INTO users VALUES (NULL, '张三', 'e10adc3949ba59abbe56e057f20f883e', '2005-01-01', NOW());

这种写法的问题是：表结构一改（多一个字段/字段顺序变了），你所有 SQL 都得跟着改。

指定列插入（更常用）

INSERT INTO users(name, password, birthday)
VALUES ('李四', 'e10adc3949ba59abbe56e057f20f883e', '2004-12-12');

一次插多行（效率更好）

INSERT INTO users(name, password, birthday)
VALUES
('王五', 'e10adc3949ba59abbe56e057f20f883e', NULL),
('赵六', 'e10adc3949ba59abbe56e057f20f883e', '2003-05-20');

插入时“顺便”查出来（INSERT … SELECT）

这种写法在初始化数据、迁移数据时非常常见：

CREATE TABLE if not exists exam_copy LIKE exam;

INSERT INTO exam_copy(name, chinese, math, english)
SELECT name, chinese, math, english
FROM exam
WHERE math >= 80;

2）Retrieve：SELECT 查询

查询写得顺不顺，关键在于：你要记住 SQL 的“逻辑执行顺序”（不是你写的顺序），同样的，针对于select查询，我们也有很多不同的查询方式，后面再为大家逐一总结。在这里插入图片描述

查所有列（调试可用，业务慎用）

SELECT * FROM exam;

其中*代表的是通配符的意思，可以把数据库的所有表都查询出来，但是这个操作也需要谨慎，要不然就会导致数据库的服务器内存吃满卡死的情况出现。

只查需要的列（业务常用）

SELECT id, name, math FROM exam;

也可以是带有表达式的查询，比如math=math+10这样的式子，这里就不再赘述。

使用AS指定别名

SELECT id, name, math AS score FROM exam;

DISTINCT：最直接的去重

单列去重

SELECT DISTINCT name
FROM users;

这个结果只保证 name 不重复。

多列去重（“组合去重”）

SELECT DISTINCT name, birthday
FROM users;

这里去重的单位是 (name, birthday) 这个组合。也就是说：只要两列都一样，才算重复，有一个不一样都算做不重复。

DISTINCT + ORDER BY

SELECT DISTINCT name
FROM users
ORDER BY name;

一般没问题。但你要是写成 SELECT DISTINCT name FROM users ORDER BY id;，很多时候会报错或结果不符合预期，因为 id 不在 select 列表里。

DISTINCT 遇到 NULL

SELECT DISTINCT birthday
FROM users;

NULL 会被当成“一个值”来处理：多个 NULL 最终只会显示一个 NULL,在SQL中，它可以表示什么都没填。而关于NULL的运算，如果有一列的结果值为NULL，那么后续运算的表达式结果都是NULL。

WHERE：条件过滤（核心中的核心）

SELECT id, name, math
FROM exam
WHERE math >= 80;

这里注意=表示比较相等，而非==

常见条件你以后会天天用：

比较：= != > >= < <=
范围：BETWEEN a AND b，这里是[a,b]的闭区间
集合：IN (…)
模糊：LIKE '%关键字%'，这是字符串的模糊匹配
空值：IS NULL / IS NOT NULL（注意不是 = NULL）

示例：

SELECT id, name
FROM exam
WHERE name LIKE '%悟%';

再补一个 IN 的例子（很多人写业务筛选会用到）：

SELECT id, name, math
FROM exam
WHERE name IN ('孙悟空', '猪悟能');

还有AND和OR的使用方式，本质是与和或运算的区别，WHERE执行的时候会根据写的逻辑判断先执行那个：如果加括号那么括号内的内容就有最高的优先级，否则的话就是按照AND优先级高于OR去执行。

ORDER BY + LIMIT：排序与分页

SELECT id, name, total
FROM (
SELECT id, name, (chinese + math + english) AS total comment 'AS在这里是起一个别名'
FROM exam
) t
ORDER BY total DESC
LIMIT 3;

这里的排序是默认升序的（ASC），如果想要降序，那么就使用desc来降序，代表的descend。LIMIT 直接从物理层面限制数据传输量。对于包含数十万甚至上亿条记录的表，每次只获取固定数量的记录能显著减少网络传输时间、降低应用程序内存占用，同时减少数据库缓冲池的压力。

LIMIT 必须与明确的 ORDER BY 子句配合才能保证分页结果的正确性和稳定性。没有排序规则的 LIMIT 返回的记录顺序是随机的，会导致重复或遗漏数据的问题。

聚合函数：COUNT / SUM / AVG / MAX / MIN

SELECT
COUNT(*) AS cnt,
AVG(math) AS avg_math,
MAX(english) AS max_english
FROM exam;

count是用来查询表中的记录数的，我们也可以写为COUNT(name)等，但是这样写遇见NULL了以后不会进行计数。

GROUP BY + HAVING：分组统计与分组过滤

先提醒一句：WHERE 是“分组前过滤”，HAVING 是“分组后过滤”。比如按分数段（随便举例）分组统计：

SELECT
CASE
WHEN math >= 90 THEN '90+'
WHEN math >= 80 THEN '80-89'
WHEN math >= 60 THEN '60-79'
ELSE '0-59'
END AS math_level,
COUNT(*) AS cnt
FROM exam
GROUP BY math_level
HAVING cnt >= 2
ORDER BY cnt DESC;

3）Update：UPDATE 修改

基本更新（一定要带 WHERE）

UPDATE exam
SET english = 60
WHERE name = '宋公明';

同时改多个字段

UPDATE exam
SET chinese = chinese + 5,
math = math + 5
WHERE name = '刘玄德';

一般会按这个习惯来：

1.先写 SELECT … WHERE … 确认命中行 2.再把 SELECT 改成 UPDATE

4）Delete：DELETE 删除

按条件删除（一定要带 WHERE）

DELETE FROM exam
WHERE name = '宋公明';

批量删除（建议带 LIMIT 做保险）

DELETE FROM exam
WHERE english < 40
LIMIT 10;

顺便再强调一次： DELETE 是“按行删”，TRUNCATE 是“直接清表”。两者的风险级别不是一个量级。

本章小结

这一章我们为大家大致介绍了下面三个内容，总结一下就是：

库/表的 DDL 会写，尤其是 SHOW CREATE 这种排障利器
CRUD 不难，但 WHERE、聚合、分组这些是分水岭，尤其是 WHERE 写错的后果很真实
数据类型要选对：整数/定点/字符串/日期的“性格”不一样，选错后期基本都要还债
字符集和排序规则要统一（建议 utf8mb4）

下一章我们会针对于数据库约束、联合查询来详细为大家展开，这一章的内容也会有所涉及。