mysql 系统查询语句

前言：
information_schema 库的 TABLES 表，字段分别是
CREATE TEMPORARY TABLE `TABLES` (
`TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
`TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
`TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
`TABLE_TYPE` varchar(64) NOT NULL DEFAULT '',
`ENGINE` varchar(64) DEFAULT NULL,
`VERSION` bigint(21) unsigned DEFAULT NULL,
`ROW_FORMAT` varchar(10) DEFAULT NULL,
`TABLE_ROWS` bigint(21) unsigned DEFAULT NULL,
`AVG_ROW_LENGTH` bigint(21) unsigned DEFAULT NULL,
`DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
`MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
`INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL,
`DATA_FREE` bigint(21) unsigned DEFAULT NULL,
`AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL,
`CREATE_TIME` datetime DEFAULT NULL,
`UPDATE_TIME` datetime DEFAULT NULL,
`CHECK_TIME` datetime DEFAULT NULL,
`TABLE_COLLATION` varchar(32) DEFAULT NULL,
`CHECKSUM` bigint(21) unsigned DEFAULT NULL,
`CREATE_OPTIONS` varchar(255) DEFAULT NULL,
`TABLE_COMMENT` varchar(2048) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8 |

[b]查询所有数据库总大小[/b]
mysql> select concat(round(sum(DATA_LENGTH/1024/1024), 2),'MB') as data_size from information_schema.tables;
+-----------+
| data_size |
+-----------+
| 1177.00MB |
+-----------+
1 row in set (19.26 sec)


[b]查询某个数据库大小[/b]
mysql> select concat(round(sum(DATA_LENGTH/1024/1024), 2),'MB') as data_size from information_schema.tables where TABLE_SCHEMA='mysql';
+-----------+
| data_size |
+-----------+
| 0.55MB |
+-----------+
1 row in set (0.25 sec)


[b]1、查询数据库中的表占用空间大小[/b]
例如，查询mysql数据库下表
mysql> select TABLE_NAME, concat(truncate(data_length/1024/1024,2),' MB') as data_size, concat(truncate(index_length/1024/1024,2),' MB') as index_size from information_schema.tables where TABLE_SCHEMA='mysql' group by TABLE_NAME order by data_length desc;
+---------------------------+-----------+------------+
| TABLE_NAME | data_size | index_size |
+---------------------------+-----------+------------+
| help_topic | 0.45 MB | 0.02 MB |
| help_keyword | 0.08 MB | 0.01 MB |
| help_relation | 0.00 MB | 0.01 MB |
| proxies_priv | 0.00 MB | 0.00 MB |
| help_category | 0.00 MB | 0.00 MB |
| user | 0.00 MB | 0.00 MB |
| proc | 0.00 MB | 0.00 MB |
| db | 0.00 MB | 0.00 MB |
| ndb_binlog_index | 0.00 MB | 0.00 MB |
| time_zone_transition | 0.00 MB | 0.00 MB |
| general_log | 0.00 MB | 0.00 MB |
| time_zone_leap_second | 0.00 MB | 0.00 MB |
| event | 0.00 MB | 0.00 MB |
| tables_priv | 0.00 MB | 0.00 MB |
| columns_priv | 0.00 MB | 0.00 MB |
| servers | 0.00 MB | 0.00 MB |
| procs_priv | 0.00 MB | 0.00 MB |
| plugin | 0.00 MB | 0.00 MB |
| host | 0.00 MB | 0.00 MB |
| time_zone_transition_type | 0.00 MB | 0.00 MB |
| time_zone_name | 0.00 MB | 0.00 MB |
| func | 0.00 MB | 0.00 MB |
| time_zone | 0.00 MB | 0.00 MB |
| slow_log | 0.00 MB | 0.00 MB |
+---------------------------+-----------+------------+
24 rows in set (0.86 sec)

[b]2、查询数据库中某个表占用空间的大小[/b]
例如，查询mysql数据库下的help_topic表
mysql> select TABLE_NAME, concat(truncate(data_length/1024/1024,2),' MB') as data_size, concat(truncate(index_length/1024/1024,2),' MB') as index_size from information_schema.tables where TABLE_SCHEMA='mysql' and TABLE_NAME='help_topic' group by TABLE_NAME order by data_length desc;
+------------+-----------+------------+
| TABLE_NAME | data_size | index_size |
+------------+-----------+------------+
| help_topic | 0.45 MB | 0.02 MB |
+------------+-----------+------------+
1 row in set (0.05 sec)


[b]3、查看数据库中表的最后修改时间[/b]
例如，查询mysql数据库下的表
mysql> SELECT TABLE_NAME,UPDATE_TIME FROM information_schema.tables where TABLE_SCHEMA='mysql';
+---------------------------+---------------------+
| TABLE_NAME | UPDATE_TIME |
+---------------------------+---------------------+
| columns_priv | 2014-12-02 14:38:56 |
| db | 2014-12-02 14:56:52 |
| event | 2014-12-02 14:39:05 |
| func | 2014-12-02 14:38:59 |
| general_log | NULL |
| help_category | 2014-12-02 14:38:34 |
| help_keyword | 2014-12-02 14:38:34 |
| help_relation | 2014-12-02 14:38:34 |
| help_topic | 2014-12-02 14:38:34 |
| host | 2014-12-02 14:39:05 |
| ndb_binlog_index | 2014-12-02 14:38:32 |
| plugin | 2014-12-02 14:39:00 |
| proc | 2014-12-02 14:39:05 |
| procs_priv | 2014-12-02 14:39:03 |
| proxies_priv | 2014-12-02 14:38:34 |
| servers | 2014-12-02 14:38:30 |
| slow_log | NULL |
| tables_priv | 2014-12-02 14:38:56 |
| time_zone | 2014-12-02 14:38:31 |
| time_zone_leap_second | 2014-12-02 14:38:31 |
| time_zone_name | 2014-12-02 14:38:30 |
| time_zone_transition | 2014-12-02 14:38:31 |
| time_zone_transition_type | 2014-12-02 14:38:31 |
| user | 2015-06-26 18:14:29 |
+---------------------------+---------------------+
24 rows in set (0.01 sec)

同理，查询某个表，加上TABLE_NAME条件。