入门openGauss数据库（华为云实验-openGauss数据库在金融领域的使用实践）

qq_1694565166

已于 2024-05-29 10:15:44 修改

阅读量1.9k

点赞数 32

文章标签：开发语言 gaussdb 华为云

于 2024-05-13 14:12:59 首次发布

本文链接：https://blog.csdn.net/qq_38018256/article/details/138492830

版权

1 进入实验

进入华为云开发者学堂官网，找到云认证，点击openGauss数据库在金融领域的使用实践，可以在这里购买认证，进行云实验。

2 实验手册

以下是实验手册：

本实验指导用户基于华为云鲲鹏服务器，使用openGauss构建金融场景下的数据库，模拟金融场景下的业务实现。

1.环境准备

1.1.预置环境

环境预置会生成一台操作系统为openEuler的华为云鲲鹏弹性云服务器ECS，ECS资源用户、密码信息，此处省略具体步骤，详见华为云实验。

1.2.登录华为云

进入【实验操作桌面】，打开火狐浏览器进入华为云登录页面。选择【IAM用户登录】模式，于登录对话框中输入系统为您分配的华为云实验账号和密码登录华为云（详见实验手册）。

2.远程登录弹性云服务器ECS

此处略去登录步骤，最终登录成功，进到【实验操作桌面】，如下图所示：

双击“Xfce终端”打开Terminal，执行如下命令（使用弹性公网IP替换命令中的EIP），登录弹性云服务器ECS；

LANG=en_us.UTF-8 ssh root@EIP

操作说明：

接受秘钥：输入“yes”回车；

输入密码：使用预置环境信息中云服务器名称为ecs-openGauss的用户密码

登录成功如下图所示（输入密码时，命令行窗口不会显示密码，输完之后直接回车）。

注意：成功登录后，实验过程中请勿关闭该Terminal窗口，否则需要重复此步骤重新建立连接。

3.创建数据表

3.1.创建金融数据库finance

切换到omm用户，以操作系统用户omm登录数据库主节点：

什么是schema？

schema又称作模式。每个数据库包含一个或多个schema。数据库中的每个schema包含表和其他类型的对象。通过管理schema，允许多个用户使用同一数据库而不相互干扰，可以将数据库对象组织成易于管理的逻辑组，同时便于将第三方应用添加到相应的schema下而不引起冲突。

su - omm

使用gsql工具登录数据库：

gsql -d postgres -p  15400 -r

创建数据库finance：

CREATE DATABASE finance ENCODING 'UTF8' template = template0;

连接finance数据库：

\connect finance

创建名为finance的schema，并设置finance为当前的schema。

CREATE SCHEMA finance;

将默认搜索路径设为finance：

SET search_path TO finance;

3.2.客户信息表的创建

在SQL编辑框中输入如下语句，创建客户信息表client。

若存在client表，为了避免冲突，这一步提前删除表client：

DROP TABLE IF EXISTS client;

创建表client：

CREATE TABLE client
(
        c_id INT PRIMARY KEY,
        c_name VARCHAR(100) NOT NULL,
        c_mail CHAR(30) UNIQUE,
        c_id_card CHAR(20) UNIQUE NOT NULL,
        c_phone CHAR(20) UNIQUE NOT NULL,
        c_password CHAR(20) NOT NULL
);

3.3.银行卡信息表的创建

在SQL编辑框中输入如下语句，创建银行卡信息表bank_card。

若存在bank_card表，为了避免冲突，这一步提前删除表bank_card：

DROP TABLE IF EXISTS bank_card;

创建表bank_card：

CREATE TABLE bank_card
(
        b_number CHAR(30) PRIMARY KEY,
        b_type CHAR(20),
        b_c_id INT NOT NULL
);

3.4.理财产品信息表的创建

创建理财产品信息表finances_product。

若存在finances_product表，为了避免冲突，提前删除表finances_product：

DROP TABLE IF EXISTS finances_product;

