📄 studb.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 */;
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -