统计mysql数据库中数据表/字段等数量

1、统计数据库一共多少张表

SELECT
	COUNT(*) TABLES,
	table_schema
FROM
	information_schema. TABLES
WHERE
	table_schema = 'test'
GROUP BY
	table_schema;

执行结果展示:

2、统计某一张表的字段数量

SELECT
	COUNT(*)
FROM
	information_schema. COLUMNS
WHERE
	table_schema = 'test'
AND table_name = 'application';

执行之后展示结果：

3、统计数据库一共多少个字段

SELECT
	COUNT(column_name)
FROM
	information_schema. COLUMNS
WHERE
	TABLE_SCHEMA = 'test';

执行之后结果展示：

4、查询一个数据库中的所有表和所有字段、字段类型及注释等信息

SELECT
	TABLE_NAME,
	column_name,
	DATA_TYPE,
	column_comment
FROM
	information_schema. COLUMNS
WHERE
	TABLE_SCHEMA = 'test';

执行之后结果展示：

test:为你的数据库名称

5、查询所有表字段和注释-mysql

 select column_name columnname, data_type datatype, column_comment columncomment from information_schema.columns where table_name = 'application';

 

 

6、查询所有库表名称-mysql

 select table_name tablename from information_schema.tables where table_schema=(select database()) order by table_name;

 

7、统计数据库所有数据记录

select * from (select TABLE_NAME, concat(truncate(data_length/1024/1024,2),' MB') as data_size,table_rows
from information_schema.tables where TABLE_SCHEMA = 'test' ORDER BY  table_rows) t ;




select *,SUM(t.table_rows) as allcount from (select TABLE_NAME, concat(truncate(data_length/1024/1024,2),' MB') as data_size,table_rows
from information_schema.tables where TABLE_SCHEMA = 'test' ORDER BY  table_rows) t ;