（一）需求：
1、需求一：绘制各个省、城市的人口条形图，依次递减排列；
2、需求二：绘制各个省、城市的人口密度条形图，依次递减排序。
（二）实现：
1、准备好数据
（1）省、城市的名称；
（2）省、城市的人口；
（3）省、城市的面积，用于计算人口密度。
2、建表、导入数据
tb_populations.sql
/*
Navicat MySQL Data Transfer
Source Server : win7_local
Source Server Version : 50717
Source Host : localhost:3306
Source Database : analyze
Target Server Type : MYSQL
Target Server Version : 50717
File Encoding : 65001
Date: 2018-11-08 19:43:24
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for tb_populations
-- ----------------------------
DROP TABLE IF EXISTS `tb_populations`;
CREATE TABLE `tb_populations` (
`id` int(11) NOT NULL,
`province_name` varchar(30) DEFAULT NULL,
`province_population` int(11) DEFAULT NULL,
`area_wanpfkm` float DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of tb_populations
-- ----------------------------
INSERT INTO `tb_populations` VALUES ('1', '北京', '19612368', '1.68');
INSERT INTO `tb_populations` VALUES ('2', '天津', '12938693', '1.1');
INSERT INTO `tb_populations` VALUES ('3', '河北', '71854210', '19');
INSERT INTO `tb_populations` VALUES ('4', '山西', '35712101', '15');
INSERT INTO `tb_populations` VALUES ('5', '内蒙古', '24706291', '110');
INSERT INTO `tb_populations` VALUES ('6', '辽宁', '43746323', '15');
INSERT INTO `tb_populations` VALUES ('7', '吉林', '27452815', '18');
INSERT INTO `tb_populations` VALUES ('8', '黑龙江', '38313991', '46');
INSERT INTO `tb_populations` VALUES ('9', '上海', '23019196', '0.58');
INSERT INTO `tb_populations` VALUES ('10', '江苏', '78660941', '10');
INSERT INTO `tb_populations` VALUES ('11', '浙江', '54426891', '10');
INSERT INTO `tb_populations` VALUES ('12', '安徽', '59500468', '13');
INSERT INTO `tb_populations` VALUES ('13', '福建', '36894217', '12');
INSERT INTO `tb_populations` VALUES ('14', '江西', '44567797', '16');
INSERT INTO `tb_populations` VALUES ('15', '山东', '95792719', '15');
INSERT INTO `tb_populations` VALUES ('16', '河南', '94029939', '16');
INSERT INTO `tb_populations` VALUES ('17', '湖北', '57237727', '18');
INSERT INTO `tb_populations` VALUES ('18', '湖南', '65700762', '21');
INSERT INTO `tb_populations` VALUES ('19', '广东', '104320459', '18');
INSERT INTO `tb_populations` VALUES ('20', '广西', '46023761', '23');
INSERT INTO `tb_populations` VALUES ('21', '海南', '8671485', '3.4');
INSERT INTO `tb_populations` VALUES ('22', '重庆', '28846170', '8.23');
INSERT INTO `tb_populations` VALUES ('23', '四川', '80417528', '48');
INSERT INTO `tb_populations` VALUES ('24', '贵州', '34748556', '17');
INSERT INTO `tb_populations` VALUES ('25', '云南', '45966766', '38');
INSERT INTO `tb_populations` VALUES ('26', '西藏', '3002165', '120');
INSERT INTO `tb_populations` VALUES ('27', '陕西', '37327379', '19');
INSERT INTO `tb_populations` VALUES ('28', '甘肃', '25575263', '39');
INSERT INTO `tb_populations` VALUES ('29', '青海', '5626723', '72');
INSERT INTO `tb_populations` VALUES ('30', '宁夏', '6301350', '6.6');
INSERT INTO `tb_populations` VALUES ('31', '新疆', '21815815', '160');
INSERT INTO `tb_populations` VALUES ('32', '成都', '16040000', '1.46');
INSERT INTO `tb_populations` VALUES ('33', '武汉', '10910000', '0.85');
INSERT INTO `tb_populations` VALUES ('34', '广州', '14490000', '0.74');
INSERT INTO `tb_populations` VALUES ('35', '深圳', '12520000', '0.2');
3、编写代码。
（1）实现：需求一
01-matplotlib-renkou.py
import pymysql
import matplotlib.pyplot as plt
import matplotlib # 载入matplotlib完整库
matplotlib.rcParams['font.family'] = 'Microsoft Yahei' # 字体，改为微软雅黑，默认 sans-serif
matplotlib.rcParams['font.size'] = 18 # 字体大小，整数字号，默认10
# 连接数据库
conn = pymysql.connect(
host='localhost',
port=3306,
user='root',
passwd='root',
db='analyze',
charset='utf8'
)
# 创建游标
cur = conn.cursor()
# 获取数据
sql_1 = "select * from tb_populations"
ret_num1 = cur.execute(sql_1) # 生成查询sql语句，并且执行。ret_num表示受影响的记录条数
pro_data = cur.fetchall() # 获取查询结果
# 按照count_id 降序排列
list1 = list(pro_data)
list2 = sorted(list1, key=lambda p: p[2], reverse=True)
# 获取数据
province_name = [p[1] for p in list2]
province_population = [p[2] for p in list2]
print(province_name)
print(province_population)
# 绘图
# 设置x，y
x = [i for i in range(len(province_name))]
y = province_population
plt.figure(figsize=(20, 10), dpi=80)
plt.bar(
x,
y,
width=0.5,
color='r'
)
# 设置x轴刻度
_xticks_labels = [str(index + 1) + " " + value for index, value in enumerate(province_name)]
plt.xticks(x, _xticks_labels, rotation=40, fontsize=12)
# 设置网格
plt.grid()
# 设置文字
plt.title("中国各个省份人口数量统计2010年第6次人口普查 数据来源：国家统计局")
plt.xlabel("省/直辖市", color='b')
plt.ylabel("人口数（亿）", color='black')
plt.show()
输出：
（2）实现：需求二
02-matplotlib-renkou_per_pfkm.py
import pymysql
import matplotlib.pyplot as plt
import matplotlib # 载入matplotlib完整库
matplotlib.rcParams['font.family'] = 'Microsoft Yahei' # 字体，改为微软雅黑，默认 sans-serif
matplotlib.rcParams['font.size'] = 18 # 字体大小，整数字号，默认10
# 连接数据库
conn = pymysql.connect(
host='localhost',
port=3306,
user='root',
passwd='root',
db='analyze',
charset='utf8'
)
# 创建游标
cur = conn.cursor()
# 获取数据
sql_1 = "select * from tb_populations"
ret_num1 = cur.execute(sql_1) # 生成查询sql语句，并且执行。ret_num表示受影响的记录条数
pro_data = cur.fetchall() # 获取查询结果
# 按照 polulation_per_pfkm
# 获取数据
province_name = [p[1] for p in pro_data] # 省份、城市名称
polulation_per_pfkm = [int(p[2] / p[3]) for p in pro_data] # 人口密度（人口/平方千米）
# 重组数据，排序
list1 = list(map(lambda a, b: (a, b), province_name, polulation_per_pfkm))
list2 = sorted(list1, key=lambda p: p[1], reverse=True)
province_name = [p[0] for p in list2]
polulation_per_pfkm = [p[1] for p in list2]
print(province_name)
print(polulation_per_pfkm)
# 绘图
# 设置x，y
x = [i for i in range(len(province_name))]
y = polulation_per_pfkm
plt.figure(figsize=(20, 10), dpi=80)
plt.bar(
x,
y,
width=0.5,
color='r'
)
# 设置x轴刻度
_xticks_labels = [str(index + 1) + " " + value for index, value in enumerate(province_name)]
plt.xticks(x, _xticks_labels, rotation=40, fontsize=12)
# 设置网格
plt.grid()
# 设置文字
plt.title("中国各个省份人口密度统计2010年第6次人口普查 数据来源：国家统计局、百度知道")
plt.xlabel("省/直辖市/城市", color='b')
plt.ylabel("人口密度（千万人/平方千米）", color='black')
plt.show()
输出：
