金融企业项目实践 - 开发者空间OpenGauss数据库助力项目完成

一、概述

1. 案例介绍

本案例使用华为开发者空间云主机环境搭建的OpenGaussDB，介绍OpenGaussDB数据库在金融业务中表和数据的规划设计，商业高可用部署方案设计。为保证数据的安全，可靠，稳定。

通过实际操作，让大家深入了解如何利用 OpenGaussDB 开发并部署一个金融型商业数据库。在这个过程中，大家将学习到从安装部署、表结构约束设计到应用部署以及与 API 驱动等一系列关键步骤，从而掌握 OpenGaussDB的基本使用方法，体验其在应用开发中的优势。

注：

本安全第3章和第4单主要是针对高斯数据库在实际商业中的案例说明，故在这两章节中，是写的GaussDB（OpenGauss的商业版本），其与OpenGauss的区别主要在于商业特性和分布式上。故在第3，4章节中用数据库严格来说是GaussDB商业版本，但在云主机环境中，大家使用OpenGauss替代方案即可。因为金融领域的SQL查询大多部分都是简单查询，其更看重的是数据库的稳定性，容灾性，安全性和对业务系统的性能要求不算太高，只要达标能满足目前的系统需求即可。

2. 适用对象

• 企业
• 个人开发者
• 高校学生

3. 案例时间

本案例总时长预计60分钟。

4. 案例流程

说明：

1. 领取华为开发者空间，登录云主机；
2. 在华为开发者空间云主机终端登录OpenGaussDB；
3. 进入开发者空间进行OpenGaussDB数据库之高可用部署和金融业务规划设计；

5. 资源总览

资源名称	规格	单价（元）	时长（分钟）
开发者空间-云主机	鲲鹏通用计算增强型 kc2 | 4vCPUs | 8G | OpenEuler 22.03 Server定制版 (40GB)	免费	60

最新案例动态，请查阅《基于开发者空间OpenGaussDB在金融企业项目中实践操作》，小伙伴快来华为开发者空间 -云开发环境实操吧！

二、开发者空间配置与GaussDB设计

1. 开发者空间配置

面向广大开发者群体，华为开发者空间提供一个随时访问的“开发桌面云主机”、丰富的“预配置工具集合”和灵活使用的“场景化资源池”，开发者开箱即用，快速体验华为根技术和资源。

领取云主机后可以直接进入华为开发者空间工作台界面，点击打开云主机 > 进入桌面连接云主机。

2. 金融数据库部署规划设计

主要为系统级容量规划、组网规划、部署规划、备份规划、脱敏规划、监控告警集成规划等方面的内容；在建设方案上分为云数据库OpenGaussDB建设方案和轻量化OpenGaussDB建设方案。

3. 金融数据库业务规划设计

主要包含两个场景：

• 新建库

新业务涉及部署形态选择、库表对象设计、容量评估等。

• 迁移库

迁移场景涉及容量评估、性能评估、对象迁移、数据迁移等场景。

注：由于金融领域需要用到很多商业特性，所以在第3章和第4章讲解中，数据库是写的GaussDB，由于GaussDB是OpenGauss的商业版本，并且GaussDB对硬件配置要求很高，故在第5章华为开发者空间—云主机环境下，使用OpenGauss替代方案，简单实践银行业务中的简单查询。

三、GaussDB金融业务部署规划设计案例

在金融领域，很看重数据库的稳定性、数据安全、高可用、容灾能力、网络稳定。所以对数据库的定时备份恢复，事务日志和归档日志的多级存储，存算分离的性能提升，容灾的数据同步，数据脱敏的安全性，数据库运维体系的规则设计在项目前期显得尤为重要。

下面从数据库在银行系统中部署的规划设计思路展开说明，数据库在金融领域的容量评估、网络评估、部署规划、组网规划、备份恢复、数据脱敏（安全）、运维体系等几个方面讲述金融领域数据库应该考虑的事项。

3.1~3.7章节是讲银行总行的数据库部署规划。

3.8~3.9章节是讲银行分行/支行的数据库轻量化部署规划。

1. 银行数据库规划建设思路

在2000年完成核心系统大集中，主要核心业务在总行数据中心部署，本地化业务在各分行部署，该行在云化战略转型中数据采用如下部署策略：

总行业务推荐使用HCS标准化GaussDB部署方案部署架构承载，充分利用云弹性、统一管理、服务化等能力，提升资源利用率和管理效能；

分行业务推荐使用HCS轻量化GaussDB部署方案承载，考虑分行系统少，规模小，轻量化方案可以节省成本。

