您可以通过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;
  • 示例
    SHOW GRANTS;
    返回结果如下:
    +---------------------------------------------------------+
    | Grants for adb_acc@%                                    |
    +---------------------------------------------------------+
    | GRANT ALL ON `*`.`*` TO 'adb_acc'@'%' 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      |         |               |
    +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+