Chapter 5 ■ MySQL priMerThis discussion on joins touches only the very basics. Indeed, joins are arguably one of the most
difficult and often confused areas in database systems. If you find you want to use joins to combine several
tables or extend data so that data is provided from several tables (outer joins), you should spend some time
with an in-depth study of database concepts such as Clare Churcher’s book Beginning Database Design
(Apress, 2012).
Additional Advanced Concepts
There are more concepts and commands available in MySQL, but two that may be of interest are PROCEDURE
and FUNCTION, sometimes called routines. I introduce these concepts here so that if you want to explore
them, you understand how they are used at a high level.
Suppose you need to run several commands to change data. That is, you need to do some complex
changes based on calculations. For these types of operations, MySQL provides the concept of a stored
procedure. The stored procedure allows you to execute a compound statement (a series of SQL commands)
whenever the procedure is called. Stored procedures are sometimes considered an advanced technique
used mainly for periodic maintenance, but they can be handy in even the more simplistic situations.
For example, suppose you want to develop your IOT solution, but since you are developing it, you need
to periodically start over and want to clear out all the data first. If you had only one table, a stored procedure
would not help much, but suppose you have several tables spread over several databases (not unusual for
larger IOT solutions). In this case, a stored procedure may be helpful.
■Tip When entering commands with compound statements in the MySQL client, you need to change
the delimiter (the semicolon) temporarily so that the semicolon at the end of the line does not terminate the
command entry. For example, use DELIMITER // before writing the command with a compound statement, use
// to end the command, and change the delimiter back with DELIMITER ;. this is only when using the client.
Since stored procedures can be quite complicated, if you decide to use them, read the “CREATE
PROCEDURE and CREATE FUNCTION Syntax” section of the online reference manual before trying to
develop your own. There is more to creating stored procedures than described in this section.
Now suppose you want to execute a compound statement and return a result—you want to use it
as a function. You can use functions to fill in data by performing calculations, data transformation, or
simple translations. Functions therefore can be used to provide values to populate column values, provide
aggregation, provide date operations, and more.
You have already seen a couple of functions (COUNT, AVG). These are considered built-in functions, and
there is an entire section devoted to them in the online reference manual. However, you can also create your
own functions. For example, you may want to create a function to perform some data normalization on your
data. More specifically, suppose you have a sensor that produces a value in a specific range, but depending
on that value and another value from a different sensor or lookup table, you want to add, subtract, average,
and so on, the value to correct it. You could write a function to do this and call it in a trigger to populate the
value for a calculation column.
■Tip Use a new column for calculated values so that you preserve the original value.