十、information_schema.tables基础应用

元数据获取

除了数据行其他的类属性表属性都属于元数据，存放在InnoDB引擎的Inode中，一个Inode就是代表一个数据段。.
元素据是被保护起来的，是无法被直接操作的,但可以通过专用的视图以及命令对元数据进行查询。

参考资料: mysql体系结构

什么是视图

在这里，视频中视图相当于封装了一段sql语句，方便以后查询。

mysql> select teacher.tname,course.cname,student.sname,score.score
    -> from student
    -> join score
    -> on student.sno=score.sno
    -> join course
    -> on score.cno=course.cno
    -> join teacher
    -> on course.tno=teacher.tno
    -> where teacher.tname='oldguo'
    -> having score.score < 60;
+--------+-------+-------+-------+
| tname  | cname | sname | score |
+--------+-------+-------+-------+
| oldguo | mysql | li4   |    40 |
| oldguo | mysql | zh4   |    40 |
+--------+-------+-------+-------+
2 rows in set (0.00 sec)

#创建一个名为check_score的视图
mysql> create view check_score as select teacher.tname,course.cname,student.sname,score.score
    -> from student
    -> join score
    -> on student.sno=score.sno
    -> join course
    -> on score.cno=course.cno
    -> join teacher
    -> on course.tno=teacher.tno
    -> where teacher.tname='oldguo'
    -> having score.score < 60;
Query OK, 0 rows affected (0.00 sec)

#使用check_score视图
mysql> select * from check_score;
+--------+-------+-------+-------+
| tname  | cname | sname | score |
+--------+-------+-------+-------+
| oldguo | mysql | li4   |    40 |
| oldguo | mysql | zh4   |    40 |
+--------+-------+-------+-------+
2 rows in set (0.00 sec)

参考资料: 什么是视图

information_schema库

该库保存了大量的对元数据进行查询的视图，视图相当于是虚拟的表。
例如其中常用的表tables保存了所有数据库的名字以及表名

mysql> use information_schema;

mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| ENGINES                               |
| EVENTS                                |
| FILES                                 |
| GLOBAL_STATUS                         |
| GLOBAL_VARIABLES                      |
| KEY_COLUMN_USAGE                      |
| OPTIMIZER_TRACE                       |
| PARAMETERS                            |
| PARTITIONS                            |
| PLUGINS                               |
| PROCESSLIST                           |
| PROFILING                             |
| REFERENTIAL_CONSTRAINTS               |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| SESSION_STATUS                        |
| SESSION_VARIABLES                     |
| STATISTICS                            |
| TABLES                                |
| TABLESPACES                           |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
| INNODB_LOCKS                          |
| INNODB_TRX                            |
| INNODB_SYS_DATAFILES                  |
| INNODB_FT_CONFIG                      |
| INNODB_SYS_VIRTUAL                    |
| INNODB_CMP                            |
| INNODB_FT_BEING_DELETED               |
| INNODB_CMP_RESET                      |
| INNODB_CMP_PER_INDEX                  |
| INNODB_CMPMEM_RESET                   |
| INNODB_FT_DELETED                     |
| INNODB_BUFFER_PAGE_LRU                |
| INNODB_LOCK_WAITS                     |
| INNODB_TEMP_TABLE_INFO                |
| INNODB_SYS_INDEXES                    |
| INNODB_SYS_TABLES                     |
| INNODB_SYS_FIELDS                     |
| INNODB_CMP_PER_INDEX_RESET            |
| INNODB_BUFFER_PAGE                    |
| INNODB_FT_DEFAULT_STOPWORD            |
| INNODB_FT_INDEX_TABLE                 |
| INNODB_FT_INDEX_CACHE                 |
| INNODB_SYS_TABLESPACES                |
| INNODB_METRICS                        |
| INNODB_SYS_FOREIGN_COLS               |
| INNODB_CMPMEM                         |
| INNODB_BUFFER_POOL_STATS              |
| INNODB_SYS_COLUMNS                    |
| INNODB_SYS_FOREIGN                    |
| INNODB_SYS_TABLESTATS                 |
+---------------------------------------+
61 rows in set (0.00 sec)

#查看tables表结构
mysql> desc tables;
+-----------------+---------------------+------+-----+---------+-------+
| Field           | Type                | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG   | varchar(512)        | NO   |     |         |       |
| TABLE_SCHEMA    | varchar(64)         | NO   |     |         |       |
| TABLE_NAME      | varchar(64)         | NO   |     |         |       |
| TABLE_TYPE      | varchar(64)         | NO   |     |         |       |
| ENGINE          | varchar(64)         | YES  |     | NULL    |       |
| VERSION         | bigint(21) unsigned | YES  |     | NULL    |       |
| ROW_FORMAT      | varchar(10)         | YES  |     | NULL    |       |
| TABLE_ROWS      | bigint(21) unsigned | YES  |     | NULL    |       |
| AVG_ROW_LENGTH  | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_LENGTH     | bigint(21) unsigned | YES  |     | NULL    |       |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES  |     | NULL    |       |
| INDEX_LENGTH    | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_FREE       | bigint(21) unsigned | YES  |     | NULL    |       |
| AUTO_INCREMENT  | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_TIME     | datetime            | YES  |     | NULL    |       |
| UPDATE_TIME     | datetime            | YES  |     | NULL    |       |
| CHECK_TIME      | datetime            | YES  |     | NULL    |       |
| TABLE_COLLATION | varchar(32)         | YES  |     | NULL    |       |
| CHECKSUM        | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_OPTIONS  | varchar(255)        | YES  |     | NULL    |       |
| TABLE_COMMENT   | varchar(2048)       | NO   |     |         |       |
+-----------------+---------------------+------+-----+---------+-------+
21 rows in set (0.01 sec)

