?? studb.sql

?? 采用基于B/S結構進行開發學生管理信息系統,本系統采用Servlet+Jsp+JavaBean+MySQL 的設計方式
?? SQL
字號:

/*!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 */;

DROP TABLE IF EXISTS `award`;
CREATE TABLE `award` (
  `STUID` char(12) NOT NULL COMMENT '學號（外鍵）',
  `LEVELS` char(2) NOT NULL COMMENT '獎勵級別代碼',
  `RECORD_TIME` varchar(20) NOT NULL COMMENT '記錄時間',
  `INFOS` varchar(200) default NULL COMMENT '描述',
  `VALIDATE` char(2) default NULL COMMENT '生效',
  PRIMARY KEY  (`STUID`,`RECORD_TIME`),
  KEY `LEVELS` (`LEVELS`),
  CONSTRAINT `award_ibfk_2` FOREIGN KEY (`LEVELS`) REFERENCES `award_levels` (`CODE`),
  CONSTRAINT `award_ibfk_1` FOREIGN KEY (`STUID`) REFERENCES `student` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='獎勵記錄信息表';


/*!40000 ALTER TABLE `award` DISABLE KEYS */;
LOCK TABLES `award` WRITE;
INSERT INTO `award` VALUES ('200200010101','A2','2006-05-19',NULL,'是'),('200200010102','A3','2006-05-19','null','否');
UNLOCK TABLES;
/*!40000 ALTER TABLE `award` ENABLE KEYS */;


DROP TABLE IF EXISTS `award_levels`;
CREATE TABLE `award_levels` (
  `CODE` char(2) NOT NULL COMMENT '代碼（主鍵）',
  `DESCRIPTION` varchar(50) default NULL COMMENT '描述',
  PRIMARY KEY  (`CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='獎勵等級代碼表';



/*!40000 ALTER TABLE `award_levels` DISABLE KEYS */;
LOCK TABLES `award_levels` WRITE;
INSERT INTO `award_levels` VALUES ('A1','校特等獎學金'),('A2','校一等獎學金'),('A3','校二等獎學金'),('A4','校三等獎學金'),('A5','院一等獎學金'),('A6','院二等獎學金'),('A7','院三等獎學金');
UNLOCK TABLES;
/*!40000 ALTER TABLE `award_levels` ENABLE KEYS */;


DROP TABLE IF EXISTS `change_code`;
CREATE TABLE `change_code` (
  `CODE` char(2) NOT NULL COMMENT '代碼',
  `DESCRIPTION` varchar(50) default NULL COMMENT '變動描述',
  PRIMARY KEY  (`CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='學籍變動代碼表';



/*!40000 ALTER TABLE `change_code` DISABLE KEYS */;
LOCK TABLES `change_code` WRITE;
INSERT INTO `change_code` VALUES ('C1','休學'),('C2','復學'),('C3','退學'),('C4','畢業');
UNLOCK TABLES;
/*!40000 ALTER TABLE `change_code` ENABLE KEYS */;


DROP TABLE IF EXISTS `changes`;
CREATE TABLE `changes` (
  `STUID` char(12) NOT NULL COMMENT '學號',
  `LEVELS` char(2) NOT NULL COMMENT '變更代碼',
  `RECORD_TIME` varchar(20) NOT NULL COMMENT '記錄時間',
  `INFOS` varchar(200) default NULL COMMENT '描述',
  `VALIDATE` char(2) default '是' COMMENT '標記',
  PRIMARY KEY  (`STUID`,`RECORD_TIME`),
  KEY `LEVELS` (`LEVELS`),
  CONSTRAINT `changes_ibfk_1` FOREIGN KEY (`STUID`) REFERENCES `student` (`ID`),
  CONSTRAINT `changes_ibfk_2` FOREIGN KEY (`LEVELS`) REFERENCES `change_code` (`CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='學籍變更信息表';



/*!40000 ALTER TABLE `changes` DISABLE KEYS */;
LOCK TABLES `changes` WRITE;
INSERT INTO `changes` VALUES ('200200010101','C1','2006-05-20','','否'),('200200010102','C1','2006-05-20','2222','是'),('200200010201','C2','2006-05-25','','是'),('200200020201','C3','2006-05-21','9999','是');
UNLOCK TABLES;
/*!40000 ALTER TABLE `changes` ENABLE KEYS */;


DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `ID` char(10) NOT NULL COMMENT '班號',
  `DATE` year(4) default NULL COMMENT '入學年份',
  `COUNT` smallint(6) default '0' COMMENT '班級人數',
  `LEAVECOUNT` smallint(6) default '0' COMMENT '轉離',
  `DEPARTMENT` char(4) default NULL COMMENT '院系',
  `MONITOR` char(12) default NULL COMMENT '班長',
  PRIMARY KEY  (`ID`),
  KEY `MONITOR` (`MONITOR`),
  KEY `DEPARTMENT` (`DEPARTMENT`),
  CONSTRAINT `class_ibfk_3` FOREIGN KEY (`DEPARTMENT`) REFERENCES `department` (`ID`),
  CONSTRAINT `class_ibfk_2` FOREIGN KEY (`MONITOR`) REFERENCES `student` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='班級信息表';



/*!40000 ALTER TABLE `class` DISABLE KEYS */;
LOCK TABLES `class` WRITE;
INSERT INTO `class` VALUES ('2002000101',2002,3,1,'0001','200200010102'),('2002000102',2002,1,1,'0001',NULL),('2002000104',2002,0,0,'0001',NULL),('2002000105',2002,0,0,'0001',NULL),('2002000106',2002,0,0,'0001',NULL),('2002000201',2002,1,0,'0002','200200020101'),('2002000202',2002,1,0,'0002','200200020201');
UNLOCK TABLES;
/*!40000 ALTER TABLE `class` ENABLE KEYS */;


DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `ID` char(6) NOT NULL COMMENT '課號',
  `NAME` varchar(24) default NULL COMMENT '課名',
  `PERIOD` smallint(6) default NULL COMMENT '學時',
  `CREDIT` float(2,1) default NULL COMMENT '學分',
  `SEMESTER` char(8) default NULL COMMENT '學期',
  `DEPARTMENT` char(4) default NULL,
  `PREPARE` char(6) default NULL COMMENT '先修課程',
  PRIMARY KEY  (`ID`),
  KEY `DEPARTMENT` (`DEPARTMENT`),
  CONSTRAINT `course_ibfk_1` FOREIGN KEY (`DEPARTMENT`) REFERENCES `department` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='課程信息表';



/*!40000 ALTER TABLE `course` DISABLE KEYS */;
LOCK TABLES `course` WRITE;
INSERT INTO `course` VALUES ('000101','數據結構',42,4.5,'第三學期','0001','0'),('000102','離散數學',42,4.5,'第四學期','0001','000101');
UNLOCK TABLES;
/*!40000 ALTER TABLE `course` ENABLE KEYS */;


DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
  `ID` char(4) NOT NULL COMMENT '院系代碼',
  `CNAME` varchar(20) default NULL COMMENT '院長',
  `NAME` varchar(24) default NULL COMMENT '院系名稱',
  `CLA_COUNT` int(11) default '0',
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='院系信息表';



/*!40000 ALTER TABLE `department` DISABLE KEYS */;
LOCK TABLES `department` WRITE;
INSERT INTO `department` VALUES ('0001','張無記','計算機科學與技術',5),('0002','張三','信息系',2),('0003','李四','交通系',0),('0004','張三風','物流系',0);
UNLOCK TABLES;
/*!40000 ALTER TABLE `department` ENABLE KEYS */;


DROP TABLE IF EXISTS `punish_levels`;
CREATE TABLE `punish_levels` (
  `CODE` char(2) NOT NULL COMMENT '代碼（主鍵）',
  `DESCRIPTION` varchar(50) default NULL COMMENT '描述',
  PRIMARY KEY  (`CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='處罰等級代碼表';



/*!40000 ALTER TABLE `punish_levels` DISABLE KEYS */;
LOCK TABLES `punish_levels` WRITE;
INSERT INTO `punish_levels` VALUES ('P1','警告'),('P2','嚴重警告'),('P3','記過'),('P4','留校查看'),('P5','開除');
UNLOCK TABLES;
/*!40000 ALTER TABLE `punish_levels` ENABLE KEYS */;


DROP TABLE IF EXISTS `punishment`;
CREATE TABLE `punishment` (
  `STUID` char(12) NOT NULL default '是' COMMENT '學號（外鍵）',
  `LEVELS` char(2) NOT NULL COMMENT '級別代碼（外鍵）',
  `RECORD_TIME` varchar(20) NOT NULL COMMENT '記錄時間',
  `INFOS` varchar(200) default NULL COMMENT '處罰描述',
  `VALIDATE` char(2) default NULL COMMENT '生效',
  PRIMARY KEY  (`STUID`,`RECORD_TIME`),
  KEY `LEVELS` (`LEVELS`),
  CONSTRAINT `punishment_ibfk_2` FOREIGN KEY (`LEVELS`) REFERENCES `punish_levels` (`CODE`),
  CONSTRAINT `punishment_ibfk_1` FOREIGN KEY (`STUID`) REFERENCES `student` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='處罰記錄信息表';



/*!40000 ALTER TABLE `punishment` DISABLE KEYS */;
LOCK TABLES `punishment` WRITE;
INSERT INTO `punishment` VALUES ('200200010101','P1','2006-05-20','','是'),('200200010102','P2','2006-05-19','444','是');
UNLOCK TABLES;
/*!40000 ALTER TABLE `punishment` ENABLE KEYS */;


DROP TABLE IF EXISTS `stu_course`;
CREATE TABLE `stu_course` (
  `STUID` char(12) NOT NULL COMMENT '學號',
  `COURSEID` char(6) NOT NULL COMMENT '課號',
  `GRADE` smallint(6) default NULL COMMENT '成績',
  PRIMARY KEY  (`STUID`,`COURSEID`),
  KEY `COURSEID` (`COURSEID`),
  CONSTRAINT `stu_course_ibfk_4` FOREIGN KEY (`COURSEID`) REFERENCES `course` (`ID`),
  CONSTRAINT `stu_course_ibfk_3` FOREIGN KEY (`STUID`) REFERENCES `student` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='選課信息表';



/*!40000 ALTER TABLE `stu_course` DISABLE KEYS */;
LOCK TABLES `stu_course` WRITE;
INSERT INTO `stu_course` VALUES ('200200010101','000101',90),('200200010102','000101',55),('200200010201','000101',85);
UNLOCK TABLES;
/*!40000 ALTER TABLE `stu_course` ENABLE KEYS */;


DROP TABLE IF EXISTS `studba`;
CREATE TABLE `studba` (
  `ID` char(10) NOT NULL COMMENT '登錄帳號',
  `PASSWORD` varchar(16) NOT NULL COMMENT '登錄密碼',
  `NAME` varchar(20) default NULL COMMENT '姓名',
  `SEX` char(2) default NULL COMMENT '性別',
  `ENROLLDAY` date default NULL COMMENT '注冊日期',
  `DEPARTMENT` char(4) default NULL COMMENT '院系',
  `INFO` varchar(200) default NULL COMMENT '備注',
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='管理員';


/*!40000 ALTER TABLE `studba` DISABLE KEYS */;
LOCK TABLES `studba` WRITE;
INSERT INTO `studba` VALUES ('2006000001','8888','admin','男','2006-01-01','0000',NULL),('2006000101','8888','wowo','女','2006-05-24','0001','ddd');
UNLOCK TABLES;
/*!40000 ALTER TABLE `studba` ENABLE KEYS */;


DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `ID` char(12) NOT NULL COMMENT '學號',
  `PASSWORD` varchar(16) default NULL COMMENT '密碼',
  `NAME` varchar(20) default NULL COMMENT '姓名',
  `SEX` char(2) default '男' COMMENT '性別',
  `ENTRY_TIME` year(4) default NULL COMMENT '入學年份',
  `AGE` smallint(6) default NULL COMMENT '年齡',
  `PROVINCE` varchar(20) default NULL COMMENT '籍貫',
  `CLASS_ID` char(10) default NULL COMMENT '班號',
  `DEPARTMENT` char(4) default NULL COMMENT '院系',
  `ASSISTANT` varchar(10) default NULL COMMENT '輔導',
  `FLAG` binary(1) default '1' COMMENT '標記',
  `INFO` varchar(200) default NULL COMMENT '備注',
  PRIMARY KEY  (`ID`),
  KEY `CLASS_ID` (`CLASS_ID`),
  KEY `DEPARTMENT` (`DEPARTMENT`),
  CONSTRAINT `student_ibfk_1` FOREIGN KEY (`CLASS_ID`) REFERENCES `class` (`ID`),
  CONSTRAINT `student_ibfk_2` FOREIGN KEY (`DEPARTMENT`) REFERENCES `department` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='學生信息表';



/*!40000 ALTER TABLE `student` DISABLE KEYS */;
LOCK TABLES `student` WRITE;
INSERT INTO `student` VALUES ('200200010101','6666','wowo','女',2002,29,'西藏','2002000101','0001','往往','1',NULL),('200200010102','8888','daodao','男',2002,26,'河北','2002000101','0001','往往','1',NULL),('200200010103','8888','大眾','男',2002,28,'上海','2002000101','0001',NULL,'0',NULL),('200200010201','8888','劉德華','男',2002,27,'海南','2002000102','0001',NULL,'1',NULL),('200200020101','8888','李子','男',2002,25,'青海','2002000201','0002',NULL,'1',NULL),('200200020201','8888','哈達','男',2002,27,'內蒙古','2002000202','0002',NULL,'1',NULL);
UNLOCK TABLES;
/*!40000 ALTER TABLE `student` ENABLE KEYS */;


DROP TABLE IF EXISTS `teach`;
CREATE TABLE `teach` (
  `CLASS_ID` char(10) NOT NULL COMMENT '班級編號',
  `TEA_ID` char(10) default NULL COMMENT '教師編號',
  `COUR_ID` char(6) NOT NULL COMMENT '課程編號',
  `ROOM` varchar(20) default NULL COMMENT '教室',
  `COUR_TIME` varchar(40) NOT NULL COMMENT '上課時間',
  KEY `CLASS_ID` (`CLASS_ID`),
  KEY `TEA_ID` (`TEA_ID`),
  KEY `COUR_ID` (`COUR_ID`),
  CONSTRAINT `teach_ibfk_3` FOREIGN KEY (`COUR_ID`) REFERENCES `course` (`ID`),
  CONSTRAINT `teach_ibfk_1` FOREIGN KEY (`CLASS_ID`) REFERENCES `class` (`ID`),
  CONSTRAINT `teach_ibfk_2` FOREIGN KEY (`TEA_ID`) REFERENCES `teacher` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='教師課程表';



/*!40000 ALTER TABLE `teach` DISABLE KEYS */;
LOCK TABLES `teach` WRITE;
INSERT INTO `teach` VALUES ('2002000101','0001200201','000101','103','Mon_1'),('2002000102','0001200001','000101','205','Mon_4');
UNLOCK TABLES;
/*!40000 ALTER TABLE `teach` ENABLE KEYS */;


DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `ID` char(10) character set utf8 NOT NULL COMMENT '教師編號',
  `PASSWORD` varchar(18) character set utf8 default '8888' COMMENT '密碼',
  `NAME` varchar(20) character set utf8 default NULL COMMENT '姓名',
  `TITLE` varchar(16) character set utf8 default NULL COMMENT '職稱',
  `DEPARTMENT` char(4) default NULL,
  `ENTRYYEAR` year(4) default NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 COMMENT='教師基本信息表';



/*!40000 ALTER TABLE `teacher` DISABLE KEYS */;
LOCK TABLES `teacher` WRITE;
INSERT INTO `teacher` VALUES ('0001200001','8888','精華','教授','0001',2000),('0001200201','9999','張張','教授','0001',2002),('0002199901','8888','張大千','副教授','0002',1999),('0002200001','8888','菲菲','助教','0002',2000),('0002200201','888','紅紅','副教授','0002',2002);
UNLOCK TABLES;
/*!40000 ALTER TABLE `teacher` ENABLE KEYS */;
/*!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 */;
?? 文件大小 1366 K
?? 上傳用戶 angela5683
?? 所屬分類 Java編程
??? 相關標簽

#JavaBean #Servlet #MySQL #Jsp
?? 快捷鍵說明

復制代碼 Ctrl + C
搜索代碼 Ctrl + F
全屏模式 F11
切換主題 Ctrl + Shift + D
顯示快捷鍵 ?
增大字號 Ctrl + =
減小字號 Ctrl + -
亚洲欧美第一页_禁久久精品乱码_粉嫩av一区二区三区免费野_久草精品视频

?? studb.sql

?? 快捷鍵說明
