MySQL的DQL应用-information_schema.tables视图

概念

information_schema视图库下的tables视图放置整个数据库里面所有表的元数据
use information_schema;
show tables;查看information_schema下的所有表
desc tables;=desc information_schema.tables;查看information_schema视图库的tables表

元数据概念

一张表有两部分组成，元数据和数据行，每次数据库启动时，会自动在内存中生成information_schema，生成查询MySQL部分元数据信息视图，
information_schema视图保存是查询元数据的方法，MySQL是有三个视图库，information_schema、
performance_schema(performance是information_schema复杂功能的封装)、sys。
MySQL提供DDL和DCL来进行对元数据进行修改，提供了information_schma和show的语句查询元数据。

负责语句转换保存为视图查询

可以把经常用到的查询语句保存到视图中，方便操作，视图不是表，不保存任何数据，只是查询方法，可以查询元数据，可以有效的减少代码量。
语法格式为：create view 名称 as 语句
举例：
create view bjg as
select teacher.tname,student.sname,sc.score
from teacher join course
on teacher.tno=course.tno
join sc
on course.cno=sc.cno
join student
on sc.sno=student.sno
where sc.score<60;
查询语句：select * from bjg;

找到系统中任意一张表的属性

DESC information_schema.tables;
TABLE_SCHEMA ---->库名
TABLE_NAME ---->表名
ENGINE ---->引擎
TABLE_ROWS ---->表的行数
AVG_ROW_LENGTH ---->表中行的平均行（字节）
INDEX_LENGTH ---->索引的占用空间大小（字节）
select TABLE_SCHEMA,TABLE_NAME,ENGINE,TABLE_ROWS,AVG_ROW_LENGTH,INDEX_LENGTH from information_schema.tables;

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

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数据库下每张表的磁盘空间占用(kb表示)

select table_schema,table_name,(table_rows*avg_row_length+index_length)/1024 as szi_kb
from information_schema.tables
where table_schema='world';

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

select sum(table_rows*avg_row_length+index_length)/1024 as szi_kb
from information_schema.tables;

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

格式：
mysqldump -uroot -p123 world city >/tmp/world_city.sql
select concat("mysqldump -uroot -p123456 ",table_schema," ",table_name," >/bak/",table_schema,"_",table_name,".sql")
from information_schema.tables
where table_schema='world'
into outfile '/tmp/b.sh';
注意：在mysql的配置文件my.cnf中mysqld下加入secure-file-priv=/tmp,这是mysql5.6之后加入的安全策略，不允许把secure数据导入到系统的文件里。

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

格式：
alter table world.city discard tablespace;
select concat("alter table ",table_schema,".",table_name," discard tablespace")from information_schema.tables
where table_schema='world'
into outfile '/tmp/discard.sh';