package llxy.project.dao;import java.awt.Font;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import javax.swing.JOptionPane;import javax.swing.JTextArea;import llxy.project.Tools.JDBCTools;publicclassOtherSelectByCourse1{publicOtherSelectByCourse1(JTextArea jt){
Connection conn = null;
Statement stmt1 = null;
Statement stmt2 = null;
Statement stmt3 = null;
ResultSet rs1 = null;
ResultSet rs2 = null;
ResultSet rs3 = null;
String sql1 ="SELECT class,CONCAT (TRUNCATE(((SELECT COUNT(chinese)FROM student WHERE chinese>90 AND class = 1 )/(SELECT COUNT(sno) FROM student WHERE class = 1))*100,2),'%' ),CONCAT (TRUNCATE(((SELECT COUNT(chinese)FROM student WHERE chinese<60 AND class = 1 )/(SELECT COUNT(sno) FROM student WHERE class = 1))*100,2),'%' ) FROM student WHERE class = 1 GROUP BY class;";
String sql2 ="SELECT class,CONCAT (TRUNCATE(((SELECT COUNT(chinese)FROM student WHERE chinese>90 AND class = 2 )/(SELECT COUNT(sno) FROM student WHERE class = 2))*100,2),'%' ),CONCAT (TRUNCATE(((SELECT COUNT(chinese)FROM student WHERE chinese<60 AND class = 2 )/(SELECT COUNT(sno) FROM student WHERE class = 2))*100,2),'%' ) FROM student WHERE class = 2 GROUP BY class;";
String sql3 ="SELECT class,CONCAT (TRUNCATE(((SELECT COUNT(chinese)FROM student WHERE chinese>90 AND class = 3 )/(SELECT COUNT(sno) FROM student WHERE class = 3))*100,2),'%' ),CONCAT (TRUNCATE(((SELECT COUNT(chinese)FROM student WHERE chinese<60 AND class = 3 )/(SELECT COUNT(sno) FROM student WHERE class = 3))*100,2),'%' ) FROM student WHERE class = 3 GROUP BY class;";try{
conn = JDBCTools.getConnection();
stmt1 = conn.createStatement();
stmt2 = conn.createStatement();
stmt3 = conn.createStatement();
rs1 = stmt1.executeQuery(sql1);
rs2 = stmt2.executeQuery(sql2);
rs3 = stmt3.executeQuery(sql3);while(rs1.next()){
String str =(rs1.getString(1)+" "+rs1.getString(2)+" "+ rs1.getString(3));
String str1 =jt.getText();
String s = str1+"\n"+str;
jt.setText(s);
jt.setFont(newFont(str,0,20));}while(rs2.next()){
String str =(rs2.getString(1)+" "+rs2.getString(2)+" "+ rs2.getString(3));
String str1 =jt.getText();
String s = str1+"\n"+str;
jt.setText(s);
jt.setFont(newFont(str,0,20));}while(rs3.next()){
String str =(rs3.getString(1)+" "+rs3.getString(2)+" "+ rs3.getString(3));
String str1 =jt.getText();
String s = str1+"\n"+str;
jt.setText(s);
jt.setFont(newFont(str,0,20));}}catch(ClassNotFoundException e){
e.printStackTrace();}catch(SQLException e){
e.printStackTrace();}finally{//关闭连接,释放资源。
JDBCTools.release(stmt1, conn);
JDBCTools.release(stmt2, conn);
JDBCTools.release(stmt3, conn);}
JOptionPane.showMessageDialog(null,"查询完成");}}
package llxy.project.dao;import java.awt.Font;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import javax.swing.JOptionPane;import javax.swing.JTextArea;import llxy.project.Tools.JDBCTools;publicclassSelectByCourse1{publicSelectByCourse1(JTextArea jt){
Connection conn = null;
Statement stmt1 = null;
Statement stmt2 = null;
ResultSet rs1 = null;
ResultSet rs2 = null;
String sql1 ="SELECT * FROM student WHERE chinese in (SELECT MAX(chinese) FROM student );";
String sql2 ="SELECT * FROM student WHERE chinese in (SELECT MIN(chinese) FROM student );";try{
conn = JDBCTools.getConnection();
stmt1 = conn.createStatement();
stmt2 = conn.createStatement();
rs1 = stmt1.executeQuery(sql1);
rs2 = stmt2.executeQuery(sql2);while(rs1.next()){
String str1 =(rs1.getString("sno")+" "+rs1.getString("name")+" "+rs1.getString("class")+" "+rs1.getString("chinese"));
String str3 =jt.getText();
String s =" "+str3 +"\n"+"最高分:"+str1;
jt.setText(s);
jt.setFont(newFont(str1,0,20));}while(rs2.next()){
String str1 =(rs2.getString("sno")+" "+rs2.getString("name")+" "+rs2.getString("class")+" "+rs2.getString("chinese"));
String str3 =jt.getText();
String s =" "+ str3 +"\n"+"最低分:"+str1;
jt.setText(s);
jt.setFont(newFont(str1,0,20));}}catch(ClassNotFoundException e){
e.printStackTrace();}catch(SQLException e){
e.printStackTrace();}finally{//关闭连接,释放资源。
JDBCTools.release(stmt1, conn);
JDBCTools.release(stmt2, conn);}
JOptionPane.showMessageDialog(null,"查询完成");}}
(2)查询优秀率和及格率.
package llxy.project.dao;import java.awt.Font;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import javax.swing.JOptionPane;import javax.swing.JTextArea;import llxy.project.Tools.JDBCTools;publicclassOtherSelectByCourse1{publicOtherSelectByCourse1(JTextArea jt){
Connection conn = null;
Statement stmt1 = null;
Statement stmt2 = null;
Statement stmt3 = null;
ResultSet rs1 = null;
ResultSet rs2 = null;
ResultSet rs3 = null;
String sql1 ="SELECT class,CONCAT (TRUNCATE(((SELECT COUNT(chinese)FROM student WHERE chinese>90 AND class = 1 )/(SELECT COUNT(sno) FROM student WHERE class = 1))*100,2),'%' ),CONCAT (TRUNCATE(((SELECT COUNT(chinese)FROM student WHERE chinese<60 AND class = 1 )/(SELECT COUNT(sno) FROM student WHERE class = 1))*100,2),'%' ) FROM student WHERE class = 1 GROUP BY class;";
String sql2 ="SELECT class,CONCAT (TRUNCATE(((SELECT COUNT(chinese)FROM student WHERE chinese>90 AND class = 2 )/(SELECT COUNT(sno) FROM student WHERE class = 2))*100,2),'%' ),CONCAT (TRUNCATE(((SELECT COUNT(chinese)FROM student WHERE chinese<60 AND class = 2 )/(SELECT COUNT(sno) FROM student WHERE class = 2))*100,2),'%' ) FROM student WHERE class = 2 GROUP BY class;";
String sql3 ="SELECT class,CONCAT (TRUNCATE(((SELECT COUNT(chinese)FROM student WHERE chinese>90 AND class = 3 )/(SELECT COUNT(sno) FROM student WHERE class = 3))*100,2),'%' ),CONCAT (TRUNCATE(((SELECT COUNT(chinese)FROM student WHERE chinese<60 AND class = 3 )/(SELECT COUNT(sno) FROM student WHERE class = 3))*100,2),'%' ) FROM student WHERE class = 3 GROUP BY class;";try{
conn = JDBCTools.getConnection();
stmt1 = conn.createStatement();
stmt2 = conn.createStatement();
stmt3 = conn.createStatement();
rs1 = stmt1.executeQuery(sql1);
rs2 = stmt2.executeQuery(sql2);
rs3 = stmt3.executeQuery(sql3);while(rs1.next()){
String str =(rs1.getString(1)+" "+rs1.getString(2)+" "+ rs1.getString(3));
String str1 =jt.getText();
String s = str1+"\n"+str;
jt.setText(s);
jt.setFont(newFont(str,0,20));}while(rs2.next()){
String str =(rs2.getString(1)+" "+rs2.getString(2)+" "+ rs2.getString(3));
String str1 =jt.getText();
String s = str1+"\n"+str;
jt.setText(s);
jt.setFont(newFont(str,0,20));}while(rs3.next()){
String str =(rs3.getString(1)+" "+rs3.getString(2)+" "+ rs3.getString(3));
String str1 =jt.getText();
String s = str1+"\n"+str;
jt.setText(s);
jt.setFont(newFont(str,0,20));}}catch(ClassNotFoundException e){
e.printStackTrace();}catch(SQLException e){
e.printStackTrace();}finally{//关闭连接,释放资源。
JDBCTools.release(stmt1, conn);
JDBCTools.release(stmt2, conn);
JDBCTools.release(stmt3, conn);}
JOptionPane.showMessageDialog(null,"查询完成");}}