省市县三级联动的SQL语句
5星 · 超过95%的资源 126 浏览量
2020-12-15
12:53:08
上传
评论
收藏 418KB PDF 举报 
省市县三级联动的省市县三级联动的SQL语句语句
首先是建表语句
代码如下:
CREATE TABLE `t_address_province` (
`id` INT AUTO_INCREMENT PRIMARY KEY COMMENT ‘主键’,
`code` CHAR(6) NOT NULL COMMENT ‘省份编码’,
`name` VARCHAR(40) NOT NULL COMMENT ‘省份名称’
) ENGINE=InnoDB DEFAULT CHARSET=UTF8 COMMENT=’省份信息表’;
CREATE TABLE `t_address_city` (
`id` INT AUTO_INCREMENT PRIMARY KEY COMMENT ‘主键’,
`code` CHAR(6) NOT NULL COMMENT ‘城市编码’,
`name` VARCHAR(40) NOT NULL COMMENT ‘城市名称’,
`provinceCode` CHAR(6) NOT NULL COMMENT ‘所属省份编码’
) ENGINE=InnoDB DEFAULT CHARSET=UTF8 COMMENT=’城市信息表’;
CREATE TABLE `t_address_town` (
`id` INT AUTO_INCREMENT PRIMARY KEY COMMENT ‘主键’,
`code` CHAR(6) NOT NULL COMMENT ‘区县编码’,
`name` VARCHAR(40) NOT NULL COMMENT ‘区县名称’,
`cityCode` CHAR(6) NOT NULL COMMENT ‘所属城市编码’
) ENGINE=InnoDB DEFAULT CHARSET=UTF8 COMMENT=’区县信息表’;
然后是省份信息表数据
代码如下:
INSERT INTO `t_address_province` VALUES (‘1’, ‘110000’, ‘北京市’);
INSERT INTO `t_address_province` VALUES (‘2’, ‘120000’, ‘天津市’);
INSERT INTO `t_address_province` VALUES (‘3’, ‘130000’, ‘河北省’);
INSERT INTO `t_address_province` VALUES (‘4’, ‘140000’, ‘山西省’);
INSERT INTO `t_address_province` VALUES (‘5’, ‘150000’, ‘内蒙古自治区’);
INSERT INTO `t_address_province` VALUES (‘6’, ‘210000’, ‘辽宁省’);
INSERT INTO `t_address_province` VALUES (‘7’, ‘220000’, ‘吉林省’);
INSERT INTO `t_address_province` VALUES (‘8’, ‘230000’, ‘黑龙江省’);
INSERT INTO `t_address_province` VALUES (‘9’, ‘310000’, ‘上海市’);
INSERT INTO `t_address_province` VALUES (’10’, ‘320000’, ‘江苏省’);
INSERT INTO `t_address_province` VALUES (’11’, ‘330000’, ‘浙江省’);
INSERT INTO `t_address_province` VALUES (’12’, ‘340000’, ‘安徽省’);
INSERT INTO `t_address_province` VALUES (’13’, ‘350000’, ‘福建省’);
INSERT INTO `t_address_province` VALUES (’14’, ‘360000’, ‘江西省’);
INSERT INTO `t_address_province` VALUES (’15’, ‘370000’, ‘山东省’);
INSERT INTO `t_address_province` VALUES (’16’, ‘410000’, ‘河南省’);
INSERT INTO `t_address_province` VALUES (’17’, ‘420000’, ‘湖北省’);
INSERT INTO `t_address_province` VALUES (’18’, ‘430000’, ‘湖南省’);
INSERT INTO `t_address_province` VALUES (’19’, ‘440000’, ‘广东省’);
INSERT INTO `t_address_province` VALUES (’20’, ‘450000’, ‘广西壮族自治区’);
INSERT INTO `t_address_province` VALUES (’21’, ‘460000’, ‘海南省’);
INSERT INTO `t_address_province` VALUES (’22’, ‘500000’, ‘重庆市’);
INSERT INTO `t_address_province` VALUES (’23’, ‘510000’, ‘四川省’);
INSERT INTO `t_address_province` VALUES (’24’, ‘520000’, ‘贵州省’);
INSERT INTO `t_address_province` VALUES (’25’, ‘530000’, ‘云南省’);
INSERT INTO `t_address_province` VALUES (’26’, ‘540000’, ‘西藏自治区’);
INSERT INTO `t_address_province` VALUES (’27’, ‘610000’, ‘陕西省’);
INSERT INTO `t_address_province` VALUES (’28’, ‘620000’, ‘甘肃省’);
INSERT INTO `t_address_province` VALUES (’29’, ‘630000’, ‘青海省’);
INSERT INTO `t_address_province` VALUES (’30’, ‘640000’, ‘宁夏回族自治区’);
INSERT INTO `t_address_province` VALUES (’31’, ‘650000’, ‘新疆维吾尔自治区’);
INSERT INTO `t_address_province` VALUES (’32’, ‘710000’, ‘台湾省’);
INSERT INTO `t_address_province` VALUES (’33’, ‘810000’, ‘香港特别行政区’);
INSERT INTO `t_address_province` VALUES (’34’, ‘820000’, ‘澳门特别行政区’);
下面是城市信息表数据
代码如下:
INSERT INTO `t_address_city` VALUES (‘1’, ‘110100’, ‘北京市’, ‘110000’);
INSERT INTO `t_address_city` VALUES (‘2’, ‘1102xx’, ‘北京下属县’, ‘1100xx’);
INSERT INTO `t_address_city` VALUES (‘3’, ‘120100’, ‘天津市’, ‘120000’);
INSERT INTO `t_address_city` VALUES (‘4’, ‘1202xx’, ‘天津下属县’, ‘1200xx’);
剩余52页未读，继续阅读
- 1
- 2
前往页