MySQL table design tutorial: Create a simple contract management table

MySQL table design tutorial: Create a simple contract management table

In daily business management, we often need to manage contracts, including contract creation, modification, deletion and query operations. In order to facilitate management and use, we can create a simple contract management table through the MySQL database. How to design and create this table is described in detail below.

First, we need to create a database, assuming our database is named "contract_management". Execute the following command in the MySQL command line or client tool:

CREATE DATABASE contract_management;

Then, we switch to the database:

USE contract_management;

Now, we start to create the contract management table. The table can contain the following fields:

• Contract number (contract_id): used to uniquely identify each contract.
• Contract name (contract_name): used to describe the contract name.
• Contract content (contract_content): used to describe the specific content of the contract.
• Contract start date (start_date): The start date of the contract.
• Contract end date (end_date): The end date of the contract.
• Contract creation time (create_time): The creation time of the contract.
• Contract update time (update_time): The last update time of the contract.

The following is the SQL statement to create the contract management table:

CREATE TABLE contracts (
  contract_id INT AUTO_INCREMENT PRIMARY KEY,
  contract_name VARCHAR(255) NOT NULL,
  contract_content TEXT NOT NULL,
  start_date DATE,
  end_date DATE,
  create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

In the above SQL statement, we use the AUTO_INCREMENT keyword to add an auto-increment attribute to the contract number field. In addition, we also use DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP to set the default values ​​and update rules for the create_time and update_time fields respectively.

Next, we can insert some test data to verify the correctness of the table. Execute the following SQL statement:

INSERT INTO contracts (contract_name, contract_content, start_date, end_date)
VALUES ('合同1', '这是合同1的内容', '2021-01-01', '2021-12-31'),
       ('合同2', '这是合同2的内容', '2021-02-15', '2022-02-14'),
       ('合同3', '这是合同3的内容', '2021-05-01', '2022-04-30');

The above SQL statement will insert three pieces of test data into the contract management table. You can insert more test data for testing and demonstration according to the actual situation.

Now, we can perform various operations on the contract management table, such as querying the information of all contracts, querying the details of a certain contract, modifying the content of the contract, etc. The following are some commonly used sample codes:

1. Query the information of all contracts:

SELECT * FROM contracts;

2. Query the detailed information of the contract name "Contract 1":

SELECT * FROM contracts WHERE contract_name = '合同1';

3. Modify the content of the contract named "Contract 2":

UPDATE contracts SET contract_content = '修改后的内容' WHERE contract_name = '合同2';

4. Delete the contract named "Contract 3":

DELETE FROM contracts WHERE contract_name = '合同3';

Through the above code examples, we can initially understand how to design and create a simple contract management table and perform common database operations. Based on actual needs, you can further expand the fields and functions of this table to meet specific business needs.

Summary:

Contract management is a common business need in many businesses and organizations. Through the table design and management of the MySQL database, we can easily create, modify, delete, and query contracts. In this article, we show you how to create a simple contract management table and provide some common examples of how to do it. I hope this article will be helpful to your learning and practice in contract management.

The above is the detailed content of MySQL table design tutorial: Create a simple contract management table. For more information, please follow other related articles on the PHP Chinese website!