MySQL table design practice: Create a product classification table and association table

MySQL table design practice: Create a product classification table and related table

In database design, table design is a very important part. This article will take creating a product classification table and association table as an example to introduce the practical design of MySQL tables.

1. Create product classification table

The product classification table is a table used to store product classification information. Each product can belong to one or more categories, and each category can contain one or more products. The following is an example design of a product category table:

CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

In this example, we create a table named categories with two columns: id and name. The id column is used to uniquely identify each category, and the name column is used to store the name of the category.

2. Create product table

Next, we create a product table to store specific product information. Each product has a unique product ID, and a category ID associated with it. The following is an example of a product table design:

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    category_id INT,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

In this example, we create a table named products, containing four columns: id, name, price and category_id. The id column is used to uniquely identify each product, the name column is used to store the name of the product, the price column is used to store the price of the product, category_id The column is used to store the category ID to which the product belongs.

It should be noted that we added a foreign key constraint on the category_id column to ensure that the category_id value in the product table must be the category ID that exists in the classification table .

3. Insert sample data

In order to better understand the design of the table, we can insert some sample data into the category table and product table. The following is the SQL statement to insert sample data:

-- 插入分类数据
INSERT INTO categories (id, name) VALUES (1, '手机');
INSERT INTO categories (id, name) VALUES (2, '电视');
INSERT INTO categories (id, name) VALUES (3, '电脑');

-- 插入产品数据
INSERT INTO products (id, name, price, category_id) VALUES (1, 'iPhone 12', 6999.00, 1);
INSERT INTO products (id, name, price, category_id) VALUES (2, '小米电视', 2999.00, 2);
INSERT INTO products (id, name, price, category_id) VALUES (3, '华硕笔记本', 5999.00, 3);

Through the above sample data, we have inserted three pieces of classified data into the classification table, namely mobile phones, TVs and computers. Three pieces of product data are inserted into the product table, namely iPhone 12, Xiaomi TV and ASUS notebook, and are associated with the corresponding categories.

4. Query sample data

We can use SQL statements to query and verify whether the table design meets our expectations. The following are some commonly used query examples:

-- 查询所有分类
SELECT * FROM categories;

-- 查询所有产品
SELECT * FROM products;

-- 查询分类为手机的所有产品
SELECT * FROM products WHERE category_id = 1;

-- 查询产品价格小于5000元的所有产品
SELECT * FROM products WHERE price < 5000.00;

Through the above example query statements, we can obtain all data in the classification table and product table, and we can also filter out data that meets the requirements based on conditions.

5. Summary

Through the above practical combat, we learned how to create a product classification table and related table, and use SQL statements to insert and query data. Such table design can help us better organize and manage data, and improve the performance and maintainability of the database.

In actual projects, table design is a very important link and requires reasonable design based on actual needs and business logic. Through learning and practice, we can continuously improve our table design capabilities and make better contributions to database construction and development.

The above is the detailed content of MySQL table design practice: Create a product classification table and association table. For more information, please follow other related articles on the PHP Chinese website!