mysql语法中有哪些查询方法

mysql语法存在哪些查询方法，下面编程教程网小编给大家详细介绍一下各种查询方法！

查看库的大小，剩余空间的大小

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');