大家好，我是讯享网，很高兴认识大家。

数据库系统原理与应用教程（070）—— MySQL 练习题：操作题 101-109（十四）：查询条件练习

101、判断空值（1）

试卷答题记录表：exam_record（uid：用户ID，exam_id：试卷ID, start_time开始作答时间, submit_time交卷时间, score得分），数据如下：

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	9001	2021-05-02 10:01:01	2021-05-02 10:30:01	81
3	1001	9001	2021-09-02 12:01:01	(NULL)	(NULL)

【问题】请统计有未完成状态的试卷的未完成数 incomplete_cnt 和未完成率 incomplete_rate。查询结果如下：

exam_id	incomplete_cnt	incomplete_rate
9001	1	0.333

表结构和数据如下：

/* drop table if exists exam_record; 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 exam_record(uid,exam_id,start_time,submit_time,score) VALUES (1001, 9001, '2020-01-02 09:01:01', '2020-01-02 09:21:01', 80), (1001, 9001, '2021-05-02 10:01:01', '2021-05-02 10:30:01', 81), (1001, 9001, '2021-09-02 12:01:01', null, null); */ 

讯享网

解答：

讯享网/* select exam_id, sum(if(submit_time is null, 1, 0)) incomplete_cnt, round(sum(if(submit_time is null, 1, 0))/count(*),3) incomplete_rate from exam_record group by exam_id having incomplete_cnt > 0; */ mysql> select exam_id, -> sum(if(submit_time is null, 1, 0)) incomplete_cnt, -> round(sum(if(submit_time is null, 1, 0))/count(*),3) incomplete_rate -> from exam_record -> group by exam_id; +---------+----------------+-----------------+ | exam_id | incomplete_cnt | incomplete_rate | +---------+----------------+-----------------+ | 9001 | 1 | 0.333 | +---------+----------------+-----------------+ 1 row in set (0.00 sec) 

102、判断空值（2）

用户信息表：user_info（uid：用户ID，nick_name：昵称，achievement：成就值，level：等级，job：职业方向， register_time：注册时间），表中数据如下：

id	uid	nick_name	achievement	level	job	register_time
1	1001	牛客1号	10	0	算法	2020-01-01 10:00:00
2	1002	牛客2号	2100	6	算法	2020-01-01 10:00:00

试卷信息表：examination_info（exam_id：试卷ID，tag：试卷类别，difficulty：试卷难度，duration：考试时长，release_time：发布时间），表中数据如下：

id	exam_id	tag	difficulty	duration	release_time
1	9001	SQL	hard	60	2020-01-01 10:00:00
2	9002	SQL	easy	60	2020-01-01 10:00:00
3	9004	算法	medium	80	2020-01-01 10:00:00

试卷作答记录表：exam_record（uid：用户ID，exam_id：试卷ID，start_time：开始作答时间，submit_time：交卷时间，score：得分），表中数据如下：

id	uid	exam_id	start_time	submit_time	score
1	1001	9001	2020-01-02 09:01:01	2020-01-02 09:21:59	80
2	1001	9001	2021-05-02 10:01:01	(NULL)	(NULL)
3	1001	9002	2021-02-02 19:01:01	2021-02-02 19:30:01	87
4	1001	9001	2021-06-02 19:01:01	2021-06-02 19:32:00	20
5	1001	9002	2021-09-05 19:01:01	2021-09-05 19:40:01	89
6	1001	9002	2021-09-01 12:01:01	(NULL)	(NULL)
7	1002	9002	2021-05-05 18:01:01	2021-05-05 18:59:02	90

【问题】查询每个 0 级用户所有的高难度试卷平均用时（保留一位小数）和平均得分（取整），未完成的试卷最大考试时长和 0 分处理。查询结果如下：

uid	avg_score	avg_time_took
1001	33	36.7

表结构和数据如下：

/* drop table if exists examination_info,user_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 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(32) COMMENT '职业方向', register_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 user_info(uid,`nick_name`,achievement,level,job,register_time) VALUES (1001, '牛客1号', 10, 0, '算法', '2020-01-01 10:00:00'), (1002, '牛客2号', 2100, 6, '算法', '2020-01-01 10:00:00'); INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES (9001, 'SQL', 'hard', 60, '2020-01-01 10:00:00'), (9002, 'SQL', 'easy', 60, '2020-01-01 10:00:00'), (9004, '算法', 'medium', 80, '2020-01-01 10:00:00'); INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES (1001, 9001, '2020-01-02 09:01:01', '2020-01-02 09:21:59', 80), (1001, 9001, '2021-05-02 10:01:01', null, null), (1001, 9002, '2021-02-02 19:01:01', '2021-02-02 19:30:01', 87), (1001, 9001, '2021-06-02 19:01:01', '2021-06-02 19:32:00', 20), (1001, 9002, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 89), (1001, 9002, '2021-09-01 12:01:01', null, null), (1002, 9002, '2021-05-05 18:01:01', '2021-05-05 18:59:02', 90); */ 

解答：

