mysql通过外键查询

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

SELECT o.order_id, o.order_date, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_name = 'John Doe';

-- 确保客户存在
INSERT INTO customers (customer_id, customer_name) VALUES (1, 'John Doe');

-- 插入订单
INSERT INTO orders (order_id, order_date, customer_id) VALUES (1, '2023-10-01', 1);

-- 确保客户存在
UPDATE customers SET customer_name = 'Jane Doe' WHERE customer_id = 1;

-- 更新订单
UPDATE orders SET customer_id = 1 WHERE order_id = 1;

-- 设置级联删除
ALTER TABLE orders DROP FOREIGN KEY fk_customer_id;
ALTER TABLE orders ADD CONSTRAINT fk_customer_id FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE;

-- 删除客户
DELETE FROM customers WHERE customer_id = 1;