mysql查询数据库表结构数据及持久化数据相关语句

查询数据库的数据表，数据量，数据库字段等信息的相关语句

查询数据表

#查询MySQL服务中数据库表数据量
SELECT COUNT(*) TABLES, table_schema FROM information_schema.TABLES    GROUP BY table_schema;

 #查询指定数据库表数量
SELECT COUNT(*) TABLES, table_schema FROM information_schema.TABLES   WHERE table_schema = 'my_schema'

查询字段

#查询一个表中有多少字段
SELECT COUNT(*) FROM information_schema. COLUMNS WHERE table_schema = 'my_schema' AND table_name = 'SystemLog';

 #查询一个数据库中有多少字段
SELECT COUNT(column_name) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'my_schema';

#查询数据库中所以表、字段、字段类型、注释等信息
SELECT TABLE_NAME, column_name, DATA_TYPE, column_comment FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'my_schema' ;

查询数据量

#统计数据库中每个表保存的数据量
use information_schema;
SELECT TABLE_NAME, (DATA_LENGTH/1024/1024) as DataM ,(INDEX_LENGTH/1024/1024) as IndexM,((DATA_LENGTH+INDEX_LENGTH)/1024/1024) as AllM,TABLE_ROWS FROM TABLES WHERE TABLE_SCHEMA = 'my_schema'

#查询每张表数量
select table_name,table_rows from tables where TABLE_SCHEMA = 'my_schema' order by table_rows desc;

#数据库总数量
SELECT sum(table_rows) from tables where TABLE_SCHEMA = 'my_schema' order by table_rows desc;