筑坐极_mysql练习01-2

-- 25. 查找山东大学或者性别为男生的信息

-- 现在运营想要分别查看学校为山东大学或者性别为男性的用户的device_id、gender、age和gpa数据，请取出相应结果，结果不去重。

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';

-- 26. 分段统计:

-- 4-5为"优"，3.5-3.9 为“良”，3-3.4为“及格”，3以下为“不及格”，并统计每一等级的人数

select

count(case when gpa between 4 and 5 then '优' end ) as '优' ,

count(case when gpa between 3.5 and 3.9 then '良' end ) as '良' ,

count(case when gpa between 3 and 3.4 then '及格' end ) as '及格' ,

count(case when gpa <3 then '不及格' end ) as '不及格'

from `user_profile`;

select

case when gpa between 4 and 5 then '优'

when gpa between 3.5 and 3.9 then '良'

when gpa between 3 and 3.4 then '及格'

when gpa <3 then '不及格'

end as gpa_levle,count(id)

from `user_profile`

group by gpa_levle;

-- select case when gpa >= 4 and gpa <= 5 then '优';

-- 统计每个学校男生和女生的数量各是多少，统计结果的表头为，学校名，男生数量，女生数量

select

`university`,

count(case when `gender`="male" then "男的" end ) as '男生数量' ,

count(case when `gender`="female" then "女的" end ) as '女生数量'

from `user_profile` group by `university`;

-- 计算25岁以上和以下的用户数量

select

case

when age < 25 then '25岁以下'

when age >= 25 then '25岁及以上'

when age is null then '无年龄信息'

end as age_cut,

count(1) as quantity

from user_profile

group by age_cut;

-- 27.查看不同年龄段的用户明细

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

select

device_id,

case

when age < 25 then '25岁以下'

when age >= 25 then '25岁及以上'

else '其他'

end as age_cut

from user_profile;

-- 28.计算用户8月每天的练题数量

-- 现在运营想要计算出2021年8月每天用户练习题目的数量，请取出相应数据。

-- 创建新表并插入数据后再运行查询的SQL

drop table if exists `question_practice_detail`;

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

-- 对应的查询语句

select date, count(question_id) as q_count

from question_practice_detail

where date like '2021-08-%'

group by date;

-- 30.统计每种性别的人数

-- 现在运营举办了一场比赛，收到了一些参赛申请，表数据记录形式如下所示，

-- 现在运营想要统计每个性别的用户分别有多少参赛者，请取出相应结果

-- 创建新表并插入数据后再运行查询的SQL

-- 需要用到字符串截取,请查看课件里的"字符串截取.png"

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

select

substring_index(profile, ',', -1) gender,

count(1) quantity

from user_submit

group by gender;

-- 31. 提取博客URL中的用户名

-- 对于申请参与比赛的用户，blog_url字段中url字符后的字符串为用户个人博客的用户名，现在运营想要把用户的个人博客用户字段提取出单独记录为一个新的字段，请取出所需数据。

-- distinct

select id,device_id,`profile`,blog_url, substring_index(blog_url,"/",-1 )as blog from `user_submit`;

select id,device_id,profile,blog_url, substring_index(blog_url,"/",-1 ) as blog from `user_submit` ;

-- 截取出年龄

-- 32. 现在运营举办了一场比赛，收到了一些参赛申请，表数据记录形式如下所示，现在运营想要统计每个年龄的用户分别有多少参赛者，请取出相应结果

select substring_index( substring_index(profile,",",-2),",",1) as age_level,count(id) from `user_submit` group by age_level ;

-- 找出每个学校GPA最低的同学

-- 33.现在运营想要找到每个学校gpa最低的同学来做调研，请你取出每个学校的最低gpa。

select `university`,min(gpa),`device_id` from `user_profile` group by `university`;

-- 子查询

-- 统计复旦用户8月练题情况

-- 34.现在运营想要了解复旦大学的每个用户在8月份练习的总题目数和回答正确的题目数情况，请取出相应明细数据，对于在8月份没有练习过的用户，答题数结果返回0.

#写法一：

select

count(question_practice_detail.device_id) as A_answer,

count(case when `result`="right" then 'true' end) as '回答正确',

count(case when `result`="wrong" then 'false' end) as '回答错误',

`user_profile`.device_id

from `user_profile` left join `question_practice_detail`

on `user_profile`.device_id = `question_practice_detail`.device_id

and `question_practice_detail`.date like "2021-08-%"

where `university` = "复旦大学"

group by

case when `result`="right" then '回答正确'

when `result`="wrong" then '回答错误'

end and `user_profile`.device_id;

-- 浙大不同难度题目的正确率

-- 35.现在运营想要了解浙江大学的用户在不同难度题目下答题的正确率情况，请取出相应数据，并按照准确率升序输出。

select

-- count(case when `question_practice_detail`.result = 'right' then 'true' end ) as '回答正确',

-- count(case when `question_practice_detail`.result = 'wrong' then false end ) as '回答错误' ,

round(count(case when `question_practice_detail`.result = 'right' then 'true' end )/count(question_practice_detail.question_id),1) as '准确率',

`question_detail`.difficult_level

from `question_practice_detail` left join `user_profile`

on `question_practice_detail`.device_id = `user_profile`.device_id

left join `question_detail`

on `question_practice_detail`.question_id = `question_detail`.question_id

where `user_profile`.university = "浙江大学"

group by `question_detail`.difficult_level;

-- 查找后排序

-- 36.现在运营想要取出用户信息表中的用户年龄，请取出相应数据，并按照年龄升序排序。

SELECT device_id,age FROM user_profile ORDER BY age ASC;

-- 查找后多列排序

-- 37.现在运营想要取出用户信息表中的年龄和gpa数据，并先按照gpa升序排序，再按照年龄升序排序输出，请取出相应数据。

SELECT device_id,age,gpa FROM user_profile ORDER BY age ASC,gpa DESC;

-- 39.21年8月份练题总数

-- 现在运营想要了解2021年8月份所有练习过题目的总用户数和练习过的题目的总次数，请取出相应结果

select

count(distinct `question_practice_detail`.device_id) as '8月练习总人数',

count( `question_practice_detail`.question_id) as '8月练习总次数'

from `question_practice_detail`

where

-- year(date) = 2021 and month(date) = 08;

date like '2021-08-%';

mysql练习01-2

苏*

mysql

mysql练习01-2

2022.06.27

439人

苏*

天津市河东区

Hot:14509

苏*

当前作品暂无评分

还未获得评语哦~