GaussDB数据库对象管理_命令行查看opengaussdb数据库的兼容模式-CSDN博客

本文链接：https://blog.csdn.net/Sebastien23/article/details/143641848

GaussDB数据库对象管理

数据库
模式
用户和角色
表
索引
视图
序列

数据库

创建数据库：

create database testdb
with owner=wolong           --数据库所属用户
encoding='UTF8'             --字符集编码
LC_COLLATE='zh_CN.UTF-8'    --排序规则
LC_CTYPE='zh_CN.UTF-8'      --字符类型
DBCOMPABILITY='A'           --兼容模式
CONNECTION LIMIT=1000;      --连接限制

查看现有的数据库：

\l

模式

模式（Schema）是一组数据库对象的逻辑集合。

检查搜索路径：

show search_path;

查看现有模式：

\dn

管理模式：

create schema mysch authorization wolong;  --创建schema并授权给用户wolong

alter schema mysch rename to ursch;        --重命名schema
alter schema ursch owner to caocao;        --变更schema所属用户

drop schema ursch;                         --删除schema

用户和角色

用户主要用来连接数据库、创建和访问数据库对象、以及执行SQL语句。
角色通常用来组织和管理权限。一个用户可以继承多个不同角色的权限。如果修改角色权限，继承该角色的用户权限会自动修改。
GaussDB中用户和角色的区别只在于角色默认是没有LOGIN登录权限的。

创建用户和角色：

create role r1 with createdb password 'xxxxx';              --角色r1具有createdb权限
create role r2 with login createdb identified by 'xxxxxx';   --角色r2具有login和createdb权限

create user u1 with login password 'xxxxxx';
create user u2 with login createdb identified by 'xxxxxx';

查看用户和角色：

\dg
\du

gaussdb=> \du
                          List of roles
  Role name   |             Attributes              |           Member of              
--------------+-------------------------------------+-----------------------------------
 r1           | Create DB, Cannot login             | {}
 r2           | Create DB                           | {}
 u1           |                                     | {}
 u2           | Create DB                           | {}

修改用户和角色：

alter role r2 with createrole;    --已有的createdb权限不受影响
alter user u2 with createrole;    --已有的createdb权限不受影响

🐯 WITH后面可修改的权限包括：SYSADMIN/NOSYSADMIN、AUDITADMIN/NOAUDITADMIN、CREATEDB/NOCREATEDB、CREATEROLE/NOCREATEROLE、LOGIN/NOLOGIN、REPLICATION/NOREPLICATION、VCADMIN/NOVCADMIN等。

角色授权和移除权限：

grant r1 to u1;
grant r1 to u1 with admin option;  --授权角色并赋予转授权限

revoke r1 from u1;                 --移除角色，不影响直接授予的权限
revoke r1 from u1 retrict;
revoke r1 from u1 cascade;         --级联移除权限

revoke admin option for r1 from u1 restrict;   --仅移除转授权限，不移除角色权限
revoke admin option for r1 from u1 cascade;

删除用户和角色：

drop role if exists r2;
drop user if exists u2;

表

表字段约束只支持NULL、NOT NULL和DEFAULT常量值。

查看表信息：

\dt
\d mysch.t1

select tablename from pg_tables where schemaname='mysch';

普通表

创建表：

create table if not exists t1 (
w_num integer not null,
w_id char(16) not null,
w_name varchar(20) unique,
w_num char(10),
w_state char(2) default 'NY',
w_offset decimal(5,2)
) with (fillfactor=70);

create table t2 as select * from emp where age>35;   --CTAS方式建表

变更表：

alter table t1 modify salary number(6,2);   --变更字段属性
alter table t1 rename column c1 to col1;    --重命名字段

alter table t1 add primary key(empno);                                               --添加主键
alter table t1 add constraint ck_dept check(deptno) is not null;                     --添加CHECK约束
alter table t1 add constraint fk_dept foreign key (deptno) references dept(deptno);  --添加外键约束

alter table t1 modify salary constraint ck_sal not null;   --修改字段约束条件

分区表

GaussDB中行存表支持范围分区、列表分区、哈希分区。列存表仅支持范围分区。

创建分区表：

create table pt1 (
  addr_sk integer not null,
  addr_id character(16) not null,
  st_number character(10)
)
partition by range(addr_sk)
(
  partition p1 values less than(5000),
  partition p2 values less than(10000),
  partition p3 values less than(15000),
  partition p4 values less than(20000),
  partition pmax values less than(maxvalue)
) enable row movement;

修改分区表：

alter table pt1 rename partition p1 to pmin;            --重命名分区
alter table pt1 move partition pmax tablespace tbs2;    --修改分区使用的表空间

