Quick Reach
The insert into MySQL statement
After creating tables in MySQL database, that we learned in the create table chapter, this chapter explains how to insert data by using the insert into statement of MySQL.
The insert statement is used to enter data in MySQL tables. How to use it with examples is explained below.
How to use the insert statement?
The general syntax of MySQL insert into statement to insert data in a table is:
Insert into table_name (
Col1,
Col2,
Col3,
Col4
…..
…..
Colx)
Values (
Col1_value,
Col2_value,
Col3_value,
Col4_value,
…..
…..
Colx_value )
);
Let us look at the line by line of the above syntax. First of all, the insert into MySQL statement is used which is followed by the table name where you want to insert data.
After this, the column names of the table that are enclosed in parenthesis are given. Each column/field name is separated by a comma. You can place all column names in a single line or multiple lines.
After specifying the column names, the value keyword is used that is followed by values corresponding to each column specified in above line.
Note that, the values are also enclosed in parenthesis and separated by commas. An important thing is the values sequence must be according to the sequence of column names.
This is just one way of using the insert into command. Alternatively, you can exclude column names part and use Values keyword after “insert into table_name” as shown below:
Insert into table_name Values (
Col1_value,
Col2_value,
Col3_value,
Col4_value,
…..
…..
Colx_value )
);
If you are using this method of entering data into the table(s), you have to ensure that sequence of column values must be according to the column name sequence in the physical table. Otherwise, you may encounter errors or end up at confusion.
Let us look at a few examples of entering data by using both ways.
An insert into statement example
For our examples to explain MySQL insert into statement we are using the same table that was used in create table chapter i.e. a tbl_employee table with five columns (emp_id, emp_name, emp_age, emp_salary and joining_date). Now we will insert rows into tbl_employee by using the insert into statements by specifying the column names first.
Example of Insert into MySQL statement with column names
1
2
3
|
insert into tbl_employee (emp_id, emp_name, emp_age, emp_salary, joining_date)
Values (001, ‘Mike’, 35, 5000.50, ‘2008/01/01’)
|
As mentioned earlier, if you are using column names in insert into query the values must be according to the order of column names. However, the column sequence can be different to the column names in table physically.
See another example below:
1
2
3
|
insert into tbl_employee (emp_id, joining_date, emp_age, emp_salary, emp_name)
Values (002, ‘2008/05/01’, 30, 4500.50, ‘Michale’)
|
As you can see, the order of columns is changed in the above query where joining_date appears before emp_name while the order of values is also according to column names. However, in actual table tbl_employee, the order of column is the same.
Example of insert into statement without column names
In the following example, we will insert a row without specifying the column names. As mentioned earlier, the order of values must correspond to the column names in the table to use insert statement.
1
|
insert into tbl_employee Values (003, ‘Jimmy’, 27, 3000.00, ‘2009/05/03’)
|
Insert multiple rows example
The following example shows how to insert multiple rows of data by using MySQL insert into statement.
1
2
3
4
5
6
7
|
insert into tbl_employee Values
(004, ‘Jimmy’, 27, 3000.00, ‘2009/05/03’),
(005, ‘Jimmy’, 27, 3000.00, ‘2009/05/03’),
(006, ‘Jimmy’, 27, 3000.00, ‘2009/05/03’)
|
You have to separate each row by a comma.
Further Reading: MySQL create database | Create table in MySQL
Leave A Comment?