Java教程

SQL新手速学->第二课

本文主要是介绍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
这篇关于SQL新手速学->第二课的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!