MySQL数据库——表的CURD（Delete）
4.Delete
①删除数据
语法：delete from table_name [where...] [order by...] [limit...]
案例
mysql> select * from result where name='孙悟空';
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 2 | 孙悟空 | 174 | 80 | 77 |
+----+-----------+---------+------+---------+
1 row in set (0.00 sec)mysql> delete from result where name='孙悟空';
Query OK, 1 row affected (0.01 sec)mysql> select * from result;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 134 | 98 | 56 |
| 3 | 猪悟能 | 176 | 98 | 90 |
| 4 | 曹孟德 | 140 | 90 | 67 |
| 5 | 刘玄德 | 110 | 115 | 45 |
| 6 | 孙权 | 140 | 73 | 78 |
| 7 | 宋公明 | 150 | 95 | 30 |
+----+-----------+---------+------+---------+
6 rows in set (0.00 sec)-- 删除总分最低的同学
mysql> select name,chinese+math+english total from result order by total limit 1;
+-----------+-------+
| name | total |
+-----------+-------+
| 刘玄德 | 270 |
+-----------+-------+
1 row in set (0.00 sec)mysql> delete from result order by english+math+chinese limit 1;
Query OK, 1 row affected (0.00 sec)mysql> select name,chinese+math+english total from result order by total limit 1;
+-----------+-------+
| name | total |
+-----------+-------+
| 宋公明 | 275 |
+-----------+-------+
1 row in set (0.00 sec)mysql> select* from result;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 134 | 98 | 56 |
| 3 | 猪悟能 | 176 | 98 | 90 |
| 4 | 曹孟德 | 140 | 90 | 67 |
| 6 | 孙权 | 140 | 73 | 78 |
| 7 | 宋公明 | 150 | 95 | 30 |
+----+-----------+---------+------+---------+
5 rows in set (0.00 sec)
-- 准备测试表
mysql> CREATE TABLE for_delete ( -> id INT PRIMARY KEY AUTO_INCREMENT, -> name VARCHAR(20) -> );
Query OK, 0 rows affected (0.02 sec)-- 准备测试数据
mysql> INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0mysql> select * from for_delete;
+----+------+
| id | name |
+----+------+
| 1 | A |
| 2 | B |
| 3 | C |
+----+------+
3 rows in set (0.00 sec)-- 此时AUTO_INCREMENT=4
mysql> show create table for_delete;
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| for_delete | CREATE TABLE `for_delete` (`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)-- 删除整张表
mysql> delete from for_delete;
Query OK, 3 rows affected (0.00 sec)-- 表已经空了
mysql> select * from for_delete;
Empty set (0.00 sec)-- AUTO_INCREMENT=4
mysql> show create table for_delete;
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| for_delete | CREATE TABLE `for_delete` (`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)mysql> insert into for_delete (name) values ('D');
Query OK, 1 row affected (0.00 sec)mysql> select * from for_delete;
+----+------+
| id | name |
+----+------+
| 4 | D |
+----+------+
1 row in set (0.00 sec)-- AUTO_INCREMENT=5
mysql> show create table for_delete;
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| for_delete | 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)
②截断表
语法：truncate [table] table_name
注意：这个操作慎用
- 只能对整表操作，不能像
DELETE一样针对部分数据操作； - 实际上MySQL不对数据操作，所以比
DELETE更快，但是TRUNCATE在删除数据的时候，并不经过真正的事务，所以无法回滚 - 会重置
AUTO_INCREMENT项
-- 准备测试表
mysql> CREATE TABLE for_truncate ( -> id INT PRIMARY KEY AUTO_INCREMENT, -> name VARCHAR(20) -> );
Query OK, 0 rows affected (0.02 sec)-- 准备测试数据
mysql> INSERT INTO for_truncate (name) VALUES ('A'), ('B'), ('C');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0mysql> desc for_truncate;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)mysql> select * from for_truncate;
+----+------+
| id | name |
+----+------+
| 1 | A |
| 2 | B |
| 3 | C |
+----+------+
3 rows in set (0.00 sec)-- AUTO_INCREMENT=4
mysql> 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=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)-- 截断表
mysql> truncate for_truncate;
Query OK, 0 rows affected (0.01 sec)-- 表还在
mysql> show tables;
+-------------------+
| Tables_in_user_db |
+-------------------+
| for_delete |
| for_truncate |
| result |
| test |
+-------------------+
4 rows in set (0.00 sec)-- 数据已经清空
mysql> select * from for_truncate;
Empty set (0.00 sec)-- AUTO_INCREMENT不见了
mysql> 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 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)-- 插入数据
mysql> insert into for_truncate (name) values ('E');
Query OK, 1 row affected (0.01 sec)-- id重新开始计数
mysql> select * from for_truncate;
+----+------+
| id | name |
+----+------+
| 1 | E |
+----+------+
1 row in set (0.00 sec)-- AUTO_INCREMENT=2
mysql> 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)