计算机191-06.7z

/* SQLyog Ultimate v11.11 (32 bit) MySQL - 5.5.40 : Database - dpm ********************************************************************* */ /*!40101 SET NAMES utf8 */; /*!40101 SET SQL_MODE=''*/; /*!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 */; CREATE DATABASE /*!32312 IF NOT EXISTS*/`dpm` /*!40100 DEFAULT CHARACTER SET utf8 */; USE `dpm`; /*Table structure for table `buildinginfo` */ DROP TABLE IF EXISTS `buildinginfo`; CREATE TABLE `buildinginfo` ( `buildingId` varchar(10) NOT NULL, `buildingName` varchar(15) NOT NULL, `managerId` varchar(20) DEFAULT NULL, `maxStu` int(20) NOT NULL, `livedStu` int(20) NOT NULL, PRIMARY KEY (`buildingId`), KEY `managerId` (`managerId`), CONSTRAINT `buildinginfo_ibfk_1` FOREIGN KEY (`managerId`) REFERENCES `managerinfo` (`managerId`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*Data for the table `buildinginfo` */ insert into `buildinginfo`(`buildingId`,`buildingName`,`managerId`,`maxStu`,`livedStu`) values ('106','东山六社 ','101',222,200),('107','东山五社','103',222,200),('108','东山四社','101',222,200),('未知','未知','000',0,0); /*Table structure for table `dormassess` */ DROP TABLE IF EXISTS `dormassess`; CREATE TABLE `dormassess` ( `id` int(30) NOT NULL AUTO_INCREMENT, `buildingId` varchar(10) NOT NULL, `dormNum` varchar(10) NOT NULL, `lateGrade` int(10) NOT NULL, `tempGrade` int(10) NOT NULL, `violationGrade` int(10) NOT NULL, `sanitationGrade` int(10) NOT NULL, `dailyEva` int(10) NOT NULL, `generalEva` int(10) NOT NULL, `evaTime` datetime NOT NULL, UNIQUE KEY `id` (`id`), KEY `buildingId` (`buildingId`), CONSTRAINT `dormassess_ibfk_1` FOREIGN KEY (`buildingId`) REFERENCES `buildinginfo` (`buildingId`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; /*Data for the table `dormassess` */ insert into `dormassess`(`id`,`buildingId`,`dormNum`,`lateGrade`,`tempGrade`,`violationGrade`,`sanitationGrade`,`dailyEva`,`generalEva`,`evaTime`) values (1,'106','222',4,2,0,1,7,2,'2022-10-24 08:02:18'),(2,'106','222',1,0,1,1,3,1,'2022-10-27 06:24:00'); /*Table structure for table `exitbuilding` */ DROP TABLE IF EXISTS `exitbuilding`; CREATE TABLE `exitbuilding` ( `exitId` int(30) NOT NULL AUTO_INCREMENT, `exitHeader` varchar(20) NOT NULL, `exitContent` varchar(255) NOT NULL, `studentId` varchar(20) NOT NULL, `approval` varchar(10) NOT NULL, `referTime` datetime NOT NULL, UNIQUE KEY `exitId` (`exitId`), KEY `studentId` (`studentId`), CONSTRAINT `exitbuilding_ibfk_1` FOREIGN KEY (`studentId`) REFERENCES `studentinfo` (`studentId`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8; /*Data for the table `exitbuilding` */ insert into `exitbuilding`(`exitId`,`exitHeader`,`exitContent`,`studentId`,`approval`,`referTime`) values (16,'衣柜','棕色，正方形衣柜','1905010106','未审批','2022-10-27 06:24:58'),(17,'椅子','损坏的电竞椅','1905010106','未审批','2022-10-27 06:25:15'),(18,'饮水机','电源插头坏掉的饮水机','1905010106','未审批','2022-10-27 06:25:32'); /*Table structure for table `feeinfo` */ DROP TABLE IF EXISTS `feeinfo`; CREATE TABLE `feeinfo` ( `feeid` int(30) NOT NULL AUTO_INCREMENT, `feeHeader` varchar(20) NOT NULL, `feeContent` varchar(255) NOT NULL, `money` int(20) NOT NULL, `cutOffTime` datetime NOT NULL, UNIQUE KEY `feeid` (`feeid`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; /*Data for the table `feeinfo` */ insert into `feeinfo`(`feeid`,`feeHeader`,`feeContent`,`money`,`cutOffTime`) values (3,'更换窗户','更换铝合金窗户 价格xxx',22,'2022-10-09 09:43:00'); /*Table structure for table `lateback` */ DROP TABLE IF EXISTS `lateback`; CREATE TABLE `lateback` ( `id` int(30) NOT NULL AUTO_INCREMENT, `studentId` varchar(20) NOT NULL, `backTime` datetime NOT NULL, `backReason` varchar(255) NOT NULL, UNIQUE KEY `id` (`id`), KEY `studentId` (`studentId`), CONSTRAINT `lateback_ibfk_1` FOREIGN KEY (`studentId`) REFERENCES `studentinfo` (`studentId`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8; /*Data for the table `lateback` */ insert into `lateback`(`id`,`studentId`,`backTime`,`backReason`) values (3,'1905010106','2022-10-27 09:06:00','返校时出现堵车'),(4,'1905010106','2022-10-08 09:06:00','返校时出现汽车抛锚'),(5,'1905010106','2022-10-27 17:58:00','脚崴了 '),(6,'1905010105','2022-11-02 17:59:00','脚崴了\r\n '); /*Table structure for table `leaveapply` */ DROP TABLE IF EXISTS `leaveapply`; CREATE TABLE `leaveapply` ( `id` int(30) NOT NULL AUTO_INCREMENT, `studentId` varchar(20) NOT NULL, `cause` varchar(255) NOT NULL, `time` datetime NOT NULL, `approve` varchar(10) NOT NULL, UNIQUE KEY `id` (`id`), KEY `studentId` (`studentId`), CONSTRAINT `leaveapply_ibfk_1` FOREIGN KEY (`studentId`) REFERENCES `studentinfo` (`studentId`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8; /*Data for the table `leaveapply` */ insert into `leaveapply`(`id`,`studentId`,`cause`,`time`,`approve`) values (2,'1905010106','有事回家','2022-10-15 01:05:22','通过'),(6,'1905010106','出校购物','2022-10-17 10:01:28','通过'),(7,'1905010106','去医院看病','2022-10-17 10:22:58','待审批'),(8,'1905010106','出去玩','2022-10-19 09:17:46','不通过'),(9,'1905010106','有病就医','2022-10-19 10:28:14','待审批'),(10,'1905010106','出门就医','2022-10-20 09:23:46','待审批'); /*Table structure for table `managerinfo` */ DROP TABLE IF EXISTS `managerinfo`; CREATE TABLE `managerinfo` ( `managerId` varchar(20) NOT NULL, `managerName` varchar(30) NOT NULL, `managerGender` varchar(10) NOT NULL, `nativePlace` varchar(10) NOT NULL, `entryYear` int(10) NOT NULL, `workYear` int(10) NOT NULL, `phone` varchar(15) NOT NULL, `buildingId` varchar(10) DEFAULT NULL, `password` varchar(15) NOT NULL, `authority` int(5) NOT NULL, PRIMARY KEY (`managerId`), KEY `buildingId` (`buildingId`), CONSTRAINT `managerinfo_ibfk_1` FOREIGN KEY (`buildingId`) REFERENCES `buildinginfo` (`buildingId`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*Data for the table `managerinfo` */ insert into `managerinfo`(`managerId`,`managerName`,`managerGender`,`nativePlace`,`entryYear`,`workYear`,`phone`,`buildingId`,`password`,`authority`) values ('000','未知','男','未知',0,0,'0000','未知','0000',1),('101','李刚 ','男','湖北武汉',207,180,'18843622785','108','101',1),('102','房宝岩 ','男','辽宁省辽阳市',2019,100,'18843690885','107','102',1),('103','吕明健','男','辽宁沈营口市',2019,20,'18241727717','107','103',2),('104','王强','男','湖北武汉',2002,20,'11132144425','108','104',2),('109','李小刚','男','辽宁省大连市',2019,22,'18241727717','106','222',2),('110','李明','男','辽宁省大连市',2019,20,'19203977746','106','asd',2); /*Table structure for table `messageinfo` */ DROP TABLE IF EXISTS `messageinfo`; CREATE TABLE `messageinfo` ( `messageId` int(30) NOT NULL AUTO_INCREMENT, `messageHeader` varchar(20) NOT NULL, `messageContent` varchar(255) NOT NULL, `studentId` varchar(20) DEFAULT NULL, `time` datetime NOT NULL, `approve` varchar(10) NOT NULL, UNIQUE KEY `messageId` (`messageId`), KEY `studentId` (`studentId`), CONSTRAINT `messageinfo_ibfk_1` FOREIGN KEY (`studentId`) REFERENCES `studentinfo