mysql.txt

现阶段很多图书租赁店仍然使用手工方式管理

mysql> create table CompanyInfo
    -> (name char(20) primary key,
    -> phone char(15),
    -> address char(50)
    -> );
Query OK, 0 rows affected (0.02 sec)
CREATE TABLE `bookrentsystem`.`memberinfo` (
  `id` CHAR(20) NOT NULL,
  `name` CHAR(20) NOT NULL,
  `py` CHAR(10) NOT NULL,
  `sex` CHAR(2) NOT NULL,
  `phone` CHAR(15) NOT NULL,
  `address` CHAR(50),
  `deposi` INTEGER UNSIGNED ZEROFILL NOT NULL COMMENT '押金',
  `remainmoney` INTEGER UNSIGNED NOT NULL COMMENT '剩余租金',
  `createdate` DATETIME NOT NULL,
  PRIMARY KEY (`id`)
)

CREATE TABLE `bookrentsystem`.`memberpayinfo` (
  `id` INTEGER UNSIGNED NOT NULL DEFAULT NULL AUTO_INCREMENT,
  `memberid` CHAR(20) NOT NULL,
  `paydate` DATETIME NOT NULL,
  `paytype` CHAR(4) NOT NULL COMMENT '租金,押金',
  `payaccont` INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY (`id`)
)

CREATE TABLE memberpayinfo(
id INTEGER PRIMARY KEY,
memberid CHAR(20) NOT NULL,
paydate DATETIME NOT NULL,
paytype CHAR(4) CHECK(PAYTYPE IN ('租金','押金')),
payaccont INTEGER NOT NULL,
FOREIGN KEY (id) REFERENCES memberinfo(id));

CREATE TABLE `bookrentsystem`.`bookinfo` (
  `id` CHAR(20) NOT NULL,
  `name` CHAR(20) NOT NULL,
  `py` CHAR(10) NOT NULL,
  `type` INTEGER UNSIGNED NOT NULL,
  `press` INTEGER UNSIGNED NOT NULL,
  `state` BOOLEAN NOT NULL,
  `lastrentmember` CHAR(20) NOT NULL,
  `buyprice` INTEGER UNSIGNED NOT NULL,
  `rentprice` INTEGER UNSIGNED,
  PRIMARY KEY (`id`),
  CONSTRAINT `FK_bookinfo_1` FOREIGN KEY `FK_bookinfo_1` (`lastrentmember`)
    REFERENCES `memberinfo` (`id`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT,
  CONSTRAINT `FK_bookinfo_2` FOREIGN KEY `FK_bookinfo_2` (`press`)
    REFERENCES `bookpress` (`id`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT,
  CONSTRAINT `FK_bookinfo_3` FOREIGN KEY `FK_bookinfo_3` (`type`)
    REFERENCES `booktype` (`id`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
)
ENGINE = InnoDB;



CREATE TABLE `bookrentsystem`.`rentinfo` (
  `memberid` CHAR(20) NOT NULL,
  `bookid` CHAR(20) NOT NULL,
  `rentdate` DATETIME NOT NULL,
  `restoredate` DATETIME NOT NULL,
  PRIMARY KEY (`memberid`, `bookid`),
  CONSTRAINT `FK_rentinfo_1` FOREIGN KEY `FK_rentinfo_1` (`memberid`)
    REFERENCES `memberinfo` (`id`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT,
  CONSTRAINT `FK_rentinfo_2` FOREIGN KEY `FK_rentinfo_2` (`bookid`)
    REFERENCES `bookinfo` (`id`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
)
ENGINE = InnoDB;


CREATE TABLE `bookrentsystem`.`employeeinfo` (
  `id` CHAR(20) NOT NULL,
  `name` CHAR(20) NOT NULL,
  `sex` CHAR(2) NOT NULL,
  `phone` CHAR(15) NOT NULL,
  `access` BOOLEAN NOT NULL COMMENT '权限（true为老板、false为员工）',
  `password` CHAR(20) NOT NULL,
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB;
ALTER TABLE `bookrentsystem`.`bookinfo` MODIFY COLUMN `py` CHAR(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL COMMENT '书名的拼音',
 MODIFY COLUMN `press` INTEGER UNSIGNED NOT NULL COMMENT '出版商',
 MODIFY COLUMN `state` BOOLEAN NOT NULL COMMENT '是否可租',
 MODIFY COLUMN `lastrentmember` CHAR(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL COMMENT '最后租借会员',
 MODIFY COLUMN `buyprice` INTEGER UNSIGNED NOT NULL COMMENT '书的价格',
 MODIFY COLUMN `rentprice` INTEGER UNSIGNED DEFAULT NULL COMMENT '出租价格';












CREATE TABLE `bookrentsystem`.`softwaremodule` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` CHAR(20) NOT NULL,
  `access` BOOLEAN NOT NULL COMMENT 'true仅老板可以访问、false都可以访问',
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB;