超详细KingbaseESV8R3（人大金仓）常用sql命令

————————————————————————————————————————————————————————————

一、系统

– 查询license有效期

select GET_LICENSE_VALIDDAYS();

– 修改字符编码

alter table 模式名称.表名称 CHARACTER SET utf8 COLLATE utf8_general_ci

– 查看数据库

select * from sys_database

– 查看表空间

select * from sys_tablespace

– 查看语言

select * from sys_language

– 查看角色用户

select * from sys_user

– 查看会话进程

select * from sys_stat_activity

– 查看当前账号会话数

select count(*), usename from sys_stat_activity group by usename

– 查看系统所有表

SELECT * FROM sys_tables

– 查看表字段

select * from information_schema.columns 

– 查看索引

select * from sys_index 

二、数据库

– 查询数据库连接情况

select  * from pg_stat_activity

– 杀死空闲连接

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state='idle'

– 查询最大连接数

show max_connections

– 超级连接数

show superuser_reserved_connections

– 杀死空闲连接

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state='idle'

– 展示大小写状况

show case_sensitive

– 查询客户端、服务端编码

show server_encoding;
show client_encoding;

– 设置客户端、服务端编码

set client_encoding to 'GBK';
set server_encodingto 'utf-8';

三、模式

– 模式创建

CREATE SCHEMA 模式名称 

– 模式进入

set search_path=模式名；

– 模式删除

DROP SCHEMA 模式名称

– 修改模式名称

alter schema 被修改模式名称 rename to 需要重命名模式名称 

–查询数据库下所有模式名称

SELECT DISTINCT TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES; 

–查询数据库下所有模式数量

 SELECT COUNT(DISTINCT TABLE_SCHEMA) FROM INFORMATION_SCHEMA.TABLES;

–查询指定模式下表名称

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_SCHEMA='PUBLIC';

–查询指定模式下指定表字段类型、注释等信息

 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 = '表名称';

四、表字段

– 修改字段类型

• alter table 模式名称.表名称 modify 修改字段 需要被修改字段类型

示例：
    alter table sub_test.tb_filegroup modify bbox varchar(32)

– 修改字段名称

• alter table 模式名称.表名称 rename 字段 to 修改字段

示例：
    alter table sub_test.tb_filegroup rename "bbox" to "b_box"
    alter table sub_test.tb_filegroup rename COLUMN bbox to b_box

– 修改字段为 ----非空

• alter table 模式名称.表名称 alter 字段 set not null

示例：
	alter table sub_test.tb_filegroup alter data_level set not null;

– 修改非空字段为 ----空

• alter table 模式名称.表名称 alter 字段 set not null

示例：
	alter table sub_test.tb_filegroup alter data_level drop not null;

– 添加字段

• alter table 模式名称.表名称 add 添加字段 字段类型

示例：
    alter table sub_test.tb_filegroup add bbox varchar(255)
    alter table sub_test.tb_filegroup add COLUMN bbox varchar(255) null

– 添加字段 （含默认值）

• alter table 模式名称.表名称 add 添加字段 字段类型 default 默认值;

示例：
    alter table sub_test.tb_filegroup add COLUMN bbox varchar(255) default '10';

– 删除字段

• alter table 模式名称.表名称 drop 需要删除字段

示例：
    alter table sub_test.tb_filegroup drop bbox

– 添加字段注释

• comment on column 表名称.字段名称 is ‘注释内容’

示例：
    comment on column sub_test.tb_filegroup.bbox is '方位'

– 创建索引

CREATE INDEX "索引名称" ON 模式名称.表名称 (字段名称)

示例：
CREATE INDEX "metadataGroupId" ON "sub_test".tb_filegroup(metadataId);

五、表

– 创建表及注释字段、实体参考

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 '创建时间';
......

对应实体：

@Data
@EqualsAndHashCode(callSuper = true)
@Accessors(chain = true)
@TableName(value = "t_table", autoResultMap = true)
public class tTableEntity extends BaseEntity {

    private static final long serialVersionUID = 1L;
    
    private String id  ;
    private String remark ;
    private Long length  ;
    private String front;
    private Integer status ;
    private Double range ;
    
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private LocalDateTime createTime;
    
    @TableField(value = "imageInfo", typeHandler = ObjectJsonHandler.class)
    private JSONObject imageInfo;

    @TableField(value = "info", typeHandler = JacksonTypeHandler.class)
    private List<String> info;
    
    @TableField(value = "result", typeHandler = JacksonTypeHandler.class)
    private ResultJson result;
    
}


@Data
@NoArgsConstructor
@AllArgsConstructor
public class ResultJson implements Serializable {
    private static final long serialVersionUID = 1L;
   
    public Map<String, Object> resultData;

    private List<Double> zValue;
}

– 表设置主键自增

第一步：创表–自增字段和主键

CREATE TABLE "sub_test"."t_table"( 
    id SERIAL NOT NULL,
	host_name VARCHAR(125),
	port VARCHAR(32),
	type TINYINT,
	launch_date TIMESTAMP,
	modify_time TIMESTAMP,
	create_time TIMESTAMP,
	CONSTRAINT "t_table_PKEY" PRIMARY KEY ("id")
);

第一步中，id设置为主键，同时类型指定为serial，创建之后，id即为自增。

但是如果创建的时候没有设为自增怎么处理？注意并不可以将ID设为serial类型。serial是一个伪类型，作用是自动创建一个序列（sequence）并赋给该字段，那么将某字段设为自增（主键与自增是两码事），操作如下：

第二步：将字段改为自增

-- 将字段改为自增
ALTER TABLE sub_test."t_table"  ALTER column id set default nextval('sub_test.t_table_id_SEQ');

--删除约束（主键就是一个约束）
ALTER TABLE sub_test."t_table" DROP CONSTRAINT t_table_constraint_1;

--添加主键
ALTER TABLE sub_test."t_table" ADD PRIMARY KEY(id);

– 修改表名称

• alter table 模式名称.表名称 rename to 修改表名

示例：
    alter table sub_test.tb_filegrouprename to "tb_filegroup1"