讯享网/* select er.uid, round(avg(ifnull(score,0)), 0) avg_score, round(avg(if(submit_time is null, ei.duration, substr(timediff(submit_time, start_time),4,2))),1) avg_time_took from exam_record er join examination_info ei on er.exam_id = ei.exam_id join user_info u on er.uid = u.uid where u.level = 0 and ei.difficulty = 'hard' group by er.uid; */ mysql> select er.uid, -> round(avg(ifnull(score,0)), 0) avg_score, -> round(avg(if(submit_time is null, ei.duration, substr(timediff(submit_time, start_time),4,2))),1) avg_time_took -> from exam_record er join examination_info ei on er.exam_id = ei.exam_id -> join user_info u on er.uid = u.uid -> where u.level = 0 and ei.difficulty = 'hard' -> group by er.uid; +------+-----------+---------------+ | uid | avg_score | avg_time_took | +------+-----------+---------------+ | 1001 | 33 | 36.7 | +------+-----------+---------------+ 1 row in set (0.01 sec) 

103、构造查询条件（1）

该题目使用的表和数据如下：

/* drop table if exists user_info,exam_record,practice_record; CREATE TABLE 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(32) COMMENT '职业方向', register_time datetime COMMENT '注册时间' )CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE TABLE practice_record ( id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID', uid int NOT NULL COMMENT '用户ID', question_id int NOT NULL COMMENT '题目ID', submit_time datetime COMMENT '提交时间', score tinyint 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 user_info(uid,`nick_name`,achievement,level,job,register_time) VALUES (1001, '牛客1号', 1000, 2, '算法', '2020-01-01 10:00:00'), (1002, '牛客2号', 1200, 3, '算法', '2020-01-01 10:00:00'), (1003, '进击的3号', 2200, 5, '算法', '2020-01-01 10:00:00'), (1004, '牛客4号', 2500, 6, '算法', '2020-01-01 10:00:00'), (1005, '牛客5号', 3000, 7, 'C++', '2020-01-01 10:00:00'); INSERT INTO practice_record(uid,question_id,submit_time,score) VALUES (1001, 8001, '2021-08-02 11:41:01', 60), (1002, 8001, '2021-09-02 19:30:01', 50), (1002, 8001, '2021-09-02 19:20:01', 70), (1002, 8002, '2021-09-02 19:38:01', 70), (1003, 8002, '2021-09-01 19:38:01', 80); INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES (1001, 9001, '2020-01-02 09:01:01', '2020-01-02 09:21:59', 80), (1001, 9001, '2021-05-02 10:01:01', null, null), (1001, 9002, '2021-02-02 19:01:01', '2021-02-02 19:30:01', 87), (1001, 9001, '2021-06-02 19:01:01', '2021-06-02 19:32:00', 20), (1001, 9002, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 89), (1001, 9002, '2021-09-01 12:01:01', null, null), (1002, 9002, '2021-05-05 18:01:01', '2021-05-05 18:59:02', 90), (1003, 9003, '2021-02-06 12:01:01', null, null), (1003, 9001, '2021-09-07 10:01:01', '2021-09-07 10:31:01', 89), (1004, 9002, '2021-08-06 12:01:01', null, null), (1002, 9001, '2020-01-01 12:01:01', '2020-01-01 12:31:01', 81), (1002, 9002, '2020-02-01 12:01:01', '2020-02-01 12:31:01', 82), (1002, 9002, '2020-02-02 12:11:01', '2020-02-02 12:31:01', 83), (1005, 9001, '2021-02-01 11:01:01', '2021-02-01 11:31:01', 84), (1006, 9001, '2021-02-01 11:01:01', '2021-02-01 11:31:01', 84), (1002, 9001, '2021-09-06 12:01:01', '2021-09-06 12:21:01', 80), (1002, 9001, '2021-09-06 12:01:01', null, null), (1002, 9001, '2021-09-07 12:01:01', null, null); */ 

用户信息表：user_info（uid：用户ID，nick_name：昵称，achievement：成就值，level：等级， job：职业方向， register_time：注册时间），表中数据如下：

讯享网mysql> select * from user_info; +----+------+---------------+-------------+-------+--------+---------------------+ | id | uid | nick_name | achievement | level | job | register_time | +----+------+---------------+-------------+-------+--------+---------------------+ | 1 | 1001 | 牛客1号 | 1000 | 2 | 算法 | 2020-01-01 10:00:00 | | 2 | 1002 | 牛客2号 | 1200 | 3 | 算法 | 2020-01-01 10:00:00 | | 3 | 1003 | 进击的3号 | 2200 | 5 | 算法 | 2020-01-01 10:00:00 | | 4 | 1004 | 牛客4号 | 2500 | 6 | 算法 | 2020-01-01 10:00:00 | | 5 | 1005 | 牛客5号 | 3000 | 7 | C++ | 2020-01-01 10:00:00 | +----+------+---------------+-------------+-------+--------+---------------------+ 5 rows in set (0.02 sec) 

