07 表的增删改查上

表的增删改查上

1. Create（创建）

1. 单行插入 + 全列插入（最基础）

1. 语法格式

1
INSERT INTO 表名 VALUES (值1, 值2, ..., 值N);

省略列名时，必须按表定义的列顺序且写齐所有列（可用 NULL 或 DEFAULT 占位）。

2. 示例

1
2
3
4
5
6
7
8
CREATE TABLE t (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20),
  age INT
);

# 全列插入（省略列名），id 用 NULL 占位触发自增
INSERT INTO t VALUES (NULL, '张三', 20);

注意：

  • 简短但 易错（列顺序变动或新增列会导致问题）。
  • 若主键自增，用 NULL 或 DEFAULT 占位。
  • 若某列 NOT NULL 且无 DEFAULT，必须提供值。

2. 多行插入 + 指定列插入（高效写法）

1. 语法格式（指定列，支持多行）

1
2
3
4
INSERT INTO 表名 (列1, 列2, ...) VALUES
  (值11, 值12, ...),
  (值21, 值22, ...),
  ...;

2. 示例

1
2
3
4
5
# 只插入 name 和 age，id 用自增
INSERT INTO t (name, age) VALUES
  ('李四', 22),
  ('王五', 19),
  ('赵六', 21);

注意：

  • 一次多行插入性能远优于循环单条插入。
  • 可以省略不需要赋值的列（使用默认值或 NULL）。
  • 若有唯一/主键冲突，会整个语句失败（除非用 IGNORE 或 ON DUPLICATE）。

3. 插入否则更新：INSERT ... ON DUPLICATE KEY UPDATE

1. 语法格式

1
2
3
4
INSERT INTO 表名 (列1, 列2, ...)
VALUES (值1, 值2, ...)
ON DUPLICATE KEY UPDATE
  列A = 新值A, 列B = 新值B, ...;

在插入行与表中已有行在 主键或唯一索引 冲突时，MySQL 执行 UPDATE 子句而不是报错或忽略。

2. 示例（常见用法：计数、冲突合并）

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE item (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  cnt INT DEFAULT 0,
  UNIQUE KEY (name)
);

# 尝试插入新名字；若 name 冲突就把 cnt 累加
INSERT INTO item (id, name, cnt)
VALUES (1, 'apple', 1)
ON DUPLICATE KEY UPDATE cnt = cnt + VALUES(cnt);

VALUES(cnt) 在 MySQL 8.0.20 以后用 VALUES() 被废弃，可用 VALUES() 的替代 NEW，实际推荐写法：

1
... ON DUPLICATE KEY UPDATE cnt = cnt + VALUES(cnt);

（在很多 MySQL 版本仍通用；新版本可用 INSERT ... AS new 结构或 VALUES() 替代注意兼容）

3. 常见变体：直接覆盖部分字段

1
2
3
4
INSERT INTO users (id, name, email)
VALUES (1, 'Tom', 't@x')
ON DUPLICATE KEY UPDATE
  name = VALUES(name), email = VALUES(email);

注意：

  • 触发器 BEFORE INSERT / AFTER INSERT 等仍会按插入流程触发，冲突时也会触发 UPDATE 相关触发器。
  • ON DUPLICATE KEY UPDATE 会把冲突情况视为更新，可能影响 AUTO_INCREMENT 值（插入失败仍可能消耗自增）。
  • 性能：对高并发唯一冲突场景，可能产生较多锁竞争。
  • VALUES(col) 返回插入语句中指定的值（注意版本兼容）。

4. 替换数据：REPLACE INTO

1. 语法格式

1
2
REPLACE INTO 表名 (列1, 列2, ...)
VALUES (...);

REPLACE 语句会首先删除表中与新数据冲突的旧记录（通常是主键或唯一索引冲突），然后插入新数据（相当于 DELETE + INSERT）。也就是说，如果插入的数据已存在，MySQL 会先删除原来的数据，再插入新的数据。

2. 示例

