人大金仓数据库常用数据库对象

最新推荐文章于 2024-05-10 17:52:07 发布

沉舟侧畔千帆过_

最新推荐文章于 2024-05-10 17:52:07 发布

阅读量543

点赞数

文章标签：金仓数据库 kingbase 数据库人大金仓 DBA

本文链接：https://blog.csdn.net/arthemis_14/article/details/133172688

版权

关键字：

数据对象，表空间

1.表空间

表空间用于存储它的数据库对象

表空间允许在文件系统里定义数据库对象存储的文件存放位置

一个数据库可有一个或多个表空间；一个表空间只隶属于一个数据库

表空间创建语法：

CREATE TABLESPACE tablespace_name
    [ OWNER { new_owner | CURRENT_USER | SESSION_USER } ]
    LOCATION 'directory'
    [ WITH ( tablespace_option = value [, ... ] ) ]

修改表空间语法：

ALTER TABLESPACE name RENAME TO new_name
ALTER TABLESPACE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER TABLESPACE name SET ( tablespace_option = value [, ... ] )
ALTER TABLESPACE name RESET ( tablespace_option [, ... ] )

表空间删除语法：

DROP TABLESPACE [ IF EXISTS ] name

创建表空间示例：

test=# CREATE TABLESPACE mysp LOCATION '/home/test/mysp';
CREATE TABLESPACE
test=# \db
          List of tablespaces
    Name     | Owner  |    Location
-------------+--------+-----------------
 mysp        | SYSTEM | /home/test/mysp
 sys_default   | SYSTEM |
 sys_global    | SYSTEM |
 sysaudit      | SYSTEM |

注意：创建表空间指定的LOCATION路径需要提前创建，并使用绝对路径

2.数据库

数据库是按照数据结构来组织、存储和管理数据的仓库。是一个长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合。

每个连接只能访问一个数据库

各个数据库在物理上分离

创建数据库语法：

CREATE DATABASE name
    [ [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ TABLESPACE [=] tablespace_name ]
           [ ALLOW_CONNECTIONS [=] allowconn ]
           [ CONNECTION LIMIT [=] connlimit ]
           [ IS_TEMPLATE [=] istemplate ] ]

OWNER：数据库的所有者

TEMPLATE：数据库模板

ENCODING：数据库使用的字符集

目前，KingbaseES支持ASCII、GBK、GB18030和UNICODE四种字符集(UNICODE为 UTF-8， GB18030向下兼容GBK)

TABLESPACE：数据库所在的表空间空间

LC_COLLATE:字符串排序顺序

LC_CTYPE:字符分类

CONNECTION LIMIT：数据库可以接受的最大连接数

修改数据库语法：

ALTER DATABASE name [ [ WITH ] option [ ... ] ]
where option can be:
    ALLOW_CONNECTIONS allowconn
    CONNECTION LIMIT connlimit
    IS_TEMPLATE istemplate
ALTER DATABASE name RENAME TO new_name
ALTER DATABASE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER DATABASE name SET TABLESPACE new_tablespace
ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER DATABASE name SET configuration_parameter FROM CURRENT
ALTER DATABASE name RESET configuration_parameter
ALTER DATABASE name RESET ALL

删除数据库语法：

DROP DATABASE [ IF EXISTS ] name

示例一：

创建数据库mydb1

CREATE DATABASE mydb1;

示例二：

创建数据库指定表空间mysp

CREATE DATABASE mydb2 TABLESPACE mysp;

示例三：

创建数据库指定所有者owner

CREATE DATABASE mydb3 OWNER tester;

示例四：

创建数据库指定编码GBK、排序方式按C语言规则、模板为template0

CREATE DATABASE mydb4 ENCODING GBK LC_CTYPE 'C' LC_COLLATE 'C' TEMPLATE template0;

示例五：

修改数据库mydb1配置参数ENABLE_FUNC_COLNAME参数值为ON

ALTER DATABASE mydb1 SET ENABLE_FUNC_COLNAME=ON;

3.模式

数据库对象的集合，如：表、视图、序列等

创建模式语法：

CREATE SCHEMA schema_name [ AUTHORIZATION role_specification ] [ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION role_specification [ schema_element [ ... ] ]
CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION role_specification ]
CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specification
其中role_specification值为user_name|CURRENT_USER|SESSION_USER

修改模式语法:

ALTER SCHEMA name RENAME TO new_name
ALTER SCHEMA name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }

删除模式语法:

DROP SCHEMA [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

示例一：

如果模式mysch1不存在，创建模式mysch1

CREATE SCHEMA IF NOT EXISTS mysch1;

示例二：

创建模式指定认证用户为tester

CREATE SCHEMA my_sch2 AUTHORIZATION tester;

示例三：

通过设置搜索路径search_path切换至指定模式下my_sch1

SET SEARCH_PATH='my_sch1';
SHOW SEARCH_PATH;

4.表

表是数据库中用来存储数据的对象，是有结构的数据的集合，是整个数据库系统的基础

表采用二维结构

一个表包含一或多个属性

属性用来定义列结构

表中的每行数据称为一个元组

每个属性和元组都是不可再分的

创建表的部分语法：

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [
  { column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
    | table_constraint
    | LIKE source_table [ like_option ... ] }
    [, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ USING method ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]
[ { ENABLE | DISABLE } [ VALIDATE | NOVALIDATE ] constraint [ ... ] ]

临时表

示例一：创建局部临时表

test=# CREATE TEMP TABLE my_temp1(id int);
CREATE TABLE
test=# BEGIN;
BEGIN
test=# INSERT INTO my_temp1 VALUES(1);
INSERT 0 1
test=# INSERT INTO my_temp1 VALUES(2);
INSERT 0 1
test=# SELECT * FROM my_temp1;
 id
----
  1
  2
(2 rows)
test=# COMMIT;
COMMIT
test=# SELECT * FROM my_temp1;
 id
----
  1
  2
(2 rows)
test=# \c
You are now connected to database "test" as user "SYSTEM".
test=# SELECT * FROM my_temp1;
ERROR:  relation "my_temp1" does not exist

如上\c切换会话后，局部临时表my_temp1被销毁,查询报错表不存在

示例二：创建全局临时表

test=# CREATE GLOBAL TEMPORARY TABLE my_temp2(tid int) ON COMMIT DELETE ROWS; --提交时删除临时表数据
CREATE TABLE
test=# BEGIN;
BEGIN
test=# INSERT INTO my_temp2 VALUES(1);
INSERT 0 1
test=# INSERT INTO my_temp2 VALUES(2);
INSERT 0 1
test=# SELECT * FROM my_temp2;
 tid
-----
   1
   2
(2 rows)
test=# COMMIT;
COMMIT
test=# SELECT * FROM my_temp2;
 tid
-----
(0 rows)

--事务提交后查询全局临时表my_temp2,数据被清空

全局临时表ON COMMIT：描述临时表的事务提交行为，包括三个选项：

•PRESERVE ROWS：缺省行为，在事务结束，将不执行任何操作（保留数据）

•DELETE ROWS ：在事务结束时，将删除临时表的所有数据，本质上相当于在每次提交时自动执行一个TRUNCATE操作

•DROP：在当前事务结束时，临时表将被删除

表继承

如果把数据库中的表当做一个对象，表继承有点类似C++中类的继承特性：

--父表的所有字段被子表所继承

--子表可以有自己的子表，层层继承

--一个子表允许同时从多个父表继承

--使用SQL关键字ONLY，表示SQL操作只作用于父表本身，不涉及继承层次中的任何子表。SELECT、UPDATE、DELETE都支持该关键字，而INSERT不支持

--子表只会继承检查约束和非空约束，其他约束都不会被继承

表继承示例：

CREATE TABLE father(name TEXT PRIMARY KEY,population INT);
CREATE TABLE son1(state CHAR(2)) INHERITS (father);
CREATE TABLE son2(id INT) INHERITS (father);
--创建父表father，子表son1和son2分别继承父表
INSERT INTO father(name) VALUES('father');
INSERT INTO son1(name) VALUES('son1');
INSERT INTO son2(name) VALUES('son2');
--分别向父表和子表中插入数据
SELECT name FROM father;
--返回3行:father、son1、son2
SELECT name FROM son1;
--返回1行:son1
SELECT name FROM son2;
--返回1行:son2
SELECT name FROM only father;
--返回1行:father

分区表

分区指将逻辑上的一个大表拆分成较小的相对独立的子表

--分区的优点

-减少I/O访问量：

•查询时通过有选择地搜索分区，降低I/O访问量

•如何降低的呢？把大表切分后，对大表访问则可转换成：对少量子表的访问，从而减少I/O访问量

–均衡I/O：可把不同分区映射到不同磁盘以平衡I/O

–利于并行计算：通过表分区可实现I/O并行与查询并行

–增强可用性：

•当表某个分区出现故障时，它的其他分区的数据仍然可用

•管理员可以分区为单位进行备份与恢复操作

–维护方便：

•当表出现故障需时，只需修复故障分区

•使通过较小批处理窗口完成大型数据库对象的维护工作成为可能

--支持的分区形式

范围划分（RANGE）：表被根据一个关键列或一组列划分为“范围”，不同的分区的范围之间没有重叠。

例如，根据日期范围划分或者特定业务对象标识符划分。

列表划分（LIST）：通过显式地列出每一个分区中出现的键值来划分表。

哈希分区（HASH）：通过为每个分区指定模数和余数来对表进行分区。每个分区所持有的行都满足：

分区键的值除以为其指定的模数将产生为其指定的余数。

组合分区:是二级分区，分区的分区,以上三种形式的组合（一级分区和二级分区分别采用不同的分区方法）

创建范围分区表示例：

CREATE TABLE orders(id SERIAL,user_id INT4,create_time TIMESTAMP(0))  PARTITION BY RANGE(create_time);
test=# CREATE TABLE orders_history PARTITION OF orders FOR VALUES FROM ('2000-01-01') TO ('2020-03-01');
test=# CREATE TABLE orders_202003 PARTITION OF orders FOR VALUES FROM ('2020-03-01') TO ('2020-04-01');
test=# CREATE TABLE orders_202004 PARTITION OF orders FOR VALUES FROM ('2020-04-01') TO ('2020-05-01');

创建列表分区表示例：

CREATE TABLE cities(city_id BIGSERIAL NOT NULL,name TEXT NOT NULL,population BIGSERIAL) PARTITION BY LIST(LEFT(LOWER(name),1));
CREATE TABLE cities_a PARTITION OF cities(CONSTRAINT city_id_nonzero CHECK(city_id !=0)) FOR VALUES IN ('a');
CREATE TABLE cities_b PARTITION OF cities(CONSTRAINT city_id_nonzero CHECK(city_id !=0)) FOR VALUES IN ('b');
CREATE TABLE cities_c PARTITION OF cities(CONSTRAINT city_id_nonzero CHECK(city_id !=0)) FOR VALUES IN ('c');

5.视图

--视图：是查询语句由一个或多个表（或其他视图）导出的虚拟的表；视图本质上就是一个虚表的定义。视图是原生数据库数据的一种变换，是查看表中数据的另外一种方式。

--视图的用途包括：

限制数据访问，视图可以隐藏一些数据

简化复杂查询

逻辑数据独立性，视图提供了一个用户访问的接口，当底层表改变后，用户可以通过改变视图的定义来适配

创建视图语法：

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] [ FORCE ] VIEW name [ ( column_name [, ...] ) ]
    [ WITH ( view_option_name [= view_option_value] [, ... ] ) ]
    AS query
    [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

TEMPORARY：创建临时视图。当session结束时，临时视图也消失

RECURSIVE：创建一个递归视图

FORCE：创建的视图依赖检查失败，视图依然创建成功，将其状态设置为无效；依赖检查成功，所创建的视图装填为有效

WITH [ CASCADED | LOCAL ] CHECK OPTION ：该子句控制自动可更新视图的行为。指定该子句后，在该视图上执行INSERT或UPDATE命令时，会检查新行是否满足视图的定义

-LOCAL：根据直接定义在该视图上的条件检查新行

-CASCADE：根据该视图和所有底层基视图上的条件检查新行

注：CHECK OPTION不能和RECURSIVE一起使用

物化视图(Materialized View)是一个包含查询结果的数据库对象。它可以是一个远程数据的本地副本、某一个表的某些行列的子集，也可以是多个表连接的结果，或者聚集函数的计算结果。

创建物化视图的过程被称为物化，它类似于缓存计算代价昂贵的函数结果，它是某种形式的预计算，主要用于优化数据库查询性能。

物化视图示例：

CREATE MATERIALIZED VIEW mv_emp AS SELECT * FROM employee WHERE empid>2;
--创建物化视图mv_emp
INSERT INTO mv_emp VALUES(0,'tester0',0);
--向物化视图插入数据，报错ERROR:  cannot change materialized view "mv_emp"
INSERT INTO employee VALUES(0,'tester100',0);
REFRESH MATERIALIZED VIEW mv_emp;
--向基表中插入数据，通过刷新命令，更新物化视图数据

6.索引

什么是索引?

--索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

使用索引的优点：

--通过创建唯一索引，可以保证数据的唯一性

--提高数据记录的查询速度

--加快表与表之间的连接速度

使用索引的缺点：

--需要占用额外的物理存储空间

--如果表中的数据有变化，则索引也需要同步进行更新，对数据库性能有一定的影响

KES支持的索引类型：

--Btree索引：Btree索引使用Btree数据结构来存储索引数据，可用于处理等值查询和范围查询，包括<、<=、=、>=、>等运算符，以及BETWEEN、IN、IS NULL、IS NOT NULL等条件；Btree索引还可以用于查询结果集排序，如ORDER BY排序

--Hash索引：Hash索引基于哈希表实现，只能用于等值查询,特别是索引列的值非常长的等值查询。

--GiST索引：GiST(Generalized Search Tree)是一种平衡的树型结构访问方法，可作为一种基础模板来实现任意索引模式。GiST索引适用于多维数据和集合数据类型。

--SP-GiST索引：与GiST索引类似，可作为一种基础模板来实现多种搜索方法。

--GIN索引：是一种通用倒排索引，可以处理包含多个键值（如数组）。用它搜索全文或JSON键值效率很高

--BRIN索引：BRIN表示块范围索引。BRIN索引存储连续相邻的数据块统计信息，可以大大缩小索引占用空间。

创建索引语法：

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ]
    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ INCLUDE ( column_name [, ...] ) ]
    [ WITH ( storage_parameter = value [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]

UNIQUE：创建唯一索引

CONCURRENTLY ：并行创建索引，在构建索引时不阻塞写操作

Method：索引方法，默认是Btree索引

COLLATE collation：索引的排序规则

ASC：升序排序（默认）

DESC：下降排序

NULLS FIRST：空值排序在非空值前面。当指定DESC时这是默认行为

NULLS LAST：空值排序在非空值后面。当没有指定DESC时这是默认行为

示例一：创建多列索引（a,b）形式,查询条件where a=1 and b=2形式,查看执行计划索引生效

CREATE INDEX empid_multicol_idx ON employee USING BTREE(empid,name);
EXPLAIN ANALYZE SELECT * FROM employee WHERE empid=100 AND name='ABC100';

示例二：查询条件where a=1 形式，查看执行计划索引生效

EXPLAIN ANALYZE SELECT * FROM employee WHERE empid=100;

示例三：查询条件where b=2形式，查看执行计划索引不生效

EXPLAIN ANALYZE SELECT * FROM employee WHERE name='ABC100';

示例四：查询条件where a=1 or b=2形式，查看执行计划索引不生效

EXPLAIN ANALYZE SELECT * FROM employee WHERE empid=100 OR name='ABC200';

常用的索引方法：

--唯一索引：唯一索引可以保证某个字段值是唯一的，也可以保证多个字段组合值是唯一的，其中字段中的null值被认为是不同的值。

--唯一索引是Btree类型的索引，对于表中有唯一性约束或主键约束的字段（或字段组合）来说，KES会自动创建相应的唯一索引，不需要手动再创建唯一索引。示例：

CREATE UNIQUE INDEX cardno_uni_idx ON employee(cardno);
EXPLAIN ANALYZE SELECT * FROM employee WHERE cardno=100;

--表达式索引：表达式索引可以使用一个函数或表达式的计算结果作为索引的字段。被索引的函数或表达式要用圆括号括起来。表达式索引适用于查询速度远比插入更新速度要求高的场景，不适用于插入更新频繁的情况。

示例：

CREATE INDEX name_low_idx ON employee(LOWER(name));
EXPLAIN ANALYZE SELECT * FROM employee WHERE LOWER(name)='abc100';

7.约束

什么是约束？

约束：语义施加在数据上的限制，保证数据的正确性、有效性和完整性

主要内容包括：

数据应满足的强制规则，例如字段的唯一性和非空约束

数据应满足的完整性规则，例如主外键约束

数据应满足的业务规则，例如CHECK约束

–主要用途包括：

防止生成无效或错误的数据

确保数据使用满足业务规则

防止非法使用数据

保证数据库始终处于正确和一致的状态

示例一：约束的粒度：列级约束

CREATE TABLE test1(id INT PRIMARY KEY,name TEXT);

\d test1

示例二：约束的粒度：表级约束

CREATE TABLE test2(id INT,name TEXT,PRIMARY KEY(id));

\d test2

示例一：非空约束


CREATE TABLE test_null(id int not null);
INSERT INTO test_null VALUES(NULL);
--违反非空约束报错ERROR:  null value in column "id" violates not-null constraint

示例二：CHECK约束

CREATE TABLE test_check(id INT,CHECK(id>0));
INSERT INTO test_check VALUES(1);
INSERT INTO test_check VALUES(-1);

--违反check约束报错ERROR: new row for relation "test_check" violates check constraint "test_check_id_check"

示例三：唯一约束

CREATE TABLE test_unique(id INT UNIQUE);
INSERT INTO test_unique VALUES(NULL);
INSERT INTO test_unique VALUES(1);
INSERT INTO test_unique VALUES(1);
--违反唯一值约束报错ERROR:  duplicate key value violates unique constraint "test_unique_id_key"

示例四：主键约束

CREATE TABLE test_pri(id INT PRIMARY KEY);
INSERT INTO test_pri VALUES(NULL);
--违反非空约束报错ERROR:  null value in column "id" violates not-null constraint
INSERT INTO test_pri VALUES(1);
INSERT INTO test_pri VALUES(1);
违反唯一值约束报错ERROR:  duplicate key value violates unique constraint "test_pri_pkey“

示例五：外键约束

CREATE TABLE test_for(id INT REFERENCES test_pri(id));
INSERT INTO test_for VALUES(2);
违反外键约束（引用值不存在）报错ERROR:  insert or update on table "test_for" violates foreign key constraint "test_for_id_fkey"
INSERT INTO test_for VALUES(1);

示例六：排他约束

CREATE EXTENSION btree_gist;
CREATE TABLE t2(c1 INT,c2 TEXT,EXCLUDE USING GIST(c1 WITH=,c2 WITH<>));
INSERT INTO t2 VALUES(1,'a');
INSERT INTO t2 VALUES(1,'a');
INSERT INTO t2 VALUES(1,'b');
--违反排他约束报错ERROR:  conflicting key value violates exclusion constraint "t2_c1_c2_excl"
INSERT INTO t2 VALUES(2,'b');

示例一：创建主键约束，但不启动约束

CREATE TABLE test_pri(id INT PRIMARY KEY DISABLE);
INSERT INTO test_pri VALUES(1);
INSERT INTO test_pri VALUES(1);--不检查，插入成功

示例二：修改约束状态为启用

ALTER TABLE test_pri ENABLE  PRIMARY KEY;
--存在违反约束数据，修改约束状态失败
ERROR:  could not create unique index "test_pri_pkey"
DETAIL:  Key (id)=(1) is duplicated.

8.序列

什么是序列?

--一种可共享的数据库对象

--系统自动生成按一定规律（增加或减小）变化的数值序列

--每个序列值都都是唯一的

--通常用于创建主键

创建语法：

CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] name
    [ AS data_type ]
    [ INCREMENT [ BY ] increment ]
    [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
    [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
    [ OWNED BY { table_name.column_name | NONE } ]

INCREMENT BY ：可选的，增减子句

START WITH ：可选的，定义序列的开始值

CACHE：为提升序列效果，可缓存序列

CYCLE：可选的，序列达到最大值或最小值时可复位并继续下去

示例：创建并使用序列

CREATE SEQUENCE myseq INCREMENT BY 2
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 100
NO CYCLE;
CREATE TABLE test_seq(col1 INT DEFAULT NEXTVAL('myseq'),col2 INT);
INSERT INTO test_seq(col2) VALUES(100);--只插入col2列，col1使用默认值
SELECT * FROM test_seq; --返回col1为1，col2为100
SELECT NEXTVAL('myseq'); --返回3
SELECT CURRVAL('myseq'); --返回3
SELECT myseq.NEXTVAL;    --返回5
SELECT myseq.CURRVAL;    --返回5
\

更多信息，参见https://help.kingbase.com.cn/v8/index.html

沉舟侧畔千帆过_

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
人大金仓数据库常用数据库对象

-Btree索引：Btree索引使用Btree数据结构来存储索引数据，可用于处理等值查询和范围查询，包括<、<=、=、>=、>等运算符，以及BETWEEN、IN、IS NULL、IS NOT NULL等条件；--唯一索引是Btree类型的索引，对于表中有唯一性约束或主键约束的字段（或字段组合）来说，KES会自动创建相应的唯一索引，不需要手动再创建唯一索引。--唯一索引：唯一索引可以保证某个字段值是唯一的，也可以保证多个字段组合值是唯一的，其中字段中的null值被认为是不同的值。
复制链接

扫一扫