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));

Here, we can see that the table has been created with the id of the employees, name of the employees, their department, their address who are living in the USA (United States Of America).

This is how we can create a MariaDB table.

9. Alter MariaDB Table

The Alter Table statement in MariaDB helps to modify, add, drop/delete columns in a table. It can also be used to change the name of the table.

The various syntaxes for altering a MariaDB table are given below.

Syntax 1 – Adding a column in a MariaDB table.

ALTER TABLE tbl_name
  MODIFY col_name col_definition
    [ FIRST | AFTER column_name ];

Here,

• tbl_name – It is the name of the table being modified.
• col_name – It is the name of the column which is going to be added in a table.
• col_definition – It is the modified data type for the column being added.
• FIRST | AFTER column_name – It is utilized to place the added column in the table.

Syntax 2 – Modifying multiple columns in a MariaDB table.

ALTER TABLE tbl_name
  MODIFY col_name col_definition
    [ FIRST | AFTER col_name ],
  MODIFY col_name col_definition
    [ FIRST | AFTER col_name ],
....
;

Here:

• tbl_name – It is the name of the table being added.
• col_name – It is the name of the column which is going to be added in a table.
• col_definition – It is the data type for the column being added.
• FIRST | AFTER col_name – It is utilized to place the added column in the table.

Syntax 3 – Drop a column in a MariaDB table.

ALTER TABLE tbl_name
  DROP COLUMN col_name;

Here,

• tbl_name – It is the name of the table whose column needs to be dropped.
• col_name – It is the name of the column which is going to be dropped in a table.

Syntax 4 – Renaming a column in a MariaDB table.

ALTER TABLE tbl_name
  CHANGE COLUMN old_name new_name 
    col_definition;

Here,

• tbl_name – It is the name of the table being modified.
• col_name – Column that we want to modify.
• old_name – Current or the old column name.
• new_name – New Column name.
• col_definition – It is the modified data type for the column being modified.

Syntax 5 – Renaming a table name in MariaDB.

ALTER TABLE tbl_name
  RENAME TO new_tbl_name;

Here,

• tbl_name – It is the name of the table which needs to be changed.
• new_tbl_name – It is the name of the table after the modification.

The examples correspond to each type of modification.

Example 1 – Adding a column in a MariaDB table.

ALTER TABLE USA_emp
   ADD emp_age int
     AFTER emp_name;

Here, a new column i.e. emp_age of data type int is being added in the table i.e. USA_emp.

Example 2 – Adding multiple columns in a MariaDB table.

ALTER TABLE USA_emp
   ADD emp_experience int
     AFTER emp_name, 
   ADD emp_nationality varchar(20)
     AFTER emp_address;

Here, two new columns i.e. emp_experience and emp_nationality of data type int and varchar(20) respectively are being added in the table i.e. USA_emp.

Example 3 – Drop a column in a MariaDB table.

ALTER TABLE USA_emp
  DROP COLUMN emp_experience;

Here, a column i.e. emp_experience is being removed from the table i.e. USA_emp.

Example 4 – Renaming a column in a MariaDB table.

ALTER TABLE USA_emp
  CHANGE COLUMN emp_name ename 
    varchar(100);

Here, a column name i.e. emp_name is being changed to ename.

Example 5 – Renaming a table name in MariaDB.

ALTER TABLE USA_emp
  RENAME TO USA_employee;

Here, the name of the table is being changed from USA_emp to USA_employee.

10. Truncate Table

Truncate table statement in MariaDB helps to remove all the records from the table.

The Syntax for truncating a MariaDB table is given below.

TRUNCATE TABLE table_name;

Here is an example to truncate a table in MariaDB.

TRUNCATE TABLE USA_employee;

11. DML(Data Manipulation Language) Commands

Data Manipulation Statements in MariaDB are used for querying and manipulating data within schema objects. Some DML commands are INSERT, UPDATE, DELETE, etc.

Some of them have been explained below with various examples.

Insert Into Table

Insert into table statement in MariaDB is operated to store a single record or multiple records in a table.

The syntax for inserting a single record into the table is given below.

INSERT INTO table_name
(col1, col2, ... )
VALUES
(exp1, exp2, ... );

Here,

• table_name – It is the name of the table in which the record is to be stored.
• col1, col2 – These are the name of the fields in which the records will be stored respectively.
• exp1, exp2 – These are the values that will be stored inside the table.