1
2
# 若 id=1 存在，则先删除 id=1 的行，再插入新行
REPLACE INTO t (id, name, age) VALUES (1, 'Tom', 30);

注意：

  • REPLACE 是 DELETE + INSERT 的组合：会导致被删除行的所有外键级联、触发器、以及自增等副作用（比如被删除行上的外键约束、触发器会触发）。
  • 被删除的行会被真正删除，删除可能触发 ON DELETE 级联或触发器。
  • REPLACE 会导致新的行有新的自增 id（如果使用自增列且没有给出 id，则新插入会产生新 id）。
  • REPLACE 不如 INSERT ... ON DUPLICATE KEY UPDATE 温和，慎用在有外键或审计需求的表上。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
# 创建t1表：id主键自增，age默认18
mysql> create table t1(
    -> id int primary key auto_increment comment '这是id，会自增长',
    -> name varchar(20) comment '这是姓名',
    -> age int default 18 comment '这是年龄，默认18');

# 错误：查看表无show 表名语法
mysql> show t1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't1' at line 1

# 查看表结构：id非空自增，age默认18
mysql> desc t1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
| age   | int         | YES  |     | 18      |                |
+-------+-------------+------+-----+---------+----------------+

# 插入数据：id=1，name=张三，age=20
mysql> insert into t1 values(1,'张三',20);

# 错误：值数量与字段数不匹配
mysql> insert t1 values(null,'李四');
ERROR 1136 (21S01): Column count doesn't match value count at row 1

# 插入数据：id用null触发自增（→2），name=李四，age=20
mysql> insert t1 values(null,'李四',20);

# 插入数据：指定name和age，id自增→3
mysql> insert t1(name,age) values('赵七',25);

# 批量插入错误：id=3已存在，主键重复
mysql> insert into t1(id,name) 
    -> values(3,'王五'),
    -> (4,'小明'),
    -> (5,'小红');
ERROR 1062 (23000): Duplicate entry '3' for key 't1.PRIMARY'

# 查看数据：现有3条记录
mysql> select *from t1;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 张三   |   20 |
|  2 | 李四   |   20 |
|  3 | 赵七   |   25 |
+----+--------+------+

# 批量插入：id=5/7/8（无重复），age用默认18
mysql> insert into t1(id,name) values
    -> (5,'王五'),
    -> (7,'小明'),
    -> (8,'小红');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

# 查看数据：新增3条，age默认18
mysql> select *from t1;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 张三   |   20 |
|  2 | 李四   |   20 |
|  3 | 赵七   |   25 |
|  5 | 王五   |   18 |
|  7 | 小明   |   18 |
|  8 | 小红   |   18 |
+----+--------+------+

# 插入错误：id=3已存在，主键重复
mysql> insert into t1(id,name) values(3,'李四');
ERROR 1062 (23000): Duplicate entry '3' for key 't1.PRIMARY'

# 错误：on duplicate key update后无更新内容，语法不完整
mysql> insert into t1(id,name) values(3,'李四') on duplicate key update;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

# 主键重复时更新：更新原有id=3的行，id改为4、name改为李四
mysql> insert into t1(id,name) values(3,'李四') on duplicate key update id=4,name='李四';

# 查看数据：原id=3的行被更新为id=4，id=3消失
mysql> select *from t1;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 张三   |   20 |
|  2 | 李四   |   20 |
|  4 | 李四   |   25 |
|  5 | 王五   |   18 |
|  7 | 小明   |   18 |
|  8 | 小红   |   18 |
+----+--------+------+

# 主键重复时更新：更新原有id=8的行，id改为99、name改为小红红
mysql> insert into t1(id,name) values(8,'小红') on duplicate key update id=99,name='小红红';

# 查看数据：原id=8的行被更新为id=99，id=8消失
mysql> select *from t1;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | 张三      |   20 |
|  2 | 李四      |   20 |
|  4 | 李四      |   25 |
|  5 | 王五      |   18 |
|  7 | 小明      |   18 |
| 99 | 小红红    |   18 |
+----+-----------+------+