2. HCS云数据库GaussDB建设计算容量评估

经调研原计划转型业务超过1000计算节点，部署上需要支持虚拟化和裸金属，所以HCS平台建设采用大规模云平台建设，支持2000KVM+2000BMS服务。

在以上评估容量下建设HCS云数据库GaussDB，至少支持下发2000个数据库实例。

3. HCS云数据库GaussDB建设存储和网络容量评估

存储资源：经过调研数据量超过PB级，存储池化架构支持扩容，采用起步配置，后续按需扩容。业务数据存储在EVS（DORADO），备份数据存储至OBS上。

网络资源：参考原数据库网络架构，普通网络接入Leaf收敛比为2:1，NOF存储网络接入Leaf收敛比为4:1；GaussDB所用的KVM宿主机和BMS均采用25GE网卡。

3.1 HCS云数据库GaussDB部署规划—集中式

系统类别：第一类（A）

容灾等级：5级

集中式部署：

方案说明：

• GaussDB集中式，存算分离。
• 本地高可用，7节点起步。
• 同城备集群，ADR复制。
• 异地集群数据库层流复制。
• 支持裸金属和ECS虚拟化部署。

系统类别：第二类（B）

容灾等级：3~4级

集中式部署：

方案说明：

• GaussDB集中式，本地盘。
• 本地高可用，6节点起步。
• 同或异地数据库层流复制。
• 追求性能采用物理机。
• 追求性价采用虚拟机。

系统类别：第三类（C）

容灾等级：2级

集中式部署：

方案说明:

• GaussDB集中式，本地盘。
• 追求性价可采用虚拟化。
• 3节点起步。

3.2 HCS云数据库GaussDB部署规划—分布式

系统类别：第一类（A）

容灾等级：5级

分布式部署：

方案说明：

• GaussDB分布式—存算分离。
• 本地高可用，6或者8节点起步。
• 同城备集群，ADR复制。
• 异地集群数据库层流复制。
• 支持裸金属和ECS虚拟化部署。

系统类别：第二类（B）

容灾等级：3~4级

分布式部署：

方案说明：

• GaussDB分布式—本地盘。
• 本地高可用，6或者8节点起步。
• 同或异地数据库层流复制。
• 追求性能采用物理机。
• 追求性价采用虚拟机。

系统类别：第三类（C）

容灾等级：2级

分布式部署：

方案说明：

• GaussDB分布式—本地盘。
• 追求性价可采用虚拟化。
• 3节点起步。

4. HCS云数据库GaussDB组网规划

上述所示，银行内网接入核心交换机，分别与管理区、网络区、对象存储区、通用KVM区、KVM+Dorado区、BMS+Dorado区联通。

5. HCS云数据库GaussDB备份恢复规划

在金融行业不同的备份数据保存时间不一样，如核心交易流水数据监管机构就要求保存2年以上，部分数据要求2年以上，以满足监管需求；所以规定保留2年以上的数据采用云外带库存储备份、2年以内的数据采用OBS备份；全行数据库备份策略如下：

全量周期：每周一16:00-17:00

增量周期：每30分钟

带宽：默认限制250MB，按需调整备份带宽

保存周期：C/D备份数据保留7天，A/B类型数据保留14天（涉及监管要求系统单独审视）

6. HCS云数据库GaussDB数据脱敏规划

银行业务生命周期内，业务升级、问题验证是基本动作，就需要使用生产数据做对应测试，为了生产案例必须对生产数据脱敏，常见脱敏方案有两种：

第一种就是使用数据库自带脱敏能力；

第二种是通过数据清洗实现数据脱敏，需要解决的是数据同步问题。

7. HCS云数据库GaussDB运维体系规划设计

商业银行有完善运维体系和管理平台，数据库规划时必须考虑如何与原平台对接，这里要对接平台有生产监控平台、高可用切换平台、密码管理平台、账单配置管理平台、备份恢复管理平台；开发中心有运行指标监控系统，提供指标查看和告警查看能力。

8. 轻量化数据库GaussDB网络规划

在分行部署场景采用轻量化部署方案，支持双IP配置，复用原分行生产中心网络，常见的配置规划要求如下。

生产环境：

部署架构：分布式或者集中式应用自选

计算：96或128核

网络：10GE/25GE、双IP、bond4方案

数据盘：SATA-SSD/RAID10

备份：NAS存储

监控对接运维平台，支持上报总行监控平台

9. 轻量化数据库GaussDB部署规划

