MySQL入门必备：一文吃透表的增删改查（CRUD）全操作

禁默

发布于 2025-12-24 15:37:42

170

MySQL入门必备：一文吃透表的增删改查（CRUD）全操作

对于后端开发和数据处理来说，MySQL是绕不开的核心工具，而表的增删改查（CRUD）更是MySQL操作的基石。无论是存储用户信息、处理考试成绩，还是管理业务数据，都离不开这四大核心操作。今天就结合实战案例，把MySQL的CRUD操作讲透，新手也能轻松上手。

一、先搞懂：什么是CRUD？

CRUD是数据库操作的核心思想，对应四种基本操作：

Create（创建）：往表中插入新数据
Retrieve（读取）：从表中查询所需数据
Update（更新）：修改表中已有的数据
Delete（删除）：移除表中不需要的数据

在开始操作前，我们先准备两个测试表，后面的案例都会基于这两个表展开，大家可以直接复制SQL语句执行：

-- 学生表：存储学号、姓名、QQ等信息
CREATE TABLE students (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  sn INT NOT NULL UNIQUE COMMENT '学号',
  name VARCHAR(20) NOT NULL,
  qq VARCHAR(20)
);

-- 考试成绩表：存储学生各科成绩
CREATE TABLE exam_result (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL COMMENT '同学姓名',
  chinese float DEFAULT 0.0 COMMENT '语文成绩',
  math float DEFAULT 0.0 COMMENT '数学成绩',
  english float DEFAULT 0.0 COMMENT '英语成绩'
);

二、Create：插入数据的正确姿势

插入数据的核心是INSERT语句，根据需求不同，有多种插入方式，新手最容易踩的坑就是“字段数量不匹配”，一定要注意！

2.1 单行全列插入

直接指定所有列的数据，要求值的数量和顺序与表结构完全一致。如果主键是自增的（AUTO_INCREMENT），也可以省略不写，MySQL会自动生成。

-- 插入两条学生记录，id自增时可省略
INSERT INTO students (sn, name, qq) VALUES (10000, '唐三藏', NULL),
INSERT INTO students (sn, name, qq) VALUES (10001, '孙悟空', '11111');

-- 插入成绩数据
INSERT INTO exam_result (name, chinese, math, english) VALUES ('唐三藏', 67, 98, 56);
INSERT INTO exam_result (name, chinese, math, english) VALUES('孙悟空', 87, 78, 77);
INSERT INTO exam_result (name, chinese, math, english) VALUES('猪悟能', 88, 98, 90);

2.2 多行指定列插入

只插入需要的字段，减少数据传输量，尤其适合表字段较多的场景。值的数量和顺序要与指定的列对应。

-- 只插入id、学号、姓名，多行一起插入
INSERT INTO students (id, sn, name) VALUES 
(102, 20001, '曹孟德'),
(103, 20002, '孙仲谋');

2.3 插入冲突怎么办？

当插入的主键或唯一键（如学号sn）已存在时，直接插入会报错。这时候可以用INSERT ... ON DUPLICATE KEY UPDATE实现“存在则更新，不存在则插入”。

-- 主键100已存在，执行更新操作
INSERT INTO students (id, sn, name) VALUES (100, 10010, '唐大师')
ON DUPLICATE KEY UPDATE sn = 10010, name = '唐大师';

还有一种是REPLACE语句，会删除冲突数据后重新插入，适合完全替换的场景，但要谨慎使用，避免误删数据。

三、Retrieve：查询数据的实用技巧

查询是使用最频繁的操作，核心是SELECT语句，配合WHERE、ORDER BY、LIMIT等子句，能精准筛选出需要的数据。

3.1 基础查询：别再用*全列查询了

很多新手习惯用SELECT *查询所有列，但这样会传输冗余数据，还可能影响索引使用。建议明确指定需要的列。

-- 不推荐：全列查询
SELECT * FROM exam_result;

-- 推荐：指定列查询，还能调整顺序
SELECT id, name, english FROM exam_result;

-- 进阶：查询表达式（如总分），并指定别名
SELECT name, chinese + math + english AS 总分 FROM exam_result;

3.2 条件筛选：WHERE子句的核心用法

用WHERE指定条件，结合比较运算符和逻辑运算符，实现精准筛选。常见场景如下：

-- 1. 英语不及格（<60）的同学
SELECT name, english FROM exam_result WHERE english < 60;

-- 2. 语文成绩在80-90之间（两种写法）
SELECT name, chinese FROM exam_result WHERE chinese BETWEEN 80 AND 90;
SELECT name, chinese FROM exam_result WHERE chinese >=80 AND chinese <=90;

-- 3. 姓孙的同学（模糊匹配，%代表任意字符）
SELECT name FROM exam_result WHERE name LIKE '孙%';

-- 4. QQ号不为空的学生（NULL要用IS NOT NULL判断）
SELECT name, qq FROM students WHERE qq IS NOT NULL;

注意：NULL不能用=判断，必须用IS NULL或IS NOT NULL，或者用<=>（NULL安全等于）。

3.3 结果排序与分页

查询结果默认顺序不确定，用ORDER BY排序，ASC升序（默认），DESC降序；数据量大时用LIMIT分页，避免数据库卡死。

-- 1. 按数学成绩降序排序，成绩相同按英语升序
SELECT name, math, english FROM exam_result 
ORDER BY math DESC, english ASC;

-- 2. 分页查询：第2页，每页3条（OFFSET代表起始位置，从0开始）
SELECT id, name, math FROM exam_result 
ORDER BY id LIMIT 3 OFFSET 3;

3.4 聚合查询：统计数据更高效

用聚合函数（COUNT、SUM、AVG等）统计数据，比如统计人数、平均分等，配合GROUP BY还能按分组统计。

-- 1. 统计班级人数（COUNT(*)不受NULL影响）
SELECT COUNT(*) FROM students;

-- 2. 统计数学成绩总分和平均分
SELECT SUM(math) AS 数学总分, AVG(math) AS 数学平均分 FROM exam_result;

-- 3. 按部门分组统计平均工资（经典案例）
SELECT deptno, AVG(sal) AS 部门平均工资 FROM EMP GROUP BY deptno;

四、Update：修改数据要精准

更新数据用UPDATE语句，必须加WHERE条件，否则会更新全表数据，后果不堪设想！

-- 1. 单条更新：孙悟空的数学成绩改为80分
UPDATE exam_result SET math = 80 WHERE name = '孙悟空';

-- 2. 多条更新：曹孟德的数学60分、语文70分
UPDATE exam_result SET math = 60, chinese = 70 WHERE name = '曹孟德';

-- 3. 批量更新：总分倒数前三的同学数学加30分
UPDATE exam_result SET math = math + 30
ORDER BY chinese + math + english LIMIT 3;

五、Delete：删除数据需谨慎

删除数据用DELETE语句，同样必须加WHERE条件，避免误删全表。另外还有TRUNCATE语句，用于清空整表。

-- 1. 单条删除：删除孙悟空的成绩
DELETE FROM exam_result WHERE name = '孙悟空';

-- 2. 清空表：两种方式的区别
-- DELETE：逐行删除，支持回滚，自增ID不重置
DELETE FROM for_delete;

-- TRUNCATE：快速清空，不支持回滚，自增ID重置（慎用）
TRUNCATE TABLE for_truncate;

小技巧：删除重复数据时，可以先创建空表，插入去重后的数据，再通过重命名表实现原子操作，避免删除过程中数据异常。