(1)学生信息管理系统。
学生成绩表Student(Sno 字符串,长度9, Sname 字符串,长度10, Class 字符串,长度10, Age 整型, Sex 字符串,长度2)
实现如下功能:
A.输入若干个学生的信息到Student表;
B.修改指定学号学生的基本信息;
C.删除指定学号的学生基本信息;
D.按照性别找出所有相应的学生基本信息;
E.按照年龄段找出所有相应的学生基本信息,例如[19,21]的学生。
package mysol;
import java.io.*;
import java.sql.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Scanner;
public class text {
public static void main(String[] args) throws IOException {
Connection conn = null;
try {
//加载驱动类
Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/text?serverTimezone=UTC","root","1");
//插入学生信息
String sql = "INSERT INTO student (Sno,Sname,Class,Age,sex)"+"VALUES('001','xiaomi','san','18','nan'),('002','xiaohong','san','19','lv'),('003','dada','er','20','nan'),('004','popo','yi','21','lv')";
Statement stmt = conn.createStatement();
stmt.executeUpdate(sql);
//ResultSet rs = stmt.executeQuery(sql);
System.out.println("数据库连接成功");
while(true) {
int N =0;
System.out.println("1.修改指定学号学生的基本信息;\r\n" +
"2.删除指定学号的学生基本信息;\r\n" +
"3.按照性别找出所有相应的学生基本信息;\r\n" +
"4.按照年龄段找出所有相应的学生基本信息,例如[19,21]的学生。\r\n"+
"0,退出\r\n"+
"输入你的选择:");
Scanner can = new Scanner(System.in);
N = can.nextInt();
BufferedReader buf;
String str;
buf =new BufferedReader(new InputStreamReader(System.in));
switch(N) {
case 1:
System.out.println("输入你要修改的学生学号:");
str=buf.readLine();
String Sq0 = "update student set Age=20 where Sno = '"+str+"'";
Statement stmt1 = conn.createStatement();
stmt1.executeUpdate(Sq0);
break;
case 2:
System.out.println("输入你要修改的学生学号:");
str=buf.readLine();
String Sq2 = "DELETE FROM student WHERE sno = "+str;
Statement stmt2 = conn.createStatement();
stmt2.executeUpdate(Sq2);
break;
case 3:
System.out.println("输入你要查找性别:");
str=buf.readLine();
String Sq3 = "SELECT Sno,Sname,Class,Age,Sex FROM student WHERE Sex='"+str+"'";
Statement stmt3 = conn.createStatement();
ResultSet rs = stmt3.executeQuery(Sq3);
//ResultSet rs = stmt3.executeQuery("SELECT * FROM student'"+str"'");
while(rs.next()){
//通过字段检索
String Sno = rs.getString("Sno");
String Sname = rs.getString("Sname");
String Class = rs.getString("Class");
int Age =rs.getInt("Age");
String Sex = rs.getString("Sex");
// 输出数据
System.out.print("Sno: " + Sno+" ");
System.out.print("Sname: " + Sname+" ");
System.out.print("Class: " + Class+" ");
System.out.print("Age"+Age+" ");
System.out.print("Sex"+Sex+" ");
System.out.print("\n");
}
break;
case 4:
System.out.println("输入你要查询的年龄区间:");
char a=0,b=0;
Scanner ca4 = new Scanner(System.in);
Scanner ca5 = new Scanner(System.in);
a= (char)ca4.nextInt();
b= (char)ca5.nextInt();
String Sq4 = "SELECT Sno,Sname,Class,Age,Sex FROM student WHERE Age<='"+b+"' AND Age>='"+a+"'";
Statement stmt4 = conn.createStatement();
stmt4.executeQuery(Sq4);
ResultSet rss = stmt4.executeQuery("SELECT * FROM student");
while(rss.next()){
//通过字段检索
String Sno = rss.getString("Sno");
String Sname = rss.getString("Sname");
String Class = rss.getString("Class");
int Age = rss.getInt("Age");
String Sex = rss.getString("Sex");
// 输出数据
System.out.print("Sno: " + Sno+" ");
System.out.print("Sname: " + Sname+" ");
System.out.print("Class: " + Class+" ");
System.out.print("Age"+Age+" ");
System.out.print("Sex"+Sex+" ");
System.out.print("\n");
}
break;
case 0:
exit(0);
break;
}
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if(conn!=null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
private static void exit(int i) {
// TODO 自动生成的方法存根
}
}