题1

用户信息表：user_profile

查询结果案例：

题目：作为牛客网的数据分析师，现在运营想查看每个学校用户的平均发贴和回帖情况，寻找低活跃度学校进行重点运营，请取出平均发贴数低于5的学校或平均回帖数小于20的学校。

解析：

限定条件：平均发贴数低于5或平均回帖数小于20的学校，avg(question_cnt)<5 or avg(answer_cnt)<20，聚合函数结果作为筛选条件时，不能用where，而是用having语法，配合重命名即可；
按学校输出：需要对每个学校统计其平均发贴数和平均回帖数，因此group by university
表头重命名：as

sql：

select

           university,AVG(question_cnt) as avg_question_cnt,

           AVG(answer_cnt) as avg_answer_cnt

from user_profile

GROUP BY university

HAVING avg_question_cnt<5 or avg_answer_cnt<20;

Round(AVG(gpa),1)

解释：Round函数是保留几位小数，所以这个意思就是gpa的平均值保留1位小数

distanct 唯一

MAX(age)

解释：MAX聚合函数，age的最大值

IN

in常用于where表达式中，其作用是查询某个范围内的数据

找到学校为北大、复旦和山大的同学

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

NOT IN

//除字段以外的

is NOT NULL

//不为空

BETWEEN .. AND..

范围限定，例如 where age BETWEEN 20 and 23;//age在20~23的

limit（偏移量，个数）

//注意偏移量不包括在内，比如limit （1,3），查找第2,3,4条数据

Order by desc 降序排列

ASC升序

题2

用户信息表：user_profile

题库练习明细表：question_practice_detail

表：question_detail

根据示例，你的查询应返回以下结果：

计算每个学校用户不同难度下的用户平均答题题目数情况，请取出相应数据

解析：

每个学校：按学校分组group by university
不同难度：按难度分组group by difficult_level
平均答题数：总答题数除以总人数count(qpd.question_id) / count(distinct qpd.device_id)
来自上面信息三个表，需要联表，up与qpd用device_id连接，qd与qpd用question_id连接。
表头重命名：as

sql：

SELECT 
      university,difficult_level,
      count(qpd.question_id)/count(distinct qpd.device_id) as avg_answer_cnt
from question_practice_detail qpd 
inner join question_detail qd
on qd.question_id=qpd.question_id
inner join user_profile as up
on up.device_id=qpd.device_id
group by university,qd.difficult_level;

联合查询

UNION 去重
UNION ALL 不去重

例子：

分别查看学校为山东大学或者性别为男性的用户的device_id、gender、age和gpa数据，请取出相应结果，结果不去重。

这里需要把山东大学和男的分别筛选出来再用UNION ALL黏在一起 UNION默认去重所以这里用UNION ALL

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';

条件函数 IF

if（x=n,a,b）表示如果x=n,则返回a，否则就是b了。

user_profile

根据示例，你的查询应返回以下结果：

题目：将用户划分为25岁以下和25岁及以上两个年龄段，分别查看这两个年龄段用户数量

解析：

年龄划分为两段：if(age>=25, "25岁以及上", "25岁以下")
统计用户数量：count，每个段分别统计，用group by age_cut分组
当然本题也可以用union all解决，不过用if更直观
表头重命名
注意题目描述的示例和testcase结果不一致！！
如果采用unionall解决，针对年龄为空的记录，需要明确是划分到哪边
不同段的释义要根据testcase调整（以及上 not 及以上）

sql：

select 
if(age>=25,'25岁及以上','25岁以下') as age_cut,
count(device_id) as Number
from user_profile
group by age_cut;

case when(多值判断)..then..else..end

例子：将用户划分为20岁以下，20-24岁，25岁及以上三个年龄段，分别查看不同年龄段用户的明细情况

user_profile

根据示例，你的查询应返回以下结果：

sql：

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

day，mongth，year关键字

day(yy-mm-dd格式参数)//会转化成day

mongth(yy-mm-dd格式参数)//会转化成month

year(yy-mm-dd格式参数)//会转化成year

使用mysql时一定要注意，尽量不要使用它的保留字作为表名或者列名，否则会出现莫名其妙的错误。如果之前用了，在sql语句中就用``包括起来。

模糊查询

like ' % '

例子：查找以张开头的人 select ...where name like '张%'

查找中间包含x的字段 select ...where name like '%x%'

查找以m结尾的字段 select ...where name like '%m'

sum函数和count函数

1.sum

（1）sum()函数里面的参数是列名的时候，是计算列名的值的相加，而不是有值项的总数。
（2）sum(条件表达式)，如果记录满足条件表达式就加1，统计满足条件的行数

例如：sum(if month(date)=8,1,0)//如果date是8月份就加一

没有group by，就只能有一条，sum得到的是总数

有group by，就分组了，sum得到的是分组后，每一个组的总数

2.count

（1）COUNT()函数里面的参数是列名的的时候,那么会计算有值项的次数。（NULL 不计入，但是' '值计入）
（2）COUNT(*)可以计算出行数，包括null
（3）COUNT（1）也可以计算出行数，1在这里代表一行
（4）COUNT(column)对特定的列的值具有的行数进行计算，不包含NULL值
（5）COUNT(条件表达式)，不管记录是否满足条件表达式，只要非NULL就加1

having的用法

语法：

group by 字段 having 条件判断;

SELECT dept,COUNT(user_name) count_tmp

FROM ec_uses GROUP BY dept HAVING count_tmp>1;

所以having子句对我们筛选分组后的数据非常方便

注意：having后面跟的条件判断的字段必须是聚合函数返回的结果，否则sql会报错

连表查询

内连接inner join on(只写join on 也是内连接)

左连接left join on

右连接right join on

因为sql很久没练习了，很多都陌生了，今日从入门开始做，一切豁然开朗

Mysql一些关键字解释

Round(AVG(gpa),1)

MAX(age)

IN

NOT IN

is NOT NULL

BETWEEN .. AND..

limit（偏移量，个数）

Order by desc 降序排列

ASC升序

联合查询

条件函数 IF

case when(多值判断)..then..else..end

sql：

day，mongth，year关键字

模糊查询

like ' % '

sum函数和count函数

1.sum

2.count

having的用法

连表查询