/*
SQLyog Ultimate v12.09 (64 bit)
MySQL - 5.7.31-log : Database - student
*********************************************************************
*/
/*!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` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `student`;
/*Table structure for table `course` */
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`subject_id` int(4) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
`subject_name` varchar(50) DEFAULT NULL COMMENT '课程名称',
`class_hour` int(4) DEFAULT NULL COMMENT '学时',
`grade_id` int(4) DEFAULT NULL COMMENT '年级编号',
PRIMARY KEY (`subject_id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8 COMMENT='课程表';
/*Data for the table `course` */
insert into `course`(`subject_id`,`subject_name`,`class_hour`,`grade_id`) values (1,'计算机基础',20,1),(2,'Java程序设计基础',16,1),(3,'数据库设计基础',18,1),(4,'HTML',32,1),(5,'javascript',38,1),(6,'css',36,1),(7,'Java面向对象程序设计',24,2),(8,'MySql从入门到删库跑路',28,2),(9,'数据结构与算法设计',12,2),(10,'设计模式之美',26,2),(11,'深入理解Spring框架',42,3),(12,'MyBatis从入门到放弃',38,3),(13,'SpringBoot实战',12,3),(14,'计算机网络',48,2),(15,'算法设计与分析',64,2);
/*Table structure for table `grade` */
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade` (
`grade_id` int(4) NOT NULL,
`grade_name` varchar(50) DEFAULT NULL COMMENT '年级名称',
PRIMARY KEY (`grade_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='年级表';
/*Data for the table `grade` */
insert into `grade`(`grade_id`,`grade_name`) values (1,'一年级'),(2,'二年级'),(3,'三年级'),(4,'四年级'),(5,'五年级'),(6,'六年级'),(7,'七年级');
/*Table structure for table `score` */
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`record_id` int(4) NOT NULL,
`student_id` int(4) NOT NULL COMMENT '学号',
`subject_id` int(4) NOT NULL COMMENT '课程编号',
`exam_date` datetime DEFAULT NULL,
`student_score` int(4) DEFAULT NULL COMMENT '考试成绩',
PRIMARY KEY (`record_id`),
KEY `foreign_grade` (`student_id`),
KEY `subject_id` (`subject_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='成绩表';
/*Data for the table `score` */
insert into `score`(`record_id`,`student_id`,`subject_id`,`exam_date`,`student_score`) values (1,1000,2,'2020-02-17 00:00:00',60),(2,1002,2,'2020-02-17 00:00:00',83),(3,1011,2,'2020-02-16 00:00:00',71),(4,1011,2,'2020-02-17 00:00:00',95),(5,1012,2,'2020-02-15 00:00:00',76),(6,2011,2,'2020-02-15 00:00:00',60),(7,2012,2,'2020-02-15 00:00:00',91),(8,2012,7,'2020-02-15 00:00:00',61),(9,2015,2,'2020-02-15 00:00:00',60),(10,2015,7,'2020-02-15 00:00:00',65),(11,3021,2,'2020-02-15 00:00:00',23),(12,3021,8,'2020-02-15 00:00:00',74),(13,3023,2,'2020-02-15 00:00:00',23),(14,3023,9,'2020-02-15 00:00:00',39),(15,1000,2,'2020-03-12 10:10:10',NULL),(16,1001,2,'2021-07-28 10:00:00',80),(17,1001,2,'2020-01-08 10:00:00',90);
/*Table structure for table `student_infor` */
DROP TABLE IF EXISTS `student_infor`;
CREATE TABLE `student_infor` (
`student_id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`student_name` varchar(50) NOT NULL COMMENT '学生姓名',
`leader_id` int(4) DEFAULT NULL,
`sex` char(2) NOT NULL DEFAULT '男' COMMENT '性别',
`grade_id` int(4) unsigned DEFAULT NULL COMMENT '年级编号',
`phone` varchar(50) DEFAULT NULL COMMENT '联系电话',
`address` varchar(255) DEFAULT '地址不详' COMMENT '地址',
`born_date` datetime DEFAULT NULL COMMENT '出生时间',
`email` varchar(50) DEFAULT NULL COMMENT '邮箱',
`identity_card` varchar(18) DEFAULT NULL COMMENT '身份证号',
PRIMARY KEY (`student_id`),
UNIQUE KEY `identity_card` (`identity_card`)
) ENGINE=InnoDB AUTO_INCREMENT=4002 DEFAULT CHARSET=utf8 COMMENT='学生表';
/*Data for the table `student_infor` */
insert into `student_infor`(`student_id`,`student_name`,`leader_id`,`sex`,`grade_id`,`phone`,`address`,`born_date`,`email`,`identity_card`) values (1000,'郭靖',NULL,'1',1,'2088762106','天津市河西区','1987-09-08 00:00:00','[email protected]','111111'),(1001,'杨过',1000,'1',1,'1062768866','地址不详','1993-09-08 00:00:00','[email protected]','111112'),(1002,'李斯文',1000,'1',1,'2183410615','河南洛阳','1992-08-08 00:00:00','[email protected]','111113'),(1011,'武松',NULL,'0',1,'1062768888','地址不详','1984-10-08 00:00:00','[email protected]','111114'),(1012,'王宝',1011,'0',1,'2183567890','地址不详','1998-08-08 00:00:00','[email protected]','111120'),(2011,'张三',NULL,'1',1,'15290234178','北京市海淀区','1994-01-01 00:00:00','[email protected]','111115'),(2012,'张秋丽',2011,'0',2,'1062751414','北京市东城区','1996-06-08 00:00:00','[email protected]','111116'),(2015,'肖梅',2011,'0',2,'31466557171','河北省石家庄市','1996-10-01 00:00:00','[email protected]','111117'),(3021,'欧阳俊雄',NULL,'1',2,'2277585000','上海市卢湾区','1995-08-08 00:00:00','[email protected]','111118'),(3023,'梅超风',3021,'0',2,'31466557172','广州市天河区','1997-08-30 00:00:00','[email protected]','111119');
/*!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 */;
