MYSQL Functions: All You Need to Know

A guide that shows you everything you need to know about MYSQL functions.

MySQL is an open-source relational database management system. For you to add, access, and process data stored in a computer database, you need a database management system such as MySQL Server.

Now, we need to understand what MySQL Functions are. A function is basically a stored program that you can pass parameters into and then return a value. They are simply pieces of code that perform some operations and then return a result.

We will look at the different types of functions we have.

Types of functions

MySQL came wrapped up with different built-in functions. These built-in functions are functions that have been implemented on the MySQL server. With these functions, we can perform different types of manipulations on our data. We can classify these functions into the different categories below;

• Numeric functions — These are functions that operate on numeric data types
• Strings functions — These are functions that operate on string data types
• Aggregate functions — These are functions that operate on all different data types and produce summarized result sets.
• Date functions — They are functions that operate on date data types.

We will discuss each of these different functions one after the other

Numeric functions

For numeric functions, we can perform mathematical calculations on numeric data in the SQL statements.

Here, we have the Arithemetic operators which consist of the following operators in which we can compute Division, Subtraction, Addition e.t.c. on any numeric data.

Addition operator (+)

1SELECT 50 + 2 ;

Whenever we run this script, this should return 52

Integer Division (DIV)

1SELECT 51 DIV 6 ;

Whenever we run the above script, it should give us 8

Division operator (/)

1SELECT 51 / 2 ;

Whenever we run the above script, it should give us 25.5

Subtraction operator (-)

1SELECT 51-6 ;

Whenever we run the above script, it should give us 45

Now, let’s move to the strings functions

Strings Functions

As we mentioned earlier the different operators the numeric functions entail, we have the same here. The string function takes a string value as an input regardless of the data type of the returned value.

There are many built-in string functions available for use, some of these are listed below;

An example on CONCAT and CHAR_LENGTH is done below;

CONCAT

1 mysql> SELECT CONCAT('My', 'S', 'QL'); 
2 +---------------------------------------------------------+ 
3 |                 CONCAT('My', 'S', 'QL')                 | 
4 +---------------------------------------------------------+ 
5 |                          MySQL                          | 
6 +---------------------------------------------------------+ 
7 1 row in set (0.00 sec)

CHAR_LENGTH

1 mysql> SELECT CHAR_LENGTH("text"); 
2 +---------------------------------------------------------+ 
3 |                   CHAR_LENGTH("text")                   | 
4 +---------------------------------------------------------+ 
5 |                            4                            | 
6 +---------------------------------------------------------+ 
7 1 row in set (0.00 sec)

Aggregate functions

MySQL Aggregate functions is always very useful whenever we want to get the statistics of our data. We can calculate the average of all values, the sum of all values, maximum & minimum value among certain groups of values.

MySQL supports the following Aggregate functions;

Let’s look at the example below on Aggregate functions;

Suppose we have price listings for different groceries listed above, we will implement one of the aggregate functions on it.

1 +-----------+----------------------------+--------------+-----+ 
2 | groc_code | groc_name                  | groc_price  
3 +-----------+----------------------------+--------------+-----+ 
4 |         1 |Little Steps Growing up Milk|    7.75  
5 |         2 | Sacla Basil Pesto 190g     |    2.50  
6 |         3 | Golden Caster Sugar 500g   |    1.30 
7 |         4 | Higgidy Spinach & Pine Nut |    4.00 
8 |         5 | Heinz Salad Cream 605g     |    2.50

We will calculate average of the groceries prices;

1 mysql> SELECT AVG(groc_price) AS price_ag FROM groceries; 
2 +----------+ 
3 | price_ag | 
4 +----------+ 
5 |  3.61 | 
6 +----------+ 
7 1 row in set (0.00 sec)

Date functions

We have different Date and Time Functions in MySQL. Few out of these functions are listed below;

An example on how to use CURDATE & DATE_ADD is shown below;

CURDATE

1 mysql> SELECT CURDATE(); 
2        -> '2022-04-21' 
3 mysql> SELECT CURDATE() + 0; 
4        -> 20220421

DATE_ADD

1 mysql> SELECT DATE_ADD('2008-01-02', INTERVAL 31 DAY); 
2        -> '2008-02-02' 
3 mysql> SELECT ADDDATE('2008-01-02', INTERVAL 31 DAY); 
4        -> '2008-02-02'

If you enjoy reading stories like these, and want to support our writers, consider signing up to become a Medium member. It’s $5 per month, giving you unlimited access to stories on Medium. If you sign up using our link, we’ll earn a small commission.