【数据库课程设计】金融数据库设计与实现

最新推荐文章于 2022-06-08 10:26:01 发布
最新推荐文章于 2022-06-08 10:26:01 发布
阅读量7.8k 收藏 42
点赞数 9
分类专栏： 学习笔记 # 数据库系统原理 文章标签： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-NC-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/BlacKingZ/article/details/118556523
版权

数据库课程设计

文章目录

1.E-R图

在这里插入图片描述

2.E-R图转换为关系模式

样式说明：外码，主码

2.1 实体集的处理

普通实体集转换为关系模式：

  • 支行 (支行ID,支行名称,系统密码,城市,街道);
  • 理财经理 (工号,姓名,系统密码,身份证号,电话,邮箱);
  • 贷款 (贷款号,贷款金额,贷款日期,还款日期);
  • 账户 (账号,金额)

一般化/特殊化实体集的处理:

由于所有一般化/特殊化实体集均为全部特殊化，且不相交，采取只将每个最低层实体集转化为关系模式

  • 个人客户 (个人客户ID,客户密码,姓名,身份证号,邮箱,手机号码);
  • 企业客户 (企业客户ID,客户密码,企业名称,企业法人,注册地址,营业执照号);
  • 信用卡 (信用卡号,支付密码,透支额度,剩余额度);
  • 结算卡 (结算卡号,支付密码,上限金额,剩余金额);
  • 保险 (保险名称,保险编号,保险价格,适用人群,保险年限,保障项目);
  • 基金 (基金名称,基金编号,基金类型,基金价格,风险等级,基金管理者);

2.2 联系集的处理

一对多联系：

  • 客户-账户(账号,客户ID)；
  • 支行-理财经理 (支行ID,工号);
  • 支行-账户 (账号支行ID);
  • 支行-贷款 (贷款号,支行ID);
  • 客户-贷款 (贷款号,客户ID);
  • 理财经理-客户(工号,客户ID);
  • 个人客户- 信用卡(个人客户ID,信用卡号);
  • 企业客户-结算卡 (企业客户ID,结算卡号);

多对多联系：

  • 购买 (客户ID,产品编号,购买份额);

2.3 整理合并

  • 支行 (支行ID,支行名称,系统密码,城市,街道);
  • 理财经理 (工号,姓名,系统密码,身份证号,电话,邮箱，工作银行ID);
  • 贷款 (贷款号,贷款金额,贷款日期,还款日期,放贷银行ID,贷款客户ID);
  • 账户 (账号,金额,开户行ID,客户ID)
  • 个人客户 (个人客户ID,客户密码,姓名,身份证号,邮箱,手机号码,理财经理工号);
  • 企业客户 (企业客户ID,客户密码,企业名称,企业法人,注册地址,营业执照号,理财经理工号);
  • 信用卡 (信用卡号,支付密码,透支额度,剩余额度,个人客户ID);
  • 结算卡 (结算卡号,支付密码,上限金额,剩余金额,企业客户ID);
  • 保险 (保险名称,保险编号,保险金额,适用人群,保险年限,保障项目);
  • 基金 (基金名称,基金编号,基金类型,基金金额,风险等级,基金管理者);
  • 存取 (客户ID,账号,存取金额,存取日期,存/取);(注：客户ID多态关联)
  • 购买基金 (客户ID,基金产品编号,购买份额);
  • 购买保险 (客户ID,保险产品编号,购买数量);

3.数据模型

3.1对象及属性对应的编号

  • Branch (branch_ID, branch_name, system_password, city, street);
  • Manager (work_number, name, system_password, ID_number, telephone, email, branch_ID);
  • Loan (loan_no,loan_amount,loan_date, repayment_date, branch_ID, customer_ID);
  • Account (account_no, account_amount, branch_ID,Customer_ID);
  • PersonalCustomer(PersonalCustomer_ID,system_password,name, ID_number,email, telephone,work_number);
  • EnterpriseCustomer (EnterpriseCustomer_ID,system_password, name, legal_person, address, business_number,work_number);
  • CreditCard (CreditCard_no, payment_password, count_limit, remain, PersonalCustomer_ID);
  • SettlementCard (SettlementCard_no, payment_password, count_limit ,remain ,EnterpriseCustomer_ID);
  • Fund(f_name,f_id，f_type，f_amount，risk_level，f_manager);
  • Insurance(i_name，i_id，i_amount，i_person，i_year，i_project);
  • Access (Customer_ID，account_no，count，access_date，state);
  • Buy_F (Customer_ID,f_id,count);
  • Buy_I (Customer_ID,i_id,count);

3.2 数据项说明

3.2.1 Branch(支行)表
  • Branch (branch_ID, branch_name, system_password, city, street);
  • 支行 (支行ID,支行名称,系统密码,城市,街道);
表3.1 Branch(支行)表
字段名称字段类型约束说明
branch_IDVARCHAR(20)PRIMARY KEY支行ID
branch_nameVARCHAR(100)UNIQUE支行名称
system_passwordVARCHAR(20)系统密码
cityVARCHAR(100)城市
streetVARCHAR(100)街道
3.2.2 Manager(理财经理)表
  • Manager (work_number, name, system_password, ID_number, telephone, email, branch_ID);
  • 理财经理 (工号,姓名,系统密码,身份证号,电话,邮箱，工作银行ID);
字段名称字段类型约束说明
work_numberVARCHAR(20)PRIMARY KEY工号
nameVARCHAR(20)NOT NULL姓名
system_passwordVARCHAR(20)NOT NULL系统密码
ID_numberVARCHAR(20)UNIQUE身份证号
telephoneVARCHAR(20)电话
emailVARCHAR(50)邮箱
branch_IDVARCHAR(20)FOREIGN KEY工作银行ID
3.2.3 Loan(贷款)表
  • Loan (loan_no,loan_amount,loan_date, repayment_date, branch_ID, customer_ID);
  • 贷款 (贷款号,贷款金额,贷款日期,还款日期,放贷银行ID,贷款客户ID);
字段名称字段类型约束说明
loan_noVARCHAR(20)PRIMARY KEY贷款号
loan_amountDOUBLE PRECISIONNOT NULL贷款金额
loan_dateDATENOT NULL贷款日期
repayment_dateDATENOT NULL还款日期
branch_IDVARCHAR(20)FOREIGN KEY放贷银行ID
customer_IDVARCHAR(20)FOREIGN KEY贷款客户ID
3.2.4 Account(账户)表
  • Account (account_no, account_amount, branch_ID,Customer_ID);
  • 账户 (账号,金额,开户行ID,客户ID);
字段名称字段类型约束说明
account_noVARCHAR(20)PRIMARY KEY账号
account_amountDOUBLE PRECISIONNOT NULL金额
branch_IDVARCHAR(20)FOREIGN KEY开户行ID
Customer_IDVARCHAR(20)FOREIGN KEY客户ID
3.2.5 PersonalCustomer(个人客户)表

  • PersonalCustomer(PersonalCustomer_ID,system_password,name, ID_number,email, telephone,work_number);

  • 个人客户 (个人客户ID,客户密码,姓名,身份证号,邮箱,手机号码,理财经理工号);

字段名称字段类型约束说明
PersonalCustomer_IDVARCHAR(20)PRIMARY KEY个人客户ID
system_passwordVARCHAR(20)NOT NULL客户密码
nameVARCHAR(20)NOT NULL姓名
ID_numberVARCHAR(20)UNIQUE身份证号
emailVARCHAR(50)邮箱
telephoneVARCHAR(20)手机号码
work_numberVARCHAR(20)FOREIGN KEY理财经理工号
3.2.6 EnterpriseCustomer(企业客户)表
  • EnterpriseCustomer (EnterpriseCustomer_ID,system_password, name, legal_person, address, business_number,work_number);
  • 企业客户 (企业客户ID,客户密码,企业名称,企业法人,注册地址,营业执照号，理财经理工号);
字段名称字段类型约束说明
EnterpriseCustomer_IDVARCHAR(20)PRIMARY KEY企业客户ID
system_passwordVARCHAR(20)NOT NULL客户密码
nameVARCHAR(20)NOT NULL企业名称
legal_personVARCHAR(20)企业法人
addressVARCHAR(100)注册地址
business_numberVARCHAR(20)营业执照
work_numberVARCHAR(20)FOREIGN KEY理财经理工号
3.2.7 CreditCard(信用卡)表
  • CreditCard (CreditCard_no, payment_password, count_limit, remain, PersonalCustomer_ID);
  • 信用卡 (信用卡号,支付密码,透支额度,剩余额度,个人客户ID);
字段名称字段类型约束说明
CreditCard_noVARCHAR(20)PRIMARY KEY信用卡号
payment_passwordVARCHAR(20)NOT NULL支付密码
count_limitDOUBLE PRECISIONCHECK透支额度
remainDOUBLE PRECISIONCHECK剩余额度
PersonalCustomer_IDVARCHAR(20)FOREIGN KEY个人客户ID
3.2.8 SettlementCard(结算卡)表
  • SettlementCard (SettlementCard_no, payment_password, count_limit ,remain ,EnterpriseCustomer_ID);
  • 结算卡 (结算卡号,支付密码,上限金额,剩余金额,企业客户ID);
字段名称字段类型约束说明
SettlementCard_noVARCHAR(20)PRIMARY KEY结算卡号
payment_passwordVARCHAR(20)NOT NULL支付密码
count_limitDOUBLE PRECISIONCHECK上限金额
remainDOUBLE PRECISIONCHECK剩余金额
EnterpriseCustomer_IDVARCHAR(20)FOREIGN KEY企业客户ID
3.2.9 fund(基金)表
  • Fund(f_name,f_id，f_type，f_amount，risk_level，f_manager);
  • 基金 (基金名称,基金编号,基金类型,基金金额,风险等级,基金管理者);
字段名称字段类型约束说明
f_nameVARCHAR(100)NOT NULL基金名称
f_idINTEGERPRIMARY KEY基金编号
f_typeVARCHAR(20)基金类型
f_amountINTEGER基金金额
risk_levelVARCHAR(20)NOT NULL风险等级
f_managerINTEGERNOT NULL基金管理者
3.2.10 insurance(保险)表
  • 保险 (保险名称,保险编号,保险金额,适用人群,保险年限,保障项目);
  • Insurance(i_name，i_id，i_amount，i_person，i_year，i_project);
字段名称字段类型约束说明
i_nameVARCHAR(100)NOT NULL保险名称
i_idINTEGERPRIMARY KEY保险编号
i_amountINTEGER保险金额
i_personVARCHAR(20)适用人群
i_yearINTEGER保险年限
i_projectVARCHAR(200)保障项目
3.2.11 Buy_I(购买保险)
  • Buy_I (Customer_ID,i_id,count);
  • 购买保险 (客户ID,基保险产品编号,购买数量);
字段名称字段类型约束说明
Customer_IDVARCHAR(20)PRIMARY KEY客户ID
i_idVARCHAR(20)PRIMARY KEY保险产品编号
countINTERGERNOT NULL、CHECK购买数量
3.2.12 Buy_F(购买基金)
  • Buy_F (Customer_ID,f_id,count);
  • 购买基金 (客户ID,基金产品编号,购买份额);
字段名称字段类型约束说明
Customer_IDVARCHAR(20)PRIMARY KEY客户ID
f_idVARCHAR(20)PRIMARY KEY保险产品编号
countINTERGERNOT NULL、CHECK购买份额

4.创建数据表

4.1 创建数据库

4.1.1 登录数据库

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

su - omm

启动数据库服务

gs_om -t start

使用gsql工具登陆数据库

gsql -d postgres -p 26000 -r

在这里插入图片描述

4.1.2 创建金融数据库

创建数据库finance

CREATE DATABASE finance ENCODING 'UTF8' template = template0;

连接finance数据库

\connect finance

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

CREATE SCHEMA finance;

将默认搜索路径设为finance

SET search_path TO finance;

在这里插入图片描述

4.2 信息表的创建

4.2.1 Branch(支行)表
  • Branch (branch_ID, branch_name, system_password, city, street);
  • 支行 (支行ID,支行名称,系统密码,城市,街道);

删除表Branch

