MySQL 实战入门从"增删改查"到"高效查询"的操作_Mysql

在数据驱动的时代，mysql 作为全球最流行的开源关系型数据库，是每一位后端开发者、数据分析师乃至全栈工程师的必修课。无论你的架构多么宏大，微服务多么复杂，最终数据的落地往往都回归到最基础的 crud（create, read, update, delete）操作。

很多初学者只会写 select *，却在面对百万级数据时束手无策；或者在更新数据时因忘记加 where 条件而酿成“删库跑路”的惨剧。本文将带你系统梳理 mysql 的核心操作，不仅教你“怎么写”，更教你“怎么写得安全、高效”。

一、基石：数据的“增删改查” (crud)

1.1 增 (create)：不仅仅是插入

插入数据看似简单，但处理批量插入和默认值才是实战关键。

单条插入：

insert into users (username, email, age, created_at) 
values ('alice', 'alice@example.com', 25, now());

批量插入（性能关键） ：不要在循环中执行单条 insert！一次性插入多条数据能减少网络交互和事务开销，性能提升数倍。

insert into users (username, email, age, created_at) 
values 
  ('bob', 'bob@example.com', 30, now()),
  ('charlie', 'charlie@example.com', 28, now()),
  ('david', 'david@example.com', 22, now());

忽略或更新：如果主键冲突怎么办？

insert into visits (ip, count) values ('192.168.1.1', 1)
on duplicate key update count = count + 1;

insert ignore: 冲突则直接忽略，不报错。
on duplicate key update: 冲突则执行更新操作（常用于统计计数）。

1.2 删 (delete)：高危操作，慎之又慎

铁律：执行 delete 前，必须先执行对应的 select 确认范围！

条件删除：

-- 先确认：select * from users where age < 18 and status = 'inactive';
delete from users 
where age < 18 and status = 'inactive';

逻辑删除 vs 物理删除：在生产环境中，严禁轻易使用物理删除（delete）。通常会在表中增加 is_deleted 或 deleted_at 字段。

做法：update users set is_deleted = 1, deleted_at = now() where id = 100;

好处：数据可恢复，便于审计，避免外键约束报错。

清空表：如果要清空整张表，用 truncate table users; 比 delete from users; 更快，且重置自增 id，但它无法回滚（取决于事务隔离级别），且不会触发删除触发器。

1.3 改 (update)：精准打击

更新操作同样需要 where 条件的保护。

单字段与多字段更新：

update users 
set age = 26, last_login = now() 
where username = 'alice';

基于计算的更新：

-- 所有用户积分加 10
update users set score = score + 10;

多表关联更新（mysql 特色）：

update users u
join orders o on u.id = o.user_id
set u.vip_level = 'gold'
where o.total_amount > 10000;

1.4 查 (select)：灵魂所在

查询是数据库最高频的操作，也是优化空间最大的部分。

基础查询：

select id, username, email from users where age > 18 order by created_at desc limit 10;

模糊查询：

-- 查找名字包含 "li" 的用户
select * from users where username like '%li%'; 
-- 注意：前缀通配符 '%li' 会导致索引失效，性能较差

去重与统计：

select distinct city from users; -- 去重
select count(*), avg(age) from users; -- 聚合统计

二、进阶：常用语句与核心功能

掌握了 crud 只是第一步，真正让 mysql 发挥威力的是以下高级特性。

2.1 多表连接 (join)

关系型数据库的核心在于“关系”。

inner join：只返回两个表中匹配的行（交集）。

select u.username, o.order_no 
from users u 
inner join orders o on u.id = o.user_id;

left join：返回左表所有行，右表没有匹配的填 null（常用于查“未下单的用户”）。

select u.username, o.order_no 
from users u 
left join orders o on u.id = o.user_id 
where o.order_no is null; -- 找出从未下过单的用户

2.2 分组与过滤 (group by & having)

group by：将数据按某列分组。

having：对分组后的结果进行过滤（where 无法用于聚合函数）。

-- 统计每个城市的用户数，只显示超过 100 人的城市
select city, count(*) as user_count 
from users 
group by city 
having user_count > 100 
order by user_count desc;

2.3 子查询 (subquery)

在查询中嵌套查询。虽然灵活，但性能通常不如 join，需谨慎使用。

-- 查找订单金额大于平均订单金额的订单
select * from orders 
where amount > (select avg(amount) from orders);

2.4 事务控制 (transaction)

涉及金钱、库存等关键业务，必须保证原子性（acid）。

start transaction;
-- 1. 扣减库存
update products set stock = stock - 1 where id = 101;
-- 2. 创建订单
insert into orders (product_id, user_id) values (101, 55);
-- 检查是否有错误，如果有则回滚，否则提交
-- rollback; 
commit;

2.5 索引管理 (index)

索引是查询速度的加速器，但会拖慢写入速度。

创建索引：

create index idx_username on users(username);
create unique index idx_email on users(email); -- 唯一索引，防止重复

查看执行计划（优化必做）：在 sql 前加 explain，查看是否用到了索引 (type: ref 或 range 为佳，all 为全表扫描，需优化)。

explain select * from users where username = 'alice';

三、避坑指南与最佳实践

拒绝 select * ：
- 原因：网络传输浪费、无法利用覆盖索引、表结构变更可能导致代码出错。
- 做法：明确列出需要的字段 select id, name, ...。
小心 null 值：
- null 不等于 0 或空字符串。在计算和判断时要格外小心（如 count(column) 不统计 null 值，而 count(*) 统计）。
- 建议在建表时尽量设置 not null 并给默认值。
分页优化的陷阱：

-- 推荐：利用主键索引
select * from users where id > 100000 limit 10;

深分页（limit 100000, 10）非常慢，因为 mysql 要扫描前 100000 条然后丢弃。
优化：使用“游标法”或“延迟关联”。
字符集选择：
- 现在统一推荐使用 utf8mb4，因为它支持 emoji 表情等特殊字符，而旧的 utf8 在 mysql 中实际上是 utf8mb3，不支持 emoji。
sql 注入防御：
- 永远不要拼接用户输入到 sql 字符串中。
- 必须使用预编译语句（prepared statements），如在 java 中使用 preparedstatement，在 node.js 中使用参数化查询，在 python 中使用 %s 占位符。