2018-12-28 作业

2018-12-28 作业

package com.neu_zuoye;

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

public class zuoye {

	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection = 
				DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "root");
		
		String sql = "select count(*) from student100 where sex = '女' and score >=80";
		
		Statement statement = connection.createStatement();
		
		ResultSet rs = statement.executeQuery(sql);
		
		while(rs.next()) {
			int n = rs.getInt(1);
			System.out.println(n);			
		}
		
		rs.close();
		statement.close();
		connection.close();
		
		
	}
	
	@org.junit.Test
	public void testzhang() throws ClassNotFoundException, SQLException{
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection = 
				DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "root");
		
		String sql = "update student100 set score = 100 where name like('张%') and sex = '男'";
		
		Statement statement = connection.createStatement();
		
		int n = statement.executeUpdate(sql);
		
		if(n>0) {
			System.out.println("修改成功");
		}else {
			System.out.println("修改失败");
		}
		statement.close();
		connection.close();
	}
	@org.junit.Test
	public void test60() throws ClassNotFoundException, SQLException{
		
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection = 
				DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "root");
		
		
		String sql = "select name,sex,score from student100 where sex ='女' and score >=60";
		
		Statement statement = connection.createStatement();
		ResultSet rs = statement.executeQuery(sql);
		
		while(rs.next()) {
			String name = rs.getString(1);
			String sex = rs.getString(2);
			int score = rs.getInt(3);
			System.out.println(name+sex+score);	
		}
		rs.close();
		statement.close();
		connection.close();
	}
	
	@org.junit.Test
	public void testAvg() throws ClassNotFoundException, SQLException{
		
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection = 
				DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "root");
		
		
		String sql = "select sex,avg(score)\r\n" + 
					"from student100 m NATURAL join student100 w\r\\n" + 
					"group by sex";
		
		Statement statement = connection.createStatement();
		ResultSet rs = statement.executeQuery(sql);
		
		while(rs.next()) {
			String sex = rs.getString(1);
			double score = rs.getDouble(2);
			System.out.println(sex+","+score);	
		}
		
		rs.close();
		statement.close();
		connection.close();
	}
	
	@org.junit.Test
	public void testss() throws ClassNotFoundException, SQLException{
		
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection = 
				DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "root");
		
		
		String sql = "select *\r\n" + 
				"from student100 \r\n" + 
				"where score > (select avg(score) avgs from student100)";
		
		Statement statement = connection.createStatement();
		ResultSet rs = statement.executeQuery(sql);
		
		while(rs.next()) {
			int id = rs.getInt(1);
			String name = rs.getString(2);
			String sex = rs.getString(3);
			int age = rs.getInt(4);
			int score = rs.getInt(5);
			System.out.println(id+","+name+","+sex+","+age+","+score);	
		}
		rs.close();
		statement.close();
		connection.close();
	}
	
	

}