DevOps开发运维
成长之路

MySQL的DQL应用-information_schema.tables视图

概念

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

元数据概念

一张表有两部分组成,元数据和数据行,每次数据库启动时,会自动在内存中生成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';
赞(0)

评论 1

评论前必须登录!

 

  1. #1

    太好了

    知识付费8个月前 (10-14)

LNMP社群 不仅仅是技术

关于我们网站地图