说明：部分语句截取至华为官方文档：https://support.huaweicloud.com/gaussdb/index.html

用户相关

创建用户并授权
create user u1 password 'awdrg@123!'; alter user u1 with dbadmin auditadmin poladmin;

锁定/解锁用户
alter user u1 account LOCK/UNLOCK;

查看指定用户已使用的会话连接数
select count(*) from dv_sessions where username='xxx';

查看所有用户已使用的会话连接数
select count(*) from dv_sessions;

查询指定用户的最大连接数
select rolname,rolconnlimit from pg_roles where rolname='user';

创建防篡改用户
create schema ledger with blockchain;

数据库相关

创建数据库
create database d1;

创建数据库并指定所有者
create database d1 owner u1;

重命名数据库
alter database d1 rename to db1;

查询指定数据库的最大连接数
select datname,datconnlimit from pg_database where datname='db';

表相关

以下示例部分引用华为官方文档
创建表
create table t1 ORIENTATION=row/column DISTRIBUTE BY HASH/REPLICATION/RANGE/LIST

创建分区表
create table t1 create table p_test ( a int,b int,c int,d int ) partition by range(b) ( partition p1 values less than (10), partition p2 values less than (20), partition p3 values less than (30), partition p4 values less than maxvalue );

create table p_test ( a int,b int,c int,d int )with (orientation = column) distribute by hash(a) partition by hash(b) ( partition p1, partition p2 );

CREATE TABLE test_list (col1 int, col2 int) partition by list(col1) ( partition p1 values (2000), partition p2 values (3000), partition p3 values (4000), partition p4 values (5000) );

创建加密表
CREATE TABLE creditcard_info (id_number int, name text encrypted with (column_encryption_key = ImgCEK, encryption_type = DETERMINISTIC), credit_card varchar(19) encrypted with (column_encryption_key = ImgCEK1, encryption_type = DETERMINISTIC)); NOTICE: The 'DISTRIBUTE BY' clause is not specified. Using 'id_number' as the distribution column by default. HINT: Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column. CREATE TABLE

修改表
添加列
ALTER TABLE tpcds.warehouse_t19 ADD W_GOODS_CATEGORY varchar(30);
添加列及约束
ALTER TABLE tpcds.warehouse_t19 ADD CONSTRAINT W_CONSTR_KEY4 CHECK (W_STATE IS NOT NULL);

修改字段类型
ALTER TABLE tpcds.warehouse_t19 ALTER COLUMN W_GOODS_CATEGORY TYPE varchar(80), ALTER COLUMN W_STREET_NAME TYPE varchar(100);

给一个已存在字段添加非空约束。
ALTER TABLE tpcds.warehouse_t19 ALTER COLUMN W_GOODS_CATEGORY SET NOT NULL;

移除已存在字段的非空约束。
ALTER TABLE tpcds.warehouse_t19 ALTER COLUMN W_GOODS_CATEGORY DROP NOT NULL;

将表移动到另一个表空间。
ALTER TABLE tpcds.warehouse_t19 SET TABLESPACE PG_DEFAULT;

将表移动到另一个模式中。
gaussdb=# ALTER TABLE tpcds.warehouse_t19 SET SCHEMA joe;

重命名已存在的表。
ALTER TABLE joe.warehouse_t19 RENAME TO warehouse_t23;

从表中删除一个字段。
ALTER TABLE joe.warehouse_t23 DROP COLUMN W_STREET_NAME;

删除表空间。
DROP TABLE tpcds.warehouse_t1;

获取表的模式名和表名
select schemaname ,tablename from pg_tables where tablename='test';

select table_schema ,table_name from information_schema.tables where table_name ='test';

select pn.nspname,pc.relname from pg_class pc , pg_namespace pn where pc.relnamespace=pn.oid and pc.relname='test';
获取指定表所在的节点名称

select node_name from pgxc_node where oid in ( select unnest(nodeoids) from pgxc_class where pcrelid ='test'::regclass );

获取指定表的所在的节点名字和表在节点上的数据数量
SELECT b.node_name, a.cnt FROM (SELECT xc_node_id,count(*) AS cnt FROM p_test GROUP BY xc_node_id) a pgxc_node b WHERE a.xc_node_id=b.node_id ORDER BY a.cnt desc;

获取指定表的owner
select c.relname,r.rolname as owner from pg_class c join pg_roles r on c.relowner=r.oid where relname='t1';
select schemaname,tablename tab,tableowner from pg_tables where tablename='t1';

索引相关

创建索引
create index idx1 on t1(l1,l2) where (l1=xx and l2 > xxx)

禁用索引
alter index idx1 unusable;

重建索引
alter index idx1 rebuild;

重命名索引
alter index idx1 rename to idx2;

权限相关

获取某个用户的表级权限
select table_schema,table_name,grantee,privilege_type from information_schema.table_privileges where grantee='user1';

获取对某表有权限的用户信息
select grantee,privilege_type from information_schema.table_privileges where table_name='table1';