文章目录

• 前言
• AC代码
• • 01 基础查询
  • • 基础查询
    • • SQL1 查询所有列
      • SQL2 查询多列
    • 简单处理查询结果
    • • SQL3 查询结果去重
      • SQL4 查询结果限制返回行数
      • SQL5 将查询后的列重新命名
  • 02 条件查询
  • • 基础排序
    • • SQL36 查找后排序
      • SQL37 查找后多列排序
      • SQL38 查找后降序排列
    • 基础操作符
    • • SQL6 查找学校是北大的学生信息
      • SQL7 查找年龄大于24岁的用户信息
      • SQL8 查找某个年龄段的用户信息
      • SQL9 查找除复旦大学的用户信息
      • SQL10 用where过滤空值练习
    • 高级操作符
    • • SQL11 高级操作符练习(1)
      • SQL12 高级操作符练习(2）
      • SQL13 Where in 和Not in
      • SQL14 操作符混合运用
      • SQL15 查看学校名称中含北京的用户
  • 03 高级查询
  • • 计算函数
    • • SQL16 查找GPA最高值
      • SQL17 计算男生人数以及平均GPA
    • 分组查询
    • • SQL18 分组计算练习题
      • SQL19 分组过滤练习题
      • SQL20 分组排序练习题
  • 04 多表查询
  • • 子查询
    • • SQL21 浙江大学用户题目回答情况
    • 链接查询
    • • SQL22 统计每个学校的答过题的用户的平均答题数
      • SQL23 统计每个学校各难度的用户平均刷题数
      • SQL24 统计每个用户的平均刷题数
    • 组合查询
    • • SQL25 查找山东大学或者性别为男生的信息
  • 05 必会的常用函数
  • • 条件函数
    • • SQL26 计算25岁以上和以下的用户数量
      • SQL27 查看不同年龄段的用户明细
    • 日期函数
    • • SQL28 计算用户8月每天的练题数量
      • SQL29 计算用户的平均次日留存率
    • 文本函数
    • • SQL30 统计每种性别的人数
      • SQL31 提取博客URL中的用户名
    • 窗口函数
    • • SQL33 找出每个学校GPA最低的同学
  • 06 综合练习
  • • 综合练习
    • • SQL34 统计复旦用户8月练题情况
      • SQL35 浙大不同难度题目的正确率
      • SQL36 查找后排序
• END

前言

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

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

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

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

AC代码

01 基础查询

基础查询

SQL1 查询所有列

select*
fromuser_profile;

SQL2 查询多列

selectdevice_id,gender,age,university
fromuser_profile;

简单处理查询结果

SQL3 查询结果去重

selectdistinct university
fromuser_profile;

SQL4 查询结果限制返回行数

selectdevice_id
fromuser_profile
limit0, 2;

SQL5 将查询后的列重新命名

selectdevice_id as user_infos_example
fromuser_profile
limit0, 2;

02 条件查询

基础排序

SQL36 查找后排序

selectdevice_id,age
fromuser_profile
order byage;

SQL37 查找后多列排序

selectdevice_id,gpa,age
fromuser_profile
order bygpa,age;

SQL38 查找后降序排列

selectdevice_id,gpa,age
fromuser_profile
order bygpa desc,age desc;

基础操作符

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

selectdevice_id,university
fromuser_profile
whereuniversity = '北京大学';

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

selectdevice_id,gender,age,university
fromuser_profile
whereage > 24;

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

selectdevice_id,gender,age
fromuser_profile
whereage between 20 and 23;

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

selectdevice_id,gender,age,university
fromuser_profile
whereuniversity ! = '复旦大学';

SQL10 用where过滤空值练习

selectdevice_id,gender,age,university
fromuser_profile
where# age is not null# 直接写age也行age;

高级操作符

SQL11 高级操作符练习(1)

selectdevice_id,gender,age,university,gpa
fromuser_profile
wheregender = 'male'and gpa > 3.5;

SQL12 高级操作符练习(2）

selectdevice_id,gender,age,university,gpa
fromuser_profile
whereuniversity = '北京大学'or gpa > 3.7;

SQL13 Where in 和Not in

selectdevice_id,gender,age,university,gpa
fromuser_profile
whereuniversity in ('北京大学', '复旦大学', '山东大学');

SQL14 操作符混合运用

selectdevice_id,gender,age,university,gpa
fromuser_profile
where(gpa > 3.5and university = '山东大学')or (gpa > 3.8and university = '复旦大学');

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

selectdevice_id,age,university
fromuser_profile
whereuniversity like '%北京%';

03 高级查询

计算函数

SQL16 查找GPA最高值

selectmax(gpa)
fromuser_profile
whereuniversity = '复旦大学';#########################
selectgpa
fromuser_profile
whereuniversity = '复旦大学'
order bygpa desc
limit1;

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

selectcount(*) as male_num,# 保留一位小数round(avg(gpa), 1) as avg_gpa
fromuser_profile
wheregender = 'male';

分组查询

SQL18 分组计算练习题

selectgender,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
fromuser_profile
group by# 两种属性分组university,gender;

SQL19 分组过滤练习题

selectuniversity,round(avg(question_cnt), 3) as avg_question_cnt,round(avg(answer_cnt), 3) as avg_answer_cnt
fromuser_profile
group byuniversity # 聚集函数不能用where要用having
havingavg_question_cnt < 5or avg_answer_cnt < 20;# avg(question_cnt) < 5# or avg(answer_cnt) < 20;

SQL20 分组排序练习题

selectuniversity,round(avg(question_cnt), 4) as avg_question_cnt
fromuser_profile
group byuniversity
order byavg_question_cnt

04 多表查询

子查询

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

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

链接查询

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

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

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

selectuser.university,detail.difficult_level,count(quest.question_id) / count(distinct quest.device_id) as avg_answer_cnt
fromuser_profile as user,question_practice_detail as quest,question_detail as detail
whereuser.device_id = quest.device_idand quest.question_id = detail.question_id
group by# 多属性分组user.university,detail.difficult_level;

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

selectuniversity,detail.difficult_level,# 题目数 / 设备数 (设备数就是人数)count(detail.question_id) / count(distinct quest.device_id) as avg_answer_cnt
fromuser_profile as user,question_practice_detail as quest,question_detail as detail
whereuser.device_id = quest.device_idand quest.question_id = detail.question_idand user.university = '山东大学'
group bydetail.difficult_level

组合查询

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

selectdevice_id,gender,age,gpa
fromuser_profile
whereuniversity = '山东大学'
# union 去重 (or也去重)
# union all 不去重
union all
selectdevice_id,gender,age,gpa
fromuser_profile
wheregender = 'male'

05 必会的常用函数

条件函数

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

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

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

selectdevice_id,gender,casewhen age < 20 then "20岁以下"when age >= 20and age <= 24 then "20-24岁"when age >= 25 then "25岁及以上"else "其他"end as age_cut
fromuser_profile;

日期函数

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

selectday(date) as day,count(id) as question_cnt
fromquestion_practice_detail
group bydate
havingmonth(date) = 08;

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

select# 算的是比例 ， 合格的 / 总量 # 将设备号于时间绑定来计数 # pre 总量 前一天 # nex 合格 后一天count(distinct nex.device_id, nex.date) / count(distinct pre.device_id, pre.date) as avg_ret
fromquestion_practice_detail as pre
# 因为是同一张表，用where无效
left joinquestion_practice_detail as nex
onpre.device_id = nex.device_id # 后一天和前一天的时间差是1and 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
fromuser_submit
group bygender;

SQL31 提取博客URL中的用户名

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

窗口函数

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

selectdevice_id,university,gpa
fromuser_profile
where# 多个元素 in(university, gpa) in (selectuniversity,min(gpa)fromuser_profilegroup byuniversity)
order byuniversity;

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
fromuser_profile as userleft join question_practice_detail as quest on user.device_id = quest.device_idand month(quest.date) = '08'
whereuniversity = '复旦大学'
group by# 这里可以不指明 device_id;

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

selectdetail.difficult_level,count(if(quest.result = 'right', 1, null)) / count(quest.id) as correct_rate
fromuser_profile as user,question_practice_detail as quest,question_detail as detail
whereuser.device_id = quest.device_idand quest.question_id = detail.question_idand user.university = '浙江大学'
group bydetail.difficult_level
order bycorrect_rate ASC;

SQL36 查找后排序

selectdevice_id,age
fromuser_profile
order byage;

END