# 插入数据：仅age=100，id自增→100，name=null
mysql> insert into t1(age) values(100) on duplicate key update age=100;

# 查看数据：新增id=100（name=null）
mysql> select *from t1;
+-----+-----------+------+
| id  | name      | age  |
+-----+-----------+------+
|   1 | 张三      |   20 |
|   2 | 李四      |   20 |
|   4 | 李四      |   25 |
|   5 | 王五      |   18 |
|   7 | 小明      |   18 |
|  99 | 小红红    |   18 |
| 100 | NULL      |  100 |
+-----+-----------+------+

# 插入数据：name=李四、age=100，id自增→101（无重复）
mysql> insert into t1(name,age) values('李四',100) on duplicate key update age=66;

# 查看数据：新增id=101
mysql> select *from t1;
+-----+-----------+------+
| id  | name      | age  |
+-----+-----------+------+
|   1 | 张三      |   20 |
|   2 | 李四      |   20 |
|   4 | 李四      |   25 |
|   5 | 王五      |   18 |
|   7 | 小明      |   18 |
|  99 | 小红红    |   18 |
| 100 | NULL      |  100 |
| 101 | 李四      |  100 |
+-----+-----------+------+

# 插入数据：name=李四、age=100，id自增→102（无重复）
mysql> insert into t1(name,age) values('李四',100) on duplicate key update age=66;

# 查看数据：新增id=102
mysql> select *from t1;
+-----+-----------+------+
| id  | name      | age  |
+-----+-----------+------+
|   1 | 张三      |   20 |
|   2 | 李四      |   20 |
|   4 | 李四      |   25 |
|   5 | 王五      |   18 |
|   7 | 小明      |   18 |
|  99 | 小红红    |   18 |
| 100 | NULL      |  100 |
| 101 | 李四      |  100 |
| 102 | 李四      |  100 |
+-----+-----------+------+

2. Retrieve（查）

WHERE + DISTINCT / ORDER / LIMIT 的顺序（记这个）

1
2
3
4
5
>SELECT DISTINCT 列
>FROM 表
>WHERE 条件
>ORDER BY 列
>LIMIT 跳过数，每页条数;

WHERE 永远在 SELECT 后、ORDER BY 前。

1. select 列

1. 全列查询（查询所有列）

全列查询表示从表中查询所有的列。只需要使用 SELECT * 即可。

1. 语法
1
2
SELECT * FROM 表名;
select * from users;

注意： 使用 * 查询会返回表中所有列，如果表的列很多或者数据量大，会影响性能，通常建议只查询需要的列。

2. 示例
1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> select * from t1;
+-----+-----------+------+
| id  | name      | age  |
+-----+-----------+------+
|   1 | 张三      |   20 |
|   2 | 李四      |   20 |
|   4 | 李四      |   25 |
|   5 | 王五      |   18 |
|   7 | 小明      |   18 |
|  99 | 小红红    |   18 |
| 100 | NULL      |  100 |
| 101 | 李四      |  100 |
| 102 | 李四      |  100 |
+-----+-----------+------+

2. 指定列查询

指定列查询用于查询表中的特定列，而不是所有列，需要通过列名来选择需要的列。

1. 语法
1
SELECT 列名1, 列名2, ... FROM 表名;
2. 示例
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
mysql> select id from t1;
+-----+
| id  |
+-----+
|   1 |
|   2 |
|   4 |
|   5 |
|   7 |
|  99 |
| 100 |
| 101 |
| 102 |
+-----+

mysql> select name, age from t1;
+-----------+------+
| name      | age  |
+-----------+------+
| 张三      |   20 |
| 李四      |   20 |
| 李四      |   25 |
| 王五      |   18 |
| 小明      |   18 |
| 小红红    |   18 |
| NULL      |  100 |
| 李四      |  100 |
| 李四      |  100 |
+-----------+------+

3. 查询字段为表达式

SELECT 语句中可以使用表达式（例如算术运算、字符串拼接、函数等）来处理数据，并将结果作为查询结果的一部分。

