南大通用GBase 8a的show命令列表和使用

GBase 8a提供了show 管理命令,来查看各种数据库信息,本文只是语法级的简单介绍和一些使用样例。

通用语法说明

指一些功能的参数,有相同的语法结构,后面就不每个都单独写一次了。

full

显示更完整的信息。默认显示的是精简的标准信息,full显示的列更多一些。

like

支持模糊匹配的部分,比如表名,列名等。

where

属于like的增强,不单单是名字,还可以对返回信息的多个列进行SQL级别的组合过滤。需要注意的是,列名字有时是关键字,建议用栓引号包围起来,比如

where "table_name" like '%abcd%'

vc

对应V95版本开始支持的虚拟集群;
1、V8没有这部分
2、如果V9没有启用虚拟集群,这部分也可以忽略

database

数据库名。如果不指定则默认是当前数据库。

show columns

显示指定表的列信息

gbase> show columns from t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| v1    | varchar(10) | YES  |     | NULL    |       |
| v2    | varchar(10) | YES  |     | NULL    |       |
| v3    | varchar(10) | YES  |     | NULL    |       |
| v4    | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (Elapsed: 00:00:00.00)

gbase> show columns from t2 from testdb;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| v1    | varchar(10) | YES  |     | NULL    |       |
| v2    | varchar(10) | YES  |     | NULL    |       |
| v3    | varchar(10) | YES  |     | NULL    |       |
| v4    | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (Elapsed: 00:00:00.00)


gbase> show columns from t2 like '%1%';
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| v1    | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (Elapsed: 00:00:00.00)


gbase> show full columns from testdb.t2 where type like '%varchar%' and "key"='';
+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type        | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |
+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| v1    | varchar(10) | utf8_general_ci | YES  |     | NULL    |       | select,insert,update,references |         |
| v2    | varchar(10) | utf8_general_ci | YES  |     | NULL    |       | select,insert,update,references |         |
| v3    | varchar(10) | utf8_general_ci | YES  |     | NULL    |       | select,insert,update,references |         |
| v4    | varchar(10) | utf8_general_ci | YES  |     | NULL    |       | select,insert,update,references |         |
+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
4 rows in set (Elapsed: 00:00:00.00)

gbase>

show create

显示对象的创建语句。格式全部是

show create OBJECT_TYPE objectName

SHOW CREATE FUNCTION

自定义函数的创建语句

SHOW CREATE PROCEDURE

存储过程创建语句

SHOW CREATE PUBLIC SYNONYM

光有的同义词创建语句

SHOW CREATE SYNONYM

私有的同义词创建语句

SHOW CREATE VIEW

视图创建语句。

show databases;

显示GBase里,当前用户能访问的数据库,有哪些库。

gbase> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
| abc                |
| db1                |
| db2                |
| dbmain             |
| gbase              |
| gclusterdb         |
| gctmpdb            |
| test               |
| testdb             |
+--------------------+
11 rows in set (Elapsed: 00:00:00.00)

gbase> show databases like '%db%';
+-----------------+
| Database (%db%) |
+-----------------+
| db1             |
| db2             |
| dbmain          |
| gclusterdb      |
| gctmpdb         |
| testdb          |
+-----------------+
6 rows in set (Elapsed: 00:00:00.00)

show distribution tables

显示express表的列表,包括分布表和复制表。


gbase> create table db1_t2_rep(id int)replicated;
Query OK, 0 rows affected (Elapsed: 00:00:01.27)

gbase> show distribution tables;
+--------+------------+-------------+
| dbName | tbName     | isReplicate |
+--------+------------+-------------+
| db1    | db1_t2_rep | YES         |
| db1    | db1_t1     | NO          |
+--------+------------+-------------+
2 rows in set (Elapsed: 00:00:00.00)

gbase> show distribution tables like '%1%';
+--------+------------+-------------+
| dbName | tbName     | isReplicate |
+--------+------------+-------------+
| db1    | db1_t2_rep | YES         |
| db1    | db1_t1     | NO          |
+--------+------------+-------------+
2 rows in set (Elapsed: 00:00:00.00)

