Skip to content
Tutorials Center
My learning

Topics

MySQL Tutorial

Learn MySQL step by step: 75 lessons in 14 chapters with examples, quizzes, diagrams and projects.

Start learning →

of lessons completed

Getting Started with MySQL

  1. 1 What Is MySQL? Learn what MySQL is, how it differs from MariaDB and PostgreSQL, how the 8.0, 8.4 LTS and 9.x releases work, and what you can run live in this tutorial.
  2. 2 MySQL vs PostgreSQL: Choosing Compare MySQL and PostgreSQL: governance, SQL features, JSON, replication, concurrency design and typical use cases, with the syntax differences that bite when you switch.
  3. 3 Installing MySQL and Connecting Install MySQL 8.4 with an installer or Docker, connect with the mysql client, MySQL Shell or Workbench, and understand users, hosts and connection options.
  4. 4 Using the mysql Command-Line Client Get fluent in the mysql client: SHOW and DESCRIBE, USE, source, vertical output with \G, pager, batch mode with --execute, custom prompt and history.
  5. 5 Your First Database and Table Create your first MySQL database and table, insert and select rows, understand utf8mb4 and collation defaults, and see how MySQL differs from SQLite.
  6. 6 SQL Modes and Strictness Learn what MySQL sql_mode does: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_DATE and ANSI_QUOTES, why strict mode matters, and how to set it.

Data Types

  1. 7 Numeric Types Pick the right MySQL numeric type: TINYINT to BIGINT, UNSIGNED, DECIMAL vs FLOAT and DOUBLE, BIT and BOOLEAN, plus how overflow behaves in strict mode.
  2. 8 String Types Learn the MySQL string types: CHAR vs VARCHAR, the TEXT family, BINARY, VARBINARY and BLOB, plus length limits, row size and how bytes differ from characters.
  3. 9 Date and Time Types Learn MySQL DATE, DATETIME, TIMESTAMP, TIME and YEAR, fractional seconds, time zone conversion, the 2038 limit and how to choose between DATETIME and TIMESTAMP.
  4. 10 Character Sets and Collations Understand MySQL character sets and collations: utf8mb4 vs utf8mb3, names like utf8mb4_0900_ai_ci, case and accent sensitivity, per-column settings and index impact.
  5. 11 ENUM and SET in Practice See how MySQL ENUM and SET store values as numbers, how they sort, why ALTER TABLE is risky, and when a lookup table or CHECK constraint is the better choice.
  6. 12 Choosing the Right Data Types Pick MySQL column types with confidence: smallest sufficient integers, DECIMAL for money, BINARY(16) UUIDs, INET6_ATON for IPs, booleans and the effect on indexes.
  7. 13 Generated Columns Learn MySQL generated columns: VIRTUAL vs STORED, expression rules, indexing a generated column, extracting JSON fields and functional indexes with live SQLite examples.

Tables, Constraints and Schema Design

  1. 14 CREATE TABLE in Depth Master MySQL CREATE TABLE: column options, DEFAULT expressions, AUTO_INCREMENT, COMMENT, ENGINE and CHARSET table options, LIKE, AS SELECT and temporary tables.
  2. 15 Copying, Cloning and Truncating Tables Copy and reset MySQL tables with CREATE TABLE LIKE, AS SELECT and INSERT SELECT, empty them with TRUNCATE and swap tables atomically with RENAME TABLE.
  3. 16 Temporary Tables and Derived Data Understand MySQL temporary tables: scope, transactions, the reopen limit, CTE alternatives and internal temp tables controlled by tmp_table_size.
  4. 17 Keys and Constraints Learn MySQL PRIMARY KEY, UNIQUE, NOT NULL and CHECK constraints (enforced since 8.0.16), constraint naming and invisible columns, with live examples.
  5. 18 Foreign Keys in MySQL Learn how MySQL foreign keys work in InnoDB: ON DELETE and ON UPDATE actions, FOREIGN_KEY_CHECKS, indexing rules and the cascade pitfalls to avoid.
  6. 19 ALTER TABLE and Online DDL Change MySQL tables safely: ADD, MODIFY, CHANGE and DROP, the INSTANT, INPLACE and COPY algorithms, the LOCK clause, metadata locks and gh-ost or pt-osc.
  7. 20 Schema Design Patterns for MySQL Practical MySQL schema design: normalization trade-offs, surrogate vs natural keys, soft deletes, audit columns, many-to-many tables, the EAV trap and JSON vs columns.
  8. 21 AUTO_INCREMENT, UUIDs and ID Strategies How MySQL AUTO_INCREMENT really behaves (gaps, LAST_INSERT_ID, offsets), plus UUID(), UUID_TO_BIN, ordered UUIDs and snowflake-style IDs, with live stand-ins.
  9. 22 Views Learn MySQL views: CREATE VIEW, MERGE and TEMPTABLE algorithms, updatable views, WITH CHECK OPTION, SQL SECURITY DEFINER vs INVOKER and the limits of views.

