基础查询——查询结果去重

答案：

SELECTDISTINCTuniversityFROMuser_profile

SELECTuniversityFROMuser_profileGROUPBYuniversity

条件查询——查找学校是北大的学生信息

答案

selectdevice_id,universityfromuser_profilewhereuniversity="北京大学"

条件查询——查找除复旦大学的用户信息

selectdevice_id,gender,age,universityfromuser_profilewhereuniversity<>"复旦大学"# where university != '复旦大学'# where not university = '复旦大学'# where university not in('复旦大学')# where university not like '复旦大学'

条件查询——Where in 和Not in

SELECTdevice_id,gender,age,university,gpaFROMuser_profileWHEREuniversityIN("北京大学","复旦大学","山东大学")

条件查询——操作符混合运用

selectdevice_id,gender,age,university,gpafromuser_profilewhere(gpa>3.5anduniversity="山东大学")or(gpa>3.8anduniversity="复旦大学")orderbydevice_idasc

条件查询——查看学校名称中含北京的用户 like

_：匹配任意一个字符；SELECT*FROM学生表WHEREnameLIKE'张__'//查询姓“张”且名字是3个字的学生姓名。%：匹配0个或多个字符；SELECT*FROM学生表WHERE姓名LIKE‘张%’//查询学生表中姓‘张’的学生的详细信息。[]：匹配[]中的任意一个字符(若要比较的字符是连续的，则可以用连字符“-”表 达)；SELECT*FROM学生表WHERE姓名LIKE'[张李刘]%’//查询学生表中姓‘张’、姓‘李’和姓‘刘’的学生的情况。 [^ ]：不匹配[ ]中的任意一个字符。 SELECT * FROM 学生表 WHERE 学号 LIKE '%[^235]'//从学生表表中查询学号的最后一位不是2、3、5的学生信息

高级查询——计算函数

答案：

selectcount(gender)asmale_num,round(avg(gpa),1)asavg_gpafromuser_profilewheregender="male"注：使用round（column,1）函数进行平均数的四舍五入，保留1位小数

高级查询——分组查询：分组计算练习题

SELECTgender,university,COUNT(*)ASuser_num,ROUND(AVG(active_days_within_30),1)ASavg_active_day,ROUND(AVG(question_cnt),1)ASavg_question_cntFROMuser_profileGROUPBYgender,universityORDERBYgenderASC,universityASC

高级查询——分组查询：分组过滤练习题

selectuniversity,round(avg(question_cnt),3)asavg_question_cnt,round(avg(answer_cnt),3)asavg_answer_cntfromuser_profilegroupbyuniversityhavingavg_question_cnt<5oravg_answer_cnt<20

多表查询——子查询

selectdevice_id,question_id,resultfromquestion_practice_detailwheredevice_id=(selectdevice_idfromuser_profilewhereuniversity="浙江大学")

多表查询——链接查询

selectuniversity,(count(question_id)/count(distinct(q.device_id)))asavg_answer_cntfromuser_profile ujoinquestion_practice_detail qonu.device_id=q.device_idgroupbyuniversity

多表查询——链接查询

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

selectuniversity,difficult_level,(count(q.question_id)/count(distinct(q.device_id)))ASavg_answer_cntfromuser_profile ujoinquestion_practice_detail qonu.device_id=q.device_idjoinquestion_detail qdonqd.question_id=q.question_idgroupbyuniversity,difficult_level

多表查询——链接查询

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

selectu.university,p.difficult_level,count(q.question_id)/count(distinctq.device_id)asavg_answer_cntfromuser_profile ujoinquestion_practice_detail qonu.device_id=q.device_idjoinquestion_detail ponq.question_id=p.question_idwhereu.university="山东大学"groupbyp.difficult_level

多表查询——组合查询

不去重表示：只要满足一个条件就被筛选出来，但总会存在一个人满足了两个条件只筛选一次。这里的坑时使用or，因为or自带去重，而union等价于or，但union all 可以不去重，所以本体考察or与union的细节使用。

