MYSQL Tutorial

MySQL Data Manipulation (DML)

Data Manipulation Language (DML) in MySQL lets you insert, update, and delete data in your tables. These commands modify the contents of your database.

1. INSERT

Add new rows to a table. You can specify columns explicitly or insert into all columns.

-- Insert into specified columns
INSERT INTO users (name, email, age)
VALUES ('John Doe', 'john@example.com', 30);

-- Insert into all columns (order must match table definition)
INSERT INTO users
VALUES (NULL, 'Jane Smith', 'jane@example.com', 28, NOW());
  

2. UPDATE

Modify existing rows. Always include a WHERE clause to avoid updating every row.

-- Update a single record
UPDATE users
SET email = 'john.doe@example.com'
WHERE id = 1;

-- Update multiple columns and multiple rows
UPDATE users
SET age = age + 1,
    updated_at = NOW()
WHERE signup_date < '2024-01-01';
  

3. DELETE

Remove rows from a table. Use WHERE to target specific rows; omitting it will delete all rows.

-- Delete a specific user
DELETE FROM users
WHERE id = 2;

-- Delete all inactive users
DELETE FROM users
WHERE last_login < DATE_SUB(NOW(), INTERVAL 1 YEAR);
  

Best Practices & Tips

• Always backup before running mass UPDATE or DELETE commands.
• Use BEGIN…COMMIT transactions for critical operations to allow rollback.
• Test your WHERE clauses with a SELECT first to confirm affected rows.
• For bulk inserts, consider INSERT ... SELECT to copy data between tables.
• Check affected_rows() in your application logic to verify changes.