常用查询数据库及表的信息

###1 查询所有数据库
SELECT SCHEMA_NAME AS `Database` FROM INFORMATION_SCHEMA.SCHEMATA;

###2 查询库下所有表
# test为库名
SELECT table_name, table_type FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'test';

###3 查询某个数据库下有多少张表
--在information_schema数据库中查询
gbase> select table_name from tables where table_schema = 'mydb';

###4 表倾斜率查询
select * from information_schema.CLUSTER_TABLE_SEGMENTS where TABLE_SCHEMA='test' and TABLE_NAME='t1';

###5 库下所有表分布情况查询
输出所有表
gccli -ugbase -pxxx -e"SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'test';" |grep -v "\+\|table_name" >/tmp/tables_`date +%F`

#! /bin/sh

database_name='test'

for table in `cat /tmp/tables_$(date +%F)`
do
   gccli -ugbase -pxxx -e "use vc vc1;select * from information_schema.CLUSTER_TABLE_SEGMENTS where TABLE_SCHEMA='${database_name}' and TABLE_NAME='${table}';" >> /tmp/tables_$(date +%F).info
done

###6 表占用空间大小统计
例如：gcluseterdb.audit_log_express
select * from information_schema.cluster_tables where table_schema = 'gclusterdb' and table_name = 'audit_log_express' ;
select min(start_time) from gclusterdb.audit_log_express ;
通过 information_schema.cluster_tables 表的 TABLE_DATA_SIZE 和 TABLE_STORAGE_SIZE 字段进行统计。
TABLE_DATA_SIZE 是数据存储空间（主备分片之和）；
TABLE_STORAGE_SIZE 是数据存储空间 + 元数据存储空间（主备分片之和）