当前位置： 代码网 > it编程>数据库>Mysql > MySQL核心必会之表的增删改查(CRUD)实战指南

MySQL核心必会之表的增删改查(CRUD)实战指南

2026年07月17日 • Mysql •我要评论
1. 引言crud 是数据库操作的核心，它代表了 create（创建）、retrieve（读取）、update（更新） 和 delete（删除） 这四种基本操作。无论你是初学者还是准备面试，熟练掌握

1. 引言

crud 是数据库操作的核心，它代表了 create（创建）、retrieve（读取）、update（更新） 和 delete（删除） 这四种基本操作。无论你是初学者还是准备面试，熟练掌握 crud 都是必备技能。

本文将基于 mysql，通过大量实战案例，带你彻底掌握表的增删改查。文章内容详尽，代码完整，并配有经典面试题，助你轻松应对面试。

2. create（创建数据）

2.1 语法

insert [into] table_name
    [(column [, column] ...)]
    values (value_list) [, (value_list)] ...

value_list: value, [, value] …

2.2 案例实操

首先，我们创建一张学生表作为操作对象：

-- 创建一张学生表
create table students (
    id int unsigned primary key auto_increment,
    sn int not null unique comment '学号',
    name varchar(20) not null,
    qq varchar(20)
);

2.2.1 单行数据 + 全列插入

-- 插入一条记录，value_list 数量必须和定义表的列的数量及顺序一致
insert into students values (101, 10001, '孙悟空', '11111');
query ok, 1 row affected (0.02 sec)

注意：这里在插入的时候，也可以不用指定 id（当然，那时候就需要明确插入数据到那些列了），那么 mysql 会使用默认的值进行自增。

insert into students values (100, 10000, '唐三藏', null);
query ok, 1 row affected (0.02 sec)

-- 查看插入结果
select * from students;
+-----+-------+-----------+-------+
| id  | sn    | name      | qq    |
+-----+-------+-----------+-------+
| 100 | 10000 | 唐三藏    | null  |
| 101 | 10001 | 孙悟空    | 11111 |
+-----+-------+-----------+-------+
2 rows in set (0.00 sec)

2.2.2 多行数据 + 指定列插入

-- 插入两条记录，value_list 数量必须和指定列数量及顺序一致
insert into students (id, sn, name) values
    (102, 20001, '曹孟德'),
    (103, 20002, '孙仲谋');
query ok, 2 rows affected (0.02 sec)
records: 2  duplicates: 0  warnings: 0

-- 查看插入结果
select * from students;
+-----+-------+-----------+-------+
| id  | sn    | name      | qq    |
+-----+-------+-----------+-------+
| 100 | 10000 | 唐三藏    | null  |
| 101 | 10001 | 孙悟空    | 11111 |
| 102 | 20001 | 曹孟德    | null  |
| 103 | 20002 | 孙仲谋    | null  |
+-----+-------+-----------+-------+
4 rows in set (0.00 sec)

2.3 插入否则更新（on duplicate key update）

当由于 主键 或者 唯一键 对应的值已经存在而导致插入失败时，可以选择性地进行同步更新操作。

语法：

insert ... on duplicate key update
    column = value [, column = value] ...

案例：

-- 主键冲突
insert into students (id, sn, name) values (100, 10010, '唐大师');
error 1062 (23000): duplicate entry '100' for key 'primary'

-- 唯一键冲突
insert into students (sn, name) values (20001, '曹阿瞒');
error 1062 (23000): duplicate entry '20001' for key 'sn'

-- 使用 on duplicate key update 解决冲突
insert into students (id, sn, name) values (100, 10010, '唐大师')
    on duplicate key update sn = 10010, name = '唐大师';
query ok, 2 rows affected (0.47 sec)

影响行数说明：

  • 0 row affected: 表中有冲突数据，但冲突数据的值和 update 的值相等
  • 1 row affected: 表中没有冲突数据，数据被 插入
  • 2 row affected: 表中有冲突数据，并且数据已经被更新
-- 通过 mysql 函数获取受到影响的数据行数
select row_count();
+-------------+
| row_count() |
+-------------+
|           2 |
+-------------+

2.4 替换（replace）

语法：

replace into students (sn, name) values (20001, '曹阿瞒');
query ok, 2 rows affected (0.00 sec)

replace 的工作原理：

  • 主键或者唯一键没有冲突，则直接插入；
  • 主键或者唯一键如果冲突，则删除后再插入。

影响行数说明：

  • 1 row affected: 表中没有冲突数据，数据被 插入
  • 2 row affected: 表中有冲突数据，删除后重新插入

3. retrieve（读取数据）

检索（查询）是 sql 中使用频率最高的操作。

3.1 语法

select
    [distinct] {* | {column [, column] ...}
    [from table_name]
    [where ...]      -- 筛选条件
    [order by column [asc | desc], ...]
    [limit ...]

3.2 案例实操

首先，创建一张考试成绩表并插入测试数据：

-- 创建表结构
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 '英语成绩'
);

-- 插入测试数据
insert into exam_result (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);
query ok, 7 rows affected (0.00 sec)
records: 7  duplicates: 0  warnings: 0

3.3 select 列

3.3.1 全列查询

select * from exam_result;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  1 | 唐三藏    |      67 |   98 |      56 |
|  2 | 孙悟空    |      87 |   78 |      77 |
|  3 | 猪悟能    |      88 |   98 |      90 |
|  4 | 曹孟德    |      82 |   84 |      67 |
|  5 | 刘玄德    |      55 |   85 |      45 |
|  6 | 孙权      |      70 |   73 |      78 |
|  7 | 宋公明    |      75 |   65 |      30 |
+----+-----------+---------+------+---------+
7 rows in set (0.00 sec)

