一条 select 在源码里从左往右写,执行时却从 from 开始。逻辑处理顺序是:

from(含 join / on) → where → group by → having → select → distinct → order by → limit

这条顺序能解释三件常被写错的事:where 里为什么不能用 select 起的别名,聚合结果为什么不能直接放进 where,order by 为什么反而能看见别名。课件末尾把它写成 from > on > join > where > group by > with > having > select > distinct > order by > limit,其中 with 放错了位置:CTE 在 from 之前就算好,之后作为一张临时表参与查询;on 和 join 是同一个连接阶段,不该拆成两步。另外,这是逻辑顺序,不是物理执行顺序,优化器会重排,但最终结果必须等价于这个顺序。

建表、插入,以及撞上主键时的两种处理

create database if not exists query_demo default character set utf8mb4;
use query_demo;
 
create table students (
    id   int unsigned primary key auto_increment,
    sn   int not null unique comment '学号',
    name varchar(20) not null,
    qq   varchar(20)
);
 
insert into students values (100, 10000, '唐三藏', null);
insert into students values (101, 10001, '孙悟空', '11111');
 
insert into students (id, sn, name) values
(102, 20001, '曹孟德'),
(103, 20002, '孙仲谋');

全列插入时,值的个数和顺序必须与表定义一致;指定列插入时,值的个数和顺序必须与列清单一致,没写的列按默认值或 NULL 补。id 可以完全不写,交给自增。

学号 20001 已经被曹孟德占了,再插一次会撞唯一键:

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 = '唐大师';

它影响的行数分三种:0 表示撞了键但新值和旧值一样,1 表示没有冲突、纯插入,2 表示撞了键且确实改了。row_count() 能读回这个数,做幂等写入时很有用。

replace into 看起来像另一种“有则更新”,语义完全不同:没冲突就插入,冲突就删掉整行再插一条新的。课件里 replace into students (sn, name) values (20001, '曹阿瞒') 之后,原来 id 为 102 的那行没了,新行由自增分配一个新的 id。所以它返回 2 行受影响,自增主键会变,触发器会分别触发 DELETE 和 INSERT。只改几个字段、想保住原主键,用 on duplicate key update;整行换新、不在乎主键变化,才用 replace。

select 的列:表达式、别名与去重

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

select * 取全部列;指定列时顺序不必和建表顺序一致,select id, name, english 就只取三列。* 的问题不在写法,在代价:列越多传输的数据越多,查询需要的列又刚好能被索引覆盖时,* 会把覆盖索引退化成回表。

投影的不一定是列,也可以是表达式:

select id, name, 10 from exam_result;                       -- 常量
select id, name, english + 10 from exam_result;             -- 单列运算
select id, name, chinese + math + english from exam_result; -- 多列运算

表达式可以起别名,as 可省略:

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

别名只在 select 之后才存在,所以 where 里不能用它,order by 和 having 里可以(后两个是 MySQL 的扩展,标准 SQL 只保证 order by 能用)。

distinct 去掉结果里的重复行,作用范围是整条 select 列表,不是某一列:

select distinct math from exam_result;

如果写 select distinct chinese, math,判重看的是 (chinese, math) 这个组合,两列都相同才算重复。NULL 在 distinct 里算同一个值,多行 NULL 只会留下一行。

where:比较、范围、模糊匹配,以及 NULL 的特殊规则

课件列出的运算符:

运算符说明
>, >=, <, <=大小比较
=等于,NULL 不安全,null = null 结果是 NULL
<=>等于,NULL 安全,null <=> null 结果是 1
!=, <>不等于
between a and b闭区间,a <= value <= b
in (值列表)命中其中任意一个
is null / is not null判断空值,唯一可靠的判空方式
like模糊匹配,% 任意多个字符,_ 恰好一个字符

几个例子:

-- 英语不及格
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 '孙_';

between 是闭区间,等价于 >= a and <= b;两端写反了不会报错,只会一行都查不到。not between 是整个条件的取反,意思是“小于 a 或大于 b”,不是“把两端去掉”。in 与一长串 or 等价,优化器通常也会把它改写成索引范围查找。

like 的 _ 匹配一个字符而不是一个字节,utf8mb4 下一个汉字算一个字符,所以 '孙_' 能匹配“孙权”,不能匹配“孙悟空”。模式以 % 开头时,前面的固定前缀没了,索引基本用不上,会退化成全表扫描。大小写是否敏感取决于列的排序规则,默认的 utf8mb4_0900_ai_ci 不区分大小写。

