学生成绩管理

山东科技大学济南校区课程设计
功能要求：
1) 输入若干同学的学号，姓名，四科成绩（应用数学、大学英语、Java程序设计、计算机应用基础），存储入数据库中。
2) 计算出平均成绩。以平均成绩降序输出成绩表。
3) 输出全组各科平均分，最高分和最低分。
4) 输入姓名查询成绩

package java课程设计;
import java.sql.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

public class Student {
public static void main(String[] args){


        // TODO Auto-generated method stub
        Connection con;//程序与数据源连接
        Statement sql;
        ResultSet rs,rsmath,rsenglish,rsjava,rscomputer;
        ResultSet rsml,rsmh,rsel,rseh,rsjl,rsjh,rscl,rsch,look;
        Scanner sc=new Scanner(System.in);
        System.out.println("请按以下命令执行你的操作：输入数字1,显示成绩单，输入数字2：成绩排名和平均成绩  输入数字3：各科成绩最高分，输入数字4：插入新的成绩");
        int n=sc.nextInt();
        try
        {
            Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
        }
        catch(ClassNotFoundException e)
        {
            System.out.println(""+e);
        }
        try 
        {
            String msAccDB = "D:\\2000\\Datamessage.accdb";
            String url = "jdbc:ucanaccess://" + msAccDB;
            con= DriverManager.getConnection(url);
            //连接内置数据库，使用DriverManager中的静态方法getConnection连接对象
            sql=con.createStatement();//创建sql语句
        switch(n)
        {
        case 1:
            {
                rs=sql.executeQuery("SELECT * FROM message");
                while(rs.next())//读取记录，只要有有效记录的返回false 
                {
                    String id=rs.getString(1);
                    String number=rs.getString(2);
                    String name=rs.getString(3);
                    double math=rs.getDouble(4);
                    double english=rs.getDouble(5);
                    double java=rs.getDouble(6);
                    double computer=rs.getDouble(7);//获得各个项目的值
                    System.out.println(id+""+number+""+name+""+math+""+english+""+java+""+computer+" "); 
                }
            break;
            }
        case 2:
        {
            double sum=0.0;
            int i=0;
            double high=0;
            double low=0;
            rsmath=sql.executeQuery("SELECT*FROM message ORDER BY math");
            rsenglish=sql.executeQuery("SELECT*FROM message ORDER BY english");
            rsjava=sql.executeQuery("SELECT*FROM message ORDER BY java");
            rscomputer=sql.executeQuery("SELECT*FROM message ORDER BY computer");
            while(rsmath.next())
            {       
            String ordername=rsmath.getString(3);
            double ordermath=rsmath.getDouble(4);
            sum=sum+ordermath;
            i++;
            System.out.println("数学的排名为"+ordername+""+ordermath);
            }
            System.out.println("平均成绩为"+(sum/i));
            double sum1=0.0;
            int i1=0;
            while(rsenglish.next())
            {   String ordername=rsenglish.getString(3);
                double orderenglish=rsenglish.getDouble(5);
                System.out.println("英语的排名为"+ordername+" "+orderenglish);
                sum1=sum1+orderenglish;
                i1=i1+1;
            }
            System.out.println("平均成绩为"+(sum1/i1));
            double sum2=0.0;
            int i2=0;
            while(rsjava.next())
            {   String ordername=rsjava.getString(3);
                double orderjava=rsjava.getDouble(6);
                System.out.println("Java的排名为"+ordername+" "+orderjava);
                sum2=sum2+orderjava;
                i2=i2+1;
            }
            System.out.println("平均成绩为"+(sum2/i2));
            double sum3=0.0;
            int i3=0;
            while(rscomputer.next())
            {   String ordername=rscomputer.getString(3);
                double ordercomputer=rscomputer.getDouble(7);
                System.out.println("计算机网络的排名为"+ordername+" "+ordercomputer);
                sum3=sum3+ordercomputer;
                i3=i3+1;
            }
            System.out.println("平均成绩为"+(sum3/i3));
            break;
        }
        case 3:
        {
            rsmh=sql.executeQuery("SELECT * FROM message where math=(select max(math) FROM message)");
            rsml=sql.executeQuery("SELECT * FROM message where math=(select min(math) FROM message)");
            rseh=sql.executeQuery("SELECT * FROM message where english=(select max(english) FROM message)");
            rsel=sql.executeQuery("SELECT * FROM message where english=(select min(english) FROM message)");
            rsjh=sql.executeQuery("SELECT * FROM message where java=(select max(java) FROM message)");
            rsjl=sql.executeQuery("SELECT * FROM message where java=(select min(java) FROM message)");
            rsch=sql.executeQuery("SELECT * FROM message where computer=(select max(computer) FROM message)");
            rscl=sql.executeQuery("SELECT * FROM message where computer=(select min(computer) FROM message)");
            while(rsmh.next())
            {
                double highmath=rsmh.getDouble(4);
                System.out.println("数学最高分为"+highmath);
            }
            while(rsml.next())
            {
                double lowmath=rsml.getDouble(4);
                System.out.println("数学最低分为"+lowmath);
            }
            while(rseh.next())
            {
                double highenglish=rseh.getDouble(5);
                System.out.println("英语最高分为"+highenglish);
            }
            while(rsel.next())
            {
                double lowenglish=rsel.getDouble(5);
                System.out.println("英语最低分为"+lowenglish);
            }
            while(rsjh.next())
            {
                double highjava=rsjh.getDouble(6);
                System.out.println("java最高分"+highjava);
            }
            while(rsjl.next())
            {
                double lowjava=rsjl.getDouble(6);
                System.out.println("java最低分"+lowjava);
            }
            while(rsch.next())
            {
                double highcomputer=rsch.getDouble(7);
                System.out.println("计算机网络最高分"+highcomputer);
            }
            while(rscl.next())
            {
                double lowcomputer=rscl.getDouble(7);
                System.out.println("计网最低分"+lowcomputer);
            }
            break;
        }
        case 4:
        {
            int ok=sql.executeUpdate("INSERT INTO message VALUES(7,'07','小亮',89,90,92,80)");
            System.out.println("插入成功");
        }
        }   //switch结尾
    con.close();    
    }
    catch(SQLException e)
    {System.out.println(e);}


}
}