DROP TABLE IF EXISTS finance.Branch;

创建表Branch

CREATE TABLE finance.Branch
(
	branch_ID VARCHAR(20) PRIMARY KEY ,
	branch_name VARCHAR(100) UNIQUE ,
	system_password VARCHAR(20) ,
	city VARCHAR(100) ,
	street VARCHAR(100)
);

在这里插入图片描述

使用命令查看Branch表详细信息： \d+ finance.Branch

                                    Table "finance.branch"
     Column      |          Type          | Modifiers | Storage  | Stats target | Description 
-----------------+------------------------+-----------+----------+--------------+-------------
 branch_id       | character varying(20)  | not null  | extended |              | 
 branch_name     | character varying(100) |           | extended |              | 
 system_password | character varying(20)  |           | extended |              | 
 city            | character varying(100) |           | extended |              | 
 street          | character varying(100) |           | extended |              | 
Indexes:
    "branch_pkey" PRIMARY KEY, btree (branch_id) TABLESPACE pg_default
    "branch_branch_name_key" UNIQUE CONSTRAINT, btree (branch_name) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no
4.2.2 Manager(理财经理)表
  • Manager (work_number, name, system_password, ID_number, telephone, email, branch_ID);
  • 理财经理 (工号,姓名,系统密码,身份证号,电话,邮箱，工作银行ID);

删除表Manager

DROP TABLE IF EXISTS finance.Manager;

创建表Manager

CREATE TABLE finance.Manager
(
	work_number VARCHAR(20) PRIMARY KEY , 
	name VARCHAR(20) NOT NULL , 
	system_password VARCHAR(20) NOT NULL , 
	ID_number VARCHAR(20) UNIQUE , 
	telephone VARCHAR(20) , 
	email VARCHAR(50) , 
	branch_ID VARCHAR(20),
	FOREIGN KEY (branch_ID) REFERENCES finance.Branch (branch_ID) ON UPDATE CASCADE ON DELETE CASCADE
);

在这里插入图片描述

使用命令查看Manager表详细信息： \d+ finance.Manager

                                   Table "finance.manager"
     Column      |         Type          | Modifiers | Storage  | Stats target | Description 
-----------------+-----------------------+-----------+----------+--------------+-------------
 work_number     | character varying(20) | not null  | extended |              | 
 name            | character varying(20) | not null  | extended |              | 
 system_password | character varying(20) | not null  | extended |              | 
 id_number       | character varying(20) |           | extended |              | 
 telephone       | character varying(20) |           | extended |              | 
 email           | character varying(50) |           | extended |              | 
 branch_id       | character varying(20) |           | extended |              | 
Indexes:
    "manager_pkey" PRIMARY KEY, btree (work_number) TABLESPACE pg_default
    "manager_id_number_key" UNIQUE CONSTRAINT, btree (id_number) TABLESPACE pg_default
Foreign-key constraints:
    "manager_branch_id_fkey" FOREIGN KEY (branch_id) REFERENCES branch(branch_id) ON UPDATE CASCADE ON DELETE CASCADE
Has OIDs: no
Options: orientation=row, compression=no
4.2.3 Account(账户)表
  • Account (account_no, account_amount, branch_ID,Customer_ID);
  • 账户 (账号,金额,开户行ID,客户ID);

删除表Account

DROP TABLE IF EXISTS finance.Account;

创建表Account

CREATE TABLE finance.Account
(
	account_no VARCHAR(20) PRIMARY KEY , 
	account_amount DOUBLE PRECISION NOT NULL , 
	branch_ID VARCHAR(20) , 
	Customer_ID VARCHAR(20),
	FOREIGN KEY (branch_ID) REFERENCES finance.Branch (branch_ID) ON UPDATE CASCADE ON DELETE CASCADE
);

在这里插入图片描述

使用命令查看Account表详细信息： \d+ finance.Account

                                  Table "finance.account"
     Column     |         Type          | Modifiers | Storage  | Stats target | Description 
----------------+-----------------------+-----------+----------+--------------+-------------
 account_no     | character varying(20) | not null  | extended |              | 
 account_amount | double precision      | not null  | plain    |              | 
 branch_id      | character varying(20) |           | extended |              | 
 customer_id    | character varying(20) |           | extended |              | 
Indexes:
    "account_pkey" PRIMARY KEY, btree (account_no) TABLESPACE pg_default
Foreign-key constraints:
    "account_branch_id_fkey" FOREIGN KEY (branch_id) REFERENCES finance.branch(branch_id) ON UPDATE CASCADE ON DELETE CASCADE
Has OIDs: no
Options: orientation=row, compression=no
4.2.4 PersonalCustomer(个人客户)表

  • PersonalCustomer(PersonalCustomer_ID,system_password,name, ID_number,email, telephone,work_number);

  • 个人客户 (个人客户ID,客户密码,姓名,身份证号,邮箱,手机号码,理财经理工号);

删除表PersonalCustomer

DROP TABLE IF EXISTS finance.PersonalCustomer;

创建表PersonalCustomer

CREATE TABLE finance.PersonalCustomer
(
	PersonalCustomer_ID VARCHAR(20) PRIMARY KEY , 
	system_password VARCHAR(20) NOT NULL , 
	name VARCHAR(20) NOT NULL , 
	ID_number VARCHAR(20) UNIQUE , 
	email VARCHAR(50) , 
	telephone VARCHAR(20) , 
	work_number VARCHAR(20) , 
	FOREIGN KEY (work_number) REFERENCES finance.Manager (work_number) ON UPDATE CASCADE ON DELETE CASCADE
);

在这里插入图片描述

使用命令查看PersonalCustomer表详细信息： \d+ finance.PersonalCustomer

                                Table "finance.personalcustomer"
       Column        |         Type          | Modifiers | Storage  | Stats target | Description 
---------------------+-----------------------+-----------+----------+--------------+-------------
 personalcustomer_id | character varying(20) | not null  | extended |              | 
 system_password     | character varying(20) | not null  | extended |              | 
 name                | character varying(20) | not null  | extended |              | 
 id_number           | character varying(20) |           | extended |              | 
 email               | character varying(50) |           | extended |              | 
 telephone           | character varying(20) |           | extended |              | 
 work_number         | character varying(20) |           | extended |              | 
Indexes:
    "personalcustomer_pkey" PRIMARY KEY, btree (personalcustomer_id) TABLESPACE pg_default
    "personalcustomer_id_number_key" UNIQUE CONSTRAINT, btree (id_number) TABLESPACE pg_default
Foreign-key constraints:
    "personalcustomer_work_number_fkey" FOREIGN KEY (work_number) REFERENCES manager(work_number) ON UPDATE CASCADE ON DELETE CASCADE
Has OIDs: no
Options: orientation=row, compression=no
4.2.5 EnterpriseCustomer(企业客户)表
  • EnterpriseCustomer (EnterpriseCustomer_ID,system_password, name, legal_person, address, business_number,work_number);
  • 企业客户 (企业客户ID,客户密码,企业名称,企业法人,注册地址,营业执照号，理财经理工号);

删除表EnterpriseCustomer

DROP TABLE IF EXISTS finance.EnterpriseCustomer;

创建表EnterpriseCustomer

CREATE TABLE finance.EnterpriseCustomer
(
	EnterpriseCustomer_ID VARCHAR(20) PRIMARY KEY , 
	system_password VARCHAR(20)	NOT NULL , 
	name VARCHAR(20) NOT NULL , 
	legal_person VARCHAR(20) , 
	address VARCHAR(100) , 
	business_number VARCHAR(20) ,
	work_number VARCHAR(20) ,
	FOREIGN KEY (work_number) REFERENCES finance.Manager (work_number) ON UPDATE CASCADE ON DELETE CASCADE
);

在这里插入图片描述

使用命令查看EnterpriseCustomer表详细信息： \d+ finance.EnterpriseCustomer

                                 Table "finance.enterprisecustomer"
        Column         |          Type          | Modifiers | Storage  | Stats target | Description 
-----------------------+------------------------+-----------+----------+--------------+-------------
 enterprisecustomer_id | character varying(20)  | not null  | extended |              | 
 system_password       | character varying(20)  | not null  | extended |              | 
 name                  | character varying(20)  | not null  | extended |              | 
 legal_person          | character varying(20)  |           | extended |              | 
 address               | character varying(100) |           | extended |              | 
 business_number       | character varying(20)  |           | extended |              | 
 work_number           | character varying(20)  |           | extended |              | 
Indexes:
    "enterprisecustomer_pkey" PRIMARY KEY, btree (enterprisecustomer_id) TABLESPACE pg_default
Foreign-key constraints:
    "enterprisecustomer_work_number_fkey" FOREIGN KEY (work_number) REFERENCES manager(work_number) ON UPDATE CASCADE ON DELETE CASCADE
Has OIDs: no
Options: orientation=row, compression=no
4.2.6 Loan(贷款)表
  • Loan (loan_no,loan_amount,loan_date, repayment_date, branch_ID, customer_ID);
  • 贷款 (贷款号,贷款金额,贷款日期,还款日期,放贷银行ID,贷款客户ID);

删除表Loan

DROP TABLE IF EXISTS finance.Loan;

创建表Loan

CREATE TABLE finance.Loan
(
	loan_no VARCHAR(20) PRIMARY KEY , 
	loan_amount DOUBLE PRECISION NOT NULL , 
	loan_date DATE NOT NULL , 
	repayment_date DATE NOT NULL , 
	branch_ID VARCHAR(20) , 
	customer_ID VARCHAR(20) ,
	FOREIGN KEY (branch_ID) REFERENCES finance.Branch (branch_ID)ON UPDATE CASCADE ON DELETE CASCADE 
);

在这里插入图片描述

使用命令查看Loan表详细信息： \d+ finance.Loan

                                        Table "finance.loan"
     Column     |              Type              | Modifiers | Storage  | Stats target | Description 
----------------+--------------------------------+-----------+----------+--------------+-------------
 loan_no        | character varying(20)          | not null  | extended |              | 
 loan_amount    | double precision               | not null  | plain    |              | 
 loan_date      | timestamp(0) without time zone | not null  | plain    |              | 
 repayment_date | timestamp(0) without time zone | not null  | plain    |              | 
 branch_id      | character varying(20)          |           | extended |              | 
 customer_id    | character varying(20)          |           | extended |              | 
Indexes:
    "loan_pkey" PRIMARY KEY, btree (loan_no) TABLESPACE pg_default
Foreign-key constraints:
    "loan_branch_id_fkey" FOREIGN KEY (branch_id) REFERENCES finance.branch(branch_id) ON UPDATE CASCADE ON DELETE CASCADE
Has OIDs: no
Options: orientation=row, compression=no
4.2.7 CreditCard(信用卡)表
  • CreditCard (CreditCard_no, payment_password, count_limit, remain, PersonalCustomer_ID);
  • 信用卡 (信用卡号,支付密码,透支额度,剩余额度,个人客户ID);

删除表CreditCard

DROP TABLE IF EXISTS finance.CreditCard;

创建表CreditCard

CREATE TABLE finance.CreditCard
(
	CreditCard_no VARCHAR(20) PRIMARY KEY , 
	payment_password VARCHAR(20) NOT NULL , 
	count_limit DOUBLE PRECISION CHECK (count_limit>=0),
	remain DOUBLE PRECISION CHECK (remain>=0), 
	PersonalCustomer_ID VARCHAR(20) , 
	FOREIGN KEY (PersonalCustomer_ID) REFERENCES finance.PersonalCustomer (PersonalCustomer_ID) ON UPDATE CASCADE ON DELETE CASCADE
);

在这里插入图片描述

使用命令查看CreditCard表详细信息： \d+ finance.CreditCard

                                   Table "finance.creditcard"
       Column        |         Type          | Modifiers | Storage  | Stats target | Description 
---------------------+-----------------------+-----------+----------+--------------+-------------
 creditcard_no       | character varying(20) | not null  | extended |              | 
 payment_password    | character varying(20) | not null  | extended |              | 
 count_limit         | double precision      |           | plain    |              | 
 remain              | double precision      |           | plain    |              | 
 personalcustomer_id | character varying(20) |           | extended |              | 
