ALTER, DROP, TRUNCATE & RENAME

ALTER TABLE

-- add
ALTER TABLE students ADD COLUMN phone VARCHAR(20) AFTER email;

-- change type only
ALTER TABLE students MODIFY COLUMN name_bn VARCHAR(150) NOT NULL;

-- rename + change type
ALTER TABLE students CHANGE COLUMN phone mobile VARCHAR(25);

-- rename a column (8.0+)
ALTER TABLE students RENAME COLUMN cgpa TO gpa;

-- drop
ALTER TABLE students DROP COLUMN mobile;

-- defaults
ALTER TABLE students ALTER COLUMN department SET DEFAULT 'EEE';
ALTER TABLE students ALTER COLUMN department DROP DEFAULT;

-- keys and indexes
ALTER TABLE students ADD INDEX idx_dept (department);
ALTER TABLE students ADD UNIQUE (email);
ALTER TABLE students DROP INDEX idx_dept;

-- table-level
ALTER TABLE students RENAME TO learners;
ALTER TABLE students ENGINE = InnoDB;
ALTER TABLE students CONVERT TO CHARACTER SET utf8mb4;

Several changes can be combined in one statement, which is much faster than running them separately:

ALTER TABLE students
  ADD COLUMN city VARCHAR(50),
  MODIFY COLUMN age SMALLINT UNSIGNED,
  ADD INDEX idx_city (city);

DROP vs DELETE vs TRUNCATE

	DELETE	TRUNCATE	DROP
Type	DML	DDL	DDL
Removes	Selected rows	All rows	Rows and the table
WHERE	Yes	No	No
Rollback	Yes (in a transaction)	No (implicit commit)	No
Resets AUTO_INCREMENT	No	Yes	N/A
Speed	Slow on big tables	Very fast	Very fast
Fires triggers	Yes	No	No

DELETE FROM students WHERE cgpa < 2.0;   -- some rows
TRUNCATE TABLE students;                 -- empty the table
DROP TABLE IF EXISTS students;           -- remove entirely

Warning: TRUNCATE and DROP cannot be rolled back. Always take a backup on production, and run SELECT with the same WHERE before any DELETE.

Online DDL

MySQL 8 performs many ALTER operations in place without blocking writes. You can control it:

ALTER TABLE students ADD INDEX idx_gpa (gpa), ALGORITHM=INPLACE, LOCK=NONE;

If MySQL cannot satisfy the requested algorithm it raises an error instead of silently locking the table — useful safety on live systems.