一个数据库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();
}
}