元数据
元数据是存储在‘基表’中。
通过专用的ddl语句，dcl语句进行修改
通过专用的视图和命令进行元数据的查询
information_schema中保存了大量的元数据查询的视图
show 命令是封装好的 提供原数据查询的基础功能
information_schema.tables视图
创建视图
create view aa as 后面接查询语句
调用视图
select * from aa
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.tablesGROUP BY table_schema;
统计所有库下的表个数
SELECT table_schema,COUNT(table_name)FROM information_schema.TABLESGROUP BY table_schema
查询所有innodb引擎的表及所在的库
SELECT table_schema,table_name,ENGINE FROM information_schema.`TABLES`WHERE ENGINE='innodb';
统计world数据库下每张表的磁盘空间占用
SELECTtable_name,CONCAT((TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024," KB")ASsize_KBFROMinformation_schema.tablesWHERETABLE_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.sqlSELECTCONCAT("mysqldump -uroot -p123 ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql")FROMinformation_schema.tablesWHEREtable_schemaNOTIN('information_schema','performance_schema','sys')INTOOUTFILE'/tmp/bak.sh';CONCAT("mysqldump -uroot -p123 ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql")
107张表，都需要执行以下2条语句
ALTERTABLEworld.cityDISCARDTABLESPACE;ALTERTABLEworld.cityIMPORTTABLESPACE;SELECTCONCAT("alter table ",table_schema,".",table_name," discard tablespace")FROMinformation_schema.tablesWHEREtable_schema='world'INTOOUTFILE'/tmp/dis.sql';
show 命令
show databases;#查看所有数据库show tables;#查看当前库的所有表SHOWTABLESFROM #查看某个指定库下的表show create database world #查看建库语句show create table world.city #查看建表语句show grantsforroot@'localhost'#查看用户的权限信息show charset； #查看字符集show collation #查看校对规则show processlist;#查看数据库连接情况show indexfrom#表的索引情况show status #数据库状态查看SHOWSTATUSLIKE'%lock%';#模糊查询数据库某些状态SHOWVARIABLES #查看所有配置信息SHOWvariables LIKE'%lock%';#查看部分配置信息show engines #查看支持的所有的存储引擎show engine innodb status\G #查看InnoDB引擎相关的状态信息show binary logs #列举所有的二进制日志show master status #查看数据库的日志位置信息show binlog evnetsin#查看二进制日志事件show slave status \G #查看从库状态SHOWRELAYLOGEVENTS #查看从库relaylog事件信息desc(show columsfromcity)#查看表的列定义信息http://dev.mysql.com/doc/refman/5.7/en/show.html
