Insert Query To Insert Rows in MySQL

This tutorial is part of the series Learn Basic SQL Queries Using MySQL. In this tutorial, we will discuss SQL queries to insert rows or store data in a table in MySQL.

Insert Into Query

The query INSERT INTO can be used to insert rows in a table. In case you are remotely logged in to the database, you will also need the INSERT privilege for the table in order to insert rows in a table.

# INSERT INTO - Syntax
INSERT INTO `table_name`(column_1,column_2,...) VALUES (value_1,value_2,...);

Query Explanation

The MySQL command INSERT INTO can be used to insert rows in the given table name where the name of the table is mandatory.

We can specify the column names in the comma-separated format if the row data is specified only for selected columns. In case the row data is provided for all the table columns in the column order, we can omit to specify the columns.

We can either provide the row data of all the columns or selected columns as specified above. We must take care of the data type while inserting the row data. The commonly used data types are listed below:

String - We must enclose all the string values in single quotes. We can also use NULL, in case the column definition allows.

Numeric - Numeric values should not be enclosed in quotes. The numeric values must be provided directly based on the column data type.

Date - The date values must be enclosed in single quotes following the MySQL format i.e. 'YYYY-MM-DD'. We can use all zeros as the default value or NULL in case the column definition allows null values.

Datetime - Similar to data values, the datetime value must be enclosed in single quotes following the MySQL format i.e. 'YYYY-MM-DD hh:mm:ss'.

You can also follow MySQL Data Types Cheatsheet to know more about the available data types in MySQL.

Examples

This section provides examples of the insert query to insert row data in a table. Use the below-mentioned query to create the user table having the id, first name, and last name columns to store user data.

# Create the User Table
CREATE TABLE `user` (
  `user_id` bigint(20) NOT NULL,
  `first_name` varchar(45),
  `last_name` varchar(45)
);

The below-mentioned queries can be used to insert data in the user table using the column names.

# Insert Single Row - All Columns
INSERT INTO `user` ( `user_id`, `first_name`, `last_name`) VALUES ( 1, 'John', 'Smith' );

# Insert Multiple Rows - All Columns
INSERT INTO `user` ( `user_id`, `first_name`, `last_name`) VALUES 
    ( 2, 'Rick', 'Jones' ),
    ( 3, 'Catherine', 'Ponting' ),
    ( 4, 'Harsh', 'Upadhyay' ),
    ( 5, 'Tajwinder', 'Singh' );

# Insert Single Row - Selected Columns - NULL for last_name
INSERT INTO `user` ( `user_id`, `first_name`) VALUES ( 6, 'Leo' );

These queries will insert 6 rows in the table having the id, first name and last name to represent 6 different users.

We can also omit the column names in case we provide the data of all the columns as shown below. We must take care of the order of the columns while inserting rows without specifying the columns.

# Insert Single Row - All Columns
INSERT INTO `user` VALUES ( 7, 'Leo', 'Murphy' );

# Insert Multiple Rows - All Columns
INSERT INTO `user` VALUES 
    ( 8, 'Ram', 'Choudhary' ),
    ( 9, 'Nicole', NULL );

We can also change the column order while inserting row values. The only thing to be taken care is that the row data must also follow the same order as shown below.

# Insert Single Row - All Columns
INSERT INTO `user` ( `first_name`, `last_name`, `user_id` ) VALUES ( 'Bill', 'Waugh', 10 );

This is how we can use the INSERT INTO command to insert data in a table.