Data Manipulation Language (DML) in SQL: Detailed Article with Examples
Data Manipulation Language (DML) is a subset of SQL (Structured Query Language) that is used for managing and manipulating data in relational databases. It provides the commands to insert, update, delete, and retrieve data from database tables. DML is essential for interacting with the actual data within a database and is widely used in applications to manage and process data.
Key DML Operations
There are four primary DML commands:
- SELECT – Retrieves data from the database.
- INSERT – Adds new data into a database table.
- UPDATE – Modifies existing data in a database table.
- DELETE – Removes data from a database table.
Let’s dive into each command with detailed explanations and examples.
1. SELECT - Retrieving Data
The SELECT statement is used to query the database and retrieve data from one or more tables. It is one of the most frequently used DML commands.
Basic Syntax:
SELECT column1, column2, ... FROM table_name;
Example:
SELECT first_name, last_name, age FROM employees;
This query retrieves the first_name,last_name, and age columns from the employees table.
Filtering Results:
You can use the WHERE clause to filter records based on specific conditions.
SELECT first_name, last_name, age FROM employees WHERE age > 30;
This query returns the names and ages of employees who are older than 30.
Using Wildcards:
The
*
wildcard can be used to select all columns from a table.
SELECT * FROM employees;
This retrieves all columns for all records from the employees table.
Sorting Results:
You can use the ORDER BY clause to sort the result set.
SELECT first_name, last_name, age FROM employees ORDER BY age DESC;
This query retrieves employee details, sorting them by age in descending order.
2. INSERT - Adding Data
The INSERT statement is used to insert new records into a table. You can insert one or multiple rows of data at once.
Basic Syntax:
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
Example:
INSERT INTO employees (first_name, last_name, age) VALUES ('John', 'Doe', 29);
This query inserts a new employee named John Doe, aged 29, into the employees table.
Inserting Multiple Rows:
You can also insert multiple rows in a single INSERT statement.
INSERT INTO employees (first_name, last_name, age) VALUES ('Jane', 'Smith', 34), ('Alice', 'Johnson', 28), ('Bob', 'Brown', 40);
This inserts three records into the employees table.
3. UPDATE - Modifying Data
The UPDATE statement is used to modify existing records in a table. You can update one or more columns for one or more rows based on specified conditions.
Basic Syntax:
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
Example:
UPDATE employees SET age = 30 WHERE first_name = 'John' AND last_name = 'Doe';
This query updates the age of the employee named John Doe to 30 in the employees table.
Updating Multiple Columns:
You can update multiple columns at once.
UPDATE employees SET age = 35, first_name = 'Jonathan' WHERE last_name = 'Doe';
This query updates both the age and the first name of employees whose last name is 'Doe'.
Important Notes:
- Always include the WHERE clause when updating records to prevent updating all rows in the table.
- If you omit the WHERE clause, all records in the table will be updated.
4. DELETE - Removing Data
The DELETE statement is used to remove one or more records from a table. Similar to the UPDATE statement, you should always use the WHERE clause to specify which records to delete.
Basic Syntax:
DELETE FROM table_name WHERE condition;
Example:
DELETE FROM employees WHERE first_name = 'John' AND last_name = 'Doe';
This query deletes the record of the employee named John Doe from the employees table.
Deleting All Records:
To delete all records from a table (without removing the table structure), you can use the DELETE statement without the WHERE clause.
DELETE FROM employees;
This deletes all rows from the employees table but keeps the table intact.
Important Notes:
- The DELETE command is a permanent action. Once the data is deleted, it cannot be recovered unless there’s a backup.
- To quickly remove all data in a table, you may use the TRUNCATE command (depending on the database system). It is faster than DELETE because it does not log individual row deletions.
Advanced DML Concepts
Using Joins with SELECT:
Joins allow you to combine rows from two or more tables based on a related column. The most common types of joins are INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.
SELECT employees.first_name, employees.last_name, departments.department_name FROM employees INNER JOIN departments ON employees.department_id = departments.department_id;
This query retrieves the first name, last name of employees, and their associated department names by joining the employees and departments tables on the department_id.
Subqueries:
A subquery is a query nested inside another query. Subqueries can be used in the SELECT, FROM, WHERE, or HAVING clauses.
SELECT first_name, last_name, age FROM employees WHERE age > (SELECT AVG(age) FROM employees);
This query retrieves employees who are older than the average age of all employees.
Conclusion
Data Manipulation Language (DML) is a core component of SQL that allows you to interact with the data stored in relational databases. By mastering the DML commands—SELECT, INSERT, UPDATE, and DELETE —you can retrieve, insert, modify, and remove data efficiently.
These commands, along with advanced techniques like joins and subqueries, enable you to handle data in powerful ways, making SQL an essential tool for database management and development.