人大金仓KingBase

安装&部署

为了方便，我们这里使用docker方式进行kingbase部署，其中kingbase使用的版本为v8r6

# 1. 下载docker镜像
docker pull warm3snow/kingbase:v8r6

# 2. 创建本地数据目录
mkdir -p /opt/kingbase/data

# 3. 启动kingbase
 docker run -d --name kingbasev8r6 -p 54321:54321 -e SYSTEM_USER=kingbase -e SYSTEM_PWD=kingbase \
 -v /opt/kingbase/data:/opt/kingbase/data \
 warm3snow/kingbase:v8r6

# 4. 查看日志, 等待启动完成
docker logs `docker ps -a | grep kingbase | awk '{print $1}'`
waiting for server to start.... done
server started
2024-06-11 10:00:20.047 GMT [14] HINT:  the value of max_connect is set 10
2024-06-11 10:00:20.050 GMT [14] WARNING:  max_connections should be less than or equal than 10 (restricted by license)
2024-06-11 10:00:20.050 GMT [14] HINT:  the value of max_connect is set 10
2024-06-11 10:00:20.057 UTC [14] LOG:  sepapower extension initialized
2024-06-11 10:00:20.058 UTC [14] LOG:  starting KingbaseES V008R006C005B0013 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit
2024-06-11 10:00:20.058 UTC [14] LOG:  listening on IPv4 address "0.0.0.0", port 54321
2024-06-11 10:00:20.058 UTC [14] LOG:  listening on IPv6 address "::", port 54321
2024-06-11 10:00:20.064 UTC [14] LOG:  listening on Unix socket "/tmp/.s.KINGBASE.54321"
2024-06-11 10:00:20.096 UTC [14] LOG:  redirecting log output to logging collector process
2024-06-11 10:00:20.096 UTC [14] HINT:  Future log output will appear in directory "sys_log".

如果提示license expired，可以从官网下载最新的license文件，替换license.dat即可。步骤如下：

1. 下载地址如下：https://www.kingbase.com.cn/xzzx/index.htm
2. 启动docker时进行挂载：

 docker run -d --name kingbasev8r6 -p 54321:54321 -e SYSTEM_USER=kingbase -e SYSTEM_PWD=kingbase \
 -v /opt/kingbase/data:/opt/kingbase/data \
 -v ./license.dat:/opt/kingbase/Server/bin/license.dat \
 warm3snow/kingbase:v8r6

登录&测试

# 进入docker容器
docker exec -it `docker ps -a | grep kingbase | awk '{print $1}'` /bin/sh

# 切换工作目录
cd Server/bin/

# 登录test数据库
./ksql -Ukingbase test #注test为默认安装的测试数据库

# 创建测试表
test=# CREATE TABLE mytest(
    id INT,
    name VARCHAR(20)
);

# 查询测试表
test=# \dt
          List of relations
 Schema |  Name  | Type  |   Owner
--------+--------+-------+------------
 public | mytest | table | kingbase
(1 row)


# 4. 插入测试数据
test=# INSERT INTO mytest VALUES(1, 'zhangsan');
test=# INSERT INTO mytest VALUES(2, 'lisi');
test=# INSERT INTO mytest VALUES(3, 'wangwu');

# 查询测试数据
test=# SELECT * FROM mytest;
 id |   name
----+----------
  1 | zhangsan
  2 | lisi
  3 | wangwu
(3 rows)

常用DDL

# 创建数据库
test=# CREATE DATABASE mydb;

# 删除数据库
test=# DROP DATABASE mydb;

# 创建表
test=# CREATE TABLE mytest(
    id INT,
    name VARCHAR(20)
);

# 删除表
test=# DROP TABLE mytest;

# 修改表: 添加列, 删除列, 修改列名, 修改表名, 清空表, 创建索引, 删除索引
test=# ALTER TABLE mytest ADD COLUMN age INT;
test=# ALTER TABLE mytest DROP COLUMN age;
test=# ALTER TABLE mytest RENAME COLUMN name TO username;
test=# ALTER TABLE mytest RENAME TO mytest2;
test=# TRUNCATE TABLE mytest;
test=# CREATE INDEX mytest_index ON mytest(id);
test=# DROP INDEX mytest_index;

# 创建用户
test=# CREATE USER myuser WITH PASSWORD '<PASSWORD>';

# 授权用户
test=# GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

# 删除用户
test=# DROP USER myuser;

常用命令

# 数据库列表
test=# \l

# 表列表
test=# \dt

# 表结构
test=# \d mytest

# 切换数据库
test=# \c mydb

# 查看所有存储过程
test=# \df

# 查看所有用户
test=# \du