KingbaseES 基本操作指南

人大金仓数据库管理系统(KingbaseES)是一款功能强大、成熟稳定的国产关系型数据库。它高度兼容 PostgreSQL 和 Oracle 语法,广泛应用于政务、军工、金融等关键领域。本文将引导您完成 KingbaseES 的一系列基本操作,包括用户权限管理、数据操作和高级对象创建。

一、 连接数据库与基本概念

在开始之前,通常需要使用命令行工具 ksql 或图形化工具(如金仓开发管理工具、DBeaver)连接到数据库实例。

连接示例:

ksql -U SYSTEM -W -p 54321 testdb
# -U 用户名
# -W 提示输入密码
# -p 端口号
# testdb 数据库名

重要概念区分:

  • 实例 (Instance): 一个独立的数据库服务进程及其所管理的内存结构。它是操作系统中一个具体的服务,一台服务器上可以运行多个金仓实例,每个实例使用不同的端口。实例是操作的上下文环境。
  • 数据库 (Database): 在一个实例中,可以创建多个数据库。每个数据库是一个独立的集合,包含自身的模式、表、数据等。例如,一个实例下可以有 finance_dbhr_db 两个完全隔离的数据库。用户是在实例级别创建的,但权限可以授予到特定数据库的对象上。
  • 模式 (Schema): 模式是数据库内部的逻辑容器或命名空间,用于组织数据库对象(如表、视图、函数)。一个数据库可以有多个模式(如 publichr_schemafinance_schema)。模式是对象的容器。 通过模式,可以允许多个用户使用同一个数据库而互不干扰。

关系类比:

实例 就像一个 独立的楼盘小区
数据库 是小区里的 一栋大楼
模式 (Schema) 是楼里的 某一层某个单元
表、视图等 就是单元里的 房间和家具

二、 创建角色并授予权限

在 KingbaseES 中,“角色”(ROLE)是一个核心概念,它既可以代表一个用户(USER, 能登录),也可以代表一个组(GROUP, 一组权限的集合)。

创建角色(作为权限组)

-- 创建一个名为 ‘data_manager’ 的角色,不具备登录权限,作为权限集合
CREATE ROLE data_manager WITH NOLOGIN;

授予权限给角色

权限包括 SELECT, INSERT, UPDATE, DELETE, CREATE, USAGE 等。

-- 授予 data_manager 对 public 模式下的所有现有表的查询、插入权限
GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA public TO data_manager;

-- 授予 data_manager 对 public 模式的 USAGE 权限,允许其使用该模式下的对象
GRANT USAGE ON SCHEMA public TO data_manager;

-- 也可以授予未来表的权限(需要先改变模式的默认权限)
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT ON TABLES TO data_manager;

三、 创建用户并授予角色

用户是带有登录权限的角色。

创建用户

-- 创建一个名为 ‘app_user’ 的用户,并设置密码
CREATE USER app_user WITH PASSWORD 'SecurePass123!';

将角色授予用户

-- 将之前创建的 data_manager 角色权限授予给 app_user 用户
GRANT data_manager TO app_user;

现在,app_user 就拥有了 data_manager 角色所包含的所有权限。

四、 回收角色/用户权限

使用 REVOKE 命令来撤销权限。

回收用户/角色的特定权限

-- 回收 app_user 对 public 模式所有表的 INSERT 权限
REVOKE INSERT ON ALL TABLES IN SCHEMA public FROM app_user;
-- 或者从 data_manager 角色回收,这样所有拥有该角色的用户都会失去此权限
REVOKE INSERT ON ALL TABLES IN SCHEMA public FROM data_manager;

从用户身上回收角色

-- 从 app_user 用户身上回收 data_manager 角色
REVOKE data_manager FROM app_user;

删除角色或用户

-- 删除用户 (如果用户拥有对象,需要使用 CASCADE 级联删除)
DROP USER IF EXISTS app_user;

-- 删除角色
DROP ROLE IF EXISTS data_manager;

五、 基本增删改查 (CRUD) 操作

首先,我们创建一个简单的表来演示。

创建表 (CREATE)

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department VARCHAR(50),
    salary NUMERIC(10, 2)
);

插入数据 (INSERT)

INSERT INTO employees (name, department, salary)
VALUES 
    ('张三', '研发部', 15000.00),
    ('李四', '市场部', 12000.00),
    ('王五', '研发部', 18000.00);

查询数据 (SELECT)

-- 查询所有数据
SELECT * FROM employees;

-- 条件查询:查询研发部员工
SELECT name, salary FROM employees WHERE department = '研发部';

-- 排序查询:按工资降序排列
SELECT * FROM employees ORDER BY salary DESC;

更新数据 (UPDATE)

-- 给所有市场部员工加薪 10%
UPDATE employees 
SET salary = salary * 1.10 
WHERE department = '市场部';

删除数据 (DELETE)

-- 删除工资低于 13000 的员工记录
DELETE FROM employees WHERE salary < 13000;

六、 创建视图 (View)

视图是一个虚拟表,基于一个 SQL 查询的结果集。

-- 创建一个视图,显示研发部员工及其工资
CREATE VIEW view_rd_employees AS
SELECT id, name, salary
FROM employees
WHERE department = '研发部';

-- 像查询普通表一样查询视图
SELECT * FROM view_rd_employees;

七、 创建存储过程 (Stored Procedure)

KingbaseES 支持多种过程语言,如 plpgsql(兼容 PostgreSQL)和 plsql(兼容 Oracle)。这里以 plpgsql 为例。

-- 创建一个存储过程,用于调整指定部门员工的工资
CREATE OR REPLACE PROCEDURE adjust_salary(
    IN dept_name VARCHAR, 
    IN raise_percent NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE employees
    SET salary = salary * (1 + raise_percent / 100)
    WHERE department = dept_name;
    
    -- 打印影响的行数(在ksql中会显示NOTICE)
    RAISE NOTICE '已更新 % 个员工的工资', FOUND;
END;
$$;

-- 调用存储过程,给研发部加薪5%
CALL adjust_salary('研发部', 5.0);

总结

通过以上步骤,我们完成了对人大金仓数据库的一系列核心操作:

  1. 权限管理: 理解了角色和用户的关系,学会了如何通过角色分组管理权限,并授予给用户。
  2. 数据操作: 掌握了对表中数据进行增、删、改、查的基本方法。
  3. 高级对象创建: 使用了视图来简化复杂查询并提供数据安全层;创建了存储过程来封装复杂的业务逻辑。
  4. 概念区分: 明确了实例、数据库、模式三者的关系和区别,这是合理规划数据库结构的基础。

这些操作是管理和使用 KingbaseES 数据库的基石。在实际生产环境中,请务必根据最小权限原则分配权限,并做好备份工作。

This entry was posted in 默认. Bookmark the permalink.