注意：通常情况下不建议使用 * 进行全列查询。

  1. 查询的列越多，意味着需要传输的数据量越大；
  2. 可能会影响到索引的使用（索引待后面课程讲解）。

3.3.2 指定列查询

-- 指定列的顺序不需要按定义表的顺序来
select id, name, english from exam_result;
+----+-----------+---------+
| id | name      | english |
+----+-----------+---------+
|  1 | 唐三藏    |      56 |
|  2 | 孙悟空    |      77 |
|  3 | 猪悟能    |      90 |
|  4 | 曹孟德    |      67 |
|  5 | 刘玄德    |      45 |
|  6 | 孙权      |      78 |
|  7 | 宋公明    |      30 |
+----+-----------+---------+
7 rows in set (0.00 sec)

3.3.3 查询字段为表达式

-- 表达式不包含字段
select id, name, 10 from exam_result;
+----+-----------+----+
| id | name      | 10 |
+----+-----------+----+
|  1 | 唐三藏    | 10 |
|  2 | 孙悟空    | 10 |
|  3 | 猪悟能    | 10 |
|  4 | 曹孟德    | 10 |
|  5 | 刘玄德    | 10 |
|  6 | 孙权      | 10 |
|  7 | 宋公明    | 10 |
+----+-----------+----+
7 rows in set (0.00 sec)

-- 表达式包含一个字段
select id, name, english + 10 from exam_result;
+----+-----------+-------------+
| id | name      | english + 10 |
+----+-----------+-------------+
|  1 | 唐三藏    |          66 |
|  2 | 孙悟空    |          87 |
|  3 | 猪悟能    |         100 |
|  4 | 曹孟德    |          77 |
|  5 | 刘玄德    |          55 |
|  6 | 孙权      |          88 |
|  7 | 宋公明    |          40 |
+----+-----------+-------------+
7 rows in set (0.00 sec)

-- 表达式包含多个字段
select id, name, chinese + math + english from exam_result;
+----+-----------+-------------------------+
| id | name      | chinese + math + english |
+----+-----------+-------------------------+
|  1 | 唐三藏    |                     221 |
|  2 | 孙悟空    |                     242 |
|  3 | 猪悟能    |                     276 |
|  4 | 曹孟德    |                     233 |
|  5 | 刘玄德    |                     185 |
|  6 | 孙权      |                     221 |
|  7 | 宋公明    |                     170 |
+----+-----------+-------------------------+
7 rows in set (0.00 sec)

3.3.4 为查询结果指定别名

语法：

select column [as] alias_name [...] from table_name;

案例：

select id, name, chinese + math + english 总分 from exam_result;
+----+-----------+--------+
| id | name      | 总分   |
+----+-----------+--------+
|  1 | 唐三藏    |    221 |
|  2 | 孙悟空    |    242 |
|  3 | 猪悟能    |    276 |
|  4 | 曹孟德    |    233 |
|  5 | 刘玄德    |    185 |
|  6 | 孙权      |    221 |
|  7 | 宋公明    |    170 |
+----+-----------+--------+
7 rows in set (0.00 sec)

3.3.5 结果去重

-- 98 分重复了
select math from exam_result;
+--------+
| math   |
+--------+
|     98 |
|     78 |
|     98 |
|     84 |
|     85 |
|     73 |
|     65 |
+--------+
7 rows in set (0.00 sec)

-- 去重结果
select distinct math from exam_result;
+--------+
| math   |
+--------+
|     98 |
|     78 |
|     84 |
|     85 |
|     73 |
|     65 |
+--------+
6 rows in set (0.00 sec)

3.4 where 条件

3.4.1 比较运算符

运算符说明
>, >=, <, <=大于，大于等于，小于，小于等于
=等于，null 不安全，例如 null = null 的结果是 null
<=>等于，null 安全，例如 null <=> null 的结果是 true(1)
!=, <>不等于
between a0 and a1范围匹配，[a0, a1]，如果 a0 <= value <= a1，返回 true(1)
in (option, ...)如果是 option 中的任意一个，返回 true(1)
is null是 null
is not null不是 null
like模糊匹配。% 表示任意多个（包括 0 个）任意字符；_ 表示任意一个字符

3.4.2 逻辑运算符

运算符说明
and多个条件必须都为 true(1)，结果才是 true(1)
or任意一个条件为 true(1)，结果为 true(1)
not条件为 true(1)，结果为 false(0)

3.4.3 案例实操

① 英语不及格的同学及英语成绩 ( < 60 )

select name, english from exam_result where english < 60;
+-----------+---------+
| name      | english |
+-----------+---------+
| 唐三藏    |      56 |
| 刘玄德    |      45 |
| 宋公明    |      30 |
+-----------+---------+
3 rows in set (0.01 sec)

② 语文成绩在 [80, 90] 分的同学及语文成绩

-- 使用 and 进行条件连接
select name, chinese from exam_result where chinese >= 80 and chinese <= 90;
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 孙悟空    |      87 |
| 猪悟能    |      88 |
| 曹孟德    |      82 |
+-----------+---------+
3 rows in set (0.00 sec)

-- 使用 between ... and ... 条件
select name, chinese from exam_result where chinese between 80 and 90;
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 孙悟空    |      87 |
| 猪悟能    |      88 |
| 曹孟德    |      82 |
+-----------+---------+
3 rows in set (0.00 sec)