1. 语法
1
SELECT 表达式 FROM 表名;
2. 示例
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
# 查询t1表所有数据
mysql> select * from t1;
+-----+-----------+------+
| id  | name      | age  |
+-----+-----------+------+
|   1 | 张三      |   20 |
|   2 | 李四      |   20 |
|   4 | 李四      |   25 |
|   5 | 王五      |   18 |
|   7 | 小明      |   18 |
|  99 | 小红红    |   18 |
| 100 | NULL      |  100 |
| 101 | 李四      |  100 |
| 102 | 李四      |  100 |
+-----+-----------+------+

# 每行判断id<50、age>=20，结果以1/0展示，返回所有行
mysql> select id<50,name, age>=20 from t1;
+-------+-----------+---------+
| id<50 | name      | age>=20 |
+-------+-----------+---------+
|     1 | 张三      |       1 |
|     1 | 李四      |       1 |
|     1 | 李四      |       1 |
|     1 | 王五      |       0 |
|     1 | 小明      |       0 |
|     0 | 小红红    |       0 |
|     0 | NULL      |       1 |
|     0 | 李四      |       1 |
|     0 | 李四      |       1 |
+-------+-----------+---------+

# 筛选age>20且<=50的行，只展示age列
mysql> select age from t1 where age>20 and age <=50;
+------+
| age  |
+------+
|   25 |
+------+

# 筛选age>20且<=500的行，展示id、name、age列
mysql> select id,name,age from t1 where age>20 and age <=500;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  4 | 李四   |   25 |
+----+--------+------+

# 展示id、name，age乘2并命名为double_age，返回所有行
mysql> select id,name, age*2 as double_age from t1;
+-----+-----------+------------+
| id  | name      | double_age |
+-----+-----------+------------+
|   1 | 张三      |         40 |
|   2 | 李四      |         40 |
|   4 | 李四      |         50 |
|   5 | 王五      |         36 |
|   7 | 小明      |         36 |
|  99 | 小红红    |         36 |
| 100 | NULL      |        200 |
| 101 | 李四      |        200 |
| 102 | 李四      |        200 |
+-----+-----------+------------+

4. 为查询结果指定别名

通过 AS 关键字，可以给查询结果的字段指定别名，使结果更具可读性，通常用于表达式或列名较长时。

1. 语法
1
SELECT 列名 AS 别名 FROM 表名;
2. 示例
1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> select id as my_id,name,age as my_age from t1;
+-------+-----------+--------+
| my_id | name      | my_age |
+-------+-----------+--------+
|     1 | 张三      |     20 |
|     2 | 李四      |     20 |
|     4 | 李四      |     25 |
|     5 | 王五      |     18 |
|     7 | 小明      |     18 |
|    99 | 小红红    |     18 |
|   100 | NULL      |    100 |
|   101 | 李四      |    100 |
|   102 | 李四      |    100 |
+-------+-----------+--------+

5. 结果去重

当我们只关心某些列的不同值时，可以用 DISTINCT 返回去除重复值的查询结果。

1. 语法
1
SELECT DISTINCT 列名 FROM 表名;

注意：distinct 是对 “所选字段的组合” 去重，不是单字段去重！DISTINCT 必须紧跟 SELECT 关键字，且只能写一次，不能插在字段列表中间/后面。

2. 示例
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
# 只对name字段去重，不管age/id
mysql> select distinct name from t1;
+-----------+
| name      |
+-----------+
| 张三      |
| 李四      |
| 王五      |
| 小明      |
| 小红红    |
| NULL      |
+-----------+

# distinct作用于(name,age)组合，去重重复的姓名+年龄组合
mysql> select distinct name,age from t1;
+-----------+------+
| name      | age  |
+-----------+------+
| 张三      |   20 |
| 李四      |   20 |
| 李四      |   25 |
| 王五      |   18 |
| 小明      |   18 |
| 小红红    |   18 |
| NULL      |  100 |
| 李四      |  100 |
+-----------+------+

2. where 条件

WHERE 用来“筛选行”，不满足条件的记录，直接不返回。

1. 语法

