前面几篇讲的都是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 ('01', '上海', '10100', '0201');……
其中:code字段才是最后真正需要的代码。
附件:
MYSQL数据库表的SQL文件:
表province:/s/1vN44u
表city:/s/19uIat
表distract:/s/1mufMI
以上文件可下载然后新建一个数据库,执行上面的SQL语句,即可创建表和插入数据。具体的获得方法将在后面继续。。。