SQL 基础

面向 MySQL 8.0 / InnoDB。这一篇解决三件事：SQL 到底按什么顺序执行、NULL 为什么这么反直觉、以及日常 90% 场景要用到的语法与函数。

一、语句分类

分类	全称	代表语句
DDL	Data Definition Language	CREATE / ALTER / DROP / TRUNCATE
DML	Data Manipulation Language	INSERT / UPDATE / DELETE
DQL	Data Query Language	SELECT
DCL	Data Control Language	GRANT / REVOKE
TCL	Transaction Control Language	BEGIN / COMMIT / ROLLBACK / SAVEPOINT

DDL 会隐式提交

MySQL 中绝大多数 DDL 会触发隐式提交（implicit commit），把当前事务提前结束掉，因此不要把 CREATE TABLE 之类的语句夹在业务事务中间。另外 MySQL 8.0 只对 DROP TABLE / CREATE TABLE 等少数操作提供了原子 DDL，ALTER TABLE 期间仍会持有 MDL 锁。

二、SELECT 的逻辑执行顺序 ⭐⭐⭐

书写顺序和执行顺序完全不同，这是理解一切 SQL 行为的地基：

SELECT   DISTINCT column, agg(...)   -- 5(SELECT) → 6(DISTINCT)
FROM     t1                          -- 1
JOIN     t2 ON t1.a = t2.a           -- 1
WHERE    ...                         -- 2
GROUP BY ...                         -- 3
HAVING   ...                         -- 4
ORDER BY ...                         -- 7
LIMIT    ...                         -- 8

完整的逻辑顺序：

FROM / JOIN  →  ON  →  WHERE  →  GROUP BY  →  HAVING
             →  窗口函数  →  SELECT  →  DISTINCT  →  ORDER BY  →  LIMIT

由此可以直接推出四条实用结论：

1. WHERE 里不能使用 SELECT 中定义的别名——执行到 WHERE 时别名还不存在。

   -- ❌ Unknown column 'total' in 'where clause'
   SELECT amount * 2 AS total FROM orders WHERE total > 100;
   -- ✅
   SELECT amount * 2 AS total FROM orders WHERE amount * 2 > 100;
   

2. ORDER BY 可以使用别名，因为它在 SELECT 之后执行。（GROUP BY / HAVING 中使用别名是 MySQL 的扩展，标准 SQL 不允许，跨库迁移时要注意。）
3. WHERE 过滤行，HAVING 过滤组。WHERE 里不能出现聚合函数，HAVING 可以。能写在 WHERE 的条件就不要写到 HAVING，越早过滤参与后续计算的数据越少。
4. 窗口函数在 HAVING 之后、ORDER BY 之前求值，所以窗口函数不能出现在 WHERE / GROUP BY / HAVING 中，要过滤只能再套一层子查询或 CTE。

逻辑顺序 ≠ 物理顺序

上面描述的是 SQL 标准定义的语义。真正执行时优化器会做谓词下推、连接重排、LIMIT 提前终止等变换，只要结果集等价即可。理解语义用于写对 SQL，理解物理执行用于写快 SQL。

三、数据类型选型要点

类型	说明与坑
INT / BIGINT	INT(11) 中的 11 只是显示宽度，不限制存储范围，8.0.17 起已废弃。范围由 INT（4 字节）本身决定
DECIMAL(M, D)	金额必须用它。FLOAT / DOUBLE 是二进制浮点，0.1 + 0.2 != 0.3，禁止用于金额与等值比较
CHAR(N)	定长，存储时右侧补空格，读取时去掉尾部空格；比较时按 PAD SPACE 规则忽略尾部空格。适合定长值：MD5、状态码
VARCHAR(N)	变长，额外 1~2 字节记录长度。N 是字符数不是字节数
DATETIME	5 字节 + 小数秒 0~3 字节（5.6.4 起；更早版本固定 8 字节），范围 1000-01-01 ~ 9999-12-31，不随时区变化
TIMESTAMP	4 字节，范围 1970-01-01 ~ 2038-01-19，存 UTC，读写按会话时区转换
ENUM / SET	内部存整数。新增枚举值需要 ALTER TABLE，业务易变时优先用 TINYINT + 字典表
TEXT / BLOB	不能有默认值，索引必须指定前缀长度。大字段建议垂直拆表
JSON	8.0 支持函数索引 / 多值索引，但不要拿它替代正经的表设计

