数据库系统原理与应用教程（067）—— MySQL 练习题：操作题 82-89（十一）：数据的增、删、改操作

82、插入数据记录（1）

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

现有两个用户的答题记录详情如下：

（1）用户 1001 在 2021 年 9 月 1 日晚上 10 点 11 分 12 秒开始作答试卷 9001，并在 50 分钟后提交，得了 90 分；

（2）用户 1002 在 2021 年 9 月 4 日上午 7 点 1 分 2 秒开始作答试卷 9002，并在 10 分钟后退出了平台。

表 exam_record 的结构：

解答：


mysql> insert into exam_record(uid, exam_id, start_time, submit_time, score) 
    -> values(1001, 9001, '2021-9-1 22:11:12', '2021-9-1 23:01:12', 90);
:11:2', null);Query OK, 1 row affected (0.00 sec)

mysql> insert into exam_record(uid, exam_id, start_time, submit_time, score) 
    -> values(1002, 9002, '2021-9-4 7:1:2', '2021-9-4 7:11:2', null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from exam_record;
+----+------+---------+---------------------+---------------------+-------+
| id | uid  | exam_id | start_time          | submit_time         | score |
+----+------+---------+---------------------+---------------------+-------+
|  1 | 1001 |    9001 | 2021-09-01 22:11:12 | 2021-09-01 23:01:12 |    90 |
|  2 | 1002 |    9002 | 2021-09-04 07:01:02 | 2021-09-04 07:11:02 |  NULL |
+----+------+---------+---------------------+---------------------+-------+
2 rows in set (0.00 sec)

83、插入数据记录（2）

数据表：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 中包含多年来用户作答试卷记录。创建一张新表 exam_record_before_2021（表结构和 exam_record 表一致）用来备份 2021 年之前的试题作答记录，请将 2021 年之前的已完成了的试题作答纪录导入到该表。

表结构及数据如下：

解答：

mysql> select * from exam_record_before_2021;
+----+------+---------+---------------------+---------------------+-------+
| id | uid  | exam_id | start_time          | submit_time         | score |
+----+------+---------+---------------------+---------------------+-------+
|  2 | 1001 |    9002 | 2020-01-02 09:01:01 | 2020-01-02 09:21:01 |    70 |
+----+------+---------+---------------------+---------------------+-------+
1 row in set (0.00 sec)

84、插入数据记录（3）

试题信息表：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)	发布时间

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

表结构及数据如下：

mysql> select * from examination_info;
+----+---------+--------+------------+----------+---------------------+
| id | exam_id | tag    | difficulty | duration | release_time        |
+----+---------+--------+------------+----------+---------------------+
|  1 |    9001 | SQL    | hard       |       60 | 2020-01-01 10:00:00 |
|  2 |    9002 | 算法   | easy       |       60 | 2020-01-01 10:00:00 |
|  3 |    9003 | SQL    | medium     |       60 | 2020-01-02 10:00:00 |
|  4 |    9004 | 算法   | hard       |       80 | 2020-01-01 10:00:00 |
+----+---------+--------+------------+----------+---------------------+
4 rows in set (0.00 sec)

解答：

mysql> replace into examination_info(exam_id, tag, difficulty, duration, release_time)
    -> values(9003, 'SQL', 'hard', 90, '2021-01-01 00:00:00');
Query OK, 2 rows affected (0.00 sec)

mysql> select * from examination_info;
+----+---------+--------+------------+----------+---------------------+
| id | exam_id | tag    | difficulty | duration | release_time        |
+----+---------+--------+------------+----------+---------------------+
|  1 |    9001 | SQL    | hard       |       60 | 2020-01-01 10:00:00 |
|  2 |    9002 | 算法   | easy       |       60 | 2020-01-01 10:00:00 |
|  4 |    9004 | 算法   | hard       |       80 | 2020-01-01 10:00:00 |
|  5 |    9003 | SQL    | hard       |       90 | 2021-01-01 00:00:00 |
+----+---------+--------+------------+----------+---------------------+
4 rows in set (0.00 sec)

85、更新数据记录（1）

现有一张试卷信息表：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】的数据全部修改为【Python】。

表结构及数据如下：

解答：

mysql> select * from examination_info;
+----+---------+--------+------------+----------+---------------------+
| id | exam_id | tag    | difficulty | duration | release_time        |
+----+---------+--------+------------+----------+---------------------+
|  1 |    9001 | SQL    | hard       |       60 | 2020-01-01 10:00:00 |
|  2 |    9002 | python | easy       |       60 | 2020-01-01 10:00:00 |
|  3 |    9003 | Python | medium     |       80 | 2020-01-01 10:00:00 |
|  4 |    9004 | PYTHON | hard       |       80 | 2020-01-01 10:00:00 |
+----+---------+--------+------------+----------+---------------------+
4 rows in set (0.00 sec)

