MSDN Documentation > SQL > Data Manipulation

SQL Data Manipulation Language (DML)

Data Manipulation Language (DML) is a subset of SQL used to retrieve, insert, update, and delete data in a database. These commands are fundamental to interacting with and managing the data stored within your relational database systems.

Core DML Statements

`SELECT`: Retrieving Data

The SELECT statement is used to query the database and retrieve one or more records. It's the most frequently used DML statement.

Example: Retrieving all columns and rows from a 'Customers' table

SELECT * FROM Customers;

Example: Retrieving specific columns and filtering rows

SELECT customer_name, email
FROM Customers
WHERE country = 'USA';

`INSERT`: Adding New Data

The INSERT statement is used to add new records (rows) into a table.

Example: Inserting a single row with specified values

INSERT INTO Customers (customer_name, email, country)
VALUES ('John Doe', 'john.doe@example.com', 'Canada');

Example: Inserting a single row without specifying column names (values must match table order)

INSERT INTO Customers
VALUES ('Jane Smith', 'jane.smith@example.com', 'UK', '2023-01-15'); -- Assumes these are the columns and their order

`UPDATE`: Modifying Existing Data

The UPDATE statement is used to modify existing records in a table. You must use a WHERE clause to specify which rows to update; otherwise, all rows will be updated.

Example: Updating the email for a specific customer

UPDATE Customers
SET email = 'new.john.doe@example.com'
WHERE customer_name = 'John Doe';

Example: Updating multiple fields for customers in a specific country

UPDATE Customers
SET country = 'USA', last_contact_date = '2024-03-10'
WHERE customer_id = 101;

`DELETE`: Removing Data

The DELETE statement is used to remove records from a table. Like UPDATE, a WHERE clause is crucial to avoid deleting all records.

Example: Deleting a specific customer

DELETE FROM Customers
WHERE customer_name = 'Jane Smith';

Example: Deleting all records from a table (use with extreme caution!)

DELETE FROM Customers; -- Empties the table

Important Considerations

WHERE Clause: Always use the WHERE clause with UPDATE and DELETE statements to target specific rows and prevent unintended data loss.
Data Types: Ensure the data you are inserting or updating matches the data types defined for the columns.
Transactions: For critical operations, use transactions to group multiple DML statements. This allows you to commit changes if successful or rollback if any part fails, ensuring data integrity.
Permissions: Access to DML operations is controlled by database permissions. You need appropriate privileges to perform these actions.
TRUNCATE vs. DELETE: While both remove data, DELETE removes rows one by one and can be rolled back. TRUNCATE is a DDL command that removes all rows quickly and cannot be rolled back easily (depending on the RDBMS).

Common Clauses and Modifiers

DML statements often include clauses that refine the operation:

WHERE: Filters records based on specified conditions.
ORDER BY: Sorts the result set of a SELECT statement.
GROUP BY: Groups rows that have the same values in specified columns into summary rows.
HAVING: Filters groups created by the GROUP BY clause.
JOIN: Combines rows from two or more tables based on a related column.
LIMIT / TOP: Restricts the number of rows returned by a SELECT statement.