Kingbase数据库基础知识(表空间、数据库、表相关操作)集合2

原创小白学信创²⁰²² ᯤ⁶ᴳ All China Database Union 2024-11-05

1278

前言：Kingbase数据库基础知识集合2，接上篇：https://www.modb.pro/db/1847168695129440256

一、数据库增删改查

1.1、创建用户

说明：

创建语法：

CREATE USER user1 PASSWORD ‘kingbase’;

[kingbase@Node1 ~]$ ksql -Usystem -dtest
用户 system 的口令：
输入 "help" 来获取帮助信息.

test=# CREATE USER user1 PASSWORD 'kingbase';
CREATE ROLE
test=#

1.2、查看用户

说明：

元命令语法：
\du

test=# \du
                             角色列表
 角色名称 |                    属性                    | 成员属于
----------+--------------------------------------------+----------
 kcluster | 无法登录                                   | {}
 role1    | 无法登录                                   | {}
 role2    | 无法登录                                   | {}
 sao      | 没有继承                                   | {}
 sso      | 没有继承                                   | {}
 sy       |                                            | {}
 system   | 超级用户, 建立角色, 建立 DB, 复制, 绕过RLS | {}
 user1    |                                            | {role1}
 user2    | 没有继承                                   | {role1}

test=#

1.3、创建数据库db01，属主为user1、编码为UTF8、参照模板template0、连接数为10

说明：

创建语法：

CREATE DATABASE db01 OWNER user1 TEMPLATE template0 ENCODING ‘UTF8’ CONNECTION LIMIT 10;

test=# CREATE DATABASE db01 OWNER user1 TEMPLATE template0 ENCODING 'UTF8' CONNECTION LIMIT 10;
CREATE DATABASE

1.4、检查数据库的定义信息

说明：

元命令语法：
\l+ db01

test=# \l+ db01
                                          数据库列表
 名称 | 拥有者 | 字元编码 |  校对规则   |    Ctype    | 存取权限 | 大小  |   表空间    | 描述
------+--------+----------+-------------+-------------+----------+-------+-------------+------
 db01 | user1  | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |          | 14 MB | sys_default |
(1 行记录)

test=#

二、数据库属性修改

2.1、修改数据库db01属主修改为user2

说明：

修改语法：
ALTER DATABASE db01 OwNER TO user2;

1、修改属主

test=# ALTER DATABASE db01 OwNER TO user2;
ALTER DATABASE
test=#

2、查看数据库信息

说明：

此时发现，属主已变为user2.

test=# \l+ db01
                                          数据库列表
 名称 | 拥有者 | 字元编码 |  校对规则   |    Ctype    | 存取权限 | 大小  |   表空间    | 描述
------+--------+----------+-------------+-------------+----------+-------+-------------+------
 db01 | user2  | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |          | 14 MB | sys_default |
(1 行记录)

test=#

2.2、修改数据库db01名称为db02

说明：

修改语法：

ALTER DATABASE db01 RENAME TO db02;

1、修改属主

test=# ALTER DATABASE db01 RENAME TO db02;
ALTER DATABASE

2、查看数据库变更后的信息。

test=# \l
                                   数据库列表
    名称    | 拥有者 | 字元编码 |  校对规则   |    Ctype    |     存取权限
------------+--------+----------+-------------+-------------+-------------------
 cssdserver | sy     | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |
 db02       | user2  | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |
 kingbase   | system | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |
 security   | system | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |
 template0  | system | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/system        +
            |        |          |             |             | system=CTc/system
 template1  | system | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/system        +
            |        |          |             |             | system=CTc/system
 test       | system | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |
(7 行记录)

test=#

三、删除数据库

3.1、使用超级管理员system连接数据库db02;

说明：

正在使用的数据库无法删除。

ksql -Usystem -db02

[kingbase@Node1 ~]$ ksql -Usystem -ddb02
用户 system 的口令：
输入 "help" 来获取帮助信息.

db02=# drop database db02 ;
ERROR:  cannot drop the currently open database
db02=#

3.2、使用user1账户连接数据库test;

说明：

非属主用户无法删除数据库。

[kingbase@Node1 ~]$ ksql -Uuser1 -dtest
用户 user1 的口令：
输入 "help" 来获取帮助信息.

