Miscellaneous operators and functions
Miscellaneous operators and functions are shown in following table.
MySQL Conditional function if
SELECT if(1<0,"yes","no");
The above statement output no.
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.
Select a MySQL database
The function database( ) provide the name of the current database.
SELECT database( )
The above statement displays the current DB name:
mydbname
Select a MySQL user
The function user( ) provide the name of the current user.
SELECT user( );
This statment shows the current username:
myuser@localhost
Authentication and securing data in MySQL
Authentication and securing data using password( ), encode( ), and decode( ) are discussed in detail in Tutorial 9.
password
SELECT password('secret');
Result:
428567f408994404
encode
SELECT encode('secret','shhh')
Result
"|ï ¨~
decode
SELECT decode('"|ï ¨~','shhh')
Result:
secret