数据库课程设计mysql

数据库课程设计mysql
当涉及到MySQL数据库的课程设计时，以下是一个示例的学生信息管理系统的设计，包括学生信息的存储、查询、添加、修改和删除功能。

假设我们有一个学生信息表students，包含以下字段：

id：学生ID（主键）
name：学生姓名
age：学生年龄
gender：学生性别
department：学生所属院系
下面是一个使用Java和MySQL的学生信息管理系统的示例代码：

java
Copy code
import java.sql.*;

public class StudentManagementSystem {
// MySQL数据库连接配置
private static final String DB_URL = "jdbc:mysql://localhost:3306/studentdb";
private static final String DB_USER = "your_username";
private static final String DB_PASSWORD = "your_password";

public static void main(String[] args) {
// 建立数据库连接
try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
System.out.println("Connected to database successfully!");

// 创建学生信息表
createStudentTable(conn);

// 添加学生信息
addStudent(conn, "S001", "Alice", 20, "Female", "Computer Science");
addStudent(conn, "S002", "Bob", 21, "Male", "Electrical Engineering");
addStudent(conn, "S003", "Cindy", 19, "Female", "Mechanical Engineering");

// 查询学生信息
queryStudents(conn);

// 修改学生信息
updateStudent(conn, "S001", "Alice Smith", 21);

// 删除学生信息
deleteStudent(conn, "S003");

// 查询更新后的学生信息
queryStudents(conn);

} catch (SQLException e) {
e.printStackTrace();
}
}

// 创建学生信息表
private static void createStudentTable(Connection conn) throws SQLException {
String sql = "CREATE TABLE IF NOT EXISTS students (id VARCHAR(10) PRIMARY KEY, " +
"name VARCHAR(50), age INT, gender VARCHAR(10), department VARCHAR(50))";
try (Statement stmt = conn.createStatement()) {
stmt.executeUpdate(sql);
System.out.println("Student table created successfully!");
}
}

// 添加学生信息
private static void addStudent(Connection conn, String id, String name, int age, String gender, String department) throws SQLException {
String sql = "INSERT INTO students (id, name, age, gender, department) VALUES (?, ?, ?, ?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, id);
pstmt.setString(2, name);
pstmt.setInt(3, age);
pstmt.setString(4, gender);
pstmt.setString(5, department);
pstmt.executeUpdate();
System.out.println("Student added successfully!");
}
}

// 查询学生信息
private static void queryStudents(Connection conn) throws SQLException {
String sql = "SELECT * FROM students";
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
System.out.println("Students in the database:");
while (rs.next()) {
String id = rs.getString("id");
String name = rs.getString("name");
int age = rs.getInt("age");
String gender = rs.getString("gender");
String department = rs.getString("department");
System.out.println("ID: " + id + ", Name: