Kenhuang Academy
  • English

      • 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

      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