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: 0

mysql> 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

注意：这个操作慎用

1. 只能对整表操作，不能像DELETE一样针对部分数据操作；
2. 实际上MySQL不对数据操作，所以比DELETE更快，但是TRUNCATE在删除数据的时候，并不经过真正的事务，所以无法回滚
3. 会重置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: 0

mysql> 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)