ssh搭建图书管理系统之数据库编写

最近实验课要写一个图书馆管理系统，因为太忙所以不会很详细地一步步地记录搭建的全过程。

做完这个系统你可能为什么要这样做那样做，因为老师要求的谢谢。

1.数据表的设计，各个字段都是字面意思。

2.详细设计。

-- MySQL Script generated by MySQL Workbench
-- Sun May 14 00:38:02 2017
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering


SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';


-- -----------------------------------------------------
-- Schema libraryms
-- -----------------------------------------------------


-- -----------------------------------------------------
-- Schema libraryms
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `libraryms` DEFAULT CHARACTER SET utf8 ;
USE `libraryms` ;


-- -----------------------------------------------------
-- Table `libraryms`.`user`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `libraryms`.`user` (
  `admin` TINYINT NOT NULL,
  `user_name` VARCHAR(8) NOT NULL,
  `user_pass` VARCHAR(16) NOT NULL,
  `user_unit` VARCHAR(20) NULL COMMENT '用户所属院系',
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `loadcount` INT NULL DEFAULT 0 COMMENT '登录次数',
  PRIMARY KEY (`id`))
ENGINE = InnoDB;




-- -----------------------------------------------------
-- Table `libraryms`.`press`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `libraryms`.`press` (
  `name` VARCHAR(15) NOT NULL,
  `phone` VARCHAR(11) NULL,
  `postcode` VARCHAR(6) NULL COMMENT '出版社编号',
  `location` VARCHAR(20) NULL,
  PRIMARY KEY (`name`))
ENGINE = InnoDB;




-- -----------------------------------------------------
-- Table `libraryms`.`bigtype`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `libraryms`.`bigtype` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `typename` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
COMMENT = '图书大类';




-- -----------------------------------------------------
-- Table `libraryms`.`smalltype`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `libraryms`.`smalltype` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `typename` VARCHAR(45) NULL,
  `bigtype_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_smalltype_bigtype1_idx` (`bigtype_id` ASC),
  CONSTRAINT `fk_smalltype_bigtype1`
    FOREIGN KEY (`bigtype_id`)
    REFERENCES `libraryms`.`bigtype` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = '图书小类';




-- -----------------------------------------------------
-- Table `libraryms`.`books`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `libraryms`.`books` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `book_name` VARCHAR(20) NOT NULL,
  `book_num` VARCHAR(45) NOT NULL COMMENT '图书编号',
  `book_position` VARCHAR(10) NOT NULL,
  `press_name` VARCHAR(15) NOT NULL,
  `smalltype_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_books_press1_idx` (`press_name` ASC),
  UNIQUE INDEX `book_name_UNIQUE` (`book_name` ASC),
  INDEX `fk_books_smalltype1_idx` (`smalltype_id` ASC),
  CONSTRAINT `fk_books_press1`
    FOREIGN KEY (`press_name`)
    REFERENCES `libraryms`.`press` (`name`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_books_smalltype1`
    FOREIGN KEY (`smalltype_id`)
    REFERENCES `libraryms`.`smalltype` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;




-- -----------------------------------------------------
-- Table `libraryms`.`borrow`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `libraryms`.`borrow` (
  `borrow_date` DATETIME(4) NOT NULL,
  `return_date` DATETIME(4) NULL,
  `book_id` INT UNSIGNED NOT NULL,
  `user_id` INT UNSIGNED NOT NULL,

//千万别想着拿 `book_id`和 `user_id`作为联合主键呀这是不行的。
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  INDEX `fk_borrow_books_idx` (`book_id` ASC),
  INDEX `fk_borrow_user1_idx` (`user_id` ASC),
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_borrow_books`
    FOREIGN KEY (`book_id`)
    REFERENCES `libraryms`.`books` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_borrow_user1`
    FOREIGN KEY (`user_id`)
    REFERENCES `libraryms`.`user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;




SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;