③ 数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩

-- 使用 or 进行条件连接
select name, math from exam_result
 where math = 58
 or math = 59
 or math = 98
 or math = 99;
+-----------+------+
| name      | math |
+-----------+------+
| 唐三藏    |   98 |
| 猪悟能    |   98 |
+-----------+------+
2 rows in set (0.01 sec)

-- 使用 in 条件
select name, math from exam_result where math in (58, 59, 98, 99);
+-----------+------+
| name      | math |
+-----------+------+
| 唐三藏    |   98 |
| 猪悟能    |   98 |
+-----------+------+
2 rows in set (0.00 sec)

④ 姓孙的同学 及 孙某同学

-- % 匹配任意多个（包括 0 个）任意字符
select name from exam_result where name like '孙%';
+-----------+
| name      |
+-----------+
| 孙悟空    |
| 孙权      |
+-----------+
2 rows in set (0.00 sec)

-- _ 匹配严格的一个任意字符
select name from exam_result where name like '孙_';
+--------+
| name   |
+--------+
| 孙权   |
+--------+
1 row in set (0.00 sec)

⑤ 语文成绩好于英语成绩的同学

select name, chinese, english from exam_result where chinese > english;
+-----------+---------+---------+
| name      | chinese | english |
+-----------+---------+---------+
| 唐三藏    |      67 |      56 |
| 孙悟空    |      87 |      77 |
| 曹孟德    |      82 |      67 |
| 刘玄德    |      55 |      45 |
| 宋公明    |      75 |      30 |
+-----------+---------+---------+
5 rows in set (0.00 sec)

⑥ 总分在 200 分以下的同学

-- where 条件中使用表达式
-- 别名不能用在 where 条件中
select name, chinese + math + english 总分 from exam_result
    where chinese + math + english < 200;
+-----------+--------+
| name      | 总分   |
+-----------+--------+
| 刘玄德    |    185 |
| 宋公明    |    170 |
+-----------+--------+
2 rows in set (0.00 sec)

⑦ 语文成绩 > 80 并且不姓孙的同学

-- and 与 not 的使用
select name, chinese from exam_result
 where chinese > 80 and name not like '孙%';
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 猪悟能    |      88 |
| 曹孟德    |      82 |
+-----------+---------+
2 rows in set (0.00 sec)

⑧ 孙某同学，否则要求总成绩 > 200 并且 语文成绩 < 数学成绩 并且 英语成绩 > 80

-- null不参与运算，即nullh和任何数据比较都是false
-- 综合性查询
select name, chinese, math, english, chinese + math + english 总分
from exam_result
where name like '孙_' or (
    chinese + math + english > 200 and chinese < math and english > 80
);
+-----------+---------+------+---------+--------+
| name      | chinese | math | english | 总分   |
+-----------+---------+------+---------+--------+
| 猪悟能    |      88 |   98 |      90 |    276 |
| 孙权      |      70 |   73 |      78 |    221 |
+-----------+---------+------+---------+--------+
2 rows in set (0.00 sec)

⑨ null 的查询

-- 查询 students 表
select * from students;
+-----+-------+-----------+-------+
| id  | sn    | name      | qq    |
+-----+-------+-----------+-------+
| 100 | 10010 | 唐大师    | null  |
| 101 | 10001 | 孙悟空    | 11111 |
| 103 | 20002 | 孙仲谋    | null  |
| 104 | 20001 | 曹阿瞒    | null  |
+-----+-------+-----------+-------+
4 rows in set (0.00 sec)

-- 查询 qq 号已知的同学姓名
select name, qq from students where qq is not null;
+-----------+-------+
| name      | qq    |
+-----------+-------+
| 孙悟空    | 11111 |
+-----------+-------+
1 row in set (0.00 sec)

-- null 和 null 的比较，= 和 <=> 的区别
select null = null, null = 1, null = 0;
+-------------+----------+----------+
| null = null | null = 1 | null = 0 |
+-------------+----------+----------+
|        null |     null |     null |
+-------------+----------+----------+
1 row in set (0.00 sec)

select null <=> null, null <=> 1, null <=> 0;
+---------------+------------+------------+
| null <=> null | null <=> 1 | null <=> 0 |
+---------------+------------+------------+
|             1 |          0 |          0 |
+---------------+------------+------------+
1 row in set (0.00 sec)

3.5 结果排序（order by）

语法：

-- mysql引号中不区分单双引号
select ... from table_name [where ...]
 order by column [asc|desc], [...]
  • asc 为升序（从小到大）
  • desc 为降序（从大到小）
  • 默认为 asc

注意：没有 order by 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序。

3.5.1 同学及数学成绩，按数学成绩升序显示

select name, math from exam_result order by math;
+-----------+------+
| name      | math |
+-----------+------+
| 宋公明    |   65 |
| 孙权      |   73 |
| 孙悟空    |   78 |
| 曹孟德    |   84 |
| 刘玄德    |   85 |
| 唐三藏    |   98 |
| 猪悟能    |   98 |
+-----------+------+
7 rows in set (0.00 sec)

3.5.2 同学及 qq 号，按 qq 号排序显示

-- null 视为比任何值都小，升序出现在最上面
select name, qq from students order by qq;
+-----------+-------+
| name      | qq    |
+-----------+-------+
| 唐大师    | null  |
| 孙仲谋    | null  |
| 曹阿瞒    | null  |
| 孙悟空    | 11111 |
+-----------+-------+
4 rows in set (0.00 sec)

