# 学生管理系统

**Repository Path**: speoki/student-management-system

## Basic Information

- **Project Name**: 学生管理系统
- **Description**: springboot+thymeleaf的学生管理系统
- **Primary Language**: Java
- **License**: Apache-2.0
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 8
- **Forks**: 0
- **Created**: 2022-03-30
- **Last Updated**: 2025-05-11

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

## 学生管理系统

本学生管理系统使用springboot和thymeleaf编写代码。

用bootstrap框架搭建前端

## 使用环境
Mysql 开源的RDBMS 5.17.9
默认用户名root 密码123456
Intellij idea 2019.3.3

## sql定义
(1).在DBMS中建立“学生-课程”数据库，其关系逻辑模式如下：
Student（学生表）:Student（Sno，Sname，Ssex，Sage，Sdept）其中Sno为主键。
Course（课程表）:Course（Cno，Cname,Cpno,Ccredit）其中Cno为主键。
SC（选修表）：SC（Sno, Cno, Grade）(Sno,Cno)为主键，同时Sno参照Student.Sno，Cno参照Course.Cno。
             

1）查询全体学生的信息，设置查询条件：例如姓“刘”的学生，所有女生等等（条件可自行定义，至少针对两个字段）；
select Sno,Sname,Ssex,Sage,Sdept from student where student.Sname like '刘%' And Ssex = 0

查询所有的女生或男生可以建立一个视图(select语句存储在介质中)来增加效率
create view girl (Sno,Sname,Ssex,Sage,Sdept) as select Sno,Sname,Ssex,Sage,Sdept from student where Ssex=0;

create view boy (Sno,Sname,Ssex,Sage,Sdept) as select Sno,Sname,Ssex,Sage,Sdept from student where Ssex=1;

select * from girl where Sname like '刘'
select * from boy where Sname like '刘'


2）输出列表显示每个学生其选修的课程名集合；
select student.Sname ,Course.Cname from student,course,sc where  sc.Sno = student.Sno And course.Cno =sc.Cno

-------------

select student.Sname ,Course.Cname from sc 
right outer join course on course.Cno = sc.Cno
right outer join student on student.Sno=sc.Sno 


3）输出每门课程及其选修学生的平均成绩；
select course.Cname,avg(sc.Grade) from course, sc where course.Cno=sc.Cno GROUP BY sc.Cno;
返回了一个新的列，用一个map来接收所有的信息。根据map的键得到想要的值
然后返回页面显示

使用一个内连结的写法
select course.Cname ,avg(sc.Grade) from course inner join sc on course.Cno=sc.Cno group by sc.Cno;

使用自然连结的写法
select course.Cname ,avg(sc.Grade) from course natural join sc  group by sc.Cno;






4）查询任何学生后，修改其姓名，然后再显示；
主键查询学生
select Sno,Sname,Ssex,Sage,Sdept from student where Sno = (id)
修改学生的姓名
update student set Sname=(sname) where Sno = id
显示学生
return Student; 