Indexes:
    "creditcard_pkey" PRIMARY KEY, btree (creditcard_no) TABLESPACE pg_default
Check constraints:
    "creditcard_count_limit_check" CHECK (count_limit >= 0::double precision)
    "creditcard_remain_check" CHECK (remain >= 0::double precision)
Foreign-key constraints:
    "creditcard_personalcustomer_id_fkey" FOREIGN KEY (personalcustomer_id) REFERENCES personalcustomer(personalcustomer_id) ON UPDATE CASCADE ON DELETE CASCADE
Has OIDs: no
Options: orientation=row, compression=no
4.2.8 SettlementCard(结算卡)表
  • SettlementCard (SettlementCard_no, payment_password, count_limit ,remain ,EnterpriseCustomer_ID);
  • 结算卡 (结算卡号,支付密码,上限金额,剩余金额,企业客户ID);

删除表SettlementCard

DROP TABLE IF EXISTS finance.SettlementCard;

创建表SettlementCard

CREATE TABLE finance.SettlementCard
(
	SettlementCard_no VARCHAR(20) PRIMARY KEY , 
	payment_password VARCHAR(20) NOT NULL , 
	count_limit DOUBLE PRECISION CHECK (count_limit>=0), 
	remain DOUBLE PRECISION CHECK (remain>=0), 
	EnterpriseCustomer_ID VARCHAR(20) , 
	FOREIGN KEY (EnterpriseCustomer_ID) REFERENCES finance.EnterpriseCustomer (EnterpriseCustomer_ID) ON UPDATE CASCADE ON DELETE CASCADE
);

在这里插入图片描述

使用命令查看SettlementCard表详细信息： \d+ finance.SettlementCard

                                  Table "finance.settlementcard"
        Column         |         Type          | Modifiers | Storage  | Stats target | Description 
-----------------------+-----------------------+-----------+----------+--------------+-------------
 settlementcard_no     | character varying(20) | not null  | extended |              | 
 payment_password      | character varying(20) | not null  | extended |              | 
 count_limit           | double precision      |           | plain    |              | 
 remain                | double precision      |           | plain    |              | 
 enterprisecustomer_id | character varying(20) |           | extended |              | 
Indexes:
    "settlementcard_pkey" PRIMARY KEY, btree (settlementcard_no) TABLESPACE pg_default
Check constraints:
    "settlementcard_count_limit_check" CHECK (count_limit >= 0::double precision)
    "settlementcard_remain_check" CHECK (remain >= 0::double precision)
Foreign-key constraints:
    "settlementcard_enterprisecustomer_id_fkey" FOREIGN KEY (enterprisecustomer_id) REFERENCES enterprisecustomer(enterprisecustomer_id) ON UPDATE CASCADE ON DELETE CASCADE
Has OIDs: no
Options: orientation=row, compression=no
4.2.9 fund(基金)表
  • Fund(f_name,f_id，f_type，f_amount，risk_level，f_manager);
  • 基金 (基金名称,基金编号,基金类型,基金金额,风险等级,基金管理者);

删除表fund

DROP TABLE IF EXISTS finance.fund;

创建表fund

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

在这里插入图片描述

使用命令查看fund表详细信息： \d+ finance.fund

                                  Table "finance.fund"
   Column   |          Type          | Modifiers | Storage  | Stats target | Description 
------------+------------------------+-----------+----------+--------------+-------------
 f_name     | character varying(100) | not null  | extended |              | 
 f_id       | integer                | not null  | plain    |              | 
 f_type     | character varying(20)  |           | extended |              | 
 f_amount   | integer                |           | plain    |              | 
 risk_level | character varying(20)  | not null  | extended |              | 
 f_manager  | integer                | not null  | plain    |              | 
Indexes:
    "fund_pkey" PRIMARY KEY, btree (f_id) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no
4.2.10 insurance(保险)表
  • 保险 (保险名称,保险编号,保险金额,适用人群,保险年限,保障项目);
  • Insurance(i_name，i_id，i_amount，i_person，i_year，i_project);

删除表insurance

DROP TABLE IF EXISTS finance.insurance;

创建表insurance

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

在这里插入图片描述

使用命令查看insurance表详细信息： \d+ finance.insurance

                               Table "finance.insurance"
  Column   |          Type          | Modifiers | Storage  | Stats target | Description 
-----------+------------------------+-----------+----------+--------------+-------------
 i_name    | character varying(100) | not null  | extended |              | 
 i_id      | integer                | not null  | plain    |              | 
 i_amount  | integer                |           | plain    |              | 
 i_person  | character varying(20)  |           | extended |              | 
 i_year    | integer                |           | plain    |              | 
 i_project | character varying(200) |           | extended |              | 
Indexes:
    "insurance_pkey" PRIMARY KEY, btree (i_id) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no
4.2.11 Buy_I(购买保险)
  • Buy_I (Customer_ID,i_id,count);
  • 保险基金 (客户ID,保险产品编号,购买数量);

删除表Buy_I

DROP TABLE IF EXISTS finance.Buy_I;

创建表Buy_I

CREATE TABLE finance.Buy_I
(
	Customer_ID VARCHAR(20) , 
	i_id INT ,
	count INT NOT NULL,
	PRIMARY KEY (Customer_ID,i_id),
	FOREIGN KEY (i_id) REFERENCES finance.insurance (i_id) ON UPDATE CASCADE ON DELETE CASCADE
);

在这里插入图片描述

使用命令查看Buy_I表详细信息： \d+ finance.Buy_I

                                  Table "finance.buy_i"
   Column    |         Type          | Modifiers | Storage  | Stats target | Description 
-------------+-----------------------+-----------+----------+--------------+-------------
 customer_id | character varying(20) | not null  | extended |              | 
 i_id        | integer               | not null  | plain    |              | 
 count       | integer               | not null  | plain    |              | 
Indexes:
    "buy_i_pkey" PRIMARY KEY, btree (customer_id, i_id) TABLESPACE pg_default
Foreign-key constraints:
    "buy_i_i_id_fkey" FOREIGN KEY (i_id) REFERENCES finance.insurance(i_id) ON UPDATE CASCADE ON DELETE CASCADE
Has OIDs: no
Options: orientation=row, compression=no
4.2.12 Buy_F(购买基金)
  • Buy_F (Customer_ID,f_id,count);
  • 购买基金 (客户ID,基金产品编号,购买份额);

删除表Buy_F

DROP TABLE IF EXISTS finance.Buy_F;

创建表Buy_F

CREATE TABLE finance.Buy_F
(
	Customer_ID VARCHAR(20) , 
	f_id INT , 
	count INT NOT NULL , 
	PRIMARY KEY (Customer_ID,f_id),
	FOREIGN KEY (f_id) REFERENCES finance.fund (f_id) ON UPDATE CASCADE ON DELETE CASCADE
);

在这里插入图片描述

使用命令查看Buy_F表详细信息： \d+ finance.Buy_F

                                  Table "finance.buy_f"
   Column    |         Type          | Modifiers | Storage  | Stats target | Description 
-------------+-----------------------+-----------+----------+--------------+-------------
 customer_id | character varying(20) | not null  | extended |              | 
 f_id        | integer               | not null  | plain    |              | 
 count       | integer               | not null  | plain    |              | 
Indexes:
    "buy_f_pkey" PRIMARY KEY, btree (customer_id, f_id) TABLESPACE pg_default
Foreign-key constraints:
    "buy_f_f_id_fkey" FOREIGN KEY (f_id) REFERENCES finance.fund(f_id) ON UPDATE CASCADE ON DELETE CASCADE
Has OIDs: no
Options: orientation=row, compression=no

4.3 表信息查看

列举表：

`finance=#`  \dt

                                List of relations
 Schema  |        Name        | Type  | Owner |             Storage              
---------+--------------------+-------+-------+----------------------------------
 finance | access             | table | omm   | {orientation=row,compression=no}
 finance | account            | table | omm   | {orientation=row,compression=no}
 finance | branch             | table | omm   | {orientation=row,compression=no}
 finance | buy_f              | table | omm   | {orientation=row,compression=no}
 finance | buy_i              | table | omm   | {orientation=row,compression=no}
 finance | creditcard         | table | omm   | {orientation=row,compression=no}
 finance | enterprisecustomer | table | omm   | {orientation=row,compression=no}
 finance | fund               | table | omm   | {orientation=row,compression=no}
 finance | insurance          | table | omm   | {orientation=row,compression=no}
 finance | loan               | table | omm   | {orientation=row,compression=no}
 finance | manager            | table | omm   | {orientation=row,compression=no}
 finance | personalcustomer   | table | omm   | {orientation=row,compression=no}
 finance | settlementcard     | table | omm   | {orientation=row,compression=no}
(13 rows)

5.数据库完整性

5.1 实体完整性约束

使用如下命令查询主码：

SELECT
     tc.constraint_name, tc.table_name, kcu.column_name, 
     ccu.table_name AS foreign_table_name,
     ccu.column_name AS foreign_column_name,
     tc.is_deferrable,tc.initially_deferred
 FROM 
     information_schema.table_constraints AS tc 
     JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
     JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
 WHERE constraint_type = 'PRIMARY KEY' ;

在这里插入图片描述

     constraint_name     |     table_name     |      column_name      | foreign_table_name |  foreign_column_name  | is_deferrable | initially_deferred 
-------------------------+--------------------+-----------------------+--------------------+-----------------------+---------------+--------------------
 branch_pkey             | branch             | branch_id             | branch             | branch_id             | NO            | NO
 manager_pkey            | manager            | work_number           | manager            | work_number           | NO            | NO
 personalcustomer_pkey   | personalcustomer   | personalcustomer_id   | personalcustomer   | personalcustomer_id   | NO            | NO
 creditcard_pkey         | creditcard         | creditcard_no         | creditcard         | creditcard_no         | NO            | NO
 settlementcard_pkey     | settlementcard     | settlementcard_no     | settlementcard     | settlementcard_no     | NO            | NO
 fund_pkey               | fund               | f_id                  | fund               | f_id                  | NO            | NO
 insurance_pkey          | insurance          | i_id                  | insurance          | i_id                  | NO            | NO
 loan_pkey               | loan               | loan_no               | loan               | loan_no               | NO            | NO
 account_pkey            | account            | account_no            | account            | account_no            | NO            | NO
 enterprisecustomer_pkey | enterprisecustomer | enterprisecustomer_id | enterprisecustomer | enterprisecustomer_id | NO            | NO
 buy_i_pkey              | buy_i              | i_id                  | buy_i              | customer_id           | NO            | NO
 buy_i_pkey              | buy_i              | customer_id           | buy_i              | customer_id           | NO            | NO
 buy_i_pkey              | buy_i              | i_id                  | buy_i              | i_id                  | NO            | NO
 buy_i_pkey              | buy_i              | customer_id           | buy_i              | i_id                  | NO            | NO
 buy_f_pkey              | buy_f              | f_id                  | buy_f              | customer_id           | NO            | NO
 buy_f_pkey              | buy_f              | customer_id           | buy_f              | customer_id           | NO            | NO
 buy_f_pkey              | buy_f              | f_id                  | buy_f              | f_id                  | NO            | NO
 buy_f_pkey              | buy_f              | customer_id           | buy_f              | f_id                  | NO            | NO
(18 rows)

5.2 参照完整性

CREATE TRIGGER Buy_IForeign
AFTER UPDATE OF Customer_ID ON finance.Buy_I
FOR EACH ROW
EXECUTE PROCEDURE Buy_IForeign() ;

使用如下命令查询外码：

SELECT
     tc.constraint_name, tc.table_name, kcu.column_name, 
     ccu.table_name AS foreign_table_name,
     ccu.column_name AS foreign_column_name,
     tc.is_deferrable,tc.initially_deferred
 FROM 
     information_schema.table_constraints AS tc 
     JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
     JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
 WHERE constraint_type = 'FOREIGN KEY' ;

在这里插入图片描述

           constraint_name           |     table_name     |     column_name     | foreign_table_name | foreign_column_name | is_deferrable | initially_deferred 
