• 数据库系统原理与应用教程（061）—— MySQL 练习题：操作题 21-31（五）

    数据库系统原理与应用教程（061）—— MySQL 练习题：操作题 21-31（五）：连接查询与判断函数

    21、连接查询（1）

    题目：要查看所有来自浙江大学的用户回答问题情况，取出相应数据。

    示例：question_practice_detail 表的数据如下。

    iddevice_idquestion_idresult
    12138111wrong
    23214112wrong
    33214113wrong
    46543114right
    52315115right
    62315116right
    72315117wrong

    示例：user_profile 表的数据如下。

    iddevice_idgenderageuniversitygpaactive_days_within_30question_cntanswer_cnt
    12138male21北京大学3.47212
    23214male复旦大学4.015525
    36543female20北京大学3.212330
    42315female23浙江大学3.6512
    55432male25山东大学3.8201570
    62131male28山东大学3.315713
    74321female26复旦大学3.69652

    查询应返回以下结果，查询结果根据 question_id 升序排序：

    device_idquestion_idresult
    2315115right
    2315116right
    2315117wrong

    表结构及数据如下：

    /*
drop table if exists `user_profile`;
drop table if  exists `question_practice_detail`;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` int ,
`question_cnt` int ,
`answer_cnt` int 
);

CREATE TABLE `question_practice_detail` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`question_id`int NOT NULL,
`result` varchar(32) NOT NULL
);

INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);

INSERT INTO question_practice_detail VALUES(1,2138,111,'wrong');
INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong');
INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong');
INSERT INTO question_practice_detail VALUES(4,6543,111,'right');
INSERT INTO question_practice_detail VALUES(5,2315,115,'right');
INSERT INTO question_practice_detail VALUES(6,2315,116,'right');
INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong');
*/

    • 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

    解答：

    /*
-- 使用连接查询
select q.device_id, q.question_id, q.result 
from question_practice_detail q join user_profile u
on q.device_id = u.device_id
where u.university = '浙江大学';

-- 使用子查询
select device_id, question_id, result 
from question_practice_detail 
where device_id in 
(select device_id from user_profile where university = '浙江大学');
*/
-- 使用连接查询
mysql> select q.device_id, q.question_id, q.result 
    -> from question_practice_detail q join user_profile u
    -> on q.device_id = u.device_id
    -> where u.university = '浙江大学';
+-----------+-------------+--------+
| device_id | question_id | result |
+-----------+-------------+--------+
|      2315 |         115 | right  |
|      2315 |         116 | right  |
|      2315 |         117 | wrong  |
+-----------+-------------+--------+
3 rows in set (0.00 sec)

-- 使用子查询
mysql> select device_id, question_id, result 
    -> from question_practice_detail 
    -> where device_id in 
    -> (select device_id from user_profile where university = '浙江大学');
