/*
 Navicat Premium Data Transfer

 Source Server         : docker-mysql
 Source Server Type    : MySQL
 Source Server Version : 100508
 Source Host           : localhost:3307
 Source Schema         : test

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

 Date: 03/11/2021 17:34:14
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for gonganju_huji
-- ----------------------------
DROP TABLE IF EXISTS `gonganju_huji`;
CREATE TABLE `gonganju_huji`  (
  `id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `cardno` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `peiou_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `peiou_cardno` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `zinv_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `zinv_cardno` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of gonganju_huji
-- ----------------------------
INSERT INTO `gonganju_huji` VALUES ('1', '吴昊', '333020211111111', '吴昊的配偶', '333020211112222', '吴昊的子女', '330203199901011125');
INSERT INTO `gonganju_huji` VALUES ('2', '放放风', '111111111', '放放风的配偶', '22222', NULL, NULL);
INSERT INTO `gonganju_huji` VALUES ('3', '程涛', '3333333333333', NULL, NULL, NULL, NULL);

-- ----------------------------
-- Table structure for hukouben
-- ----------------------------
DROP TABLE IF EXISTS `hukouben`;
CREATE TABLE `hukouben`  (
  `no` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`no`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of hukouben
-- ----------------------------
INSERT INTO `hukouben` VALUES ('111111111');

-- ----------------------------
-- Table structure for sfz
-- ----------------------------
DROP TABLE IF EXISTS `sfz`;
CREATE TABLE `sfz`  (
  `cardno` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `hukouben_no` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  PRIMARY KEY (`cardno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sfz
-- ----------------------------
INSERT INTO `sfz` VALUES ('111111111', '放放风', '111111111');
INSERT INTO `sfz` VALUES ('22222', '放放风的配偶', '111111111');
INSERT INTO `sfz` VALUES ('330203199901011125', '吴昊的子女', '333020211111111');
INSERT INTO `sfz` VALUES ('333020211111111', '吴昊', '333020211111111');
INSERT INTO `sfz` VALUES ('333020211112222', '吴昊的配偶', '333020211111111');
INSERT INTO `sfz` VALUES ('3333333333333', '程涛', '3333333333333');

-- ----------------------------
-- Function structure for NewProc
-- ----------------------------
DROP FUNCTION IF EXISTS `NewProc`;
delimiter ;;
CREATE DEFINER=`root`@`%` FUNCTION `NewProc`() RETURNS int(11)
BEGIN
RETURN( select  1 from sfz a left join hukouben b on a.hukouben_no=b.no where a.cardno=cardno and b.no !='');
  
END
;;
delimiter ;

-- ----------------------------
-- Function structure for seq_function
-- ----------------------------
DROP FUNCTION IF EXISTS `seq_function`;
delimiter ;;
CREATE DEFINER=`root`@`%` FUNCTION `seq_function`(seq_name VARCHAR(20)) RETURNS int(11)
BEGIN
   return( select  1 from sfz a left join hukouben b on a.hukouben_no=b.no where a.cardno=cardno and b.no !='');
END
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;