xiaoan/xiaoan_user.sql

/*
 Navicat Premium Data Transfer

 Source Server         : 192.168.0.163_xiaoan_sit
 Source Server Type    : MySQL
 Source Server Version : 50708
 Source Host           : 192.168.0.163:3306
 Source Schema         : 4dkankan_v2

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

 Date: 10/04/2020 11:08:19
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;


-- ----------------------------
-- Table structure for tb_department
-- ----------------------------
DROP TABLE IF EXISTS `tb_department`;
CREATE TABLE `tb_department`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
  `rec_status` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '状态，A：激活，I：禁用',
  `update_time` datetime NULL DEFAULT NULL COMMENT '更新时间',
  `num` int(50) NOT NULL COMMENT '部门代号',
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '部门名称',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 15 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of tb_department
-- ----------------------------
INSERT INTO `tb_department` VALUES (1, '2020-02-27 15:57:10', 'A', '2020-04-08 09:25:27', 1, '测试部门');
INSERT INTO `tb_department` VALUES (3, '2020-03-10 15:16:19', 'A', '2020-03-10 15:16:19', 2, '开发部2');
INSERT INTO `tb_department` VALUES (7, '2020-03-17 16:26:19', 'A', '2020-04-01 18:23:54', 3, 'web开发');
INSERT INTO `tb_department` VALUES (13, '2020-04-08 09:25:35', 'A', '2020-04-08 09:25:35', 4, '123');
INSERT INTO `tb_department` VALUES (14, '2020-04-09 17:15:46', 'A', '2020-04-09 17:16:19', 5, '设计部23123123123112312312312311');


-- Table structure for tb_resource
-- ----------------------------
DROP TABLE IF EXISTS `tb_resource`;
CREATE TABLE `tb_resource`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime NULL DEFAULT NULL COMMENT '更新时间',
  `description` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '资源描述',
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '资源名称',
  `url` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '资源地址',
  `parent_id` bigint(20) NULL DEFAULT NULL COMMENT '父资源id',
  `resource_type` enum('menu','button') CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '资源类型',
  `resource_key` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '资源key',
  `rec_status` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '记录的状态，A: 生效，I: 禁用',
  `sort` int(6) NULL DEFAULT NULL COMMENT '排序',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `FKf5ra2gn0xedeida2op8097sr5`(`parent_id`) USING BTREE,
  CONSTRAINT `FKf5ra2gn0xedeida2op8097sr5` FOREIGN KEY (`parent_id`) REFERENCES `tb_resource` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 26 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '资源表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of tb_resource
-- ----------------------------
INSERT INTO `tb_resource` VALUES (1, '2018-05-10 17:18:36', '2018-05-10 17:22:42', NULL, '首页', NULL, NULL, 'menu', 'system', 'A', 1);
INSERT INTO `tb_resource` VALUES (2, '2018-05-11 10:05:41', '2018-12-04 11:22:02', NULL, '部门信息', NULL, NULL, 'menu', 'admin', 'A', 100);
INSERT INTO `tb_resource` VALUES (3, '2018-05-14 14:09:07', '2018-05-14 14:09:10', NULL, '用户管理', NULL, NULL, 'menu', 'admin', 'A', 200);
INSERT INTO `tb_resource` VALUES (4, '2018-05-14 15:08:06', '2018-05-14 15:08:08', NULL, '角色权限', NULL, NULL, 'menu', 'admin', 'A', 300);
INSERT INTO `tb_resource` VALUES (5, '2018-07-27 16:30:51', '2018-07-27 16:30:51', NULL, '场景管理', NULL, NULL, 'menu', 'admin:scene:list', 'I', 400);
INSERT INTO `tb_resource` VALUES (6, '2019-03-29 11:36:57', '2019-03-29 11:36:57', NULL, '数据统计', NULL, NULL, 'menu', 'admin', 'A', 500);
INSERT INTO `tb_resource` VALUES (7, NULL, NULL, NULL, '操作日志', NULL, NULL, 'menu', 'admin', 'A', 600);
INSERT INTO `tb_resource` VALUES (8, NULL, NULL, NULL, '问题反馈', NULL, NULL, 'menu', 'admin:issue:list', 'A', 700);
INSERT INTO `tb_resource` VALUES (9, NULL, NULL, NULL, '个人中心', NULL, NULL, 'menu', 'system', 'A', 800);
INSERT INTO `tb_resource` VALUES (10, NULL, NULL, NULL, '我的场景', NULL, NULL, 'menu', 'admin:scene:list', 'A', 810);
INSERT INTO `tb_resource` VALUES (11, NULL, NULL, NULL, '我的相机', NULL, NULL, 'menu', 'admin:camera:list', 'A', 820);
INSERT INTO `tb_resource` VALUES (12, NULL, NULL, NULL, '部门新增', NULL, 2, 'button', 'admin', 'A', 101);
INSERT INTO `tb_resource` VALUES (13, NULL, NULL, NULL, '部门删除', NULL, 2, 'button', 'admin', 'A', 102);
INSERT INTO `tb_resource` VALUES (14, NULL, NULL, NULL, '用户新增', NULL, 3, 'button', 'admin:user:add', 'A', 201);
INSERT INTO `tb_resource` VALUES (15, NULL, NULL, NULL, '用户编辑', NULL, 3, 'button', 'admin:user:edit', 'A', 202);
INSERT INTO `tb_resource` VALUES (16, NULL, NULL, NULL, '重置密码', NULL, 3, 'button', 'admin:user:resetPass', 'A', 203);
INSERT INTO `tb_resource` VALUES (17, NULL, NULL, NULL, '角色新增', NULL, 4, 'button', 'admin:role:add', 'A', 301);
INSERT INTO `tb_resource` VALUES (18, NULL, NULL, NULL, '角色授权', NULL, 4, 'button', 'admin:role:edit', 'A', 302);
INSERT INTO `tb_resource` VALUES (19, NULL, NULL, NULL, '问题新增', NULL, 8, 'button', 'admin:issue:add', 'A', 701);
INSERT INTO `tb_resource` VALUES (20, NULL, NULL, NULL, '问题回复', NULL, 8, 'button', 'admin:issue:reply', 'A', 702);
INSERT INTO `tb_resource` VALUES (21, NULL, NULL, NULL, '问题删除', NULL, 8, 'button', 'admin:issue:remove', 'A', 703);
INSERT INTO `tb_resource` VALUES (22, NULL, NULL, NULL, '场景删除', NULL, 10, 'button', 'admin:scene:remove', 'A', 811);
INSERT INTO `tb_resource` VALUES (23, NULL, NULL, NULL, '场景编辑', NULL, 10, 'button', 'admin:scene:edit', 'A', 812);
INSERT INTO `tb_resource` VALUES (24, NULL, NULL, NULL, '新增相机', NULL, 11, 'button', 'admin:camera:add', 'A', 821);
INSERT INTO `tb_resource` VALUES (25, NULL, NULL, NULL, '角色删除', NULL, 4, 'button', 'admin:role:remove', 'A', 303);

-- ----------------------------
-- Table structure for tb_role
-- ----------------------------
DROP TABLE IF EXISTS `tb_role`;
CREATE TABLE `tb_role`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime NULL DEFAULT NULL COMMENT '更新时间',
  `role_desc` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '角色描述',
  `role_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '角色名',
  `role_key` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '角色key',
  `rec_status` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '记录的状态，A: 生效，I: 禁用',
  `sort` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 12 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '角色表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of tb_role
-- ----------------------------
INSERT INTO `tb_role` VALUES (1, '2018-05-11 10:02:27', '2020-04-02 11:44:35', '所有权限', '超级管理员', 'admin', 'A', '1');
INSERT INTO `tb_role` VALUES (2, '2019-11-12 09:10:25', '2020-04-09 14:04:38', '指定模块增删改', '普通用户', 'normal', 'A', '2');
INSERT INTO `tb_role` VALUES (6, '2020-04-02 17:15:04', '2020-04-09 14:02:54', '', 'testRole', 'normal', 'A', NULL);
INSERT INTO `tb_role` VALUES (8, '2020-04-03 12:21:13', '2020-04-09 14:04:15', '', 'AAA', 'normal', 'A', NULL);
INSERT INTO `tb_role` VALUES (9, '2020-04-07 17:07:38', '2020-04-09 18:26:48', '具备所有权限', 'test超管账户', 'normal', 'A', NULL);
INSERT INTO `tb_role` VALUES (10, '2020-04-07 17:09:41', '2020-04-09 18:26:39', '场景编辑 管理 ', 'test普通用户', 'normal', 'A', NULL);
INSERT INTO `tb_role` VALUES (11, '2020-04-08 09:45:07', '2020-04-08 09:45:07', '32231123', '只有首页', 'normal', 'A', NULL);

-- ----------------------------
-- Table structure for tb_role_resource
-- ----------------------------
DROP TABLE IF EXISTS `tb_role_resource`;
CREATE TABLE `tb_role_resource`  (
  `role_id` bigint(20) NOT NULL COMMENT '角色表id',
  `resource_id` bigint(20) NOT NULL COMMENT '资源表id',
  PRIMARY KEY (`role_id`, `resource_id`) USING BTREE,
  INDEX `FK868kc8iic48ilv5npa80ut6qo`(`resource_id`) USING BTREE,
  CONSTRAINT `tb_role_resource_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `tb_role` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tb_role_resource_ibfk_2` FOREIGN KEY (`resource_id`) REFERENCES `tb_resource` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '角色资源关系表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of tb_role_resource
-- ----------------------------
INSERT INTO `tb_role_resource` VALUES (1, 1);
INSERT INTO `tb_role_resource` VALUES (2, 1);
INSERT INTO `tb_role_resource` VALUES (8, 1);
INSERT INTO `tb_role_resource` VALUES (9, 1);
INSERT INTO `tb_role_resource` VALUES (10, 1);
INSERT INTO `tb_role_resource` VALUES (11, 1);
INSERT INTO `tb_role_resource` VALUES (1, 2);
INSERT INTO `tb_role_resource` VALUES (1, 3);
INSERT INTO `tb_role_resource` VALUES (1, 4);
INSERT INTO `tb_role_resource` VALUES (1, 6);
INSERT INTO `tb_role_resource` VALUES (1, 7);
INSERT INTO `tb_role_resource` VALUES (1, 8);
INSERT INTO `tb_role_resource` VALUES (2, 8);
INSERT INTO `tb_role_resource` VALUES (6, 8);
INSERT INTO `tb_role_resource` VALUES (9, 8);
INSERT INTO `tb_role_resource` VALUES (10, 8);
INSERT INTO `tb_role_resource` VALUES (1, 9);
INSERT INTO `tb_role_resource` VALUES (2, 9);
INSERT INTO `tb_role_resource` VALUES (6, 9);
INSERT INTO `tb_role_resource` VALUES (8, 9);
INSERT INTO `tb_role_resource` VALUES (9, 9);
INSERT INTO `tb_role_resource` VALUES (10, 9);
INSERT INTO `tb_role_resource` VALUES (1, 10);
INSERT INTO `tb_role_resource` VALUES (2, 10);
INSERT INTO `tb_role_resource` VALUES (6, 10);
INSERT INTO `tb_role_resource` VALUES (8, 10);
INSERT INTO `tb_role_resource` VALUES (9, 10);
INSERT INTO `tb_role_resource` VALUES (10, 10);
INSERT INTO `tb_role_resource` VALUES (1, 11);
INSERT INTO `tb_role_resource` VALUES (2, 11);
INSERT INTO `tb_role_resource` VALUES (6, 11);
INSERT INTO `tb_role_resource` VALUES (8, 11);
INSERT INTO `tb_role_resource` VALUES (9, 11);
INSERT INTO `tb_role_resource` VALUES (10, 11);
INSERT INTO `tb_role_resource` VALUES (1, 12);
INSERT INTO `tb_role_resource` VALUES (1, 13);
INSERT INTO `tb_role_resource` VALUES (1, 14);
INSERT INTO `tb_role_resource` VALUES (1, 15);
INSERT INTO `tb_role_resource` VALUES (1, 16);
INSERT INTO `tb_role_resource` VALUES (1, 17);
INSERT INTO `tb_role_resource` VALUES (1, 18);
INSERT INTO `tb_role_resource` VALUES (1, 19);
INSERT INTO `tb_role_resource` VALUES (2, 19);
INSERT INTO `tb_role_resource` VALUES (6, 19);
INSERT INTO `tb_role_resource` VALUES (9, 19);
INSERT INTO `tb_role_resource` VALUES (10, 19);
INSERT INTO `tb_role_resource` VALUES (1, 20);
INSERT INTO `tb_role_resource` VALUES (6, 20);
INSERT INTO `tb_role_resource` VALUES (9, 20);
INSERT INTO `tb_role_resource` VALUES (10, 20);
INSERT INTO `tb_role_resource` VALUES (1, 21);
INSERT INTO `tb_role_resource` VALUES (6, 21);
INSERT INTO `tb_role_resource` VALUES (9, 21);
INSERT INTO `tb_role_resource` VALUES (10, 21);
INSERT INTO `tb_role_resource` VALUES (1, 22);
INSERT INTO `tb_role_resource` VALUES (2, 22);
INSERT INTO `tb_role_resource` VALUES (6, 22);
INSERT INTO `tb_role_resource` VALUES (8, 22);
INSERT INTO `tb_role_resource` VALUES (9, 22);
INSERT INTO `tb_role_resource` VALUES (10, 22);
INSERT INTO `tb_role_resource` VALUES (1, 23);
INSERT INTO `tb_role_resource` VALUES (2, 23);
INSERT INTO `tb_role_resource` VALUES (6, 23);
INSERT INTO `tb_role_resource` VALUES (8, 23);
INSERT INTO `tb_role_resource` VALUES (9, 23);
INSERT INTO `tb_role_resource` VALUES (10, 23);
INSERT INTO `tb_role_resource` VALUES (1, 24);
INSERT INTO `tb_role_resource` VALUES (2, 24);
INSERT INTO `tb_role_resource` VALUES (6, 24);
INSERT INTO `tb_role_resource` VALUES (8, 24);
INSERT INTO `tb_role_resource` VALUES (9, 24);
INSERT INTO `tb_role_resource` VALUES (10, 24);
INSERT INTO `tb_role_resource` VALUES (1, 25);

-- ----------------------------
-- Table structure for tb_user
-- ----------------------------
DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
  `rec_status` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '状态，0：激活，1：禁用',
  `update_time` datetime NULL DEFAULT NULL COMMENT '更新时间',
  `password` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '登录密码',
  `real_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户名',
  `user_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '手机号，用户号',
  `department_id` bigint(20) NULL DEFAULT NULL COMMENT '部门id',
  `email` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '邮箱',
  `user_num` int(255) NULL DEFAULT NULL COMMENT '用户代号',
  `sex` tinyint(4) NULL DEFAULT NULL COMMENT '性别， 0：男， 1：女',
  `status` int(11) NULL DEFAULT NULL,
  `view_count` int(11) NULL DEFAULT 0 COMMENT '登录次数',
  `phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '手机号',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 52 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of tb_user
-- ----------------------------
INSERT INTO `tb_user` VALUES (1, '2020-03-17 15:19:23', 'A', '2020-04-10 10:27:34', 'ebbbf6e8b86a8711ed006913d3b908dc', '伟浩(超级管理员)', 'owen', 1, '38@qq.com', 1, 0, 0, 102, '15015981234');
INSERT INTO `tb_user` VALUES (2, '2020-03-25 14:51:00', 'A', '2020-04-09 17:18:32', '5ceaedeb368e10b4', 'zhiguang', 'zhiguang', 3, '38@qq.com', 2, 0, 0, 25, '13700001234');
INSERT INTO `tb_user` VALUES (3, '2020-04-01 14:04:26', 'A', '2020-04-09 17:24:11', 'e7e2fc8bd858494a2fe39df56f9fc061', '13138102395', '13138102395', 1, '38@qq.com', 3, 0, 0, 13, '13700001234');
INSERT INTO `tb_user` VALUES (4, '2020-04-01 14:04:35', 'A', '2020-04-09 15:18:41', 'c9b2440b2e0bd0ca259e323b5ed1ea8c', '13112311178', '13112311178', 1, '38@qq.com', 4, 0, 0, 84, '13700001234');
INSERT INTO `tb_user` VALUES (5, '2020-04-01 14:54:31', 'A', '2020-04-09 14:11:56', '015bafd4fd53b9173ff70515044074a3', '超级管理员', '15088889999', 1, '38@qq.com', 7, 0, 0, 2, '15088889999');
INSERT INTO `tb_user` VALUES (42, '2020-04-01 15:28:26', 'A', '2020-04-09 18:27:06', 'ff73953c9e1380bba421cb7d85b5b2c7', '13112311179', '13112311179', 1, '123@qq.com', 5, 1, 0, 20, '13112311179');
INSERT INTO `tb_user` VALUES (43, '2020-04-01 15:58:36', 'A', '2020-04-01 16:05:43', 'ad8b100332a73b080450f7a48e0dfb38', '18826490500', '18826490500', 3, '111@qq.com', 6, 0, 0, 1, '18826490500');
INSERT INTO `tb_user` VALUES (46, '2020-04-02 17:15:53', 'A', '2020-04-09 14:55:07', 'f6f591035e3d3e28711dc8c74c4b6cb2', 'weihao', 'hao', 7, '38@qq.com', 8, 0, 0, 23, '15012345678');
INSERT INTO `tb_user` VALUES (51, '2020-04-09 17:19:41', 'A', '2020-04-09 17:27:23', '61e026a8939e8da1a93a47ac4227edc7', '123123311231231', 'test', 14, 'qqweq@qq.com', 9, 0, 0, 0, '13112311176');

-- ----------------------------
-- Table structure for tb_user_role
-- ----------------------------
DROP TABLE IF EXISTS `tb_user_role`;
CREATE TABLE `tb_user_role`  (
  `user_id` bigint(20) NOT NULL COMMENT '用户表id',
  `role_id` bigint(20) NOT NULL COMMENT '角色表id',
  PRIMARY KEY (`user_id`, `role_id`) USING BTREE,
  INDEX `role_id`(`role_id`) USING BTREE,
  CONSTRAINT `tb_user_role_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tb_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tb_user_role_ibfk_2` FOREIGN KEY (`role_id`) REFERENCES `tb_role` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户角色关系表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of tb_user_role
-- ----------------------------
INSERT INTO `tb_user_role` VALUES (1, 1);
INSERT INTO `tb_user_role` VALUES (2, 1);
INSERT INTO `tb_user_role` VALUES (5, 1);
INSERT INTO `tb_user_role` VALUES (43, 2);
INSERT INTO `tb_user_role` VALUES (51, 2);
INSERT INTO `tb_user_role` VALUES (46, 6);
INSERT INTO `tb_user_role` VALUES (3, 9);
INSERT INTO `tb_user_role` VALUES (4, 9);
INSERT INTO `tb_user_role` VALUES (42, 10);

SET FOREIGN_KEY_CHECKS = 1;