数据库存储11.11促销活动

-- 创建商品表
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(255),
    original_price DECIMAL(10, 2)
);

-- 创建促销活动表
CREATE TABLE promotions (
    promotion_id INT PRIMARY KEY,
    product_id INT,
    discount DECIMAL(5, 2),
    start_date DATE,
    end_date DATE,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- 插入示例商品
INSERT INTO products (product_id, name, original_price) VALUES (1, 'Smartphone', 500.00);

-- 插入促销活动
INSERT INTO promotions (promotion_id, product_id, discount, start_date, end_date) 
VALUES (1, 1, 100.00, '2023-11-01', '2023-11-11');

-- 查询促销价格
SELECT p.name, p.original_price - pr.discount AS sale_price 
FROM products p 
JOIN promotions pr ON p.product_id = pr.product_id 
WHERE pr.start_date <= CURRENT_DATE AND pr.end_date >= CURRENT_DATE;