import java.sql.*;
import java.util.Scanner;
public class StudentManagementSystem {
// 数据库连接信息
private static final String JDBC_URL = "jdbc:mysql://localhost:3306/student_management_system";
private static final String JDBC_USER = "root";
private static final String JDBC_PASSWORD = "yourpassword"; // 替换为你的数据库密码
private Connection connection;
private Scanner scanner;
public StudentManagementSystem() {
scanner = new Scanner(System.in);
try {
// 加载JDBC驱动
Class.forName("com.mysql.cj.jdbc.Driver");
// 建立数据库连接
connection = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);
System.out.println("数据库连接成功!");
} catch (Exception e) {
System.err.println("数据库连接失败: " + e.getMessage());
}
}
public static void main(String[] args) {
StudentManagementSystem system = new StudentManagementSystem();
system.run();
}
public void run() {
while (true) {
System.out.println("\n=== 学生管理系统 ===");
System.out.println("1. 学生管理");
System.out.println("2. 课程管理");
System.out.println("3. 成绩管理");
System.out.println("4. 查询统计");
System.out.println("5. 退出系统");
System.out.print("请选择主菜单(1-5): ");
int choice = scanner.nextInt();
scanner.nextLine(); // 消耗换行符
switch (choice) {
case 1:
studentMenu();
break;
case 2:
courseMenu();
break;
case 3:
scoreMenu();
break;
case 4:
queryMenu();
break;
case 5:
System.out.println("感谢使用学生管理系统,再见!");
try {
if (connection != null) connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
return;
default:
System.out.println("无效的选择,请重新输入!");
}
}
}
// 学生管理子菜单
private void studentMenu() {
while (true) {
System.out.println("\n=== 学生管理 ===");
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.print("请选择操作(1-6): ");
int choice = scanner.nextInt();
scanner.nextLine();
switch (choice) {
case 1:
addStudent();
break;
case 2:
listAllStudents();
break;
case 3:
getStudentById();
break;
case 4:
updateStudent();
break;
case 5:
deleteStudent();
break;
case 6:
return;
default:
System.out.println("无效的选择,请重新输入!");
}
}
}
// 课程管理子菜单
private void courseMenu() {
while (true) {
System.out.println("\n=== 课程管理 ===");
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.print("请选择操作(1-6): ");
int choice = scanner.nextInt();
scanner.nextLine();
switch (choice) {
case 1:
addCourse();
break;
case 2:
listAllCourses();
break;
case 3:
getCourseByCode();
break;
case 4:
updateCourse();
break;
case 5:
deleteCourse();
break;
case 6:
return;
default:
System.out.println("无效的选择,请重新输入!");
}
}
}
// 成绩管理子菜单
private void scoreMenu() {
while (true) {
System.out.println("\n=== 成绩管理 ===");
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.print("请选择操作(1-7): ");
int choice = scanner.nextInt();
scanner.nextLine();
switch (choice) {
case 1:
addScore();
break;
case 2:
listAllScores();
break;
case 3:
getScoresByStudent();
break;
case 4:
getScoresByCourse();
break;
case 5:
updateScore();
break;
case 6:
deleteScore();
break;
case 7:
return;
default:
System.out.println("无效的选择,请重新输入!");
}
}
}
// 查询统计子菜单
private void queryMenu() {
while (true) {
System.out.println("\n=== 查询统计 ===");
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.print("请选择操作(1-6): ");
int choice = scanner.nextInt();
scanner.nextLine();
switch (choice) {
case 1:
queryStudentsByClass();
break;
case 2:
queryStudentsByMajor();
break;
case 3:
queryCourseAverage();
break;
case 4:
queryStudentAverage();
break;
case 5:
queryScoreRanking();
break;
case 6:
return;
default:
System.out.println("无效的选择,请重新输入!");
}
}
}
// ========== 学生管理方法 ==========
private void addStudent() {
System.out.println("\n--- 添加新学生 ---");
System.out.print("学号: ");
String studentId = scanner.nextLine();
System.out.print("姓名: ");
String name = scanner.nextLine();
System.out.print("性别(M/F): ");
String gender = scanner.nextLine();
System.out.print("年龄: ");
int age = scanner.nextInt();
scanner.nextLine();
System.out.print("班级: ");
String className = scanner.nextLine();
System.out.print("专业: ");
String major = scanner.nextLine();
System.out.print("电话: ");
String phone = scanner.nextLine();
System.out.print("邮箱: ");
String email = scanner.nextLine();
System.out.print("入学日期(YYYY-MM-DD): ");
String admissionDate = scanner.nextLine();
String sql = "INSERT INTO students (student_id, name, gender, age, class_name, major, phone, email, admission_date) " +
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
try (PreparedStatement statement = connection.prepareStatement(sql)) {
statement.setString(1, studentId);
statement.setString(2, name);
statement.setString(3, gender);
statement.setInt(4, age);
statement.setString(5, className);
statement.setString(6, major);
statement.setString(7, phone);
statement.setString(8, email);
statement.setString(9, admissionDate);
int rowsInserted = statement.executeUpdate();
if (rowsInserted > 0) {
System.out.println("学生添加成功!");
}
} catch (SQLException e) {
System.err.println("添加学生失败: " + e.getMessage());
}
}
private void listAllStudents() {
System.out.println("\n--- 所有学生列表 ---");
String sql = "SELECT * FROM students ORDER BY class_name, student_id";
try (Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(sql)) {
System.out.printf("%-10s %-15s %-5s %-5s %-15s %-20s %-15s %-25s %-12s%n",
"学号", "姓名", "性别", "年龄", "班级", "专业", "电话", "邮箱", "入学日期");
System.out.println("--------------------------------------------------------------------------------------------------------");
while (resultSet.next()) {
System.out.printf("%-10s %-15s %-5s %-5d %-15s %-20s %-15s %-25s %-12s%n",
resultSet.getString("student_id"),
resultSet.getString("name"),
resultSet.getString("gender"),
resultSet.getInt("age"),
resultSet.getString("class_name"),
resultSet.getString("major"),
resultSet.getString("phone"),
resultSet.getString("email"),
resultSet.getDate("admission_date"));
}
} catch (SQLException e) {
System.err.println("查询学生列表失败: " + e.getMessage());
}
}
private void getStudentById() {
System.out.print("\n请输入要查询的学生学号: ");
String studentId = scanner.nextLine();
String sql = "SELECT * FROM students WHERE student_id = ?";
try (PreparedStatement statement = connection.prepareStatement(sql)) {
statement.setString(1, studentId);
ResultSet resultSet = statement.executeQuery();
if (resultSet.next()) {
System.out.println("\n--- 学生详细信息 ---");
System.out.println("学号: " + resultSet.getString("student_id"));
System.out.println("姓名: " + resultSet.getString("name"));
System.out.println("性别: " + resultSet.getString("gender"));
System.out.println("年龄: " + resultSet.getInt("age"));
System.out.println("班级: " + resultSet.getString("class_name"));
System.out.println("专业: " + resultSet.getString("major"));
System.out.println("电话: " + resultSet.getString("phone"));
System.out.println("邮箱: " + resultSet.getString("email"));
System.out.println("入学日期: " + resultSet.getDate("admission_date"));
} else {
System.out.println("未找到学号为 " + studentId + " 的学生");
}
} catch (SQLException e) {
System.err.println("查询学生失败: " + e.getMessage());
}
}
private void updateStudent() {
System.out.print("\n请输入要更新的学生学号: ");
String studentId = scanner.nextLine();
// 先查询学生是否存在
String checkSql = "SELECT * FROM students WHERE student_id = ?";
try (PreparedStatement checkStatement = connection.prepareStatement(checkSql)) {
checkStatement.setString(1, studentId);
ResultSet resultSet = checkStatement.executeQuery();
if (!resultSet.next()) {
System.out.println("未找到学号为 " + studentId + " 的学生");
return;
}
// 显示当前信息
System.out.println("\n当前学生信息:");
System.out.println("1. 学号: " + resultSet.getString("student_id"));
System.out.println("2. 姓名: " + resultSet.getString("name"));
System.out.println("3. 性别: " + resultSet.getString("gender"));
System.out.println("4. 年龄: " + resultSet.getInt("age"));
System.out.println("5. 班级: " + resultSet.getString("class_name"));
System.out.println("6. 专业: " + resultSet.getString("major"));
System.out.println("7. 电话: " + resultSet.getString("phone"));
System.out.println("8. 邮箱: " + resultSet.getString("email"));
System.out.println("9. 入学日期: " + resultSet.getDate("admission_date"));
System.out.print("\n请输入要更新的字段编号(1-9): ");
int field = scanner.nextInt();
scanner.nextLine();
String fieldName = "";
String newValue = "";
switch (field) {
case 1:
fieldName = "student_id";
System.out.print("输入新学号: ");
newValue = scanner.nextLine();
break;
case 2:
fieldName = "name";
System.out.print("输入新姓名: ");
newValue = scanner.nextLine();
break;
case 3:
fieldName = "gender";
System.out.print("输入新性别(M/F): ");
newValue = scanner.nextLine();
break;
case 4:
fieldName = "age";
System.out.print("输入新年龄: ");
newValue = String.valueOf(scanner.nextInt());
scanner.nextLine();
break;
case 5:
fieldName = "class_name";
System.out.print("输入新班级: ");
newValue = scanner.nextLine();
break;
case 6:
fieldName = "major";
System.out.print("输入新专业: ");
newValue = scanner.nextLine();
break;
case 7:
fieldName = "phone";
System.out.print("输入新电话: ");
newValue = scanner.nextLine();
break;
case 8:
fieldName = "email";
System.out.print("输入新邮箱: ");
newValue = scanner.nextLine();
break;
case 9:
fieldName = "admission_date";
System.out.print("输入新入学日期(YYYY-MM-DD): ");
newValue = scanner.nextLine();
break;
default:
System.out.println("无效的字段编号");
return;
}
String updateSql = "UPDATE students SET " + fieldName + " = ? WHERE student_id = ?";
try (PreparedStatement updateStatement = connection.prepareStatement(updateSql)) {
updateStatement.setString(1, newValue);
updateStatement.setString(2, studentId);
int rowsUpdated = updateStatement.executeUpdate();
if (rowsUpdated > 0) {
System.out.println("学生信息更新成功!");
}
}
} catch (SQLException e) {
System.err.println("更新学生信息失败: " + e.getMessage());
}
}
private void deleteStudent() {
System.out.print("\n请输入要删除的学生学号: ");
String studentId = scanner.nextLine();
// 先检查是否有成绩记录
String checkSql = "SELECT COUNT(*) FROM scores WHERE student_id = ?";
try (PreparedStatement checkStatement = connection.prepareStatement(checkSql)) {
checkStatement.setString(1, studentId);
ResultSet resultSet = checkStatement.executeQuery();
if (resultSet.next() && resultSet.getInt(1) > 0) {
System.out.println("该学生有成绩记录,请先删除成绩记录!");
return;
}
} catch (SQLException e) {
System.err.println("检查成绩记录失败: " + e.getMessage());
return;
}
String sql = "DELETE FROM students WHERE student_id = ?";
try (PreparedStatement statement = connection.prepareStatement(sql)) {
statement.setString(1, studentId);
int rowsDeleted = statement.executeUpdate();
if (rowsDeleted > 0) {
System.out.println("学生删除成功!");
} else {
System.out.println("未找到学号为 " + studentId + " 的学生");
}
} catch (SQLException e) {
System.err.println("删除学生失败: " + e.getMessage());
}
}
// ========== 课程管理方法 ==========
private void addCourse() {
System.out.println("\n--- 添加新课程 ---");
System.out.print("课程代码: ");
String courseCode = scanner.nextLine();
System.out.print("课程名称: ");
String courseName = scanner.nextLine();
System.out.print("学分: ");
int credit = scanner.nextInt();
scanner.nextLine();
System.out.print("授课教师: ");
String teacher = scanner.nextLine();
String sql = "INSERT INTO courses (course_code, course_name, credit, teacher) VALUES (?, ?, ?, ?)";
try (PreparedStatement statement = connection.prepareStatement(sql)) {
statement.setString(1, courseCode);
statement.setString(2, courseName);
statement.setInt(3, credit);
statement.setString(4, teacher);
int rowsInserted = statement.executeUpdate();
if (rowsInserted > 0) {
System.out.println("课程添加成功!");
}
} catch (SQLException e) {
System.err.println("添加课程失败: " + e.getMessage());
}
}
private void listAllCourses() {
System.out.println("\n--- 所有课程列表 ---");
String sql = "SELECT * FROM courses ORDER BY course_code";
try (Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(sql)) {
System.out.printf("%-10s %-30s %-6s %-15s%n",
"课程代码", "课程名称", "学分", "授课教师");
System.out.println("----------------------------------------------------");
while (resultSet.next()) {
System.out.printf("%-10s %-30s %-6d %-15s%n",
resultSet.getString("course_code"),
resultSet.getString("course_name"),
resultSet.getInt("credit"),
resultSet.getString("teacher"));
}
} catch (SQLException e) {
System.err.println("查询课程列表失败: " + e.getMessage());
}
}
private void getCourseByCode() {
System.out.print("\n请输入要查询的课程代码: ");
String courseCode = scanner.nextLine();
String sql = "SELECT * FROM courses WHERE course_code = ?";
try (PreparedStatement statement = connection.prepareStatement(sql)) {
statement.setString(1, courseCode);
ResultSet resultSet = statement.executeQuery();
if (resultSet.next()) {
System.out.println("\n--- 课程详细信息 ---");
System.out.println("课程代码: " + resultSet.getString("course_code"));
System.out.println("课程名称: " + resultSet.getString("course_name"));
System.out.println("学分: " + resultSet.getInt("credit"));
System.out.println("授课教师: " + resultSet.getString("teacher"));
} else {
System.out.println("未找到课程代码为 " + courseCode + " 的课程");
}
} catch (SQLException e) {
System.err.println("查询课程失败: " + e.getMessage());
}
}
private void updateCourse() {
System.out.print("\n请输入要更新的课程代码: ");
String courseCode = scanner.nextLine();
// 先查询课程是否存在
String checkSql = "SELECT * FROM courses WHERE course_code = ?";
try (PreparedStatement checkStatement = connection.prepareStatement(checkSql)) {
checkStatement.setString(1, courseCode);
ResultSet resultSet = checkStatement.executeQuery();
if (!resultSet.next()) {
System.out.println("未找到课程代码为 " + courseCode + " 的课程");
return;
}
// 显示当前信息
System.out.println("\n当前课程信息:");
System.out.println("1. 课程代码: " + resultSet.getString("course_code"));
System.out.println("2. 课程名称: " + resultSet.getString("course_name"));
System.out.println("3. 学分: " + resultSet.getInt("credit"));
System.out.println("4. 授课教师: " + resultSet.getString("teacher"));
System.out.print("\n请输入要更新的字段编号(1-4): ");
int field = scanner.nextInt();
scanner.nextLine();
String fieldName = "";
String newValue = "";
switch (field) {
case 1:
fieldName = "course_code";
System.out.print("输入新课程代码: ");
newValue = scanner.nextLine();
break;
case 2:
fieldName = "course_name";
System.out.print("输入新课程名称: ");
newValue = scanner.nextLine();
break;
case 3:
fieldName = "credit";
System.out.print("输入新学分: ");
newValue = String.valueOf(scanner.nextInt());
scanner.nextLine();
break;
case 4:
fieldName = "teacher";
System.out.print("输入新授课教师: ");
newValue = scanner.nextLine();
break;
default:
System.out.println("无效的字段编号");
return;
}
String updateSql = "UPDATE courses SET " + fieldName + " = ? WHERE course_code = ?";
try (PreparedStatement updateStatement = connection.prepareStatement(updateSql)) {
updateStatement.setString(1, newValue);
updateStatement.setString(2, courseCode);
int rowsUpdated = updateStatement.executeUpdate();
if (rowsUpdated > 0) {
System.out.println("课程信息更新成功!");
}
}
} catch (SQLException e) {
System.err.println("更新课程信息失败: " + e.getMessage());
}
}
private void deleteCourse() {
System.out.print("\n请输入要删除的课程代码: ");
String courseCode = scanner.nextLine();
// 先检查是否有成绩记录
String checkSql = "SELECT COUNT(*) FROM scores WHERE course_code = ?";
try (PreparedStatement checkStatement = connection.prepareStatement(checkSql)) {
checkStatement.setString(1, courseCode);
ResultSet resultSet = checkStatement.executeQuery();
if (resultSet.next() && resultSet.getInt(1) > 0) {
System.out.println("该课程有成绩记录,请先删除成绩记录!");
return;
}
} catch (SQLException e) {
System.err.println("检查成绩记录失败: " + e.getMessage());
return;
}
String sql = "DELETE FROM courses WHERE course_code = ?";
try (PreparedStatement statement = connection.prepareStatement(sql)) {
statement.setString(1, courseCode);
int rowsDeleted = statement.executeUpdate();
if (rowsDeleted > 0) {
System.out.println("课程删除成功!");
} else {
System.out.println("未找到课程代码为 " + courseCode + " 的课程");
}
} catch (SQLException e) {
System.err.println("删除课程失败: " + e.getMessage());
}
}
// ========== 成绩管理方法 ==========
private void addScore() {
System.out.println("\n--- 添加成绩记录 ---");
System.out.print("学生学号: ");
String studentId = scanner.nextLine();
System.out.print("课程代码: ");
String courseCode = scanner.nextLine();
System.out.print("分数: ");
double score = scanner.nextDouble();
scanner.nextLine();
System.out.print("学期(如:2023-2024-1): ");
String semester = scanner.nextLine();
String sql = "INSERT INTO scores (student_id, course_code, score, semester) VALUES (?, ?, ?, ?)";
try (PreparedStatement statement = connection.prepareStatement(sql)) {
statement.setString(1, studentId);
statement.setString(2, courseCode);
statement.setDouble(3, score);
statement.setString(4, semester);
int rowsInserted = statement.executeUpdate();
if (rowsInserted > 0) {
System.out.println("成绩记录添加成功!");
}
} catch (SQLException e) {
System.err.println("添加成绩记录失败: " + e.getMessage());
}
}
private void listAllScores() {
System.out.println("\n--- 所有成绩记录 ---");
String sql = "SELECT s.*, st.name AS student_name, co.course_name " +
"FROM scores s " +
"JOIN students st ON s.student_id = st.student_id " +
"JOIN courses co ON s.course_code = co.course_code " +
"ORDER BY s.semester, s.course_code, s.student_id";
try (Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(sql)) {
System.out.printf("%-10s %-15s %-10s %-30s %-6s %-15s%n",
"学号", "姓名", "课程代码", "课程名称", "分数", "学期");
System.out.println("--------------------------------------------------------------------------------");
while (resultSet.next()) {
System.out.printf("%-10s %-15s %-10s %-30s %-6.1f %-15s%n",
resultSet.getString("student_id"),
resultSet.getString("student_name"),
resultSet.getString("course_code"),
resultSet.getString("course_name"),
resultSet.getDouble("score"),
resultSet.getString("semester"));
}
} catch (SQLException e) {
System.err.println("查询成绩列表失败: " + e.getMessage());
}
}
private void getScoresByStudent() {
System.out.print("\n请输入要查询的学生学号: ");
String studentId = scanner.nextLine();
String sql = "SELECT s.*, co.course_name, co.credit " +
"FROM scores s " +
"JOIN courses co ON s.course_code = co.course_code " +
"WHERE s.student_id = ? " +
"ORDER BY s.semester, s.course_code";
try (PreparedStatement statement = connection.prepareStatement(sql)) {
statement.setString(1, studentId);
ResultSet resultSet = statement.executeQuery();
System.out.println("\n--- 学生成绩记录 ---");
System.out.printf("%-10s %-30s %-6s %-6s %-15s%n",
"课程代码", "课程名称", "学分", "分数", "学期");
System.out.println("----------------------------------------------------");
boolean hasRecords = false;
while (resultSet.next()) {
hasRecords = true;
System.out.printf("%-10s %-30s %-6d %-6.1f %-15s%n",
resultSet.getString("course_code"),
resultSet.getString("course_name"),
resultSet.getInt("credit"),
resultSet.getDouble("score"),
resultSet.getString("semester"));
}
if (!hasRecords) {
System.out.println("未找到学号为 " + studentId + " 的成绩记录");
}
} catch (SQLException e) {
System.err.println("查询学生成绩失败: " + e.getMessage());
}
}
private void getScoresByCourse() {
System.out.print("\n请输入要查询的课程代码: ");
String courseCode = scanner.nextLine();
String sql = "SELECT s.*, st.name AS student_name, st.class_name " +
"FROM scores s " +
"JOIN students st ON s.student_id = st.student_id " +
"WHERE s.course_code = ? " +
"ORDER BY s.score DESC";
try (PreparedStatement statement = connection.prepareStatement(sql)) {
statement.setString(1, courseCode);
ResultSet resultSet = statement.executeQuery();
System.out.println("\n--- 课程成绩记录 ---");
System.out.printf("%-10s %-15s %-15s %-6s %-15s%n",
"学号", "姓名", "班级", "分数", "学期");
System.out.println("----------------------------------------------------");
boolean hasRecords = false;
while (resultSet.next()) {
hasRecords = true;
System.out.printf("%-10s %-15s %-15s %-6.1f %-15s%n",
resultSet.getString("student_id"),
resultSet.getString("student_name"),
resultSet.getString("class_name"),
resultSet.getDouble("score"),
resultSet.getString("semester"));
}
if (!hasRecords) {
System.out.println("未找到课程代码为 " + courseCode + " 的成绩记录");
}
} catch (SQLException e) {
System.err.println("查询课程成绩失败: " + e.getMessage());
}
}
private void updateScore() {
System.out.print("\n请输入要更新的成绩记录ID: ");
int scoreId = scanner.nextInt();
scanner.nextLine();
// 先查询成绩记录是否存在
String checkSql = "SELECT * FROM scores WHERE id = ?";
try (PreparedStatement checkStatement = connection.prepareStatement(checkSql)) {
checkStatement.setInt(1, scoreId);
ResultSet resultSet = checkStatement.executeQuery();
if (!resultSet.next()) {
System.out.println("未找到ID为 " + scoreId + " 的成绩记录");
return;
}
// 显示当前信息
System.out.println("\n当前成绩记录:");
System.out.println("学号: " + resultSet.getString("student_id"));
System.out.println("课程代码: " + resultSet.getString("course_code"));
System.out.println("分数: " + resultSet.getDouble("score"));
System.out.println("学期: " + resultSet.getString("semester"));
System.out.print("\n请输入新分数: ");
double newScore = scanner.nextDouble();
scanner.nextLine();
String updateSql = "UPDATE scores SET score = ? WHERE id = ?";
try (PreparedStatement updateStatement = connection.prepareStatement(updateSql)) {
updateStatement.setDouble(1, newScore);
updateStatement.setInt(2, scoreId);
int rowsUpdated = updateStatement.executeUpdate();
if (rowsUpdated > 0) {
System.out.println("成绩记录更新成功!");
}
}
} catch (SQLException e) {
System.err.println("更新成绩记录失败: " + e.getMessage());
}
}
private void deleteScore() {
System.out.print("\n请输入要删除的成绩记录ID: ");
int scoreId = scanner.nextInt();
scanner.nextLine();
String sql = "DELETE FROM scores WHERE id = ?";
try (PreparedStatement statement = connection.prepareStatement(sql)) {
statement.setInt(1, scoreId);
int rowsDeleted = statement.executeUpdate();
if (rowsDeleted > 0) {
System.out.println("成绩记录删除成功!");
} else {
System.out.println("未找到ID为 " + scoreId + " 的成绩记录");
}
} catch (SQLException e) {
System.err.println("删除成绩记录失败: " + e.getMessage());
}
}
// ========== 查询统计方法 ==========
private void queryStudentsByClass() {
System.out.print("\n请输入要查询的班级名称: ");
String className = scanner.nextLine();
String sql = "SELECT * FROM students WHERE class_name = ? ORDER BY student_id";
try (PreparedStatement statement = connection.prepareStatement(sql)) {
statement.setString(1, className);
ResultSet resultSet = statement.executeQuery();
System.out.println("\n--- 班级学生列表 ---");
System.out.printf("%-10s %-15s %-5s %-5s %-20s %-15s %-25s%n",
"学号", "姓名", "性别", "年龄", "专业", "电话", "邮箱");
System.out.println("----------------------------------------------------------------------------");
boolean hasRecords = false;
while (resultSet.next()) {
hasRecords = true;
System.out.printf("%-10s %-15s %-5s %-5d %-20s %-15s %-25s%n",
resultSet.getString("student_id"),
resultSet.getString("name"),
resultSet.getString("gender"),
resultSet.getInt("age"),
resultSet.getString("major"),
resultSet.getString("phone"),
resultSet.getString("email"));
}
if (!hasRecords) {
System.out.println("未找到班级为 " + className + " 的学生");
}
} catch (SQLException e) {
System.err.println("查询班级学生失败: " + e.getMessage());
}
}
private void queryStudentsByMajor() {
System.out.print("\n请输入要查询的专业名称: ");
String major = scanner.nextLine();
String sql = "SELECT * FROM students WHERE major = ? ORDER BY class_name, student_id";
try (PreparedStatement statement = connection.prepareStatement(sql)) {
statement.setString(1, major);
ResultSet resultSet = statement.executeQuery();
System.out.println("\n--- 专业学生列表 ---");
System.out.printf("%-10s %-15s %-5s %-5s %-15s %-15s %-25s%n",
"学号", "姓名", "性别", "年龄", "班级", "电话", "邮箱");
System.out.println("------------------------------------------------------------------------");
boolean hasRecords = false;
while (resultSet.next()) {
hasRecords = true;
System.out.printf("%-10s %-15s %-5s %-5d %-15s %-15s %-25s%n",
resultSet.getString("student_id"),
resultSet.getString("name"),
resultSet.getString("gender"),
resultSet.getInt("age"),
resultSet.getString("class_name"),
resultSet.getString("phone"),
resultSet.getString("email"));
}
if (!hasRecords) {
System.out.println("未找到专业为 " + major + " 的学生");
}
} catch (SQLException e) {
System.err.println("查询专业学生失败: " + e.getMessage());
}
}
private void queryCourseAverage() {
System.out.println("\n--- 课程平均分统计 ---");
String sql = "SELECT c.course_code, c.course_name, COUNT(s.score) AS student_count, " +
"AVG(s.score) AS average_score, MAX(s.score) AS max_score, MIN(s.score) AS min_score " +
"FROM courses c LEFT JOIN scores s ON c.course_code = s.course_code " +
"GROUP BY c.course_code, c.course_name " +
"ORDER BY average_score DESC";
try (Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(sql)) {
System.out.printf("%-10s %-30s %-10s %-10s %-10s %-10s%n",
"课程代码", "课程名称", "学生人数", "平均分", "最高分", "最低分");
System.out.println("--------------------------------------------------------------------");
while (resultSet.next()) {
double avgScore = resultSet.getDouble("average_score");
System.out.printf("%-10s %-30s %-10d %-10.1f %-10.1f %-10.1f%n",
resultSet.getString("course_code"),
resultSet.getString("course_name"),
resultSet.getInt("student_count"),
Double.isNaN(avgScore) ? 0 : avgScore,
resultSet.getDouble("max_score"),
resultSet.getDouble("min_score"));
}
} catch (SQLException e) {
System.err.println("查询课程平均分失败: " + e.getMessage());
}
}
private void queryStudentAverage() {
System.out.println("\n--- 学生平均分统计 ---");
String sql = "SELECT st.student_id, st.name, st.class_name, COUNT(sc.score) AS course_count, " +
"AVG(sc.score) AS average_score, SUM(c.credit) AS total_credit " +
"FROM students st LEFT JOIN scores sc ON st.student_id = sc.student_id " +
"LEFT JOIN courses c ON sc.course_code = c.course_code " +
"GROUP BY st.student_id, st.name, st.class_name " +
"HAVING course_count > 0 " +
"ORDER BY average_score DESC";
try (Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(sql)) {
System.out.printf("%-10s %-15s %-15s %-10s %-10s %-10s%n",
"学号", "姓名", "班级", "课程数", "平均分", "总学分");
System.out.println("----------------------------------------------------------------");
while (resultSet.next()) {
System.out.printf("%-10s %-15s %-15s %-10d %-10.1f %-10d%n",
resultSet.getString("student_id"),
resultSet.getString("name"),
resultSet.getString("class_name"),
resultSet.getInt("course_count"),
resultSet.getDouble("average_score"),
resultSet.getInt("total_credit"));
}
} catch (SQLException e) {
System.err.println("查询学生平均分失败: " + e.getMessage());
}
}
private void queryScoreRanking() {
System.out.print("\n请输入要排名的课程代码: ");
String courseCode = scanner.nextLine();
String sql = "SELECT s.student_id, st.name, st.class_name, s.score, " +
"RANK() OVER (ORDER BY s.score DESC) AS ranking " +
"FROM scores s " +
"JOIN students st ON s.student_id = st.student_id " +
"WHERE s.course_code = ? " +
"ORDER BY s.score DESC";
try (PreparedStatement statement = connection.prepareStatement(sql)) {
statement.setString(1, courseCode);
ResultSet resultSet = statement.executeQuery();
System.out.println("\n--- 课程成绩排名 ---");
System.out.printf("%-5s %-10s %-15s %-15s %-6s%n",
"排名", "学号", "姓名", "班级", "分数");
System.out.println("---------------------------------------------");
boolean hasRecords = false;
while (resultSet.next()) {
hasRecords = true;
System.out.printf("%-5d %-10s %-15s %-15s %-6.1f%n",
resultSet.getInt("ranking"),
resultSet.getString("student_id"),
resultSet.getString("name"),
resultSet.getString("class_name"),
resultSet.getDouble("score"));
}
if (!hasRecords) {
System.out.println("未找到课程代码为 " + courseCode + " 的成绩记录");
}
} catch (SQLException e) {
System.err.println("查询成绩排名失败: " + e.getMessage());
}
}
}为什么无法连接
最新发布