mysql> update examination_info set tag = 'Python' where tag = 'PYTHON';
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from examination_info;
+----+---------+--------+------------+----------+---------------------+
| id | exam_id | tag    | difficulty | duration | release_time        |
+----+---------+--------+------------+----------+---------------------+
|  1 |    9001 | SQL    | hard       |       60 | 2020-01-01 10:00:00 |
|  2 |    9002 | python | easy       |       60 | 2020-01-01 10:00:00 |
|  3 |    9003 | Python | medium     |       80 | 2020-01-01 10:00:00 |
|  4 |    9004 | Python | hard       |       80 | 2020-01-01 10:00:00 |
+----+---------+--------+------------+----------+---------------------+
4 rows in set (0.00 sec)

86、更新数据记录（2）

现有一张试卷答题记录表：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 表中 2021 年 9 月 1 日之前开始作答的未完成记录全部改为被动完成。即：将完成时间改为 ‘2099-01-01 00:00:00’，分数改为 0。

表结构及数据如下：

解答：

mysql> select * from exam_record;
+----+------+---------+---------------------+---------------------+-------+
| id | uid  | exam_id | start_time          | submit_time         | score |
+----+------+---------+---------------------+---------------------+-------+
|  1 | 1001 |    9001 | 2020-01-02 09:01:01 | 2020-01-02 09:21:01 |    80 |
|  2 | 1001 |    9002 | 2021-09-01 09:01:01 | 2021-09-01 09:21:01 |    90 |
|  3 | 1002 |    9001 | 2021-08-02 19:01:01 | NULL                |  NULL |
|  4 | 1002 |    9002 | 2021-09-05 19:01:01 | 2021-09-05 19:40:01 |    89 |
|  5 | 1003 |    9001 | 2021-09-02 12:01:01 | NULL                |  NULL |
|  6 | 1003 |    9002 | 2021-09-01 12:01:01 | NULL                |  NULL |
+----+------+---------+---------------------+---------------------+-------+
6 rows in set (0.00 sec)



mysql> update exam_record set submit_time = '2099-01-01 0:0:0', score = 0
    -> where start_time < '2021-9-1 0:0:0' and submit_time is null;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from exam_record;
+----+------+---------+---------------------+---------------------+-------+
| id | uid  | exam_id | start_time          | submit_time         | score |
+----+------+---------+---------------------+---------------------+-------+
|  1 | 1001 |    9001 | 2020-01-02 09:01:01 | 2020-01-02 09:21:01 |    80 |
|  2 | 1001 |    9002 | 2021-09-01 09:01:01 | 2021-09-01 09:21:01 |    90 |
|  3 | 1002 |    9001 | 2021-08-02 19:01:01 | 2099-01-01 00:00:00 |     0 |
|  4 | 1002 |    9002 | 2021-09-05 19:01:01 | 2021-09-05 19:40:01 |    89 |
|  5 | 1003 |    9001 | 2021-09-02 12:01:01 | NULL                |  NULL |
|  6 | 1003 |    9002 | 2021-09-01 12:01:01 | NULL                |  NULL |
+----+------+---------+---------------------+---------------------+-------+
6 rows in set (0.00 sec)

87、删除记录（1）

现有一张试卷答题记录表：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 表中答题时间小于 5 分钟整且分数不及格（及格线为60分）的记录；

表结构及数据如下：

解答：

mysql> select * from exam_record;
+----+------+---------+---------------------+---------------------+-------+
| id | uid  | exam_id | start_time          | submit_time         | score |
+----+------+---------+---------------------+---------------------+-------+
|  1 | 1001 |    9001 | 2020-01-01 22:11:12 | 2020-01-01 23:16:12 |    50 |
|  2 | 1001 |    9002 | 2020-01-02 09:01:01 | 2020-01-02 09:06:00 |    58 |
|  3 | 1002 |    9001 | 2021-05-02 10:01:01 | 2021-05-02 10:05:58 |    60 |
|  4 | 1002 |    9002 | 2021-06-02 19:01:01 | 2021-06-02 19:05:01 |    54 |
|  5 | 1003 |    9001 | 2021-09-05 19:01:01 | 2021-09-05 19:40:01 |    49 |
|  6 | 1003 |    9001 | 2021-09-05 19:01:01 | 2021-09-05 19:15:01 |    70 |
|  7 | 1003 |    9001 | 2021-09-06 19:01:01 | 2021-09-06 19:05:01 |    80 |
|  8 | 1003 |    9002 | 2021-09-09 07:01:02 | NULL                |  NULL |
+----+------+---------+---------------------+---------------------+-------+
8 rows in set (0.00 sec)


