In database management, DDL (Data Definition Language) and DML (Data Manipulation Language) are two main categories of SQL commands that serve different purposes:
DDL (Data Definition Language)
DDL commands are used to define, modify, or manage the structure of database objects like tables, indexes, and schemas. These commands typically affect the database schema.
CREATE: Used to create new objects in the database (e.g., tables, views, indexes).
Example:
CREATE TABLE employees (id INT, name VARCHAR(50));
ALTER: Used to modify an existing database object (e.g., adding a column to a table).
Example:
ALTER TABLE employees ADD COLUMN salary DECIMAL(10,2);
DROP: Removes database objects like tables, views, or indexes. Once dropped, the data and structure are permanently deleted.
Example:
DROP TABLE employees;
TRUNCATE: Deletes all rows from a table but preserves its structure. It’s faster than DELETE because it doesn’t log individual row deletions.
Example:
TRUNCATE TABLE employees;
COMMENT: Adds descriptive comments to database objects for documentation purposes.
Example:
COMMENT ON COLUMN employees.salary IS 'Employee salary in USD';
RENAME: Renames database objects like tables or columns.
Example:
ALTER TABLE employees RENAME TO staff;
DML (Data Manipulation Language)
DML commands are used to retrieve, insert, update, or delete data within database objects (typically tables). They work directly with the data.
SELECT: Retrieves data from the database based on certain conditions.
Example:
SELECT name, salary FROM employees WHERE salary > 50000;
INSERT: Adds new data into a table.
Example:
INSERT INTO employees (id, name, salary) VALUES (1, 'Alice', 60000);
UPDATE: Modifies existing data in a table based on specified criteria.
Example:
UPDATE employees SET salary = 65000 WHERE name = 'Alice';
DELETE: Removes data from a table based on conditions.
Example:
DELETE FROM employees WHERE name = 'Alice';
In summary:
DDL commands modify the structure of the database.
DML commands interact with and manipulate the data within the structure.
Let me know your thoughts on the above, if you have any!