开放 本地mysql 56个民族 省

视图

SELECT * FROM prov;
SELECT * FROM cit;
SELECT * FROM are;

 

 CREATE OR REPLACE VIEW prov AS 
 
(SELECT * FROM `all` WHERE type=1) ;

CREATE OR REPLACE VIEW cit AS 
(SELECT * FROM `all` WHERE type=2) ;


CREATE OR REPLACE VIEW are AS 
(SELECT * FROM `all` WHERE type=3) ;

 

-- ----------------------------
-- View structure for `are`
-- ----------------------------
DROP VIEW IF EXISTS `are`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `are` AS (select `thisgod`.`all`.`id` AS `id`,`thisgod`.`all`.`name` AS `name`,`thisgod`.`all`.`super` AS `super`,`thisgod`.`all`.`type` AS `type` from `all` where (`thisgod`.`all`.`type` = 3)) ;

-- ----------------------------
-- View structure for `cit`
-- ----------------------------
DROP VIEW IF EXISTS `cit`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `cit` AS (select `thisgod`.`all`.`id` AS `id`,`thisgod`.`all`.`name` AS `name`,`thisgod`.`all`.`super` AS `super`,`thisgod`.`all`.`type` AS `type` from `all` where (`thisgod`.`all`.`type` = 2)) ;

-- ----------------------------
-- View structure for `prov`
-- ----------------------------
DROP VIEW IF EXISTS `prov`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `prov` AS (select `thisgod`.`all`.`id` AS `id`,`thisgod`.`all`.`name` AS `name`,`thisgod`.`all`.`super` AS `super`,`thisgod`.`all`.`type` AS `type` from `all` where (`thisgod`.`all`.`type` = 1)) ;

 

SELECT * FROM 
(SELECT * FROM `all` WHERE type=1) prov
LEFT JOIN   (SELECT * FROM `all` WHERE type=2) city ON prov.id=city.super
LEFT JOIN  (SELECT * FROM `all` WHERE type=3) area ON city.id=area.super

 

SELECT * FROM `all` WHERE type=1;
SELECT * FROM `all` WHERE type=2;
SELECT * FROM `all` WHERE type=3;

合表】、

 

INSERT INTO `all`
SELECT * FROM province
UNION all
SELECT * FROM city
UNION all
SELECT * FROM area 

 

