MySQL Reference Guide

1

Database Operations

Create, list, select, and drop databases

Create a Database

DDL

-- Create a new database for a school management system
CREATE DATABASE school_db;

-- Safer version: only creates if it doesn't already exist
CREATE DATABASE IF NOT EXISTS school_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

What it does: Creates a new database named school_db.
IF NOT EXISTS prevents an error if the database already exists — always use this in production scripts.
CHARACTER SET utf8mb4 supports full Unicode including emojis. COLLATE utf8mb4_unicode_ci sets case-insensitive comparison rules.

Show All Databases

READ

SHOW DATABASES;

Example Output

Database
information_schema
mysql
performance_schema
school_db

Lists every database on the MySQL server, including system databases like information_schema and mysql.

Select / Switch Database

DDL

USE school_db;

-- Verify which database is currently active
SELECT DATABASE();

USE sets the active database for all subsequent queries. Think of it as "changing into a folder" before running commands inside it.

Drop a Database

DDL

-- ⚠️  IRREVERSIBLE — deletes everything inside
DROP DATABASE IF EXISTS school_db;

⚠️ Warning: This permanently deletes the database and all its tables and data. Always back up before dropping.

2

Table Operations

Create, inspect, and drop tables with constraints

Create a Table with Constraints

DDL

CREATE TABLE IF NOT EXISTS students (
  student_id   INT           AUTO_INCREMENT PRIMARY KEY,
  first_name   VARCHAR(50)   NOT NULL,
  last_name    VARCHAR(50)   NOT NULL,
  email        VARCHAR(100)  UNIQUE NOT NULL,
  age          TINYINT UNSIGNED,
  class_id     INT,
  enrolled_at  DATETIME      DEFAULT CURRENT_TIMESTAMP,

  -- Foreign key reference to the classes table
  CONSTRAINT fk_class
    FOREIGN KEY (class_id) REFERENCES classes(class_id)
    ON DELETE SET NULL
    ON UPDATE CASCADE
);

AUTO_INCREMENT — Automatically assigns the next integer ID on each insert.
PRIMARY KEY — Uniquely identifies each row; implies NOT NULL + UNIQUE.
NOT NULL — Field must always have a value.
UNIQUE — No two rows can share the same email.
DEFAULT CURRENT_TIMESTAMP — Auto-fills with the current date/time if not provided.
FOREIGN KEY … ON DELETE SET NULL — If the referenced class is deleted, set class_id to NULL instead of crashing.

Show Tables & Describe Structure

READ

-- List all tables in the active database
SHOW TABLES;

-- Show column definitions for the students table
DESCRIBE students;

-- Full CREATE TABLE statement (useful for backups)
SHOW CREATE TABLE students;

DESCRIBE students — Output

Field	Type	Null	Key	Default	Extra
student_id	int	NO	PRI	NULL	auto_increment
first_name	varchar(50)	NO		NULL
email	varchar(100)	NO	UNI	NULL
enrolled_at	datetime	YES		CURRENT_TIMESTAMP

Drop / Modify a Table

DDL

-- Delete the table permanently
DROP TABLE IF EXISTS students;

-- Add a new column to an existing table
ALTER TABLE students
  ADD COLUMN phone VARCHAR(20) AFTER email;

-- Remove a column
ALTER TABLE students
  DROP COLUMN phone;

ALTER TABLE is the safe way to modify structure without losing existing data. DROP TABLE is permanent and removes both structure and data.

3

CRUD Operations

Create, Read, Update, Delete — the four pillars of data manipulation

INSERT — Add Records

DML

-- Single record insert
INSERT INTO students (first_name, last_name, email, age)
VALUES ('Alice', 'Mugisha', '[email protected]', 20);

-- Multiple records in one statement (more efficient)
INSERT INTO students (first_name, last_name, email, age)
VALUES
  ('Bob',     'Nkurunziza', '[email protected]',    22),
  ('Claire', 'Uwimana',    '[email protected]', 19),
  ('David',  'Habimana',   '[email protected]',  21);

-- Insert or update if duplicate key exists
INSERT INTO students (email, first_name)
VALUES ('[email protected]', 'Alice')
ON DUPLICATE KEY UPDATE first_name = VALUES(first_name);