+-----------+-------------+--------+
| device_id | question_id | result |
+-----------+-------------+--------+
|      2315 |         115 | right  |
|      2315 |         116 | right  |
|      2315 |         117 | wrong  |
+-----------+-------------+--------+
3 rows 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
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40

    22、连接查询（2）

    题目：查询每个学校答过题的用户平均答题数量，取出相关数据。

    示例：user_profile 表的数据如下。

    device_idgenderageuniversitygpaactive_days_within_30
    2138male21北京大学3.47
    3214maleNULL复旦大学415
    6543female20北京大学3.212
    2315female23浙江大学3.65
    5432male25山东大学3.820
    2131male28山东大学3.315
    4321male28复旦大学3.69

    示例：question_practice_detail 表的数据如下。

    device_idquestion_idresult
    2138111wrong
    3214112wrong
    3214113wrong
    6543111right
    2315115right
    2315116right
    2315117wrong
    5432118wrong
    5432112wrong
    2131114right
    5432113wrong

    查找每个学校用户的平均答题数目（说明：某学校用户平均答题数量计算方式为该学校用户答题总次数除以答过题的不同用户个数），查询应返回以下结果（结果保留4位小数），按照 university 升序排序。

    universityavg_answer_cnt
    北京大学1.0000
    复旦大学2.0000
    山东大学2.0000
    浙江大学3.0000

    表结构及数据如下：

    /*
drop table if exists `user_profile`;
drop table if  exists `question_practice_detail`;
CREATE TABLE `user_profile` (
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` int
);

CREATE TABLE `question_practice_detail` (
`device_id` int NOT NULL,
`question_id`int NOT NULL,
`result` varchar(32) NOT NULL
);

INSERT INTO user_profile VALUES(2138,'male',21,'北京大学',3.4,7);
INSERT INTO user_profile VALUES(3214,'male',null,'复旦大学',4.0,15);
INSERT INTO user_profile VALUES(6543,'female',20,'北京大学',3.2,12);
INSERT INTO user_profile VALUES(2315,'female',23,'浙江大学',3.6,5);
INSERT INTO user_profile VALUES(5432,'male',25,'山东大学',3.8,20);
INSERT INTO user_profile VALUES(2131,'male',28,'山东大学',3.3,15);
INSERT INTO user_profile VALUES(4321,'male',28,'复旦大学',3.6,9);

INSERT INTO question_practice_detail VALUES(2138,111,'wrong');
INSERT INTO question_practice_detail VALUES(3214,112,'wrong');
INSERT INTO question_practice_detail VALUES(3214,113,'wrong');
INSERT INTO question_practice_detail VALUES(6543,111,'right');
INSERT INTO question_practice_detail VALUES(2315,115,'right');
INSERT INTO question_practice_detail VALUES(2315,116,'right');
INSERT INTO question_practice_detail VALUES(2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(5432,118,'wrong');
INSERT INTO question_practice_detail VALUES(5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(2131,114,'right');
INSERT INTO question_practice_detail VALUES(5432,113,'wrong');
*/

    • 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

    解答：

    /*
select u.university, 
       round(count(*)/count(distinct u.device_id),4) avg_answer_cnt
from question_practice_detail q join user_profile u
on q.device_id = u.device_id
group by u.university
order by u.university;
*/
mysql> select u.university, 
    ->        round(count(*)/count(distinct u.device_id),4) avg_answer_cnt
    -> from question_practice_detail q join user_profile u
    -> on q.device_id = u.device_id
    -> group by u.university
    -> order by u.university;
