Sql入门和综合

现在运营想要查看用户信息表中所有的数据，请你取出相应结果

SELECT id,device_id,gender,age,university,province from user_profile

不建议select * from

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

select distinct university from user_profile  使用distinct

select university from user_profile group by university; 使用group by

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

SELECT * FROM table LIMIT 5,5，使用limit

SELECT  device_id FROM user_profile ORDER BY id LIMIT 2

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

SELECT device_id AS user_infos_example FROM user_profile LIMIT 2   使用as

可对查询结果进行排序。

select device_id,age from user_profile order by age asc; // order by + 列名 asc/desc:根据那一列升序/降序  默认升序

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

select device_id,gpa,age from user_profile order by gpa asc,age asc;  order by 可以多个

在运营想要对用户的年龄分布开展分析，在分析时想要剔除没有获取到年龄的用户，

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

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

SELECT device_id,gender,age,university,gpa FROM user_profile WHERE university IN ('北京大学','复旦大学','山东大学')

现在运营想要找到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);

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

SELECT device_id,age,university FROM user_profile WHERE university LIKE '%北京%'

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

# 方法1
# select max(gpa) as gpa
# from user_profile
# where university='复旦大学';

#方法2
select gpa
from user_profile
where university='复旦大学'
order by gpa desc limit 1

题目要求得到『男性用户有多少人』以及『他们的平均gpa是多少』

SELECT COUNT(gender)male_num,ROUND(AVG(gpa),1) AS avg_gpa FROM user_profile
WHERE gender='male'  count统计数量，保留一位小数用round

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

SELECT university,AVG(question_cnt)avg_quesition_cnt,AVG(answer_cnt)avg_answer_cnt
FROM user_profile
GROUP BY university
HAVING avg_quesition_cnt < 5 OR avg_answer_cnt < 20

先分组再筛选  group by having

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

-- join 以及子查询 方法1： select qpd.device_id, qpd.question_id, qpd.result from question_practice_detail as qpd inner join user_profile as up on up.device_id=qpd.device_id and up.university='浙江大学'

方法2：

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

方法3： select a.device_id device_id,a.question_id question_id,a.result result from question_practice_detail a,user_profile b where b.device_id = a.device_id and university = '浙江大学'

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

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
u.university,
qd.difficult_level,
count(q.question_id)/count(distinct(q.device_id)) AS avg_answer_cnt
FROM question_practice_detail AS q
LEFT JOIN user_profile AS u
ON u.device_id=q.device_id
LEFT JOIN question_detail AS qd
ON q.question_id=qd.question_id
GROUP BY u.university, qd.difficult_level;

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

结果不去重就用union all，去重就用 union。
where university='山东大学' or gender="male"的话，也是去重的。