4. MySQL: INSERT INTO Statement

Before reading the points to remember, I suggest you go through the blog once.

Points to remember:

1. Always check the table's schema(the data types of the columns where) before entering the data because SQL won't show any error while inserting, but if we use the data later for computation, it might lead us errors.

2. Date time values should always be enclosed within a single quote (' ').

3. If we try to run the insert command with the same entries(actor_id, first_name, last_name, last_update) again on our dataset, we will get an error because the column "actor_id" is one of the primary keys, and duplication is not allowed.

4. But, if we try to use the insert command with the entires(first_name, last_name) again, a new entry will be created with an auto-generated 'actor_id.'

INSERT INTO Statement:

The INSERT INTO statement is a commonly used SQL command that allows users to insert new data rows into a database table. This statement is beneficial when you want to add new data to a table that already contains data or to create a new table and populate it with data simultaneously.

• The syntax is :

INSERT INTO table_name(columnname_1, columnname_2, columnname_3)
VALUES (value1,value2,value3);

Before inserting data in the table in any of the two ways we will discuss, always check the table's schema like this.

desc actor;

Output:

We will verify queries from the actor table, so let's look at it.

select * from actor;

Output:

We can insert a record in 2 ways

1. We mention the column names and enter the values according to the column names.

2. We can mention the table name and then directly enter the values, BUT we need to enter them in the order of the default table structure.

TYPE 1:

We mention the column names and enter the values according to the column names.

• For Example:

INSERT INTO actor(first_name,actor_id,  last_name,last_update)
VALUES ('SQL',2000,'ISFUN','2022-12-16 19:11:46');

• Output:

We can check the inserted value in the table using the select and where clause.

SELECT * FROM actor
WHERE actor_id = 2000;

Output:

TYPE 2:

We can mention the table name and then directly enter the values, BUT we need to enter it in the order of the default table.

INSERT INTO actor
VALUES (2001,'SQL','ROCKS','2022-12-16 19:11:46');

In the above query, the order is 'actor_id' followed by first_name, last_name, and last_update. So while inserting the second way, we need to enter it in the same order.

Output *:

We can check the inserted value in the table using the select and where clause.

SELECT * FROM actor
WHERE actor_id = '2001';

Output*:

VARIATIONS:

In our table schema, we see that the 'actor_id' is a primary key with auto-increment, and the 'last update' column is default generated.

So, in this case, we can mention the 'first_name' and 'last_name', and we will be good to go.

INSERT INTO actor(first_name, last_name)
values ('THIS BLOG','ROCKSS');

Output:

Now for verification:

SELECT * FROM actor
WHERE last_name = 'ROCKSS';

Output:

So here we see that it automatically updated the actor_id and the last_update timestamp.

Now, if we only give the 'last_name' in the query, it will show us an error because there is no default value set for 'first_name'.

So here we have covered the INSERT INTO Statement.

We'll meet again tomorrow :)

*****All the outputs provided are a snippet of the actual result.

To see more examples, you can always refer w3 schools; it has always been my go-to website to understand things.

References:

https://www.w3schools.com/mysql/default.asp