show engines

gbase> show engines;
+-----------+---------+-----------------------------------------------------------+--------------+------+------------+
| Engine    | Support | Comment                                                   | Transactions | XA   | Savepoints |
+-----------+---------+-----------------------------------------------------------+--------------+------+------------+
| MRG_GSSYS | YES     | Collection of identical GsSYS tables                      | NO           | NO   | NO         |
| CSV       | YES     | CSV storage engine                                        | NO           | NO   | NO         |
| EXPRESS   | DEFAULT | Express storage engine                                    | YES          | YES  | NO         |
| GsSYS     | YES     | GsSYS engine                                              | NO           | NO   | NO         |
| MEMORY    | YES     | Hash based, stored in memory, useful for temporary tables | NO           | NO   | NO         |
+-----------+---------+-----------------------------------------------------------+--------------+------+------------+
5 rows in set (Elapsed: 00:00:00.00)

show errors

显示错误信息

gbase> show errors;
Empty set (Elapsed: 00:00:00.00)

gbase> show errors limit 3,1;
Empty set (Elapsed: 00:00:00.00)

show fields

show columns一样,别名。

show full tables

包含表类型, 视图是VIEW, 表是BASE TABLE

gbase> show full tables;
+------------------+------------+
| Tables_in_testdb | Table_type |
+------------------+------------+
| hash             | BASE TABLE |
| nodedatamap      | BASE TABLE |
| t                | BASE TABLE |
| t1               | BASE TABLE |
| t2               | BASE TABLE |
| t3               | BASE TABLE |
| t4               | BASE TABLE |
| tdecimal         | BASE TABLE |
| tdecimal2        | BASE TABLE |
| thash            | BASE TABLE |
| tlike            | BASE TABLE |
| tlike2           | BASE TABLE |
| tmp_nodedatamap  | BASE TABLE |
| tr               | BASE TABLE |
| tsi              | BASE TABLE |
| tt               | BASE TABLE |
| tti              | BASE TABLE |
| ttt              | BASE TABLE |
| v_tr             | VIEW       |
+------------------+------------+
19 rows in set (Elapsed: 00:00:00.00)

show function status

显示自定义函数的列表。

gbase> delimiter //
gbase> create function f_now() returns datetime begin return now();end //
Query OK, 0 rows affected (Elapsed: 00:00:00.02)

gbase> delimiter ;
gbase> select f_now();
+---------------------+
| f_now()             |
+---------------------+
| 2020-12-14 14:54:43 |
+---------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> show function status;
+------------------------------------------------------------------+------------------------------------------------------------------+----------+-------------------------------------------------------------------------------+---------------------+---------------------+---------------+------------------------------------------------------------------+----------------------------------+----------------------------------+----------------------------------+
| Db                                                               | Name                                                             | Type     | Definer                                                                       | Modified            | Created             | Security_type | Comment                                                          | character_set_client             | collation_connection             | Database Collation               |
+------------------------------------------------------------------+------------------------------------------------------------------+----------+-------------------------------------------------------------------------------+---------------------+---------------------+---------------+------------------------------------------------------------------+----------------------------------+----------------------------------+----------------------------------+
| db1                                                              | f_now                                                            | FUNCTION | root@%                                                                        | 2020-12-14 14:54:26 | 2020-12-14 14:54:26 | DEFINER       |                                                                  | utf8                             | utf8_general_ci                  | utf8_general_ci                  |
| testdb                                                           | gcadmin                                                          | FUNCTION | root@%                                                                        | 2020-11-19 10:19:34 | 2020-11-19 10:19:34 | DEFINER       |                                                                  | utf8                             | utf8_general_ci                  | utf8_general_ci                  |
+------------------------------------------------------------------+------------------------------------------------------------------+----------+-------------------------------------------------------------------------------+---------------------+---------------------+---------------+------------------------------------------------------------------+----------------------------------+----------------------------------+----------------------------------+
2 rows in set (Elapsed: 00:00:00.00)