mysql> delete from exam_record 
    -> where timediff(submit_time, start_time) < '00:05:00' and score < 60 or submit_time is null;
Query OK, 3 rows affected (0.00 sec)

mysql> select * from exam_record;                                          
+----+------+---------+---------------------+---------------------+-------+
| id | uid  | exam_id | start_time          | submit_time         | score |
+----+------+---------+---------------------+---------------------+-------+
|  1 | 1001 |    9001 | 2020-01-01 22:11:12 | 2020-01-01 23:16:12 |    50 |
|  3 | 1002 |    9001 | 2021-05-02 10:01:01 | 2021-05-02 10:05:58 |    60 |
|  5 | 1003 |    9001 | 2021-09-05 19:01:01 | 2021-09-05 19:40:01 |    49 |
|  6 | 1003 |    9001 | 2021-09-05 19:01:01 | 2021-09-05 19:15:01 |    70 |
|  7 | 1003 |    9001 | 2021-09-06 19:01:01 | 2021-09-06 19:05:01 |    80 |
+----+------+---------+---------------------+---------------------+-------+
5 rows in set (0.00 sec)

88、删除记录（2）

现有一张试卷答题记录表：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 表中未完成作答或作答时间小于 5 分钟整的记录中，开始作答时间最早的 3 条记录。

表结构及数据如下：

解答：

mysql> select * from exam_record;
+----+------+---------+---------------------+---------------------+-------+
| id | uid  | exam_id | start_time          | submit_time         | score |
+----+------+---------+---------------------+---------------------+-------+
|  1 | 1001 |    9001 | 2020-01-01 22:11:12 | 2020-01-01 23:16:12 |    50 |
|  2 | 1001 |    9002 | 2020-01-02 09:01:01 | 2020-01-02 09:06:00 |    58 |
|  3 | 1001 |    9002 | 2020-01-02 09:01:01 | 2020-01-02 09:05:01 |    58 |
|  4 | 1002 |    9001 | 2021-05-02 10:01:01 | 2021-05-02 10:06:58 |    60 |
|  5 | 1002 |    9002 | 2021-06-02 19:01:01 | NULL                |  NULL |
|  6 | 1003 |    9001 | 2021-09-05 19:01:01 | NULL                |  NULL |
|  7 | 1003 |    9001 | 2021-09-05 19:01:01 | NULL                |  NULL |
|  8 | 1003 |    9002 | 2021-09-09 07:01:02 | NULL                |  NULL |
+----+------+---------+---------------------+---------------------+-------+
8 rows in set (0.00 sec)


mysql> delete from exam_record 
    -> where timediff(submit_time, start_time) < '00:05:00' or submit_time is null
    -> order by start_time limit 3;
Query OK, 3 rows affected (0.00 sec)

mysql> select * from exam_record;
+----+------+---------+---------------------+---------------------+-------+
| id | uid  | exam_id | start_time          | submit_time         | score |
+----+------+---------+---------------------+---------------------+-------+
|  1 | 1001 |    9001 | 2020-01-01 22:11:12 | 2020-01-01 23:16:12 |    50 |
|  4 | 1002 |    9001 | 2021-05-02 10:01:01 | 2021-05-02 10:06:58 |    60 |
|  6 | 1003 |    9001 | 2021-09-05 19:01:01 | NULL                |  NULL |
|  7 | 1003 |    9001 | 2021-09-05 19:01:01 | NULL                |  NULL |
|  8 | 1003 |    9002 | 2021-09-09 07:01:02 | NULL                |  NULL |
+----+------+---------+---------------------+---------------------+-------+
5 rows in set (0.00 sec)

89、删除记录（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)	得分

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

表结构及数据如下：

解答：

mysql> select * from exam_record;
+----+------+---------+---------------------+---------------------+-------+
| id | uid  | exam_id | start_time          | submit_time         | score |
+----+------+---------+---------------------+---------------------+-------+
|  1 | 1001 |    9001 | 2020-01-01 22:11:12 | 2020-01-01 23:16:12 |    50 |
|  2 | 1001 |    9002 | 2020-01-02 09:01:01 | 2020-01-02 09:06:00 |    58 |
+----+------+---------+---------------------+---------------------+-------+
2 rows in set (0.00 sec)

mysql> truncate table exam_record;
Query OK, 0 rows affected (0.01 sec)

mysql> truncate table exam_record;
Query OK, 0 rows affected (0.01 sec)

数据库系统原理与应用教程（067）—— MySQL 练习题：操作题 82-89（十一）：数据的增、删、改操作

Java相关栏目本月热门文章