← SQL roadmap

Topic 31 of 52 · Beginner

Commands Reference: DDL (CREATE, ALTER, DROP, TRUNCATE)

Every Data Definition Language command and its common options, with examples.

Concept

DDL commands create and change the structure of a database: databases, tables, columns, indexes, views and constraints.

In MySQL, DDL statements commit automatically — you cannot ROLLBACK a DROP TABLE or ALTER TABLE.

TRUNCATE removes every row by recreating the table, so it is much faster than DELETE, resets AUTO_INCREMENT and cannot be filtered with WHERE.

Cheat sheet

CommandWhat it doesExample
CREATE DATABASE [IF NOT EXISTS]Creates a databaseCREATE DATABASE IF NOT EXISTS campus;
USE dbSwitches the current databaseUSE campus;
CREATE TABLECreates a table with columns and constraintsCREATE TABLE dept (id INT PRIMARY KEY, name VARCHAR(40) NOT NULL);
CREATE TABLE ... AS SELECTCreates a table from a query resultCREATE TABLE top_students AS SELECT * FROM students WHERE marks > 90;
CREATE TABLE ... LIKECopies structure only (no rows)CREATE TABLE students_bak LIKE students;
ALTER TABLE ADD COLUMNAdds a columnALTER TABLE students ADD COLUMN email VARCHAR(80);
ALTER TABLE MODIFY COLUMNChanges type / nullabilityALTER TABLE students MODIFY name VARCHAR(100) NOT NULL;
ALTER TABLE RENAME COLUMNRenames a column (MySQL 8)ALTER TABLE students RENAME COLUMN marks TO score;
ALTER TABLE CHANGERenames and retypes in one stepALTER TABLE students CHANGE score marks DECIMAL(5,2);
ALTER TABLE DROP COLUMNRemoves a columnALTER TABLE students DROP COLUMN email;
ALTER TABLE ADD CONSTRAINTAdds PK / FK / UNIQUE / CHECKALTER TABLE students ADD CONSTRAINT chk_marks CHECK (marks BETWEEN 0 AND 100);
ALTER TABLE DROP CONSTRAINT / FOREIGN KEYRemoves a constraintALTER TABLE students DROP FOREIGN KEY fk_dept;
RENAME TABLE a TO bRenames a tableRENAME TABLE students TO learners;
DROP TABLE [IF EXISTS]Deletes table and dataDROP TABLE IF EXISTS temp_data;
TRUNCATE TABLEDeletes all rows fast, resets AUTO_INCREMENTTRUNCATE TABLE logs;
CREATE [UNIQUE] INDEXCreates an indexCREATE INDEX idx_students_name ON students(name);
DROP INDEXRemoves an indexDROP INDEX idx_students_name ON students;
CREATE [OR REPLACE] VIEWSaves a query as a virtual tableCREATE OR REPLACE VIEW toppers AS SELECT name FROM students WHERE marks > 90;
DROP VIEWRemoves a viewDROP VIEW IF EXISTS toppers;
DROP DATABASEDeletes the whole databaseDROP DATABASE IF EXISTS old_campus;
COMMENTDocuments a table/columnALTER TABLE students COMMENT = 'Enrolled students';

Code (SQL · MySQL 8)

CREATE TABLE dept (
  id   INT PRIMARY KEY,
  name VARCHAR(40) NOT NULL UNIQUE
);

CREATE TABLE students (
  id      INT AUTO_INCREMENT PRIMARY KEY,
  name    VARCHAR(50) NOT NULL,
  dept_id INT,
  marks   DECIMAL(5,2) DEFAULT 0,
  CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES dept(id),
  CONSTRAINT chk_marks CHECK (marks BETWEEN 0 AND 100)
);

ALTER TABLE students ADD COLUMN email VARCHAR(80) UNIQUE;
ALTER TABLE students RENAME COLUMN marks TO score;
CREATE INDEX idx_students_dept ON students(dept_id);

TRUNCATE TABLE students;           -- empty it, keep structure
DROP TABLE IF EXISTS students;     -- remove completely

Remember

  • DDL auto-commits in MySQL — no rollback.
  • DROP removes structure + data; TRUNCATE removes data only; DELETE removes chosen rows.
  • Use IF EXISTS / IF NOT EXISTS to make scripts safe to re-run.
  • Name constraints (CONSTRAINT fk_dept ...) so you can drop them later.

Interview questions

DELETE vs TRUNCATE vs DROP?

DELETE is DML, row-by-row, supports WHERE and can be rolled back. TRUNCATE is DDL, removes all rows quickly and resets AUTO_INCREMENT. DROP deletes the table structure as well.

MODIFY vs CHANGE in ALTER TABLE?

MODIFY changes a column's definition keeping its name; CHANGE takes old and new names, so it can rename and retype together.

Can TRUNCATE fire triggers?

No — TRUNCATE does not fire DELETE triggers and fails if another table has a foreign key referencing it.

Practise SQL with the AI mentor

Lesson test

Take an 8-question test on “Commands Reference: DDL (CREATE, ALTER, DROP, TRUNCATE)” — concepts, output prediction, bug spotting and interview questions.

Can't find a SQL topic you need for exams or placements? Request it and we'll add it.