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.
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)
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.
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 –
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
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
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 –
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.
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.
- MySQL documentation on the
- JournalDev article on the