学生管理系统数据库版

数据库 

use school_java

CREATE TABLE student (
    -> id INT(11),
    -> name VARCHAR(25),
    -> tel INT(11),
    -> sex VARCHAR(6)
    -> );

 

INSERT INTO student VALUES
    -> (1,'小一',1234567891,'男'),
    -> (2,'小二',1234745880,'男'),
    -> (3,'小三',1482457890,'男'),
    -> (4,'小四',1234625387,'女'),
    -> (5,'小五',1232947109,'女'),
    -> (6,'小六',1292075256,'男'),
    -> (7,'小七',1936728467,'女'),
    -> (8,'小八',1248503896,'男'),
    -> (9,'小九',1408204721,'女'),
    -> (10,'小十',1037862893,'女');

 

代码 

package mysql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
 
import com.mysql.cj.jdbc.Driver;
 
public class school_java {
	Connection connection;
		
	public static void main(String[] args) throws SQLException {
		school_java schooljava=new school_java();
		schooljava.getConnection();
		System.out.println("查询数据为：");
		schooljava.selectAll();
		System.out.println("新增3条数据为:");
		schooljava.add(11,"小十一",1824539890,"女");
		schooljava.add(12,"小十二",1391027890,"女");
		schooljava.add(13,"小十三",1893283635,"男");
		schooljava.select182();   //182
		schooljava.xiugai();   //修改
		schooljava.chanan();    //查男
		schooljava.dsg();    //大帅哥
		schooljava.select139();    //139
		schooljava.sc();
		schooljava.chanv();   //查女
		schooljava.selectAll();   //查询全部
	}
	public void add(int id, String name,int tel,String sex) throws SQLException {
		PreparedStatement preparedStatement=connection.prepareStatement("insert into student values(?,?,?,?);");
		preparedStatement.setInt(1, id);
		preparedStatement.setString(2, name);
		preparedStatement.setInt(3,tel);
		preparedStatement.setString(4,sex);
		preparedStatement.executeUpdate();
	}
	public void getConnection()throws SQLException {
		DriverManager.registerDriver(new Driver());//第一步：注册驱动
		connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/school_java","root","1234");//第二步：获取连接
}
 
	public void selectAll() throws SQLException {		//查询课程表所有数据
		PreparedStatement preparedStatement=connection.prepareStatement("select*from  student");//第三步：获取statement对象
		ResultSet resultSet=preparedStatement.executeQuery();//第四步：执行SQL语句返回结果集
		while(resultSet.next()){
			System.out.print(resultSet.getInt("id"));
			System.out.print(resultSet.getString("name"));
			System.out.print(resultSet.getInt("tel"));
			System.out.println(resultSet.getString("sex"));//第五步：遍历结果集
			
		}resultSet.close();
		preparedStatement.close();}
	public void select182() throws SQLException {
		PreparedStatement preparedStatement=connection.prepareStatement("select*from  student where tel like '182%'");
		ResultSet resultSet=preparedStatement.executeQuery();    //电话以182开头的所有学生信息
		while(resultSet.next()){
			System.out.print(resultSet.getInt("id"));
			System.out.print(resultSet.getString("name"));
			System.out.print(resultSet.getInt("tel"));
			System.out.println(resultSet.getString("sex"));}
		
}
	
	public void xiugai() throws SQLException {
		PreparedStatement preparedStatement=connection.prepareStatement("update student set tel=1312423530 where id=5");
		preparedStatement.executeUpdate( );    //第5条数据，电话改为“13124235300”
		
	}
	public void chanan() throws SQLException {
		PreparedStatement preparedStatement=connection.prepareStatement("select*from  student where sex='男'");
		ResultSet resultSet=preparedStatement.executeQuery();        //男
		while(resultSet.next()){
			System.out.print(resultSet.getInt("id"));
			System.out.print(resultSet.getString("name"));
			System.out.print(resultSet.getInt("tel"));
			System.out.println(resultSet.getString("sex"));}
	}
	public void dsg() throws SQLException {
		PreparedStatement preparedStatement=connection.prepareStatement("update student set name='大帅哥' where id=10");
		preparedStatement.executeUpdate( );      //第10条数据，姓名改为“大帅哥”
		
	}
	public void select139() throws SQLException {
		PreparedStatement preparedStatement=connection.prepareStatement("select*from  student where tel like '139%'");
		ResultSet resultSet=preparedStatement.executeQuery();       //电话以139开头的所有学生信息
		while(resultSet.next()){
			System.out.print(resultSet.getInt("id"));
			System.out.print(resultSet.getString("name"));
			System.out.print(resultSet.getInt("tel"));
			System.out.println(resultSet.getString("sex"));}
		}
	public void sc() throws SQLException {
		PreparedStatement preparedStatement=connection.prepareStatement("delete from student where id=3");
		preparedStatement.executeUpdate( );          //第3条数据
		
	}
	public void chanv() throws SQLException {
		PreparedStatement preparedStatement=connection.prepareStatement("select*from  student where sex='女'");
		ResultSet resultSet=preparedStatement.executeQuery();   //女
		while(resultSet.next()){
			System.out.print(resultSet.getInt("id"));
			System.out.print(resultSet.getString("name"));
			System.out.print(resultSet.getInt("tel"));
			System.out.println(resultSet.getString("sex"));}}
	
}

结果