Question

SQL 插入记录的方式汇总：

• 普通插入（全字段） ： INSERT INTO table_name VALUES (value1, value2, ...)
• 普通插入（限定字段） ： INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...)
• 多条一次性插入 ： INSERT INTO table_name (column1, column2, ...) VALUES (value1_1, value1_2, ...), (value2_1, value2_2, ...), ...
• 从另一个表导入 ： INSERT INTO table_name SELECT * FROM table_name2 [WHERE key=value]
• 带更新的插入 ： REPLACE INTO table_name VALUES (value1, value2, ...) （注意这种原理是检测到主键或唯一性索引键重复就删除原记录后重新插入）

插入记录（一）

描述 ：牛客后台会记录每个用户的试卷作答记录到 exam_record 表，现在有两个用户的作答记录详情如下：

• 用户 1001 在 2021 年 9 月 1 日晚上 10 点 11 分 12 秒开始作答试卷 9001，并在 50 分钟后提交，得了 90 分；
• 用户 1002 在 2021 年 9 月 4 日上午 7 点 1 分 2 秒开始作答试卷 9002，并在 10 分钟后退出了平台。

试卷作答记录表 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)	得分

答案 ：

// 存在自增主键，无需手动赋值
INSERT INTO exam_record (uid, exam_id, start_time, submit_time, score) VALUES
(1001, 9001, '2021-09-01 22:11:12', '2021-09-01 23:01:12', 90),
(1002, 9002, '2021-09-04 07:01:02', NULL, NULL);

插入记录（二）

描述 ：现有一张试卷作答记录表 exam_record ，结构如下表，其中包含多年来的用户作答试卷记录，由于数据越来越多，维护难度越来越大，需要对数据表内容做精简，历史数据做备份。

表 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)	得分

我们已经创建了一张新表 exam_record_before_2021 用来备份 2021 年之前的试题作答记录，结构和 exam_record 表一致，请将 2021 年之前的已完成了的试题作答纪录导入到该表。

答案 ：

INSERT INTO exam_record_before_2021 (uid, exam_id, start_time, submit_time, score)
SELECT uid,exam_id,start_time,submit_time,score
FROM exam_record
WHERE YEAR(submit_time) < 2021;

插入记录（三）

描述 ：现在有一套 ID 为 9003 的高难度 SQL 试卷，时长为一个半小时，请你将 2021-01-01 00:00:00 作为发布时间插入到试题信息表 examination_info ，不管该 ID 试卷是否存在，都要插入成功，请尝试插入它。

试题信息表 examination_info ：

Filed	Type	Null	Key	Extra	Default	Comment
id	int(11)	NO	PRI	auto_increment	(NULL)	自增 ID
exam_id	int(11)	NO	UNI		(NULL)	试卷 ID
tag	varchar(32)	YES			(NULL)	类别标签
difficulty	varchar(8)	YES			(NULL)	难度
duration	int(11)	NO			(NULL)	时长（分钟数)
release_time	datetime	YES			(NULL)	发布时间

答案 ：

REPLACE INTO examination_info VALUES
 (NULL, 9003, "SQL", "hard", 90, "2021-01-01 00:00:00");

更新记录（一）

描述 ：现在有一张试卷信息表 examination_info , 表结构如下图所示：

Filed	Type	Null	Key	Extra	Default	Comment
id	int(11)	NO	PRI	auto_increment	(NULL)	自增 ID
exam_id	int(11)	NO	UNI		(NULL)	试卷 ID
tag	char(32)	YES			(NULL)	类别标签
difficulty	char(8)	YES			(NULL)	难度
duration	int(11)	NO			(NULL)	时长
release_time	datetime	YES			(NULL)	发布时间

请把 examination_info 表中 tag 为 PYTHON 的 tag 字段全部修改为 Python 。

思路 ：这题有两种解题思路，最容易想到的是直接 update + where 来指定条件更新，第二种就是根据要修改的字段进行查找替换

答案一 ：

UPDATE examination_info SET tag = 'Python' WHERE tag='PYTHON'

答案二 ：

UPDATE examination_info
SET tag = REPLACE(tag,'PYTHON','Python')

# REPLACE (目标字段，"查找内容","替换内容")

