筑坐极_mysql学习篇-2

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 auto_increment,

`device_id` int NOT NULL unique comment '设备id 不可重复',

`gender` varchar(14) NOT NULL comment '性别',

`age` int ,

`university` varchar(32) NOT NULL comment '大学',

`gpa` float comment '学分',

`active_days_within_30` int comment '在过去的30天里面活跃了几天',

`question_cnt` int comment '发帖数量',

`answer_cnt` int comment '回复数量',

PRIMARY KEY (`id`)

)

COMMENT='用户信息表';

CREATE TABLE `question_practice_detail` (

`id` int NOT NULL auto_increment,

`device_id` int NOT NULL comment '用户设备id',

`question_id`int NOT NULL comment '题目id',

`result` varchar(32) NOT NULL comment '回答正确还是错误赋值为right 或 wrong',

PRIMARY KEY (`id`)

)

COMMENT='题库练习明细表';

CREATE TABLE `question_detail` (

`id` int NOT NULL auto_increment,

`question_id`int NOT NULL unique comment '题目id',

`difficult_level` varchar(32) NOT NULL comment '难度难hard 中medium 易easy',

PRIMARY KEY (`id`)

)

COMMENT='题库难度表';

--插入表字段

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.现在运营想要查看所有数据。

select * from user_profile;

-- 查询多列

-- 2.现在运营想要用户的设备id对应的性别、年龄和学校的数据，请你取出相应数据。

select device_id,gender,age,university from practice1.user_profile;

-- 查询结果去重

-- 3.现在运营需要查看用户来自于哪些学校，请从用户信息表中取出学校的去重数据。

select university from practice1.user_profile group by university;

select distinct university from practice1.user_profile;

-- 查询结果限制返回行数

-- 4.现在运营只需要查看前2个用户明细设备ID数据，请你从用户信息表 user_profile 中取出相应结果。

select device_id from practice1.user_profile limit 0,2;

-- 将查询后的列重新命名

-- 5.现在你需要查看前2个用户明细设备ID数据，并将显示结果的列名改为 'user_infos_example',请你从用户信息表取出相应结果。

select device_id user_infos_example from practice1.user_profile limit 0,2;

select device_id as '前两条用户明细ID设备' from practice1.user_profile limit 0,2;

-- 查找学校是北大的学生信息

-- 6.现在运营想要筛选出所有北京大学的学生进行用户调研，请你从用户信息表中取出满足条件的数据，结果返回设备id和学校。

select device_id,university from practice1.user_profile where university = '北京大学';

-- 查找年龄大于24岁的用户信息

-- 7.现在运营想要针对24岁以上的用户开展分析，请你取出满足条件的设备ID、性别、年龄、学校。

select device_id,gender,age,university from practice1.user_profile where age > 24;

-- 查找除复旦大学的用户信息

-- 9.现在运营想要查看除复旦大学以外的所有用户明细，请你取出相应数据

select * from practice1.user_profile where university != '复旦大学';

-- 用where过滤空值练习

-- 10.现在运营想要对用户的年龄分布开展分析，在分析时想要剔除没有获取到年龄的用户，请你取出所有年龄值不为空的用户的设备ID，性别，年龄，学校的信息。

-- 不为空

select device_id,gender,age from practice1.user_profile where age is not null;

-- 查询空

select device_id,gender,age from practice1.user_profile where age is null;

- Where in 和Not in

-- 13.现在运营想要找到学校为北大、复旦和山大的同学进行调研，请你取出相关数据。

select university, device_id from practice1.user_profile where university in('北京大学','复旦大学','山东大学') ;

-- 操作符混合运用

-- 14.现在运营想要找到gpa在3.5以上(不包括3.5)的山东大学用户或 gpa在3.8以上(不包括3.8)的复旦大学同学进行用户调研，请你取出相应数据

select device_id,university,gpa from practice1.user_profile where

(gpa >3.5 and university = '山东大学') or

(gpa >3.8 and university = '复旦大学');

-- 查看学校名称中含北京的用户

-- 15.现在运营想查看所有大学中带有北京的用户的信息，请你取出相应数据。

select device_id,university from practice1.user_profile where university in ('北京大学');

select device_id,university from practice1.user_profile where university like '%北京%';

-- 查找GPA最高值

-- 16.运营想要知道复旦大学学生gpa最高值是多少，请你取出相应数据

select gpa from practice1.user_profile where university = '复旦大学' order by gpa desc limit 0,1;

-- 计算男生人数以及平均GPA

-- 17.现在运营想要看一下男性用户有多少人以及他们的平均gpa(保留一位小数)是多少，用以辅助设计相关活动，请你取出相应数据。

select avg(gpa) from practice1.user_profile where gender = 'male';

select round(avg(gpa),2) from practice1.user_profile where gender = 'male';

-- 分组计算练习题

-- 18.现在运营想要对每个学校不同性别的用户活跃情况和发帖数量进行分析，请分别计算出每个学校每种性别的用户数、30天内平均活跃天数和平均发帖数量。

select gender, university,count(1),avg(active_days_within_30), avg(question_cnt) from practice1.user_profile;

-- 根据学校和性别分组

select gender, university,count(1),avg(active_days_within_30), avg(question_cnt)

from practice1.user_profile

group by gender,university;

-- 分组

-- 19.现在运营想查看每个学校用户的平均发贴和回帖情况，寻找低活跃度学校进行重点运营，请取出平均发贴数低于5的学校或平均回帖数小于20的学校。

select university, avg(question_cnt) as aqc ,avg(answer_cnt) as aac

