MySQL 基础篇（五）：数据操作基础 —— CRUD

目录

本文内容概要

一、认识 CRUD

二、新增数据：INSERT

2.1 INSERT 基本语法

2.2 全列插入

2.3 指定列插入

2.4 一次插入多条数据

2.5 插入冲突则更新：ON DUPLICATE KEY UPDATE

2.6 替换数据：REPLACE

三、查询数据：SELECT

3.1 SELECT 基本语法

3.2 查询全部字段

3.3 查询指定字段

3.4 查询字段起别名

3.5 查询结果去重：DISTINCT

3.6 查询表达式

四、条件筛选：WHERE

4.1 WHERE 基本语法

4.2 比较运算符

4.3 逻辑运算符

4.4 范围查询：BETWEEN ... AND ...

4.5 集合查询：IN / NOT IN

4.6 模糊查询：LIKE 

4.7 NULL 值判断：IS NULL / IS NOT NULL

五、查询结果排序：ORDER BY

5.1 ORDER BY 基本语法

5.2 多字段排序

六、限制查询结果：LIMIT

6.1 LIMIT 基本语法

6.2 指定查询起始位置

6.3 SELECT 语句的逻辑执行顺序

七、更新数据：UPDATE

7.1 UPDATE 基本语法

7.2 更新一个或多个字段

7.3 更新表中全部数据

八、删除数据：DELETE

8.1 DELETE 基本语法

8.2 条件删除

8.3 删除表中全部数据

8.4 截断表：TRUNCATE

---

本文内容概要

本文主要介绍 MySQL 中数据的增删改查操作。通过本文的学习，需要掌握 INSERT 数据插入、SELECT 数据查询、UPDATE 数据更新以及 DELETE 数据删除等基本操作；理解 WHERE 条件筛选、ORDER BY 查询结果排序、LIMIT 结果限制与分页查询的基本用法；掌握 DISTINCT 去重、BETWEEN 范围查询、IN 集合查询、LIKE 模糊匹配以及 NULL 值判断等常用查询语法；同时了解 REPLACE、ON DUPLICATE KEY UPDATE 和 TRUNCATE 等相关操作，并对基础 SELECT 语句的逻辑执行顺序进行总结，为后续学习聚合查询、多表查询以及子查询等进阶 SQL 内容打下基础。

一、认识 CRUD

CRUD 是数据库中最基本的四类数据操作，分别对应数据的新增、查询、修改和删除。

CRUD 由四个英文单词的首字母组成：

1. Create   （创建）：向数据表中插入新的数据
2. Retrieve（读取）：从数据表中查询所需要的数据
3. Update  （更新）：修改数据表中已经存在的数据
4. Delete   （删除）：删除数据表中已有的数据

因此，对于一张已经创建完成的数据表，我们日常最主要的操作基本都是 CRUD。

二、新增数据：INSERT

2.1 INSERT 基本语法

INSERT [INTO] 表名 [(列属性, 列属性, ...)] VALUES [(值, 值, ...)]

示例：在学生表中插入学生的相关信息 

create table student(
    -> id int unsigned primary key,
    -> name varchar(20) not null,
    -> age int
    -> );

desc student;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | NO   | PRI | NULL    |       |
| name  | varchar(20)      | NO   |     | NULL    |       |
| age   | int(11)          | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+

2.2 全列插入

insert into student values (1, '张三', 18);
insert student values (2, '李四', 20);

select * from student;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 张三   |   18 |
|  2 | 李四   |   20 |
+----+--------+------+
2 rows in set (0.00 sec)

INTO 在 MySQL 中可以省略不写，其中值列表信息必须把所有列属性全部填充。

2.3 指定列插入

insert into student (id, name) values (1, '张三');

select * from student;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 张三   | NULL |
+----+--------+------+
1 row in set (0.00 sec)

其他未被指定的列必须存在默认值或者主键自增值。

2.4 一次插入多条数据

