Mysql查看表结构信息

查看所有的库
SELECT lower(schema_name) schema_name
  FROM information_schema.schemata
 WHERE schema_name NOT IN (
        'mysql',
        'information_schema',
        'test',
        'search',
        'tbsearch',
        'sbtest',
        'dev_ddl'
		)

查看某一个库中的所有表
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 = 'employees'
   AND table_name NOT LIKE 'tmp#_%' ESCAPE '#'

查看某一个库下某一个表的所有字段
SELECT lower(column_name) column_name,
       ordinal_position position,
       column_default dafault_value,
       substring(is_nullable, 1, 1) nullable,
       column_type data_type,
       column_comment,
       character_maximum_length data_length,
       numeric_precision data_precision,
       numeric_scale data_scale
  FROM information_schema.COLUMNS
 WHERE table_schema = 'employees'
   AND table_name = 'employees';

查看某一个库下某一张表的索引
SELECT DISTINCT
       lower(index_name) index_name,
       lower(index_type) type
  FROM information_schema.statistics
 WHERE table_schema = 'employees'
   AND table_name = 'employees';

查看某一个库下某一张表的某一个索引
SELECT lower(column_name) column_name,
       seq_in_index column_position
  FROM information_schema.statistics
 WHERE table_schema = 'employees'
   AND table_name = 'employees'
   AND index_name = 'primary';

查看某一个库下某一个表的注释
SELECT table_comment comments
  FROM information_schema.TABLES
 WHERE table_schema = 'employees'
   AND table_name = 'tableName';

查看某一个库下某一个表的列的注释
SELECT LOWER(column_name) column_name,  
       column_comment comments  
  FROM information_schema.columns
 WHERE table_schema ='databaseName'
   AND table_name = 'tableName';