gbase> show function status from db1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your GBase server version for the right syntax to use near 'from db1' at line 1
gbase> show function status where db='db1';
+------------------------------------------------------------------+------------------------------------------------------------------+----------+-------------------------------------------------------------------------------+---------------------+---------------------+---------------+------------------------------------------------------------------+----------------------------------+----------------------------------+----------------------------------+
| Db                                                               | Name                                                             | Type     | Definer                                                                       | Modified            | Created             | Security_type | Comment                                                          | character_set_client             | collation_connection             | Database Collation               |
+------------------------------------------------------------------+------------------------------------------------------------------+----------+-------------------------------------------------------------------------------+---------------------+---------------------+---------------+------------------------------------------------------------------+----------------------------------+----------------------------------+----------------------------------+
| db1                                                              | f_now                                                            | FUNCTION | root@%                                                                        | 2020-12-14 14:54:26 | 2020-12-14 14:54:26 | DEFINER       |                                                                  | utf8                             | utf8_general_ci                  | utf8_general_ci                  |
+------------------------------------------------------------------+------------------------------------------------------------------+----------+-------------------------------------------------------------------------------+---------------------+---------------------+---------------+------------------------------------------------------------------+----------------------------------+----------------------------------+----------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase>

show gcluster entry

显示连接数最少的节点信息。

gbase> show gcluster entry;
+------------+------+
| IP         | Port |
+------------+------+
| 10.0.2.201 | 5258 |
+------------+------+
1 row in set (Elapsed: 00:00:00.00)

show gcluster nodes

获得集群所有调度/管理cooridinator节点

gbase> show gcluster nodes;
+------------+------------+--------------+--------+-----------+
| Id         | ip         | name         | status | datastate |
+------------+------------+--------------+--------+-----------+
| 1694629898 | 10.0.2.101 | coordinator1 | online |         0 |
+------------+------------+--------------+--------+-----------+
1 row in set (Elapsed: 00:00:00.00)

show grants

显示用户的授权情况。

gbase> show grants;
+-----------------------------------------------------------------------------+
| Grants for root@%                                                           |
+-----------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION TASK_PRIORITY 2 |
+-----------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> show grants for abc;
+-------------------------------------------------------------------------------------------------------------------------+
| Grants for abc@%                                                                                                        |
+-------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'abc'@'%' IDENTIFIED BY PASSWORD '*0D3CED9BEC10A777AEC23CCC353A8C08A633045E' WITH TASK_PRIORITY 2 |
| GRANT ALL PRIVILEGES ON "abc".* TO 'abc'@'%'                                                                            |
| GRANT ALL PRIVILEGES ON "testdb".* TO 'abc'@'%'                                                                         |
+-------------------------------------------------------------------------------------------------------------------------+
3 rows in set (Elapsed: 00:00:00.00)

show indexs

查看索引。目前express表只有global hash索引和grouped的行存列两种。

gbase> create index idx_v2 on t2(v2) key_block_size=4096 using hash global;
Query OK, 0 rows affected (Elapsed: 00:00:01.05)
Records: 0  Duplicates: 0  Warnings: 0

gbase> alter table t2 add grouped grp_234(v2,v3,v4);
Query OK, 1 row affected (Elapsed: 00:00:00.51)
Records: 1  Duplicates: 1  Warnings: 0

gbase> show index from testdb.t2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+-------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type  | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+-------------+---------+
| t2    |          1 | idx_v2   |            1 | v2          | NULL      |        NULL |     NULL | NULL   | YES  | GLOBAL HASH |         |
| t2    |          1 | grp_234  |            1 | v2          | NULL      |        NULL |     NULL | NULL   | YES  | GROUPED     |         |
| t2    |          1 | grp_234  |            2 | v3          | NULL      |        NULL |     NULL | NULL   | YES  | GROUPED     |         |
| t2    |          1 | grp_234  |            3 | v4          | NULL      |        NULL |     NULL | NULL   | YES  | GROUPED     |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+-------------+---------+
4 rows in set (Elapsed: 00:00:00.00)

show nodes

显示计算节点的信息;

gbase> show nodes;
+------------+------------+-------+--------------+----------------+--------+-----------+
| Id         | ip         | name  | primary part | duplicate part | status | datastate |
+------------+------------+-------+--------------+----------------+--------+-----------+
| 3389128714 | 10.0.2.202 | node2 | n1           | n2             | online |         0 |
| 3372351498 | 10.0.2.201 | node1 | n2           | n1             | online |         0 |
+------------+------------+-------+--------------+----------------+--------+-----------+
2 rows in set (Elapsed: 00:00:00.00)

gbase> show gcluster nodes;
+------------+------------+--------------+--------+-----------+
| Id         | ip         | name         | status | datastate |
+------------+------------+--------------+--------+-----------+
| 3372351498 | 10.0.2.201 | coordinator1 | online |         0 |
+------------+------------+--------------+--------+-----------+
1 row in set (Elapsed: 00:00:00.00)

show open tables

gbase> show open tables;
+------------+-------------------+--------+-------------+
| Database   | Table             | In_use | Name_locked |
+------------+-------------------+--------+-------------+
| testdb     | t1                |      0 |           0 |
| testdb     | t2                |      0 |           0 |
| gbase      | event             |      0 |           0 |
| gclusterdb | audit_log_express |      0 |           0 |
+------------+-------------------+--------+-------------+
4 rows in set (Elapsed: 00:00:00.00)
gbase> show open tables from testdb;
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| testdb   | t1    |      0 |           0 |
| testdb   | t2    |      0 |           0 |
+----------+-------+--------+-------------+
2 rows in set (Elapsed: 00:00:00.00)

gbase> show open tables from testdb where "table" like '%1%' and in_use=0;
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| testdb   | t1    |      0 |           0 |
+----------+-------+--------+-------------+
1 row in set (Elapsed: 00:00:00.00)

SHOW PRIORITIES

显示资源管控的当前默认优先级。

gbase> SHOW PRIORITIES ;
+-----------+-------+----------+-----------------+--------+------------------------------------------------------------------------------------------------------------------------+
| node_name | group | priority | priority_weight | status | description                                                                                                            |
+-----------+-------+----------+-----------------+--------+------------------------------------------------------------------------------------------------------------------------+
| node1     |     0 |        0 |              20 | OFF    | control group path: "gbase/gbase/ugroup0/upriority0 "   parameters:  " cpuset=  memset=  cpu_shares=  blkio_weight= "  |
| node1     |     0 |        1 |              40 | OFF    | control group path: "gbase/gbase/ugroup0/upriority1 "   parameters:  " cpuset=  memset=  cpu_shares=  blkio_weight= "  |
| node1     |     0 |        2 |              60 | OFF    | control group path: "gbase/gbase/ugroup0/upriority2 "   parameters:  " cpuset=  memset=  cpu_shares=  blkio_weight= "  |
| node1     |     0 |        3 |              80 | OFF    | control group path: "gbase/gbase/ugroup0/upriority3 "   parameters:  " cpuset=  memset=  cpu_shares=  blkio_weight= "  |

........... 忽略

| node2     |    15 |        2 |              60 | OFF    | control group path: "gbase/gbase/ugroup15/upriority2 "   parameters:  " cpuset=  memset=  cpu_shares=  blkio_weight= " |
| node2     |    15 |        3 |              80 | OFF    | control group path: "gbase/gbase/ugroup15/upriority3 "   parameters:  " cpuset=  memset=  cpu_shares=  blkio_weight= " |
+-----------+-------+----------+-----------------+--------+------------------------------------------------------------------------------------------------------------------------+
128 rows in set (Elapsed: 00:00:00.00)

show procedure status

显示自定义存储过程的列表。

gbase> delimiter //
gbase> create procedure p_now() begin select now(); end;//
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

