How to use MySQL to create a user points record table for the grocery shopping system

The vegetable shopping system is a very practical application. It can easily provide users with fresh vegetables, fruits and other ingredients. In order to better serve users, user points need to be established Record form to track users’ consumption behavior and usage habits. This article will introduce how to use MySQL to create a user points record table for the grocery shopping system, and provide specific code examples.

1. Design of user points record table

The user points record table is a table used to record changes in user points. As users use the grocery shopping system to shop and consume, user points also increase. Corresponding changes will occur. Therefore, the following factors need to be considered when establishing a user points record table:

1. User information
2. Points change type (consumption, refund, activity, etc.)
3. Points change amount
4. Change time

Based on the above considerations, we can design the following user points record table:

##idint(11)Auto-increment primary keyuser_idint(11)userIDchange_typevarchar(20 )Points change type (consumption, refund, activity, etc.)change_amountint(11)Change amount change_timedatetimeChange time (format: YYYY-MM-DD HH:MM:SS)

Field name	Data type	Description

2. Specific code for creating user points record table

Creating a table in MySQL is very simple, just use the CREATE TABLE statement. The following is a sample code for creating a user points record table:

CREATE TABLE user_points (
  id int(11) NOT NULL AUTO_INCREMENT,
  user_id int(11) NOT NULL COMMENT '用户ID',
  change_type varchar(20) NOT NULL COMMENT '积分变化类型（消费、退款、活动等）',
  change_amount int(11) NOT NULL COMMENT '积分变化额度',
  change_time datetime NOT NULL COMMENT '变化时间（格式：YYYY-MM-DD HH:MM:SS）',
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户积分记录表';

In the above code, we used the CREATE TABLE statement to create a table named "user_points", which contains 4 fields. They are: id (auto-increment primary key), user_id (user ID), change_type (point change type), change_amount (point change amount) and change_time (change time). When creating, we also set the table to use the InnoDB storage engine and specified the encoding to be utf8mb4.

3. Add data to the user points record table

After creating the user points record table, we need to start entering data. The following is some sample data that can be used to test the correctness of the table:

INSERT INTO user_points (user_id, change_type, change_amount, change_time) 
VALUES 
(10001, '消费', -100, '2021-06-01 12:00:00'),
(10001, '消费', -50, '2021-06-03 18:00:00'),
(10001, '活动', 200, '2021-06-05 09:00:00'),
(10002, '消费', -80, '2021-06-02 15:00:00'),
(10002, '消费', -120, '2021-06-04 20:00:00'),
(10002, '活动', 150, '2021-06-06 13:00:00');

In the above code, we used the INSERT INTO statement to insert 6 pieces of data into the "user_points" table, so that It's time to start testing the correctness of the table.

4. Query the user points record table

Finally, we need to query the user points record table to verify the correctness of the table. The following are some common query statement examples:

1. Query the total points of a user:

SELECT SUM(change_amount) AS point_total FROM user_points WHERE user_id=10001;

In this statement, we use the SELECT SUM() statement to calculate the ID of The total points of 10001 users, and the result is returned with the alias "point_total".

2. Query the last 5 points change records of a user:

SELECT * FROM user_points WHERE user_id=10001 ORDER BY change_time DESC LIMIT 5;

In this statement, we use the SELECT * statement to query the last 5 changes of the user with ID 10001 Integral changes are recorded and sorted in reverse order of change time.

3. Query the score change records of all users within a certain period of time:

SELECT * FROM user_points WHERE change_time BETWEEN '2021-06-01 00:00:00' AND '2021-06-30 23:59:59';

In this statement, we used the SELECT * statement to query the points change records between June 1, 2021 and All points changes that occurred between June 30 are recorded.

To sum up, this article introduces how to use MySQL to create a user points record table for the grocery shopping system, and provides specific code examples. These codes can be used to test the correctness of the table and check the accuracy of the data through query statements, thereby completely establishing a complete user points recording system.

The above is the detailed content of How to use MySQL to create a user points record table for the grocery shopping system. For more information, please follow other related articles on the PHP Chinese website!