And the syntax for inserting multiple records into the table is given below.

INSERT INTO table_name
(col1, col2, ... )
VALUES
(exp1, exp2, ... );

Here,

• table_name – It is the name of the table in which the record is to be stored.
• col1, col2 – These are the name of the fields in which the records will be stored respectively.
• exp1, exp2 – These are the values that will be stored inside the table.

Here are the examples for the above syntaxes.

Example for inserting a single record into the table.

INSERT INTO USA_employee
(emp_id, ename, emp_age, emp_dept, emp_address, emp_nationality)
VALUES
(1, 'Joseph', 28, 'Frontend', 'California, USA', 'American');

Example for inserting multiple records into the table.

This is how you can use insert into statements to store records.

Update Statement

The UPDATE the statement is employed to update the table data in MariaDB, The syntax is given below.

UPDATE table_name
SET column_name = column_value
WHERE condtion;

Here,

• UPDATE – It is the command to update the table data.
• table_name – It is the name of the table whose data we want to update.
• SET column_name = column_valu e – The SET command helps to set the column value to a new value where column_name is the name of the column and column_value is the value of that column.
• WHERE condition – The data can be updated based on matching conditions specified within the WHERE clause.

Read: MariaDB Drop Table + Examples

MariaDB max_queries_per_hour

In MariaDB, MAX_QUERIES_PER_HOUR is a resource option for every user that can be set while creating a new user. The MAX_QUERIES_PER_HOUR determines the number of queries that a user can send to a server.

So here we are going to create a new user using the below code with the resource option MAX_QUERIES_PER_HOUR.

CREATE USER 'Thomas'@'localhost' WITH MAX_QUERIES_PER_HOUR 100;

Where,

• CREATE USER: It is the command to create a new user in MariaDB.
• ‘Thomas’@’localhost’: The Thomas is the name of the new user and localhost is a place where the server is running, and the user is created on that server.
• WITH MAX_QUERIES_PER_HOUR 100: It means created user can execute 100 queries per hour.

The information about the resource option MAX_QUERIES_PER_HOUR are stored in the table user of database mysql in a columns max_questions.

Log into the database and view the information about the user resource option MAX_QUERIES_PER_HOUR using the below code.

use mysql;
SELECT user,max_questions FROM user;

Here in the above code, we are viewing the user and its resource limit of MAX_QUERIES_PER_HOUR.

Look at the above output, The user Thomas has max_question equal to 100 that we have set in the above code.

This is how we can set the MAX_QUERIES_PER_HOUR for a user.

Read: MariaDB Variables Tutorial

MariaDB Log Or Show All Queries

All the queries get logged into to file called General Query Log in MariaDB.

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.

Before setting the general_log to 1, let us check the general query log by typing the below command.

SELECT * FROM mysql.general_log\G

To get the log of all queries, you need to type the following command.

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.

After setting the value of general_log to 1, we need to select the General Query Log Output Destination.

The General Log Query can be written in 2 ways.

• Writing General Log Query to a file
• Writing General Log Query to a table

Writing General Log Query to a file – By default, the general log query is output to a file. Although, it can be changed dynamically by setting the log_output system variable to FILE using SET GLOBAL.

To change the General Log Output Destination to a FILE, you need to type the following command.

SET GLOBAL log_output='FILE';

Writing General Log Query to a table – By default, the general log query is output to a file. Although, it can be changed dynamically by setting the log_output system variable to TABLE using SET GLOBAL.

To change the General Log Output Destination to a TABLE, you need to type the following command.

SET GLOBAL log_output='TABLE';

After setting the General Log Output Destination to either a FILE or TABLE, you need to type the following command to get all the queries in a log.

SELECT * FROM mysql.general_log\G

This is how we can show and get the log of all MariaDB Queries.

Read: MariaDB Foreign Key + Examples

MariaDB Show Active Queries

In this, we will learn about how to get the list of active queries in the MariaDB database.

Active queries are the queries that are running or currently in use in the MariaDB database.

We can view all active queries in MariaDB from the below two places:

• Information Schema
• ProcessList

ProcessList:

It can also be done using ProcessList, using the below code you can see the active running queries or threads.

SHOW [FULL] PROCESSLIST;

Information Schema:

It can be viewed using information schema as it contains all the running threads.

To get the list of active queries or threads using Information Schema, you need to type the following command.

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST\G

This is how you can show the list of all active queries.

MariaDB Basic Queries

Here, you will learn about the useful queries listed above with their syntax.

Creating a Table – This query is employed to create tables in MariaDB.

The syntax for creating a table is given below.

CREATE TABLE <table_name>(
 column_1 <data_type> <column_constraint>,
 column_2 <data_type> <column_constraints>
);

Here is an example of the creation of the MariaDB table.

Create table USA_student (
id int,
subject varchar(20),
name varchar(20),
marks int);

Here, we can see that the table has been created.

Inserting Records – This query is utilized to add data into the table using the INSERT statement.

The syntax for inserting a record in MariaDB is given below.

INSERT INTO <tablename> (field,field2,...) 
VALUES (value, value2,...);

Here is an example to insert a record into a MariaDB table.

Insert into USA_student(id, name, subject, marks) 
values(1, 'James', 'MariaDB', 78),
(1, 'Jack', 'SQLite', 56),
(3, 'Mary', 'MySQL', 97),
(4, 'Caroline', 'PostgreSQL', 81);

And to view the data in the table, we can type the below command.

Select * from USA_Student;

This is how your table along with the data will be displayed. And you can notice that the id is not unique as there are no such constraints given like Primary Key or Auto Increment.

Using AUTO_INCREMENT – This provides a column with a feature with which its values increase automatically.

The syntax for using AUTO_INCREMENT is given below.

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

Here is an example to create a table with AUTO_INCREMENT.

Create table USA_student1 (
id int NOT NULL AUTO_INCREMENT,
subject varchar(20),
name varchar(20),
marks int);

Finding the Maximum Value – This query is utilized to get the maximum value in a particular column in a table in MariaDB.

The syntax for the query to find the maximum value is given below.

SELECT MAX(Expression) 
FROM tableName;

Here is an example to find the maximum value in a table.

Select max(marks) 
from USA_student;

Here, we can see the maximum marks from the table containing the data of students in the United States Of America.

Finding the Minimum Value – To get the minimum value in a particular column in a table in MariaDB.

The syntax for the query to find the minimum value is given below.

SELECT MIN(Expression) 
FROM tableName;

Here is an example to find the minimum value in a table.

Select min(marks) 
from USA_student;

Here, we can see the minimum marks from the table containing the data of students in the United States Of America.

Finding the Average Value – This query is utilized to get the average value of a particular column in a table in MariaDB.

The syntax for the query to find the average value is given below.

SELECT AVG(a) 
FROM tableName;

Here is an example to find the average value in a table.

Select avg(marks) 
from USA_student;

Here, we can see the average marks from the table containing the data of students in the United States Of America.

Finding the Maximum Value and Grouping the Results – This query is utilized to get the maximum value in a group of rows in MariaDB.

The Syntax for the query to find the maximum value and group the results are given below.

SELECT COLUMN1, MAX(EXPRESSION)
FROM tableName GROUP BY COLUMN2;

Here is an example to find the maximum value and group the results.

Select name, max(marks)
from USA_student group by name;

Ordering Results – This query is utilized to get the results in a particular order and it is returned using the SELECT statement in MariaDB.

The Syntax for the query to order the results is given below.

SELECT COLUMN2, COLUMN3, COLUMN1 FROM tableName ORDER BY COLUMN1;

Here is an example to order the results using the SELECT statement.

Select id, name, subject, marks
from USA_student order by marks;

Here, we can see the output in which the table has been ordered according to marks increasingly.

Finding the Row with the Minimum of a Particular Column – This query helps to find the row with the minimum value for a particular column.

The syntax for the query to find the row with the minimum value for a particular column is given below.

SELECT COLUMN2,COLUMN3, COLUMN1 
FROM <tableName> 
WHERE COLUMN1=(
    SELECT MIN(Expression) 
    FROM <tableName>);

Here is an example to find a row with the minimum of a particular column.

SELECT id, name, subject, marks 
FROM USA_student
WHERE marks=(
    SELECT MIN(marks) 
    FROM USA_student);

Here, we can see the row with the minimum of a particular column.

Finding Rows with the Maximum Value of a Column by Group – This query helps to find the row with the maximum value for a particular column.

