DDL, DML and DCL in MySQL

DDL- Data Definition Language (DDL) statements are used to define the database structure or schema. Data Definition Language understanding with database schemas and describes how the data should consist in the database, therefore language statements like CREATE TABLE or ALTER TABLE belongs to the DDL. DDL is about "metadata".

DDL includes commands such as CREATE, ALTER and DROP statements.DDL is used to CREATE, ALTER OR DROP the database objects (Table, Views, Users).

 Data Definition Language (DDL) are used different statements :

• CREATE - to create objects in the database
• ALTER - alters the structure of the database
• DROP - delete objects from the database
• TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
• COMMENT - add comments to the data dictionary
• RENAME - rename an object

CREATE TABLE
Syntax: Create table table name( fieldname1 datatype(),fieldname2 datatype()...);

ALTER TABLE
1. ADD
2.MODIFY

ADD
Syntax:alter table table name  ADD (fieldname datatype()...);

modify
syntax: Alter table table name modify (fieldname datatype()...);

DESCRIBE TABLE
Syntax: DESCRIBE TABLE NAME;

DROP TABLE
Syntax: DROP Table name;

COMMENT - add comments to the data dictionary

RENAME - rename a table
Synatax: rename table table name to new table name

Examples : In this example we creates a table and insert the values.

Example : In the following figure shows the alter a table .

For Example : In this figure shows the describe command.

 

 

DML- Data Manipulation Language (DML) statements are used for managing data within schema objects DML deals with data manipulation, and therefore includes most common SQL statements such SELECT, INSERT, etc. DML allows to add / modify / delete data itself.
DML is used to manipulate with the existing data in the database objects (insert, select, update, delete).

DML Commands:
1.INSERT
2.SELECT
3.UPDATE
4.DELETE

*INSERT:
Syntax: INSERT INTO Table name values();

*SELECT:
Syntax: Select*from <table name>

*UPDATE:
Syntax: Update<table name> set to(calculation);

*DELETE:
Syntax: Delete form<table name>

 

Example : In the below figure shows the update and delete command on the given table.



DCL- DCL is the abstract of Data Control Language. Data Control Language includes commands such as GRANT, and concerns with rights, permissions and other controls of the database system. DCL is used to grant / revoke permissions on databases and their contents. DCL is simple, but MySQL permissions are a bit complex. DCL is about security. DCL is used to control the database transaction.DCL statement allow you to control who has access to specific object in your database.

1. GRANT
2. REVOKE

GRANT :It provides the  user's access privileges to the database. In the MySQL database offers both the administrator and user a great extent of the control options. By the administration side of the process includes the possibility for the administrators to control certain user privileges over the MySQL server by restricting their access to an entire the database or ust limiting permissions for a specific table.It Creates an entry in the security system that
allows a user in the current database to work with data in the current database or execute specific statements.

Syntax :
Statement permissions:

GRANT { ALL | statement [ ,...n ] }
TO security_account [ ,...n ]

Normally, a database administrator first uses CREATE USER to create an account, then GRANT to define its privileges and characteristics.

For example:
CREATE USER 'arjun'@'localhost' IDENTIFIED BY 'mypass';

GRANT ALL ON db1.* TO 'arjun'@'localhost';

GRANT SELECT ON child TO 'arjun'@'localhost';

GRANT USAGE ON *.* TO 'arjun'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;

REVOKE : The REVOKE statement enables system administrators and to revoke the privileges from MySQL accounts.

Syntax : REVOKE
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
FROM user [, user] ...

REVOKE ALL PRIVILEGES, GRANT OPTION
FROM user [, user] ...

For example:

mysql> REVOKE INSERT ON *.* FROM 'arjun'@'localhost';