Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "how to increase the value of fields in mysql". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

In mysql, you can use insert statements to increase the value of the field, syntax "INSERT INTO table name field name list VALUES (values list);" or "INSERT INTO table name SET field name 1 = value 1, field name 2 = value 2, …" .

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

In mysql, you can use the insert statement to increase the value of a field.

The INSERT statement can insert one or more rows of tuple data into an existing table in the database.

There are two grammatical forms of INSERT statements, which are INSERT. VALUES statement and INSERT... SET statement.

1) INSERT... VALUES statement

The syntax format of INSERT VALUES is: INSERT INTO [, … ]] VALUES (value 1) [… , (value n)]

The syntax is explained as follows.

Specifies the name of the table being manipulated

Specifies the name of the field in which the data needs to be inserted If you insert data into all the fields in the table, all field names can be omitted and directly use INSERTVALUES (…) That's it.

VALUES or VALUE clause: this clause contains the list of data to insert. The order of the data in the data list corresponds to the order of the columns.

2) INSERT... SET statement

The syntax format is:

INSERT INTO SET =, =,...

This statement is used to directly assign the corresponding column value to some columns in the table, that is, the column name of the data to be inserted is specified in the SET clause, col_name is the specified column name, the equal sign is followed by the specified data, and for unspecified columns, the column value is specified as the default value for that column.

You can see from the two forms of the INSERT statement:

Use INSERT... The VALUES statement can insert a row of data into a table or multiple rows of data

Use INSERT... The SET statement can specify the value of each column in the inserted row, or you can specify the value of some columns

INSERT... The SELECT statement inserts data from other tables into the table.

Adopt INSERT … The SET statement can insert values of some columns into the table, which is more flexible.

INSERT... VALUES statements can insert more than one piece of data at a time.

In MySQL, it is faster to handle multiple inserts with a single INSERT statement than with multiple INSERT statements.

When inserting multiple rows of data using a single INSERT statement, you only need to enclose each row in parentheses.

Add values to all fields in the table

There are two ways to insert values into all fields in a table: one is to specify all field names, and the other is to specify no field names at all.

[example 1] insert a new record in the tb_courses table with a course_id value of 1 and a Network value of "Network" and a course_grade value of 3 and a value of "Computer Network"

Before performing the insert operation, look at the SQL statement of the tb_courses table and the execution results are shown below.

Mysql > SELECT * FROM tb_courses;Empty set (0.00 sec)

The query results show that the content of the current table is empty and there is no data, and then the operation of inserting data is performed. The input SQL statement and execution process are shown below.

Mysql > INSERT INTO tb_courses-> (course_id,course_name,course_grade,course_info)-> VALUES; Query OK, 1 rows affected (0.08 sec) mysql > SELECT * FROM tb_courses +-+ | course_id | course_name | course_grade | course_info | +-+ -+ | 1 | Network | 3 | Computer Network | +-+ 1 row in set (0.00 sec)

You can see that the record was inserted successfully. When you insert data, all fields of the tb_courses table are specified, so a new value is inserted for each field.

The order of column names after the INSERT statement may not be the order in which the tb_courses table is defined, that is, when inserting data, you do not need to insert data in the order defined by the table, as long as you ensure that the order of the values is the same as the order of the column fields.

[example 2] insert a new record in the tb_courses table with a course_id value of 2, a course_grade value of 3, and an info value of "MySQL". The SQL statement entered and the execution result are shown below.

Mysql > INSERT INTO tb_courses-> (course_name,course_info,course_id,course_grade)-> VALUES ('Database','MySQL',2,3); Query OK, 1 rows affected (0.08 sec) mysql > SELECT * FROM tb_courses +-+ | course_id | course_name | course_grade | course_info | +-+ -+ | 1 | Network | 3 | Computer Network | | 2 | Database | 3 | MySQL | +-+ 2 rows in set (0.00 sec)

When inserting data using INSERT, the column name list column_list is allowed to be empty, where values need to be specified for each field in the table, and the order of values must be the same as when the fields in the data table were defined.

[example 3] insert a new record in the tb_courses table with a course_id value of 3 and a value of "Java" and a course_grade value of 4 and a value of "Jave EE". The SQL statement entered and the execution result are shown below.

Mysql > INSERT INTO tb_courses-> VLAUES; Query OK, 1 rows affected (0.08 sec) mysql > SELECT * FROM tb_courses +-+ | course_id | course_name | course_grade | course_info | +-+ -+ | 1 | Network | 3 | Computer Network | | 2 | Database | 3 | MySQL | | 3 | Java | 4 | Java EE | +- -+-+ 3 rows in set (0.00 sec)

There is no insert list specified in the INSERT statement, only a list of values. In this case, the values list specifies the inserted values for each field column, and the order of these values must be the same as the order defined by the fields in the tb_courses table.

Note: although the column name of the inserted data can be ignored when inserting data using INSERT, if the value does not contain a column name, the values after the VALUES keyword must not only be complete, but must also be in the same order as the columns when the table was defined. If the structure of the table is modified, columns are added, deleted, or relocated, which will change the order in which data is inserted in this way. If you specify a column name, it is not affected by changes in the table structure.

Add a value to the specified field in the table

Inserting data for the specified field of the table inserts values into only some fields in the INSERT statement, while the values of other fields are the default values when the table is defined.

[example 4] insert a new record in the tb_courses table with a value of "System" for course_name and a value of "Operating System" for course_grade. The SQL statement entered and the execution result are shown below.

Mysql > INSERT INTO tb_courses-> (course_name,course_grade,course_info)-> VALUES ('System',3,'Operation System'); Query OK, 1 rows affected (0.08 sec) mysql > SELECT * FROM tb_courses +-+ | course_id | course_name | course_grade | course_info | +-+ -+ | 1 | Network | 3 | Computer Network | | 2 | Database | 3 | MySQL | | 3 | Java | 4 | Java EE | | 4 | System | 3 | Operating System | +-+- -+ 4 rows in set (0.00 sec)

You can see that the record was inserted successfully. As the query results show, the course_id field here automatically adds an integer value of 4. At this time, the course_id field is the primary key of the table and cannot be empty, and the system automatically inserts a self-increasing sequence value for the field. When inserting a record, if some field does not specify an insert value, MySQL inserts the default value when the field was defined.

This is the end of the content of "how to increase the value of a field by mysql". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.