MySQL Comprehensive Syntax & Queries Cheatsheet
A quick-reference guide covering DDL, DML, DQL, Joins, Subqueries, and Correlated Subqueries.
Category Syntax Template Explanation / Rules Real-World Example
DDL (Structure) CREATE TABLE table_name ( Creates a new table blueprint. CREATE TABLE employees (
col1 datatype Auto-committed instantly. emp_id INT AUTO_INCREMENT,
constraints, emp_name VARCHAR(50) NOT NULL,
col2 datatype, salary DECIMAL(10,2),
PRIMARY KEY (col1) PRIMARY KEY (emp_id)
); );
DDL (Structure) ALTER TABLE table_name Adds a new column to an existing ALTER TABLE employees
ADD column_name datatype; table structure. ADD dept_id VARCHAR(10);
DDL (Structure) ALTER TABLE table_name Changes data type, size, or ALTER TABLE employees
MODIFY COLUMN col_name constraints of a column. MODIFY COLUMN emp_name
new_type; VARCHAR(100) NOT NULL;
DDL (Structure) ALTER TABLE table_name Permanently removes a column ALTER TABLE employees
DROP COLUMN column_name; from the structural blueprint. DROP COLUMN salary;
DDL (Structure) TRUNCATE TABLE table_name; Wipes out all records completely. TRUNCATE TABLE employees;
Resets auto-increment keys.
Cannot be rolled back.
DDL (Structure) DROP TABLE table_name; Permanently deletes the entire DROP TABLE employees;
table structure and its data from
the database.
DML (Data) INSERT INTO table_name Inserts a new raw row of data into INSERT INTO employees (emp_name,
(col1, col2) the table. salary)
VALUES (val1, val2); VALUES ('Rahul Sharma',
65000.00);
DML (Data) UPDATE table_name CRITICAL: Modifies existing UPDATE employees
SET col1 = val1 values. Always use WHERE to SET salary = salary * 1.10
WHERE condition; avoid overwriting all rows. WHERE emp_id = 1;
DML (Data) DELETE FROM table_name CRITICAL: Deletes specific rows. DELETE FROM employees
WHERE condition; Always use WHERE to prevent WHERE salary < 30000.00;
full table wipeout.
DQL (Query) SELECT col1, col2 Fetches data from specific SELECT emp_name, salary
FROM table_name columns based on row filters. FROM employees
WHERE condition; WHERE salary > 50000;
DQL (Query) SELECT col, SUM(col2) Groups rows. WHERE filters rows SELECT dept_id, AVG(salary)
FROM table before grouping; HAVING filters FROM employees
GROUP BY col groups after aggregation. GROUP BY dept_id
HAVING SUM(col2) > val; HAVING AVG(salary) > 60000;
JOINS SELECT * Returns rows only when there is SELECT e.emp_name, d.dept_name
FROM T1 a matching value in both tables. FROM employees e
INNER JOIN T2 ON [Link] = INNER JOIN departments d ON
[Link]; e.dept_id = d.dept_id;
JOINS SELECT * Returns all rows from the left SELECT e.emp_name, d.dept_name
FROM T1 table, plus matched rows from the FROM employees e
LEFT JOIN T2 ON [Link] = right table (NULL if no match). LEFT JOIN departments d ON
[Link]; e.dept_id = d.dept_id;
JOINS SELECT * Returns all rows from the right SELECT e.emp_name, d.dept_name
FROM T1 table, plus matched rows from the FROM employees e
RIGHT JOIN T2 ON [Link] = left table (NULL if no match). RIGHT JOIN departments d ON
[Link]; e.dept_id = d.dept_id;
Subqueries SELECT * Standard / Independent SELECT emp_name, salary
FROM T1 Subquery. The inner query runs FROM employees
WHERE col > (SELECT exactly once first, then passes its WHERE salary > (SELECT
AVG(col) FROM T1); single value to the outer query. AVG(salary) FROM employees);
Correlated SELECT * Correlated Subquery. The inner SELECT e1.emp_name, [Link],
Subqueries FROM T1 e1 query executes repeatedly for e1.dept_id
WHERE col > ( every single row processed by FROM employees e1
SELECT AVG(col) the outer query, referencing outer WHERE [Link] > (
FROM T1 e2 columns. SELECT AVG([Link])
WHERE [Link] = [Link] FROM employees e2
); WHERE e2.dept_id = e1.dept_id
);