MySQL tutorial 83-INSERT insert data (add data)

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

Basic grammar

The INSERT statement has two syntax forms: INSERT... VALUES statement and INSERT... SET statement.

1) INSERT... VALUES statement

The syntax format of INSERT VALUES is:

INSERT INTO <Table name> [(<Column name 1> [ , ... <Listing n>] )] VALUES (Value 1) [... , (value n) ];

The syntax is as follows.

  • < table name >: Specifies the table name to be operated on.
  • < column name >: specify the column name to insert data. If you insert data into all columns in the table, all column names can be omitted, and you can directly use insert < table name > values (...).
  • VALUES # or # VALUE # clause: this clause contains a list of data to insert. The order of data in the data list should correspond to the order of columns.

2) INSERT... SET statement

The syntax format is:

INSERT INTO <Table name>
SET <Column name 1> = <Value 1>,
        <Column name 2> = <Value 2>,
        ...

This statement is used to directly specify the corresponding column value for 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, followed by the specified data. For unspecified columns, the column value will be specified as the default value of the column.

It can be seen from the two forms of INSERT statement:

  • Use the INSERT... VALUES statement to INSERT a row of data or multiple rows of data into the table;
  • Use the INSERT... SET statement to specify the value of each column in the inserted row or the value of some columns;
  • INSERT... SELECT statement inserts data from another table into a table.
  • The INSERT... SET statement can be used to INSERT the values of some columns into the table, which is more flexible;
  • The INSERT... VALUES statement can INSERT multiple pieces of data at a time.

In MySQL, processing multiple inserts with a single INSERT statement is faster than using multiple INSERT statements.

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

Add values to all fields in the table

In test_ Create a course information table tb in DB database_ Courses, including course number_ ID, Course Name course_name, course credits_ Grade and course notes course_info, the input SQL statement and execution results are as follows.

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(100) NULL,
    -> PRIMARY KEY(course_id)
    -> );
Query OK, 0 rows affected (0.00 sec)

There are two methods to insert values into all fields in the table: one is to specify all field names; The other is not to specify the field name at all.

[example 1] in TB_ Insert a new record into the courses table, course_id value is 1, course_name value is "Network", course_ The grade value is 3 and the info value is "Computer Network".

Before performing the insert operation, view TB_ The SQL statement and execution results of the courses table are shown below.

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

The query result shows that the content of the current table is empty and there is no data. Next, execute the operation of inserting data. The entered SQL statement and execution process are as follows.

mysql> INSERT INTO tb_courses
    -> (course_id,course_name,course_grade,course_info)
    -> VALUES(1,'Network',3,'Computer Network');
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 is inserted successfully. TB was specified when inserting data_ All fields of the courses table, so a new value will be inserted for each field.

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

[example 2] in TB_ Insert a new record into the courses table, course_id value is 2, course_name value is "Database", course_ The grade value is 3 and the info value is "MySQL". The input SQL statement and execution results are as follows.

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 with INSERT, the column name list column is allowed_ List is empty. At this time, each field of the table needs to be specified with values in the value list, and the order of values must be the same as that when defining fields in the data table.

[example 3] in TB_ Insert a new record into the courses table, course_id value is 3, course_name value is“ Java ”´╝îcourse_ The grade value is 4 and the info value is "Jave EE". The input SQL statement and execution results are as follows.

mysql> INSERT INTO tb_courses
    -> VLAUES(3,'Java',4,'Java EE');
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. There is only one value list. In this case, the value list specifies the inserted values for each field column, and the order of these values must be the same as TB_ The fields in the courses table are defined in the same order.

Note: although used INSERT When inserting data, you can ignore the column name of the inserted data. If the value does not contain the column name, then VALUES The values after the keyword must not only be complete, but also be in the same order as the columns when the table is defined. If the structure of the table is modified and the columns are added, deleted or position changed, these operations will change the order of inserting data in this way at the same time. If you specify a column name, it will not be affected by the change of table structure.

Adds a value to the specified field in the table

Inserting data into the specified field of the table is to INSERT 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] in TB_ Insert a new record into the courses table, course_name value is "System", course_grade value is 3, course_ The info value is "Operating System". The entered SQL statement and execution results are as follows.

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 is inserted successfully. As the query result shows, the course here_ The ID field automatically adds an integer value 4. Course at this time_ The ID field is the primary key of the table and cannot be empty. The system automatically inserts the self incremented sequence value for this field. When inserting a record, if some fields do not specify an insertion value, MySQL will insert the default value when the field is defined.

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

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

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

Test in database_ Create a with TB in DB_ Courses table data table TB with the same structure_ courses_ New, the SQL statement and execution process of 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(100) 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] from TB_ Query all records in the courses table and insert them into tb_courses_new table. The input SQL statement and execution results are as follows.

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: 0
mysql> 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)

Tags: MySQL

Posted by fothers on Sat, 21 May 2022 16:49:24 +0300