Commonly Used MySQL Functions You Must Know

Commonly Used MySQL Functions You Must Know

Commonly Used MySQL Functions You Must Know

MySQL is one of the most popular databases used all over the world for storing data. Developed by Oracle Corporation, the “My” part comes from the name of the daughter of Micheal Widenius, co-founder of MySQL, while SQL stands for Structured Query Language. Due to its advanced features and freeware nature, it is used by companies like Netflix, Verizon, Twitter among others as their primary database.

The main component of MySQL is its server, the MySQL Server. This is where the data is stored. A client like a web browser communicates with the server. MySQL functions are an important part of MySQL that enable this communication. In this article, we’ll discuss about some commonly used MySQL functions.

What is an MySQL Function?

A MySQL function is a stored program that can be called when required and passed parameters to get a value in return. You can create your own functions, but there are many built-in functions provided you can use off the shelves.

You can use the functions in SQL statements or as queries in MySQL. Alternatively, they can also be used within a MySQL programming environment.

Types of MySQL Functions

There are different types of MySQL Functions. These can be divided into 7 categories. Those are:

1. MySQL Aggregate Functions

In these type of functions, multiple rows are added to provide a single summary value as output. The AVG() is the most common Aggregate function used in MySQL. It takes in multiple numbers are input and returns their average as output.

The syntax for the Aggregate functions is:

Code: 
function_name(DISTINCT | ALL expression)
  • function_name is the name of the function
  • Inside the brackets, you can either use DISTINCT or ALL depending on whether you want to calculate distinct values or all values including duplicates
  • Finally provide the expression consisting of column and arithmetic operation
Some aggregate functions in MySQL are:
  • AVG()
Basic Syntax:
Code: 
AVG(DISTINCT | ALL expression)
SELECT
AVG(buyprice) ‘Average Price’
FROM
products;
This code will return the average price of all the product’s price listed in the products table.
  • COUNT()
It has three forms: COUNT(*), COUNT(expression), COUNT(DISTINCT expression)

The first one returns the number of rows in a set.

Syntax:

Code: 
SELECT COUNT(*)
FROM count_employees;
The above code will return all rows from count_employees table.
  • MAX()
Syntax

Code: 
SELECT MAX(amount)
FROM salaries;
The above expression will find the biggest amount from the salaries table.
  • MIN()
Syntax

Code: 
SELECT MIN(buyPrice)
FROM grocery;
This code will select the minimum price from the grocery table.
  • VARIANCE()
Syntax

Code: 
SELECT VARIANCE(total_cost)
FROM database;
This code will return the population standard variance.

2. Comparison Functions

These types of MySQL functions are used to compare the values in a row or array and returns the desired value, without performing any calculations. For example, in GREATEST() function, it returns the greatest value in the row as output.

Some example of MySQL Comparison Functions are:
  • COALESCE()
Syntax

Code: 
SELECT COALESCE(state, N/A)
FROM addresses;
In the above code, COALESCE function will look for the values in state and if it finds NULL, it will replace it with N/A.
  • GREATEST() and LEAST()
Syntax

Code: 
SELECT
employee_id
LEAST (s1, s2, s3, …, sN) low,
GREATEST (s1, s2, s3,…, sN) high
FROM
employees;
The above code will return the highest and lowest values in the list. If there’s a NULL value, then it’ll return NULL without doing any calculations.
  • ISNULL()
Syntax

Code: 
SELECT ISNULL (NULL);
It checks whether the argument is NULL or not.

3. Control Flow and Expression Functions

MySQL Control Flow Functions are the functions to use if you want to add logic to your MySQL program. These functions save you the time of writing procedural code on your own.

Commonly used functions of this category are:
  • CASE
Syntax

Code: 
CASE
WHEN condition1 then statement1
WHEN condition2 then statement2
[ELSE else_result]
END
CASE functions evaluates each condition and then performs the corresponding statement. If nothing found, then executes the else_result.
  • IF()
Syntax

Code: 
SELECT IF(expression, true_expression, false_expression)
If the expression evaluates to true, then true_expression is executed, otherwise false_expression.
  • IFNULL()
