Mysql中的sum()与group by_mysql group by sum

0 总结

Get to the points first. The article comes from LawsonAbs!

• updata on 20200505：修改相关格式；小弟技艺不精，对不起各位观众

1.初始化操作

#建表
create table myworld(
	country varchar(255),
	population varchar(255)	
)

#插入数据,注意如果你的数据库字符集不是utf8，那么下面的插入语句会出错
insert into mydatabase.myworld VALUES
("英国","200"),
("法国","300"),
("日本","250"),
("德国","200"),
("墨西哥","50"),
("印度","250"),
("中国","600");
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

select * 
from mydatabase.myworld;
1
2

插入新的一列（state）

ALTER TABLE mydatabase.myworld add state VARCHAR(10) not NULL
desc mydatabase.myworld
1
2

根据每个国家的地理位置添加所在洲

update  mydatabase.myworld 
set state='亚洲' 
where country in ('中国','日本','印度');


update mydatabase.myworld 
set state='欧洲' 
where country in ('英国','法国','德国'); 


update  mydatabase.myworld 
set state='南美洲' 
where country in ('美国','墨西哥');
1
2
3
4
5
6
7
8
9
10
11
12
13

select state,sum(population) 
from mydatabase.myworld 
group by state; #根据不同的洲名来分组


select state,sum(population) as totalPopulation 
from mydatabase.myworld 
where state = "亚洲";#只选择亚洲的国家
1
2
3
4
5
6
7
8