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

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

    101、判断空值（1）

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

    iduidexam_idstart_timesubmit_timescore
    1100190012020-01-02 09:01:012020-01-02 09:21:0180
    2100190012021-05-02 10:01:012021-05-02 10:30:0181
    3100190012021-09-02 12:01:01(NULL)(NULL)

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

    exam_idincomplete_cntincomplete_rate
    900110.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);
*/

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    解答：

    /*
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)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19

    102、判断空值（2）

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

    iduidnick_nameachievementleveljobregister_time
    11001牛客1号100算法2020-01-01 10:00:00
    21002牛客2号21006算法2020-01-01 10:00:00

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

    idexam_idtagdifficultydurationrelease_time
    19001SQLhard602020-01-01 10:00:00
    29002SQLeasy602020-01-01 10:00:00
    39004算法medium802020-01-01 10:00:00

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

    iduidexam_idstart_timesubmit_timescore
    1100190012020-01-02 09:01:012020-01-02 09:21:5980
    2100190012021-05-02 10:01:01(NULL)(NULL)
    3100190022021-02-02 19:01:012021-02-02 19:30:0187
    4100190012021-06-02 19:01:012021-06-02 19:32:0020
    5100190022021-09-05 19:01:012021-09-05 19:40:0189
    6100190022021-09-01 12:01:01(NULL)(NULL)
    7100290022021-05-05 18:01:012021-05-05 18:59:0290

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

    uidavg_scoreavg_time_took
    10013336.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);
*/

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48

    解答：

    /*
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)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22

    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);
*/

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63

    用户信息表：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)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    试卷作答记录表：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)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24

    题目练习记录表：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)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

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

    uidnick_nameachievement
    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)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18

    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, '666666', 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);
*/

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63

    用户信息表：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 | 666666        |        3000 |     6 | C++    | 2020-01-01 10:00:00 |
