MySQL is one of the most popular relational database management systems (RDBMS) used worldwide. It helps store, manage, and retrieve data efficiently. MySQL is widely used in web development, especially with technologies like PHP, Python, Node.js, and Java. This guide covers MySQL from beginner to advanced level and helps in interview preparation.

1. What is MySQL?

MySQL is an open-source relational database that stores data in tables and allows users to perform operations using SQL (Structured Query Language).

2. Features of MySQL

Open-source and free
High performance
Secure and reliable
Supports large databases
Multi-user access

3. What is a Database?

A database is a collection of organized data that can be easily accessed and managed.

4. What is a Table?

Tables store data in rows and columns.

Example:

ID	Name	Age
1	John	25

5. What is SQL?

SQL is a language used to interact with databases for creating, reading, updating, and deleting data.

6. Installing MySQL

MySQL can be installed using MySQL Server, XAMPP, or WAMP.

7. MySQL Data Types

Common data types include:

INT
VARCHAR
TEXT
DATE
FLOAT

Example:

CREATE TABLE users (
  id INT,
  name VARCHAR(50),
  age INT
);

8. Creating a Database

CREATE DATABASE company;

9. Using a Database

USE company;

10. Creating Tables

CREATE TABLE employees (
  id INT,
  name VARCHAR(100),
  salary INT
);

11. Inserting Data

INSERT INTO employees VALUES (1, 'Raj', 30000);

12. Selecting Data

SELECT * FROM employees;

13. WHERE Clause

Filters data based on conditions.

SELECT * FROM employees WHERE salary > 20000;

14. Updating Data

UPDATE employees SET salary = 40000 WHERE id = 1;

15. Deleting Data

DELETE FROM employees WHERE id = 1;

16. Primary Key

Uniquely identifies each record.

id INT PRIMARY KEY

17. Auto Increment

Automatically increases values.

id INT AUTO_INCREMENT PRIMARY KEY

18. Constraints in MySQL

Constraints control data rules:

NOT NULL
UNIQUE
DEFAULT
CHECK

19. Foreign Key

Links two tables together.

FOREIGN KEY (dept_id) REFERENCES department(id)

20. Joins in MySQL

Used to combine data from multiple tables.

INNER JOIN

SELECT * FROM employees
INNER JOIN department
ON employees.dept_id = department.id;

21. LEFT JOIN

Returns all records from left table.

22. RIGHT JOIN

Returns all records from right table.

23. GROUP BY Clause

Groups data based on column values.

SELECT dept_id, COUNT(*)
FROM employees
GROUP BY dept_id;

24. ORDER BY Clause

Sorts data.

SELECT * FROM employees ORDER BY salary DESC;

25. Aggregate Functions

Used for calculations:

COUNT()
SUM()
AVG()
MAX()
MIN()

26. HAVING Clause

Filters grouped data.

SELECT dept_id, COUNT(*)
FROM employees
GROUP BY dept_id
HAVING COUNT(*) > 2;

27. Indexes in MySQL

Improve query performance.

CREATE INDEX idx_name ON employees(name);

28. Views in MySQL

Virtual tables based on queries.

CREATE VIEW high_salary AS
SELECT name, salary FROM employees WHERE salary > 30000;

29. Stored Procedures

Reusable SQL blocks.

CREATE PROCEDURE GetEmployees()
SELECT * FROM employees;

30. Triggers in MySQL

Automatically execute actions.

CREATE TRIGGER before_insert
BEFORE INSERT ON employees
FOR EACH ROW
SET NEW.salary = NEW.salary + 1000;

31. Transactions in MySQL

Used to maintain data consistency.

START TRANSACTION;
COMMIT;
ROLLBACK;

32. Normalization

Organizing data to reduce redundancy.
Levels include:

33. Denormalization

Combines tables to improve performance.

34. MySQL Security

Use strong passwords
Restrict user permissions
Backup databases

35. Backup and Restore

Backup:

mysqldump -u root -p company > backup.sql

Restore:

mysql -u root -p company < backup.sql

36. Performance Optimization

Use indexes
Avoid unnecessary queries
Use proper data types

37. MySQL with Programming Languages

MySQL works with:

PHP
Python
Java
Node.js

38. MySQL vs Other Databases

MySQL vs PostgreSQL
MySQL vs MongoDB
MySQL vs Oracle

39. Real-World MySQL Usage

E-commerce websites
Banking systems
Social media platforms
Business applications

40. Common MySQL Interview Questions

What is MySQL?

MySQL is an RDBMS used to store and manage structured data.

Difference between WHERE and HAVING?

WHERE filters rows before grouping, HAVING filters after grouping.

What is JOIN?

JOIN combines data from multiple tables.

What is Index?

Index improves database performance.

Conclusion

MySQL is a powerful database system widely used in web development and enterprise applications. Learning MySQL from beginner to advanced level helps developers manage data efficiently, optimize performance, and build scalable applications. Mastering queries, joins, transactions, and indexing is essential for both development and interview success.