/*
 Navicat MySQL Data Transfer

 Source Server         : mylink
 Source Server Type    : MySQL
 Source Server Version : 80022
 Source Host           : localhost:3306
 Source Schema         : librarydb

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

 Date: 07/11/2020 11:55:03
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for 借阅表
-- ----------------------------
DROP TABLE IF EXISTS `借阅表`;
CREATE TABLE `借阅表`  (
  `借阅号` int(0) NOT NULL AUTO_INCREMENT,
  `条码` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `读者编号` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `借阅日期` date NULL DEFAULT NULL,
  `还书日期` date NULL DEFAULT NULL,
  `借阅状态` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`借阅号`) USING BTREE,
  INDEX `读者编号`(`读者编号`) USING BTREE,
  INDEX `条码`(`条码`) USING BTREE,
  CONSTRAINT `借阅表_ibfk_1` FOREIGN KEY (`读者编号`) REFERENCES `读者表` (`读者编号`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `借阅表_ibfk_2` FOREIGN KEY (`条码`) REFERENCES `库存表` (`条码`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for 图书表
-- ----------------------------
DROP TABLE IF EXISTS `图书表`;
CREATE TABLE `图书表`  (
  `书号` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `书名` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `类别` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `作者` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `出版社` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `单价` float(5, 2) NULL DEFAULT NULL,
  `数量` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`书号`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for 库存表
-- ----------------------------
DROP TABLE IF EXISTS `库存表`;
CREATE TABLE `库存表`  (
  `条码` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `书号` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `位置` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `库存状态` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`条码`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for 读者类型表
-- ----------------------------
DROP TABLE IF EXISTS `读者类型表`;
CREATE TABLE `读者类型表`  (
  `类别号` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `类名` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `可借数量` int(0) NULL DEFAULT NULL,
  `可借天数` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`类别号`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for 读者表
-- ----------------------------
DROP TABLE IF EXISTS `读者表`;
CREATE TABLE `读者表`  (
  `读者编号` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `姓名` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `类别号` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `单位` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `有效性` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`读者编号`) USING BTREE,
  INDEX `类别号`(`类别号`) USING BTREE,
  CONSTRAINT `读者表_ibfk_1` FOREIGN KEY (`类别号`) REFERENCES `读者类型表` (`类别号`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;