where 的比较符两侧都可以是列:

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

where 里也能写表达式,但不能写别名:

select name, chinese + math + english 总分 from exam_result
where chinese + math + english < 200;   -- 这里必须重写表达式

and 的优先级高于 or,所以课件这条语句里的括号加不加,语义一样:or 右边的一整串 and 会先算完。

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

真正会出错的是反过来的写法。(A or B) and C 去掉括号会变成 A or (B and C),筛选范围从“两个条件同时满足”变成“满足 A,或者满足 B 且 C”,结果悄悄改变,而且不报错。or 和 and 混用时,括号该写就写。

NULL 的查询要单独记:

select null = null, null = 1, null = 0;      -- NULL, NULL, NULL
select null <=> null, null <=> 1, null <=> 0; -- 1, 0, 0
 
select name, qq from students where qq is not null;

= NULL 的结果永远不是真,所以 where qq = null 一行都查不到;要判空只能用 is null。<=> 是 NULL 安全的等于,两边都为 NULL 时返回 1。

in 和 not in 会把这个坑放大。x in (1, 2, null) 在 x 不等于 1 也不等于 2 时,结果不是假,而是 UNKNOWN;x not in (1, 2, null) 则对每一行都给不出真,整条查询被筛空。子查询里可能出现 NULL 时,先过滤掉,或者改用 not exists。

exists (子查询) 只问子查询有没有返回行,不比较具体值;not exists 用来表达“不存在”,而且不像 not in 那样被 NULL 拖成空集。判断“有没有”时,它比“先连接再 distinct”更直接,命中一行就能停。

order by 与 limit

order by 默认升序,desc 降序;多列排序时,前一个键相等才看后一个键,每个键的方向各自独立:

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

它后面可以接表达式,也可以接 select 里的别名:

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

MySQL 把 NULL 当成最小值:升序时排在最前,降序时排在最后。要让空值永远沉底,别用 desc 赌,显式排:

select name, qq from students order by qq is null, qq;

qq is null 对空行返回 1、非空行返回 0,先按这个升序排,空值自然到最后。

没有 order by 的查询,返回顺序是未定义的。索引变化、页分裂、并行扫描都可能让顺序变,分页、取最新一条、取第一名,凡是依赖顺序的地方都得显式写。

分页有三种写法:

select id, name from exam_result order by id limit 3;          -- 前 3 条
select id, name from exam_result order by id limit 3, 3;       -- 跳过 3 条取 3 条
select id, name from exam_result order by id limit 3 offset 3; -- 同上,语义更清楚

偏移量从 0 开始,第 K 页、每页 N 条,偏移量是 (K-1)*N。页号从 1 数、偏移量从 0 数,这两个起点不同,很容易差一页。排序键有重复值时,边界上的行可能重复或漏掉,加一个唯一键做决胜键,例如 order by math desc, id asc。深分页 limit 100000, 10 要先扫掉前十万行,数据量大时改用游标分页:where id > 6 order by id limit 3,下一页再把 6 换成上一页最后一条的 id。对不熟悉的表先 limit 1 看一眼,也别在线上随手 select * 扫全表。

改、删、截断,以及用查询结果插入

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

update 的 set 里可以用原值参与运算,math = math + 30 是合法的,math += 30 不是。把总分倒数三名的数学加 30 分:

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

这里不能写 order by 总分:UPDATE 没有 select 列表,别名不存在,只能把表达式重写一遍。update 不带 where 会改全表,执行前先用同样的条件 select 一遍确认范围。

再比如把所有同学的语文成绩翻倍:

update exam_result set chinese = chinese * 2;

delete 也支持 order by 和 limit,同样慎用不带 where 的写法:

delete from exam_result where name = '孙悟空';
-- delete from exam_result;   -- 清空整表,执行后后面的示例就没有数据了

delete 逐行删、记录事务日志、可以回滚,但不重置自增计数器。课件里 for_delete 删光后插入 D,拿到的 id 是 4,show create table 里还留着 AUTO_INCREMENT=5。

truncate 是另一回事:

create table for_truncate (
    id   int primary key auto_increment,
    name varchar(20)
);
 
insert into for_truncate (name) values ('A'), ('B'), ('C');
truncate table for_truncate;
 
insert into for_truncate (name) values ('D');   -- 拿到 1,不是 4