创建表finances_product：

CREATE TABLE finances_product
(
        p_name VARCHAR(100) NOT NULL,
        p_id INT PRIMARY KEY,
        p_description VARCHAR(4000),
        p_amount INT,
        p_year INT
);

3.5.保险信息表的创建

在SQL编辑框中输入如下语句，创建保险信息表insurance。

若存在insurance表，为了避免冲突，这一步提前删除表insurance：

DROP TABLE IF EXISTS insurance;

创建表insurance：

CREATE TABLE insurance
(
        i_name VARCHAR(100) NOT NULL,
        i_id INT PRIMARY KEY,
        i_amount INT,
        i_person CHAR(20),
        i_year INT,
        i_project VARCHAR(200)
);

3.6.基金信息表的创建

在SQL编辑框中输入如下语句，创建保险信息表fund。

若存在fund表，为了避免冲突，这一步提前删除表fund：

DROP TABLE IF EXISTS fund;

创建表fund：

CREATE TABLE fund
(
        f_name VARCHAR(100) NOT NULL,
        f_id INT PRIMARY KEY,
        f_type CHAR(20),
        f_amount INT,
        risk_level CHAR(20) NOT NULL,
        f_manager INT NOT NULL
);

3.7.资产信息表的创建

在SQL编辑框中输入如下语句，创建资产信息表property。

若存在property表，为了避免冲突，这一步提前删除表property：

DROP TABLE IF EXISTS property;

创建表property：

CREATE TABLE property
(
        pro_id INT PRIMARY KEY,
pro_c_id INT NOT NULL,
        pro_pif_id INT NOT NULL,
        pro_type INT NOT NULL,
        pro_status CHAR(20),
        pro_quantity INT,
        pro_income INT,
        pro_purchase_time DATE
);

4.插入表数据

4.1.对client表进行数据初始化

执行insert操作：

INSERT INTO client(c_id,c_name,c_mail,c_id_card,c_phone,c_password) VALUES 
(1,'张一','zhangyi@huawei.com','340211199301010001','18815650001','gaussdb_001'),
(2,'张二','zhanger@huawei.com','340211199301010002','18815650002','gaussdb_002'),
(3,'张三','zhangsan@huawei.com','340211199301010003','18815650003','gaussdb_003'),
(4,'张四','zhangsi@huawei.com','340211199301010004','18815650004','gaussdb_004'),
(5,'张五','zhangwu@huawei.com','340211199301010005','18815650005','gaussdb_005'),
(6,'张六','zhangliu@huawei.com','340211199301010006','18815650006','gaussdb_006'),
(7,'张七','zhangqi@huawei.com','340211199301010007','18815650007','gaussdb_007'),
(8,'张八','zhangba@huawei.com','340211199301010008','18815650008','gaussdb_008'),
(9,'张九','zhangjiu@huawei.com','340211199301010009','18815650009','gaussdb_009'),
(10,'李一','liyi@huawei.com','340211199301010010','18815650010','gaussdb_010'),
(11,'李二','lier@huawei.com','340211199301010011','18815650011','gaussdb_011'),
(12,'李三','lisan@huawei.com','340211199301010012','18815650012','gaussdb_012'),
(13,'李四','lisi@huawei.com','340211199301010013','18815650013','gaussdb_013'),
(14,'李五','liwu@huawei.com','340211199301010014','18815650014','gaussdb_014'),
(15,'李六','liliu@huawei.com','340211199301010015','18815650015','gaussdb_015'),
(16,'李七','liqi@huawei.com','340211199301010016','18815650016','gaussdb_016'),
(17,'李八','liba@huawei.com','340211199301010017','18815650017','gaussdb_017'),
(18,'李九','lijiu@huawei.com','340211199301010018','18815650018','gaussdb_018'),
(19,'王一','wangyi@huawei.com','340211199301010019','18815650019','gaussdb_019'),
(20,'王二','wanger@huawei.com','340211199301010020','18815650020','gaussdb_020'),
(21,'王三','wangsan@huawei.com','340211199301010021','18815650021','gaussdb_021'),
(22,'王四','wangsi@huawei.com','340211199301010022','18815650022','gaussdb_022'),
(23,'王五','wangwu@huawei.com','340211199301010023','18815650023','gaussdb_023'),
(24,'王六','wangliu@huawei.com','340211199301010024','18815650024','gaussdb_024'),
(25,'王七','wangqi@huawei.com','340211199301010025','18815650025','gaussdb_025'),
(26,'王八','wangba@huawei.com','340211199301010026','18815650026','gaussdb_026'),
(27,'王九','wangjiu@huawei.com','340211199301010027','18815650027','gaussdb_027'),
(28,'钱一','qianyi@huawei.com','340211199301010028','18815650028','gaussdb_028'),
(29,'钱二','qianer@huawei.com','340211199301010029','18815650029','gaussdb_029'),
(30,'钱三','qiansan@huawei.com','340211199301010030','18815650030','gaussdb_030');

