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
| Command | What it does | Example |
|---|---|---|
| CREATE DATABASE [IF NOT EXISTS] | Creates a database | CREATE DATABASE IF NOT EXISTS campus; |
| USE db | Switches the current database | USE campus; |
| CREATE TABLE | Creates a table with columns and constraints | CREATE TABLE dept (id INT PRIMARY KEY, name VARCHAR(40) NOT NULL); |
| CREATE TABLE ... AS SELECT | Creates a table from a query result | CREATE TABLE top_students AS SELECT * FROM students WHERE marks > 90; |
| CREATE TABLE ... LIKE | Copies structure only (no rows) | CREATE TABLE students_bak LIKE students; |
| ALTER TABLE ADD COLUMN | Adds a column | ALTER TABLE students ADD COLUMN email VARCHAR(80); |
| ALTER TABLE MODIFY COLUMN | Changes type / nullability | ALTER TABLE students MODIFY name VARCHAR(100) NOT NULL; |
| ALTER TABLE RENAME COLUMN | Renames a column (MySQL 8) | ALTER TABLE students RENAME COLUMN marks TO score; |
| ALTER TABLE CHANGE | Renames and retypes in one step | ALTER TABLE students CHANGE score marks DECIMAL(5,2); |
| ALTER TABLE DROP COLUMN | Removes a column | ALTER TABLE students DROP COLUMN email; |
| ALTER TABLE ADD CONSTRAINT | Adds PK / FK / UNIQUE / CHECK | ALTER TABLE students ADD CONSTRAINT chk_marks CHECK (marks BETWEEN 0 AND 100); |
| ALTER TABLE DROP CONSTRAINT / FOREIGN KEY | Removes a constraint | ALTER TABLE students DROP FOREIGN KEY fk_dept; |
| RENAME TABLE a TO b | Renames a table | RENAME TABLE students TO learners; |
| DROP TABLE [IF EXISTS] | Deletes table and data | DROP TABLE IF EXISTS temp_data; |
| TRUNCATE TABLE | Deletes all rows fast, resets AUTO_INCREMENT | TRUNCATE TABLE logs; |
| CREATE [UNIQUE] INDEX | Creates an index | CREATE INDEX idx_students_name ON students(name); |
| DROP INDEX | Removes an index | DROP INDEX idx_students_name ON students; |
| CREATE [OR REPLACE] VIEW | Saves a query as a virtual table | CREATE OR REPLACE VIEW toppers AS SELECT name FROM students WHERE marks > 90; |
| DROP VIEW | Removes a view | DROP VIEW IF EXISTS toppers; |
| DROP DATABASE | Deletes the whole database | DROP DATABASE IF EXISTS old_campus; |
| COMMENT | Documents a table/column | ALTER 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 completelyRemember
- 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.
Lesson test
Take an 8-question test on “Commands Reference: DDL (CREATE, ALTER, DROP, TRUNCATE)” — concepts, output prediction, bug spotting and interview questions.