2018-12-28

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)	

package comm.neu;

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

import com.mysql.jdbc.PreparedStatement;

public class _1228作业 {

	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="insert into student1 values(null,'李少荣','女',20,80)";
		Statement statement = connection.createStatement();
		 int n = statement.executeUpdate(sql);
		if(n>0) {
			System.out.println("执行成功");
		}else {
			System.out.println("执行失败");
		}
		connection.close();
	}
	private char[] count;	
	@org.junit.Test
	public void testStudent1Insert() throws ClassNotFoundException, SQLException, ParseException {
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection = 
				DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "root");
		String sql="insert into student1 values(null,'邵凯','男',24,75)";
		Statement statement = connection.createStatement();
		 int n = statement.executeUpdate(sql);
		if(n>0) {
			System.out.println("执行成功");
		}else {
			System.out.println("执行失败");
		}
		connection.close();
	}	
	@org.junit.Test
	public void testStudent2Insert() throws ClassNotFoundException, SQLException, ParseException {
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection = 
				DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "root");
		String sql="insert into student1 values(null,'张强','男',23,95)";
		Statement statement = connection.createStatement();
		 int n = statement.executeUpdate(sql);
		if(n>0) {
			System.out.println("执行成功");
		}else {
			System.out.println("执行失败");
		}
		connection.close();
	}	
	@org.junit.Test
	public void testStudent3Insert() throws ClassNotFoundException, SQLException, ParseException {
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection = 
				DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "root");
		String sql="insert into student1 values(null,'王晓婷','女',21,55)";
		Statement statement = connection.createStatement();
		 int n = statement.executeUpdate(sql);
		if(n>0) {
			System.out.println("执行成功");
		}else {
			System.out.println("执行失败");
		}
		connection.close();
	}	
	@org.junit.Test
	public void testStudent4Insert() throws ClassNotFoundException, SQLException, ParseException {
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection = 
				DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "root");
		String sql="insert into student1 values(null,'张秀花','女',23,68)";
		Statement statement = connection.createStatement();
		 int n = statement.executeUpdate(sql);
		if(n>0) {
			System.out.println("执行成功");
		}else {
			System.out.println("执行失败");
		}
		connection.close();
	}	
	@org.junit.Test
	public void testStudent5Insert() throws ClassNotFoundException, SQLException, ParseException {
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection = 
				DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "root");
		String sql="insert into student1 values(null,'顾会','女',22,50)";
		Statement statement = connection.createStatement();
		 int n = statement.executeUpdate(sql);
		if(n>0) {
			System.out.println("执行成功");
		}else {
			System.out.println("执行失败");
		}
		connection.close();
	}	
	@org.junit.Test
	public void testStudent6Insert() throws ClassNotFoundException, SQLException, ParseException {
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection = 
				DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "root");
		String sql="insert into student1 values(null,'赵天一','男',24,32)";
		Statement statement = connection.createStatement();
		 int n = statement.executeUpdate(sql);
		if(n>0) {
			System.out.println("执行成功");
		}else {
			System.out.println("执行失败");
		}
		connection.close();
	}	

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

@org.junit.Test
	public void testQuery() 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 student1 where sex ='女' and score>80";
		Statement statement = connection.createStatement();
		ResultSet rs = statement.executeQuery(sql);	
		while(rs.next()) {
			System.out.println(rs.getInt(1));
		}
		rs.close();
		statement.close();
		connection.close();
	}

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

@org.junit.Test
	public void testUpdate() throws ClassNotFoundException, SQLException {
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection = 
				DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "root");
		String sql ="update student1 set score = 100 where sex ='男'and name like '张%'";
		Statement statement = connection.createStatement();
		 int n = statement.executeUpdate(sql);
		if(n==1) {
			System.out.println("执行成功");
		}else {
			System.out.println("执行失败");
		}
		connection.close();
	}

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

@org.junit.Test
	public void testQuery1() 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 student1 where 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);
		}
		connection.close();
	}

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

@org.junit.Test
	public void testQuery2() throws ClassNotFoundException, SQLException {
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection = 
				DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "root");
		String sql ="select avg(m.score) mscore from student1 m group by sex having sex = '男' ";
		Statement statement = connection.createStatement();
		ResultSet rs = statement.executeQuery(sql);
		while(rs.next()) {
			int mscore=rs.getInt(1);
			System.out.println(mscore);
		}
		rs.close();
		statement.close();
		
		sql="select avg(w.score) wscore from student1 w group by sex having sex = '女'";
		statement  = connection.createStatement();
		rs = statement.executeQuery(sql);
		while(rs.next()) {
			int wscore=rs.getInt(1);
			System.out.println(wscore);
		}
		rs.close();
		statement.close();
	
		sql="select avg(score) zscore from student1 ";
		statement  = connection.createStatement();
		rs = statement.executeQuery(sql);
		while(rs.next()) {
			int zscore=rs.getInt(1);
			System.out.println(zscore);
		}
		rs.close();
		statement.close();
		connection.close();
	}	

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

@org.junit.Test
	public void testQuery3() throws ClassNotFoundException, SQLException {
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection = 
				DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "root");
		String sql ="select id,name,sex,score from student1 where score >65 order by score asc";
		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 score=rs.getInt(4);
			System.out.println(id+" "+name+" "+sex+" "+score);
		}	
	}		
}