简单java学生成绩管理系统,无UI界面,连接SQL数据库
学生成绩管理,设计学生成绩管理数据库应用程序,对课程表和学生成绩表进行数据插入、修改、删除、查询和成绩统计等操作,并从学生信息表中获得相关数据。
StudentManagement.java
import java.sql.*;
import java.util.Scanner;
public class StudentManagement {
//取得数据库的连接
String driverName="com.microsoft.sqlserver.jdbc.SQLServerDriver";//加载JDBC驱动"
String dbURL="jdbc:sqlserver://localhost:1433; DatabaseName=StudentManagement"; //JDBC协议
String userName = "test"; //用户名 (确保该用户和数据库有映射关系,即有权限访问)
String userPwd = "123456"; //密码
Connection dbConn=null;
PreparedStatement stmt=null;//Statement 提供执行基本SQL语句操作的功能
Scanner sc = new Scanner(System.in);
public void AddScore() {
String stuno;
String couno;
String score;
try {
Class.forName(driverName);
dbConn = DriverManager.getConnection(dbURL, userName, userPwd); //连接服务器和数据库
System.out.println("Connection Successful!"); //如果连接成功 控制台输出Connection Successful!
}
catch (Exception e) {
e.printStackTrace();
}
try {
System.out.print("请输入学生的学号: ");
stuno = sc.next();
System.out.print("请输入课程号:");
couno = sc.next();
System.out.print("请输入学生的成绩:");
score = sc.next();
String addsc = "INSERT INTO [SC]([Sno],[Cno],[Grade]) VALUES(?,?,?);";
stmt = dbConn.prepareStatement(addsc);
stmt.setString(1,stuno);
stmt.setString(2,couno);
stmt.setString(3,score);
stmt.executeUpdate();
System.out.println("添加成功!");
}
catch(SQLException e) {
System.out.println("添加失败!");
e.printStackTrace();
}
}
public void AddCourse() {
String couname;
String couno;
String cpno;
String credit;
try {
Class.forName(driverName);
dbConn = DriverManager.getConnection(dbURL, userName, userPwd); //连接服务器和数据库
System.out.println("Connection Successful!"); //如果连接成功 控制台输出Connection Successful!
}
catch (Exception e) {
e.printStackTrace();
}
try {
System.out.print("请输入课程名: ");
couname = sc.next();
System.out.print("请输入课程号:");
couno = sc.next();
System.out.print("请输入该课程的基础课(无基础课填0000):");
cpno = sc.next();
System.out.print("请输入该课程的学分:");
credit = sc.next();
String addcou = "INSERT INTO [Course]([Cno],[Cname],[Cpno],[Ccredit]) VALUES(?,?,?,?)";
stmt = dbConn.prepareStatement(addcou);
stmt.setString(1,couno);
stmt.setString(2,couname);
stmt.setString(3,cpno);
stmt.setString(4,credit);
stmt.executeUpdate();
System.out.println("添加成功!");
}
catch(SQLException e) {
System.out.println("添加失败!");
e.printStackTrace();
}
}
public void DeleteCourse() {
String couno;
try {
Class.forName(driverName);
dbConn = DriverManager.getConnection(dbURL, userName, userPwd); //连接服务器和数据库
System.out.println("Connection Successful!"); //如果连接成功 控制台输出Connection Successful!
}
catch (Exception e) {
e.printStackTrace();
}
try {
System.out.print("请输入课程号:");
couno = sc.next();
String delecou = "DELETE Course where Cno=?";
stmt = dbConn.prepareStatement(delecou);
stmt.setString(1,couno);
stmt.executeUpdate();
System.out.println("删除成功!");
}
catch(SQLException e) {
System.out.println("删除失败!");
e.printStackTrace();
}
}
public void DeleteScore() {
String couno;
String stuno;
try {
Class.forName(driverName);
dbConn = DriverManager.getConnection(dbURL, userName, userPwd); //连接服务器和数据库
System.out.println("Connection Successful!"); //如果连接成功 控制台输出Connection Successful!
}
catch (Exception e) {
e.printStackTrace();
}
try {
System.out.print("请输入学号:");
stuno = sc.next();
System.out.print("请输入课程号:");
couno = sc.next();
String delesc = "DELETE SC where Grade in(SELECT Grade FROM SC where Sno=? and Cno=?)";
stmt = dbConn.prepareStatement(delesc);
stmt.setString(1,stuno);
stmt.setString(2,couno);
stmt.executeUpdate();
System.out.println("删除成功!");
}
catch(SQLException e) {
System.out.println("删除失败!");
e.printStackTrace();
}
}
public void UpdateScore() {
String couno;
String stuno;
String score;
try {
Class.forName(driverName);
dbConn = DriverManager.getConnection(dbURL, userName, userPwd); //连接服务器和数据库
System.out.println("Connection Successful!"); //如果连接成功 控制台输出Connection Successful!
}
catch (Exception e) {
e.printStackTrace();
}
try {
System.out.print("请输入学号:");
stuno = sc.next();
System.out.print("请输入课程号:");
couno = sc.next();
System.out.print("请输入要改成的分数:");
score = sc.next();
String updatesc = "UPDATE SC SET Grade=? where Sno=? and Cno=?";
stmt = dbConn.prepareStatement(updatesc);
stmt.setString(1,score);
stmt.setString(2,stuno);
stmt.setString(3,couno);
stmt.executeUpdate();
System.out.println("修改成功!");
}
catch(SQLException e) {
System.out.println("修改失败!");
e.printStackTrace();
}
}
public void UpdateCourse() {
String couno;
String couname;
String precou;
String credit;
try {
Class.forName(driverName);
dbConn = DriverManager.getConnection(dbURL, userName, userPwd); //连接服务器和数据库
System.out.println("Connection Successful!"); //如果连接成功 控制台输出Connection Successful!
}
catch (Exception e) {
e.printStackTrace();
}
try {
System.out.print("请输入课程号:");
couno = sc.next();
System.out.print("请输入要改成的课程名:");
couname = sc.next();
System.out.print("请输入要改成的基础课的课程号(无基础课填0000):");
precou = sc.next();
System.out.print("请输入要改成的学分:");
credit = sc.next();
String updatecou = "UPDATE Course SET Cname=? where Cno=?;UPDATE Course SET Cpno=? where Cno=?;UPDATE Course SET Ccredit=? where Cno=?";
stmt = dbConn.prepareStatement(updatecou);
stmt.setString(1,couname);
stmt.setString(3,precou);
stmt.setString(5,credit);
stmt.setString(2,couno);
stmt.setString(4,couno);
stmt.setString(6,couno);
stmt.executeUpdate();
System.out.println("修改成功!");
}
catch(SQLException e) {
System.out.println("修改失败!");
e.printStackTrace();
}
}
public void SearchScore() {
String couno;
String stuno;
try {
Class.forName(driverName);
dbConn = DriverManager.getConnection(dbURL, userName, userPwd); //连接服务器和数据库
System.out.println("Connection Successful!"); //如果连接成功 控制台输出Connection Successful!
}
catch (Exception e) {
e.printStackTrace();
}
try {
System.out.print("请输入学号:");
stuno = sc.next();
System.out.print("请输入课程号:");
couno = sc.next();
ResultSet rs=null;
String seasc = "SELECT * FROM SC where Sno=? and Cno=?";
stmt = dbConn.prepareStatement(seasc);
stmt.setString(1,stuno);
stmt.setString(2,couno);
rs=stmt.executeQuery();
System.out.println("查询结果如下:");
System.out.println("课程号"+"\t"+"学号"+"\t"+"分数");
System.out.println("------------------------");
String counum=null;
String stunum=null;
String score=null;
//没到数据库最后一条记录就继续
while(rs.next()) {
counum=rs.getString("Cno");
stunum=rs.getString("Sno");
score=rs.getString("Grade");
System.out.println(counum+"\t"+stunum+"\t"+score);
}
rs.close();
}
catch(SQLException e) {
System.out.println("查询失败!");
e.printStackTrace();
}
}
public void SearchCourse() {
String couno;
try {
Class.forName(driverName);
dbConn = DriverManager.getConnection(dbURL, userName, userPwd); //连接服务器和数据库
System.out.println("Connection Successful!"); //如果连接成功 控制台输出Connection Successful!
}
catch (Exception e) {
e.printStackTrace();
}
try {
System.out.print("请输入课程号:");
couno = sc.next();
ResultSet rs=null;
String seacou = "SELECT * FROM Course where Cno=?";
stmt = dbConn.prepareStatement(seacou);
stmt.setString(1,couno);
rs=stmt.executeQuery();
System.out.println("查询结果如下:");
System.out.println("课程号"+"\t"+"课程名"+"\t"+"\t"+"基础课课程号"+"\t"+"学分");
System.out.println("----------------------------------------");
String counum=null;
String couname=null;
String precou=null;
String credit=null;
//没到数据库最后一条记录就继续
while(rs.next()) {
counum=rs.getString("Cno");
couname=rs.getString("Cname");
precou=rs.getString("Cpno");
credit=rs.getString("Ccredit");
System.out.println(counum+"\t"+couname+precou+"\t"+credit);
}
rs.close();
}
catch(SQLException e) {
System.out.println("查询失败!");
e.printStackTrace();
}
}
public void Statisticse() {
String couno;
try {
Class.forName(driverName);
dbConn = DriverManager.getConnection(dbURL, userName, userPwd); //连接服务器和数据库
System.out.println("Connection Successful!"); //如果连接成功 控制台输出Connection Successful!
}
catch (Exception e) {
e.printStackTrace();
}
try {
System.out.print("请输入课程号:");
couno = sc.next();
ResultSet rs=null;
String sta = "SELECT * FROM SC where Cno=?";
stmt = dbConn.prepareStatement(sta);
stmt.setString(1,couno);
rs=stmt.executeQuery();
System.out.println("查询结果如下:");
System.out.println("课程号"+"\t"+"学号"+"\t"+"分数");
System.out.println("------------------------");
String counum=null;
String stunum=null;
int score=0;
int sum = 0;
int max = 0;
int min = 101;
double count = 0;
//没到数据库最后一条记录就继续
while(rs.next()) {
counum=rs.getString("Cno");
stunum=rs.getString("Sno");
score=rs.getInt("Grade");
sum=sum+score;
max=Math.max(score, max);
min=Math.min(score, min);
count++;
System.out.println(counum+"\t"+stunum+"\t"+score);
}
System.out.println("本门课程平均分为:"+sum/count);
System.out.println("最高分为:"+max);
System.out.println("最低分为:"+min);
rs.close();
}
catch(SQLException e) {
System.out.println("查询失败!");
e.printStackTrace();
}
}
}
Test.java
import java.util.Scanner;
public class Test {
public static void main(String[] args) {
Scanner input = new Scanner(System.in);
String choice = "1";
StudentManagement sm = new StudentManagement();
System.out.println("**********学生成绩管理系统********");
while(choice.equals("0") == false) {
System.out.println("1.添加某位学生成绩记录");
System.out.println("2.修改某位学生成绩记录");
System.out.println("3.删除某位学生成绩记录");
System.out.println("4.查看某位学生成绩记录");
System.out.println("5.查看课程记录");
System.out.println("6.添加课程记录");
System.out.println("7.删除课程记录");
System.out.println("8.修改课程记录");
System.out.println("9.统计某门课平均分,最高分和最低分");
System.out.println("0.退出程序");
System.out.print("Enter your choice: ");
choice = input.next();
switch(choice) {
case "0":
System.out.println("谢谢您的使用,欢迎下次光临!\n" + "**********按任意键结束程序**********");
input.close();
break;
case "1":
int j = 1;
do {
sm.AddScore();
System.out.println("\n是否继续添加?" + "\n" + "0.否" + "\n" + "1.是");
j = input.nextInt();
}while(j == 1);
System.out.println("请问您还需要什么服务?\n");
break;
case "2":
int i = 1;
do {
sm.UpdateScore();
System.out.println("\n是否继续修改?" + "\n" + "0.否" + "\n" + "1.是");
i = input.nextInt();
}while(i == 1);
System.out.println("请问您还需要什么服务?\n");
break;
case "3":
sm.DeleteScore();
System.out.println("请问您还需要什么服务?\n");
break;
case "4":
sm.SearchScore();
System.out.println("请问您还需要什么服务?\n");
break;
case "5":
sm.SearchCourse();
System.out.println("请问您还需要什么服务?\n");
break;
case "6":
int k = 1;
do {
sm.AddCourse();
System.out.println("\n是否继续添加?" + "\n" + "0.否" + "\n" + "1.是");
k = input.nextInt();
}while(k == 1);
System.out.println("请问您还需要什么服务?\n");
break;
case "7":
sm.DeleteCourse();
System.out.println("请问您还需要什么服务?\n");
break;
case "8":
int l = 1;
do {
sm.UpdateCourse();
System.out.println("\n是否继续修改?" + "\n" + "0.否" + "\n" + "1.是");
l = input.nextInt();
}while(l == 1);
System.out.println("请问您还需要什么服务?\n");
break;
case "9":
sm.Statisticse();
System.out.println("请问您还需要什么服务?\n");
break;
default:
System.out.println("Invalid input! Please enter again.");
break;
}
}
}
}
数据库
数据库代码借用前辈的代码后稍加修改
数据库建表代码
create table Student
(
Sno char(10)primary key,
Sname char(10) unique,
Ssex char(2) check (Ssex in ('男','女')),
Sage smallint check(Sage between 18 and 20),
Sdept char(20),
);
create table Course(
Cno char(4) primary key,
Cname char(20) not null,
Cpno char(4),
Ccredit smallint,
foreign key (Cpno) references Course(Cno),
);
create table SC(
Sno char(10),
Cno char(4),
Grade smallint,
primary key(Sno,Cno),
foreign key(Sno) references Student(Sno) on delete cascade,
foreign key(Cno) references Course(Cno) on delete cascade
);
insert into dbo.Student(Sno, Sname, Ssex, Sage, Sdept)
values('60001','zhangsan','女',18,'art'),
('60002','lisi','女',18,'it'),
('60003','wangwu','女',18,'art'),
('60004','chenliu','女',18,'pe'),
('60005','tisi','女',18,'pe');
INSERT INTO [Course]([Cno],[Cname],[Cpno],[Ccredit])
VALUES ('0000','0000',null,0),
('1000','c#','1002',100),
('1001','asp.net','1000',100),
('1002','c',null,100),
('1003','HTML',null,100),
('1004','python',null,100),
('1005','django','1004',100)
GO
INSERT INTO [SC]([Sno],[Cno],[Grade])
VALUES('60001','1000','48'),
('60002','1003','98'),
('60001','1001','56'),
('60001','1004','83'),
('60001','1003','35'),
('60002','1002','71'),
('60003','1005','49'),
('60005','1002','37')
GO
Cno char(4) primary key,
Cname char(20) not null,
Cpno char(4),
Ccredit smallint,
foreign key (Cpno) references Course(Cno),
);
create table SC(
Sno char(10),
Cno char(4),
Grade smallint,
primary key(Sno,Cno),
foreign key(Sno) references Student(Sno),
foreign key(Cno) references Course(Cno)
);
insert into dbo.Student(Sno, Sname, Ssex, Sage, Sdept)
values('60001','zhangsan','女',18,'art'),
('60002','lisi','女',18,'it'),
('60003','wangwu','女',18,'art'),
('60004','chenliu','女',18,'pe'),
('60005','tisi','女',18,'pe');
INSERT INTO [Course]([Cno],[Cname],[Cpno],[Ccredit])
VALUES('1000','c#','1002',100),
('1001','asp.net','1000',100),
('1002','c',null,100),
('1003','HTML',null,100),
('1004','python',null,100),
('1005','django','1004',100)
GO
INSERT INTO [SC]([Sno],[Cno],[Grade])
VALUES('60001','1000','48'),
('60002','1003','98'),
('60001','1001','56'),
('60001','1004','83'),
('60001','1003','35'),
('60002','1002','71'),
('60003','1005','49'),
('60005','1002','37')
GO
无UI界面的简单学生管理系统就完成了
仅供新手参考
运行结果:
学生成绩管理系统
1.添加某位学生成绩记录
2.修改某位学生成绩记录
3.删除某位学生成绩记录
4.查看某位学生成绩记录
5.查看课程记录
6.添加课程记录
7.删除课程记录
8.修改课程记录
9.统计某门课平均分,最高分和最低分
0.退出程序
Enter your choice: 1
Connection Successful!
请输入学生的学号: 60003
请输入课程号:1002
请输入学生的成绩:99
添加成功!
是否继续添加?
0.否
1.是
0
请问您还需要什么服务?
1.添加某位学生成绩记录
2.修改某位学生成绩记录
3.删除某位学生成绩记录
4.查看某位学生成绩记录
5.查看课程记录
6.添加课程记录
7.删除课程记录
8.修改课程记录
9.统计某门课平均分,最高分和最低分
0.退出程序
Enter your choice: 2
Connection Successful!
请输入学号:60002
请输入课程号:1003
请输入要改成的分数:96
修改成功!
是否继续修改?
0.否
1.是