我的书签
添加书签
移除书签SHOW
SHOW 关键字提供数据库和表信息。
SHOW DATABASES
展示所有数据库：
1. SHOW [FULL] DATABASES;
1. +---------+
2. | Schemas |
3. +---------+
4. | public |
5. +---------+
6. 1 row in set (0.01 sec)
展示名称符合 LIKE 模式的数据库：
1. SHOW DATABASES LIKE 'p%';
根据 where 表达式展示数据库：
1. SHOW DATABASES WHERE Schemas='test_public_schema';
展示所有数据库，包括它们的选项：
1. create database with(ttl='7d');
2. SHOW FULL DATABASES;
1. +--------------------+-------------+
2. | Database | Options |
3. +--------------------+-------------+
4. | greptime_private | |
5. | information_schema | |
6. | public | |
7. | test | ttl='7days' |
8. +--------------------+-------------+
SHOW TABLES
展示所有表：
1. SHOW TABLES;
1. +---------+
2. | Tables |
3. +---------+
4. | numbers |
5. | scripts |
6. +---------+
7. 2 rows in set (0.00 sec)
展示 test 数据库中的所有表：
1. SHOW TABLES FROM test;
展示名称符合 LIKE 模式的表：
1. SHOW TABLES like '%prometheus%';
根据 where 表达式展示表：
1. SHOW TABLES FROM test WHERE Tables='numbers';
SHOW FULL TABLES
1. SHOW FULL TABLES [IN | FROM] [DATABASE] [LIKE pattern] [WHERE query]
将会展示指定数据库（或者默认 public）中所有的表及其类型：
1. SHOW FULL TABLES;
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 查询：
1. SHOW FULL TABLES FROM public like '%mo%';
1. +---------+------------+
2. | Tables | Table_type |
3. +---------+------------+
4. | monitor | BASE TABLE |
5. +---------+------------+
6. 1 row in set (0.01 sec)
1. SHOW FULL TABLES WHERE Table_type='BASE TABLE';
1. +---------+------------+
2. | Tables | Table_type |
3. +---------+------------+
4. | monitor | BASE TABLE |
5. +---------+------------+
6. 1 row in set (0.01 sec)
SHOW CREATE TABLE
展示创建指定表的 CREATE TABLE 语句：
1. SHOW CREATE TABLE [table]
例如：
1. SHOW CREATE TABLE system_metrics;
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
SHOW CREATE FLOW
展示创建指定 Flow 任务的 CREATE FLOW 语句。
比如：
1. public=> SHOW CREATE FLOW filter_numbers;
1. Flow | Create Flow
2. ----------------+-------------------------------------------------------
3. filter_numbers | CREATE OR REPLACE FLOW IF NOT EXISTS filter_numbers +
4. | SINK TO out_num_cnt +
5. | AS SELECT number FROM numbers_input WHERE number > 10
6. (1 row)
SHOW FLOWS
展示当前所有 Flow 任务：
1. public=> SHOW FLOWS;
1. Flows
2. ----------------
3. filter_numbers
4. (1 row)
同样也支持 LIKE 表达式：
1. public=> show flows like "filter%";
1. Flows
2. ----------------
3. filter_numbers
4. (1 row)
SHOW CREATE VIEW
用于显示视图（View）的定义：
1. SHOW CREATE VIEW cpu_monitor;
1. +-------------+--------------------------------------------------------------+
2. | View | Create View |
3. +-------------+--------------------------------------------------------------+
4. | cpu_monitor | CREATE VIEW cpu_monitor AS SELECT cpu, host, ts FROM monitor |
5. +-------------+--------------------------------------------------------------+
SHOW VIEWS
列出所有视图：
1. SHOW VIEWS;
1. +----------------+
2. | Views |
3. +----------------+
4. | cpu_monitor |
5. | memory_monitor |
6. +----------------+
当然，它也支持 LIKE 查询：
1. SHOW VIEWS LIKE 'cpu%';
1. +-------------+
2. | Views |
3. +-------------+
4. | cpu_monitor |
5. +-------------+
以及 WHERE 条件：
1. SHOW VIEWS WHERE Views = 'memory_monitor';
1. +----------------+
2. | Views |
3. +----------------+
4. | memory_monitor |
5. +----------------+
SHOW 语句的扩展
与 MySQL 类似，一些 SHOW 语句的扩展伴随着 INFORMATION_SCHEMA 的实现，它们还接受 WHERE 子句，提供了在指定显示的行时更大的灵活性。
GreptimeDB 为 MySQL 兼容性实现了这些扩展的一部分，这对于像 Navicat for MySQL 或 dbeaver 这样的工具连接 GreptimeDB 非常有用。
1. SHOW CHARACTER SET;
输出类似于 INFORMATION_SCHEMA.CHARACTER_SETS 表：
1. +---------+---------------+-------------------+--------+
2. | Charset | Description | Default collation | Maxlen |
3. +---------+---------------+-------------------+--------+
4. | utf8 | UTF-8 Unicode | utf8_bin | 4 |
5. +---------+---------------+-------------------+--------+
使用 SHOW COLLATION 来查看 INFORMATION_SCHEMA.COLLATIONS 表。
1. SHOW INDEX FROM monitor;
列出表中的所有索引：
1. +---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+----------------------------+---------+---------------+---------+------------+
2. | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
3. +---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+----------------------------+---------+---------------+---------+------------+
4. | monitor | 1 | PRIMARY | 1 | host | A | NULL | NULL | NULL | YES | greptime-inverted-index-v1 | | | YES | NULL |
5. | monitor | 1 | TIME INDEX | 1 | ts | A | NULL | NULL | NULL | NO | greptime-inverted-index-v1 | | | YES | NULL |
6. +---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+----------------------------+---------+---------------+---------+------------+
这是 INFORMATION_SCHEMA.TABLE_CONSTRAINTS 的扩展。
列出表中的所有列：
1. SHOW COLUMNS FROM monitor;
输出类似于 INFORMATION_SCHEMA.COLUMNS：
1. +--------+--------------+------+------------+---------------------+-------+----------------------+
2. | Field | Type | Null | Key | Default | Extra | Greptime_type |
3. +--------+--------------+------+------------+---------------------+-------+----------------------+
4. | cpu | double | Yes | | 0 | | Float64 |
5. | host | string | Yes | PRI | NULL | | String |
6. | memory | double | Yes | | NULL | | Float64 |
7. | ts | timestamp(3) | No | TIME INDEX | current_timestamp() | | TimestampMillisecond |
8. +--------+--------------+------+------------+---------------------+-------+----------------------+
所有这些 SHOW 扩展都接受 WHERE 子句：
1. SHOW COLUMNS FROM monitor WHERE Field = 'cpu';
1. +-------+--------+------+------+---------+-------+---------------+
2. | Field | Type | Null | Key | Default | Extra | Greptime_type |
3. +-------+--------+------+------+---------+-------+---------------+
4. | cpu | double | Yes | | 0 | | Float64 |
5. +-------+--------+------+------+---------+-------+---------------+
其他 SHOW 扩展语句：
SHOW STATUS和SHOW VARIABLES不支持，仅返回空结果。SHOW TABLE STATUS是INFORMATION_SCHEMA.TABLES的扩展。
