MariaDB Queries – Detailed Guide

In this MariaDB tutorial, you will learn about MariaDB Queries. Additionally, we will cover the following topics.

• MariaDB Queries
• MariaDB Queries Per Second
• MariaDB Queries Log
• MariaDB Queries Slow
• MariaDB Multiple Queries
• MariaDB Stacked Queries
• MariaDB SQL Queries
• MariaDB max_queries_per_hour
• MariaDB Log and Show All Queries
• MariaDB Show Active Queries
• MariaDB Kill All Queries
• MariaDB Basic Queries
• MariaDB Queries Database
• MariaDB Queries Date
• MariaDB Query Execution Time
• MariaDB Queries Kill
• MariaDB Query Table Names
• MariaDB Query Optimization

MariaDB Queries

Here, you will learn about MariaDB Queries and some useful MariaDB Queries.

In RDBMS (Relational Database Management System), a query is a command used to retrieve data from a table. In MariaDB, most of the queries are made using the SELECT statement.

Here are several useful queries listed below.

1. Creating a Table
2. Inserting Records
3. Using AUTO_INCREMENT
4. Querying from two tables on a common value
5. Finding the Maximum Value
6. Finding the Minimum Value
7. Finding the Average Value
8. Finding the Maximum Value and Grouping the Results
9. Ordering Results
10. Finding the Row with the Minimum of a Particular Column
11. Finding Rows with the Maximum Value of a Column by Group

Let’s see an example of all the above queries on a table.

Create a new table using the below query.

CREATE TABLE stock_price(st_rank INT,st_name VARCHAR(40),st_price FLOAT,st_country VARCHAR(40));

Insert the following records in a table 'stock_price'.

INSERT INTO stock_price(st_rank,st_name,st_price,st_country)VALUES
(1,'Apple',150.62,'United State'),
(2,'Microsoft',276.44,'United States'),
(3,'Alphabet',2534.82,'United States'),
(4,'Amazon',2837.06,'United States'),
(5,'Tesla',766.37,'United States'),
(6,'Berkshire Hathaway',493785,'United States'),
(7,'Meta (Facebook)',186.63,'United States'),
(8,'NVIDIA',213.3,'United States'),
(9,'UnitedHealth', 487.92,'United States'),
(10,'Johnson & Johnson',171.69,'United States');

View the table using the below query.

SELECT * FROM stock_price;

Let’s again create a new table using the below steps and insert the data to show the auto_increment.

Create a table name Student_Marks using the below query.

CREATE TABLE student_marks (
 id INT NOT NULL AUTO_INCREMENT, st_name VARCHAR(50), 
 st_subject VARCHAR(30),st_score float, PRIMARY KEY (id)
);

Insert the following records in that table.

INSERT INTO student_marks(st_name,st_subject,st_score)VALUES
('Thomas A. Cambell','Data Science',80),
('Joshua R. Anderson','Machine Learning',75),
('Mckinley L. Hatley','Deep Learning',90),
('Gordon T. Cook','NLP',50),
('Thomas A. Cambell','Machine Learning',80),
('Joshua R. Anderson','Data Science',75),
('Mckinley L. Hatley','NLP',90),
('Gordon T. Cook','Deep Learning',50);

View the table using the below query.

SELECT * FROM student_marks;

Find the maximum stock price from the column st_price of the table using a function Max(column_name) that returns the maximum value of a column.

SELECT MAX(st_price) FROM stock_price;

Find the minimum stock price from the column st_price of the table using a function Min(column_name) that returns the minimum value of a column.

SELECT MIN(st_price) FROM stock_price;

Find the average stock price from the column st_price of the table using a function Avg(column_name) that returns the average value of all the values in a column.

Find the maximum stock price and group them by company name, To group the result in MariaDB the clause GROUP BY is used.

SELECT st_name, MAX(st_price) FROM stock_price GROUP BY st_name;

View the result by ordering in a specific way like ascending or descending using the ORDER BY clause.

SELECT st_name, st_price FROM stock_price ORDER BY st_price DESC;

Find the row with the minimum of a particular column means to find the lowest stock price for any company in a table using the below query.

SELECT st_name, st_price FROM stock_price WHERE st_price = (SELECT MIN(st_price) FROM stock_price);

Find the row with the maximum value of a particular column by group means to find the best stock price of the companies in a table using the below query.

SELECT st_name,st_subject,st_score FROM student_marks student1
WHERE st_score = (SELECT MAX(st_score) FROM student_marks student2 WHERE student1.st_name = student2.st_name);

