- 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.
