MySQL之删除数据
MySQL 刘宇帅 5年前 阅读量: 1381
单表删除语法
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias]
[PARTITION (partition_name [, partition_name] ...)]
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
多表删除
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
tbl_name[.*] [, tbl_name[.*]] ...
FROM table_references
[WHERE where_condition]
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
FROM tbl_name[.*] [, tbl_name[.*]] ...
USING table_references
[WHERE where_condition]
使用修饰符LOW_PRIORITY时 SQL 执行优先级会很低直到所有查询语句执行完毕才执行。LOW_PRIORITY 只对只支持表锁的引擎有用,例如 MyISAM、MEMORY、MERGE。
使用修饰符QUICK 时,存储引擎不会去处理因删除数据而需要合并索引叶子节点,这样可以在一定程度上加快删除速度,例如 MyISAM。
修饰符IGNORE 用来忽略 SQL 执行过程中的错误报错。
另外 delete 语句支持 order by 和 limit 来删除满足 where 的部分数据。
删除单表例子
根据id删除一个学生
mysql> delete from students where id=1;
Query OK, 1 row affected (0.00 sec)
使用 order by
limit
删除 id 最大的学生
mysql> delete from students order by id desc limit 1;
Query OK, 1 row affected (0.00 sec)
mysql> select * from students;
+----+------+--------+-----+------+---------------------+---------------------+
| id | no | name | sex | age | created_at | updated_at |
+----+------+--------+-----+------+---------------------+---------------------+
| 2 | 0002 | 冷锋 | 2 | 25 | 2019-12-20 20:00:00 | 2019-12-20 20:00:00 |
| 3 | 0003 | 六一 | 2 | 25 | 2019-12-20 20:00:00 | 2019-12-20 20:00:00 |
| 4 | 0004 | 杨过 | 2 | 25 | 2019-12-20 00:26:14 | NULL |
| 6 | 0006 | 铁蛋 | 2 | 25 | 2019-12-20 01:51:46 | NULL |
+----+------+--------+-----+------+---------------------+---------------------+
4 rows in set (0.00 sec)
删除多表的例子
新建一个作业表并初始化一部分数据
mysql> CREATE TABLE `homeworks` (
-> `id` int NOT NULL AUTO_INCREMENT COMMENT '作业id',
-> `student_id` int NOT NULL COMMENT '学生学号',
-> `name` varchar(128) NOT NULL COMMENT '作业名字',
-> `status` tinyint NOT NULL DEFAULT '0' COMMENT '0 未做 1 已提交 2 已批改',
-> `content` text DEFAULT NULL COMMENT '作业内容',
-> `score` int not null default 0 comment '作业分数',
-> `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
-> `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
-> PRIMARY KEY (`id`),
-> index `student_id_index` (`student_id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='作业表';
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql>
mysql>
mysql> insert into homeworks (`student_id`,`name`) values(3,'1+1'),(3,'2+2'),(2,'1+1');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from homeworks;
+----+------------+------+--------+---------+-------+---------------------+------------+
| id | student_id | name | status | content | score | created_at | updated_at |
+----+------------+------+--------+---------+-------+---------------------+------------+
| 1 | 3 | 1+1 | 0 | NULL | 0 | 2019-12-29 22:39:57 | NULL |
| 2 | 3 | 2+2 | 0 | NULL | 0 | 2019-12-29 22:39:57 | NULL |
| 3 | 2 | 1+1 | 0 | NULL | 0 | 2019-12-29 22:39:57 | NULL |
+----+------------+------+--------+---------+-------+---------------------+------------+
删除 id=3 的学生及其所有作业
mysql> delete s,h from students s left join homeworks h on s.id=h.student_id where s.id=3;
Query OK, 2 rows affected (0.00 sec)
mysql> select * from students;
+----+------+--------+-----+------+---------------------+------------+
| id | no | name | sex | age | created_at | updated_at |
+----+------+--------+-----+------+---------------------+------------+
| 4 | 0004 | 杨过 | 2 | 25 | 2019-12-20 00:26:14 | NULL |
| 6 | 0006 | 铁蛋 | 2 | 25 | 2019-12-20 01:51:46 | NULL |
+----+------+--------+-----+------+---------------------+------------+
2 rows in set (0.00 sec)
mysql> select * from homeworks;
+----+------------+------+--------+---------+-------+---------------------+------------+
| id | student_id | name | status | content | score | created_at | updated_at |
+----+------------+------+--------+---------+-------+---------------------+------------+
| 1 | 1 | 1+1 | 0 | NULL | 0 | 2019-12-29 22:39:57 | NULL |
| 2 | 1 | 2+2 | 0 | NULL | 0 | 2019-12-29 22:39:57 | NULL |
+----+------------+------+--------+---------+-------+---------------------+------------+
2 rows in set (0.00 sec)
truncate
我们有时候需要清除数据库数据,我们可以使用 delete 也可以用 truncate,两者对比如下
- truncate 只能用于表,delete 可以用做表和视图
- delete语句为DML,这个操作会被放到 rollback segment中,事务提交后才生效。如果有 trigger,执行的时候也会触发。
- truncate DDL 操作立即生效,原数据不放到 rollback segment中,不能回滚。
- 有 FOREIGN KEY 的表不能执行 truncate,truncate 不返回影响的行数,truncate 执行后表中所有 AUTO_INCREMENT 的行会重置为 0,truncate 不记录到日志不触发 trigger 所以速度比 delete 快很多,但是我们要谨慎使用 truncate