With MySQL we can perform all the CRUD operations and some other major commands that are necessary for building an interactive application. Data insertion is one of the most used operations in any DBMS (Database Management System). So, in this article, we are going to learn some of the different ways to insert data into a table using the INSERT statement in MySQL.
INSERT statement is used to insert data in rows of a table.
Syntax
The syntax for inserting data into a table is:
In this syntax:
First, mention the table_name (in which you want to insert data) along with the column names in parentheses (column_name_1, column_name_2, …) (columns of the table), followed by the INSERT INTO clause.
After mentioning the table name and column names in parentheses, you need to provide the values after the VALUES clause like this:
(value_1, value_2, …); these are values or data you want to insert corresponding to the columns.
You can also provide or add multiple rows in a single query by separating them with a comma.
Let’s try some examples of data insertion into a table in MySQL and have a better understanding of the INSERT command.
Examples
Before getting started learning the data insertion. Let’s first create a table and set some different data types of the columns so that we can insert different types of data. The query for creating a table would be like this:
In this query, we have created a table with the name of cars, which includes the following columns:
An integer type car_id column with the constraint of AUTO_INCREMENT (which means that during data insertion, even if we don’t provide any value, it will automatically increment the value and add that value in this column).
A name column with the data type of VARCHAR, which includes the name of the car, and set the constraint so it can’t be NULL.
A manufacturing_date column will have the date of when the car was manufactured.
An engine column will have the engine type. For example, Gasoline, Diesel, or Hybrid. We have set the constraints that forbid this value to be null, and if it is not provided while inserting a new row, then it sets the default value to ‘Gasoline’.
A description column that includes the description of the car.
And in the end, we have created a primary key on the car_id column.
After creating a table successfully, let’s move towards the data Insertion.
INSERT Command
In the INSERT command, it is not necessary to insert data into all the columns. We can just insert data into some specific columns until we are fulfilling the requirements that we have during the creation of the table. So, let’s first try to enter the car name and its engine type only. The query for inserting the data would be like this:
After successfully adding one row to the table. The table should be updated.
As you can see, we have not added any car_id, but because of the AUTO INCREMENT constraint, the car_id is automatically inserted, along with the other two fields
Well, we can also provide the DEFAULT keyword while inserting data. When we provide the DEFAULT keyword while inserting data, the DEFAULT value that will be assigned is what we have set during the creation of the table. For example:
Now, let’s take a look at the table again.
The default value ‘Gasoline’ is assigned. That’s great!
Alright, now, let’s learn about the format of inserting the date into MySQL’s Table.
Insert Date into a table
To insert a date into MySQL, we need to follow the following syntax:
‘YYYY-MM-DD’
Year, Month, and Date are separated by dashes. For Example:
Or if you want to insert the current date. We can use MySQL’s built-in functions like CURRENT_DATE() or NOW(). You can get today’s date by using any of these functions. For example:
Similarly, the NOW() function would do the same for us:
Now, let’s see the current status of the table.
It can be observed that today’s date is inserted successfully by both of the functions.
Alright, now, let’s try to insert more than one row in a single INSERT statement.
Inserting Multiple Values
To insert multiple values, we can provide them in the parentheses separated by a comma followed by the VALUES clause. For example:
In this single query, we have added three different car models of AUDI in three different rows of the ‘cars’ table. The table should contain three added rows.
As you can see, all of the three rows are inserted as we desired.
So, these are some of the different syntaxes and ways to insert data into a table.
Wrapping Up
In this article, we have learned different syntaxes to insert different types of data into the table. We have also learned to use the CURRENT_DATE() function, NOW() function, and DEFAULT keyword to understand the different syntaxes for adding or inserting data into a table.
