The Ultimate MySQL Queries Cheat Sheet (With Examples & Outputs)

🗄️ The Ultimate MySQL Queries Cheat Sheet (With Examples & Outputs)

SQL (Structured Query Language) is the standard way to interact with databases.
If you’re learning MySQL, here’s the all-in-one guide with all essential queries, simple explanations, and real examples with outputs.

We’ll build this step by step with a students table + courses table for clarity.

---

🏗️ Setup: Create Tables

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    course VARCHAR(100),
    marks INT
);

CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(100),
    instructor VARCHAR(100)
);

Insert sample data:

INSERT INTO students (name, age, course, marks) VALUES
('Alice', 20, 'Computer Science', 85),
('Bob', 22, 'Mathematics', 90),
('Charlie', 21, 'Physics', 75),
('Daisy', 23, 'Computer Science', 95);

INSERT INTO courses (course_name, instructor) VALUES
('Computer Science', 'Dr. Smith'),
('Mathematics', 'Dr. John'),
('Physics', 'Dr. Lee');

✅ Now let’s dive into queries.

---

🔹 Basic SQL Queries

1. Show All Databases

SHOW DATABASES;

👉 Output:

Database
mysql
information_schema
test_db

---

2. Use a Database

USE test_db;

---

3. Show All Tables

SHOW TABLES;

👉 Output:

Tables_in_test_db
students
courses

---

4. Describe a Table

DESCRIBE students;

👉 Output:

Field	Type	Null	Key	Default	Extra
id	int	NO	PRI	NULL	auto_increment
name	varchar(100)	YES		NULL
age	int	YES		NULL
course	varchar(100)	YES		NULL
marks	int	YES		NULL

---

🔹 Data Retrieval

5. SELECT – Fetch Data

SELECT * FROM students;

👉 Output:

id	name	age	course	marks
1	Alice	20	Computer Science	85
2	Bob	22	Mathematics	90
3	Charlie	21	Physics	75
4	Daisy	23	Computer Science	95

---

6. WHERE – Filter Data

SELECT name, marks FROM students WHERE marks > 80;

👉 Output:

name	marks
Alice	85
Bob	90
Daisy	95

---

7. ORDER BY – Sorting

SELECT * FROM students ORDER BY marks DESC;

👉 Output:

name	marks
Daisy	95
Bob	90
Alice	85
Charlie	75

---

8. LIMIT – Restrict Rows

SELECT * FROM students LIMIT 2;

👉 Output:

name	age	course	marks
Alice	20	Computer Science	85
Bob	22	Mathematics	90

---

9. DISTINCT – Unique Values

SELECT DISTINCT course FROM students;

👉 Output:

course
Computer Science
Mathematics
Physics

---

🔹 Data Modification

10. INSERT – Add Data

INSERT INTO students (name, age, course, marks)
VALUES ('Eve', 20, 'Mathematics', 88);

---

11. UPDATE – Modify Data

UPDATE students SET marks = 92 WHERE name = 'Alice';

---

12. DELETE – Remove Data

DELETE FROM students WHERE name = 'Charlie';

---

13. DROP – Remove a Table

DROP TABLE students;

---

🔹 Advanced SQL Queries

14. GROUP BY – Aggregation

SELECT course, AVG(marks) AS avg_marks
FROM students
GROUP BY course;

👉 Output:

course	avg_marks
Computer Science	90
Mathematics	89
Physics	75

---

15. HAVING – Filter Groups

SELECT course, AVG(marks) AS avg_marks
FROM students
GROUP BY course
HAVING avg_marks > 80;

👉 Output:

course	avg_marks
Computer Science	90
Mathematics	89

---

16. JOIN – Combine Tables

SELECT s.name, s.course, c.instructor
FROM students s
JOIN courses c ON s.course = c.course_name;

👉 Output:

name	course	instructor
Alice	Computer Science	Dr. Smith
Bob	Mathematics	Dr. John
Daisy	Computer Science	Dr. Smith

---

17. LIKE – Pattern Matching

SELECT * FROM students WHERE name LIKE 'A%';

👉 Output:

id	name	age	course	marks
1	Alice	20	Computer Science	92

---

18. BETWEEN & IN

-- Marks between 80 and 95
SELECT * FROM students WHERE marks BETWEEN 80 AND 95;

-- Students in specific courses
SELECT * FROM students WHERE course IN ('Mathematics', 'Physics');

---

🔹 Transactions

19. START TRANSACTION, COMMIT, ROLLBACK

START TRANSACTION;

UPDATE students SET marks = 100 WHERE name = 'Bob';

ROLLBACK;  -- undo changes
-- COMMIT; -- confirm changes

---

🔹 Triggers & Stored Procedures

20. DELIMITER + TRIGGER

DELIMITER //

CREATE TRIGGER after_insert_student
AFTER INSERT ON students
FOR EACH ROW
BEGIN
    INSERT INTO courses (course_name, instructor)
    VALUES ('New Course', 'TBD');
END //

DELIMITER ;

---

21. STORED PROCEDURE

DELIMITER //

CREATE PROCEDURE GetHighScorers()
BEGIN
    SELECT name, marks FROM students WHERE marks > 85;
END //

DELIMITER ;

CALL GetHighScorers();

👉 Output:

name	marks
Alice	92
Bob	90
Daisy	95

---

🔹 Views & Indexes

22. CREATE VIEW

CREATE VIEW high_scorers AS
SELECT name, course, marks
FROM students
WHERE marks > 85;

SELECT * FROM high_scorers;

👉 Output:

name	course	marks
Alice	Computer Science	92
Bob	Mathematics	90
Daisy	Computer Science	95

---

23. INDEX

CREATE INDEX idx_course ON students(course);

👉 Helps speed up searches on course.

---

🎯 Final Thoughts

We just covered all essential MySQL queries:

• ✅ Basic (CREATE, SELECT, WHERE, ORDER, LIMIT, DISTINCT)
• ✅ Modification (INSERT, UPDATE, DELETE, DROP)
• ✅ Advanced (GROUP BY, HAVING, JOIN, LIKE, IN, BETWEEN)
• ✅ Transactions (COMMIT, ROLLBACK)
• ✅ Triggers, Procedures, Views, Indexes

This is your complete MySQL cheat sheet 📖. Bookmark it, copy-paste it, and practice with real data.

---

✍️ Written by Nivesh Bansal