11.GaussDB普通表的管理

1.创建行存表

sjzt=> create table emp(id int,ename varchar(20),sal number,empno int,deptno int) distribute by hash(empno);
CREATE TABLE
insert into emp values(1,'xsq1',2000,6379,10);
sjzt=> create table dept(deptno int,name varchar(20));
CREATE TABLE
sjzt=> create table emp1 as select * from emp where sal<2000;
INSERT 0 0
sjzt=> create table emp2 as table emp;
INSERT 0 0

2.创建表并使用特定表空间

-- 表不存在时才创建，使得当该表存在时该建表语句不会报错
 -- 事务结束时检查字段是否有重复
 -- 缺省值为'GA'
 --需要给用户授予表空间的CREATE权限,才可以在表空间中创建对象。
gaussdb=> grant create on tablespace tbs3 to sjzt;
GRANT

sjzt=> CREATE TABLE IF NOT EXISTS warehouse_t1 
(
W_WAREHOUSE_SK INTEGER NOT NULL,
W_WAREHOUSE_ID CHAR(16) NOT NULL,
W_WAREHOUSE_NAME VARCHAR(20) UNIQUE DEFERRABLE,
W_SUITE_NUMBER CHAR(10),
W_STATE CHAR(2) DEFAULT 'GA', 
W_GMT_OFFSET DECIMAL(5,2)
) TABLESPACE tbs3;
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "warehouse_t1_w_warehouse_name_key" for table "warehouse_t1"
CREATE TABLE
-- 创建非日志表，即不写入预写日志
-- 主键约束,-- 唯一键约束,-- 检查列约束
sjzt=> 
CREATE UNLOGGED TABLE warehouse_t2  
(
W_WAREHOUSE_SK INTEGER PRIMARY KEY, 
W_WAREHOUSE_ID CHAR(16) NOT NULL,
W_WAREHOUSE_NAME VARCHAR(20) UNIQUE, 
W_GMT_OFFSET DECIMAL(5,2) ,
CONSTRAINT W_CONSTR_KEY2 CHECK(W_WAREHOUSE_SK > 0 AND W_WAREHOUSE_NAME IS NOT NULL) 
) WITH(fillfactor=70);

NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "warehouse_t2_pkey" for table "warehouse_t2"
NOTICE:  CREATE TABLE / UNIQUE will create implicit global secondary index "warehouse_t2_w_warehouse_name_key_g" for table "warehouse_t2"
CREATE TABLE

3.列存表创建

--列存表创建：
 -- 局部聚簇存储
 -- 带有压缩特性的列存储表(列存储表不支持约束)
sjzt=> CREATE TABLE warehouse_t3
(
W_WAREHOUSE_SK INTEGER NOT NULL,
W_WAREHOUSE_ID CHAR(16) NOT NULL,
W_WAREHOUSE_NAME VARCHAR(20),
W_GMT_OFFSET DECIMAL(5,2),
PARTIAL CLUSTER KEY(W_WAREHOUSE_SK, W_WAREHOUSE_ID)
) WITH (ORIENTATION = COLUMN, COMPRESSION=HIGH);  
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'w_warehouse_sk' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE

4.修改表的属性

--修改表属性:
--WHERE条件创建表。
sjzt=> create table emp1 as select * from emp where sal<2000;
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'id' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
INSERT 0 0
--AS 创建表。
sjzt=> create table emp2 as table emp;
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'id' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
INSERT 0 1
--修改列属性。
sjzt=> alter table emp1 modify sal number(10,2);
ALTER TABLE
--重命名列名称。
sjzt=> alter table emp1 rename column ename to name;
ALTER TABLE
 --无法直接创建主键,必须保护分布列。emp1的分布列是ID;
sjzt=> alter table emp1 add primary key (empno);
ERROR:  Cannot create index whose evaluation cannot be enforced to remote nodes
sjzt=> \d+ emp1
                                 Table "sjzt.emp1"
 Column |         Type          | Modifiers | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+--------------+-------------
 id     | integer               |           | plain    |              | 
 name   | character varying(20) |           | extended |              | 
 sal    | numeric(10,2)         |           | main     |              | 
 empno  | integer               |           | plain    |              | 
 deptno | integer               |           | plain    |              | 
Has OIDs: no
Distribute By: HASH(id)
Location Nodes: ALL DATANODES
Options: orientation=row, compression=no

--添加主键,需要增加分布列。
sjzt=> alter table emp1 add primary key (empno,id);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "emp1_pkey" for table "emp1"
ALTER TABLE
--添加检查约束。
sjzt=>  alter table emp1 add constraint chk_dept check (deptno is not null);
ALTER TABLE

--GaussDB目前不支持外键约束。
sjzt=>  alter table emp1 add constraint fk_dept foreign key (deptno) references dept(deptno);
ERROR:  FOREIGN KEY ... REFERENCES constraint is not yet supported.
--设置sal列为非空约束。
sjzt=> alter table emp1 modify sal constraint chk_sal not null; 
ALTER TABLE
--重命名约束名称
sjzt=> alter table emp1 rename constraint chk_dept to chk_deptno;
ALTER TABLE
--重置schema;
sjzt=> alter table emp1 set schema mydb1;
ALTER TABLE
--表重命名
sjzt=> alter table mydb1.emp1 rename to emp2;
ALTER TABLE
sjzt=> \d mydb1.emp2
             Table "mydb1.emp2"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | integer               | not null
 name   | character varying(20) | 
 sal    | numeric(10,2)         | not null
 empno  | integer               | not null
 deptno | integer               | 
Indexes:
    "emp1_pkey" PRIMARY KEY, btree (empno, id) TABLESPACE pg_default
Check constraints:
    "chk_deptno" CHECK (deptno IS NOT NULL)

5.总结

（1）使用表空间需要有对表空间的CREATE权限。

（2）GaussDB MYSQL兼容模式不支持外键。

（3）GaussDB 支持列存和行存表。