insert into student values (1, '张三', 18), (2, '李四', 19);
insert into student (id, name) values (3, '王五'), (4, '赵六');

select * from student;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 张三   |   18 |
|  2 | 李四   |   19 |
|  3 | 王五   | NULL |
|  4 | 赵六   | NULL |
+----+--------+------+
4 rows in set (0.00 sec)

一次插入多条数据时，既可以全列插入也可以指定列插入。数据之间以逗号分割。

2.5 插入冲突则更新：ON DUPLICATE KEY UPDATE

在插入数据时，插入的新数据可能由于主键或者唯一键冲突，导致插入失败。

insert into student values (1, '张三', 18);
Query OK, 1 row affected (0.00 sec)

insert into student values (1, 'zhangsan', 20);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

此时，我们可以选择性的进行同步更新数据：

INSERT ... ON DUPLICATE KEY UPDATE column = value [, column = value] ...

select * from student;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 张三   |   20 |
+----+--------+------+
1 row in set (0.00 sec)

insert into student values (1, 'zhangsan', 20) 
on duplicate key update name = 'zhangsan', age = 20;
Query OK, 2 rows affected (0.14 sec)

insert into student values (2, 'lisi', 16) 
on duplicate key update nameme = 'lisi', age = 16;
Query OK, 1 row affected (0.00 sec)

insert into student values (2, 'lisi', 16) 
on duplicate key update name = 'lisi', age = 16;

Query OK, 0 rows affected (0.00 sec)
select * from student;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | zhangsan |   20 |
|  2 | lisi     |   16 |
+----+----------+------+
2 rows in set (0.00 sec)

在使用插入否则更新的操作时，我们可以通过 MySQL 响应看到操作对于表的影响

Query OK, 0 rows affected (0.00 sec) ：表中有冲突数据，但冲突数据的值与 update 值相同

Query OK, 1 row affected (0.00 sec)：表中没有冲突数据，直接插入

Query OK, 2 rows affected (0.14 sec)：表中有冲突数据，并且数据已经被更新

补充：通过 MySQL 函数获取上次操作影响的数据行数

insert into student values (2, 'lisi', 16) 
on duplicate key update name = 'lisi', age = 16;
Query OK, 0 rows affected (0.00 sec)

select row_count();
+-------------+
| row_count() |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

insert into student values (2, 'lisi', 15) 
on duplicate key update name = 'lisi', age = 15;
Query OK, 2 rows affected (0.00 sec)

select row_count();
+-------------+
| row_count() |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

2.6 替换数据：REPLACE

在插入新数据时，我们可以采用 REPLACE 方式进行插入新数据。当新数据发生主键或者唯一键冲突时，REPLACE 方式会先删除旧数据，再插入新数据。

基本语法：

REPLACE [INTO] 表名 [(列属性, 列属性, ...)] VALUES [(值, 值, ...)]

REPLACE 也会支持全列插入、指定列插入和一次插入多条数据

replace into student values (1, '张三', 18);
Query OK, 1 row affected (0.00 sec)

select * from student;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 张三   |   18 |
+----+--------+------+
1 row in set (0.00 sec)

replace student values (1, 'zhangsan', 20);
Query OK, 2 rows affected (0.00 sec)

replace student values (1, 'zhangsan', 20);
Query OK, 1 row affected (0.00 sec)

select * from student;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | zhangsan |   20 |
+----+----------+------+
1 row in set (0.00 sec)

REPLACE 新增记录时影响 1 行；发生唯一键冲突并替换旧记录时影响 2 行。需要注意，在 InnoDB 中，当新旧记录完全相同时，可能出现影响 1 行的情况，因此不建议仅通过 affected rows 判断 REPLACE是否发生了数据替换。

三、查询数据：SELECT

3.1 SELECT 基本语法

SELECT [DISTINCT] * | 列属性, 列属性, ... FROM 表名

示例：构建学生表，准备学生表数据

