[大数据]SQL 进阶挑战（6 - 10）

SQL6 删除记录（一）

描述

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

作答记录表exam_record：

start_time是试卷开始时间

submit_time 是交卷，即结束时间

问题

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

示例

输入：
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;
INSERT INTO exam_record(uid, exam_id, start_time, submit_time, score) VALUES
(1001, 9001, '2020-01-01 22:11:12', '2020-01-01 23:16:12', 50),
(1001, 9002, '2020-01-02 09:01:01', '2020-01-02 09:06:00', 58),
(1002, 9001, '2021-05-02 10:01:01', '2021-05-02 10:05:58', 60),
(1002, 9002, '2021-06-02 19:01:01', '2021-06-02 19:05:01', 54),
(1003, 9001, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 49),
(1003, 9001, '2021-09-05 19:01:01', '2021-09-05 19:15:01', 70),
(1003, 9001, '2021-09-06 19:01:01', '2021-09-06 19:05:01', 80),
(1003, 9002, '2021-09-09 07:01:02', null, null);
输出：
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 [数据表名] WHERE [条件语句];

除此之外，注意题意可以发现是两个条件相联合，所以要用关键字 AND。而为了计算结束时间和开始之间的时间差，需要用到函数 TIMESTAMPDIFF(interval, time_start, time_end)，其中 iterval 可以指定的单位为：

• SECOND
• MINUTE
• HOUR
• DAY
• MONTH
• YEAR

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

SQL7 删除记录（二）

描述

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

作答记录表exam_record：

start_time是试卷开始时间

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

问题

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

示例

输入：
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;
INSERT INTO exam_record(uid, exam_id, start_time, submit_time, score) VALUES
(1001, 9001, '2020-01-01 22:11:12', '2020-01-01 23:16:12', 50),
(1001, 9002, '2020-01-02 09:01:01', '2020-01-02 09:06:00', 58),
(1001, 9002, '2020-01-02 09:01:01', '2020-01-02 09:05:01', 58),
(1002, 9001, '2021-05-02 10:01:01', '2021-05-02 10:06:58', 60),
(1002, 9002, '2021-06-02 19:01:01', null, null),
(1003, 9001, '2021-09-05 19:01:01', null, null),
(1003, 9001, '2021-09-05 19:01:01', null, null),
(1003, 9002, '2021-09-09 07:01:02', null, null);
输出：
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|None|None
7|1003|9001|2021-09-05 19:01:01|None|None
8|1003|9002|2021-09-09 07:01:02|None|None

解答

同样考察删除语句，不过此时加入了多的限制，其语法结构如下：

DELETE FROM [数据表名] WHERE [条件语句] ORDER BY [字段名] LIMIT [数量];

要删除未作答或作答时间小于 5 分钟的记录，需要用到关键字 OR 将两个条件并联，其次，要对结果进行排序，则需要用到 ORDER BY，最后则是限制输出记录的调试，用到 LIMIT 关键字。

DELETE FROM 
exam_record
WHERE
TIMESTAMPDIFF(MINUTE, start_time, submit_time) < 5
OR
submit_time 
IS NULL
ORDER BY 
start_time
LIMIT 3;

SQL8 删除记录（三）

描述

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

问题

请删除 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;
INSERT INTO exam_record(uid, exam_id, start_time, submit_time, score) VALUES
(1001, 9001, '2020-01-01 22:11:12', '2020-01-01 23:16:12', 50),
(1001, 9002, '2020-01-02 09:01:01', '2020-01-02 09:06:00', 58);
输出：
0|None

解答

题目中是需要删除所有记录并重置自增计数器，所以此时可以直接使用 TRUNCATE 语句，其语法结构如下：

TRUNCATE [数据表名];

TRUNCATE exam_record;

当然，以上方式虽然简单，但我们也可以用一种手动重置自增计数器的方式，即先删除记录，然后在手动重置自增计数器。

DELETE FROM exam_record;
ALTER TABLE exam_record auto_increment = 1;

SQL9 创建一张新表

描述

现有一张用户信息表，其中包含多年来在平台注册过的用户信息，随着牛客平台的不断壮大，用户量飞速增长，为了高效地为高活跃用户提供服务，现需要将部分用户拆分出一张新表。

原来的用户信息表：

问题

请创建一张优质用户信息表user_info_vip，表结构和用户信息表一致。