-------------------------------------+--------------------+---------------------+--------------------+---------------------+---------------+--------------------
 manager_branch_id_fkey              | manager            | branch_id           | branch             | branch_id           | NO            | NO
 loan_branch_id_fkey                 | loan               | branch_id           | branch             | branch_id           | NO            | NO
 account_branch_id_fkey              | account            | branch_id           | branch             | branch_id           | NO            | NO
 personalcustomer_work_number_fkey   | personalcustomer   | work_number         | manager            | work_number         | NO            | NO
 enterprisecustomer_work_number_fkey | enterprisecustomer | work_number         | manager            | work_number         | NO            | NO
 creditcard_personalcustomer_id_fkey | creditcard         | personalcustomer_id | personalcustomer   | personalcustomer_id | NO            | NO
 buy_f_f_id_fkey                     | buy_f              | f_id                | fund               | f_id                | NO            | NO
 buy_i_i_id_fkey                     | buy_i              | i_id                | insurance          | i_id                | NO            | NO
(8 rows)

5.3 用户自定义完整性

查看自定义触发器

SELECT * FROM pg_trigger WHERE tgisinternal = 'f';

tgisinternal 表示触发器是否为内部生成的

在这里插入图片描述

 tgrelid |          tgname          | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstraint | tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs | tgqual | tgowner 
---------+--------------------------+--------+--------+-----------+--------------+---------------+---------------+--------------+--------------+----------------+---------+--------+--------+--------+---------
   17273 | deleteenterprisecustomer |  17359 |      9 | O         | f            |             0 |             0 |            0 | f            | f              |       0 |        | \x     |        |      10
   17224 | accountforeign           |  17246 |      5 | O         | f            |             0 |             0 |            0 | f            | f              |       0 |        | \x     |        |      10
   17193 | loanforeign              |  17250 |      5 | O         | f            |             0 |             0 |            0 | f            | f              |       0 |        | \x     |        |      10
   17304 | buy_iforeign             |  17350 |      5 | O         | f            |             0 |             0 |            0 | f            | f              |       0 |        | \x     |        |      10
   17314 | buy_fforeign             |  17352 |      5 | O         | f            |             0 |             0 |            0 | f            | f              |       0 |        | \x     |        |      10
   17008 | updatepersonalcustomer   |  17355 |     17 | O         | f            |             0 |             0 |            0 | f            | f              |       0 | 1      | \x     |        |      10
   17273 | updateenterprisecustomer |  17357 |     17 | O         | f            |             0 |             0 |            0 | f            | f              |       0 | 1      | \x     |        |      10
   17224 | updateaccount            |  17365 |     19 | O         | f            |             0 |             0 |            0 | f            | f              |       0 | 4      | \x     |        |      10
   17193 | updateloan               |  17368 |     19 | O         | f            |             0 |             0 |            0 | f            | f              |       0 | 6      | \x     |        |      10
   17304 | updatebuy_i              |  17370 |     19 | O         | f            |             0 |             0 |            0 | f            | f              |       0 | 1      | \x     |        |      10
   17314 | updatebuy_f              |  17372 |     19 | O         | f            |             0 |             0 |            0 | f            | f              |       0 | 1      | \x     |        |      10
   17008 | deletepersonalcustomer   |  17359 |      9 | O         | f            |             0 |             0 |            0 | f            | f              |       0 |        | \x     |        |      10
(12 rows)
5.3.1AccountForeign触发器
DROP TRIGGER IF EXISTS AccountForeign on finance.account;

CREATE OR REPLACE FUNCTION AccountForeign() 
RETURNS TRIGGER AS $$ 
BEGIN
IF (NEW.Customer_ID NOT IN 
(
SELECT PersonalCustomer_ID
FROM finance.PersonalCustomer
UNION
SELECT EnterpriseCustomer_ID
FROM finance.EnterpriseCustomer
) )
THEN
RAISE NOTICE 'can not insert';
RETURN NULL;
END IF;
RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;

DELETE FROM finance.Account
WHERE finance.Account.Customer_ID=NEW.Customer_ID;

CREATE TRIGGER AccountForeign
BEFORE INSERT ON finance.Account
FOR EACH ROW
EXECUTE PROCEDURE AccountForeign() ;
5.3.2 LoanForeign触发器
DROP TRIGGER IF EXISTS LoanForeign on finance.Loan;

CREATE OR REPLACE FUNCTION LoanForeign() 
RETURNS TRIGGER AS $$ 
BEGIN
IF (NEW.Customer_ID NOT IN 
(
SELECT PersonalCustomer_ID
FROM finance.PersonalCustomer
UNION
SELECT EnterpriseCustomer_ID
FROM finance.EnterpriseCustomer
) )
THEN
RAISE NOTICE 'can not insert';
RETURN NULL;
END IF;
RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER LoanForeign
BEFORE INSERT ON finance.Loan
FOR EACH ROW
EXECUTE PROCEDURE LoanForeign() ;
5.3.3 Buy_IForeign触发器
DROP TRIGGER IF EXISTS Buy_IForeign on finance.Buy_I;

CREATE OR REPLACE FUNCTION Buy_IForeign() 
RETURNS TRIGGER AS $$ 
BEGIN
IF (NEW.Customer_ID NOT IN 
(
SELECT PersonalCustomer_ID
FROM finance.PersonalCustomer
UNION
SELECT EnterpriseCustomer_ID
FROM finance.EnterpriseCustomer
) )
THEN
RAISE NOTICE 'can not insert';
RETURN NULL;
END IF;
RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER Buy_IForeign
BEFORE INSERT ON finance.Buy_I
FOR EACH ROW
EXECUTE PROCEDURE Buy_IForeign() ;
5.3.4 Buy_FForeign触发器
DROP TRIGGER IF EXISTS Buy_FForeign on finance.Buy_F;

CREATE OR REPLACE FUNCTION Buy_FForeign() 
RETURNS TRIGGER AS $$ 
BEGIN
IF (NEW.Customer_ID NOT IN 
(
SELECT PersonalCustomer_ID
FROM finance.PersonalCustomer
UNION
SELECT EnterpriseCustomer_ID
FROM finance.EnterpriseCustomer
) )
THEN
RAISE NOTICE 'can not insert';
RETURN NULL;
END IF;
RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER Buy_FForeign
BEFORE INSERT ON finance.Buy_F
FOR EACH ROW
EXECUTE PROCEDURE Buy_FForeign() ;
5.3.5 UpdatePersonalCustomer触发器
DROP TRIGGER IF EXISTS UpdatePersonalCustomer on finance.PersonalCustomer;

CREATE OR REPLACE FUNCTION UpdatePersonalCustomer() 
RETURNS TRIGGER AS $$ 
BEGIN
UPDATE finance.Account SET Customer_ID = NEW.PersonalCustomer_ID
WHERE Customer_ID = OLD.PersonalCustomer_ID;
UPDATE finance.Loan SET Customer_ID = NEW.PersonalCustomer_ID
WHERE Customer_ID = OLD.PersonalCustomer_ID;
UPDATE finance.Buy_I SET Customer_ID = NEW.PersonalCustomer_ID
WHERE Customer_ID = OLD.PersonalCustomer_ID;
UPDATE finance.Buy_F SET Customer_ID = NEW.PersonalCustomer_ID
WHERE Customer_ID = OLD.PersonalCustomer_ID;
RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER UpdatePersonalCustomer
AFTER UPDATE OF PersonalCustomer_ID ON finance.PersonalCustomer
FOR EACH ROW
EXECUTE PROCEDURE UpdatePersonalCustomer() ;

在这里插入图片描述

5.3.6 UpdateEnterpriseCustomer触发器
DROP TRIGGER IF EXISTS UpdateEnterpriseCustomer on finance.EnterpriseCustomer;

CREATE OR REPLACE FUNCTION UpdateEnterpriseCustomer() 
RETURNS TRIGGER AS $$ 
BEGIN
UPDATE finance.Account SET Customer_ID = NEW.EnterpriseCustomer_ID
WHERE Customer_ID = OLD.EnterpriseCustomer_ID;
UPDATE finance.Loan SET Customer_ID = NEW.EnterpriseCustomer_ID
WHERE Customer_ID = OLD.EnterpriseCustomer_ID;
UPDATE finance.Buy_I SET Customer_ID = NEW.EnterpriseCustomer_ID
WHERE Customer_ID = OLD.EnterpriseCustomer_ID;
UPDATE finance.Buy_F SET Customer_ID = NEW.EnterpriseCustomer_ID
WHERE Customer_ID = OLD.EnterpriseCustomer_ID;
RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER UpdateEnterpriseCustomer
AFTER UPDATE OF EnterpriseCustomer_ID ON finance.EnterpriseCustomer
FOR EACH ROW
EXECUTE PROCEDURE UpdateEnterpriseCustomer() ;

在这里插入图片描述

5.3.7 DeletePersonalCustomer触发器
DROP TRIGGER IF EXISTS DeletePersonalCustomer on finance.PersonalCustomer;

CREATE OR REPLACE FUNCTION DeletePersonalCustomer() 
RETURNS TRIGGER AS $$ 
BEGIN
DELETE FROM finance.Account 
WHERE Customer_ID = OLD.PersonalCustomer_ID;
DELETE FROM finance.Loan 
WHERE Customer_ID = OLD.PersonalCustomer_ID;
DELETE FROM finance.Buy_I 
WHERE Customer_ID = OLD.PersonalCustomer_ID;
DELETE FROM finance.Buy_F 
WHERE Customer_ID = OLD.PersonalCustomer_ID;
RETURN OLD;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER DeletePersonalCustomer
AFTER DELETE ON finance.PersonalCustomer
FOR EACH ROW
EXECUTE PROCEDURE DeletePersonalCustomer() ;
5.3.8 DeleteEnterpriseCustomer触发器
DROP TRIGGER IF EXISTS DeleteEnterpriseCustomer on finance.EnterpriseCustomer;

CREATE OR REPLACE FUNCTION DeleteEnterpriseCustomer() 
RETURNS TRIGGER AS $$ 
BEGIN
DELETE FROM finance.Account 
WHERE Customer_ID = OLD.EnterpriseCustomer_ID;
DELETE FROM finance.Loan 
WHERE Customer_ID = OLD.EnterpriseCustomer_ID;
DELETE FROM finance.Buy_I 
WHERE Customer_ID = OLD.EnterpriseCustomer_ID;
DELETE FROM finance.Buy_F 
WHERE Customer_ID = OLD.EnterpriseCustomer_ID;
RETURN OLD;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER DeleteEnterpriseCustomer
AFTER DELETE ON finance.EnterpriseCustomer
FOR EACH ROW
EXECUTE PROCEDURE DeletePersonalCustomer() ;
5.3.9 UpdateAccount触发器
DROP TRIGGER IF EXISTS UpdateAccount on finance.account;

CREATE OR REPLACE FUNCTION UpdateAccount() 
RETURNS TRIGGER AS $$ 
BEGIN
RAISE NOTICE 'can not update';
RETURN NULL;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER UpdateAccount
BEFORE UPDATE OF Customer_ID ON finance.Account
FOR EACH ROW
EXECUTE PROCEDURE UpdateAccount() ;
5.3.10 UpdateLoan触发器
DROP TRIGGER IF EXISTS UpdateLoan on finance.Loan;

CREATE OR REPLACE FUNCTION UpdateLoan() 
RETURNS TRIGGER AS $$ 
BEGIN
RAISE NOTICE 'can not update';
RETURN NULL;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER UpdateLoan
BEFORE UPDATE OF Customer_ID ON finance.Loan
FOR EACH ROW
EXECUTE PROCEDURE UpdateLoan() ;
5.3.11 UpdateBuy_I触发器
DROP TRIGGER IF EXISTS UpdateBuy_I on finance.Buy_I;

CREATE OR REPLACE FUNCTION UpdateBuy_I() 
RETURNS TRIGGER AS $$ 
BEGIN
RAISE NOTICE 'can not update';
RETURN NULL;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER UpdateBuy_I
BEFORE UPDATE OF Customer_ID ON finance.Buy_I
FOR EACH ROW
EXECUTE PROCEDURE UpdateBuy_I() ;
5.3.12 UpdateBuy_F触发器
DROP TRIGGER IF EXISTS UpdateBuy_F on finance.Buy_F;

