drop table if exists examination_info,exam_record;
CREATE TABLE 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_general_ci;
CREATE TABLE 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;
INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
(9001, 'SQL', 'hard', 60, '2020-01-01 10:00:00'),
(9002, 'C++', 'hard', 80, '2020-01-01 10:00:00'),
(9003, '算法', 'hard', 80, '2020-01-01 10:00:00'),
(9004, 'PYTHON', 'medium', 70, '2020-01-01 10:00:00');
INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-08-02 10:01:01', '2020-08-02 10:31:01', 89),
(1002, 9001, '2020-04-01 18:01:01', '2020-04-01 18:59:02', 90),
(1001, 9001, '2020-04-01 09:01:01', '2020-04-01 09:21:59', 80),
(1002, 9003, '2021-01-20 10:01:01', '2021-01-20 10:10:01', 81),
(1002, 9001, '2021-03-02 19:01:01', '2021-03-02 19:32:00', 20),
(1001, 9003, '2021-04-02 19:01:01', '2021-04-02 19:40:01', 89),
(1004, 9004, '2020-05-02 12:01:01', '2020-05-02 12:20:01', 99),
(1003, 9001, '2021-05-02 12:01:01', '2021-05-02 12:31:01', 98),
(1001, 9002, '2020-02-02 12:01:01', '2020-02-02 12:20:01', 99),
(1002, 9002, '2020-02-02 12:01:01', '2020-02-02 12:43:01', 81),
(1001, 9002, '2020-01-02 19:01:01', '2020-01-02 19:59:01', 69),
(1001, 9004, '2021-09-02 12:11:01', null, null),
(1003, 9001, '2020-01-02 10:01:01', '2020-01-02 10:31:01', 89),
(1002, 9004, '2020-01-01 12:11:01', '2020-01-01 12:31:01', 83),
(1002, 9003, '2021-01-01 18:01:01', '2021-01-01 18:59:02', 90),
(1002, 9002, '2020-02-02 12:01:01', null, null),
(1002, 9002, '2020-03-02 12:11:01', null, null),
(1001, 9002, '2021-05-05 18:01:01', null, null);