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.
employee
comments
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.
table_name
'val1', 'val2', ... are the values for the columns of the table.
'val1', 'val2', ...
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.
val1
column1
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)
Query OK, 1 row affected (0.00 sec)
We can check the result using the SELECT query.
SELECT
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.
employeeid
In the following example we are inserting a comment from the e03 employeeid account.
e03
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.
e00
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.