gbase> delimiter ;
gbase> call p_now();
+---------------------+
| now()               |
+---------------------+
| 2020-12-14 14:58:13 |
+---------------------+
1 row in set (Elapsed: 00:00:00.01)

Query OK, 0 rows affected (Elapsed: 00:00:00.01)

gbase> show procedure status;
+------------------------------------------------------------------+------------------------------------------------------------------+-----------+-------------------------------------------------------------------------------+---------------------+---------------------+---------------+------------------------------------------------------------------+----------------------------------+----------------------------------+----------------------------------+
| Db                                                               | Name                                                             | Type      | Definer                                                                       | Modified            | Created             | Security_type | Comment                                                          | character_set_client             | collation_connection             | Database Collation               |
+------------------------------------------------------------------+------------------------------------------------------------------+-----------+-------------------------------------------------------------------------------+---------------------+---------------------+---------------+------------------------------------------------------------------+----------------------------------+----------------------------------+----------------------------------+
| db1                                                              | p_now                                                            | PROCEDURE | root@%                                                                        | 2020-12-14 14:58:05 | 2020-12-14 14:58:05 | DEFINER       |                                                                  | utf8                             | utf8_general_ci                  | utf8_general_ci                  |
+------------------------------------------------------------------+------------------------------------------------------------------+-----------+-------------------------------------------------------------------------------+---------------------+---------------------+---------------+------------------------------------------------------------------+----------------------------------+----------------------------------+----------------------------------+
1 row in set (Elapsed: 00:00:00.00)

show processlist

显示进程信息。请参考 GBase8a 显示集群正在跑的SQL进程show [full | detail] processlist

show schemas

show databases完全相同,显示数据库。

show status

显示运行状态信息。参考 GBase 8a集群查看当前运行状态,内存使用情况

show table locks

显示表的节点锁使用情况。用于在计算节点gnode查看锁。

参考 GBase 8a集群查看gnode计算节点某张表的锁

show table status

显示表的当前详细状态,包括磁盘占用等。数据来源于information_schema.tables的元数据表。

提示:如果表很多,比如几万以上,不建议不带条件,避免磁盘大量随机读取。

gbase> use db1;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

gbase>
gbase> show table status;
+-------------------+---------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+------------------+--------------------+--------------+-----------------+---------+----------------------------+-----------------------------+
| Name              | Engine  | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options   | Limit_storage_size | storage_size | table_data_size | Comment | local_hash_index_file_size | global_hash_index_file_size |
+-------------------+---------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+------------------+--------------------+--------------+-----------------+---------+----------------------------+-----------------------------+
| db1_t1            | EXPRESS |      10 | Compressed |    0 |              0 |           0 |               0 |            0 |         0 |           NULL | 2020-12-14 08:28:45 | 2020-12-14 08:28:45 | NULL       | utf8_general_ci |     NULL | avg_row_length=5 |                  0 |          414 |               0 |         |                          0 |                           0 |
| v_t1              | NULL    |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | NULL                | NULL                | NULL       | NULL            |     NULL | NULL             |               NULL |         NULL |            NULL | VIEW    |                       NULL |                        NULL |
| v_t1_178291728712 | NULL    |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | NULL                | NULL                | NULL       | NULL            |     NULL | NULL             |               NULL |         NULL |            NULL | VIEW    |                       NULL |                        NULL |
+-------------------+---------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+------------------+--------------------+--------------+-----------------+---------+----------------------------+-----------------------------+
3 rows in set (Elapsed: 00:00:00.00)

