👋 欢迎来到吴老二技术博客,专注运维 / 数据库 / 云原生
首页/数据库/正文

人大金仓 KingbaseES 数据库表增删改查

吴
吴老二
2026-11-17数据库11 次浏览0 条评论

在人大金仓(KingbaseES)数据库日常运维过程中,表的增删改查是最基础也是最常用的操作。

与 MySQL 不同,人大金仓采用 Schema(模式)组织数据库对象。同一个数据库中可以创建多个 Schema,不同 Schema 下可以存在相同名称的表。

例如:

数据库:TEST
│
├── public
│   ├── user_info
│   └── order_info
│
├── app
│   ├── user_info
│   └── product_info
│
└── backup
    ├── user_info
    └── order_info

上面的结构中,public、app、backup 是三个不同的 Schema,它们都可以拥有一个名为 user_info 的表。

因此,在操作人大金仓数据库时,除了掌握普通的 INSERT、UPDATE、DELETE、SELECT 操作,还需要了解 Schema 的使用方式,以及如何跨 Schema 访问表。

本文使用以下环境作为演示:

| 配置项 | 示例 | | ---------- | ------ | | 数据库名称 | TEST | | Schema 1 | public | | Schema 2 | app | | Schema 3 | backup | | 用户 | system |

一、Schema(模式)基本操作

1.1 查看当前数据库

SELECT current_database();

查看当前用户:

SELECT current_user;

查看当前 Schema:

SELECT current_schema();

查看当前搜索路径:

SHOW search_path;

1.2 查看所有 Schema

通过 KSQL 命令查看:

\dn

也可以通过 SQL 查询:

SELECT schema_name
FROM information_schema.schemata
ORDER BY schema_name;

1.3 创建 Schema

创建一个名为 app 的 Schema:

CREATE SCHEMA app;

指定 Schema 所有者:

CREATE SCHEMA app AUTHORIZATION system;

如果 Schema 已经存在,可以使用:

CREATE SCHEMA IF NOT EXISTS app;

1.4 删除 Schema

删除空 Schema:

DROP SCHEMA app;

如果 Schema 下存在表、视图等对象,直接删除可能会失败。

如果确定需要连同内部对象一起删除:

DROP SCHEMA app CASCADE;

注意:CASCADE 会同时删除该 Schema 内的对象,以及依赖这些对象的其他数据库对象。生产环境执行前必须确认影响范围。

1.5 设置默认 Schema

可以通过设置 search_path 来调整当前会话默认搜索的 Schema:

SET search_path TO app, public;

查看:

SHOW search_path;

之后执行:

SELECT * FROM user_info;

数据库会按照 search_path 的顺序查找 user_info。

也可以直接使用完整名称:

SELECT * FROM app.user_info;

推荐在生产环境中操作时尽量使用 Schema.表名 的完整形式,避免操作到其他 Schema 下的同名表。

二、创建表(CREATE TABLE)

2.1 创建普通表

在 public Schema 下创建 user_info 表:

