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

This article mainly introduces the method of inserting multiple data into mysql, which is very detailed and has certain reference value. Friends who are interested must finish reading it.

Mysql inserts multiple pieces of data: 1. Use "INSERT INTO table name field list VALUES (value 1), (value n);" statement insert data; 2, use "INSERT INTO table name SET field 1 = value 1, field 2 = value 2, …" Statement to insert data.

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 statement to insert one or more rows of tuple data into an existing table in the database.

Basic grammar

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 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.

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

Create a course information table tb_courses in the test_db database, including course number course_id, course name course_name, course credits course_grade, and course Notes course_info. The SQL statements entered and the results of the execution are shown below.

Mysql > CREATE TABLE tb_courses-> (- > course_id INT NOT NULL AUTO_INCREMENT,-> course_name CHAR (40) NOT NULL,-> course_grade FLOAT NOT NULL,-> course_info CHAR (100th) NULL,-> PRIMARY KEY (course_id)->); Query OK, 0 rows affected (0.00 sec)

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.

Use INSERT INTO... FROM statement to copy table data

INSERT INTO... SELECT... The FROM statement is used to quickly pull data from one or more tables and insert that data into another table as row data.

The SELECT clause returns a result set of a query, and the INSERT statement inserts the result set into the specified table. The number of fields in each row of data in the result set and the data type of the fields must be exactly the same as the table being manipulated.

Create a data table tb_courses_new with the same structure as the tb_courses table in the database test_db. The SQL statement and execution process for creating the table are as follows.

Mysql > CREATE TABLE tb_courses_new-> (- > course_id INT NOT NULL AUTO_INCREMENT,-> course_name CHAR (40) NOT NULL,-> course_grade FLOAT NOT NULL,-> course_info CHAR (100th) NULL,-> PRIMARY KEY (course_id)->); Query OK, 0 rows affected (0.00 sec) mysql > SELECT * FROM tb_courses_new;Empty set (0.00 sec)

[example 5] query all records from the tb_courses table and insert them into the tb_courses_new table. The SQL statement entered and the execution result are shown below.

Mysql > INSERT INTO tb_courses_new-> (course_id,course_name,course_grade,course_info)-> SELECT course_id,course_name,course_grade,course_info-> FROM tb_courses;Query OK, 4 rows affected (0.17 sec) Records: 4 Duplicates: 0 Warnings: 0mysql > SELECT * FROM tb_courses_new +-+ | 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) is all the contents of mysql's method of inserting multiple pieces of data. Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.