MySQL Most Popular Functions Tutorial

1. Introduction

In this post, we feature a comprehensive Tutorial on MySQL Most Popular Functions. MySQL is one of the most used SQL databases in many of the world-enterprise applications due to its ability to manage transactions and the ACID-behaviour which is built into its core. One of the reasons MySQL is so popular is due to the easiness it provides for its usage and its related commands. In the open source web application framework LAMP (which consist of Linux, Apache, MySQL and PHP), MySQL server is a central & important component. The MySQL Database server is written using C and C++ which internally uses a lexical analyser to parse and understand the SQL queries.

We will start this lesson by simple installation process of MySQL server followed by some popular MySQL functions which are helpful in a daily life of not only a Database Administrator but a Developer as well. These functions will involve performing aggregations on data, grouping it by some means, finding maximum and minimum of records with given condition and much more. When we need to look at our data at a deeper level and find some insights in it which are not clearly visible in the data, we order it, aggregate it and then show it to the end users to answer business questions which ask about deeper meaning of the data.

2. MySQL Setup

For this lesson, we will make a new server which we will be using as our MySQL installation. This installation is on an Ubuntu 16.04 machine but it can be installed over any other operating systems in a very similar fashion as this one. To proceed further, we need to set up MySQL Server and Client tools on that server. We can do this by executing the following command:

Installing MySQL Server and Client

sudo apt-get install mysql-server mysql-client

Once we run this command, the mentioned utilities will be installed on the server which includes MySQL Server and the client utility. While installing, we also set a MySQL root password:

MySQL Most Popular Functions - Setting Root Password
Setting Root Password

Once the installation process is completed, we can confirm if the MySQL server is up and running by executing the following command:

Check MySQL Service status

sudo service mysql status

On an Ubuntu machine, we will see an Active status for the mysql service when we execute the above command:

MySQL Most Popular Functions - Check MySQL Server Status
Check MySQL Server Status

Now that we know that the MySQL server is up and running, we can connect to it using the root username and password we supplied to it during the installation process. We can use the following command to do so:

Logging into MySQL

mysql -u root -p

When we press enter after entering the command, MySQL Server will silently wait for us to provide the password. When we type it, the password will not be echoed back to the terminal due to security reasons. We will see the following prompt once we have logged into the MySQL command line:

MySQL Most Popular Functions - MySQL Login
MySQL Login

When we are inside the MySQL command prompt, we can use the given command to show the databases which are present in the system and to ensure that MySQL is running fine:

Show All databases

show databases;

Here is what we get back with this command:

MySQL Most Popular Functions - MySQL Databases
MySQL Databases

In the output, MySQL just displays the list of MySQL default databases that are used for administration purpose. As this is our fresh installation, only four databases are visible as of now which are made by MySQL itself. Now that we know that our MySQL set up is complete and running, we can start working with some popular MySQL functions to try with sample data.

In this section, we will look at most used MySQL functions which are helpful and should be known when you are working with MySQL to gain a deeper insight into your data. Let us quickly take a look at the database and tables we will use in the remainder of the section of this tutorial. Here is the list of databases currently in my installation:

MySQL Most Popular Functions - MySQL Databases
MySQL Databases

Once we start using the database, here is the list of tables in the schema jcg_schema which we will use:

MySQL Most Popular Functions - MySQL Table List
MySQL Table List

If you want to know the query with which we created the table Posts, here is the query for the same:


mysql> CREATE Table Posts (
    -> title varchar(255) NOT NULL,
    -> author_id INT NOT NULL,
    -> views INT  DEFAULT 0,
    -> date DATE NOT NULL);
Query OK, 0 rows affected (0.01 sec)

Let us describe the table as well to get a tabular view of the schema:

MySQL Most Popular Functions - Posts Table structure and create statement
Posts Table structure and CREATE statement

Finally, we will insert some data into the Posts table so that we can perform some operations on this data. Here is the SQL query we used to insert the data into the mentioned Posts table which you can use to replicate data as is:

Insert Data into Table

INSERT INTO Posts (title, author_id, views, date) VALUES
('MySQL Aggregation Tutorial', 1, 12, '2015-12-14'),
('MySQL Full-text Search Tutorial', 2, 23, '2018-10-10'),
('MySQL Replication Tutorial', 3, 108, '2018-09-26'),
('MySQL Server Tutorial', 1, 12, '2018-12-14'),
('MySQL Command Line Tutorial', 3, 887, '2018-09-22'),
('MySQL Popular Functions Tutorial', 2, 1987, '2018-08-21'),
('MySQL Clustering Tutorial', 5, 901, '2018-07-19'),
('Java JDBC Drivers', 6, 1097, '2018-06-18'),
('Java ODBC Drivers', 7, 1901, '2018-05-17'),
('Java Spring Boot tutorial', 11, 2103, '2018-04-16'),
('Java Spring Cloud Tutorial', 2, 1965, '2018-03-27'),
('Java Spring Batch Tutorial', 2, 2065, '2018-11-25'),
('Java Spring Data Tutorial', 3, 1122, '2018-10-24'),
('Java Spring MongoDB Tutorial', 3, 2211, '2018-09-23'),
('Java Spring Elasticsearch Tutorial', 1, 1093, '2018-08-22'),
('Java Spring GraphQL Tutorial', 1, 112, '2018-07-05'),
('Java Spring NoSQL Tutorial', 7, 554, '2018-06-07'),
('Java Spring Solar Tutorial', 7, 665, '2018-11-08'),
('Java Spring Redis Cache Tutorial', 8, 772, '2018-11-09'),
('Java Spring RabbitMQ Tutorial', 9, 109, '2018-11-12'),
('MongoDB NoSQL Booster Tutorial', 10, 1201, '2018-12-13'),
('Python Tutorial', 11, 952, '2018-06-14'),
('Python datetime module Tutorial', 12, 654, '2018-07-14'),
('Python sys module Tutorial', 13, 962, '2018-08-14'),
('Python pwd module Tutorial', 14, 904, '2018-03-18'),
('Python date module Tutorial', 12, 219, '2018-02-21'),
('Python MySQL Tutorial', 14, 2102, '2018-01-24'),
('MySQL NodeJS Tutorial', 11, 905, '2018-05-24');

