sql tips

# for quick debugging

instead of connecting first to the mysql server and then see the result of your query. We can do it in one command using the 'e' flag.

mysql -uUSER -pPASSWORD -D DATABASE -e "select * from your_table;"

you can also remove the default table formatting provided by mysql.

mysql -uUSER -pPASSWORD -D DATABASE -s -e "select * from your_table;"

#return limited rows from a result

MySQL provides a keyword 'limit' to achieve this task. Lets say you want to retrieve first 10 records from a query.

select * from your_table limit 10;

you can also specify the 'start' and 'end' of this result. Let's say you want to retrieve 10 records after first 10 records. This is a useful feature especially for pagination.

select * from your_table limit 11, 20;

http://www.petefreitag.com/item/59.cfm

#for oracle

Oracle provides a keyword 'rownum' that behaves exactly same as 'limit' in mysql.

select name, price

from items

where rownum > 5 and

rownum < 11;

http://www.adp-gmbh.ch/ora/sql/analytical/dense_rank_vs_rank_vs_row_number.html