mysql两张表外键查询

-- 创建 customers 表
CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(100)
);

-- 创建 orders 表
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_date DATE,
    amount DECIMAL(10, 2),
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

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

INSERT INTO orders (order_date, amount, customer_id) VALUES ('2023-01-01', 100.00, 1);
INSERT INTO orders (order_date, amount, customer_id) VALUES ('2023-01-02', 200.00, 2);

-- 外键查询示例
SELECT o.id AS order_id, o.order_date, o.amount, c.name AS customer_name, c.email AS customer_email
FROM orders o
JOIN customers c ON o.customer_id = c.id;

-- 示例：插入无效数据
INSERT INTO orders (order_date, amount, customer_id) VALUES ('2023-01-03', 150.00, 3); -- customer_id 3 不存在

-- 解决方法：确保 customer_id 存在
INSERT INTO customers (name, email) VALUES ('Charlie', 'charlie@example.com');
INSERT INTO orders (order_date, amount, customer_id) VALUES ('2023-01-03', 150.00, 3);

-- 示例：添加索引
ALTER TABLE orders ADD INDEX idx_customer_id (customer_id);

-- 示例：分页查询
SELECT o.id AS order_id, o.order_date, o.amount, c.name AS customer_name, c.email AS customer_email
FROM orders o
JOIN customers c ON o.customer_id = c.id
LIMIT 10 OFFSET 0;