ksql使用-青学会&金仓专栏（7）

本期投稿人

青学会_胖虎。熟悉Oracle，MySQL，对国产数据库也有着浓厚的兴趣。

正文开始

一.ksql连接方法

1.登录数据库

ksql -h 192.168.126.91 -p 54321 -U system -d test -E

常用必选参数:

-h:数据库服务器ip地址
-p:端口号
-U:用户名
-d:想要登陆的数据库,kingbase安装后自动有test数据库

其他参数:

-E:显示 psql 生成的内部 SQL 命令,为了更好的认识\封装命令。

帮助信息

test=# help
您正在使用ksql，这是Kingbase的命令行接口。
键入：? 显示 ksql 命令的说明
\g 或者以分号(;)结尾以执行查询
\q 退出

显示连接信息

test=# \conninfo
以用户 "system" 的身份, 在主机"192.168.126.91", 端口"54321"连接到数据库 "test"

查看数据库版本

test=# select version();
version
KingbaseES V009R001C001B0030 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
(1 行记录)

二.数据库的创建、查看、修改与删除

1.创建,查看数据库

test=# CREATE DATABASE testdb ENCODING 'UTF8' template = template0;
CREATE DATABASE

显示数据库

test=# \l+
********* QUERY **********
SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
d.datcollate as "Collate",
d.datctype as "Ctype",
pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges",
CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
ELSE 'No Access'
END as "Size",
t.spcname as "Tablespace",
pg_catalog.shobj_description(d.oid, 'pg_database') as "Description"
FROM pg_catalog.pg_database d
JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid
ORDER BY 1;
**************************
数据库列表
名称 | 拥有者 | 字元编码 | 校对规则 | Ctype | 存取权限 | 大小 | 表空间 | 描述
-----------+--------+----------+-------------+-------------+-------------------+-------+-------------+--------------------------------------------
kingbase | system | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | | 14 MB | sys_default | default administrative connection database
security | system | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | | 14 MB | sys_default |
template0 | system | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/system +| 14 MB | sys_default | unmodifiable empty database
| | | | | system=CTc/system | | |
template1 | system | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/system +| 14 MB | sys_default | default template for new databases
| | | | | system=CTc/system | | |
test | system | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | | 14 MB | sys_default | default administrative connection database
testdb | system | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | | 14 MB | sys_default |
(6 行记录)

备注:

从\l+被解析可以看到,底层实际是用到表pg_catalog.pg_database
ENCODING 'UTF8' #这部分指定新数据库的字符编码为 UTF-8
template = template0 #这部分指定使用 template0 作为模板数据库

2.修改数据库search_path配置,切换数据库,查看search_path

test=# ALTER DATABASE testdb SET search_path TO pa_catalog,public;

切换数据库

test=# \c testdb
您现在以用户名"system"连接到数据库"testdb"。

查看search_path

testdb=# show search_path;
search_path
--------------------
pa_catalog, public

备注:

search_path 参数定义 PostgreSQL 在查找未加模式前缀的对象名时,应该搜索的模式（schema）顺序。
这里可以理解成在数据库中,from table 查的表是哪个用户下的表,这里呢设置了search_path,
首先在 pa_catalog 模式中查找对象,如果在 pa_catalog 中没有找到,则在 public 模式中查找对象。
验证:直接用第二步得到的sql测试,查看结果为6
SELECT count(1) FROM pg_database;

3.查看scheme

test=# \dn
********* QUERY **********
SELECT n.nspname AS "Name",
pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
AND n.nspname <> 'sys'
AND n.nspname <> 'sys_catalog'
ORDER BY 1;
**************************
架构模式列表
名称 | 拥有者
------------------+--------
anon | system
dbms_sql | system
perf | system
public | system
src_restrict | system
sys_hm | system
sysaudit | system
sysmac | system
wmsys | system
xlog_record_read | system
(10 行记录)

备注:通过sql可以看到是有筛选条件的,所以pa_catalog没有显示出来,去掉筛选条件可以看到pa_catalog

4.重命名数据库

test=# ALTER DATABASE testdb RENAME TO testdb1;
ALTER DATABASE
**************************
数据库列表
名称 | 拥有者 | 字元编码 | 校对规则 | Ctype | 存取权限 | 大小 | 表空间 | 描述
-----------+--------+----------+-------------+-------------+-------------------+-------+-------------+--------------------------------------------
kingbase | system | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | | 14 MB | sys_default | default administrative connection database
security | system | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | | 14 MB | sys_default |
template0 | system | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/system +| 14 MB | sys_default | unmodifiable empty database
| | | | | system=CTc/system | | |
template1 | system | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/system +| 14 MB | sys_default | default template for new databases
| | | | | system=CTc/system | | |
test | system | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | | 14 MB | sys_default | default administrative connection database
testdb1 | system | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | | 14 MB | sys_default |
(6 行记录)

5.删除数据库

test=# DROP DATABASE testdb1;
DROP DATABASE

三.表的基本对象管理