create table student(
    -> id int unsigned primary key auto_increment,
    -> name varchar(20) not null,
    -> math tinyint unsigned,
    -> english tinyint unsigned,
    -> chinese tinyint unsigned
    -> );

desc student;
+---------+---------------------+------+-----+---------+----------------+
| Field   | Type                | Null | Key | Default | Extra          |
+---------+---------------------+------+-----+---------+----------------+
| id      | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name    | varchar(20)         | NO   |     | NULL    |                |
| math    | tinyint(3) unsigned | YES  |     | NULL    |                |
| english | tinyint(3) unsigned | YES  |     | NULL    |                |
| chinese | tinyint(3) unsigned | YES  |     | NULL    |                |
+---------+---------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

insert into student (name, math, english, chinese) values ('张三', 88, 92, 93);
insert into student (name, math, english, chinese) values ('李四', 82, 95, 77);
insert into student (name, math, english, chinese) values ('王五', 59, 34, 88);

select * from student;
+----+--------+------+---------+---------+
| id | name   | math | english | chinese |
+----+--------+------+---------+---------+
|  1 | 张三   |   88 |      92 |      93 |
|  2 | 李四   |   82 |      95 |      77 |
|  3 | 王五   |   59 |      34 |      88 |
+----+--------+------+---------+---------+
3 rows in set (0.00 sec)

3.2 查询全部字段

select * from student;
+----+--------+------+---------+---------+
| id | name   | math | english | chinese |
+----+--------+------+---------+---------+
|  1 | 张三   |   88 |      92 |      93 |
|  2 | 李四   |   82 |      95 |      77 |
|  3 | 王五   |   59 |      34 |      88 |
+----+--------+------+---------+---------+
3 rows in set (0.00 sec)

3.3 查询指定字段

select id, name, math from student;
+----+--------+------+
| id | name   | math |
+----+--------+------+
|  1 | 张三   |   88 |
|  2 | 李四   |   82 |
|  3 | 王五   |   59 |
+----+--------+------+
3 rows in set (0.00 sec)

select name, math, id from student;
+--------+------+----+
| name   | math | id |
+--------+------+----+
| 张三   |   88 |  1 |
| 李四   |   82 |  2 |
| 王五   |   59 |  3 |
+--------+------+----+
3 rows in set (0.00 sec)

显示顺序与指定顺序相关

3.4 查询字段起别名

基本语法：字段名 [AS] 别名

select id as 学号, name 姓名, math 数学 from student;
+--------+--------+--------+
| 学号   | 姓名   | 数学   |
+--------+--------+--------+
|      1 | 张三   |     88 |
|      2 | 李四   |     82 |
|      3 | 王五   |     59 |
+--------+--------+--------+
3 rows in set (0.00 sec)

select * from student;
+----+--------+------+---------+---------+
| id | name   | math | english | chinese |
+----+--------+------+---------+---------+
|  1 | 张三   |   88 |      92 |      93 |
|  2 | 李四   |   82 |      95 |      77 |
|  3 | 王五   |   59 |      34 |      88 |
+----+--------+------+---------+---------+
3 rows in set (0.01 sec)

注：别名只是显示效果，起别名不会修改列属性

3.5 查询结果去重：DISTINCT

示例：准备一张学生表，查询学生表中有哪些专业

select * from student;
+------+--------+------+--------------+
| id   | name   | age  | major        |
+------+--------+------+--------------+
|    1 | 张三   |   18 | 计算机       |
|    2 | 李四   |   19 | 计算机       |
|    3 | 王五   |   20 | 软件工程     |
|    4 | 赵六   |   18 | 计算机       |
|    5 | 小红   |   19 | 软件工程     |
|    6 | 小明   |   20 | 人工智能     |
+------+--------+------+--------------+

select major from student;
+----------+
| major    |
+----------+
| 计算机   |
| 计算机   |
| 软件工程 |
| 计算机   |
| 软件工程 |
| 人工智能 |
+----------+

