master

分支 (3)

管理

管理

master

NEW_SSS

yym

smart-campus-service-platform
/
zhxy.sql

/*
SQLyog Ultimate v12.08 (64 bit)
MySQL - 5.7.12-log : Database - zhxy
*********************************************************************
*/

/*!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*/`zhxy` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `zhxy`;

/*Table structure for table `addresslist` */

DROP TABLE IF EXISTS `addresslist`;

CREATE TABLE `addresslist` (
  `addressID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) DEFAULT NULL,
  `telephone` int(255) DEFAULT NULL,
  `collegeID` int(11) DEFAULT NULL,
  PRIMARY KEY (`addressID`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

/*Data for the table `addresslist` */

insert  into `addresslist`(`addressID`,`name`,`telephone`,`collegeID`) values (1,'罗元杨',10086,2),(2,'高新波',10001,1),(3,'小红',10010,1),(4,'老师1号',10000,1),(5,'钱鹰',12123,2),(6,'老师2号',132123,5);

/*Table structure for table `college` */

DROP TABLE IF EXISTS `college`;

CREATE TABLE `college` (
  `collegeID` int(11) NOT NULL,
  `collegeName` varchar(200) NOT NULL COMMENT '璇剧▼鍚?',
  PRIMARY KEY (`collegeID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `college` */

insert  into `college`(`collegeID`,`collegeName`) values (1,'通信学院'),(2,'软件学院'),(3,'自动化学院'),(4,'传媒学院'),(5,'体育学院');

/*Table structure for table `comment` */

DROP TABLE IF EXISTS `comment`;

CREATE TABLE `comment` (
  `commentID` int(11) NOT NULL AUTO_INCREMENT,
  `courseID` int(11) DEFAULT NULL,
  `teacherID` int(11) DEFAULT NULL,
  `studentID` int(11) DEFAULT NULL,
  `attendance` int(20) DEFAULT NULL COMMENT '考勤严格程度',
  `task` int(20) DEFAULT NULL COMMENT '作业安排程度',
  `satisfaction` int(20) DEFAULT NULL COMMENT '满意度',
  `suggestion` varchar(500) DEFAULT NULL COMMENT '给老师的意见',
  `tag` int(11) DEFAULT '0' COMMENT '完成情况',
  PRIMARY KEY (`commentID`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

/*Data for the table `comment` */

insert  into `comment`(`commentID`,`courseID`,`teacherID`,`studentID`,`attendance`,`task`,`satisfaction`,`suggestion`,`tag`) values (1,1,1001,9999,10,10,0,'无',1),(2,6,1003,9999,NULL,NULL,NULL,NULL,0),(3,1,1001,10001,NULL,NULL,NULL,NULL,0),(4,2,1002,10001,NULL,NULL,NULL,NULL,0),(5,3,1003,10001,NULL,NULL,NULL,NULL,0),(6,1,1001,10002,NULL,NULL,NULL,NULL,0),(7,1,1001,10003,NULL,NULL,NULL,NULL,0),(8,2,1002,10003,NULL,NULL,NULL,NULL,0),(9,4,1001,9999,0,0,0,'',1),(10,5,1003,10001,NULL,NULL,NULL,NULL,0),(11,4,1001,10003,NULL,NULL,NULL,NULL,0);

/*Table structure for table `course` */

DROP TABLE IF EXISTS `course`;

CREATE TABLE `course` (
  `courseID` int(11) NOT NULL,
  `courseName` varchar(200) NOT NULL COMMENT '璇剧▼鍚嶇О',
  `teacherID` int(11) NOT NULL,
  `courseTime` varchar(200) DEFAULT NULL COMMENT '寮€璇炬椂闂?',
  `classRoom` varchar(200) DEFAULT NULL COMMENT '寮€璇惧湴鐐?',
  `courseWeek` int(200) DEFAULT NULL COMMENT '瀛︽椂',
  `courseType` varchar(20) DEFAULT NULL COMMENT '璇剧▼绫诲瀷',
  `collegeID` int(11) NOT NULL COMMENT '鎵€灞為櫌绯?',
  `score` int(11) NOT NULL COMMENT '瀛﹀垎',
  PRIMARY KEY (`courseID`),
  KEY `collegeID` (`collegeID`),
  KEY `teacherID` (`teacherID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `course` */

insert  into `course`(`courseID`,`courseName`,`teacherID`,`courseTime`,`classRoom`,`courseWeek`,`courseType`,`collegeID`,`score`) values (1,'C++课程设计(面向过程程序设计）',1001,'周一9-12节课','实验楼B403/404',18,'必修课',1,3),(2,'形势与政策',1002,'周四3-4节课','三教3018',8,'必修课',1,1),(3,'体育（俱乐部）篮球中级',1003,'周五5-6','操场',18,'必修课',1,2),(4,'程序设计  (Java平台)',1001,'周三5-8节课','实验楼A504/505',18,'必修课',2,3),(5,'西方文化与动画艺术',1003,'周二9-10节课','二教2106',8,'必修课',4,2),(6,'数字媒体技术',1003,'周二1-2节课','四教4112',18,'选修课',4,2);

/*Table structure for table `document` */

DROP TABLE IF EXISTS `document`;

CREATE TABLE `document` (
  `documentID` int(11) NOT NULL AUTO_INCREMENT,
  `startTime` date DEFAULT NULL,
  `deadline` date DEFAULT NULL,
  `title` varchar(150) DEFAULT NULL,
  `content` varchar(500) DEFAULT NULL,
  `tag` int(11) DEFAULT '0',
  PRIMARY KEY (`documentID`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

/*Data for the table `document` */

insert  into `document`(`documentID`,`startTime`,`deadline`,`title`,`content`,`tag`) values (1,'2022-07-01','2022-07-07','招生计划审批','完成2022年大一新生的招生计划安排的审批',1),(2,'2022-07-04','2022-08-09','安排就业交流大会','安排和重庆大学共同举办的应届生就业相关交流会议',1),(5,'2022-07-04','2015-09-02','软件工程工程认证','软件工程工程认证',0);

/*Table structure for table `leader` */

DROP TABLE IF EXISTS `leader`;

CREATE TABLE `leader` (
  `userID` int(11) NOT NULL AUTO_INCREMENT,
  `userName` varchar(200) DEFAULT NULL,
  `sex` varchar(20) DEFAULT NULL,
  `birthYear` date NOT NULL,
  `degree` varchar(20) DEFAULT NULL,
  `title` varchar(225) DEFAULT NULL,
  `grade` date DEFAULT NULL,
  `collegeID` int(11) DEFAULT NULL,
  PRIMARY KEY (`userID`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

/*Data for the table `leader` */

insert  into `leader`(`userID`,`userName`,`sex`,`birthYear`,`degree`,`title`,`grade`,`collegeID`) values (11,'高新波','男','1970-07-07','本科','校长','2006-06-23',1),(12,'钱鹰','男','1972-08-25','本科','院长','2009-02-12',2);

/*Table structure for table `role` */

DROP TABLE IF EXISTS `role`;

CREATE TABLE `role` (
  `roleID` int(11) NOT NULL,
  `roleName` varchar(20) NOT NULL,
  `permissions` varchar(255) DEFAULT NULL COMMENT '鏉冮檺',
  PRIMARY KEY (`roleID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `role` */

insert  into `role`(`roleID`,`roleName`,`permissions`) values (0,'admin',NULL),(1,'teacher',NULL),(2,'student',NULL),(3,'leader',NULL);

/*Table structure for table `selectedcourse` */

DROP TABLE IF EXISTS `selectedcourse`;

CREATE TABLE `selectedcourse` (
  `courseID` int(11) NOT NULL,
  `studentID` int(11) NOT NULL,
  `mark` int(11) DEFAULT NULL COMMENT '鎴愮哗',
  KEY `courseID` (`courseID`),
  KEY `studentID` (`studentID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `selectedcourse` */

insert  into `selectedcourse`(`courseID`,`studentID`,`mark`) values (2,10001,61),(1,10001,95),(1,10002,66),(2,10003,99),(5,10001,NULL),(3,10001,NULL),(1,10003,90),(4,10003,NULL),(4,9999,NULL),(6,9999,NULL),(1,9999,NULL);

/*Table structure for table `student` */

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (
  `userID` int(11) NOT NULL AUTO_INCREMENT,
  `userName` varchar(200) NOT NULL,
  `sex` varchar(20) DEFAULT NULL,
  `birthYear` date DEFAULT NULL COMMENT '鍑虹敓鏃ユ湡',
  `grade` date DEFAULT NULL COMMENT '鍏ュ鏃堕棿',
  `collegeID` int(11) NOT NULL COMMENT '闄㈢郴id',
  PRIMARY KEY (`userID`),
  KEY `collegeID` (`collegeID`)
) ENGINE=InnoDB AUTO_INCREMENT=10008 DEFAULT CHARSET=utf8;

/*Data for the table `student` */

insert  into `student`(`userID`,`userName`,`sex`,`birthYear`,`grade`,`collegeID`) values (9999,'mike1','男','1996-09-03','2019-11-13',3),(10001,'mike2','女','2001-06-14','2020-03-02',1),(10002,'mike3','男','2002-11-08','2020-03-10',1),(10003,'mike4','女','1996-09-02','2015-09-02',1),(10005,'mike5','男','1996-09-02','2015-09-02',2),(10007,'mike6','女','1996-09-02','2015-09-02',2);

/*Table structure for table `teacher` */

DROP TABLE IF EXISTS `teacher`;

CREATE TABLE `teacher` (
  `userID` int(11) NOT NULL AUTO_INCREMENT,
  `userName` varchar(200) NOT NULL,
  `sex` varchar(20) DEFAULT NULL,
  `birthYear` date NOT NULL,
  `degree` varchar(20) DEFAULT NULL COMMENT '学位',
  `title` varchar(255) DEFAULT NULL COMMENT '职称',
  `grade` date DEFAULT NULL COMMENT '入职日期',
  `collegeID` int(11) NOT NULL COMMENT '学院对应的序号',
  PRIMARY KEY (`userID`),
  KEY `collegeID` (`collegeID`)
) ENGINE=InnoDB AUTO_INCREMENT=1006 DEFAULT CHARSET=utf8;

/*Data for the table `teacher` */

insert  into `teacher`(`userID`,`userName`,`sex`,`birthYear`,`degree`,`title`,`grade`,`collegeID`) values (1000,'高新波','男','1970-07-07','博士','教授','2006-06-23',1),(1001,'罗元杨','男','1985-07-20','博士','教授','2009-09-02',2),(1002,'小红','女','1992-10-02','硕士','讲师','2017-11-15',1),(1003,'老师1号','男','1996-09-02','本科','助教','2017-07-07',1),(1004,'钱鹰','男','1972-08-25','博士','教授','2009-02-12',2),(1005,'老师2号','女','1996-09-02','本科','普通教师','2015-09-02',5);

/*Table structure for table `userlogin` */

DROP TABLE IF EXISTS `userlogin`;

CREATE TABLE `userlogin` (
  `userID` int(11) NOT NULL AUTO_INCREMENT,
  `userName` varchar(200) NOT NULL,
  `password` varchar(200) NOT NULL,
  `role` int(11) NOT NULL DEFAULT '2' COMMENT '瑙掕壊鏉冮檺',
  PRIMARY KEY (`userID`),
  KEY `role` (`role`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

/*Data for the table `userlogin` */

insert  into `userlogin`(`userID`,`userName`,`password`,`role`) values (1,'admin','123',0),(2,'1001','0000',1),(3,'1002','0000',1),(4,'1003','0000',1),(5,'9999','0000',2),(6,'10001','123',2),(7,'10002','0000',2),(8,'10003','0000',2),(9,'10005','0000',2),(10,'10007','0000',2),(11,'11','123',3),(12,'12','0000',3),(13,'1004','0000',1),(14,'1000','0000',1),(15,'1005','0000',1);

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;