Cheat Sheet for MariaDB SQL Database Commands

This is a quick reference cheat sheet for the most commonly used MariaDB SQL database commands.

While you're here, learn more about Zuar's data and analytics services. From raw data through to dashboard creation, we've got you covered!

About MariaDB

MariaDB is a robust protocol that allows more efficient storage engines to run SQL and NoSQL within a database system. It’s particularly effective at handling big data from large enterprises.

MariaDB is a popular alternative to MySQL, and is actually a fork of MySQL. And because it is a fork, you can easily transition a MySQL database to the more robust MariaDB without changing your application. If you’re already using a MySQL platform, you may want to consider switching for improved performance. For a better understanding of the differences between MariaDB and MySQL, check out this article.

MariaDB provides some improvements over MySQL:

• It offers better security using password checks, PAM and LDAP authentication, user roles, and encryption.
• It has more efficient and faster performance, reducing your bounce rate.
• It offers better user support and notification services to keep you updated on patches and bug fixes.

Defining Your MariaDB Database Commands

These are the most common commands for initiating and creating a MariaDB database. These are essential commands you need when setting up your database and tables.

Connecting to the MariaDB

Mysql –u root –p –h localhost

Creating a Database

CREATE DATABASE db_name;

[create specifications]

If you want to replace an existing database:

CREATE OR REPLACE DATABASE db_name;

Dropping a Database

You’ll need the DROP privilege enabled to execute this command:

DROP DATABASE db_name;

To prevent an error and give a warning for all nonexistent databases:

DROP DATABASE IF EXISTS db_name;

Selecting a Default Database

USE db_name;

Creating a Table

CREATE TABLE tbl_name;

If you want to replace an existing table:

CREATE OR REPLACE TABLE tbl_name;

Altering a Table

ALTER TABLE tbl_name;

[add alter specifications and options]

Dropping a Table

You’ll need the DROP privilege enabled to execute this command:

DROP TABLE tbl_name;

Describing the Structure of a Table

DESCRIBE tbl_name;

Adding & Editing Data in MariaDB

Once your tables and database are defined, you need to enter your data. You can set many conditions in the SELECT command, enabling you to customize your queries. You’ll want to check the manual for all conditions. Here are the most common commands for manipulating data in the database:

Selecting Data

To get data from one or more tables, you can use the select command.

SELECT select_expression FROM tbl_name

If you want to add conditions, you can use:

SELECT select_expression FROM tbl_name WHERE where_conditon;

Check out the specifics on SELECT Expressions at this link.

Inserting New Data Into a New Row

INSERT INTO tbl_name (col_name, col_name) VALUES (‘data’, ‘more data’);

See the MariaDB manual for additional insert specifications.

Updating Existing Data

UPDATE tbl_name SET col_specifications WHERE where clause;

See the MariaDB manual for additional update specifications.

Deleting Existing Data

DELETE FROM tbl_name WHERE where_condition;

See the MariaDB manual for more complicated delete specifications and conditions.

Replacing Data

REPLACE INTO tbl_name VALUES (expression, ‘expression’) RETURNING col_name as new_name

Truncating All Data From a Template

TRUNCATE TABLE tbl_name

Handling MariaDB Transactions

Handling transactions is another typical command for MariaDB. There are many specifics about the conditions for the transaction. You’ll want to check the manual for all the possibilities. Here is the main command syntax:

Starting a Transaction

START TRANSACTION;

[State conditions for transaction]

Applying Changes and Ending a Transaction

Use this at the end of a transaction command:

COMMIT;

Example:

START TRANSACTION;

SELECT condition FROM tbl_name WHERE where_clause;

UPDATE tbl_name SET condition WHERE where_clause;

COMMIT;

Discarding Changes and Ending a Transaction

ROLLBACK;

Example:

SELECT condition FROM tbl_name WHERE where_clause;

UPDATE tbl_name SET condition WHERE where_clause;

ROLLBACK;

Common MariaDB Queries

These are common queries used in a MariaDB that can help improve your query commands (for tutorials and in-depth syntax, you can access the MariaDB knowledge base):

Using Auto Increment to Create Unique Identity for New Rows

CREATE TABLE tbl_name (id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (id));

Querying from Multiple Tables on a Common Value

SELECT * FROM tbl_name_1 INNER JOIN tbl_name_2 ON value = value;

Finding Data Row With a Minimum in a Column

SELECT col_name, col_name2 FROM tbl_name WHERE col_name3=(SELECT MIN(col_name3) FROM tbl_name2);

Finding Data Row With a Maximum in a Column

SELECT col_name, col_name2 FROM tbl_name WHERE col_name3=(SELECT MAX(col_name3) FROM tbl_name2);

Ordering Results

SELECT col_name FROM tbl_name ORDER BY col_name2 DESC;

Grouping Data Results

SELECT col_name FROM tbl_name GROUP BY col_name2;

Finding an Average Value

SELECT AVG(col_name) FROM tbl_name;

Beyond the Cheat Sheet

If you need a more in-depth tutorial, you should check out the MariaDB Primer.

If you need industry-leading data solutions:

• For an ETL solution that works seamlessly with MariaDB, check out Runner!
• For expert-led data services, from strategy to implementation, see what Zuar can offer.