MySQL
In this tutorial we will learn to insert data into tables in MySQL.
We will be using the employee
and comments
table that we created in the CREATE Table tutorial.
We use the following syntax to insert data into a table.
INSERT INTO table_name VALUES('val1', 'val2', ...);
We can also add the database name like the one given below.
INSERT INTO database_name.table_name VALUES('val1', 'val2', ...);
Where, table_name
is the name of the table in which we want to insert the data.
'val1', 'val2', ...
are the values for the columns of the table.
We can also specify the column names in the insert query as shown below.
INSERT INTO table_name(column1, column2, ...) VALUES ('val1', 'val2', ...);
Where, table_name
is the name of the table and value val1
is inserted into column1
column and so on.
We use the following syntax to insert multiple rows in the table.
INSERT INTO table_name(column1, column2, ...)
VALUES
('val1', 'val2', ...),
('val11', 'val22', ...);
When inserting data into the table keep the following points in mind.
In the following example we are inserting the detail of an employee.
We are inserting values for the given columns: employeeid, firstname, lastname, email, birthday, lastmodified and created.
Since, value for the score column is skipped so, it will get the default value set for it and i.e., 0.
INSERT INTO `employee`
(`employeeid`, `firstname`, `lastname`, `email`, `birthday`, `lastmodified`, `created`)
VALUES
('e01', 'Yusuf', 'Shakeel', 'yusuf@example.com', '1900-01-01', '2018-01-01 01:01:01', '2018-01-01 01:01:01');
And we get the following response:Query OK, 1 row affected (0.00 sec)
We can check the result using the SELECT
query.
We will learn about SELECT query in the later part of this tutorial series.
mysql> SELECT * FROM employee;
+------------+-----------+----------+-------------------+-------+------------+---------------------+---------------------+
| employeeid | firstname | lastname | email | score | birthday | lastmodified | created |
+------------+-----------+----------+-------------------+-------+------------+---------------------+---------------------+
| e01 | Yusuf | Shakeel | yusuf@example.com | 0 | 1900-01-01 | 2018-01-01 01:01:01 | 2018-01-01 01:01:01 |
+------------+-----------+----------+-------------------+-------+------------+---------------------+---------------------+
1 row in set (0.00 sec)
Lets insert two more employee details and this time we will insert data for all the columns and we will insert multiple rows.
INSERT INTO `employee`
(`employeeid`, `firstname`, `lastname`, `email`, `score`, `birthday`, `lastmodified`, `created`)
VALUES
('e02', 'John', 'Doe', 'johndoe@example.com', 8, '1900-02-03', '2018-01-01 01:01:04', '2018-01-01 01:01:04'),
('e03', 'Jane', 'Doe', 'janedoe@example.com', 6, '1900-05-20', '2018-01-01 01:01:04', '2018-01-01 01:01:04');
And we get the following output:
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
We can now run the SELECT query and get the three rows.
mysql> SELECT * FROM employee;
+------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+
| employeeid | firstname | lastname | email | score | birthday | lastmodified | created |
+------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+
| e01 | Yusuf | Shakeel | yusuf@example.com | 0 | 1900-01-01 | 2018-01-01 01:01:01 | 2018-01-01 01:01:01 |
| e02 | John | Doe | johndoe@example.com | 8 | 1900-02-03 | 2018-01-01 01:01:04 | 2018-01-01 01:01:04 |
| e03 | Jane | Doe | janedoe@example.com | 6 | 1900-05-20 | 2018-01-01 01:01:04 | 2018-01-01 01:01:04 |
+------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+
3 rows in set (0.00 sec)
The comments
table has a foreign key employeeid
so, we have to give employeeid column of comments table a value that also exists in the employee
table for the employeeid column.
In the following example we are inserting a comment from the e03
employeeid account.
INSERT INTO `mysql_project`.`comments`
(`employeeid`, `commentbody`, `lastmodified`, `created`)
VALUES
('e03', 'Awesome', '2018-01-01 02-03-04', '2018-01-01 02-03-04');
We get the following output:Query OK, 1 row affected (0.00 sec)
We can check the inserted row using the SELECT query.
mysql> SELECT * FROM comments;
+-----------+------------+-------------+---------------------+---------------------+
| commentid | employeeid | commentbody | lastmodified | created |
+-----------+------------+-------------+---------------------+---------------------+
| 1 | e03 | Awesome | 2018-01-01 02:03:04 | 2018-01-01 02:03:04 |
+-----------+------------+-------------+---------------------+---------------------+
1 row in set (0.00 sec)
If we try to insert any value in the foreign key column of the child table that does not exists in the referred column in the parent table then we will get an error.
In the following example we are trying to insert data into the comments table using employeeid e00
which doesn't exists in the employeeid
column of the parent table employee
. So, the query will return an error.
INSERT INTO `comments`
(`employeeid`, `commentbody`, `lastmodified`, `created`)
VALUES
('e00', 'Hello World', '2018-01-01 02-03-10', '2018-01-01 02-03-10');
Output:
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`mysql_project`.`comments`, CONSTRAINT `employeeid_comments_FK` FOREIGN KEY (`employeeid`) REFERENCES `employee` (`employeeid`) ON DELETE CASCADE ON UPDATE CASCADE)
The error is telling us that we can't insert the data because 'e00' is not present in employeeid
column of the parent table employee
.
ADVERTISEMENT