试卷作答记录表：exam_record（uid：用户ID，exam_id试卷：ID，start_time：开始作答时间，submit_time：交卷时间，score：得分），表中数据如下：

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:59 | 80 | | 2 | 1001 | 9001 | 2021-05-02 10:01:01 | NULL | NULL | | 3 | 1001 | 9002 | 2021-02-02 19:01:01 | 2021-02-02 19:30:01 | 87 | | 4 | 1001 | 9001 | 2021-06-02 19:01:01 | 2021-06-02 19:32:00 | 20 | | 5 | 1001 | 9002 | 2021-09-05 19:01:01 | 2021-09-05 19:40:01 | 89 | | 6 | 1001 | 9002 | 2021-09-01 12:01:01 | NULL | NULL | | 7 | 1002 | 9002 | 2021-05-05 18:01:01 | 2021-05-05 18:59:02 | 90 | | 8 | 1003 | 9003 | 2021-02-06 12:01:01 | NULL | NULL | | 9 | 1003 | 9001 | 2021-09-07 10:01:01 | 2021-09-07 10:31:01 | 89 | | 10 | 1004 | 9002 | 2021-08-06 12:01:01 | NULL | NULL | | 11 | 1002 | 9001 | 2020-01-01 12:01:01 | 2020-01-01 12:31:01 | 81 | | 12 | 1002 | 9002 | 2020-02-01 12:01:01 | 2020-02-01 12:31:01 | 82 | | 13 | 1002 | 9002 | 2020-02-02 12:11:01 | 2020-02-02 12:31:01 | 83 | | 14 | 1005 | 9001 | 2021-02-01 11:01:01 | 2021-02-01 11:31:01 | 84 | | 15 | 1006 | 9001 | 2021-02-01 11:01:01 | 2021-02-01 11:31:01 | 84 | | 16 | 1002 | 9001 | 2021-09-06 12:01:01 | 2021-09-06 12:21:01 | 80 | | 17 | 1002 | 9001 | 2021-09-06 12:01:01 | NULL | NULL | | 18 | 1002 | 9001 | 2021-09-07 12:01:01 | NULL | NULL | +----+------+---------+---------------------+---------------------+-------+ 18 rows in set (0.00 sec) 

题目练习记录表：practice_record（uid：用户ID，question_id：题目ID，submit_time：提交时间，score：得分），表中数据如下：

讯享网mysql> select * from practice_record; +----+------+-------------+---------------------+-------+ | id | uid | question_id | submit_time | score | +----+------+-------------+---------------------+-------+ | 1 | 1001 | 8001 | 2021-08-02 11:41:01 | 60 | | 2 | 1002 | 8001 | 2021-09-02 19:30:01 | 50 | | 3 | 1002 | 8001 | 2021-09-02 19:20:01 | 70 | | 4 | 1002 | 8002 | 2021-09-02 19:38:01 | 70 | | 5 | 1003 | 8002 | 2021-09-01 19:38:01 | 80 | +----+------+-------------+---------------------+-------+ 5 rows in set (0.00 sec) 

【问题】请查询昵称以【牛客】开头、以【号】结尾、成就值在 1200~2500 之间，且最近一次活跃（答题或作答试卷）在 2021 年 9 月的用户信息。查询结果如下：

uid	nick_name	achievement
1002	牛客2号	1200

解答：

/* select uid, nick_name, achievement from user_info where nick_name like '牛客%号' and achievement between 1200 and 2500 and uid in (select uid from exam_record where submit_time between '2021-9-1' and '2021-9-30') and uid in (select uid from practice_record where submit_time between '2021-9-1' and '2021-9-30'); */ mysql> select uid, nick_name, achievement from user_info -> where nick_name like '牛客%号' and achievement between 1200 and 2500 and uid in -> (select uid from exam_record where submit_time between '2021-9-1' and '2021-9-30') -> and uid in -> (select uid from practice_record where submit_time between '2021-9-1' and '2021-9-30'); +------+------------+-------------+ | uid | nick_name | achievement | +------+------------+-------------+ | 1002 | 牛客2号 | 1200 | +------+------------+-------------+ 1 row in set (0.07 sec) 

104、构造查询条件（2）

该题目使用的表和数据如下：

讯享网/* drop table if exists examination_info,user_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 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(32) COMMENT '职业方向', register_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 user_info(uid,`nick_name`,achievement,level,job,register_time) VALUES (1001, '牛客1', 1900, 2, '算法', '2020-01-01 10:00:00'), (1002, '牛客2号', 1200, 3, '算法', '2020-01-01 10:00:00'), (1003, '牛客3号♂', 2200, 5, '算法', '2020-01-01 10:00:00'), (1004, '牛客4号', 2500, 6, '算法', '2020-01-01 10:00:00'), (1005, '牛客555号', 2000, 7, 'C++', '2020-01-01 10:00:00'), (1006, '', 3000, 6, 'C++', '2020-01-01 10:00:00'); INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES (9001, 'C++', 'hard', 60, '2020-01-01 10:00:00'), (9002, 'c#', 'hard', 80, '2020-01-01 10:00:00'), (9003, 'SQL', 'medium', 70, '2020-01-01 10:00:00'); INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES (1001, 9001, '2020-01-02 09:01:01', '2020-01-02 09:21:59', 80), (1001, 9001, '2021-05-02 10:01:01', null, null), (1001, 9002, '2021-02-02 19:01:01', '2021-02-02 19:30:01', 87), (1001, 9001, '2021-06-02 19:01:01', '2021-06-02 19:32:00', 20), (1001, 9002, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 89), (1001, 9002, '2021-09-01 12:01:01', null, null), (1002, 9002, '2021-05-05 18:01:01', '2021-05-05 18:59:02', 90), (1003, 9003, '2021-02-06 12:01:01', null, null), (1003, 9001, '2021-09-07 10:01:01', '2021-09-07 10:31:01', 89), (1004, 9002, '2021-08-06 12:01:01', null, null), (1002, 9001, '2020-01-01 12:01:01', '2020-01-01 12:31:01', 81), (1002, 9002, '2020-02-01 12:01:01', '2020-02-01 12:31:01', 82), (1002, 9002, '2020-02-02 12:11:01', '2020-02-02 12:31:01', 83), (1005, 9001, '2021-02-01 11:01:01', '2021-02-01 11:31:01', 84), (1006, 9001, '2021-09-01 11:01:01', '2021-09-01 11:31:01', 84), (1002, 9001, '2021-09-06 12:01:01', '2021-09-06 12:21:01', 80), (1002, 9001, '2021-09-06 12:01:01', null, null), (1002, 9001, '2021-09-07 12:01:01', null, null); */ 