alter table pt1 split partition p3 at(12000) into (partition p31,partition p32);   --拆分分区
alter table pt1 merge partitions p31,p32 into partition p3;                        --合并分区

alter table pt1 exchange partition (p2) with table t2;  --普通表与分区表交换数据

增加或删除分区：

--删除分区
alter table pt1 drop partition p4;
alter table pt1 drop partition for(16000);

--新增分区
alter table pt1 add partition p5 values less than (25000);
alter table pt1 add partition p6 values less than (maxvalue);

分布式表

略。

索引

GaussDB行存表支持以下索引类型：

btree索引：使用一种类似于B+树的结构来存储数据的键值。Btree索引适合比较点查询和范围查询。
gist索引：适用于几何和地理等多维数据类型、集合数据类型。
gin索引：倒排索引，可以处理包含多个键的值，例如数组。
ubtree索引：仅供Ustore存储引擎使用的多版本Btree索引。索引上包含事务信息，能自主回收数据页。

创建索引：

create unique index idx_t1_sal on t1(salary);                   --创建唯一索引
create index idx_t1_comp on t1(id,name);                        --创建复合索引
create index idx_t1_part on t1(relnum) where relnum<1000;       --创建部分索引
create index idx_t1_func on t1(upper(name));                    --创建函数索引

create index idx_pt1_local on pt1(id) local;                    --创建分区表的本地索引
create index idx_pt1_glob on pt1(score) global tablespace tbs2; --创建分区表的全局索引

变更索引：

alter index idx1 rename to idx2;        --重命名索引
alter index idx2 set tablespace tbs2;   --修改索引存储的表空间

alter index idx2 unusable;              --索引标记为不可用
alter index idx2 rebuild;               --重建索引

删除索引：

drop index idx1;

视图

创建视图：

create view v1 as select * from pg_tablespace where spcname='pg_default';
create materialized view vm1 as select * from pg_tablespace where spcname='pg_default';   --创建物化视图

查询视图定义：

select pg_get_viewdef('v1');

管理变更视图：

alter view v1 rename to v2;       --重命名视图
alter view v2 owner to wolong;    --修改视图属主
alter view v2 set schema mysch;   --修改视图所属模式

refresh materialized view vm1;    --更新物化视图

删除视图：

drop view mysch.v2;
drop materialized view vm1;

🦁 关于物化视图：

物化视图将视图查询结果存储在磁盘中，可以有效提高查询性能。
物化视图的数据更新需要通过REFRESH从基表获取更新的数据。

序列

创建序列：

create sequence seq01
increment by 1                --每次递增/递减的步伐
minvalue 1 maxvalue 10000     --指定最小和最大值
start with 1;                 --指定初始值

查看序列属性：

gaussdb=> \d seq01
     Sequence "public.seq01"
    Column     |  Type   | Value  
---------------+---------+--------
 sequence_name | name    | seq01
 last_value    | bigint  | 1
 start_value   | bigint  | 1
 increment_by  | bigint  | 1
 max_value     | bigint  | 10000
 min_value     | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt       | bigint  | 0
 is_cycled     | boolean | f
 is_called     | boolean | f
 uuid          | bigint  | 0

使用序列：

select nextval('seq01');    --获取序列的下一个值
select seq01.nextval;

select currval('seq01');    --获取序列的当前值
select seq01.currval;

select lastval();           --最近一次nextval返回的值
select setval('seq01',1);   --设置序列的当前值

修改序列：

alter sequence seq01 maxvalue 99999;    --修改序列最大值

alter sequence seq01 owner to wolong;   --修改序列属主

删除序列：

drop sequence seq01;

⭐️ 使用序列使得表字段成为唯一标识符的两种方法：

将字段的类型声明为序列整型（smallserial、serial或bigserial）。由数据库在后台自动创建一个对应的序列。

create table tab1(id serial,name varchar(20),tag int);
insert into tab1 values (default,'Liubei');
insert into tab1 values (default,'Guanyu');

gaussdb=> select * from tab1;
 id |  name  | tag 
----+--------+-----
  1 | Liubei |    
  2 | Guanyu |

使用CREATE SEQUENCE创建一个新的序列，然后将nextval('seqName')指定为字段的默认值。

alter table tab1 alter tag set default nextval('seq01');
insert into tab1 values (default,'Zhangfei');
insert into tab1 values (default,'Zhaoyun');

gaussdb=> select * from tab1;
 id |   name   | tag 
----+----------+-----
  1 | Liubei   |    
  2 | Guanyu   |    
  3 | Zhangfei |   1
  4 | Zhaoyun  |   2