学生信息管理数据库及表创建脚本.sql

学生信息管理系统简单实现 可以简单得实现现阶段学生信息得增加 修改 删除 查看

create database if not exists `smsdb`;

CREATE TABLE `college_info` (
  `college_id` varchar(8) NOT NULL default '',
  `name` varchar(20) NOT NULL default '',
  `description` varchar(200) default NULL,
  `remark` varchar(100) default NULL,
  PRIMARY KEY  (`college_id`)
) DEFAULT CHARSET=gbk;

CREATE TABLE `teach_info` (
  `id` varchar(8) NOT NULL default '',
  `name` varchar(20) NOT NULL default '',
  `college` varchar(8) NOT NULL default '',
  `sex` char(1) NOT NULL default '',
  `title` varchar(20) default NULL,
  `description` text,
  PRIMARY KEY  (`id`),
  KEY `college` (`college`),
  CONSTRAINT `teach_info_ibfk_1` FOREIGN KEY (`college`) REFERENCES `college_info` (`college_id`) ON DELETE NO ACTION
) CHARSET=gbk;

CREATE TABLE `news_info` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(100) NOT NULL default '',
  `content` text NOT NULL,
  `author` varchar(10) default NULL,
  `add_time` date default '0000-00-00',
  `quarry` varchar(20) default NULL,
  `type` varchar(10) NOT NULL default '',
  PRIMARY KEY  (`id`)
) DEFAULT CHARSET=gbk;

CREATE TABLE `student_info` (
  `stu_id` varchar(8) NOT NULL default '',
  `password` varchar(8) NOT NULL default '',
  `class_id` varchar(8) NOT NULL default '',
  `name` varchar(20) NOT NULL default '',
  `sex` char(1) NOT NULL default '',
  `enroll_time` date NOT NULL default '0000-00-00',
  `come_from` varchar(20) default NULL,
  `age` int(4) default NULL,
  `college_id` varchar(8) NOT NULL default '',
  `nationality` varchar(20) default NULL,
  `charact` varchar(10) default NULL,
  `len_of_schooling` int(1) default NULL,
  `specialty` varchar(20) default NULL,
  `email` varchar(20) default NULL,
  `telephone` varchar(12) default NULL,
  `address` varchar(100) default NULL,
  `description` text,
  PRIMARY KEY  (`stu_id`,`class_id`,`college_id`),
  KEY `college_id` (`college_id`),
  CONSTRAINT `student_info_ibfk_1` FOREIGN KEY (`college_id`) REFERENCES `college_info` (`college_id`) ON DELETE NO ACTION
) DEFAULT CHARSET=gbk;

CREATE TABLE `course_info` (
  `course_id` varchar(8) NOT NULL default '',
  `course_name` varchar(50) NOT NULL default '',
  `type` char(1) NOT NULL default '',
  `teach_id` varchar(8) NOT NULL default '',
  `description` varchar(100) default NULL,
  `term` int(2) NOT NULL default '0',
  `week` int(1) NOT NULL default '0',
  `period` int(1) NOT NULL default '0',
  `begintime` int(2) default NULL,
  `endtime` int(2) default NULL,
  `classroom` varchar(10) default NULL,
  `remark` varchar(100) default NULL,
  PRIMARY KEY  (`course_id`),
  KEY `teachid` (`teach_id`),
  CONSTRAINT `course_info_ibfk_1` FOREIGN KEY (`teach_id`) REFERENCES `teach_info` (`id`)
) DEFAULT CHARSET=gbk;

CREATE TABLE `messege_info` (
  `id` int(11) NOT NULL auto_increment,
  `topic` varchar(20) NOT NULL default '',
  `content` text NOT NULL,
  `author` varchar(20) default '??',
  `column_id` varchar(20) NOT NULL default '',
  `add_time` date NOT NULL default '0000-00-00',
  `browse_num` int(11) default '0',
  `score_num` int(11) default '0',
  `total_score` float default '0',
  `remark` varchar(20) default NULL,
  PRIMARY KEY  (`id`)
) DEFAULT CHARSET=gbk;

CREATE TABLE `comment_info` (
  `id` int(11) NOT NULL auto_increment,
  `messege_id` int(11) NOT NULL default '0',
  `title` varchar(20) NOT NULL default '',
  `content` varchar(100) default '?',
  `add_time` date NOT NULL default '0000-00-00',
  `author` varchar(20) default '?',
  `remark` varchar(20) default '?',
  PRIMARY KEY  (`id`),
  KEY `messege_id` (`messege_id`),
  CONSTRAINT `comment_info_ibfk_1` FOREIGN KEY (`messege_id`) REFERENCES `messege_info` (`id`) ON DELETE CASCADE
) DEFAULT CHARSET=gbk;

CREATE TABLE `messege_answer_info` (
  `id` int(11) NOT NULL auto_increment,
  `messege_id` int(11) NOT NULL default '0',
  `title` varchar(20) default NULL,
  `content` text NOT NULL,
  `answer_department` varchar(20) NOT NULL default '',
  `answer_time` date default NULL,
  `remar` varchar(20) default NULL,
  PRIMARY KEY  (`id`),
  KEY `messege_id` (`messege_id`),
  CONSTRAINT `messege_answer_info_ibfk_1` FOREIGN KEY (`messege_id`) REFERENCES `messege_info` (`id`) ON DELETE CASCADE
) DEFAULT CHARSET=gbk;

CREATE TABLE `required_course_scheme` (
  `id` int(8) NOT NULL default '0',
  `class_id` varchar(8) NOT NULL default '',
  `course_id` varchar(8) NOT NULL default '',
  `remark` varchar(100) default NULL,
  PRIMARY KEY  (`id`),
  KEY `course_id` (`course_id`),
  CONSTRAINT `required_course_scheme_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `course_info` (`course_id`) ON DELETE CASCADE
) DEFAULT CHARSET=gbk;

CREATE TABLE `selective_course_scheme` (
  `id` int(8) default '0',
  `stu_id` varchar(8) NOT NULL default '',
  `course_id` varchar(8) NOT NULL default '',
  `remark` varchar(100) default NULL,
  PRIMARY KEY  (`stu_id`,`course_id`),
  KEY `course_id` (`course_id`),
  CONSTRAINT `selective_course_scheme_ibfk_1` FOREIGN KEY (`stu_id`) REFERENCES `student_info` (`stu_id`) ON DELETE CASCADE,
  CONSTRAINT `selective_course_scheme_ibfk_2` FOREIGN KEY (`course_id`) REFERENCES `course_info` (`course_id`) ON DELETE CASCADE
) DEFAULT CHARSET=gbk;

CREATE TABLE `stu_score` (
  `stu_id` varchar(8) NOT NULL default 'null',
  `course_id` varchar(8) NOT NULL default 'null',
  `score` float NOT NULL default '0',
  `remark` varchar(50) default NULL,
  PRIMARY KEY  (`stu_id`,`course_id`),
  KEY `couseid` (`course_id`),
  CONSTRAINT `stu_score_ibfk_1` FOREIGN KEY (`stu_id`) REFERENCES `student_info` (`stu_id`) ON DELETE CASCADE,
  CONSTRAINT `stu_score_ibfk_2` FOREIGN KEY (`course_id`) REFERENCES `course_info` (`course_id`) ON DELETE NO ACTION
) DEFAULT CHARSET=gbk;