MySQL INSERT INTO

The INSERT INTO statement adds new rows to a table.

INSERT INTO students
VALUES (1, 'Rahul', 20);

After executing the statement, a new student record is added to the table.

The basic syntax is:

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

The values must be provided in the same order as the table columns.

Let's create a table for practice.

CREATE TABLE students (
    id INT,
    name VARCHAR(100),
    age INT
);

We can insert one student like this:

INSERT INTO students
VALUES (1, 'Rahul', 20);

This adds one row to the students table.

Use the SELECT statement to check the inserted record.

SELECT * FROM students;

The result will contain the newly inserted student.

Multiple rows can be inserted using a single INSERT statement.

INSERT INTO students
VALUES
(1, 'Rahul', 20),
(2, 'Priya', 21),
(3, 'Amit', 19);

It is often better to specify the column names explicitly.

INSERT INTO students (id, name, age)
VALUES (1, 'Rahul', 20);

This makes the statement easier to understand and maintain.

You can insert data into only selected columns when other columns can use defaults or allow NULL values.

INSERT INTO students (id, name)
VALUES (4, 'Neha');

The age column is not specified in this example.

The values must match the order of the specified columns.

INSERT INTO students (name, age, id)
VALUES ('Amit', 22, 5);

Here, Amit goes into name, 22 into age, and 5 into id.

Text values are normally written inside single quotes.

INSERT INTO students (id, name, age)
VALUES (6, 'Suresh', 25);

Here, 'Suresh' is a string value.

Numeric values such as INT values do not need quotes.

INSERT INTO students (id, name, age)
VALUES (7, 'Pooja', 23);

Here, 7 and 23 are numeric values.

DECIMAL columns can store values containing decimal points.

CREATE TABLE fees (
    student_id INT,
    amount DECIMAL(10,2)
);

INSERT INTO fees (student_id, amount)
VALUES (1, 2500.50);

DATE values can be inserted using the YYYY-MM-DD format.

CREATE TABLE admissions (
    id INT,
    admission_date DATE
);

INSERT INTO admissions (id, admission_date)
VALUES (1, '2026-09-21');

DATETIME values contain both date and time.

INSERT INTO attendance (student_id, login_time)
VALUES (1, '2026-09-21 09:30:00');

If a column allows NULL, you can explicitly insert NULL.

INSERT INTO students (id, name, age)
VALUES (8, 'Ravi', NULL);

NULL means that a value is missing or unknown. It is not the same as zero or an empty string.

If a column uses AUTO_INCREMENT, you normally do not need to provide its value.

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    age INT
);

INSERT INTO students (name, age)
VALUES ('Ankit', 22);

MySQL automatically generates the ID.

If a column has a DEFAULT value, MySQL can use that default when the column is not specified.

CREATE TABLE students (
    id INT,
    name VARCHAR(100),
    active BOOLEAN DEFAULT TRUE
);

INSERT INTO students (id, name)
VALUES (1, 'Ravi');

The active column can receive its default value.

A PRIMARY KEY must contain unique values.

INSERT INTO students (id, name)
VALUES (1, 'Rahul');

Trying to insert another record with the same primary key can result in a duplicate key error.

For tables with many columns, specifying column names is recommended.

INSERT INTO students
(id, name, father_name, mobile, course)
VALUES
(1, 'Rahul', 'Ramesh Kumar', '9876543210', 'Python');

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    mobile VARCHAR(15),
    course VARCHAR(100),
    fee DECIMAL(10,2)
);

INSERT INTO students
(name, mobile, course, fee)
VALUES
('Rahul', '9876543210', 'Python', 15000.00);

Multiple students can be inserted in one statement.

INSERT INTO students
(name, mobile, course, fee)
VALUES
('Rahul', '9876543210', 'Python', 15000.00),
('Priya', '9876543211', 'Java', 18000.00),
('Amit', '9876543212', 'PHP', 12000.00);

MySQL can also insert rows returned by a SELECT query.

INSERT INTO old_students (id, name)
SELECT id, name
FROM students;

This is useful when copying data between tables.

You can specify the database name before the table name.

INSERT INTO training_db.students
(id, name, age)
VALUES
(1, 'Rahul', 20);

This is useful when working with multiple databases.

After inserting data, use SELECT to verify the records.

SELECT * FROM students;

You can also select specific columns:

SELECT id, name, course
FROM students;

• Wrong number of values
• Incorrect data type
• Duplicate primary key
• Missing required NOT NULL value
• Incorrect date format
• Missing quotes around text values
• Using a table that does not exist

The number of values should match the specified columns.

Incorrect:

INSERT INTO students (id, name, age)
VALUES (1, 'Rahul');

Correct:

INSERT INTO students (id, name, age)
VALUES (1, 'Rahul', 20);

A simple workflow is:

1. Create or select the database.
2. Use the required table.
3. Check the table structure.
4. Write the INSERT statement.
5. Execute the statement.
6. Use SELECT to verify the data.

USE training_db;

DESCRIBE students;

INSERT INTO students (name, age)
VALUES ('Neha', 21);

SELECT * FROM students;

You can execute INSERT statements using MySQL Workbench.

INSERT INTO students (name, age)
VALUES ('Aman', 24);

Click the execute button in the SQL editor and then run SELECT to view the result.

PHP applications commonly use INSERT statements to save form data into MySQL.

$sql = "INSERT INTO students (name, age)
        VALUES (:name, :age)";

$stmt = $pdo->prepare($sql);

$stmt->execute([
    ':name' => $name,
    ':age' => $age
]);

Prepared statements are commonly used when inserting user-provided data.

CREATE DATABASE IF NOT EXISTS training_db;

USE training_db;

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    mobile VARCHAR(15),
    course VARCHAR(100),
    fee DECIMAL(10,2),
    admission_date DATE
);

INSERT INTO students
(name, mobile, course, fee, admission_date)
VALUES
('Rahul', '9876543210', 'Python', 15000.00, '2026-09-21'),
('Priya', '9876543211', 'Java', 18000.00, '2026-09-21'),
('Amit', '9876543212', 'PHP', 12000.00, '2026-09-21');

SELECT * FROM students;

This example creates a table, inserts multiple records, and then displays the inserted data.