Mysql Tricks

• How to get current time

select now();
select curdate();
select curtime();

• How to get date in standard format

select date from table;

will give answer as "2011-08-15"
But

select date+0 from table;

will give answer as "20110815"

• To use group by with a summary.

select col from .... group by col with rollup;

This will give a summary(addition) in the last row.

• When performing string operations

Shell "*" => mysql "%"
Shell "?" => mysql "_"

• Conditional statements in mysql

select (sum(case when shares>0 then shares else - end) from table;

• Case-sensetive comparison

select 'A' like 'a';
1
select 'A' like binary 'a';
0

• Using variables

set @total=0;select (@total := 2+@total) as total from table;

• Checking table sizes

select table_schema "Database name", sum(data_length _ index_length)/1024/1024 "size", sum (data_free)/1024/1024 "Free space " from information_schema.TABLES group by table_schema;

• Command line execution and output
  

bash# mysql -e "select * from table";
mysql> select * into outfile "/tmp/hpatil" from ...;

But watchout for mysqld host and users when searching for outfile on commandline.