在国内分行或海外机构采用轻量化部署方案，主要考虑成本低，方便灵活，支持GaussDB和DRS、即开即用，从业务能力上覆盖分行客户的业务诉求。应用场景有单中心、双中心部署，具体部署情况如下：

系统类别：第二类（B）

容灾等级：3~4级

集中式部署：

分布式部署：

说明：

• GaussDB采用物理机本地盘部署
• 分布式场景：3~4级应用均采用6节点起步
• 集中式场景：4级应用采用3+3部署，3级应用采用3+1部署
• 同城或异地数据库层流式复制
• 可以采用2路或者4路服务器部署
• 一般场景下优先使用集中式方案

系统类别：第三类（C）

容灾等级：1~2级

集中式部署：

分布式部署：

说明：

• GaussDB采用物理机本地盘部署，占比80%
• 追求性价可用虚拟化
• 分布式3节点起步

10. 部署规划设计总结

金融银行HCS云数据库GaussDB部署规划评估及作用：

• 云化方案部署需要考虑数据库节点规模、存储规模、容灾方案、监控运维、备份恢复等方面内容，该方案适用于大规模集群，具有业务全生命周期运维、资源360度监控的能力。
• 轻量化部署方案支持GaussDB、DRS等不同服务的部署，有效保障用户开发、运维、优化监控、迁移等日常工作需要，该金融机构按照业务等级和网络情况选择部署方案。

四、GaussDB金融系统业务规划设计案例

本章节主要通过银行业务系统和业务逻辑，从中统计出当前银行业务对数据库的性能要求，以达到后面项目的业务性能测试标准。所以本章节主要分为两大部分：

1. 银行系统数据库业务规划。

• 其中包含理解银行业务流程和管理流程。
• 统计出银行业务对数据库的性能指标。
• 数据库性能的评估方案。

2. 银行系统数据库迁移规划。

• 其中包含信贷系统从Oracle/DB2迁移到GaussDB的调研。
• 迁移业务时应考虑资源（计算，存储，网络，存储空间）的评估。
• 业务表的迁移设计（UGO，DRS工具等）。
• 迁移方案实施的工作进展日期规划。

1. 企业流程管理系统数据库业务规划设计案例

企业流程管理系统将跨业务、跨系统数据整合，实现跨领域、跨系统的企业级流程整合，实现对流程的调试与监控；构建统一的流程模型、规则模型、流程组件库，形成统一的流程数据标准，赋能业务发展。

全业务周期管理：对业务流程的整合、编排、集成、调度，统一数据规范。

端到端流程体验：赋能提升端到端长服务流程的体验。以流程数据标准为依规，提供端到端的数据观测。

流程资产沉淀：过程中不断沉淀流程的模型资产与指标，记录流程模型演变过程。

流程孪生与演绎：基于数据对业务流程进行探索，孪生与演绎。持续流程再造与优化，提升流程效果。

1.1 企业流程管理系统流程引擎架构

流程引擎是业务操作过程的流程化实现，用于支撑构建银行企业级和业务领域的流程应用，实现流程应用开发平台和技术的标准化，将流程再造过程工程化。

1.2 企业流程管理系统调研信息

调研生产系统的业务特征，包括用户数、并发数、系统核心处理时间、数据结构、数据分布、查询复杂度、数据量、未来业务增长情况等，调研结果如下：

测试环境工作时间：8:30 – 17:30

生产环境工作时间：22:00 – 24:00

业务量：100万笔/日（每笔平均10QPS，每QPS平均0.01MB）

用户数：10万

设计并发数：1000

查询复杂度：简单SQL，无存储过程

业务等级：5A类核心

备份策略：保持5A核心默认备份策略

数据保留时长：3年

1.3 企业流程管理系统数据库方案评估

评估项	评估说明
SQL	简单SQL无存储过程 数据按照业务+时间可完美sharding
交易量	多读多写，峰值10000QPS
数据量	每天1000万QPS、每笔0.01MB、保留3年 需要规划110TB数据。未来数据库总容量线性增长
业务	并发高，业务量持续快速扩展 容量大，水平扩展Scale out，有线性比提升 5A类核心业务，采用两地三中心部署

评估结论：

• 采用HCS云数据库分布式双集群部署架构
• 初始配置4分片起步即可

2. 信贷管理系统数据库迁移规划设计案例

信贷管理系统是银行核心业务系统，包含客户信用管理系统、全球信贷投资系统、全球风险资产管理三大部分，提供贷前的风险管理、贷中的信贷流程操作和贷后的风险资产管理工作。实现境内外、个人法人一体化的信用管理模式，较好的满足银行信贷资产管理的要求。