Read: MariaDB JSON Function + Examples

MariaDB Queries Per Second

The running number of queries per second differs from version to version or machine to machine. The MariaDB 10.1 version can run 1142464 number queries per second and the MariaDB version before the MariaDB 10.1 like MariaDB 10.0.21 can run 396285.

The number of queries running per second also increases or decreases based upon the OLTP clients. The OLTP stands for Online Transaction Processing.

MariaDB Query Log

In this, we will learn about the MariaDB Query Log that keeps a record of each query the user executes.

The General query log is a collection of every MariaDB Query received from the client, as well as each client connect and disconnect. It grows quickly as it is a record of every query received by the server.

The general query log is disabled by default. To enable the general query log, set the general_log to 1 using SET GLOBAL.

SET GLOBAL general_log=1;

This is how you can enable the general query log but this can only be disabled for a connection by a user with super privilege by changing the SQL_LOG_OFF variable to 1.

The above query is an example of one of many log queries in MariaDB, if you want to know about all the most common log queries in MariaDB then visit our tutorial “MariaDB Logs”

Read: MariaDB vs Postgres – Detailed Comparison

MariaDB Queries Slow

In MariaDB, Slow Queries are SQL Statements that usually take longer than the default long_query_time(10 seconds) and require at least min_examined_row_limit rows to be examined.

• The slow queries are identified by enabling MariaDB slow query log which is disabled by default.

Slow queries slow down the process of execution, so it is needed to be resolved by enabling slow query log and SHOW EXPLAIN which helps us to identify the slow queries and where the query is having problems or lacking making it a long-running query.

To know more about the slow query, then visit our tutorial “MariaDB Logs”.

It can be fixed using several Query Optimization Techniques.

• Use SHOW EXPLAIN
• By Normalizing your tables
• Use the right data types
• Indexing all the columns
• By replacing complex subqueries
• Use INNER JOINS instead of OUTER JOIN
• Don’t include the unecessary columns in the SELECT statement

MariaDB Multiple Queries

The Multiple Queries can be combined in MariaDB using the UNION operator into a single result. To know more about how UNION operator works, then visit our another tutorial “MariaDB Union Operator”.

Let’s take an example using the below code.

(SELECT * FROM student_marks WHERE st_score < 70)
UNION
(SELECT * FROM student_marks WHERE st_score > 90)

In the above code, we are selecting the student whose marks is less than 70 and greater than 90 by combing the two separate queries using the UNION operator.

The output shows the combined result from the two separate queries. The student Gordon T. Cook has marks less than 70 and Thomas Shelby has marks greater than 90.

MariaDB Stacked Queries

In MariaDB, Stacked Queries are the queries that contain more than one statement and get executed within one transaction. When one by one many operations are done on a database within a single logical unit of work, then it is called a transaction.

Stacked Queries means running more than one query on a database one by one in the same transaction. Here as soon as one SQL statement end, after that, another SQL statement starts.

Let’s understand through an example using the same table Student_Marks that we have created in the above sub-section “MariaDB Queries”.

Here we are going to change one of the student marks and add a new student in that table using the below code.

UPDATE student_marks
SET st_score = 70
WHERE id =8;

INSERT INTO student_marks(st_name,st_subject,st_score)VALUES
('Thomas Shelby','NLP',98);

SELECT * FROM student_marks;

Here in the above code, we are executing the three SQL statements in one transaction one by one. The first statement updates the student marks, the second one inserts the new student record and the third shows the table records.

From the output, we can see that the updated student marks to 70 whose id is 8 and added a new student Thomas Shelby whose id is 9.

Read: MariaDB If Null + Examples

MariaDB SQL Queries

Here, we will learn about various MariaDB SQL Queries with their syntax and an example.

There are various categories of MariaDB SQL statements, they are listed below.

• Account Management SQL Commands
• DDL(Data Definition Language) Commands
• DML(Data Manipulation Language) Commands
• Prepared Statements
• Programmatic and Compound Statements
• Stored Routine Statements
• Table Statements
• Transactions
• Help Command
• Comment Syntax
• Built-in functions

1. Account Management SQL Commands

SQL provides various commands like create and drop for creation and deletion of a user, grant and revoke for granting different privileges, alter user, etc for account management in MariaDB.

The different Account Management commands are as follows.

Create User statement in MariaDB

Creating a User statement in MariaDB is used for creating a new user.

The syntax for creating a new user is given below.

CREATE USER <user_name>@localhost IDENTIFIED BY 'new_passwrod'