The syntax for the query to find the row with maximum value for a particular column is given below.

SELECT COLUMN2,COLUMN3, COLUMN1 
FROM <tableName> 
WHERE COLUMN1=(
    SELECT MAX(Expression) 
    FROM <tableName>);

Here is an example to find a row with the maximum of a particular column.

SELECT id, name, subject, marks 
FROM USA_student
WHERE marks=(
    SELECT MAX(marks) 
    FROM USA_student);

Here, we can see the row with the maximum of a particular column.

Read: MariaDB Temporary Table + Examples

MariaDB Query Database

In this, we will learn about how to Select a MariaDB database.

After you log in to the MariaDB server, we need to specify the database you are going to work on. Otherwise, it will give us an Error 1046 after we issue a query without specifying the database.

For this, we need to use the 'USE' statement to instructs MariaDB to use the 'database_name' as its current database.

The syntax for the USE statement is given below.

USE database_name;

Here is an example to specify the database to the MariaDB server.

USE USA_bookstore;

We can see that the database has been changed from USA_data to USA_bookstore.

Before using the USE the statement, we can get the list of all databases created by typing the following command.

SHOW DATABASES;

Here, we can see the list of all databases created.

Note – To view the current database being used, you can type the following command.

SELECT DATABASE();

Here, we can see the output being a table with the name of the current database i.e. USA_bookstore.

This is how you can select a MariaDB Database.

MariaDB Queries Date

In MariaDB, The queries can be issued to get information about data based upon the date, If the table contains the column containing the date, then we can get the specific rows by filtering the information based on date.

For example demonstration, we are going to use the table Student_Adm that we are going to create using the below code.

CREATE TABLE Student_Adm(id INT, stu_name VARCHAR(50),add_date DATE);

Insert the following records in that table.

INSERT INTO Student_Adm(id,stu_name,add_date)VALUES
(1,'Jhon','2010-02-15'),
(2,'Teresa R. Jackson','2010-04-15'),
(3,'Angela C. Wood','2011-06-10'),
(4,'Shannon M. Naylor','2017-05-15'),
(5,'Wm M. Higuera','2019-05-25'),
(6,'Nicholas P. Kosinski','2015-04-28'),
(7,'Benjamin H. Greggs','2016-03-06');

View the table data using the below code.

SELECT * FROM Student_Adm

Let’s extract the information from the table based on filter date using the below code.

SELECT stu_name,add_date FROM Student_Adm
WHERE add_date > '2012-01-01';

Here in the code, we are listing the student whose admission date is after 2012-01-01.

From the output, five students have taken admission after 2012-01-01.

MariaDB Query Execution Time

In MariaDB, to check the query execution time, the command SHOW PROFILES is used after running any query. But before running SHOW PROFILES we need to enable the session variable name profiling. This profiling is set to value 0 which means it is OFF by default.

To enable the profiling use the below code.

SET Profiling = 1;

After setting the value profiling to 1, run any query as shown below.

SELECT * FROM sales;

Now check the query name and its time to execute.

SHOW PROFILES;

Look at the above output, which shows the Query_ID, Duration and Query.

Read: MariaDB Backup Database

MariaDB Queries Kill

Here, we will learn how to kill or terminate a MariaDB Query.

To terminate a MariaDB query. we need to use the KILL statement which terminates the connection associated with a thread or query id.

The syntax of the KILL statement is given below.

KILL <thread_id| query_id>;

In the process of killing a query, firstly you need to know the running thread or active queries. We can do this using PROCESSLIST and then we can terminate any query with its Query Id or Thread Id using KILL statement.

The syntax for viewing the PROCESSLIST is given below.

SHOW [FULL] PROCESSLIST;

If you don’t use the FULL keyword, only 100 characters of the query will be displayed in the info field.

To view the running active threads/queries, we need to run the following command.

SHOW PROCESSLIST;

Now, we need to terminate the query using the KILL Statement by typing the following command.

KILL (17);

Here, ‘17‘ is the query id that we want to terminate, and after running this statement, the query will be terminated.

Note – Here, the ERROR 1927 can be seen which is a MariaDB Internal Error and should be ignored.

This is how we can terminate a MariaDB Query using the KILL and PROCESSLIST statements.

Read: MariaDB Reset Root Password

MariaDB Kill All Queries

