mysql外键关联表查询

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

-- 创建订单表
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    amount DECIMAL(10, 2) NOT NULL,
    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 u.name AS user_name, o.id AS order_id, o.amount AS order_amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.name = 'Alice';

-- 插入父表数据
INSERT INTO users (name) VALUES ('Charlie');

-- 插入子表数据
INSERT INTO orders (user_id, amount) VALUES (3, 300.00);

-- 创建索引
CREATE INDEX idx_user_id ON orders(user_id);

-- 优化查询语句
SELECT u.name AS user_name, o.id AS order_id, o.amount AS order_amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.name = 'Alice' AND o.amount > 100.00;