GaussDB数据库开发设计之数据库对象与管理

近期在学习GaussDB数据库。GaussDB数据库开发设计系列共包括3个实验：
实验1：数据库对象与管理
实验2：存储过程
实验3：JDBC高可用性

实验1：数据库对象与管理

1.表空间管理

步骤1、登录数据库

gsql -h 172.x.x.xx -d postgres -p 8000 -U root -W '********' -r
连接数据库。其中，数据库的IP及用户密码请根据实际情况替换。

步骤2、创建用户：

CREATE USER user1 WITH PASSWORD '********';
CREATE USER user2 WITH PASSWORD '********';
GRANT ALL PRIVILEGES TO user2;

步骤3、创建表空间：

CREATE TABLESPACE my_tablespace
    OWNER user1
    RELATIVE LOCATION 'test_tablespace/test_tablespace_1';

步骤4、查看表空间：

SELECT * FROM pg_tablespace;
select * from pg_tablespace_location((select oid from pg_tablespace where spcname='my_tablespace'));

步骤5、修改表空间所有者：

ALTER TABLESPACE my_tablespace OWNER TO user2;

步骤6、修改表空间命名

ALTER TABLESPACE my_tablespace RENAME TO my_tb2;

步骤7、删除表空间：

DROP TABLESPACE my_tb2;

2. 普通表管理

普通表是数据库的基本存储结构，用于存储应用程序的数据。

步骤1、创建表：

创建模式hr。

CREATE SCHEMA hr;

设置当前模式为hr。

set search_path = hr;

创建employees表。

CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    name VARCHAR2(50),
    department_id NUMBER,
    position VARCHAR2(50)
);

步骤2、插入数据：

INSERT INTO employees (employee_id, name, department_id, position) VALUES (1, 'john', 1, 'Manager');
INSERT INTO employees (employee_id, name, department_id, position) VALUES (2, 'jane', 2, 'Developer');
INSERT INTO employees (employee_id, name, department_id, position) VALUES (3, 'alice', 1, 'Developer');
INSERT INTO employees (employee_id, name, department_id, position) VALUES (4, 'bob', 2, 'Manager');

步骤3、查询表数据：

SELECT * FROM employees;

步骤4、进行行级访问控制：

create user alice WITH PASSWORD '********';

grant usage on schema hr to alice;
grant select on employees to alice;
alter table employees enable row level security;
create row level security policy employees_rls on employees using(name = current_user);

\c - alice                          
select * from hr.employees;

步骤5、删除普通表：

\c - root
                                  
set search_path = hr;
drop table employees;

3. 分区表管理

分区表将数据分散到多个物理段中，有助于提高查询性能并简化数据管理。

步骤1、使用RANGE 分区策略（按值范围分区）

CREATE TABLE range_emp (
  employee_id NUMBER PRIMARY KEY,
  first_name VARCHAR2(50),
  last_name VARCHAR2(50),
  hire_date DATE
)
PARTITION BY RANGE (hire_date) (
  PARTITION p1 VALUES LESS THAN (TO_DATE('2020-01-01', 'YYYY-MM-DD')),
  PARTITION p2 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD'))
);

步骤2、使用LIST 分区策略（按具体值列表分区）

CREATE TABLE list_emp (
  employee_id NUMBER PRIMARY KEY,
  first_name VARCHAR2(50),
  last_name VARCHAR2(50),
  department_id NUMBER
)
PARTITION BY LIST (department_id) (
  PARTITION p_sales VALUES ('1'),
  PARTITION p_hr VALUES ('2'),
  PARTITION p_it VALUES ('3'),
  PARTITION p_marketing VALUES ('4')
);

步骤3、使用HASH 分区策略（按哈希值分区）

CREATE TABLE hash_emp (
  employee_id NUMBER PRIMARY KEY,
  first_name VARCHAR2(50),
  last_name VARCHAR2(50),
  hire_date DATE
)
PARTITION BY HASH (employee_id) 
PARTITIONS 4;  -- 4个分区

步骤4、查询分区信息

SELECT 
    table_name, 
    partition_name, 
    high_value 
FROM 
    db_tab_partitions 
WHERE 
    table_name = 'range_emp';

步骤5、修改分区表
添加分区：如果需要增加新的分区，可以使用 ALTER TABLE 语句：

ALTER TABLE range_emp
ADD PARTITION p3 VALUES LESS THAN (TO_DATE('2030-01-01', 'YYYY-MM-DD'));

查看当前range_emp表中的分区：

SELECT 
    table_name, 
    partition_name, 
    high_value 
FROM 
    db_tab_partitions 
WHERE 
    table_name = 'range_emp';

拆分分区：如果一个分区的数据量过大，可以通过拆分分区来优化性能：

ALTER TABLE range_emp
SPLIT PARTITION p2 AT (TO_DATE('2023-01-01', 'YYYY-MM-DD'))
INTO (PARTITION p2a, PARTITION p2b);                              

查看当前range_emp表中的分区：

SELECT 
    table_name, 
    partition_name, 
    high_value 
FROM 
    db_tab_partitions 
WHERE 
    table_name = 'range_emp';

合并分区：可以通过合并分区来减少分区数量：

ALTER TABLE range_emp
MERGE PARTITIONS p2b, p3 INTO PARTITION p3;

查看当前range_emp表中的分区：

SELECT 
    table_name, 
    partition_name, 
    high_value 
FROM 
    db_tab_partitions 
WHERE 
    table_name = 'range_emp';