CREATE OR REPLACE FUNCTION UpdateBuy_F() 
RETURNS TRIGGER AS $$ 
BEGIN
RAISE NOTICE 'can not update';
RETURN NULL;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER UpdateBuy_F
BEFORE UPDATE OF Customer_ID ON finance.Buy_F
FOR EACH ROW
EXECUTE PROCEDURE UpdateBuy_F() ;

6.物理结构设计

6.1 建立索引

6.1.1 Account表建立索引

银行-账户

CREATE INDEX branch_account
ON finance.Account (branch_id);

在这里插入图片描述

6.1.2 Manager表建立索引

银行-经理

CREATE INDEX branch_manager
ON finance.Manager (branch_id);

在这里插入图片描述

6.1.3 PersonalCustomer表建立索引

经理-个人客户

CREATE INDEX manager_PersonalCustomer
ON finance.PersonalCustomer (work_number);

在这里插入图片描述

6.1.4 EnterpriseCustomer表建立索引

经理-企业客户

CREATE INDEX manager_EnterpriseCustomer
ON finance.EnterpriseCustomer (work_number);

在这里插入图片描述

6.1.5 CreditCard表建立索引

个人客户-信用卡

CREATE INDEX PersonalCustomer_CreditCard
ON finance.CreditCard (PersonalCustomer_ID);

在这里插入图片描述

6.1.6 SettlementCard表建立索引

企业客户-结算卡

CREATE INDEX EnterpriseCustomer_SettlementCard
ON finance.SettlementCard (EnterpriseCustomer_ID);

在这里插入图片描述

6.2 查看索引

SELECT *
FROM pg_indexes
WHERE schemaname = 'finance'
ORDER BY tablename,indexname;

在这里插入图片描述

 schemaname |     tablename      |             indexname             | tablespace |                                                              indexdef                                                               
------------+--------------------+-----------------------------------+------------+-------------------------------------------------------------------------------------------------------------------------------------
 finance    | account            | account_pkey                      |            | CREATE UNIQUE INDEX account_pkey ON finance.account USING btree (account_no) TABLESPACE pg_default
 finance    | account            | branch_account                    |            | CREATE INDEX branch_account ON finance.account USING btree (branch_id) TABLESPACE pg_default
 finance    | branch             | branch_branch_name_key            |            | CREATE UNIQUE INDEX branch_branch_name_key ON finance.branch USING btree (branch_name) TABLESPACE pg_default
 finance    | branch             | branch_pkey                       |            | CREATE UNIQUE INDEX branch_pkey ON finance.branch USING btree (branch_id) TABLESPACE pg_default
 finance    | buy_f              | buy_f_pkey                        |            | CREATE UNIQUE INDEX buy_f_pkey ON finance.buy_f USING btree (customer_id, f_id) TABLESPACE pg_default
 finance    | buy_i              | buy_i_pkey                        |            | CREATE UNIQUE INDEX buy_i_pkey ON finance.buy_i USING btree (customer_id, i_id) TABLESPACE pg_default
 finance    | creditcard         | creditcard_pkey                   |            | CREATE UNIQUE INDEX creditcard_pkey ON finance.creditcard USING btree (creditcard_no) TABLESPACE pg_default
 finance    | creditcard         | personalcustomer_creditcard       |            | CREATE INDEX personalcustomer_creditcard ON finance.creditcard USING btree (personalcustomer_id) TABLESPACE pg_default
 finance    | enterprisecustomer | enterprisecustomer_pkey           |            | CREATE UNIQUE INDEX enterprisecustomer_pkey ON finance.enterprisecustomer USING btree (enterprisecustomer_id) TABLESPACE pg_default
 finance    | enterprisecustomer | manager_enterprisecustomer        |            | CREATE INDEX manager_enterprisecustomer ON finance.enterprisecustomer USING btree (work_number) TABLESPACE pg_default
 finance    | fund               | fund_pkey                         |            | CREATE UNIQUE INDEX fund_pkey ON finance.fund USING btree (f_id) TABLESPACE pg_default
 finance    | insurance          | insurance_pkey                    |            | CREATE UNIQUE INDEX insurance_pkey ON finance.insurance USING btree (i_id) TABLESPACE pg_default
 finance    | loan               | loan_pkey                         |            | CREATE UNIQUE INDEX loan_pkey ON finance.loan USING btree (loan_no) TABLESPACE pg_default
 finance    | manager            | branch_manager                    |            | CREATE INDEX branch_manager ON finance.manager USING btree (branch_id) TABLESPACE pg_default
 finance    | manager            | manager_id_number_key             |            | CREATE UNIQUE INDEX manager_id_number_key ON finance.manager USING btree (id_number) TABLESPACE pg_default
 finance    | manager            | manager_pkey                      |            | CREATE UNIQUE INDEX manager_pkey ON finance.manager USING btree (work_number) TABLESPACE pg_default
 finance    | personalcustomer   | manager_personalcustomer          |            | CREATE INDEX manager_personalcustomer ON finance.personalcustomer USING btree (work_number) TABLESPACE pg_default
 finance    | personalcustomer   | personalcustomer_id_number_key    |            | CREATE UNIQUE INDEX personalcustomer_id_number_key ON finance.personalcustomer USING btree (id_number) TABLESPACE pg_default
 finance    | personalcustomer   | personalcustomer_pkey             |            | CREATE UNIQUE INDEX personalcustomer_pkey ON finance.personalcustomer USING btree (personalcustomer_id) TABLESPACE pg_default
 finance    | settlementcard     | enterprisecustomer_settlementcard |            | CREATE INDEX enterprisecustomer_settlementcard ON finance.settlementcard USING btree (enterprisecustomer_id) TABLESPACE pg_default
 finance    | settlementcard     | settlementcard_pkey               |            | CREATE UNIQUE INDEX settlementcard_pkey ON finance.settlementcard USING btree (settlementcard_no) TABLESPACE pg_default
(21 rows)

7.数据库安全性

7.1 视图

SELECT schemaname,viewname,viewowner
FROM   pg_views  
WHERE schemaname = 'finance';

 schemaname |       viewname        | viewowner 
------------+-----------------------+-----------
 finance    | newbranch             | omm
 finance    | newmanager            | omm
 finance    | newpersonalcustomer   | omm
 finance    | newenterprisecustomer | omm
(4 rows)

使用视图屏蔽掉密码

7.1.1 newBranch视图
CREATE VIEW newBranch AS
SELECT branch_ID,branch_name,city,street
FROM finance.Branch;

在这里插入图片描述

7.1.2 newManager视图
  • Manager (work_number, name, system_password, ID_number, telephone, email, branch_ID);
CREATE VIEW newManager AS
SELECT work_number, name, ID_number, telephone, email, branch_ID
FROM finance.Manager;

在这里插入图片描述

7.1.3 newPersonalCustomer视图
CREATE VIEW newPersonalCustomer AS
SELECT PersonalCustomer_ID,name, ID_number,email, telephone,work_number
FROM finance.PersonalCustomer;

在这里插入图片描述

7.1.4 newEnterpriseCustomer视图
  • EnterpriseCustomer (EnterpriseCustomer_ID,system_password, name, legal_person, address, business_number,work_number);
CREATE VIEW newEnterpriseCustomer AS
SELECT EnterpriseCustomer_ID, name, legal_person, address, business_number,work_number
FROM finance.EnterpriseCustomer;

在这里插入图片描述

7.2 角色及用户权限

7.2.1 客户系统权限

创建个人客户用户

CREATE USER pclient IDENTIFIED BY 'iebokai@163.com';

数据库授权

GRANT ALL ON DATABASE finance to pclient;

模式授权

GRANT ALL ON SCHEMA finance to pclient;

表授权

GRANT SELECT ON TABLE finance.Account to pclient;
GRANT SELECT ON TABLE finance.Loan to pclient;
GRANT SELECT ON TABLE finance.CreditCard to pclient;
GRANT SELECT ON TABLE finance.fund to pclient;
GRANT SELECT ON TABLE finance.insurance to pclient;
GRANT SELECT ON TABLE finance.Buy_I to pclient;
GRANT SELECT ON TABLE finance.Buy_F to pclient;

GRANT SELECT ON finance.newBranch to pclient;
GRANT SELECT ON finance.newManager to pclient;
GRANT SELECT ON finance.newPersonalCustomer to pclient;
GRANT SELECT ON finance.newEnterpriseCustomer to pclient;

创建企业客户用户

CREATE USER eclient IDENTIFIED BY 'iebokai@163.com';

数据库授权

GRANT ALL ON DATABASE finance to eclient;

模式授权

GRANT ALL ON SCHEMA finance to eclient;

表授权

GRANT SELECT ON TABLE finance.Account to eclient;
GRANT SELECT ON TABLE finance.EnterpriseCustomer to eclient;
GRANT SELECT ON TABLE finance.Loan to eclient;
GRANT SELECT ON TABLE finance.SettlementCard to eclient;
GRANT SELECT ON TABLE finance.fund to eclient;
GRANT SELECT ON TABLE finance.insurance to eclient;
GRANT SELECT ON TABLE finance.Buy_I to eclient;
GRANT SELECT ON TABLE finance.Buy_F to eclient;

视图授权

GRANT SELECT ON finance.newBranch to eclient;
GRANT SELECT ON finance.newManager to eclient;
GRANT SELECT ON finance.newPersonalCustomer to eclient;
GRANT SELECT ON finance.newEnterpriseCustomer to eclient;

创建客户用户

CREATE USER client IDENTIFIED BY 'iebokai@163.com';

数据库授权

GRANT ALL ON DATABASE finance to client;

模式授权

GRANT ALL ON SCHEMA finance to client;

表授权

GRANT SELECT ON TABLE finance.Branch to client;
GRANT SELECT ON TABLE finance.Manager to client;
GRANT ALL ON TABLE finance.Account to client;
GRANT ALL ON TABLE finance.PersonalCustomer to client;
GRANT ALL ON TABLE finance.EnterpriseCustomer to client;
GRANT ALL ON TABLE finance.Loan to client;
GRANT ALL ON TABLE finance.CreditCard to client;
GRANT ALL ON TABLE finance.SettlementCard to client;
GRANT SELECT ON TABLE finance.fund to client;
GRANT SELECT ON TABLE finance.insurance to client;
GRANT ALL ON TABLE finance.Buy_I to client;
GRANT ALL ON TABLE finance.Buy_F to client;

视图授权

GRANT SELECT ON finance.newBranch to client;
GRANT SELECT ON finance.newManager to client;
GRANT SELECT ON finance.newPersonalCustomer to client;
GRANT SELECT ON finance.newEnterpriseCustomer to client;

在这里插入图片描述

7.2.2 经理系统权限

创建经理用户

CREATE USER manager IDENTIFIED BY 'iebokai@163.com';

数据库授权

GRANT ALL ON DATABASE finance to manager;

模式授权

GRANT ALL ON SCHEMA finance to manager;

表授权

GRANT UPDATE ON TABLE finance.Manager to manager;
GRANT SELECT ON TABLE finance.Account to manager;
GRANT SELECT ON TABLE finance.Loan to manager;
GRANT SELECT ON TABLE finance.CreditCard to manager;
GRANT SELECT ON TABLE finance.SettlementCard to manager;
GRANT SELECT ON TABLE finance.fund to manager;
GRANT SELECT ON TABLE finance.insurance to manager;
GRANT SELECT ON TABLE finance.Buy_I to manager;
GRANT SELECT ON TABLE finance.Buy_F to manager;

视图授权

GRANT SELECT ON finance.newBranch to manager;
GRANT SELECT ON finance.newManager to manager;
GRANT SELECT ON finance.newPersonalCustomer to manager;
GRANT SELECT ON finance.newEnterpriseCustomer to manager;

在这里插入图片描述

7.2.3 管理员权限

创建管理员用户bk

CREATE USER bk IDENTIFIED BY 'iebokai@163.com';

数据库授权

GRANT ALL ON DATABASE finance to bk;

模式授权

GRANT ALL ON SCHEMA finance to bk;

