分組
- 按照字段分組，表示此字段相同的數(shù)據(jù)會被放到一個組中
- 分組后谢澈，只能查詢出相同的數(shù)據(jù)列煌贴，對于有差異的數(shù)據(jù)列無法出現(xiàn)在結(jié)果集中
- 可以對分組后的數(shù)據(jù)進行統(tǒng)計，做聚合運算
- 語法：
select 列1,列2,聚合... from 表名 group by 列1,列2,列3...
- 查詢男女生總數(shù)
select gender as 性別,count(*)
from students
group by gender;
- 查詢各城市人數(shù)
select hometown as 家鄉(xiāng),count(*)
from students
group by hometown;
分組后的數(shù)據(jù)篩選
- 語法：
select 列1,列2,聚合... from 表名
group by 列1,列2,列3...
having 列1,...聚合...
- having后面的條件運算符與where的相同
- 查詢男生總?cè)藬?shù)
方案一
select count(*)
from students
where gender=1;
-----------------------------------
方案二：
select gender as 性別,count(*)
from students
group by gender
having gender=1;
對比where與having
where是對from后面指定的表進行數(shù)據(jù)篩選锥忿，屬于對原始數(shù)據(jù)的篩選
having是對group by的結(jié)果進行篩選
