作业

2018.12.28作业

查询女性,成绩80以上的学生数量

public static void main(String[] args) throws SQLException, ClassNotFoundException {
			Class.forName("com.mysql.jdbc.Driver");
			Connection connection =   //ctrl+2 L
			DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "root");			
			String sql = "select count(*) from s where score>80 and sex ='女'";
			Statement statement = connection.createStatement();
			ResultSet rs = statement.executeQuery(sql);
			while(rs.next()) {			
			System.out.println(rs.getInt(1));
			}
			connection.close();	
		}

将姓张的男同学的的成绩改为100

@org.junit.Test
	public void sUpdate() throws SQLException, ClassNotFoundException {
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection =   //ctrl+2 L
		DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "root");
		//编写sql语句
		String sql = "update s set score = 100 where name like '张%' and sex = '男'";
		//基于当前连接得到一个执行sql语句的命令对象
		Statement statement = connection.createStatement();
		//执行sql语句，该方法一般用来执行增删改操作，返回值表示影响数据库的行数
		int n = statement.executeUpdate(sql); 	
		if(n==1) {
			System.out.println("执行成功");
		}else {
			System.out.println("执行失败");
		}
		//关闭连接，使用完马上关闭，释放资源 
		connection.close();	
	}

查询成绩大于60的女性,显示姓名,性别,成绩

@org.junit.Test
	public void sSelect() throws SQLException, ClassNotFoundException {
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection =   //ctrl+2 L
		DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "root");			
		String sql = "select name,sex,score from s where score > 60 and sex = '女'";
		Statement statement = connection.createStatement();
		ResultSet rs = statement.executeQuery(sql);
		while(rs.next()) {	
		System.out.println(rs.getString(1)+" "+rs.getString(2)+" "+rs.getInt(3));
		}
		connection.close();	
	}
	@org.junit.Test
	public void sSelect2() throws SQLException, ClassNotFoundException {
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection =   //ctrl+2 L
		DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "root");			
		String sql = "select avg(score) from s group by sex";
		Statement statement = connection.createStatement();
		ResultSet rs = statement.executeQuery(sql);
		while(rs.next()) {	
		System.out.println(rs.getDouble(1));
		}
		connection.close();	
	}

分别统计所有男同学的平均分，所有女同学的平均分及总平均分

@org.junit.Test
	public void sSelect1() throws SQLException, ClassNotFoundException {
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection =   //ctrl+2 L
		DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "root");			
		String sql = "select avg(score) from s";
		Statement statement = connection.createStatement();
		ResultSet rs = statement.executeQuery(sql);
		while(rs.next()) {	
		System.out.println(rs.getDouble(1));
		}
		connection.close();	
	}

按照分数从小到大的顺序打印分数大于总平均分的学员信息(id-name-sex-score)

@org.junit.Test
	public void sSelect3() throws SQLException, ClassNotFoundException {
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection =   //ctrl+2 L
		DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "root");			
		String sql = "select id,name,sex,score from s where score > (select avg(score) from s) order by score";
		Statement statement = connection.createStatement();
		ResultSet rs = statement.executeQuery(sql);
		while(rs.next()) {	
		System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3)+" "+rs.getInt(4));
		}
		connection.close();	
	}