🐬 Ultimate Guide to MySQL – Complete SQL Cheat Sheet & Best Practices

🌱 Introduction to MySQL

MySQL is an open-source Relational Database Management System (RDBMS) that uses Structured Query Language (SQL) to manage and manipulate data. It is widely used for web applications, analytics, and enterprise solutions.

📌 What is a Database?

A database is a structured collection of data stored in a way that makes retrieval and management easy.

✅ Types of Databases:

Relational (SQL-based) – Data is stored in tables with predefined relationships.
- Examples: MySQL, PostgreSQL, Oracle.
Non-Relational (NoSQL) – Uses flexible data models like key-value, document, or graph-based storage.
- Examples: MongoDB, Cassandra, Firebase.

🔥 Installing MySQL on Windows, Linux, and Ubuntu

🏁 Windows:

Download MySQL from MySQL Official Website.
Run the installer and select "Developer Default."
Set a root password during installation.
Verify installation by opening Command Prompt and running:
```
 mysql --version
```

🐧 Linux (Ubuntu/Debian):

Update package lists:
```
 sudo apt update
```
Install MySQL:
```
 sudo apt install mysql-server
```
Secure MySQL installation:
```
 sudo mysql_secure_installation
```
Verify installation:
```
 mysql --version
```

📌 SQL vs MySQL – What’s the Difference?

Feature	SQL	MySQL
Definition	Structured Query Language	Database management system using SQL
Usage	Language for querying databases	Software that uses SQL for queries
Complexity	Standardized	Implementation varies
Vendor	Universal	Oracle Corporation
Example	`SELECT * FROM users;`	MySQL supports `SELECT * FROM users;`

🛠️ Basic MySQL Commands

📌 Database Commands

-- Create a new database
CREATE DATABASE mydatabase;

-- Use a specific database
USE mydatabase;

-- Show all databases
SHOW DATABASES;

📌 Table Commands

-- Create a table
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    age INT
);

-- Show all tables
SHOW TABLES;

-- Describe table structure
DESC students;

📌 Insert Data

INSERT INTO students (name, age) VALUES ('Alice', 21), ('Bob', 22);

📌 Select and Filter Data

-- Select all rows
SELECT * FROM students;

-- Select specific columns
SELECT name, age FROM students;

-- Use WHERE clause
SELECT * FROM students WHERE age > 20;

📌 Updating Data

-- Update a record
UPDATE students SET age = 23 WHERE name = 'Alice';

📌 Deleting Data

-- Delete a record
DELETE FROM students WHERE name = 'Bob';

📌 Constraints

Constraint	Description
PRIMARY KEY	Uniquely identifies each row
FOREIGN KEY	References another table’s key
NOT NULL	Ensures a column cannot be null
UNIQUE	Ensures unique values
CHECK	Enforces a condition

📌 Using JOINS

-- INNER JOIN
SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;

-- LEFT JOIN
SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id;

📊 Aggregate Functions

-- Count the number of records
SELECT COUNT(*) FROM students;

-- Find the average age
SELECT AVG(age) FROM students;

-- Find the maximum age
SELECT MAX(age) FROM students;

📌 Subqueries

-- Find students older than the average age
SELECT name FROM students WHERE age > (SELECT AVG(age) FROM students);

📌 EXISTS and NOT EXISTS

-- EXISTS Example
SELECT name FROM students WHERE EXISTS (SELECT id FROM cities WHERE name = 'London');

-- NOT EXISTS Example
SELECT name FROM students WHERE NOT EXISTS (SELECT id FROM cities WHERE name = 'London');

🔥 Top 19 MySQL Interview Questions

What is MySQL?
- MySQL is a relational database management system (RDBMS) used for data storage and retrieval.
What is the difference between SQL and MySQL?
- SQL is a query language; MySQL is a database system that uses SQL.
What are the different types of joins in SQL?
- INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN.

How do you create a foreign key in MySQL?

 CREATE TABLE orders (
     id INT PRIMARY KEY AUTO_INCREMENT,
     customer_id INT,
     FOREIGN KEY (customer_id) REFERENCES customers(id)
 );

How do you use the LIKE operator?

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

What is an index in MySQL?
- An index speeds up searches on large datasets.
What is the difference between WHERE and HAVING?
- WHERE filters rows before aggregation; HAVING filters aggregated results.
What is ACID compliance?
- Atomicity, Consistency, Isolation, and Durability ensure data reliability.

What is a stored procedure?

 CREATE PROCEDURE GetStudents()
 BEGIN
     SELECT * FROM students;
 END;

What is a trigger in MySQL?
- A trigger automatically executes a function when a condition is met.

🎯 Conclusion

Mastering MySQL allows you to efficiently manage and query databases for applications, analytics, and enterprise solutions. By understanding its commands, joins, subqueries, and best practices, you can optimize data storage and retrieval efficiently.

💡 Happy Learning! 🚀