mysql关联两表数据

-- users 表结构
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

-- orders 表结构
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    product VARCHAR(100),
    amount DECIMAL(10, 2)
);

-- 插入示例数据
INSERT INTO users (id, name, email) VALUES
(1, 'Alice', 'alice@example.com'),
(2, 'Bob', 'bob@example.com');

INSERT INTO orders (id, user_id, product, amount) VALUES
(1, 1, 'Laptop', 1000),
(2, 1, 'Phone', 500),
(3, 2, 'Tablet', 300);

SELECT users.name, orders.product, orders.amount
FROM users
INNER JOIN orders ON users.id = orders.user_id;

SELECT users.name, orders.product, orders.amount
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

SELECT users.name, orders.product, orders.amount
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

-- 错误的关联条件
SELECT users.name, orders.product, orders.amount
FROM users
INNER JOIN orders ON users.email = orders.user_id; -- 错误

-- 正确的关联条件
SELECT users.name, orders.product, orders.amount
FROM users
INNER JOIN orders ON users.id = orders.user_id; -- 正确

-- 在关联字段上创建索引
CREATE INDEX idx_user_id ON orders(user_id);

-- 添加外键约束
ALTER TABLE orders
ADD CONSTRAINT fk_user_id
FOREIGN KEY (user_id) REFERENCES users(id);