查询client表的插入条目数：

select count(*) from client;

4.2.对bank_card表进行数据初始化

执行insert操作：

INSERT INTO bank_card(b_number,b_type,b_c_id) VALUES 
('6222021302020000001','信用卡',1),
('6222021302020000002','信用卡',3),
('6222021302020000003','信用卡',5),
('6222021302020000004','信用卡',7),
('6222021302020000005','信用卡',9),
('6222021302020000006','信用卡',10),
('6222021302020000007','信用卡',12),
('6222021302020000008','信用卡',14),
('6222021302020000009','信用卡',16),
('6222021302020000010','信用卡',18),
('6222021302020000011','储蓄卡',19),
('6222021302020000012','储蓄卡',21),
('6222021302020000013','储蓄卡',7),
('6222021302020000014','储蓄卡',23),
('6222021302020000015','储蓄卡',24),
('6222021302020000016','储蓄卡',3),
('6222021302020000017','储蓄卡',26),
('6222021302020000018','储蓄卡',27),
('6222021302020000019','储蓄卡',12),
('6222021302020000020','储蓄卡',29);

查询插入结果：

select count(*) from bank_card;

4.3.对finances_product表进行数据初始化

执行insert操作：

INSERT INTO finances_product(p_name,p_id,p_description,p_amount,p_year) VALUES 
('债券',1,'以国债、金融债、央行票据、企业债为主要投资方向的银行理财产品。',50000,6),
('信贷资产',2,'一般指银行作为委托人将通过发行理财产品募集资金委托给信托公司，信托公司作为受托人成立信托计划，将信托资产购买理财产品发售银行或第三方信贷资产。',50000,6),
('股票',3,'与股票挂钩的理财产品。目前市场上主要以港股挂钩居多',50000,6),
('大宗商品',4,'与大宗商品期货挂钩的理财产品。目前市场上主要以挂钩黄金、石油、农产品的理财产品居多。',50000,6);

查询finances_product表插入的数据条目数：

select count(*) from finances_product;

4.4.对insurance表进行数据初始化

执行insert操作：

INSERT INTO insurance(i_name,i_id,i_amount,i_person,i_year,i_project) VALUES 
('健康保险',1,2000,'老人',30,'平安保险'),
('人寿保险',2,3000,'老人',30,'平安保险'),
('意外保险',3,5000,'所有人',30,'平安保险'),
('医疗保险',4,2000,'所有人',30,'平安保险'),
('财产损失保险',5,1500,'中年人',30,'平安保险');

查询insurance表插入的数据条目数：

select count(*) from insurance;

4.5.对fund表进行数据初始化

执行insert操作：

INSERT INTO fund(f_name,f_id,f_type,f_amount,risk_level,f_manager) VALUES 
('股票',1,'股票型',10000,'高',1),
('投资',2,'债券型',10000,'中',2),
('国债',3,'货币型',10000,'低',3),
('沪深300指数',4,'指数型',10000,'中',4);

