What is DDL and DML in SQL Database?

SQL is the standard language that database administrators (DBAs) use to manage databases. This article explains two key parts of SQL: Data Definition Language (DDL) and Data Manipulation Language (DML). We show how DDL commands like CREATE and ALTER define the structure of your database, while DML commands such as INSERT and UPDATE manage the data inside. By understanding DDL and DML, DBAs can efficiently set up and maintain databases, ensuring data is reliable and accessible.

1. Introduction (background and overview)

SQL, or Structured Query Language, is the standard language we use to talk to databases. Think of it as the language that lets you create, change, and get information from a database. SQL is super important for database administrators (DBAs) and developers because it helps them manage and work with data effectively.

I. The Importance of SQL

SQL is the key to unlocking the power of relational databases. Without it, we wouldn’t be able to easily store, organize, and retrieve the massive amounts of data that power websites, apps, and businesses today. SQL allows us to:

• Create databases and tables
• Add, update, and delete data
• Find specific information quickly

II. Introducing DDL and DML

SQL is divided into different parts, and two important parts are DDL (Data Definition Language) and DML (Data Manipulation Language).

• DDL is like the architect of your database. It allows you to define the structure of your database, like creating tables and setting up their columns.
• DML is how you work with the data inside the database. It allows you to add new data, change existing data, and remove data you no longer need.

III. Purpose of this Article

This article will help you understand what DDL and DML are, what commands they use, and how they are used in database management. We will explain everything in a way that is easy to understand, even if you are new to databases. 🎯

IV. Other SQL Subsets

While we’re focusing on DDL and DML, it’s good to know that SQL has other parts too, like:

• DCL (Data Control Language): Controls access and permissions to the database.
• DQL (Data Query Language): Used for retrieving data from the database (using the SELECT statement).

We will focus primarily on DDL and DML in this article, as they are fundamental to database administration.

2. Understanding Data Definition Language (DDL)

Data Definition Language, or DDL, is a set of SQL commands that we use to define the structure of a database. Think of it as the blueprint for your database – it tells the database what tables exist, what columns each table has, and how those tables relate to each other. DDL focuses on describing the data, not manipulating it directly.

I. Define DDL

DDL is all about defining the schema, or the overall design, of your database. It lets you create, change, and delete database objects. These objects include things like tables, indexes, views, and other structures that hold your data. Without DDL, your database wouldn’t know how to store or organize information.

II. Key DDL Commands

DDL includes several important commands that are essential for managing your database structure. Let’s look at some of the most common ones:

I. CREATE

The CREATE command is used to make new database objects. You can use it to create tables, indexes, views, stored procedures, and triggers.

Example: Creating a Table

Let’s create a simple table called “Customers” with columns for ID, Name, and City.

1
2
3
4
5

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(255),
    City VARCHAR(255)
);

In this example:

• CREATE TABLE Customers tells the database we want to create a new table named “Customers”.
• CustomerID INT PRIMARY KEY creates a column named “CustomerID” that stores whole numbers (INT) and is the main identifier (PRIMARY KEY) for each customer.
• Name VARCHAR(255) creates a column named “Name” that stores text up to 255 characters long.
• City VARCHAR(255) creates a column named “City” that also stores text up to 255 characters long.

II. ALTER

The ALTER command is used to change existing database objects. You can add, modify, or delete columns from a table, change data types, or add constraints.

Example: Adding a Column

Let’s add a new column called “Email” to the “Customers” table.

1
2

ALTER TABLE Customers
ADD Email VARCHAR(255);

Example: Modifying a Column

Let’s change the data type of the “City” column to allow longer city names.

1
2

ALTER TABLE Customers
ALTER COLUMN City VARCHAR(500);

Example: Adding a Constraint

Constraints are rules that you can set on your data to ensure its accuracy and reliability. Let’s add a NOT NULL constraint to the “Name” column, which means that every customer must have a name.

1
2

ALTER TABLE Customers
ALTER COLUMN Name VARCHAR(255) NOT NULL;

III. DROP

The DROP command is used to delete database objects. This is a powerful command, so be careful! Once you drop an object, it’s gone (unless you have a backup).

⚠️ Important: Dropping a table permanently removes the table and all its data.

Example: Dropping a Table

1

DROP TABLE Customers;

This command will delete the entire “Customers” table.

IV. TRUNCATE

The TRUNCATE command is used to remove all data from a table, but it keeps the table structure intact. This is faster than deleting all rows using a DELETE statement (which we’ll discuss in the DML section).

Example: Truncating a Table

1

TRUNCATE TABLE Customers;

This command will remove all the data from the “Customers” table, but the table itself will still exist with its columns.

III. Importance of DDL

DDL commands are super important for setting up your database and keeping it organized. They help DBAs and developers define the structure of their data, ensuring that it is stored correctly and efficiently. DDL is used during database setup and maintenance, helping DBAs define tables, indexes, and schemas that keep data organized and reliable. It’s the foundation upon which all other database operations are built.

3. Understanding Data Manipulation Language (DML)

Data Manipulation Language, or DML, is the set of SQL commands that allow you to work with the data stored inside the database structures you created with DDL. While DDL builds the house, DML is what you use to furnish it!

I. Define DML

DML stands for Data Manipulation Language. These commands work with the actual data stored inside the database structure you created with DDL. Think of DML as the tools you use to add, change, and remove information from your database tables. DML focuses on manipulating the data itself, not the structure of the database.

II. Key DML Commands

Here are the main DML commands you’ll use:

• INSERT
• UPDATE
• DELETE
• SELECT (We’ll explain why SELECT is included even though it’s technically DQL)

I. INSERT: Adding New Data

The INSERT command adds new rows of data into a table.

Example: Let’s say you have a table called Customers with columns for CustomerID, FirstName, and LastName.

To add a new customer, you would use the following SQL command:

1
2

INSERT INTO Customers (CustomerID, FirstName, LastName)
VALUES (1, 'Jane', 'Doe');

This command adds a new row to the Customers table with CustomerID as 1, FirstName as ‘Jane’, and LastName as ‘Doe’.

You can also insert multiple rows at once:

1
2
3
4

INSERT INTO Customers (CustomerID, FirstName, LastName)
VALUES
(2, 'John', 'Smith'),
(3, 'Alice', 'Brown');

This adds two new customers: John Smith and Alice Brown.

II. UPDATE: Changing Existing Data

The UPDATE command modifies existing data in a table. ⚠️ It’s very important to use the WHERE clause with UPDATE to specify which rows you want to change. Otherwise, you might accidentally update all the rows in the table!

Example: Let’s say Jane Doe got married and changed her last name to Williams. To update her record, you would use the following SQL command:

1
2
3

UPDATE Customers
SET LastName = 'Williams'
WHERE CustomerID = 1;

This command changes the LastName of the customer with CustomerID 1 to ‘Williams’.

III. DELETE: Removing Data

The DELETE command removes rows from a table. ⚠️ Just like with UPDATE, it’s very important to use the WHERE clause with DELETE to specify which rows you want to remove. Otherwise, you might accidentally delete all the rows in the table!

Example: Let’s say you want to remove the customer with CustomerID 3. You would use the following SQL command:

1
2

DELETE FROM Customers
WHERE CustomerID = 3;

This command removes the row with CustomerID 3 from the Customers table.

IV. SELECT: Retrieving Data (and Its Connection to DML)

SELECT is technically part of Data Query Language (DQL), but it’s closely related to DML. You often use SELECT after using INSERT, UPDATE, or DELETE to verify that your changes were made correctly.

Example: After inserting a new customer, you might use SELECT to view the contents of the Customers table:

1

SELECT * FROM Customers;

This command displays all columns and rows from the Customers table, allowing you to confirm that the new customer was added successfully. Similarly, you can use SELECT after an UPDATE or DELETE command to confirm the changes.

III. Importance of DML

🎯 DML commands are essential for managing and maintaining the data within your database. They allow you to add new information, modify existing information, and remove outdated or incorrect information. Without DML, your database would be a static and unchanging collection of data. DML is what makes your database dynamic and useful.

4. DDL vs. DML: Key Differences and Use Cases

Now that you understand DDL and DML individually, let’s compare them directly and see how they are used in real-world situations.

I. Scope: Structure vs. Data

The biggest difference between DDL and DML is their scope. 💡 DDL deals with the structure of the database. It’s about defining what data can be stored and how it’s organized. DML, on the other hand, is all about the data itself. It allows you to add, change, and remove the actual information stored in the database.

Think of it this way:

II. User Roles: Who Uses Which?

DDL commands are usually used by database administrators (DBAs) or developers who have specific permissions. ⚠️ This is because changing the database structure can have a big impact on the entire system. You don’t want just anyone changing the blueprints!

DML commands, however, are more commonly used by application users or developers who need to access and modify data. For example, an application might use DML to add a new customer to the database or update an existing customer’s information.

III. Transactionality: Handling Errors

DML operations are usually transactional. This means that a series of DML commands are treated as a single unit of work. If any command in the series fails, the entire transaction can be rolled back, undoing all the changes. This helps to keep your data consistent and reliable.

Think of ACID properties:

• Atomicity: All operations in a transaction succeed or fail as a single unit.
• Consistency: The transaction maintains the integrity of the database.
• Isolation: Transactions are isolated from each other.
• Durability: Once a transaction is committed, the changes are permanent.

DDL operations are often auto-committed, meaning that changes are immediately saved and less easily undone. While some database systems offer some rollback capabilities for DDL, it’s generally more complex and might not always be possible. Therefore, it’s crucial to be careful when using DDL commands.

IV. Examples of Use Cases

Let’s look at some practical examples of when you would use DDL and DML:

DDL Use Cases:

• Creating a new table to store customer information: CREATE TABLE Customers (CustomerID INT, Name VARCHAR(255));
• Adding an index to improve query performance: CREATE INDEX idx_name ON Customers (Name);
• Modifying a column’s data type: ALTER TABLE Customers ALTER COLUMN CustomerID BIGINT;

DML Use Cases:

• Adding a new customer to the customer table: INSERT INTO Customers (CustomerID, Name) VALUES (1, 'John Doe');
• Updating a customer’s address: UPDATE Customers SET Name = 'Jane Doe' WHERE CustomerID = 1;
• Deleting a customer from the table: DELETE FROM Customers WHERE CustomerID = 1;

🎯 In summary, DDL defines the structure, and DML manipulates the data. Understanding the difference is key to managing your database effectively.

Recommended reading

• SQL Optimization Course
• SQL optimization knowledge
• Database industry knowledge

What is SQLFlash?

SQLFlash is your AI-powered SQL Optimization Partner.

Based on AI models, we accurately identify SQL performance bottlenecks and optimize query performance, freeing you from the cumbersome SQL tuning process so you can fully focus on developing and implementing business logic.

How to use SQLFlash in a database?

• SQLFlash + MySQL

• SQLFlash + PostgreSQL