《MySQL数据操作与查询》- 维护学生信息、老师信息和成绩信息 支持按多种条件组合查询学生信息和成绩信息
综合项目需求
一、系统整体功能
系统需支持以下功能:
维护学生信息、老师信息和成绩信息
支持按多种条件组合查询学生信息和成绩信息
学生 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
五、数据库导入
/*
Navicat MySQL Data Transfer
Source Server : test
Source Server Version : 50646
Source Host : localhost:3306
Source Database : schools
Target Server Type : MYSQL
Target Server Version : 50646
File Encoding : 65001
Date: 2020-09-11 16:31:56
*/
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********', '班主任');
本文来自博客园,作者:明金同学,转载请注明原文链接:https://www.cnblogs.com/vmuu/p/15663536.html
公众号:【明金同学】