查询所有列

select * from user_profile；

查询用户的设备id对应的性别、年龄和学校的数据

select device_id,gender,age,university from user_profile;

从用户信息表中取出学校的去重数据

select distinct university from user_profile ;

查看前2个用户明细设备ID数据

select device_id from user_profile limit 2;

查看后10个用户明细设备ID数据

select device_id from user_profile limit 10,-1; //11行到last

查看前2个用户明细设备ID数据，并将列名改为 'user_infos_example'

select device_id as user_infos_example from user_profile limit 2;

用户年龄升序查看信息

select device_id, age from user_profile order by age asc;

用户年龄降序查看信息

select device_id, age from user_profile order by age desc;

并先按照gpa升序排序，再按照年龄升序排序输出

select device_id, gpa,age from user_profile order by gpa asc, age asc;

查找2021年8月份所有练习过题目的总用户数和练习过题目的总次数

select count(distinct device_id) as did_cnt, count(question_id) as question_cnt
from question_practice_detail
where date like '2021-08%';

查找所有北京大学的学生

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

查找24岁以上的用户

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

查找20岁及以上且23岁及以下的用户

select device_id, gender, age from user_profile where age >= 20 and age <= 23 ;
select device_id, gender, age from user_profile where age between 20 and 23 ;

查看除复旦大学以外的所有用户明细

select device_id, gender, age, university from user_profile where university != '复旦大学';
select device_id, gender,age,university from user_profile where university not in("复旦大学")

查看年龄值不为空的用户信息

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

查找gpa在3.5以上(不包括3.5)的山东大学用户 或 gpa在3.8以上(不包括3.8)的复旦大学同学

select device_id, gender, age, university, gpa from user_profile
where (university = '山东大学' and gpa > 3.5) or (university = '复旦大学' and gpa > 3.8);

查找复旦大学gpa最高值

select max(gpa) from user_profile where university = '复旦大学';

查找男性用户有多少人以及他们的平均gpa

select
count(gender) as male_num,
round(avg(gpa), 1) as avg_gpa
from user_profile where gender="male";

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

select gender, university, count(device_id) as user_num,
avg(active_days_within_30) as avg_active_days,
avg(question_cnt) as avg_question_cnt
from user_profile group by gender, university;

where 和 having的区别

执行时机不一样：where 是分组之前进行限定，不满足where条件，则不参与分组，而having是分组之后对结果进行过
滤。
可判断的条件不一样：where 不能对聚合函数进行判断，having 可以。

请取出平均发贴数低于5的学校或平均回帖数小于20的学校

select university,
avg(question_cnt) as avg_question_cnt,
avg(answer_cnt) as avg_answer_cnt
from user_profile group by university
having avg_question_cnt < 5 or avg_answer_cnt < 20;

不同大学的用户平均发帖情况，并期望结果按照平均发帖情况进行升序排列

select university, avg(question_cnt) as avg_question_count
from user_profile
group by university
order by avg_question_count asc;

查看所有来自浙江大学的用户题目回答明细情况

select device_id,question_id,result
from question_practice_detail
where device_id in(
select device_id from user_profile
where university = '浙江大学'
)
order by question_id;

每个学校答过题的用户平均答题数量情况

//显示内连接
select university,
count(question_id) / count(distinct qpd.device_id) as avg_answer_cnt
from question_practice_detail as qpd
inner join user_profile as up
on qpd.device_id=up.device_id
group by university
//隐式内连接
select university, count(question_id)/count(distinct qpd.device_id)
from user_profile as up, question_practice_detail as qpd
where up.device_id = qpd.device_id
group by university

查看学校为山东大学或者性别为男性的用户的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'