How to get database structure in MySQL via query

To get the whole database structure as a set of CREATE TABLE statements, use mysqldump:

mysqldump database_name --compact --no-data

For single tables, add the table name after db name in mysqldump.

 You get the same results with SQL and SHOW CREATE TABLE, 

SHOW CREATE TABLE table;

 To read from schema. 

SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME ='products';  

other commands which may be helpful 

1. Login to Mysql, 

-> mysql -u [username] -p 


2. To see all databases inside mysql, 

mysql -> show databases;  

3. To go inside a particular database, 

mysql -> use [database_name];

4. see all tables inside database,
 
mysql -> SHOW TABLES;

5. Just to look at details of column listing,

mysql -> DESCRIBE table;