In the realm of databases, SQL (Structured Query Language) serves as the lingua franca, enabling users to interact with data stored in various systems effectively. While SQL encompasses a wide array of commands, understanding the distinctions between Data Manipulation Language (DML), Data Definition Language (DDL), and Data Control Language (DCL) is fundamental for wielding this powerful tool with finesse.
DML, DDL, and DCL constitute the triad of SQL commands that form the backbone of database management. Each category serves distinct purposes, catering to different aspects of data manipulation, schema definition, and access control. Let’s delve deeper into each domain to grasp their significance and functionality within the SQL ecosystem.
DML:
DML stands for Data Manipulation Language. It is a subset of SQL used to perform operations on data stored in the database. DML commands enable users to retrieve, insert, update, and delete data from database tables. Here’s a breakdown of the operations:
INSERT: Used to insert new records into a table.
Syntax:
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
Example:
INSERT INTO employees (emp_id, emp_name, emp_salary) VALUES (101, 'John Doe', 50000);
SELECT: Used to retrieve data from one or more tables.
Syntax:
SELECT column1, column2, ... FROM table_name WHERE condition;
Example:
SELECT emp_name, emp_salary FROM employees WHERE emp_id = 101;
UPDATE: Used to modify existing records in a table.
Syntax:
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
Example:
UPDATE employees SET emp_salary = 55000 WHERE emp_id = 101;
DELETE: Used to remove one or more records from a table.
Syntax:
DELETE FROM table_name WHERE condition;
Example:
DELETE FROM employees WHERE emp_id = 101;
MERGE: Used to perform an “upsert” operation, which is a combination of INSERT and UPDATE operations based on a condition.
Syntax:
MERGE INTO target_table USING source_table ON condition WHEN MATCHED THEN UPDATE SET column1 = value1, column2 = value2, ... WHEN NOT MATCHED THEN INSERT (column1, column2, ...) VALUES (value1, value2, ...);
Example:
MERGE INTO target_table USING source_table ON target_table.id = source_table.id WHEN MATCHED THEN UPDATE SET target_table.value = source_table.value WHEN NOT MATCHED THEN INSERT (id, value) VALUES (source_table.id, source_table.value);
DML commands are essential for managing the contents of the database and are commonly used in conjunction with DDL and DCL commands to manipulate, define, and control the structure and access to the database.
DDL:
DDL stands for Data Definition Language. It is a subset of SQL used to define the structure and organization of the database objects. DDL commands are responsible for creating, modifying, and deleting database objects such as tables, indexes, views, and schemas.
CREATE TABLE: Used to create a new table in the database.
Syntax:
CREATE TABLE table_name (
column1 datatype constraints,
column2 datatype constraints,
...
); Example:
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(100),
emp_salary DECIMAL(10, 2)
); ALTER TABLE: Used to modify an existing table’s structure.
Syntax:
ALTER TABLE table_name
ADD column_name datatype constraints,
MODIFY column_name datatype constraints,
DROP COLUMN column_name; Example:
ALTER TABLE employees ADD emp_department VARCHAR(50), DROP COLUMN emp_salary;
DROP TABLE: Used to delete a table and all its data from the database.
Syntax:
DROP TABLE table_name;
Example:
DROP TABLE employees;
CREATE INDEX: Used to create an index on a table. Indexes improve the speed of data retrieval operations.
Syntax:
CREATE INDEX index_name ON table_name (column_name);
Example:
CREATE INDEX emp_name_index ON employees (emp_name);
DROP INDEX: Used to remove an index from a table.
Syntax:
DROP INDEX index_name;
Example:
DROP INDEX emp_name_index;
CREATE VIEW: Used to create a virtual table based on the result set of a SELECT query.
Syntax:
CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;
Example:
CREATE VIEW employee_view AS SELECT emp_id, emp_name FROM employees WHERE emp_department = 'IT';
DDL commands are crucial for database administrators and developers to manage the structure of the database and ensure data integrity. They are often used in combination with DML and DCL commands to complete database management tasks effectively.
DCL:
DCL stands for Data Control Language. It is a subset of SQL used to control access to data within a database. DCL commands are primarily concerned with defining and managing user privileges and permissions, ensuring data security and integrity.
GRANT: Used to grant specific privileges to a user or role.
Syntax:
GRANT privilege1, privilege2, ... ON object_name TO user_or_role;
Example:
GRANT SELECT, INSERT ON employees TO user1;
REVOKE: Used to revoke previously granted privileges from a user or role.
Syntax:
REVOKE privilege1, privilege2, ... ON object_name FROM user_or_role;
Example:
REVOKE SELECT, INSERT ON employees FROM user1;
COMMIT: Used to make permanent changes to the database since the last COMMIT or ROLLBACK statement.
Syntax:
COMMIT;
Example:
COMMIT;
ROLLBACK: Used to undo changes made in the current transaction and restore the database to its previous state.
Syntax:
ROLLBACK;
Example:
ROLLBACK;
SAVEPOINT: Used to set a named point within a transaction to which you can later roll back.
Syntax:
SAVEPOINT savepoint_name;
Example:
SAVEPOINT before_update;
SET TRANSACTION: Used to set properties for the current transaction.
Syntax:
SET TRANSACTION { ISOLATION LEVEL | READ WRITE | READ ONLY }; Example:
SET TRANSACTION READ WRITE;
DCL commands are essential for ensuring data security and controlling access to sensitive information within a database. They are often used in conjunction with DDL and DML commands to manage the overall security and integrity of the database.
