MySQL去重与子查询and聚合和group by 分组
去重与子查询and聚合
1、DISTINCT 去重
select distinct 字段名 from 表名
select distinct age from user;
2、子查询(查询的条件还是一条SQL语句)
select * from 表名 where 字段名 in (SQL语句)
select * from user where age in(select age from user where id in(1,3,5));
3、聚合函数
- count 统计
- max 最大值
- min 最小值
- sum 求和
- avg 求平均数
select count(*),max(age), min(age), sum(age), avg(age) from user;
group by 分组
1、主体结构
select count(字段名) from 表名 group by 字段名
2、使用 无条件
-
统计男生女生分别有多少人
select sex,count(*) from user group by sex;
-
统计每个班级有多少人
select grade, count(*) from user group by grade;
-
统计每个班级的男生和女生分别有多少人
select grade,sex, count(*) from user group by grade,sex;
3、having 条件字句
-
查询班级人数大于2人
select grade,count(*) as count from user group by grade having count>2;
-
查看python35和python36班级的人数
select grade,count(*) as count from user group by grade having grade in('python35', 'python36');
-
查看python35和python36班级的人数 人数大于2人
select grade,count(*) as count from user group by grade having grade in('python35', 'python36') and count > 2;
本文来自博客园,作者:寻月隐君,转载请注明原文链接:https://www.cnblogs.com/QiaoPengjun/p/16013021.html