超市管理系统大表生成及数据记录

超市管理系统子表

-- Creating database for supermarket management
CREATE DATABASE IF NOT EXISTS supermarket;
USE supermarket;

-- Creating table for product categories
CREATE TABLE Categories (
    category_id INT PRIMARY KEY AUTO_INCREMENT,
    category_name VARCHAR(50) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Creating table for products
CREATE TABLE Products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    category_id INT,
    product_name VARCHAR(100) NOT NULL,
    barcode VARCHAR(50) UNIQUE,
    unit_price DECIMAL(10,2) NOT NULL,
    cost_price DECIMAL(10,2) NOT NULL,
    stock_quantity INT NOT NULL DEFAULT 0,
    min_stock_level INT DEFAULT 10,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES Categories(category_id)
);

-- Creating table for suppliers
CREATE TABLE Suppliers (
    supplier_id INT PRIMARY KEY AUTO_INCREMENT,
    supplier_name VARCHAR(100) NOT NULL,
    contact_person VARCHAR(50),
    phone VARCHAR(20),
    email VARCHAR(100),
    address TEXT
);

-- Creating table for inventory transactions
CREATE TABLE Inventory_Transactions (
    transaction_id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT,
    transaction_type ENUM('PURCHASE', 'SALE', 'RETURN', 'ADJUSTMENT') NOT NULL,
    quantity INT NOT NULL,
    transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    supplier_id INT,
    FOREIGN KEY (product_id) REFERENCES Products(product_id),
    FOREIGN KEY (supplier_id) REFERENCES Suppliers(supplier_id)
);

-- Creating table for orders
CREATE TABLE Orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10,2) NOT NULL,
    status ENUM('PENDING', 'COMPLETED', 'CANCELLED') DEFAULT 'PENDING'
);

