Insert Into Command In MySQL

In MySQL, The "INSERT INTO" statement is used to add single or multiple records or rows to the table.

We can insert data into the table in two ways.

Declare column names while Inserting.
By omitting the column names while Inserting.

Syntax For INSERT INTO Statement:

INSERT INTO tableName (columnName1, columnName2, columnN)
VALUES (value1, value2, valueN);

tableName: The name of the table to which we want to insert data.
columnName1, columnName2, columnN: The columns into which we want to insert data.
VALUES: The keyword used to specify the values to be inserted.
value1, value2, valueN: The values to be inserted into the corresponding columns.

Inserting Values into All Columns:

In the below query, we are inserting a new row into the "users" table with values for the "firstName", and "lastName" columns.

Example:

INSERT INTO users (first_name, last_name)
VALUES ('Alice', 'Collin');

Inserting Values into Specific Columns:

In the below query, we are inserting a new row into the "users" table with values for the "userId", "firstName", and "lastName" columns.

Example:

INSERT INTO users (userId, firstName, lastName)
VALUES (U1001, 'Alice', 'Collin');

Inserting Multiple Rows:

In the below query, we are inserting multiple rows into the "products" table with values for the "product_name", "product_code", "category" and "product_price" columns.

Example:

INSERT INTO products (product_name, product_code, category, product_price)
VALUES ('iPhone XR', 'I101', 'ELECTRONICS', 1200),
       ('onePlus 9x', 'O101', 'ELECTRONICS', 800),
       ('Samsung Flip', 'S101', 'ELECTRONICS', 600);

Inserting Values from Another Query:

In the below query, we are inserting multiple rows into the "products" table with values for the "product_name", "product_code", "category" and "product_price" columns using the `SELECT` Command

Example:

INSERT INTO products (product_name, product_code, category, product_price)
SELECT product_name, product_code, category, product_price 
FROM old_products;

Inserting Values with Subquery:

We can use the INSERT INTO ... SELECT statement to insert data into a table based on the results of a SELECT query.

Example:

INSERT INTO products (product_name, product_code, category, product_price, created_at)
SELECT product_name, product_code, category, product_price, created_at
FROM old_products
WHERE created_at < '2023-12-31' AND created_at > '2023-01-01';

In the above query, we are inserting rows into the "products" table based on the result set of the SELECT query where products created_at date less than '2023-12-31' and created_at date greater than '2023-01-01'.

Inserting Values By Omitting Columns:

In the below query, we are inserting a new row into the "users" table with values for the "userId", "firstName", and "lastName" columns but omitted from Query.

Example:

INSERT INTO users VALUES ("Alice", 'Collin', 'U101');

<-- Update

Delete -->