【MySQL】表的增删查改_mysql查询、添加、修改

目录

一、Creat

1.1 insert

1.2 插入否则更新

1.3 替换 replace

二、Retrieve

2.1 select

2.2 where

2.3 order by

2.4 limit

2.5 插入查询结果

2.6 聚合函数

2.7 group by 和 having

三、Update

四、Delete

4.1 delete

4.2 truncate

---

CRUD操作是数据库管理中的基本操作，它们分别代表以下概念

Create(创建)，Retrieve(读取)，Update(更新)，Delete（删除）

一、Creat

1.1 insert

语法：

insert [into] 表名 [(列名...)] values (列值...)

注意：

• into 可以省略，但不推荐，加上into看着会更直接，当然是否省略看个人习惯。
• 列名可以不写或者选定列，如果忽略列名就是全列插入。
• 全列插入时，列值数量必须和定义表的列的数量及顺序一致。
• 指定列插入时，列值数量必须和SQL语句中列名的数量及顺序一致。
• 插入时可以一次插入单行或多行数据，如果是多行就用逗号隔开。

示例：

创建一张学生表

mysql> create table students(
    -> id int unsigned primary key auto_increment,
    -> sn int not null unique comment '学号',
    -> name varchar(20) not null,
    -> qq varchar(20)
    -> );

单行数据 + 全列插入

mysql> insert into students values (1, 1001, '李白', null);
Query OK, 1 row affected (0.01 sec)

多行数据 + 指定列插入

mysql> insert into students (sn, name, qq) values (1002, '杜甫', '123321'),(1003, '苏轼', '11111');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> select * from students;
+----+------+--------+--------+
| id | sn   | name   | qq     |
+----+------+--------+--------+
|  1 | 1001 | 李白   | NULL   |
|  2 | 1002 | 杜甫   | 123321 |
|  3 | 1003 | 苏轼   | 11111  |
+----+------+--------+--------+
3 rows in set (0.01 sec)

1.2 插入否则更新

在MySQL中，如果希望在插入记录时检查记录是否已存在，如果存在则更新该记录，否则插入新记录，可以使用 insert... on duplicate key update语句。这个语句结合了插入和更新的操作，适用于当表中存在唯一索引或主键约束时。

语法：

INSERT INTO table_name (column1, column2, ..., columnN)
VALUES (value1, value2, ..., valueN)
ON DUPLICATE KEY UPDATE
column1 = value1,
column2 = value2,
...;

注意：插入记录时，可以检查受影响的行数来判断操作的结果。

• 0 row affected: 表中有冲突数据，但冲突数据的值和 update 的值相等
• 1 row affected: 表中没有冲突数据，数据被插入
• 2 row affected: 表中有冲突数据，并且数据已经被更新

示例：

由于主键或者唯一键对应的值已经存在而导致插入失败，选择进行更新操作。

mysql> insert into students 
    -> values (1, 1001, '李白', '10001')
    -> on duplicate key update
    -> sn = 1000,
    -> qq = '100001';
Query OK, 2 rows affected (0.00 sec)
 
mysql> select * from students;
+----+------+--------+--------+
| id | sn   | name   | qq     |
+----+------+--------+--------+
|  1 | 1000 | 李白   | 100001 |
|  2 | 1002 | 杜甫   | 123321 |
|  3 | 1003 | 苏轼   | 11111  |
+----+------+--------+--------+
3 rows in set (0.00 sec)

1.3 替换 replace

replace语句是一种特殊的SQL操作，它首先尝试插入新行，如果遇到主键或唯一键的冲突，它会先删除冲突的行，然后插入新行。replace语句实际上是一个delete和insert的组合。

语法：

replace into 表名 [(列名...)] values (列值...)

 注意：

• 主键 或者 唯一键 没有冲突，则直接插入。
• 主键 或者 唯一键 如果冲突，则删除后再插入。
• 1 row affected：表中没有冲突数据，数据被插入。
• 2 row affected：表中有冲突数据，删除后重新插入。

mysql> replace into students values(3,1003, 'sushi', '111111');
Query OK, 2 rows affected (0.00 sec)
 
mysql> select * from students;
+----+------+--------+--------+
| id | sn   | name   | qq     |
+----+------+--------+--------+
|  1 | 1000 | 李白   | 100001 |
|  2 | 1002 | 杜甫   | 123321 |
|  3 | 1003 | sushi  | 111111 |
+----+------+--------+--------+
3 rows in set (0.00 sec)
 

二、Retrieve

