(SQL) 牛客 在线编程 非技术快速入门

前言

本文为牛客的在线编程的SQL非技术快速入门题集的个人解析

链接：牛客网 - SQL非技术快速入门

虽然这个题集叫：非技术快速入门，但还是学到很多东西的(主要是本身sql太菜了)

评论区各位大神的各种思路都很棒，同一个效果的sql，不同的人真的可以写出不同的形式

AC代码

01 基础查询

基础查询

SQL1 查询所有列

select
  *
from
  user_profile;

SQL2 查询多列

select
  device_id,
  gender,
  age,
  university
from
  user_profile;

简单处理查询结果

SQL3 查询结果去重

select
  distinct university
from
  user_profile;

SQL4 查询结果限制返回行数

select
  device_id
from
  user_profile
limit
  0, 2;

SQL5 将查询后的列重新命名

select
  device_id as user_infos_example
from
  user_profile
limit
  0, 2;

02 条件查询

基础排序

SQL36 查找后排序

select
  device_id,
  age
from
  user_profile
order by
  age;

SQL37 查找后多列排序

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

SQL38 查找后降序排列

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

基础操作符

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

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

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

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

SQL8 查找某个年龄段的用户信息

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

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

select
  device_id,
  gender,
  age,
  university
from
  user_profile
where
  university ! = '复旦大学';

SQL10 用where过滤空值练习

select
  device_id,
  gender,
  age,
  university
from
  user_profile
where
  # age is not null
  # 直接写age也行
  age;

高级操作符

SQL11 高级操作符练习(1)

select
  device_id,
  gender,
  age,
  university,
  gpa
from
  user_profile
where
  gender = 'male'
  and gpa > 3.5;

