SQL练习题-基础查询、条件查询、高级查询、多表查询、常用函数练习题集合

基础查询——查询结果去重答案SELECTDISTINCTuniversityFROMuser_profileSELECTuniversityFROMuser_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 inSELECTdevice_id,gender,age,university,gpaFROMuser_profileWHEREuniversityIN(北京大学,复旦大学,山东大学)条件查询——操作符混合运用selectdevice_id,gender,age,university,gpafromuser_profilewhere(gpa3.5anduniversity山东大学)or(gpa3.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_profilewheregendermale注使用roundcolumn,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_cnt5oravg_answer_cnt20多表查询——子查询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_idq.device_idgroupbyuniversity多表查询——链接查询统计每个学校各难度的用户平均刷题数selectuniversity,difficult_level,(count(q.question_id)/count(distinct(q.device_id)))ASavg_answer_cntfromuser_profile ujoinquestion_practice_detail qonu.device_idq.device_idjoinquestion_detail qdonqd.question_idq.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_idq.device_idjoinquestion_detail ponq.question_idp.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_profilewheregendermale必会的常用函数——条件函数select25岁以下asage_cut,count(device_id)asnumberfromuser_profilewhereage25orageisnullunionallselect25岁及以上asage_cut,count(device_id)asnumberfromuser_profilewhereage25必会的常用函数——条件函数使用case whenselectdevice_id,gender,casewhenage25then25岁及以上whenagebetween20and24then20-24岁whenage20then20岁以下else其他endasage_cutfromuser_profile使用ifSELECTdevice_id,gender,IF(age25,25岁及以上,IF(ageBETWEEN20AND24,20-24岁,IF(age20,20岁以下,其他)))FROMuser_profile;必会的常用函数——日期函数4种得到20221年8月和日的方法--法一like运算符selectday(date)asday,count(question_id)asquestion_cntfromquestion_practice_detailwheredatelike2021-08%groupbyday(date);--法二regexp运算符selectday(date)asday,count(question_id)asquestion_cntfromquestion_practice_detailwheredateregexp2021-08groupbyday(date);--法三substring提取日期selectday(date)asday,count(question_id)asquestion_cntfromquestion_practice_detailwheresubstring(date,1,7)2021-08groupbyday(date);--法四 使用year、month、dayselectday(date)asday,count(question_id)asquestion_cntfromquestion_practice_detailwhereyear(date)2021andmonth(date)08groupbyday必会的常用函数——日期函数计算用户的平均次日留存率思路是需要知道两天都上线的人数需要知道第一天上线的人数做法用datediff区分第一天和第二天在线的device_id用left outer join做自表联结用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_idq2.device_idanddatediff(q2.date,q1.date)1必会的常用函数——文本函数统计每种性别的人数要用到substring_index()这个函数的用法substring_index(str,delim,count)str:要处理的字符串 delim:分隔符 count:计数例子strwww.wikibt.comsubstring_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)只是27male所以还得再套一个selectsubstring_index(substring_index(profile,,,-2),,,1)asage,count(device_id)asnumberfromuser_submitgroupbyage必会的常用函数——窗口函数找出每个学校GPA最低的同学selectdevice_id,university,gpafromuser_profile uwheregpa(selectmin(gpa)fromuser_profilewhereuniversityu.university)orderbyuniversity窗口函数的含义为先分组再排序, row_number() over (partition by col1 order bycol2),表示根据col1分组在分组内部根据col2排序。Selectdevice_id,university,gpaFrom(Selectdevice_id,university,gpa,row_number()over(partitionbyuniversityorderbygpa)asrkFromuser_profile)awhererk1