2025-09-24-增删改查

本文章是 MySQL 数据库基础的重点内容

它可以被简写为 CRUD

• C: create, 新增
• R: read, 查询
• U: update, 更新
• D: delete, 删除

下述操作都以如下的表为主

drop table if exists student;
create table if not exists student (
  id int,
  age int,
  name varchar(20),
  math int,
  chinese int,
  english int
);

C: create （新增）

单个元素插入

insert into student values (1,23,'小明', 100, 20, 90);

指定列元素插入

insert into student(age, name) values (26, '大明');

多个元素插入

insert into student 
  values 
(3, 9, '小花', 10, 120, 67),
(4, 91, '老花', 130, 126, 98),
(5, 19, '老花1', 130, 120, 98),
(6, 21, '老花2', 130, 126, 67)
;

R: read （查询）

这里仅仅是单表查询，没有涉及到多表查询

Select

全列查询（工作中慎用）

select * from student;

指定列查询

select id, name, math from student;

查询字段为表达式

select id, name, math + chinese + english from student;

为查询结果指定别名

select name as '姓名', math + chinese + english as '总分' from student;

结果去重查询

-- 会去重 math 相同的值，如果多个字段，那么会去重那几个字段同时相同的结果
select distinct math from student;

Where 条件查询

查询英语成绩小于 70 分的学生姓名及其英语成绩

select name, english from student where english < 70;

查询语文成绩高于英语成绩的同学

select name, chinese, english from student where chinese > english;

总分在 300 分以上的同学

-- 由于先执行 where 语句，再执行 select 语句，那么就不能写为下面这样的形式
-- select name, chinese + math + english as total from student where total > 300;
-- select name, total from student where chinese + math + english as total > 300;
select name, chinese + math + english from student where chinese + math + english > 300;

查询语文成绩大于80分且英语成绩大于80分的同学

select name, chinese, english from student where chinese > 80 and english > 80;

查询语文成绩大于80分或英语成绩大于80分的同学

select name, chinese, english from student where chinese > 80 or english > 80;

查询语文成绩在[120,126]分的同学及语文成绩

select name, chinese from student where chinese between 120 and 126;

数学成绩是 10 或者 100 分的同学及数学成绩

select name, math from student where math in (100, 10);

查询名字中有“花”的学生（工作中慎用）

-- 模糊查询偏低，工作中慎用
select name, math from student where name like '%花%';

查询英语成绩为 NULL 的记录

-- 注意：不能用 =，只能用 <=> 或者 is 
select name, english from student where english is NULL;

Order By 排序

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

select * from student order by math desc, english asc, chinese asc;

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

select name, math + chinese + english as total from student
where math is not null and chinese is not null and english is not null
order by total desc;

分页查询

查询总分倒数的三名同学

select name, math + english + chinese as total from student
where math is not null and chinese is not null and english is not null
order by total asc limit 3 offset 0;

U: update（更新）

一定要添加条件后再进行修改，不然就是危险操作

将 id 为 3 的小花同学的数学成绩变更为 60 分，语文成绩变更为 70 分

update student set math = 60, chinese = 70 where id = 3 and `name` = '小花';

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

-- 先查询一下
select name, math + english + chinese  from student
where math is not null and chinese is not null and english is not null
order by (math + english + chinese) asc limit 3 offset 0;

-- 然后更新，注意 update 与 delete 中 limit 后面不能跟偏移量
-- AI 说
--  “UPDATE ... LIMIT row_count 的主要作用是防止一次性锁定或修改过多数据（常用于大表的数据分批更新）。
--  MySQL 设计者认为，在修改数据时使用 OFFSET 容易引发不可预期的结果和主从同步问题。
--  由于数据在不断被修改，跳过前 N 行再去修改后面的行，其结果在并发环境下是非常不可靠的。” 
update student set math = math + 30
where math is not null and chinese is not null and english is not null
order by (math + english + chinese) asc limit 3;

D: delete（删除）

与 update 一样，一定要添加条件后再进行修改，不然就是危险操作

删除大明同学的考试成绩

delete from student where `name` = '大明';

其他内容

插入查询结果

-- 创建一模一样的新表
drop table if exists student2;
create table if not exists student2 (
  stu2_id int,
  age int,
  name varchar(20),
  math int,
  chinese int,
  english int
);

-- 名称可以不一样，但是类型必须要一样
insert into student2(math, chinese, english) 
select chinese, english, math from student;

select * from student2;

聚合查询

函数

函数	说明
count()	统计执行结果的个数，结果为 NULL 的自动忽略
sum()	求和
max()	求最大值
min()	求最小值
avg()	求平均值

Group by 分组查询

表结构创建：

drop table if exists emp;
create table if not exists emp (
  id int,
  name varchar(20),
  -- 这个应该使用 int 通过联合其他表来表示，但是没学到，就暂时先这样了
  role varchar(20), 
  salary decimal(10, 2)
);

insert into emp values 
  (1, '大明', 'CEO', 12000000),
  (2, '小明', '开发', 19100),
  (3, '小美', '开发', 17300),
  (4, '小王', '测试', 19200),
  (5, '小花', '测试', 16200);

统计每个角色的平均工资，最高工资，最低工资

select role, 
  avg(salary) as '平均工资',
  max(salary) as '最高工资', 
  min(salary) as '最低工资'
from emp group by role;

排除小花后，获取到平均工资小于 20000 的岗位

select role, avg(salary) as avg from emp 
where name != '小花'
group by role having avg < 20000;

总结

1. where 中可以用表达式，但是不能用别名
2. and 优先级大于 or ，但是一般情况下建议用 () 表示优先执行的部分
3. 想要筛选出 NULL ，那么需要使用 is 或者 <=>
4. NULL 与任何值运算结果都是 NULL
5. count() 不会统计为 NULL 的值，count(*) 除外