表授权

GRANT ALL ON TABLE finance.Branch to bk;
GRANT ALL ON TABLE finance.Manager to bk;
GRANT ALL ON TABLE finance.Account to bk;
GRANT ALL ON TABLE finance.PersonalCustomer to bk;
GRANT ALL ON TABLE finance.EnterpriseCustomer to bk;
GRANT ALL ON TABLE finance.Loan to bk;
GRANT ALL ON TABLE finance.CreditCard to bk;
GRANT ALL ON TABLE finance.SettlementCard to bk;
GRANT ALL ON TABLE finance.fund to bk;
GRANT ALL ON TABLE finance.insurance to bk;
GRANT ALL ON TABLE finance.Buy_I to bk;
GRANT ALL ON TABLE finance.Buy_F to bk;

视图授权

GRANT ALL ON newBranch to bk;
GRANT ALL ON newManager to bk;
GRANT ALL ON newPersonalCustomer to bk;
GRANT ALL ON newEnterpriseCustomer to bk;

在这里插入图片描述

7.3 权限查看

7.3.1 查看所有角色
SELECT * FROM pg_roles;

 rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolreplication | rolauditadmin | rolsystemadmin | rolconnlimit | rolpassword | rolvalidbegin | rolvaliduntil |  rolrespool  | rolparentid | roltabspace | rolconfig |  oid  | roluseft | rolkind | nodegroup | roltempspace | rolspillspace | rolmonitoradmin | roloperatoradmin | rolpolicyadmin 
---------+----------+------------+---------------+-------------+--------------+-------------+----------------+---------------+----------------+--------------+-------------+---------------+---------------+--------------+-------------+-------------+-----------+-------+----------+---------+-----------+--------------+---------------+-----------------+------------------+----------------
 omm     | t        | t          | t             | t           | t            | t           | t              | t             | t              |           -1 | ********    |               |               | default_pool |           0 |             |           |    10 | t        | n       |           |              |               | t               | t                | t
 dbuser  | f        | t          | t             | t           | f            | t           | f              | f             | f              |           -1 | ********    |               |               | default_pool |           0 |             |           | 16679 | f        | n       |           |              |               | f               | f                | f
 bk      | f        | t          | f             | f           | f            | t           | f              | f             | f              |           -1 | ********    |               |               | default_pool |           0 |             |           | 17374 | f        | n       |           |              |               | f               | f                | f
 manager | f        | t          | f             | f           | f            | t           | f              | f             | f              |           -1 | ********    |               |               | default_pool |           0 |             |           | 17378 | f        | n       |           |              |               | f               | f                | f
 client  | f        | t          | f             | f           | f            | t           | f              | f             | f              |           -1 | ********    |               |               | default_pool |           0 |             |           | 17382 | f        | n       |           |              |               | f               | f                | f
(5 rows)
7.3.2 查看角色权限

客户用户权限查看

SELECT * FROM INFORMATION_SCHEMA.role_table_grants 
where table_schema ='finance' AND grantee='client';

 grantor | grantee | table_catalog | table_schema |      table_name       | privilege_type | is_grantable | with_hierarchy 
---------+---------+---------------+--------------+-----------------------+----------------+--------------+----------------
 omm     | client  | finance       | finance      | branch                | SELECT         | NO           | YES
 omm     | client  | finance       | finance      | manager               | SELECT         | NO           | YES
 omm     | client  | finance       | finance      | fund                  | SELECT         | NO           | YES
 omm     | client  | finance       | finance      | creditcard            | INSERT         | NO           | NO
 omm     | client  | finance       | finance      | creditcard            | SELECT         | NO           | YES
 omm     | client  | finance       | finance      | creditcard            | UPDATE         | NO           | NO
 omm     | client  | finance       | finance      | creditcard            | DELETE         | NO           | NO
 omm     | client  | finance       | finance      | creditcard            | TRUNCATE       | NO           | NO
 omm     | client  | finance       | finance      | creditcard            | REFERENCES     | NO           | NO
 omm     | client  | finance       | finance      | creditcard            | TRIGGER        | NO           | NO
 omm     | client  | finance       | finance      | creditcard            | ALTER          | NO           | NO
 omm     | client  | finance       | finance      | creditcard            | DROP           | NO           | NO
 omm     | client  | finance       | finance      | creditcard            | COMMENT        | NO           | NO
 omm     | client  | finance       | finance      | creditcard            | INDEX          | NO           | NO
 omm     | client  | finance       | finance      | creditcard            | VACUUM         | NO           | NO
 omm     | client  | finance       | finance      | settlementcard        | INSERT         | NO           | NO
 omm     | client  | finance       | finance      | settlementcard        | SELECT         | NO           | YES
 omm     | client  | finance       | finance      | settlementcard        | UPDATE         | NO           | NO
 omm     | client  | finance       | finance      | settlementcard        | DELETE         | NO           | NO
 omm     | client  | finance       | finance      | settlementcard        | TRUNCATE       | NO           | NO
 omm     | client  | finance       | finance      | settlementcard        | REFERENCES     | NO           | NO
 omm     | client  | finance       | finance      | settlementcard        | TRIGGER        | NO           | NO
 omm     | client  | finance       | finance      | settlementcard        | ALTER          | NO           | NO
 omm     | client  | finance       | finance      | settlementcard        | DROP           | NO           | NO
 omm     | client  | finance       | finance      | settlementcard        | COMMENT        | NO           | NO
 omm     | client  | finance       | finance      | settlementcard        | INDEX          | NO           | NO
 omm     | client  | finance       | finance      | settlementcard        | VACUUM         | NO           | NO
 omm     | client  | finance       | finance      | personalcustomer      | INSERT         | NO           | NO
 omm     | client  | finance       | finance      | personalcustomer      | SELECT         | NO           | YES
 omm     | client  | finance       | finance      | personalcustomer      | UPDATE         | NO           | NO
 omm     | client  | finance       | finance      | personalcustomer      | DELETE         | NO           | NO
 omm     | client  | finance       | finance      | personalcustomer      | TRUNCATE       | NO           | NO
 omm     | client  | finance       | finance      | personalcustomer      | REFERENCES     | NO           | NO
 omm     | client  | finance       | finance      | personalcustomer      | TRIGGER        | NO           | NO
 omm     | client  | finance       | finance      | personalcustomer      | ALTER          | NO           | NO
 omm     | client  | finance       | finance      | personalcustomer      | DROP           | NO           | NO
 omm     | client  | finance       | finance      | personalcustomer      | COMMENT        | NO           | NO
 omm     | client  | finance       | finance      | personalcustomer      | INDEX          | NO           | NO
 omm     | client  | finance       | finance      | personalcustomer      | VACUUM         | NO           | NO
 omm     | client  | finance       | finance      | insurance             | SELECT         | NO           | YES
 omm     | client  | finance       | finance      | account               | INSERT         | NO           | NO
 omm     | client  | finance       | finance      | account               | SELECT         | NO           | YES
 omm     | client  | finance       | finance      | account               | UPDATE         | NO           | NO
 omm     | client  | finance       | finance      | account               | DELETE         | NO           | NO
 omm     | client  | finance       | finance      | account               | TRUNCATE       | NO           | NO
 omm     | client  | finance       | finance      | account               | REFERENCES     | NO           | NO
 omm     | client  | finance       | finance      | account               | TRIGGER        | NO           | NO
 omm     | client  | finance       | finance      | account               | ALTER          | NO           | NO
 omm     | client  | finance       | finance      | account               | DROP           | NO           | NO
 omm     | client  | finance       | finance      | account               | COMMENT        | NO           | NO
 omm     | client  | finance       | finance      | account               | INDEX          | NO           | NO
 omm     | client  | finance       | finance      | account               | VACUUM         | NO           | NO
 omm     | client  | finance       | finance      | enterprisecustomer    | INSERT         | NO           | NO
 omm     | client  | finance       | finance      | enterprisecustomer    | SELECT         | NO           | YES
 omm     | client  | finance       | finance      | enterprisecustomer    | UPDATE         | NO           | NO
 omm     | client  | finance       | finance      | enterprisecustomer    | DELETE         | NO           | NO
 omm     | client  | finance       | finance      | enterprisecustomer    | TRUNCATE       | NO           | NO
 omm     | client  | finance       | finance      | enterprisecustomer    | REFERENCES     | NO           | NO
 omm     | client  | finance       | finance      | enterprisecustomer    | TRIGGER        | NO           | NO
 omm     | client  | finance       | finance      | enterprisecustomer    | ALTER          | NO           | NO
 omm     | client  | finance       | finance      | enterprisecustomer    | DROP           | NO           | NO
 omm     | client  | finance       | finance      | enterprisecustomer    | COMMENT        | NO           | NO
 omm     | client  | finance       | finance      | enterprisecustomer    | INDEX          | NO           | NO
 omm     | client  | finance       | finance      | enterprisecustomer    | VACUUM         | NO           | NO
 omm     | client  | finance       | finance      | loan                  | INSERT         | NO           | NO
 omm     | client  | finance       | finance      | loan                  | SELECT         | NO           | YES
 omm     | client  | finance       | finance      | loan                  | UPDATE         | NO           | NO
 omm     | client  | finance       | finance      | loan                  | DELETE         | NO           | NO
 omm     | client  | finance       | finance      | loan                  | TRUNCATE       | NO           | NO
 omm     | client  | finance       | finance      | loan                  | REFERENCES     | NO           | NO
 omm     | client  | finance       | finance      | loan                  | TRIGGER        | NO           | NO
 omm     | client  | finance       | finance      | loan                  | ALTER          | NO           | NO
 omm     | client  | finance       | finance      | loan                  | DROP           | NO           | NO
 omm     | client  | finance       | finance      | loan                  | COMMENT        | NO           | NO
 omm     | client  | finance       | finance      | loan                  | INDEX          | NO           | NO
 omm     | client  | finance       | finance      | loan                  | VACUUM         | NO           | NO
 omm     | client  | finance       | finance      | buy_i                 | INSERT         | NO           | NO
 omm     | client  | finance       | finance      | buy_i                 | SELECT         | NO           | YES
 omm     | client  | finance       | finance      | buy_i                 | UPDATE         | NO           | NO
 omm     | client  | finance       | finance      | buy_i                 | DELETE         | NO           | NO
 omm     | client  | finance       | finance      | buy_i                 | TRUNCATE       | NO           | NO
 omm     | client  | finance       | finance      | buy_i                 | REFERENCES     | NO           | NO
 omm     | client  | finance       | finance      | buy_i                 | TRIGGER        | NO           | NO
 omm     | client  | finance       | finance      | buy_i                 | ALTER          | NO           | NO
 omm     | client  | finance       | finance      | buy_i                 | DROP           | NO           | NO
 omm     | client  | finance       | finance      | buy_i                 | COMMENT        | NO           | NO
 omm     | client  | finance       | finance      | buy_i                 | INDEX          | NO           | NO
 omm     | client  | finance       | finance      | buy_i                 | VACUUM         | NO           | NO
 omm     | client  | finance       | finance      | buy_f                 | INSERT         | NO           | NO
 omm     | client  | finance       | finance      | buy_f                 | SELECT         | NO           | YES
 omm     | client  | finance       | finance      | buy_f                 | UPDATE         | NO           | NO
 omm     | client  | finance       | finance      | buy_f                 | DELETE         | NO           | NO
 omm     | client  | finance       | finance      | buy_f                 | TRUNCATE       | NO           | NO
 omm     | client  | finance       | finance      | buy_f                 | REFERENCES     | NO           | NO
 omm     | client  | finance       | finance      | buy_f                 | TRIGGER        | NO           | NO
 omm     | client  | finance       | finance      | buy_f                 | ALTER          | NO           | NO
 omm     | client  | finance       | finance      | buy_f                 | DROP           | NO           | NO
 omm     | client  | finance       | finance      | buy_f                 | COMMENT        | NO           | NO
 omm     | client  | finance       | finance      | buy_f                 | INDEX          | NO           | NO
 omm     | client  | finance       | finance      | buy_f                 | VACUUM         | NO           | NO
 omm     | client  | finance       | finance      | newmanager            | SELECT         | NO           | YES
 omm     | client  | finance       | finance      | newpersonalcustomer   | SELECT         | NO           | YES
 omm     | client  | finance       | finance      | newenterprisecustomer | SELECT         | NO           | YES
 omm     | client  | finance       | finance      | newbranch             | SELECT         | NO           | YES
