PTA数据库补题6-7 | 表操作

插入记录

(一)

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

用户1001在2021年9月1日晚上10点11分12秒开始作答试卷9001，并在50分钟后提交，得了90分；
用户1002在2021年9月4日上午7点1分2秒开始作答试卷9002，并在10分钟后退出了平台。
试卷作答记录表exam_record中，表已建好，其结构如下，请用一条语句将这两条记录插入表中。

drop table if EXISTS exam_record;
CREATE TABLE IF NOT EXISTS exam_record (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int NOT NULL COMMENT '用户ID',
exam_id int NOT NULL COMMENT '试卷ID',
start_time datetime NOT NULL COMMENT '开始时间',
submit_time datetime COMMENT '提交时间',
score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
TRUNCATE exam_record; 

exam_record表：

Field	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,start_time,exam_id,submit_time,score)
values (1001,'2021-9-1 22:11:12',9001,date_add('2021-9-1 22:11:12',interval 50 minute),90)
,(1002,'2021-9-4 07:01:02',9002,null,null) 

芝士点： 

insert语句有两种语法形式，分别是 insert...values语句 和 insert...set语句

• insert...values语句

insert into <表名> [ <列名1> [  , ...<列名n> ] ]

values (值1) [..., (值n) ];

<表名>：指定被操作的表名

<列名>：指定需要插入数据的列名。若向表中的所有列插入数据，则全部的列明可以省略，直接采用insertvalues(...)即可

values 或 value子句：改子句包含要插入的数据清单。数据清单中数据的顺序要和列的顺序对应。

• insert...set语句

insert into <表名>

set <列名1> = <值1>,

                <列名2>  = <值2>,  ...

可以精准的对列进行复制

（二） 

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

表结构：

drop table if EXISTS exam_record;
CREATE TABLE IF NOT EXISTS exam_record (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int NOT NULL COMMENT '用户ID',
exam_id int NOT NULL COMMENT '试卷ID',
start_time datetime NOT NULL COMMENT '开始时间',
submit_time datetime COMMENT '提交时间',
score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE TABLE IF NOT EXISTS exam_record_before_2021 (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int NOT NULL COMMENT '用户ID',
exam_id int NOT NULL COMMENT '试卷ID',
start_time datetime NOT NULL COMMENT '开始时间',
submit_time datetime COMMENT '提交时间',
score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
TRUNCATE exam_record;
TRUNCATE exam_record_before_2021;

分析：

新插入一张表 要求提交时间在年以前的

insert into exam_record_before_2021
select null,uid,exam_id,start_time,submit_time,score
from exam_record
where year(submit_time) < '2021'

芝士点： 

insert...into与select搭配（复制旧表到新表中）

insert into <表名> [ <列名1>,[ ...<列名n> ] 

select <列名1>...<列名n>

from <表名>

where <条件限制>

 更新记录

（一）

现有一张试卷信息表examination_info，
请把examination_info表中tag为PYTHON的tag字段全部修改为Python。

drop table if EXISTS examination_info;
CREATE TABLE IF NOT EXISTS examination_info (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
tag varchar(32) COMMENT '类别标签',
difficulty varchar(8) COMMENT '难度',
duration int NOT NULL COMMENT '时长',
release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_bin;
TRUNCATE examination_info;

 update examination_info
set tag = 'Python'
where tag = 'PYTHON'

 芝士点： 

update语句：

update <表名>

set 列名1 = 值1 [,列名2 = 值2...]

where 条件

order by ...

limit row_count

set子句：用于指定表中要修改的列名及其列值。其中，每个指定的列值可以是表达式，也可以是该列对应的默认值。如果指定的是默认值，可用关键字default表示列值

where子句：用于限定表中要修改的行，若不指定，则修改表中所有的行

order by子句：按照指定的顺序对行进行更新

limit子句：限制可更新的行数

（二）

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

表结构:

请在这里写定义表结构的SQL语句。例如：

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

输出样例:

uid	exam_id	start_time	submit_time	score
1001	9001	2020-01-02 09:01:01	2020-01-02 09:21:01	80
1001	9002	2021-09-01 09:01:01	2021-09-01 09:21:01	90
1002	9001	2021-08-02 19:01:01	2099-01-01 00:00:00	0
1002	9002	2021-09-05 19:01:01	2021-09-05 19:40:01	89
1003	9001	2021-09-02 12:01:01
1003	9002	2021-09-01 12:01:01

update exam_record
set submit_time = "2099-01-01 00:00:00",score = 0
where start_time < "2021-09-01 00:00:00" and submit_time is null

删除记录

(三)

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

作答记录表exam_record：
start_time是试卷开始时间
submit_time 是交卷，即结束时间

表结构:

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

后台会执行您的SQL，然后通过 SELECT * FROM exam_record; 语句来筛选出剩下的数据，与正确数据进行对比。

输出样例:

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

8|1003|9002|2021-09-09 07:01:02|None|None

delete from exam_record
where timestampdiff(minute,start_time,submit_time) < 5
and score < 60

芝士点：

delete语句： 删除表中部分数据

delete from <表名>

where 限制条件

删除符合where条件后面的数据，不填写where条件，就会清空表的数据

（二）

现有一张试卷作答记录表exam_record，其中包含多年来的用户作答试卷记录，
请删除exam record表中未完成作答或作答时间小于5分钟整的记录中，开始作答时间最早的3条记录

表结构:

输出样例:

请在这里给出输出样例。例如：
复制
输出：

uid	exam_id	start_time	submit_time	score
1	1001	9001	2020-01-01 22:11:12	2020-01-01 23:16:12
4	1002	9001	2021-05-02 10:01:01	2021-05-02 10:06:58
6	1003	9001	2021-09-05 19:01:01	None
7	1003	9001	2021-09-05 19:01:01	None
8	1003	9002	2021-09-09 07:01:02	None

排序一下，再删掉最前面的三行 

delete from exam_record
where submit_time is NULL
or timestampdiff(minute,start_time,submit_time) < 5
order by start_time 
limit 3