筑坐极_mysql练习01-1

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 id, device_id, gender, age,university FROM user_profile;

-- 查询结果去重

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

SELECT device_id,university FROM user_profile group by university

union all

SELECT distinct device_id,university FROM user_profile;

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

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

SELECT device_id FROM user_profile limit 0,2;

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

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

SELECT device_id user_infos_example FROM user_profile limit 0,2;

SELECT device_id as '前两条设备ID' FROM user_profile limit 0,2;

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

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

select * from user_profile where university in ('北京大学');

select * from user_profile where university like ('北京大学%');

select * from user_profile where university = '北京大学';

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

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

select * from user_profile where age > 24 ;

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

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

select device_id, university from user_profile where

-- university != '复旦大学';

-- university <> '复旦大学';

-- university not like '复旦大学';

university not in ('复旦大学');

-- 用where过滤空值练习

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

select device_id, age, university from user_profile where age != 'null';

select device_id, age, university from user_profile where age is not null;

select device_id, age, university from user_profile where age <> 'null';

select device_id, age, university from user_profile where age not like 'null';

select device_id, age, university from user_profile where age not in ('null');

-- 查询空

select device_id, age, university from user_profile where age is null;

-- Where in 和Not in

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

select device_id, university from user_profile where

university in ('复旦大学', '山东大学');

-- 操作符混合运用

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

select device_id, university,gpa

from user_profile where

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

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

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

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

select * from user_profile where university like '北京%';

-- 查找GPA最高值

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

-- max(gpa)

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

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

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

-- round(1.589, 2)

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

select round(avg(gpa), 1), count(id) from user_profile where gender = 'male';

-- 分组计算练习题

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

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

from user_profile group by university, gender;

-- 分组

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

select university, avg(question_cnt) as '平均发帖', avg(answer_cnt) as '平均回帖'

from user_profile

group by university

having '平均发帖' < 5.0 or '平均回帖' < 20.0;

-- 分组

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

select university, avg(question_cnt) as avg_question_cnt

from user_profile

group by university

order by avg_question_cnt ;

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

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

SELECT * FROM question_practice_detail

where

device_id

in ( SELECT device_id FROM user_profile where university = '北京大学');

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

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

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

from question_practice_detail as qpd, user_profile as up

where up.device_id = qpd.device_id

group by up.university;

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

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

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

from question_practice_detail as qpd

left join user_profile as up on

up.device_id = qpd.device_id

left join question_detail as qd on

qd.question_id = qpd.question_id

group by up.university, qd.difficult_level;

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

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

-- 1

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

from question_practice_detail as qpd

left join user_profile as up on

up.device_id = qpd.device_id

left join question_detail as qd on

qd.question_id = qpd.question_id

where up.university = '山东大学'

group by qd.difficult_level;

-- 2

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

from

question_practice_detail as qpd,

user_profile as up,

question_detail as qd

where

up.device_id = qpd.device_id and

qd.question_id = qpd.question_id and

up.university = '山东大学'

group by qd.difficult_level;

mysql练习01-1

苏*

mysql

mysql练习01-1

2022.06.27

637人

苏*

天津市河东区

Hot:18675

苏*

当前作品暂无评分

还未获得评语哦~