In the above section “MariaDB Queries Kill”, we have learned about how to kill specific queries in MariaDB using the query Query Id or Thread Id. But here we will learn how to kill all queries relate to specific users in a database.

The syntax is given below to kill all queries.

KILL USER user_name

Where KILL USER is the command to kill the user with its related queries and user_name is the name of the user whose all queries we want to kill.

MariaDB Query Table Names

In this, we will learn about how can we get the table names using a query in MariaDB.

To get the names of tables in a particular database, we need to use the SHOW TABLES statement. It provides us with non-temporary tables, sequences, and views in a particular database.

The syntax for the SHOW TABLES statement is given below.

SHOW [FULL] TABLES
[LIKE 'pattern' | WHERE expr];

• FULL: It is a modifier that adds a new column ‘Table Type’, the values can be BASE TABLE, VIEW and SEQUENCE.
• Like: It indicates that the table name matches with a particular name pattern.
• Where: It can be used to select a table name with a specific condition.

Here is an example to get the name of tables using the SHOW TABLES statement.

SHOW TABLES;

Now, we will see the significance of the FULL keyword.

SHOW FULL TABLES;

Here, we can see that after adding the FULL keyword, a new column has been added which contains the type of table i.e. BASE TABLE corresponding to the table names.

Using the WHERE and LIKE clauses, we can get the names of tables with specific patterns or conditions.

Here is an example to get table names using SHOW TABLES with LIKE and WHERE clauses.

SHOW TABLES 
WHERE Tables_in_USA_data 
LIKE 'USA%';

Here, we can see that the table names starting with ‘USA’ is only displayed here.

This is how you can get the names of tables in MariaDB.

Read: MariaDB Enable Remote Access

MariaDB Query Optimization

In this, we will learn about Query Optimization, the reasons for slow queries, and how to resolve them in MariaDB.

Query Optimization is the process of identifying the slow queries using the slow query log which takes execution time more than the default long_query_time and tuning them to make execution much faster. The slow queries are identified by enabling MariaDB slow query log which is disabled by default.

After tracing the slow queries along with details of execution, an alternative plan for execution should be created to bring an optimal solution to execute the query statement.

MariaDB slow query log is a record of all the queries which take more than 10 seconds(Which is the default execution time) for execution. It is by default disabled, so you need to enable it by setting the slow_query_log variable to 1.

Once you recognize the slow query using the slow query log, here are a few optimization techniques to enhance MariaDB performance.

• 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

Use SHOW EXPLAIN – To know the execution details, use SHOW EXPLAIN statement to understand where the query is having problems or lacking making it a long-running query.
To tune the slow query, you can check each possible way of execution using the SHOW EXPLAIN statement to find the most optimal way for the execution of the slow query.

Normalizing your tables – It is the most important step to store your data in your database. You should reduce redundancy and also design rules to link different tables in your database.

Use the right data types – It is the most important and basic thing that you should be using the right data types in a table. For example, you should be using ‘Integer’ for Student_Id instead of ‘Varchar’ as it is lightweight and faster to execute.

Indexing all the columns – The columns should be indexed at the beginning which is going to be used or has been used in ‘where’, ‘group by’, ‘join’, and ‘group by’ clauses. It prevents the database from performing a full table scan to retrieve records.

By replacing the complex subqueries – You can replace the complex subqueries with the temporary tables, it may be useful in decreasing the execution time for the identified slow queries from the slow query log.

Use INNER JOINS instead of OUTER JOIN – Using the INNER JOINS instead of OUTER JOINS can tune the slow query which can enhance the performance.

Don’t include the unnecessary columns in the SELECT statement – We often use the ‘SELECT *’ statement to view the changes made in the table. Instead, we can only view the columns in which changes have been made and are necessary to be viewed.

Also, take a look at some more MariaDB tutorials.

• MariaDB Rename Table
• MariaDB Transaction – Helpful Guide
• MariaDB Select Statement- Detailed Guide

We can use the below syntax instead of using the ‘SELECT *’ statement.

SELECT Column1, Column2 FROM tableName;

In this tutorial, you have learned about MariaDB Queries. Additionally, we have covered the following topics.

• MariaDB Queries
• MariaDB Queries Per Second
• MariaDB Queries Log
• MariaDB Queries Slow
• MariaDB Show Queries
• 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