MySQL
This MySQL cheatsheet is a concise guide
1. What is MySQL?
- Open-source Relational Database Management System (RDBMS)
- Uses SQL (Structured Query Language)
2. DATABASE OPERATIONS
Create Database
CREATE DATABASE mydb;
Show Databases
SHOW DATABASES;
Use Database
USE mydb;
Delete Database
DROP DATABASE mydb;
3. TABLE OPERATIONS
Create Table
CREATE TABLE students ( id INT PRIMARY KEY, name VARCHAR(50), age INT, marks FLOAT );
Show Tables
SHOW TABLES;
Describe Table
DESC students;
Drop Table
DROP TABLE students;
4. INSERT DATA
INSERT INTO students (id, name, age, marks) VALUES (1, 'Uday', 21, 85.5);
Insert Multiple Rows
INSERT INTO students VALUES (2, 'Rahul', 22, 78), (3, 'Aman', 20, 90);
5. SELECT (READ DATA)
Basic
SELECT * FROM students;
Specific Columns
SELECT name, marks FROM students;
WHERE Condition
SELECT * FROM students WHERE marks > 80;
Operators
=, >, <, >=, <=, !=
AND / OR
SELECT * FROM students WHERE age > 20 AND marks > 80;
BETWEEN
SELECT * FROM students WHERE marks BETWEEN 70 AND 90;
LIKE (Pattern)
SELECT * FROM students WHERE name LIKE 'A%';
6. AGGREGATE FUNCTIONS
SELECT COUNT(*) FROM students; SELECT SUM(marks) FROM students; SELECT AVG(marks) FROM students; SELECT MAX(marks) FROM students; SELECT MIN(marks) FROM students;
7. GROUP BY & HAVING
SELECT age, COUNT(*) FROM students GROUP BY age;
HAVING (with condition)
SELECT age, AVG(marks) FROM students GROUP BY age HAVING AVG(marks) > 80;
8. UPDATE DATA
UPDATE students SET marks = 95 WHERE id = 1;
9. DELETE DATA
DELETE FROM students WHERE id = 2;
10. JOINS
INNER JOIN
SELECT students.name, courses.course_name FROM students INNER JOIN courses ON students.id = courses.student_id;
LEFT JOIN
SELECT * FROM students LEFT JOIN courses ON students.id = courses.student_id;
RIGHT JOIN
SELECT * FROM students RIGHT JOIN courses ON students.id = courses.student_id;
11. CONSTRAINTS
CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, email VARCHAR(100) UNIQUE, age INT CHECK (age >= 18) );
12. AUTO INCREMENT
id INT AUTO_INCREMENT PRIMARY KEY
13. INDEX
CREATE INDEX idx_name ON students(name);
14. VIEWS
CREATE VIEW top_students AS SELECT name, marks FROM students WHERE marks > 85;
15. STORED PROCEDURE
DELIMITER // CREATE PROCEDURE getStudents() BEGIN SELECT * FROM students; END // DELIMITER ;
Call Procedure
CALL getStudents();
16. TRIGGERS
CREATE TRIGGER before_insert_student BEFORE INSERT ON students FOR EACH ROW SET NEW.marks = NEW.marks + 5;
17. TRANSACTIONS
START TRANSACTION; UPDATE students SET marks = 100 WHERE id = 1; ROLLBACK; -- Undo COMMIT; -- Save
18. NORMALIZATION (Concept)
- 1NF → No duplicate columns
- 2NF → Remove partial dependency
- 3NF → Remove transitive dependency
19. SUBQUERIES
SELECT name FROM students WHERE marks > (SELECT AVG(marks) FROM students);
20. ORDER BY & LIMIT
SELECT * FROM students ORDER BY marks DESC LIMIT 3;
21. ALTER TABLE
ALTER TABLE students ADD email VARCHAR(100); ALTER TABLE students DROP COLUMN age; ALTER TABLE students MODIFY name VARCHAR(100);
22. USER MANAGEMENT
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON mydb.* TO 'user1'@'localhost'; FLUSH PRIVILEGES;
23. IMPORT / EXPORT
Export
mysqldump -u root -p mydb > backup.sql
Import
mysql -u root -p mydb < backup.sql
24. PERFORMANCE TIPS
- Use INDEXES
- Avoid `SELECT *`
- Use `LIMIT`
- Normalize tables
- Use proper data types
25. COMMON INTERVIEW QUERIES
Second Highest Marks
SELECT MAX(marks) FROM students WHERE marks < (SELECT MAX(marks) FROM students);
Duplicate Records
SELECT name, COUNT(*) FROM students GROUP BY name HAVING COUNT(*) > 1;
BONUS: DATA TYPES
Numeric
- INT, FLOAT, DOUBLE
String
- CHAR, VARCHAR, TEXT
Date
- DATE, TIME, DATETIME