+--------------+----------------+
| university   | avg_answer_cnt |
+--------------+----------------+
| 北京大学     |         1.0000 |
| 复旦大学     |         2.0000 |
| 山东大学     |         2.0000 |
| 浙江大学     |         3.0000 |
+--------------+----------------+
4 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

    23、连接查询（3）

    题目：查询参加了答题的不同学校、不同难度的用户平均答题量，取出相应数据。

    示例：user_profile（用户信息表）的数据如下。

    iddevice_idgenderageuniversitygpaactive_days_within_30question_cntanswer_cnt
    12138male21北京大学3.47212
    23214maleNULL复旦大学415525
    36543female20北京大学3.212330
    42315female23浙江大学3.6512
    55432male25山东大学3.8201570
    62131male28山东大学3.315713
    74321male28复旦大学3.69652

    示例：question_practice_detail（题库练习明细表）的数据如下。

    iddevice_idquestion_idresult
    12138111wrong
    23214112wrong
    33214113wrong
    46534111right
    52315115right
    62315116right
    72315117wrong
    85432117wrong
    95432112wrong
    102131113right
    115432113wrong
    122315115right
    132315116right
    142315117wrong
    155432117wrong
    165432112wrong
    172131113right
    185432113wrong
    192315117wrong
    205432117wrong
    215432112wrong
    222131113right
    235432113wrong

    示例：question_detail（用户信息表）的数据如下。

    idquestion_iddifficult_level
    1111hard
    2112medium
    3113easy
    4115easy
    5116medium
    6117easy

    请写出 SQL 语句，计算不同学校、不同难度的用户平均答题量，查询应返回以下结果（数据四舍五入保留 4 位小数)：

    universitydifficult_levelavg_answer_cnt
    北京大学hard1.0000
    复旦大学easy1.0000
    复旦大学medium1.0000
    山东大学easy4.5000
    山东大学medium3.0000
    浙江大学easy5.0000
    浙江大学medium2.0000

    表结构及数据如下：

    /*
drop table if exists `user_profile`;
drop table if  exists `question_practice_detail`;
drop table if  exists `question_detail`;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` int ,
`question_cnt` int ,
`answer_cnt` int 
);

CREATE TABLE `question_practice_detail` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`question_id`int NOT NULL,
`result` varchar(32) NOT NULL
);

CREATE TABLE `question_detail` (
`id` int NOT NULL,
`question_id`int NOT NULL,
`difficult_level` varchar(32) NOT NULL
);

INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);

INSERT INTO question_practice_detail VALUES(1,2138,111,'wrong');
INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong');
INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong');
INSERT INTO question_practice_detail VALUES(4,6543,111,'right');
INSERT INTO question_practice_detail VALUES(5,2315,115,'right');
INSERT INTO question_practice_detail VALUES(6,2315,116,'right');
INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(8,5432,117,'wrong');
INSERT INTO question_practice_detail VALUES(9,5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(10,2131,113,'right');
INSERT INTO question_practice_detail VALUES(11,5432,113,'wrong');
INSERT INTO question_practice_detail VALUES(12,2315,115,'right');
INSERT INTO question_practice_detail VALUES(13,2315,116,'right');
INSERT INTO question_practice_detail VALUES(14,2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(15,5432,117,'wrong');
INSERT INTO question_practice_detail VALUES(16,5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(17,2131,113,'right');
INSERT INTO question_practice_detail VALUES(18,5432,113,'wrong');
INSERT INTO question_practice_detail VALUES(19,2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(20,5432,117,'wrong');
INSERT INTO question_practice_detail VALUES(21,5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(22,2131,113,'right');
INSERT INTO question_practice_detail VALUES(23,5432,113,'wrong');

INSERT INTO question_detail VALUES(1,111,'hard');
INSERT INTO question_detail VALUES(2,112,'medium');
INSERT INTO question_detail VALUES(3,113,'easy');
INSERT INTO question_detail VALUES(4,115,'easy');
INSERT INTO question_detail VALUES(5,116,'medium');
INSERT INTO question_detail VALUES(6,117,'easy');
*/

    • 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
    • 64
    • 65
    • 66
    • 67
    • 68

    解答：

    /*
select u.university, qd.difficult_level,
       round(count(*)/count(distinct u.device_id),4) avg_answer_cnt
from user_profile u join question_practice_detail q
     on u.device_id = q.device_id
     join question_detail qd on q.question_id = qd.question_id
group by u.university, qd.difficult_level;
*/
mysql> select u.university, qd.difficult_level,
    ->        round(count(*)/count(distinct u.device_id),4) avg_answer_cnt
    -> from user_profile u join question_practice_detail q
    ->      on u.device_id = q.device_id
    ->      join question_detail qd on q.question_id = qd.question_id
    -> group by u.university, qd.difficult_level;
