Database Languages in DBMS

Types of Database Languages

The Data Languages are categorized into four different types based upon the various operations performed by the language. These include:

Now, let's explore the four different types of database languages, their usage, and how they are implemented in MySQL software.

1. Data Definition Language (DDL)

Data Definition Language (DDL) is a set of special commands that allows us to define and modify the structure and the metadata of the database. These commands can be used to create, modify, and delete the database structures such as schema, tables, indexes, etc.

Since DDL commands can alter the structure of the whole database and every change implemented by a DDL command is auto-committed (the change is saved permanently in the database), these commands are normally not used by an end-user (someone who is accessing the database via an application).

Some of the DDL commands are:

CREATE :

• It is used to create the database or its schema objects.
• MySQL Syntax -
  • To create a new database :
  • To create a new table :

DROP :

• It is used to delete the database or its schema objects.
• MySQL Syntax -
  • To delete an object :
  • To delete an existing table :
  • To delete the whole database :

ALTER :

• It is used to modify the structure of the database objects.
• MySQL Syntax -
  • To add new column(s) in a table :
  • To change the datatype of a column in a table :
  • To remove a column from a table :

TRUNCATE :

• It is used to remove the whole content of the table along with the deallocation of the space occupied by the data, without affecting the table's structure.
• MySQL Syntax -
  • To remove data present inside a table :

COMMENT:

• It is used to add comments about the tables, views, and columns into the data dictionary. These comments can help the developers to better understand the structure of the database.
• MySQL Syntax -
  • To comment on table/view :
  • To comment on a column :
  • To drop a comment :

RENAME :

• It is used to change the name of an existing table or a database object.
• MySQL Syntax -
  • To rename a table :
  • To rename a column of a table :

Highlights:

1. Set of commands used to alter/create schema and metadata of the database.

2. All changes are auto-committed.

3. DDL are normally not used by end-users.

4. Examples - CREATE, ALTER, DROP, TRUNCATE, COMMENT, RENAME

2. Data Manipulation Language (DML)

Data Manipulation Language (DML) is a set of special commands that allows us to access and manipulate data stored in existing schema objects. These commands are used to perform certain operations such as insertion, deletion, updation, and retrieval of the data from the database.

These commands deal with the user requests as they are responsible for all types of data modification. The DML commands that deal with the retrieval of the data are known as Data Query language.

Some of the DML commands are:

SELECT :

• It is used to retrieve or fetch data from a database. The SELECT statement cannot manipulate data, it can only access it. Hence, it is known as the Data Query Language, i.e., a limited form of DML statement.
• MySQL Syntax -
  • To fetch an entire table :
  • To fetch particular columns from a table :
  • To fetch particular columns from a table based on a condition:
  • Fetching data with various clauses (General SELECT statement):

INSERT :

• It is used to insert new rows into the table.
• MySQL Syntax -
  • To insert values according to the table structure :
  • To insert values based on the columns :

UPDATE :

• It is used to update existing column(s)/value(s) within a table.
• MySQL Syntax -
  • To update the columns of a table based on a condition (General UPDATE statement) :

DELETE :

• It is used to delete existing records from a table, i.e., it is used to remove one or more rows from a table.
• MySQL Syntax -
  • To delete rows from a table based on a condition :

MERGE:

• It is a combination of the INSERT, UPDATE, and DELETE statements.
• It is used to merge data from a source table or query-set with a target table based on the specified condition.
• MySQL Syntax -
  • To delete an object :

CALL :

• It is used to execute a structured query language function or a Java subprogram from within SQL.
• To use the CALL statement, we need to define a function using CREATE PROCEDURE command. Its syntax is:

• Now, we can execute this procedure using the CALL statement.
• MySQL Syntax -

EXPLAIN PLAN :

• It is used to display the sequence of operations performed by the DBMS software upon the execution of a DML statement.
• MySQL Syntax -
  • To display the execution plan of a query:

Highlights:

1. Set of commands used to access and manipulate data stored in existing schema objects.

2. All changes done by DML commands can be rolled back.

3. DML statements deal with the user requests.

4. Examples - SELECT, INSERT, UPDATE, DELETE, MERGE, CALL, EXPLAIN PLAN

3. Data Control Language (DCL)

Data Control Language (DCL) is a set of special commands that are used to control the user privileges in the database system. The user privileges include ALL, CREATE, SELECT, INSERT, UPDATE, DELETE, EXECUTE, etc.

We require data access permissions to execute any command or query in the database system. This user access is controlled using the DCL statements. These statements are used to grant and revoke user access to data or the database.

DCL commands are transactional i.e., these commands include rollback parameters. These commands include:

Grant :

• It is used to provide user access to the database or its objects.
• MySQL Syntax -
  • To grant user privilege to specified users on a database object :

Revoke :

• It is used to revoke user access to the database system.
• MySQL Syntax -
  • To revoke user privilege to specified users on a database object :

Highlights:

1. Set of commands used to control the user privileges in the database system.

2. The user privileges include ALL, CREATE, SELECT, INSERT, UPDATE, DELETE, EXECUTE, etc.

3. DCL commands are transactional.

4. Examples - GRANT, REVOKE

5. In practice, a combination of DDL, DML, and DCL is used as a single database language.

4. Transaction Control Language (TCL)

Transaction Control Language (TCL) is a set of special commands that deal with the transactions within the database. A transaction is a collection of related tasks that are treated as a single execution unit by the DBMS software. Hence, transactions are responsible for the execution of different tasks within a database.

The modifications performed using the DML commands are executed or rollbacked with the help of TCL commands. These commands are used to keep a check on other commands and their effects on the database. These include:

COMMIT :

• It is used to permanently save all the modifications are done (all the transactions) by the DML commands in the database. Once issued, it cannot be undone.
• DBMS software implicitly uses the COMMIT command before and after every DDL command to save the change permanently in the database.
• MySQL Syntax -

ROLLBACK :

• It is used to undo the transactions that have not already been permanently saved (or committed) to the database.
• It restores the previously stored value, i.e., the data present before the execution of the transactions.
• MySQL Syntax -
  • To undo a group of transactions since last COMMIT or SAVEPOINT :
  • To undo a group of transactions to a certain point :

SAVEPOINT :

• It is used to create a point within the groups of transactions to save or roll back later.
• It is used to roll the transactions back to a certain point without the need to roll back the whole group of transactions.
• MySQL Syntax -
  • To create a SAVEPOINT :
  • To release a SAVEPOINT :

AUTOCOMMIT :

• It is used to enable/disable the auto-commit process that commits each transaction after its execution.
• MySQL Syntax -
  • To enable the AUTOCOMMIT process:
  • To disable the AUTOCOMMIT process:

Highlights:

1. Set of commands that deal with the transactions within the database.
2. Used to keep a check on other commands and their effects on the database.
3. A transaction is a group of related tasks, treated as a single execution unit.
4. Examples - COMMIT, ROLLBACK, SAVEPOINT, AUTOCOMMIT.

Read More:

• Views in DBMS.
• Applications of DMBS.
• Types of Databases.
• Data Dictionary in DBMS.