2.1 信贷系统调研信息

本案例以系统D不良资产管理为例说明如何将业务迁移至GaussDB，需要调研的信息如下：

应用名称	系统D
应用等级	A级
灾备等级	4级
高可用模式	同城AS
数据量	1.5TB
日交易量	120万
表个数	963
存储过程个数	5799
存储过程代码	79万行
CPU配置	Intel Xeon Gold 5120
路数	2
峰值并发数	300
存储类型	SAN
网卡	10GE
CPU使用率	50%
峰值TPS	400
增量日志	2G/日

2.2 迁移场景系统D计算资源、存储资源、部署网络评估

计算	系统D整体硬件算力SPECrate为294，实际使用147，可使用鲲鹏四路服务承载
存储	系统D数据量约为1.5TB，考虑迁移是最大1.5倍膨胀系数，并且预留40%空间，建议申请3.75TB存储空间，实际上一般会向上取整；从存储容量上看集中式方案即可
网络	数据库内部为25GE，外部要求400*10*1K共计4000KB带宽，DRS全量复制最大约350MB/s
部署架构	系统D为A类业务，结合以上信息采用集中式双集群两地三中心部署方案即可，见第一类部署组网

2.3 迁移场景系统D备份空间资源评估

• 默认备份策略
  • 全是备份周期：每天16:00 – 17:00
  • 备份保留周期：14天
  • 压缩比：2:1
  • 全量数据：1.5TB
  • 每日新增数据量：2GB
  • 数据膨胀比：1.5
• 评估过程
  • 全量备份容量C1 = 业务总数据量S * 数据膨胀比 * （ceil（备份保留周期T1 / 备份周期T2）+ 1）* 压缩比R = 1500GB * 1.5 * （ceil（14 / 1）+ 1）* 0.5 = 16875 GB
  • 差量备份容量C2 = 每日新增数据量D * 数据膨胀比 * 备份保留周期T1 * 压缩比R = 2GB * 1.5 * 14 *0.5 = 21GB
  • 日志归档容量C3 = 每日新增数据量D * 数据膨胀比 * 备份保留周期T1 = 2GB * 1.5 *14 = 42GB
  • 备份容量 = 全量备份容量 + 差量备份容量 + 日志归档容量 = 16938GB

2.4 系统D库表迁移设计

源库对象整体迁移至GaussDB，表、存储过程等对象做同构迁移、用户权限不变；要求目标库对源库有较好的兼容性且能够自动迁移，为此华为提供了对象迁移UGO工具、数据迁移DRS工具、用于业务功能测试的流量回放工具。

2.5 系统D业务迁移实施整体规划

3. 业务规划设计总结

新建数据库场景主要考虑系统的业务复杂度、数据增长量、保留时长、容灾等级、峰值交易量等信息，即可做数据库部署选型决策。

数据库业务迁移场景主要考虑对象迁移、数据迁移、业务测试场景、数据库部署方案等，其中数据库算力评估是迁移场景难点和重点，算力评估需要从计算、数据量、网络、业务量等方面统一分析，评估对等资源。

五、OpenGaussDB在理财系统的设计实践

本章节主要为在华为开发者空间—云主机环境中，GaussDB是商业版并且对硬件配置要求过高，所以云主机中无法使用GaussDB实践，故用OpenGauss（其内核功能与GaussDB除了商业特性外没有差别）模拟银行业务中一些常用的SQL实践。因为在金融领域，看重的是商业的高可用，稳定性和容灾能力，还有业务性能达标即可，而平时业务SQL的复杂度并不高，87%都属于简单查询。故第5章主要是模拟银行中常见的表之间的外键和约束组合查询的操作。

在银行理财产品业务中，涉及的数据库对象分为客户、银行卡、理财产品、保险、基金等5个对象。因此，目标数据库对象存在以下关系，客户可以办理银行卡，同时客户可以购买不同的银行产品，如理财产品，基金和保险。根据该银行的对象关系，设计相应的关系模型和E-R图，并对其进行较为复杂的数据库操作。

1. 对象及关系设计