1
2
3
SELECT 列名
FROM 表名
WHERE 条件;

WHERE 常用比较与条件运算符速查表：

分类运算符 / 语法含义典型示例注意事项（重点）
比较> >= < <=大于 / 大于等于 / 小于 / 小于等于age >= 18常用，配合索引效果好
比较=等于id = 1NULL 不安全，NULL = NULL 结果是 NULL
比较<=>等于（NULL 安全）col <=> NULL一般不用，判断 NULL 优先用 IS NULL
比较!= <>不等于status != 0两者等价，常用 !=
范围BETWEEN a AND b在区间内（含边界）age BETWEEN 18 AND 30等价于 >= a AND <= b
集合IN (v1, v2, ...)在给定集合中id IN (1,3,5)值不要太多
集合NOT IN (v1, v2, ...)不在给定集合中id NOT IN (1,3)集合中有 NULL 会出大坑
空值IS NULL是 NULLemail IS NULL判断 NULL 只能用它
空值IS NOT NULL不是 NULLemail IS NOT NULL可正常用索引
模糊LIKE 'xxx%'以 xxx 开头name LIKE 'tom%'能用索引，推荐
模糊LIKE '%xxx'以 xxx 结尾name LIKE '%tom'不能用索引，慢
模糊LIKE '%xxx%'包含 xxxname LIKE '%tom%'大表慎用
模糊NOT LIKE不匹配name NOT LIKE 'test%'常配合 AND
逻辑AND同时满足age>=18 AND age<=30xxxxxxxxxx6 1SELECT class_id, gender,2  COUNT() AS student_num3FROM student4GROUP BY class_id, gender  # 步骤1：按“班级+性别”分组统计5HAVING COUNT() >= 2       # 步骤2：过滤出人数≥2的分组6ORDER BY student_num DESC, class_id ASC;  # 步骤3：对过滤后的结果排序mysql
逻辑OR满足一个即可city='bj' OR city='sh'一定配合括号
逻辑NOT条件取反NOT status=1常和 IN / LIKE 用

LIKE 通配符说明：

符号含义
%任意多个字符（包括 0 个）
_任意 1 个字符（name LIKE 'a_%' 表示以 a 开头，至少 2 个字符）

实用规则：

  • 判断 NULL 永远用 IS NULL / IS NOT NULL
  • AND / OR 混用、条件多时，一定加括号保证逻辑清晰
  • 优先用等值查询（=），能用 = 就别用 LIKE
  • WHERE 里的列尽量别参与计算
  • 写 UPDATE / DELETE 前先写 SELECT 验证

2. 示例

1. 等值查询（常用）
1
SELECT * FROM users WHERE id = 1;
  • 查 id 等于 1 的那一行
  • 主键 / 唯一键查询最常见
  • 注意：字符串要加单引号
1
SELECT * FROM users WHERE name = 'tom';
2. 不等于 / 大小比较
1
2
3
4
SELECT * FROM users WHERE age > 18;
SELECT * FROM users WHERE age >= 18;
SELECT * FROM users WHERE age < 60;
SELECT * FROM users WHERE age != 30;
3. AND / OR 组合条件（常用）
1
2
3
4
SELECT * FROM users
WHERE age >= 18 AND age <= 30;
SELECT * FROM users
WHERE city = 'beijing' OR city = 'shanghai';

注意：优先级坑（重点）

1
2
3
4
5
WHERE a = 1 OR b = 2 AND c = 3
# 等价于：
WHERE a = 1 OR (b = 2 AND c = 3)
# 实际开发一定加括号
WHERE (a = 1 OR b = 2) AND c = 3;
4. IN（多个值之一）
1
2
3
SELECT * FROM users WHERE id IN (1, 3, 5);
# 等价于：
WHERE id = 1 OR id = 3 OR id = 5;

注意：IN 里值不要太多（几百上千会慢）！

5. BETWEEN（范围查询）
1
2
3
SELECT * FROM users WHERE age BETWEEN 18 AND 30;
# 等价于：
WHERE age >= 18 AND age <= 30;
6. LIKE（模糊查询）
1
SELECT * FROM users WHERE name LIKE 'tom%';