+--------------+-----------------+----------------+
| university   | difficult_level | avg_answer_cnt |
+--------------+-----------------+----------------+
| 北京大学     | hard            |         1.0000 |
| 复旦大学     | easy            |         1.0000 |
| 复旦大学     | medium          |         1.0000 |
| 山东大学     | easy            |         4.5000 |
| 山东大学     | medium          |         3.0000 |
| 浙江大学     | easy            |         5.0000 |
| 浙江大学     | medium          |         2.0000 |
+--------------+-----------------+----------------+
7 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

    24、连接查询（4）

    题目：查询参加了答题的山东大学的用户在不同难度下的平均答题题目数，取出相应数据。

    示例：user_profile（用户信息表）的数据如下。

    iddevice_idgenderageuniversitygpaactive_days_within_30question_cntanswer_cnt
    12138male21北京大学3.47212
    23214maleNULL复旦大学415525
    36543female20北京大学3.212330
    42315female23浙江大学3.6512
    55432male25山东大学3.8201570
    62131male28山东大学3.315713
    74321male28复旦大学3.69652

    示例：question_practice_detail（题库练习明细表）的数据如下。

    iddevice_idquestion_idresult
    12138111wrong
    23214112wrong
    33214113wrong
    46534111right
    52315115right
    62315116right
    72315117wrong
    85432117wrong
    95432112wrong
    102131113right
    115432113wrong
    122315115right
    132315116right
    142315117wrong
    155432117wrong
    165432112wrong
    172131113right
    185432113wrong
    192315117wrong
    205432117wrong
    215432112wrong
    222131113right
    235432113wrong

    示例：question_detail（用户信息表）的数据如下。

    idquestion_iddifficult_level
    1111hard
    2112medium
    3113easy
    4115easy
    5116medium
    6117easy

    查询应返回以下结果（数据四舍五入保留 4 位小数）：

    universitydifficult_levelavg_answer_cnt
    山东大学easy4.5000
    山东大学medium3.0000

    解答：

    /*
select u.university, qd.difficult_level,
       round(count(*)/count(distinct u.device_id),4) avg_answer_cnt
from user_profile u join question_practice_detail q
     on u.device_id = q.device_id
     join question_detail qd on q.question_id = qd.question_id
where u.university = '山东大学'
group by u.university, qd.difficult_level;
*/
mysql> select u.university, qd.difficult_level,
    ->        round(count(*)/count(distinct u.device_id),4) avg_answer_cnt
    -> from user_profile u join question_practice_detail q
    ->      on u.device_id = q.device_id
    ->      join question_detail qd on q.question_id = qd.question_id
    -> where u.university = '山东大学'
    -> group by u.university, qd.difficult_level;
+--------------+-----------------+----------------+
| university   | difficult_level | avg_answer_cnt |
+--------------+-----------------+----------------+
| 山东大学     | easy            |         4.5000 |
| 山东大学     | medium          |         3.0000 |
+--------------+-----------------+----------------+
2 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

    25、联合查询（union）

    题目：查看学校为山东大学或者性别为男性的用户的 device_id、gender、age 和 gpa数据，取出相应结果（结果不去重）。

    示例：user_profile 的数据如下。

    iddevice_idgenderageuniversitygpaactive_days_within_30question_cntanswer_cnt
    12138male21北京大学3.47212
    23214male复旦大学415525
    36543female20北京大学3.212330
    42315female23浙江大学3.6512
    55432male25山东大学3.8201570
    62131male28山东大学3.315713
    74321male26复旦大学3.69652

    查询应返回以下结果：

    device_idgenderagegpa
    5432male253.8
    2131male283.3
    2138male213.4
    3214maleNone4
    5432male253.8
    2131male283.3
    4321male283.6

    解答：

    /*
select device_id, gender, age, gpa from user_profile where university= '山东大学'
union all
select device_id, gender, age, gpa from user_profile where gender = 'male';
*/
mysql> select device_id, gender, age, gpa from user_profile where university= '山东大学'
    -> union all
    -> select device_id, gender, age, gpa from user_profile where gender = 'male';