查询fund表插入的数据条目数：

select count(*) from fund;

4.6.对property表进行数据初始化

执行insert操作：

INSERT INTO property(pro_id,pro_c_id,pro_pif_id,pro_type,pro_status,pro_quantity,pro_income,pro_purchase_time) VALUES 
(1,5,1,1,'可用',4,8000,'2018-07-01'),
(2,10,2,2,'可用',4,8000,'2018-07-01'),
(3,15,3,3,'可用',4,8000,'2018-07-01'),
(4,20,4,1,'冻结',4,8000,'2018-07-01');

查询property表插入的数据条目数：

select count(*) from property;

5.手工插入一条数据

当C银行有新的信息需要加入数据库时，系统需要在对应的数据表中手动插入一条新的数据。因此，针对主键属性定义的场景，介绍如何手动插入一条数据。

5.1.属性冲突的场景

在金融数据库的客户信息表中添加一个客户的信息（c_id_card和c_phone非唯一）：

INSERT INTO client(c_id,c_name,c_mail,c_id_card,c_phone,c_password) VALUES (31,'李丽','lili@huawei.com','340211199301010005','18815650005','gaussdb_005');

错误信息如下：

说明：由于在表的创建过程中，实验定义了c_id_card和c_phone为唯一且非空（UNIQUE NOT NULL），所以当表中存在时，插入数据失败。

5.2.插入成功的场景

在金融数据库的客户信息表中添加一个客户的信息：

INSERT INTO client(c_id,c_name,c_mail,c_id_card,c_phone,c_password) VALUES (31,'李丽','lili@huawei.com','340211199301010031','18815650031','gaussdb_031');
select * from client where c_id=31;

显示如下即表示添加成功：

6.添加约束

6.1.向finances_product表添加约束

为finances_product表的p_amount列添加大于等于0的约束：

ALTER table finances_product ADD CONSTRAINT c_p_mount CHECK (p_amount >=0);

6.2.插入数据测试

尝试手工插入一条金额小于0的记录：

INSERT INTO finances_product(p_name,p_id,p_description,p_amount,p_year) VALUES ('信贷资产',10,'一般指银行作为委托人将通过发行理财产品募集资金委托给信托公司，信托公司作为受托人成立信托计划，将信托资产购买理财产品发售银行或第三方信贷资产。',-10,6);

执行失败，如下图所示：

6.3.向fund表添加约束

为fund表的f_amount列添加大于等于0的约束：

ALTER table fund ADD CONSTRAINT c_f_mount CHECK (f_amount >=0);

6.4.向insurance表添加约束

为insurance表的i_amount列添加大于等于0的约束：

ALTER table insurance ADD CONSTRAINT c_i_mount CHECK (i_amount >=0);

6.5.验证约束的创建结果

select conname,connamespace,contype from pg_constraint where conrelid in (select oid from pg_class where relname in ('fund','insurance'));

输出结果如下，能查询到已创建的约束c_i_mount和c_f_mount信息，即创建约束成功：

7.查询数据

在本小节的金融数据库实验中，主要目的是为了让读者学习到更深一层的查询操作，让学习者能够更深入的去了解openGauss数据库的复杂操作。

什么是半连接和反连接?

半连接是一种特殊的连接类型，在SQL中没有指定的关键字，通过在WHERE后面使用IN或EXISTS子查询实现。当IN/EXISTS右侧的多行满足子查询的条件时，主查询也只返回一行与EXISTS子查询匹配的行，而不是复制左侧的行。

反连接是一种特殊的连接类型，在SQL中没有指定的关键字，通过在WHERE后面使用 NOT IN或NOT EXISTS子查询实现。返回所有不满足条件的行。这个关系的概念跟半连接相反。

7.1.单表查询

查询银行卡信息表：