Querying with MySQL

  1. 23 SELECT: MySQL Specifics The parts of SELECT that are special in MySQL: backtick quoting, LIMIT and OFFSET, SQL_CALC_FOUND_ROWS history, STRAIGHT_JOIN, index hints and string comparison.
  2. 24 GROUP BY, HAVING and Aggregates in MySQL Master GROUP BY in MySQL: ONLY_FULL_GROUP_BY, ANY_VALUE, GROUP_CONCAT with ORDER BY and SEPARATOR, WITH ROLLUP subtotals and how aggregates treat NULL.
  3. 25 Joins in MySQL Learn joins in MySQL: INNER, LEFT and RIGHT, emulating FULL JOIN with UNION, CROSS and self joins, nested loop and hash join, and multi-table UPDATE and DELETE.
  4. 26 Subqueries, Derived Tables and LATERAL Learn MySQL subqueries: scalar, row and table subqueries, IN vs EXISTS, correlated subqueries, derived table merging and LATERAL derived tables from 8.0.14.
  5. 27 UNION, INTERSECT and EXCEPT Combine query results in MySQL: UNION vs UNION ALL, INTERSECT and EXCEPT added in 8.0.31, operator precedence, and how ORDER BY and LIMIT work with unions.
  6. 28 Common Table Expressions and Recursive CTEs Learn MySQL common table expressions: WITH, multiple CTEs, recursive hierarchies, cte_max_recursion_depth and number or date series generation.
  7. 29 Window Functions Learn MySQL window functions: OVER, PARTITION BY, ROW_NUMBER, RANK, DENSE_RANK, LAG and LEAD, frames, named windows and running totals.
  8. 30 Strings, Dates and Math Functions A practical tour of MySQL functions: CONCAT_WS, SUBSTRING_INDEX, REGEXP_REPLACE, DATE_FORMAT, DATE_ADD, TIMESTAMPDIFF, ROUND vs TRUNCATE and SQLite equivalents.
  9. 31 CAST, CONVERT and Implicit Conversion Learn MySQL type conversion: CAST and CONVERT, CONVERT USING for character sets, STR_TO_DATE, and the implicit coercion surprises that break comparisons and indexes.
  10. 32 NULL Handling and Comparison Rules Understand NULL in MySQL: three-valued logic, the null-safe <=> operator, IFNULL and COALESCE, and the implicit type conversion traps in comparisons.

Changing Data

  1. 33 INSERT, UPDATE and DELETE in MySQL Change data in MySQL safely: multi-row INSERT, INSERT SET, UPDATE and DELETE with ORDER BY and LIMIT, TRUNCATE versus DELETE and the danger of REPLACE.
  2. 34 Upserts: ON DUPLICATE KEY UPDATE and INSERT IGNORE Insert a row or update it if it exists: MySQL ON DUPLICATE KEY UPDATE, the row alias replacing VALUES(), INSERT IGNORE hazards, REPLACE, affected rows and deadlocks.
  3. 35 Bulk Loading and Exporting Data Move large amounts of data in and out of MySQL: LOAD DATA INFILE, secure_file_priv, SELECT INTO OUTFILE, mysqlimport, MySQL Shell importTable and insert batching.

