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.

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

Need a quick edit on the go? Download TablePlus for iOS

TablePlus in Dark mode