pptfo7k6m/Resources/bms.sql

-- MySQL dump 10.13  Distrib 5.7.25, for Linux (x86_64)
--
-- Host: localhost    Database: bms
-- ------------------------------------------------------
-- Server version	5.7.25-0ubuntu0.16.04.2

/*!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 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!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' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `admin_info`
--

DROP TABLE IF EXISTS `admin_info`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `admin_info` (
  `admin_id` int(11) NOT NULL AUTO_INCREMENT,
  `admin_name` varchar(255) NOT NULL,
  `admin_email` varchar(100) NOT NULL,
  `register_time` datetime NOT NULL,
  `admin_phone` char(11) NOT NULL,
  PRIMARY KEY (`admin_id`),
  KEY `admin_email` (`admin_email`),
  CONSTRAINT `admin_info_ibfk_1` FOREIGN KEY (`admin_email`) REFERENCES `login_info` (`user_email`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `admin_info`
--

LOCK TABLES `admin_info` WRITE;
/*!40000 ALTER TABLE `admin_info` DISABLE KEYS */;
INSERT INTO `admin_info` VALUES (1,'猪八戒','zhubajie@gmail.com','2018-10-02 18:23:29','15347370392'),(2,'唐三藏','tangsanzang@gmail.com','2018-10-02 22:38:14','15347370392'),(3,'孙悟空','monkeyKing@gmail.com','2018-10-02 23:51:31','15347370392'),(4,'沙悟净','shawujing@gmail.com','2018-10-06 08:24:50','12345678920');
/*!40000 ALTER TABLE `admin_info` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `announcement`
--

DROP TABLE IF EXISTS `announcement`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `announcement` (
  `announcement_id` int(11) NOT NULL AUTO_INCREMENT,
  `announcement_title` varchar(255) NOT NULL,
  `announcement_content` text NOT NULL,
  PRIMARY KEY (`announcement_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `announcement`
--

LOCK TABLES `announcement` WRITE;
/*!40000 ALTER TABLE `announcement` DISABLE KEYS */;
INSERT INTO `announcement` VALUES (1,'欢迎使用图书管理系统','欢迎欢迎\\n热烈欢迎'),(2,'通报批评','giao\n批评！\n批评！！\n批评！！！\n\n');
/*!40000 ALTER TABLE `announcement` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `books`
--

DROP TABLE IF EXISTS `books`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `books` (
  `book_id` int(11) NOT NULL AUTO_INCREMENT,
  `book_name` varchar(255) NOT NULL,
  `book_ISBN` varchar(13) NOT NULL,
  `book_price` float(12,2) NOT NULL,
  `book_pages` int(11) NOT NULL,
  `book_author` varchar(255) DEFAULT NULL,
  `book_press` varchar(255) DEFAULT NULL,
  `classify_id` int(11) NOT NULL,
  `book_num` int(11) NOT NULL,
  PRIMARY KEY (`book_id`),
  KEY `classify_id` (`classify_id`),
  CONSTRAINT `books_ibfk_1` FOREIGN KEY (`classify_id`) REFERENCES `books_classify` (`classify_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `books`
--

LOCK TABLES `books` WRITE;
/*!40000 ALTER TABLE `books` DISABLE KEYS */;
INSERT INTO `books` VALUES (1,'西游记','9787020089864',88.70,480,'吴承恩','机械工业出版社',12,0),(4,'水浒传','9787020089863',88.60,450,'施耐庵','机械工业出版社',12,9),(5,'三国演义','9787020089861',98.40,860,'罗贯中','机械工业出版社',12,10),(6,'新华字典','9787020089860',78.50,2018,'新华辞书社','商务印书馆',11,9),(7,'内科学','1452147456321',89.60,650,'阿花','教育出版社',20,10),(8,'马克思主义基本原理','1554123654125',66.50,123,'马克思','教育出版社',1,10);
/*!40000 ALTER TABLE `books` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `books_classify`
--

DROP TABLE IF EXISTS `books_classify`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `books_classify` (
  `classify_id` int(11) NOT NULL AUTO_INCREMENT,
  `classify_name` varchar(255) NOT NULL,
  PRIMARY KEY (`classify_id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `books_classify`
--

LOCK TABLES `books_classify` WRITE;
/*!40000 ALTER TABLE `books_classify` DISABLE KEYS */;
INSERT INTO `books_classify` VALUES (1,'马列主义'),(5,'哲学、宗教'),(6,'社会科学总论'),(7,'政治、法律'),(8,'军事'),(9,'经济'),(10,'文化、科学、教育、体育'),(11,'语言、文字'),(12,'文学'),(13,'艺术'),(14,'历史、地理'),(15,'综合性图书'),(16,'自然科学总论'),(17,'数理科学和化学'),(18,'天文学、地球科学'),(19,'生物科学'),(20,'医药、卫生'),(21,'农业科学'),(22,'工业技术'),(23,'交通运输'),(24,'航空、航天'),(25,'环境科学、安全科学');
/*!40000 ALTER TABLE `books_classify` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `borrow_books`
--

DROP TABLE IF EXISTS `borrow_books`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `borrow_books` (
  `borrow_id` int(11) NOT NULL AUTO_INCREMENT,
  `reader_id` int(11) NOT NULL,
  `book_id` int(11) NOT NULL,
  `borrow_time` datetime NOT NULL,
  PRIMARY KEY (`borrow_id`),
  KEY `reader_id` (`reader_id`),
  KEY `book_id` (`book_id`),
  CONSTRAINT `borrow_books_ibfk_1` FOREIGN KEY (`reader_id`) REFERENCES `reader_info` (`reader_id`),
  CONSTRAINT `borrow_books_ibfk_2` FOREIGN KEY (`book_id`) REFERENCES `books` (`book_id`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `borrow_books`
--

LOCK TABLES `borrow_books` WRITE;
/*!40000 ALTER TABLE `borrow_books` DISABLE KEYS */;
INSERT INTO `borrow_books` VALUES (21,1,4,'2018-10-06 15:14:42'),(22,1,5,'2017-01-01 15:15:04'),(23,1,6,'2017-01-07 10:50:01'),(24,1,1,'2018-10-07 10:50:10'),(25,2,1,'2018-10-07 14:25:01'),(26,2,4,'2018-10-07 14:25:08');
/*!40000 ALTER TABLE `borrow_books` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `compensation`
--

DROP TABLE IF EXISTS `compensation`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `compensation` (
  `compensation_id` int(11) NOT NULL AUTO_INCREMENT,
  `reader_id` int(11) NOT NULL,
  `book_id` int(11) NOT NULL,
  `amount` float(12,2) NOT NULL,
  `compensation_time` datetime NOT NULL,
  PRIMARY KEY (`compensation_id`),
  KEY `reader_id` (`reader_id`),
  KEY `book_id` (`book_id`),
  CONSTRAINT `compensation_ibfk_1` FOREIGN KEY (`reader_id`) REFERENCES `reader_info` (`reader_id`),
  CONSTRAINT `compensation_ibfk_2` FOREIGN KEY (`book_id`) REFERENCES `books` (`book_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `compensation`
--

LOCK TABLES `compensation` WRITE;
/*!40000 ALTER TABLE `compensation` DISABLE KEYS */;
/*!40000 ALTER TABLE `compensation` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `login_info`
--

DROP TABLE IF EXISTS `login_info`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `login_info` (
  `user_email` varchar(100) NOT NULL,
  `user_password` varchar(255) NOT NULL,
  `user_type` varchar(255) NOT NULL,
  PRIMARY KEY (`user_email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `login_info`
--

LOCK TABLES `login_info` WRITE;
/*!40000 ALTER TABLE `login_info` DISABLE KEYS */;
INSERT INTO `login_info` VALUES ('1304831372@qq.com','111','reader'),('candice@qq.com','1586321','reader'),('corwin@qq.com','1586321','reader'),('monkeyKing@gmail.com','1586321','admin'),('root@gmail.com','1586321','root'),('sbaby@qq.com','1586321','reader'),('shawujing@gmail.com','1586321','admin'),('tangsanzang@gmail.com','1586321','admin'),('zhubajie@gmail.com','1586321','admin');
/*!40000 ALTER TABLE `login_info` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `overdue_fine`
--

DROP TABLE IF EXISTS `overdue_fine`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `overdue_fine` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `reader_id` int(11) NOT NULL,
  `book_id` int(11) NOT NULL,
  `days_overdue` int(11) NOT NULL,
  `money_overdue` float(12,2) NOT NULL,
  `pay_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `reader_id` (`reader_id`),
  KEY `book_id` (`book_id`),
  CONSTRAINT `overdue_fine_ibfk_1` FOREIGN KEY (`reader_id`) REFERENCES `reader_info` (`reader_id`),
  CONSTRAINT `overdue_fine_ibfk_2` FOREIGN KEY (`book_id`) REFERENCES `books` (`book_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `overdue_fine`
--

LOCK TABLES `overdue_fine` WRITE;
/*!40000 ALTER TABLE `overdue_fine` DISABLE KEYS */;
INSERT INTO `overdue_fine` VALUES (8,1,5,584,58.00,'2018-10-07 08:50:50');
/*!40000 ALTER TABLE `overdue_fine` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `reader_info`
--

DROP TABLE IF EXISTS `reader_info`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `reader_info` (
  `reader_id` int(11) NOT NULL AUTO_INCREMENT,
  `reader_name` varchar(255) NOT NULL,
  `reader_email` varchar(100) NOT NULL,
  `reader_sex` char(2) NOT NULL,
  `reader_phone` char(11) NOT NULL,
  `reader_birthday` date DEFAULT NULL,
  `reader_IDnumber` char(18) NOT NULL,
  `register_time` datetime NOT NULL,
  `expire_time` datetime NOT NULL,
  PRIMARY KEY (`reader_id`),
  KEY `reader_email` (`reader_email`),
  CONSTRAINT `reader_info_ibfk_1` FOREIGN KEY (`reader_email`) REFERENCES `login_info` (`user_email`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `reader_info`
--

LOCK TABLES `reader_info` WRITE;
/*!40000 ALTER TABLE `reader_info` DISABLE KEYS */;
INSERT INTO `reader_info` VALUES (1,'张三','sbaby@qq.com','男','12345678910','1998-08-08','123456567896541235','2017-08-08 00:00:00','2022-10-09 00:00:00'),(2,'李四','corwin@qq.com','男','15474589632','1998-08-08','452144569874585213','2018-10-04 15:45:17','2018-10-04 15:45:17'),(5,'王五','candice@qq.com','女','15874589654','1998-08-08','452147458965412541','2018-10-06 09:44:06','2019-04-06 09:44:06');
/*!40000 ALTER TABLE `reader_info` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `return_books`
--

DROP TABLE IF EXISTS `return_books`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `return_books` (
  `return_id` int(11) NOT NULL AUTO_INCREMENT,
  `borrow_id` int(11) NOT NULL,
  `reader_id` int(11) NOT NULL,
  `book_id` int(11) NOT NULL,
  `return_time` datetime NOT NULL,
  PRIMARY KEY (`return_id`),
  KEY `reader_id` (`reader_id`),
  KEY `book_id` (`book_id`),
  KEY `borrow_id` (`borrow_id`),
  CONSTRAINT `return_books_ibfk_1` FOREIGN KEY (`reader_id`) REFERENCES `reader_info` (`reader_id`),
  CONSTRAINT `return_books_ibfk_2` FOREIGN KEY (`book_id`) REFERENCES `books` (`book_id`),
  CONSTRAINT `return_books_ibfk_3` FOREIGN KEY (`borrow_id`) REFERENCES `borrow_books` (`borrow_id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `return_books`
--

LOCK TABLES `return_books` WRITE;
/*!40000 ALTER TABLE `return_books` DISABLE KEYS */;
INSERT INTO `return_books` VALUES (16,21,1,4,'2018-10-06 15:14:49'),(17,22,1,5,'2018-10-07 08:50:52');
/*!40000 ALTER TABLE `return_books` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `root_info`
--

DROP TABLE IF EXISTS `root_info`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `root_info` (
  `root_id` int(11) NOT NULL AUTO_INCREMENT,
  `root_name` varchar(255) NOT NULL,
  `root_email` varchar(100) NOT NULL,
  `root_phone` char(11) NOT NULL,
  PRIMARY KEY (`root_id`),
  KEY `root_email` (`root_email`),
  CONSTRAINT `root_info_ibfk_1` FOREIGN KEY (`root_email`) REFERENCES `login_info` (`user_email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `root_info`
--

LOCK TABLES `root_info` WRITE;
/*!40000 ALTER TABLE `root_info` DISABLE KEYS */;
/*!40000 ALTER TABLE `root_info` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!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 */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2019-04-24 11:30:13