Home
Database Language with syntax example
types

Database Language

 

Database Language

  • A DBMS has appropriate languages and interfaces to express database queries and updates.
  • Database languages can be used to read, store and update the data in the database.

Types of Database Language


DBMS Language


1. Data Definition Language

  • DDL stands for Data Definition Language. It is used to define database structure or pattern.
  • It is used to create schema, tables, indexes, constraints, etc. in the database.
  • Using the DDL statements, you can create the skeleton of the database.
  • Data definition language is used to store the information of metadata like the number of tables and schemas, their names, indexes, columns in each table, constraints, etc.

  • 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 :
      CREATE DATABASE database_name;
      • To create a new table :
      CREATE TABLE table_name (
    column_1 DATATYPE,
    column_2 DATATYPE,
    column_n DATATYPE );

    DROP :

    • It is used to delete the database or its schema objects.
    • MySQL Syntax -
      • To delete an object :
      DROP object object_name
      • To delete an existing table :
      DROP TABLE table_name;
      • To delete the whole database :
      DROP DATABASE database_name;

    ALTER :

    • It is used to modify the structure of the database objects.
    • MySQL Syntax -
      • To add new column(s) in a table :
      ALTER TABLE table_name ADD (
    column_1 DATATYPE,
    column_2 DATATYPE,
    column_n DATATYPE );
      • To change the datatype of a column in a table :
      ALTER TABLE table_name 
MODIFY column_name DATATYPE;
      • To remove a column from a table :
      ALTER TABLE table_name
DROP COLUMN column_name;

    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 :
      TRUNCATE TABLE  table_name;

NOTE - We can also use the DROP command to delete the complete table, but the DROP command will remove the structure along with the contents of the table. Hence, if you want to remove the data present inside a table but not the table itself, you can use TRUNCATE instead of DROP

  • 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 :
      COMMENT ON TABLE table_name 
IS 'text';
      • To comment on a column :
      COMMENT ON COLUMN table_name.column_name 
IS 'text';
      • To drop a comment :
      COMMENT ON TABLE table_name 
IS ' ';

    RENAME :

    • It is used to change the name of an existing table or a database object.
    • MySQL Syntax -
      • To rename a table :
      RENAME old_table_name TO new_table_name;
      • To rename a column of a table :
      ALTER TABLE table_name 
RENAME COLUMN old_Column_name to new_Column_name;

    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

Here are some tasks that come under DDL:

  • Create: It is used to create objects in the database.
  • Alter: It is used to alter the structure of the database.
  • Drop: It is used to delete objects from the database.
  • Truncate: It is used to remove all records from a table.
  • Rename: It is used to rename an object.
  • Comment: It is used to comment on the data dictionary.

These commands are used to update the database schema that's why they come under Data definition language.

2. Data Manipulation Language

DML stands for Data Manipulation Language. It is used for accessing and manipulating data in a database. It handles user requests.

Here are some tasks that come under DML:

  • Select: It is used to retrieve data from a database.
  • Insert: It is used to insert data into a table.
  • Update: It is used to update existing data within a table.
  • Delete: It is used to delete all records from a table.
  • Merge: It performs UPSERT operation, i.e., insert or update operations.
  • Call: It is used to call a structured query language or a Java subprogram.
  • Explain Plan: It has the parameter of explaining data.
  • Lock Table: It controls concurrency.

  • 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 :
      SELECT * FROM table_name;
      • To fetch particular columns from a table :
      SELECT column_1, column_2 FROM table_name;
      • To fetch particular columns from a table based on a condition:
      SELECT column_1, column_2 
FROM table_name
WHERE <condition>;
      • Fetching data with various clauses (General SELECT statement):
      SELECT column_list FROM table-name
[WHERE Clause]
[GROUP BY clause]
[HAVING clause]
[ORDER BY clause];

    INSERT :

    • It is used to insert new rows into the table.
    • MySQL Syntax -
      • To insert values according to the table structure :
      INSERT INTO table_name
VALUES value1, value2, value3;
      • To insert values based on the columns :
      INSERT INTO table_name column1, column2, column3
VALUES value1, value2, value3;

Note: While using the INSERT command, make sure that the datatypes of each column match with the inserted value, as this may lead to unexpected scenarios and errors in the database.

  • 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) :
      UPDATE table_name
SET column_1 = value1, 
    column_2 = value2,
    column_3 = value3,
[WHERE condition]

Note: Here, the SET statement is used to set new values to the particular column, WHERE clause is used to select rows for which the columns are updated for the given table.

  • 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 :
      DELETE FROM table_name [WHERE condition];

Note: The DELETE statement only removes the data from the table, whereas the TRUNCATE statement also frees the memory along with data removal. Hence, TRUNCATE is more efficient in removing all the data from a table.

  • 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 :
      MERGE INTO target_table_name  
USING source_table_name  
    ON <condition>
WHEN MATCHED THEN 
    UPDATE <statements>                     
WHEN NOT MATCHED THEN 
    INSERT <statements>

    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:
      CREATE PROCEDURE procedure_name (parameter_1 DATATYPE_1, ...)
AS
BEGIN
  QUERY
END
    • Now, we can execute this procedure using the CALL statement.
    • MySQL Syntax -
      SET @parameter1 = value1;
CALL procedure_name([@parameter1,.. ])

    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:
      EXPLAIN PLAN FOR
    QUERY;

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 stands for Data Control Language. It is used to retrieve the stored or saved data.
  • The DCL execution is transactional. It also has rollback parameters.

    • (But in Oracle database, the execution of data control language does not have the feature of rolling back.)

Here are some tasks that come under DCL:

  • Grant: It is used to give user access privileges to a database.
  • Revoke: It is used to take back permissions from the user.

There are the following operations which have the authorization of Revoke:

CONNECT, INSERT, USAGE, EXECUTE, DELETE, UPDATE and SELECT.

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 :
    GRANT <privilege>
ON <object>
TO user1, user2;

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 :
    REVOKE <privilege>
ON <object>
FROM user1, user2;

Note: In practical usage, instead of having a separate language for every operation, a combination of DDL, DML, and DCL is used as part of a single database language such as SQL.

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 is used to run the changes made by the DML statement. TCL can be grouped into a logical transaction.

Here are some tasks that come under TCL:

  • Commit: It is used to save the transaction on the database.
  • Rollback: It is used to restore the database to original since the last Commit.

  • 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 -
      COMMIT;

    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 :
      ROLLBACK;
      • To undo a group of transactions to a certain point :
      ROLLBACK TO savepoint_name;

    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 :
      SAVEPOINT savepoint_name;
      • To release a SAVEPOINT :
      RELEASE SAVEPOINT savepoint_name;

    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:
      SET AUTOCOMMIT = 1 ;
      • To disable the AUTOCOMMIT process:
      SET AUTOCOMMIT = 0;

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.

Post a Comment

© ‧ DBMS. The Best Codder All rights reserved. Distributed by