综合项目需求
一、系统整体功能
系统需支持以下功能：
维护学生信息、老师信息和成绩信息
支持按多种条件组合查询学生信息和成绩信息
学生 Student（id,班级id，学号，姓名，性别，电话，地址，出生日期，民族，家长电话1，家长电话2）
老师 Teacher（id,工号，姓名，性别，电话，类型（班主任、讲师））
班级 Class（id，班级名称，讲师id，班主任id）
课程 Subject（id，课程名称、课程简介）
成绩 Score（id，stu_id,sub_id,卷面成绩，项目成绩，平时成绩，综合成绩）
二、系统的信息需求
一个班级有一个讲师一个班主任
一个班级有多名学生
一个学生有多门课程，一门课程也有多名学生
三、表结构
【学生表 Student】
| 列名 |
含义 |
数据类型 |
长度 |
允许空 |
约束 |
| id |
编号 |
Int |
|
NOT |
主键 |
| class_id |
班级id |
Int |
|
|
关联班级id |
| noStudent |
学号 |
varchar |
30 |
|
|
| name |
姓名 |
varchar |
20 |
|
|
| sex |
性别 |
char |
2 |
|
|
| phone |
电话 |
varchar |
20 |
|
|
| address |
地址 |
varchar |
200 |
|
|
| birthday |
出身日期 |
Date |
|
|
|
| nation |
民族 |
varchar |
20 |
|
|
| parent_phone_1 |
家长电话1 |
varchar |
20 |
|
|
| parent_phone_2 |
家长电话 |
varchar |
20 |
|
|
【教师表 Teacher】
| 列名 |
含义 |
数据类型 |
长度 |
允许空 |
约束 |
| id |
编号 |
Int |
|
NOT |
主键 |
| noNumber |
工号 |
varchar |
30 |
|
|
| name |
姓名 |
varchar |
20 |
|
|
| sex |
性别 |
char |
2 |
|
|
| phone |
电话 |
varchar |
20 |
|
|
| Teacher_type |
类型 |
nvarchar |
20 |
|
讲师/班主任 |
【班级表Class】
| 列名 |
含义 |
数据类型 |
长度 |
允许空 |
约束 |
| id |
编号 |
Int |
|
NOT |
主键 |
| name |
班级名称 |
varchar |
20 |
|
|
| lecturer_id |
讲师id |
int |
1 |
|
关联教师表 |
| class_teacher_id |
班主任id |
int |
20 |
|
关联教师表 |
【课程表Subject】
| 列名 |
含义 |
数据类型 |
长度 |
允许空 |
约束 |
| id |
编号 |
Int |
|
NOT |
主键 |
| name |
课程名称 |
varchar |
50 |
|
|
| remark |
简介 |
varchar |
200 |
|
|
【成绩表Score】
| 列名 |
含义 |
数据类型 |
长度 |
允许空 |
约束 |
| id |
编号 |
Int |
|
NOT |
主键 |
| stu_id |
学生id |
int |
20 |
|
关联学生表 |
| sub_id |
课程id |
int |
1 |
|
关联课程表 |
| exam |
卷面成绩 |
decimal |
18,1 |
|
|
| project |
项目成绩 |
decimal |
18,1 |
|
|
| daily |
平时成绩 |
decimal |
18,1 |
|
|
| result |
综合成绩 |
decimal |
18,1 |
|
|
四、数据维护
（1）查询自己的信息，显示学号，姓名，性别，电话，地址，出生日期，民族，家长电话1，家长电话2
SELECt nostudent 学号,name 姓名,sex 性别,phone 电话,address 地址,birthday 出生日期,nation 名族,parent_phone_1 家长电话1,parent_phone_2 家长电话2
FROM student
WHERe id=1;（2）查询自己和所在班级的信息，显示班级，学号，姓名，性别，电话，地址，出生日期，民族，家长电话1，家长电话2
SELECt class.name 班级,nostudent 学号,student.name 姓名,sex 性别,phone 电话,address 地址,birthday 出生日期,nation 名族,parent_phone_1 家长电话1,parent_phone_2 家长电话2
FROM student,class WHERe student.class_id = class.id
AND class_id=1;（3）查询所有讲师的带班信息，显示，讲师姓名，班级，班级人数
SELECt t.name 讲师姓名,c.name 班级,COUNT(*) 班级人数 FROM student s,class c,teacher t
WHERe s.class_id=c.id AND c.lecturer_id = t.id
AND teacher_type='讲师'
GROUP BY c.name;（4）查询班级信息，显示班级名称、讲师姓名、班主任姓名，班级人数
SELECt c.name 班级名称,t.name 讲师姓名,COUNT(*) 班级人数 FROM student s,class c,teacher t
WHERe s.class_id=c.id AND c.lecturer_id = t.id AND teacher_type='讲师'
GROUP BY c.name;
SELECt c.name 班级名称,t.name 讲师姓名,COUNT(*) 班级人数 FROM student s,class c,teacher t
WHERe s.class_id=c.id AND c.class_teacher_id = t.id AND teacher_type='班主任'
GROUP BY c.name;（5）查询我们班的成绩信息，要求显示，姓名，卷面成绩，项目成绩，平时成绩，综合成绩,按综合成绩降序排序
SELECt st.name 姓名,exam 卷面成绩,project 项目成绩,daily 平时成绩,result 综合成绩
FROM student st,score sc
WHERe sc.stu_id=st.id AND st.class_id=1;（6）查询每个讲师带了多少学生，显示讲师姓名，学生数
SELECt t.name 讲师姓名,COUNT(s.class_id) 学生数 FROM student s,class c,teacher t
WHERe s.class_id=c.id AND c.lecturer_id = t.id
AND teacher_type='讲师'
GROUP BY teacher_type;（7）查询各班H5卷面成绩，显示班级、平均分，最高分，最低分
SELECt c.name 班级,AVG(exam) 平均成绩,MAX(exam) 最高成绩,MIN(exam) 最低成绩 FROM class c,score s,student st
WHERe c.id=st.class_id AND s.stu_id=st.id AND s.sub_id=3;（8）查询你自己各科成绩，显示，姓名、卷面成绩，项目成绩，平时成绩，综合成绩
SELECt st.name 姓名,exam 卷面成绩,project 项目成绩,daily 平时成绩,result 综合成绩 FROM student st,score sc,`subject` su
WHERe sc.stu_id=st.id AND sc.sub_id=su.id
AND st.name='杨明金';（9）查询各地州的学生人数，显示，地址州，学生人数，按人数排序
SELECt address 地址州,COUNT(name) 学生人数 FROM student
GROUP BY address
ORDER BY COUNT(name)（10）查询自己所有成绩的平均分，显示姓名 卷面成绩，项目成绩，平时成绩，综合成绩
SELECt st.name 姓名,AVG(exam) 卷面成绩,AVG(project) 项目成绩,AVG(daily) 平时成绩,AVG(result) 综合成绩 FROM student st,score sc,`subject` su
WHERe sc.stu_id=st.id AND sc.sub_id=su.id
AND st.name='杨明金';（11）查询各地州的学生平均成绩，显示，地址州，学生人数，平均卷面成绩，平均项目成绩，平均平时成绩，平均综合成绩，按综合成绩排序从高到低排序
SELECt address 地址州,COUNT(st.name) 学生人数,AVG(exam) 卷面成绩,AVG(project) 项目成绩,AVG(daily) 平时成绩,AVG(result) 综合成绩 FROM student st,score sc,`subject` su
WHERe sc.stu_id=st.id AND sc.sub_id=su.id
GROUP BY address（12）查询所有学生的成绩信息，显示，学生姓名，班级，讲师，班主任，课程名，卷面成绩，项目成绩，平时成绩，综合成绩，按卷面成绩排序从高到低排序
SELECt st.name 学生姓名,cl.name 班级名称,te.name 老师姓名,su.name 课程名称,exam 卷面成绩,project 项目成绩,daily 平时成绩, result 综合成绩
FROM score sc,`subject` su,student st,class cl,teacher te
WHERe sc.sub_id=su.id AND sc.stu_id=st.id AND cl.id=st.class_id AND te.id=cl.lecturer_id
ORDER BY exam DESC（13）查询各讲师各科目的平均分，显示讲师，科目，卷面成绩，项目成绩，平时成绩，综合成绩，选择合适的排序字段和排序方式
SELECt te.name 讲师,su.name 科目,exam 卷面成绩,project 项目成绩,daily 平时成绩,result 综合成绩
FROM score sc,`subject` su,student st,class cl,teacher te
WHERe sc.sub_id=su.id AND sc.stu_id=st.id AND cl.id=st.class_id AND te.id=cl.lecturer_id
ORDER BY result DESC（14）查询各讲师各班各科目的平均分，显示讲师，班级，科目，卷面成绩，项目成绩，平时成绩，综合成绩, 选择合适的排序字段和排序方式
SELECt te.name 讲师,cl.name 班级, su.name 科目,AVG(exam) 卷面成绩,AVG(project) 项目成绩,AVG(daily) 平时成绩,AVG(result) 综合成绩
FROM score sc,`subject` su,student st,class cl,teacher te
WHERe sc.sub_id=su.id AND sc.stu_id=st.id AND cl.id=st.class_id AND te.id=cl.lecturer_id
GROUP BY te.name,cl.name
ORDER BY result DESC
五、数据库导入
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for class
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`lecturer_id` int(1) DEFAULT NULL,
`class_teacher_id` int(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO `class` VALUES ('1', '19计算机网络技术一班', '1', '2');
INSERT INTO `class` VALUES ('2', '19计算机网络技术二班', '1', '2');
INSERT INTO `class` VALUES ('3', '19大数据技术与应用一班', '1', '3');
-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`stu_id` int(20) DEFAULT NULL,
`sub_id` int(1) DEFAULT NULL,
`exam` decimal(18,1) DEFAULT NULL,
`project` decimal(18,1) DEFAULT NULL,
`daily` decimal(18,1) DEFAULT NULL,
`result` decimal(18,1) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('1', '1', '1', '50.0', '70.0', '90.0', '230.0');
INSERT INTO `score` VALUES ('2', '1', '3', '85.0', '60.0', '50.0', '603.0');
INSERT INTO `score` VALUES ('3', '2', '4', '50.0', '90.0', '80.0', '656.0');
INSERT INTO `score` VALUES ('4', '4', '5', '65.0', '86.0', '56.0', '455.0');
INSERT INTO `score` VALUES ('5', '2', '6', '45.0', '48.0', '65.0', '915.0');
INSERT INTO `score` VALUES ('6', '3', '5', '45.0', '46.0', '45.0', '564.0');
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`class_id` int(11) DEFAULT NULL,
`nostudent` varchar(30) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`sex` char(2) DEFAULT NULL,
`phone` varchar(20) DEFAULT NULL,
`address` varchar(200) DEFAULT NULL,
`birthday` date DEFAULT NULL,
`nation` varchar(20) DEFAULT NULL,
`parent_phone_1` varchar(20) DEFAULT NULL,
`parent_phone_2` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '1', '193610202629', '杨明金', '男', '18214217246', '云南昭通', '2020-09-11', '汉族', '13587757775', '5454874545');
INSERT INTO `student` VALUES ('2', '1', '195656546544', '肖明安', '女', '154345343535', '云南昆明', '2020-10-21', '藏族', '26565656565', '5656565655');
INSERT INTO `student` VALUES ('3', '2', '235656565555', '闪正', '女', '1534565654', '四川眉州', '2020-09-08', '汉族', '15645878787', '1546787645');
INSERT INTO `student` VALUES ('4', '2', '156487867557', '岩奔', '男', '12345454886', '云南文山', '2020-09-16', '白族', '15647867878', '5413434543');
INSERT INTO `student` VALUES ('5', '3', '156986564565', '刘泽龙', '女', '15649878978', '云南曲靖', '2020-09-06', '布依族', '15645484777', '1565456475');
-- ----------------------------
-- Table structure for subject
-- ----------------------------
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject` (
`id` int(255) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`remark` varchar(200) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of subject
-- ----------------------------
INSERT INTO `subject` VALUES ('1', '计算机应用基础', '介绍');
INSERT INTO `subject` VALUES ('2', 'Photoshop UI设计', '介绍');
INSERT INTO `subject` VALUES ('3', 'HTML5网页设计', '简介');
INSERT INTO `subject` VALUES ('4', 'Java程序设计基础', '这是简介');
INSERT INTO `subject` VALUES ('5', 'C#程序设计基础', '这是介绍');
-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`nonumber` varchar(30) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`sex` char(2) DEFAULT NULL,
`phone` varchar(20) DEFAULT NULL,
`Teacher_type` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', '1500077', '王威', '男', '159********', '讲师');
INSERT INTO `teacher` VALUES ('2', '1500044', '鲁卫秀', '女', '183********', '班主任');
INSERT INTO `teacher` VALUES ('3', '1500027', '李松龄', '女', '184********', '班主任');