JSON and Modern Features

  1. 36 The JSON Data Type Store and query JSON in MySQL: the JSON column type, the -> and ->> operators, JSON_EXTRACT, JSON_SET, JSON_INSERT, JSON_REPLACE, JSON_REMOVE and validation.
  2. 37 JSON_TABLE, Multi-Valued Indexes and JSON Performance Turn JSON arrays into rows with JSON_TABLE, search them with MEMBER OF and JSON_CONTAINS, and make JSON fast with multi-valued and generated-column indexes.
  3. 38 Full-Text Search Search text in MySQL with FULLTEXT indexes and MATCH AGAINST: natural-language and boolean modes, relevance, stopwords, ngram for CJK, and when to use a search engine instead.
  4. 39 Spatial Data and Geometry Store places and areas in MySQL with POINT and POLYGON columns, understand SRID 4326 and axis order, measure distances, use spatial indexes and exchange GeoJSON.

Storage Engines and Internals

  1. 40 Storage Engines Overview Learn what MySQL storage engines are, compare InnoDB, MyISAM, MEMORY and ARCHIVE, see why InnoDB is the default, and avoid the traps of mixing engines in one database.
  2. 41 InnoDB Architecture See how InnoDB stores and protects data: the clustered index, 16 KB pages, buffer pool, redo and undo logs, doublewrite buffer, change buffer and row formats.
  3. 42 The Data Dictionary, information_schema and sys Schema Let MySQL describe itself: the data dictionary, information_schema tables, performance_schema, sys views, finding large tables and unused indexes, SHOW CREATE TABLE.

Indexes and Performance

  1. 43 How MySQL Indexes Work Understand MySQL indexes from the inside: B-trees, the clustered primary key, secondary indexes that hold the primary key, covering indexes, selectivity and cardinality.
  2. 44 Designing Composite Indexes Design multi-column MySQL indexes: the leftmost prefix rule, equality before range, sorting from the index, prefix, descending, functional and invisible indexes.
  3. 45 EXPLAIN and EXPLAIN ANALYZE Read MySQL query plans: the type, key, rows, filtered and Extra columns, FORMAT=TREE and JSON output, EXPLAIN ANALYZE actual times, and how join order is chosen.
  4. 46 Query Optimization Techniques Speed up MySQL queries: sargable predicates, functions on indexed columns, implicit casts, keyset pagination versus OFFSET, SELECT *, subquery rewrites and hints.
  5. 47 Optimizer Hints, Index Hints and optimizer_trace Steer the MySQL optimizer: USE, FORCE and IGNORE INDEX, /*+ */ hints, invisible indexes, optimizer_switch and optimizer_trace explained.
  6. 48 Finding Slow Queries Find the queries that hurt: the slow query log, long_query_time, pt-query-digest, performance_schema digests, the sys schema and handler counters.
  7. 49 Server Configuration and Tuning Tune a MySQL server sensibly: innodb_buffer_pool_size, redo log capacity, flush settings, max_connections, temporary tables, my.cnf, SET PERSIST and sysbench.
  8. 50 Partitioning Split big MySQL tables with RANGE, LIST, HASH and KEY partitioning, learn partition pruning, the unique key rule, maintenance and when partitioning does not help.

Programmable Objects

  1. 51 Stored Procedures and Functions Write MySQL stored procedures and functions: DELIMITER, IN, OUT and INOUT parameters, variables, IF, CASE and loops, DETERMINISTIC, SQL SECURITY and calling them from code.
  2. 52 User Variables and Prepared Statements Use MySQL @user variables and run dynamic SQL safely with PREPARE, EXECUTE USING and DEALLOCATE PREPARE, including scope rules and injection pitfalls.
  3. 53 Cursors, Handlers and Error Handling Handle errors in MySQL routines: DECLARE CURSOR, CONTINUE and EXIT handlers, SIGNAL and RESIGNAL, GET DIAGNOSTICS and dynamic SQL with PREPARE and EXECUTE.
  4. 54 Triggers Learn MySQL triggers: BEFORE and AFTER INSERT, UPDATE and DELETE, the OLD and NEW rows, an audit log pattern, SIGNAL validation, limits, costs and alternatives.
  5. 55 Events and the Event Scheduler Learn MySQL events: CREATE EVENT, one-time and recurring schedules, the event_scheduler variable, a purge-old-rows pattern, and when cron is the better choice.

