Introduction to Data Manipulation Language
In this article, we will learn about Data Manipulation Language. As we are well aware that SQL (Structured Query Language) is one of the widely used languages to deal with structured data in the database. Though there are certain operations that we can perform in the database like operations related to the schema, some related to the data in the database, handling the transactions, etc. Creation of database, querying of the already present data, manipulation of data, etc. are a part of these operations. In SQL, these operations are broadly categorized into four categories which are DDL, DML, DQL, DCL. As the name DML indicates that all the SQL operations are related to the manipulation of already present data in the database like the insertion of new records, deletion of records, updation of records, etc falls under the category of DML. SQL commands like INSERT, UPDATE, DELETE, SELECT, etc are DML commands.
Why Do We Need Data Manipulation Language?
Consider a scenario of end-user, where the user has started using an application. The user signs up in the application by submitting the basic details. In order to keep the data of all the registered users, all the entries need to get inserted in a specified table. When the user logins in the application all the data related to that user will be displayed. Users can then perform the operations accordingly in their profile like inserting, deleting, retrieving according to the requirements, etc. All these operations are handled in the background using the DML commands. Since the DML commands are the frequently used commands and are the ones that are used for the end-users in order to interact with the system, it provides various facilities to the user according to the requirements. Users can perform the desired task using various clauses and conditions like WHERE HAVING, GROUP BY along with the SQL commands.
How Does Data Manipulation Language Work?
Practically DML operations can be done either on a single object or in a bulk. For example, an update can be performed on a single record or in some cases the whole table needs to get updated. If the user needs to perform operations on the whole table, a single query to perform the operation in bulk should be fired instead of a single record at a time as it is very time consuming and helps in avoiding the governor limits which in turn hampers the overall performance of query processing. DML statements are used in PL/ SQL programs in order to extend SQL capabilities and performing operations. They are of 2 types of DML used, i.e. Procedural DML (which allows the programmer to specify what data is required and how to retrieve that data) and Declarative DML (which allows the programmer to specify what data is required without specifying how to access that data). It is comparatively easier to work on the later one as the system would find the best way to retrieve the data efficiently.
Data Manipulation Language Commands
As discussed earlier Data Manipulation Language commands are used for manipulating data in the database. DML commands are used for update, insert, delete and alter of data in the database. Some of the DML commands commonly used by the programmers while dealing with the database are given below:
1. SELECT
SELECT Command is used to retrieve the records from the table. It is one of the most commonly used commands while working with the databases because at the end-user wants to retrieve the results after any operation be it update, delete or modify.
Syntax: SELECT [column_name(s)] from [table_name] where [condition]
2. INSERT
INSERT command is used for the insertion of one or more records in the table.
Syntax: INSERT into [table_name] values (val1, val2, val3, …)
If we want to insert the data on particular columns, column names need to be specified in the SQL command.
Syntax: INSERT into [table_name] (col1, col2, col3, …) values (val1, val2, val3, …)
3. DELETE
DELETE command is used to delete one or more records from the table on the basis of the where condition specified in the command.
Syntax: DELETE from [table_name] where [condition to be specified]
If where the condition is not specified in the SQL command with DELETE, the whole table will get deleted.
Syntax: DELETE from [table_name]
4. UPDATE
UPDATE command is used for the modification of one or more records in the existing table.
Syntax: UPDATE [table_name] SET [col1 = val1, col2 = val2,...] where [condition to be specified]
5. MERGE
MERGE command is a combination of INSERT, UPDATE and DELETE. When two tables (source table and the target table) need to be merged then all the above mentioned three operations are performed all together using the MERGE command.
Advantages of Data Manipulation Language
Though DML is the lifeline when talking about performing operations in a structured database. Some of the advantages of DML are given below:
- DML commands give us the flexibility to retrieve the data according to the requirements by applying the conditions using the WHERE clause.
- Data stored in the database can easily be modified anytime and that too very easily using the DML commands.
- DML provides the facility of the efficient and fast interaction of the human (end-user using the system) with the system or the backend data stored in the database.
- We can apply the various restrictions at the schema level and yet provide some privilege to the end-user to manipulate and retrieve the data under a controlled environment.
- Moreover, the end-user cannot make any modification at the schema level in the database using DML which again is an advantage when talking about privacy issues.
Conclusion
The above description clearly explains what is DML and why is it important when dealing with the querying the already present records in the database/ table. After the whole schema of the table including the columns their datatype, limit, etc are prepared, the main thing is to deal with the data. In Fact, the end-user, using the system performs these basic operations like insertion, deletion, updation, retrieving of data which is reflected on his/ her system is all controlled by DML commands in the backend.
Recommended Articles
This is a guide to Data Manipulation Language. Here we discuss why do we need Data Manipulation Language? how does it work? along with the DML commands and its advantages. You can also go through our other related articles to learn more –