更新记录（二）

描述 ：现有一张试卷作答记录表 exam_record，其中包含多年来的用户作答试卷记录，结构如下表：作答记录表 exam_record ： 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)	得分

题目要求 ：请把 exam_record 表中 2021 年 9 月 1 日==之前==开始作答的==未完成==记录全部改为被动完成，即：将完成时间改为'2099-01-01 00:00:00'，分数改为 0。

思路 ：注意题干中的关键字（已经高亮) " xxx 时间 " 之前这个条件， 那么这里马上就要想到要进行时间的比较 可以直接 xxx_time < "2021-09-01 00:00:00", 也可以采用 date() 函数来进行比较；第二个条件就是 "未完成" ， 即完成时间为 NULL，也就是题目中的提交时间 ----- submit_time 为 NULL 。

答案 ：

UPDATE exam_record SET submit_time = '2099-01-01 00:00:00', score = 0 WHERE DATE(start_time) < "2021-09-01" AND submit_time IS null

删除记录（一）

描述 ：现有一张试卷作答记录表 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)	得分

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

思路 ：这一题虽然是练习删除，仔细看确是考察对时间函数的用法，这里提及的分钟数比较，常用的函数有 TIMEDIFF 和 TIMESTAMPDIFF ，两者用法稍有区别，后者更为灵活，这都是看个人习惯。

1.　 TIMEDIFF ：两个时间之间的差值

TIMEDIFF(time1, time2)

两者参数都是必须的，都是一个时间或者日期时间表达式。如果指定的参数不合法或者是 NULL，那么函数将返回 NULL。

对于这题而言，可以用在 minute 函数里面，因为 TIMEDIFF 计算出来的是时间的差值，在外面套一个 MINUTE 函数，计算出来的就是分钟数。

1. TIMESTAMPDIFF ：用于计算两个日期的时间差

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)
# 参数说明
#unit: 日期比较返回的时间差单位，常用可选值如下:
SECOND：秒
MINUTE：分钟
HOUR：小时
DAY：天
WEEK：星期
MONTH：月
QUARTER：季度
YEAR：年
# TIMESTAMPDIFF 函数返回 datetime_expr2 - datetime_expr1 的结果（人话： 后面的 - 前面的  即 2-1），其中 datetime_expr1 和 datetime_expr2 可以是 DATE 或 DATETIME 类型值（人话：可以是“2023-01-01”， 也可以是“2023-01-01- 00:00:00”）

这题需要进行分钟的比较，那么就是 TIMESTAMPDIFF(MINUTE, 开始时间， 结束时间) < 5

答案 ：

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

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

删除记录（二）

描述 ：现有一张试卷作答记录表 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)	分数

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

思路 ：这题比较简单，但是要注意题干中给出的信息，结束时间，如果未完成的话，则为空，这个其实就是一个条件

还有一个条件就是小于 5 分钟，跟上题类似，但是这里是 或 ，即两个条件满足一个就行；另外就是稍微考察到了排序和 limit 的用法。

答案 ：

DELETE FROM exam_record WHERE submit_time IS null OR TIMESTAMPDIFF(MINUTE, start_time, submit_time) < 5
ORDER BY start_time
LIMIT 3
# 默认就是 asc， desc 是降序排列

删除记录（三）

描述 ：现有一张试卷作答记录表 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)	分数

要求 ：请删除 exam_record 表中所有记录，==并重置自增主键==

思路 ：这题考察对三种删除语句的区别，注意高亮部分，要求重置主键；

• DROP : 清空表，删除表结构，不可逆
• TRUNCATE : 格式化表，不删除表结构，不可逆
• DELETE ：删除数据，可逆

这里选用 TRUNCATE 的原因是：TRUNCATE 只能作用于表； TRUNCATE 会清空表中的所有行，但表结构及其约束、索引等保持不变； TRUNCATE 会重置表的自增值；使用 TRUNCATE 后会使表和索引所占用的空间会恢复到初始大小。

这题也可以采用 DELETE 来做，但是在删除后，还需要手动 ALTER 表结构来设置主键初始值；

同理也可以采用 DROP 来做，直接删除整张表，包括表结构，然后再新建表即可。

答案 ：

TRUNCATE  exam_record;