KingBaseES 数据库管理系统安装
环境以及安装前准备
安装包获取
访问官网获取安装包 下载链接直达
操作系统规划准备
| 操作系统 | 核心 | 内存 | 存储 | IP |
|---|---|---|---|---|
| KyinLinux V10 | 8c | 16g | 100g | 10.168.1.175 |
上传安装包到服务器
[root@kyinLinx ~]# cd /soft/
[root@kyinLinx soft]# ll
总用量 3290824
-rw-r--r-- 1 root root 2974226432 8月 23 23:42 KingbaseES_V009R001C010B0004_Lin64_install.iso
-rw-r--r-- 1 root root 395572609 9月 25 14:56 ksqldeveloper-V1R1C1-lin64.tar.gz
[root@kyinLinx soft]#
创建KingBase安装用户
[root@kyinLinx soft]# useradd kingbase
[root@kyinLinx soft]# passwd kingbase
更改用户 kingbase 的密码 。
新的 密码：[King^123456]
重新输入新的 密码：
passwd：所有的身份验证令牌已经成功更新。
创建安装目录
[root@kyinLinx soft]# su - kingbase
上一次登录：四 9月 25 23:59:50 CST 2025从 172.16.2.208pts/2 上
[kingbase@kyinLinx ~]$ mkdir -p /home/kingbase/kdb
[kingbase@kyinLinx ~]$ mkdir -p /home/kingbase/kdata
验证安装包MD5
[kingbase@kyinLinx ~]$ md5sum /soft/KingbaseES_V009R001C010B0004_Lin64_install.iso
9b39aaceb8611b1bbc587274cb58f126 /soft/KingbaseES_V009R001C010B0004_Lin64_install.iso
[kingbase@kyinLinx ~]$ sha1sum /soft/KingbaseES_V009R001C010B0004_Lin64_install.iso
353006ed0e71695a2ce0324edd83ea38c6302874 /soft/KingbaseES_V009R001C010B0004_Lin64_install.iso
挂载ISO安装镜像
[kingbase@kyinLinx ~]$ su - root
密码：
上一次登录：四 9月 25 23:48:52 CST 2025从 172.16.2.208pts/1 上
[root@kyinLinx ~]#
[root@kyinLinx ~]# mkdir -p /KINGISO
[root@kyinLinx ~]# mount /soft/KingbaseES_V009R001C010B0004_Lin64_install.iso /KINGISO/
mount: /KINGISO: WARNING: device write-protected, mounted read-only.
[root@kyinLinx ~]# cd /KINGISO/
[root@kyinLinx KINGISO]# ll
总用量 7
dr-xr-xr-x 3 root root 2048 6月 26 2023 setup
-r-xr-xr-x 1 root root 4299 6月 26 2023 setup.sh
安装过程
开始安装
初始化数据库
这里有一个报错，是没有安装图形化界面，按照如下解决
[root@kyinLinx ~]# yum -y install xorg-x11-xauth xorg-x11-server-utils xorg-x11-server-Xnest libXtst
上次元数据过期检查：1:08:48 前，执行于 2025年09月25日 星期四 23时44分25秒。
软件包 xorg-x11-xauth-1:1.0.9-12.el8.x86_64 已安装。
软件包 xorg-x11-server-utils-7.7-27.el8.x86_64 已安装。
软件包 libXtst-1.2.3-7.el8.x86_64 已安装。
依赖关系解决。
==================================================================================================================
软件包 架构 版本 仓库 大小
==================================================================================================================
安装:
xorg-x11-server-Xnest x86_64 1.20.6-3.el8.ks8.01 ks8-adv-AppStream 772 k
事务概要
==================================================================================================================
安装 1 软件包
总下载：772 k
安装大小：1.7 M
下载软件包：
xorg-x11-server-Xnest-1.20.6-3.el8.ks8.01.x86_64.rpm 1.1 MB/s | 772 kB 00:00
------------------------------------------------------------------------------------------------------------------
总计 1.1 MB/s | 772 kB 00:00
警告：/var/cache/dnf/ks8-adv-AppStream-7e9b6aaba2dddefd/packages/xorg-x11-server-Xnest-1.20.6-3.el8.ks8.01.x86_64.rpm: 头V4 RSA/SHA1 Signature, 密钥 ID 7a486d9f: NOKEY
Kylin Linux Advanced Server 10 - AppStream 74 kB/s | 1.7 kB 00:00
导入 GPG 公钥 0x7A486D9F:
Userid: "NeoKylin (release key) <support@cs2c.com.cn>"
指纹: B814 9E68 5286 4585 CE41 143B 41F8 AEBE 7A48 6D9F
来自: /etc/pki/rpm-gpg/RPM-GPG-KEY-kylin
导入公钥成功
运行事务检查
事务检查成功。
运行事务测试
事务测试成功。
运行事务
准备中 : 1/1
安装 : xorg-x11-server-Xnest-1.20.6-3.el8.ks8.01.x86_64 1/1
运行脚本: xorg-x11-server-Xnest-1.20.6-3.el8.ks8.01.x86_64 1/1
验证 : xorg-x11-server-Xnest-1.20.6-3.el8.ks8.01.x86_64 1/1
已安装:
xorg-x11-server-Xnest-1.20.6-3.el8.ks8.01.x86_64
完毕！
设置DISPLAY变量到本地计算机IP
export DISPLAY=172.16.2.208:0.0
在执行初始化数据库命令
[kingbase@kyinLinx KINGISO]$ /home/kingbase/kdb//Server/bin/kconsole.sh
╔═════════════════════════════════════════════════╗
欢迎使用金仓数据库管控工具
JDK Version： 1.8.0_92
Local: zh
IncludeFeatures: DB_MODE_ALL
╚═════════════════════════════════════════════════╝
正在打开工具，请稍等..已打开！
打开初始化安装界面 选择创建新实例
创建实例【选择Oracle兼容模式】
安装摘要
执行安装并注册服务
手动注册服务
[kingbase@kyinLinx kdb]$ su - root
密码：
上一次登录：五 9月 26 01:00:54 CST 2025pts/1 上
最后一次失败的登录：五 9月 26 01:22:06 CST 2025pts/2 上
最有一次成功登录后有 1 次失败的登录尝试。
[root@kyinLinx ~]# /home/kingbase/.kes_dbca/root-service-kes_instance.sh
等待服务器进程关闭 .... 完成
服务器进程已经关闭
Starting KingbaseES V9:
等待服务器进程启动 .... 完成
服务器进程已经启动
KingbaseES V9 started successfully
安装完成
安装验证
[root@kyinLinx KINGISO]# su - kingbase
上一次登录：五 9月 26 00:12:10 CST 2025pts/0 上
[kingbase@kyinLinx ~]$ cd /home/kingbase/kdb/Server/bin/
[kingbase@kyinLinx bin]$ ./kingbase -V
kingbase (KingbaseES) V009R001C010
查看实例状态
KSQL Developer 数据库开发工具安装
安装包以及环境准备
安装包获取
访问官网获取安装包 下载链接直达
操作系统规划准备
| 操作系统 | 核心 | 内存 | 存储 | IP |
|---|---|---|---|---|
| KyinLinux V10 | 8c | 16g | 100g | 10.168.1.175 |
上传安装包到服务器
[root@kyinLinx ~]# cd /soft/
[root@kyinLinx soft]# ll
总用量 3290824
-rw-r--r-- 1 root root 2974226432 8月 23 23:42 KingbaseES_V009R001C010B0004_Lin64_install.iso
-rw-r--r-- 1 root root 395572609 9月 25 14:56 ksqldeveloper-V1R1C1-lin64.tar.gz
[root@kyinLinx soft]#
解压安装包
[root@kyinLinx soft]# tar -zxf ksqldeveloper-V1R1C1-lin64.tar.gz
[root@kyinLinx soft]# ll
总用量 3290844
-rw-r--r-- 1 root root 2974226432 8月 23 23:42 KingbaseES_V009R001C010B0004_Lin64_install.iso
-rw-r--r-- 1 root root 395572609 9月 25 14:56 ksqldeveloper-V1R1C1-lin64.tar.gz
-rw-r--r-- 1 root root 4998 9月 25 14:18 license_V009R001C-开发版.dat
-rw-r--r-- 1 root root 4995 9月 25 14:18 license_V009R001C-企业版.dat
drwxr-xr-x 2 root root 102 9月 18 18:18 setup
-rwxr-xr-x 1 root root 2956 9月 18 18:18 setup.sh
[root@kyinLinx soft]# su - kingbase
[kingbase@kyinLinx ~]$ export DISPLAY=172.16.2.208:0.0
[kingbase@kyinLinx ~]$ sh /soft/setup.sh -i swing
安装过程
[root@kyinLinx soft]# su - kingbase
[kingbase@kyinLinx ~]$ export DISPLAY=172.16.2.208:0.0
[kingbase@kyinLinx ~]$ sh /soft/setup.sh -i swing
启动开发者工具
[kingbase@kyinLinx ~]$ cd /home/kingbase/ksql-developer/
[kingbase@kyinLinx ksql-developer]$ ll
总用量 364
-rw-rw-r-- 1 kingbase kingbase 88014 9月 18 18:18 artifacts.xml
drwxrwxr-x 4 kingbase kingbase 96 9月 26 01:58 configuration
drwxrwxr-x 21 kingbase kingbase 4096 9月 26 01:58 features
drwxrwxr-x 2 kingbase kingbase 48 9月 26 01:58 gtk-3
-rw-rw-r-- 1 kingbase kingbase 151469 9月 18 18:18 icon.xpm
-rwxrwxr-x 1 kingbase kingbase 80072 9月 18 18:18 KSQLDeveloper
-rw-rw-r-- 1 kingbase kingbase 423 9月 18 18:18 KSQLDeveloper.ini
-rw-rw-r-- 1 kingbase kingbase 545 9月 18 18:18 kstudio.png
drwxrwxr-x 2 kingbase kingbase 112 9月 26 01:58 licenses
drwxrwxr-x 2 kingbase kingbase 149 9月 26 02:04 logs
drwxrwxr-x 4 kingbase kingbase 78 9月 26 01:58 p2
drwxrwxr-x 10 kingbase kingbase 28672 9月 26 01:58 plugins
-rw-rw-r-- 1 kingbase kingbase 1810 9月 18 18:18 readme.txt
drwxrwxr-x 2 kingbase kingbase 70 9月 26 02:03 Uninstaller
[kingbase@kyinLinx ksql-developer]$ ./KSQLDeveloper -i swing
链接测试
Oracle兼容性测试
接下来我们围绕 金仓数据库（KES）对 Oracle 的兼容性测试，设计一个 学生与成绩管理系统 的测试场景，涵盖多个兼容特性。
测试目标
-
SQL兼容：
- 数据类型：
YMINTERVAL,DSINTERVAL - 虚拟列、伪列、分区表（Interval分区）
- SQL语句：
EXECUTE IMMEDIATE,RETURNING INTO,BULK COLLECT INTO,LATERAL连接 - 多表连接视图的DML
- HINT调优
- 数据类型：
-
PL/SQL兼容：
- 游标属性、集合类型
- 存储过程
一、表结构设计（含 Interval 分区、虚拟列、伪列）
1. 学生表
CREATE TABLE students (
student_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
name VARCHAR2(100),
birth_date DATE,
enrollment_ds INTERVAL DAY TO SECOND,
enrollment_ym INTERVAL YEAR TO MONTH
)
PARTITION BY RANGE (birth_date)
INTERVAL(NUMTOYMINTERVAL(1, 'YEAR'))
( PARTITION p0 VALUES LESS THAN (DATE'2000-01-01') );
2. 成绩表（含外键、多表连接视图）
CREATE TABLE scores (
score_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
student_id NUMBER,
course VARCHAR2(100),
score NUMBER(5,2),
exam_date DATE DEFAULT SYSDATE,
CONSTRAINT fk_student FOREIGN KEY (student_id) REFERENCES students(student_id)
);
二、插入测试数据（含 Interval 类型）
-- 插入学生数据
INSERT INTO students (name, birth_date, enrollment_ds, enrollment_ym)
VALUES ('Alice', DATE '2001-05-15', INTERVAL '5 12:30:45' DAY TO SECOND, INTERVAL '2-6' YEAR TO MONTH);
INSERT INTO students (name, birth_date, enrollment_ds, enrollment_ym)
VALUES ('Bob', DATE '2002-08-20', INTERVAL '3 08:15:30' DAY TO SECOND, INTERVAL '1-3' YEAR TO MONTH);
SELECT * FROM "students" ;
student_id|name |birth_date |enrollment_ds|enrollment_ym|
----------+-------+-------------------+-------------+-------------+
1|Alice |2001-05-15 00:00:00|5 12:30:45.0 |2-6 |
2|Bob |2002-08-20 00:00:00|3 8:15:30.0 |1-3 |
-- 插入成绩数据
INSERT INTO scores (student_id, course, score) VALUES (1, 'Math', 95.5);
INSERT INTO scores (student_id, course, score) VALUES (1, 'English', 88.0);
INSERT INTO scores (student_id, course, score) VALUES (2, 'Math', 76.0);
INSERT INTO scores (student_id, course, score) VALUES (2, 'Physics', 82.5);
SELECT * FROM "scores" ;
score_id|student_id|course |score|exam_date |
--------+----------+-------+-----+-------------------+
1| 1|Math |95.50|2025-09-26 03:16:49|
2| 1|English|88.00|2025-09-26 03:16:49|
3| 2|Math |76.00|2025-09-26 03:16:49|
4| 2|Physics|82.50|2025-09-26 03:16:49|
三、SQL语句兼容性测试
1. EXECUTE IMMEDIATE + RETURNING INTO
DECLARE
v_new_id NUMBER;
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO students (name, birth_date, enrollment_ds, enrollment_ym) VALUES (:1, :2, :3, :4) RETURNING student_id INTO :5'
USING 'Charlie', DATE '2003-01-10', INTERVAL '4 10:20:30' DAY TO SECOND, INTERVAL '0-9' YEAR TO MONTH
RETURNING INTO v_new_id;
DBMS_OUTPUT.PUT_LINE('New Student ID: ' || v_new_id);
END;
SELECT * FROM "students" ;
student_id|name |birth_date |enrollment_ds|enrollment_ym|
----------+-------+-------------------+-------------+-------------+
1|Alice |2001-05-15 00:00:00|5 12:30:45.0 |2-6 |
2|Bob |2002-08-20 00:00:00|3 8:15:30.0 |1-3 |
3|Charlie|2003-01-10 00:00:00|4 10:20:30.0 |0-9 |
2. BULK COLLECT INTO（批量查询）
DECLARE
TYPE score_table IS TABLE OF scores%ROWTYPE;
v_scores score_table;
BEGIN
SELECT * BULK COLLECT INTO v_scores FROM scores WHERE student_id = 1;
FOR i IN 1..v_scores.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Course: ' || v_scores(i).course || ', Score: ' || v_scores(i).score);
END LOOP;
END;
3. LATERAL 连接（子查询引用外部列）
SELECT s.name, sc.course, sc.score
FROM students s,
LATERAL (SELECT course, score FROM scores WHERE student_id = s.student_id AND score > 80) sc;
name |course |score|
-----+-------+-----+
Alice|Math |95.50|
Alice|English|88.00|
Bob |Physics|82.50|
四、视图与DML操作（多表连接视图）
创建视图
CREATE OR REPLACE VIEW v_student_scores AS
SELECT s.student_id, s.name, sc.course, sc.score
FROM students s JOIN scores sc ON s.student_id = sc.student_id;
SELECT * FROM v_student_scores;
student_id|name |course |score|
----------+-----+-------+-----+
1|Alice|Math |95.50|
1|Alice|English|88.00|
2|Bob |Math |76.00|
2|Bob |Physics|82.50|
创建触发器
CREATE OR REPLACE FUNCTION trg_v_stu_score_io()
RETURNS TRIGGER AS
$$
BEGIN
IF TG_OP = 'INSERT' THEN
-- 只负责插 scores；name 仅用于显示，不实际写 students
INSERT INTO scores(student_id, course, score)
VALUES (NEW.student_id, NEW.course, NEW.score);
RETURN NEW;
ELSIF TG_OP = 'UPDATE' THEN
UPDATE scores
SET course = NEW.course,
score = NEW.score
WHERE student_id = OLD.student_id
AND course = OLD.course;
RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
DELETE FROM scores
WHERE student_id = OLD.student_id
AND course = OLD.course;
RETURN OLD;
END IF;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_v_stu_score
INSTEAD OF INSERT OR UPDATE OR DELETE
ON v_student_scores
FOR EACH ROW
EXECUTE FUNCTION trg_v_stu_score_io();
测试DML（INSERT/UPDATE/DELETE）
-- 插入视图（需满足键保留）
INSERT INTO v_student_scores (student_id, name, course, score)
VALUES (2, 'Bob', 'Chemistry', 91.0);
SELECT * FROM v_student_scores;
student_id|name |course |score|
----------+-----+---------+-----+
1|Alice|Math |95.50|
1|Alice|English |88.00|
2|Bob |Math |76.00|
2|Bob |Physics |82.50|
2|Bob |Chemistry|91.00|
-- 更新视图
UPDATE v_student_scores SET score = 93 WHERE name = 'Bob' AND course = 'Chemistry';
SELECT * FROM v_student_scores;
student_id|name |course |score|
----------+-----+---------+-----+
1|Alice|Math |95.50|
1|Alice|English |88.00|
2|Bob |Math |76.00|
2|Bob |Physics |82.50|
2|Bob |Chemistry|93.00|
-- 删除视图
DELETE FROM v_student_scores WHERE name = 'Bob' AND course = 'Chemistry';
SELECT * FROM v_student_scores;
student_id|name |course |score|
----------+-----+-------+-----+
1|Alice|Math |95.50|
1|Alice|English|88.00|
2|Bob |Math |76.00|
2|Bob |Physics|82.50|
五、PL/SQL兼容性测试
1. 游标属性与集合类型
DECLARE
CURSOR c_scores IS SELECT course, score FROM scores WHERE student_id = 1;
TYPE score_rec IS RECORD (course VARCHAR2(100), score NUMBER);
TYPE score_tab IS TABLE OF score_rec;
v_scores score_tab;
BEGIN
OPEN c_scores;
FETCH c_scores BULK COLLECT INTO v_scores;
CLOSE c_scores;
FOR i IN 1..v_scores.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(v_scores(i).course || ': ' || v_scores(i).score);
END LOOP;
END;
2. 存储过程（Procedure）
CREATE OR REPLACE PROCEDURE log_score_change(p_student_id INTEGER, p_old_score NUMBER(5,2) , p_new_score NUMBER(5,2)) AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO score_log(student_id, old_score, new_score, change_time)
VALUES (p_student_id, p_old_score, p_new_score, SYSDATE);
COMMIT;
END;
--调试
DECLARE
"p_student_id" integer := 1;
"p_old_score" numeric := 95.5;
"p_new_score" numeric := 96;
BEGIN
call "public"."log_score_change"("p_student_id","p_old_score","p_new_score");
END;
SELECT * FROM score_log;
student_id|old_score|new_score|change_time |
----------+---------+---------+-------------------+
1| 95.5| 96|2025-09-26 03:38:29|
六、HINT 调优测试
SELECT /*+ INDEX(s PK_STUDENTS) */ * FROM students s WHERE student_id = 1;
student_id|name |birth_date |enrollment_ds|enrollment_ym|
----------+-----+-------------------+-------------+-------------+
1|Alice|2001-05-15 00:00:00|5 12:30:45.0 |2-6 |
✅ 总结
| 特性类别 | 测试点 | 状态 |
|---|---|---|
| SQL兼容 | Interval 类型、虚拟列、分区表 | ✅ |
| SQL语句 | EXECUTE IMMEDIATE、BULK COLLECT、LATERAL | ✅ |
| 视图DML | 多表连接视图插入/更新/删除 | ✅ |
| PL/SQL | 游标、集合、存储过程 | ✅ |
| 调优 | HINT | ✅ |
评论区