select distinct major from student;
+----------+
| major    |
+----------+
| 计算机   |
| 软件工程 |
| 人工智能 |
+----------+

select distinct age, major from student;
+------+--------------+
| age  | major        |
+------+--------------+
|   18 | 计算机       |
|   19 | 计算机       |
|   20 | 软件工程     |
|   19 | 软件工程     |
|   20 | 人工智能     |
+------+--------------+
5 rows in set (0.00 sec)

DISTINCT 去重的是查询结果中的整行数据，且 DISTINCT 支持组合去重。

例如：

select distinct age, major from student;

这里去重的是 (age, major) 这个组合，而不是分别对 age 和 major 单独去重。

3.6 查询表达式

select * from student;
+----+--------+------+---------+---------+
| id | name   | math | english | chinese |
+----+--------+------+---------+---------+
|  1 | 张三   |   88 |      92 |      93 |
|  2 | 李四   |   82 |      95 |      77 |
|  3 | 王五   |   59 |      34 |      88 |
+----+--------+------+---------+---------+
3 rows in set (0.01 sec)

select name, math+english+chinese from student;
+--------+----------------------+
| name   | math+english+chinese |
+--------+----------------------+
| 张三   |                  273 |
| 李四   |                  254 |
| 王五   |                  181 |
+--------+----------------------+
3 rows in set (0.00 sec)

select name 姓名, math+english+chinese as 总分 from student;
+--------+--------+
| 姓名   | 总分   |
+--------+--------+
| 张三   |    273 |
| 李四   |    254 |
| 王五   |    181 |
+--------+--------+
3 rows in set (0.00 sec)

四、条件筛选：WHERE

在前面介绍的 SELECT 可以查询表中的数据，但如果不添加任何限制条件，则会返回表中的全部记录。而在实际开发中，我们往往需要获取满足特定条件的数据。例如：

• 查询年龄大于 18 岁的学生；
• 查询计算机专业的学生；
• 查询姓名为张三或者李四的学生；
• 查询年龄处于某个范围内的学生。

此时就需要 WHERE 字句对数据进行筛选。

4.1 WHERE 基本语法

WHERE 用于指定查询条件，只要满足条件的数据才能出现到显示结果中。

SELECT 字段列表 FROM 表名 WHERE 条件

4.2 比较运算符

运算符	说明
>、>=、<、<=	大于、大于等于、小于、小于等于
=	等于；对 NULL 不安全，例如 NULL = NULL 的结果为 NULL
<=>	NULL 安全等于，例如 NULL <=> NULL 的结果为 TRUE(1)
!= 或者 <>	不等于

示例：
+----+--------+------+----------+
| id | name   | age  | major    |
+----+--------+------+----------+
|  1 | 张三   |   18 | 计算机   |
|  3 | 王五   |   20 | 人工智能 |
+----+--------+------+----------+

查询年龄大于 18 岁的学生：
select * from student where age > 18;

查询年龄不等于 18 岁的学生：
select * from student where age != 18;
select * from student where age <> 18;

查询姓名为 "张三" 的学生:
select * from student where name = '张三';

4.3 逻辑运算符

运算符	说明
AND	多个条件必须都为 TRUE(1)，结果才为 TRUE(1)
OR	任意一个条件为 TRUE(1)，结果就为 TRUE(1)
NOT	对条件结果取反，例如条件为 TRUE(1)，结果为 FALSE(0)

查询年龄大于等于 18 岁，并且专业为计算机的学生:
select * from student where age >= 18 and major = '计算机';

查询专业为计算机或者人工智能的学生:
select * from student where major = '计算机' or major = '人工智能';

查询专业不是计算机的学生:
select * from student where not major = '计算机';

查询年龄大于等于 18 岁，并且专业是计算机或者人工智能:
select * from student where age >= 18 and (major = '计算机' or major = '人工智能');

