DDL, DML, DCL and TCL Commands in SQL/MySQL/NoSQL - Tutorial Boy -->
Home
DBMS

DDL, DML, DCL and TCL Commands in SQL/MySQL/NoSQL


Objective 

to understand database DDL(Data Definition Language), DML(Data Manipulation Language), DCL (Data-Control Language), and TCL (Transaction Control Language) Command in SQL/MySQL/NoSQL.

Relevant Theory

SQL:-

  • IBM developed the original version of SQL, originally called Sequel, as part of the System R project in the early 1970s. The Sequel language has evolved since then, and its name has changed to SQL (Structured Query Language). Many products now support the SQL language. SQL has clearly established itself as the standard relational database language. 
  • In 1986, the American National Standards Institute (ANSI) and the International Organization for Standardization (ISO) published an SQL standard, called SQL-86. ANSI published an extended standard for SQL, SQL-89, in 1989. The next version of the standard was SQL-92 standard, followed by SQL:1999, SQL:2003, SQL:2006, and most recently SQL:2008. The bibliographic notes provide references to these standards.

Selecting Databases:

  • Once you get a connection with the MySQL server, it is required to select a particular database to work with. This is because there may be more than one database available with MySQL Server.
Selecting MySQL Database from Command Prompt:
  • This is very simple to select a particular database from mysql> prompt. You can use the SQL command used to select a particular database.
The SQL language has several parts:
  • DDL (Data-definition Language)
  • DML (Data-manipulation Language)
  • DCL (Data-Control Language)
  • TCL (Transaction Control Language)

DDL(Data-definition Language):-

The SQL DDL provides commands for defining relation schemas, deleting relations, and modifying relation schemas.

DDL commands are as follows :
  • CREATE
  • DROP
  • ALTER
  • TRUNCATE
  • COMMENT
  • RENAME

CREATE COMMAND :

  • This is used to create the database or its objects. You would need special privileges to create or delete a MySQL database. So assuming you have access to the root user, you can create any database using MySQL.
Syntax : 

CREATE TABLE table name (column_name1 data type (size), column_name2 data_type(size), 
....... )

DROP COMMAND :

  • This is used to delete objects from the database. You would need special privileges to create or to delete a MySQL database. SIt is very easy to drop an existing MySQL table, but you need to be very careful while deleting any existing table because data lost will not be recovered after deleting a table.
Syntax :

DROP TABLE table_name;

ALTER COMMAND :

  • This is used to alter the structure of the database. ALTER command is very useful when you want to change the name of your table, any table field, or if you want to add or delete an existing column in a table. 
Syntax :

ALTER TABLE table_name ADD (newcolumn_name1 data_type(size), 
newcolumn_name2 data_type(size), .......)

TRUNCATE COMMAND :

  • This is used to remove all records from a table, including all spaces allocated for the records are removed.

COMMENT COMMAND :

  • This is used to add comments to the data dictionary.

RENAME COMMAND: 

  • This is used to rename an object existing in the database.

DML(Data-manipulation Language) :-

  • The SQL DML provides the ability to query information from the database and to insert tuples into, delete tuples from, and modify tuples in the database. The SQL commands that deals with the manipulation of data present in the database belong to DML or Data Manipulation Language and this includes most of the SQL statements.
DML commands are as follows :
  • SELECT COMMAND
  • INSERT COMMAND
  • UPDATE COMMAND
  • DELETE COMMAND

SELECT COMMAND :

  • This is used to retrieve data from the database. Select is the most commonly used statement in SQL. The SELECT Statement in SQL is used to retrieve or fetch data from a database. We can fetch either the entire table or according to some specified rules. The data returned is stored in a result table. This result table is also called the result-set.
  • The select command multiple clauses are used to display data in different manners. we have seen in deep in clause section.

INSERT COMMAND :

  • The INSERT command in SQL is used to add records to an existing table. is used to insert data into a table. To insert data into the MySQL table, you would need to use SQL INSERT INTO command. You can insert data into the MySQL table by using MySQL > prompt or by using any script like PHP.
Format 1:- Inserting a single row of data into a table

Syntax :

INSERT INTO table name [(column name, column name)] VALUES (expression, expression);

Format 2: Inserting data into a table from another table

Syntax :

INSERT INTO table name SELECT column name, column name FROM table name.

UPDATE COMMAND :

  • The UPDATE command can be used to modify the information contained within a table. There may be a requirement where existing data in a MySQL table needs to be modified. You can do so by using the SQL UPDATE command. This will modify any field value of any MySQL table.
Syntax :

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

DELETE COMMAND :

The DELETE command can be used to delete information contained within a table. If you want to delete a record from any MySQL table, then you can use SQL command DELETE FROM. You can use this command at mysql> prompt as well as in any script like PHP.

Syntax :

DELETE FROM table_name [WHERE Clause]

DCL (Data-Control Language):-

  • DCL includes commands such as GRANT and REVOKE which mainly deal with the rights, permissions, and other controls of the database system. Data Control Language(DCL) is used to control privileges in a Database. To perform any operation in the database, such as for creating tables, sequences, or views, a user needs privileges. Privileges are of two types:-
  • System: This includes permissions for creating sessions, tables, etc, and all types of other system privileges.
  • Object: This includes permissions for any command or query to perform any operation on the database tablesDCL commands are as follows :
    • GRANT COMMAND
    • REVOKE COMMAND

GRANT COMMAND : 

This is Used to provide any user access privileges or other privileges for the database.

REVOKE COMMAND :

This is Used to take back permissions from any user.

TCL (Transaction Control Language):-

  • Transaction Control Language(TCL) commands are used to manage transactions in the database. These are used to manage the changes made to the data in a table by DML statements. It also allows statements to be grouped together into logical transactions.
DCL commands are as follows :
  • COMMIT
  • ROLLBACK
  • SAVEPOINT

COMMIT COMMAND: 

  • The COMMIT command saves all the work done. When we use any DML command like INSERT, UPDATE, and DELETE the changes made by these commands are not permanent, until the current session is closed, the changes made by these commands can be rolled back.
  • It ends the current transaction and makes permanent changes during the transaction.
Syntax :

commit ;

ROLLBACK COMMAND : 

ROLLBACK command restores the database to the original since the last COMMIT. This command restores the database to the last committed state. It is also used with the savepoint command to jump to a savepoint in an ongoing transaction. It is used to restore the database to the last committed state.

Syntax :

ROLLBACK TO SAVEPOINT <savepoint_name>;

SAVEPOINT COMMAND:

SAVEPOINT command is used for saving all the current points in the processing of a transaction. the command is used to temporarily save a transaction so that you can roll back to that point whenever required.

Syntax :

SAVEPOINT savepoint_name;


Post a Comment