Skip to content
Tutorials Center
My learning

Topics

MariaDB Tutorial

Learn MariaDB step by step: 40 lessons in 9 chapters with examples, quizzes, diagrams and projects.

Start learning →

of lessons completed

Getting Started

  1. 1 Introduction to MariaDB Learn what MariaDB is, how it relates to MySQL, how a client talks to the server, and which features make it a popular open source relational database.
  2. 2 Installing MariaDB on Windows, Linux, macOS and Docker Install MariaDB on Windows, Debian/Ubuntu, macOS or Docker, secure the first run, set the root password, choose a port and character set, and test the connection.
  3. 3 The mariadb Client and Other Tools Use the mariadb command-line client, run scripts, read SHOW and DESCRIBE output, change the prompt and output format, and pick a graphical tool such as HeidiSQL or DBeaver.
  4. 4 Create, Select, Alter and Drop a Database Create and manage MariaDB databases: CREATE DATABASE with character sets and collations, IF NOT EXISTS, USE, ALTER DATABASE, DROP DATABASE and what happens on disk.
  5. 5 Working with Tables in MariaDB Create, inspect, change, copy, empty and drop MariaDB tables with CREATE TABLE, SHOW TABLES, ALTER TABLE, TRUNCATE and DROP, with runnable examples and the traps to avoid.

SQL Basics

  1. 6 INSERT, UPDATE and DELETE in MariaDB Add, change and remove rows in MariaDB with INSERT, UPDATE and DELETE, bulk inserts, INSERT ... SELECT, REPLACE, ON DUPLICATE KEY UPDATE, RETURNING and removing duplicate rows.
  2. 7 SELECT, WHERE, ORDER BY and DISTINCT in MariaDB Query MariaDB tables with SELECT, filter rows with WHERE and comparison operators, IN, BETWEEN and IS NULL, sort with ORDER BY, remove duplicates with DISTINCT and page results with LIMIT.
  3. 8 Pattern Matching with LIKE and REGEXP in MariaDB Match text in MariaDB with LIKE wildcards, ESCAPE, NOT LIKE and case rules, then use REGEXP and RLIKE (PCRE) for advanced patterns, REGEXP_REPLACE and REGEXP_SUBSTR, with runnable stand-ins.
  4. 9 NULL, Variables and Quoting in MariaDB Handle NULL correctly in MariaDB (IS NULL, COALESCE, IFNULL, NULL-safe equals), set columns to NULL, use user-defined and local variables, and quote strings and identifiers the right way.
  5. 10 GROUP BY, HAVING and Aggregate Functions in MariaDB Summarize data in MariaDB with COUNT, SUM, AVG, MIN, MAX, GROUP_CONCAT, GROUP BY, HAVING and WITH ROLLUP, and learn the difference between WHERE and HAVING and ONLY_FULL_GROUP_BY.
  6. 11 UNION, INTERSECT and EXCEPT in MariaDB Combine query results in MariaDB with UNION, UNION ALL, INTERSECT and EXCEPT, learn the column rules, ordering and limits of combined queries, and see the older emulations.

Data Types and Storage Engines

  1. 12 MariaDB Data Types Choose the right MariaDB data type: integer sizes and UNSIGNED, DECIMAL versus FLOAT, CHAR and VARCHAR, TEXT and BLOB, DATE, DATETIME, TIMESTAMP, ENUM, SET, BOOLEAN, JSON, UUID and INET6.
  2. 13 Primary Keys, Foreign Keys and Constraints in MariaDB Keep MariaDB data correct with PRIMARY KEY, AUTO_INCREMENT, FOREIGN KEY with ON DELETE rules, UNIQUE, NOT NULL, DEFAULT and CHECK constraints, and learn how to add and drop them safely.
  3. 14 MariaDB Storage Engines โ€“ InnoDB, Aria, MyISAM and More Understand MariaDB's pluggable storage engines: InnoDB, Aria, MyISAM, MEMORY, CONNECT, ColumnStore, MyRocks and Spider, how to choose one, check it, change it and why InnoDB is the default.
  4. 15 Character Sets and Collations in MariaDB Learn how MariaDB stores and compares text: character sets such as utf8mb4, collations like utf8mb4_unicode_ci and utf8mb4_bin, the four levels they are set at, converting tables and fixing garbled text.
  5. 16 Generated Columns, Sequences and System-Versioned Tables Use MariaDB features beyond plain tables: virtual and persistent generated columns, CREATE SEQUENCE, system-versioned tables with FOR SYSTEM_TIME, invisible columns and JSON indexing, with runnable stand-ins.

