Common Functions and Expressions

Lets now peep into common function in MySQL and probably when they are uses.

Comparison Operators

Operator	Operation	Usage	Comments
=	Equal	a=b	true if equal, else false
!= or <>	Not equal	a!=b or a<>b	true if not equal
<	Less than
<=	Less than or equal
>	Greater than
>=	Greater than or equal
IN	equal to any one in list	a in (1,2,3)	true if a=1 or a=2 or a=3
LIKE	string matching	matches the pattern	strings starting with C followed by a character, then d followed by any number of characters
BETWEEN … AND	searches in a range	a between 5 and 10

More if interested at : http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html

Logical Operations

Operator	Operation	Usage
AND , &&	Logical AND two expressions	1=2 AND 5>6
OR , ||	Logical OR
NOT, !	Negative check	NOT a and b !c
XOR	Logical XOR

Control Functions

if - if(expr_to_check, true_expr, false_expr)

eg: 

if (5 > 3, "yes", "no")  => returns "yes"

ifnull - ifnull(expr_to_check, expr_if_first_expr_empty)

eg:

if(5, 10) => returns 5

if(5 and NULL, "was null") => returns "was null" (5 AND NULL evaluates to null)

case .. when

This is similar to switch..case in programming languages

eg:

case 1+3

when 4-2 then "4-2"

when 6-2 then "6-2"

when 4 then "4"

else "else"

end  

 => return "6-2" (it wont go to case 4 as, case 6-2 already matched)

Common Functions

concat 

eg: concat( "hello", " ", "world") => returns "hello world"

trim, rtrim, ltrim - Trim white spaces in a string

eg: trim(" a ") 

date_format 

eg: date_format(now(), '%Y-%m-%d') => returns "2013-01-31"

adddate / subdate 

eg: adddate('2012-01-20', interval 5 day) => return '2012-01-25 00:00:00' 

interval is a keyword followed by no:of units and unit to operate

cast/convert - for type casting

eg: cast("124as" as unsigned) / convert("124as", unsigned) => returns 124

You might have noticed by now various default MySQL type casting happening here.

Some Tips

• If you are having some indexes, to use the advantage of it, do not use it with function.  For example convert(id as char)= "10" will not make use of the index on id
• While using like, if search pattern starts with %, the index could not  be used.
• concat(col1, col2) returns null if either one of them is null
• MySQL does internal type casting, but could be touch slower(as indexes might not be utilized) if types does not match 

By now we have seen just the basics of running select queries, modifying data and some basic DB schema design. Its time to move to phase 2 where we will get deeper into everything we have seen so far. 

Keep reading :)