DML, TCL, DDL, DLC

 

 

 

 

 

 

1. Data Manipulation Language (DML):

   - DML is used to manipulate data in a database, including querying, inserting, updating, and deleting data.
   - Common DML commands include SELECT, INSERT, UPDATE, and DELETE.
   - It allows users to interact with and manage data in the database.
   - For example, the SELECT statement retrieves data from tables, INSERT inserts new records into a table, UPDATE modifies existing records, and DELETE removes records from a table.

 

-- SELECT statement to retrieve data
SELECT * FROM table_name;

-- INSERT statement to insert new records
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

-- UPDATE statement to modify existing records
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

-- DELETE statement to delete records
DELETE FROM table_name
WHERE condition;

 

2. Transaction Control Language (TCL):

   - TCL is used to control transactions in a database.
   - Key TCL commands include COMMIT and ROLLBACK.
   - COMMIT commits all changes made in a transaction to the database permanently, while ROLLBACK cancels the changes and restores the database to its previous state.
   - It supports the logical unit of work in a transaction to maintain the consistency of the database.

 

-- COMMIT statement to commit changes
COMMIT;

-- ROLLBACK statement to rollback changes
ROLLBACK;

3. Data Definition Language (DDL):

   - DDL is used to define and manage the schema of a database.
   - Major DDL commands include CREATE, ALTER, and DROP.
   - CREATE statement creates new database objects such as tables, indexes, views, etc.
   - ALTER statement modifies the structure of tables (adding, deleting, or modifying columns, etc.).
   - DROP statement deletes database objects from the database.

-- CREATE statement to create new database objects
CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
);

-- ALTER statement to modify database objects
ALTER TABLE table_name
ADD column_name datatype;

-- DROP statement to delete database objects
DROP TABLE table_name;

4. Data Control Language (DCL):

   - DCL is used to manage access permissions in a database.
   - Key DCL commands include GRANT and REVOKE.
   - GRANT statement grants users specific privileges on database objects (tables, views, etc.).
   - REVOKE statement revokes previously granted privileges from users.

These languages play important roles in efficiently managing a database system and securely manipulating data.

 

-- GRANT statement to grant permissions
GRANT SELECT, INSERT ON table_name TO user_name;

-- REVOKE statement to revoke permissions
REVOKE SELECT, INSERT ON table_name FROM user_name;