Multi-row INSERT is significantly faster than running individual INSERT statements in a loop. ON DUPLICATE KEY UPDATE is the MySQL equivalent of an "upsert" — update if exists, insert if not.

SELECT — Read Records

SELECT

-- Select all columns
SELECT * FROM students;

-- Select specific columns with an alias
SELECT
  student_id,
  CONCAT(first_name, ' ', last_name) AS full_name,
  email,
  age
FROM students
WHERE age >= 20
ORDER BY last_name ASC
LIMIT 10;

Example Output

student_id	full_name	email	age
4	David Habimana	[email protected]	21
1	Alice Mugisha	[email protected]	20
2	Bob Nkurunziza	[email protected]	22

ORDER BY … ASC/DESC sorts results. LIMIT restricts the number of rows returned — essential for pagination. AS renames a column in the output (alias).

UPDATE — Modify Records

DML

-- Update a single student's age
UPDATE students
SET    age = 21
WHERE  student_id = 1;

-- Update multiple columns at once
UPDATE students
SET
  email      = '[email protected]',
  last_name  = 'Mugisha-Nkusi'
WHERE student_id = 1;

Always include a WHERE clause on UPDATE. Without it, every row in the table gets updated — a common and costly mistake.

DELETE — Remove Records Safely

DML

-- Delete a single record
DELETE FROM students
WHERE student_id = 4;

-- Delete with multiple conditions
DELETE FROM students
WHERE age < 18 AND class_id IS NULL;

-- Preview what you're about to delete first
SELECT * FROM students
WHERE age < 18 AND class_id IS NULL;

-- Remove ALL rows but keep the table structure
TRUNCATE TABLE students;

Best practice: Run the equivalent SELECT first to confirm what will be deleted before executing DELETE. TRUNCATE is faster than DELETE for clearing a whole table — it resets AUTO_INCREMENT too.

4

Filtering & Conditions

WHERE clause operators and logical conditions

Comparison & Range Operators

SELECT

-- Exact match
SELECT * FROM students WHERE age = 20;

-- Greater than / Less than
SELECT * FROM students WHERE age > 20;
SELECT * FROM students WHERE age <= 21;

-- Range (inclusive on both ends)
SELECT * FROM students
WHERE age BETWEEN 18 AND 25;

-- Pattern matching: % = any chars, _ = one char
SELECT * FROM students
WHERE email LIKE '%@school.rw';

-- Match from a list of values
SELECT * FROM students
WHERE class_id IN (1, 2, 5);

-- NULL check (never use = NULL)
SELECT * FROM students
WHERE class_id IS NULL;

BETWEEN is inclusive — BETWEEN 18 AND 25 includes both 18 and 25.
LIKE '%@school.rw' — the % matches any characters before the @.
IN is cleaner than writing multiple OR conditions.
IS NULL must be used instead of = NULL because NULL is not equal to anything — not even itself.

Logical Operators: AND, OR, NOT

SELECT

-- AND: both conditions must be true
SELECT * FROM students
WHERE age >= 20 AND class_id = 3;

-- OR: at least one condition must be true
SELECT * FROM students
WHERE class_id = 1 OR class_id = 2;

-- NOT: excludes matches
SELECT * FROM students
WHERE age NOT BETWEEN 18 AND 20;

-- Combining with parentheses for clarity
SELECT * FROM students
WHERE (age > 20 OR class_id = 1)
  AND email LIKE '%@school.rw';

Use parentheses when mixing AND/OR to make precedence explicit and avoid logic bugs. AND has higher precedence than OR by default.

5

Joins

Combine data from multiple related tables

INNER JOIN — Matching rows only

JOIN

-- Returns students that HAVE a class assigned
SELECT
  s.student_id,
  CONCAT(s.first_name, ' ', s.last_name) AS student_name,
  c.class_name,
  c.teacher
FROM students s
INNER JOIN classes c ON s.class_id = c.class_id;

INNER JOIN returns only rows where the join condition matches in both tables. Students without a class_id (NULL) are excluded.

Example Output

student_id	student_name	class_name	teacher
1	Alice Mugisha	Mathematics	Mr. Kamanzi
2	Bob Nkurunziza	Computer Science	Ms. Uwera

LEFT JOIN — All left rows, matched or not

JOIN

