Understanding Database Commands: A Comprehensive Guide to DDL, DML, DCL, and TCL

Data Definition Language (DDL)

DDL is used to define or modify the structure of a database object, including creating, altering, and deleting database objects such as tables, schemas, and databases.

Key Operations:

• CREATE: Defines new database objects (tables, schemas, databases).
• ALTER: Modifies existing database objects (adding, removing columns or constraints).
• DROP: Deletes database objects permanently.
• TRUNCATE: Delete all the rows in table but table definition still remains.

Non-Reversible: DDL commands generally commit automatically, meaning changes are saved permanently without the possibility of rollback.

Schema Definition: DDL commands help in defining the structure and constraints that govern how data is stored and accessed.

CREATE Statements:

• Table

CREATE TABLE emp (
    id INT,
    name VARCHAR(50)
);

• Schema

CREATE SCHEMA demoSchema;

• Database

CREATE DATABASE demoDB;

ALTER Statements:

• Add a column

ALTER TABLE emp ADD COLUMN role VARCHAR(20);

• Drop a column

ALTER TABLE emp DROP COLUMN role;

• Modify a column

ALTER TABLE emp MODIFY COLUMN name VARCHAR(30);

• Rename column

ALTER TABLE emp RENAME COLUMN name TO emp_name;

• Add a constraint

ALTER TABLE emp ADD CONSTRAINT pk_key PRIMARY KEY (id);
ALTER TABLE emp ADD CONSTRAINT fk_key FOREIGN KEY (role, exp);

• Drop constraint

ALTER TABLE emp DROPCONSTRAINT pk_key;

# you can't able to delete the constraint specific to single column
ALTER TABLE emp ADD CONSTRAINT fk_key; 

• Rename constraint

ALTER TABLE emp RENAME CONSTRAINT pk_key TO primary_key;

DROP Statements

• Deletes the table entirely.

DROP TABLE emp;

TRUNCATE Statement

• Deletes only the rows but table and its structure remains same.

TRUNCATE TABLE emp;

Data Manipulation Language (DML)

DML is used to retrieve, insert, update, or delete data in a database. It deals with the manipulation of data in existing database structures.

Key Operations:

• SELECT: Retrieves data from tables based on certain conditions.
• INSERT: Adds new records into the database.
• UPDATE: Modifies existing data.
• DELETE: Removes data from the database (though not the structure itself).

Transactional: DML statements can be rolled back if wrapped in a transaction, meaning changes are not permanent until explicitly committed.

Core Interaction: DML commands allow interaction with the data in the database, making it essential for day-to-day operations.

SELECT Statement

SELECT * FROM emp;

SELECT id FROM emp;

SELECT id, name FROM emp;

INSERT Statement:

• Insert into a table:

INSERT INTO emp (id, name) VALUES (1, 'employee');

UPDATE Statement:

• Update single field

UPDATE emp SET role="Manager" WHERE id=10415;

• Update multiple fields

UPDATE emp SET role="Manager", exp=2 WHERE id=10415;

Data Control Language (DCL)

DCL is used to control access to data within the database, managing user permissions and access control.

Key Operations:

• GRANT: Gives a user or role permission to perform certain actions on a database object.
• REVOKE: Removes previously granted permissions.

Security Management: DCL ensures that only authorized users can perform specific actions, helping maintain data integrity and security.

User Rights: It enables administrators to enforce granular control over database access, like who can read, write, or alter data.

GRANT Statement

• Grant permissions to users

GRANT SELECT, INSERT ON emp TO "emp_122";

REVOKE Statement

• Revoke permissions from a user

REVOKE UPDATE ON emp FROM "emp_123";

Transaction Control Language (TCL)

TCL is used to manage and control transactions within a database, ensuring that they either succeed completely or fail entirely.

Key Operations:

• COMMIT: Saves all changes made in a transaction permanently.
• ROLLBACK: Undoes changes made within a transaction, reverting the database to its previous state.
• SAVEPOINT: Creates intermediate points within a transaction to rollback to, without affecting the whole transaction.

Atomicity: Ensures that a series of DML operations either complete fully or not at all, maintaining the database’s consistency.

Transaction Management: TCL commands provide mechanisms for handling multiple operations as one logical unit, preventing partial updates and maintaining data integrity.

COMMIT Statement

BEGIN;

UPDATE emp SET team="New Team" WHERE id=10415;

COMMIT;

ROLLBACK Statement

BEGIN;

UPDATE emp SET team="New Team" WHERE id=10415;

ROLLBACK;