您可以通过SHOW语句查看数据库相关信息,例如数据库列表、数据库中的表以及表中的列或索引等。

SHOW DATABASES

查看当前集群中的数据库。

语法

SHOW DATABASES;            

示例

SHOW DATABASES;

返回结果如下:

+--------------------+
| Database           |
+--------------------+
| adb_test           |
| MYSQL              |
| adb_demo           |
| INFORMATION_SCHEMA |
+--------------------+

SHOW TABLES

查看用户当前数据库中的表。

语法

SHOW TABLES [IN db_name];          

示例

SHOW TABLES IN adb_demo;

返回结果如下:

+--------------------+
| Tables_in_adb_demo |
+--------------------+
| customer           |
| json_test          |
+--------------------+

SHOW COLUMNS

查看表的列信息。

语法

SHOW COLUMNS IN db_name.table_name;        

示例

SHOW COLUMNS IN adb_demo.customer;

返回结果如下:

+---------+---------+------+------+---------+-------+
| Field   | Type    | Null | Key  | Default | Extra |
+---------+---------+------+------+---------+-------+
| id      | int     | NO   | PRI  | NULL    |       |
| name    | varchar | YES  |      | NULL    |       |
| address | varchar | YES  |      | NULL    |       |
| gender  | boolean | YES  |      | NULL    |       |
+---------+---------+------+------+---------+-------+

SHOW CREATE TABLE

查看表的建表语句。

语法

SHOW CREATE TABLE db_name.table_name;          

示例

SHOW CREATE TABLE adb_demo.customer;

返回结果如下:

+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                    |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| customer | Create Table `customer` (
 `id` int NOT NULL,
 `name` varchar(50),
 `address` varchar(80),
 `gender` boolean,
 primary key (`id`)
) DISTRIBUTE BY HASH(`id`) INDEX_ALL='Y' STORAGE_POLICY='HOT' BLOCK_SIZE=8192 |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

SHOW GRANTS

查看用户的权限。

语法

SHOW GRANTS [FOR 'username'@'hostname'];

其中,username为用户名,hostname为主机名或主机IP。

示例

  • 查看当前用户的权限,无需指定用户名和主机。

    SHOW GRANTS;

    返回结果如下:

    +---------------------------------------------------------+
    | Grants for adb_acc@%                                    |
    +---------------------------------------------------------+
    | GRANT ALL ON `*`.`*` TO 'adb_acc'@'%' WITH GRANT OPTION |
    +---------------------------------------------------------+
  • 查看指定用户的权限。

    SHOW GRANTS FOR 'test'@'%';

    返回结果如下:

    +---------------------------------------------------------+
    | Grants for  test@%                                      |
    +---------------------------------------------------------+
    | GRANT ALL ON `*`.`*` TO 'adb'@'%' WITH GRANT OPTION     |
    +---------------------------------------------------------+

SHOW INDEXES

查看表的索引信息。

语法

SHOW INDEXES FROM db_name.table_name;  

示例

SHOW INDEXES FROM adb_demo.json_test;

返回结果如下,其中Key_name即为索引名:

+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| json_test |          1 | id_0_idx |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| json_test |          1 | vj_idx   |            1 | vj          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+