-- null 视为比任何值都小，降序出现在最下面
select name, qq from students order by qq desc;
+-----------+-------+
| name      | qq    |
+-----------+-------+
| 孙悟空    | 11111 |
| 唐大师    | null  |
| 孙仲谋    | null  |
| 曹阿瞒    | null  |
+-----------+-------+
4 rows in set (0.00 sec)

3.5.3 查询同学各门成绩，依次按数学降序，英语升序，语文升序的方式显示

-- 多字段排序，排序优先级随书写顺序
select name, math, english, chinese from exam_result
 order by math desc, english, chinese;
+-----------+------+---------+---------+
| name      | math | english | chinese |
+-----------+------+---------+---------+
| 唐三藏    |   98 |      56 |      67 |
| 猪悟能    |   98 |      90 |      88 |
| 刘玄德    |   85 |      45 |      55 |
| 曹孟德    |   84 |      67 |      82 |
| 孙悟空    |   78 |      77 |      87 |
| 孙权      |   73 |      78 |      70 |
| 宋公明    |   65 |      30 |      75 |
+-----------+------+---------+---------+
7 rows in set (0.00 sec)

3.5.4 查询同学及总分，由高到低

-- order by 中可以使用表达式
select name, chinese + english + math from exam_result
order by chinese + english + math desc;
+-----------+-------------------------+
| name | chinese + english + math |
+-----------+-------------------------+
| 猪悟能 | 										276 |
| 孙悟空 | 										242 |
| 曹孟德 | 										233 |
| 唐三藏 | 										221 |
| 孙权 | 										221 |
| 刘玄德 | 										185 |
| 宋公明 |										 170 |
+-----------+-------------------------+
7 rows in set (0.00 sec)

-- order by 子句中可以使用列别名
select name, chinese + english + math 总分 from exam_result
order by 总分 desc;
+-----------+--------+
| name | 总分 |
+-----------+--------+
| 猪悟能 | 276 |
| 孙悟空 | 242 |
| 曹孟德 | 233 |
| 唐三藏 | 221 |
| 孙权 | 	221 |
| 刘玄德 | 185 |
| 宋公明 | 170 |
+-----------+--------+
7 rows in set (0.00 sec)
  • order by 语句在total语句之后，所以这里能使用别名
  • order by 需要先有数据，再排序，即order by 指令是在将数据筛选之后再排序

3.5.5 查询姓孙的同学或者姓曹的同学数学成绩，结果按数学成绩由高到低显示

-- 结合 where 子句 和 order by 子句
select name, math from exam_result
where name like '孙%' or name like '曹%'
order by math desc;
+-----------+--------+
| name | math |
+-----------+--------+
| 曹孟德 | 84 |
| 孙悟空 | 78 |
| 孙权 | 73 |
+-----------+--------+
3 rows in set (0.00 sec)

3.6 筛选分页结果（limit）

语法：

-- 从 0 开始，筛选 n 条结果
select ... from table_name [where ...] [order by ...] limit n;

-- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
select ... from table_name [where ...] [order by ...] limit n offset s;

建议：对未知表进行查询时，最好加一条 limit 1，避免因为表中数据过大，查询全表数据导致数据库卡死。

1.需要数据才能排序

2.只有数据准备好了，你才能显示，limit的本质功能是“显示”，即limit的执行顺序更靠后。

3.s是开始位置（下标从0开始）

4.n是步长，从指定位置开始，连续读取多少条记录

5.offset:开始位置,n为连续读取记录数

案例：按 id 进行分页，每页 3 条记录，分别显示第 1、2、3 页

-- 第 1 页
select id, name, math, english, chinese from exam_result
 order by id limit 3 offset 0;
+----+-----------+------+---------+---------+
| id | name      | math | english | chinese |
+----+-----------+------+---------+---------+
|  1 | 唐三藏    |   98 |      56 |      67 |
|  2 | 孙悟空    |   78 |      77 |      87 |
|  3 | 猪悟能    |   98 |      90 |      88 |
+----+-----------+------+---------+---------+
3 rows in set (0.02 sec)

-- 第 2 页
select id, name, math, english, chinese from exam_result
 order by id limit 3 offset 3;
+----+-----------+------+---------+---------+
| id | name      | math | english | chinese |
+----+-----------+------+---------+---------+
|  4 | 曹孟德    |   84 |      67 |      82 |
|  5 | 刘玄德    |   85 |      45 |      55 |
|  6 | 孙权      |   73 |      78 |      70 |
+----+-----------+------+---------+---------+
3 rows in set (0.00 sec)

-- 第 3 页，如果结果不足 3 个，不会有影响
select id, name, math, english, chinese from exam_result
 order by id limit 3 offset 6;
+----+-----------+------+---------+---------+
| id | name      | math | english | chinese |
+----+-----------+------+---------+---------+
|  7 | 宋公明    |   65 |      30 |      75 |
+----+-----------+------+---------+---------+
1 row in set (0.00 sec)

4. update（更新数据）

4.1 语法

update table_name set column = expr [, column = expr ...]
 [where ...] [order by ...] [limit ...]

对查询到的结果进行列值更新。

4.2 案例实操

4.2.1 将孙悟空同学的数学成绩变更为 80 分

-- 查看原数据
select name, math from exam_result where name = '孙悟空';
+-----------+------+
| name      | math |
+-----------+------+
| 孙悟空    |   78 |
+-----------+------+
1 row in set (0.00 sec)

