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,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- 查询某个客户的所有订单
SELECT * FROM orders WHERE customer_id = 1;

-- 查询订单及其对应的客户信息
SELECT orders.id, orders.order_date, customers.name, customers.email
FROM orders
JOIN customers ON orders.customer_id = customers.id;

ALTER TABLE orders MODIFY COLUMN customer_id INT NULL;

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
);

DELETE FROM orders WHERE customer_id = 1;
DELETE FROM customers WHERE id = 1;