mysql数据库级联查询

-- 创建订单表
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE
);

-- 创建客户表
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    email VARCHAR(100)
);

SELECT orders.order_id, orders.order_date, customers.customer_name, customers.email
FROM orders
LEFT JOIN customers ON orders.customer_id = customers.customer_id;

SELECT DISTINCT orders.order_id, orders.order_date, customers.customer_name, customers.email
FROM orders
LEFT JOIN customers ON orders.customer_id = customers.customer_id;

-- 在customer_id字段上添加索引
ALTER TABLE orders ADD INDEX idx_customer_id (customer_id);
ALTER TABLE customers ADD INDEX idx_customer_id (customer_id);

-- 设置外键约束
ALTER TABLE orders
ADD CONSTRAINT fk_customer_id
FOREIGN KEY (customer_id) REFERENCES customers(customer_id);