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:
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:
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:
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:
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.
3. MySQL Popular functions
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:
Once we start using the database, here is the list of tables in the schema jcg_schema which we will use:
If you want to know the query with which we created the table Posts, here is the query for the same:
pom.xml
mysql> CREATE Table Posts (
-> id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
-> 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:
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:
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 GROUP BY id ORDER BY MAX(views) DESC;
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:
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 FROM Posts GROUP BY id ORDER BY MIN(views);
Above query will display the posts ordered by minimum views. Let us see what output this command produces:
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 ORDER BY AVG(views) DESC;
Above query will display the posts ordered by average views any author will get. Let us see what output this command produces:
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 ORDER BY SUM(views) DESC;
Above query will display the view count added with any author get on his posts. Let us see what output this command produces:
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 ORDER BY COUNT(1) DESC;
Above query will display the number of posts by any given author. Let us see what output this command produces:
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:
DISTINCT Function
SELECT DISTINCT(author_id) FROM Posts;
Above query will display distinct author which are present in the Posts table. Let us see what output this command produces:
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.