SELECT b_number,b_type FROM bank_card;

7.2.条件查询

查询资产信息中‘可用’的资产数据：

select * from property where pro_status='可用';

7.3.聚合查询

查询用户表中有多少个用户：

SELECT count(*) FROM client;

查询银行卡信息表中，储蓄卡和信用卡的个数：

SELECT b_type,COUNT(*) FROM bank_card GROUP BY b_type;

查询保险信息表中，保险金额的平均值：

SELECT AVG(i_amount) FROM insurance;

查询保险信息表中保险金额的最大值和最小值所对应的险种和金额：

select i_name,i_amount from insurance where i_amount in (select max(i_amount) from insurance)
union
select i_name,i_amount from insurance where i_amount in (select min(i_amount) from insurance);

7.4.连接查询

半连接

查询用户编号在银行卡表中出现的用户的编号，用户姓名和身份证：

SELECT c_id,c_name,c_id_card FROM client WHERE EXISTS (SELECT * FROM bank_card WHERE client.c_id = bank_card.b_c_id);

反连接

查询银行卡号不是‘622202130202000001*’（*表示未知）的用户的编号，姓名和身份证：

SELECT c_id,c_name,c_id_card FROM client WHERE c_id NOT IN (SELECT b_c_id FROM bank_card WHERE b_number LIKE '622202130202000001_');

7.5.子查询

通过子查询，查询保险产品中保险金额大于平均值的保险名称和适用人群：

SELECT i1.i_name,i1.i_amount,i1.i_person FROM insurance i1 WHERE i_amount > (SELECT avg(i_amount) FROM insurance i2);

7.6.ORDER BY和GROUP BY

ORDER BY子句

按照保额降序查询保险编号大于2的保险名称，保额和适用人群：

SELECT i_name,i_amount,i_person FROM insurance WHERE i_id>2 ORDER BY i_amount DESC;

GROUP BY子句

查询各理财产品信息总数，按照p_year分组：

SELECT p_year,count(p_id) FROM finances_product GROUP BY p_year;

7.7.HAVING和WITH AS

HAVING子句

查询保险金额统计数量等于2的适用人群数：

SELECT i_person,count(i_amount) FROM insurance GROUP BY i_person HAVING count(i_amount)=2;

备注：HAVING子句依附于GROUP BY子句而存在。

WITH AS子句

使用WITH AS查询基金信息表：

WITH temp AS (SELECT f_name,ln(f_amount) FROM fund ORDER BY f_manager DESC) SELECT * FROM temp;

备注：该语句为定义一个SQL片段，该SQL片段会被整个SQL语句用到。

可以使SQL语句的可读性更高。存储SQL片段的表与基本表不同，是一个虚表。数据库不存放对应的定义和数据，这些数据仍存放在原来的基本表中。若基本表中的数据发生变化，从存储SQL片段的表中查询出的数据也随之改变。

8.视图

8.1.创建视图

视图是什么?

视图是一个虚拟表，是sql的查询结果，其内容由查询定义。对于来自多张关联表的复杂查询，就不得不使用十分复杂的SQL语句进行查询，造成极差的体验感。使用视图之后，可以极大的简化操作，使用视图不需要关心相应表的结构、关联条件等。

针对“查询用户编号在银行卡表中出现的用户的编号，用户姓名和身份证” 的查询，创建视图：

CREATE VIEW v_client as SELECT c_id,c_name,c_id_card FROM client WHERE EXISTS (SELECT * FROM bank_card WHERE client.c_id = bank_card.b_c_id);

使用视图进行查询：

SELECT * FROM v_client;

8.2.修改视图内容

修改视图，在原有查询的基础上，过滤出信用卡用户：

CREATE OR REPLACE VIEW v_client as SELECT c_id,c_name,c_id_card FROM client WHERE EXISTS (SELECT * FROM bank_card WHERE client.c_id = bank_card.b_c_id and bank_card.b_type='信用卡');

