SQL新手速学->第二课

对结果集按照一个列或者多个列进行排序：ORDER BY

语法：
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC(升序)|DESC（降序）;

按年龄进行排序-升序

SELECT device_id,age FROM user_profile
ORDER by age

求平均值：AVG
统计：COUNT
小数位限制：ROUND

求平均值和统计人数

SELECT 
    count(gender) as male_num,
    round(AVG(gpa),1) as avg_gpa  --求平均值且小数位为1
FROM 
    user_profile
WHERE 
    gender='male'; --条件为统计男性

结合合计函数，根据一个或多个列对结果集进行分组:GROUP BY

分组计算：

SELECT 
    gender,university,
    count(device_id) 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

聚合函数结果作为筛选条件时，不能用where，而是用having语法
过滤练习：

SELECT 
    university,
    round(avg(question_cnt),3) as avg_question_cnt,
    round(avg(answer_cnt),3) as avg_answer_cnt
FROM 
    user_profile
GROUP BY university 
having   avg_question_cnt<5 or avg_answer_cnt<20

分组排序：

SELECT
    university,
    avg(question_cnt) as avg_question_cnt
FROM
    user_profile
group by university
ORDER BY avg_question_cnt ASC

注：group by 是创建一个组，order by 是排序，而university的值是汉字，汉字是没办法排序的，也不是根据笔画来排序，所有只有数字可以排序，而大学名臣直接用group by 创建一个组就行了

嵌套：

SELECT 
    device_id,
    question_id,
    result
FROM
    question_practice_detail
WHERE device_id in (
    SELECT device_id from user_profile
    where university='浙江大学'
)
order by question_id