Shulou(Shulou.com)06/01 Report--

This article mainly introduces the method of adding value to mysql, which has certain reference value and can be used for reference by friends who need it. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

The way to add value to mysql: using the "INSERT INTO" statement, you can insert one or more rows of data into an existing table in the database; the syntax format is "INSERT INTO table name [column name 1 [, … column name n]] VALUES (value 1) […, (value n)];".

After the database and table are created successfully, you need to insert data into the table in the database. In MySQL, you can use the INSERT INTO statement to insert one or more rows of tuple data into an existing table in the database.

Basic grammar

The syntax format of INSERT INTO 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 column in which the data needs to be inserted If you insert data into all the columns in the table, all column 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.

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.

Thank you for reading this article carefully. I hope the method of adding value to mysql shared by the editor will be helpful to everyone. At the same time, I also hope that you will support us, pay attention to the industry information channel, and find out if you encounter problems. Detailed solutions are waiting for you to learn!

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.