Mysql Tables
Each database will contain one or more tables once an application starts storing data in it. The application should usually be what creates tables and modifies them.
List tables
Use the “show tables” command to list the tables in a database:
SHOW TABLES FROM databasename;
Count the rows in a table
A simple query to count the number of rows (entries) in a table would look like:
SELECT COUNT(*) FROM databasename.tablename;
Show all data in a table
To list absolutely every entry in a table, run:
SELECT * FROM databasename.tablename;
Note that this will usually be a huge result. You can list just the fields you want to view from each entry by listing them in place of “*” above, separating them with commas.
How to re-enable the grant table with mysql
root@dell:~# mysqladmin flush-privileges -uroot -p
When you create accounts with INSERT, it is necessary to use FLUSH PRIVILEGES to tell the server to reload the grant tables. Otherwise, the changes go unnoticed until you restart the server. With CREATE USER, FLUSH PRIVILEGES is unnecessary.
As usual when you modify the grant tables directly, you must tell the server to reload them with FLUSH PRIVILEGES so that the privilege changes take effect.
Taking dump of multiple tables
The mysqldump can be used to dump a database or a collection of databases for backup or for transferring the data to another MySQL server.
Execute the following command to get the dump of a table.
$ mysqldump -uusername -p databasename tablename > dump_filename.sql
This table can be added to the database using the following command.
$ mysql -uusername -p databasename < dump_filename.sql
In order to take dump of N tables named table1, table2.table3….tableN use the following syntax:
$ mysqldump -uusername -p databasename table1 table2 table3 …. tableN > dump_filename.sql
