记录 MySQL的学习过程（7）简单查询

简单查询

a.查询 人口数量大于8000000并且国家名称是中国的城市名称和人口数
select NAME,population from City where Population > 8000000 and countrycode='CHN'

b.查询中国或美国的城市名和人口数
select NAME,population from City where countrycode='CHN' or countrycode='USA'

也可以这样写
select NAME,population from City where countrycode IN ('chn','usa');

c.查询人口数量大于5000000 小于 6000000的城市信息

select NAME,population from City where Population > 5000000 and Population < 6000000

select NAME,population from City where Population between 5000000 and 6000000

可以使用逻辑and连接2个条件，也可以直接使用between and 指定范围，效果都是一样的

模糊查询

查询一下countrycode中有C开头的城市信息

select NAME,population from City where Name like 'c%'

以c结尾
select NAME,population from City where Name like '%c'

select NAME,population from City where Name like '%c%'
这种前后都有%是会影响性能的，因为这样不走索引

 

GROUP BY

将某列中有共同条件的数据行,分成一组,然后在进行聚合函数操作

a.统计每个国家的城市个数

select countrycode ,COUNT(id) from City group by countrycode

+-------------+-----------+
| countrycode | COUNT(id) |
+-------------+-----------+
| ABW | 1 |
| AFG | 4 |
| AGO | 5 |
| AIA | 2 |
...
如果想要将输出的结果根据指定的列排序，可以使用以下语句

select countrycode ,COUNT(id) from City group by countrycode order by COUNT(id) asc

b.查询每个国家的人口汇总

select countrycode,SUM(population) from City group by countrycode

+-------------+-----------------+
| countrycode | SUM(population) |
+-------------+-----------------+
| ABW | 29034 |
| AFG | 2332100 |
| AGO | 2561600 |
| AIA | 1556 |
| ALB | 270000 |
...

c.查询每个国家的省的个数

select countrycode,count(DISTINCT district) from City group by countrycode;

因为省份会有重复，所以需要排除重复的

d.统计出中国每个省得总人口数

mysql> select district,sum(population) from City where countrycode='chn' group by district;
+----------------+-----------------+
| district | sum(population) |
+----------------+-----------------+
| Anhui | 5141136 |
| Chongqing | 6351600 |
| Fujian | 3575650 |
| Gansu | 2462631 |
...

e.统计中国每个省城市的个数

select district,count(name) from City where countrycode='chn' group by district;
+----------------+-------------+
| district | count(name) |
+----------------+-------------+
| Anhui | 16 |
| Chongqing | 1 |
| Fujian | 12 |
| Gansu | 7 |

...

f.统计中国每个省城市的名字列表

select district,GROUP_CONCAT(name) from City where countrycode='chn' group by district

使用 GROUP_CONCAT 函数实现了列转行

g.拼接成指定格式

select CONCAT(district,'-----',GROUP_CONCAT(NAME)) from City where countrycode='CHN' GROUP BY district;

效果如下
Ningxia-----Yinchuan,Shizuishan
其中 CONCAT用于拼接字符串

having

a.统计所有国家的总人口数，将总人口数大于1亿的过滤出来

select countrycode,sum(population) from City group by countrycode having sum(population) > 100000000;
+-------------+-----------------+
| countrycode | sum(population) |
+-------------+-----------------+
| CHN | 175953614 |
| IND | 123298526 |
+-------------+-----------------+

由此可以看出，having是在groupby以后再次根据条件过滤

注意 因为having是不走索引的，前面的运算结果如果很大的话，使用having会影响性能

order by

统计所有国家的总人口数，将总人口数大于5千万的过滤出来，然后按照从大到小排序

select countrycode,sum(population) from City group by countrycode having sum(population) > 50000000 order by sum(population) desc ;
+-------------+-----------------+
| countrycode | sum(population) |
+-------------+-----------------+
| CHN | 175953614 |
| IND | 123298526 |
| BRA | 85876862 |
| USA | 78625774 |
| JPN | 77965107 |
| RUS | 69150700 |
| MEX | 59752521 |

asc 正序 desc 逆序

limit

上面的列子 进行分页显示
显示前3行

select countrycode,sum(population) from City group by countrycode having sum(population) > 50000000 order by sum(population) desc limit 3;
+-------------+-----------------+
| countrycode | sum(population) |
+-------------+-----------------+
| CHN | 175953614 |
| IND | 123298526 |
| BRA | 85876862 |

显示4-6行
mysql> select countrycode,sum(population) from City group by countrycode having sum(population) > 50000000 order by sum(population) desc limit 3,3;
+-------------+-----------------+
| countrycode | sum(population) |
+-------------+-----------------+
| USA | 78625774 |
| JPN | 77965107 |
| RUS | 69150700 |

练习题

a.统计中国每个省得总人口数，只打印总人口数小于100W的

select district,sum(population) from City where CountryCode='CHN' group by district having sum(population) < 1000000;
+----------+-----------------+
| district | sum(population) |
+----------+-----------------+
| Hainan | 557120 |
| Ningxia | 802362 |
| Qinghai | 700200 |
| Tibet | 120000 |
+----------+-----------------+

b.统计中国所有的城市，并按照人口数进行排序（从大到小）

select * from City where CountryCode='CHN' order by population desc ;

c.统计中国各个省的总人口数，按照总人口从大到小排序

select district,sum(population) from City where CountryCode ='CHN' group by District order by sum(population) desc;

d.统计中国每个省的总人口，找出总人口大于500W的，并按照总人口从大到小排序，只显示前三名

select district,sum(population) from City where CountryCode ='CHN' group by District having sum(population)> 5000000 order by sum(population) desc limit 3;
+--------------+-----------------+
| district | sum(population) |
+--------------+-----------------+
| Liaoning | 15079174 |
| Shandong | 12114416 |
| Heilongjiang | 11628057 |
+--------------+-----------------+

关于书写（执行）顺序

select
from
where
group by
having
order by
limit

关于面试题中一旦涉及到统计的，就基本上会是使用group by 加聚合函数（sum，count之类)

 

 

关于 group by 中的sql_mode

only_full_group_by

这个问题只会在5.7中出现，

问题现象为 在带有 group by语句的select中，select后的条件列 要么为group by后面的关键字，要么需要用函数包裹

解决办法，修改my.cnf 然后 将sqlmode的选项值设置为不包含 only_full_group_by 的项即可，可使用select @@sql_mode 查看sql_mode都是使用了哪些参数