Syntax

Code: 
IFNULL(expr_1, expr_2)
Returns expr_1 if it is not NULL, otherwise returns expr_2.
  • NULLIF
Syntax

Code: 
NULLIF(expr_1, expr_2)
Returns NULL expr_1 is equal to expr_2, otherwise returns expr_1.

4. Date Functions

If you want to manipulate date and time in a MySQL environment, then employ Date functions. One of the functions, DAY(), returns the day of the month of a specific date as output.

Common date functions in MySQL are:
  • CURDATE()
Syntax

Code: 
SELECT CURDATE()
Returns the current date in the YYYY-MM-DD format.
  • DATEDIFF()
Syntax

Code: 
SELECT DATEDIFF(first_date, second_date)
Calculates the time difference between the first and second date.
  • DAY()
Syntax

Code: 
SELECT DAY(‘Date’)
Selects the date from the current date.
  • DATE_ADD()
Syntax

Code: 
SELECT DATE_ADD(start_date, INTERVAL expr unit);
It takes in two arguments. First is the starting date and second is the interval value that is added to the starting date.

5. String Functions

If you want to work with character string data in MySQL and manipulate them at will, then you should be using MySQL string functions. For example, CONCAT() will combine two or more strings into an unified string. Please note, this is different from aggregate function and is meant only to manipulate strings. While aggregate functions can manipulate other data type as well.

Common string functions in MySQL are:
  • CONCAT()
Syntax

Code: 
SELECT CONCAT(str1, str2, str3, ...)
Converts the arguments to strings followed by concatenating to a single value. NULL is the output if any of the arguments in NULL.
  • INSTR()
Syntax

Code: 
SELECT INSTR(str, substr)
INSTR check for the first appearance of the substring in a string. First argument is the string (str), second argument is substring (substr).
  • LENGTH()
Syntax

Code: 
SELECT LENGTH(str);
Returns the length of the string (str) in bytes.

6. Window Functions

Window functions in MySQL allow you to take inputs from the window of multiple rows. You should use them in solving analytical queries. These are distinguished from other MySQL functions by the OVER prefix.

Some common MySQL window functions include:
  • CUME_DIST()
Syntax

Code: 
CUME_DIST() OVER {
PARTITION BY expr, ….
ORDER BY expr [ASC | DESC], ….
}
Calculates and returns the cumulative distribution of a particular value within the set.
  • DENSE_RANK()
Syntax

Code: 
DENSE_RANK() OVER {
PARTITION BY <expr>[{, <expr>...}]
ORDER_BY <expr> [ASC | DESC], [{, <expr>}]
}
DENSE_RANK function assigns rank to each row present within a partition or result set. This returns consecutive rank values.
  • FIRST_VALUE()
Syntax

Code: 
FIRST_VALUE (expr) OVER {
[partition_clause]
[order_clause]
[frame_clause]
}
FIRST_VALUE() will select the first row of a window frame depending on the clauses mentioned.

7. Math Functions

Math functions in MySQL allows you to carry out mathematical calculations. For example, ROUND() function will round off a number to a specific number of decimal places.

Commonly used MySQL match functions are:
  • ABS()
Syntax

Code: 
SELECT ABS(n);
The ABS function will return the absolute positive value of n. For example, -20 will return 20.
  • ROUND()
Syntax

Code: 
SELECT ROUND(n, [d]);
This function will round off the number (n) to the decimal places ([d]). [d] is optional here.
  • TRUNCATE()
Syntax

Code: 
SELECT TRUNCATE(X, D);
This function will truncate the number (X) to a specific number of decimal places which is specified as D. So TRUNCATE(1.555, 1) will return 1.5.

  • DELETE()
Syntax
Code: 
DELETE FROM table_name WHERE condition;
These built-in function save a lot of your programming hours. There are many more useful built-in MySQL functions which you can access here. Apart from that, you can create your own!
Code: 
SELECT IF(expression, true_expression, false_expression)
Author
bhawanisingh
Views
7,419
First release
Last update
Rating
0.00 star(s) 0 ratings

More resources from bhawanisingh

Share this resource

Top