用户信息表：user_info（uid：用户ID，nick_name：昵称，achievement：成就值，level：等级，job：职业方向， register_time：注册时间）表中数据如下：

mysql> select * from user_info; +----+------+---------------+-------------+-------+--------+---------------------+ | id | uid | nick_name | achievement | level | job | register_time | +----+------+---------------+-------------+-------+--------+---------------------+ | 1 | 1001 | 牛客1 | 1900 | 2 | 算法 | 2020-01-01 10:00:00 | | 2 | 1002 | 牛客2号 | 1200 | 3 | 算法 | 2020-01-01 10:00:00 | | 3 | 1003 | 牛客3号♂ | 2200 | 5 | 算法 | 2020-01-01 10:00:00 | | 4 | 1004 | 牛客4号 | 2500 | 6 | 算法 | 2020-01-01 10:00:00 | | 5 | 1005 | 牛客555号 | 2000 | 7 | C++ | 2020-01-01 10:00:00 | | 6 | 1006 |  | 3000 | 6 | C++ | 2020-01-01 10:00:00 | +----+------+---------------+-------------+-------+--------+---------------------+ 6 rows in set (0.00 sec) 

试卷信息表：examination_info（exam_id：试卷ID，tag：试卷类别，difficulty：试卷难度，duration：考试时长， release_time：发布时间），表中数据如下：

讯享网mysql> select * from examination_info; +----+---------+------+------------+----------+---------------------+ | id | exam_id | tag | difficulty | duration | release_time | +----+---------+------+------------+----------+---------------------+ | 1 | 9001 | C++ | hard | 60 | 2020-01-01 10:00:00 | | 2 | 9002 | c# | hard | 80 | 2020-01-01 10:00:00 | | 3 | 9003 | SQL | medium | 70 | 2020-01-01 10:00:00 | +----+---------+------+------------+----------+---------------------+ 3 rows in set (0.00 sec) 

试卷作答记录表：exam_record（uid：用户ID，exam_id：试卷ID，start_time：开始作答时间，submit_time：交卷时间,，score：得分）表中数据如下：

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:59 | 80 | | 2 | 1001 | 9001 | 2021-05-02 10:01:01 | NULL | NULL | | 3 | 1001 | 9002 | 2021-02-02 19:01:01 | 2021-02-02 19:30:01 | 87 | | 4 | 1001 | 9001 | 2021-06-02 19:01:01 | 2021-06-02 19:32:00 | 20 | | 5 | 1001 | 9002 | 2021-09-05 19:01:01 | 2021-09-05 19:40:01 | 89 | | 6 | 1001 | 9002 | 2021-09-01 12:01:01 | NULL | NULL | | 7 | 1002 | 9002 | 2021-05-05 18:01:01 | 2021-05-05 18:59:02 | 90 | | 8 | 1003 | 9003 | 2021-02-06 12:01:01 | NULL | NULL | | 9 | 1003 | 9001 | 2021-09-07 10:01:01 | 2021-09-07 10:31:01 | 89 | | 10 | 1004 | 9002 | 2021-08-06 12:01:01 | NULL | NULL | | 11 | 1002 | 9001 | 2020-01-01 12:01:01 | 2020-01-01 12:31:01 | 81 | | 12 | 1002 | 9002 | 2020-02-01 12:01:01 | 2020-02-01 12:31:01 | 82 | | 13 | 1002 | 9002 | 2020-02-02 12:11:01 | 2020-02-02 12:31:01 | 83 | | 14 | 1005 | 9001 | 2021-02-01 11:01:01 | 2021-02-01 11:31:01 | 84 | | 15 | 1006 | 9001 | 2021-09-01 11:01:01 | 2021-09-01 11:31:01 | 84 | | 16 | 1002 | 9001 | 2021-09-06 12:01:01 | 2021-09-06 12:21:01 | 80 | | 17 | 1002 | 9001 | 2021-09-06 12:01:01 | NULL | NULL | | 18 | 1002 | 9001 | 2021-09-07 12:01:01 | NULL | NULL | +----+------+---------+---------------------+---------------------+-------+ 18 rows in set (0.00 sec) 

【问题】查询昵称以【牛客 + 纯数字 + 号】或者纯数字组成的用户对于字母 c 开头的试卷类别（如C、C++、c#等）的已完成的试卷ID 和平均得分，按用户ID、平均分升序排序。查询结果如下：

