I execute below command line statement to optimize a table:
optimize table tablename;
Is there any command or statement which can optimize all the tables one by one of the selected database?
I usually use mysqlcheck with --optimize and --databases combination. mysqlcheck is a command-line interface for administrators to check, optimize and repair tables.
Here is the MySQL doc : http://dev.mysql.com/doc/refman/5.5/en/mysqlcheck.html
You can use information schema to generate the statements you need:
You could script it and place all tables on a single line
ALL TABLES IN THE CURRENT DATABASE
ALL TABLES IN ALL USER DATABASES
GIVE IT A TRY !!!