package com.sinoi.javatraining3;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
/**
* @author zhangyunkang E-mail:1743755073@qq.com
* @version creat time：2014/04/29 10:56:02
*/
public class MySql {
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
try {
Class.forName("com.mysql.jdbc.Driver");
System.out.println("Success loading Mysql Driver!");
}
catch (Exception e) {
// TODO: handle exception
System.out.println("Error loading Mysql Driver!");
e.printStackTrace();
}
try{
Connection connection=DriverManager.getConnection("jdbc:mysql://localhost:3306/school","root","060383");
System.out.println("Success connect Mysql server!");
Statement stmtStatement=connection.createStatement();
File file=new File("E:\\stu3.csv");
BufferedWriter bufW = new BufferedWriter(new FileWriter(file));
ResultSet rS1=stmtStatement.executeQuery("SELECT AVG(Chinese+Math+English) FROM stuinfo GROUP BY stuinfo.Class");
int i=1;
while (rS1.next()){
bufW.write("Class"+i+":");
i++;
bufW.write(rS1.getString(1));
bufW.newLine();
}
rS1.close();
int j=1;
ResultSet rS2=stmtStatement.executeQuery("SELECT SUM(Chinese+Math+English) FROM stuinfo GROUP BY stuinfo.StuNum");
while (rS2.next()){
bufW.write("stu"+j+":");
j++;
bufW.write(rS2.getString(1));
bufW.newLine();
}
}
catch (Exception e) {
// TODO: handle exception
System.out.print("get data error");
e.printStackTrace();
}
}
}
//SELECT SUM(Chinese+Math+English) FROM stuinfo GROUP BY stuinfo.StuNum 总分
//SELECT AVG(Chinese+Math+English) FROM stuinfo GROUP BY stuinfo.Class 平均成绩

数据库如下所示：