mysql一对多实现

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

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

INSERT INTO users (name) VALUES ('Alice');
INSERT INTO orders (user_id, order_date) VALUES (1, '2023-01-01');
INSERT INTO orders (user_id, order_date) VALUES (1, '2023-01-15');

SELECT users.name, orders.order_date
FROM users
JOIN orders ON users.id = orders.user_id;

-- 错误示例
INSERT INTO orders (user_id, order_date) VALUES (2, '2023-02-01'); -- user_id=2不存在

-- 正确示例
INSERT INTO users (name) VALUES ('Bob');
INSERT INTO orders (user_id, order_date) VALUES (2, '2023-02-01'); -- user_id=2存在

-- 添加索引
ALTER TABLE orders ADD INDEX idx_user_id (user_id);

-- 优化查询
SELECT users.name, orders.order_date
FROM users
JOIN orders ON users.id = orders.user_id
WHERE users.name = 'Alice';