uid	exam_id	avg_score
1002	9001	81
1002	9002	85
1005	9001	84
1006	9001	84

解答：

讯享网/* select u.uid, er.exam_id, round(avg(er.score),0) avg_score from user_info u join exam_record er on u.uid = er.uid join examination_info ei on er.exam_id = ei.exam_id where (nick_name REGEXP '^牛客[0-9]*号$' or nick_name REGEXP '^[0-9][0-9]*[0-9]$') and (ei.tag like 'c%' or ei.tag like 'C%') and er.submit_time is not null group by u.uid, er.exam_id order by u.uid, avg_score; */ mysql> select u.uid, er.exam_id, round(avg(er.score),0) avg_score -> from user_info u join exam_record er on u.uid = er.uid -> join examination_info ei on er.exam_id = ei.exam_id -> where (nick_name REGEXP '^牛客[0-9]*号$' or nick_name REGEXP '^[0-9][0-9]*[0-9]$') and -> (ei.tag like 'c%' or ei.tag like 'C%') and er.submit_time is not null -> group by u.uid, er.exam_id -> order by u.uid, avg_score; +------+---------+-----------+ | uid | exam_id | avg_score | +------+---------+-----------+ | 1002 | 9001 | 81 | | 1002 | 9002 | 85 | | 1005 | 9001 | 84 | | 1006 | 9001 | 84 | +------+---------+-----------+ 4 rows in set (0.05 sec) 

105、构造查询条件（3）

用户信息表：user_info（uid：用户ID，nick_name：昵称，achievement：成就值，level：等级， job：职业方向， register_time：注册时间），表中数据如下：

讯享网

id	uid	nick_name	achievement	level	job	register_time
1	1001	牛客1号	19	0	算法	2020-01-01 10:00:00
2	1002	牛客2号	1200	3	算法	2020-01-01 10:00:00
3	1003	牛客3号♂	22	0	算法	2020-01-01 10:00:00
4	1004	牛客4号	25	0	算法	2020-01-01 10:00:00
5	1005	牛客555号	2000	7	C++	2020-01-01 10:00:00
6	1006		3000	6	C++	2020-01-01 10:00:00

试卷作答记录表：exam_record（uid：用户ID，exam_id：试卷ID，start_time：开始作答时间，submit_time：交卷时间，score：得分），表中数据如下：

id	uid	exam_id	start_time	submit_time	score
1	1001	9001	2020-01-02 09:01:01	2020-01-02 09:21:59	80
2	1001	9001	2021-05-02 10:01:01	(NULL)	(NULL)
3	1001	9002	2021-02-02 19:01:01	2021-02-02 19:30:01	75
4	1001	9002	2021-09-01 12:01:01	2021-09-01 12:11:01	60
5	1001	9003	2021-09-02 12:01:01	2021-09-02 12:41:01	90
6	1001	9001	2021-06-02 19:01:01	2021-06-02 19:32:00	20
7	1001	9002	2021-09-05 19:01:01	2021-09-05 19:40:01	89
8	1001	9004	2021-09-03 12:01:01	(NULL)	(NULL)
9	1002	9001	2020-01-01 12:01:01	2020-01-01 12:31:01	99
10	1002	9003	2020-02-01 12:01:01	2020-02-01 12:31:01	82
11	1002	9003	2020-02-02 12:11:01	2020-02-02 12:41:01	76

【问题】将试卷得分按分界点 [90，75，60] 分为优良中差四个得分等级（分界点划分到左区间），请统计不同用户等级的人在完成过的试卷中各得分等级占比（结果保留 3 位小数），未完成过试卷的用户无需输出，结果按用户等级降序、占比降序排序。查询结果如下：

level	score_grade	ratio
3	良	0.667
3	优	0.333
0	良	0.500
0	中	0.167
0	优	0.167
0	差	0.167

该题目使用的表和数据如下：

/* drop table if exists user_info,exam_record; CREATE TABLE 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(32) COMMENT '职业方向', register_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 user_info(uid,`nick_name`,achievement,level,job,register_time) VALUES (1001, '牛客1', 19, 0, '算法', '2020-01-01 10:00:00'), (1002, '牛客2号', 1200, 3, '算法', '2020-01-01 10:00:00'), (1003, '牛客3号♂', 22, 0, '算法', '2020-01-01 10:00:00'), (1004, '牛客4号', 25, 0, '算法', '2020-01-01 10:00:00'), (1005, '牛客555号', 2000, 7, 'C++', '2020-01-01 10:00:00'), (1006, '', 3000, 6, 'C++', '2020-01-01 10:00:00'); INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES (1001, 9001, '2020-01-02 09:01:01', '2020-01-02 09:21:59', 80), (1001, 9001, '2021-05-02 10:01:01', null, null), (1001, 9002, '2021-02-02 19:01:01', '2021-02-02 19:30:01', 75), (1001, 9002, '2021-09-01 12:01:01', '2021-09-01 12:11:01', 60), (1001, 9003, '2021-09-02 12:01:01', '2021-09-02 12:41:01', 90), (1001, 9001, '2021-06-02 19:01:01', '2021-06-02 19:32:00', 20), (1001, 9002, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 89), (1001, 9004, '2021-09-03 12:01:01', null, null), (1002, 9001, '2020-01-01 12:01:01', '2020-01-01 12:31:01', 99), (1002, 9003, '2020-02-01 12:01:01', '2020-02-01 12:31:01', 82), (1002, 9003, '2020-02-02 12:11:01', '2020-02-02 12:41:01', 76); */ 