1.创建表

CREATE TABLE address (
addressid serial,
userid integer NOT NULL,
realname character varying(50 char) NOT NULL,
telephone character varying(20 char) NOT NULL,
province character varying(20 char) NOT NULL,
city character varying(20 char) NOT NULL,
district character varying(20 char) NOT NULL,
address text NOT NULL,
CONSTRAINT market_address_constraint_1 PRIMARY KEY (addressid)
);

2.数据库对象查看

显示所有表、视图、序列和索引
test=# \d
********* QUERY **********
select n.nspname as "Schema",
c.relname as "Name",
case c.relkind when 'r' then 'table' when 'v' then 'view' when 'm' then 'materialized view' when 'i' then 'index' when 'S' THEN 'sequence' when 's' THEN 'special' when 'f' then 'foreign table' when 'p' then 'addressitioned table' when 'I' then 'addressitioned index' when 'g' then 'global index' end as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p','v','m','S','f','')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname <> 'sys'
AND n.nspname <> 'sys_catalog'
AND (c.oid not in (select reloid from sys_recyclebin))
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************
关联列表
架构模式 | 名称 | 类型 | 拥有者
----------+-------------------------+--------+--------
public | address | 数据表 | system
public | address_addressid_seq | 序列数 | system
public | sys_stat_statements | 视图 | system
public | sys_stat_statements_all | 视图 | system
(4 行记录)

备注:
可以看到新建表address,类型数据表,和建表时使用数据类型serial自动创建的序列序列数\d的QUERY是由多个系统表组合进行包装的命令
特定用法

显示特定表的结构 \d tablename
显示详细的表结构 \d+ tablename
其他用法
显示所有表 \dt
显示所有索引 \di
显示所有序列 \ds
显示所有视图 \dv
显示所有外部表 \dx
显示所有类型 \dT
显示所有复合类型 \dC
显示所有域 \dD
显示所有函数 \df
显示所有聚合函数 \da
示所有操作符 \do
显示所有规则 \dr
显示所有触发器 \dy
显示所有外部数据包装器 \dew
显示所有外部服务器 \des

3.修改表的属性为表增加一列

test=# ALTER TABLE address ADD COLUMN p_col1 bigint;

验证新增的列

test=# \d address
数据表 "public.address"
栏位 | 类型 | 校对规则 | 可空的 | 预设
-----------+----------------------------+----------+----------+--------------------------------------------
addressid | integer | | not null | nextval('address_addressid_seq'::regclass)
userid | integer | | not null |
realname | character varying(50 char) | | not null |
telephone | character varying(20 char) | | not null |
province | character varying(20 char) | | not null |
city | character varying(20 char) | | not null |
district | character varying(20 char) | | not null |
address | text | | not null |
p_col1 | bigint | | |
索引：
"market_address_constraint_1" PRIMARY KEY, btree (addressid)

4.增删列上的默认值

新增默认值

test=# ALTER TABLE address ALTER COLUMN p_col1 SET DEFAULT 1;

删除默认值

test=# ALTER TABLE address ALTER COLUMN p_col1 drop DEFAULT ;

验证默认值变化

test=# \d address

5.修改字段的数据类型,名称

修改字段类型

test=# ALTER TABLE address MODIFY p_col1 INT;

修改字段名称

test=# ALTER TABLE address RENAME p_col1 to p_col;

验证字段变化

test=# \d address

6.删除列

删除address表中的p_col列

test=# ALTER TABLE address DROP COLUMN p_col;

验证列是否删除

test=# \d address

7.删除表

删除address表

test=# DROP TABLE address;

查看表信息

test=# \d address
*************************
Did not find any relation named "address".

四.用户管理

1.创建用户

创建用户普通用户

test=# CREATE USER lsih PASSWORD 'kingbase@123';
CREATE ROLE

创建用户，使其拥有创建数据库的权限

test=# CREATE USER lsih2 CREATEDB PASSWORD 'kingbase@123';
CREATE ROLE

备注:注意这里返回的是CREATE ROLE!!!
CREATE USER 和 CREATE ROLE 实际上是同一个命令的两个别名。
CREATE USER 是 CREATE ROLE 的一种特定形式，用于创建具有登录权限的角色。
因此，当使用 CREATE USER 时，系统实际上是在执行 CREATE ROLE，并且默认会赋予该角色登录权限。
这里系统实际上是在执行：
CREATE ROLE lsih WITH LOGIN PASSWORD 'kingbase@123';

2 查看用户

test=# SELECT * FROM pg_user;
usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig
---------+----------+-------------+----------+---------+--------------+----------+----------+-----------
sao | 9 | f | f | f | f | ******** | |
sso | 8 | f | f | f | f | ******** | |
system | 10 | t | t | t | t | ******** | |
lsih | 16407 | f | f | f | f | ******** | |
lsih2 | 16409 | t | f | f | f | ******** | |
(5 行记录)

