MySQL-SQL基础应用(元数据获取)

2.1 元数据介绍

基表---->数据字典信息（表列结构，存储在frm文件中）、系统状态、对象状态；
相当于Linux中的Inode

2.2 show 语句(MySQL独家)

show databases;
show tables; 
show create database  xxx;
show create table  xxx;
show grants for xxx;
show charset ;
show collation;
show variables like '%trx%';
show engines; 
show processlist;
show index from t1;
show status ;
show engine innodb status\G
show binlog events in ''
show binary logs 
show master status 
show slave status\G 
show relaylog events in ''
SHOW TABLE STATUS ;

help show; 

2.3 information_schema虚拟库（试图）

2.3.1 试图

将一些常用的查询需求，分装成试图，后期调用直接查询试图就可以，更加简便、安全

-- 创建试图
CREATE VIEW test AS SELECT    
country.name AS co_name,country.SurfaceArea,city.name AS ci_name,city.Population
FROM city   JOIN country
ON city.CountryCode=country.code
WHERE city.Population<100;

-- 调用试图
SELECT * FROM test; 

2.3.2 TABLES 的作用和结构

1>TABLES 的作用
存储整个数据库中，所有表中的元数据的查询方式.
2>TABLES 的结构

wenjuan[information_schema]>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.00 sec)

wenjuan[information_schema]>

常用查询列：
TABLE_SCHEMA 表所在的库
TABLE_NAME 表名
ENGINE 表的引擎
TABLE_ROWS 表的行数
AVG_ROW_LENGTH 平均行长度
INDEX_LENGTH 索引长度

3> 举例
（1）查询 world 数据库下的所有表名

wenjuan[(none)]>show tables from world;

（2）查询整个数据库下的所有表名

wenjuan[(none)]>select table_name from information_schema.tables;

（3）查询所有InnoDB引擎的表

wenjuan[(none)]>SELECT table_schema,table_name,ENGINE FROM information_schema.TABLES WHERE ENGINE='innodb';

（4）统计每张表的实际占用空间大小情况(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)

SELECT table_name,AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH  FROM information_schema.TABLES

（5）统计每个库的空间使用情况大小情况

SELECT table_schema,SUM(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024  FROM information_schema.TABLES GROUP BY table_schema

中小型：300G---500G
中大型：2T------9T

（6）对MySQL的数据库进行分库分表备份（重要）

-- 单表备份
mysqldump -uroot -p123456  world city >/backup/world_city.sql

-- 进行分库分表，并将批量备份语句保存到文件中，之后`sh /tmp/sqlback.sql`执行文件就可以
SELECT CONCAT('mysqldump -uroot -p123456 ',table_schema,' ',table_name,' > /backup_sql/',table_schema,'_',table_name,'.sql')
FROM information_schema.TABLES INTO OUTFILE '/tmp/sqlback.sql'

注意：The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
出现以上的错误是没有设置安全路径，5.7以后才出现的约束
解决：将secure-file-priv=这个参数添加到/etc/my.cnf的服务端里面，可以指定*(所有目录)，也可以指定具体的某个目录，然后重启mysql：/etc/init.d/mysqld restart

（7）模仿模板语句,批量生成对world数据库下的表操作的语句

-- 单表修改
-- atler table world.city engine=innodb;

-- 批量修改
SELECT CONCAT("alter table ",table_schema,".",table_name," engine=innodb;")
FROM information_schema.tables WHERE table_schema='world'
INTO OUTFILE '/tmp/world_innodb.sql';

[root@db01 ~]# cat /tmp/world_innodb.sql 
alter table world.city engine=innodb;
alter table world.country engine=innodb;
alter table world.countrylanguage engine=innodb;
[root@db01 ~]#