MySQL INSERT INTO Statement

The INSERT INTO statement in MySQL is used to add new records to a table. It allows you to populate data into specific columns or all columns of a table, depending on the requirement.

Syntax

1. Specifying Columns and Values

To insert data into specific columns:

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

2. Inserting Values for All Columns

If you are adding data for all columns in the table and know the order of the columns, you can omit specifying the column names:

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

3. Inserting Data into Specific Columns

To insert data only into specific columns (not all columns in the table):

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

Examples

Example Table: Customers

1. Insert Data into All Columns

Add a new record to the Customers table:

INSERT INTO Customers
VALUES (3, 'Antonio Moreno Taquería', 'México D.F.', 'Mexico');

2. Insert Data by Specifying Columns

Insert a record by specifying only the CustomerName, City, and Country. The CustomerID will auto-increment:

INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Around the Horn', 'London', 'UK');

3. Insert Multiple Records

You can add multiple rows in a single query:

INSERT INTO Customers (CustomerName, City, Country)
VALUES 
('Berglunds snabbköp', 'Luleå', 'Sweden'),
('Bon app\'', 'Marseille', 'France');

Resulting Table

Practical Tips

1. Avoid SQL Injection: Use prepared statements or parameterized queries when inserting user-supplied data.
2. Default Values: Columns not specified in the INSERT statement will use their default values if defined.
3. Auto-Increment: Ensure the primary key (like CustomerID) is set to auto-increment if you don’t want to specify it manually.
4. Data Validation: Validate the data before inserting to maintain database integrity.

By mastering the INSERT INTO statement, you can efficiently populate your MySQL tables with meaningful data.