MySQL INSERT Query

Introduction

In this chapter, we will learn how to use the INSERT statement in MySQL to add data to a table. The INSERT statement is used to insert new rows into a table. We will cover the syntax, examples, and important considerations for using the INSERT statement.

Inserting Data into a Table

To insert data into a table, we use the INSERT INTO statement. This command adds new rows to the specified table.

Syntax

INSERT INTO table_name (column1, column2, ..., columnN) VALUES (value1, value2, ..., valueN);

• table_name: The name of the table where you want to insert data.
• column1, column2, ..., columnN: The names of the columns where you want to insert data.
• value1, value2, ..., valueN: The values to insert into the columns.

Example

INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Rahul', 'Sharma', 'rahul.sharma@example.com', '2023-07-01');

This example inserts a new row into the employees table with the specified values.

Inserting Multiple Rows

You can also insert multiple rows into a table in a single INSERT statement by separating the values with commas.

Syntax

INSERT INTO table_name (column1, column2, ..., columnN) VALUES
(value1, value2, ..., valueN),
(value1_2, value2_2, ..., valueN_2),
...;

Example

INSERT INTO employees (first_name, last_name, email, hire_date) VALUES
('Rahul', 'Sharma', 'rahul.sharma@example.com', '2023-07-01'),
('Priya', 'Singh', 'priya.singh@example.com', '2023-07-02');

This example inserts two new rows into the employees table.

Inserting Data into Specific Columns

If you do not want to insert data into all columns, you can specify only the columns you want to insert data into. The remaining columns will take their default values.

Syntax

INSERT INTO table_name (column1, column2) VALUES (value1, value2);

Example

INSERT INTO employees (first_name, email) VALUES ('Amit', 'amit.kumar@example.com');

This example inserts a new row into the employees table, setting values only for the first_name and email columns. The other columns will take their default values.

Inserting Data Without Specifying Column Names

You can also insert data without specifying the column names if you are providing values for all columns in the exact order they are defined in the table.

Syntax

INSERT INTO table_name VALUES (value1, value2, ..., valueN);

Example

INSERT INTO employees VALUES (NULL, 'Neha', 'Verma', 'neha.verma@example.com', '2023-07-03');

This example inserts a new row into the employees table with values for all columns. The NULL value is used for the id column, which is an auto-increment column.

Full Example

Let's go through a full example where we create a table, insert data into it, and then query the data to verify the insertion.

1. Create a Database:

CREATE DATABASE company;

2. Select the Database:

USE company;

3. Create a Table:

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE
);

4. Insert Data into the Table:

INSERT INTO employees (first_name, last_name, email, hire_date) VALUES
('Rahul', 'Sharma', 'rahul.sharma@example.com', '2023-07-01'),
('Priya', 'Singh', 'priya.singh@example.com', '2023-07-02'),
('Amit', 'Kumar', 'amit.kumar@example.com');

5. Insert Data Without Specifying Column Names:

INSERT INTO employees VALUES
(NULL, 'Neha', 'Verma', 'neha.verma@example.com', '2023-07-03');

6. Query the Data:

SELECT * FROM employees;

Output

id	first_name	last_name	email	hire_date
1	Rahul	Sharma	rahul.sharma@example.com	2023-07-01
2	Priya	Singh	priya.singh@example.com	2023-07-02
3	Amit	Kumar	amit.kumar@example.com	NULL
4	Neha	Verma	neha.verma@example.com	2023-07-03

Important Considerations

• Data Types: Ensure that the values you insert match the data types of the columns.
• Constraints: Be aware of constraints such as NOT NULL, UNIQUE, and PRIMARY KEY that may affect your ability to insert data.
• Default Values: If you do not specify a value for a column, it will take its default value if one is defined.

Conclusion

Inserting data into a table is a fundamental operation in MySQL. This chapter covered how to use the INSERT INTO statement to add new rows to a table, including examples of inserting single and multiple rows, inserting data into specific columns, and inserting data without specifying column names. In the next chapter, we will learn how to select the data from tables.