test=> drop database db02 ;
ERROR:  must be owner of database db02
test=>

3.3、使用超级管理员system连接数据库db02

说明：

使用超级用户或属主账户删除非使用的数据库。

[kingbase@Node1 ~]$ ksql  -Uuser2 -dtest
用户 system 的口令：
输入 "help" 来获取帮助信息.

test=# drop database db02 ;
DROP DATABASE
test=#

四、模式增删改查

4.1、查询模式

说明：

列出test库中的模式清单

\dn+

[kingbase@Node1 ~]$ ksql -Uuser1 -dtest
用户 user1 的口令：
输入 "help" 来获取帮助信息.

test=> \dn+
                             架构模式列表
       名称       | 拥有者 |     存取权限     |          描述
------------------+--------+------------------+------------------------
 anon             | system | system=UC/system+|
                  |        | sso=UC/system    |
 dbms_sql         | system |                  |
 perf             | system | system=UC/system |
 public           | system | system=UC/system+| standard public schema
                  |        | =UC/system       |
 src_restrict     | system | system=UC/system+|
                  |        | sso=UC/system    |
 sys_hm           | system |                  |
 sysaudit         | system | system=UC/system+|
                  |        | sso=UC/system   +|
                  |        | sao=UC/system   +|
                  |        | =UC/system       |
 sysmac           | system | system=UC/system+|
                  |        | sso=UC/system   +|
                  |        | =U/system        |
 wmsys            | system | system=UC/system+|
                  |        | =UC/system       |
 xlog_record_read | system |                  |
(10 行记录)

test=>

4.2、创建模式

说明：

新建模式user1，属主为user1

CREATE SCHEMA user1 AUTHORIZATION user1;

[kingbase@Node1 ~]$ ksql -Usystem -dtest
用户 system 的口令：
输入 "help" 来获取帮助信息.

test=# 
test=# CREATE SCHEMA user1 AUTHORIZATION user1;
CREATE SCHEMA
test=#

4.3、检查模式定义信息

说明：

检查模式定义的元命令：\dn+ user1

test=# \dn+ user1
           架构模式列表
 名称  | 拥有者 | 存取权限 | 描述
-------+--------+----------+------
 user1 | user1  |          |
(1 行记录)

test=#

4.4、修改模式

说明：

修改模式user1的属主为system

修改脚本：ALTER SCHEMA user1 OWNER TO system;

test=# ALTER SCHEMA user1 OWNER TO system;
ALTER SCHEMA
test=#

4.5、修改模式user1的名称为schema01

说明：

-修改脚本： ALTER SCHEMA user1 RENAME TO schema01;

test=# ALTER SCHEMA user1 RENAME TO schema1;
ALTER SCHEMA
test=#

4.6、检查修改后的模式

说明：

查询元命令：\dn+ schema1

test=# \dn+ schema1
            架构模式列表
  名称   | 拥有者 | 存取权限 | 描述
---------+--------+----------+------
 schema1 | system |          |
(1 行记录)

test=#

4.7、删除模式

1、用管理员system连接数据库test

[kingbase@Node1 ~]$ ksql -Usystem -dtest
用户 system 的口令：
输入 "help" 来获取帮助信息.

test=#

2、在schema01下创建t01表

说明：

创建脚本：CREATE TABLE schema1.t01(id int);

test=# CREATE TABLE schema1.t01(id int);
CREATE TABLE

3、删除模式schema01，提示“有对象依赖于该模式、无法删除”

说明：

-删除脚本：DROP SCHEMA schema1;

test=# DROP SCHEMA schema1;
ERROR:  cannot drop schema schema1 because other objects depend on it
描述:  table schema1.t01 depends on schema schema1
提示:  Use DROP ... CASCADE to drop the dependent objects too.

4、检查模式schema01中的对象信息

说明：

查看模式元命令：\d+ schema1.*

test=# \d+ schema1.*
                        数据表 "schema1.t01"
 栏位 |  类型   | 校对规则 | 可空的 | 预设 | 存储  | 统计目标 | 描述
------+---------+----------+--------+------+-------+----------+------
 id   | integer |          |        |      | plain |          |
访问方法 heap

5、通过cascade删除模式schema01

