INSERT Data

The INSERT statement is used to insert new data rows into a table. This chapter introduces various uses of the INSERT statement.

Basic Syntax

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

Insert Single Row

-- Insert user data
INSERT INTO users (id, name, age, email)
VALUES (1, 'Zhang San', 25, 'zhang@example.com');

Ways to Insert Data

Method 1: Specify Column Names (Recommended)

INSERT INTO users (name, age, email)
VALUES ('Li Si', 30, 'li@example.com');

Method 2: Without Column Names

-- Must provide all values in table column order
INSERT INTO users
VALUES (2, 'Wang Wu', 28, 'wang@example.com');

Method 3: Partial Column Insert

-- Insert only some columns, others use default or NULL
INSERT INTO users (name, email)
VALUES ('Zhao Liu', 'zhao@example.com');

Insert Multiple Rows

INSERT INTO users (name, age, email)
VALUES 
    ('Zhang San', 25, 'zhang@example.com'),
    ('Li Si', 30, 'li@example.com'),
    ('Wang Wu', 28, 'wang@example.com');

Insert from Query Results

-- Copy data from another table
INSERT INTO users_backup (id, name, age, email)
SELECT id, name, age, email
FROM users
WHERE age > 18;

Handle Duplicate Data

INSERT IGNORE

-- Ignore error if data exists
INSERT IGNORE INTO users (name, email)
VALUES ('Zhang San', 'zhang@example.com');

ON DUPLICATE KEY UPDATE

-- Update if data exists
INSERT INTO users (id, name, age)
VALUES (1, 'Zhang San', 26)
ON DUPLICATE KEY UPDATE age = 26;

Use Default Values

-- Use column default value
INSERT INTO users (name, age, status)
VALUES ('Zhang San', 25, DEFAULT);

Get Inserted ID

-- Get auto-generated ID after insert
INSERT INTO users (name, age) VALUES ('Zhang San', 25);
SELECT LAST_INSERT_ID();

Practical Examples

User Registration

START TRANSACTION;

INSERT INTO users (username, email, password_hash, created_at)
VALUES ('zhangsan', 'zhang@example.com', SHA2('password', 256), NOW());

SET @user_id = LAST_INSERT_ID();

INSERT INTO user_profiles (user_id, phone)
VALUES (@user_id, '13800138000');

COMMIT;

Best Practices

1. Always specify column names
2. Use batch insert for better performance
3. Use transactions for related inserts
4. Use parameterized queries to prevent SQL injection

Summary

• INSERT INTO ... VALUES: Insert data
• Batch insert: Insert multiple rows at once
• INSERT SELECT: Insert from query results
• Handle duplicates: IGNORE, ON DUPLICATE KEY UPDATE

Next Step: Learn UPDATE Data