备注:
usecreatedb:用户是否有创建数据库的权限
usesuper:用户是否是超级用户
userepl:用户是否有复制（replication）权限
usebypassrls:用户是否可以绕过行级安全（Row-Level Security, RLS）
valuntil:用户密码的有效期

test=# \du
********* QUERY **********
select r.rolname, r.rolsuper, r.rolinherit,
r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
r.rolconnlimit, r.rolvaliduntil,
ARRAY(select b.rolname
from pg_catalog.pg_auth_members m
join pg_catalog.pg_roles b on (m.roleid = b.oid)
where m.member = r.oid) as memberof
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
WHERE r.rolname !~ '^pg_'
ORDER BY 1;
**************************
角色列表
角色名称 | 属性 | 成员属于
----------+--------------------------------------------+----------
kcluster | 无法登录 | {}
lsih | | {}
lsih2 | 建立 DB | {}
sao | 没有继承 | {}
sso | 没有继承 | {}
system | 超级用户, 建立角色, 建立 DB, 复制, 绕过RLS | {}

备注:可以看到\du底层用表是pg_roles,与pg_user对比
pg_user 只显示具有登录权限的用户角色
pg_roles 显示所有角色,包括不能登录的角色
4.将用户lsih的登录密码修改为Abcd@123

方法1:

ALTER USER lsih WITH PASSWORD '123123';

方法2:

ALTER USER lsih PASSWORD '123123';

方法3:

ksql -h 192.168.126.91 -p 54321 -U lsih -d test -E

登陆进去后使用\password修改
\password
5.授权lsih2建立角色

test=> ALTER USER lsih2 CREATEROLE;

验证权限是否添加

test=> \du lsih2
角色列表
角色名称 | 属性 | 成员属于
----------+-------------------+----------
lsih2 | 建立角色, 建立 DB | {}

备注:
授权语法只会新增权限,不会覆盖权限
撤销权限:

ALTER USER lsih2 WITH CREATEROLE;

6.锁定lsih帐户

ALTER USER lsih ACCOUNT LOCK;

解锁

ALTER ROLE lsih ACCOUNT UNLOCK;
ALTER ROLE lsih WITH LOGIN;

备注:
这里在锁住lsih之前,他是一个user,如果用alter role它会报错
ERROR: lsih is not a role
锁住之后他就是一个role了,如果用alter user它会报错
ERROR: lsih is not a user

7.开一个窗口登陆用户,删除用户

test=# DROP USER lsih;
ERROR: current logined user cannot be dropped

与其他数据库一样,正在连接用户无法删除
-- 锁用户

ALTER USER lsih ACCOUNT LOCK;

-- 查找活动会话

SELECT pid, usename, client_addr, application_name
FROM pg_stat_activity
WHERE usename = 'lsih';

-- 终止所有与该用户相关的会话

SELECT pg_terminate_backend(26661)
FROM pg_stat_activity
WHERE usename = 'lsih';

-- 删除角色

DROP ROLE lsih;

五.Schema管理

1.创建模式

CREATE SCHEMA ds_lsih3;

验证schema是否创建

test=# \dn
********* QUERY **********
SELECT n.nspname AS "Name",
pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
AND n.nspname <> 'sys'
AND n.nspname <> 'sys_catalog'
ORDER BY 1;
**************************
架构模式列表
名称 | 拥有者
------------------+--------
anon | system
dbms_sql | system
ds_lsih3 | system
perf | system
public | system
src_restrict | system
sys_hm | system
sysaudit | system
sysmac | system
wmsys | system
xlog_record_read | system
(11 行记录)

备注:这里的条件就是单纯限制不显示系统schema
Schema作用:
通过管理Schema，允许多个用户使用同一数据库而不相互干扰;
每个数据库包含一个或多个Schema
--层次结构
数据库 是更高层次的容器，它包含所有的数据和模式。
Schema 是数据库内部的子容器，用于组织和管理数据库对象。
换句话说，Schema存在于数据库中，而数据库存在于数据库服务器实例中。
--命名空间
数据库提供了一个独立的命名空间，使得不同数据库中的对象可以同名而不冲突。
模式提供了一个进一步的命名空间，使得同一数据库中的不同模式可以包含同名的对象。
--访问和权限：
用户连接到数据库后，可以访问该数据库中的模式和对象。
数据库级别的权限控制可以限制哪些用户可以连接到数据库。
模式级别的权限控制可以限制哪些用户可以访问或操作特定模式中的对象。
2.将当前模式 ds_lsih3 更名为 ds_lsih_new

test=# ALTER SCHEMA ds_lsih3 RENAME TO ds_lsih_new;
ALTER SCHEMA

验证schema名字是否修改

test=# \dn
架构模式列表
名称 | 拥有者
------------------+--------
anon | system
dbms_sql | system
ds_lsih_new | system
perf | system
public | system
src_restrict | system
sys_hm | system
sysaudit | system
sysmac | system
wmsys | system
xlog_record_read | system
(11 行记录)

3.删除模式ds_lsih_new

DROP SCHEMA ds_lsih_new;