Different types of DBMS Languages

In this article, we will have an overview of different types of DBMS languages and their functionalities.

DDL

DDL stands for data definition language. it deals with schema and structure. It is used to establish and modify the structures of objects. It is used to define schema, tables, indexes, constraints. Let’s learn in brief about all these commands.

CREATE command is used to create a new database, create a new table in the database, create index value (index values helps to speed up search and queries)

ALTER command is used to add, delete, or modify a column in existing tables.

DROP drop table is used to delete tables from the database

TRUNCATE command is used to remove all the records from a table in the database

RENAME command is used to rename the name of the table.

DML

DML stands for Data Manipulation Language. It is used to manipulate data in the tables. DML is used only after a structure is created. The values for DDL are added and manipulated through DML. The command includes select, insert, update, delete.

SELECT command is used to retrieved data from tables or databases.

INSERT command is used to insert data into a table.

UPDATE command is used to update existing data from a table.

DELETE command is used to delete all the records from a table in the database. The main difference between the delete and truncate command is: delete can also use the “where” command to delete specific records.

MERGE command is the combination of insert, delete, and update statements.

CALL command is used to call a stored procedure or a java program.

LOCK TABLE: lock table helps to protect concurrency in data. It is used to protect shared resources or objects.

DCL

DCL stands for Data Control Language. It mainly deals with granting permission, authorization, rights control of the database system. Some popular DCL commands are :

GRANT command provides privileges to the user.

REVOKE command takes back the permission from the user.

TCL

TCL stands for Transaction Control Language. They are used to manage the changes made by DML statements. The main TCL commands are commit, rollback, and save point.

COMMIT is used to permanently save any transaction into the database. Let’s take an example when we use the insert command to insert data. The changes made are not permanent. We use the commit command to mark the changes as permanent.

ROLLBACK command is used to restore the database into the last committed state. Suppose we used the update command to make some changes on a record but later we decided that the previous record was fine. In this case, we can use the rollback command to bring back those changes unless they are committed.

SAVEPOINT command is used to temporarily save a transaction so that it can be rolled back whenever necessary.