常见写法：

写法含义
'tom%'以 tom 开头
'%tom'以 tom 结尾
'%tom%'包含 tom

重要性能点：%xxx 开头基本用不到索引、大表慎用模糊查询。

7. IS NULL / IS NOT NULL
1
2
3
4
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

WHERE email = NULL;	# 错误写法！！！
8. WHERE 中用表达式（能用但要小心）
1
2
3
4
SELECT * FROM users
WHERE age + 1 = 19;
# 注意：列参与计算，索引基本失效，实际应写成：
WHERE age = 18;

3. 结果排序

结果排序用 ORDER BY，默认升序；会影响性能，尤其在大表上。

1. 语法

1
2
3
4
SELECT 列
FROM 表
WHERE 条件
ORDER BY 列 [ASC|DESC];
  • ASC：升序（默认，可不写）。
  • DESC：降序。

注意：升序（ASC）时 NULL 在最前，降序（DESC）时 NULL 在最后，因为 MySQL 把 NULL 当成“最小的值”来排序。NULL 代表“没有值”，不是具体数据，所以排序时统一放在最小端，ASC 就排前面，DESC 就被放到最后。

2. 示例

1. 单列排序
1
SELECT * FROM users ORDER BY age [ASC|DESC];
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
mysql> select name,age from t1 order by age asc;
+-----------+------+
| name      | age  |
+-----------+------+
| 王五      |   18 |
| 小明      |   18 |
| 小红红    |   18 |
| 张三      |   20 |
| 李四      |   20 |
| 李四      |   25 |
| NULL      |  100 |
| 李四      |  100 |
| 李四      |  100 |
+-----------+------+

mysql> select name,age from t1 order by age desc;
+-----------+------+
| name      | age  |
+-----------+------+
| NULL      |  100 |
| 李四      |  100 |
| 李四      |  100 |
| 李四      |   25 |
| 张三      |   20 |
| 李四      |   20 |
| 王五      |   18 |
| 小明      |   18 |
| 小红红    |   18 |
+-----------+------+
2. 多列排序（真实项目常用）
1
SELECT * FROM users ORDER BY age DESC, id ASC;	# 先按 age 排，age 相同，再按 id 排
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 按id降序、name升序、age降序多级排序
mysql> select * from t1 order by id desc, name asc, age desc;
+-----+-----------+------+
| id  | name      | age  |
+-----+-----------+------+
| 102 | 李四      |  100 |
| 101 | 李四      |  100 |
| 100 | NULL      |  100 |
|  99 | 小红红    |   18 |
|   7 | 小明      |   18 |
|   5 | 王五      |   18 |
|   4 | 李四      |   25 |
|   2 | 李四      |   20 |
|   1 | 张三      |   20 |
+-----+-----------+------+
3. ORDER BY + WHERE（常见组合）
1
2
3
SELECT * FROM users
WHERE age >= 18
ORDER BY created_at DESC;

执行顺序：

  1. WHERE 过滤
  2. ORDER BY 排序
  3. 返回结果
1
2
3
4
5
6
7
8
9
10
11
12
# 先给 age 起别名，再按别名排序
mysql> select *, age as my_age from t1 where age<30 order by my_age desc;
+----+-----------+------+--------+
| id | name      | age  | my_age |
+----+-----------+------+--------+
|  4 | 李四      |   25 |     25 |
|  1 | 张三      |   20 |     20 |
|  2 | 李四      |   20 |     20 |
|  5 | 王五      |   18 |     18 |
|  7 | 小明      |   18 |     18 |
| 99 | 小红红    |   18 |     18 |
+----+-----------+------+--------+

3. ORDER BY 的几种进阶写法

1. 按别名排序
1
2
3
SELECT age + 1 AS new_age
FROM users
ORDER BY new_age DESC;

可读性好，表达式排序可能慢！

2. 按表达式排序（能用但慎用）
1
ORDER BY age + score DESC;

