bookrentsystem.sql

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

-- MySQL Administrator dump 1.4
--
-- ------------------------------------------------------
-- Server version	5.0.16-nt


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;


--
-- Create schema bookrentsystem
--

CREATE DATABASE IF NOT EXISTS bookrentsystem;
USE bookrentsystem;

--
-- Definition of table `bookinfo`
--

DROP TABLE IF EXISTS `bookinfo`;
CREATE TABLE `bookinfo` (
  `id` char(20) NOT NULL,
  `name` char(20) character set utf8 NOT NULL,
  `py` char(10) NOT NULL COMMENT '书名的拼音',
  `type` int(10) unsigned NOT NULL,
  `press` int(10) unsigned NOT NULL COMMENT '出版商',
  `state` tinyint(1) NOT NULL COMMENT '是否可租',
  `lastrentmember` char(20) default NULL COMMENT '最后租借会员',
  `buyprice` double NOT NULL COMMENT '书的价格',
  `rentprice` double NOT NULL COMMENT '出租价格',
  `times` int(10) unsigned default '0' COMMENT '出租次数',
  `joindate` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `FK_bookinfo_1` (`lastrentmember`),
  KEY `FK_bookinfo_2` (`press`),
  KEY `FK_bookinfo_3` (`type`),
  CONSTRAINT `FK_bookinfo_1` FOREIGN KEY (`lastrentmember`) REFERENCES `memberinfo` (`id`),
  CONSTRAINT `FK_bookinfo_2` FOREIGN KEY (`press`) REFERENCES `bookpress` (`id`),
  CONSTRAINT `FK_bookinfo_3` FOREIGN KEY (`type`) REFERENCES `booktype` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `bookinfo`
--

/*!40000 ALTER TABLE `bookinfo` DISABLE KEYS */;
INSERT INTO `bookinfo` (`id`,`name`,`py`,`type`,`press`,`state`,`lastrentmember`,`buyprice`,`rentprice`,`times`,`joindate`) VALUES 
 ('1','1','1',1,1,1,NULL,1,1,1,'2007-06-28 17:08:30');
/*!40000 ALTER TABLE `bookinfo` ENABLE KEYS */;


--
-- Definition of table `bookpress`
--

DROP TABLE IF EXISTS `bookpress`;
CREATE TABLE `bookpress` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` char(20) character set utf8 NOT NULL,
  PRIMARY KEY  USING BTREE (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `bookpress`
--

/*!40000 ALTER TABLE `bookpress` DISABLE KEYS */;
INSERT INTO `bookpress` (`id`,`name`) VALUES 
 (1,'文学'),
 (2,'社科');
/*!40000 ALTER TABLE `bookpress` ENABLE KEYS */;


--
-- Definition of table `booktype`
--

DROP TABLE IF EXISTS `booktype`;
CREATE TABLE `booktype` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` char(20) character set utf8 NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

--
-- Dumping data for table `booktype`
--

/*!40000 ALTER TABLE `booktype` DISABLE KEYS */;
INSERT INTO `booktype` (`id`,`name`) VALUES 
 (1,'文学'),
 (2,'社科');
/*!40000 ALTER TABLE `booktype` ENABLE KEYS */;


--
-- Definition of table `companyinfo`
--

DROP TABLE IF EXISTS `companyinfo`;
CREATE TABLE `companyinfo` (
  `name` char(20) character set utf8 NOT NULL,
  `phone` char(15) NOT NULL,
  `address` char(50) character set utf8 default NULL,
  PRIMARY KEY  (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `companyinfo`
--

/*!40000 ALTER TABLE `companyinfo` DISABLE KEYS */;
/*!40000 ALTER TABLE `companyinfo` ENABLE KEYS */;


--
-- Definition of table `employeeinfo`
--

DROP TABLE IF EXISTS `employeeinfo`;
CREATE TABLE `employeeinfo` (
  `id` char(20) NOT NULL,
  `name` char(20) character set utf8 NOT NULL,
  `sex` char(2) character set utf8 NOT NULL,
  `phone` char(15) NOT NULL,
  `access` tinyint(1) NOT NULL COMMENT '权限（true为老板、false为员工）',
  `password` char(20) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `employeeinfo`
--

/*!40000 ALTER TABLE `employeeinfo` DISABLE KEYS */;
INSERT INTO `employeeinfo` (`id`,`name`,`sex`,`phone`,`access`,`password`) VALUES 
 ('111','222','女','135632431',0,'111111'),
 ('222','222','女','24353452345',1,'111111'),
 ('admin','被子','男','1223423423',1,'111111');
/*!40000 ALTER TABLE `employeeinfo` ENABLE KEYS */;


--
-- Definition of table `memberinfo`
--

DROP TABLE IF EXISTS `memberinfo`;
CREATE TABLE `memberinfo` (
  `id` char(20) NOT NULL,
  `name` char(20) character set utf8 NOT NULL,
  `py` char(10) NOT NULL,
  `sex` char(2) character set utf8 NOT NULL,
  `phone` char(15) NOT NULL,
  `address` char(50) default NULL,
  `deposi` int(10) unsigned zerofill NOT NULL COMMENT '押金',
  `remainmoney` int(10) unsigned NOT NULL COMMENT '剩余租金',
  `createdate` datetime NOT NULL,
  `state` tinyint(1) NOT NULL COMMENT '是否可用',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `memberinfo`
--

/*!40000 ALTER TABLE `memberinfo` DISABLE KEYS */;
INSERT INTO `memberinfo` (`id`,`name`,`py`,`sex`,`phone`,`address`,`deposi`,`remainmoney`,`createdate`,`state`) VALUES 
 ('1','1','1','ÄÐ','123123','1212',0000000321,22,'2007-06-28 16:58:42',1),
 ('11','dd','22','ÄÐ','21412423423','ddfas',0000009889,22,'2007-06-28 16:53:08',1);
/*!40000 ALTER TABLE `memberinfo` ENABLE KEYS */;


--
-- Definition of table `memberpayinfo`
--

DROP TABLE IF EXISTS `memberpayinfo`;
CREATE TABLE `memberpayinfo` (
  `id` int(11) NOT NULL,
  `memberid` char(20) NOT NULL,
  `paydate` datetime NOT NULL,
  `paytype` char(4) NOT NULL COMMENT '租金、押金',
  `payaccont` int(11) NOT NULL COMMENT '付款数额',
  PRIMARY KEY  (`id`),
  KEY `FK_memberpayinfo_1` (`memberid`),
  CONSTRAINT `FK_memberpayinfo_1` FOREIGN KEY (`memberid`) REFERENCES `memberinfo` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `memberpayinfo`
--

/*!40000 ALTER TABLE `memberpayinfo` DISABLE KEYS */;
/*!40000 ALTER TABLE `memberpayinfo` ENABLE KEYS */;


--
-- Definition of table `rentinfo`
--

DROP TABLE IF EXISTS `rentinfo`;
CREATE TABLE `rentinfo` (
  `memberid` char(20) NOT NULL,
  `bookid` char(20) NOT NULL,
  `rentdate` datetime NOT NULL,
  `restoredate` char(20) default NULL,
  PRIMARY KEY  USING BTREE (`memberid`,`bookid`,`rentdate`),
  KEY `FK_rentinfo_2` (`bookid`),
  CONSTRAINT `FK_rentinfo_1` FOREIGN KEY (`memberid`) REFERENCES `memberinfo` (`id`),
  CONSTRAINT `FK_rentinfo_2` FOREIGN KEY (`bookid`) REFERENCES `bookinfo` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `rentinfo`
--

/*!40000 ALTER TABLE `rentinfo` DISABLE KEYS */;
INSERT INTO `rentinfo` (`memberid`,`bookid`,`rentdate`,`restoredate`) VALUES 
 ('1','1','2007-06-28 17:08:54','2007-6-28 17:08:59');
/*!40000 ALTER TABLE `rentinfo` ENABLE KEYS */;


--
-- Definition of table `softwaremodule`
--

DROP TABLE IF EXISTS `softwaremodule`;
CREATE TABLE `softwaremodule` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` char(20) character set utf8 NOT NULL,
  `access` tinyint(1) NOT NULL COMMENT 'true仅老板可以访问、false都可以访问',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `softwaremodule`
--

/*!40000 ALTER TABLE `softwaremodule` DISABLE KEYS */;
/*!40000 ALTER TABLE `softwaremodule` ENABLE KEYS */;




/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;