字符集统一用 utf8mb4（真正的 4 字节 UTF-8，支持 emoji）；MySQL 里的 utf8 是 utf8mb3 别名，最多 3 字节，属于历史遗留。

四、NULL 与三值逻辑 ⭐⭐⭐

SQL 的逻辑是 TRUE / FALSE / UNKNOWN 三值逻辑。NULL 表示"未知"，任何与 NULL 的比较结果都是 UNKNOWN，而 WHERE 只保留结果为 TRUE 的行。

SELECT NULL = NULL;    -- NULL（不是 1）
SELECT NULL <> 2;      -- NULL（不是 1）
SELECT NULL <=> NULL;  -- 1，<=> 是 MySQL 的 NULL 安全等值比较
SELECT 1 + NULL;       -- NULL，任何算术运算遇 NULL 即 NULL

因此判空必须用 IS NULL / IS NOT NULL。

经典陷阱：NOT IN 遇上 NULL。

-- 若子查询结果里含有 NULL，这条语句永远返回空集
SELECT * FROM employee
WHERE id NOT IN (SELECT manager_id FROM employee);

原因：id NOT IN (1, 2, NULL) 等价于 id <> 1 AND id <> 2 AND id <> NULL，最后一项恒为 UNKNOWN，整个 AND 最多也只能是 UNKNOWN，永远不为 TRUE。解决方式：子查询中过滤掉 NULL，或改用 NOT EXISTS（NOT EXISTS 不受 NULL 影响，见子查询篇）。

其他与 NULL 相关的规则，记住这张表就够了：

场景	行为
COUNT(*)	统计行数，包含全 NULL 行
COUNT(col)	跳过 col IS NULL 的行
SUM / AVG / MAX / MIN	忽略 NULL；AVG 的分母是非 NULL 行数
GROUP BY	所有 NULL 归为同一组
ORDER BY	MySQL 把 NULL 视为最小值，ASC 时排最前（PostgreSQL 默认相反，可用 NULLS FIRST/LAST 指定）
DISTINCT / UNION	多个 NULL 被视为相等，会去重
UNIQUE 索引	允许多行同时为 NULL（因为 NULL != NULL）

处理函数：

IFNULL(expr, default)        -- expr 为 NULL 时返回 default（MySQL）
COALESCE(a, b, c, ...)       -- 返回第一个非 NULL 值（标准 SQL，推荐）
NULLIF(a, b)                 -- a = b 时返回 NULL，否则返回 a，常用于避免除零
SELECT total / NULLIF(cnt, 0) FROM t;  -- cnt 为 0 时得到 NULL 而不是报错

五、过滤：WHERE 与谓词

-- 比较：=  <>(!=)  >  >=  <  <=  <=>
-- 范围：BETWEEN 是闭区间 [a, b]
SELECT * FROM orders WHERE amount BETWEEN 100 AND 200;   -- 含 100 和 200

-- 集合
SELECT * FROM orders WHERE status IN (1, 2);

-- 模糊匹配：% 任意个字符，_ 恰好一个字符
SELECT * FROM employee WHERE name LIKE 'J%';      -- 可用索引（前缀匹配）
SELECT * FROM employee WHERE name LIKE '%son';    -- 左模糊，B+ 树索引失效
SELECT * FROM t WHERE path LIKE 'a\_%';           -- 转义，匹配以 "a_" 开头
SELECT * FROM t WHERE path LIKE 'a#_%' ESCAPE '#';-- 自定义转义符