gbase> show table status from db1;
+-------------------+---------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+------------------+--------------------+--------------+-----------------+---------+----------------------------+-----------------------------+
| Name              | Engine  | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options   | Limit_storage_size | storage_size | table_data_size | Comment | local_hash_index_file_size | global_hash_index_file_size |
+-------------------+---------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+------------------+--------------------+--------------+-----------------+---------+----------------------------+-----------------------------+
| db1_t1            | EXPRESS |      10 | Compressed |    0 |              0 |           0 |               0 |            0 |         0 |           NULL | 2020-12-14 08:28:45 | 2020-12-14 08:28:45 | NULL       | utf8_general_ci |     NULL | avg_row_length=5 |                  0 |          414 |               0 |         |                          0 |                           0 |
| v_t1              | NULL    |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | NULL                | NULL                | NULL       | NULL            |     NULL | NULL             |               NULL |         NULL |            NULL | VIEW    |                       NULL |                        NULL |
| v_t1_178291728712 | NULL    |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | NULL                | NULL                | NULL       | NULL            |     NULL | NULL             |               NULL |         NULL |            NULL | VIEW    |                       NULL |                        NULL |
+-------------------+---------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+------------------+--------------------+--------------+-----------------+---------+----------------------------+-----------------------------+
3 rows in set (Elapsed: 00:00:00.01)

gbase> show table status from db1 like '%1%';
+-------------------+---------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+------------------+--------------------+--------------+-----------------+---------+----------------------------+-----------------------------+
| Name              | Engine  | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options   | Limit_storage_size | storage_size | table_data_size | Comment | local_hash_index_file_size | global_hash_index_file_size |
+-------------------+---------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+------------------+--------------------+--------------+-----------------+---------+----------------------------+-----------------------------+
| db1_t1            | EXPRESS |      10 | Compressed |    0 |              0 |           0 |               0 |            0 |         0 |           NULL | 2020-12-14 08:28:45 | 2020-12-14 08:28:45 | NULL       | utf8_general_ci |     NULL | avg_row_length=5 |                  0 |          414 |               0 |         |                          0 |                           0 |
| v_t1              | NULL    |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | NULL                | NULL                | NULL       | NULL            |     NULL | NULL             |               NULL |         NULL |            NULL | VIEW    |                       NULL |                        NULL |
| v_t1_178291728712 | NULL    |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | NULL                | NULL                | NULL       | NULL            |     NULL | NULL             |               NULL |         NULL |            NULL | VIEW    |                       NULL |                        NULL |
+-------------------+---------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+------------------+--------------------+--------------+-----------------+---------+----------------------------+-----------------------------+
3 rows in set (Elapsed: 00:00:00.00)

gbase> show table status from db1 where name like '%1%' and comment='VIEW';
+-------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+--------------------+--------------+-----------------+---------+----------------------------+-----------------------------+
| Name              | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Limit_storage_size | storage_size | table_data_size | Comment | local_hash_index_file_size | global_hash_index_file_size |
+-------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+--------------------+--------------+-----------------+---------+----------------------------+-----------------------------+
| v_t1              | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | NULL        | NULL        | NULL       | NULL      |     NULL | NULL           |               NULL |         NULL |            NULL | VIEW    |                       NULL |                        NULL |
| v_t1_178291728712 | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | NULL        | NULL        | NULL       | NULL      |     NULL | NULL           |               NULL |         NULL |            NULL | VIEW    |                       NULL |                        NULL |
+-------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+--------------------+--------------+-----------------+---------+----------------------------+-----------------------------+
2 rows in set (Elapsed: 00:00:00.00)

查看某个表的当前状态

gbase> show table status where name='t1'\G;
*************************** 1. row ***************************
                       Name: t1
                     Engine: EXPRESS
                    Version: 10
                 Row_format: Compressed
                       Rows: 0
             Avg_row_length: 0
                Data_length: 0
            Max_data_length: 0
               Index_length: 0
                  Data_free: 0
             Auto_increment: NULL
                Create_time: 2021-01-25 09:44:04
                Update_time: NULL
                 Check_time: NULL
                  Collation: utf8_general_ci
                   Checksum: NULL
             Create_options: avg_row_length=5
         Limit_storage_size: 0
               storage_size: 285
            table_data_size: 0
                    Comment:
 local_hash_index_file_size: 0
global_hash_index_file_size: 0
            tablespace_name: NULL
            tablespace_path: NULL
