foreign-people-manage
/
foreignpeoplemanage.sql

/*
 Navicat MySQL Data Transfer

 Source Server         : Mysql
 Source Server Type    : MySQL
 Source Server Version : 80037
 Source Host           : localhost:3306
 Source Schema         : foreignpeoplemanage

 Target Server Type    : MySQL
 Target Server Version : 80037
 File Encoding         : 65001

 Date: 24/05/2024 21:38:07
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for child
-- ----------------------------
DROP TABLE IF EXISTS `child`;
CREATE TABLE `child`  (
  `pid` int NOT NULL,
  `children` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`pid`) USING BTREE,
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `people` (`pid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = COMPACT;

-- ----------------------------
-- Records of child
-- ----------------------------
INSERT INTO `child` VALUES (4, '14 15 19');
INSERT INTO `child` VALUES (12, '14 15 19');

-- ----------------------------
-- Table structure for couple
-- ----------------------------
DROP TABLE IF EXISTS `couple`;
CREATE TABLE `couple`  (
  `pid_1` int NOT NULL,
  `pid_2` int NULL DEFAULT NULL,
  PRIMARY KEY (`pid_1`) USING BTREE,
  INDEX `pid_2`(`pid_2`) USING BTREE,
  CONSTRAINT `couple_ibfk_1` FOREIGN KEY (`pid_1`) REFERENCES `people` (`pid`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `couple_ibfk_2` FOREIGN KEY (`pid_2`) REFERENCES `people` (`pid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = COMPACT;

-- ----------------------------
-- Records of couple
-- ----------------------------
INSERT INTO `couple` VALUES (3, 1);
INSERT INTO `couple` VALUES (1, 3);
INSERT INTO `couple` VALUES (12, 4);
INSERT INTO `couple` VALUES (4, 12);
INSERT INTO `couple` VALUES (16, 15);
INSERT INTO `couple` VALUES (15, 16);

-- ----------------------------
-- Table structure for notice
-- ----------------------------
DROP TABLE IF EXISTS `notice`;
CREATE TABLE `notice`  (
  `nid` int NOT NULL AUTO_INCREMENT,
  `title` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
  `workPlace` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
  `time` date NULL DEFAULT NULL,
  `adrs` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
  `type` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
  `context` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
  PRIMARY KEY (`nid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = COMPACT;

-- ----------------------------
-- Records of notice
-- ----------------------------
INSERT INTO `notice` VALUES (2, '傻逼玩意', '就挺该死', '2024-05-16', '厦门', '随便说说', NULL);
INSERT INTO `notice` VALUES (3, '傻逼玩意', '就挺该死', '2024-05-25', '厦门', '随便说说', 'aewf qwea fasdf2 daf');
INSERT INTO `notice` VALUES (4, '不知道说啥', '就挺该死', '2024-05-24', '厦门', '骂人', '啊发射点发华盛顿林肯接收到的,公司开具返利苏杭分厘卡电视机恢复了健康');

-- ----------------------------
-- Table structure for people
-- ----------------------------
DROP TABLE IF EXISTS `people`;
CREATE TABLE `people`  (
  `pid` int NOT NULL AUTO_INCREMENT,
  `ideCard` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
  `name` varchar(24) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
  `sex` int NULL DEFAULT NULL,
  `telph` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
  `napla1` int NULL DEFAULT NULL,
  `napla2` int NULL DEFAULT NULL,
  `adres1` int NULL DEFAULT NULL,
  `adres2` int NULL DEFAULT NULL,
  `email` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
  `eduBack` int NULL DEFAULT NULL,
  `isMarried` int NULL DEFAULT NULL,
  `isArmy` int NULL DEFAULT NULL,
  `health` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
  `work` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
  `isCrim` int NULL DEFAULT NULL,
  PRIMARY KEY (`pid`) USING BTREE,
  INDEX `ideCard`(`ideCard`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 19 CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = COMPACT;

-- ----------------------------
-- Records of people
-- ----------------------------
INSERT INTO `people` VALUES (1, '352228190111140010', '张高祯', 1, '18750644042', 0, 8, 0, 1, '1975363741@qq.com', 5, 1, 1, '健康', '程序员', 1);
INSERT INTO `people` VALUES (2, '352229399299914493', '不认识', 0, '12345678900', 0, 0, 0, 0, '1975363741@qq.com', 5, 0, 0, '健康', '公务员', 0);
INSERT INTO `people` VALUES (3, '352228200003241101', '梦里找的', 0, '18741047529', 0, 8, 0, 8, '1975363741@qq.com', 6, 1, 0, NULL, NULL, 0);
INSERT INTO `people` VALUES (4, '345332565566543345', '哈哈哈', 1, '12312312313', 0, 4, 1, 1, '1975363741@qq.com', 5, 1, 0, '健康', '公务员', 0);
INSERT INTO `people` VALUES (12, '345332565566543332', '北纬', 0, '18594029487', 2, 7, 1, 5, 'itheima@itcast.cn', 7, 1, 0, NULL, NULL, 0);
INSERT INTO `people` VALUES (14, '345386099211030001', '孩子1', 1, NULL, 2, 1, 1, 2, NULL, 5, 0, 0, '健康', '学生', 0);
INSERT INTO `people` VALUES (15, '345386099211030002', '孩子2', 0, '18594029487', 2, 1, 1, 2, 'itheima@itcast.cn', 5, 1, 0, NULL, NULL, 0);
INSERT INTO `people` VALUES (16, '345332565566543343', '女友', 0, '18594029487', 1, 0, 0, 1, 'itheima@itcast.cn', 5, 1, 1, NULL, NULL, 0);
INSERT INTO `people` VALUES (19, '352228190111143432', '你好', 0, '12332323111', 2, 7, 1, 5, '1975363741@qq.com', 5, 0, 0, NULL, NULL, 0);

-- ----------------------------
-- Table structure for relation
-- ----------------------------
DROP TABLE IF EXISTS `relation`;
CREATE TABLE `relation`  (
  `pid` int NOT NULL,
  `rel_pid` int NULL DEFAULT NULL,
  `relation` int NULL DEFAULT NULL,
  PRIMARY KEY (`pid`) USING BTREE,
  INDEX `rel_pid`(`rel_pid`) USING BTREE,
  CONSTRAINT `relation_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `people` (`pid`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `relation_ibfk_2` FOREIGN KEY (`rel_pid`) REFERENCES `people` (`pid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of relation
-- ----------------------------

-- ----------------------------
-- Table structure for suggest
-- ----------------------------
DROP TABLE IF EXISTS `suggest`;
CREATE TABLE `suggest`  (
  `sid` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
  `ideCard` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
  `context` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
  PRIMARY KEY (`sid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = COMPACT;

-- ----------------------------
-- Records of suggest
-- ----------------------------
INSERT INTO `suggest` VALUES (1, '张高祯', '352228190111140010', '傻逼玩意的洒家分厘卡释放了在细胞分裂看见我哈佛`给看v轮廓接近崩溃几个客家话看见黄瓜看能否句就会华人妇女调用感觉创业教育人们从激活经济法客户吧');
INSERT INTO `suggest` VALUES (2, '张高祯', '352228190111140010', '林俊安骑着野猪带领就挺该死小队在墙上横跳,乐此不疲.');
INSERT INTO `suggest` VALUES (3, '张高祯', '352228190111140010', '拉萨市的回答和饭卡手动阀你拉开距离后来就卡死的很惨爱看厉害v的撒可立即回复来看');
INSERT INTO `suggest` VALUES (4, '张高祯', '352228190111140010', 'asdfadsdfasdfdas');
INSERT INTO `suggest` VALUES (5, '张高祯', '352228190111140010', 'asdfasdfweessvzxcvweeqer');
INSERT INTO `suggest` VALUES (6, '张高祯', '352228190111140010', 'vregqfwsdvcxaaascccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc');
INSERT INTO `suggest` VALUES (7, '张高祯', '352228190111140010', 'dsafqewwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwww');
INSERT INTO `suggest` VALUES (8, '张高祯', '352228190111140010', 'asdfasdfasdfasdf');

-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users`  (
  `ideCard` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `password` varchar(24) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
  `rec` int NULL DEFAULT NULL,
  PRIMARY KEY (`ideCard`) USING BTREE,
  CONSTRAINT `users_ibfk_1` FOREIGN KEY (`ideCard`) REFERENCES `people` (`ideCard`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = COMPACT;

-- ----------------------------
-- Records of users
-- ----------------------------
INSERT INTO `users` VALUES ('345332565566543345', '123', 0);
INSERT INTO `users` VALUES ('345386099211030002', '123', 0);
INSERT INTO `users` VALUES ('352228190111140010', '123', 0);
INSERT INTO `users` VALUES ('352229399299914493', '123', 1);

-- ----------------------------
-- View structure for crim
-- ----------------------------
DROP VIEW IF EXISTS `crim`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `crim` AS select `people`.`ideCard` AS `ideCard`,`people`.`name` AS `name` from `people` where (`people`.`isCrim` = 1);

SET FOREIGN_KEY_CHECKS = 1;