DML (Data Manipulation Language)

The Data Manipulation Language is used to Manipulate the data in the database by using different commands. In this category we can able to perform Insert new data into Table, Update existing data in the Table, Delete Data from the Table and other functions we can perform on data by using these DML commands. Below I listed those commands for your reference

• Select It is used for select data from the Table based on the requirements
• Insert It is sued for Inserting data into existing table
• Update It is used for update data in the Table based on the requirement
• Delete It is used for delete data from the Table
• Merge It is used for upsert operations
• Call It is used for call a structured query language or Java sub program
• Lock Table It have ability to control the concurrency

Now I will explain each command with proper example for better understanding the concept.

Select

The Select command is used for select required data based on conditions from a existing Table. Here I select all data from the ClassMembers Table.

Syntax:

SELECT * FROM Table_Name

Example:

Insert

The Insert command is used for inserting new data into Table. Now I insert a new data into ClassMembers Table. Below I provide the example.

Syntax:

INSERT INTO Table_Name (Column 1, Column 2, Column 3, Column 4) VALUES (Value 1, Value 2,Value 3, Value 4);

Example:

Update

The Update command is used for update information In the Table. Now I will update name John Doe to Roman in the ClassMemebers Table. Below I provide that Example you can update any row or columns data.

Syntax:

UPDATE Table_Name SET Name = 'New_Value' WHERE Name = 'Ola_Value';

Example:

Delete:

The Delete command is used for delete data from the Table. Here I delete Student Id with 2 from the ClassMembers. Below I provide the Example for your reference.

Syntax:

DELETE FROM Table_Name WHERE Column  = Value;

Example:

Merge

The Merge command is used for perform upsert operation means It inserts rows that doesn’t exist and updates rows that do.

Example:

MERGE INTO target_table AS target
USING source_table AS source
ON (target.id = source.id)
WHEN MATCHED THEN
    UPDATE SET target.name = source.name
WHEN NOT MATCHED THEN
    INSERT (id, name) VALUES (source.id, source.name);

CALL

The Call command is used for call the user defined functions

Example:

CALL user_defined_function(parameter 1, parameter 2);

LOCK TABLE

The lock table command is used for lock the table for preventing access from the others for same Table

Syntax:

LOCK TABLE your_table IN EXCLUSIVE MODE;

Example:

LOCK TABLE ClassMembers IN EXCLUSIVE MODE;

Databases are used to store information. The Database is related to both software and hardware here The Software is used for accessing the data in the form of Software applications, and The Hardware is used for storing the data in the memory or hard disk.

Any Database provides an interface between the end user and the database by using this interface, the end user can access the database data. It is a very secure means before accessing database data we need to authenticate first, and then we get access to the database. We have different types of Database Languages which are represented in the below image.