DDL, DML and DCL in MySQL

Emmanuel

Administrator
Staff member
May 11, 2020
141
28
84
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:
SQL:
Please, Log in or Register to view codes content!
Example: Here, we are creating a sample table.

Code:
Please, Log in or Register to view codes content!
1


B) ALTER TABLE

1) ADD

Syntax
:
Code:
Please, Log in or Register to view codes content!
Example: Here, we are adding a new column to the existing table.

Code:
Please, Log in or Register to view codes content!
2


2) MODIFY

Syntax:

Code:
Please, Log in or Register to view codes content!
Example: Modify a datatype in an existing table.
Code:
Please, Log in or Register to view codes content!
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:
Code:
Please, Log in or Register to view codes content!
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.
Code:
Please, Log in or Register to view codes content!
6


2) SELECT

Syntax:
Code:
Please, Log in or Register to view codes content!
Example: select query is used to fetch the data from tables.

  1. SELECT * FROM DDL
7


3) UPDATE

Syntax:


Example:
Update command is used to update any value from any table.
Code:
Please, Log in or Register to view codes content!
8


4) DELETE

Syntax:
DELETE FROM <table_name>

Example: Delete query is used to delete a row from a table.
Code:
Please, Log in or Register to view codes content!
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 :
Code:
Please, Log in or Register to view codes content!
Normally, a database administrator first uses CREATE USER to create an account, then GRANT to define its privileges and characteristics.

For example:
Code:
Please, Log in or Register to view codes content!
REVOKE

The REVOKE statement enables system administrators and to revoke (back permission) the privileges from MySQL accounts.

Syntax:
Code:
Please, Log in or Register to view codes content!
For example:

Code:
Please, Log in or Register to view codes content!
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!
Code:
Please, Log in or Register to view codes content!