项目概述：

该项目要求设计一个“学生信息管理系统”，并且该系统有能对学生信息进行增、删、查、改的功能。 

这里我们需要用到MySQL Workbench 8.0 CE 和IntelliJ IDEA Community Edition 2021.2。

项目目标：

根据“学生信息管理系统”的需求，我们有三大目标需要实现。

1. 首先，我们要在创建的数据库中创建三张表。分别是“学生表”、“课程表”、“教师表”。这三张表要有一定的联系。并且，每张表要有各自的属性。
2. 其次，我们要定义三种用户，分别是管理员、教师、和学生。管理员权限最大，教师有查看自己所教班级的学生信息，并且能够修改所教课程的学生成绩。学生只能查看自己的学生信息，并且只有在管理员授权的情况下才能修改信息。这就要用到: CREATE USER 用户名 IDENTIFIED BY "密码"; drop user 用户名;等SQL语句。
3. 最后，还要设计一个图形界面来进行登录、查询等操作。该界面一定要有实现各个功能的按钮，以及显示结果的地方。

具体语句：

可与下面的语句类似

删：

package database;
import java.sql.*;
public class Delete {
	public static void main(String[] args) throws SQLException {
		String dburl = "jdbc:mysql://localhost:3306/student?serverTimezone=Asia/Shanghai";
		Connection conn = null;
		PreparedStatement stmt = null;				
		String delete_stuid = "101";
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			conn = DriverManager.getConnection(dburl, "root", "1234");
			String deletesql = "delete from stuinfo where stuid = ?";
			stmt = conn.prepareStatement(deletesql);
			stmt.setString(1, delete_stuid);			
			int delete_count = stmt.executeUpdate();
			System.out.println("删除" + delete_count + "条记录");
		}
		catch (SQLException | ClassNotFoundException e) {
			System.out.println("错误：" + e.getMessage());
		}
		finally {				
			stmt.close();
			conn.close();
		}
	}
}

增：

package database;
import java.sql.*;
public class Insert {
    public static void main(String[] args) throws SQLException {
        String dburl = "jdbc:mysql://localhost:3306/student?serverTimezone=Asia/Shanghai";
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String insert_stuid = "120";
        String insert_stuname = "Jerry";
        double insert_math = 70;
        double insert_english = 65;
        double insert_history = 98;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(dburl, "root", "1234");
            String sql = "select * from stuinfo  where stuid = ?";
            stmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
            stmt.setString(1, insert_stuid);
            rs = stmt.executeQuery();
            if (!rs.next()) {
                String insertsql = "insert into stuinfo values(?,?,?,?,?)";
                stmt = conn.prepareStatement(insertsql);
                stmt.setString(1, insert_stuid);
                stmt.setString(2, insert_stuname);
                stmt.setDouble(3, insert_math);
                stmt.setDouble(4, insert_english);
                stmt.setDouble(5, insert_history);
                stmt.executeUpdate();
                System.out.println(insert_stuid + "记录成功插入");
            }
            else {
                System.out.println(insert_stuid + "已在库中");
            }
            System.out.println("-----------------------------------");
        }
        catch (SQLException | ClassNotFoundException e) {
            System.out.println("错误：" + e.getMessage());
        }
        finally {
            rs.close();
            stmt.close();
            conn.close();
            System.out.println("结果集、状态、连接已关闭");
        }
    }
}

查：

package database;

import java.sql.*;

public class Query {
    public static void main(String[] args) throws SQLException {
        // 待操作数据库表的位置
        String dburl = "jdbc:mysql://localhost:3306/student?serverTimezone=Asia/Shanghai";
        Connection conn = null;  // 连接
        PreparedStatement stmt = null;  // 状态
        ResultSet rs = null;  // 结果集
        int count;  // 查询记录条数
        String stuid_key = "100";  // 查询关键字
        double math_key = 0;  // 查询关键字
        try {
            // 装载MySQL数据库驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立数据库连接
            conn = DriverManager.getConnection(dburl, "root", "1234");
            // 创建sql语句
            String sql = "select * from stuinfo where stuid > ? and math > ? "
                    + "order by stuid asc";
            // 创建状态
            stmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            stmt.setString(1, stuid_key);  // 填补sql语句参数
            stmt.setDouble(2, math_key);  // 填补sql语句参数
            rs = stmt.executeQuery();  // 执行查询并返回结果集
            while (rs.next()) {  // 结果集记录输出
                System.out.println(rs.getString(1) + " " + rs.getString(2)
                        + " " + rs.getDouble(3) + " " + rs.getDouble(4)
                        + " " + rs.getDouble(5));
            }
            System.out.println("----------------------------------------------");
            rs.previous();  // 结果集指针回指一次
            count = rs.getRow();  // 得到结果集记录条数
            System.out.println("共检索出" + count + "条记录");
        }
        catch (SQLException | ClassNotFoundException e) {
            System.out.println("错误：" + e.getMessage());
        }
        finally {
            rs.close();
            stmt.close();
            conn.close();
            System.out.println("结果集、状态、连接已关闭");
        }
    }
}

改：

package database;
import java.sql.*;
public class Update {
	public static void main(String[] args) throws SQLException {
		String dburl = "jdbc:mysql://localhost:3306/student?serverTimezone=Asia/Shanghai";
		Connection conn = null;
		PreparedStatement stmt = null;		
		String key_name = "张三";
		String update_name = "李四";
		double update_math = 90;
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			conn = DriverManager.getConnection(dburl, "root", "1234");
			String updatesql = "update stuinfo set stuname = ?,math = ? where "
					+ "stuname = ?";
			stmt = conn.prepareStatement(updatesql);
			stmt.setString(1, update_name);
			stmt.setDouble(2, update_math);
			stmt.setString(3, key_name);
			int update_count = stmt.executeUpdate();
			System.out.println("共更新" + update_count + "条记录");
		}
		catch (SQLException | ClassNotFoundException e) {
			System.out.println("错误：" + e.getMessage());
		}
		finally {
			stmt.close();
			conn.close();
		}
	}
}

同时数据库与IDEA的连接也很重要。

①导入的驱动包

②程序代码

具体可参考https://blog.csdn.net/weixin_33888685/article/details/113466510