-- Returns ALL students, even those without a class
SELECT
  s.first_name,
  s.last_name,
  COALESCE(c.class_name, 'Not Enrolled') AS class_name
FROM students s
LEFT JOIN classes c ON s.class_id = c.class_id;

LEFT JOIN returns all rows from the left table (students) and matching rows from the right. If no match, right-side columns are NULL. COALESCE replaces NULL with a fallback value.

RIGHT JOIN — All right rows, matched or not

JOIN

-- Returns ALL classes, even those with no students
SELECT
  c.class_name,
  c.teacher,
  COUNT(s.student_id) AS student_count
FROM students s
RIGHT JOIN classes c ON s.class_id = c.class_id
GROUP BY c.class_id, c.class_name, c.teacher;

RIGHT JOIN keeps all rows from the right table (classes), even classes with zero students. Combining it with COUNT gives each class's enrollment count, including empty ones.

6

Aggregate Functions

COUNT, SUM, AVG, MAX, MIN — Group and summarize data

Core Aggregate Functions

AGGREGATE

-- Total number of students
SELECT COUNT(*) AS total_students FROM students;

-- Count students who have a class assigned
SELECT COUNT(class_id) AS enrolled FROM students;

-- Age statistics
SELECT
  MIN(age)  AS youngest,
  MAX(age)  AS oldest,
  AVG(age)  AS average_age,
  SUM(age)  AS total_age_sum
FROM students;

Example Output

youngest	oldest	average_age	total_age_sum
19	22	20.50	82

GROUP BY & HAVING

AGGREGATE

-- Count students per class
SELECT
  c.class_name,
  COUNT(s.student_id) AS student_count,
  ROUND(AVG(s.age), 1)  AS avg_age
FROM students s
INNER JOIN classes c ON s.class_id = c.class_id
GROUP BY c.class_id, c.class_name

-- HAVING filters AFTER grouping (WHERE filters before)
HAVING COUNT(s.student_id) >= 5

ORDER BY student_count DESC;