Retrieve（检索）是指从数据库中查询和获取数据的过程。在MySQL中，这是通过使用SELECT语句来实现的。

创建表结构

mysql> 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 '英语成绩'
    -> );
Query OK, 0 rows affected (0.03 sec)

插入测试数据

mysql> 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);
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

2.1 select

语法：

SELECT
[DISTINCT] {* | {column [, column] ...}
[FROM table_name]
[WHERE ...]
[GROUP BY column1, column2, ...]
[HAVING ...]
[ORDER BY column [ASC | DESC], ...]
LIMIT ...

注意：

• 操作的本质是对数据进行遍历，默认是拿整张表的信息。
• 若指定列，就只显示那几列的信息。（还是会把整张表的数据读取）
• select 后面可以加语句，也可以加表达式。
• 给列名过表达式后面加上as，select显示时按照as后的别名。（as可省略）

select的执行顺序：

1. FROM table_name: 首先确定要查询的表。
2. WHERE …: 然后根据条件筛选表中的行。
3. GROUP BY …: 对筛选后的数据进行分组。
4. HAVING …: 对分组后的结果进行进一步筛选。
5. SELECT: 选择要返回的列。
6. DISTINCT: 如果指定了DISTINCT，则去除重复的行。
7. ORDER BY column [ASC | DESC]: 对结果进行排序。
8. LIMIT …: 最后，如果指定了LIMIT，则限制返回的行数。

全列查询

通常情况下不建议使用 * 进行全列查询

1. 查询的列越多，意味着需要传输的数据量越大；
2. 可能会影响到索引的使用。

mysql> SELECT * FROM exam_result;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  1 | 唐三藏    |      67 |   98 |      56 |
|  2 | 孙悟空    |      87 |   78 |      77 |
|  3 | 猪悟能    |      88 |   98 |      90 |
|  4 | 曹孟德    |      82 |   84 |      67 |
|  5 | 刘玄德    |      55 |   85 |      45 |
|  6 | 孙权      |      70 |   73 |      78 |
|  7 | 宋公明    |      75 |   65 |      30 |
+----+-----------+---------+------+---------+
7 rows in set (0.00 sec)

指定列查询

指定列的顺序不需要按定义表的顺序来

mysql> SELECT id, name, english FROM exam_result;
+----+-----------+---------+
| id | name      | english |
+----+-----------+---------+
|  1 | 唐三藏    |      56 |
|  2 | 孙悟空    |      77 |
|  3 | 猪悟能    |      90 |
|  4 | 曹孟德    |      67 |
|  5 | 刘玄德    |      45 |
|  6 | 孙权      |      78 |
|  7 | 宋公明    |      30 |
+----+-----------+---------+
7 rows in set (0.00 sec)

查询字段为表达式

mysql> SELECT id, name, english + 10 FROM exam_result;
+----+-----------+--------------+
| id | name      | english + 10 |
+----+-----------+--------------+
|  1 | 唐三藏    |           66 |
|  2 | 孙悟空    |           87 |
|  3 | 猪悟能    |          100 |
|  4 | 曹孟德    |           77 |
|  5 | 刘玄德    |           55 |
|  6 | 孙权      |           88 |
|  7 | 宋公明    |           40 |
+----+-----------+--------------+
7 rows in set (0.00 sec)

表达式包含多个字段

SELECT id, name, chinese + math + english FROM exam_result;

为查询结果指定别名

语法：SELECT column [AS] alias_name [...] FROM table_name;

• AS可省略

mysql> SELECT id, name, chinese + math + english 总分 FROM exam_result;
+----+-----------+--------+
| id | name      | 总分   |
+----+-----------+--------+
|  1 | 唐三藏    |    221 |
|  2 | 孙悟空    |    242 |
|  3 | 猪悟能    |    276 |
|  4 | 曹孟德    |    233 |
|  5 | 刘玄德    |    185 |
|  6 | 孙权      |    221 |
|  7 | 宋公明    |    170 |
+----+-----------+--------+
7 rows in set (0.00 sec)

结果去重（distinct）

mysql> SELECT DISTINCT math FROM exam_result;
+------+
| math |
+------+
|   98 |
|   78 |
|   84 |
|   85 |
|   73 |
|   65 |
+------+
6 rows in set (0.00 sec)

2.2 where

比较运算符：

逻辑运算符：

注意：

• where 子句不能用列的别名进行条件判断。

示例： 

英语不及格的同学及英语成绩 ( < 60 )

mysql> SELECT name, english FROM exam_result WHERE english < 60;
+-----------+---------+
| name      | english |
+-----------+---------+
| 唐三藏    |      56 |
| 刘玄德    |      45 |
| 宋公明    |      30 |
+-----------+---------+
3 rows in set (0.00 sec)

语文成绩在 [80, 90] 分的同学及语文成绩

SELECT name, chinese FROM exam_result WHERE chinese >= 80 AND chinese <= 90;
 
或者
 
SELECT name, chinese FROM exam_result WHERE chinese BETWEEN 80 AND 90;

数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩

SELECT name, math FROM exam_result
 
或者
 
SELECT name, math FROM exam_result WHERE math IN (58, 59, 98, 99);

姓孙的同学 及 孙某同学 ，

• % 匹配任意多个（包括 0 个）任意字符
• _ 匹配严格的一个任意字符

SELECT name FROM exam_result WHERE name LIKE '孙%';
+-----------+
| name      |
+-----------+
| 孙悟空    |
| 孙权      |
+-----------+
2 rows in set (0.01 sec)
 
 
SELECT name FROM exam_result WHERE name LIKE '孙_';
+--------+
| name   |
+--------+
| 孙权   |
+--------+
1 row in set (0.00 sec)

NULL 的查询

NULL 和 NULL 的比较，= 和 <=> 的区别：

mysql> SELECT NULL = NULL, NULL = 1, NULL = 0;
+-------------+----------+----------+
| NULL = NULL | NULL = 1 | NULL = 0 |
+-------------+----------+----------+
|        NULL |     NULL |     NULL |
+-------------+----------+----------+
1 row in set (0.00 sec)
 
mysql> SELECT NULL <=> NULL, NULL <=> 1, NULL <=> 0;
+---------------+------------+------------+
| NULL <=> NULL | NULL <=> 1 | NULL <=> 0 |
+---------------+------------+------------+
|             1 |          0 |          0 |
+---------------+------------+------------+
1 row in set (0.00 sec)
 

查询 qq 号已知的同学姓名

mysql> select * from students;
+----+------+-----------+--------+
| id | sn   | name      | qq     |
+----+------+-----------+--------+
|  1 | 1000 | 李白      | 100001 |
|  2 | 1002 | 杜甫      | 123321 |
|  3 | 1003 | sushi     | 111111 |
|  4 | 1004 | 李煜      | 234561 |
|  5 | 1005 | 辛弃疾    | NULL   |
|  6 | 1006 | 杨慎      | NULL   |
+----+------+-----------+--------+
6 rows in set (0.00 sec)
 
mysql> SELECT name, qq FROM students WHERE qq IS NOT NULL;
+--------+--------+
| name   | qq     |
+--------+--------+
| 李白   | 100001 |
| 杜甫   | 123321 |
| sushi  | 111111 |
| 李煜   | 234561 |
+--------+--------+
4 rows in set (0.00 sec)

2.3 order by

使用order by 进行结果排序。

语法：

• ASC 为升序（从小到大）
• DESC 为降序（从大到小）
• 默认为 ASC
• NULL 比任何值都小。
• 可以使用列别名。（顺序在select之后）
• 没有使用 ORDER BY 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序。

SELECT ... FROM table_name [WHERE ...]
ORDER BY column [ASC|DESC], [...];

示例：同学及数学成绩，按数学成绩升序显示

mysql> SELECT name, math FROM exam_result ORDER BY math;
+-----------+------+
| name      | math |
+-----------+------+
| 宋公明    |   65 |
| 孙权      |   73 |
| 孙悟空    |   78 |
| 曹孟德    |   84 |
| 刘玄德    |   85 |
| 唐三藏    |   98 |
| 猪悟能    |   98 |
+-----------+------+
7 rows in set (0.00 sec)

2.4 limit

limit 用于筛选分页结果，语法如下：

-- 起始下标为 0
 
-- 从 s 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;
 
-- 从 0 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;
 
-- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

注意：

• 对未知表进行查询时，最好加一条 LIMIT 1，避免因为表中数据过大，查询全表数据导致数据库卡死。

示例：

按 id 进行分页，每页 3 条记录，分别显示 第 1、2、3 页

mysql> SELECT id, name, math, english, chinese FROM exam_result
    -> ORDER BY id LIMIT 3 OFFSET 0;
+----+-----------+------+---------+---------+
| id | name      | math | english | chinese |
+----+-----------+------+---------+---------+
|  1 | 唐三藏    |   98 |      56 |      67 |
|  2 | 孙悟空    |   78 |      77 |      87 |
|  3 | 猪悟能    |   98 |      90 |      88 |
+----+-----------+------+---------+---------+
3 rows in set (0.00 sec)
 
mysql> SELECT id, name, math, english, chinese FROM exam_result 
    -> ORDER BY id LIMIT 3 OFFSET 3;
+----+-----------+------+---------+---------+
| id | name      | math | english | chinese |
+----+-----------+------+---------+---------+
|  4 | 曹孟德    |   84 |      67 |      82 |
|  5 | 刘玄德    |   85 |      45 |      55 |
|  6 | 孙权      |   73 |      78 |      70 |
+----+-----------+------+---------+---------+
3 rows in set (0.00 sec)

2.5 插入查询结果

将查询结果插入到另一个表中，可以使用INSERT INTO ... SELECT语句。（属于 CRUD操作中的 Create（创建）操作）

语法：

INSERT INTO table_name [(column1, column2, ...)]
SELECT select_list
FROM another_table
[WHERE condition];

说明：

• table_name: 你想要插入数据的表名。
• column1, column2, …: 可选的，指定目标表中的列名。如果省略，则假定目标表中的列与SELECT语句中的列顺序和数量相匹配。
• select_list: 从另一个表中选择的列或者表达式，它们将被插入到目标表中。
• another_table: 数据来源的表名。
• WHERE condition: 可选的，用于筛选源表中的记录

步骤说明：

1. 确定目标表：首先，确定您要将数据插入到的目标表。
2. 编写SELECT查询：编写一个SELECT查询，从源表中检索您想要插入的数据。
3. 执行INSERT INTO … SELECT：使用INSERT INTO ... SELECT语句将SELECT查询的结果插入到目标表中。
4. 匹配列和数据类型：确保SELECT查询返回的列与目标表中的列匹配，包括列的数量和数据类型。

在使用“插入 SELECT”操作时，要确保：

• 目标表中的列能够接收来自源表的列数据类型。
• 如果目标表中有不允许NULL的列，而源表的选择列表可能返回NULL值，则需要确保插入操作不会违反这些约束。
• 如果目标表有主键或唯一键约束，确保插入的数据不会违反这些约束，除非你打算在INSERT语句中使用ON DUPLICATE KEY UPDATE子句来处理冲突。

示例：

删除表中的的重复复记录，重复的数据只能有一份

-- 创建原数据表
mysql> CREATE TABLE duplicate_table (id int, name varchar(20));
Query OK, 0 rows affected (0.04 sec)
 
-- 插入测试数据
mysql> INSERT INTO duplicate_table VALUES
    -> (100, 'aaa'),
    -> (100, 'aaa'),
    -> (200, 'bbb'),
    -> (200, 'bbb'),
    -> (200, 'bbb'),
    -> (300, 'ccc');
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

-- 创建一张空表 no_duplicate_table，结构和 duplicate_table 一样
mysql> CREATE TABLE no_duplicate_table LIKE duplicate_table;
Query OK, 0 rows affected (0.03 sec)
 
-- 将 duplicate_table 的去重数据插入到 no_duplicate_table
mysql> INSERT INTO no_duplicate_table SELECT DISTINCT * FROM duplicate_table;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
 
-- 通过重命名表，实现原子的去重操作
mysql> RENAME TABLE duplicate_table TO old_duplicate_table,
    -> no_duplicate_table TO duplicate_table;
Query OK, 0 rows affected (0.04 sec)
 
-- 查看最终结果
mysql> SELECT * FROM duplicate_table;
+------+------+
| id   | name |
+------+------+
|  100 | aaa  |
|  200 | bbb  |
|  300 | ccc  |
+------+------+
3 rows in set (0.00 sec)

2.6 聚合函数

注意：

• 使用 * 做统计，不受 NULL 影响，NULL 不会计入结果

示例：

统计班级有多少同学

mysql> select * from students;
+----+------+-----------+--------+
| id | sn   | name      | qq     |
+----+------+-----------+--------+
|  1 | 1000 | 李白      | 100001 |
|  2 | 1002 | 杜甫      | 123321 |
|  3 | 1003 | sushi     | 111111 |
|  4 | 1004 | 李煜      | 234561 |
|  5 | 1005 | 辛弃疾    | NULL   |
|  6 | 1006 | 杨慎      | NULL   |
+----+------+-----------+--------+
6 rows in set (0.00 sec)
 
mysql> SELECT COUNT(*) FROM students;
+----------+
| COUNT(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

2.7 group by 和 having

在select中使用group by 子句可以对指定列进行分组查询

select column1, column2, .. from table group by column;

 having和group by配合使用，对group by结果进行过滤

select column1, column2, .. 
from table 
group by column
having ...;

HAVING和WHERE的区别

WHERE和HAVING都是用于过滤SQL查询结果的条件子句，但它们的工作范围和作用对象不同。

WHERE子句

• 作用范围：WHERE子句在执行SELECT语句之前应用于所有行，用于过滤原始的记录集。
• 过滤对象：WHERE子句过滤的是未经过GROUP BY子句处理的所有行。
• 可用的条件：WHERE子句可以包含所有的SQL条件表达式，包括比较运算符、逻辑运算符、子查询等。
• 不适用于分组后的数据：WHERE子句在执行分组之前应用，因此它不适用于分组后的数据。

HAVING子句

• 作用范围：HAVING子句在执行SELECT语句之前应用于所有行，但它的过滤是在GROUP BY子句处理后的分组结果上进行的。
• 过滤对象：HAVING子句过滤的是经过GROUP BY子句处理后的分组结果。
• 可用的条件：HAVING子句通常与聚合函数一起使用，因为它们是对分组后的结果进行操作的。HAVING子句不能包含非聚合的列，因为它们没有聚合函数。
• 适用于分组后的数据：HAVING子句在执行分组之后应用，因此它适用于分组后的数据。

三、Update

UPDATE语句用于修改表中现有记录的列值。

语法：

UPDATE table_name 
SET column = expr [, column = expr ...]
[WHERE ...] 
[ORDER BY ...]
[LIMIT ...]

说明：

• UPDATE table_name: 指定要更新数据的表名。
• SET column = expr: 指定要更新的列及其新值。可以同时更新多个列，每个列的更新用逗号隔。
• WHERE ...：可选的，用于指定哪些行应该被更新。如果不使用WHERE子句，表中的所有行都会被更新。
• ORDER BY ...：可选的，用于指定更新行的顺序。通常与LIMIT一起使用，以确保只更新特定的行。
• LIMIT ...：可选的，用于限制被更新的行数。
• SET 不支持 += 操作。

示例：

更新值为具体值：将孙悟空同学的数学成绩变更为 80 分

mysql> UPDATE exam_result SET math = 80 WHERE name = '孙悟空';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> SELECT name, math FROM exam_result WHERE name = '孙悟空';
+-----------+------+
| name      | math |
+-----------+------+
| 孙悟空    |   80 |
+-----------+------+
1 row in set (0.00 sec)

一次更新多个列：将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分

mysql> UPDATE exam_result SET math=60,chinese=70 WHERE name='曹孟德';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> SELECT name, math, chinese FROM exam_result WHERE name='曹孟德';
+-----------+------+---------+
| name      | math | chinese |
+-----------+------+---------+
| 曹孟德    |   60 |      70 |
+-----------+------+---------+
1 row in set (0.00 sec)

更新值为原值基础上变更：将总成绩倒数前三的 3 位同学的数学成绩加上 30 分

mysql> SELECT name, math, chinese + math + english 总分 FROM exam_result
    -> ORDER BY 总分 LIMIT 3;
+-----------+------+--------+
| name      | math | 总分   |
+-----------+------+--------+
| 宋公明    |   95 |    200 |
| 刘玄德    |  115 |    215 |
| 唐三藏    |   98 |    221 |
+-----------+------+--------+
3 rows in set (0.00 sec)
 
mysql> UPDATE exam_result SET math = math + 30
    -> ORDER BY chinese + math + english LIMIT 3;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0
 
mysql> SELECT name, math, chinese + math + english 总分 FROM exam_result
    -> WHERE name IN ('宋公明', '刘玄德', '唐三藏');
+-----------+------+--------+
| name      | math | 总分   |
+-----------+------+--------+
| 唐三藏    |  128 |    251 |
| 刘玄德    |  145 |    245 |
| 宋公明    |  125 |    230 |
+-----------+------+--------+
3 rows in set (0.00 sec)

四、Delete

4.1 delete

语法：

DELETE FROM table_name 
[WHERE ...] 
[ORDER BY ...] 
[LIMIT ...]

注意：

• 操作类型：DELETE是一个DML（Data Manipulation Language）操作，用于删除表中的行。
• 删除方式：可以删除表中的部分行，通过WHERE子句指定条件来删除特定的行。
• 性能：删除大量数据时可能会较慢，因为它会逐行删除，并且每行删除都会记录在事务日志中。
• 触发器：会触发与删除操作相关的触发器（如果有的话）。
• 返回信息：执行后返回被删除的行数。

示例：

删除孙悟空同学的考试成绩

mysql> DELETE FROM exam_result WHERE name = '孙悟空';
Query OK, 1 row affected (0.00 sec)
 
mysql> SELECT * FROM exam_result WHERE name = '孙悟空';
Empty set (0.00 sec)

删除整张表数据

-- 准备测试表
CREATE TABLE for_delete (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20)
);
Query OK, 0 rows affected (0.16 sec)
 
-- 插入测试数据
INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C');
Query OK, 3 rows affected (1.05 sec)
Records: 3 Duplicates: 0 Warnings: 0
 
-- 查看测试数据
SELECT * FROM for_delete;
+----+------+
| id | name |
+----+------+
| 1  | A    |
| 2  | B    |
| 3  | C    |
+----+------+
3 rows in set (0.00 sec)
 
-- 删除整表数据
DELETE FROM for_delete;
Query OK, 3 rows affected (0.00 sec)
 
-- 查看删除结果
SELECT * FROM for_delete;
Empty set (0.00 sec)
 
-- 再插入一条数据，自增 id 在原值上增长
INSERT INTO for_delete (name) VALUES ('D');
Query OK, 1 row affected (0.00 sec)
 
-- 查看数据
SELECT * FROM for_delete;
+----+------+
| id | name |
+----+------+
| 4  | D    |
+----+------+
1 row in set (0.00 sec)
 
-- 查看表结构，会有 AUTO_INCREMENT=n 项
SHOW CREATE TABLE for_delete\G
*************************** 1. row ***************************
Table: for_delete
Create Table: CREATE TABLE `for_delete` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

4.2 truncate

语法：

TRUNCATE [TABLE] table_name

注意：

• TABLE关键字是可省略的。
• 操作类型：TRUNCATE是一个DDL（Data Definition Language）操作，用于删除表中的所有行。
• 删除方式：删除表中的所有行，但不删除表结构。
• 性能：通常比DELETE快，因为它不会逐行删除，而是直接删除数据页，并且不会记录每一行的删除操作。
• 触发器：不会触发删除行的触发器。
• 返回信息：执行后不返回被删除的行数

DELETE 和 TRUNCATE 的对比

• 数据恢复：使用DELETE时，如果需要，可以通过事务回滚来恢复数据。而TRUNCATE操作通常不可逆，因为它不记录每一行的删除操作。
• 自增字段：使用TRUNCATE后，表的自增字段（如果有的话）会被重置。而使用DELETE则不会重置 AUTO_INCREMENT项。
• 外键约束：如果表中存在外键约束，使用DELETE可能会因为违反约束而失败，而TRUNCATE通常不会检查外键约束。
• 触发器：如果你依赖于删除操作的触发器，那么应该使用DELETE，因为TRUNCATE不会触发这些触发器。
• 锁：TRUNCATE通常会在表上获取更少的锁，这可能会对并发操作有影响。

示例： 

-- 准备测试表
CREATE TABLE for_truncate (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);
Query OK, 0 rows affected (0.16 sec)
 
-- 插入测试数据
INSERT INTO for_truncate (name) VALUES ('A'), ('B'), ('C');
Query OK, 3 rows affected (1.05 sec)
Records: 3 Duplicates: 0 Warnings: 0
 
-- 查看测试数据
SELECT * FROM for_truncate;
+----+------+
| id | name |
+----+------+
| 1  | A    |
| 2  | B    |
| 3  | C    |
+----+------+
3 rows in set (0.00 sec)
 
-- 截断整表数据，注意影响行数是 0，所以实际上没有对数据真正操作
TRUNCATE for_truncate;
Query OK, 0 rows affected (0.10 sec)
 
-- 查看删除结果
SELECT * FROM for_truncate;
Empty set (0.00 sec)
 
-- 再插入一条数据，自增 id 在重新增长
INSERT INTO for_truncate (name) VALUES ('D');
Query OK, 1 row affected (0.00 sec)
 
-- 查看数据
SELECT * FROM for_truncate;
+----+------+
| id | name |
+----+------+
| 1  | D    |
+----+------+
1 row in set (0.00 sec)
 
-- 查看表结构，会有 AUTO_INCREMENT=2 项
SHOW CREATE TABLE for_truncate\G
*************************** 1. row ***************************
Table: for_truncate
Create Table: CREATE TABLE `for_truncate` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)