记录 MySQL的学习过程（9）sql扩展 元数据获取 information_schema show语句

sql扩展

元数据获取

元数据是存储在“基表”中

通过专用的ddl dcl语句进行修改
通过专用视图和命令进行元数据的查询
information_schema中保存了大量元数据查询的视图
show 命令是封装好的功能，提供元数据的基础查询功能

视图可以简单理解为将一个很复杂的sql语句封装成视图，在使用时可以很方便的调用

information_schema的基本应用

use information_schema 使用元数据所在的库

随后可以像在普通的库上操作一样，比如show tables；查看都有哪些视图

desc tables 查看视图的详细信息

比较重要的如下

TABLE_SCHEMA 表所在的库
TABLE_NAME 表名
ENGINE 存储引擎
TABLE_ROWS 数据行
AVG_ROW_LENGTH 平均行长度
INDEX_LENGTH 索引长度

1.假如我想查询整个库里面的所有库和表的信息

SELECT table_schema,TABLE_NAME FROM information_schema.tables;

...

| school | course |
| school | sc |
| school | student |
| school | teacher |
| wordpress | stu |
| wordpress | test |
| world | City |
| world | Country |
| world | CountryLanguage |
+--------------------+----------------------------------------------------+

2.换一种显示的方法

SELECT table_schema,group_concat(TABLE_NAME) FROM information_schema.tables group by table_schema ;

3.查询所有存储引擎为innodb的表

SELECT table_schema,TABLE_NAME,engine FROM information_schema.tables where engine='innodb';
+--------------+----------------------+--------+
| table_schema | TABLE_NAME | engine |
+--------------+----------------------+--------+
| mysql | innodb_index_stats | InnoDB |
| mysql | innodb_table_stats | InnoDB |
| mysql | slave_master_info | InnoDB |
| mysql | slave_relay_log_info | InnoDB |
| mysql | slave_worker_info | InnoDB |
| school | course | InnoDB |
| school | sc | InnoDB |
| school | student | InnoDB |
| school | teacher | InnoDB |
| wordpress | stu | InnoDB |
| wordpress | test | InnoDB |
+--------------+----------------------+--------+

4.统计world表下的city表占用的空间大小（KB）

公式如下
表的数据量 = 平均行长度*行数+索引长度

AVG_ROW_LENGTH * TABLE_ROWS + INDEX_LENGTH

select table_name,(avg_row_length*table_rows+index_length)/1024 from information_schema.tables where
table_schema='world' and table_name='City';
+------------+-----------------------------------------------+
| table_name | (avg_row_length*table_rows+index_length)/1024 |
+------------+-----------------------------------------------+
| City | 308.8877 |
+------------+-----------------------------------------------+

5.统计world库的数据量的总大小（KB）

select table_schema,sum((avg_row_length*table_rows+index_length))/1024 from information_schema.tables where
table_schema='world';
+--------------+----------------------------------------------------+
| table_schema | sum((avg_row_length*table_rows+index_length))/1024 |
+--------------+----------------------------------------------------+
| world | 435.2813 |
+--------------+----------------------------------------------------+

6.统计每个库的数据量大小，并按照数据量从大到小排序

select table_schema,sum((avg_row_length*table_rows+index_length))/1024 as total_KB from
information_schema.tables group by table_schema order by total_KB desc;
+--------------------+----------+
| table_schema | total_KB |
+--------------------+----------+
| mysql | 931.6602 |
| world | 435.2813 |
| school | 63.9893 |
| wordpress | 31.9961 |
| sccs | 15.2109 |
| performance_schema | 0.0000 |
| information_schema | NULL |
+--------------------+----------+

7.配合 concat 拼接语句或者命令

a.模仿以下语句，进行数据库的分库分表备份

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

select concat("mysqldump -uroot -p123 ",table_schema," ",table_name,"
>/bak/",table_schema,"_",table_name,".sql") from information_schema.tables;

...
|mysqldump -uroot -p123 wordpress stu >/bak/wordpress_stu.sql
|
| mysqldump -uroot -p123 wordpress test >/bak/wordpress_test.sql
|
| mysqldump -uroot -p123 world City >/bak/world_City.sql
|
| mysqldump -uroot -p123 world Country >/bak/world_Country.sql
|
| mysqldump -uroot -p123 world CountryLanguage >/bak/world_CountryLanguage.sql

...
实际上相当于执行了以下的命令，只不过在这个过程中又拼接了一些字符串进去

select table_schema,table_name from information_schema.tables;

b.模仿以下语句，进行批量生成对world库下所有表进行操作

 

alter table world.city discard tablespace

select concat("alter table ",table_schema,".",table_name," discard tablespace;") from
information_schema.tables;

 

show 语句

show databases; #查看所有数据库
show tables; #查看当前库的所有表
SHOW TABLES FROM #查看某个指定库下的表
show create database xxx #查看建库语句
show create table xxx.xxx #查看建表语句
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