学生管理系统 数据库版

写SQL语句

1. 创建school_java数据库
2.  创建student数据表包含 id、name姓名、tel电话、sex性别字段
3. 往student表中加10条数据

写Java代码（要求只用PreparedStatement对象，变化的值都用?代替）

代码

package sx;

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 StudentDemo {
	Connection connection;
	public static void main(String[] args) throws SQLException {
		StudentDemo student= new StudentDemo();
		student.getConnection();
		student.selectA11();
		student.add(11, "一号", "12345678901", "男");
		student.add(12, "二号", "12345678902", "女");
		student.add(13, "三号", "12345678903", "男");
		student.selectA11();
		student.selectmll182();
		student.upd(5, "13124235300");
		student.selectA11();
		student.selectnan();
		student.upm(10, "大帅哥");
		student.selectA11();
		student.selectmll139();
		student.delete(3);
		student.selectA11();
		student.selectnv();
		student.selectA11();
		student.close();
		
	}
	//创建连接
	public void getConnection() throws SQLException {
	DriverManager.registerDriver(new Driver());
	connection=DriverManager.getConnection
			("jdbc:mysql://localhost:3306/school_java", "root", "1234");
	}
	//查询
	public void selectA11() throws SQLException {
		System.out.println("查询结果如下：");
		PreparedStatement preparedStatement=
				connection.prepareStatement("select * from student;");
		ResultSet resultSet=preparedStatement.executeQuery();
		while(resultSet.next()) {
			System.out.println(resultSet.getInt("id")+"	"+
								resultSet.getString("name")+"	"+
								resultSet.getString("tel")+"	"+
								resultSet.getString("sex"));
		}
		resultSet.close();
		preparedStatement.close();
	}
	public void selectnan() throws SQLException {
		System.out.println("查询结果如下：");
		PreparedStatement preparedStatement=
				connection.prepareStatement("select * from student where sex='男';");
		ResultSet resultSet=preparedStatement.executeQuery();
		while(resultSet.next()) {
			System.out.println(resultSet.getInt("id")+"	"+
								resultSet.getString("name")+"	"+
								resultSet.getString("tel")+"	"+
								resultSet.getString("sex"));
		}
		resultSet.close();
		preparedStatement.close();
	}
	public void selectnv() throws SQLException {
		System.out.println("查询结果如下：");
		PreparedStatement preparedStatement=
				connection.prepareStatement("select * from student where sex='女';");
		ResultSet resultSet=preparedStatement.executeQuery();
		while(resultSet.next()) {
			System.out.println(resultSet.getInt("id")+"	"+
								resultSet.getString("name")+"	"+
								resultSet.getString("tel")+"	"+
								resultSet.getString("sex"));
		}
		resultSet.close();
		preparedStatement.close();
	}
	//模糊查询
	public void selectmll182() throws SQLException {
		System.out.println("查询结果如下：");
		PreparedStatement preparedStatement2=
				connection.prepareStatement("select * from student where tel like '182%';");
		ResultSet resultSet2=preparedStatement2.executeQuery();
		while(resultSet2.next()) {
			System.out.println(resultSet2.getInt("id")+"	"+
								resultSet2.getString("name")+"	"+
								resultSet2.getString("tel")+"	"+
								resultSet2.getString("sex"));
		}
		resultSet2.close();
		preparedStatement2.close();
	}
	public void selectmll139() throws SQLException {
		System.out.println("查询结果如下：");
		PreparedStatement preparedStatement2=
				connection.prepareStatement("select * from student where tel like '139%';");
		ResultSet resultSet2=preparedStatement2.executeQuery();
		while(resultSet2.next()) {
			System.out.println(resultSet2.getInt("id")+"	"+
								resultSet2.getString("name")+"	"+
								resultSet2.getString("tel")+"	"+
								resultSet2.getString("sex"));
		}
		resultSet2.close();
		preparedStatement2.close();
	}
	//修改
	private void upd(int id, String tel) throws SQLException {
		System.out.println("修改结果如下：");
		PreparedStatement preparedStatement=
				connection.prepareStatement("update student set tel = ? where id = ?");
		preparedStatement.setString(1, tel);
		preparedStatement.setInt(2, id);
		preparedStatement.executeUpdate();
	}
	private void upm(int id, String name) throws SQLException {
		System.out.println("修改结果如下：");
		PreparedStatement preparedStatement=
				connection.prepareStatement("update student set name = ? where id = ?");
		preparedStatement.setString(1, name);
		preparedStatement.setInt(2, id);
		preparedStatement.executeUpdate();
	}
	//增加
	public void add(int id,String name,String tel,String sex) throws SQLException {
		System.out.println("增加结果如下：");
		PreparedStatement preparedStatement=
				connection.prepareStatement("insert into student values(?,?,?,?);");
		preparedStatement.setInt(1,id);
		preparedStatement.setString(2,name);
		preparedStatement.setString(3,tel);
		preparedStatement.setString(4,sex);
		preparedStatement.executeUpdate();
	}
	//删除
	private void delete(int id) throws SQLException {
		PreparedStatement preparedStatement=
				connection.prepareStatement("delete from  student  where id = ?;");
		preparedStatement.setInt(1, id);
		preparedStatement.executeUpdate();
		System.out.println("已删除第"+id+"条数据");
	}
	//关闭
	private void close() throws SQLException {
		connection.close();
	}

}

结果 

查询student表中所有学生信息

student表中新增三条数据

查询student表中电话以182开头的所有学生信息（SQL要用到模糊查询like）

修改student表中第5条数据，电话改为“13124235300”

查询student表中所有男性的学生信息

修改student表中第10条数据，姓名改为“大帅哥”

查询student表中电话以139开头的所有学生信息（SQL要用到模糊查询like）

删除student表中第3条数据

查询student表中所有女性的学生信息

查询student表中所有学生信息