DDL
SQL

SQL DDL: The Definitive Guide on Data Definition Language

intro

Let’s learn how to use DDL statements to change the structure of a database.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT

Adding new tables, indexes, and triggers and updating existing ones are all common operations performed by database administrators. These tasks impact the database schema, and the queries behind them belong to SQL DDL. But what does that mean?

In this guide, you will see what DDL stands for, how it is related to other SQL languages, and how to use it to manipulate database objects.

Let's dive in!

What Is DDL In SQL?

In SQL, DDL stands for “Data Definition Language” and represents the syntax for describing the database schema. In detail, DDL consists of SQL commands to create and update database objects, such as tables, indexes, views, stored procedures, and triggers.

The DDL commands in SQL are:

  • CREATE: To add a new object to the database.
  • ALTER: To change the structure of the database.
  • DROP: To remove an existing object from the database. Check out our guide on how to delete a column in SQL.
  • TRUNCATE: To remove all records from a table, including the space allocated to store this data.

Note that SQL dialects may have some unique DDL commands. For example, MySQL has the RENAME statement to change the name of existing tables. Similarly, PostgreSQL and Oracle offer the COMMENT command to add or update a comment about a database object.

As you can notice, all DDL SQL statement allows you to change the database structure, but not the data. To add or update data from a database, you need to the CRUD commands offered by DML (Data Manipulation Language).

SQL DDL vs DML vs DQL vs DCL vs TCL

The SQL commands can be categorized into the following categories:

  • DDL (Data Definition Language): Commands like CREATE, ALTER, and DROP to define and manage the structure of a database, such as tables, indexes, and other objects. This also includes the constraints set to ensure data integrity.
  • DML (Data Manipulation Language): Commands like INSERT, UPDATE, and DELETE to manipulate data within the database.
  • DQL (Data Query Language): The primary command is SELECT, which is used to retrieve data from the database based on specified criteria and conditions.
  • DCL (Data Control Language): Commands like GRANT and REVOKE to control access and permissions in the database.
  • TCL (Transaction Control Language): Commands like COMMIT and ROLLBACK to manage database transactions.

Now that you know the different SQL languages available and their differences, it is time to delve deeper into the SQL DDL commands!

SQL DDL Commands

In this section, you will explore the DDL statements in SQL: CREATE, ALTER, DROP, and TRUNCATE. For each command, you will see details on its syntax, how to use it, and a real-world example. Since DDL commands modify the structure of a database, it is essential to have a tool that simplifies the process of exploring their effects. This is where DbVisualizer comes into play!

As a comprehensive database client, DbVisualizer enables you to visually explore databases across a wide range of DBMS technologies. Here, you will use it to execute queries involving those DDL SQL commands. Keep in mind that DbVisualizer provides user-friendly modals and buttons to perform DDL operations in a simplified way, making database management more accessible.

The following examples will be in MySQL, but the queries below will work in any other SQL-based DBMS. Let’s explore DDL commands in SQL!

CREATE

The CREATE statement is used to create new database objects, such as tables, views, or indexes. The syntax of the SQL DDL command changes based on the object to create. To add a new table to the database, the syntax is:

Copy
        
1 CREATE TABLE <table_name> ( 2 <column_1> <sql_datatype>, 3 <column_2> <sql_datatype>, 4 ... 5 <column_N> <sql_datatype>, 6 );

For example, you can use a CREATE query to add an employees table to the company database as below:

Copy
        
1 CREATE TABLE employees ( 2 id INT AUTO_INCREMENT PRIMARY KEY, 3 first_name VARCHAR(50), 4 last_name VARCHAR(50), 5 hire_date DATE 6 );

A new table will be added to the database as desired:

Launching a CREATE query in DbVisualizer
Launching a CREATE query in DbVisualizer

Given a specific table, some useful information to get is the DDL command used to create it. Retrieving that info in DbVisualizer is very simple. All you have to do is open the table in a new tab by clicking on it and selecting the “DDL” tab:

Retrieving the DDL definition of a table in DbVisualizer
Retrieving the DDL definition of a table in DbVisualizer

You can then copy the query and use it to create a similar table on the database. Otherwise, you could create a table like another table with the special CREATE ... LIKE syntax.

ALTER

The ALTER command modifies an existing database object's structure, such as adding, changing, or deleting columns from a table. Other use cases include renaming database objects or updating the logic of an index, stored procedure, or trigger.

The syntax of the SQL DDL statement changes based on the operation to be performed. For adding a new column to an existing table, the syntax is:

Copy
        
1 ALTER TABLE <table_name> 2 ADD <column_name> <sql_datatype>;

For instance, you can use an ALTER statement to add the department_id column to the employees as follows:

Copy
        
1 ALTER TABLE employees 2 ADD department_id INT;
Running the ALTER query
Running the ALTER query

As you can visually verify in DbVisualizer in just a couple of clicks, employees will now have a new column:

Note the department_id column
Note the department_id column

Its DDL definition will be updated accordingly:

Note the new DDL definition
Note the new DDL definition

Note the department_id definition in the CREATE TABLE query.

DROP

The DROP statement removes a database object entirely. It is typically used to delete tables, views, indexes, or other objects from the database. The syntax to drop a table is:

Copy
        
1 DROP TABLE <table_name>;

For example, launch the following query to delete the employees table:

Copy
        
1 DROP TABLE employees;
Dropping a table with a query in DbVisualizer
Dropping a table with a query in DbVisualizer

Refresh the database connection and notice that the company database no longer has an employees table:

Note that the Tables dropdown no longer contains the employees table
Note that the Tables dropdown no longer contains the employees table

TRUNCATE

The TRUNCATE statement removes all the data from a table while retaining the table structure. It is a faster alternative to deleting all records one by one. Its syntax is simple:

Copy
        
1 TRUNCATE [TABLE] <table_name>;

For example, you can clear all records from the employees table in MySQL with:

Copy
        
1 TRUNCATE TABLE employees;
Launching the TRUNCATE query in DbVisualizer
Launching the TRUNCATE query in DbVisualizer

The employees table will now contain 0 records:

Note the Result set is empty message that indicates that there is no data in the table
Note the Result set is empty message that indicates that there is no data in the table

Et voilà! You are now a SQL DDL expert!

Conclusion

In this article, you dug into the SQL data definition language. You started from the main concepts and then explored each DDL command. You now know:

  1. What SQL DDL means.
  2. The key differences in the DDL vs DMS SQL comparison.
  3. How to use DDL statements in SQL.

DDL is about changing the structure of a database. Keep track of that with a visual database client like DbVisualizer. On top of supporting dozens of databases, the tool comes with ER schema representation functionality, a drag-and-drop UI to build queries, and powerful query optimization capabilities. Try DbVisualizer for free now!

FAQ

What are the SQL DDL statements?

SQL DDL statements are used to define and manage the structure of a database. These commands include:

  • CREATE: For creating tables, indexes, and databases
  • ALTER: For modifying existing database objects
  • DROP: For deleting database objects
  • TRUNCATE: For quickly deleting data from a table while preserving its structure.

DDL statements in SQL focus on the database's schema, ensuring its organization and integrity.

What is the difference between SQL DDL and sql.dll?

DDL refers to Data Definition Language or Data Description Language, which consists of commands for defining the structure of a database. On the other hand, sql.dll is a Windows dynamic-link library file used by some software applications. These are entirely different concepts: DDL is a category of SQL commands, while sql.dll is a file used for software functionality.

What are the key aspects to consider in the DDL vs DML SQL comparison?

In a DDL vs DML comparison, key aspects to consider are their purposes. DDL focuses on defining and modifying the database structure, such as creating or altering tables. DML deals instead with manipulating data within tables, including inserting, updating, and deleting records. DDL changes the schema, while DML affects the actual data.

How to get the SQL DDL definition of a table?

To obtain the SQL DDL definition, you can use the SHOW CREATE TABLE statement with the syntax below:

Copy
        
1 SHOW CREATE TABLE <table_name>;

This query will provide the DDL definition of the specified table via a CREATE query. Otherwise, you can get that information with a couple of clicks in a powerful database client like DbVisualizer.

How to use DDL to prevent SQL injection?

To prevent SQL injection, you need to focus on the use of prepared statements, parameterized queries, and input validation in DDL statements. At the same time, keep in mind that most SQL injection attacks occur during data addition or update, so they are more related to DML statements. To prevent SQL injection, do not pass user input straight into a database without validation.

Dbvis download link img
About the author
Antonello Zanini

Antonello is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

Glossary of the SQL Commands You Need to Know

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 12 min 2024-04-11
title

SUBSTRING_INDEX in SQL Explained: A Guide

author Lukas Vileikis tags MySQL SQL 8 min 2024-04-08
title

SQL NOT IN: the Good, Bad & the Ugly

author Lukas Vileikis tags MySQL SQL 7 min 2024-04-04
title

SQL Add to Date Operations: A Complete Guide

author Antonello Zanini tags DATE DATETIME MySQL ORACLE POSTGRESQL SQL SQL SERVER 6 min 2024-04-01
title

SQL CAST Function: Everything You Need to Know

author Antonello Zanini tags CAST MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-03-28
title

INSERT INTO SQL Clause

author Leslie S. Gyamfi tags INSERT SQL 6 min 2024-03-18
title

ALTER TABLE ADD COLUMN in SQL: A Comprehensive Guide

author TheTable tags SQL 5 min 2024-03-12
title

SQL CONTAINS Function: SQL Server Guide With Examples

author Antonello Zanini tags SQL 8 min 2024-01-18
title

SQL DISTINCT: A Comprehensive Guide

author Bonnie tags DISTINCT POSTGRESQL SQL 5 MINS 2024-01-11
title

SQL Interview Questions – SQL Queries

author Lukas Vileikis tags SQL 6 MINS 2024-01-08

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