说明：

删除模式脚本：DROP SCHEMA schema1 CASCADE;

test=# DROP SCHEMA schema1 CASCADE;
NOTICE:  drop cascades to table schema1.t01
DROP SCHEMA
test=#

五、表空间的增删改查

5.1、查询表空间的清单

1、使用元命令查看表空间信息

说明：

查看表空间的元命令：\db+

test=# \db+
                                  表空间列表
    名称     | 拥有者 |        所在地        | 存取权限 | 选项 |  大小  | 描述
-------------+--------+----------------------+----------+------+--------+------
 cssd        | sy     | /Kingbase/ES/V9/cssd |          |      | 21 MB  |
 sys_default | system |                      |          |      | 85 MB  |
 sys_global  | system |                      |          |      | 737 kB |
 sysaudit    | system |                      |          |      | 24 kB  |
(4 行记录)

test=#

2、使用数据字典查看表空间信息

说明：

创建脚本：使用数据字典查看表空间脚本：SELECT oid,spcname FROM sys_tablespace;

test=# SELECT oid,spcname FROM sys_tablespace;
  oid  |   spcname
-------+-------------
  1663 | sys_default
  1664 | sys_global
  1986 | sysaudit
 16385 | cssd
(4 行记录)

test=#

3、查看数据库的默认表空间信息

说明：

创建脚本：使用元命令查看数据默认表空间信息：\l+

test=# \l+
                                                                    数据库列表
    名称    | 拥有者 | 字元编码 |  校对规则   |    Ctype    |     存取权限      | 大小  |   表空间    |                    描述

------------+--------+----------+-------------+-------------+-------------------+-------+-------------+----------------------------
----------------
 cssdserver | sy     | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |                   | 21 MB | cssd        |
 db02       | user2  | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |                   | 14 MB | sys_default |
 kingbase   | system | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |                   | 14 MB | sys_default | default administrative conn
ection 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 da
tabases
            |        |          |             |             | system=CTc/system |       |             |
 test       | system | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |                   | 14 MB | sys_default | default administrative conn
ection database
(7 行记录)

test=#

5.2、创建表空间

1、创建表空间文件系统目录

说明：

使用root账户创建目录/opt/server1

2、在根目录下新建目录/opt/server1

说明：

创建系统表空间目录：mkdir -p /opt/server1

3、设定目录/opt/server1的属主、属组、权限

说明：

修改目录权限，属主：

chown -R kingbase:kingbase /opt/server1

chmod -R 700 /opt/server1

4、检查目录dir01的定义信息

说明：

查看文件权限：ls -ld /opt/server1

[root@Node1 ~]# mkdir -p /opt/server1
[root@Node1 ~]# chown -R kingbase:kingbase /opt/server1
[root@Node1 ~]# chmod -R 700 /opt/server1
[root@Node1 ~]# ls -ld /opt/server1
drwx------ 2 kingbase kingbase 6 11月  4 22:49 /opt/server1
[root@Node1 ~]#

5、新建表空间tabspace01，关联目录/opt/server1，表空间属主默认

说明：

创建表空间脚本：

CREATE TABLESPACE tabspace01 LOCATION ‘/opt/server1’;

[kingbase@Node1 ~]$ ksql -U system -d test
用户 system 的口令：
输入 "help" 来获取帮助信息.

test=# \db
                 表空间列表
    名称     | 拥有者 |        所在地
-------------+--------+----------------------
 cssd        | sy     | /Kingbase/ES/V9/cssd
 sys_default | system |
 sys_global  | system |
 sysaudit    | system |
(4 行记录)

test=#
test=# CREATE TABLESPACE tabspace01 owner user2  LOCATION '/opt/server1';
CREATE TABLESPACE
test=#

6、查看表空间tbs01的信息

test=# \db
                 表空间列表
    名称     | 拥有者 |        所在地
-------------+--------+----------------------
 cssd        | sy     | /Kingbase/ES/V9/cssd
 sys_default | system |
 sys_global  | system |
 sysaudit    | system |
 tabspace01  | user2  | /opt/server1
(5 行记录)

test=#

六、修改表空间

6.1、修改表空间tabspace01的属主为user1

ALTER TABLESPACE tabspace01 OWNER TO user1;

