MySQL inserts data for a specified field in a data table

Username NOT NULL no default value/default insert does not insert username field no error

July 23, 2014 21:05 Encyclopedia 369

MySQL inserts data for a specified field in a data table

Inserts data for the specified field in the data table, that is, inserting values into only part of a field in the INSERT statement, while the values of the other fields are the default values when the data table is defined.

The basic syntax format is as follows:

INSERT into table name (Field 1, Field 2,..., field m) VALUES (value 1, value 2,..., value m);

• Field 1, Field 2,..., field M: Represents the field name in the data table, specifying the name of a part of the field in the data table.
• A value of 1, a value of 2,..., a value of M: represents the value of the specified field, and each value corresponds to the corresponding field.

Example 1

1. Insert data into the Xuesheng field and the Yuwen field of the student table. The code for the INSERT statement is as follows:

Mysql>insert into student (Xuesheng,yuwen) VALUES (' Emily Wang ', 88);

The effect of inserting data is as shown in the following:

The results of the code execution show that the record was inserted successfully.

2. Execute the SELECT statement to query the student table. As shown in the following:

3. The result of the code execution shows that the record has been inserted successfully. However, both the Shuxue field and the Yungyu field are null values (NULL). Execute the Show CREATE table statement to view the detailed structure of the student table. As shown in the following:

The result of the query from code execution shows that the default value for the Shuxue field and the Yingyu field is null (NULL), because the two fields do not have a value inserted, and the database system automatically inserts the default value for the field definition, so the values for both fields are null. If the default value is set to a specific value, the values of the two fields are displayed as that specific value.

In addition, although we did not insert the value of the ID field, the field automatically added an integer value of 6. This is because the ID field is set to the primary key of the table, although it cannot be empty, but the database system automatically inserts a self-increment sequence value for the field.

Tips

The database system inserts a default value for a field that is not assigned a value. This default value is defined when the data table is created. As the default value for the Shuxue field and the Yingyu field above is null. If a field does not have a default value set and is non-null, it must be assigned a value. Otherwise, the database system will prompt a warning message.

Example 2

This way you can also set the order of the fields arbitrarily, without having to insert the data in the order in which the data table is defined.

1. Insert data into the ID, Xuesheng, and Yingyu fields in the student table. Insert statement, the order of the 3 fields can be arranged arbitrarily, the code is as follows:

Mysql>insert into student (Yingyu,id,xuesheng) VALUES (99,4, ' Yang Ping ');

The operation effect is as follows:

The results of the code execution show that the record was inserted successfully.

2. Execute the SELECT statement to query the student table. As shown in the following:

The results of the code execution show that the record was successfully inserted.

MySQL inserts data for a specified field in a data table