Web Database Applications with PHP & MySQL, 2nd Edition

only for RuBoard - do not distribute or recompile

3.9 Functions

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.

3.9.1 Arithmetic and comparison operators

Table 3-2 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.

Table 3-2. Using the arithmetic and comparison operators

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 3-2. 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. Less-than-or-equal-to is represented by <=, and greater-than-or-equal-to is represented by >=. Parentheses can explicitly express the evaluation order.

3.9.1.1 String-comparison operators and functions

Table 3-3 shows examples of the MySQL string-comparison operators and functions. Many of the MySQL string functions shown here are similar to PHP functions, which were introduced in Chapter 2.

Table 3-3. Using string comparison functions and operators

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:

While not detailed in Table 3-3, regular expressions can be used through the function regexp( ). For more on regular expressions in PHP, see Chapter 2.

3.9.1.2 Mathematical functions

We make little use of the mathematical functions provided by MySQL in this book. However, Table 3-4 shows selected MySQL mathematical functions and their output.

Table 3-4. Using the MySQL mathematical functions

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 3-4 require some explanation:

3.9.1.3 Date and time functions

Table 3-5 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.

Table 3-5. Using the date and time functions

Statement

Output

SELECT dayofweek('2000-05-03');

3

SELECT dayname('2000-05-03');

Wednesday

SELECT monthname('2000-05-03');

May

SELECT week('2000-05-03');

18

SELECT date_add("2000-05-03", INTERVAL 1 DAY);

2000-05-04

SELECT curdate( );

2002-01-01

SELECT curtime( );

11:27:20

SELECT now( );

2002-01-01 11:27:20

3.9.1.4 Miscellaneous operators and functions

Miscellaneous operators and functions are shown in Table 3-6.

Table 3-6. Miscellaneous operators and functions

Statement

Output

Control flow functions

SELECT if(1<0,"yes","no")

no

Encryption functions

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
Related

Категории