中国天气网城市代码表(MYSQL) 缺乏、安全感 2022-09-19 05:26 163阅读 0赞 前面几篇讲的都是JSON格式的代码,本篇来列举MYSQL5中存放城市代码: 第一张表:省份表(province) -- ---------------------------- -- Table structure for `province` -- ---------------------------- DROP TABLE IF EXISTS `province`; CREATE TABLE `province` ( `id` varchar(10) NOT NULL DEFAULT '', `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 表中的数据: -- ---------------------------- -- Records of province -- ---------------------------- INSERT INTO `province` VALUES ('01', '北京'); INSERT INTO `province` VALUES ('02', '上海'); INSERT INTO `province` VALUES ('03', '天津'); INSERT INTO `province` VALUES ('04', '重庆'); INSERT INTO `province` VALUES ('05', '黑龙江'); INSERT INTO `province` VALUES ('06', '吉林'); INSERT INTO `province` VALUES ('07', '辽宁'); INSERT INTO `province` VALUES ('08', '内蒙古'); INSERT INTO `province` VALUES ('09', '河北'); INSERT INTO `province` VALUES ('10', '山西'); INSERT INTO `province` VALUES ('11', '陕西'); INSERT INTO `province` VALUES ('12', '山东'); INSERT INTO `province` VALUES ('13', '新疆'); INSERT INTO `province` VALUES ('14', '西藏'); INSERT INTO `province` VALUES ('15', '青海'); INSERT INTO `province` VALUES ('16', '甘肃'); INSERT INTO `province` VALUES ('17', '宁夏'); INSERT INTO `province` VALUES ('18', '河南'); INSERT INTO `province` VALUES ('19', '江苏'); INSERT INTO `province` VALUES ('20', '湖北'); INSERT INTO `province` VALUES ('21', '浙江'); INSERT INTO `province` VALUES ('22', '安徽'); INSERT INTO `province` VALUES ('23', '福建'); INSERT INTO `province` VALUES ('24', '江西'); INSERT INTO `province` VALUES ('25', '湖南'); INSERT INTO `province` VALUES ('26', '贵州'); INSERT INTO `province` VALUES ('27', '四川'); INSERT INTO `province` VALUES ('28', '广东'); INSERT INTO `province` VALUES ('29', '云南'); INSERT INTO `province` VALUES ('30', '广西'); INSERT INTO `province` VALUES ('31', '海南'); INSERT INTO `province` VALUES ('32', '香港'); INSERT INTO `province` VALUES ('33', '澳门'); INSERT INTO `province` VALUES ('34', '台湾'); 第二张表:城市表(city) -- ---------------------------- -- Table structure for `city` -- ---------------------------- DROP TABLE IF EXISTS `city`; CREATE TABLE `city` ( `id` varchar(10) NOT NULL DEFAULT '', `name` varchar(20) DEFAULT NULL, `pro_id` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`), KEY `pro_id` (`pro_id`), CONSTRAINT `city_ibfk_1` FOREIGN KEY (`pro_id`) REFERENCES `province` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 表中的数据: -- ---------------------------- -- Records of city -- ---------------------------- INSERT INTO `city` VALUES ('0101', '北京', '01'); INSERT INTO `city` VALUES ('0201', '上海', '02'); INSERT INTO `city` VALUES ('0301', '天津', '03'); INSERT INTO `city` VALUES ('0401', '重庆', '04'); INSERT INTO `city` VALUES ('0501', '哈尔滨', '05'); INSERT INTO `city` VALUES ('0502', '齐齐哈尔', '05'); INSERT INTO `city` VALUES ('0503', '牡丹江', '05'); INSERT INTO `city` VALUES ('0504', '佳木斯', '05'); INSERT INTO `city` VALUES ('0505', '绥化', '05'); INSERT INTO `city` VALUES ('0506', '黑河', '05'); INSERT INTO `city` VALUES ('0507', '大兴安岭', '05'); INSERT INTO `city` VALUES ('0508', '伊春', '05'); …… 数据太长,具体的下面会说。 第三张表:行政区表(distract) -- ---------------------------- -- Table structure for `distract` -- ---------------------------- DROP TABLE IF EXISTS `distract`; CREATE TABLE `distract` ( `id` varchar(10) NOT NULL DEFAULT '', `name` varchar(20) DEFAULT NULL, `code` varchar(15) DEFAULT NULL, `city_id` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`), KEY `city_id` (`city_id`), CONSTRAINT `distract_ibfk_1` FOREIGN KEY (`city_id`) REFERENCES `city` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 表中的部分数据: -- ---------------------------- -- Records of distract -- ---------------------------- INSERT INTO `distract` VALUES ('010101', '北京', '101010100', '0101'); INSERT INTO `distract` VALUES ('010102', '海淀', '101010200', '0101'); INSERT INTO `distract` VALUES ('010103', '朝阳', '101010300', '0101'); INSERT INTO `distract` VALUES ('010104', '顺义', '101010400', '0101'); INSERT INTO `distract` VALUES ('010105', '怀柔', '101010500', '0101'); INSERT INTO `distract` VALUES ('010106', '通州', '101010600', '0101'); INSERT INTO `distract` VALUES ('010107', '昌平', '101010700', '0101'); INSERT INTO `distract` VALUES ('010108', '延庆', '101010800', '0101'); INSERT INTO `distract` VALUES ('010109', '丰台', '101010900', '0101'); INSERT INTO `distract` VALUES ('010110', '石景山', '101011000', '0101'); INSERT INTO `distract` VALUES ('010111', '大兴', '101011100', '0101'); INSERT INTO `distract` VALUES ('010112', '房山', '101011200', '0101'); INSERT INTO `distract` VALUES ('010113', '密云', '101011300', '0101'); INSERT INTO `distract` VALUES ('010114', '门头沟', '101011400', '0101'); INSERT INTO `distract` VALUES ('010115', '平谷', '101011500', '0101'); INSERT INTO `distract` VALUES ('020101', '上海', '101020100', '0201'); …… 其中:code字段才是最后真正需要的代码。 附件: MYSQL数据库表的SQL文件: 表province:[http://pan.baidu.com/s/1vN44u][http_pan.baidu.com_s_1vN44u] 表city:[http://pan.baidu.com/s/19uIat][http_pan.baidu.com_s_19uIat] 表distract:[http://pan.baidu.com/s/1mufMI][http_pan.baidu.com_s_1mufMI] 以上文件可下载然后新建一个数据库,执行上面的SQL语句,即可创建表和插入数据。具体的获得方法将在后面继续。。。 [http_pan.baidu.com_s_1vN44u]: http://pan.baidu.com/s/1vN44u [http_pan.baidu.com_s_19uIat]: http://pan.baidu.com/s/19uIat [http_pan.baidu.com_s_1mufMI]: http://pan.baidu.com/s/1mufMI
还没有评论,来说两句吧...