#1 India's Top IT Training Institute
×

About Uncodemy

Know more about who we are and what we stand for.

About Us

About Us

Discover our mission to equip individuals with essential IT skills and industry expertise.

Read More →
UnCodeMy Gallery

UnCodeMy Gallery

Explore highlights from our IT training sessions and success stories.

Read More →
Mentors

Mentors

Meet the industry experts who train and guide you through your course.

Read More →
Free Resources

Free Resources

Free tutorials, interview questions, tools and calculators to help you learn.

Explore →
×

Free Resources

Learn, practice and plan your career — bilkul free.

Tutorials

Tutorials

Step-by-step written tutorials to learn any technology at your own pace.

Read More →
Free Counselling

Free Counselling

Book a free video counselling session and pick the right course for you.

Read More →
Online Compiler

Online Compiler

Write, run and test your code online — no setup required.

Read More →
MySQL Tutorial · Data Analytics

DDL, DML, DCL and TCL Commands in MySQL Explained

Try sorting these five statements into groups before reading on: CREATE TABLE, UPDATE, GRANT, COMMIT, TRUNCATE. If you put TRUNCATE next to DELETE, you have made the most common mistake in this topic, and it matters for more than viva marks: it changes whether you can undo it. SQL statements are grouped by what they act on: the structure of the database, the data inside it, who may use it, and transactions.

GroupFull formActs onMySQL statementsCan be rolled back?
DDLData Definition LanguageStructure: databases, tables, indexes, viewsCREATE, ALTER, DROP, TRUNCATE, RENAMENo, each causes an implicit commit
DMLData Manipulation LanguageRows of dataINSERT, UPDATE, DELETE, REPLACE (and SELECT, see below)Yes, inside a transaction (InnoDB)
DCLData Control LanguageUsers and permissionsGRANT, REVOKE (plus CREATE USER, CREATE ROLE)No, implicit commit
TCLTransaction Control LanguageTransactionsSTART TRANSACTION, COMMIT, ROLLBACK, SAVEPOINTNot applicable

Some textbooks list SELECT separately as DQL (Data Query Language). The MySQL manual groups it with the data manipulation statements. Either answer is accepted if you explain it.

DDL: defining structure

CREATE TABLE courses (
  course_id      INT PRIMARY KEY,
  title          VARCHAR(40) NOT NULL,
  duration_weeks INT
);
ALTER TABLE courses ADD COLUMN mode VARCHAR(10) DEFAULT 'online';
DESCRIBE courses;
FieldTypeNullKeyDefaultExtra
course_idintNOPRINULL
titlevarchar(40)NONULL
duration_weeksintYESNULL
modevarchar(10)YESonline

DDL changes what the table is. DROP TABLE courses; would remove the table and all its rows; RENAME TABLE courses TO course; renames it. Table design is covered step by step in how to create a table in MySQL.

DML: working with rows

INSERT INTO courses (course_id, title, duration_weeks) VALUES
  (1,'SQL Basics',4), (2,'Power BI',6), (3,'Python for Analysts',8);
UPDATE courses SET mode = 'hybrid' WHERE course_id = 2;
DELETE FROM courses WHERE course_id = 3;

SELECT * FROM courses;
course_idtitleduration_weeksmode
1SQL Basics4online
2Power BI6hybrid

Rows inserted without a mode picked up the default 'online' defined by the DDL earlier. The full syntax and safety habits for these three statements are in INSERT, UPDATE and DELETE in MySQL.

TCL: the undo button for DML, and why DDL ignores it

This experiment shows the practical difference between the groups. First a DML delete inside a transaction:

START TRANSACTION;
DELETE FROM courses;      -- DML
ROLLBACK;
SELECT COUNT(*) FROM courses;
COUNT(*)
2

Both rows are back. Now the same idea with TRUNCATE, which is DDL:

START TRANSACTION;
TRUNCATE TABLE courses;   -- DDL: commits implicitly
ROLLBACK;
SELECT COUNT(*) FROM courses;
COUNT(*)
0

The rows are gone. TRUNCATE drops and re-creates the table internally, resets AUTO_INCREMENT, and commits on its own, so ROLLBACK has nothing to undo. The same happens with CREATE, ALTER and DROP, and even with GRANT. Details on COMMIT, ROLLBACK and SAVEPOINT are in COMMIT and ROLLBACK in MySQL, and the three ways of removing data are compared in DELETE vs TRUNCATE vs DROP.

DCL: who can do what

MySQL 8 added roles, which make permissions easier to manage: give privileges to a role once, then give the role to people.

CREATE ROLE 'analyst_ro';
GRANT SELECT ON college.* TO 'analyst_ro';

CREATE USER 'meena'@'localhost' IDENTIFIED BY 'An0ther#Pass';
GRANT 'analyst_ro' TO 'meena'@'localhost';
SET DEFAULT ROLE 'analyst_ro' TO 'meena'@'localhost';

SHOW GRANTS FOR 'meena'@'localhost' USING 'analyst_ro';
Grants for meena@localhost
GRANT USAGE ON *.* TO `meena`@`localhost`
GRANT SELECT ON `college`.* TO `meena`@`localhost`
GRANT `analyst_ro`@`%` TO `meena`@`localhost`

USAGE means "can log in, nothing more"; the SELECT comes through the role. Taking it away is one statement:

REVOKE 'analyst_ro' FROM 'meena'@'localhost';

Without SET DEFAULT ROLE, a granted role is not active when the user logs in, which confuses many people the first time. Users, roles and privilege levels are explained in MySQL user management and privileges.

Common exam and interview mix-ups

For a compact list of all these commands with syntax, keep the MySQL commands cheat sheet open while you practise.

Become a Job-Ready Data Analyst

Master MySQL, Advanced Excel, Power BI, Tableau and Python with Uncodemy's Data Analytics Training in Noida — live projects, expert mentors and Dedicated Placement Support.

Enroll in Data Analytics Course →