SQL12 高级操作符练习(2）

select
  device_id,
  gender,
  age,
  university,
  gpa
from
  user_profile
where
  university = '北京大学'
  or gpa > 3.7;

SQL13 Where in 和Not in

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

SQL14 操作符混合运用

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

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

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

03 高级查询

计算函数

SQL16 查找GPA最高值

select
  max(gpa)
from
  user_profile
where
  university = '复旦大学';
  
#########################
select
  gpa
from
  user_profile
where
  university = '复旦大学'
order by
  gpa desc
limit
  1;

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

select
  count(*) as male_num,
  # 保留一位小数
  round(avg(gpa), 1) as avg_gpa
from
  user_profile
where
  gender = 'male';

分组查询

SQL18 分组计算练习题

select
  gender,
  university,
  count(*) as user_num,
  round(avg(active_days_within_30), 1) as avg_active_day,
  round(avg(question_cnt), 1) as avg_question_cnt
from
  user_profile
group by
  # 两种属性分组
  university,
  gender;

SQL19 分组过滤练习题

select
  university,
  round(avg(question_cnt), 3) as avg_question_cnt,
  round(avg(answer_cnt), 3) as avg_answer_cnt
from
  user_profile
group by
  university 
  # 聚集函数不能用where要用having
having
  avg_question_cnt < 5
  or avg_answer_cnt < 20;
  # avg(question_cnt) < 5
  # or avg(answer_cnt) < 20;

SQL20 分组排序练习题

select
  university,
  round(avg(question_cnt), 4) as avg_question_cnt
from
  user_profile
group by
  university
order by
  avg_question_cnt

04 多表查询

子查询

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

# 链接查询 # 相同属性的必须指明是哪个表
select
  question_practice_detail.device_id,
  question_id,
  result
from
  question_practice_detail,
  user_profile
where
  user_profile.university = '浙江大学'
  and question_practice_detail.device_id = user_profile.device_id;
  
######################################
select
  device_id,
  question_id,
  result
from
  question_practice_detail
where
  device_id in (
    select
      device_id
    from
      user_profile
    where
      university = '浙江大学'
  );

链接查询

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

select
  university,
  # 表中没有的属性 ， 直接用函数算 
  # 答题总数 / 答题人数 
  count(quest.question_id) / count(distinct(quest.device_id)) as avg_answer_cnt
from
  user_profile as user,
  question_practice_detail as quest
where
  user.device_id = quest.device_id
group by
  user.university

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

select
  user.university,
  detail.difficult_level,
  count(quest.question_id) / count(distinct quest.device_id) as avg_answer_cnt
from
  user_profile as user,
  question_practice_detail as quest,
  question_detail as detail
where
  user.device_id = quest.device_id
  and quest.question_id = detail.question_id
group by
  # 多属性分组
  user.university,
  detail.difficult_level;

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

select
  university,
  detail.difficult_level,
  # 题目数 / 设备数 (设备数就是人数)
  count(detail.question_id) / count(distinct quest.device_id) as avg_answer_cnt
from
  user_profile as user,
  question_practice_detail as quest,
  question_detail as detail
where
  user.device_id = quest.device_id
  and quest.question_id = detail.question_id
  and user.university = '山东大学'
group by
  detail.difficult_level

组合查询

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

select
  device_id,
  gender,
  age,
  gpa
from
  user_profile
where
  university = '山东大学'
# union 去重 (or也去重)
# union all 不去重
union all
select
  device_id,
  gender,
  age,
  gpa
from
  user_profile
where
  gender = 'male'

05 必会的常用函数

条件函数

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

# 条件函数 if 语句
select
  if(
    age < 25
    or age is null,
    "25岁以下",
    "25岁及以上"
  ) as age_cnt,
  count(id) as number
from
  user_profile
group by
  age_cnt;
  
#######################
# 条件判断case when
select
  case
    when age >= 25 then "25岁及以上"
    else "25岁以下"
  end as age_cnt,
  count(*) as number
from
  user_profile
group by
  age_cnt;
  
#######################
# union 联合
select
  '25岁以下' as age_cnt,
  count(id) as number
from
  user_profile
where
  age < 25
  or age is null
union
select
  '25岁及以上' as age_cnt,
  count(id) as number
from
  user_profile
where
  age >= 25;

SQL27 查看不同年龄段的用户明细

select
  device_id,
  gender,
  case
    when age < 20 then "20岁以下"
    when age >= 20
    and age <= 24 then "20-24岁"
    when age >= 25 then "25岁及以上"
    else "其他"
  end as age_cut
from
  user_profile;

日期函数

SQL28 计算用户8月每天的练题数量

select
  day(date) as day,
  count(id) as question_cnt
from
  question_practice_detail
group by
  date
having
  month(date) = 08;

SQL29 计算用户的平均次日留存率

select
  # 算的是比例 ， 合格的 / 总量 
  # 将设备号于时间绑定来计数 
  # pre 总量 前一天 
  # nex 合格 后一天
  count(distinct nex.device_id, nex.date) / count(distinct pre.device_id, pre.date) as avg_ret
from
  question_practice_detail as pre
# 因为是同一张表，用where无效
left join
  question_practice_detail as nex
on
  pre.device_id = nex.device_id 
  # 后一天和前一天的时间差是1
  and nex.date = date_add(pre.date, interval 1 day);
  # and datediff(q2.date, q1.date) = 1

文本函数

SQL30 统计每种性别的人数

select
  # substring_index(分割的串， 分割符， 取第几个)
  # 范围[1, n]，-1表示最后一个 
  substring_index(profile, ',', -1) as gender,
  count(device_id) as number
from
  user_submit
group by
  gender;

SQL31 提取博客URL中的用户名

select
  device_id,
  # 根据分割字符分割，第三个参数可以正负，下标从1开始
  substring_index(blog_url, '/', -1) as user_name
from
  user_submit;
  
######################################
select
  device_id,
  # substr(s, idx, len) 从第idx位开始截取len
  # 没有len则表示全截取
  # [1, n]
  substr(blog_url, 11) as user_name
from
  user_submit;
  
#####################################
select
  device_id,
  # 替换函数，直接换位空串
  replace(blog_url, 'http:/url/', '') as user_name
from
  user_submit;

窗口函数

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

select
  device_id,
  university,
  gpa
from
  user_profile
where
  # 多个元素 in
  (university, gpa) in (
    select
      university,
      min(gpa)
    from
      user_profile
    group by
      university
  )
order by
  university;

06 综合练习

综合练习

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

select
  # 这里必须是user的id ， 以左侧为主 
  user.device_id,
  university,
  # sum + if 进行计数 
  sum(if(result is not null, 1, 0)) as question_cnt,
  # count + if 第三个参数得是null 
  count(if(result = "right", 1, null)) as right_question_cnt
from
  user_profile as user
  left join question_practice_detail as quest on user.device_id = quest.device_id
  and month(quest.date) = '08'
where
  university = '复旦大学'
group by
  # 这里可以不指明 
  device_id;

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

select
  detail.difficult_level,
  count(if(quest.result = 'right', 1, null)) / count(quest.id) as correct_rate
from
  user_profile as user,
  question_practice_detail as quest,
  question_detail as detail
where
  user.device_id = quest.device_id
  and quest.question_id = detail.question_id
  and user.university = '浙江大学'
group by
  detail.difficult_level
order by
  correct_rate ASC;

SQL36 查找后排序

select
  device_id,
  age
from
  user_profile
order by
  age;

---

---

---

END