• 对象属性：
• 客户（客户编号，客户名称，客户邮箱，客户身份证，客户手机号，客户登录密码）
• 银行卡（银行卡号，银行卡类型）
• 理财产品（产品名称，产品编号，产品描述，购买金额，理财年限）
• 保险（保险名称，保险编号，保险金额，适用人群，保险年限，保障项目）
• 基金（基金名称，基金编号，基金类型，基金金额，风险等级，基金管理者）
• 对象之间的关系：
• 一个客户可以办理多张银行卡
• 一个客户可以购买多个理财产品，同一类理财产品可由多个客户购买
• 一个客户可以购买多个基金，同一类基金可由多个客户购买
• 一个客户可以购买多个保险，同一类保险可由多个客户购买

2. 数据库设计

2.1 E-R图

2.2 关系模型设计

注意事项：

• 由于一个客户可以办理多张银行卡，所以银行卡表引用客户表的客户编号作为外键。
• 由于 一个客户可以购买多个理财产品，同一类理财产品可由多个客户购买。所以生成关系表—资产表。资产表引用客户表的商品编号作为外键，引用理财产品表的产品编号作为外键，并添加商品状态、商品数量、商品收益和购买时间等属性。
• 客户和保险、客户和基金的关系同理，所以资产表同样作为生成的关系表，修改资产表的理财产品编号为商品编号，商品编号引用自理财产品表、保险和基金表的编号。

2.3 物理模型

• client 客户表

字段名称	字段类型	约束	说明
c_id	integer	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	客户登录密码

• fund 基金表

字段名称	字段类型	约束	说明
f_name	varchar(100)	not null	基金名称
f_id	integer	primary key	基金编号
f_type	char(20)		基金类型
f_amount	integer		基金金额
risk_level	char(20)	not null	风险等级
f_manager	integer	not null	基金管理者（本字段引用自银行员工表的id字段，由于简化系统没有的本实验展示员工表）

• bank_card 银行表

字段名称	字段类型	约束	说明
b_number	char(30)	primary key	银行卡号
b_type	char(20)		银行卡类型
b_c_id	integer	not null foreign key	所属客户编号（本字段引用自client表的c_id字段）

• finances_product 理财产品信息表

字段名称	字段类型	约束	说明
p_name	varchar(100)	not null	产品名称
p_id	integer	primary key	产品编号
p_description	varchar(4000)		产品描述
p_amount	integer		购买金额
p_year	integer		理财年限

• insurance 保险表

字段名称	字段类型	约束	说明
i_name	varchar(100)	not null	保险名称
i_id	integer	primary key	保险编号
i_amount	integer		保险金额
i_person	char(20)		适用人群
i_year	integer		保险年限
i_project	varchar(200)		保障项目

• property 资产表

字段名称	字段类型	约束	说明
pro_id	integer	primary key	资产编号
pro_c_id	varchar(100)	not null foreign key	客户编号（本字段引用client表的c_id字段）
pro_pif_id	integer	not null foreign key	商品编号（本字段引用finances_product表、insurance表和fund表三个表的id字段）
pro_type	integer	not null	商品类型（1表示理财产品；2表示保险；3表示基金）
pro_status	char(20)		商品状态
pro_quantity	integer		商品数量
pro_income	integer		商品收益
pro_purchase_time	date		购买时间

3. 启动OpenGaussDB实例并登录

本案例中，使用OpenGaussDB开发平台，完成SQL的编程和自定义函数等多种功能。

基于之前案例《基于开发者空间部署OpenGauss主备集中式数据库系统》。在云主机部署OpenGaussDB实例。并启动数据库服务。

进入OpenGaussDB的安装目录的bin文件，该案例云主机环境中安装目录在环境变量$GAUSSHOME中，读者根据自己云主机安装目录进行操作修改。

cd $GAUSSHOME/bin

初始化数据库实例，初始化数据库目录在当前目录下data，设置节点名称和初始化用户密码。如下所示

./gs_initdb -D data --nodename=n1 -w GaussDB@123

以单节点模式启动数据库实例，并在当前目录下输出日志文件logfile

./gs_ctl start -D data -Z single_node -l logfile

用gsql客户端工具，进入OpenGaussDB数据库。参数 -a表示追加、-r表示使用readline

./gsql -d postgres -ar

4. 数据库操作

创建数据库finance

CREATE DATABASE finance ENCODING 'UTF-8' template = template0;

连接数据库finance

\connect finance

创建模式finance

CREATE SCHEMA finance;

设置默认搜索路径

SET search_path TO finance;

创建客户信息表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
);

创建银行卡信息表bank_card

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

创建理财产品信息表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
);

创建保险信息表 insurance

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

创建基金信息表 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
);

创建资产信息表 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
);

5. 写入测试数据

