MySQL INSERT INTO Statement – How to insert values into a table in MySQL?

By /
Insert Statement

In this tutorial, we will explore the MySQL INSERT INTO statement. Suppose you have a table ready with columns and now you wish to put in values in it. To put values in a table, MySQL provides you with the INSERT statement. It is important to note that the CREATE TABLE statement enables you to create columns while the INSERT statement enables you to insert rows or records into a table.

The INSERT statement is an example of a Data Manipulation Language (DML). As the name suggests, Data Manipulation Languages deal with the manipulation of data.

Syntax of the MySQL INSERT INTO Statement

INSERT INTO table_name(
column1, column2, column3,....... columnN) 
VALUES
(value_for_column1, value_for_column2, value_for_column3,... value_for_columnN),
(value_for_column1, value_for_column2, value_for_column3,... value_for_columnN),
…;Code language: SQL (Structured Query Language) (sql)

Note that, you should mention column names only if you are inserting values in those specific columns. If your INSERT statement includes values for all columns in a table then you may not mention columns.

Examples of the MySQL INSERT INTO Statement

Let us consider the ‘Students’ table we created in the CREATE TABLE statement tutorial. We will use the DESCRIBE query to get an idea of what the table structure is like.

DESC Students;Code language: SQL (Structured Query Language) (sql)
MySQL Describe Students Table

As you can see, we get information about every column in the table, including the data types and associated constraints.

1. Inserting A Single Column In A Table

Let us start by filling up our empty table.

Arun Dixit is a student who scored 75, 64, and 88 in English, Maths, and Science respectively. His date of birth is 12th March 2006. His ID is 1.

Let us add the above as our first record in the Students table. We do so using the following query,

INSERT INTO Students VALUES(1, 'Arun Dixit', '2006-03-12', 75, 64, 88);Code language: SQL (Structured Query Language) (sql)

And now let us use the SELECT statement to display our table.

Insert Single Row

And there you go, we’ve inserted our first record into the table.

2. Inserting Multiple Columns In A Table

Let us now check how to use the MySQL insert into statement to insert multiple columns in a table. Let us insert the records of the following two girls.

  • Devika Kulkarni scored 73, 95, and 56 in English, Math, and Science respectively. Her date of birth is 30 December 2005. Her ID is 2.
  • Drishti Chauhan scored 66, 72, and 70 in English, Math, and Science respectively. Her date of birth is 2nd January 2006. Her ID is 3.

We will insert the above entries in our table using the query,

INSERT INTO Students VALUES(2, 'Devika Kulkarni', '2005-12-30', 73, 95, 56), (3, 'Drishti Chauhan', '2006-01-02', 66, 72, 70);Code language: SQL (Structured Query Language) (sql)

And we will use the SELECT statement to display our table with the newly added entries –

Insert Multiple Rows

3. Inserting Values Only In Specific Columns

Now, if you refer to the table structure we got using the DESCRIBE command, we see that ID, Name, and DOB cannot have NULL values. However, the marks of the individual subjects may contain NULL values.

It may so happen that a student was unwell during that exam and scheduled to give the test at a later date. What we can also see in the table structure is that the ID column has the AUTO_INCREMENT constraint.

That means even if we do not specify the ID column’s value for a record, it will automatically increment the ID value of the preceding record and make that the ID value for the new record.

Jay Bhatnagar did not appear for the Math test due to being unwell. However, he did appear for the English and Science exams and scored 93 and 84 in them respectively. His date of birth is 11th May 2005. Let us insert his record into the table using the following query,

INSERT INTO Students(Name, DOB, English, Science) VALUES('Jay Bhatnagar', '2005-05-11', 93, 84);Code language: SQL (Structured Query Language) (sql)

And we will use the SELECT statement to display our table with the newly added entry –

Insert In Specific Columns

As you can see, even though we did not specify the value of ID, it auto-incremented itself. Since we did not specify the marks in Maths, it has a NULL value. It is important to note here that since the Maths column did not have a NOT NULL constraint, this was possible. However, if we had skipped the value for the Name or DOB column, we would have gotten an error.

Conclusion

The MySQL INSERT INTO statement is one of the fundamental statements of MySQL. Inserting data into a table is very important as only after that you can proceed with other operations.

References