成全视频免费观看在线看下载动漫_千图网官网素材_网址和网站的区别_各国足球世界排名

 

目录

一、前言

二、正文

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) 

三、结语

        到此为止，本文关于MySQL基本查询中的删的内容到此结束了，如有不足之处，欢迎小伙伴们指出呀！

         关注我 _麦麦_分享更多干货：_麦麦_-CSDN博客

         大家的「关注❤️ + 点赞👍 + 收藏⭐」就是我创作的最大动力！谢谢大家的支持，我们下期见！