Overview of DML Commands

CommandDescriptionSyntax Example
INSERTAdds new rows to a tableINSERT INTO table_name (column1, column2) VALUES (value1, value2);
UPDATEModifies existing rows in a tableUPDATE table_name SET column1 = value1 WHERE condition;
DELETERemoves rows from a tableDELETE FROM table_name WHERE condition;

1. Using the INSERT Command

The INSERT command is used to add new records to a table. It can be executed in two ways: inserting data into all columns or specifying particular columns.

Example: Inserting Data into All Columns

INSERT INTO employees (first_name, last_name, email, hire_date, job_title)
VALUES ('John', 'Doe', '[email protected]', '2023-01-15', 'Software Engineer');

Example: Inserting Data into Specific Columns

INSERT INTO employees (first_name, last_name)
VALUES ('Jane', 'Smith');

Best Practices for INSERT

  • Always specify the columns you are inserting data into. This improves readability and reduces errors.
  • Use transactions when inserting multiple records to maintain data integrity.

2. Using the UPDATE Command

The UPDATE command modifies existing records in a table. It is crucial to include a WHERE clause to avoid updating all records unintentionally.

Example: Updating a Single Record

UPDATE employees
SET job_title = 'Senior Software Engineer'
WHERE employee_id = 1;

Example: Updating Multiple Records

UPDATE employees
SET job_title = 'Software Engineer'
WHERE hire_date < '2020-01-01';

Best Practices for UPDATE

  • Always back up your data before performing updates.
  • Use transactions to ensure that updates can be rolled back in case of errors.
  • Test your UPDATE queries with a SELECT statement first to ensure you are targeting the correct records.

3. Using the DELETE Command

The DELETE command removes records from a table. Like UPDATE, it is critical to use a WHERE clause to prevent accidental deletion of all records.

Example: Deleting a Single Record

DELETE FROM employees
WHERE employee_id = 2;

Example: Deleting Multiple Records

DELETE FROM employees
WHERE hire_date < '2015-01-01';

Best Practices for DELETE

  • Always perform a SELECT query first to confirm which records will be deleted.
  • Consider implementing soft deletes (e.g., adding a deleted_at timestamp) instead of permanently deleting records.

Conclusion

Mastering DML commands is essential for effective database management. By using INSERT, UPDATE, and DELETE commands judiciously and following best practices, you can ensure that your data remains accurate and reliable.

Learn more with useful resources