(1)在student数据库上创建一个用户表tb_account,该表包含用户id,用户名和密码。
字段名称 | 数据类型 | 注释 | 约束 |
user_id | Char(8) | 用户id | 主键 |
user_name | char(10) | 用户名 | 不能为空 |
password | char(10) | 密码 | 默认值:123456 |
(2) 将学生表tb_student表中的学号和姓名全部添加到用户表tb_account中。
参考完整代码如下:
import java.sql.*;
public class case2 {
static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost:3306/student";
static final String USER = "user"; // 替换为你的用户名
static final String PASS = "password"; // 替换为你的密码
public static void main(String[] args) {
Connection conn = null;
try {
// 加载 JDBC 驱动
Class.forName(JDBC_DRIVER);
// 建立连接
conn = DriverManager.getConnection(DB_URL, USER, PASS);
// 创建 tb_account 表
String createTableSQL = "CREATE TABLE IF NOT EXISTS `tb_account` ("
+ " `user_id` CHAR(8) NOT NULL,"
+ " `user_name` CHAR(10) NOT NULL,"
+ " `password` CHAR(10) NOT NULL DEFAULT '123456',"
+ " PRIMARY KEY (`user_id`)"
+ ")";
try (Statement statement = conn.createStatement()) {
statement.executeUpdate(createTableSQL);
System.out.println("Table 'tb_account' created successfully.");
}
// 插入数据到 tb_account 表
String insertSQL = "INSERT INTO `tb_account` (`user_id`, `user_name`)"
+ " SELECT `stu_id`, `stu_name` FROM `tb_student`";
try (Statement statement = conn.createStatement()) {
int rowsAffected = statement.executeUpdate(insertSQL);
if (rowsAffected > 0) {
System.out.println("Data inserted successfully into 'tb_account'.");
}
}
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
// 关闭数据库连接
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}