(104 rows)

经理用户权限查看

SELECT * FROM INFORMATION_SCHEMA.role_table_grants 
where table_schema ='finance' AND grantee='manager';

 grantor | grantee | table_catalog | table_schema |      table_name       | privilege_type | is_grantable | with_hierarchy 
---------+---------+---------------+--------------+-----------------------+----------------+--------------+----------------
 omm     | manager | finance       | finance      | manager               | UPDATE         | NO           | NO
 omm     | manager | finance       | finance      | fund                  | SELECT         | NO           | YES
 omm     | manager | finance       | finance      | creditcard            | SELECT         | NO           | YES
 omm     | manager | finance       | finance      | settlementcard        | SELECT         | NO           | YES
 omm     | manager | finance       | finance      | insurance             | SELECT         | NO           | YES
 omm     | manager | finance       | finance      | account               | SELECT         | NO           | YES
 omm     | manager | finance       | finance      | loan                  | SELECT         | NO           | YES
 omm     | manager | finance       | finance      | buy_i                 | SELECT         | NO           | YES
 omm     | manager | finance       | finance      | buy_f                 | SELECT         | NO           | YES
 omm     | manager | finance       | finance      | newmanager            | SELECT         | NO           | YES
 omm     | manager | finance       | finance      | newpersonalcustomer   | SELECT         | NO           | YES
 omm     | manager | finance       | finance      | newenterprisecustomer | SELECT         | NO           | YES
 omm     | manager | finance       | finance      | newbranch             | SELECT         | NO           | YES
(13 rows)

7.4 审计技术

7.4.1 建立审计表

Branch(支行)表

CREATE TABLE log_Branch
(
update_time timestamp,
db_user varchar(40),
orp_tymp varchar(10)
);

Manager(理财经理)表

CREATE TABLE log_Manager
(
update_time timestamp,
db_user varchar(40),
orp_tymp varchar(10)
);

Loan(贷款)表

CREATE TABLE log_Loan
(
update_time timestamp,
db_user varchar(40),
orp_tymp varchar(10)
);

Account(账户)表

CREATE TABLE log_Account
(
update_time timestamp,
db_user varchar(40),
orp_tymp varchar(10)
);

PersonalCustomer(个人客户)表

CREATE TABLE log_PersonalCustomer
(
update_time timestamp,
db_user varchar(40),
orp_tymp varchar(10)
);

EnterpriseCustomer(企业客户)表

CREATE TABLE log_EnterpriseCustomer
(
update_time timestamp,
db_user varchar(40),
orp_tymp varchar(10)
);

CreditCard(信用卡)表

CREATE TABLE log_CreditCard
(
update_time timestamp,
db_user varchar(40),
orp_tymp varchar(10)
);

SettlementCard(结算卡)表

CREATE TABLE log_SettlementCard
(
update_time timestamp,
db_user varchar(40),
orp_tymp varchar(10)
);

fund(基金)表

CREATE TABLE log_fund
(
update_time timestamp,
db_user varchar(40),
orp_tymp varchar(10)
);

insurance(保险)表

CREATE TABLE log_insurance
(
update_time timestamp,
db_user varchar(40),
orp_tymp varchar(10)
);

Buy_I(购买保险)

CREATE TABLE log_Buy_I
(
update_time timestamp,
db_user varchar(40),
orp_tymp varchar(10)
);

Buy_F(购买基金)

CREATE TABLE log_Buy_F
(
update_time timestamp,
db_user varchar(40),
orp_tymp varchar(10)
);

在这里插入图片描述

7.4.2 建立审计触发器

Branch(支行)表

CREATE OR REPLACE FUNCTION log_Branch_trigger() 
RETURNS TRIGGER AS $$ 
BEGIN
INSERT INTO finance.log_Branch VALUES (now(),user,TG_OP);
RETURN NULL;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER log_Branch_trigger
AFTER INSERT OR DELETE OR UPDATE ON finance.Branch
FOR EACH ROW
EXECUTE PROCEDURE log_Branch_trigger() ;

Manager(理财经理)表

CREATE OR REPLACE FUNCTION log_Manager_trigger() 
RETURNS TRIGGER AS $$ 
BEGIN
INSERT INTO finance.log_Manager VALUES (now(),user,TG_OP);
RETURN NULL;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER log_Manager_trigger
AFTER INSERT OR DELETE OR UPDATE ON finance.Manager
FOR EACH ROW
EXECUTE PROCEDURE log_Manager_trigger() ;

Loan(贷款)表

CREATE OR REPLACE FUNCTION log_Loan_trigger() 
RETURNS TRIGGER AS $$ 
BEGIN
INSERT INTO finance.log_Loan VALUES (now(),user,TG_OP);
RETURN NULL;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER log_Loan_trigger
AFTER INSERT OR DELETE OR UPDATE ON finance.Loan
FOR EACH ROW
EXECUTE PROCEDURE log_Loan_trigger() ;

Account(账户)表

CREATE OR REPLACE FUNCTION log_Account_trigger() 
RETURNS TRIGGER AS $$ 
BEGIN
INSERT INTO finance.log_Account VALUES (now(),user,TG_OP);
RETURN NULL;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER log_Account_trigger
AFTER INSERT OR DELETE OR UPDATE ON finance.Account
FOR EACH ROW
EXECUTE PROCEDURE log_Account_trigger() ;

PersonalCustomer(个人客户)表

CREATE OR REPLACE FUNCTION log_PersonalCustomer_trigger() 
RETURNS TRIGGER AS $$ 
BEGIN
INSERT INTO finance.log_PersonalCustomer VALUES (now(),user,TG_OP);
RETURN NULL;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER log_PersonalCustomer_trigger
AFTER INSERT OR DELETE OR UPDATE ON finance.PersonalCustomer
FOR EACH ROW
EXECUTE PROCEDURE log_PersonalCustomer_trigger() ;

EnterpriseCustomer(企业客户)表

CREATE OR REPLACE FUNCTION log_EnterpriseCustomer_trigger() 
RETURNS TRIGGER AS $$ 
BEGIN
INSERT INTO finance.log_EnterpriseCustomer VALUES (now(),user,TG_OP);
RETURN NULL;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER log_EnterpriseCustomer_trigger
AFTER INSERT OR DELETE OR UPDATE ON finance.EnterpriseCustomer
FOR EACH ROW
EXECUTE PROCEDURE log_EnterpriseCustomer_trigger() ;

CreditCard(信用卡)表

CREATE OR REPLACE FUNCTION log_CreditCard_trigger() 
RETURNS TRIGGER AS $$ 
BEGIN
INSERT INTO finance.log_CreditCard VALUES (now(),user,TG_OP);
RETURN NULL;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER log_CreditCard_trigger
AFTER INSERT OR DELETE OR UPDATE ON finance.CreditCard
FOR EACH ROW
EXECUTE PROCEDURE log_CreditCard_trigger() ;

SettlementCard(结算卡)表

CREATE OR REPLACE FUNCTION log_SettlementCard_trigger() 
RETURNS TRIGGER AS $$ 
BEGIN
INSERT INTO finance.log_SettlementCard VALUES (now(),user,TG_OP);
RETURN NULL;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER log_SettlementCard_trigger
AFTER INSERT OR DELETE OR UPDATE ON finance.SettlementCard
FOR EACH ROW
EXECUTE PROCEDURE log_SettlementCard_trigger() ;

fund(基金)表

CREATE OR REPLACE FUNCTION log_fund_trigger() 
RETURNS TRIGGER AS $$ 
BEGIN
INSERT INTO finance.log_fund VALUES (now(),user,TG_OP);
RETURN NULL;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER log_fund_trigger
AFTER INSERT OR DELETE OR UPDATE ON finance.fund
FOR EACH ROW
EXECUTE PROCEDURE log_fund_trigger() ;

insurance(保险)表

CREATE OR REPLACE FUNCTION log_insurance_trigger() 
RETURNS TRIGGER AS $$ 
BEGIN
INSERT INTO finance.log_insurance VALUES (now(),user,TG_OP);
RETURN NULL;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER log_insurance_trigger
AFTER INSERT OR DELETE OR UPDATE ON finance.insurance
FOR EACH ROW
EXECUTE PROCEDURE log_insurance_trigger() ;

Buy_I(购买保险)

CREATE OR REPLACE FUNCTION log_Buy_I_trigger() 
RETURNS TRIGGER AS $$ 
BEGIN
INSERT INTO finance.log_Buy_I VALUES (now(),user,TG_OP);
RETURN NULL;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER log_Buy_I_trigger
AFTER INSERT OR DELETE OR UPDATE ON finance.Buy_I
FOR EACH ROW
EXECUTE PROCEDURE log_Buy_I_trigger() ;

Buy_F(购买基金)

CREATE OR REPLACE FUNCTION log_Buy_F_trigger() 
RETURNS TRIGGER AS $$ 
BEGIN
INSERT INTO finance.log_Buy_F VALUES (now(),user,TG_OP);
RETURN NULL;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER log_Buy_F_trigger
AFTER INSERT OR DELETE OR UPDATE ON finance.Buy_F
FOR EACH ROW
EXECUTE PROCEDURE log_Buy_F_trigger() ;

在这里插入图片描述

7.5 安全策略设置

为了保证帐户安全，如果用户输入密码次数超过一定次数（failed_login_attempts），系统将自动锁定该帐户，默认值为10。次数设置越小越安全，但是在使用过程中会带来不便。
当帐户被锁定时间超过设定值（password_lock_time），则当前帐户自动解锁，默认值为1天。时间设置越长越安全，但是在使用过程中会带来不便。

7.5.1 账户安全策略设置

启动服务器，再使用gsql客户端以管理员用户身份连接postgres数据库

gsql -d postgres -p 26000 -r

配置failed_login_attempts参数

gs_guc reload -D /gaussdb/data/dbnode -c "failed_login_attempts=10"

在这里插入图片描述
配置password_lock_time

gs_guc reload -N all -I all -c "password_lock_time=1"

在这里插入图片描述

7.5.2 密码安全策略设置

配置的加密算法

gs_guc reload -N all -I all -c "password_encryption_type=2"
  • 当参数password_encryption_type设置为0时，表示采用md5方式对密码加密。md5为不安全的加密算法，不建议使用。
  • 当参数password_encryption_type设置为1时，表示采用sha256和md5方式对密码加密。其中包含md5为不安全的加密算法，不建议使用。
  • 当参数password_encryption_type设置为2时，表示采用sha256方式对密码加密，为默认配置。

在这里插入图片描述
配置密码安全参数

gs_guc reload -N all -I all -c "password_policy=1"
  • 参数password_policy设置为1时表示采用密码复杂度校验，默认值；
  • 参数password_policy设置为0时表示不采用任何密码复杂度校验，设置为0会存在安全风险，不建议设置为0，即使需要设置也要将所有openGauss节点中的password_policy都设置为0才能生效。

在这里插入图片描述

8.日志

8.1 账号金额日志记录

针对Account中金额的更新建立日志

8.1.1 建立日志表

针对Account建立日志表

CREATE TABLE finance.AccountRecord
(
update_time timestamp,
account_no VARCHAR(20),
oldaccount_amount DOUBLE PRECISION,
newaccount_amount DOUBLE PRECISION,
orp_tymp varchar(10)
);

在这里插入图片描述

