SQL DDL: The Definitive Guide on Data Definition Language

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

DBVISUALIZER

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.

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:

  1 CREATE TABLE (  2  ,  3  ,  4 .  5  ,  6 );