-- 正则（8.0 起底层换为 ICU，支持 Unicode）
SELECT * FROM patients WHERE conditions REGEXP '^DIAB1|\\sDIAB1';

日期范围不要写函数

WHERE DATE(created_at) = '2024-01-01' 会让 created_at 上的索引失效（索引列被函数包裹）。改写成半开区间：

WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'

六、排序与分页

SELECT * FROM employee
ORDER BY department_id ASC, salary DESC   -- 多列排序，方向可各自指定
LIMIT 20 OFFSET 40;                       -- 等价于 LIMIT 40, 20

三个必须知道的点：

1. 没有 ORDER BY 就没有顺序保证。即使某次结果看起来有序，那也只是当前执行计划的副产品，换个索引就变了。
2. 排序不稳定。排序列有重复值时，行之间的相对顺序未定义；分页要稳定必须补一个唯一列兜底，如 ORDER BY salary DESC, id ASC。
3. LIMIT 1000000, 20 是性能杀手，MySQL 需要先扫描并丢弃前 100 万行。优化方案见深分页优化。

七、常用内置函数

字符串

CONCAT('a', 'b')                 -- 'ab'；任一参数为 NULL 则结果为 NULL
CONCAT_WS('-', 'a', NULL, 'c')   -- 'a-c'；用分隔符连接，自动跳过 NULL
LENGTH('中')                      -- 3，字节数
CHAR_LENGTH('中')                 -- 1，字符数
UPPER(s) / LOWER(s)
LEFT(s, n) / RIGHT(s, n)
SUBSTRING(s, pos, len)           -- pos 从 1 开始，可为负数（从右数）
TRIM(s) / LTRIM(s) / RTRIM(s)
REPLACE(s, from, to)
LOCATE(sub, s)                   -- 子串位置，找不到返回 0
LPAD(s, len, pad) / RPAD(...)

数值

ROUND(x, d)      -- 四舍五入到 d 位小数
CEIL(x) / FLOOR(x)
TRUNCATE(x, d)   -- 直接截断，不进位
ABS(x) / MOD(a, b) / POW(a, b) / SQRT(x)

日期时间

NOW()            -- 语句开始时间（同一条语句内多次调用值相同）
CURDATE() / CURTIME()
DATE(dt) / YEAR(dt) / MONTH(dt) / DAY(dt) / HOUR(dt) / WEEKDAY(dt)
DATE_FORMAT(dt, '%Y-%m-%d %H:%i:%s')     -- 注意分钟是 %i，不是 %m
STR_TO_DATE('2024-01-01', '%Y-%m-%d')
DATE_ADD(dt, INTERVAL 1 DAY) / DATE_SUB(dt, INTERVAL 3 MONTH)
DATEDIFF(d1, d2)     -- 相差天数（d1 - d2），只看日期部分
TIMESTAMPDIFF(HOUR, d1, d2)  -- 指定单位的差值（d2 - d1），注意方向与 DATEDIFF 相反
LAST_DAY(dt)         -- 当月最后一天

流程控制

-- CASE 简单形式
CASE sex WHEN 'm' THEN 'f' ELSE 'm' END
-- CASE 搜索形式（更常用，可写任意条件）
CASE WHEN salary >= 10000 THEN '高'
     WHEN salary >= 5000  THEN '中'
     ELSE '低' END
-- MySQL 专有简写
IF(cond, a, b)

八、DML 常用写法

-- 批量插入：一条语句远快于多条
INSERT INTO t (a, b) VALUES (1, 2), (3, 4), (5, 6);

-- 从查询结果插入
INSERT INTO t_backup (id, name) SELECT id, name FROM t WHERE created_at < '2024-01-01';

-- 主键/唯一键冲突时更新（upsert）
INSERT INTO stat (day, pv) VALUES ('2024-01-01', 1)
ON DUPLICATE KEY UPDATE pv = pv + 1;

