MySQL

This MySQL cheatsheet is a concise guide

1. What is MySQL?

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)

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

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

String

Date