DDL, DML and DCL in MySQL

Introduction

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

What is 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 is 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 that are removed
• COMMENT:add comments to the data dictionary
• RENAME:rename an object

CREATE TABLE

Syntax

CREATE TABLE table_name(
Col_name1 datatype(),
Col_name2 datatype(),…
Col_namen datatype(),
);

Example

;Here, we are creating a sample table.

CREATE TABLE DDL  
(  
     id int,  
     DDL_Type varchar(50),  
     DDL_Value int  
);  

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.

ALTER TABLE DDL  
ADD COLUMN DDL_Example varchar(50);

  

2. Modify

Syntax

ALTER TABLE table_name
MODIFY (fieldname datatype()...);

Example

 Modify a datatype in an existing table.

ALTER TABLE DDL  
MODIFY DDL_Example BIGINT;

  

 3. Describe Table

Syntax

DESCRIBE TABLE NAME;

Example

 This query is used to view the table.

DESCRIBE DDL

DROP TABLE

Syntax

DROP Table name;

Example

 Used to drop a table.

DROP TABLE DDL

COMMENT

Add comments to the data dictionary

RENAME

Rename a table

Syntax

RENAME table table_name to new table_name

What is DML?

Data Manipulation Language (DML) statements are used for managing data within schema objects. DML deals with data manipulation and therefore includes the 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

• INSERT
• SELECT
• UPDATE
• DELETE

INSERT

Syntax

INSERT INTO Table_Name VALUES();

Example

Here, we are going to insert some values.

INSERT INTO DDL (id, DDL_Type, DDL_Value) VALUES  
(2, 'DML', 123),  
(3, 'DCL', 123);  

SELECT

Syntax

SELECT * FROM <table_name>

Example

 select query is used to fetch the data from tables.

SELECT * FROM DDL  

UPDATE

Syntax

UPDATE <table name> set to(calculation);

Example

 The update command is used to update any value from any table.

UPDATE ddl  
SET DDL_Value = 555 WHERE DDL_Type = 'DML';

  

DELETE

Syntax

DELETE FROM <table_name>

Example

 Delete query is used to delete a row from a table.

DELETE FROM DDL  
Where id = 2  

What is 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.

•  GRANT
•  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 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

CREATE USER vatsa@'localhost' IDENTIFIED BY 'mypass';  
GRANT ALL ON MY_TABLE TO vatsa@'localhost';  
GRANT SELECT ON Users TO vatsa@'localhost';  

REVOKE

The REVOKE statement enables system administrators 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

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!