考点1：in的用法
题目：现在运营想要找到学校为北大、复旦和山大的同学进行调研，请你取出相关数据。
select device_id,gender,age,university,gpa from user_profile where university in ("北京大学","复旦大学","山东大学")
考点2：or的用法
题目：现在运营想要找到学校为北大或GPA在3.7以上(不包括3.7)的用户进行调研，请你取出相关数据（使用OR实现）
考点3：不为空
题目：现在运营想要对用户的年龄分布开展分析，在分析时想要剔除没有获取到年龄的用户，请你取出所有年龄值不为空的用户的设备ID，性别，年龄，学校的信息。
select device_id,gender,age,university from user_profile where age !="" # 第二种age is not null
考点4：不包含某一项
题目：现在运营想要查看除复旦大学以外的所有用户明细，请你取出相应数据
select device_id,gender,age,university from user_profile #where university !="复旦大学" #where university not like "复旦大学" where university not in ("复旦大学")
考点5：and和or
题目：现在运营想要找到gpa在3.5以上(不包括3.5)的山东大学用户 或 gpa在3.8以上(不包括3.8)的复旦大学同学进行用户调研，请你取出相应数据
select device_id,gender,age,university,gpa from user_profile where (gpa>3.5 and university="山东大学") or (gpa>3.8 and university="复旦大学")
考点6：like模糊语句
题目：现在运营想查看所有大学中带有北京的用户的信息，请你取出相应数据。
select device_id,age,university from user_profile where university like ("%北京%") #包含北京的 university like ("%北京%") #以北京开头的 university like ("北京%") # 以北京结尾的("%北京")
考点7：distinct去重唯一
题目：现在运营需要查看用户来自于哪些学校，请从用户信息表中取出学校的去重数据。
select distinct university from user_profile
考点8：最大值和降序（取第一行数据）
题目：运营想要知道复旦大学学生gpa最高值是多少，请你取出相应数据
法1：通过降序，然后取第一条
select gpa from user_profile where university = "复旦大学" order by gpa DESC limit 1
法2：使用聚合函数取最大值
select max(gpa) from user_profile where university = "复旦大学"
考点9：count()计数、avg()平均数和round()保留小数
题目：现在运营想要看一下男性用户有多少人以及他们的平均gpa是多少，用以辅助设计相关活动，请你取出相应数据。
select count(gender) as male_nuum,round(avg(gpa),1) from user_profile where gender = 'male'
考点10：group by分组
题目：现在运营想要对每个学校不同性别的用户活跃情况和发帖数量进行分析，请分别计算出每个学校每种性别的用户数、30天内平均活跃天数和平均发帖数量。
30天内活跃天数字段（active_days_within_30）
发帖数量字段（question_cnt）
回答数量字段（answer_cnt）
第一行表示:id为1的用户的常用信息为使用的设备id为2138，性别为男，年龄21岁，北京大学，gpa为3.4在过去的30天里面活跃了7天，发帖数量为2，回答数量为12
说明：每个设备ID就相当于一个用户
select gender,university, count(device_id) as user_num, avg(active_days_within_30) as avg_within, avg(question_cnt) as avg_quest from user_profile group by gender,university
考点11：having过滤
题目：现在运营想查看每个学校用户的平均发贴和回帖情况，寻找低活跃度学校进行重点运营，请取出平均发贴数低于5的学校或平均回帖数小于20的学校。
select university, avg(question_cnt) as avg_quest, avg(answer_cnt) as avg_answer from user_profile group by university having avg_quest < 5 or avg_answer <20
注意：
- 按学校进行分组（每个学校）
avg()聚合函数结果作为筛选条件时，不能用where，而是用having语法。
考点12：order by排序（默认升序asc,降序的desc）
题目：现在运营想要查看不同大学的用户平均发帖情况，并期望结果按照平均发帖情况进行升序排列，请你取出相应数据。
题干解析：
不同大学：按学校分组group by university
平均发帖：聚合函数avg(question_cnt)
平均发帖情况升序排列：order by avg_question_cnt
select university, avg(question_cnt) as avg_question_cnt from user_profile group by university order by avg_question_cnt asc
考点13：多表联查
题目：现在运营想要查看所有来自浙江大学的用户题目回答明细情况，请你取出相应数据
示例：user_profile
根据示例，你的查询应返回以下结果，查询结果根据question_id升序排序：
方法1：连接查询
select q.device_id,question_id,result from question_practice_detail q,user_profile u where q.device_id = u.device_id and u.university = "浙江大学" order by q.question_id
方法2：子查询
select device_id,question_id,result from question_practice_detail where device_id = ( select device_id from user_profile where university = "浙江大学" ) order by question_id
方法3：内连接查询
select q.device_id,question_id,result from question_practice_detail q inner join user_profile u on q.device_id = u.device_id and university = "浙江大学" order by question_id
考点14：求平均值
题目：运营想要了解每个学校答过题的用户平均答题数量情况，请你取出数据。
用户信息表 user_profile
device_id指终端编号（认为每个用户有唯一的一个终端），
gender指性别，
age指年龄，
university指用户所在的学校，
gpa是该用户平均学分绩点，
active_days_within_30是30天内的活跃天数。
答题情况明细表 question_practice_detail
- question_id是题目编号，
- result是答题结果
输出结果
题干解析
- 按学校分组
- 平均答题数量：在每个学校的分组内，用总答题数量除以总人数即可得到平均答题数量
count(question_id) / count(distinct device_id)（一个用户可以答多题，需要去重） - 学校和答题信息在不同的表，需要做连接
方法1：连接查询
select u.university, count(q.question_id)/count(distinct(q.device_id)) as avg_answer_num from user_profile u,question_practice_detail q where u.device_id = q.device_id group by u.university
方法2：内连接查询
select u.university, count(q.question_id)/count(distinct(q.device_id)) as avg_answer_num from user_profile u join question_practice_detail q on u.device_id = q.device_id group by university
考点15：三表联查
题目：运营想要计算一些参加了答题的不同学校、不同难度的用户平均答题量，请你写SQL取出相应数据。
用户信息表：user_profile
题库练习明细表：question_practice_detail
题库详情表：question_detail
输出结果
题干解析：
- 不同学校：按学校分组
group by university - 不同难度：按难度分组
group by difficult_level 用户平均答题数:总题数/用户数（一个用户可以答多题，要去重distinct）count(qpd.question_id)/count(distinct qpd.device_id)
方法1
select university, difficult_level, count(qpd.question_id)/count(distinct qpd.device_id) as avg_answer_cnt from user_profile u, question_detail qd, question_practice_detail qpd where u.device_id = qpd.device_id and qd.question_id = qpd.question_id group by university,difficult_level
方法2
select university, difficult_level, count(qpd.question_id)/count(distinct qpd.device_id) as avg_answer_cnt from user_profile u join question_practice_detail qpd on u.device_id = qpd.device_id join question_detail qd on qd.question_id = qpd.question_id group by university,difficult_level
考点16：平均值、分组、限定条件、三表联查
题目：运营想要查看参加了答题的山东大学的用户在不同难度下的平均答题题目数，请取出相应数据
用户信息表：user_profile
题库练习明细表：question_practice_detail
题库详情表：question_detail
输出结果
题干解析
- 限定条件：山东大学的用户
university = "山东大学" 不同难度：按难度分组group by difficult_level平均答题数：答题总数/答题总人数count(qpd.question_id)/count(distinct qpd.device_id)
select university, difficult_level, count(qpd.question_id)/count(distinct qpd.device_id) as avg_answer_cnt from user_profile up, question_detail qd, question_practice_detail qpd where university = "山东大学" and up.device_id = qpd.device_id and qd.question_id = qpd.question_id group by difficult_level
考点17：union特性(去重不去重)
| union | 得到两个查询结果的并集 | 自动去掉重复行 | 不会排序 |
| union all | 不会去掉重复行 | ||
| intersect | 得到两个查询结果的交集 | 按照结果集的第一个列进行排序 | |
| minus | 得到两个查询结果的减集 |
题目：现在运营想要分别查看学校为山东大学或者性别为男性的用户的device_id、gender、age和gpa数据，请取出相应结果，结果不去重。
题干解析：
- 限定条件：学校为山东大学
university = "山东大学";性别为男性gender = "male"- 分别查看和结果不去重：不能用
or了，用union all
表user_profile
输出结果
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"
