您可以通过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 | | | +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+