MySQL - INSERT INTO Table - MySQL - dyclassroom

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.

INSERT syntax

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.

INSERT by specifying the column names

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.

INSERT multiple rows

We use the following syntax to insert multiple rows in the table.

INSERT INTO table_name(column1, column2, ...)
VALUES
('val1', 'val2', ...),
('val11', 'val22', ...);

Points to note!

When inserting data into the table keep the following points in mind.

Use double or single quotes to enclose string.
For integers and floating-point numbers we don't have to enclose them in quotes.
Date and time format must be enclosed in quotes.
Specify the column names for which you are inserting the value if the number of values is less than the number of columns in the table.

Exercise

Insert into employee table

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)

Insert into comments table with foreign key

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.