CREATE TABLE public.user_info (
    id INTEGER,
    username VARCHAR(100),
    age INTEGER,
    email VARCHAR(200),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

查看表结构:

\d public.user_info

查看表数据:

SELECT * FROM public.user_info;

2.2 创建带主键的表

CREATE TABLE public.user_info (
    id INTEGER PRIMARY KEY,
    username VARCHAR(100) NOT NULL,
    age INTEGER,
    email VARCHAR(200),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

其中:

  • PRIMARY KEY:设置主键。
  • NOT NULL:不允许字段为空。
  • DEFAULT:设置默认值。

2.3 创建自增主键

使用 GENERATED AS IDENTITY:

CREATE TABLE public.order_info (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_no VARCHAR(100),
    amount NUMERIC(12,2),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

如果需要允许显式插入 ID,可以使用:

id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY

2.4 在指定 Schema 下创建表

创建表时直接指定 Schema:

CREATE TABLE app.user_info (
    id INTEGER PRIMARY KEY,
    username VARCHAR(100),
    age INTEGER
);

即使当前 search_path 是 public,也可以明确指定将表创建到 app Schema。

2.5 根据已有表结构创建新表

只复制表结构,不复制数据:

CREATE TABLE backup.user_info
(LIKE public.user_info INCLUDING ALL);

如果目标 Schema 不存在,需要先创建:

CREATE SCHEMA IF NOT EXISTS backup;

复制表结构和数据:

CREATE TABLE backup.user_info AS
SELECT *
FROM public.user_info;

需要注意,CREATE TABLE AS 主要复制查询结果及列定义,不等同于完整复制原表的索引、主键、约束和其他对象定义。

三、查询表数据(SELECT)

3.1 查询整张表

SELECT * FROM public.user_info;

只查询指定字段:

SELECT id, username, age
FROM public.user_info;

3.2 根据条件查询

SELECT *
FROM public.user_info
WHERE id = 1;

查询年龄大于 25 岁的用户:

SELECT *
FROM public.user_info
WHERE age > 25;

多个条件:

SELECT *
FROM public.user_info
WHERE age > 25
AND username = 'zhangsan';

使用 IN 查询:

SELECT *
FROM public.user_info
WHERE id IN (1, 2, 3);

使用 LIKE 模糊查询:

SELECT *
FROM public.user_info
WHERE username LIKE '%zhang%';

3.3 排序查询

按照 ID 升序:

SELECT *
FROM public.user_info
ORDER BY id ASC;

按照 ID 降序:

SELECT *
FROM public.user_info
ORDER BY id DESC;

3.4 分页查询

查询前 10 条:

SELECT *
FROM public.user_info
LIMIT 10;

查询第 11 到第 20 条:

SELECT *
FROM public.user_info
ORDER BY id
LIMIT 10 OFFSET 10;

3.5 跨 Schema 查询

这是人大金仓中非常常用的操作。

假设 public 和 app 两个 Schema 中都存在 user_info 表。

查询 public 中的数据:

SELECT * FROM public.user_info;

查询 app 中的数据:

SELECT * FROM app.user_info;

查询 backup 中的数据:

SELECT * FROM backup.user_info;

跨 Schema 查询的语法:

SELECT 字段
FROM Schema名称.表名称;

这里的 Schema 名称和表名称之间使用英文句点 . 连接。

3.6 跨 Schema 关联查询

假设 public.user_info 保存用户信息,app.order_info 保存订单信息。

可以直接进行关联查询:

SELECT
    u.id,
    u.username,
    o.order_no,
    o.amount
FROM public.user_info u
LEFT JOIN app.order_info o
ON u.id = o.user_id;

不需要切换 Schema,也不需要额外建立数据库连接。

前提是两个表位于同一个数据库中,并且当前用户拥有对应对象的访问权限。

3.7 查询多个 Schema 中的数据

如果多个 Schema 中的表结构一致,可以使用 UNION ALL:

SELECT id, username, age
FROM public.user_info

UNION ALL

SELECT id, username, age
FROM app.user_info

UNION ALL

SELECT id, username, age
FROM backup.user_info;

UNION ALL 会保留重复记录,而 UNION 会对结果去重。

四、修改表结构(ALTER TABLE)

修改表结构和修改表数据是两种不同的操作。

ALTER TABLE 主要用于增加字段、删除字段、修改字段类型、修改字段名称等。

4.1 增加字段

ALTER TABLE public.user_info
ADD COLUMN phone VARCHAR(20);

增加多个字段:

ALTER TABLE public.user_info
ADD COLUMN address VARCHAR(200),
ADD COLUMN status INTEGER DEFAULT 1;

4.2 删除字段

ALTER TABLE public.user_info
DROP COLUMN phone;

删除字段之前,需要确认业务程序是否还在使用该字段。

4.3 修改字段类型

ALTER TABLE public.user_info
ALTER COLUMN age TYPE BIGINT;

如果新旧数据类型之间不能直接转换,需要使用 USING:

ALTER TABLE public.user_info
ALTER COLUMN age TYPE VARCHAR(20)
USING age::VARCHAR;

4.4 修改字段名称

ALTER TABLE public.user_info
RENAME COLUMN username TO user_name;

4.5 修改表名称

ALTER TABLE public.user_info
RENAME TO user_info_old;

注意:RENAME TO 只修改表名,不会修改 Schema。

4.6 移动表到其他 Schema

例如,将 public.user_info 移动到 app Schema:

ALTER TABLE public.user_info
SET SCHEMA app;

执行后,表的完整名称变成:

app.user_info

注意:目标 Schema 必须存在,当前用户需要具备相应权限。移动表不会自动把相关视图、函数等对象全部迁移到新 Schema。

五、插入数据(INSERT)

5.1 插入单条数据

INSERT INTO public.user_info
(id, username, age, email)
VALUES
(1, 'zhangsan', 25, 'zhangsan@example.com');

5.2 插入多条数据

INSERT INTO public.user_info
(id, username, age, email)
VALUES
(2, 'lisi', 28, 'lisi@example.com'),
(3, 'wangwu', 30, 'wangwu@example.com'),
(4, 'zhaoliu', 32, 'zhaoliu@example.com');

5.3 跨 Schema 插入数据

将 public.user_info 中的数据插入到 app.user_info:

INSERT INTO app.user_info
(id, username, age, email)
SELECT
    id,
    username,
    age,
    email
FROM public.user_info;

这是一种常见的数据迁移方式。

5.4 插入指定条件的数据

只迁移年龄大于 25 岁的用户:

INSERT INTO app.user_info
(id, username, age, email)
SELECT
    id,
    username,
    age,
    email
FROM public.user_info
WHERE age > 25;

5.5 避免主键重复

如果目标表中可能已经存在相同 ID,可以使用 ON CONFLICT:

INSERT INTO app.user_info
(id, username, age, email)
SELECT
    id,
    username,
    age,
    email
FROM public.user_info
ON CONFLICT (id) DO NOTHING;

这要求目标表的 id 字段存在主键或唯一约束。

如果需要重复数据时更新原记录:

INSERT INTO app.user_info
(id, username, age, email)
SELECT
    id,
    username,
    age,
    email
FROM public.user_info
ON CONFLICT (id)
DO UPDATE SET
    username = EXCLUDED.username,
    age = EXCLUDED.age,
    email = EXCLUDED.email;

六、修改数据(UPDATE)

6.1 修改指定记录

UPDATE public.user_info
SET age = 30
WHERE id = 1;

6.2 修改多个字段

UPDATE public.user_info
SET
    age = 31,
    email = 'new@example.com'
WHERE id = 1;

6.3 批量修改

UPDATE public.user_info
SET status = 1
WHERE age >= 18;

6.4 跨 Schema 修改数据

可以利用另一个 Schema 中的表作为更新数据来源。

例如,根据 app.user_info 中的用户信息,更新 public.user_info:

UPDATE public.user_info p
SET age = a.age
FROM app.user_info a
WHERE p.id = a.id;

这里的逻辑是:

  1. 读取 app.user_info 中的数据。
  2. 根据 ID 匹配 public.user_info。
  3. 将匹配记录的 age 更新为 app 中的 age。

6.5 更新前先查询

生产环境中,建议执行 UPDATE 之前先确认影响范围:

SELECT *
FROM public.user_info
WHERE id = 1;

确认无误后再执行:

UPDATE public.user_info
SET age = 30
WHERE id = 1;

不要随意省略 WHERE 条件,否则可能修改整张表的数据。

七、删除数据(DELETE)

7.1 删除指定记录

DELETE FROM public.user_info
WHERE id = 1;

7.2 根据条件批量删除

DELETE FROM public.user_info
WHERE age < 18;

7.3 清空整张表

DELETE FROM public.user_info;

也可以使用:

TRUNCATE TABLE public.user_info;

两种方式都可以删除表中的全部记录,但 TRUNCATE 通常更适合整表清空。

7.4 跨 Schema 删除数据

如果需要删除 public.user_info 中存在、但 app.user_info 中不存在的记录,可以使用 NOT EXISTS:

DELETE FROM public.user_info p
WHERE NOT EXISTS (
    SELECT 1
    FROM app.user_info a
    WHERE a.id = p.id
);

执行前建议先使用相同条件进行 SELECT 检查:

SELECT *
FROM public.user_info p
WHERE NOT EXISTS (
    SELECT 1
    FROM app.user_info a
    WHERE a.id = p.id
);

确认记录范围后再执行 DELETE。

八、跨 Schema 表操作实战

下面以实际运维中经常遇到的情况为例。

假设现在需要将 public Schema 中的业务表迁移到 app Schema。

8.1 查看两个 Schema 中的表

\dt public.*
\dt app.*

8.2 创建目标表

如果目标表不存在,可以通过 LIKE 创建:

CREATE TABLE app.user_info
(LIKE public.user_info INCLUDING ALL);

注意,LIKE INCLUDING ALL 会复制支持的表结构属性,但不代表所有外部依赖、触发器、权限和其他关联对象都会自动完整复制。

8.3 复制数据

INSERT INTO app.user_info
SELECT *
FROM public.user_info;

如果两个表字段顺序不一致,建议明确指定字段:

INSERT INTO app.user_info
(id, username, age, email, create_time)
SELECT
    id,
    username,
    age,
    email,
    create_time
FROM public.user_info;

8.4 比较数据量

查询源表:

SELECT COUNT(*) FROM public.user_info;

查询目标表:

SELECT COUNT(*) FROM app.user_info;

两边数量一致,只能说明记录数量相同,并不能证明数据内容完全一致。

还需要进一步检查关键字段和业务数据。

8.5 比较两张表的数据差异

可以使用 EXCEPT 检查源表中存在、目标表中不存在的记录:

SELECT id, username, age, email
FROM public.user_info

EXCEPT

SELECT id, username, age, email
FROM app.user_info;

反过来检查目标表中多出来的记录:

SELECT id, username, age, email
FROM app.user_info

EXCEPT

SELECT id, username, age, email
FROM public.user_info;

如果两个查询都没有返回记录,说明指定字段的数据集合一致。

注意:EXCEPT 默认会去重。如果表中允许重复记录,需要使用 EXCEPT ALL 或其他方式检查重复数据差异。

8.6 确认无误后删除源表

如果只是复制数据,不需要移动原表,可以在确认业务切换完成后执行:

DROP TABLE public.user_info;

如果只是想清空源表数据,保留表结构:

TRUNCATE TABLE public.user_info;

生产环境执行前,应确认应用连接、视图、外键、触发器和其他依赖关系,必要时安排业务停写或切换窗口。

九、跨 Schema 操作权限管理

跨 Schema 操作除了要求用户具备表权限,还需要具备访问 Schema 的权限。

例如,允许用户 app_user 访问 app Schema:

GRANT USAGE ON SCHEMA app TO app_user;

允许查询表:

GRANT SELECT ON app.user_info TO app_user;

允许插入数据:

GRANT INSERT ON app.user_info TO app_user;

允许修改数据:

GRANT UPDATE ON app.user_info TO app_user;

允许删除数据:

GRANT DELETE ON app.user_info TO app_user;

一次授予多种权限:

GRANT SELECT, INSERT, UPDATE, DELETE
ON app.user_info
TO app_user;

如果需要操作该 Schema 下已有的所有表,可以使用:

GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA app
TO app_user;

注意:这条命令只作用于执行时已经存在的表,不会自动授权未来新创建的表。需要对未来创建的表设置默认权限:

ALTER DEFAULT PRIVILEGES
IN SCHEMA app
GRANT SELECT, INSERT, UPDATE, DELETE
ON TABLES TO app_user;

默认权限还与执行命令的对象创建角色有关,需要根据实际建表用户配置。

十、日常操作注意事项

人大金仓中进行表操作时,下面几个问题需要特别注意:

1. 尽量使用 Schema.表名

推荐:

SELECT * FROM public.user_info;

不推荐在多个 Schema 存在同名表时直接使用:

SELECT * FROM user_info;

后者会受到 search_path 影响。

2. UPDATE 和 DELETE 必须确认 WHERE 条件

特别是生产环境,不要直接执行没有 WHERE 的更新和删除语句。

3. DROP 和 TRUNCATE 要谨慎

DROP 会删除表对象,TRUNCATE 会清空整张表的数据,两者都不是普通的单条记录删除操作。

4. 跨 Schema 不代表跨数据库

public.user_info 和 app.user_info 可以直接进行关联查询,是因为它们位于同一个数据库中。

如果两个表位于不同数据库,不能直接使用普通的 Schema.表名 进行关联,需要考虑数据库链接、外部表或数据迁移等方案。

5. 生产环境建议使用事务

对于支持事务的普通表操作,可以先开启事务:

BEGIN;

UPDATE public.user_info
SET age = 30
WHERE id = 1;

SELECT *
FROM public.user_info
WHERE id = 1;

确认修改结果后:

COMMIT;

如果发现操作错误:

ROLLBACK;

注意,事务中的修改在 COMMIT 之前不会正式提交。TRUNCATE 在支持事务的普通表上也可以回滚,但涉及外部表、特殊对象或其他非事务性操作时,需要单独确认。

十一、常用命令速查

| 操作 | 命令 | | -------------------- | ---------------------------------------------------- | | 查看 Schema | \dn | | 查看表 | \dt | | 查看指定 Schema 的表 | \dt public.* | | 查看表结构 | \d public.user_info | | 创建 Schema | CREATE SCHEMA app; | | 删除 Schema | DROP SCHEMA app; | | 创建表 | CREATE TABLE public.test (...); | | 删除表 | DROP TABLE public.test; | | 查询数据 | SELECT * FROM public.test; | | 插入数据 | INSERT INTO public.test ...; | | 修改数据 | UPDATE public.test SET ... WHERE ...; | | 删除数据 | DELETE FROM public.test WHERE ...; | | 清空表 | TRUNCATE TABLE public.test; | | 增加字段 | ALTER TABLE public.test ADD COLUMN ...; | | 删除字段 | ALTER TABLE public.test DROP COLUMN ...; | | 修改字段类型 | ALTER TABLE public.test ALTER COLUMN ... TYPE ...; | | 修改表名 | ALTER TABLE public.test RENAME TO test_new; | | 移动 Schema | ALTER TABLE public.test SET SCHEMA app; | | 跨 Schema 查询 | SELECT * FROM app.test; | | 跨 Schema 插入 | INSERT INTO app.test SELECT * FROM public.test; | | 跨 Schema 更新 | UPDATE ... FROM ...; | | 跨 Schema 删除 | DELETE ... WHERE NOT EXISTS (...); |

十二、总结

人大金仓的表操作与 PostgreSQL 风格的关系型数据库有很多相似之处,日常使用中需要重点掌握 CREATE、SELECT、INSERT、UPDATE、DELETE 和 ALTER TABLE 等 SQL 命令。

而 Schema 是人大金仓数据库对象管理中的重要组成部分。

在同一个数据库中,可以创建多个 Schema,不同 Schema 可以拥有相同名称的表。通过 Schema.表名 的方式,可以直接完成跨 Schema 查询、数据复制、关联查询、更新和删除。

对于数据库运维人员来说,熟悉 Schema 的管理方式和跨 Schema 操作,能够更方便地完成业务数据迁移、表结构调整和数据库日常维护。

评论 0

评论需 登录 后发表(注册并验证邮箱)。
还没有评论,来抢沙发~