DDL, DML and DCL in MySQL

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

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

ALTER TABLE table_name
ADD Col_name datatype()...;

ALTER TABLE DDL  
ADD COLUMN DDL_Example varchar(50);

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

ALTER TABLE DDL  
MODIFY DDL_Example BIGINT;

DESCRIBE TABLE NAME;

DROP Table name;

RENAME table table_name to new table_name

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!