SQL基础应用扩展

元数据获取

元数据介绍及获取介绍

元数据是存储在“基表”中。
通过专用的DDL语句，DCL语句进行修改
通过专用视图进行元数据的查询
information_schema中保存了大量元数据查询的视图
show命令是封装的功能，提供元数据查询基础功能

什么是视图

例子

mysql> create view aa AS select CountryCode,SUM(Population) from city GROUP BY CountryCode HAVING SUM(Population) > 50000000 ORDER BY SUM(Population) DESC limit 3;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM aa;
+-------------+-----------------+
| CountryCode | SUM(Population) |
+-------------+-----------------+
| CHN         |       175953614 |
| IND         |       123298526 |
| BRA         |        85876862 |
+-------------+-----------------+
3 rows in set (0.01 sec)

information_schema的基本应用

查看tables视图

mysql> use information_schema;
mysql> show tables where Tables_in_information_schema = 'TABLES';
+------------------------------+
| Tables_in_information_schema |
+------------------------------+
| TABLES                       |
+------------------------------+
1 row 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.00 sec)

例子：

1. 显示所有库和表的信息

select TABLE_SCHEMA,GROUP_CONCAT(TABLE_NAME) from TABLES GROUP BY TABLE_SCHEMA;

2. 统计以下所有innodb引擎的表

select TABLE_NAME from TABLES where `ENGINE`='innodb'

3. 统计world库下的city表占用空间大小(KB)
   平均行长度*行数+索引长度（表的数据量）

select TABLE_NAME,(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024 FROM `TABLES` WHERE TABLE_SCHEMA='world' AND TABLE_NAME='city'

4. 统计world库数据量总大小(KB)

select TABLE_SCHEMA,sum(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024 FROM `TABLES` WHERE TABLE_SCHEMA='world'

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

select TABLE_SCHEMA,sum(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024 FROM `TABLES` GROUP BY TABLE_SCHEMA ORDER BY sum(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH) DESC;

配合concat()函数来拼接语句或命令

例子：

1. 模仿以下语句，进行数据库的分库分表备份
   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 `TABLES` 

show语句的介绍

show databases;  --查看数据库名
show tables;  --查看表名
show create databases 库名 --查看建库语句
show create table 表名 --查看建表语句
show processlist; -- 查看所有用户的连接情况
show charset;  --所有支持的字符集
show collation; --所有支持的校对规则
show grants for 用户; --查看用户的权限信息
show variables like '%xx%'  --查看参数信息
show engines;  --查看所有支持的存储引擎类型
show index from xxx  --查看表的索引信息
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 '' --查看数据库整体状态信息