你应该返回的输出如下表格所示，请写出建表语句将表格中所有限制和说明记录到表里。

示例

输入：
drop table if EXISTS user_info_vip;
输出：
id|int|None|NO|PRI|None|auto_increment|select,insert,update,references|自增ID
uid|int|None|NO|UNI|None||select,insert,update,references|用户ID
nick_name|varchar(64)|utf8_general_ci|YES||None||select,insert,update,references|昵称
achievement|int|None|YES||0||select,insert,update,references|成就值
level|int|None|YES||None||select,insert,update,references|用户等级
job|varchar(32)|utf8_general_ci|YES||None||select,insert,update,references|职业方向
register_time|datetime|None|YES||CURRENT_TIMESTAMP|DEFAULT_GENERATED|select,insert,update,references|注册时间

解答

考察表的创建方式，其语法结构如下：

CREATE TABLE
[IF NOT EXISTS] [数据表名] -- 不存在才创建，存在就跳过
(列名 [列类型] -- 列名和类型必选
  [ PRIMARY KEY -- 可选的约束，主键
   | FOREIGN KEY -- 外键，引用其他表的键值
   | AUTO_INCREMENT -- 自增ID
   | COMMENT comment -- 列注释（评论）
   | DEFAULT default_value -- 默认值
   | UNIQUE -- 唯一性约束，不允许两条记录该列值相同
   | NOT NULL -- 该列非空
  ], ...
) [CHARACTER SET charset] -- 字符集编码
[COLLATE collate_value] -- 列排序和比较时的规则（是否区分大小写等）

然后按照上述语法，套入相关字段即可。

CREATE TABLE IF NOT EXISTS user_info_vip (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int UNIQUE NOT NULL COMMENT '用户ID',
    nick_name varchar(64) COMMENT '昵称',
    achievement int DEFAULT 0 COMMENT '成就值',
    level int COMMENT '用户等级',
    job varchar(32) COMMENT '职业方向',
    register_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间'
) CHARACTER SET utf8 COLLATE utf8_general_ci;

SQL10 修改表

描述

现有一张用户信息表user_info，其中包含多年来在平台注册过的用户信息。

用户信息表user_info：

问题

请在用户信息表，字段level的后面增加一列最多可保存15个汉字的字段school；并将表中job列名改为profession，同时varchar字段长度变为10；achievement的默认值设置为0。

输出结果示例：

示例

输入：
drop table if exists user_info;
CREATE TABLE IF NOT EXISTS user_info (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int UNIQUE NOT NULL COMMENT '用户ID',
`nick_name` varchar(64) COMMENT '昵称',
achievement int COMMENT '成就值',
level int COMMENT '用户等级',
job varchar(10) COMMENT '职业方向',
register_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
输出：
id|int|None|NO|PRI|None|auto_increment|select,insert,update,references|自增ID
uid|int|None|NO|UNI|None||select,insert,update,references|用户ID
nick_name|varchar(64)|utf8_general_ci|YES||None||select,insert,update,references|昵称
achievement|int|None|YES||0||select,insert,update,references|
level|int|None|YES||None||select,insert,update,references|用户等级
school|varchar(15)|utf8_general_ci|YES||None||select,insert,update,references|
profession|varchar(10)|utf8_general_ci|YES||None||select,insert,update,references|
register_time|datetime|None|YES||CURRENT_TIMESTAMP|DEFAULT_GENERATED|select,insert,update,references|注册时间

解答

主要考察数据表结构的修改，主要语法如下：

• 在已有列后新增列

ALTER TABLE [数据表名] ADD [列名] [列类型] AFTER [列名];

• 修改已有列名和列类型

ALTER TABLE [数据表名] CHANGE [旧列名] [新列名] [列类型];

• 修改已有列的默认值

ALTER TABLE [数据表名] CHANGE COLUMN [旧列名] [新列名] [新列类型] DEFAULT [默认值];

依照题意，主要实现以下功能：

• level 后面增加一列最多可保存 15 个汉字的字段 school

• 将表中 job 列名改为 profession

• 设置 achievement 的默认值为 0

ALTER TABLE user_info ADD school varchar(15) AFTER `level`;
ALTER TABLE user_info CHANGE job profession varchar(10);
ALTER TABLE user_info CHANGE COLUMN achievement achievement int DEFAULT 0;