根据ID统计分数求最高分-E

本文介绍了一种通过输入参赛者的学校编号和成绩来统计各校总分的方法,并使用两种不同的数据结构实现:数组和映射。最终输出总得分最高的学校编号及总分。
现请你根据比赛结果统计出技术最强的那个学校。
输入描述:
输入在第1行给出不超过105的正整数N,即参赛人数。随后N行,每行给出一位参赛者的信息和成绩,包括其所代表的学校的编号(从1开始

连续编号)、及其比赛成绩(百分制),中间以空格分隔。

输出描述:
在一行中给出总得分最高的学校的编号、及其总分,中间以空格分隔。题目保证答案唯一,没有并列。

输入例子:
6
3 65
2 80
1 100
2 70
3 40
3 0
输出例子:
2 150

#include<iostream>
#include<map>
using namespace std;

int arr[100000+1]={0};

void solve_1(){
    int n;
    cin>>n;
    for(int i=0;i<n;i++){
        int shoolNo;
        int score;
        cin>>shoolNo>>score;
        arr[shoolNo]+=score;
    }
    int maxIndex=0;
    int maxValue=0;
    for(int i=0;i<n;i++){
        if(maxValue<arr[i]){
            maxValue=arr[i];
            maxIndex=i;
        }
    }
    cout<<maxIndex<<" "<<maxValue<<endl;
    return;
}

void solve_2(){
    int n;
    map<int,int> map_count;
    cin>>n;
    map<int,int>::iterator  p;
    for(int i=0;i<n;i++){
        int schoolNo;
        int score;
        cin>>schoolNo>>score;
        p = map_count.find(schoolNo);
        if(p==map_count.end())
            map_count[schoolNo]=score;
        else
            map_count[schoolNo]+=score;
    }
    int maxIndex=0;
    int maxValue=0;
    p = map_count.begin();
    while(p!=map_count.end()){
        if(p->second>maxValue){
            maxValue=p->second;
            maxIndex=p->first;
        }
        p++;
    }
    cout<<maxIndex<<" "<<maxValue<<endl;
}

int main()
{
    //solve_1();
    solve_2();
    return 0;
}



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()); } } }为什么无法连接
最新发布
07-02
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值