EasyEnv
MySQL
Intermediate

MySQL

Learn MySQL from CLI to schema design. SQL basics, joins, indexes, transactions, stored procedures, EXPLAIN, and operations.

13 lessons6.8h hands-on11 languages
Start learning freeMySQL course

13 lessons, in order

  1. 1

    What is MySQL

    Welcome. If you have never used a database before, you are in the right place. This lesson has no queries to write. We just want to know what MySQL...

    Relational databaseHistory and Oracle ownershipMariaDB forkMySQL vs Postgres trade-offsInnoDB default engine
    20 min
  2. 2

    The mysql CLI and First Commands

    The last lesson was all talk: what MySQL is, where it came from, why it exists. None of that lets you actually ask a server anything. A database...

    Mysql clientConnecting with -u and -pSHOW DATABASESUSESHOW TABLESDESCRIBE+2
    25 min
  3. 3

    Creating Databases and Tables

    Everything you did last lesson was look. You connected, browsed the system databases, and peeked inside a table that MySQL had already built for you....

    CREATE DATABASECharacter sets and collationCREATE TABLEPRIMARY KEYAUTO_INCREMENTNOT NULL+3
    30 min
  4. 4

    Data Types

    Look back at the products table from the last lesson and you will notice every column had a type: INT, VARCHAR(120), DECIMAL(10, 2). Those were not...

    INT and BIGINTDECIMAL vs FLOATVARCHAR vs TEXTBOOLEAN as TINYINTDATE vs DATETIME vs TIMESTAMPENUM+1
    30 min
  5. 5

    SELECT and WHERE

    Your table has ten thousand products in it and you need the six that are out of stock in the electronics category. Everything you will ever do with a...

    SELECT columnsWHEREAND and ORBETWEENINLIKE with % and _+3
    30 min
  6. 6

    Joins

    Your users live in one table and their orders live in another, and the only thread connecting an order to the person who placed it is a number in a...

    INNER JOINLEFT JOINRIGHT JOINNo FULL OUTER JOINUNION workaroundSelf-join+1
    35 min
  7. 7

    Aggregates and Grouping

    Somebody asks how the EU region did last quarter. They do not want four thousand rows, they want one number, and if you fetch all four thousand rows...

    COUNTSUMAVGMIN and MAXGROUP BYHAVING+2
    30 min
  8. 8

    Indexes

    The login endpoint worked fine with two hundred test users. In production, with four million, WHERE email = '[email protected]' takes eight seconds,...

    B-tree indexCREATE INDEXMulti-column index left-prefix ruleUNIQUE indexFULLTEXT indexIndexes hurt write performance
    30 min
  9. 9

    Transactions and Storage Engines

    Two UPDATE statements move twenty pounds from Ada to Bilal. The first runs, the connection drops, the second never happens, and twenty pounds have...

    InnoDB vs MyISAMSTART TRANSACTIONCOMMITROLLBACKIsolation levelsREPEATABLE READ default+1
    35 min
  10. 10

    Stored Procedures and Triggers

    Every order in the system is supposed to be written to an audit log. Most of them are. The ones that are not came from the admin console, the nightly...

    DELIMITERCREATE PROCEDUREIN and OUT parametersCALLCREATE TRIGGERBEFORE INSERT and AFTER UPDATE+1
    35 min
  11. 11

    EXPLAIN and Query Tuning

    "This query is slow." You will hear that sentence for the rest of your career, and the tempting response is to start changing things: add an index...

    EXPLAINType column (ALL, ref, range, eq_ref, const)Key and rows columnsEXPLAIN ANALYZE (MySQL 8+)Adding a missing indexSlow query log
    35 min
  12. 12

    mysqldump and Restore

    The nightly job has been writing a file to disk for eight months and nobody has ever opened it. That is not a backup, that is a hopeful file. A...

    MysqldumpRedirecting to .sqlRestoring with mysql client--single-transaction for InnoDB--no-data for schema only--all-databases
    25 min
  13. 13

    Capstone: Design an Ecommerce Schema

    Every lesson so far handed you a table and asked you to query it. Real work runs the other way round: somebody describes a shop, and you have to...

    Foreign keysON DELETE behaviorIndexes on lookup columnsTransactional order creationEXPLAIN on a real query
    50 min
MySQL Course: Hands-on, in 11 Languages | Engineering Courses