Chapter 6: Selecting Data Part 2

 

In the previous chapter, we learned quite a few keywords that we can use to select data from our tables.

 

In this chapter, we’ll learn to use built-in functions in our SELECT statements.

 

What is a Function?

 

First off, what is a function?

 

A function is a block of code that does a certain job for us. For an analogy, think of the mathematical functions available in MS Excel. To add numbers, we can use the sum() function and type sum(A1:A5) instead of typing A1+A2+A3+A4+A5.

 

A function in MySQL is similar; it helps us accomplish certain tasks more easily.

 

MySQL provides us with a large number of pre-written functions that we can use in our SQL statements.

 

The full list of MySQL functions can be found at https://dev.mysql.com/doc/refman/8.0/en/built-in-function-reference.html.

 

Let’s look at some of them in this chapter.

 

MySQL Functions

 

The first two functions that we’ll look at are for working with strings.

 

CONCAT()

 

The first is the CONCAT() function. This function allows us to combine two or more strings into a single string. This is known as concatenating the strings.

 

To use the CONCAT() function, we need to provide it with some input. Specifically, we need to tell the function what strings to concatenate.

 

For instance, if we want to concatenate 'Hello' and ' World', we write

 

CONCAT('Hello', ' World');

 

To display the result of this function, we use the SELECT keyword.

 

Besides using the SELECT keyword to select information from tables, we can also use it to display messages.

 

Try running the statement below:

 

SELECT CONCAT('Hello', ' World');

 

You’ll get

 

Hello World

 

as the output.

 

SUBSTRING()

 ...

Aggregate Functions

 

Besides the functions mentioned above, MySQL also comes with a large number of pre-written aggregate functions.

 

An aggregate function is a function that performs calculation on a set of values and returns the result of the calculation as a single value.

 

We can use some of these built-in aggregate functions to perform various calculations on the data in our tables.

 

Let’s look at some of these functions and apply them on our employees table.

 

The calculations below are based on the employees table found in Appendix A.

 

The commonly used aggregate functions in MySQL include:

 

COUNT()

 

The COUNT() function returns the number of the rows in the table.

 

If we pass in * to the function, it returns the total number of rows in the table.

 

If we pass in a column name instead, it returns the number of non NULL values in that column (NULL values are ignored).

 

If we want to remove...