它只能整表操作,不能带 where;它是 DDL,隐式提交,不能回滚;它重置 AUTO_INCREMENT,删完再插 id 从 1 开始;返回的“影响行数”是 0,因为 InnoDB 实际做的是丢弃并重建表,而不是逐行删除。所以它比 delete 快,但也更不可逆。

用查询结果插入可以完成“去重换表”:

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;

insert 接 select 时,列按位置对齐,不按名字。源表和目标表列顺序不一致时,数据会错位塞进去,而且不报错,显式写列名更稳。rename table 一条语句换两张表,换名过程对外是原子的。

聚合函数:count(*)、count(1)、count(列)

函数作用空集时返回对 NULL
count(*)行数0不跳过,数的是行
count(1)行数0与 count(*) 等价
count(expr)非 NULL 值的个数0跳过
sum(expr)求和NULL跳过
avg(expr)平均NULL跳过,分母是非 NULL 行数
max / min极值NULL跳过

count(*) 和 count(1) 数的是行,NULL 不影响它们;count(列) 只数这一列非 NULL 的值。students 表里只有孙悟空填了 qq,所以 count(*) 是 4,count(qq) 是 1。count(distinct math) 再去一层重,统计的是不同的数学分数个数。

在 InnoDB 里,count(*) 会挑一个最小的二级索引来扫,避免读整张聚簇索引;count(1) 的执行计划通常和它一样。count(列) 必须读那一列并判断 NULL,开销略大,但语义不同,不能为了快把 count(qq) 改成 count(*)。扫描对象是索引而不是数据行,并不意味着它免费:没有 where 的 count(*) 在大表上仍要遍历整棵索引。

avg 的分母不是总行数,是有值的行数。同样叫平均分,“参考者平均”和“按全班人数摊平”是两个口径,报表上要写清楚。一组里全是 NULL 时,sum 和 avg 返回 NULL,不是 0;要显示 0,得用 ifnull(sum(math), 0) 兜一下。

max / min 并不要求数值。字符串按排序规则比较,日期按时间先后比较,取字典序最大的姓名、最晚的入职日期都有明确意义。真正要求数值语义的是 sum 和 avg,字符串传进去会按数值转换,结果不可靠。课件把 max / min 也标成“不是数字没有意义”,这句写反了。

用最初那七行看两个例子:select max(english) from exam_result 得到 90,英语最高分;select min(math) from exam_result where math > 70 先过滤再取最小,得到 73。

课件到聚合这一节时,exam_result 已经被前面的 UPDATE 和 DELETE 改过:孙悟空那行删了,数学成绩从 78 改成 80,所以 count(math) 是 6、sum(math) 是 569。拿最初那七行算,是 7 和 581。两组数都对,差的是表的版本,不是课件算错。

group by 与 having:分组看组合,过滤看组

create table staff (
    id     int primary key auto_increment,
    name   varchar(20) not null,
    deptno int not null,
    job    varchar(20) not null,
    sal    int not null
);
 
insert into staff (name, deptno, job, sal) values
('张三', 10, 'MANAGER',  5000),
('李四', 10, 'CLERK',    2450),
('王五', 20, 'MANAGER',  2975),
('赵六', 20, 'ANALYST',  3000),
('孙七', 20, 'CLERK',    1100),
('周八', 30, 'MANAGER',  2850),
('吴九', 30, 'SALESMAN', 1250),
('郑十', 30, 'CLERK',     950);

按部门分组,求平均工资和最高工资:

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

按“部门 + 岗位”分组,求平均和最低:

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

多列分组看的是组合,(10, 'MANAGER') 和 (20, 'MANAGER') 是两个组。分组只对出现过的值成立,没有员工的部门不会凭空出现一组。

having 过滤的是组,where 过滤的是行:

select deptno, avg(sal) as avg_sal
from staff
group by deptno
having avg_sal < 2000;

where 在执行顺序里排在 group by 之前,那时还没有组,所以 where avg(sal) < 2000 会报 1111;聚合结果只能放 having。别名在 having 里能用,这是 MySQL 的扩展。

MySQL 5.7.5 起默认开启 ONLY_FULL_GROUP_BY,select 里只能出现分组列或被聚合函数包住的列:

select deptno, name, avg(sal) from staff group by deptno;
-- ERROR 1055 (42000):非分组列不能出现在 select 列表里

确实不在乎组内是哪一行时,用 any_value(name) 明说。MySQL 8.0 还移除了 group by 的隐式排序,要顺序必须自己写 order by。