步骤6、 删除分区表
– 删除整个表

DROP TABLE list_emp;
DROP TABLE hash_emp;

– 删除特定分区

ALTER TABLE range_emp DROP PARTITION p1;

查看当前range_emp表中的分区：

SELECT 
    table_name, 
    partition_name, 
    high_value 
FROM 
    db_tab_partitions 
WHERE 
    table_name = 'range_emp';

4. 索引管理

索引加速了查询操作，但过多或不当的索引会影响插入、更新和删除操作的性能。

步骤1、创建索引：

CREATE INDEX idx_last_name ON range_emp(last_name);

步骤2、查看索引：
查询指定索引的信息：

\di+ idx_last_name;

步骤3、查询系统和用户定义的所有索引（返回421行）。

SELECT RELNAME FROM PG_CLASS WHERE RELKIND='i' or RELKIND='I';

步骤4、删除索引：

DROP INDEX idx_last_name;

5. 视图管理

视图是保存的查询结果，可以简化复杂查询的执行。

步骤1、创建视图：

CREATE VIEW employee_full_name AS
SELECT employee_id, first_name || ' ' || last_name AS full_name, hire_date
FROM range_emp;

步骤2、查询视图：

SELECT * FROM employee_full_name;

步骤3、创建物化视图：
因为当前数据库默认使用ustore，而ustore暂不支持创建物化视图，因此，需要创建表employees_atore，使用astore存储方式。

CREATE TABLE employees_atore(
employee_id NUMBER PRIMARY KEY,
  first_name VARCHAR2(50),
  last_name VARCHAR2(50),
  hire_date DATE)
with(orientation=row, storage_type=astore);

插入数据到employees_atore表

INSERT INTO employees_atore (employee_id, first_name, last_name, hire_date)
VALUES (1, 'John', 'Doe', TO_DATE('2020-01-15', 'YYYY-MM-DD'));

INSERT INTO employees_atore (employee_id, first_name, last_name, hire_date)
VALUES (2, 'Jane', 'Smith', TO_DATE('2019-06-30', 'YYYY-MM-DD'));

INSERT INTO employees_atore (employee_id, first_name, last_name, hire_date)
VALUES (3, 'Michael', 'Johnson', TO_DATE('2021-02-20', 'YYYY-MM-DD'));

INSERT INTO employees_atore (employee_id, first_name, last_name, hire_date)
VALUES (4, 'Emily', 'Davis', TO_DATE('2018-10-05', 'YYYY-MM-DD'));

INSERT INTO employees_atore (employee_id, first_name, last_name, hire_date)
VALUES (5, 'James', 'Brown', TO_DATE('2022-08-12', 'YYYY-MM-DD'));

创建物化视图：

CREATE MATERIALIZED VIEW emp_atore_full_name AS
SELECT employee_id, first_name || ' ' || last_name AS full_name, hire_date
FROM employees_atore;

步骤4、查询物化视图：

SELECT * FROM emp_atore_full_name;

步骤5、修改表employees_atore数据：
修改员工 employee_id 为 3 的 hire_date为2025年1月1日：

UPDATE employees_atore
SET hire_date = TO_DATE('2025-01-01', 'YYYY-MM-DD')
WHERE employee_id = 3;

查询物化视图，发现结果并没有改变。

SELECT * FROM emp_atore_full_name;

步骤6、刷新物化视图：

REFRESH MATERIALIZED VIEW emp_atore_full_name;

再次查询

SELECT * FROM emp_atore_full_name;

发现结果变化

步骤7、删除视图：

DROP VIEW employee_full_name;
DROP MATERIALIZED VIEW emp_atore_full_name;

6.序列管理

序列用于自动生成唯一的数值，通常用于主键字段。

步骤1、创建序列：

CREATE SEQUENCE employee_id_seq
START WITH 1000
INCREMENT BY 1;

步骤2、使用序列插入数据：

INSERT INTO range_emp (employee_id, first_name, last_name, hire_date)
VALUES (employee_id_seq.NEXTVAL, 'Alice', 'Smith', TO_DATE('2023-01-15', 'YYYY-MM-DD'));

查看当前表range_emp中的数据：

select * from range_emp;

7.同义词管理

步骤1、创建同义词。

CREATE OR REPLACE SYNONYM remp FOR range_emp;

步骤2、使用同义词。

SELECT * FROM remp;
INSERT INTO remp VALUES (2, 'John', 'King', TO_DATE('2025-03-26', 'YYYY-MM-DD'));
                           
查看当前表range_emp中的数据：                                 
select * from range_emp;

步骤3、查询已经创建的同义词。

SELECT synname,synobjschema,synobjname FROM pg_synonym WHERE synname = 'remp';

步骤4、删除同义词。

DROP SYNONYM remp;

总结

本次主要是提供了GaussDB分布式数据库数据库对象与管理实验脚本~

关于作者

网名：飞天，墨天轮2024年度、2025年度优秀原创作者，拥有 Oracle 10g OCM 认证、PGCE认证、MySQL 8.0 OCP认证以及OBCA、KCP、KCSM、ACP、YCP、HCIP、磐维等众多国产数据库认证证书，目前从事Oracle、MySQL、PostgreSQL、磐维数据库管理运维工作，喜欢结交更多志同道合的朋友，热衷于研究、分享数据库技术。
微信公众号：飞天online
墨天轮：https://www.modb.pro/u/15197
如有任何疑问，欢迎大家留言，一起探讨，共同进步~~~