SHOW

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

SHOW DATABASES

展示所有数据库:

  1. SHOW 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';

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, TEMPORARYVIEW 等等。

同样也支持 likewhere 查询:

  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. )
  14. ENGINE=mito
  15. WITH(
  16. regions = 1
  17. ) |
  18. +----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  • Table: 表的名称
  • Create Table: 用于创建该表的 SQL