mysql实战从入门到精通

• 查询去重数据:

select distinct device_id from user_profile

• 查询前面两行数据:

select device_id from user_profile limit 2

• 查询前面两行数据,且把原表中的字段名字换成user_infos_example

select device_id as user_infos_example from user_profile limit 2

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

select device_id,gender,age,university from user_profile where university not in (‘复旦大学’)
或者select device_id, gender, age, university from user_profile where university != ‘复旦大学’

• 用where过滤空值

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

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

select gpa from user_profile where university =‘复旦大学’ order by gpa desc limit 1
或者select Max(gpa) as 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(id) as user_num,avg(active_days_within_30)as avg_active_day,
avg(question_cnt) as avg_question from user_profilarounde group by university,gender

• 现在运营想要查看不同大学的用户平均发帖情况，并期望结果按照平均发帖情况进行升序排列，请你取出相应数据。
  select university, avg(question_cnt) as avg_question_cnt from user_profile group by university
  order by avg_question_cnt
• 现在运营想要查看所有来自浙江大学的用户题目回答明细情况，请你取出相应数据

select q.device_id,question_id,result from question_practice_detail as q ,user_profile as u where u.university =‘浙江大学’ and q.device_id=u.device_id

• 运营想要了解每个学校答过题的用户平均答题数量情况，请你取出数据。
  select u.university,count (q.question_id)/count(distinct q.device_id) as avg_answer_cnt
  from question_practice_detail as q join user_profile as u on q.device_id=u.device_id
  group by u.university;
  (平均答题数=答题总数/不重复的答题者人数)
• 运营想要计算一些参加了答题的不同学校、不同难度的用户平均答题量，请你写SQL取出相应数据
  select us.university,qd.difficult_level,count(qu.question_id)/count(distinct (us.device_id)) AS avg_answer_cnt from user_profile as us
  join question_practice_detail as qu on us.device_id=qu.device_id
  join question_detail as qd on qd.question_id=qu.question_id
  group by us.university,qd.difficult_level
• 运营想要查看参加了答题的山东大学的用户在不同难度下的平均答题题目数，请取出相应数据

select u.university,qd.difficult_level,count(qu.question_id)/count(distinct(qu.device_id)) as avg_answer_cnt
from user_profile as u
join question_practice_detail as qu on u.device_id=qu.device_id
join question_detail as qd on qd.question_id=qu.question_id
where u.university=‘山东大学’ group by qd.difficult_level

• 去重查询
  区别： union 会去重，union all 不去重 all表示在最后结果中不去重展示所有,如果有分组排序，ORDER BY语句要放在最后一个查询语句的后边
  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’

• 题目：现在运营想要将用户划分为25岁以下和25岁及以上两个年龄段，分别查看这两个年龄段用户数量(本题注意：age为null 也记为 25岁以下) 分两段筛选
  select if(age<25 or age is null,“25岁以下”,‘25岁及以上’)age_cut,count(device_id) number from user_profile
  group by age_cut
  或者
  select "25岁以下"age_cut,count(device_id)number from user_profile
  where age<25 or age is null
  union
  select"25岁及以上"age_cut,count(device_id)Number from user_profile
  where age>=25

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

select device_id,gender,
case when age<20 then’20以下’
when age between 20 and 24 then ‘20-24岁’
when age >24 then’25岁及以上’
else’其他’ end age_cut
from user_profile

或者
SELECT device_id, gender,
IF (age <20, ‘20岁以下’, IF(age >=25, ‘25岁及以上’,IF(age BETWEEN 20 AND 24, ‘20-24岁’, ‘其他’))) AS age_cut
FROM user_profile;

• 现在运营想要计算出2021年8月每天用户练习题目的数量，请取出相应数据。
  select day(date) day,count(question_id) question_cnt
  from question_practice_detail
  where date like ‘%2021-08%’
  group by day;
  或者
  select dayY(date) as day,
  count(question_id) as question_cnt
  from question_practice_detail
  where year(date)=“2021” and month(date)=“08”
  group by day;

• 题目：现在运营想要查看用户在某天刷题后第二天还会再来刷题的平均概率。请你取出相应数据。
  (次日留存率= 去重的数据表中所有条目数目/去重的数据表中符合次日留存的条目数目)
  select count(q2.device_id)/count(q1.device_id) as avg_ret
  from(select distinct device_id,date from question_practice_detail)as q1
  left join(select distinct device_id,date from question_practice_detail)as q2
  on q1.device_id=q2.device_id and q2.date=date_add(q1.date,interval 1 day)
  
  SELECT a.device_id
  ,a.university
  ,a.gpa
  from user_profile a
  where gpa <= all (
  select b.gpa
  from user_profile b
  where a.university = b.university )
  order by a.university
  
  

• 现在运营想要了解浙江大学的用户在不同难度题目下答题的正确率情况，请取出相应数据，并按照准确率升序输出。
  select qd.difficult_level,round(sum(if(qp.result=‘right’,1,0))/count(1),4)correct_rate
  from (user_profile u
  join question_practice_detail qp on u.device_id=qp.device_id
  join question_detail qd on qp.question_id=qd.question_id)
  where
  u.university=‘浙江大学’ group by qd.difficult_level order by correct_rate