# **MySQL Learning Path for Django Users (What to Teach)**
---
## **Phase 1: MySQL Basics**
1. **How to install MySQL and MySQL Workbench** – GUI and command-line.
2. **How to start, stop, and connect to MySQL server** – `mysql -u root -p`.
3. **Why databases are needed** – store structured data for apps.
4. **How to create and use a database**
```sql
CREATE DATABASE school_db;
USE school_db;
```
5. **How to create a user and grant privileges**
```sql
CREATE USER 'django_user'@'localhost' IDENTIFIED BY 'password123';
GRANT ALL PRIVILEGES ON school_db.* TO 'django_user'@'localhost';
FLUSH PRIVILEGES;
```
---
## **Phase 2: Tables and Data Types**
6. **How to create tables**
```sql
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
fullname VARCHAR(100),
age INT,
course VARCHAR(50),
date_registered DATETIME DEFAULT CURRENT_TIMESTAMP
);
```
7. **Why data types matter** – choosing INT, VARCHAR, DATE, etc.
8. **How to modify tables** – add columns, change types:
```sql
ALTER TABLE students ADD COLUMN email VARCHAR(100);
```
9. **How to delete tables** – `DROP TABLE students;`
---
## **Phase 3: CRUD in MySQL**
10. **How to insert data**
```sql
INSERT INTO students (fullname, age, course) VALUES ('Raheem', 22, 'Math');
```
11. **How to read/select data**
```sql
SELECT * FROM students;
SELECT fullname, course FROM students WHERE age > 20;
```
12. **How to update data**
```sql
UPDATE students SET age = 23 WHERE fullname = 'Raheem';
```
13. **How to delete data**
```sql
DELETE FROM students WHERE fullname = 'Raheem';
```
---
## **Phase 4: Filtering, Sorting, and Searching**
14. **How to filter data with WHERE**
```sql
SELECT * FROM students WHERE course='Math';
```
15. **How to use operators** – =, >, <, >=, <=, !=, LIKE, BETWEEN
16. **How to sort results with ORDER BY**
```sql
SELECT * FROM students ORDER BY fullname ASC;
```
17. **How to limit results**
```sql
SELECT * FROM students LIMIT 10;
```
---
## **Phase 5: Aggregate Functions & Grouping**
18. **How to count rows** – `SELECT COUNT(*) FROM students;`
19. **How to use SUM, AVG, MIN, MAX**
20. **How to group data** – `GROUP BY course;`
21. **How to filter groups with HAVING**
---
## **Phase 6: Relationships and Foreign Keys**
22. **How to create related tables** – students and courses:
```sql
CREATE TABLE courses (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50)
);
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
fullname VARCHAR(100),
age INT,
course_id INT,
FOREIGN KEY (course_id) REFERENCES courses(id)
);
```
23. **Why relationships matter** – normalize data, avoid duplicates
24. **How to join tables** – INNER JOIN, LEFT JOIN:
```sql
SELECT s.fullname, c.name AS course_name
FROM students s
INNER JOIN courses c ON s.course_id = c.id;
```
---
## **Phase 7: Indexes and Optimization**
25. **Why indexes are important** – speed up searches
26. **How to create an index**
```sql
CREATE INDEX idx_fullname ON students(fullname);
```
27. **How to view query execution plan** – `EXPLAIN SELECT * FROM students;`
---
## **Phase 8: Views, Stored Procedures & Triggers (Advanced)**
28. **How to create a view**
```sql
CREATE VIEW student_view AS
SELECT fullname, age FROM students WHERE age>20;
```
29. **How to create stored procedures** – encapsulate repetitive tasks
30. **How to create triggers** – automatic actions on INSERT/UPDATE/DELETE
---
## **Phase 9: Connecting MySQL with Django**
31. **How to update Django `settings.py` for MySQL**
32. **How to run migrations in MySQL** – `makemigrations` & `migrate`
33. **How to test CRUD from Django** – verify data in MySQL
34. **How to use ORM for queries instead of raw SQL**
---
## **Phase 10: Backup, Restore, and Security**
35. **How to backup a database** – `mysqldump`
36. **How to restore a database** – `mysql < backup.sql`
37. **How to secure MySQL users and passwords** – limited privileges
---
💡 **Teaching Tip:**
* For **beginners**, cover **Phases 1–4** first (basics and CRUD).
* For **intermediate**, teach **Phases 5–7** (aggregation, relationships, optimization).
* For **advanced students or web dev**, cover **Phases 8–10**, then integrate with Django.
---
0 comments:
Post a Comment