學(xué)生表：
01.png
班級(jí)表：
02.png
1.group by + group_concat()
#按照性別分組，把每組包含數(shù)據(jù)中的name字段顯示。
select gender,group_concat(name) from students group by gender;
03.png
2.group by + 聚合函數(shù)
#分別統(tǒng)計(jì)性別為男/女的人年齡平均值
select gender,avg(age) from students group by gender;
04.png
3.group by + having
having 條件表達(dá)式：用來分組查詢后指定一些條件來輸出查詢結(jié)果
having作用和where一樣患亿，但having只能用于group by
select gender,count(*) from students group by gender having count(*)>2;
05.png
4.group by + with rollup
with rollup的作用是：在最后新增一行，來記錄當(dāng)前列里所有記錄的總和
例1：
select gender,count(*) from students group by gender with rollup;
06.png
例2：
select gender,group_concat(age) from students group by gender with rollup;
07.png
5. group by + order by + limit
查找 員工名字中最常見的10個(gè)名字。
select first_name, count(first_name) as count from employees group by first_name order by count desc limit 10;
09.png
6. 查找每年給與員工的薪水總額超营，并且按照高低 進(jìn)行排序协屡。
-
year函數(shù)會(huì)返回 給定日期的 年份
select '2020-06-11', year('2020-06-11');10.png
select year(from_date),sum(salary) as sum from salaries group by year(from_date) order by sum desc;
11.png
