1.清空用户下的所有表里面的数据
select concat('truncate table ',table_name,';') from information_schema.TABLES where table_schema='test_cp';
2.统计某个库下面的数据总量和表数目和总条目数
SELECT count(*) TABLES, concat(round(sum(DATA_LENGTH/1024/1024),2),'M'),table_schema ,sum(table_rows)FROM information_schema.TABLES t where t.TABLE_SCHEMA='XX';
3.查询每张表的条目数
SELECT table_name,table_rows,concat(round(DATA_LENGTH/1024/1024,2),'M') FROM information_schema.TABLES t where t.TABLE_SCHEMA='dwjy';
4.语句拼接
select CONCAT("insert into tt(uname,tname,records,spaces,systemName) select `用户名` ,`表名`,`记录数`,`占用空间（M）` ,\"",t.TABLE_NAME,"\" from `",t.TABLE_NAME,"`;") from tables t where t.TABLE_SCHEMA='dashuju'；
select CONCAT("select count(1),\"",t.TABLE_NAME,"\" from `",t.TABLE_NAME,"` union all") from `TABLES` t where t.table_schema='yu';
select CONCAT("insert into tt(tname,records,systemName) select `表名称（英文）`,`记录数`,`系统名称`, from `",t.TABLE_NAME,"`;") from tables t where t.TABLE_SCHEMA='yu';
