学生管理系统

一、写SQL语句

1、创建school_java数据库

create database schol_java;

2、创建student数据表包含 id、name姓名、tel电话、sex性别字段

create table student(

id int(11),

name varchar(3),

tel char(11),

sex char(1)

);

 

3、往student表中加10条数据

insert into student(id,name,tel,sex)
values(1,'王敏','18207042633','女'),
(2,'李勇','18257542633','男'), 
(3,'刘晨','13107042633','男'),  
(4,'肖琴','19107042633','女'),
(5,'王浩','15207042633','男'),   
(6,'周春义','13907042633','女'), 
(7,'程兆兰','15907042633','女'),  
(8,'李明','15907042631','男'),
(9,'刘兰','15907042639','女'),
(10,'杨鹏','15907042638','男');

 

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

1. 查询student表中所有学生信息
2. student表中新增三条数据
3. 查询student表中电话以182开头的所有学生信息（SQL要用到模糊查询like）
4. 修改student表中第5条数据，电话改为“13124235300”
5. 查询student表中所有男性的学生信息
6. 修改student表中第10条数据，姓名改为“大帅哥”
7. 查询student表中电话以139开头的所有学生信息（SQL要用到模糊查询like）
8. 删除student表中第3条数据
9. 查询student表中所有女性的学生信息
10. 查询student表中所有学生信息

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 studentSql {
	Connection connection;

	public static void main(String[] args) throws SQLException {
		studentSql studentsql=new studentSql();
		studentsql.getConnection();
		System.out.println("查询所有学生数据，结果为：");
		studentsql.selectAll();
		System.out.println("添加三条学生数据,结果为：");
		studentsql.add(11,"王荣","13307032621","男");
		studentsql.add(12,"李华","13307032622","男");
		studentsql.add(13,"王丽","13907032623","女");
		studentsql.selectAll();
		System.out.println("查询中电话以182开头的所有学生信息,结果为：");
		studentsql.selectPart182();
		System.out.println("修改第5条数据,结果为：");
		studentsql.update5(5,"13124235300");
		studentsql.selectAll();
		System.out.println("查询男性所有学生信息,结果为：");
		studentsql.selectMan();
		System.out.println("修改第10条数据,结果为：");
		studentsql.update10(10,"大帅哥");
		studentsql.selectAll();
		System.out.println("查询中电话以139开头的所有学生信息,结果为：");
		studentsql.selectPart139();
		System.out.println("删除第3条数据,结果为：");
		studentsql.delete(3);
		studentsql.selectAll();
		System.out.println("查询女性所有学生信息,结果为：");
		studentsql.selectWoman();
		System.out.println("查询所有学生数据，结果为：");
		studentsql.selectAll();
		studentsql.close();
	}	
	
	//查询女性所有学生信息
	public void selectWoman() throws SQLException {
		//获取statement对象
		PreparedStatement preparedStatement=
		connection.prepareStatement("select*from student where sex='女';");
				//执行SQL语句返回结果集
		ResultSet resultSet=preparedStatement.executeQuery();
					//遍历结果集
		while(resultSet.next()) {
			System.out.print(resultSet.getInt("id")+" ");
			System.out.print(resultSet.getString("name")+" ");
			System.out.print(resultSet.getString("tel")+" ");
			System.out.println(resultSet.getString("sex")+" ");
						}
					//关闭连接释放资源
					resultSet.close();
					preparedStatement.close();
	}


	//删除student表中第3条数据
	public void delete(int id) throws SQLException {
		//获取statement对象
		PreparedStatement preparedStatement=
		connection.prepareStatement("delete from student where id= ?;");
		preparedStatement.setInt(1,id);
					//执行SQL语句返回结果集
    	preparedStatement.executeUpdate();							
	}

	//查询中电话以139开头的所有学生信息
	public void selectPart139() throws SQLException {
		//获取statement对象
				PreparedStatement preparedStatement=
				connection.prepareStatement("select*from student where tel like '139%';");
				//执行SQL语句返回结果集
				ResultSet resultSet=preparedStatement.executeQuery();
				//遍历结果集
				while(resultSet.next()) {
					System.out.print(resultSet.getInt("id")+" ");
					System.out.print(resultSet.getString("name")+" ");
					System.out.print(resultSet.getString("tel")+" ");
					System.out.println(resultSet.getString("sex")+" ");
					}
				//关闭连接释放资源
				resultSet.close();
				preparedStatement.close();							
				}

	//修改student表中第10条数据，姓名改为“大帅哥”
	public void update10(int id, String name) throws SQLException {
		//获取statement对象
				PreparedStatement preparedStatement=
		connection.prepareStatement("update student set name= ? where id= ?;");
				preparedStatement.setString(1, name);
				preparedStatement.setInt(2, id);
				//获取statement对象
				preparedStatement.executeUpdate();
			}		

	//查询所有男性的学生信息
	public void selectMan() throws SQLException {
		//获取statement对象
	PreparedStatement preparedStatement=
	connection.prepareStatement("select*from student where sex='男';");
			//执行SQL语句返回结果集
	ResultSet resultSet=preparedStatement.executeQuery();
				//遍历结果集
	while(resultSet.next()) {
		System.out.print(resultSet.getInt("id")+" ");
		System.out.print(resultSet.getString("name")+" ");
		System.out.print(resultSet.getString("tel")+" ");
		System.out.println(resultSet.getString("sex")+" ");
					}
				//关闭连接释放资源
				resultSet.close();
				preparedStatement.close();					
				}
		
//修改第5条数据
	public void update5(int id,String tel) throws SQLException {
		//获取statement对象
		PreparedStatement preparedStatement=
connection.prepareStatement("update student set tel= ? where id= ?;");
		preparedStatement.setString(1, tel);
		preparedStatement.setInt(2, id);
		//获取statement对象
		preparedStatement.executeUpdate();
	}
	

//查询电话以182开头的所有学生信息
public void selectPart182() throws SQLException {
	//获取statement对象
		PreparedStatement preparedStatement=
		connection.prepareStatement("select*from student where tel like '182%';");
		//执行SQL语句返回结果集
		ResultSet resultSet=preparedStatement.executeQuery();
		//遍历结果集
		while(resultSet.next()) {
			System.out.print(resultSet.getInt("id")+" ");
			System.out.print(resultSet.getString("name")+" ");
			System.out.print(resultSet.getString("tel")+" ");
			System.out.println(resultSet.getString("sex")+" ");
			}
		//关闭连接释放资源
		resultSet.close();
		preparedStatement.close();		
		}

	//添加课程
	public void add(int id,String name,String tel,String sex) throws SQLException {
//获取statement对象
		PreparedStatement preparedStatement=connection.prepareStatement("insert into student value(?,?,?,?);");
		preparedStatement.setInt(1,id);
		preparedStatement.setString(2,name);
		preparedStatement.setString(3,tel);
		preparedStatement.setString(4,sex);
	//执行SQL语句返回结果集
		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 {
	//第三步：获取statement对象
	PreparedStatement preparedStatement=
	connection.prepareStatement("select*from student;");
	//第四步：执行SQL语句返回结果集
	ResultSet resultSet=preparedStatement.executeQuery();
	//第五步：遍历结果集
	while(resultSet.next()) {
		System.out.print(resultSet.getInt("id")+" ");
		System.out.print(resultSet.getString("name")+" ");
		System.out.print(resultSet.getLong("tel")+" ");
		System.out.println(resultSet.getString("sex")+" ");
		}
	//第六步：关闭连接释放资源
	resultSet.close();
	preparedStatement.close();
		
	}
	public void close()throws SQLException {
		connection.close();
	}
}

运行结果：