4.4 范围查询：BETWEEN ... AND ...

如果需要判断某个值是否位于指定范围内，可以使用：

BETWEEN 最小值 AND 最大值

查询年龄在 18 ~ 20 岁之间的学生：
select * from student where age between 18 and 20;
也可以这样写:
select * from student where age >= 18 and age <= 20;

查找年龄不在 18 ~ 20 岁之间的学生:
select * from student where age not between 18 and 20;

注：BETWEEN ... AND ... 包含左右边界

4.5 集合查询：IN / NOT IN

当一个字段满足多个离散值中的任意一个时，如果一直使用 OR，SQL 语句会比较繁琐。

例如：查找年龄等于 18 或者等于 20 或者等于 21 的学生:
select * from student where age = 18 or age = 20 or age = 21;

此时可以使用 IN:
select * from student where age in (18, 20, 21);

查询专业为软件工程或者计算机或者人工智能的学生：
select * from student where major in ('软件工程', '计算机', '人工智能');

查找专业不为软件工程或者计算机或者人工智能的学生：
select * from student where major not in ('软件工程', '计算机', '人工智能');

4.6 模糊查询：LIKE 

在学生表信息中，我们可能需要找所有姓张的学生，此时就可以使用 LIKE 进行模糊匹配。

通配符	含义
%	匹配任意长度的字符，可以是 0 个或多个
_	匹配任意一个字符

查询所有姓张的学生:
select * from student where name like '张%';

查找名字以'三'为结尾的学生:
select * from student where name like '%三';

查找名字中包含'三'的学生:
select * from student where name like '%三%';

查找姓张且只有两个字的学生:
select * from student where name like '张_';

查找不是以'张'开头的学生:
select * from student where name not like '张%';

4.7 NULL 值判断：IS NULL / IS NOT NULL

在 MySQL 中，NULL 表示空，不参与运算和比较。

假设部分学生暂时没有填写专业：

+----+--------+------+----------+
| id | name   | age  | major    |
+----+--------+------+----------+
|  1 | 张三   |   18 | 计算机   |
|  2 | 李四   |   20 | NULL     |
|  3 | 王五   |   19 | 人工智能 |
+----+--------+------+----------+

如果想查询 major 为 NULL 的记录，不能写：

select * from student where major = NULL;

而是：

select * from student where major is null;

查询专业不为 NULL 的学生：

select * from student where major is not null;

五、查询结果排序：ORDER BY

5.1 ORDER BY 基本语法

ORDER BY 用于按照指定字段对查询结果进行排序。

基本语法：

SELECT 字段列表 FROM 表名 ORDER BY 字段名 [ASC | DESC];

关键字	说明
ASC	升序排列（Ascending）
DESC	降序排列（Descending）

select * from student;
+----+--------+------+---------+---------+
| id | name   | math | english | chinese |
+----+--------+------+---------+---------+
|  1 | 张三   |   88 |      92 |      93 |
|  2 | 李四   |   82 |      95 |      77 |
|  3 | 王五   |   59 |      34 |      88 |
+----+--------+------+---------+---------+
3 rows in set (0.00 sec)

select * from student order by chinese;
+----+--------+------+---------+---------+
| id | name   | math | english | chinese |
+----+--------+------+---------+---------+
|  2 | 李四   |   82 |      95 |      77 |
|  3 | 王五   |   59 |      34 |      88 |
|  1 | 张三   |   88 |      92 |      93 |
+----+--------+------+---------+---------+
3 rows in set (0.00 sec)

select * from student order by chinese asc;
+----+--------+------+---------+---------+
| id | name   | math | english | chinese |
+----+--------+------+---------+---------+
|  2 | 李四   |   82 |      95 |      77 |
|  3 | 王五   |   59 |      34 |      88 |
|  1 | 张三   |   88 |      92 |      93 |
+----+--------+------+---------+---------+
3 rows in set (0.00 sec)