常用的表为TABLES
常用的列如下所示

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

案例

#查看所有数据库的表名
mysql> select table_schema,table_name from information_schema.tables;
#仅显示部分
+--------------------+------------------------------------------------------+
| table_schema       | table_name                                           |
+--------------------+------------------------------------------------------+
| school             | check_score                                          |
| school             | course                                               |
| school             | score                                                |
| school             | student                                              |
| school             | teacher                                              |
| school             | test                                                 |
+--------------------+------------------------------------------------------+

#一行显示每个数据库的表名
select table_schema,GROUP_CONCAT(table_name) as tables 
from information_schema.tables 
group by table_schema;

#查询所有innodb引擎的表
select table_schema,table_name,engine 
from information_schema.tables
where ENGINE='innodb';

#统计school数据库中course表占用空间大小
#公式：平均行长度*行数+索引长度
select table_name,concat(((avg_row_length*table_rows+index_length)/1024),'kb') as size
from information_schema.tables
where table_schema='school' and table_name='course';
+------------+-----------+
| table_name | size      |
+------------+-----------+
| course     | 15.9990kb |
+------------+-----------+
1 row in set (0.00 sec)

#求school库所有表总大小
select table_schema,concat((sum(avg_row_length*table_rows+index_length)/1024),'kb') as size
from information_schema.tables
where table_schema='school' ;
+--------------+-----------+
| table_schema | size      |
+--------------+-----------+
| school       | 79.9814kb |
+--------------+-----------+
1 row in set (0.00 sec)

#统计每个数据库的大小，并从大到小排序
select table_schema,concat((sum(avg_row_length*table_rows+index_length)/1024),'kb') as size
from information_schema.tables 
group by table_schema
order by size desc;
+--------------------+-------------+
| table_schema       | size        |
+--------------------+-------------+
| school             | 79.9814kb   |
| mysql              | 2307.5078kb |
| sys                | 15.9961kb   |
| performance_schema | 0.0000kb    |
| information_schema | NULL        |
+--------------------+-------------+
5 rows in set (6.15 sec)
#注意information_schema是一个虚拟库所以不占空间，每次数据库启动就会自动生成。

对数据库进行备份
可使用mysqldump工具对mysql数据库进行备份

#对school数据库中的student表进行备份
mysqldump -uroot -p123 school student >/bak/school_student.sql

#concat拼接语句如下
select concat("mysqldump -uroot -p123 ",table_schema," ",table_name
," >/bak/",table_schema,"_",table_name,".sql")
from information_schema.tables;

#仅备份school库所有表
select concat("mysqldump -uroot -p123 ",table_schema," ",table_name
," >/bak/",table_schema,"_",table_name,".sql")
from information_schema.tables
where information_schema.tables.TABLE_SCHEMA='school';

+----------------------------------------------------------------------------------+
| concat("mysqldump -uroot -p123 ",table_schema," ",table_name
," >/bak/",table_schema,"_",table_name,".sql") |
+----------------------------------------------------------------------------------+
| mysqldump -uroot -p123 school check_score >/bak/school_check_score.sql           |
| mysqldump -uroot -p123 school course >/bak/school_course.sql                     |
| mysqldump -uroot -p123 school score >/bak/school_score.sql                       |
| mysqldump -uroot -p123 school student >/bak/school_student.sql                   |
| mysqldump -uroot -p123 school teacher >/bak/school_teacher.sql                   |
| mysqldump -uroot -p123 school test >/bak/school_test.sql                         |
+----------------------------------------------------------------------------------+
6 rows in set (0.00 sec)

使用show命令

#查看帮助
mysql> help show;

#常用的show命令
show databases;        		            #查看数据库名
show tables; 		   		            #查看表名
show create database xx;  	            #查看建库语句
show create table table_name;	        #查看建表语句
show processlist;			            #查看所有用户连接情况
show charset;				            #查看支持的字符集
show collation;				            #查看所有支持的校对规则
show grants for root;			        #查看用户的权限信息
show variables like '%xx%'              #查看参数信息
show engines;				            #查看所有支持的存储引擎类型
show index from table_name;		        #查看表的索引信息
show engine innodb status\G;            #查看innoDB引擎详细状态信息
show binary logs; 			            #查看二进制日志的列表信息
show binlog events in '日志文件名';	    #查看二进制日志的事件信息
show master status;		                #查看mysql当前使用二进制日志信息
show slave status\G; 		            #查看从库状态信息
show relaylog events in '日志文件名';	    #查看中继日志的事件信息
show status like '';			        #查看数据库整体状态信息

将sql导出保存为文件

在mysql中可以将sql语句导出到文件中，但mysql会检查导出目录的安全性，需要在配置文件中设置安全目录路径

修改配置文件，设置安全目录

$ vim /etc/my.cnf
[mysqld]
#可以为空，表示可以导出到任何目录中
secure-file-priv=

使用concat连接sql语句导出到文件

mysql> select concat("alter table ",table_schema," ",table_name," discard tablespace;") \ 
from information_schema.tables where table_schema='world' \ 
into outfile '/tmp/world.sql'

#应用
mysql> source /tmp/world.sql

导出数据到文件

#数据一般使用excle表格式,csv或者xlsx格式
mysql> select * from city into outfile '/tmp/city.xlsx';

学习来自：郭老师博客，老男孩深标DBA课程 第三章