SET NAMES utf8mb4;

SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `bookcategory`;

CREATE TABLE `bookcategory` (

`Category` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '类别',

PRIMARY KEY (`Category`) USING BTREE

) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

DROP TABLE IF EXISTS `booktable`;

CREATE TABLE `booktable` (

`bookid` int(10) NOT NULL AUTO_INCREMENT COMMENT '书号',

`category` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '类别',

`bookname` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '书名',

`author` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '作者',

`press` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '出版社',

`state` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '状态',

PRIMARY KEY (`bookid`) USING BTREE,

INDEX `category`(`category`) USING BTREE,

CONSTRAINT `booktable_ibfk_1` FOREIGN KEY (`category`) REFERENCES `bookcategory` (`Category`) ON DELETE NO ACTION ON UPDATE CASCADE

) ENGINE = InnoDB AUTO_INCREMENT = 19 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

DROP TABLE IF EXISTS `borrowrecords`;

CREATE TABLE `borrowrecords` (

`id` int(10) NOT NULL AUTO_INCREMENT COMMENT '借书序列',

`user` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户名',

`bookid` int(10) NOT NULL COMMENT '书号',

`bookname` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '书名',

`borrowtime` date NOT NULL COMMENT '借书时间',

`returntime` date NULL DEFAULT NULL COMMENT '还书时间',

`status` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '状态',

PRIMARY KEY (`id`) USING BTREE

) ENGINE = InnoDB AUTO_INCREMENT = 23 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

DROP TABLE IF EXISTS `usertable`;

CREATE TABLE `usertable` (

`user` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户名',

`studentid` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学号',

`name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '姓名',

`password` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '密码',

`admin` int(10) NOT NULL COMMENT '是否为管理员账户 1为管理员，0为普通用户，默认为0',

PRIMARY KEY (`user`) USING BTREE

) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

SET FOREIGN_KEY_CHECKS = 1;