山东科技大学济南校区课程设计
功能要求:
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);}
}
}