7.排序order by
#升序
mysql> select * from world.city where countrycode=\'CHN\' order by population;
+------+---------------------+-------------+----------------+------------+
| ID | Name
| CountryCode | District
| Population |
+------+---------------------+-------------+----------------+------------+
| 2252 | Huangyan
| CHN
| Zhejiang
|
89288 |
| 2251 | Pingyi
| CHN
| Shandong
|
89373 |
| 2250 | Huaying
| CHN
| Sichuan
|
89400 |
| 2249 | Junan
| CHN
| Shandong
|
90222 |
| 2248 | Shaowu
| CHN
| Fujian
|
90286 |
| 2247 | Xilin Hot
| CHN
| Inner Mongolia |
90646 |
| 2246 | Linhai
| CHN
| Zhejiang
|
90870 |
| 2245 | Putian
| CHN
| Fujian
|
91030 |
| 2244 | Tumen
| CHN
| Jilin
|
91471 |
.......
#降序
mysql> select * from world.city where countrycode=\'CHN\' order by population desc;
+------+---------------------+-------------+----------------+------------+
| ID | Name
| CountryCode | District
| Population |
+------+---------------------+-------------+----------------+------------+
| 1890 | Shanghai
| CHN
| Shanghai
| 9696300 |
| 1891 | Peking
| CHN
| Peking
| 7472000 |
| 1892 | Chongqing
| CHN
| Chongqing
| 6351600 |
| 1893 | Tianjin
| CHN
| Tianjin
| 5286800 |
| 1894 | Wuhan
| CHN
| Hubei
| 4344600 |
| 1895 | Harbin
| CHN
| Heilongjiang | 4289800 |
| 1896 | Shenyang
| CHN
| Liaoning
| 4265200 |
| 1897 | Kanton [Guangzhou] | CHN
| Guangdong
| 4256300 |
| 1898 | Chengdu
| CHN
| Sichuan
| 3361500 |
......
8.分组 group by
1.遇到统计想函数
2.形容词前groupby
3.函数中央是名词
4.列名select后添加
#统计世界上每个国家的总人口数
sum(population)
group by countrycode
select countrycode,sum(population) from world.city group by countrycode;
mysql> select countrycode ,sum(population) from world.city group by countrycode;
+-------------+-----------------+
| countrycode | sum(population) |
+-------------+-----------------+
| ABW
|
29034 |
| AFG
|
2332100 |
| AGO
|
2561600 |
| AIA
|
1556 |
| ALB
|
270000 |
| AUS
|
11313666 |
......
#统计中国各个省的人口数量
sum(population)
group by district
select district,sum(population) from world.city where countrycode=\'CHN\' group by district order by sum(population);
mysql> select district ,sum(population) from world.city where countrycode=\'CHN\' group by district;
+----------------+-----------------+
| district
| sum(population) |
+----------------+-----------------+
| Anhui
|
5141136 |
| Chongqing
|
6351600 |
| Fujian
|
3575650 |
| Gansu
|
2462631 |
| Guangdong
|
9510263 |
| Guangxi
|
2925142 |
| Guizhou
|
2512087 |
......
#别名 统计中国各个省的人口数量
select district as 省 ,sum(population) as 总人口数 from world.city where countrycode=\'CHN\' group by 省 order by 总人口数;
mysql> select district as 省,sum(population) as 总人口数 from world.city where countrycode=\'CHN\' group by 省 order by 总人口数;
+----------------+--------------+
| 省
| 总人口数
|
+----------------+--------------+
| Tibet
|
120000 |
| Hainan
|
557120 |
| Qinghai
|
700200 |
| Ningxia
|
802362 |
| Yunnan
|
2451016 |
| Gansu
|
2462631 |
| Guizhou
|
2512087 |
......
#倒叙 统计中国各个省的人口数量
mysql> mysql> select countrycode as 国家,count(name) as 城市 from world.city group by 国家 order by 城市 desc;
+--------+--------+
| 国家 | 城市 |
+--------+--------+
| CHN | 363 |
| IND | 341 |
| USA | 274 |
| BRA | 250 |
| JPN | 248 |
| RUS | 189 |
......
#统计每个国家的城市数量
count(name)
group by countrycode
mysql> select countrycode,count(name) from world.city group by countrycode;
mysql> select countrycode,count(name) from world.city group by countrycode;
+-------------+-------------+
| countrycode | count(name) |
+-------------+-------------+
| ABW
|
1 |
| AFG
|
4 |
| AGO
|
5 |
| AIA
|
2 |
| ALB
|
1 |
| AND
|
1 |
| ANT
|
1 |
......
#统计每个国家的省数量 distinct是去重的意思
mysql> select countrycode,count(distinct(district)) from world.city where countrycode=\'CHN\' group by countrycode;
+-------------+---------------------------+
| countrycode | count(distinct(district)) |
+-------------+---------------------------+
| CHN
|
31 |
+-------------+---------------------------+
