Understanding SQL Commands: Exploring DDL, DML, DCL, and TCL Operations

SQL, which stands for Structured Query Language, is a tool used to manage and manipulate data in a database. MySQL, on the other hand, is a relational database management system (RDBMS) that uses SQL to interact with large and customizable databases.

DATA DEFINATION LANGUAGE

• CREATE
• ALTER
• DROP
• TRUNCATE

1. CREATE – It is used to create a new table in the database.
Syntax:
CREATE TABLE TableName (
Column1 datatype,
Column2 datatype,
Column3 datatype,
ColumnN datatype
);

3. DROP – It is used to delete both the structure and record in the table, this SQL command will remove the table structure along with its data from the database.
Syntax : DROP TABLE TableName;

4. TRUNCATE – This truncate command is used to delete all the rows from the table and free the space.
Syntax : TRUNCATE TABLE TableName;

Data Manipulation Language
Data Manipulation Language (DML) is the language that gives users the ability to access or manipulate the condition that the appropriate data model has inherited.

Here are some SQL commands that come under DML:

• INSERT
• UPDATE
• DELETE

1. INSERT – INSERT command is used to insert new rows or records in a table.
Syntax : INSERT INTO TABLE_NAME (column1, column2, column3,…columnN)
VALUES (value1, value2, value3,…valueN);

2. UPDATE – This command is used to update or modify the value of a column in the table.
Syntax : UPDATE table_name
SET column1 = value1, column2 = value2…., columnN = valueN
WHERE [condition];

3. DELETE – DELETE is used for removing one or more rows from the table.
Syntax : DELETE FROM TableName
WHERE Condition;

Data Control Language
Data Control Language is used to manage roles, permissions, and referential integrity on the database.

Here are some commands that come under DCL:

• GRANT
• REVOKE

1. GRANT – GRANT command is used to give access or permission to specific users.
Syntax : GRANT object_privileges ON table_name TO user_name1;

2. REVOKE – REVOKE is used for taking back permission, which is given to the user.
Syntax : REVOKE object_privileges ON table_name FROM user1, user2,… userN;

Transaction Control Language
TCL manages the issues and matters related to the transactions in any database. They are used to rollback or commit the changes in the database.

Here are some commands that come under TCL:

• COMMIT
• ROLLBACK

1. COMMIT – The COMMIT command is used to save all the transactions to the database.
   Syntax : COMMIT;

2. ROLLBACK – The rollback command is used to undo transactions that have not already been saved to the database.
Syntax: ROLLBACK;