1 row in set (Elapsed: 00:00:00.00)

show tables

查看表名字。 包括 show full tables

提示:如果表很多,比如几万以上,不建议不带条件,避免磁盘大量随机读取。

gbase> use db1;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

gbase> show tables;
+-------------------+
| Tables_in_db1     |
+-------------------+
| db1_t1            |
| v_t1              |
| v_t1_178291728712 |
+-------------------+
3 rows in set (Elapsed: 00:00:00.00)

gbase> show full tables;
+-------------------+------------+
| Tables_in_db1     | Table_type |
+-------------------+------------+
| db1_t1            | BASE TABLE |
| v_t1              | VIEW       |
| v_t1_178291728712 | VIEW       |
+-------------------+------------+
3 rows in set (Elapsed: 00:00:00.00)

gbase> show full tables like '%t1%';
+----------------------+------------+
| Tables_in_db1 (%t1%) | Table_type |
+----------------------+------------+
| db1_t1               | BASE TABLE |
| v_t1                 | VIEW       |
| v_t1_178291728712    | VIEW       |
+----------------------+------------+
3 rows in set (Elapsed: 00:00:00.00)

gbase> show full tables where tables_in_db1 like '%t1%';
+-------------------+------------+
| Tables_in_db1     | Table_type |
+-------------------+------------+
| db1_t1            | BASE TABLE |
| v_t1              | VIEW       |
| v_t1_178291728712 | VIEW       |
+-------------------+------------+
3 rows in set (Elapsed: 00:00:00.00)

gbase> show full tables where tables_in_db1 like '%t1%' and table_type='VIEW';
+-------------------+------------+
| Tables_in_db1     | Table_type |
+-------------------+------------+
| v_t1              | VIEW       |
| v_t1_178291728712 | VIEW       |
+-------------------+------------+
2 rows in set (Elapsed: 00:00:00.00)

gbase> show full tables where tables_in_db1 like '%t1%' and table_type='view';
+-------------------+------------+
| Tables_in_db1     | Table_type |
+-------------------+------------+
| v_t1              | VIEW       |
| v_t1_178291728712 | VIEW       |
+-------------------+------------+
2 rows in set (Elapsed: 00:00:00.00)

show tablespaces

查看表空间,从V95版本才支持。

gbase> use testdb;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)

gbase> show tablespaces;
+----------------------+
| Tablespace_in_testdb |
+----------------------+
| sys_tablespace       |
+----------------------+
1 row in set (Elapsed: 00:00:00.00)


gbase> create tablespace tablespace_ssd datadir '/opt/gbase/ssd';
Query OK, 0 rows affected (Elapsed: 00:00:00.01)

gbase> show tablespaces;
+----------------------+
| Tablespace_in_testdb |
+----------------------+
| sys_tablespace       |
| tablespace_ssd       |
+----------------------+
2 rows in set (Elapsed: 00:00:00.00)

show variables

显示参数设置。参考 GBase8a 集群查看数据库参数的当前值

show vcs

显示VC列表。 V95支持vC的版本才有。

gbase> show vcs;
+---------+--------------+---------+
| id      | name         | default |
+---------+--------------+---------+
| vc00001 | vcname000001 | Y       |
+---------+--------------+---------+
1 row in set (Elapsed: 00:00:00.00)

show warnings

显示告警信息

gbase> show warnings;
Empty set (Elapsed: 00:00:00.00)

gbase> show warnings limit 3,1;
Empty set (Elapsed: 00:00:00.00)

show views

显示视图。当前版本尚不支持这个写法,但可以通过如下方法获得

show full tables where table_type='VIEW'

gbase> create view testdb.v_tr as select * from tr;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

gbase> show full tables where table_type='VIEW';
+------------------+------------+
| Tables_in_testdb | Table_type |
+------------------+------------+
| v_tr             | VIEW       |
+------------------+------------+
1 row in set (Elapsed: 00:00:00.00)

详情请参考 GBase8a 集群查看库里有哪些视图(类似 show views)