MySQL之数据库对象查看工具mysqlshow

    mysqlshow：数据库对象查看工具，用来快速查找存在哪些数据库、数据库中的表、表中的列或索引。

选项：--count    显示数据库和表的统计信息

      -k         显示指定的表中的索引

      -i         显示表的状态信息

不带任何参数显示所有数据库

[root@www mysql-5.6.30]# mysqlshow

+--------------------+

|     Databases      |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| test               |

+--------------------+

不指定数据库，显示数据库和表的统计信息

[root@www mysql-5.6.30]# mysqlshow --count

+--------------------+--------+--------------+

|     Databases      | Tables |  Total Rows  |

+--------------------+--------+--------------+

| information_schema |     59 |        13157 |

| mysql              |     28 |         2474 |

| performance_schema |     52 |        16615 |

| test               |      2 |           24 |

+--------------------+--------+--------------+

4 rows in set.

指定数据库，显示统计信息

[root@www mysql-5.6.30]# mysqlshow -uroot test --count

Database: test

+----------+----------+------------+

|  Tables  | Columns  | Total Rows |

+----------+----------+------------+

| backt118 |        4 |         12 |

| t118     |        4 |         12 |

+----------+----------+------------+

2 rows in set.

指定数据库和表，显示统计信息

[root@www mysql-5.6.30]# mysqlshow -uroot test t118 --count

Database: test  Table: t118  Rows: 12

+---------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+

| Field   | Type     | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |

+---------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+

| name    | char(10) | utf8_general_ci | YES  | MUL |         |       | select,insert,update,references |         |

| age     | int(3)   |                 | YES  |     |         |       | select,insert,update,references |         |

| chushen | date     |                 | YES  |     |         |       | select,insert,update,references |         |

| shengao | int(3)   |                 | YES  |     |         |       | select,insert,update,references |         |

+---------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+

显示指定表所有的索引，前部分是表的结构，后部分是表的索引信息

[root@www mysql-5.6.30]# mysqlshow -uroot test t118 -k

Database: test  Table: t118

+---------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+

| Field   | Type     | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |

+---------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+

| name    | char(10) | utf8_general_ci | YES  | MUL |         |       | select,insert,update,references |         |

| age     | int(3)   |                 | YES  |     |         |       | select,insert,update,references |         |

| chushen | date     |                 | YES  |     |         |       | select,insert,update,references |         |

| shengao | int(3)   |                 | YES  |     |         |       | select,insert,update,references |         |

+---------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+

+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| t118  | 1          | index_name | 1            | name        | A         | 12          |          |        | YES  | BTREE      |         |               |

| t118  | 1          | index_ccc  | 1            | name        | A         | 12          |          |        | YES  | BTREE      |         |               |

+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

显示表的状态信息

[root@www mysql-5.6.30]# mysqlshow -i -uroot test t118

Database: test  Wildcard: t118

+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+

| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |

+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+

| t118 | InnoDB | 10      | Compact    | 12   | 1365           | 16384       | 0               | 0            | 0         |                | 2017-04-29 22:33:47 |             |            | utf8_general_ci |          |                |         |

+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+