test=# \db
                 表空间列表
    名称     | 拥有者 |        所在地
-------------+--------+----------------------
 cssd        | sy     | /Kingbase/ES/V9/cssd
 sys_default | system |
 sys_global  | system |
 sysaudit    | system |
 tabspace01  | user2  | /opt/server1
(5 行记录)

test=# ALTER TABLESPACE tabspace01 OWNER TO user1;
ALTER TABLESPACE
test=#

6.2、修改表空间tabspace01的名称为tabspace02

ALTER TABLESPACE tabspace01 RENAME TO tabspace02;

test=# ALTER TABLESPACE tabspace01 RENAME TO tabspace02;
ALTER TABLESPACE
test=#

6.3、修改数据库默认表空间为tabspace02

1、查看数据库所在表空间

test=# SELECT datname, spcname FROM pg_database JOIN pg_tablespace ON pg_database.dattablespace = pg_tablespace.oid WHERE datname = 'db02';
datname |   spcname
---------+-------------
db02    | sys_default
(1 行记录)

test=#

2、修改数据库db02默认表空间为tabspace02

ALTER DATABASE db02 SET TABLESPACE tabspace02;

test=# ALTER DATABASE db02 SET TABLESPACE tabspace02;
ALTER DATABASE
test=#

3、再次查看数据库所在表空间

方法1：

test=# SELECT datname, spcname FROM pg_database JOIN pg_tablespace ON pg_database.dattablespace = pg_tablespace.oid WHERE datname = 'db02';
 datname |  spcname
---------+------------
 db02    | tabspace02
(1 行记录)

test=#

方法2：

test=# \l+ db02
                                         数据库列表
 名称 | 拥有者 | 字元编码 |  校对规则   |    Ctype    | 存取权限 | 大小  |   表空间   | 描述
------+--------+----------+-------------+-------------+----------+-------+------------+------
 db02 | user2  | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |          | 14 MB | tabspace02 |
(1 行记录)

test=#

6.4、移动表到新的表空间

1、查看表所在表空间

test=# SELECT n.nspname as "Schema",c.relname as "Table",t.spcname as "Tablespace" FROM   pg_class c JOIN pg_namespace n ON n.oid>
Schema | Table | Tablespace
--------+-------+------------
public | t01   | tabspace02
(1 行记录)

test=#

2、移动表到新的表空间

ALTER TABLE t01 SET TABLESPACE tabspace02;

test=# ALTER TABLE t01 SET TABLESPACE tabspace02 ;
ALTER TABLE
test=#

七、删除表空间

7.1、删除表空间tabspace02

1、由管理员system连接数据库test

ksql -U system -d test

2、删除表空间tabspace02失败，提示表空间非空

[kingbase@Node1 ~]$ ksql -U system -d test
用户 system 的口令：
输入 "help" 来获取帮助信息.

test=# DROP TABLESPACE tabspace02;
ERROR:  tablespace "tabspace02" is not empty
test=#

3、排查tabspace02表空间中有哪些对象并移动或删除这些对象

1、查看tabspace02表空间的OID


[kingbase@Node1 ~]$ ksql -U system -d test
用户 system 的口令：
输入 "help" 来获取帮助信息.

test=# SELECT oid,spcname FROM sys_tablespace WHERE spcname='tabspace02';
  oid  |  spcname
-------+------------
 40966 | tabspace02
(1 行记录)

test=#

2、通过表空间的OID查找该表空间中存在对象的数据库列表


test=# SELECT datname FROM sys_database WHERE oid IN (SELECT sys_tablespace_databases(40966));
 datname
---------
 db02
 test
(2 行记录)

test=#

3、根据上一步获取的数据库列表登录每个数据库并查找放在tabspace02表空间中的对象

test=# SELECT relname FROM sys_class WHERE reltablespace=40966;
 relname
---------
 t01
(1 行记录)

test=#

4、迁移所有在表空间tabspace02中的对象到其它表空间

test=# ALTER TABLE t01 SET TABLESPACE sys_default;
ALTER TABLE
test=#

SELECT relname FROM sys_class WHERE reltablespace=40966;

5、排查tabspace02表空间被设置为哪些数据库的默认表空间并解除关联