+-----------+--------+------+------+
| device_id | gender | age  | gpa  |
+-----------+--------+------+------+
|      5432 | male   |   25 |  3.8 |
|      2131 | male   |   28 |  3.3 |
|      2138 | male   |   21 |  3.4 |
|      3214 | male   | NULL |    4 |
|      5432 | male   |   25 |  3.8 |
|      2131 | male   |   28 |  3.3 |
|      4321 | male   |   28 |  3.6 |
+-----------+--------+------+------+
7 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

    26、IF 函数的使用

    题目：将用户划分为 25 岁以下和 25 岁及以上两个年龄段，分别查询这两个年龄段用户数量（age为null 也记为 25岁以下）。

    示例：user_profile 的数据如下。

    iddevice_idgenderageuniversitygpaactive_days_within_30question_cntanswer_cnt
    12138male21北京大学3.47212
    23214male复旦大学415525
    36543female20北京大学3.212330
    42315female23浙江大学3.6512
    55432male25山东大学3.8201570
    62131male28山东大学3.315713
    74321male26复旦大学3.69652

    查询应返回以下结果：

    age_cutnumber
    25 岁以下4
    25 岁及以上3
    /*
select '25 岁以下' age_cut,sum(if(age >=25, 0, 1)) number from user_profile
union
select '25 岁及以上' age_cut,sum(if(age >=25, 1, 0)) number from user_profile;

select '25 岁以下' age_cut, count(*) number from user_profile where age < 25 or age is null
union
select '25 岁及以上' age_cut, count(*) number from user_profile where age >=25;

select if(age >=25, '25 岁及以上', '25 岁以下') age_cut, count(*) number 
from user_profile group by age_cut; 
*/
mysql> select if(age >=25, '25 岁及以上', '25 岁以下') age_cut, count(*) number 
    -> from user_profile group by age_cut; 
+-----------------+--------+
| age_cut         | number |
+-----------------+--------+
| 25 岁以下       |      4 |
| 25 岁及以上     |      3 |
+-----------------+--------+
2 rows in set (0.00 sec)

mysql> select '25 岁以下' age_cut,sum(if(age >=25, 0, 1)) number from user_profile
    -> union
    -> select '25 岁及以上' age_cut,sum(if(age >=25, 1, 0)) number from user_profile;
+-----------------+--------+
| age_cut         | number |
+-----------------+--------+
| 25 岁以下       |      4 |
| 25 岁及以上     |      3 |
+-----------------+--------+
2 rows in set (0.00 sec)

mysql> select '25 岁以下' age_cut, count(*) number from user_profile where age < 25 or age is null
    -> union
    -> select '25 岁及以上' age_cut, count(*) number from user_profile where age >=25;
+-----------------+--------+
| age_cut         | number |
+-----------------+--------+
| 25 岁以下       |      4 |
| 25 岁及以上     |      3 |
+-----------------+--------+
2 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
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43

    27、CASE 函数的使用

    题目：将用户划分为 20 岁以下，20-24岁，25岁及以上三个年龄段，分别查看不同年龄段用户的明细情况，取出相应数据。（注：若年龄为空则返回其他）

    示例：user_profile 的数据如下。

    iddevice_idgenderageuniversitygpaactive_days_within_30question_cntanswer_cnt
    12138male21北京大学3.47212
    23214male复旦大学415525
    36543female20北京大学3.212330
    42315female23浙江大学3.6512
    55432male25山东大学3.8201570
    62131male28山东大学3.315713
    74321male26复旦大学3.69652

    查询应返回以下结果：

    device_idgenderage_cut
    2138male20-24岁
    3214male其他
    6543female20-24岁
    2315female20-24岁
    5432male25岁及以上
    2131male25岁及以上
    4321male25岁及以上

    解答：

    /*
select device_id, gender, 
       case 
           when age is null then '其他' 
           when age >=20 and age <= 24 then '20-24岁' 
           when age > 24 then '25岁及以上' 
        end age_cut 
from user_profile;
*/

mysql> select device_id, gender, 
    ->        case 
    ->            when age is null then '其他' 
    ->            when age >=20 and age <= 24 then '20-24岁' 
    ->            when age > 24 then '25岁及以上' 
    ->         end age_cut 
    -> from user_profile;