解答：

讯享网/* select u.level, case when score >= 90 then '优' when score >= 75 then '良' when score >= 60 then '中' else '差' end grade, round(count(submit_time)/a.total,3) ratio from user_info u join exam_record er on u.uid = er.uid join (select u.level, count(submit_time) total from user_info u join exam_record er on u.uid = er.uid group by u.level) a on a.level = u.level group by u.level, grade order by u.level desc, ratio desc; */ mysql> select u.level, case when score >= 90 then '优' when score >= 75 then '良' when score >= 60 then '中' else '差' end grade, round(count(submit_time)/a.total,3) ratio -> from user_info u join exam_record er on u.uid = er.uid join -> (select u.level, count(submit_time) total -> from user_info u join exam_record er on u.uid = er.uid -> group by u.level) a on a.level = u.level -> group by u.level, grade -> order by u.level desc, ratio desc; +-------+-------+-------+ | level | grade | ratio | +-------+-------+-------+ | 3 | 良 | 0.667 | | 3 | 优 | 0.333 | | 0 | 良 | 0.500 | | 0 | 差 | 0.167 | | 0 | 中 | 0.167 | | 0 | 优 | 0.167 | +-------+-------+-------+ 6 rows in set (0.00 sec) 

106、查询前几条记录（1）

用户信息表：user_info（uid：用户ID，nick_name：昵称，achievement：成就值，level：等级，job：职业方向， register_time：注册时间），表中数据如下：

id	uid	nick_name	achievement	level	job	register_time
1	1001	牛客1号	19	0	算法	2020-01-01 10:00:00
2	1002	牛客2号	1200	3	算法	2020-02-01 10:00:00
3	1003	牛客3号♂	22	0	算法	2020-01-02 10:00:00
4	1004	牛客4号	25	0	算法	2020-01-02 11:00:00
5	1005	牛客555号	4000	7	C++	2020-01-11 10:00:00
6	1006		3000	6	C++	2020-11-01 10:00:00

【问题】查询注册时间最早的 3 个人。查询结果如下：

uid	nick_name	register_time
1001	牛客1	2020-01-01 10:00:00
1003	牛客3号♂	2020-01-02 10:00:00
1004	牛客4号	2020-01-02 11:00:00

该题目使用的表和数据如下：

/* drop table if exists user_info,exam_record; CREATE TABLE 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(32) COMMENT '职业方向', register_time datetime COMMENT '注册时间' )CHARACTER SET utf8 COLLATE utf8_general_ci; INSERT INTO user_info(uid,`nick_name`,achievement,level,job,register_time) VALUES (1001, '牛客1', 19, 0, '算法', '2020-01-01 10:00:00'), (1002, '牛客2号', 1200, 3, '算法', '2020-02-01 10:00:00'), (1003, '牛客3号♂', 22, 0, '算法', '2020-01-02 10:00:00'), (1004, '牛客4号', 25, 0, '算法', '2020-01-02 11:00:00'), (1005, '牛客555号', 4000, 7, 'C++', '2020-01-11 10:00:00'), (1006, '', 3000, 6, 'C++', '2020-11-01 10:00:00'); */ 

解答：

讯享网mysql> select uid, nick_name, register_time from user_info order by register_time limit 3; +------+---------------+---------------------+ | uid | nick_name | register_time | +------+---------------+---------------------+ | 1001 | 牛客1 | 2020-01-01 10:00:00 | | 1002 | 牛客2号 | 2020-01-01 10:00:00 | | 1003 | 牛客3号♂ | 2020-01-01 10:00:00 | +------+---------------+---------------------+ 3 rows in set (0.03 sec) 

107、查询前几条记录（2）

用户信息表：user_info（uid：用户ID，nick_name：昵称，achievement：成就值，level：等级，job：职业方向， register_time：注册时间），表中数据如下：

id	uid	nick_name	achievement	level	job	register_time
1	1001	牛客1	19	0	算法	2020-01-01 10:00:00
2	1002	牛客2号	1200	3	算法	2020-01-01 10:00:00
3	1003	牛客3号♂	22	0	算法	2020-01-01 10:00:00
4	1004	牛客4号	25	0	算法	2020-01-01 10:00:00
5	1005	牛客555号	4000	7	算法	2020-01-11 10:00:00
6	1006	牛客6号	25	0	算法	2020-01-02 11:00:00
7	1007	牛客7号	25	0	算法	2020-01-02 11:00:00
8	1008	牛客8号	25	0	算法	2020-01-02 11:00:00
9	1009	牛客9号	25	0	算法	2020-01-02 11:00:00
10	1010	牛客10号	25	0	算法	2020-01-02 11:00:00
11	1011		3000	6	C++	2020-01-02 10:00:00

试卷信息表：examination_info（exam_id：试卷ID，tag：试卷类别，difficulty：试卷难度，duration：考试时长， release_time：发布时间），表中数据如下：

id	exam_id	tag	difficulty	duration	release_time
1	9001	算法	hard	60	2020-01-01 10:00:00
2	9002	算法	hard	80	2020-01-01 10:00:00
3	9003	SQL	medium	70	2020-01-01 10:00:00

