wws_student_system
/
student_system.sql

/*
SQLyog v10.2
MySQL - 5.7.27-log : Database - student_end
*********************************************************************
*/

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

USE `student_end`;

/*Table structure for table `college_faculty_intermediate` */

DROP TABLE IF EXISTS `college_faculty_intermediate`;

CREATE TABLE `college_faculty_intermediate` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `college_unique_number` varchar(256) CHARACTER SET utf8 DEFAULT NULL COMMENT '学院唯一标识',
  `faculty_unique_number` varchar(256) CHARACTER SET utf8 DEFAULT NULL COMMENT '系别唯一标识',
  PRIMARY KEY (`id`),
  UNIQUE KEY `college_unique_number` (`college_unique_number`,`faculty_unique_number`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1;

/*Data for the table `college_faculty_intermediate` */

insert  into `college_faculty_intermediate`(`id`,`college_unique_number`,`faculty_unique_number`) values (1,'1','a'),(2,'1','b'),(14,'10','u'),(3,'2','c'),(4,'3','d'),(5,'4','e'),(6,'5','f'),(7,'6','g'),(8,'7','h'),(9,'8','i'),(11,'8','k'),(12,'8','m'),(13,'9','o');

/*Table structure for table `faculty_class_intermediate` */

DROP TABLE IF EXISTS `faculty_class_intermediate`;

CREATE TABLE `faculty_class_intermediate` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `faculty_unique_number` varchar(256) CHARACTER SET utf8 DEFAULT NULL COMMENT '系别唯一标识',
  `class_unique_number` varchar(256) CHARACTER SET utf8 DEFAULT NULL COMMENT '班级唯一标识',
  PRIMARY KEY (`id`),
  UNIQUE KEY `class_unique_number` (`class_unique_number`,`faculty_unique_number`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;

/*Data for the table `faculty_class_intermediate` */

insert  into `faculty_class_intermediate`(`id`,`faculty_unique_number`,`class_unique_number`) values (1,'a','111'),(2,'a','112'),(3,'c','113'),(4,'d','114'),(5,'e','115'),(6,'f','116'),(7,'g','117'),(8,'h','118'),(9,'i','119'),(10,'i','120'),(11,'k','121'),(12,'k','122'),(13,'m','123'),(14,'o','124'),(15,'u','125');

/*Table structure for table `stu_basic_info` */

DROP TABLE IF EXISTS `stu_basic_info`;

CREATE TABLE `stu_basic_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8 DEFAULT NULL COMMENT '学生姓名',
  `phone` varchar(11) CHARACTER SET utf8 DEFAULT NULL COMMENT '学生手机号',
  `cards` varchar(18) CHARACTER SET utf8 DEFAULT NULL COMMENT '学生身份证号',
  `birthday` datetime DEFAULT NULL COMMENT '出生日期',
  `sex` tinyint(2) DEFAULT NULL COMMENT '性别',
  PRIMARY KEY (`id`),
  UNIQUE KEY `phone` (`phone`,`cards`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

/*Data for the table `stu_basic_info` */

insert  into `stu_basic_info`(`id`,`name`,`phone`,`cards`,`birthday`,`sex`) values (1,'武文生','17790012693','370983199604064916','1996-04-06 12:36:11',1),(3,'xiaoxiao','18262699159','37098319951129452X','1997-11-29 03:23:07',0),(5,'heihei','18262699157','37098319951129452y','1997-11-29 03:23:12',0),(6,'张三','18262699122','37098319951129451z','1995-02-19 03:23:16',1);

/*Table structure for table `stu_class` */

DROP TABLE IF EXISTS `stu_class`;

CREATE TABLE `stu_class` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `class_unique_number` varchar(256) CHARACTER SET utf8 DEFAULT NULL COMMENT '班级唯一标识',
  `class_name` varchar(256) CHARACTER SET utf8 DEFAULT NULL COMMENT '班级名称',
  `class_des` varchar(256) CHARACTER SET utf8 DEFAULT NULL COMMENT '班级描述',
  PRIMARY KEY (`id`),
  UNIQUE KEY `class_unique_number` (`class_unique_number`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;

/*Data for the table `stu_class` */

insert  into `stu_class`(`id`,`class_unique_number`,`class_name`,`class_des`) values (1,'111','商业贸易一班','商业贸易下属班级'),(2,'112','商业贸易二班','商业贸易下属班级'),(3,'113','外贸专业一班','外贸专业下属班级'),(4,'114','建筑工程专业一班','建筑工程下属班级'),(5,'115','文化传媒专业一班','文化传媒专业下属班级'),(6,'116','旅游航空一班','旅游航空专业下属班级'),(7,'117','机电工程一班','机电工程专业下属班级'),(8,'118','生物工程一班','生物工程专业下属班级'),(9,'119','软件工程一班','软件工程专业下属班级'),(10,'120','软件工程二班','软件工程专业下属班级'),(11,'121','网络工程一班','网络工程专业下属班级'),(12,'122','网络工程二班','网络工程专业下属班级'),(13,'123','计算机科学与技术一班','计算机科学与技术下属班级'),(14,'124','纺织服装专业一班','纺织服装专业下属班级'),(15,'125','艺术设计专业','艺术设计专业下属班级');

/*Table structure for table `stu_college` */

DROP TABLE IF EXISTS `stu_college`;

CREATE TABLE `stu_college` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学院的主键id',
  `college_unique_number` varchar(128) CHARACTER SET utf8 DEFAULT NULL COMMENT '学院的唯一编号',
  `college_name` varchar(128) CHARACTER SET utf8 DEFAULT NULL COMMENT '学院的名称',
  `college_des` varchar(128) CHARACTER SET utf8 COLLATE utf8_estonian_ci DEFAULT NULL COMMENT '学院描述',
  PRIMARY KEY (`id`),
  UNIQUE KEY `college_unique_number` (`college_unique_number`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

/*Data for the table `stu_college` */

insert  into `stu_college`(`id`,`college_unique_number`,`college_name`,`college_des`) values (1,'1','商学院','商业相关'),(2,'2','外贸学院',NULL),(3,'3','建筑工程学院',NULL),(4,'4','文化与传媒学院',NULL),(5,'5','旅游与航空服务学院',NULL),(6,'6','机电工程学院',NULL),(7,'7','生物工程学院',NULL),(8,'8','计算机学院','我心永恒'),(9,'9','纺织与服装工程学院',NULL),(10,'10','艺术设计学院',NULL);

/*Table structure for table `stu_detail_info` */

DROP TABLE IF EXISTS `stu_detail_info`;

CREATE TABLE `stu_detail_info` (
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `cards` varchar(18) CHARACTER SET utf8 DEFAULT NULL COMMENT '身份证号、和基本表相关联',
  `class_unique_number` varchar(256) CHARACTER SET utf8 DEFAULT NULL COMMENT '学生所属班级唯一标识',
  `faculty_unique_number` varchar(256) CHARACTER SET utf8 DEFAULT NULL COMMENT '学生所属系唯一标识',
  `college_unique_number` varchar(256) CHARACTER SET utf8 DEFAULT NULL COMMENT '学生所属院系唯一标识',
  `detail_address` varchar(128) CHARACTER SET utf8 DEFAULT NULL COMMENT '学生家庭详细住址',
  `enrollment_date` datetime DEFAULT NULL COMMENT '入学日期',
  `is_graduation` tinyint(2) DEFAULT NULL COMMENT '是否毕业',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

/*Data for the table `stu_detail_info` */

insert  into `stu_detail_info`(`id`,`cards`,`class_unique_number`,`faculty_unique_number`,`college_unique_number`,`detail_address`,`enrollment_date`,`is_graduation`) values (1,'370983199604064916','123','m','8','山东省泰安市肥城市孙伯镇西程村145号','2019-11-13 05:44:30',0),(2,'37098319951129452X','121','k','8','山东省泰安市肥城市安站镇xx村','2015-09-11 05:23:11',0),(4,'37098319951129452y','111','a','1','山东省泰安市肥城市潮泉镇yy村','2015-09-11 05:23:17',0),(5,'37098319951129451z','111','a','1','山东省泰安市肥城市安驾庄镇zz村','2015-09-07 07:23:17',0);

/*Table structure for table `stu_faculty` */

DROP TABLE IF EXISTS `stu_faculty`;

CREATE TABLE `stu_faculty` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '系别主键id',
  `faculty_unique_number` varchar(128) CHARACTER SET utf8 DEFAULT NULL COMMENT '系别唯一编号',
  `faculty_name` varchar(128) CHARACTER SET utf8 DEFAULT NULL COMMENT '系别名称',
  `faculty_des` varchar(128) CHARACTER SET utf8 DEFAULT NULL COMMENT '系别描述',
  PRIMARY KEY (`id`),
  UNIQUE KEY `faculty_unique_number` (`faculty_unique_number`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;

/*Data for the table `stu_faculty` */

insert  into `stu_faculty`(`id`,`faculty_unique_number`,`faculty_name`,`faculty_des`) values (1,'a','商业贸易','商学院下属系'),(2,'b','商业管理','商学院下属系'),(3,'c','外贸专业','外贸学院下属系'),(4,'d','建筑工程专业','建筑工程下属系'),(5,'e','文化传媒专业','文化与传媒专业下属系'),(6,'f','旅游航空专业','旅游航空服务学院下属系'),(9,'g','机电工程专业','机电工程学院下属系'),(10,'h','生物工程专业','生物工程学院下属系'),(11,'i','软件工程专业','计算机学院下属系'),(12,'k','网络工程','计算机学院下属系'),(13,'m','计算机科学与技术','计算机学院下属系'),(14,'o','纺织服装专业','纺织与服装学院下属系'),(15,'u','艺术设计专业','艺术设计学院下属系');

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