Learn MySQL from Scratch
Beginner-friendly guide to databases, tables, queries, joins, and more. Follow the learning path, try examples, and check your understanding.
Quick Facts
- SQL stands for Structured Query Language
- MySQL is a popular SQL database server
- Data lives in databases and tables
- Use SELECT to read data, INSERT to add, UPDATE to change, DELETE to remove
Learning Path Flowchart
graph TD
A[Start Here] --> B[Install MySQL]
B --> C[Core Concepts: Database, Table, Row, Column, Types]
C --> D[CRUD: SELECT/INSERT/UPDATE/DELETE]
D --> E[Filtering, Sorting, Limiting]
E --> F[Aggregations: COUNT, SUM, AVG, GROUP BY]
F --> G[Joins: INNER, LEFT, RIGHT]
G --> H[Design: Keys, Constraints, Normalization]
H --> I[Practice & Quiz]
I --> J[Next: Indexes, Transactions]
SQL Query Execution Flow
flowchart LR
Q[Write SQL] --> P[Parse]
P --> O[Optimize]
O --> EX[Execute]
EX --> RS[Result Set]
Getting Started
What is MySQL?
MySQL is a relational database management system. Data is organized into tables with rows and columns. You use SQL to create structures and query data.
Install
- Download MySQL Community Server from dev.mysql.com
- Install a GUI client like MySQL Workbench
- Connect to localhost with your username and password
Create Your First Database
SQL
CREATE DATABASE school;
USE school;
CREATE TABLE students (
id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
age INT,
grade VARCHAR(5)
);
INSERT INTO students (first_name, last_name, age, grade) VALUES
('Asha', 'Verma', 14, '9'),
('Ravi', 'Kumar', 15, '10'),
('Neha', 'Singh', 13, '8');Paste this in Sql input and select all and test
SQL
CREATE TABLE IF NOT EXISTS kali (
id INTEGER PRIMARY KEY AUTOINCREMENT,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
age INTEGER,
grade TEXT,
kamei TEXT,
kioi INTEGER,
make TEXT
);
INSERT INTO kamli (first_name, last_name, age, grade, kamei, kioi, make)
VALUES ('a','b',25,'A','k1',123,'m1');
Fundamentals
Data Types
- INT, BIGINT
- DECIMAL, FLOAT
- DATE, DATETIME
- CHAR, VARCHAR, TEXT
Constraints
- PRIMARY KEY
- FOREIGN KEY
- UNIQUE
- NOT NULL
Naming
- snake_case for names
- Meaningful table and column names
CRUD
Read Data
SQL
SELECT id, first_name, last_name, grade
FROM students
WHERE age >= 14
ORDER BY last_name ASC
LIMIT 5;Insert
SQL
INSERT INTO students (first_name, last_name, age, grade)
VALUES ('Isha', 'Patel', 14, '9');Update
SQL
UPDATE students
SET grade = '10'
WHERE id = 1;Delete
SQL
DELETE FROM students
WHERE id = 3;Filtering, Sorting, Limiting
- WHERE filters rows
- ORDER BY sorts results
- LIMIT restricts number of rows
SQL
SELECT *
FROM students
WHERE grade IN ('9','10') AND age BETWEEN 13 AND 16
ORDER BY age DESC, last_name ASC
LIMIT 10;Aggregations
- COUNT, SUM, AVG, MIN, MAX
- GROUP BY groups rows
- HAVING filters groups
SQL
SELECT grade, COUNT(*) AS num_students, AVG(age) AS avg_age
FROM students
GROUP BY grade
HAVING COUNT(*) >= 1
ORDER BY grade;Joins
Combine rows from multiple tables using matching keys.
- INNER JOIN returns matching rows
- LEFT JOIN keeps all left rows
- RIGHT JOIN keeps all right rows
SQL
CREATE TABLE grades (
student_id INT,
subject VARCHAR(50),
score INT,
FOREIGN KEY (student_id) REFERENCES students(id)
);
SELECT s.first_name, s.last_name, g.subject, g.score
FROM students s
INNER JOIN grades g ON g.student_id = s.id
WHERE g.score >= 75
ORDER BY g.score DESC;Keys, Constraints, Normalization
- Primary keys uniquely identify a row
- Foreign keys link related tables
- Normalization reduces duplication (1NF, 2NF, 3NF)
SQL
CREATE TABLE subjects (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) UNIQUE NOT NULL
);
CREATE TABLE enrollments (
student_id INT,
subject_id INT,
PRIMARY KEY (student_id, subject_id),
FOREIGN KEY (student_id) REFERENCES students(id),
FOREIGN KEY (subject_id) REFERENCES subjects(id)
);Quick Quiz
1) Which statement reads data?
2) Which clause sorts results?
3) Which join returns only matching rows?