-- Creating table for order details
CREATE TABLE Order_Details (
    detail_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    product_id INT,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    subtotal DECIMAL(10,2) GENERATED ALWAYS AS (quantity * unit_price) STORED,
    FOREIGN KEY (order_id) REFERENCES Orders(order_id),
    FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

-- Creating trigger for updating stock after sale
DELIMITER //
CREATE TRIGGER after_order_detail_insert
AFTER INSERT ON Order_Details
FOR EACH ROW
BEGIN
    DECLARE current_stock INT;
    
    -- Get current stock
    SELECT stock_quantity INTO current_stock 
    FROM Products 
    WHERE product_id = NEW.product_id;
    
    -- Update stock
    UPDATE Products 
    SET stock_quantity = current_stock - NEW.quantity,
        last_updated = CURRENT_TIMESTAMP
    WHERE product_id = NEW.product_id;
    
    -- Insert inventory transaction
    INSERT INTO Inventory_Transactions (product_id, transaction_type, quantity)
    VALUES (NEW.product_id, 'SALE', NEW.quantity);
    
    -- Check if stock is below minimum level
    IF (current_stock - NEW.quantity) < (SELECT min_stock_level FROM Products WHERE product_id = NEW.product_id) THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Warning: Stock level below minimum threshold!';
    END IF;
END //
DELIMITER ;

-- Creating stored procedure for generating sales report
DELIMITER //
CREATE PROCEDURE GenerateSalesReport(
    IN start_date DATE,
    IN end_date DATE,
    IN category_id INT
)
BEGIN
    SELECT 
        c.category_name,
        p.product_name,
        SUM(od.quantity) as total_quantity_sold,
        SUM(od.subtotal) as total_revenue,
        AVG(od.unit_price) as avg_selling_price,
        COUNT(DISTINCT o.order_id) as total_orders
    FROM Orders o
    JOIN Order_Details od ON o.order_id = od.order_id
    JOIN Products p ON od.product_id = p.product_id
    JOIN Categories c ON p.category_id = c.category_id
    WHERE o.order_date BETWEEN start_date AND end_date
    AND (category_id IS NULL OR p.category_id = category_id)
    AND o.status = 'COMPLETED'
    GROUP BY p.product_id
    ORDER BY total_revenue DESC;
END //
DELIMITER ;

-- Creating stored procedure for restocking products
DELIMITER //
CREATE PROCEDURE RestockProduct(
    IN p_product_id INT,
    IN p_quantity INT,
    IN p_supplier_id INT
)
BEGIN
    DECLARE v_cost_price DECIMAL(10,2);
    
    -- Start transaction
    START TRANSACTION;
    
    -- Get current cost price
    SELECT cost_price INTO v_cost_price 
    FROM Products 
    WHERE product_id = p_product_id;
    
    -- Update stock
    UPDATE Products 
    SET stock_quantity = stock_quantity + p_quantity,
        last_updated = CURRENT_TIMESTAMP
    WHERE product_id = p_product_id;
    
    -- Record inventory transaction
    INSERT INTO Inventory_Transactions (product_id, transaction_type, quantity, supplier_id)
    VALUES (p_product_id, 'PURCHASE', p_quantity, p_supplier_id);
    
    COMMIT;
END //
DELIMITER ;

-- Creating view for low stock products
CREATE VIEW Low_Stock_Products AS
SELECT 
    p.product_id,
    p.product_name,
    p.stock_quantity,
    p.min_stock_level,
    c.category_name,
    (p.min_stock_level - p.stock_quantity) as restock_needed
FROM Products p
JOIN Categories c ON p.category_id = c.category_id
WHERE p.stock_quantity < p.min_stock_level;

-- Inserting sample data
INSERT INTO Categories (category_name, description) VALUES
('Beverages', 'Soft drinks and juices'),
('Snacks', 'Chips and snacks'),
('Dairy', 'Milk and cheese products');

INSERT INTO Suppliers (supplier_name, contact_person, phone, email) VALUES
('Fresh Foods Inc.', 'John Smith', '555-0101', 'john@freshfoods.com'),
('Global Snacks Ltd.', 'Mary Johnson', '555-0102', 'mary@globalsnacks.com');

INSERT INTO Products (category_id, product_name, barcode, unit_price, cost_price, stock_quantity, min_stock_level) VALUES
(1, 'Cola 500ml', '123456789012', 1.99, 1.20, 100, 20),
(1, 'Orange Juice 1L', '123456789013', 2.99, 1.80, 50, 15),
(2, 'Potato Chips 200g', '123456789014', 2.49, 1.50, 80, 25),
(3, 'Cheddar Cheese 500g', '123456789015', 5.99, 4.00, 30, 10);

-- Creating index for frequently searched columns
CREATE INDEX idx_product_barcode ON Products(barcode);
CREATE INDEX idx_order_date ON Orders(order_date);

-- Example usage of stored procedures
CALL RestockProduct(1, 50, 1);
CALL GenerateSalesReport('2025-01-01', '2025-12-31', NULL);

总表

-- 0. 如果之前已经建过，先清掉
DROP TABLE IF EXISTS bigshop;

-- 1. 建立总表 bigshop
CREATE TABLE bigshop (
    -- 公共维度
    source_table      ENUM('Categories','Products','Suppliers',
                          'Inventory_Transactions','Orders','Order_Details'),
    source_pk         INT,          -- 原表主键
    source_fk         INT,          -- 可能的父表主键（如 order_id / product_id）

    -- ========= Categories =========
    category_id       INT,
    category_name     VARCHAR(50),
    description       TEXT,

    -- ========= Products =========
    product_id        INT,
    barcode           VARCHAR(50),
    unit_price        DECIMAL(10,2),
    cost_price        DECIMAL(10,2),
    stock_quantity    INT,
    min_stock_level   INT,
    last_updated      TIMESTAMP,

    -- ========= Suppliers =========
    supplier_id       INT,
    supplier_name     VARCHAR(100),
    contact_person    VARCHAR(50),
    phone             VARCHAR(20),
    email             VARCHAR(100),
    address           TEXT,

    -- ========= Inventory_Transactions =========
    transaction_id    INT,
    transaction_type  ENUM('PURCHASE','SALE','RETURN','ADJUSTMENT'),
    quantity          INT,
    transaction_date  TIMESTAMP,

    -- ========= Orders =========
    order_id          INT,
    order_date        TIMESTAMP,
    total_amount      DECIMAL(10,2),
    status            ENUM('PENDING','COMPLETED','CANCELLED'),

    -- ========= Order_Details =========
    detail_id         INT,
    quantity_detail   INT,          -- 避免与 Inventory_Transactions.quantity 冲突
    unit_price_detail DECIMAL(10,2),
    subtotal          DECIMAL(10,2),

    -- 统一时间戳
    created_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

数据插入

-- 8.0+ 随机插入 50 行
INSERT INTO bigshop (
    source_table, source_pk, source_fk,
    category_id, category_name, description,
    product_id, barcode,
    unit_price, cost_price, stock_quantity, min_stock_level,
    last_updated,
    supplier_id, supplier_name, contact_person, phone, email, address,
    transaction_id, transaction_type, quantity, transaction_date,
    order_id, order_date, total_amount, status,
    detail_id, quantity_detail, unit_price_detail, subtotal,
    created_at
)
WITH RECURSIVE rowgen AS (
    SELECT 1 AS n UNION ALL
    SELECT n + 1 FROM rowgen WHERE n < 50
)
SELECT
    ELT(1 + FLOOR(RAND()*6),
        'Categories','Products','Suppliers',
        'Inventory_Transactions','Orders','Order_Details')            AS source_table,

    FLOOR(RAND()*1000000)                                              AS source_pk,
    CASE WHEN RAND() < 0.4 THEN FLOOR(RAND()*1000) END                 AS source_fk,

    -- Categories
    FLOOR(RAND()*100)                                                  AS category_id,
    SUBSTRING(REPLACE(UUID(),'-',''), 1, 10)                          AS category_name,
    SUBSTRING(REPLACE(UUID(),'-',''), 1, 30)                          AS description,

    -- Products
    FLOOR(RAND()*100000)                                               AS product_id,
   
    LPAD(FLOOR(RAND()*1e13), 13, '0')                                  AS barcode,
    ROUND(1 + RAND()*99, 2)                                            AS unit_price,
    ROUND(0.5 + RAND()*80, 2)                                          AS cost_price,
    FLOOR(RAND()*1000)                                                 AS stock_quantity,
    FLOOR(RAND()*50)                                                   AS min_stock_level,
    DATE_SUB(NOW(), INTERVAL FLOOR(RAND()*365) DAY)                    AS last_updated,

    -- Suppliers
    FLOOR(RAND()*1000)                                                 AS supplier_id,
    CONCAT('Supplier-', FLOOR(RAND()*999))                             AS supplier_name,
    CONCAT('Person-', FLOOR(RAND()*100))                               AS contact_person,
    CONCAT('1-', LPAD(FLOOR(RAND()*9999999), 7, '0'))                  AS phone,
    CONCAT('email', FLOOR(RAND()*10000), '@test.com')                  AS email,
    CONCAT('Addr-', SUBSTRING(MD5(RAND()),1,16))                       AS address,

    -- Inventory_Transactions
    FLOOR(RAND()*100000)                                               AS transaction_id,
    ELT(1 + FLOOR(RAND()*4), 'PURCHASE','SALE','RETURN','ADJUSTMENT')  AS transaction_type,
    FLOOR(RAND()*200 - 100)                                            AS quantity,
    DATE_SUB(NOW(), INTERVAL FLOOR(RAND()*365) DAY)                    AS transaction_date,

    -- Orders
    FLOOR(RAND()*100000)                                               AS order_id,
    DATE_SUB(NOW(), INTERVAL FLOOR(RAND()*365) DAY)                    AS order_date,
    ROUND(10 + RAND()*1000, 2)                                         AS total_amount,
    ELT(1 + FLOOR(RAND()*3), 'PENDING','COMPLETED','CANCELLED')        AS status,

    -- Order_Details
    FLOOR(RAND()*1000000)                                              AS detail_id,
    FLOOR(RAND()*20)                                                   AS quantity_detail,
    ROUND(1 + RAND()*100, 2)                                           AS unit_price_detail,
    ROUND((FLOOR(RAND()*20)) * (1 + RAND()*100), 2)                    AS subtotal,

    NOW()                                                              AS created_at
FROM rowgen;