8.1.2 建立日志触发器
CREATE OR REPLACE FUNCTION AccountRecord() 
RETURNS TRIGGER AS $$ 
BEGIN
IF(TG_OP='UPDATE' OR TG_OP='update')
THEN
INSERT INTO finance.AccountRecord VALUES (now(),NEW.account_no,OLD.account_amount,NEW.account_amount,TG_OP);
END IF;
IF((TG_OP='INSERT' OR TG_OP='insert'))
THEN
INSERT INTO finance.AccountRecord VALUES (now(),NEW.account_no,NULL,NEW.account_amount,TG_OP);
END IF;
IF((TG_OP='DELETE' OR TG_OP='delete'))
THEN
INSERT INTO finance.AccountRecord VALUES (now(),NEW.account_no,OLD.account_amount,NULL,TG_OP);
END IF;
RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER AccountRecord
BEFORE INSERT OR DELETE OR UPDATE OF account_amount ON finance.Account
FOR EACH ROW
EXECUTE PROCEDURE AccountRecord() ;

在这里插入图片描述

8.2 openGauss日志管理

8.2.1系统日志

运行时日志

cd /var/log/gaussdb/omm/pg_log/dn_6001
ls

在这里插入图片描述

安装卸载时日志

 cd /var/log/gaussdb/omm/om
 ll -h

在这里插入图片描述

8.2.2 操作日志

操作日志是指数据库管理员使用工具操作数据库时以及工具被openGauss调用时产生的日志。如果openGauss发生故障，可以通过这些日志信息跟踪用户对数据库进行了哪些操作，重现故障场景。

cd /var/log/gaussdb/omm/bin

在这里插入图片描述

8.2.3 审计日志

连接数据库。

gsql -d postgres -p 26000 -r

选择日志维护方式

  • 配置审计文件占用磁盘空间的大小
    如下命令设置成默认值1024 MB。

    gs_guc reload -N all -I all -c "audit_space_limit=1024MB"

    在这里插入图片描述

  • 配置审计文件个数的最大值
    如下命令设置成默认值1048576。

    gs_guc reload -N all -I all -c "audit_file_remain_threshold=1048576"

    在这里插入图片描述

  • 备份审计文件
    获得审计文件所在目录

    SHOW audit_directory;

    在这里插入图片描述
    将审计目录整个拷贝出来进行保存。

    cp -r /var/log/gaussdb/omm/pg_audit/dn_6001 /var/log/gaussdb/omm/pg_audit/dn_6001_bak

    查看备份
    在这里插入图片描述

9 数据库备份

进行多种类型文件备份

9.1 生成备份文件

创建存储备份文件的文件夹

mkdir -p /home/omm/logical/finance_backup

执行gs_dump，导出的MPPDB_backup.sql文件格式为纯文本格式

gs_dump -U omm -W Bigdata@123 -f /home/omm/logical/finance_backup/MPPDB_backup.sql -p 26000 finance -F p

在这里插入图片描述

执行gs_dump，导出的MPPDB_backup.tar文件格式为tar格式

gs_dump -U omm -W Bigdata@123 -f  /home/omm/logical/finance_backup/MPPDB_backup.tar -p 26000 finance -F t

在这里插入图片描述

执行gs_dump，导出的MPPDB_backup.dmp文件格式为自定义归档格式

gs_dump -U omm -W Bigdata@123 -f  /home/omm/logical/finance_backup/MPPDB_backup.dmp -p 26000 finance -F c

在这里插入图片描述

执行gs_dump，导出的MPPDB_backup文件格式为目录格式

gs_dump -U omm -W Bigdata@123 -f /home/omm/logical/finance_backup/MPPDB_backup -p 26000  finance -F d

在这里插入图片描述

9.2 查看备份文件

ll /home/omm/logical/finance_backup/

在这里插入图片描述

BkbK-
关注
  • 9
    点赞
  • 踩
  • 42
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 3
    评论
专栏目录
SQL Server用触发器强制执行业务规则
03-03
Microsoft:registered: SQL Server:trade_mark: 2000 提供了两种主要机制来强制业务规则和数据完整性：约束和触发器。触发器是一种特殊类型的存储过程，它在指定的表中的数据发生变化时自动生效。唤醒调用触发 器以响应 INSERT、UPDATE 或 DELETE 语句。触发器可以查询其它表，并可以包含复杂的 Transact-SQL 语句。将触发器和触发它的语句作为可在触发器内回滚的单个事务对待。如果检测到严重错误（例如，磁盘空间不足），则整个事务即自动回滚。
2021Web前端中高级面试题复盘
sinat_39049092的博客
03-06 7421
一、js基础 1.call、apply、bind三者的用法和区别 （1）三者的使用 func.call(thisArg, arg1, arg2, ...) func.bind(thisArg, arg1, arg2, ...) func.apply(thisArg, [arg1, arg2, ...]) （2）手写call()方法 Function.prototype.myCall = function(thisArg, ...args) { thisArg.fn = this
vue2+vue3小白零基础教程—vue2篇，全网2021最详细教程
lvyuanxiang的博客
09-23 4136
vue教程 1. Vue核心 1.1 Vue简介 1.1.1 Vue是什么 一套用于构建用户界面的渐进式JavaScript框架 渐进式：Vue可以自底向上逐层的应用 简单应用：只需一个轻量小巧的核心库 复杂应用：可以引入各式各样的Vue插件 1.1.2 谁开发的 1.1.3 Vue的特点 采用组件化模式，提高代码复用率、且让代码更好维护。 声明式编码,让编码人员无需直接操作DOM,提高开发效率。 使用虚拟DOM+优秀的Diff算法，尽量复用DOM节点 1.2 Vue快速体验
工作总结以及学习总结和整理的链接
weixin_33924220的博客
01-01 4478
碰到父组件的数据通过props传递给子组件，结果子组件没有接收到 已经解决// 子组件监听 watch: { getSelect(obj) { // console.log(obj) this.$emit('select', obj) }, // 监听数据变化 schedule(val){ this.parentSc...
基于SSH保险业务管理系统的设计与实现
半夜爱磨刀的博客
02-09 2021
源码及链接： 源码及论文下载：http://www.byamd.xyz/tag/java/ 摘要 历经二十余年的高速发展，我国保险行业的市场竞争已经达到白热化的程度，在同一个城市往往有数十家主体参与保险业务的竞争。保险企业的竞争异常激烈，想要在现在这复杂多变的市场环境中跳出来，必须好好的进行业务的管理。广大保户在享受着竞争带来的实惠同时也变得更加挑剔，哪家保险公司能提供更好的服务往往成为其最终决定的关键因素。在这种环境下，越来越多的保险公司使用管理系统来提供更好的服务。目前保险公司的业务和管理信息平台，
数据库设计原则（金融行业）
eagle89的专栏
07-19 4908
一个好的数据结构设计或者说数据库设计，不仅能应对复杂的业务变化，更可以应对未来海量的数据扩容，同时数据结构逻辑清晰更方便业务人员去使用数据。     适用场景：互联网高并发、高吞吐、海量数据、强一致性要求高、mysql数据库、互联网金融业务。      一、数据库设计框架思维     1. 数据库设计第一原则，领域设计 + 恒等式设计 + 数据流转设计     注：这三个思想后面会单独写...
金融业分布式数据库选型及HTAP场景实践
杨建荣的学习笔记
05-27 423
作为数据基础设施的重要组成部分，数据库在其中扮演着重要的角色。近些年来，数据库整体发展也呈现出较之以往很大的不同。其一、是开源数据库受到更为广泛的关注，从多家机构的最新报告来看，开源数据库无论从产品数量还是受关注程度都超过商业数据库。开源这一新模式，正成为未来数据库发展的主流。其二、是云计算成为未来主要资源供给方式得到普遍共识。已经有越来越多的企业选择在云上构建基础环境，...
建库建表添加数据查询数据
qq_68040915的博客
06-08 131
创建数据库 create database financingSys character set utf8; create database 数据库名称 character set utf8;用库 use financingSys; use 库名称;创建数据表 create table financingProduct( 列名称 列类型 约束 id varchar(20) primary key, risk int not null comment ‘r1:1,r2:2,R3:3’, income varc
金融级分布式数据库架构设计要点
Enmotech的博客
02-03 1424
行业背景 银行业从最初的手工记账到会计电算化，到金融电子化，再到现在的金融科技，可以看到金融与科技的结合越来越紧密，人工智能、大数据、物联网、区块链等新兴技术改变了金融的交易方式，为金融行业的创新前行提供了源源不断的动力。同时互联网金融的兴起是一把双刃剑，带来了机遇的同时也带来了挑战。普惠金融使得金融的门槛降低，更多的普通大众参与到金融活动中，这让金融信息系统承受了越来越大的压力。于是我们可以...
数据库课程设计jsp1159金融交易页面ssh.sql
09-07
数据库课程设计，毕业设计，数据库设计
银行管理系统数据库的设计与实现
07-20
这次的数据库课程设计我以这 一应用环境为背景，应用数据库原理课程所学的数据库设计与关系数据理论，结 合实际的操作和设计进行设计。目标是制定一套合理、有效，规范和实用的银行 管理系统，对银行信息进行集中...
数据库原理与应用：广东金融学院学习资料整理
最新发布
04-15
数据库原理与应用：广东金融学院学习资料整理 数据库原理与应用：广东金融学院学习资 料整理 数据库原理与应用：广东金融学院学习资料整理 数据库原理与应用：广东金融学 院学习资料整理 数据库原理与应用：广东金融...
数据库课程设计文档于源码
06-22
数据库课程设计，前台是用PHP语言实现的，后台是用MySQL数据库管理软件。全是资格儿做的啊。
计算机课程(数据库)-数据库安全性
07-13
无论是企业的客户数据、金融机构的财务信息，还是医疗机构的病人记录，数据库中存储的敏感数据都需要得到妥善的保护，以防止未授权访问和数据泄露。本文将深入探讨数据库安全性的各个方面，并提供实用的建议和最佳...
【汇编语言】 变量
热门推荐
PKM of bk
03-29 1万+
汇编语言 变量 一、变量的概念 二、变量的定义 (1)变量名 (2)变量定义伪指令 1.助记符：BYTE 2.助记符：WORD 3.助记符：DWORD (3)初值表 三、多字节数据的存储顺序 (1)字节编址的主存储器 (2)数据的存储顺序 (3)小端存储和大端存储 四、变量的定位 (1)变量（代码）的存储位置 (2)定位伪指令 (3)多字节数据对齐地址边界获得更高的性能 五、变量属性 (1)变量的地址属性 (2)变量的类型属性
【数据库原理实验(openGauss)】创建数据库、表和索引
PKM of bk
06-13 9930
创建数据库、表和索引 一、创建数据库 二、创建与删除模式 三、创建与修改表 (1)创建表 (2)修改表 三、创建与删除索引 (1)创建索引 (2)删除索引
【汇编语言】寄存器和地址
PKM of bk
03-04 9141
寄存器和地址 主要内容： 一、寄存器 (1)透明寄存器 (2)可编程寄存器 1.通用寄存器 2.专用寄存器 二、地址 (1)存储模型 (2)存储空间分段管理 (3)物理地址与逻辑地址 (4)操作数寻址过程
【数据库原理实验(openGauss)】 使用JDBC连接数据库
PKM of bk
06-15 7798
使用JDBC连接数据库 一、准备连接环境 1.修改数据库的pg_hba.conf文件 2.登陆数据库授权退出 3.修改数据库监听地址 4.下载J驱动包导入工具 5.创建测试数据库demo 6.创建schema 7.创建测试表websites 8.插入数据 9.退出数据库 二、确定26000端口是否放开 三、下载并安装JDK 四、1.2.4 配置JDK环境变量 五、连接openGauss并执行java代码 (1)使用Java程序连接数据库并进行查询 (2)编译后执行 (3)执行结果
数据库系统课程设计目的和意义
05-20
数据库系统课程设计的主要目的是让学生通过实践来深入理解数据库系统的原理和应用，提高学生的数据库设计和管理能力。同时，该课程设计还能够锻炼学生的团队协作能力和实际问题解决能力，将理论知识与实际操作结合起来，使学生能够更好地适应未来工作中的挑战。 数据库系统在现代信息化社会中扮演着重要的角色，被广泛应用于各种领域，如电子商务、金融、医疗、物流等。因此，掌握数据库系统的设计与管理技能对于学生未来的职业发展至关重要。通过课程设计，学生能够全面掌握数据库系统的设计与管理方法，并能够将所学知识应用于实际项目中，为未来的职业发展打下坚实的基础。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 3
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

BkbK-

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值