MySQL useful functions

#To know the current database used
select database();

#To know the version of the database server
select version();

#To know the current logged in user
select user();

#To diff two dates in days,years,etc.
TimeStampDiff(YEAR, date1, date2);
   Param1- const, like YEAR,MONTH,DAY,HOUR,etc
   param2 & 3 - actual dates

#To fetch the year part of a date
select year(<date>);

#To fetch the month part of a date
select month(<date>);

#To fetch the day part of date
select dayofmonth(<date>);

#To get the mod i.e. 5mod3
select mod(5,3);

# Ifnull
select ifnull(exp1,exp2) - this will output exp1 if it's true or else exp2
select ifnull(null,10) - will return 10


# _ represents a character
To find names containing exactly five characters, use five instances of the “_” pattern character:
 SELECT * FROM pet WHERE name LIKE '_____';

# regular expressions

regexp 'w' -> containing w
regexp '^b' -> starting with b
regexp 'fy$' -> ending with fy
regexp '^…..$' -> containing 5 letters
regexp '^.{5}$' -> containing 5 letters

“.” matches any single character.

A character class “[...]” matches any character within the brackets. For example, “[abc]” matches “a”, “b”, or “c”. To name a range of characters, use a dash. “[a-z]” matches any letter, whereas “[0-9]” matches any digit.

“*” matches zero or more instances of the thing preceding it. For example, “x*” matches any number of “x” characters, “[0-9]*” matches any number of digits, and “.*” matches any number of anything.

A REGEXP pattern match succeeds if the pattern matches anywhere in the value being tested. (This differs from a LIKE pattern match, which succeeds only if the pattern matches the entire value.)

To anchor a pattern so that it must match the beginning or end of the value being tested, use “^” at the beginning or “$” at the end of the pattern

# To fetch total records from all tables of a database.
mysqlshow --count databasename -uroot -p


#User defined variable
mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;


To be continued..