-- 数据更新
update exam_result set math = 80 where name = '孙悟空';
query ok, 1 row affected (0.04 sec)
rows matched: 1  changed: 1  warnings: 0

-- 查看更新后数据
select name, math from exam_result where name = '孙悟空';
+-----------+------+
| name      | math |
+-----------+------+
| 孙悟空    |   80 |
+-----------+------+
1 row in set (0.00 sec)

4.2.2 将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分

-- 查看原数据
select name, math, chinese from exam_result where name = '曹孟德';
+-----------+------+---------+
| name      | math | chinese |
+-----------+------+---------+
| 曹孟德    |   84 |      82 |
+-----------+------+---------+
1 row in set (0.00 sec)

-- 数据更新
update exam_result set math = 60, chinese = 70 where name = '曹孟德';
query ok, 1 row affected (0.14 sec)
rows matched: 1  changed: 1  warnings: 0

-- 查看更新后数据
select name, math, chinese from exam_result where name = '曹孟德';
+-----------+------+---------+
| name      | math | chinese |
+-----------+------+---------+
| 曹孟德    |   60 |      70 |
+-----------+------+---------+
1 row in set (0.00 sec)

4.2.3 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分

-- 查看原数据
-- 别名可以在 order by 中使用
select name, math, chinese + math + english 总分 from exam_result
 order by 总分 limit 3;
+-----------+------+--------+
| name      | math | 总分   |
+-----------+------+--------+
| 宋公明    |   65 |    170 |
| 刘玄德    |   85 |    185 |
| 曹孟德    |   60 |    197 |
+-----------+------+--------+
3 rows in set (0.00 sec)

-- 数据更新，不支持 math += 30 这种语法
update exam_result set math = math + 30
 order by chinese + math + english limit 3;

-- 查看更新后数据
select name, math, chinese + math + english 总分 from exam_result
 where name in ('宋公明', '刘玄德', '曹孟德');
+-----------+------+--------+
| name      | math | 总分   |
+-----------+------+--------+
| 曹孟德    |   90 |    227 |
| 刘玄德    |  115 |    215 |
| 宋公明    |   95 |    200 |
+-----------+------+--------+
3 rows in set (0.00 sec)

4.2.4 将所有同学的语文成绩更新为原来的 2 倍

注意：更新全表的语句慎用！

-- 查看原数据
select * from exam_result;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  1 | 唐三藏    |      67 |   98 |      56 |
|  2 | 孙悟空    |      87 |   80 |      77 |
|  3 | 猪悟能    |      88 |   98 |      90 |
|  4 | 曹孟德    |      70 |   90 |      67 |
|  5 | 刘玄德    |      55 |  115 |      45 |
|  6 | 孙权      |      70 |   73 |      78 |
|  7 | 宋公明    |      75 |   95 |      30 |
+----+-----------+---------+------+---------+
7 rows in set (0.00 sec)

-- 数据更新
update exam_result set chinese = chinese * 2;
query ok, 7 rows affected (0.00 sec)
rows matched: 7  changed: 7  warnings: 0

-- 查看更新后数据
select * from exam_result;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  1 | 唐三藏    |     134 |   98 |      56 |
|  2 | 孙悟空    |     174 |   80 |      77 |
|  3 | 猪悟能    |     176 |   98 |      90 |
|  4 | 曹孟德    |     140 |   90 |      67 |
|  5 | 刘玄德    |     110 |  115 |      45 |
|  6 | 孙权      |     140 |   73 |      78 |
|  7 | 宋公明    |     150 |   95 |      30 |
+----+-----------+---------+------+---------+
7 rows in set (0.00 sec)

5. delete（删除数据）

5.1 删除数据（delete）

语法：

delete from table_name [where ...] [order by ...] [limit ...]

5.1.1 删除孙悟空同学的考试成绩

-- 查看原数据
select * from exam_result where name = '孙悟空';
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  2 | 孙悟空    |    174 |   80 |      77 |
+----+-----------+---------+------+---------+
1 row in set (0.00 sec)

-- 删除数据
delete from exam_result where name = '孙悟空';
query ok, 1 row affected (0.17 sec)

-- 查看删除结果
select * from exam_result where name = '孙悟空';
empty set (0.00 sec)

5.1.2 删除整张表数据

注意：删除整表操作要慎用！

-- 准备测试表
create table for_delete (
    id int primary key auto_increment,
    name varchar(20)
);
query ok, 0 rows affected (0.16 sec)

-- 插入测试数据
insert into for_delete (name) values ('a'), ('b'), ('c');
query ok, 3 rows affected (1.05 sec)
records: 3  duplicates: 0  warnings: 0

-- 查看测试数据
select * from for_delete;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
+----+------+
3 rows in set (0.00 sec)

-- 删除整表数据
delete from for_delete;
query ok, 3 rows affected (0.00 sec)

-- 查看删除结果
select * from for_delete;
empty set (0.00 sec)

-- 再插入一条数据，自增 id 在原值上增长
insert into for_delete (name) values ('d');
query ok, 1 row affected (0.00 sec)

-- 查看数据
select * from for_delete;
+----+------+
| id | name |
+----+------+
|  4 | d    |
+----+------+
1 row in set (0.00 sec)

-- 查看表结构，会有 auto_increment=n 项
show create table for_delete\g
*************************** 1. row ***************************
       table: for_delete
create table: create table `for_delete` (
  `id` int(11) not null auto_increment,
  `name` varchar(20) default null,
  primary key (`id`)
) engine=innodb auto_increment=5 default charset=utf8
1 row in set
### 5.2 ✂️ 截断表（truncate）

