DDL (Data Definition Language) in SQL
Introduction
In SQL, commands are divided into different categories based on their purpose. One important category is Data Definition Language (DDL).
DDL commands are used to define and manage the structure of a database. They allow users to create, modify, and delete database objects such as tables, databases, indexes, and schemas.
Unlike other SQL commands that work with data inside tables, DDL commands focus on the structure or design of the database.
These commands are commonly used when creating a new database system or modifying the structure of existing tables.
Common DDL Commands
Several SQL commands fall under the Data Definition Language category.
The most commonly used DDL commands include:
CREATE
ALTER
DROP
TRUNCATE
Each command serves a different purpose in managing the database structure.
CREATE Command
The CREATE command is used to create new database objects, such as tables or databases.
For example, when designing a database, you first create tables that will store the data.
Example:
CREATE TABLE Students ( id INT PRIMARY KEY, name VARCHAR(50), age INT ); This command creates a table named Students with three columns:
id
name
age
Each column defines the type of data that can be stored in that field.
ALTER Command
The ALTER command is used to modify the structure of an existing table.
It allows you to:
Add new columns
Modify existing columns
Delete columns
Example:
ALTER TABLE Students ADD email VARCHAR(100); This command adds a new column called email to the Students table.
DROP Command
The DROP command is used to permanently delete a database object,t such as a table or database.
Example:
DROP TABLE Students; This command removes the Students table completely, including all data stored in it.
Important:
Once a table is dropped, it cannot be recovered unless a backup exists.
TRUNCATE Command
The TRUNCATE command removes all records from a table but keeps the table structure intact.
Example:
TRUNCATE TABLE Students; After running this command:
All rows in the Students table are deleted
The table structure remains the same
This command is usually faster than DELETE because it removes all rows at once.
Characteristics of DDL Commands
DDL commands have some important characteristics:
They define and modify the database structure
They operate on tables, schemas, and indexes
Most DDL operations are auto-committed
They are mainly used during database design and maintenance
Example Scenario
Suppose you are building a student management system.
You might use DDL commands to:
Create the Students table
Add new columns like email or phone number
Remove unnecessary tables
Clear table data when resetting the system
These tasks are all performed using DDL commands.
Key Points to Remember
DDL stands for Data Definition Language
It is used to define and modify the database structure
Common commands include CREATE, ALTER, DROP, and TRUNCATE
These commands work on database objects rather than the data itself
Understanding DDL is essential because it forms the foundation of database design.