Once we see what all the data is present in our table, here is the output we will get:

MySQL Most Popular Functions - Posts Table data
Posts Table data

3.1 MAX Function

One of the most popular MySQL function is the MAX Function. This is used to find the maximum value in a set of values or a column. For example, we can use the MAX function to get the most viewed author in the Posts table with the following query:

MAX Function

SELECT id, MAX(views)
FROM Posts

Above query will display the posts ordered by maximum views in a descending order. Notice that we selected the MAX column and we ordered by the same column with a MAX function again. Let us see what output this command produces:

MySQL Most Popular Functions - SQL Max Function
SQL Max Function

Please note that when we use aggragation functions like MAX, we must GROUP BY all fields which we want to SELECT in the SQL query. You can try the MAX function on Date column as well, as it works by using the collating sequence so it can work on character and datetime columns in addition to numeric ones.

3.2 MIN Function

Another function which comes handy while using SQL query is the MIN function which, as it sounds, works opposite to the MAX function we saw in the last section. This function also uses a collate sequence so that it can also work with numeric, date and alphabet columns. Let us look at an example to see what output it creates:

MIN Function

SELECT id, MIN(views), title 
ORDER BY MIN(views);

Above query will display the posts ordered by minimum views. Let us see what output this command produces:

MySQL Most Popular Functions - SQL Min function
SQL Min function

Clearly, we selected three columns with the MIN function and arranged the items with minimum views to be on top.

3.3 AVG Function

With the AVG function, we can calculate the average value of the set of values or an expression or a column. We can couple the AVG function with the DISTINCT keyword to calculate the average value of the distinct values as well, without which it will take into account repeating values as well. Let us look at the example for AVG Function here:

AVG Function

SELECT author_id, AVG(views) 
FROM Posts 
GROUP BY author_id 

Above query will display the posts ordered by average views any author will get. Let us see what output this command produces:

MySQL Most Popular Functions - SQL AVG Function
SQL AVG Function

See that we didn’t have to use the DISTINCT keyword to select disctinct author’s by their ID because we already grouped them by their ID. Even though average is a nice function, statistically speaking, it might not always give how your authors perform in an overall manner.

3.4 SUM Function

One of the most used function is SUM function in SQL, with which we can sum a given column based on a group. Let us look at an example where we can total the number of views which any Author get on his views:

SUM Function

SELECT author_id, SUM(views)
FROM Posts
GROUP BY author_id

Above query will display the view count added with any author get on his posts. Let us see what output this command produces:

MySQL Most Popular Functions - SUM function in SQL
SUM function in SQL

3.5 COUNT Function

The COUNT function counts the number of rows when it is used with the GROUP BY function. We will use this function to find the number of posts produced by each Author. Let us look at the query for the mentioned example here:

COUNT Function

SELECT author_id, COUNT(1)
FROM Posts
GROUP BY author_id

Above query will display the number of posts by any given author. Let us see what output this command produces:

MySQL Most Popular Functions - SQL Count functions
SQL Count functions

Please note that if we had not used the GROUP BY author_id clause in the above query, the author_id field could have duplicated and the count provided will be incorrect.

3.6 DISTINCT Function

With one of the most common function to be used in SQL, the DISTINCT function allows us to select distinct values of a given column. Let us look at an example to collect all distinct Author IDs which have written at least one post in the following example query:


FROM Posts;

Above query will display distinct author which are present in the Posts table. Let us see what output this command produces:

MySQL Most Popular Functions - SQL Distinct function
SQL Distinct function

5. MySQL Most Popular Functions – Conclusion

In this lesson, we looked at some of the most popular functions used in MySQL today by developers and database administrators to gain a deeper insight into their data. All of the functions we mentioned use aggregation internally to collect data and perform necessary operations to filter the values. We use all of these aggregation functions every day while writing SQL queries for various of our operations and that is why they are necessary to be understood. Once the size for the data starts increasing, the aggregation functions start to perform slower because fo the fact that they need to perform a complete table scan (if proper indexes are not placed correctly) to find complete data they must perform upon.

We did not cover all the SQL functions in this lesson but only the ones which were most popular in everyday SQL queries we write for performing aggregation on data and collect important results. Don’t forget to check the examples of using Java with SQL: JDBC Best Practices Tutorial and Java JDBC ResultSet Example. These lessons explain how to effectively use SQL commands with Java & JDBC drivers.

Shubham Aggarwal

Shubham is a Java Backend and Data Analytics Engineer with more than 3 years of experience in building quality products with Spring Boot, MongoDB, Elasticsearch, MySQL, Docker, AWS, Git, PrestoDB tools and I have a deep knowledge and passion towards analytics, Micro-service based architecture, design patterns, antipatterns and software design thinking.
Notify of

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Newest Most Voted
Inline Feedbacks
View all comments
Back to top button