MySQL 基本查询（6）

目录

• • 1. CRUD 概述
  • 2. Create：插入数据
  • • 2.1 创建学生表
    • 2.2 单行 + 全列插入
    • 2.3 多行 + 指定列插入
    • 2.4 插入否则更新：ON DUPLICATE KEY UPDATE
    • 2.5 替换：REPLACE
  • 3. Retrieve：查询数据
  • • 3.1 准备考试成绩表
    • 3.2 SELECT 列
    • • 3.2.1 全列查询
      • 3.2.2 指定列查询
      • 3.2.3 查询字段为表达式
      • 3.2.4 为查询结果指定别名
      • 3.2.5 结果去重
    • 3.3 WHERE 条件
    • 3.4 ORDER BY 排序
    • 3.5 LIMIT 分页
  • 4. Update：更新数据
  • 5. Delete：删除数据
  • 6. 插入查询结果与去重
  • 7. 聚合函数
  • 8. GROUP BY 与 HAVING
  • 9. 实战 OJ 与执行顺序

在 MySQL 中，增删改查（CRUD）是数据库操作的核心。本文从实战角度出发，带你系统掌握 INSERT、SELECT、UPDATE、DELETE 等基本查询，配合大量案例与执行结果，让你的数据操作坚如磐石！

1. CRUD 概述

CRUD 指数据库中最基础的四种操作：

下面以学生表和考试成绩表为例，逐步展开。

2. Create：插入数据

2.1 创建学生表

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 单行 + 全列插入

插入时 values 的数量必须与表中列的数量、顺序一致。

insert into students values (100, 10000, '唐三藏', null);
insert into students values (101, 10001, '孙悟空', '11111');

查看插入结果：

select * from students;

+—–+——-+——–+——-+
| id | sn | name | qq |
+—–+——-+——–+——-+
| 100 | 10000 | 唐三藏 | NULL |
| 101 | 10001 | 孙悟空 | 11111 |
+—–+——-+——–+——-+
2 rows in set (0.00 sec)

如果 id 是自增主键，也可以不指定 id，但需要明确插入哪些列：

insert into students (sn, name, qq) values (10002, '猪悟能', null);

2.3 多行 + 指定列插入

insert into students (sn, name) values
(20001, '曹孟德'),
(20002, '孙仲谋');

查看结果：

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.4 插入否则更新：ON DUPLICATE KEY UPDATE

当主键或唯一键冲突时，可以选择更新已有记录：

insert into students (id, sn, name)
values (100, 10010, '唐大师')
on duplicate key update sn = 10010, name = '唐大师';

影响行数含义：

• 0 row affected：冲突数据存在，但更新值与原值相同；
• 1 row affected：没有冲突，插入成功；
• 2 row affected：有冲突，并且执行了更新。

可通过 ROW_COUNT() 查看：

select row_count();

2.5 替换：REPLACE

replace into students (sn, name) values (20001, '曹阿瞒');

• 1 row affected：无冲突，直接插入；
• 2 row affected：有冲突，删除旧记录后重新插入。

3. Retrieve：查询数据

3.1 准备考试成绩表

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);

3.2 SELECT 列

3.2.1 全列查询

select * from exam_result;

通常不建议使用 select *，原因是：

3.2.2 指定列查询

select id, name, english from exam_result;

指定列的顺序不需要按定义表的顺序来。

3.2.3 查询字段为表达式

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

3.2.4 为查询结果指定别名

select id, name, chinese + math + english as 总分 from exam_result;

3.2.5 结果去重

select distinct math from exam_result;

3.3 WHERE 条件

常用比较运算符：

逻辑运算符：

• and：多个条件都成立；
• or：任意一个条件成立；
• not：条件取反。

示例：

— 英语不及格
select name, english from exam_result where english < 60;

— 语文成绩在 [80, 90]
select name, chinese from exam_result
where chinese between 80 and 90;

— 数学成绩是 58、59、98、99
select name, math from exam_result
where math in (58, 59, 98, 99);

— 姓孙的同学
select name from exam_result where name like '孙%';

