2018-12-28作业

一个数据库stdb,用户名为root 密码为root, 已存在一个表student中有七个学生的信息,姓名,性别,年龄,分数.

id(int)       name(varchar(20))      sex(varchar(20))   age（int）         score(int)
 1                    李少荣                 女             20		          80
 2                    邵凯                   男             24     	          75
 3                    张强                   男             23      	     	  95
 4                    王晓婷                 女             21                 55
 5                    张秀花                 女             23                 68
 6                    顾会                   女             22                 50
 7                    赵天一                 男             24                 32

(1)查询女性,成绩80以上的学生数量
(2)将姓张的男同学的的成绩改为100
(3)查询成绩大于60的女性,显示姓名,性别,成绩
(4)分别统计所有男同学的平均分，所有女同学的平均分及总平均分
(5)按照分数从小到大的顺序打印分数大于总平均分的学员信息(id-name-sex-score)

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class zy20181228 {

	@org.junit.Test
	public void test1() throws ClassNotFoundException, SQLException{
		//查询女性,成绩80以上的学生数量
		Class.forName("com.mysql.jdbc.Driver");
		
		Connection connection = 
				DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "root");
		
		String sql = "select count(*) from student where sex = '女' and score > 80";
		
		PreparedStatement statement = connection.prepareStatement(sql);
		ResultSet rs = statement.executeQuery();
		
		if(rs.next()){
			System.out.println("女性成绩80以上的学生数量为："+rs.getInt(1));
		}else{
			System.out.println("查询失败");
		}
		
		rs.close();
		statement.close();
		connection.close();
	}
	
	@org.junit.Test
	public void test2() throws ClassNotFoundException, SQLException{
		//将姓张的男同学的的成绩改为100
		Class.forName("com.mysql.jdbc.Driver");
		
		Connection connection = 
				DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "root");
		
		String sql = "update student set score = 100 where name like '张%' and sex = '男'";
		
		PreparedStatement statement = connection.prepareStatement(sql);
		int n = statement.executeUpdate();
		System.out.println("修改了"+n+"条数据");
		
		statement.close();
		connection.close();
	}
	
	@org.junit.Test
	public void test3() throws ClassNotFoundException, SQLException{
		//查询成绩大于60的女性,显示姓名,性别,成绩
		Class.forName("com.mysql.jdbc.Driver");
		
		Connection connection = 
				DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "root");
		
		String sql = "select name,sex,score from student where sex = '女' and score > 60";
		
		PreparedStatement statement = connection.prepareStatement(sql);
		ResultSet rs = statement.executeQuery();
		
		System.out.println("姓名\t性别\t成绩");
		while(rs.next()){
			System.out.print(rs.getString(1)+"\t");
			System.out.print(rs.getString(2)+"\t");
			System.out.print(rs.getInt(3)+"\t");
			System.out.println();
		}
		
		rs.close();
		statement.close();
		connection.close();
	}
	
	@org.junit.Test
	public void test4() throws ClassNotFoundException, SQLException{
		//分别统计所有男同学的平均分，所有女同学的平均分及总平均分
		Class.forName("com.mysql.jdbc.Driver");
		
		Connection connection = 
				DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "root");
		//如果在方法内定义局部变量，使用StringBuilder，如果在方法外定义成员变量，使用StringBuffer
		StringBuilder stb = new StringBuilder();
		stb.append("select (select avg(score) from student where sex = '男'),");
		stb.append("			 (select avg(score) from student where sex = '女'),");
		stb.append("			 (select avg(score) from student)");
		String sql = stb.toString();
		
		PreparedStatement statement = connection.prepareStatement(sql);
		ResultSet rs = statement.executeQuery();
		
		if(rs.next()){
			System.out.println(rs.getDouble(1)+","+rs.getDouble(2)+","+rs.getDouble(3));
		}
		
		rs.close();
		statement.close();
		connection.close();
	}
	
	@org.junit.Test
	public void test5() throws ClassNotFoundException, SQLException{
		//按照分数从小到大的顺序打印分数大于总平均分的学员信息(id-name-sex-score)
		Class.forName("com.mysql.jdbc.Driver");
		
		Connection connection = 
				DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "root");
		
		StringBuilder stb = new StringBuilder();
		stb.append("select id,name,sex,score from student ");
		stb.append("where score > (select avg(score) from student) ");
		stb.append("order by score");
		
		String sql = stb.toString();
		PreparedStatement statement = connection.prepareStatement(sql);
		ResultSet rs = statement.executeQuery();
		
		System.out.println("id\t姓名\t性别\t成绩");
		while(rs.next()){
			System.out.print(rs.getInt(1)+"\t");
			System.out.print(rs.getString(2)+"\t");
			System.out.print(rs.getString(3)+"\t");
			System.out.print(rs.getInt(4)+"\t");
			System.out.println();
		}
		
		rs.close();
		statement.close();
		connection.close();
	}

}