Query of table space and indexes in the mysql table structure

Mysql table structure table space and index query 1. query table structure information SQL code desc tableName; show columns from tableName; describe tableName the results returned above are the same. 2. query the column information of the table. SQL code select * from information_schema.columns where table_name = 'tablename'; 3. view all the database SQL code SELECT LOWER (schema_name) schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('mysql ', 'Information _ scheme', 'test') 4. query all the table SQL codes in a database: SELECT table_name, create_time updated_at, table_type, ENGINE, table_rows num_rows, table_comment, CEIL (data_length/1024/1024) store_capacity FROM information_schema.TABLES WHERE table_schema = 'schema _ name' AND table_name not like 'tmp # _ % 'escape '#'5. view the SQL code SELECT for all fields of a table in a database lower (column_name) column_name, ordinal_position position, column_default dafault_value, substring (is_nullable, 1, 1) nullable, column_type data_type, column_comment, comment data_length, numeric_precision data_precision, numeric_scale data_scale FROM information_schema.COLUMNS WHERE table_schema = 'admin _ portal 'AND table_name = 'ap _ epiboly_task '; 6. view the index SQL code of a table in a database <strong> SELECT DISTINCT lower (index_name) index_name, lower (index_type) type FROM information_schema.statistics WHERE table_schema = 'ployees' AND table_name = 'ployees '; </strong> 7. view the SQL code SELECT table_comment comments FROM information_schema.TABLES WHERE table_schema = 'ployees' AND table_name = 'ployees' of a table under a database; 8 1. view indexes (1) in the unit of gb select concat (ROUND (SUM (index_length)/(1024*1024*1024), 2), 'gb ') AS 'total Index size' FROM information_schema.TABLES WHERE table_schema LIKE 'test'; + ------------------ + | Total Index Size | + ------------------ + | 1.70 GB | + ------------------ + (2) unit: mb select concat (ROUND (SUM (index_length)/(1024*1024), 2), 'mb ') AS 'total Index size' FROM information_schema.TABLES WHERE table_schema LIKE 'test'; WHERE "database" is the database you want to view 2. view the tablespace select concat (ROUND (SUM (data_length)/(1024*1024*1024), 2), 'gb ') AS 'total Data size' FROM information_schema.TABLES WHERE table_schema LIKE 'test'; + ----------------- + | Total Data Size | + ----------------- + | 3.01 GB | + ----------------- + 3. view information about all tables in the database. select concat (table_schema ,'. ', table_name) AS 'table name', CONCAT (ROUND (table_rows/Rows), 'M') AS 'number of Rows ', CONCAT (ROUND (data_length/(1024*1024*1024), 2), 'G') AS 'data size ', CONCAT (ROUND (index_length/(1024*1024*1024), 2), 'G') AS 'index size', CONCAT (ROUND (data_length + index_length) /(1024*1024*1024), 2), 'G') AS 'Total' FROM information_schema.TABLES WHERE table_schema LIKE 'test ';