CREATE TABLE `all` (
  `id` int(11) NOT NULL COMMENT 'id',
  `name` varchar(255) NOT NULL COMMENT '名',
  `super` int(11) NOT NULL DEFAULT '0',
  `type` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`,`name`,`super`,`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

INSERT INTO `all`
SELECT * FROM province
UNION all
SELECT * FROM city
UNION all
SELECT * FROM area 

 

http://www.mca.gov.cn/article/sj/     中国民政部

 

SELECT * FROM (

SELECT COUNT(DISTINCT c.city_id) AS cou,p.province_name FROM sys_province p
LEFT JOIN sys_city c ON c.province_id=p.province_id
GROUP BY p.province_id
) s WHERE s.cou<=2

SELECT * FROM sys_province p
LEFT JOIN sys_city c ON c.province_id=p.province_id

查询36省下直辖只有两个及以下的

 

56个民族：

 

 

 

 
SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `s_nation`
-- ----------------------------
DROP TABLE IF EXISTS `s_nation`;
CREATE TABLE `s_nation` (
  `id` varchar(32) NOT NULL,
  `nation` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of s_nation
-- ----------------------------
INSERT INTO `s_nation` VALUES ('1', '汉族');
INSERT INTO `s_nation` VALUES ('10', '朝鲜族');
INSERT INTO `s_nation` VALUES ('11', '满族');
INSERT INTO `s_nation` VALUES ('12', '侗族');
INSERT INTO `s_nation` VALUES ('13', '瑶族');
INSERT INTO `s_nation` VALUES ('14', '白族');
INSERT INTO `s_nation` VALUES ('15', '土家族');
INSERT INTO `s_nation` VALUES ('16', '哈尼族');
INSERT INTO `s_nation` VALUES ('17', '哈萨克族');
INSERT INTO `s_nation` VALUES ('18', '傣族');
INSERT INTO `s_nation` VALUES ('19', '黎族');
INSERT INTO `s_nation` VALUES ('2', '蒙古族');
INSERT INTO `s_nation` VALUES ('20', '傈僳族');
INSERT INTO `s_nation` VALUES ('21', '佤族');
INSERT INTO `s_nation` VALUES ('22', '畲族');
INSERT INTO `s_nation` VALUES ('23', '高山族');
INSERT INTO `s_nation` VALUES ('24', '拉祜族');
INSERT INTO `s_nation` VALUES ('25', '水族');
INSERT INTO `s_nation` VALUES ('26', '东乡族');
INSERT INTO `s_nation` VALUES ('27', '纳西族');
INSERT INTO `s_nation` VALUES ('28', '景颇族');
INSERT INTO `s_nation` VALUES ('29', '柯尔克孜族');
INSERT INTO `s_nation` VALUES ('3', '回族');
INSERT INTO `s_nation` VALUES ('30', '土族');
INSERT INTO `s_nation` VALUES ('31', '达翰尔族');
INSERT INTO `s_nation` VALUES ('32', '么佬族');
INSERT INTO `s_nation` VALUES ('33', '羌族');
INSERT INTO `s_nation` VALUES ('34', '布朗族');
INSERT INTO `s_nation` VALUES ('35', '撒拉族');
INSERT INTO `s_nation` VALUES ('36', '毛南族');
INSERT INTO `s_nation` VALUES ('37', '仡佬族');
INSERT INTO `s_nation` VALUES ('38', '锡伯族');
INSERT INTO `s_nation` VALUES ('39', '阿昌族');
INSERT INTO `s_nation` VALUES ('4', '藏族');
INSERT INTO `s_nation` VALUES ('40', '普米族');
INSERT INTO `s_nation` VALUES ('41', '塔吉克族');
INSERT INTO `s_nation` VALUES ('42', '怒族');
INSERT INTO `s_nation` VALUES ('43', '乌孜别克族');
INSERT INTO `s_nation` VALUES ('44', '俄罗斯族');
INSERT INTO `s_nation` VALUES ('45', '鄂温克族');
INSERT INTO `s_nation` VALUES ('46', '德昂族');
INSERT INTO `s_nation` VALUES ('47', '保安族');
INSERT INTO `s_nation` VALUES ('48', '裕固族');
INSERT INTO `s_nation` VALUES ('49', '京族');
INSERT INTO `s_nation` VALUES ('5', '维吾尔族');
INSERT INTO `s_nation` VALUES ('50', '塔塔尔族');
INSERT INTO `s_nation` VALUES ('51', '独龙族');
INSERT INTO `s_nation` VALUES ('52', '鄂伦春族');
INSERT INTO `s_nation` VALUES ('53', '赫哲族');
INSERT INTO `s_nation` VALUES ('54', '门巴族');
INSERT INTO `s_nation` VALUES ('55', '珞巴族');
INSERT INTO `s_nation` VALUES ('56', '基诺族');
INSERT INTO `s_nation` VALUES ('6', '苗族');
INSERT INTO `s_nation` VALUES ('7', '彝族');
INSERT INTO `s_nation` VALUES ('8', '壮族');
INSERT INTO `s_nation` VALUES ('9', '布依族');

 

 

 

 

 

 

 

 

怎么开放本地的数据，在同一个局域网都可以访问 mysql？

打开MySQL，用 for SQL 去连接上，找到一个叫mysql库的user表

方法一： 

update user set host='%' where host='127.0.0.1'

方法二： 

方法三：

INSERT INTO `user` VALUES ('%', 'tiger',
 '*81F5E21E35407D984A6CD4A731AEBFB6AF909E1B',
 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',
 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',
 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 
'Y', 'Y', '', '', '', '', '0', '0', '0', '0',
 'mysql_native_password', null, 'N');

 

目的就是，将localhost改为%，能够让所有主机ip访问自己的本地MySQL

任选其一，即可

 

flush privileges

警告：一定要 执行此方法

一共两步，即可成功！