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

How to use MySQL to build a user table for a grocery shopping system

The user table is an important part of any grocery shopping system. It is used to store the user’s basic information and Login credentials. In this article, we will introduce how to use a MySQL database to build a simple but practical user table and provide specific code examples.

1. Create database and user table

First, we need to create a database to store the user table. In MySQL, you can use the following statement to create a database named "grocery_system":

CREATE DATABASE grocery_system;

Next, we can use the following statement to create a database named "users" User table:

USE grocery_system;

CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255) NOT NULL,
password VARCHAR(255 ) NOT NULL,
email VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
last_login TIMESTAMP
);

2. Set the fields of the user table

The user table includes the following fields:

• id: user ID, primary key, automatically incremented.
• username: Username, cannot be repeated.
• password: Login password, which should usually be stored encrypted.
• email: User email, used to retrieve passwords and send shopping information.
• created_at: User creation time, the default value is the current time.
• last_login: The user’s last login time, the initial value is empty.

3. Insert and modify user information

Next, we can use the INSERT statement to insert new user information into the user table. Here is an example:

INSERT INTO users (username, password, email) VALUES ('user1', 'password1', 'user1@example.com');

This will send the user Insert a record into the table, containing the new user information with the user name "user1", password "password1", and email address "user1@example.com".

If you need to modify user information, you can use the UPDATE statement. For example, to modify the email address of user "user1" to "user1_updated@example.com", you can use the following statement:

UPDATE users SET email = 'user1_updated@example.com' WHERE username = 'user1';

4. Query user information

In the grocery shopping system, we usually need to find user information based on the user name or email. The following are some sample query statements:

-- Query user information based on username
SELECT * FROM users WHERE username = 'user1';

-- Query user information based on email address
SELECT * FROM users WHERE email = 'user1@example.com';

-- Query all user information
SELECT * FROM users;

5. Delete user information

If you need to delete user information, you can use the DELETE statement. For example, to delete user information with the user name "user1", you can use the following statement:

DELETE FROM users WHERE username = 'user1';

The above is a simple but practical user table Examples of creation and usage. Of course, in an actual grocery shopping system, the user table may include more fields and functions, such as user address, mobile phone number, etc. This is just a basic starting point, you can expand and optimize according to actual needs.

I hope this article can help you understand how to use MySQL to build a user table for the grocery shopping system.

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