mysql多表添加数据

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    order_date DATETIME,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    product_name VARCHAR(255),
    quantity INT,
    FOREIGN KEY (order_id) REFERENCES orders(id)
);

-- 插入用户
INSERT INTO users (name) VALUES ('Alice');
INSERT INTO users (name) VALUES ('Bob');

-- 获取插入的用户ID
SET @user_id_alice = LAST_INSERT_ID();
SET @user_id_bob = LAST_INSERT_ID() + 1;

-- 插入订单
INSERT INTO orders (user_id, order_date) VALUES (@user_id_alice, NOW());
INSERT INTO orders (user_id, order_date) VALUES (@user_id_bob, NOW());

-- 获取插入的订单ID
SET @order_id_alice = LAST_INSERT_ID();
SET @order_id_bob = LAST_INSERT_ID();

-- 插入订单项
INSERT INTO order_items (order_id, product_name, quantity) VALUES (@order_id_alice, 'Product A', 2);
INSERT INTO order_items (order_id, product_name, quantity) VALUES (@order_id_alice, 'Product B', 1);
INSERT INTO order_items (order_id, product_name, quantity) VALUES (@order_id_bob, 'Product C', 3);

-- 确保用户存在
INSERT INTO users (name) VALUES ('Charlie');

-- 再插入订单
INSERT INTO orders (user_id, order_date) VALUES (LAST_INSERT_ID(), NOW());

START TRANSACTION;

INSERT INTO users (name) VALUES ('David');
SET @user_id_david = LAST_INSERT_ID();

INSERT INTO orders (user_id, order_date) VALUES (@user_id_david, NOW());
SET @order_id_david = LAST_INSERT_ID();

INSERT INTO order_items (order_id, product_name, quantity) VALUES (@order_id_david, 'Product D', 4);

COMMIT;