-- 关联更新
UPDATE employee e
JOIN department d ON e.department_id = d.id
SET e.salary = e.salary * 1.1
WHERE d.name = 'Engineering';

-- 多表删除：删 a 表中匹配的行
DELETE a FROM employee a JOIN employee b
ON a.name = b.name AND a.id > b.id;

REPLACE INTO 的坑

REPLACE INTO 在冲突时是先 DELETE 再 INSERT，会导致：自增主键跳号、其他列被重置为默认值、外键级联删除被触发、产生更多的 binlog。需要 upsert 语义时请用 ON DUPLICATE KEY UPDATE。

DELETE / TRUNCATE / DROP 的区别：

	类型	是否可回滚	是否重置自增	是否触发触发器	速度
DELETE	DML	可（在事务中）	否	是	慢，逐行写 undo
TRUNCATE	DDL	否（隐式提交）	是	否	快，等价于重建表
DROP	DDL	否	—	否	快，表结构一并删除

九、集合运算

SELECT a FROM t1 UNION     SELECT a FROM t2;  -- 并集，去重（需排序/哈希，有开销）
SELECT a FROM t1 UNION ALL SELECT a FROM t2;  -- 并集，不去重，性能更好

MySQL 8.0.31 起支持 INTERSECT（交集）与 EXCEPT（差集）；更早的版本用 INNER JOIN 模拟交集、LEFT JOIN ... IS NULL 或 NOT EXISTS 模拟差集。

参与集合运算的各分支列数必须相同、类型需兼容，结果集列名取第一个分支的；ORDER BY / LIMIT 只能写在最后，作用于整体。

---

实战：LeetCode 基础题

选择

大的国家

👉 Leetcode 链接-595

World 表：

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| name        | varchar |
| continent   | varchar |
| area        | int     |
| population  | int     |
| gdp         | int     |
+-------------+---------+

name 是这张表的主键。 这张表的每一行提供：国家名称、所属大陆、面积、人口和 GDP 值。

如果一个国家满足下述两个条件之一，则认为该国是 大国 ：

• 面积至少为 300 万平方公里（即，3000000 km2）
• 或者人口至少为 2500 万（即 25000000）

编写一个 SQL 查询以报告 大国 的国家名称、人口和面积。按任意顺序返回结果表

条件查询

select
    name, population,area 
from
    World 
where 
    area >= 3000000 or population >= 25000000

联合查询

为什么 UNION 有时更快

OR 连接两个不同列上的条件时，优化器往往只能全表扫描；拆成两条单列条件的查询再 UNION，每一支都可以各自走自己的索引（这就是 index_merge 的思路）。代价是要做一次去重，数据量小时反而更慢。

select 
    name,population,area
from 
    World
where 
    area >= 3000000
union
select 
    name,population,area
from 
    World
where 
    population >= 25000000

寻找用户推荐人

👉 Leetcode 链接-584

给定表 customer ，里面保存了所有客户信息和他们的推荐人。

+------+------+-----------+
| id   | name | referee_id|
+------+------+-----------+
|    1 | Will |      NULL |
|    2 | Jane |      NULL |
|    3 | Alex |         2 |
|    4 | Bill |      NULL |
|    5 | Zack |         1 |
|    6 | Mark |         2 |
+------+------+-----------+

写一个查询语句，返回一个客户列表，列表中客户的推荐人的编号都 不是 2。

对于上面的示例数据，结果为：

+------+
| name |
+------+
| Will |
| Jane |
| Bill |
| Zack |
+------+

考点

三值逻辑。referee_id != 2 对 referee_id IS NULL 的行求值为 UNKNOWN，会被 WHERE 丢弃，所以必须显式补上 IS NULL 分支。

select
    name
from
    customer
where
    referee_id != 2
    or
    referee_id is null

也可以用 NULL 安全比较一步到位：

select name from customer where not (referee_id <=> 2);

