KB:SQL DDL/DML

DDL (Data Definition Language) provides the ability to define, create and modify database objects such as tables, views, indexes, and users. DML (Data Manipulation Language) allows for manipulating data in a database, such as inserting, updating, and deleting records.

In SQL, DDL (Data Definition Language) and DML (Data Manipulation Language) are two categories of SQL commands used for different purposes:

Data Definition Language (DDL)

DDL commands are used to define and manage database schema, which includes creating, altering, and deleting database objects such as tables, indexes, and views. Common DDL commands include:

1. CREATE: Used to create database objects like tables, indexes, views, etc.

   • Example:

     CREATE TABLE Employees (
         EmployeeID int PRIMARY KEY,
         FirstName varchar(255),
         LastName varchar(255),
         BirthDate date
     );
     

2. ALTER: Used to modify an existing database object.

   • Example:

     ALTER TABLE Employees ADD COLUMN Salary decimal(10, 2);
     

3. DROP: Used to delete database objects.

   • Example:

     DROP TABLE Employees;

4. TRUNCATE: Removes all records from a table but doesn't delete the table itself.

   • Example:

     TRUNCATE TABLE Employees;

Data Manipulation Language (DML)

DML commands are used for managing data within schema objects, including retrieving, inserting, updating, and deleting data. Common DML commands include:

1. SELECT: Used to retrieve data from the database.

   • Example:

     SELECT FirstName, LastName FROM Employees WHERE EmployeeID = 1;

2. INSERT: Used to add new records to a table.

   • Example:

     INSERT INTO Employees (EmployeeID, FirstName, LastName, BirthDate)
     VALUES (1, 'John', 'Doe', '1980-01-01');
     

3. UPDATE: Used to modify existing records in a table.

   • Example:

     UPDATE Employees SET Salary = 60000 WHERE EmployeeID = 1;

4. DELETE: Used to remove records from a table.

   • Example:

     DELETE FROM Employees WHERE EmployeeID = 1;

Key Differences

• DDL commands are typically used to create and modify the structure of database objects.
• DML commands are used to manage the data within those objects.

DDL changes the schema structure itself and is usually not rolled back (though this depends on the DBMS), whereas DML operations can usually be rolled back if needed.