selectdevice_id,gender,age,gpafromuser_profilewhereuniversity="山东大学"unionallselectdevice_id,gender,age,gpafromuser_profilewheregender="male"

必会的常用函数——条件函数

select'25岁以下'asage_cut,count(device_id)asnumberfromuser_profilewhereage<25orageisnullunionallselect'25岁及以上'asage_cut,count(device_id)asnumberfromuser_profilewhereage>=25

必会的常用函数——条件函数

使用case when

selectdevice_id,gender,casewhenage>=25then'25岁及以上'whenagebetween20and24then'20-24岁'whenage<20then'20岁以下'else'其他'endasage_cutfromuser_profile

使用if

SELECTdevice_id,gender,IF(age>=25,'25岁及以上',IF(ageBETWEEN20AND24,'20-24岁',IF(age<20,'20岁以下','其他')))FROMuser_profile;

必会的常用函数——日期函数
4种得到20221年8月和日的方法

--法一：like运算符selectday(date)asday,count(question_id)asquestion_cntfromquestion_practice_detailwheredatelike'2021-08%'groupbyday(date);--法二：regexp运算符selectday(date)asday,count(question_id)asquestion_cntfromquestion_practice_detailwheredateregexp'2021-08'groupbyday(date);--法三：substring提取日期selectday(date)asday,count(question_id)asquestion_cntfromquestion_practice_detailwheresubstring(date,1,7)='2021-08'groupbyday(date);--法四 使用year、month、dayselectday(date)asday,count(question_id)asquestion_cntfromquestion_practice_detailwhereyear(date)=2021andmonth(date)=08groupbyday

必会的常用函数——日期函数
计算用户的平均次日留存率

思路是：

1. 需要知道两天都上线的人数
2. 需要知道第一天上线的人数
   做法：
3. 用datediff区分第一天和第二天在线的device_id
4. 用left outer join做自表联结
5. 用distinct q2.device_id,q2.date做双重去重，找到符合条件的当天在线人数

selectcount(distinctq2.device_id,q2.date)/count(distinctq1.device_id,q1.date)asavg_retfromquestion_practice_detailasq1leftjoinquestion_practice_detailasq2onq1.device_id=q2.device_idanddatediff(q2.date,q1.date)=1

必会的常用函数——文本函数：统计每种性别的人数

要用到substring_index()这个函数的用法
substring_index(str,delim,count)

str:要处理的字符串 delim:分隔符 count:计数

例子：str=www.wikibt.com

substring_index(str,'.',1) 结果是：www substring_index(str,'.',2) 结果是：www.wikibt 如果count是正数，那么就是从左往右数，第N个分隔符的左边的所有内容 如果count是负数，那么就是从右往左数，第N个分隔符的右边的所有内容 substring_index(str,'.',-2) 结果为：wikibt.com 有人会问，如果我要中间的的wikibt怎么办？ 很简单的，两个方向： 从右数第二个分隔符的右边全部，再从左数的第一个分隔符的左边：

substring_index(substring_index(str,‘.’,-2),‘.’,1);

selectsubstring_index(profile,',',-1)asgender,count(device_id)fromuser_submitgroupbygender

必会的常用函数——文本函数：截取出年龄

易错点：SUBSTRING_INDEX(profile,‘,’,-2)只是27，male，所以还得再套一个

selectsubstring_index(substring_index(profile,',',-2),',',1)asage,count(device_id)asnumberfromuser_submitgroupbyage

必会的常用函数——窗口函数
找出每个学校GPA最低的同学

selectdevice_id,university,gpafromuser_profile uwheregpa=(selectmin(gpa)fromuser_profilewhereuniversity=u.university)orderbyuniversity

窗口函数的含义为先分组再排序, row_number() over (partition by col1 order by
col2),表示根据col1分组，在分组内部根据col2排序。

Selectdevice_id,university,gpaFrom(Selectdevice_id,university,gpa,row_number()over(partitionbyuniversityorderbygpa)asrkFromuser_profile)awhererk=1