Database

MySQL in a Nutshell: From Zero to Keys

A comprehensive summary of the essential MySQL concepts, covering everything from basic DDL to advanced joins and aggregation.

Mandy · February 21, 2026
Data

While organizing our technical knowledge base, we revisited the classic “Bro Code” SQL course. It’s a fantastic resource for beginners and a great refresher for pros. To keep these insights handy, I’ve compiled a detailed “scholar’s notebook” based on the complete course structure.

The core logic of this tutorial flows from Setup -> Data Operations (CRUD) -> Advanced Queries.

Phase 1: Building the Structure (DDL - Data Definition)

  1. CREATE DATABASE: Where it all begins.

    CREATE DATABASE myDB;
USE myDB; -- Don't forget to switch to it!

  2. CREATE TABLE: Defining your schema.

    • Focus on Data Types: INT, VARCHAR, DECIMAL, DATE.
    • Example: CREATE TABLE employees (id INT, name VARCHAR(50));

  3. ALTER TABLE: Fixing design regrets.

    • ADD (new column), RENAME (table/column), DROP (remove column), MODIFY (change type).

Phase 2: Playing with Data (DML - Data Manipulation)

  1. INSERT INTO: Adding data.

    • INSERT INTO employees VALUES (1, 'Spongebob');

  2. SELECT: The most important command.

    • SELECT * (everything) vs SELECT name (specific columns).
    • WHERE: The filter (age >= 18).

  3. UPDATE: Modifying existing data.

    • ⚠️ High Risk: Always include a WHERE clause!
    • UPDATE employees SET hourly_pay = 10.50 WHERE id = 1;

  4. DELETE: Removing data.

    • ⚠️ Critical Risk: Missing WHERE means wiping the table—a precursor to “rm -rf /”.

Phase 3: Setting Rules (Constraints)

Constraints keep your data clean and reliable.

  1. NOT NULL: Field cannot be empty.
  2. UNIQUE: No duplicates allowed (e.g., emails).
  3. DEFAULT: Fallback value if none is provided (DEFAULT 0).
  4. PRIMARY KEY: The fingerprint. Unique + Not Null.
  5. AUTO_INCREMENT: Let the ID count itself.
  6. FOREIGN KEY: The bridge between tables (prevents orphan data).

Phase 4: Advanced Query Skills

  1. Functions:

    • COUNT(): Headcount.
    • MAX() / MIN(): Extremes.
    • AVG(): Averages.
    • SUM(): Totals.

  2. Wildcards (LIKE):

    • %: Matches any number of characters (LIKE 'a%' = everyone starting with ‘a’).
    • _: Matches exactly one character.

  3. ORDER BY: Sorting.

    • DESC (Descending), ASC (Ascending).

  4. LIMIT: Pagination tool.

    • LIMIT 10: First 10 rows.
    • LIMIT 10, 10: Skip 10, take next 10.

Phase 5: Combination Skills (Joins & Unions)

  1. UNION: Vertical stacking.

    • Combines results from two queries into one list (columns must match).

  2. JOINs: Horizontal stitching.

    • INNER JOIN: The intersection (only matches).
    • LEFT JOIN: All from Left, matches from Right (NULL if no match).
    • RIGHT JOIN: The reverse.

Phase 6: Advanced Logic

  1. Views:

    • Saving a complex query as a “virtual table” for easy access later.

  2. Indexes:

    • Like a book’s table of contents. Speeds up reads, slows down writes (updates).

  3. Subqueries:

    • Nested queries. SELECT ... WHERE id IN (SELECT ...).

  4. GROUP BY: Categorizing.

    • Used with aggregations (e.g., “Average salary per department”).

  5. HAVING:

    • The WHERE clause for groups. (Key interview distinction: WHERE filters rows, HAVING filters groups).
← Back to Blog