向client表插入测试数据

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'),
(25, '王七', 'wangqi@huawei.com', '340211199301010025', '18815650025', 'gaussdb_025'),
(26, '王八', 'wangba@huawei.com', '340211199301010026', '18815650026', 'gaussdb_026'),
(27, '王九', 'wangjiu@huawei.com', '340211199301010027', '18815650027', 'gaussdb_027'),
(31, '李丽', 'lili@huawei.com', '340211199301010031', '18815650031', 'gaussdb_031');

向bank_card表插入测试数据

INSERT INTO bank_card(b_number, b_type, b_c_id) VALUES ('62220213020200000001', '信用卡', 1),
('62220213020200000002', '信用卡', 3),
('62220213020200000003', '信用卡', 5),
('62220213020200000004', '信用卡', 7),
('62220213020200000005', '信用卡', 9),
('62220213020200000006', '信用卡', 10),
('62220213020200000007', '信用卡', 12),
('62220213020200000008', '信用卡', 14),
('62220213020200000009', '信用卡', 16),
('622202130202000000010', '信用卡', 18),
('622202130202000000011', '储蓄卡', 19),
('622202130202000000012', '储蓄卡', 21),
('622202130202000000013', '储蓄卡', 7),
('622202130202000000014', '储蓄卡', 23),
('622202130202000000015', '储蓄卡', 24),
('622202130202000000016', '储蓄卡', 3),
('622202130202000000017', '储蓄卡', 26),
('622202130202000000018', '储蓄卡', 27),
('622202130202000000019', '储蓄卡', 12),
('622202130202000000020', '储蓄卡', 29);

向finances_product表插入测试数据

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

向insurance表插入测试数据

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, '平安保险');

向fund表插入测试数据

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);

向property插入测试数据

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');

6. 查询数据

• 单表查询

查询银行卡信息表：

SELECT b_number, b_type FROM bank_card;

• 条件查询

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

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

• 聚合查询

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

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);

• 子查询

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

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

• 连接查询
  • 半连接

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

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_');

• ORDRE 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;

• HAVING和WITH AS查询
  • HAVING子句

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

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

• WITH AS子句

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

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

7. 数据修改

• 修改数据

更新bank_card表中c_id < 10且在client表中存在的所有行，设置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;

• 删除数据

删除fund表中，f_id < 3的数据

delete from fund where f_id < 3;

查询删除结果

select * from fund;

8. 创建约束

添加Check约束

alter table finances_product add constraint c_p_mount check (p_amount \>= 0);
alter table fund add constraint c_f_mount check (f_amount \>= 0);
alter table insurance add constraint c_i_mount check (i_amount \>= 0);

查询约束信息

select t1.table_name, t1.column_name, t1.constraint_name, t1.constraint_schema, t2.contype, t2.consrc
from information_schema.constraint_column_usage t1,
pg_catalog.pg_constraint t2
where t1.constraint_name = t2.conname
and t1.constraint_schema =
(select nspname from pg_namespace where oid = t2.connamespace)
and t2.conrelid in
(select oid from pg_class where relname in
('finances_product', 'fund', 'insurance'))
and t2.contype = 'c';

9. 视图引用

创建视图v_client用以查询拥有银行卡的用户编号、用户姓名、用户身份证号

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;

• 修改视图

在原有查询的基础上过滤出信用卡用户

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 = '信用卡');

修改视图名称

ALTER VIEW v_client RENAME TO v_client_new;

删除视图

DROP VIEW v_client_new;

10. 索引操作

在property表创建索引idx_property

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

重建idx_property索引

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

重命名索引idx_property为idx_property_temp

ALTER INDEX idx_property RENAME TO idx_property_temp;

删除索引idx_property_temp

DROP INDEX idx_property_temp;

11. 用户操作

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

CREATE USER dbuser with createdb IDENTIFIED BY 'Gauss#3demo';

授权用户dbuser对finance数据库bank_card表的查询和插入权限

GRANT SELECT, INSERT ON finance.bank_card TO dbuser;

将finance模式的所有权限授予dbuser用户

GRANT ALL ON SCHEMA finance to dbuser;
\q

使用新用户连接finance数据库

./gsql -d finance -U dbuser -p 5432 -W Gauss#3demo -r

查询bank_card表中，b_c_id < 10的数据

select * from finance.bank_card where b_c_id \< 10;

12. schema操作

查看schema

\dn

设置默认查询路径为finance

set search_path to finance;

查看finance这个schema内的对象

\dt

级联删除schema

drop schema finance cascade;

确认schema内的对象已删除

\dt