select * from student order by chinese desc;
+----+--------+------+---------+---------+
| id | name   | math | english | chinese |
+----+--------+------+---------+---------+
|  1 | 张三   |   88 |      92 |      93 |
|  3 | 王五   |   59 |      34 |      88 |
|  2 | 李四   |   82 |      95 |      77 |
+----+--------+------+---------+---------+
3 rows in set (0.00 sec)

默认情况下，ORDER BY 会按照升序进行排列。

5.2 多字段排序

基本语法：

ORDER BY 字段1 排序方式, 字段2 排序方式, ...;

select * from student;
+----+--------+------+---------+---------+
| id | name   | math | english | chinese |
+----+--------+------+---------+---------+
|  1 | 张三   |   88 |      92 |      93 |
|  2 | 李四   |   82 |      95 |      77 |
|  3 | 王五   |   59 |      34 |      88 |
|  4 | 赵六   |   73 |      90 |      93 |
+----+--------+------+---------+---------+
4 rows in set (0.00 sec)

select * from student order by chinese desc;
+----+--------+------+---------+---------+
| id | name   | math | english | chinese |
+----+--------+------+---------+---------+
|  1 | 张三   |   88 |      92 |      93 |
|  4 | 赵六   |   73 |      90 |      93 |
|  3 | 王五   |   59 |      34 |      88 |
|  2 | 李四   |   82 |      95 |      77 |
+----+--------+------+---------+---------+
4 rows in set (0.00 sec)

select * from student order by chinese desc, english asc;
+----+--------+------+---------+---------+
| id | name   | math | english | chinese |
+----+--------+------+---------+---------+
|  4 | 赵六   |   73 |      90 |      93 |
|  1 | 张三   |   88 |      92 |      93 |
|  3 | 王五   |   59 |      34 |      88 |
|  2 | 李四   |   82 |      95 |      77 |
+----+--------+------+---------+---------+
4 rows in set (0.00 sec)

多个字段排序时，MySQL 会优先按照第一个字段排序。只有当前面的字段值相同时，才会继续按照后面的字段进行排序。

另外 ORDER BY 也可以和 WHERE 配合使用。

查询数学成绩大于等于 90 分的学生，并按照语文成绩降序排列:
select * from student where math >= 90 order by chinese desc;

六、限制查询结果：LIMIT

6.1 LIMIT 基本语法

LIMIT 用于限制查询返回的数据条数。

基本语法：

SELECT 字段列表 FROM 表名 LIMIT 数量;

select * from student;
+----+--------+------+---------+---------+
| id | name   | math | english | chinese |
+----+--------+------+---------+---------+
|  1 | 张三   |   88 |      92 |      93 |
|  2 | 李四   |   82 |      95 |      77 |
|  3 | 王五   |   59 |      34 |      88 |
|  4 | 赵六   |   73 |      90 |      93 |
+----+--------+------+---------+---------+
4 rows in set (0.00 sec)

select * from student limit 2;
+----+--------+------+---------+---------+
| id | name   | math | english | chinese |
+----+--------+------+---------+---------+
|  1 | 张三   |   88 |      92 |      93 |
|  2 | 李四   |   82 |      95 |      77 |
+----+--------+------+---------+---------+
2 rows in set (0.00 sec)

其中 LIMIT 通常会和 ORDER BY 配合使用。

查询数学成绩最高的 3 名学生:
select * from student order by math desc limit 3;
+----+--------+------+---------+---------+
| id | name   | math | english | chinese |
+----+--------+------+---------+---------+
|  1 | 张三   |   88 |      92 |      93 |
|  2 | 李四   |   82 |      95 |      77 |
|  4 | 赵六   |   73 |      90 |      93 |
+----+--------+------+---------+---------+
3 rows in set (0.00 sec)

6.2 指定查询起始位置

基本语法：

LIMIT offset, count;
或者
LIMIT count offest value;

