/*
 Navicat Premium Data Transfer

 Source Server         : MySQL
 Source Server Type    : MySQL
 Source Server Version : 80300
 Source Host           : localhost:3306
 Source Schema         : book_manage

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

 Date: 08/12/2024 16:07:40
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for bms_book
-- ----------------------------
DROP TABLE IF EXISTS `bms_book`;
CREATE TABLE `bms_book`  (
  `id` int(0) NOT NULL AUTO_INCREMENT COMMENT 'id,自动递增',
  `name` varchar(50) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL COMMENT '图书名称',
  `sn` varchar(50) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL COMMENT '图书编号',
  `pic` varchar(500) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL COMMENT '图书封面',
  `intro` varchar(500) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL COMMENT '图书介绍',
  `category_id` int(0) NULL DEFAULT NULL COMMENT '图书分类id',
  `added_time` datetime(0) NULL DEFAULT NULL COMMENT '添加日期',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `category_id`(`category_id`) USING BTREE,
  CONSTRAINT `bms_book_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `bms_category` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = gbk COLLATE = gbk_chinese_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of bms_book
-- ----------------------------
INSERT INTO `bms_book` VALUES (1, 'Java Web及其框架技术', '9787121468230', NULL, NULL, 1, '2024-04-23 22:13:06');
INSERT INTO `bms_book` VALUES (2, 'Python量化投资指南——基础、数据与实战', '9787121472862', NULL, NULL, 1, '2024-04-23 22:14:13');
INSERT INTO `bms_book` VALUES (3, '李可老中医急危重症疑难病经验专辑', '9787537761314', NULL, NULL, 3, '2024-04-23 22:15:15');
INSERT INTO `bms_book` VALUES (4, '伤寒论译释（第四版）', '9787532399291', NULL, NULL, 3, '2024-04-23 22:15:28');

-- ----------------------------
-- Table structure for bms_borrowlog
-- ----------------------------
DROP TABLE IF EXISTS `bms_borrowlog`;
CREATE TABLE `bms_borrowlog`  (
  `id` int(0) NOT NULL AUTO_INCREMENT COMMENT 'id，自动递增',
  `user_id` int(0) NOT NULL COMMENT '用户id，字段值来源于用户表sys_user的id字段值',
  `card_id` int(0) NOT NULL COMMENT '图书卡id，字段值来源于图书卡表bms_card的id字段值',
  `book_id` int(0) NOT NULL COMMENT '图书id，字段值来源于图书表bms_book的id字段值',
  `num` int(0) NOT NULL COMMENT '借阅/归还数量',
  `status` int(0) NOT NULL COMMENT '操作状态，0表示“归还”1表示“借出”',
  `added_time` datetime(0) NULL DEFAULT NULL COMMENT '添加日期',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `user_id`(`user_id`) USING BTREE,
  INDEX `card_id`(`card_id`) USING BTREE,
  INDEX `book_id`(`book_id`) USING BTREE,
  CONSTRAINT `bms_borrowlog_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `sys_user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `bms_borrowlog_ibfk_2` FOREIGN KEY (`card_id`) REFERENCES `bms_card` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `bms_borrowlog_ibfk_3` FOREIGN KEY (`book_id`) REFERENCES `bms_book` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = gbk COLLATE = gbk_chinese_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of bms_borrowlog
-- ----------------------------

-- ----------------------------
-- Table structure for bms_card
-- ----------------------------
DROP TABLE IF EXISTS `bms_card`;
CREATE TABLE `bms_card`  (
  `id` int(0) NOT NULL AUTO_INCREMENT COMMENT 'id，递增',
  `name` varchar(50) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL COMMENT '图书卡名称',
  `sn` varchar(50) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL COMMENT '图书卡编号',
  `intro` varchar(255) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL COMMENT '图书卡介绍',
  `borrowed_num` int(0) NULL DEFAULT NULL COMMENT '借出书数量',
  `user_id` int(0) NOT NULL COMMENT '所属用户id',
  `added_time` datetime(0) NULL DEFAULT NULL COMMENT '添加日期',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `user_id`(`user_id`) USING BTREE,
  CONSTRAINT `bms_card_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `sys_user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = gbk COLLATE = gbk_chinese_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of bms_card
-- ----------------------------
INSERT INTO `bms_card` VALUES (2, '校园图书卡2', '20240428001', NULL, 800000, 2, '2024-04-28 14:38:37');
INSERT INTO `bms_card` VALUES (3, '校园图书卡2', '20240428002', NULL, 800000, 3, '2024-04-28 14:38:40');

-- ----------------------------
-- Table structure for bms_category
-- ----------------------------
DROP TABLE IF EXISTS `bms_category`;
CREATE TABLE `bms_category`  (
  `id` int(0) NOT NULL AUTO_INCREMENT COMMENT 'id,自动递增',
  `name` varchar(50) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL COMMENT '分类名称',
  `intro` varchar(255) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL COMMENT '分类介绍',
  `parent_id` int(0) NULL DEFAULT NULL COMMENT '上级分类id',
  `added_time` datetime(0) NULL DEFAULT NULL COMMENT '添加日期',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `parent_id`(`parent_id`) USING BTREE,
  CONSTRAINT `bms_category_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `bms_category` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = gbk COLLATE = gbk_chinese_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of bms_category
-- ----------------------------
INSERT INTO `bms_category` VALUES (1, '计算机', NULL, NULL, '2024-04-23 22:04:55');
INSERT INTO `bms_category` VALUES (2, '建筑', NULL, NULL, '2024-04-23 22:05:12');
INSERT INTO `bms_category` VALUES (3, '医学', NULL, NULL, '2024-04-23 22:05:26');
INSERT INTO `bms_category` VALUES (4, '工业技术', NULL, NULL, '2024-04-23 22:05:56');
INSERT INTO `bms_category` VALUES (5, '自然科学', NULL, NULL, '2024-04-23 22:05:53');
INSERT INTO `bms_category` VALUES (6, '农业/林业', NULL, NULL, '2024-04-23 22:06:02');

-- ----------------------------
-- Table structure for sys_privilege
-- ----------------------------
DROP TABLE IF EXISTS `sys_privilege`;
CREATE TABLE `sys_privilege`  (
  `id` int(0) NOT NULL AUTO_INCREMENT COMMENT '权限id,自动递增',
  `name` varchar(50) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL COMMENT '权限名称，是给人看的“文章增加”',
  `code` varchar(50) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL COMMENT '权限码（给电脑程序看的如“article_add”）',
  `url` varchar(255) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL COMMENT '该权限操作的地址如“/article_add.do”',
  `added_time` datetime(0) NULL DEFAULT NULL COMMENT '添加日期',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 30 CHARACTER SET = gbk COLLATE = gbk_chinese_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sys_privilege
-- ----------------------------
INSERT INTO `sys_privilege` VALUES (1, ' 用户列表查询', 'sysuser_list', '/back/sysuser/list', '2024-03-13 15:15:44');
INSERT INTO `sys_privilege` VALUES (11, '用户增加', 'sysuser_add', '/back/sysuser/add', '2024-12-08 13:14:14');
INSERT INTO `sys_privilege` VALUES (12, '用户删除', 'sysuser_delete', '/back/sysuser/delete', '2024-12-08 13:15:59');
INSERT INTO `sys_privilege` VALUES (13, '用户修改', 'sysuser_update', '/back/sysuser/update', '2024-12-08 13:16:22');
INSERT INTO `sys_privilege` VALUES (14, '角色列表查询', 'sysrole_list', '/back/sysrole/list', '2024-12-08 13:17:00');
INSERT INTO `sys_privilege` VALUES (15, '角色删除', 'sysrole_delete', '/back/sysrole/delete', '2024-12-08 13:17:41');
INSERT INTO `sys_privilege` VALUES (16, '角色增加', 'sysrole_add', '/back/sysrole/add', '2024-12-08 13:18:15');
INSERT INTO `sys_privilege` VALUES (17, '角色修改', 'sysrole_update', '/back/sysrole/update', '2024-12-08 13:18:48');
INSERT INTO `sys_privilege` VALUES (18, '权限列表查询', 'sysprivilege_list', '/back/sysprivilege/list', '2024-12-08 13:19:28');
INSERT INTO `sys_privilege` VALUES (19, '权限删除', 'sysprivilege_delete', '/back/sysprivilege/delete', '2024-12-08 13:19:51');
INSERT INTO `sys_privilege` VALUES (20, '权限增加', 'sysprivilege_add', '/back/sysprivilege/add', '2024-12-08 13:20:15');
INSERT INTO `sys_privilege` VALUES (21, '权限修改', 'sysprivilege_update', '/back/sysprivilege/update', '2024-12-08 13:20:35');
INSERT INTO `sys_privilege` VALUES (22, '用户分配角色列车查询', 'sysuserrole_list', '/back/sysuserrole/list', '2024-12-08 13:21:32');
INSERT INTO `sys_privilege` VALUES (23, '用户分配角色删除', 'sysuserrole_delete', '/back/sysuserrole/delete', '2024-12-08 13:22:18');
INSERT INTO `sys_privilege` VALUES (24, '用户分配角色增加', 'sysuserrole_add', '/back/sysuserrole/add', '2024-12-08 13:22:42');
INSERT INTO `sys_privilege` VALUES (25, '用户分配角色修改', 'sysuserrole_update', '/back/sysuserrole/update', '2024-12-08 13:23:01');
INSERT INTO `sys_privilege` VALUES (26, '角色分配权限列表查询', 'sysroleprivilege_list', '/back/sysroleprivilege/list', '2024-12-08 13:26:05');
INSERT INTO `sys_privilege` VALUES (27, '角色分配权限删除', 'sysroleprivilege_delete', '/back/sysroleprivilege/delete', '2024-12-08 13:26:25');
INSERT INTO `sys_privilege` VALUES (28, '角色分配权限增加', 'sysroleprivilege_add', '/back/sysroleprivilege/add', '2024-12-08 13:26:47');
INSERT INTO `sys_privilege` VALUES (29, '角色分配权限修改', 'sysroleprivilege_update', '/back/sysroleprivilege/update', '2024-12-08 13:27:14');

-- ----------------------------
-- Table structure for sys_role
-- ----------------------------
DROP TABLE IF EXISTS `sys_role`;
CREATE TABLE `sys_role`  (
  `id` int(0) NOT NULL AUTO_INCREMENT COMMENT '角色id,自动递增',
  `name` varchar(20) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL COMMENT '角色名称，是给人看的“管理员”',
  `code` varchar(20) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL COMMENT '角色码（给电脑程序看的“admin”）',
  `added_time` datetime(0) NULL DEFAULT NULL COMMENT '添加日期',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 14 CHARACTER SET = gbk COLLATE = gbk_chinese_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sys_role
-- ----------------------------
INSERT INTO `sys_role` VALUES (1, '超级管理员', 'superadmin', '2024-03-13 15:10:47');
INSERT INTO `sys_role` VALUES (2, '管理员', 'admin', '2024-03-13 15:11:02');
INSERT INTO `sys_role` VALUES (3, '普通会员', 'user', '2024-03-13 15:12:31');

-- ----------------------------
-- Table structure for sys_roleprivilege
-- ----------------------------
DROP TABLE IF EXISTS `sys_roleprivilege`;
CREATE TABLE `sys_roleprivilege`  (
  `id` int(0) NOT NULL AUTO_INCREMENT COMMENT '主键id,自动递增',
  `role_id` int(0) NOT NULL COMMENT '角色id，字段值来源于角色表sys_role的id字段值',
  `privilege_id` int(0) NOT NULL COMMENT '权限id，字段值来源于权限表sysprivilege的id字段值',
  `added_time` datetime(0) NULL DEFAULT NULL COMMENT '添加日期',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `role_id`(`role_id`) USING BTREE,
  INDEX `privilege_id`(`privilege_id`) USING BTREE,
  CONSTRAINT `sys_roleprivilege_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `sys_role` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `sys_roleprivilege_ibfk_2` FOREIGN KEY (`privilege_id`) REFERENCES `sys_privilege` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 36 CHARACTER SET = gbk COLLATE = gbk_chinese_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sys_roleprivilege
-- ----------------------------
INSERT INTO `sys_roleprivilege` VALUES (16, 1, 1, '2024-12-08 13:29:11');
INSERT INTO `sys_roleprivilege` VALUES (17, 1, 11, '2024-12-08 13:29:15');
INSERT INTO `sys_roleprivilege` VALUES (18, 1, 12, '2024-12-08 13:29:20');
INSERT INTO `sys_roleprivilege` VALUES (19, 1, 13, '2024-12-08 13:29:23');
INSERT INTO `sys_roleprivilege` VALUES (20, 1, 14, '2024-12-08 13:29:29');
INSERT INTO `sys_roleprivilege` VALUES (21, 1, 15, '2024-12-08 13:29:32');
INSERT INTO `sys_roleprivilege` VALUES (22, 1, 16, '2024-12-08 13:29:36');
INSERT INTO `sys_roleprivilege` VALUES (23, 1, 17, '2024-12-08 13:29:40');
INSERT INTO `sys_roleprivilege` VALUES (24, 1, 18, '2024-12-08 13:29:45');
INSERT INTO `sys_roleprivilege` VALUES (25, 1, 19, '2024-12-08 13:29:51');
INSERT INTO `sys_roleprivilege` VALUES (26, 1, 20, '2024-12-08 13:29:56');
INSERT INTO `sys_roleprivilege` VALUES (27, 1, 21, '2024-12-08 13:29:59');
INSERT INTO `sys_roleprivilege` VALUES (28, 1, 22, '2024-12-08 13:30:03');
INSERT INTO `sys_roleprivilege` VALUES (29, 1, 23, '2024-12-08 13:30:07');
INSERT INTO `sys_roleprivilege` VALUES (30, 1, 24, '2024-12-08 13:30:11');
INSERT INTO `sys_roleprivilege` VALUES (31, 1, 25, '2024-12-08 13:30:15');
INSERT INTO `sys_roleprivilege` VALUES (32, 1, 26, '2024-12-08 13:30:19');
INSERT INTO `sys_roleprivilege` VALUES (33, 1, 27, '2024-12-08 13:30:23');
INSERT INTO `sys_roleprivilege` VALUES (34, 1, 28, '2024-12-08 13:30:26');
INSERT INTO `sys_roleprivilege` VALUES (35, 1, 29, '2024-12-08 13:30:31');

-- ----------------------------
-- Table structure for sys_user
-- ----------------------------
DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user`  (
  `id` int(0) NOT NULL AUTO_INCREMENT COMMENT '用户id,自动递增',
  `username` varchar(20) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL COMMENT '用户名',
  `truename` varchar(20) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL COMMENT '真实姓名',
  `password` varchar(25) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL COMMENT '密码',
  `email` varchar(50) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL COMMENT '邮箱',
  `intro` varchar(500) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL COMMENT '简介',
  `photo` varchar(500) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL COMMENT '头像',
  `added_time` datetime(0) NULL DEFAULT NULL COMMENT '添加日期',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 15 CHARACTER SET = gbk COLLATE = gbk_chinese_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sys_user
-- ----------------------------
INSERT INTO `sys_user` VALUES (2, 'zwt', '周伟涛', '123456', NULL, NULL, NULL, '2024-03-13 15:09:42');
INSERT INTO `sys_user` VALUES (3, 'qyc', '覃勇铖', '123456', '543@qq.com', '帅气逼人', 'd:/1.jpg', '2024-03-27 15:33:31');

-- ----------------------------
-- Table structure for sys_userrole
-- ----------------------------
DROP TABLE IF EXISTS `sys_userrole`;
CREATE TABLE `sys_userrole`  (
  `id` int(0) NOT NULL AUTO_INCREMENT COMMENT '主键id,自动递增',
  `user_id` int(0) NULL DEFAULT NULL COMMENT '用户id，字段值来源于用户表sys_user的id字段值',
  `role_id` int(0) NULL DEFAULT NULL COMMENT '角色id，字段值来源于角色表sys_role表的id字段值',
  `added_time` datetime(0) NULL DEFAULT NULL COMMENT '添加日期',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `user_id`(`user_id`) USING BTREE,
  INDEX `role_id`(`role_id`) USING BTREE,
  CONSTRAINT `sys_userrole_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `sys_user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `sys_userrole_ibfk_2` FOREIGN KEY (`role_id`) REFERENCES `sys_role` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 13 CHARACTER SET = gbk COLLATE = gbk_chinese_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sys_userrole
-- ----------------------------
INSERT INTO `sys_userrole` VALUES (12, 3, 1, '2024-12-08 13:29:00');

SET FOREIGN_KEY_CHECKS = 1;