Cheat Sheet for MariaDB SQL Database Commands
This is a quick reference MariaDB cheat sheet for the most commonly used 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.
