人大金仓数据库Kingbase服务SQL基础操作手册

1 kingbase服务
1.1 查看kingbase数据库服务进程
ps -ef|grep kingbase
1.2 命令启动kingbase数据库服务
# /opt/Kingbase/ES/V8 为金仓安装目录
# /opt/Kingbase/ES/V8/data 为金仓数据目录
# sys_ctl是数据库服务器启停命令，通过-D选项来来指定数据库数据目录
# 注意要切换成kingbase用户
/opt/Kingbase/ES/V8/Server/bin/sys_ctl -D /opt/Kingbase/ES/V8/data start
1.3 命令停止kingbase数据库服务
# /opt/Kingbase/ES/V8 为金仓安装目录
# /opt/Kingbase/ES/V8/data 为金仓数据目录
# sys_ctl是数据库服务器启停命令，通过-D选项来来指定数据库数据目录
# 注意要切换成kingbase用户
/opt/Kingbase/ES/V8/Server/bin/sys_ctl -D /opt/Kingbase/ES/V8/data stop
1.4 命令重启kingbase数据库服务
# /opt/Kingbase/ES/V8 为金仓安装目录
# /opt/Kingbase/ES/V8/data 为金仓数据目录
# sys_ctl是数据库服务器启停命令，通过-D选项来来指定数据库数据目录
# 注意要切换成kingbase用户
/opt/Kingbase/ES/V8/Server/bin/sys_ctl -D /opt/Kingbase/ES/V8/data restart
1.5 查询licence有效期
select GET_LICENSE_VALIDDAYS();
1.6 修改字符编码
alter table 模式名称.表名称 CHARACTER SET utf8 COLLATE utf8_general_ci
1.7 查看语言
select * from sys_language
1.8 查看角色用户
select * from sys_user
1.9 查看会话进程
select * from sys_stat_activity
1.10 查看当前账号会话数
select count(*), usename from sys_stat_activity group by usename
1.11 查看系统所有表
SELECT * FROM sys_tables
1.12 查看表字段
select * from information_schema.columns
1.13 查看索引
select * from sys_index
2 数据库操作
2.1 查看所有数据库
select * from sys_database
2.2 查看表空间
select * from sys_tablespace
2.3 查看数据库大小
# 查看kingbase数据库的大小，单位字节
select sys_database_size('kingbase')
# 查看kingbase数据库的大小，格式化后单位兆
select sys_size_pretty(sys_database_size('kingbase'));
2.4 查看所有数据库大小
select sys_database.datname, sys_database_size(sys_database.datname) as size from sys_database order by size desc;
2.5 查看数据库模式大小
SELECT
sys_size_pretty(sum(table_size)::bigint) as "disk space",
sum(table_size)::bigint as "total size"
FROM (
SELECT sys_catalog.sys_namespace.nspname as schema_name,
sys_total_relation_size(sys_catalog.sys_class.oid) as table_size
FROM  sys_catalog.sys_class
JOIN sys_catalog.sys_namespace
ON relnamespace = sys_catalog.sys_namespace.oid
WHERE sys_catalog.sys_namespace.nspname = 'kingbase'
) t
2.6 查看数据库所有模式大小
SELECT schema_name,
sys_size_pretty(sum(table_size)::bigint) as "disk space",
sum(table_size)::bigint as "total size"
FROM (
SELECT sys_catalog.sys_namespace.nspname as schema_name,
sys_total_relation_size(sys_catalog.sys_class.oid) as table_size
FROM  sys_catalog.sys_class
JOIN sys_catalog.sys_namespace
ON relnamespace = sys_catalog.sys_namespace.oid
WHERE sys_catalog.sys_namespace.nspname NOT IN ('information_schema','src_restrict','anon','dbms_sql','xlog_record_read','pg_catalog','pg_bitmapindex','sys_catalog','sysaudit','sysmac','sys')
) t
GROUP BY schema_name
2.7 查看单表大小
select sys_size_pretty(sys_relation_size('kingbase.test_szie'));
2.8 查询模式下所有表大小
SELECT
table_name,
sys_size_pretty(table_size) AS table_size,
sys_size_pretty(indexes_size) AS indexes_size,
sys_size_pretty(total_size) AS total_size
FROM (
SELECT
table_name,
sys_table_size(table_name) AS table_size,
sys_indexes_size(table_name) AS indexes_size,
sys_total_relation_size(table_name) AS total_size
FROM (
SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
FROM information_schema.TABLES WHERE table_schema ='kingbase'
) AS all_tables
ORDER BY total_size DESC
) AS pretty_sizes
2.9 查询数据库连接情况
select  * from pg_stat_activity
2.10 杀死空闲连接
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state='idle'
2.11 查询最大连接数
show max_connections
2.12 查询超级连接数
show superuser_reserved_connections
2.13 查看大小写情况
# V8R3查看,ON-敏感,OFF-不敏感
show case_sensitive
# V8R6查看,ON-不敏感,OFF-敏感
show enable_ci
2.14 查询客户端、服务端编码
show server_encoding;
show client_encoding;
2.15 设置客户端、服务端编码
set client_encoding to 'GBK';
set server_encoding to 'UTF-8';
3 模式操作
3.1 模式创建
CREATE SCHEMA 模式名称
3.2 模式删除
DROP SCHEMA 模式名称
3.3 修改模式名称
alter schema 被修改模式名称 rename to 需要重命名模式名称
3.4 查询数据库下所有模式名称
SELECT DISTINCT TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES;
3.5 查询数据库下所有模式数量
SELECT COUNT(DISTINCT TABLE_SCHEMA) FROM INFORMATION_SCHEMA.TABLES;
3.6 查询指定模式下表名称
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_SCHEMA='PUBLIC';
3.7 查询指定模式下指定表字段类型、注释等信息
SELECT  '表名称' AS table_name, col.COLUMN_NAME,
CASE WHEN  col.UDT_NAME='VARCHAR' THEN  CONCAT(col.UDT_NAME,'(',col.character_maximum_length,')')
ELSE col.UDT_NAME END AS UDTNAME,d.DESCRIPTION FROM information_schema.COLUMNS col
JOIN SYS_CLASS c ON c.RELNAME = col.TABLE_NAME
LEFT JOIN SYS_DESCRIPTION d ON d.OBJOID = c.OID AND d.OBJSUBID = col.ORDINAL_POSITION
LEFT JOIN sys_constraint s on c.OID = s.conrelid and col.ORDINAL_POSITION=ANY(conkey::int[])
WHERE col.table_schema = '模式名称'
AND col.TABLE_NAME = '表名称';
4 表操作
4.1 创建表
CREATE TABLE "sub_test"."t_table"(
id VARCHAR(32) NOT NULL，
remark VARCHAR(255),
length INT8,
front TEXT,
status TINYINT,
range FLOAT8,
create_time TIMESTAMP,
image_info JSON,
info JSON,
result JSON,
CONSTRAINT "t_table_PKEY" PRIMARY KEY ("id")
);
CREATE INDEX "createTime" ON "sub_test".t_table(create_time);
comment on column weather.remark is '备注';
comment on column weather.length is '长度';
comment on column weather.status is '状态';
comment on column weather.range is '范围';
comment on column weather.create_time is '创建时间';
4.2 创表–自增字段和主键

1 kingbase服务
1.1 查看kingbase数据库服务进程
ps -ef|grep ki