select * from student limit 0, 3;
+----+--------+------+---------+---------+
| id | name   | math | english | chinese |
+----+--------+------+---------+---------+
|  1 | 张三   |   88 |      92 |      93 |
|  2 | 李四   |   82 |      95 |      77 |
|  3 | 王五   |   59 |      34 |      88 |
+----+--------+------+---------+---------+
3 rows in set (0.00 sec)

select * from student limit 3 offset 0;
+----+--------+------+---------+---------+
| id | name   | math | english | chinese |
+----+--------+------+---------+---------+
|  1 | 张三   |   88 |      92 |      93 |
|  2 | 李四   |   82 |      95 |      77 |
|  3 | 王五   |   59 |      34 |      88 |
+----+--------+------+---------+---------+
3 rows in set (0.00 sec)

select * from student limit 3 offset 1;
+----+--------+------+---------+---------+
| id | name   | math | english | chinese |
+----+--------+------+---------+---------+
|  2 | 李四   |   82 |      95 |      77 |
|  3 | 王五   |   59 |      34 |      88 |
|  4 | 赵六   |   73 |      90 |      93 |
+----+--------+------+---------+---------+
3 rows in set (0.00 sec)

LIMIT 一个非常常见的应用常见就是分页查询。

例如，一个学生管理系统中存在 1000 名学生，如果一次将所有学生信息全部展示出来，不仅页面不方便查看，也没有必要一次查询全部数据。因此可以规定：每页显示 50 条数据。

第一页查询:
select * from student limit 50 offset 0;
第二页查询:
select * from student limit 50 offset 50;
第三页查询:
select * from student limit 50 offset 100;

实现进行分页查询时，通常还会搭配 ORDEY BY 使用，此时：

SELECT 字段列表 FROM 表名 WHERE 条件 ORDER BY 排序字段 LIMIT count OFFSET value;

6.3 SELECT 语句的逻辑执行顺序

书写顺序	逻辑处理顺序
SELECT	FROM
FROM	WHERE
WHERE	SELECT
ORDER BY	DISTINCT
LIMIT	ORDER BY
	LIMIT

SELECT DISTINCT name, age FROM student WHERE age >= 18 ORDER BY age DESC LIMIT 3;

1. FROM student
   先确定从哪张表获取数据

2. WHERE age >= 18
   筛选满足条件的数据

3. SELECT name, age
   决定最终需要哪些字段

4. DISTINCT
   对查询结果进行去重

5. ORDER BY age DESC
   对结果进行排序

6. LIMIT 3
   最后限制返回的数据数量

SELECT 语句的逻辑执行顺序带来的经典问题：

SELECT age + 1 AS new_age FROM student WHERE new_age > 20;
此时 SELECT 语句发生报错：WHERE 子句不认识 new_age
原因：执行 WHERE 时, new_age 这个别名还没有产生

SELECT age + 1 AS new_age FROM student ORDER BY new_age;
此时 SELECT 语句正常执行
原因：SELECT -> ORDER BY, 到 ORDER BY 时，别名已经产生了

七、更新数据：UPDATE

7.1 UPDATE 基本语法

UPDATE 表名 SET [列属性=新数据, 列属性=新数据, ...] [WHERE ...] [ORDER BY ...] [LIMIT...]

7.2 更新一个或多个字段

select * from student;
+----+--------+------+---------+---------+
| id | name   | math | english | chinese |
+----+--------+------+---------+---------+
|  1 | 张三   |   88 |      92 |      93 |
|  2 | 李四   |   82 |      95 |      77 |
|  3 | 王五   |   59 |      34 |      88 |
|  4 | 赵六   |   73 |      90 |      93 |
+----+--------+------+---------+---------+
4 rows in set (0.00 sec)

更新一个字段:将张三的数学成绩加上 5 分
update student set math = math + 5 where id = 1;

更新多个字段:将王五的数据成绩加上 5 分,英语成绩设置为 97 分
update student set math = math + 5, english = 97 where id = 3;