+----+------+---------------+-------------+-------+--------+---------------------+
6 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    试卷信息表：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)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    试卷作答记录表：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)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24

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

    uidexam_idavg_score
    1002900181
    1002900285
    1005900184
    1006900184

    解答：

    /*
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)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27

    105、构造查询条件（3）

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

    操作系统练习题
    doc 5星 超过95%的资源 57KB
    下载
    iduidnick_nameachievementleveljobregister_time
    11001牛客1号190算法2020-01-01 10:00:00
    21002牛客2号12003算法2020-01-01 10:00:00
    31003牛客3号♂220算法2020-01-01 10:00:00
    41004牛客4号250算法2020-01-01 10:00:00
    51005牛客555号20007C++2020-01-01 10:00:00
    6100666666630006C++2020-01-01 10:00:00

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

    iduidexam_idstart_timesubmit_timescore
    1100190012020-01-02 09:01:012020-01-02 09:21:5980
    2100190012021-05-02 10:01:01(NULL)(NULL)
    3100190022021-02-02 19:01:012021-02-02 19:30:0175
    4100190022021-09-01 12:01:012021-09-01 12:11:0160
    5100190032021-09-02 12:01:012021-09-02 12:41:0190
    6100190012021-06-02 19:01:012021-06-02 19:32:0020
    7100190022021-09-05 19:01:012021-09-05 19:40:0189
    8100190042021-09-03 12:01:01(NULL)(NULL)
    9100290012020-01-01 12:01:012020-01-01 12:31:0199
    10100290032020-02-01 12:01:012020-02-01 12:31:0182
    11100290032020-02-02 12:11:012020-02-02 12:41:0176

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

    levelscore_graderatio
    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, '666666', 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);
*/

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42

    解答：

    /*
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)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27

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

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

    iduidnick_nameachievementleveljobregister_time
    11001牛客1号190算法2020-01-01 10:00:00
    21002牛客2号12003算法2020-02-01 10:00:00
    31003牛客3号♂220算法2020-01-02 10:00:00
    41004牛客4号250算法2020-01-02 11:00:00
    51005牛客555号40007C++2020-01-11 10:00:00
    6100666666630006C++2020-11-01 10:00:00

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

    uidnick_nameregister_time
    1001牛客12020-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, '666666', 3000, 6, 'C++', '2020-11-01 10:00:00');
*/

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20

    解答：

    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)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

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

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

    iduidnick_nameachievementleveljobregister_time
    11001牛客1190算法2020-01-01 10:00:00
    21002牛客2号12003算法2020-01-01 10:00:00
    31003牛客3号♂220算法2020-01-01 10:00:00
    41004牛客4号250算法2020-01-01 10:00:00
    51005牛客555号40007算法2020-01-11 10:00:00
    61006牛客6号250算法2020-01-02 11:00:00
    71007牛客7号250算法2020-01-02 11:00:00
    81008牛客8号250算法2020-01-02 11:00:00
    91009牛客9号250算法2020-01-02 11:00:00
    101010牛客10号250算法2020-01-02 11:00:00
    11101166666630006C++2020-01-02 10:00:00

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

    idexam_idtagdifficultydurationrelease_time
    19001算法hard602020-01-01 10:00:00
    29002算法hard802020-01-01 10:00:00
    39003SQLmedium702020-01-01 10:00:00

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

    iduidexam_idstart_timesubmit_timescore
    1100190012020-01-02 09:01:012020-01-02 09:21:5980
    2100290032020-01-20 10:01:012020-01-20 10:10:0181
    3100290022020-01-01 12:11:012020-01-01 12:31:0183
    4100390022020-01-01 19:01:012020-01-01 19:30:0175
    5100490022020-01-01 12:01:012020-01-01 12:11:0160
    6100590022020-01-01 12:01:012020-01-01 12:41:0190
    7100690012020-01-02 19:01:012020-01-02 19:32:0020
    8100790022020-01-02 19:01:012020-01-02 19:40:0189
    9100890032020-01-02 12:01:012020-01-02 12:20:0199
    10100890012020-01-02 12:01:012020-01-02 12:31:0198
    11100990022020-01-02 12:01:012020-01-02 12:31:0182
    12101090022020-01-02 12:11:012020-01-02 12:41:0176
    13101190012020-01-02 10:01:012020-01-02 10:31:0189
    操作系统考题例题练习题
    zip 0星 超过10%的资源 1.35MB
    下载

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

    uidlevelregister_timemax_score
    101002020-01-02 11:00:0076
    100302020-01-01 10:00:0075
    100402020-01-01 11:00:0060

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

    /*
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, '666666', 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);
*/

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63

    解答：

    /*
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)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22

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

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

    idexam_idtagdifficultydurationrelease_time
    19001算法hard602021-01-01 10:00:00
    29002算法hard802021-01-01 10:00:00
    39003SQLmedium702021-01-01 10:00:00
    49004算法,medium,8002021-01-01 10:00:00

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

    exam_idtagdifficultyduration
    9004算法medium80

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

    /*
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');
  */

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    解答：

    /*
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)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20

    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, '牛客5678901234号', 4000, 7, '算法', '2020-01-01 10:00:00'),
  (1006, '牛客67890123456789号', 25, 0, '算法', '2020-01-02 11:00:00');
  */

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20

    用户信息表：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 | 牛客5678901234号        |        4000 |     7 | 算法   | 2020-01-01 10:00:00 |
|  6 | 1006 | 牛客67890123456789号    |          25 |     0 | 算法   | 2020-01-02 11:00:00 |
+----+------+-------------------------+-------------+-------+--------+---------------------+
6 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

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

    查询结果如下：

    uidnick_name
    1005牛客5678901234号
    1006牛客67890123…

    解答：

    /*
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 | 牛客5678901234号    |
| 1006 | 牛客67890123...     |
+------+---------------------+
2 rows in set (0.03 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
  • 相关阅读:
    Vuex：辅助函数：mapState，mapMutations，mapActions，mapGetters
    vue3接口封装 亲测有效
    枚举的定义和应用（JAVA基础十二）
    JVM的内存模型
    Flutter应用发布流程详解：从开发到上架一站式指南
    Cesium屏幕中心坐标和相机位置坐标计算
    05-`Linux`的软件管理
    去腾讯面试，直接让人出门左拐 ：幂等性都不知道！
    【CSDN｜每日一练】小艺改编字符串
    LeetCode_二分搜索_容斥原理_困难_878.第 N 个神奇数字
  • 原文地址：https://blog.csdn.net/weixin_44377973/article/details/126095593