Here, the user_name is the name of the user and 'new_password' is the password corresponding to the user.

Here is an example to create a new user.

CREATE USER 'Johnny'@localhost IDENTIFIED BY 'johnny123';

To verify the creation of the user, we can type the following command.

SELECT User FROM mysql.user;

This verifies the creation of a new user and this is how we can create a new user.

2. Alter MariaDB User

Alter User statement in MariaDB is used in changing the password of the user.

The syntax for changing the password corresponding to a user is given below.

SET old_passwords=0;
ALTER USER user_name@localhost IDENTIFIED BY 'new_password';

Here, the 'SET old_password=0' is utilized to reset the old password to zero and then assign the user with a new password.

Here is an example of Alter a user using the SET statement.

SET old_passwords=0;
ALTER USER 'Johnny'@localhost IDENTIFIED BY 'johnny321';

This is how we can change the password using Alter Statement.

3. Drop MariaDB User

Drop User statement in MariaDB is utilized to remove an already existing MariaDB user.

The syntax for removing a MariaDB user using the DROP USER statement is given below.

DROP USER user_name;

Here is an example to remove a MariaDB user using the DROP USER statement.

DROP USER 'Johnny'@localhost;

This is how we can remove a MariaDB User.

4. Grant Privileges

Grant statement in MariaDB is employed to grant privileges to the user using only one or a combination of SELECT, INSERT, CREATE, ALTER, DROP, UPDATE, DELETE, INDEX, GRANT OPTION, or ALL.

The syntax for Grant Statement is given below.

GRANT privileges ON object TO user;

Here, the Object can be either a database or a table and the user is the name of the user who will be granted privileges.

Here are some examples to grant privileges.

Example 1 – Grant SELECT access only.

GRANT SELECT ON USA_data TO 'Josh'@localhost;

Example 2 – Grant SELECT, INSERT, UPDATE, DELETE privileges on a database

Grant SELECT, INSERT, UPDATE, DELETE ON USA_data.* TO 'Josh'@localhost;

Example 3 – Grant ALL privileges to a database.

GRANT ALL ON USA_data.* TO 'Josh'@'localhost';

These are the ways by which we can grant privileges to a user.

5. Revoke Privileges

Once we grant privileges on a database to a user, then we may need to revoke/ remove some or all the privileges from a user. We can revoke any one or combination of SELECT, INSERT, CREATE, ALTER, DROP, UPDATE, DELETE, INDEX, GRANT OPTION, or ALL.

The syntax for Revoke Statement is given below.

REVOKE privileges ON object TO user;

Here, the Object can be either a database or a table and the user is the name of the user who has been granted privileges.

Here are some examples to grant privileges.

Example 1 – Revoke SELECT access only.

Revoke SELECT ON USA_data.* FROM 'Josh'@localhost;

Example 2 – Revoke INSERT, UPDATE, DELETE privileges on a database

Revoke INSERT, UPDATE, DELETE ON USA_data.* FROM 'Josh'@localhost;

Example 3 – Revoke ALL privileges to a database.

REVOKE ALL ON USA_data.* FROM 'Josh'@'localhost';

These are the ways by which we can revoke privileges from a user.

6. Show Grants

Show Grant’s statement in MariaDB shows us the list of privileges granted to a particular MariaDB user.

The syntax Show Grants is given below.

SHOW GRANTS FOR [ username ];

Here, the username is the name of a MariaDB user whose privileges we are going to review.

Here is an example to view grants for a particular MariaDB user.

SHOW GRANTS FOR 'Jack'@localhost;

Here, we can see that Jack has been granted privileges of all databases.

7. DDL(Data Definition Language) Commands

Data Definition Language(DDL) are the commands which are used to give database structure or schema. Commands like Create, Alter, Drop, etc are the most used to provide structure to the database.

Some of them are explained below with the help of examples.

8. Create MariaDB Table

Create Table statement in MariaDB is utilized to create tables and define the tables.

The syntax for creating tables in MariaDB is given below.

CREATE TABLE tbl_name
( 
  col1 datatype [ NULL | NOT NULL ],
  col2 datatype [ NULL | NOT NULL ],
  ...
);

Here, tbl_name is the name of the table being created, col1 and col2 are the columns of the table with their corresponding data type.

Here is an example to create a table in MariaDB.

Create Table USA_emp(
 ->emp_id int auto_increment,
 ->emp_name varchar(255) not null,
 ->emp_dept varchar(255) not null,
 ->emp_address varchar(255) not null,
 ->primary key(emp_id));