Catalog / MariaDB Cheat Sheet
MariaDB Cheat Sheet
A comprehensive cheat sheet for MariaDB, covering essential commands, data types, SQL syntax, and administrative tasks.
Basic SQL Commands
Data Definition Language (DDL)
|
Creates a new database. |
|
Deletes an existing database. |
|
Creates a new table. |
|
Adds a new column to a table. |
|
Deletes a column from a table. |
|
Deletes an existing table. |
Data Manipulation Language (DML)
|
Inserts new data into a table. |
|
Updates existing data in a table. |
|
Deletes data from a table. |
|
Retrieves data from a table. |
|
Replaces data in a table if a row with the same primary key or unique key exists. |
|
Removes all rows from a table. It’s faster than DELETE because it deallocates the table’s data pages. |
Data Control Language (DCL)
|
Grants privileges to a user. |
|
Revokes privileges from a user. |
|
Sets a new password for a user. |
|
Reloads the grant tables. |
Advanced SQL Queries
Joins
|
|
|
|
|
Subqueries
A subquery is a query nested inside another query.
|
|
|
|
Subqueries in the
|
Aggregate Functions
|
Counts the number of rows. |
|
Calculates the sum of values in a column. |
|
Calculates the average of values in a column. |
|
Finds the minimum value in a column. |
|
Finds the maximum value in a column. |
|
Concatenates values from a column into a single string. |
MariaDB Specific Features
Storage Engines
MariaDB supports multiple storage engines, each with its own characteristics.
|
To specify a storage engine when creating a table:
|
To change the storage engine of an existing table:
|
Dynamic Columns
Dynamic columns allow storing multiple values in a single column, similar to JSON or key-value stores.
|
Example:
|
JSON Functions
MariaDB provides functions for working with JSON data.
|
Example:
|
Administrative Tasks
User Management
|
Creates a new user account. |
|
Renames an existing user account. |
|
Deletes a user account. |
|
Displays the privileges granted to a user. |
|
Sets a new password for a user. |
|
Changes the password for a user. |
Backup and Restore
Backup:
Restore:
|
Using Backup:
Prepare:
Restore:
|
Consider using logical (mysqldump) for smaller databases and physical (mariabackup) for larger databases |
Server Management
|
Displays the current status of the MariaDB server. |
|
Displays the global variables of the MariaDB server. |
|
Displays a list of currently running threads. |
|
Kills a specific thread. |
|
Closes all open tables, forcing them to be written to disk. |
|
Removes all query results from the query cache. |