from practice1.user_profile

group by university

having aqc < 5.0 or aac < 20.0;

-- 分组

-- 20.现在运营想要查看不同大学的用户平均发帖情况，并期望结果按照平均发帖情况进行升序排列，请你取出相应数据。

select university,avg(question_cnt) from practice1.user_profile

group by university

order by avg(question_cnt);

-- 浙江大学用户题目回答情况

-- 21.现在运营想要查看所有来自浙江大学的用户题目回答明细情况，请你取出相应数据

select *from practice1.question_practice_detail

where device_id

in(select device_id from practice1.user_profile where university = '浙江大学');

-- 统计每个学校的答过题的用户的平均答题数

-- 22.运营想要了解每个学校答过题的用户平均答题数量情况，请你取出数据。

-- 题目id对应的设备id数量除用户大学对应的设备id的数量分组查

select up.university,count(qpd.question_id) / count(distinct up.device_id) as Acount

from question_practice_detail as qpd, user_profile as up

where qpd.device_id = up.device_id

group by up.university;

-- 统计每个学校各难度的用户平均刷题数

-- 23.运营想要计算一些参加了答题的不同学校、不同难度的用户平均答题量，请你写SQL取出相应数据

select up.university,pd.difficult_level,round(count(qbd.question_id) / count(distinct qbd.device_id),2)

from practice1.question_practice_detail as qbd

left join practice1.user_profile as up

on qbd.device_id = up.device_id

left join practice1.question_detail as pd

on qbd.question_id = pd.question_id

group by up.university,pd.difficult_level;

-- 统计每个用户的平均刷题数

-- 24.运营想要查看参加了答题的山东大学的用户在不同难度下的平均答题题目数，请取出相应数据

select up.university, qd.difficult_level, round(count(qpd.question_id) / count( distinct qpd.device_id), 2) as avg_answer_cnt

from niuke.question_practice_detail as qpd

left join niuke.user_profile as up on

up.device_id = qpd.device_id

left join niuke.question_detail as qd on

qd.question_id = qpd.question_id

where up.university = '山东大学'

group by qd.difficult_level;

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

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

select device_id,gender,age,gpa

from practice1.user_profile

where university = '山东大学'

union all

select device_id,gender,age,gpa

from practice1.user_profile

where gender = 'male';

-- 26. 分段统计:

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

select case when gpa between 4 and 5 then '优'

when gpa between 3 and 3.4 then '及格'

when gpa < 3 then '不及格'

end as gpa_cut,

count(1) as rs

from practice1.user_profile

group by gpa_cut;

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

select university,

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

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

from practice1.user_profile

group by university;

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

select age,

count(case when age >25 or age < 25 then '年龄' end) as '25岁以上或以下的用户数量'

from practice1.user_profile

group by age;

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

-- 现在运营想要将用户划分为20岁以下，20-24岁，25岁及以上三个年龄段，分别查看不同年龄段用户的明细情况，

-- 请取出相应数据。（注：若年龄为空请返回其他。）

select *,

case when age< 20 then '20岁以下'

when age between 20 and 24 then '20-24岁'

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

when age is null then '其他'

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

INSERT INTO question_practice_detail VALUES(16,4321,111,'right','2021-08-13');

-- 对应的查询语句

select date,count(question_practice_detail.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) rs

from practice1.user_submit

group by gender;

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

-- 对于申请参与比赛的用户，blog_url字段中url字符后的字符串为用户个人博客的用户名，

-- 现在运营想要把用户的个人博客用户字段提取出单独记录

-- 为一个新的字段，请取出所需数据。

select substring_index(blog_url,'/', -1) userName

from practice1.user_submit

group by blog_url;

-- 裁剪

-- 裁剪2

-- substr(字段, 开始位置, 裁多少位);

select substr(blog_url, 11, length(blog_url)-10) username

from user_submit order by username;

-- 替换

select replace(blog_url,'url','a') th

from user_submit

group by th;

-- 删除

select trim('http' from blog_url) as username

from user_submit

group by username;

-- 截取出年龄

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

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

-- '168cm,45kg,22,female'

select substring_index(substring_index(profile,',',-2),',',1) age,

count(1) rs

from user_submit

group by age;

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

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

select university,min(gpa),device_id from user_profile

group by university;

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

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

-- 对于在8月份没有练习过的用户，答题数结果返回0.

select up.device_id,university, count(qbd.question_id) as q_cnt,

sum( if(qbd.result = 'right', 1, 0) ) as q_r_cnt

from user_profile up

left join question_practice_detail qbd

on up.device_id = qbd.device_id and qbd.date like'2021-08-%'

where up.university = '复旦大学'

group by qbd.device_id;

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

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

select qd.difficult_level,sum(if(qbd.result = 'right',1,0)) / count(qbd.question_id) as rate

from

user_profile as up left join question_practice_detail as qbd

on up.device_id = qbd.device_id left join question_detail as qd

on qbd.question_id = qd.question_id

where up.university = '浙江大学'

group by qd.difficult_level

order by rate;

-- 查找后排序

-- 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 gpa asc, age asc;

-- 39.21年8月份练题总数

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

select count(distinct device_id) as user_cut,

count(question_id) as q_cut

from question_practice_detail

where date like '2021-08-%';

mysql学习篇-2

韩琳*

MySQL, 数据库,

mysql学习篇-2

2022.06.25

609人

韩琳*

天津市宁河县

Hot:14396

韩琳*

当前作品暂无评分

还未获得评语哦~