+-----------+--------+----------------+
| device_id | gender | age_cut        |
+-----------+--------+----------------+
|      2138 | male   | 20-24岁        |
|      3214 | male   | 其他           |
|      6543 | female | 20-24岁        |
|      2315 | female | 20-24岁        |
|      5432 | male   | 25岁及以上     |
|      2131 | male   | 25岁及以上     |
|      4321 | male   | 25岁及以上     |
+-----------+--------+----------------+
7 rows 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
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29

    28、日期函数（YEAR）的使用

    题目：计算出 2021 年 8 月每天用户练习题目的数量，取出相应数据。

    示例：question_practice_detail 的数据如下。

    iddevice_idquestion_idresultdate
    12138111wrong2021-05-03
    23214112wrong2021-05-09
    33214113wrong2021-06-15
    46543111right2021-08-13
    52315115right2021-08-13
    62315116right2021-08-14
    72315117wrong2021-08-15
    ……

    查询应返回以下结果：

    dayquestion_cnt
    135
    142
    153
    161
    181

    表结构及数据如下：

    /*
CREATE TABLE `question_practice_detail` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`question_id`int NOT NULL,
`result` varchar(32) NOT NULL,
`date` date NOT NULL
);

INSERT INTO question_practice_detail VALUES(1,2138,111,'wrong','2021-05-03');
INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong','2021-05-09');
INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong','2021-06-15');
INSERT INTO question_practice_detail VALUES(4,6543,111,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(5,2315,115,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(6,2315,116,'right','2021-08-14');
INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(8,3214,112,'wrong','2021-05-09');
INSERT INTO question_practice_detail VALUES(9,3214,113,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(10,6543,111,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(11,2315,115,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(12,2315,116,'right','2021-08-14');
INSERT INTO question_practice_detail VALUES(13,2315,117,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(14,3214,112,'wrong','2021-08-16');
INSERT INTO question_practice_detail VALUES(15,3214,113,'wrong','2021-08-18');
INSERT INTO question_practice_detail VALUES(16,6543,111,'right','2021-08-13');
*/

    • 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

    解答：

    /*
select day(date) day, count(*) question_cnt
from question_practice_detail 
where date between '2021-8-1' and '2021-8-31'
group by day;
*/
mysql> select day(date) day, count(*) question_cnt
    -> from question_practice_detail 
    -> where date between '2021-8-1' and '2021-8-31'
    -> group by day;
+------+--------------+
| day  | question_cnt |
+------+--------------+
|   13 |            5 |
|   14 |            2 |
|   15 |            3 |
|   16 |            1 |
|   18 |            1 |
+------+--------------+
5 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

    29、SUBSTRING_INDEX 函数的使用（1）

    题目：统计每个性别的用户分别有多少参赛者，取出相应结果。

    示例：user_submit 的数据如下。

    device_idprofileblog_url
    2138180cm,75kg,27,malehttp:/url/bigboy777
    3214165cm,45kg,26,femalehttp:/url/kittycc
    6543178cm,65kg,25,malehttp:/url/tiger
    4321171cm,55kg,23,femalehttp:/url/uhksd
    2131168cm,45kg,22,femalehttp:/urlsydney

    查询应返回以下结果：

    gendernumber
    male2
    female3

    表结构及数据如下：

    /*
drop table if exists user_submit;
CREATE TABLE `user_submit` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`profile` varchar(100) NOT NULL,
`blog_url` varchar(100) NOT NULL
);
INSERT INTO user_submit VALUES(1,2138,'180cm,75kg,27,male','http:/url/bisdgboy777');
INSERT INTO user_submit VALUES(1,3214,'165cm,45kg,26,female','http:/url/dkittycc');
INSERT INTO user_submit VALUES(1,6543,'178cm,65kg,25,male','http:/url/tigaer');
INSERT INTO user_submit VALUES(1,4321,'171cm,55kg,23,female','http:/url/uhsksd');
INSERT INTO user_submit VALUES(1,2131,'168cm,45kg,22,female','http:/url/sysdney');
*/

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

    解答：

    /*
select SUBSTRING_INDEX(profile,',',-1) gender, count(*) number
from user_submit
group by gender;
*/

