How Does MySQL Help?
MySQL provides a lot of functions that can help perform many tasks. These functions can be used in lieu of
code at the application level, or they can be used in conjunction with the code in your application. Let the
guidelines that were discussed earlier help you decide what to do.
Because of MySQL's speed, it is sometimes to your advantage to implement some of the functionality
that you would need in your application at the server level. Cheat a little. Take advantage of what
MySQL has to offer. If you need a full name to be displayed in your application and you store the last
name and first name in two different columns, use a MySQL intrinsic function to tie them together before
it is returned to your application. This can save some time and headaches.The Basic MySQL Functions
The MySQL functions can be broken down into four types: numerical or mathematical functions, logic
functions, string manipulation functions, and date and time functions. You will learn about the first three types
today. Because of the complexity and number of the date and time functions, they will be covered on Day 16,
"MySQL and Time." There is also an alphabetical listing of each function with a brief description and example
listed in Appendix B, "Current MySQL Functions," for your convenience.
Today's lesson covers only a partial listing of the functions. The most frequently used functions are
discussed, and there are some examples showing how to use them.Numerical Functions
The numerical functions are composed of various mathematical operations. They range from the complex
(standard deviation) to the simple (addition). In this section, the use and syntax of the more simple
operations will be discussed. After you get the hang of how they're used, you can look at Appendix B and try
it with the more complex functions.
The anatomy of a function is quite simple. You have the function name followed by its arguments, which
are enclosed in parentheses (see Figure 10.1). There is no space between the name of the function and
the opening parenthesis ("("). If you put in a space unintentionally, you will get an error because
MySQL will think you are trying to name a column from your table instead of a function—that's the way
the parser works. A function may take one, more than one, or no arguments. The numeric function can
only use number data types, and the string functions can only use character or string data types. If you
try to operate on something for which the function was not intended, an error is returned.
Figure 10.1 The anatomy of a function.
Addition
All of the MySQL functions operate pretty much the same way. To use the addition function, or any other
function for that matter, you must call the function from inside an SQL statement. You would use the
additional function as shown in the following:
SELECT (Column_1 + Column_2) as Total FROM Orders
This statement would return the total of the values in Column_1 and Column_2 for every row in the
table. For example, suppose you have two rows in your table. The first column in the first row has a
value of 3, and the second column in the same row has a value of 4. The second row of your table has
the values 3 and 3. It would look something like the following:
Column 1 Column 2
3 4
3 3Now, if you performed the query shown in the beginning of this section, your result set would be
Total
7
6