— 孙某同学，严格两个字符
select name from exam_result where name like '孙_';

— 语文高于英语
select name, chinese, english from exam_result
where chinese > english;

NULL 比较：= 与 <=>

select null = null, null = 1, null = 0;
— 结果都是 NULL

select null <=> null, null <=> 1, null <=> 0;
— 结果：1, 0, 0

因此判断 NULL 应使用：

select name, qq from students where qq is not null;

3.4 ORDER BY 排序

select name, math from exam_result order by math;

• 默认 asc 升序；
• desc 为降序；
• NULL 视为比任何值都小；
• 没有 order by 时，返回顺序未定义。

多字段排序：

select name, math, english, chinese
from exam_result
order by math desc, english, chinese;

使用别名排序：

select name, chinese + english + math as 总分
from exam_result
order by 总分 desc;

3.5 LIMIT 分页

— 从第 0 条开始，取 3 条
select id, name, math, english, chinese
from exam_result
order by id
limit 3 offset 0;

— 从第 3 条开始，取 3 条
select id, name, math, english, chinese
from exam_result
order by id
limit 3 offset 3;

建议查询未知大表时加 limit 1，避免全表扫描导致数据库压力过大。

4. Update：更新数据

语法：

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

示例：

update exam_result set math = 80 where name = '孙悟空';

update exam_result
set math = 60, chinese = 70
where name = '曹孟德';

更新时基于原值计算：

update exam_result
set math = math + 30
order by chinese + math + english
limit 3;

注意：MySQL 不支持 math += 30 这种写法。

没有 where 时会更新全表，务必谨慎：

update exam_result set chinese = chinese * 2;

5. Delete：删除数据

语法：

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

删除指定记录：

delete from exam_result where name = '孙悟空';

删除整表：

delete from for_delete;

注意：delete 删除全表后，自增主键不会重置。再次插入时，auto_increment 会继续增长。

如果需要重置自增，可使用：

truncate table for_truncate;

但 truncate 属于更危险的操作，会清空整表并重置自增，使用前需确认。

6. 插入查询结果与去重

语法：

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

案例：删除重复记录，只保留一份。

create table duplicate_table (id int, name varchar(20));

insert into duplicate_table values
(100, 'aaa'), (100, 'aaa'),
(200, 'bbb'), (200, 'bbb'), (200, 'bbb'),
(300, 'ccc');

— 创建结构相同的空表
create table no_duplicate_table like duplicate_table;

— 插入去重后的数据
insert into no_duplicate_table
select distinct * from duplicate_table;

— 重命名表，实现原子替换
rename table duplicate_table to old_duplicate_table,
no_duplicate_table to duplicate_table;

7. 聚合函数

示例：

select count(*) from students;
select count(qq) from students; — NULL 不计入
select count(distinct math) from exam_result;

select sum(math) from exam_result;
select avg(chinese + math + english) as 平均总分 from exam_result;
select max(english) from exam_result;
select min(math) from exam_result where math > 70;

8. GROUP BY 与 HAVING

group by 用于分组查询。

select deptno, avg(sal), max(sal)
from emp
group by deptno;

select deptno, job, avg(sal), min(sal)
from emp
group by deptno, job;

having 用于对分组结果过滤：

select avg(sal) as myavg
from emp
group by deptno
having myavg < 2000;

where 过滤行，having 过滤分组，二者常配合 group by 使用。

9. 实战 OJ 与执行顺序

常见 OJ 题目：

• 牛客：批量插入数据
• 牛客：找出所有员工当前薪水，相同薪水只显示一次，逆序显示
• 牛客：查找最晚入职员工的所有信息
• 牛客：查找入职时间排名倒数第三的员工信息
• 牛客：查找薪水涨幅超过 15 次的员工号及涨幅次数
• LeetCode：duplicate-emails、big-countries、nth-highest-salary

面试常见问题：SQL 查询中关键字的执行先后顺序大致为：

from > on > join > where > group by > with > having > select > distinct > order by > limit

理解执行顺序有助于分析别名、聚合、排序和过滤条件为什么能或不能在某些位置使用。