SQL Data Definition Language

Data Removal Commands
  • TRUNCATE TABLE

    • Removes all rows rapidly while preserving table structure.

    • Typically resets AUTO_INCREMENT counters.

    • Does not accept a WHERE clause (unlike DELETE).

    • Syntax: TRUNCATE TABLE table_name;

  • DROP TABLE

    • Permanently removes both the table data and table schema.

    • Supports IF EXISTS to prevent errors if the table is already absent.

    • Syntax: DROP TABLE [IF EXISTS] table_name;

  • DROP DATABASE

    • Permanently deletes an entire database along with all contained tables and data.

    • Syntax: DROP DATABASE [IF EXISTS] database_name;

Core DDL Command Comparison
  • CREATE: Defines and instantiates a new database object.

  • ALTER: Modifies the structure of an existing object (e.g., adding or changing columns).

  • RENAME: Renames a table or column without altering its definition.

  • TRUNCATE: Clears all table records while preserving the structure.

  • DROP: Deletes the database object entirely.

DDL Quick Reference (MariaDB / MySQL)
  • Database Management

    • Create database: CREATE DATABASE database_name;

    • Select database: USE database_name;

    • Delete database: DROP DATABASE [IF EXISTS] database_name;

  • Table Management

    • Create table: CREATE TABLE table_name (...);

    • Inspect structure: DESCRIBE table_name;

    • Rename table: RENAME TABLE old_name TO new_name;

    • Empty table: TRUNCATE TABLE table_name;

    • Delete table: DROP TABLE [IF EXISTS] table_name;

  • Column Management (ALTER TABLE)

    • Add column: ALTER TABLE table_name ADD COLUMN column_name datatype;

    • Modify column: ALTER TABLE table_name MODIFY COLUMN column_name datatype;

    • Rename column: ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

    • Remove column: ALTER TABLE table_name DROP COLUMN column_name;