《MySQL数据操作与查询》- 维护学生信息、老师信息和成绩信息支持按多种条件组合查询学生信息和成绩信息

2022-05-11 238

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

云数据库 RDS MySQL，集群系列 2核4GB

RDS MySQL Serverless 基础系列，0.5-2RCU 50GB

云数据库 RDS MySQL，高可用系列 2核4GB

简介： 《MySQL数据操作与查询》- 维护学生信息、老师信息和成绩信息支持按多种条件组合查询学生信息和成绩信息

综合项目需求

一、系统整体功能

系统需支持以下功能：

维护学生信息、老师信息和成绩信息

支持按多种条件组合查询学生信息和成绩信息

学生 Student（id,班级id，学号，姓名，性别，电话，地址，出生日期，民族，家长电话1，家长电话2）

老师 Teacher（id,工号，姓名，性别，电话，类型（班主任、讲师））

班级 Class（id，班级名称，讲师id，班主任id）

课程 Subject（id，课程名称、课程简介）

成绩 Score（id，stu_id,sub_id,卷面成绩，项目成绩，平时成绩，综合成绩）

二、系统的信息需求

一个班级有一个讲师一个班主任

一个班级有多名学生

一个学生有多门课程，一门课程也有多名学生

三、表结构

【学生表 Student】

【教师表 Teacher】

【班级表Class】

【课程表Subject】

【成绩表Score】

四、数据维护

（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********', '班主任');

《MySQL数据操作与查询》- 维护学生信息、老师信息和成绩信息支持按多种条件组合查询学生信息和成绩信息

一、系统整体功能

二、系统的信息需求

三、表结构

四、数据维护

五、数据库导入

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

《MySQL数据操作与查询》- 维护学生信息、老师信息和成绩信息 支持按多种条件组合查询学生信息和成绩信息

一、系统整体功能

二、系统的信息需求

三、表结构

四、数据维护

五、数据库导入

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

《MySQL数据操作与查询》- 维护学生信息、老师信息和成绩信息支持按多种条件组合查询学生信息和成绩信息