当前位置：首页 > article >正文

《剑指数据库：MySQL玄阶查术秘典·下卷》

article 2025/3/23 15:36:27

一、前言

二、正文

1.Delete

1.1 删除数据

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

1.1.2 删除整张表数据

1.2 截断表

三、结语

一、前言

本文将继续为大家带来MySQL基本查询中有关删的操作，希望小伙伴们能够从中有所收获！！！

二、正文

1.Delete

1.1 删除数据

语法：

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

案例：

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

-- 查看原数据

SELECT * FROM exam_result WHERE name = '孙悟空';

-- 查看原数据 
SELECT * FROM exam_result WHERE name = '孙悟空'; 
+----+-----------+-------+--------+--------+ 
| id | name | chinese | math | english | 
+----+-----------+-------+--------+--------+ 
| 2 | 孙悟空 | 174 | 80 | 77 | 
+----+-----------+-------+--------+--------+ 
1 row in set (0.00 sec)

-- 删除数据 DELETE FROM exam_result WHERE name = '孙悟空';

-- 删除数据 
DELETE FROM exam_result WHERE name = '孙悟空'; 
Query OK, 1 row affected (0.17 sec)

-- 查看删除结果 SELECT * FROM exam_result WHERE name = '孙悟空';

-- 查看删除结果 
SELECT * FROM exam_result WHERE name = '孙悟空'; 
Empty set (0.00 sec)

1.1.2 删除整张表数据

注意：删除整表操作要慎用！

-- 准备测试表 CREATE TABLE for_delete ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) );

-- 准备测试表 
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');

-- 插入测试数据 
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;

-- 查看测试数据 
SELECT * FROM for_delete; 
+----+------+ 
| id | name | 
+----+------+ 
| 1 | A | 
| 2 | B | 
| 3 | C | 
+----+------+ 
3 rows in set (0.00 sec)

-- 删除整表数据

DELETE FROM for_delete;

-- 删除整表数据 
 
DELETE FROM for_delete; 
Query OK, 3 rows affected (0.00 sec)

-- 查看删除结果 SELECT * FROM for_delete;

-- 查看删除结果 
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)

1.2 截断表

语法：

TRUNCATE [TABLE] table_name

注意：这个操作慎用

1. 只能对整表操作，不能像 DELETE 一样针对部分数据操作；

2. 实际上 MySQL 不对数据操作，所以比 DELETE 更快，但是TRUNCATE在删除数据的时候，并不经过真正的事物，所以无法回滚

3. 会重置 AUTO_INCREMENT 项

-- 准备测试表 
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)