2024年10月02日 建站教程
mysql语法存在哪些查询方法，下面web建站小编给大家详细介绍一下各种查询方法！
查看库的大小，剩余空间的大小
select table_schema,round((sum(data_length / 1024 / 1024) + sum(index_length / 1024 / 1024)),2)
dbsize,round(sum(DATA_FREE / 1024 / 1024),2) freesize,
round((sum(data_length / 1024 / 1024) + sum(index_length / 1024 / 1024) +
sum(DATA_FREE / 1024 / 1024)),2) spsize from information_schema.tables
where table_schema not in ('mysql','information_schema','performance_schema')
group by table_schema order by freesize desc;
查询正在运行中的事务
select p.id,p.user,p.host,p.db,p.command,p.time,i.trx_state,i.trx_started,p.info from information_schema.processlist p,information_schema.innodb_trx i where p.id=i.trx_mysql_thread_id;
查看库中所有表的大小
select table_name,concat(round(sum(DATA_LENGTH/1024/1024),2),'M') from information_schema.tables where table_schema='t1' group by table_name;
查看库中有索引的表
select TABLE_SCHEMA,TABLE_NAME,group_concat(INDEX_NAME) from information_schema.STATISTICS where
TABLE_SCHEMA not in ('sys','mysql','information_schema','performance_schema')
group by TABLE_NAME ;
查看库中没有索引的表
select TABLE_SCHEMA,TABLE_NAME from information_schema.tables
where TABLE_NAME not in (select distinct(any_value(TABLE_NAME))
from information_schema.STATISTICS group by INDEX_NAME)
and TABLE_SCHEMA not in ('sys','mysql','information_schema','performance_schema');
本文链接：http://so.lmcjl.com/news/14424/