GROUP BY collapses multiple rows into one summary row per group.
HAVING filters on aggregated results (you can't use WHERE to filter on COUNT).
Rule: Use WHERE for raw row conditions, HAVING for conditions on aggregate results.

7

Indexes & Constraints

Speed up queries and enforce data integrity rules

Creating & Managing Indexes

DDL

-- Create an index on frequently searched column
CREATE INDEX idx_student_email
  ON students (email);

-- Composite index for queries filtering by class + age
CREATE INDEX idx_class_age
  ON students (class_id, age);

-- View all indexes on a table
SHOW INDEX FROM students;

-- Remove an index
DROP INDEX idx_student_email ON students;

-- See how MySQL uses indexes for a query
EXPLAIN SELECT * FROM students
WHERE email = '[email protected]';

An index is like a book's table of contents — it lets MySQL find rows without scanning the entire table.

When to index: Columns used frequently in WHERE, JOIN ON, or ORDER BY clauses.
When NOT to: Small tables, columns rarely queried, or tables with very frequent INSERT/UPDATE operations (indexes slow writes).
EXPLAIN reveals whether MySQL uses an index or does a full table scan (type: ALL = no index used).

Constraints Summary

DDL

-- CHECK constraint: enforce business rules at DB level
ALTER TABLE students
  ADD CONSTRAINT chk_age
  CHECK (age >= 16 AND age <= 100);

-- DEFAULT constraint: auto-fill if value not provided
ALTER TABLE students
  ALTER COLUMN age SET DEFAULT 18;

-- NOT NULL on existing column
ALTER TABLE students
  MODIFY email VARCHAR(100) NOT NULL;

Constraints enforce data quality at the database level, providing a safety net independent of application-layer validation.

PRIMARY KEY — Unique identifier per row, never NULL.
FOREIGN KEY — Ensures referential integrity between tables.
UNIQUE — No duplicates allowed in that column.
NOT NULL — Field must have a value.
CHECK — Custom rule (e.g., age must be between 16–100).
DEFAULT — Fallback value when none is provided.

8

Real-World Example

Student Management System — complete schema & realistic queries

classes

class_idPK

class_nameVARCHAR

teacherVARCHAR

capacityTINYINT

students

student_idPK

first_nameVARCHAR

last_nameVARCHAR

emailUNIQUE

ageTINYINT

class_idFK

grades

grade_idPK

student_idFK

subjectVARCHAR

scoreDECIMAL

exam_dateDATE

Complete Schema Setup

DDL

CREATE DATABASE IF NOT EXISTS sms_db;
USE sms_db;

CREATE TABLE classes (
  class_id    INT           AUTO_INCREMENT PRIMARY KEY,
  class_name  VARCHAR(100)  NOT NULL,
  teacher     VARCHAR(100)  NOT NULL,
  capacity    TINYINT UNSIGNED DEFAULT 30
);

CREATE TABLE students (
  student_id  INT           AUTO_INCREMENT PRIMARY KEY,
  first_name  VARCHAR(50)   NOT NULL,
  last_name   VARCHAR(50)   NOT NULL,
  email       VARCHAR(100)  UNIQUE NOT NULL,
  age         TINYINT UNSIGNED,
  class_id    INT,
  FOREIGN KEY (class_id) REFERENCES classes(class_id)
    ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE grades (
  grade_id    INT           AUTO_INCREMENT PRIMARY KEY,
  student_id  INT           NOT NULL,
  subject     VARCHAR(80)   NOT NULL,
  score       DECIMAL(5,2)  NOT NULL,
  exam_date   DATE          NOT NULL,
  FOREIGN KEY (student_id) REFERENCES students(student_id)
    ON DELETE CASCADE
);

Seed Data

DML

INSERT INTO classes (class_name, teacher, capacity) VALUES
  ('Computer Science', 'Ms. Uwera',   25),
  ('Mathematics',      'Mr. Kamanzi', 30),
  ('Physics',          'Dr. Mutesa',  20);

INSERT INTO students (first_name, last_name, email, age, class_id) VALUES
  ('Alice',  'Mugisha',    '[email protected]',  20, 1),
  ('Bob',    'Nkurunziza','[email protected]',    22, 2),
  ('Claire','Uwimana',    '[email protected]', 19, 1),
  ('David', 'Habimana',   '[email protected]',  21, 3);

INSERT INTO grades (student_id, subject, score, exam_date) VALUES
  (1, 'Programming', 87.50, '2024-03-15'),
  (1, 'Databases',   92.00, '2024-03-20'),
  (2, 'Algebra',     78.00, '2024-03-15'),
  (3, 'Programming', 95.00, '2024-03-15'),
  (4, 'Mechanics',   83.00, '2024-03-18');

Full Student Report Card

SELECT

-- Student name, class, and their average grade
SELECT
  CONCAT(s.first_name, ' ', s.last_name) AS student,
  c.class_name,
  COUNT(g.grade_id)               AS exams_taken,
  ROUND(AVG(g.score), 2)           AS average_score,
  MAX(g.score)                    AS highest_score,
  CASE
    WHEN AVG(g.score) >= 90 THEN 'A'
    WHEN AVG(g.score) >= 80 THEN 'B'
    WHEN AVG(g.score) >= 70 THEN 'C'
    ELSE 'F'
  END AS grade_letter
FROM students s
INNER JOIN classes  c ON s.class_id   = c.class_id
INNER JOIN grades   g ON s.student_id = g.student_id
GROUP BY s.student_id, s.first_name, s.last_name, c.class_name
ORDER BY average_score DESC;

Example Output

student	class_name	exams_taken	average_score	highest_score	grade_letter
Alice Mugisha	Computer Science	2	89.75	92.00	B
Claire Uwimana	Computer Science	1	95.00	95.00	A
David Habimana	Physics	1	83.00	83.00	B
Bob Nkurunziza	Mathematics	1	78.00	78.00	C

This query demonstrates a real-world report combining multiple JOINs, aggregate functions, GROUP BY, and a CASE expression to translate numeric scores into letter grades — all in a single query.

Top Performers per Class

AGGREGATE

-- Find the best student in each class
SELECT
  c.class_name,
  CONCAT(s.first_name, ' ', s.last_name) AS top_student,
  ROUND(AVG(g.score), 2) AS avg_score
FROM students s
JOIN classes c ON s.class_id = c.class_id
JOIN grades  g ON s.student_id = g.student_id
GROUP BY c.class_id, s.student_id
ORDER BY c.class_id, avg_score DESC
LIMIT 1;

MySQL Fundamentalsfor Developers

MySQL Fundamentals
for Developers