MySQL INSERT Statement

MySQL INSERT statement is used to store or add data in MySQL table within the database. We can perform insertion of records in two ways using a single query in MySQL:

1. Insert record in a single row
2. Insert record in multiple rows

Syntax:

The below is generic syntax of SQL INSERT INTO command to insert a single record in MySQL table:

INSERT INTO table_name ( field1, field2,...fieldN )    

VALUES    

( value1, value2,...valueN );

In the above syntax, we first have to specify the table name and list of comma-separated columns. Second, we provide the list of values corresponding to columns name after the VALUES clause.

NOTE: Field name is optional. If we want to specify partial values, the field name is mandatory. It also ensures that the column name and values should be the same. Also, the position of columns and corresponding values must be the same.

If we want to insert multiple records within a single command, use the following statement:

INSERT INTO table_name VALUES  

( value1, value2,...valueN )  

( value1, value2,...valueN )  

...........  

( value1, value2,...valueN )

   

In the above syntax, all rows should be separated by commas in the value fields.

MySQL INSERT Example

Let us understand how INSERT statements work in MySQL with the help of multiple examples. First, create a table “People” in the database using the following command:

CREATE TABLE People(  

    id int NOT NULL AUTO_INCREMENT,  

    name varchar(45) NOT NULL,  

    occupation varchar(35) NOT NULL,  

    age int,  

    PRIMARY KEY (id)  

);

1. If we want to store single records for all fields, use the syntax as follows:

INSERT INTO People (id, name, occupation, age)   

VALUES (101, 'Peter', 'Engineer', 32);

 

2. If we want to store multiple records, use the following statements where we can either specify all field names or don’t specify any field.

INSERT INTO People VALUES  

(102, 'Joseph', 'Developer', 30),  

(103, 'Mike', 'Leader', 28),  

(104, 'Stephen', 'Scientist', 45);

3. If we want to store records without giving all fields, we use the following partial field statements. In such case, it is mandatory to specify field names.

INSERT INTO People (name, occupation)   

VALUES ('Stephen', 'Scientist'), ('Bob', 'Actor');

In the below output, we can see that all INSERT statements have successfully executed and stored the value in a table correctly.

We can use the below syntax to show the records of the People table:

mysql> SELECT * FROM People; 

We will get the output as follows:

Inserting Date in MySQL Table:

We can also use the INSERT STATEMENT to add the date in MySQL table. MySQL provides several data types for storing dates such as DATE, TIMESTAMP, DATETIME, and YEAR. The default format of the date in MySQL is YYYY-MM-DD.

This format has the below descriptions:

• YYYY: It represents the four-digit year, like 2020.
• MM: It represents the two-digit month, like 01, 02, 03, and 12.
• DD: It represents the two-digit day, like 01, 02, 03, and 31.

Following is the basic syntax to insert date in MySQL table:

INSERTINTO table_name VALUES (STR_TO_DATE(date_value, format_specifier)); 

If we want to insert a date in the mm/dd/yyyy format, it is required to use the below statement:

INSERT INTO table_name VALUES (STR_TO_DATE(date_value, format_specifier));