7.3 更新表中全部数据

注意：更新表中全部数据谨慎使用！

select * from student;
+----+--------+------+---------+---------+
| id | name   | math | english | chinese |
+----+--------+------+---------+---------+
|  1 | 张三   |   88 |      92 |      93 |
|  2 | 李四   |   82 |      95 |      77 |
|  3 | 王五   |   59 |      34 |      88 |
|  4 | 赵六   |   73 |      90 |      93 |
+----+--------+------+---------+---------+
4 rows in set (0.00 sec)

将所有人的数学成绩加上五分
update student set math = math + 5;

select * from student;
+----+--------+------+---------+---------+
| id | name   | math | english | chinese |
+----+--------+------+---------+---------+
|  1 | 张三   |   93 |      92 |      93 |
|  2 | 李四   |   87 |      95 |      77 |
|  3 | 王五   |   69 |      97 |      88 |
|  4 | 赵六   |   78 |      90 |      93 |
+----+--------+------+---------+---------+
4 rows in set (0.00 sec)

八、删除数据：DELETE

8.1 DELETE 基本语法

基本语法：

DELETE FROM 表名 [WHERE ...] [ORDER BY ...] [LIMIT ...]

8.2 条件删除

select * from student;
+----+--------+------+---------+---------+
| id | name   | math | english | chinese |
+----+--------+------+---------+---------+
|  1 | 张三   |   93 |      92 |      93 |
|  2 | 李四   |   82 |      95 |      77 |
|  3 | 王五   |   64 |      97 |      88 |
|  4 | 赵六   |   73 |      90 |      93 |
+----+--------+------+---------+---------+
4 rows in set (0.00 sec)

删除姓名为'赵六'的信息
delete from student where name = '赵六';

select * from student;
+----+--------+------+---------+---------+
| id | name   | math | english | chinese |
+----+--------+------+---------+---------+
|  1 | 张三   |   93 |      92 |      93 |
|  2 | 李四   |   82 |      95 |      77 |
|  3 | 王五   |   64 |      97 |      88 |
+----+--------+------+---------+---------+
3 rows in set (0.00 sec)

8.3 删除表中全部数据

注意：删除表中全部数据谨慎使用！

select * from student;
+----+--------+------+---------+---------+
| id | name   | math | english | chinese |
+----+--------+------+---------+---------+
|  1 | 张三   |   93 |      92 |      93 |
|  2 | 李四   |   82 |      95 |      77 |
|  3 | 王五   |   64 |      97 |      88 |
+----+--------+------+---------+---------+
3 rows in set (0.00 sec)

删除表中所有数据
delete from student;

select * from student;
Empty set (0.00 sec)

8.4 截断表：TRUNCATE

TRUNCATE 用于快速删除表中的全部数据。

基本语法：

TRUNCATE TABLE 表名;

create table student(
    id int primary key auto_increment,
    name varchar(20)
    );

insert into student (name) values ('张三'), ('李四'), ('王五');

show create table student \G;
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

delete from student;

show create table student \G;
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

truncate table student;

show create table student \G;
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

如果执行 DELETE，AUTO_INCREMENT 不会改变，而执行 TRUNCATE，AUTO_INCREMENT 会重新开始。

对比项	DELETE FROM 表名	TRUNCATE TABLE 表名
删除范围	可以配合 WHERE 删除部分数据，也可以删除全部数据	只能删除整张表的数据
表结构	保留	保留
自增长计数	通常不会重置	通常会重置
SQL 类型	DML	DDL
删除方式	按删除语句处理记录	更接近重新创建一张空表
使用场景	需要灵活删除数据	快速清空整张表

当需要按照条件删除数据时使用 DELETE；当需要快速清空整张表时，可以考虑使用 TRUNCATE

转载自 CSDN-专业IT技术社区

原文链接：https://blog.csdn.net/Felix_kiss_L/article/details/167081480