MySQL 的 CRUD 重点不在记住语法本身,而在细节处理:插入支持单行、多行、冲突更新和查询结果导入;查询需要掌握 where、order by、limit、聚合和 group by/having 的配合;更新和删除最怕范围失控,必须先确认条件;分页要带排序,null 判断要用 is null/is not null,truncate 虽快但不可回滚。
-- 主键冲突(id=100 已存在),执行更新insert into students (id, sn, name) values (100, 10010, '唐大师')
on duplicate key update sn =10010, name ='唐大师';
-- 唯一键冲突(sn=20001 已存在),执行更新insert into students (sn, name) values (20001, '曹阿瞒')
on duplicate key update name ='曹阿瞒';
-- 查询姓名、语文、数学成绩select name, chinese, math from exam_result;
3.1.3 查询表达式
可以查常量,也可以做简单运算。
-- 常量表达式select id, name, 10from exam_result;
-- 单字段运算(英语成绩 +10)select id, name, english +10from exam_result;
-- 多字段运算(总分)select id, name, chinese + math + english from exam_result;
3.1.4 结果别名
别名主要是为了让结果更好读,尤其是表达式列。
select id, name, chinese + math + english as 总分 from exam_result;
3.1.5 去重
-- 去重查询数学成绩 distinctselectdistinct math from exam_result;
3.2 条件查询:where
where 负责过滤行。比较运算符和逻辑运算符组合起来,基本够应付大多数场景。
运算符
说明
>, >=, <, <=
大于、大于等于、小于、小于等于
=
等于(null 不安全,null = null 结果为 null)
<=>
等于(null 安全,null <=> null 结果为 1)
!=, <>
不等于
between a and b
范围匹配([a, b])
in (option...)
匹配选项中的任意一个
is null
为空
is not null
不为空
like
模糊匹配(% 匹配任意字符,_ 匹配单个字符)
运算符
说明
AND
多个条件同时成立
OR
任意一个条件成立
NOT
条件取反
-- 1. 英语不及格(<60)select name, english from exam_result where english <60;
-- 2. 语文成绩在 [80, 90] 之间(between...and)select name, chinese from exam_result where chinese between80and90;
-- 3. 数学成绩是 58、59、98、99 中的一个(in)select name, math from exam_result where math in (58, 59, 98, 99);
-- 4. 姓孙的同学(like %)select name from exam_result where name like'孙%';
-- 5. 姓名是两个字且姓孙(like _)select name from exam_result where name like'孙_';
-- 6. 语文成绩好于英语成绩select name, chinese, english from exam_result where chinese > english;
-- 7. 总分低于 200 分(表达式作为条件)select name, chinese + math + english as 总分 from exam_result where chinese + math + english <200;
-- 不能直接用总分 < 20,因为执行这里的时候还没执行前面的部分-- 8. 语文>80 且不姓孙(and + not)select name, chinese from exam_result where chinese >80and name notlike'孙%';
-- 9. qq 号不为空(is not null)select name, qq from students where qq isnot null;
-- 10. null 安全比较(<=>)select name, qq from students where qq <=>null;
3.3 排序:order by
默认是升序,想倒过来就加 desc。多字段排序也很常用,尤其是先按主排序字段排,再用次级字段兜底。
-- 1. 按数学成绩升序select name, math from exam_result orderby math;
-- 2. 按数学降序,英语升序,语文升序select name, math, english, chinese from exam_result orderby math desc, english, chinese;
-- 3. 按总分降序(表达式排序)select name, chinese + math + english as 总分 from exam_result orderby 总分 desc;
-- 4. null 排序(null 视为最小值,升序在最前)select name, qq from students orderby qq;
select name, qq from students orderby qq desc;
3.4 分页:limit
分页一定要配合排序用,不然每次返回的顺序都可能不一样,翻页结果会飘。
-- 语法 1:limit 条数(取前 n 条)select*from exam_result orderby id limit 3;
-- 语法 2:limit 起始下标,条数(从 s(下标从 0 开始) 开始取 n 条)select*from exam_result orderby id limit 3, 3;
-- 语法 3:limit 条数 offset 起始下标(推荐,更清晰)select*from exam_result orderby id limit 3offset6;
-- 分页示例:每页 3 条,第 1-3 页select*from exam_result orderby id limit 3offset0; -- 第 1 页select*from exam_result orderby id limit 3offset3; -- 第 2 页select*from exam_result orderby id limit 3offset6; -- 第 3 页
3.5 聚合查询
聚合函数是做统计的,写法不复杂,但要记得它们会忽略 null,这点常常影响结果判断。
函数
说明
count([distinct] expr)
统计记录数(distinct 去重)
sum([distinct] expr)
求和(仅数字类型)
avg([distinct] expr)
求平均值(仅数字类型)
max([distinct] expr)
求最大值(仅数字类型)
min([distinct] expr)
求最小值(仅数字类型)
-- 1. 统计学生总数(count(*)不受 null 影响)selectcount(*) as 学生总数 from students;
-- 2. 统计 qq 号非空的学生数(null 不计入)selectcount(qq) as qq 已收集人数 from students;
-- 3. 统计数学成绩总分和去重后总分selectsum(math) as 数学总分, sum(distinct math) as 去重数学总分 from exam_result;
-- 4. 统计语文成绩平均分selectavg(chinese) as 语文平均分 from exam_result;
-- 5. 英语最高分和最低分selectmax(english) as 英语最高分, min(english) as 英语最低分 from exam_result;
-- 6. 统计 70 分以上的数学最低分selectmin(math) as70+ 数学最低分 from exam_result where math >70;
3.6 分组查询:group by + having
group by 是先分组,having 是在分组之后再筛一遍。这个顺序别搞反了,where 不能直接替代 having。
-- 1. 更新单字段(孙悟空数学成绩改为 80)update exam_result set math =80where name ='孙悟空';
-- 2. 更新多字段(曹孟德数学 60、语文 70)update exam_result set math =60, chinese =70where name ='曹孟德';
-- 3. 按表达式更新(所有同学语文成绩翻倍)update exam_result set chinese = chinese *2;
-- 4. 结合排序和 limit(总分倒数前三的同学数学 +30)update exam_result set math = math +30orderby chinese + math + english limit 3;
-- 5. 条件更新(英语<60 的同学英语 +10)update exam_result set english = english +10where english <60;