Joins and Subqueries

  1. 17 Joins in MariaDB โ€“ INNER, LEFT, RIGHT, CROSS and Self Joins Combine tables in MariaDB with INNER, LEFT, RIGHT, CROSS and self joins, ON versus USING, joins over several tables, anti-joins, a FULL OUTER JOIN emulation and multi-table UPDATE and DELETE.
  2. 18 Subqueries in MariaDB Nest queries in MariaDB: scalar, row and table subqueries, IN, ANY, ALL, EXISTS, correlated subqueries, derived tables in FROM, CTEs, and when a join is the better choice.
  3. 19 Views in MariaDB Save queries as views in MariaDB with CREATE VIEW and CREATE OR REPLACE, updatable views, WITH CHECK OPTION, MERGE and TEMPTABLE algorithms, SQL SECURITY, SHOW CREATE VIEW and the limits of views.
  4. 20 Recursive CTEs and Hierarchical Queries in MariaDB Walk trees and hierarchies in MariaDB with WITH and WITH RECURSIVE: org charts, category paths, bill of materials, number and date series, cycle protection and the recursion limit.

Functions and Window Functions

  1. 21 String Functions in MariaDB Learn MariaDB string functions: CONCAT, SUBSTRING, LEFT, LPAD, REPLACE, LOCATE, TRIM and more, plus how to update or replace part of a string stored in a column.
  2. 22 Numeric and Date Functions in MariaDB Master MariaDB numeric and date functions: ROUND, FLOOR, MOD, NOW, DATE_ADD, DATEDIFF, TIMESTAMPDIFF and DATE_FORMAT, with portable runnable examples.
  3. 23 Aggregate Functions, GROUP BY and HAVING in MariaDB Summarize data in MariaDB with COUNT, SUM, AVG, MIN, MAX and GROUP_CONCAT, group rows with GROUP BY and HAVING, add subtotals with WITH ROLLUP and remove duplicates.
  4. 24 Window Functions in MariaDB Learn MariaDB window functions (10.2+): OVER, PARTITION BY, ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, running totals and frames, with runnable examples.
  5. 25 CTEs and Recursive Queries in MariaDB Use MariaDB common table expressions (WITH) to structure queries, and write recursive CTEs for org charts, category trees, number series and loop-safe hierarchy walks.

Stored Programs and Triggers

  1. 26 Views and Updatable Views in MariaDB Create, replace and drop MariaDB views, learn which views are updatable, use WITH CHECK OPTION, ALGORITHM and SQL SECURITY, and see when a view helps or hurts.
  2. 27 Variables and Stored Functions in MariaDB Declare and use MariaDB user variables, local variables and system variables, then write stored functions with DECLARE, IF, CASE and RETURN, including DETERMINISTIC and DELIMITER.
  3. 28 Stored Procedures in MariaDB Write MariaDB stored procedures with IN, OUT and INOUT parameters, IF, CASE and loops, error handlers, SIGNAL and transactions, then call, inspect and drop them safely.
  4. 29 Triggers in MariaDB Create MariaDB triggers for auditing, validation and derived data: BEFORE and AFTER INSERT, UPDATE, DELETE, the NEW and OLD rows, SIGNAL errors, trigger order and pitfalls.
  5. 30 Cursors and Scheduled Events in MariaDB Loop over query results with MariaDB cursors (DECLARE, OPEN, FETCH, CLOSE) and run SQL on a schedule with CREATE EVENT and the event scheduler, with safe patterns and alternatives.

Indexes and Performance

  1. 31 Creating and Managing Indexes in MariaDB Create, show, drop and manage MariaDB indexes: primary, unique, composite, prefix and FULLTEXT indexes, IGNORED indexes and online DDL, with runnable index examples.
  2. 32 EXPLAIN and Index Design in MariaDB Read MariaDB EXPLAIN and ANALYZE output, understand type, key, rows and Extra, and design composite and covering indexes using the leftmost-prefix and sargable-query rules.
  3. 33 Query Tuning and the Slow Query Log in MariaDB Find slow MariaDB queries with the slow query log and mariadb-dumpslow, then fix them: keyset pagination, FORCE INDEX, statistics and histograms, batching, buffer pool and timeouts.

Replication, Backup and Security

  1. 34 MariaDB Replication and Galera Cluster Set up MariaDB primary-replica replication with the binary log and GTIDs, check lag with SHOW REPLICA STATUS, understand semi-sync and Galera multi-primary clusters and failover.
  2. 35 Backup and Restore in MariaDB Back up and restore MariaDB with mariadb-dump, mariadb-backup and the binary log for point-in-time recovery, plus consistency options, scheduling, encryption and restore testing.
  3. 36 Users, Grants and Roles in MariaDB Create MariaDB users, grant and revoke privileges at global, database, table and column level, use roles, resource limits and password policies, and follow least privilege.
  4. 37 Securing MariaDB Harden a MariaDB server: network exposure, TLS, encryption at rest, the audit plugin, data masking with views, prepared statements against SQL injection and a deployment checklist.

Projects and Practice

  1. 38 Project: Library Management System in MariaDB Build a library database step by step: schema and constraints, lending and returning books safely, overdue and fine reports, views, indexes and the MariaDB procedure and event behind each step.
  2. 39 Project: Sales Reporting Database in MariaDB Turn an order database into business reports with MariaDB: monthly revenue, growth, running totals, top products per category, customer segments, summary tables and a reporting account.
  3. 40 Practice: MariaDB Exercises and Interview Questions Test your MariaDB skills with graded exercises on aggregates, joins, window functions, CTEs, indexes and stored programs, each with a runnable solution, plus common interview questions.