人大金仓数据库管理系统(KingbaseES)是一款功能强大、成熟稳定的国产关系型数据库。它高度兼容 PostgreSQL 和 Oracle 语法,广泛应用于政务、军工、金融等关键领域。本文将引导您完成 KingbaseES 的一系列基本操作,包括用户权限管理、数据操作和高级对象创建。
一、 连接数据库与基本概念
在开始之前,通常需要使用命令行工具 ksql
或图形化工具(如金仓开发管理工具、DBeaver)连接到数据库实例。
连接示例:
ksql -U SYSTEM -W -p 54321 testdb
# -U 用户名
# -W 提示输入密码
# -p 端口号
# testdb 数据库名
重要概念区分:
- 实例 (Instance): 一个独立的数据库服务进程及其所管理的内存结构。它是操作系统中一个具体的服务,一台服务器上可以运行多个金仓实例,每个实例使用不同的端口。实例是操作的上下文环境。
- 数据库 (Database): 在一个实例中,可以创建多个数据库。每个数据库是一个独立的集合,包含自身的模式、表、数据等。例如,一个实例下可以有
finance_db
和hr_db
两个完全隔离的数据库。用户是在实例级别创建的,但权限可以授予到特定数据库的对象上。 - 模式 (Schema): 模式是数据库内部的逻辑容器或命名空间,用于组织数据库对象(如表、视图、函数)。一个数据库可以有多个模式(如
public
,hr_schema
,finance_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);
总结
通过以上步骤,我们完成了对人大金仓数据库的一系列核心操作:
- 权限管理: 理解了角色和用户的关系,学会了如何通过角色分组管理权限,并授予给用户。
- 数据操作: 掌握了对表中数据进行增、删、改、查的基本方法。
- 高级对象创建: 使用了视图来简化复杂查询并提供数据安全层;创建了存储过程来封装复杂的业务逻辑。
- 概念区分: 明确了实例、数据库、模式三者的关系和区别,这是合理规划数据库结构的基础。
这些操作是管理和使用 KingbaseES 数据库的基石。在实际生产环境中,请务必根据最小权限原则分配权限,并做好备份工作。