only for RuBoard  do not distribute or recompile 
Functions and operators can be used in SQL statements. This section lists these functions and operators and provides examples. A full list of functions with examples is available in Section 7.4 of the manual.html file distributed with MySQL.
Table 32 shows examples of the basic arithmetic and comparison operators in SELECT statements. The basic arithmetic operators are *, +, /, and , as well as the parentheses ( ) to control the order of evaluation of an expression.
Statement 
Output 

SELECT 8+3*2; 
14 
SELECT (8+3)*2; 
22 
SELECT 2=2; 
1 
SELECT 1!=2; 
1 
SELECT 2<=2; 
1 
SELECT 3<=2; 
0 
The comparison operators include =, !=, <, >, <=, and >=. Four examples are shown in Table 32. If an expression evaluates as true, the output is 1; if an expression evaluates as false, the output is 0. To test for equality, a single equals sign is used; this contrasts with PHP, where the double equals (==) is used for equality tests, and a single equals sign is used for assignment.
To test whether two items are equal, the != operator is provided. Lessthanorequalto is represented by <=, and greaterthanorequalto is represented by >=. Parentheses can explicitly express the evaluation order.
Table 33 shows examples of the MySQL stringcomparison operators and functions. Many of the MySQL string functions shown here are similar to PHP functions, which were introduced in Chapter 2.
Statement 
Output 

SELECT 'Apple' LIKE 'A%'; 
1 
SELECT 'Apple' LIKE 'App%'; 
1 
SELECT 'Apple' LIKE 'A%l%'; 
1 
SELECT concat('con','cat'); 
'concat' 
SELECT length('Apple'); 
5 
SELECT locate('pp','Apple'); 
2 
SELECT substring('Apple',2,3); 
'ppl' 
SELECT ltrim(' Apple'); 
'Apple' 
SELECT rtrim('Apple '); 
'Apple' 
SELECT trim(' Apple '); 
'Apple' 
SELECT space(3); 
' ' 
SELECT strcmp('a','a'); 
0 
SELECT strcmp('a','b'); 
1 
SELECT strcmp('b','a'); 
1 
SELECT lower('Apple'); 
'apple' 
SELECT upper('Apple'); 
'APPLE' 
The string functions work as follows:
The stringcomparison function LIKE is useful. The % character represents any number of unspecified characters, are generally known as wildcards. So, for example, the comparison of the string 'Apple' LIKE 'A%' is 1 (true), as is the comparison of 'Apple' LIKE 'App%'. The underscore character can be used to match a single unspecified, wildcard character; for example, 'Apple' LIKE 'Appl_' is true, while 'Appl' LIKE 'Appl_' is false.
concat( ) joins or concatenates two strings together, so the result of calling concat( ) with two string parameters is a single string consisting of the parameters.
locate( ) returns the location of the first string parameter in the second string parameter. If the string doesn't occur, the result is 0.
substring( ) returns part of the string passed as the first parameter. The string that is returned begins at the offset supplied as the second parameter and is of the length supplied as the third parameter.
ltrim( ) removes any leftpadding space characters from the string parameter and returns the lefttrimmed string.
rtrim( )removes any rightpadding space characters from the string parameter and returns the righttrimmed string.
trim( ) performs the function of both ltrim( ) and rtrim( ); that is, any leading or trailing spaces are removed, and the trimmed string is returned.
space( ) returns a string consisting of spaces of the length of the integer parameter.
strcmp( ) compares two string parameters. If they are identical, it returns 0. If the first string is alphabetically less than the second, it returns a negative number. If the first string is alphabetically greater than the second, it returns a positive number. Uppercase characters are less than lowercase characters.
lower( ) converts the string parameter to lowercase and returns the lowercase string.
upper( ) converts the string parameter to uppercase and returns the uppercase string.
While not detailed in Table 33, regular expressions can be used through the function regexp( ). For more on regular expressions in PHP, see Chapter 2.
We make little use of the mathematical functions provided by MySQL in this book. However, Table 34 shows selected MySQL mathematical functions and their output.
Statement 
Output 

SELECT abs(33); 
33 
SELECT abs(33); 
33 
SELECT mod(10,3); 
1 
SELECT 10 % 3; 
1 
SELECT floor(3.14159); 
3 
SELECT ceiling(3.14159); 
4 
SELECT round(3.14159); 
3 
SELECT log(100); 
4.605170 
SELECT log10(100); 
2 
SELECT pow(2,3); 
8 
SELECT sqrt(36); 
6 
SELECT sin(pi( )); 
0.000000 
SELECT cos(pi( )); 
1.000000 
SELECT tan(pi( )); 
0.000000 
SELECT rand( ); 
0.8536 
SELECT truncate(3.14159,3); 
3.141 
SELECT format(12345.23,0); 
12,345 
SELECT format(12345.23, 1); 
12,345.2 
Several of the functions in Table 34 require some explanation:
The abs( ) operator returns the absolute value of a number; that is, it removes the negative sign from negative numbers.
The modulo operator—which has two identical variants, % and mod( )—divides the first number by the second number and outputs the remainder.
The floor( ) and ceiling( ) functions are complementary: floor( ) returns the largest integer not greater than the parameter; ceiling( ) returns the smallest integer not less than the parameter.
The round( ) function rounds to the nearest integer.
Both the natural logarithm, log( ), and base10 logarithm, log10( ), are available.
The pow( ) function raises the first number to the power of the second.
sqrt( ) takes the square root of the parameter.
The trigonometry functions sin( ), cos( ), and tan( ) take values expressed in radians as parameters. The complementary arc sin, arc cos, and arc tan are available as asin( ), acos( ), and atan( ).
The rand( ) function returns a pseudorandom number in the range 0 to 1.
The truncate( ) function removes decimal places without rounding.
The format( ) function isn't really a mathematical function but is instead used for returning numbers in a predefined format. The first parameter is the number, and the second parameter is the number of decimal places to return. The first parameter is rounded so that, for example, 123.56 formatted to one decimal place is 123.6. This function is seldom used in web database applications, because formatting is usually performed in PHP scripts.
Table 35 shows sample uses of selected time and date functions available in MySQL. The date_add( ) function can be used to add and subtract times and dates; more details can be found in Section 7.4.11 of the manual.html file distributed with MySQL.
Statement 
Output 

SELECT dayofweek('20000503'); 
3 
SELECT dayname('20000503'); 
Wednesday 
SELECT monthname('20000503'); 
May 
SELECT week('20000503'); 
18 
SELECT date_add("20000503", INTERVAL 1 DAY); 
20000504 
SELECT curdate( ); 
20020101 
SELECT curtime( ); 
11:27:20 
SELECT now( ); 
20020101 11:27:20 
Miscellaneous operators and functions are shown in Table 36.
Statement 
Output 

SELECT if(1<0,"yes","no") 
no 
SELECT password('secret') 
428567f408994404 
SELECT encode('secret','shhh') 
"ï ¨~ 
SELECT decode('"ï ¨~','shhh') 
secret 
Other functions 

SELECT database( ) 
winestore 
SELECT user( ) 
dimitria@localhost 
The conditional function if outputs the first string if the expression is true and the second if it is false. This can be used in complex ways. For example, it could be used in an UPDATE statement for intelligent changes to an attribute:
UPDATE customer SET country = if(trim(country)='','Australia',country);
In this case, the SQL statement replaces blank country attributes with Australia and leaves already filled country attributes unaltered.
Authentication and securing data using password( ), encode( ), and decode( ) are discussed in Chapter 9. The functions database( ) and user( ) provide the names of the current database and user.
only for RuBoard  do not distribute or recompile 