风险：索引失效、数据量大时很慢。

3. ORDER BY NULL（禁止排序）
1
SELECT COUNT(*) FROM users ORDER BY NULL;

用途：明确告诉 MySQL 不需要排序，聚合查询时可微优化。

4. 常见坑（重点）

1. ORDER BY 大表非常慢

原因：排序需要额外内存 / 磁盘。

解决思路：

  • ORDER BY 的列尽量建索引。
  • 排序方向要一致（ASC / DESC）。
2. ORDER BY 和索引方向不一致
1
2
# 索引： (age ASC)
ORDER BY age DESC;  # 可能用不上索引
3. ORDER BY 随机排序（千万别用）
1
ORDER BY RAND();  # 大表灾难

4. 筛选分页结果

“筛选分页结果”= WHERE 先筛选 + ORDER BY 定序 + LIMIT 分页，顺序写错、方式选错，要么结果乱、要么性能炸。

1. 语法

1
2
3
4
5
SELECT 列
FROM 表
WHERE 条件
ORDER BY 排序列
LIMIT offset, page_size; # 注意：LIMIT单参数表示仅取前n条数据（非分页），双参数表示跳过前x条、取后续n条数据（属于分页）

分页计算逻辑：

LIMIT offset, page_size 是 MySQL 分页语法：

  • offset：需要跳过的记录数（计算方式：(页码 - 1) * 每页条数/offset = (page - 1) * page_size）。
  • page_size：每页显示的条数（这里是 10）。

示例： 假设需要取第 3 页的数据，每页显示 10 条，那么：

1
2
3
4
SELECT * FROM users
WHERE status = 1  # 筛选条件（只查status=1的记录）
ORDER BY id DESC  # 按id降序排列
LIMIT 20, 10;     # 分页：跳过前20条，取接下来的10条(offset = (3-1)*10 = 20)

2. 示例

1. 首页 / 最新列表（常用）
1
2
3
4
SELECT * FROM users
WHERE age = 18
ORDER BY created_at DESC
LIMIT 10;	# 只取器前10条数据（非分页）
2. 条件 + 分页（）
1
2
3
4
5
6
7
8
9
SELECT * FROM users
WHERE id = 1001
  AND age = 18
ORDER BY id DESC
LIMIT 0, 20;	# 跳过前0条数据，取紧接着后面的20条数据（分页）

SELECT * FROM users
ORDER BY id DESC
LIMIT 20 OFFSET 40;	# 等价写法，但 LIMIT 40, 20; 更常见

3. 注意事项（坑点）

1. 没写 ORDER BY（致命）
1
SELECT * FROM users LIMIT 10;

问题：每次返回顺序可能不同、翻页会 重复 / 丢数据，分页必须 ORDER BY！

2. ORDER BY + LIMIT 深分页（性能杀手）
1
LIMIT 100000, 20;  # 非常慢，MySQL需要先扫描100000条，再丢掉，只取20条。

4. 高性能分页方式（进阶重点）

1. 游标分页（强烈推荐）

思想：用“上一页最后一条数据”翻页

1
2
3
4
SELECT * FROM users
WHERE id < 10000
ORDER BY id DESC
LIMIT 20;

特点：不用 offset、越往后 速度越快、实际项目常用。

2. 时间游标分页
1
2
3
4
SELECT * FROM users
WHERE created_at < '2025-01-01 10:00:00'
ORDER BY created_at DESC
LIMIT 20;
3. 分页 + 索引的黄金组合
1
2
3
WHERE 条件
ORDER BY id DESC
LIMIT 20;

排序列必须有索引，否则分页再小也慢。推荐：

1
2
INDEX(id)
INDEX(created_at)
4. 分页时统计总数（常见需求）
1
2
3
4
5
6
7
8
9
# 正确做法：分两条 SQL，不要在一条 SQL 里又分页又 count（慢）
# 查数据
SELECT * FROM users
WHERE age = 18
ORDER BY id DESC
LIMIT 20;

# 查总数
SELECT COUNT(*) FROM users WHERE age = 1;