（1）查看默认表空间为tabspace02的数据库信息

ksql -U system -d test
\l+

（2）解除数据库与tbs10的关联

ALTER DATABASE db01 SET TABLESPACE sys_default;

6、再次登录管理员账户system删除表空间tabspace02

说明：

解除数据库与表空间的关联后，使用管理员账户即可删除表空间。

[kingbase@Node1 ~]$ ksql -U system -d test
用户 system 的口令：
输入 "help" 来获取帮助信息.

test=# DROP TABLESPACE tabspace02 ;
DROP TABLESPACE
test=#

八、补充

8.1、查看db01数据库当前的默认表空间

说明：

查看db01数据库所在的表空间的元命令：\l+ db02

test=# \l+ db02
                                         数据库列表
 名称 | 拥有者 | 字元编码 |  校对规则   |    Ctype    | 存取权限 | 大小  |   表空间   | 描述
------+--------+----------+-------------+-------------+----------+-------+------------+------
 db02 | user2  | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |          | 14 MB | tabspace02 |
(1 行记录)

test=#

8.2、查看test数据库中t01表当前所在的表空间

说明：

查看表所在的表空间的元命令：\d+ t01

\d+ t01

test=# \d+ t01
                         数据表 "public.t01"
 栏位 |  类型   | 校对规则 | 可空的 | 预设 | 存储  | 统计目标 | 描述
------+---------+----------+--------+------+-------+----------+------
 id   | integer |          |        |      | plain |          |
表空间："tabspace02"
访问方法 heap

test=#

8.3、查看新表空间的名称、属主等信息

说明：

查看表空间元命令：\db+ tabspace02

test=# \db+ tabspace02
                             表空间列表
    名称    | 拥有者 |    所在地    | 存取权限 | 选项 | 大小  | 描述
------------+--------+--------------+----------+------+-------+------
 tabspace02 | user1  | /opt/server1 |          |      | 14 MB |
(1 行记录)

test=#

九、总结

9.1、创建表空间

在Kingbase数据库中，创建表空间是为了将相关的数据库逻辑数据（如表或索引等）组合在一起，并指定它们在文件系统中的存储位置。表空间允许用户为频繁使用和不频繁使用的数据库对象分配不同的存储，或者在特定的数据库对象上控制I/O性能。

创建表空间的步骤：

创建目录结构：在操作系统上创建目录结构，以为该表空间指定存储的物理位置。

设置目录权限：为该目录设置正确的所有权。

使用超级用户创建表空间：使用CREATE TABLESPACE语句创建表空间，并指定目录作为该表空间的LOCATION。

选择合适的表空间名：表空间名不能以sys_开头，因为sys_名称是为系统表空间保留的。

9.2、创建数据库

在Kingbase数据库中，创建数据库是为了存储和管理数据。数据库是一组SQL对象（“数据库对象”）的命名集合，每个数据库中可以包含多个数据库对象，如表、索引、序列等。

创建数据库的步骤：

使用CREATE DATABASE语句：创建数据库，并可以指定数据库的所有者、编码规则、校对规则等属性。

指定数据库所有者：如果执行用户具有合适的权限，可以通过-O选项指定一个不同的所有者。

9.3、创建表

在Kingbase数据库中，创建表是为了存储和组织数据。表是数据库中最基本的对象，它由一系列的行和列组成，每行代表一个记录，每列代表一个字段。

创建表的步骤：

使用CREATE TABLE语句：创建表，并指定表的名称、列名、数据类型、约束等属性。

指定表的所有者：如果执行用户具有合适的权限，可以通过-O选项指定一个不同的所有者。

9.4、创建模式

在Kingbase数据库中，模式（Schema）是一个非常重要的概念。模式可以理解为数据库中用户定义的命名空间，它包含了用户定义的所有数据库对象，如表、视图、索引、序列、函数、过程等。通过模式，我们可以有效地组织和管理数据库对象，提高数据库的可维护性和可管理性。

创建模式的步骤：

使用CREATE SCHEMA语句：创建模式，并指定模式的名称。

在模式中创建对象：在新创建的模式中创建表、视图等数据库对象。

最后修改时间：2024-11-05 14:19:17

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

信创数据库学习中心（共80篇）

信创数据库学习中心