mysql 创建表关联

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

INSERT INTO users (username, email) VALUES
('Alice', 'alice@example.com'),
('Bob', 'bob@example.com');

INSERT INTO orders (user_id, order_date, total_amount) VALUES
(1, '2023-01-01', 100.00),
(1, '2023-02-01', 150.00),
(2, '2023-01-15', 200.00);

SELECT u.username, o.order_date, o.total_amount
FROM users u
JOIN orders o ON u.user_id = o.user_id;

-- 确保 user_id 存在于 users 表中
INSERT INTO orders (user_id, order_date, total_amount) VALUES
(1, '2023-03-01', 120.00);

-- 添加索引
CREATE INDEX idx_user_id ON orders(user_id);

-- 优化查询语句
SELECT u.username, o.order_date, o.total_amount
FROM users u
JOIN orders o ON u.user_id = o.user_id
WHERE u.username = 'Alice';