5）添加新课程。
首先搜集新课程的所有信息，然后根据主键cno查询是否有相同的课程
如果相同则插入失败，否则即可存入数据库中
创建一个视图
create view c(cno) as (select cno from course)
查询操作
select cno from c where cno = id; 
判断查询结果是否为空
INSERT INTO `course` VALUES (8, '计算机组成原理', 6, 4);


       
首先编写三个数据库的DDL
Student
因为后面编写根据姓名前缀查询学生，为了查询效率更高效建立了索引。长度为3，utf8一个汉字占3个字符
1.	DROP TABLE IF EXISTS `student`;  
2.	CREATE TABLE `student`  (  
3.	  `Sno` int(5) NOT NULL,  
4.	  `Sname` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,  
5.	  `Ssex` int(1) NOT NULL,  
6.	  `Sage` int(3) NULL DEFAULT NULL,  
7.	  `Sdept` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,  
8.	  PRIMARY KEY (`Sno`) USING BTREE,  
9.	  INDEX `index1`(`Sname`(3)) USING BTREE  
10.	) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;  
Course
1.	DROP TABLE IF EXISTS `course`;  
2.	CREATE TABLE `course`  (  
3.	  `Cno` int(3) NOT NULL,  
4.	  `Cname` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,  
5.	  `Cpno` int(2) NULL DEFAULT NULL,  
6.	  `Ccredit` int(2) NULL DEFAULT NULL,  
7.	  PRIMARY KEY (`Cno`) USING BTREE,  
8.	  INDEX `Cpno`(`Cpno`) USING BTREE,  
9.	  CONSTRAINT `Cpno` FOREIGN KEY (`Cpno`) REFERENCES `course` (`Cno`) ON DELETE NO ACTION ON UPDATE CASCADE  
10.	) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;  
SC
1.	DROP TABLE IF EXISTS `sc`;  
2.	CREATE TABLE `sc`  (  
3.	  `Sno` int(5) NOT NULL,  
4.	  `Cno` int(2) NOT NULL,  
5.	  `Grade` int(3) NULL DEFAULT NULL,  
6.	  PRIMARY KEY (`Sno`, `Cno`) USING BTREE,  
7.	  INDEX `Cno`(`Cno`) USING BTREE,  
8.	  CONSTRAINT `Cno` FOREIGN KEY (`Cno`) REFERENCES `course` (`Cno`) ON DELETE NO ACTION ON UPDATE CASCADE,  
9.	  CONSTRAINT `Sno` FOREIGN KEY (`Sno`) REFERENCES `student` (`Sno`) ON DELETE NO ACTION ON UPDATE CASCADE  
10.	) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;  


 
 （2）插入表中数据
1.	INSERT INTO `student` VALUES (95001, '李勇', 1, 20, 'CS');  
2.	INSERT INTO `student` VALUES (95002, '刘晨', 0, 19, 'IS');  
3.	INSERT INTO `student` VALUES (95003, '王敏', 0, 18, 'MA');  
4.	INSERT INTO `student` VALUES (95004, '张立', 1, 18, 'IS');  
5.	INSERT INTO `student` VALUES (95005, '王强', 1, 17, 'IS');  
6.	INSERT INTO `student` VALUES (95015, '张三', 1, 20, 'CS');  
7.	INSERT INTO `student` VALUES (95019, '李四', 1, 20, 'CS');  
8.	INSERT INTO `student` VALUES (95020, '陈冬', 1, 18, 'IS');  
 


1.	INSERT INTO `course` VALUES (1, '数据库', 5, 4);  
2.	INSERT INTO `course` VALUES (2, '数学', NULL, 2);  
3.	INSERT INTO `course` VALUES (3, '信息系统', 1, 4);  
4.	INSERT INTO `course` VALUES (4, '操作系统', 6, 3);  
5.	INSERT INTO `course` VALUES (5, '数据结构', 7, 4);  
6.	INSERT INTO `course` VALUES (6, '数据处理', NULL, 2);  
7.	INSERT INTO `course` VALUES (7, 'Java语言', 6, 4);  



66
1.	INSERT INTO `sc` VALUES (95001, 1, 65);  
2.	INSERT INTO `sc` VALUES (95001, 2, 88);  
3.	INSERT INTO `sc` VALUES (95001, 3, 57);  
4.	INSERT INTO `sc` VALUES (95001, 4, 79);  
5.	INSERT INTO `sc` VALUES (95001, 5, 45);  
6.	INSERT INTO `sc` VALUES (95001, 6, 90);  
7.	INSERT INTO `sc` VALUES (95001, 7, 81);  
8.	INSERT INTO `sc` VALUES (95002, 2, 90);  
9.	INSERT INTO `sc` VALUES (95002, 3, 80);  
10.	INSERT INTO `sc` VALUES (95002, 4, 55);  
11.	INSERT INTO `sc` VALUES (95003, 2, 0);