从不订购的客户

👉 Leetcode 链接-183

某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。

Customers 表：

+----+-------+
| Id | Name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+

Orders 表：

+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+

例如给定上述表格，你的查询应返回：

+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

考点

反连接（anti join）的三种写法：NOT IN / LEFT JOIN ... IS NULL / NOT EXISTS。

not in

select 
    name as Customers
from
    Customers
where 
    id not in
(
    select
        customerId
    from 
        Orders 
)

前提

只有当 Orders.customerId 声明为 NOT NULL 时这种写法才安全，否则子查询里出现一个 NULL 就会让结果集整体为空（见上文三值逻辑）。生产代码建议统一用 NOT EXISTS。

left join

select 
    name as Customers
from
    Customers as a
left join
    Orders as b
on
    a.id = b.customerId
where
    b.customerId is null

not exists（推荐）

select
    name as Customers
from
    Customers c
where not exists (
    select 1 from Orders o where o.customerId = c.id
);

排序 & 修改

变更性别

👉 Leetcode 链接-627 Salary 表：

+-------------+----------+
| Column Name | Type     |
+-------------+----------+
| id          | int      |
| name        | varchar  |
| sex         | ENUM     |
| salary      | int      |
+-------------+----------+

id 是这个表的主键。 sex 这一列的值是 ENUM 类型，只能从 ('m', 'f') 中取。 本表包含公司雇员的信息。

请你编写一个 SQL 查询来交换所有的 'f' 和 'm' （即，将所有 'f' 变为 'm' ，反之亦然），仅使用 单个 update 语句 ，且不产生中间临时表。

注意，你必须仅使用一条 update 语句，且 不能 使用 select 语句。

考点

UPDATE 中使用条件表达式。SQL 的 UPDATE 是基于旧值整体求值的，不存在"先改成 f 又被改回 m"的问题。

# case&when
update
    salary
set 
    sex = case sex
        when 'm' then 'f'
        else 'm' 
    end

# if
update
    salary
set 
    sex=if(sex='m','f','m')

删除重复的电子邮箱 ⭐⭐

👉 Leetcode 链接-196

表: Person

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| email       | varchar |
+-------------+---------+

id 是该表的主键列。 该表的每一行包含一封电子邮件。电子邮件将不包含大写字母。

编写一个 SQL 删除语句来 删除 所有重复的电子邮件，只保留一个 id 最小的唯一电子邮件。

以任意顺序返回结果表。（注意：仅需要写删除语句，将自动对剩余结果进行查询）

查询结果格式如下所示。

输入: 
+----+------------------+
| id | email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+
输出: 
+----+------------------+
| id | email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+

解释: john@example.com重复两次。我们保留最小的 Id = 1。

考点

自连接 + 多表删除语法。DELETE p1 FROM ... 指明只删除 p1 这一侧匹配到的行。

delete
    p1
from
    Person p1, Person p2
where
    p1.email = p2.email and p1.id > p2.id

MySQL 的 1093 限制

不能在 DELETE / UPDATE 的子查询中直接引用被修改的同一张表：

-- ❌ ERROR 1093: You can't specify target table 'Person' for update
DELETE FROM Person WHERE id NOT IN (SELECT MIN(id) FROM Person GROUP BY email);

解法一是上面的自连接，解法二是给子查询套一层派生表（会物化成临时表，绕开限制）：

DELETE FROM Person WHERE id NOT IN (
    SELECT * FROM (SELECT MIN(id) FROM Person GROUP BY email) AS t
);

字符串处理函数/正则

修复表中的名字

👉 Leetcode 链接-1667

知识点

• concat() 函数连接多个字符串
• left(str, length) 从左开始截取字符串，length 是截取的长度
• upper & lower 大小写转换函数
• substring(str, start, len) 截取字符串，省略 len 表示取到末尾

编写一个 SQL 查询来修复名字，使得只有第一个字符是大写的，其余都是小写的。

