DDL statements in SQL

The meaning of DDL

The SQL commands can be categorized into 4 main subsets:

DDL – Data Definition Language. This includes these statements: CREATE, DROP, ALTER, TRUNCATE, RENAME, COMMENT
DQl – Data Query Language. This includes all SELECT statements.
DML – Data Manipulation Language. This includes INSERT, UPDATE, and DELETE statements.
DCL – Data Control Language. This includes GRANT and REVOKE statements.

In this post, we are going to learn more about the DDL - Data Definition Language subsets.

DDL statements are used to make a change to the structure of the database schema, which involves creating, renaming, modifying, or deleting a database object such as database, table, and view.

It depends on which database management system you are using to have a corresponding subset of DDL statements as the functions and syntaxes might vary. But here is the list of common DDL statements in SQL:

CREATE: to create a new database or its objects such as table or view
DROP: to delete an existing database or its object
ALTER: to modify the structure of an existing database
TRUNCATE: to remove all existing records from the table and make it empty
COMMENT: to add comments to the data dictionary
RENAME: to rename a database or its object

Example

Create a new table:

CREATE TABLE table_name (
   column1 datatype,
   column2 datatype,
   column3 datatype,
  ....
);

Drop an existing table:

DROP TABLE table_name;

Alter an existing table to add a new column:

ALTER TABLE table_name
ADD column_name datatype;

Truncate a table:

TRUNCATE TABLE table_name;

Rename table:

ALTER TABLE table_name
RENAME TO new_table_name;

Comment on a table:

COMMENT ON TABLE table_name IS 'Your comment';

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL and many other databases simultaneously using an intuitive and powerful graphical interface.