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;