返回按 user_id 排序的结果表。

查询结果格式示例如下。

Users table:
+---------+-------+
| user_id | name  |
+---------+-------+
| 1       | aLice |
| 2       | bOB   |
+---------+-------+
输出：
+---------+-------+
| user_id | name  |
+---------+-------+
| 1       | Alice |
| 2       | Bob   |
+---------+-------+

题解

# Write your MySQL query statement below
select
    user_id, 
    concat(upper(left(name,1)), lower(substring(name,2))) as name
from
    Users
order by
    user_id

按日期分组销售产品

👉 Leetcode 链接-1484

编写一个 SQL 查询来查找每个日期、销售的不同产品的数量及其名称。 每个日期的销售产品名称应按词典序排列。 返回按 sell_date 排序的结果表。 查询结果格式如下例所示

输入：
Activities 表：
+------------+-------------+
| sell_date  | product     |
+------------+-------------+
| 2020-05-30 | Headphone   |
| 2020-06-01 | Pencil      |
| 2020-06-02 | Mask        |
| 2020-05-30 | Basketball  |
| 2020-06-01 | Bible       |
| 2020-06-02 | Mask        |
| 2020-05-30 | T-Shirt     |
+------------+-------------+
输出：
+------------+----------+------------------------------+
| sell_date  | num_sold | products                     |
+------------+----------+------------------------------+
| 2020-05-30 | 3        | Basketball,Headphone,T-shirt |
| 2020-06-01 | 2        | Bible,Pencil                 |
| 2020-06-02 | 1        | Mask                         |
+------------+----------+------------------------------+

考点

count & distinct & group_concat & group by。GROUP_CONCAT 内部可以单独写 DISTINCT 与 ORDER BY，用 SEPARATOR 指定分隔符（默认逗号）。

select
    sell_date,
    count(distinct product) num_sold,
    group_concat(distinct product order by product separator ',') products
from 
    Activities
group by
    sell_date

GROUP_CONCAT 会被静默截断

结果长度受 group_concat_max_len 限制（默认 1024 字节），超长部分直接被截掉且默认只有 warning。生产环境使用前先 SET SESSION group_concat_max_len = 1024 * 1024;

患某种疾病的患者

👉 Leetcode 链接-1527

写一条 SQL 语句，查询患有 I 类糖尿病的患者 ID （patient_id）、患者姓名（patient_name）以及其患有的所有疾病代码（conditions）。I 类糖尿病的代码总是包含前缀 DIAB1 。

按 任意顺序 返回结果表。

查询结果格式如下示例所示。

输入：
Patients表：
+------------+--------------+--------------+
| patient_id | patient_name | conditions   |
+------------+--------------+--------------+
| 1          | Daniel       | YFEV COUGH   |
| 2          | Alice        |              |
| 3          | Bob          | DIAB100 MYOP |
| 4          | George       | ACNE DIAB100 |
| 5          | Alain        | DIAB201      |
+------------+--------------+--------------+
输出：
+------------+--------------+--------------+
| patient_id | patient_name | conditions   |
+------------+--------------+--------------+
| 3          | Bob          | DIAB100 MYOP |
| 4          | George       | ACNE DIAB100 | 
+------------+--------------+--------------+

考点

正则匹配 \\s、^、|。注意 DIAB1 必须是单词开头，直接 LIKE '%DIAB1%' 会错误匹配到 XDIAB1。

select 
    * 
from
    Patients
where
    conditions rlike '^DIAB1|.*\\sDIAB1'

# anther method
select 
    * 
from
    Patients
where
    conditions like '% DIAB1%'
    or
    conditions like 'DIAB1%'

这是典型的反范式设计

把多个疾病代码塞进一个 varchar 里，注定只能全表扫描（正则和左模糊都用不上 B+ 树索引）。正确做法是拆出 patient_condition(patient_id, code) 关联表，见表结构设计。

---

下一步 👉 连接查询