试卷作答记录表：exam_record（uid：用户ID，exam_id：试卷ID，start_time：开始作答时间，submit_time：交卷时间，score：得分），表中数据如下：

id	uid	exam_id	start_time	submit_time	score
1	1001	9001	2020-01-02 09:01:01	2020-01-02 09:21:59	80
2	1002	9003	2020-01-20 10:01:01	2020-01-20 10:10:01	81
3	1002	9002	2020-01-01 12:11:01	2020-01-01 12:31:01	83
4	1003	9002	2020-01-01 19:01:01	2020-01-01 19:30:01	75
5	1004	9002	2020-01-01 12:01:01	2020-01-01 12:11:01	60
6	1005	9002	2020-01-01 12:01:01	2020-01-01 12:41:01	90
7	1006	9001	2020-01-02 19:01:01	2020-01-02 19:32:00	20
8	1007	9002	2020-01-02 19:01:01	2020-01-02 19:40:01	89
9	1008	9003	2020-01-02 12:01:01	2020-01-02 12:20:01	99
10	1008	9001	2020-01-02 12:01:01	2020-01-02 12:31:01	98
11	1009	9002	2020-01-02 12:01:01	2020-01-02 12:31:01	82
12	1010	9002	2020-01-02 12:11:01	2020-01-02 12:41:01	76
13	1011	9001	2020-01-02 10:01:01	2020-01-02 10:31:01	89

【问题】查询求职方向为【算法】工程师，且注册当天就完成了算法类试卷的人，按参加过的所有考试最高得分排名。采用分页展示，每页3条，请取出第 3 页（页码从1开始）的人的信息。查询结果如下：

uid	level	register_time	max_score
1010	0	2020-01-02 11:00:00	76
1003	0	2020-01-01 10:00:00	75
1004	0	2020-01-01 11:00:00	60

该题目使用的表和数据如下：

/* drop table if exists examination_info,user_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 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(32) COMMENT '职业方向', register_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 user_info(uid,`nick_name`,achievement,`level`,job,register_time) VALUES (1001, '牛客1', 19, 0, '算法', '2020-01-01 10:00:00'), (1002, '牛客2号', 1200, 3, '算法', '2020-01-01 10:00:00'), (1003, '牛客3号♂', 22, 0, '算法', '2020-01-01 10:00:00'), (1004, '牛客4号', 25, 0, '算法', '2020-01-01 11:00:00'), (1005, '牛客555号', 4000, 7, '算法', '2020-01-01 10:00:00'), (1006, '牛客6号', 25, 0, '算法', '2020-01-02 11:00:00'), (1007, '牛客7号', 25, 0, '算法', '2020-01-02 11:00:00'), (1008, '牛客8号', 25, 0, '算法', '2020-01-02 11:00:00'), (1009, '牛客9号', 25, 0, '算法', '2020-01-02 11:00:00'), (1010, '牛客10号', 25, 0, '算法', '2020-01-02 11:00:00'), (1011, '', 3000, 6, 'C++', '2020-01-02 10:00:00'); INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES (9001, '算法', 'hard', 60, '2020-01-01 10:00:00'), (9002, '算法', 'hard', 80, '2020-01-01 10:00:00'), (9003, 'SQL', 'medium', 70, '2020-01-01 10:00:00'); INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES (1001, 9001, '2020-01-01 09:01:01', '2020-01-01 09:21:59', 80), (1002, 9003, '2020-01-20 10:01:01', '2020-01-20 10:10:01', 81), (1002, 9002, '2020-01-01 12:11:01', '2020-01-01 12:31:01', 83), (1003, 9002, '2020-01-01 19:01:01', '2020-01-01 19:30:01', 75), (1004, 9002, '2020-01-01 12:01:01', '2020-01-01 12:11:01', 60), (1005, 9002, '2020-01-01 12:01:01', '2020-01-01 12:41:01', 90), (1006, 9001, '2020-01-02 19:01:01', '2020-01-02 19:32:00', 20), (1007, 9002, '2020-01-02 19:01:01', '2020-01-02 19:40:01', 89), (1008, 9003, '2020-01-02 12:01:01', '2020-01-02 12:20:01', 99), (1008, 9001, '2020-01-02 12:01:01', '2020-01-02 12:31:01', 98), (1009, 9002, '2020-01-02 12:01:01', '2020-01-02 12:31:01', 82), (1010, 9002, '2020-01-02 12:11:01', '2020-01-02 12:41:01', 76), (1011, 9001, '2020-01-02 10:01:01', '2020-01-02 10:31:01', 89); */ 

解答：

讯享网/* select u.uid, u.level, u.register_time, score max_score from user_info u join exam_record er on u.uid = er.uid and date(register_time) = date(submit_time) where u.job = '算法' and er.exam_id in (select exam_id from examination_info where tag = '算法') order by er.score desc limit 6,3; */ mysql> select u.uid, u.level, u.register_time, score max_score -> from user_info u join exam_record er -> on u.uid = er.uid and date(register_time) = date(submit_time) -> where u.job = '算法' and er.exam_id in -> (select exam_id from examination_info where tag = '算法') -> order by er.score desc limit 6,3; +------+-------+---------------------+-----------+ | uid | level | register_time | max_score | +------+-------+---------------------+-----------+ | 1010 | 0 | 2020-01-02 11:00:00 | 76 | | 1003 | 0 | 2020-01-01 10:00:00 | 75 | | 1004 | 0 | 2020-01-01 11:00:00 | 60 | +------+-------+---------------------+-----------+ 3 rows in set (0.00 sec) 

