MySQL-07-information

information_schema.tables视图

DESC information_schema.TABLES

/**

TABLE_SCHEMA    ---->库名

TABLE_NAME      ---->表名

ENGINE          ---->引擎

TABLE_ROWS      ---->表的行数

AVG_ROW_LENGTH  ---->表中行的平均行（字节）

INDEX_LENGTH    ---->索引的占用空间大小（字节）

**/

-- 查询整个数据库中所有库和所对应的表信息

SELECT table_schema,GROUP_CONCAT(table_name)

FROM  information_schema.tables

GROUP BY table_schema;

-- 统计所有库下的表个数

SELECT table_schema,COUNT(table_name)

FROM information_schema.TABLES

GROUP BY table_schema

-- 查询所有innodb引擎的表及所在的库

SELECT table_schema,table_name,ENGINE FROM information_schema.`TABLES`

WHERE ENGINE='innodb';

-- 统计world数据库下每张表的磁盘空间占用

SELECT table_name,CONCAT((TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024," KB")  AS size_KB

FROM information_schema.tables WHERE TABLE_SCHEMA='world';

-- 统计所有数据库的总的磁盘空间占用

SELECT

TABLE_SCHEMA,

CONCAT(SUM(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024," KB") AS Total_KB

FROM information_schema.tables

GROUP BY table_schema;

mysql -uroot -p123 -e "SELECT TABLE_SCHEMA,CONCAT(SUM(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024,' KB') AS Total_KB FROM information_schema.tables GROUP BY table_schema;"

-- 生成整个数据库下的所有表的单独备份语句

-- 模板语句：

mysqldump -uroot -p123 world city >/tmp/world_city.sql

SELECT CONCAT("mysqldump -uroot -p123",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql" )

FROM information_schema.tables

WHERE table_schema NOT IN('information_schema','performance_schema','sys')

INTO OUTFILE '/tmp/bak.sh' ;

CONCAT("mysqldump -uroot -p123 ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql" )

-- 107张表，都需要执行以下2条语句

ALTER TABLE world.city DISCARD TABLESPACE;

ALTER TABLE world.city IMPORT TABLESPACE;

SELECT CONCAT("alter table ",table_schema,".",table_name," discard tablespace")

FROM information_schema.tables

WHERE table_schema='world'

INTO OUTFILE '/tmp/dis.sql';

show 命令

show  databases;                          #查看所有数据库

show tables;                              #查看当前库的所有表

SHOW TABLES FROM                        	#查看某个指定库下的表

show create database world                #查看建库语句

show create table world.city              #查看建表语句

show  grants for  root@'localhost'       	#查看用户的权限信息

show  charset                             #查看字符集

show collation                            #查看校对规则

show processlist;                         #查看数据库连接情况

show index from                           #表的索引情况

show status                               #数据库状态查看

SHOW STATUS LIKE '%lock%'                 #模糊查询数据库某些状态

SHOW VARIABLES                            #查看所有配置信息

SHOW variables LIKE '%lock%';             #查看部分配置信息

show engines                              #查看支持的所有的存储引擎

show engine innodb status\G               #查看InnoDB引擎相关的状态信息

show binary logs                          #列举所有的二进制日志

show master status                        #查看数据库的日志位置信息

show binlog evnets in                     #查看二进制日志事件

show slave status \G                      #查看从库状态

SHOW RELAYLOG EVENTS               		    #查看从库relaylog事件信息

desc  (show colums from city)             #查看表的列定义信息

http://dev.mysql.com/doc/refman/5.7/en/show.html

巴特西

MySQL-07-information_schema/show

information_schema.tables视图

show 命令

最新文章

热门文章