How to use MySQL to create an order product table to implement order product management functions

How to use MySQL to create an order product table to implement the order product management function

When establishing an e-commerce platform or implementing the order management function, order product management is a very important link. MySQL database is one of the most commonly used relational databases, which can help us easily manage and store order product information. This article will introduce how to use MySQL to create an order product table and implement the order product management function.

First, create a table named "order_goods" in the MySQL database to store order product information. This table needs to contain some columns to store information about the products. The following is an example table structure:

CREATE TABLE order_goods (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    price DECIMAL(10,2),
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

In the above code, we defined six columns, namely:

• id: auto-incremented primary key, used for uniqueness Identify each order item.
• order_id: Foreign key, pointing to the order number in the order table.
• product_id: foreign key, pointing to the product number in the product table.
• quantity: Product quantity.
• price: Product unit price.

After creating the table structure, we can insert order product data into the "order_goods" table through the following example code:

INSERT INTO order_goods (order_id, product_id, quantity, price)
VALUES
    (1, 1, 2, 10.99),
    (1, 2, 1, 5.99),
    (2, 3, 3, 7.99);

With the above code, we can insert order product data into the order product table The data of three order items is inserted into . Each order item is associated with an order and a product, and includes quantity and unit price.

The following is an example code of how to use MySQL to query order product information:

SELECT
    og.id AS order_goods_id,
    o.order_number,
    p.product_name,
    og.quantity,
    og.price
FROM
    order_goods og
    JOIN orders o ON o.id = og.order_id
    JOIN products p ON p.id = og.product_id
WHERE
    o.order_number = '202105010001';

Through the above code, we can query the order product information of a specified order number (such as '202105010001') and return Order product ID, order number, product name, product quantity and product unit price.

The above code example shows how to use MySQL to create an order product table and implement the order product management function. Through this table, we can easily query, insert, update and delete order product information. In actual applications, we can expand the table structure according to needs, such as adding more columns to store other related information. I hope this article will help you understand and use MySQL to create an order item table.

The above is the detailed content of How to use MySQL to create an order product table to implement order product management functions. For more information, please follow other related articles on the PHP Chinese website!