Last Updated: November 21, 2025
Connection & Basics
mariadb -u user -p
Connect to MariaDB server
mariadb -h host -u user -p database
Connect to remote database
SHOW DATABASES;
List all databases
USE database_name;
Switch to database
SHOW TABLES;
List tables in current database
DESCRIBE table_name;
Show table structure
SHOW CREATE TABLE table_name;
Show CREATE TABLE statement
SELECT VERSION();
Show MariaDB version
STATUS;
Show connection and server status
EXIT;
Exit MariaDB shell
Database Management
CREATE DATABASE db_name;
Create new database
CREATE DATABASE IF NOT EXISTS db_name;
Create database if doesn't exist
DROP DATABASE db_name;
Delete database
CREATE DATABASE db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Create database with charset
ALTER DATABASE db CHARACTER SET utf8mb4;
Change database charset
Table Operations
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY);
Create table with auto-increment ID
DROP TABLE table_name;
Delete table
TRUNCATE TABLE table_name;
Delete all rows, reset auto-increment
ALTER TABLE users ADD COLUMN email VARCHAR(255);
Add column to table
ALTER TABLE users DROP COLUMN email;
Remove column from table
ALTER TABLE users MODIFY COLUMN name VARCHAR(300);
Change column definition
ALTER TABLE users RENAME TO customers;
Rename table
CREATE INDEX idx_email ON users(email);
Create index on column
DROP INDEX idx_email ON users;
Remove index
CRUD Operations
INSERT INTO users (name, email) VALUES ('John', 'john@example.com');
Insert single row
INSERT INTO users VALUES (1, 'John'), (2, 'Jane');
Insert multiple rows
SELECT * FROM users;
Select all rows
SELECT name, email FROM users WHERE id = 1;
Select specific columns with condition
UPDATE users SET email = 'new@example.com' WHERE id = 1;
Update rows
DELETE FROM users WHERE id = 1;
Delete specific rows
SELECT * FROM users ORDER BY name ASC;
Order results ascending
SELECT * FROM users LIMIT 10 OFFSET 20;
Pagination (skip 20, take 10)
Joins & Relations
SELECT * FROM users INNER JOIN orders ON users.id = orders.user_id;
Inner join (matching rows only)
LEFT JOIN orders ON users.id = orders.user_id
Left join (all left table rows)
RIGHT JOIN orders ON users.id = orders.user_id
Right join (all right table rows)
CROSS JOIN
Cartesian product of tables
NATURAL JOIN orders
Auto join on common columns
Aggregate Functions
SELECT COUNT(*) FROM users;
Count total rows
SELECT SUM(amount) FROM orders;
Sum numeric column
SELECT AVG(price) FROM products;
Calculate average
SELECT MAX(price), MIN(price) FROM products;
Get maximum and minimum
SELECT status, COUNT(*) FROM orders GROUP BY status;
Group by with count
HAVING COUNT(*) > 5
Filter grouped results
MariaDB-Specific Features
CREATE SEQUENCE seq START WITH 1 INCREMENT BY 1;
Create sequence (not in MySQL)
SELECT NEXTVAL(seq);
Get next sequence value
CREATE OR REPLACE TABLE users (...);
Drop and recreate table if exists
WITH RECURSIVE cte AS (...) SELECT * FROM cte;
Common Table Expressions (CTEs)
SELECT * FROM users WHERE id IN (VALUES 1, 2, 3);
Table value constructor
SELECT JSON_EXTRACT(data, '$.name') FROM users;
Query JSON data
CREATE TABLE users ENGINE=Aria;
Use Aria storage engine (MariaDB-specific)
ENGINE=ColumnStore
Columnar storage for analytics
User Management
CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
Create new user
GRANT ALL PRIVILEGES ON db.* TO 'user'@'localhost';
Grant all privileges on database
GRANT SELECT, INSERT ON db.table TO 'user'@'localhost';
Grant specific privileges
REVOKE ALL PRIVILEGES ON db.* FROM 'user'@'localhost';
Revoke privileges
DROP USER 'user'@'localhost';
Delete user
SHOW GRANTS FOR 'user'@'localhost';
Show user privileges
FLUSH PRIVILEGES;
Reload grant tables
SET PASSWORD FOR 'user'@'localhost' = PASSWORD('newpass');
Change user password
Transactions
START TRANSACTION;
Begin transaction
COMMIT;
Commit transaction
ROLLBACK;
Rollback transaction
SAVEPOINT savepoint_name;
Create savepoint
ROLLBACK TO SAVEPOINT savepoint_name;
Rollback to savepoint
SET autocommit = 0;
Disable auto-commit
Backup & Restore
mariadb-dump -u user -p database > backup.sql
Export database to SQL file
mariadb-dump --all-databases > all.sql
Backup all databases
mariadb -u user -p database < backup.sql
Import SQL file
mariadb-dump --no-data database > schema.sql
Export schema only (no data)
mariadb-dump --single-transaction database
Consistent backup without locking
Performance & Optimization
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
Analyze query execution plan
SHOW PROCESSLIST;
Show running queries
KILL QUERY process_id;
Stop running query
OPTIMIZE TABLE table_name;
Optimize table storage
ANALYZE TABLE table_name;
Update table statistics
SHOW TABLE STATUS;
Show table information and stats
SHOW ENGINE InnoDB STATUS;
Detailed InnoDB engine status
💡 Pro Tip:
MariaDB offers better performance than MySQL with features like sequences, parallel replication, and the Aria storage engine. Use ColumnStore for analytics workloads!