Transactions and Concurrency

  1. 56 Transactions and ACID in InnoDB Learn MySQL transactions in InnoDB: ACID, autocommit, START TRANSACTION, COMMIT, ROLLBACK, SAVEPOINT, statements that commit implicitly, and durability settings.
  2. 57 Isolation Levels and MVCC Understand MySQL isolation levels and InnoDB MVCC: dirty, non-repeatable and phantom reads, REPEATABLE READ vs READ COMMITTED, snapshots and undo logs.
  3. 58 Locking, Gap Locks and Deadlocks Learn InnoDB locking: row, gap and next-key locks, SELECT FOR UPDATE, NOWAIT and SKIP LOCKED, lock wait timeouts, reading deadlock reports and retrying safely.

Security and Administration

  1. 59 Users, Privileges and Roles Learn MySQL account management: CREATE USER, host patterns, GRANT and REVOKE, privilege levels, roles, caching_sha2_password, password policies and least privilege.
  2. 60 Securing MySQL Harden MySQL: TLS connections, network exposure, mysql_secure_installation, SQL injection and prepared statements, data-at-rest encryption, auditing and secrets handling.
  3. 61 Backup and Restore Back up MySQL with mysqldump, MySQL Shell dump utilities, XtraBackup and the Clone plugin, recover to a point in time with the binary log, and test restores.
  4. 62 The Binary Log and Replication Learn how the MySQL binary log works, set up GTID-based source and replica replication, understand semi-sync and lag, and use replicas for reads safely.
  5. 63 High Availability: Group Replication, InnoDB Cluster and Managed Services Understand MySQL Group Replication, InnoDB Cluster, MySQL Router, ProxySQL, Galera and managed services such as RDS and Aurora, plus a short sharding overview.
  6. 64 Upgrading and Monitoring MySQL Plan MySQL upgrades with LTS and innovation releases and the upgrade checker, then monitor key metrics with mysqld_exporter and Prometheus and plan capacity.

Using MySQL from Code

  1. 65 MySQL from PHP with PDO Connect PHP to MySQL with PDO: a utf8mb4 DSN, prepared statements, error modes, fetch modes, transactions and pooling notes, with runnable SQLite stand-ins.
  2. 66 MySQL from Python and Node.js Connect Python and Node.js to MySQL with mysql-connector, PyMySQL and mysql2: parameterized queries, connection pools, async drivers and an ORM overview.

Projects

  1. 67 Project: Blog Database Build a blog database in MySQL: users, posts, many-to-many tags, threaded comments, full-text search, indexes for the front page and reporting queries.
  2. 68 Project: E-commerce Orders and Inventory Design MySQL tables for products, carts, orders and stock, check out inside a transaction, avoid overselling with an atomic decrement, and write sales reports.
  3. 69 Project: Audit Trail and History Tables Record who changed what in MySQL: audit triggers, valid-from and valid-to history tables, point-in-time queries, restoring an old version and retention.
  4. 70 Project: Multi-Tenant SaaS Schema Design a multi-tenant MySQL schema: tenant_id on every table, composite keys and foreign keys, isolation through views, per-tenant reports and a sharding plan.
  5. 71 Project: Analytics and Reporting Queries Write reporting queries in MySQL: month-over-month growth with window functions, rollup subtotals, cohort retention, summary tables and caching of aggregates.

Practice and Review

  1. 72 MySQL Exercises with Solutions Practice MySQL with 25 graded exercises on data types, joins, indexes, JSON, window functions and transactions, each with a worked solution you can run.
  2. 73 Common MySQL Pitfalls and Anti-Patterns Avoid the MySQL mistakes that cost teams the most: implicit conversions, utf8 vs utf8mb4, silent truncation, OFFSET paging, N+1 queries and long transactions.
  3. 74 MySQL Interview Questions Prepare for MySQL interviews with clear answers on InnoDB vs MyISAM, indexing, isolation levels, replication, EXPLAIN, locks and query optimization.
  4. 75 MySQL Cheat Sheet A one-page MySQL cheat sheet: connecting, DDL, data types, queries, JSON, indexes, transactions, admin commands and how to read EXPLAIN output.