SHOW

SHOW 关键字提供数据库和表信息。

SHOW DATABASES

展示所有数据库：

sql

1. SHOW DATABASES;

sql

1. +---------+
2. | Schemas |
3. +---------+
4. | public  |
5. +---------+
6. 1 row in set (0.01 sec)

展示名称符合 LIKE 模式的数据库：

sql

1. SHOW DATABASES LIKE 'p%';

根据 where 表达式展示数据库：

sql

1. SHOW DATABASES WHERE Schemas='test_public_schema';

SHOW TABLES

展示所有表：

sql

1. SHOW TABLES;

sql

1. +---------+
2. | Tables  |
3. +---------+
4. | numbers |
5. | scripts |
6. +---------+
7. 2 rows in set (0.00 sec)

展示 test 数据库中的所有表：

sql

1. SHOW TABLES FROM test;

展示名称符合 LIKE 模式的表：

sql

1. SHOW TABLES like '%prometheus%';

根据 where 表达式展示表：

sql

1. SHOW TABLES FROM test WHERE Tables='numbers';

SHOW FULL TABLES

sql

1. SHOW FULL TABLES [IN | FROM] [DATABASE] [LIKE pattern] [WHERE query]

将会展示指定数据库（或者默认 public）中所有的表及其类型：

sql

1. SHOW FULL TABLES;

sql

1. +---------+------------+
2. | Tables  | Table_type |
3. +---------+------------+
4. | monitor | BASE TABLE |
5. | numbers | TEMPORARY  |
6. +---------+------------+
7. 2 rows in set (0.00 sec)

• Tables: 表的名称
• Table_type: 表的类型，例如 BASE_TABLE, TEMPORARY 和 VIEW 等等。

同样也支持 like 和 where 查询：

sql

1. SHOW FULL TABLES FROM public like '%mo%';

sql

1. +---------+------------+
2. | Tables  | Table_type |
3. +---------+------------+
4. | monitor | BASE TABLE |
5. +---------+------------+
6. 1 row in set (0.01 sec)

sql

1. SHOW FULL TABLES WHERE Table_type='BASE TABLE';

sql

1. +---------+------------+
2. | Tables  | Table_type |
3. +---------+------------+
4. | monitor | BASE TABLE |
5. +---------+------------+
6. 1 row in set (0.01 sec)

SHOW CREATE TABLE

展示创建指定表的 CREATE TABLE 语句：

sql

1. SHOW CREATE TABLE [table]

例如：

sql

1. SHOW CREATE TABLE system_metrics;

sql

1. +----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2. | Table          | Create Table                                                                                                                                                                                                                                                                                                                 |
3. +----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4. | system_metrics | CREATE TABLE IF NOT EXISTS `system_metrics` (
5. `host` STRING NULL,
6. `idc` STRING NULL,
7. `cpu_util` DOUBLE NULL,
8. `memory_util` DOUBLE NULL,
9. `disk_util` DOUBLE NULL,
10. `ts` TIMESTAMP(3) NOT NULL DEFAULT current_timestamp(),
11. TIME INDEX (`ts`),
12. PRIMARY KEY (`host`, `idc`)
13. )

15. ENGINE=mito
16. WITH(
17. regions = 1
18. ) |
19. +----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

• Table: 表的名称
• Create Table: 用于创建该表的 SQL