DDL, DML and DCL in MySQL

INTRODUCTION

 
In this tutorial, I am going to explain about DDL, DML, and DCL in MySQL with examples. Without wasting time, let’s start.
 

DDL

 
Data Definition Language (DDL) statements are used to define the database structure or schema. Data Definition Language  describes how the data should exist in the database, therefore language statements like CREATE TABLE or ALTER TABLE belong to the DDL. DDL is about "metadata".
 
DDL includes commands such as CREATE, ALTER, and DROP statements.DDL are used to CREATE, ALTER, OR DROP the database objects (Table, Views, Users).
 
Data Definition Language (DDL) is used in 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

A) CREATE TABLE

 
Syntax:
CREATE TABLE table_name(
Col_name1 datatype(),
Col_name2 datatype(),…
Col_namen datatype(),
);
 
Example: Here, we are creating a sample table.
  1. CREATE TABLE DDL  
  2. (  
  3.      id int,  
  4.      DDL_Type varchar(50),  
  5.      DDL_Value int  
  6. );  
1
 

B) ALTER TABLE

 

1) ADD

 
Syntax:
ALTER TABLE table_name
ADD Col_name datatype()...;
 
Example: Here, we are adding a new column to the existing table.
  1. ALTER TABLE DDL  
  2. ADD COLUMN DDL_Example varchar(50);  
2
 

2) MODIFY

 
Syntax:
ALTER TABLE table_name
MODIFY (fieldname datatype()...);
 
Example: Modify a datatype in an existing table.
  1. ALTER TABLE DDL  
  2. MODIFY DDL_Example BIGINT;  
3
 

C) DESCRIBE TABLE

 
Syntax:
DESCRIBE TABLE NAME;
 
Example: This query is used to view the table.
  1. DESCRIBE DDL;   
4
 

D) DROP TABLE

 
Syntax:
DROP Table name;
 
Example: Used to drop a table.
  1. DROP TABLE DDL;  
5
 

E) COMMENT

 
Add comments to the data dictionary
 

F) RENAME

 
Rename a table
 
Syntax:
RENAME table table_name to new table_name
 

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 as SELECT, INSERT, etc. DML allows adding / modifying / deleting data itself.
 
DML is used to manipulate the existing data in the database objects (insert, select, update, delete).
 
DML Commands
 
1.INSERT
2.SELECT
3.UPDATE
4.DELETE
 

1) INSERT

 
Syntax:
INSERT INTO Table_Name VALUES();
 
Example: Here, we are going to insert some values.
  1. INSERT INTO DDL (id, DDL_Type, DDL_Value) VALUES  
  2. (2, 'DML', 123),  
  3. (3, 'DCL', 123);  
6
 

2) SELECT

 
Syntax:
SELECT * FROM <table_name>
 
Example: select query is used to fetch the data from tables.
  1. SELECT * FROM DDL  
7
 

3) UPDATE

 
Syntax:
UPDATE <table name> set to(calculation);
 
Example: Update command is used to update any value from any table.
  1. UPDATE ddl  
  2. SET DDL_Value = 555 WHERE DDL_Type = 'DML';  
8
 

4) DELETE

 
Syntax:
DELETE FROM <table_name>
 
Example: Delete query is used to delete a row from a table.
  1. DELETE FROM DDL  
  2. Where id = 2  
9
 

DCL

 
DCL is the abstract of Data Control Language. Data Control Language includes commands such as GRANT, and is concerned 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 statements allow you to control who has access to a specific object in your database.
1. GRANT
2. REVOKE
 

GRANT

 
It provides the user's access privileges to the database. The MySQL database offers both the administrator and user a great extent of the control options. 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 database or usage 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:
  1. CREATE USER vatsa@'localhost' IDENTIFIED BY 'mypass';  
  2. GRANT ALL ON MY_TABLE TO vatsa@'localhost';  
  3. GRANT SELECT ON Users TO vatsa@'localhost';  

REVOKE

 
The REVOKE statement enables system administrators and to revoke (back permission) 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:
  1. REVOKE INSERT ON *.* FROM 'vatsa'@'localhost';  
 

CONCLUSION

 
In this article, I have discussed the concept of DDL, DML, and DCL in MySQL with various examples.
 
I hope you enjoyed this article. Follow C# Corner to learn more new and amazing things about MySQL.
 
Thanks for reading this article!