108、字符串函数的使用（1）

试卷信息表：examination_info（exam_id：试卷ID，tag：试卷类别，difficulty：试卷难度，duration：考试时长， release_time：发布时间），表中数据如下：

id	exam_id	tag	difficulty	duration	release_time
1	9001	算法	hard	60	2021-01-01 10:00:00
2	9002	算法	hard	80	2021-01-01 10:00:00
3	9003	SQL	medium	70	2021-01-01 10:00:00
4	9004	算法,medium,80		0	2021-01-01 10:00:00

【问题】有部分记录的试题类别tag、难度、时长同时录入到了 tag 字段，请找出这些录错了的记录，拆分后按正确的列类型输出。查询结果如下：

exam_id	tag	difficulty	duration
9004	算法	medium	80

该题目使用的表和数据如下：

/* 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; INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES (9001, '算法', 'hard', 60, '2020-01-01 10:00:00'), (9002, '算法', 'hard', 80, '2020-01-01 10:00:00'), (9003, 'SQL', 'medium', 70, '2020-01-01 10:00:00'), (9004, '算法,medium,80','', 0, '2020-01-01 10:00:00'); */ 

解答：

讯享网/* select exam_id, SUBSTRING_INDEX(tag,',',1) tag, SUBSTRING_INDEX(SUBSTRING_INDEX(tag,',',2),',',-1) difficulty, SUBSTRING_INDEX(tag,',',-1) duration from examination_info where duration = 0; */ mysql> select exam_id, -> SUBSTRING_INDEX(tag,',',1) tag, -> SUBSTRING_INDEX(SUBSTRING_INDEX(tag,',',2),',',-1) difficulty, -> SUBSTRING_INDEX(tag,',',-1) duration -> from examination_info -> where duration = 0; +---------+--------+------------+----------+ | exam_id | tag | difficulty | duration | +---------+--------+------------+----------+ | 9004 | 算法 | medium | 80 | +---------+--------+------------+----------+ 1 row in set (0.00 sec) 

109、字符串函数的使用（2）

该题目使用的表和数据如下：

/* drop table if exists user_info; CREATE TABLE 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(32) COMMENT '职业方向', register_time datetime COMMENT '注册时间' )CHARACTER SET utf8 COLLATE utf8_general_ci; INSERT INTO user_info(uid,`nick_name`,achievement,`level`,job,register_time) VALUES (1001, '牛客1', 19, 0, '算法', '2020-01-01 10:00:00'), (1002, '牛客2号', 1200, 3, '算法', '2020-01-01 10:00:00'), (1003, '牛客3号♂', 22, 0, '算法', '2020-01-01 10:00:00'), (1004, '牛客4号', 25, 0, '算法', '2020-01-01 11:00:00'), (1005, '牛客号', 4000, 7, '算法', '2020-01-01 10:00:00'), (1006, '牛客789号', 25, 0, '算法', '2020-01-02 11:00:00'); */ 

用户信息表：user_info（uid：用户ID，nick_name：昵称，achievement：成就值，level：等级，job：职业方向， register_time：注册时间），表中数据如下：

讯享网mysql> select * from user_info; +----+------+-------------------------+-------------+-------+--------+---------------------+ | id | uid | nick_name | achievement | level | job | register_time | +----+------+-------------------------+-------------+-------+--------+---------------------+ | 1 | 1001 | 牛客1 | 19 | 0 | 算法 | 2020-01-01 10:00:00 | | 2 | 1002 | 牛客2号 | 1200 | 3 | 算法 | 2020-01-01 10:00:00 | | 3 | 1003 | 牛客3号♂ | 22 | 0 | 算法 | 2020-01-01 10:00:00 | | 4 | 1004 | 牛客4号 | 25 | 0 | 算法 | 2020-01-01 11:00:00 | | 5 | 1005 | 牛客号 | 4000 | 7 | 算法 | 2020-01-01 10:00:00 | | 6 | 1006 | 牛客789号 | 25 | 0 | 算法 | 2020-01-02 11:00:00 | +----+------+-------------------------+-------------+-------+--------+---------------------+ 6 rows in set (0.00 sec) 

【问题】查询字符数大于 10 的用户信息，对于字符数大于 13 的用户输出前 10 个字符然后加上三个点号：『…』。

查询结果如下：

uid	nick_name
1005	牛客号
1006	牛客…

解答：

/* select uid, if(char_length(nick_name) > 13, concat(left(nick_name,10),'...'), nick_name) nick_name from user_info where char_length(nick_name) > 10; */ mysql> select uid, if(char_length(nick_name) > 13, concat(left(nick_name,10),'...'), nick_name) nick_name -> from user_info -> where char_length(nick_name) > 10; +------+---------------------+ | uid | nick_name | +------+---------------------+ | 1005 | 牛客号 | | 1006 | 牛客... | +------+---------------------+ 2 rows in set (0.03 sec)