**语法：**

```sql
truncate [table] table_name

注意：这个操作慎用！

  1. 只能对整表操作，不能像 delete 一样针对部分数据操作；
  2. 实际上 mysql 不对数据操作，所以比 delete 更快，但是 truncate 在删除数据的时候，并不经过真正的事务，所以无法回滚；
  3. 会重置 auto_increment 项。
-- 准备测试表
create table for_truncate (
    id int primary key auto_increment,
    name varchar(20)
);
query ok, 0 rows affected (0.16 sec)

-- 插入测试数据
insert into for_truncate (name) values ('a'), ('b'), ('c');
query ok, 3 rows affected (1.05 sec)
records: 3  duplicates: 0  warnings: 0

-- 查看测试数据
select * from for_truncate;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
+----+------+
3 rows in set (0.00 sec)

-- 截断整表数据
truncate for_truncate;
query ok, 0 rows affected (0.19 sec)

-- 查看截断结果
select * from for_truncate;
empty set (0.00 sec)

-- 再插入一条数据，自增 id 从 1 开始
insert into for_truncate (name) values ('d');
query ok, 1 row affected (0.00 sec)

-- 查看数据，id 从 1 重新开始
select * from for_truncate;
+----+------+
| id | name |
+----+------+
|  1 | d    |
+----+------+
1 row in set (0.00 sec)

-- 查看表结构，auto_increment 已重置为 1
show create table for_truncate\g
*************************** 1. row ***************************
       table: for_truncate
create table: create table `for_truncate` (
  `id` int(11) not null auto_increment,
  `name` varchar(20) default null,
  primary key (`id`)
) engine=innodb auto_increment=2 default charset=utf8
1 row in set (0.00 sec)

delete vs truncate 对比：

对比项deletetruncate
条件删除✅ 支持 where 条件❌ 只能整表删除
事务支持✅ 支持事务，可回滚❌ 不经过事务，无法回滚
速度慢（逐行删除）快（直接释放数据页）
auto_increment不会重置会重置
触发器会触发 delete 触发器不会触发触发器
dml/dcldml（数据操作语言）ddl（数据定义语言）

6. 插入查询结果（insert into select）

6.1 语法

insert into table_name [(column [, column ...])] select ...

将查询结果插入到目标表中，查询的列数、类型必须与目标表匹配。

6.2 案例实操

案例：删除表中的重复记录，重复的数据只能有一份

-- 创建一张有重复数据的表
create table duplicate_data (
    id int,
    name varchar(20)
);

-- 插入测试数据（包含重复记录）
insert into duplicate_data values
    (1, '唐三藏'),
    (2, '孙悟空'),
    (3, '猪悟能'),
    (1, '唐三藏'),   -- 重复
    (2, '孙悟空');   -- 重复
query ok, 5 rows affected (0.00 sec)
records: 5  duplicates: 0  warnings: 0

-- 查看原数据
select * from duplicate_data;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | 唐三藏    |
|    2 | 孙悟空    |
|    3 | 猪悟能    |
|    1 | 唐三藏    |
|    2 | 孙悟空    |
+------+-----------+
5 rows in set (0.00 sec)

-- 第一步：创建一张结构相同的临时表
create table duplicate_tmp like duplicate_data;
query ok, 0 rows affected (0.13 sec)

-- 第二步：将去重后的数据插入临时表
insert into duplicate_tmp select distinct * from duplicate_data;
query ok, 3 rows affected (0.00 sec)
records: 3  duplicates: 0  warnings: 0

-- 查看临时表数据
select * from duplicate_tmp;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | 唐三藏    |
|    2 | 孙悟空    |
|    3 | 猪悟能    |
+------+-----------+
3 rows in set (0.00 sec)

-- 第三步：删除原表
drop table duplicate_data;
query ok, 0 rows affected (0.05 sec)

-- 第四步：将临时表重命名为原表名
rename table duplicate_tmp to duplicate_data;
query ok, 0 rows affected (0.05 sec)

-- 查看最终结果
select * from duplicate_data;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | 唐三藏    |
|    2 | 孙悟空    |
|    3 | 猪悟能    |
+------+-----------+
3 rows in set (0.00 sec)

7. 聚合函数

7.1 常用聚合函数

函数说明
count([distinct] expr)返回查询到的数据的 数量
sum([distinct] expr)返回查询到的数据的 总和，不是数字没有意义
avg([distinct] expr)返回查询到的数据的 平均值，不是数字没有意义
max([distinct] expr)返回查询到的数据的 最大值，不是数字没有意义
min([distinct] expr)返回查询到的数据的 最小值，不是数字没有意义

7.2 案例实操

以下案例基于 exam_result 表（已恢复原始数据）。

7.2.1 统计班级共有多少同学

-- 使用 * 做统计，不受 null 影响
select count(*) from exam_result;
+----------+
| count(*) |
+----------+
|        7 |
+----------+
1 row in set (0.00 sec)

-- 使用表达式，不受 null 影响
select count(1) from exam_result;
+----------+
| count(1) |
+----------+
|        7 |
+----------+
1 row in set (0.00 sec)

小知识：count(*) 和 count(1) 在 innodb 引擎下性能几乎一致，没有本质区别。

7.2.2 统计班级收集的 qq 号有多少

-- count(具体列) 会忽略 null 值
select count(qq) from students;
+-----------+
| count(qq) |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

7.2.3 统计本次考试的数学成绩分数个数

-- count(distinct 列) 会去重
select count(distinct math) from exam_result;
+---------------------+
| count(distinct math)|
+---------------------+
|                   6 |
+---------------------+
1 row in set (0.00 sec)

7.2.4 统计数学成绩总分

select sum(math) from exam_result;
+-----------+
| sum(math) |
+-----------+
|       581 |
+-----------+
1 row in set (0.00 sec)

7.2.5 统计数学成绩平均分

select avg(math) from exam_result;
+-----------+
| avg(math) |
+-----------+
|   83.0000 |
+-----------+
1 row in set (0.00 sec)

7.2.6 统计英语成绩最高分

select max(english) from exam_result;
+--------------+
| max(english) |
+--------------+
|           90 |
+--------------+
1 row in set (0.00 sec)

7.2.7 统计英语成绩最低分

select min(english) from exam_result;
+--------------+
| min(english) |
+--------------+
|           30 |
+--------------+
1 row in set (0.00 sec)

7.2.8 统计英语成绩总分、平均分、最高分、最低分

select
    sum(english)  英语总分,
    avg(english)  英语平均分,
    max(english)  英语最高分,
    min(english)  英语最低分
from exam_result;
+-----------+--------------+--------------+--------------+
| 英语总分  | 英语平均分   | 英语最高分   | 英语最低分   |
+-----------+--------------+--------------+--------------+
|       443 |      63.2857 |           90 |           30 |
+-----------+--------------+--------------+--------------+
1 row in set (0.00 sec)

8. group by 子句

8.1 语法

解释：

1.在select中使用group by 子句可以对指定列进行分组查询

2.先对一列中的不同数据进行分组，再进行聚合统计。

3.分组的目的是为了进行分组之后，方便进行聚合统计。

select column, aggregate_function(column)
from table_name
[where ...]
group by column
[having ...]
[order by ...]
[limit ...]

执行顺序：from → where → group by → having → select → order by → limit

执行顺序理解：

1.来之哪个表

2.where条件筛选

3.group by 分组

4.执行聚合

5.执行having进行条件筛选

注解：

1.指定列名，实际分组，使用该列的不同行数据来进行分组的分组的条件deptno，组内一定是相同的 可以被聚合压缩分组，不就是把一组按照条件拆成了多少个组，进行各自组内的统计

2.分组（分表），不就是把一张表按照条件在逻辑上拆成了多个子表，然后分别对各自的子表进行聚合统计。

注意：

1.只有在group by 后面的列才能出现在select后面

2.group by 后面可以跟多个列来进行分组。

8.2 案例实操

准备雇员信息表（emp）、部门表（dept）、薪资等级表（salgrade）。

-- 创建部门表
create table dept (
    deptno int primary key,
    dname varchar(20) comment '部门名称',
    loc varchar(20) comment '部门所在地'
);

-- 插入部门数据
insert into dept values
    (10, '教研部', '北京'),
    (20, '学工部', '上海'),
    (30, '销售部', '广州'),
    (40, '财务部', '深圳');

-- 创建薪资等级表
create table salgrade (
    grade int primary key comment '薪资等级',
    losal int comment '最低薪资',
    hisal int comment '最高薪资'
);

-- 插入薪资等级数据
insert into salgrade values
    (1, 700, 1200),
    (2, 1201, 1400),
    (3, 1401, 2000),
    (4, 2001, 3000),
    (5, 3001, 9999);

-- 创建雇员表
create table emp (
    empno int primary key comment '雇员编号',
    ename varchar(20) comment '雇员姓名',
    job varchar(20) comment '雇员职位',
    mgr int comment '雇员上级编号',
    hiredate date comment '雇佣日期',
    sal int comment '薪资',
    comm int comment '奖金',
    deptno int comment '部门编号'
);

-- 插入雇员数据
insert into emp values
    (1001, '孙悟空', '讲师', 1004, '2000-12-17', 8000, null, 10),
    (1002, '猪悟能', '学工主任', 1009, '2001-02-20', 16000, 3000, 20),
    (1003, '沙悟净', '咨询师', 1004, '2001-02-22', 12500, 5000, 30),
    (1004, '唐三藏', '教研主任', 1009, '2001-04-02', 29750, null, 10),
    (1005, '刘玄德', '销售员', 1006, '2001-09-28', 12500, 14000, 30),
    (1006, '曹孟德', '销售经理', 1009, '2001-05-01', 28500, null, 30),
    (1007, '孙仲谋', '分析师', 1004, '2001-09-01', 24500, null, 10),
    (1008, '宋公明', '讲师', 1004, '2001-04-12', 15000, null, 10),
    (1009, '刘邦', '总裁', null, '2001-11-17', 50000, null, 20),
    (1010, '韩信', '销售员', 1006, '2001-09-08', 15000, 0, 30),
    (1011, '周公瑾', '分析师', 1004, '2007-01-12', 21000, null, 10);

8.2.1 显示每个部门的平均工资和最高工资

select
    deptno,
    avg(sal) 平均工资,
    max(sal) 最高工资
from emp
group by deptno;
+--------+--------------+--------------+
| deptno | 平均工资     | 最高工资     |
+--------+--------------+--------------+
|     10 | 19550.0000   |        29750 |
|     20 | 33000.0000   |        50000 |
|     30 | 16375.0000   |        28500 |
+--------+--------------+--------------+
3 rows in set (0.00 sec)

8.2.2 显示每个部门的每种岗位的平均工资和最低工资

select
    deptno,
    job,
    avg(sal) 平均工资,
    min(sal) 最低工资
from emp
group by deptno, job;
+--------+--------------+--------------+--------------+
| deptno | job          | 平均工资     | 最低工资     |
+--------+--------------+--------------+--------------+
|     10 | 分析师       | 22750.0000   |        21000 |
|     10 | 教研主任     | 29750.0000   |        29750 |
|     10 | 讲师         | 11500.0000   |         8000 |
|     20 | 学工主任     | 16000.0000   |        16000 |
|     20 | 总裁         | 50000.0000   |        50000 |
|     30 | 销售经理     | 28500.0000   |        28500 |
|     30 | 销售员       | 13333.3333   |        12500 |
|     30 | 咨询师       | 12500.0000   |        12500 |
+--------+--------------+--------------+--------------+
8 rows in set (0.00 sec)

8.2.3 显示平均工资低于 20000 的部门及其平均工资

-- having 用于对分组后的结果进行过滤
select
    deptno,
    avg(sal) 平均工资
from emp
group by deptno
having 平均工资 < 20000;
+--------+--------------+
| deptno | 平均工资     |
+--------+--------------+
|     10 | 19550.0000   |
|     30 | 16375.0000   |
+--------+--------------+
2 rows in set (0.00 sec)
--having经常和group by搭配使用，作用是对分组进行筛选，作用有些像where

where vs having 区别：

  • where：在 分组之前 过滤数据，不能使用聚合函数
  • having：在 分组之后 过滤数据，可以使用聚合函数

小总结：

1.不要单纯的认为，只有磁盘上表结构导入到mysql,真实存在的表才叫表之间筛选出来的，包括最终的结果，在我看来，全部都是逻辑上的表！即 “mysql一切皆是表”

2.未来只要我们能够处理好单表的curd，所有的sql场景，我们全部都能用统一的方式执行。

9. 实战 oj 练习

以下题目来自牛客网、leetcode 等平台，帮助你巩固 crud 知识。

9.1 查找最晚入职员工的所有信息

-- 方法一：使用 order by + limit
select * from employees
order by hire_date desc
limit 1;

-- 方法二：使用子查询
select * from employees
where hire_date = (select max(hire_date) from employees);

9.2 查找入职员工时间排名倒数第三的员工信息

-- 使用 order by + limit offset
select * from employees
order by hire_date desc
limit 1 offset 2;

9.3 查找当前薪水详情以及部门编号 dept_no

select
    s.*,
    d.dept_no
from salaries s
join dept_emp d on s.emp_no = d.emp_no
where s.to_date = '9999-01-01'
  and d.to_date = '9999-01-01';

9.4 查找所有已经分配部门的员工的姓名和部门号

select
    e.last_name,
    e.first_name,
    d.dept_no
from employees e
join dept_emp d on e.emp_no = d.emp_no;

9.5 查找所有员工的姓名以及其部门号（包含未分配部门的员工）

select
    e.last_name,
    e.first_name,
    d.dept_no
from employees e
left join dept_emp d on e.emp_no = d.emp_no;

10. 总结与面试题

10.1 知识总结

操作关键字要点
创建insert支持单行/多行插入，on duplicate key update 处理冲突
读取select支持条件筛选（where）、排序（order by）、分页（limit）
更新update配合 where 限定范围，支持表达式运算
删除delete / truncatedelete 可条件删除，truncate 清空整表并重置自增
聚合count / sum / avg / max / min常与 group by 配合使用
分组group by / havinghaving 过滤分组结果，可使用聚合函数

10.2 经典面试题

q1：where 和 having 的区别？

a：

  • where 在分组前过滤行，不能使用聚合函数
  • having 在分组后过滤组，可以使用聚合函数
  • 执行顺序：where → group by → having

q2：delete 和 truncate 的区别？

a：

  • delete 是 dml，支持事务可回滚，可加 where 条件，不重置自增
  • truncate 是 ddl，不支持事务不可回滚，只能整表删除，重置自增

q3：count(*)、count(1)、count(列名) 的区别？

a：

  • count(*) 统计所有行数，包含 null
  • count(1) 与 count(*) 效果相同，innodb 下性能一致
  • count(列名) 统计该列非 null 的行数

q4：sql 语句的执行顺序是怎样的？

a：

from → where → group by → having → select → order by → limit

q5：on duplicate key update 和 replace 的区别？

a：

  • on duplicate key update：冲突时执行 update，不删除原记录
  • replace：冲突时先 delete 再 insert，可能导致自增 id 变化

恭喜你！ 你已经完整掌握了 mysql 的 crud 操作。从基础的增删改查到高级的聚合分组、去重分页，再到实战 oj 练习，相信你已经具备了扎实的 sql 基础。继续加油，向更高级的 sql 优化、索引、事务等知识进发吧！

以上就是mysql核心必会之表的增删改查(crud)实战指南的详细内容，更多关于mysql表增删改查的资料请关注代码网其它相关文章！

赞 (0)

相关文章:

版权声明：本文内容由互联网用户贡献，该文观点仅代表作者本人。本站仅提供信息存储服务，不拥有所有权，不承担相关法律责任。 如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 2386932994@qq.com 举报，一经查实将立刻删除。

发表评论

验证码：
最近更新的文章
推荐阅读
大家感兴趣的文章
Copyright © 2017-2026  代码网 保留所有权利. 粤ICP备2024248653号
站长QQ:2386932994 | 联系邮箱：2386932994@qq.com