-- Table structure for table `book_info`--DROP TABLE IF EXISTS `book_info`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREA TE TABLE `book_info` (`b_id` char(50) NOT NULL,`b_name` char(50) NOT NULL,`b_author` char(50) DEFAULT NULL,`b_publish` char(50) DEFAULT NULL,`b_price` decimal(5,2) NOT NULL,`b_quantity` int(3) NOT NULL,`b_remain` int(3) DEFAULT NULL,PRIMARY KEY (`b_id`),KEY `publishname` (`b_publish`)) ENGINE=InnoDB DEFAULT CHARSET=gbk;/*!40101 SET character_set_client = @saved_cs_client */;---- Dumping data for table `book_info`--LOCK TABLES `book_info` WRITE;/*!40000 ALTER TABLE `book_info` DISABLE KEYS */;INSERT INTO `book_info` V ALUES ('789','信号系统','吴大正','高等教育','37.40',20,20),('870','操作系统','Peter Baer','高等教育','60.00',5,4),('978','MySQL','Ben Forta','人民邮电','39.00',10,10);/*!40000 ALTER TABLE `book_info` ENABLE KEYS */;UNLOCK TABLES;/*!50003 SET @saved_cs_client = @@character_set_client */ ;/*!50003 SET @saved_cs_results = @@character_set_results */ ;/*!50003 SET @saved_col_connection = @@collation_connection */ ;/*!50003 SET character_set_client = gbk */ ;/*!50003 SET character_set_results = gbk */ ;/*!50003 SET collation_connection = gbk_chinese_ci */ ;/*!50003 SET @saved_sql_mode = @@sql_mode */ ;/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREA TE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;;/*!50003 CREA TE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 trigger clear after insert on book_info for each rowbegindelete from borrow_infowhere datediff(r_date,b_date)>=3;end */;;DELIMITER ;/*!50003 SET sql_mode = @saved_sql_mode */ ;/*!50003 SET character_set_client = @saved_cs_client */ ;/*!50003 SET character_set_results = @saved_cs_results */ ;/*!50003 SET collation_connection = @saved_col_connection */ ;---- Table structure for table `borrow_info`--DROP TABLE IF EXISTS `borrow_info`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREA TE TABLE `borrow_info` (`id` int(11) NOT NULL AUTO_INCREMENT,`r_num` int(11) NOT NULL,`b_id` char(50) NOT NULL,`quantity` char(2) DEFAULT '1',`b_date` char(50) NOT NULL,`r_date` char(50) DEFAULT NULL,`damage` char(2) DEFAULT '否',PRIMARY KEY (`id`),KEY `fk_borrow_info_reader_info` (`r_num`),KEY `fk_borrow_info_book_info` (`b_id`),CONSTRAINT `fk_borrow_info_book_info` FOREIGN KEY(`b_id`) REFERENCES `book_info` (`b_id`),CONSTRAINT `fk_borrow_info_reader_info` FOREIGN KEY (`r_num`) REFERENCES `reader_info` (`r_num`)) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=gbk;/*!40101 SET character_set_client = @saved_cs_client */;---- Dumping data for table `borrow_info`--LOCK TABLES `borrow_info` WRITE;/*!40000 ALTER TABLE `borrow_info` DISABLE KEYS */;INSERT INTO `borrow_info` V ALUES (2,10087,'789','2','2010-12-26','2010-12-26','否'),(3,10087,'978','1','2010-12-26','2010-12-26','否'),(7,10088,'870','1','2010-10-26',NULL,'否');/*!40000 ALTER TABLE `borrow_info` ENABLE KEYS */;UNLOCK TABLES;/*!50003 SET @saved_cs_client = @@character_set_client */ ;/*!50003 SET @saved_cs_results = @@character_set_results */ ;/*!50003 SET @saved_col_connection = @@collation_connection */ ;/*!50003 SET character_set_client = gbk */ ;/*!50003 SET character_set_results = gbk */ ;/*!50003 SET collation_connection = gbk_chinese_ci */ ;/*!50003 SET @saved_sql_mode = @@sql_mode */ ;/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREA TE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;;/*!50003 CREA TE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 trigger bookborrow after insert on borrow_info for each rowbeginupdate book_infoset b_remain=b_remain-NEW.quantitywhere b_id=NEW.b_id;end */;;DELIMITER ;/*!50003 SET sql_mode = @saved_sql_mode */ ;/*!50003 SET character_set_client = @saved_cs_client */ ;/*!50003 SET character_set_results = @saved_cs_results */ ;/*!50003 SET collation_connection = @saved_col_connection */ ;/*!50003 SET @saved_cs_client = @@character_set_client */ ;/*!50003 SET @saved_cs_results = @@character_set_results */ ;/*!50003 SET @saved_col_connection = @@collation_connection */ ;/*!50003 SET character_set_client = gbk */ ;/*!50003 SET character_set_results = gbk */ ;/*!50003 SET collation_connection = gbk_chinese_ci */ ;/*!50003 SET @saved_sql_mode = @@sql_mode */ ;/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREA TE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;;/*!50003 CREA TE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 trigger bookreturn after update on borrow_info for each rowbegindeclare c int;update book_infoset b_remain=b_remain+NEW.quantitywhere b_id-NEW.b_id;set c=NEW.r_num;call penalty(c);end */;;DELIMITER ;/*!50003 SET sql_mode = @saved_sql_mode */ ;/*!50003 SET character_set_client = @saved_cs_client */ ;/*!50003 SET character_set_results = @saved_cs_results */ ;/*!50003 SET collation_connection = @saved_col_connection */ ;---- Table structure for table `family_member`--DROP TABLE IF EXISTS `family_member`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREA TE TABLE `family_member` (`r_num` int(11) NOT NULL,`relation` char(4) NOT NULL,`name` char(5) NOT NULL,`company` char(10) DEFAULT NULL,`job` char(6) DEFAULT NULL,`tel` char(50) DEFAULT NULL,PRIMARY KEY (`relation`,`name`)) ENGINE=InnoDB DEFAULT CHARSET=gbk;/*!40101 SET character_set_client = @saved_cs_client */;---- Dumping data for table `family_member`--LOCK TABLES `family_member` WRITE;/*!40000 ALTER TABLE `family_member` DISABLE KEYS */;INSERT INTO `family_member` V ALUES (10088,'爸爸','李四','思科','研发','87825'),(10087,'妈妈','翠花','锐捷','会计','45432');/*!40000 ALTER TABLE `family_member` ENABLE KEYS */;UNLOCK TABLES;---- Temporary table structure for view `notreturn`--DROP TABLE IF EXISTS `notreturn`;/*!50001 DROP VIEW IF EXISTS `notreturn`*/;SET @saved_cs_client = @@character_set_client;SET character_set_client = utf8;/*!50001 CREA TE TABLE `notreturn` (`r_num` int(11),`r_name` char(5),`r_tel` char(50),`b_date` char(50),`r_date` char(50),`b_name` char(50),`b_price` decimal(5,2),`quantity` char(2)) ENGINE=MyISAM */;SET character_set_client = @saved_cs_client;---- Table structure for table `publish_info`--DROP TABLE IF EXISTS `publish_info`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREA TE TABLE `publish_info` (`p_id` int(11) NOT NULL,`p_name` char(50) NOT NULL,`P_address` char(50) DEFAULT NULL,`P_charger` char(50) DEFAULT NULL,`p_tel` char(50) DEFAULT NULL,`p_zip` char(50) DEFAULT NULL,PRIMARY KEY (`p_id`),KEY `publishname` (`p_name`),CONSTRAINT `fk_publish_info_book_info` FOREIGN KEY(`p_name`) REFERENCES `book_info` (`b_publish`)) ENGINE=InnoDB DEFAULT CHARSET=gbk;/*!40101 SET character_set_client = @saved_cs_client */;---- Dumping data for table `publish_info`--LOCK TABLES `publish_info` WRITE;/*!40000 ALTER TABLE `publish_info` DISABLE KEYS */;INSERT INTO `publish_info` V ALUES (19112,'人民邮电','北京市朝阳区','刘晓霞','88593820','100021'),(83419,'高等教育','北京市海淀区','郑扣根','87825848','100001');/*!40000 ALTER TABLE `publish_info` 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 */;CREA TE TABLE `reader_info` (`r_num` int(11) NOT NULL AUTO_INCREMENT,`r_job` char(4) NOT NULL,`r_name` char(5) NOT NULL,`r_tel` char(50) NOT NULL,`r_id` char(50) NOT NULL,`r_birth` char(50) DEFAULT NULL,`r_address` char(10) DEFAULT NULL,`r_zip` char(10) DEFAULT NULL,PRIMARY KEY (`r_num`)) ENGINE=InnoDB AUTO_INCREMENT=10089 DEFAULT CHARSET=gbk;/*!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` V ALUES (10087,'学生','王五','43231','51073','1991-2-21','xxxx','610021'),(10088,'学生','张三','12345','51010','1990-7-11','xxxx','610000');/*!40000 ALTER TABLE `reader_info` ENABLE KEYS */;UNLOCK TABLES;/*!50003 SET @saved_cs_client = @@character_set_client */ ;/*!50003 SET @saved_cs_results = @@character_set_results */ ;/*!50003 SET @saved_col_connection = @@collation_connection */ ;/*!50003 SET character_set_client = gbk */ ;/*!50003 SET character_set_results = gbk */ ;/*!50003 SET collation_connection = gbk_chinese_ci */ ;/*!50003 SET @saved_sql_mode = @@sql_mode */ ;/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREA TE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;;/*!50003 CREA TE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 trigger dropreader before delete on reader_info for each rowbegindelete from family_member where r_num=OLD.r_num;delete from borrow_info where r_num=OLD.r_num and r_date!='NULL';end */;;DELIMITER ;/*!50003 SET sql_mode = @saved_sql_mode */ ;/*!50003 SET character_set_client = @saved_cs_client */ ;/*!50003 SET character_set_results = @saved_cs_results */ ;/*!50003 SET collation_connection = @saved_col_connection */ ;---- Final view structure for view `notreturn`--/*!50001 DROP TABLE IF EXISTS `notreturn`*/;/*!50001 DROP VIEW IF EXISTS `notreturn`*/;/*!50001 SET @saved_cs_client = @@character_set_client */;/*!50001 SET @saved_cs_results = @@character_set_results */;/*!50001 SET @saved_col_connection = @@collation_connection */;/*!50001 SET character_set_client = gbk */;/*!50001 SET character_set_results = gbk */;/*!50001 SET collation_connection = gbk_chinese_ci */;/*!50001 CREA TE ALGORITHM=UNDEFINED *//*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER *//*!50001 VIEW `notreturn` AS select `reader_info`.`r_num` AS `r_num`,`reader_info`.`r_name` AS `r_name`,`reader_info`.`r_tel` AS `r_tel`,`borrow_info`.`b_date` AS `b_date`,`borrow_info`.`r_date` AS `r_date`,`book_info`.`b_name` AS `b_name`,`book_info`.`b_price` AS `b_price`,`borrow_info`.`quantity` AS `quantity` from ((`reader_info` join `book_info`) join `borrow_info`) where ((`reader_info`.`r_num` = `borrow_info`.`r_num`) and (`book_info`.`b_id` = `borrow_info`.`b_id`) and isnull(`borrow_info`.`r_date`)) order by `reader_info`.`r_num` */;/*!50001 SET character_set_client = @saved_cs_client */;/*!50001 SET character_set_results = @saved_cs_results */;/*!50001 SET collation_connection = @saved_col_connection */;/*!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 COLLA TION_CONNECTION=@OLD_COLLA TION_CONNECTION */;/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;-- Dump completed on 2010-12-27 16:48:30。