《剑指数据库：MySQL玄阶查术秘典·下卷》
https://i-blog.csdnimg.cn/direct/ad3113eba3f940dd98a152638cc2ccf5.gifhttps://i-blog.csdnimg.cn/direct/0faccedb0c404b839f672654bdbdf87d.png
目次
一、媒介
二、正文
1.Delete
1.1 删除数据
1.1.1 删除孙悟空同学的考试成绩
1.1.2 删除整张表数据
1.2 截断表
三、结语
一、媒介
本文将继续为各人带来MySQL基本查询中有关删的操作，希望小搭档们能够从中有所劳绩！！！
二、正文
1.Delete
1.1 删除数据
语法：
DELETE FROM table_name
案例：
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_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博客
各人的「关注❤️ + 点赞
页:
[1]