SQL语句执行顺序 ：
FROM - ON - JOIN - WHERE - GROUP BY - WITH - HAVING - SELECT - DISTINCT - ORDER BY - LIMIT
1）mysql子查询
select device_id,question_id,result from question_practice_detail
where device_id in (select device_id
from user_profile
where university='浙江大学')
2）内连接查询
select q.device_id,question_id,result
from question_practice_detail q
inner join user_profile u
on q.device_id=u.device_id
where university='浙江大学'
3）连接查询
select question_practice_detail.device_id,question_id,result
from question_practice_detail,user_profile
where question_practice_detail.device_id = user_profile.device_id
and university='浙江大学'
4）左外连接查询
select q.device_id,q.question_id,q_result
from question_practice_detail q
left join user_profile u
on q.device_id = u.device_id
where u.university='浙江大学'
5）sql计数查询
select count(gender) as male_num,avg(gpa) as avg_gpa from user_profile where
gender='male'
#如果要保留一位小数
select count(gender) as male_num,round(avg(gpa),1) as avg_gpa from user_profile where
gender='male'
6）分组计算
select
gender,
university,
count(gender) as user_num,
avg(active_days_within_30) as avg_active_day,
avg(question_cnt) as avg_question_cnt
from
user_profile
group by
gender,
university
7）where in 和 not in
select device_id,gender,age,university,gpa
from user_profile
where university in(
"北京大学",
"复旦大学",
"山东大学"
)
select device_id,gender,age,university,gpa
from user_profile
where university not in(
"浙江大学")
8）like的用法
select device_id,age,university from user_profile where university like'北京%'
9）having的用法
聚合函数结果作为筛选条件时，不能用where，而是用having语法
where 从记录中法过滤出某一条记录
having 可以从一组组记录中过滤掉其哪几组
使用了having必须使用group by,但是使用group by 不一定使用having
SQL语句执行顺序 ：
FROM - ON - JOIN - WHERE - GROUP BY - WITH - HAVING - SELECT - DISTINCT - ORDER BY - LIMIT
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
