【SQL开源代码栏目提醒】:网学会员为广大网友收集整理了,学生信息管理数据库及表创建脚本.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 '',
`pass
word` 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`) R