最近实验课要写一个图书馆管理系统,因为太忙所以不会很详细地一步步地记录搭建的全过程。
做完这个系统你可能为什么要这样做那样做,因为老师要求的谢谢。
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;