SQL之删除

1、删除记录的方式汇总：

• 根据条件删除：DELETE FROM tb_name [WHERE options] [ [ ORDER BY fields ] LIMIT n ]
• 全部删除（表清空，包含自增计数器重置）：TRUNCATE tb_name

2、时间差：

• TIMESTAMPDIFF(interval, time_start, time_end)可计算time_start-time_end的时间差，单位以指定的interval为准，常用可选：
  • SECOND 秒
  • MINUTE 分钟（返回秒数差除以60的整数部分）
  • HOUR 小时（返回秒数差除以3600的整数部分）
  • DAY 天数（返回秒数差除以3600*24的整数部分）
  • MONTH 月数
  • YEAR 年数

3、TRUNCATE 和 DELETE 的区别

• 从逻辑上说，TRUNCATE 语句与 DELETE 语句作用相同，但是在某些情况下，两者在使用上有所区别。
• DELETE 是 DML 类型的语句；TRUNCATE 是 DDL 类型的语句。它们都用来清空表中的数据。
• DELETE 是逐行一条一条删除记录的；TRUNCATE 则是直接删除原来的表，再重新创建一个一模一样的新表，而不是逐行删除表中的数据，执行数据比 DELETE 快。因此需要删除表中全部的数据行时，尽量使用 TRUNCATE 语句， 可以缩短执行时间。
• DELETE 删除数据后，配合事件回滚可以找回数据；TRUNCATE 不支持事务的回滚，数据删除后无法找回。
• DELETE 删除数据后，系统不会重新设置自增字段的计数器；TRUNCATE 清空表记录后，系统会重新设置自增字段的计数器。
• DELETE 的使用范围更广，因为它可以通过 WHERE 子句指定条件来删除部分数据；而 TRUNCATE 不支持 WHERE 子句，只能删除整体。
• DELETE 会返回删除数据的行数，但是 TRUNCATE 只会返回 0，没有任何意义

4、案例

（1）现有一张试卷作答记录表exam_record，其中包含多年来的用户作答试卷记录，结构如下表：

作答记录表exam_record：

start_time是试卷开始时间

submit_time 是交卷，即结束时间

Filed	Type	Null	Key	Extra	Default	Comment
id	int(11)	NO	PRI	auto_increment	(NULL)	自增ID
uid	int(11)	NO			(NULL)	用户ID
exam_id	int(11)	NO			(NULL)	试卷ID
start_time	datetime	NO			(NULL)	开始时间
submit_time	datetime	YES			(NULL)	提交时间
score	tinyint(4)	YES			(NULL)	得分

DELETE FROM exam_record
WHERE TIMESTAMPDIFF(MINUTE, start_time, submit_time) < 5
    AND score < 60;

请删除exam_record表中作答时间小于5分钟整且分数不及格（及格线为60分）的记录；

（2）现有一张试卷作答记录表exam_record，其中包含多年来的用户作答试卷记录，结构如下表：

作答记录表exam_record：

start_time是试卷开始时间

submit_time 是交卷时间，即结束时间，如果未完成的话，则为空

Filed	Type	Null	Key	Extra	Default	Comment
id	int(11)	NO	PRI	auto_increment	(NULL)	自增ID
uid	int(11)	NO			(NULL)	用户ID
exam_id	int(11)	NO			(NULL)	试卷ID
start_time	datetime	NO			(NULL)	开始时间
submit_time	datetime	YES			(NULL)	提交时间
score	tinyint(4)	YES			(NULL)	得分

Delete from exam_record
WHERE TIMESTAMPDIFF(MINUTE, start_time, submit_time) < 5
    or submit_time IS NULL
order by start_time
limit 3;

请删除exam_record表中未完成作答或作答时间小于5分钟整的记录中，开始作答时间最早的3条记录。

(3)现有一张试卷作答记录表exam_record，其中包含多年来的用户作答试卷记录，结构如下表：

Filed	Type	Null	Key	Extra	Default	Comment
id	int(11)	NO	PRI	auto_increment	(NULL)	自增ID
uid	int(11)	NO			(NULL)	用户ID
exam_id	int(11)	NO			(NULL)	试卷ID
start_time	datetime	NO			(NULL)	开始时间
submit_time	datetime	YES			(NULL)	提交时间
score	tinyint(4)	YES			(NULL)	得分

DELETE FROM exam_record;
ALTER TABLE exam_record drop id;

请删除exam_record表中所有记录，并重置自增主键。