mysql> select SUBSTRING_INDEX(profile,',',-1) gender, count(*) number
    -> from user_submit
    -> group by gender;
+--------+--------+
| gender | number |
+--------+--------+
| female |      3 |
| male   |      2 |
+--------+--------+
2 rows in set (0.02 sec)

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

    30、SUBSTRING_INDEX 函数的使用（2）

    题目：blog_url 列中 url 字符后的字符串为用户个人博客的用户名，取出用户名作为一个新列，取出所需数据。

    示例：user_submit 的数据如下。

    device_idprofileblog_url
    2138180cm,75kg,27,malehttp:/ur/bisdgboy777
    3214165cm,45kg,26,femalehttp:/url/dkittycc
    6543178cm,65kg,25,malehttp:/ur/tigaer
    4321171 cm,55kg,23,femalehttp:/url/uhksd
    2131168cm,45kg,22,femalehttp:/url/sydney

    查询应返回以下结果：

    device_iduser_name
    2138bisdgboy777
    3214dkittycc
    6543tigaer
    4321uhsksd
    2131sydney

    解答：

    /*
select device_id,
       SUBSTRING_INDEX(blog_url,'/',-1) user_name
from user_submit;
*/
mysql> select device_id,
    ->        SUBSTRING_INDEX(blog_url,'/',-1) user_name
    -> from user_submit;
+-----------+-------------+
| device_id | user_name   |
+-----------+-------------+
|      2138 | bisdgboy777 |
|      3214 | dkittycc    |
|      6543 | tigaer      |
|      4321 | uhsksd      |
|      2131 | sysdney     |
+-----------+-------------+
5 rows in set (0.00 sec)

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

    31、SUBSTRING_INDEX 函数的使用（3）

    题目：统计每个年龄的用户分别有多少参赛者，取出相应结果。

    示例：user_submit 的数据如下。

    device_idprofileblog_url
    2138180cm,75kg,27,malehttp:/ur/bigboy777
    3214165cm,45kg,26,femalehttp:/url/kittycc
    6543178cm,65kg,25,malehttp:/url/tiger
    4321171cm,55kg,23,femalehttp:/url/uhksd
    2131168cm,45kg,22,femalehttp:/url/sydney

    查询应返回以下结果：

    agenumber
    271
    261
    251
    231
    221

    解答：

    /*
select SUBSTRING_INDEX(SUBSTRING_INDEX(profile,',',3),',',-1) age,
       count(*) number
from user_submit
group by age;
*/
mysql> select SUBSTRING_INDEX(SUBSTRING_INDEX(profile,',',3),',',-1) age,
    ->        count(*) number
    -> from user_submit
    -> group by age;
+------+--------+
| age  | number |
+------+--------+
| 22   |      1 |
| 23   |      1 |
| 25   |      1 |
| 26   |      1 |
| 27   |      1 |
+------+--------+
5 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
  • 相关阅读:
    导图解文 从梦想到财富（41）比起熬夜加班，创业更需要赢在起点
    7、AWS SDK for Go-文件分片上传
    9.23 校招 实习 内推 面经
    CSDN 界面布局优化
    Hive (六) --------- 查询
    Hive3：数据的加载与导出
    【学习记录】Dynaslam源代码魔改-替换MaskRCNN为YoloV5
    目前无法建立VS2013与Qt的连接？？？
    Spring MVC ViewNameMethodReturnValueHandler原理解析
    Android AssetManager初探
  • 原文地址：https://blog.csdn.net/weixin_44377973/article/details/126027811