05-show 语句的列表介绍

1.show 简单语句

查看所有的库
show databases;
查看当前库下的所有表
show tables;
show tables from world;
查看当前并发会话信息
show processlist;
show full processlist;
查看数据库支持的权限
show privileges;
查看数据库参数信息
show variables
show variables like '%trx%';
查看字符集&校对规则
show charset;
show collation;
查看建库&建表语句
show create database world;
show create table world.city;
查看用户权限
show grants for root@'localhost';
查看支持的存储引擎
show engines;
查询表中索引信息
show index from world.city;
查看数据库当前状态信息
show status;
show status like '%lock%';
查看InnoDB引擎相关的状态信息(内存,事务,锁,线程...)
show engine innodb status\G
查看二进制日志相关信息
show binary logs ;
show master status;
show binlog events in 'xxxx';
查看主从复制相关信息
show relaylog events in 'xxxx';
show slave status \G
查看帮助
help show ;

2.Information_schema 统计信息库

3.视图

1.介绍

安全 : 只允许查询,不知道操作的是什么对象.
方便 : 只需要简单的select语句即可使用

2.作用

方便做数据库资产统计
库\表 :
个数
数据量(大小,行数)
每张表的数据字典信息
获取到Server层状态信息
获取到InnoDB引擎层的状态信息

3.应用举例

TABLES : 
TABLE_SCHEMA  : 表所在的库 
TABLE_NAME    : 表名
ENGINE        : 表的引擎      
TABLE_ROWS    : 表的行数
AVG_ROW_LENGTH: 平均行长度(字节) 
INDEX_LENGTH  : 索引占用长度(字节) 
TABLE_COMMENT : 表注释

例子

简单查询体验TABLES信息
SELECT * FROM TABLES;
所有业务库和表的名字.

SELECT table_schema , table_name 
FROM information_schema.tables
WHERE table_schema NOT IN ('sys','information_schema','performance_schema','mysql');

统计每个业务库,表的个数和列表

SELECT table_schema , COUNT(table_name),GROUP_CONCAT(table_name) 
FROM information_schema.tables
WHERE table_schema NOT IN ('sys','information_schema','performance_schema','mysql')
GROUP BY table_schema;

统计业务数据库的总数据量

SELECT SUM(table_rows * AVG_ROW_LENGTH+index_length)/1024 AS total_KB
FROM information_schema.tables
WHERE table_schema NOT IN ('sys','information_schema','performance_schema','mysql');

每个业务库分别统计数据量

SELECT table_schema,SUM(table_rows * AVG_ROW_LENGTH+index_length)/1024 AS total_KB
FROM information_schema.tables
WHERE table_schema NOT IN ('sys','information_schema','performance_schema','mysql')
GROUP BY table_schema
ORDER  BY total_KB DESC ;

top 3 数据量大的表

SELECT table_schema,table_name,(table_rows * AVG_ROW_LENGTH+index_length)/1024 AS table_kb
FROM information_schema.tables
WHERE table_schema NOT IN ('sys','information_schema','performance_schema','mysql')
ORDER BY  table_kb DESC 
LIMIT 3;

查询所有非INNODB的表

SELECT table_schema,table_name ,ENGINE FROM information_schema.tables
WHERE table_schema NOT IN ('sys','information_schema','performance_schema','mysql')
AND ENGINE <> 'innodb';

查询所有非INNODB的表 , 并且提出修改建议

SELECT 
table_schema,
table_name ,
ENGINE ,
CONCAT("alter table ",table_schema,".",table_name," engine=innodb;") AS "修改建议"
FROM information_schema.tables
WHERE table_schema NOT IN ('sys','information_schema','performance_schema','mysql')
AND ENGINE <> 'innodb';

所有业务库和表的名字,并且生成备份语句

SELECT 
table_schema , 
table_name ,
CONCAT("mysqldump ",table_schema," ",table_name," > /bak/",table_schema,"_",table_name,".sql") AS "备份"
FROM information_schema.tables
WHERE table_schema NOT IN ('sys','information_schema','performance_schema','mysql');

最后编辑于：2019.12.25 08:29:16