用Java语句创建三个表,订单表,商品表,用户表和管理员表,并创建触发器
时间: 2023-08-29 08:04:29 浏览: 41
好的,以下是用Java语句创建四个表的示例代码:
```java
import java.sql.*;
public class CreateTablesWithTriggers {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/mydatabase";
String username = "root";
String password = "password";
try (Connection conn = DriverManager.getConnection(url, username, password);
Statement stmt = conn.createStatement()) {
// 创建用户表
String createUserTable = "CREATE TABLE users (" +
"id INT NOT NULL AUTO_INCREMENT," +
"username VARCHAR(50) NOT NULL," +
"password VARCHAR(50) NOT NULL," +
"PRIMARY KEY (id)" +
")";
stmt.executeUpdate(createUserTable);
// 创建商品表
String createProductTable = "CREATE TABLE products (" +
"id INT NOT NULL AUTO_INCREMENT," +
"name VARCHAR(50) NOT NULL," +
"price DECIMAL(10, 2) NOT NULL," +
"description VARCHAR(100)," +
"PRIMARY KEY (id)" +
")";
stmt.executeUpdate(createProductTable);
// 创建订单表
String createOrderTable = "CREATE TABLE orders (" +
"id INT NOT NULL AUTO_INCREMENT," +
"user_id INT NOT NULL," +
"product_id INT NOT NULL," +
"quantity INT NOT NULL," +
"total DECIMAL(10, 2) NOT NULL," +
"timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP," +
"PRIMARY KEY (id)," +
"FOREIGN KEY (user_id) REFERENCES users(id)," +
"FOREIGN KEY (product_id) REFERENCES products(id)" +
")";
stmt.executeUpdate(createOrderTable);
// 创建管理员表
String createAdminTable = "CREATE TABLE admins (" +
"id INT NOT NULL AUTO_INCREMENT," +
"username VARCHAR(50) NOT NULL," +
"password VARCHAR(50) NOT NULL," +
"PRIMARY KEY (id)" +
")";
stmt.executeUpdate(createAdminTable);
// 创建订单触发器,自动更新商品表中的库存数量
String createOrderTrigger = "CREATE TRIGGER update_inventory " +
"AFTER INSERT ON orders " +
"FOR EACH ROW " +
"BEGIN " +
"UPDATE products SET inventory = inventory - NEW.quantity " +
"WHERE id = NEW.product_id; " +
"END";
stmt.executeUpdate(createOrderTrigger);
System.out.println("Tables and triggers created successfully.");
} catch (SQLException e) {
e.printStackTrace();
}
}
}
```
注意:以上代码仅为示例,实际应用中需要根据具体业务需求进行修改。
相关推荐
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)