使用视图进行查询：

select * from v_client;

输出结果如下：

8.3.修改视图名称

ALTER VIEW v_client RENAME TO v_client_new;

8.4.删除视图

将v_client视图删除，删除视图不影响基表：

DROP VIEW v_client_new;

9.索引

9.1.创建索引

什么是索引？

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息。

在普通表property上创建索引。

CREATE INDEX idx_property ON property(pro_c_id DESC,pro_income,pro_purchase_time);

9.2.重建索引

重建property表的idx_property索引：

DROP INDEX idx_property;
CREATE INDEX idx_property ON property(pro_c_id DESC,pro_income,pro_purchase_time);

9.3.重命名索引

重命名索引idx_property为idx_property_temp：

ALTER INDEX idx_property RENAME TO idx_property_temp;

9.4.删除索引

删除索引idx_property_temp：

DROP INDEX idx_property_temp;

10.数据的修改和删除

10.1.修改数据

修改/更新银行卡信息表中b_c_id小于10和客户信息表中c_id相同的记录的b_type字段。

查看表数据：

SELECT * FROM bank_card where b_c_id<10 ORDER BY b_c_id;

更新数据，根据client表的c_id列更新bank_card表中c_id<10的所有行，设置b_type的值为“借记卡”：

UPDATE bank_card SET bank_card.b_type='借记卡' from client where bank_card.b_c_id = client.c_id and bank_card.b_c_id<10;

重新查询数据情况：

SELECT * FROM bank_card ORDER BY b_c_id;

10.2.删除基金信息表中编号小于3的行

删除前查询结果：

SELECT * FROM fund;

开始删除表fund中，f_id<3的数据条目：

DELETE FROM fund WHERE f_id<3;

查询删除结果。

SELECT * FROM fund;

11.新用户的创建、授权和连接数据库

在本小节中，假设C银行的某新员工想要在自己的用户下去访问sys用户下的金融数据库，则该员工需要向sys申请添加相关权限，具体操作如下：

11.1.创建用户dbuser，并赋予创建数据库的权限

连接数据库后，进入SQL命令界面。创建用户“dbuser”，密码自定义：

CREATE USER dbuser with createdb IDENTIFIED BY '自定义密码';

11.2.授权用户

给用户dbuser授予finance数据库下bank_card表的查询和插入权限，并将SCHEMA的权限也授予dbuser用户：

GRANT SELECT,INSERT ON finance.bank_card TO dbuser;
GRANT ALL ON SCHEMA finance to dbuser;

11.3.退出数据库

\q

11.4.使用新用户连接finance数据库

使用操作系统omm用户在新的窗口登录并执行以下命令，并输入已设置的密码。

gsql -d finance -U dbuser -p 15400 -r

11.5.查询bank_card表数据

查询finance数据库的表bank_card中，b_c_id<10的数据条目：

select * from finance. bank_card where b_c_id<10;

11.6.退出数据库

\q

12.删除schema

12.1.登录数据库

使用操作系统omm用户使用gsql，登录finance数据库：

gsql -d finance -p 15400

12.2.查看schema

使用“\dn”查看数据库下的schema：

\dn

12.3.设置默认查询路径

设置默认查询路径search_path 为finance：

set search_path to finance;

12.4.查看finance内的对象

使用“\dt”命令可以看到在finance中的对象：

\dt

12.5.删除schema finance(异常)

使用DROP SCHEMA 命令删除finance会有报错，因为finance下存在对象：

DROP SCHEMA finance;

12.6.级联删除schema finance

使用DROP SCHEMA…..CASCADE删除，会将finance连同下的对象一起删除：

DROP SCHEMA finance CASCADE;

12.7.检查schema内的对象已删除

使用“\dt”命令可以看到在finance和public中的对象，对象已删除：

\dt

12.8.退出数据库

\q

qq_1694565166

关注

32
点赞
踩
32

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