Data Manipulation Language (DML) is a subset of SQL (Structured Query Language) that is used to manipulate data within a database. DML commands allow you to add, modify, and delete data from tables in a database. In this guide, we will cover the different DML commands and their usage.
- SELECT: The SELECT command is used to retrieve data from one or more tables. It allows you to specify which columns you want to retrieve and which conditions to apply.
Syntax: SELECT column_name(s) FROM table_name WHERE condition;
Example: SELECT * FROM Customers WHERE City = 'New York';
- INSERT: The INSERT command is used to insert new rows into a table.
Syntax: INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
Example: INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) VALUES ('Alfreds Futterkiste', 'Maria Anders', 'Obere Str. 57', 'Berlin', '12209', 'Germany');
- UPDATE: The UPDATE command is used to modify existing data in a table.
Syntax: UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
Example: UPDATE Customers SET ContactName = 'Ana Trujillo', City='Mexico City' WHERE CustomerID = 1;
- DELETE: The DELETE command is used to remove rows from a table.
Syntax: DELETE FROM table_name WHERE condition;
Example: DELETE FROM Customers WHERE CustomerID = 2;
- MERGE: The MERGE command allows you to combine INSERT, UPDATE, and DELETE operations into a single statement. It is used to synchronize two tables with the same structure.
Syntax: MERGE INTO target_table USING source_table ON condition WHEN MATCHED THEN UPDATE SET column1 = value1, column2 = value2, ... WHEN NOT MATCHED THEN INSERT (column1, column2, ...) VALUES (value1, value2, ...);
Example:
MERGE INTO Customers USING NewCustomers ON Customers.CustomerID = NewCustomers.CustomerID WHEN MATCHED THEN UPDATE SET Customers.ContactName = NewCustomers.ContactName, Customers.City = NewCustomers.City WHEN NOT MATCHED THEN INSERT (CustomerID, CustomerName, ContactName, City, PostalCode, Country) VALUES (NewCustomers.CustomerID, NewCustomers.CustomerName, NewCustomers.ContactName, NewCustomers.City, NewCustomers.PostalCode, NewCustomers.Country);
- COMMIT and ROLLBACK: The COMMIT command is used to permanently save changes made to the database, while the ROLLBACK command is used to undo changes made since the last COMMIT.
Syntax: COMMIT; or ROLLBACK;
Example: COMMIT;
These are the most commonly used DML commands in SQL. With these commands, you can easily manipulate data in your database to suit your needs.