MySQL 聚合函数，group by，order by，去重distinct

统计个数: count()

select count(*) from student;#8
select count(*) from t_studentinfo;#7
select count(sex) from t_studentinfo;#6
select count(name) from t_studentinfo;#7

求和: sum()

select sum(score) from sc where C = '01';#387
select sum(score) from sc where S = '02';#210

最大值: max()

select max(score) from sc where C = '01';#80

最小值: min()

select min(score),max(score) from sc where S = '02';#60 80

平均值: avg()

select avg(score) from sc where C = '01';#64.5

分组 group by

分组: group by 分组条件;

查询每门课程的总分

select C,sum(score) from sc group by C;

查询每位同学的平均分

select S,avg(score) from sc group by S;

查询总成绩大于200的学生的学号和总成绩

1.查询每个同学的总成绩

select S,sum(score) from sc group by S;

2.筛选总成绩大于200的学生

#查找总成绩大于200学生的id
select S from sc group by S having sum(score)>200;
#根据id查找这些学生的详细信息
select * from student where S in(select S from sc group by S having sum(score)>200);

添加条件的时候,什么时候用where,什么时候用having:

作为条件的列是表中原来就有的使用where加条件

作为条件的列是表中原来没有的,就是后来计算出来的,使用having加条件

排序order by

排序: order by 列名 排序规则(asc升序/desc降序);

select S,sum(score) total from sc group by S order by total desc;

distinct 去重

#distinct 去重
select s from sc;
select distinct s from sc;

#两列无法去重
select s,c from sc;
select distinct s,c from sc;

去重前 去重后         两列无法去重结果一致