mysql 一对多查询

-- 创建用户表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100)
);

-- 创建订单表
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    amount DECIMAL(10, 2),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 插入示例数据
INSERT INTO users (name) VALUES ('Alice'), ('Bob');
INSERT INTO orders (user_id, amount) VALUES (1, 100.00), (1, 200.00), (2, 150.00);

-- 查询一个用户的所有订单
SELECT * FROM orders WHERE user_id = 1;

-- 检查外键约束
SHOW CREATE TABLE orders;

-- 插入数据时确保user_id存在
INSERT INTO orders (user_id, amount) VALUES (1, 300.00); -- user_id 1 存在

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

-- 优化查询语句
SELECT * FROM orders WHERE user_id = 1;