21/12/6 Database exp 8
分析系统功能
物理设计与数据字典
表及其属性
client(c_id,c_name,c_mail,c_id_card,c_phone,c_password)
bank_card(b_id,b_type,b_c_id)
property(p_id,p_c_id,p_i_id,p_status,p_amount,p_get_time,p_income,p_type)
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)
f_product(fp_id,fp_name,fp_description,fp_amount,fp_year)数据字典
表1-1 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 客户登录密码 表1-2bank_card表
字段名称 字段类型 约束 说明 b_id INTEGER PRIMARY KEY 银行卡号 b_type CHAR(20) NOT NULL 银行卡类型 b_c_id INTERGER NOT NULL
FOREIGNKEY客户编号 表1-3 property资产表
字段名称 字段类型 约束 说明 p_id INTEGER PRIMARY KEY 资产编号 p_c_id INTEGER NOT NULL
FOREIGN KEY客户编号
外键,对应client中的c_idp_i_id INTEGER NOT NULL
FOREIGN KEY商品编号 ,对应三种产品的id p_status CHAR(20) NOT NULL 状态 p_amount INTERGER NOT NULL 购买数量 p_get_time DATE NOT NULL 购买时间 p_income INTERGER 收益 p_type INTERGER NOT NULL 1代表是理财产品,2代表保险,3代表基金 表1-4 f_products 理财产品表
字段名称 字段类型 约束 说明 fp_id INTEGER PRIMARY KEY 理财产品编号 fp_year INTEGER 理财年限 fp_amount INTERGER 购买金额 fp_name VARCHAR(200) NOT NULL 产品名称 fp_description VARCHAR(2000) NOT NULL 产品描述 表1-5 insurance 保险表
字段名称 字段类型 约束 说明 i_id INTEGER PRIMARY KEY 保险编号 i_name VARCHAR(100) NOT NULL 保险名称 i_person CHAR(30) 适用人群 i_project VARCHAR(200) 保险项目 i_amount INTERGER 保险金额 i_year CHAR(20) NOT NULL 保险年限 表1-6 fund基金表
字段名称 字段类型 约束 说明 f_id INTEGER PRIMARY KEY 基金编号 f_name VARCHAR(100) NOT NULL 基金名称 f_type CHAR(30) 基金类型 f_risk INTERGER 风险等级 f_manager INTERGER NOT NULL 基金管理者 f_amount INTERGER NOT NULL 基金金额
创建数据库表
创建finance数据库作为项目数据库,数据库编码为UTF-8。(2分)
- 切换到omm用户
- 启动数据库服务
- 登入postres数据库(为了在里面创建新的数据库)
- 创建finance数据库,设置编码为UTF-8
- 切换到omm用户
连接finance数据库,创建名为finance的schema,并设置finance为当前的schema。(2分)
- 连接finance数据库
- 创建名为finance的schema,并设置finance为当前的schema
- 连接finance数据库
在finance模式下完成金融管理系统中所有数据库对象(数据表)的创建,并完成数据的填充。其中客户数据不少于20条,银行卡数据不少于10条,其他数据不少于5条;(6分)
数据表创建
客户表创建
银行卡表创建
理财产品表创建
保险信息表创建
基金信息表创建
资产信息表创建
添加数据
客户信息添加
银行卡数据填充
理财产品填充
INSERT INTO finance.f_products
(fp_id ,fp_year ,fp_amount ,fp_name ,fp_description ) VALUES
(1 ,2 ,4000000 ,’储蓄’ ,’储蓄理财产品’ ),
(2 ,1 ,3000 ,’债券’ ,’债券是政府、企业、银行等债务人为筹集资金,按照法定程序发行并向债权人承诺于指定日期还本付息的有价证券’),
(3 ,1 ,200000 ,’股票’ ,’股份公司为筹集资金而发行给各个股东作为持股凭证并借以取得股息和红利的一种有价证券’),
(4 ,3 ,2000 ,’国债’ ,’国家以其信用为基础,按照债的一般原则,通过向社会筹集资金所形成的债权债务关系’),
(5 ,2 ,10000 ,’大宗商品’ ,’与大宗商品期货挂钩的理财产品。目前市场上主要以挂钩黄金、石油、农产品的理财产品居多’)
保险产品填充
INSERT INTO finance.insurance(i_name,i_id,i_amount,i_person,i_year,i_project) VALUES
(‘意外保险’,3,5000,’所有人’,20,’平安保险’),
(‘医疗保险’,4,2000,’所有人’,20,’平安保险’),
(‘健康保险’,1,2000,’老年人’,10,’平安保险’),
(‘人寿保险’,2,3000,’老年人’,10,’平安保险’),
(‘财产损失保险’,5,1500,’中年人’,3,’平安保险’);
基金表格填充
INSERT INTO finance.fund (f_id,fp_name,f_type,f_risk,f_manager,f_amount) VALUES
(1,’股票基金’,’股票型’,3,1,200000),
(2,’债券基金’,’债券型’,2,2,3000),
(3,’投资基金’,’债券型’,3,2,1000),
(4,’国债’,’货币型’,1,4,2000),
(5,’期货’,’期货型’,2,5,1500);
资产表格填充
INSERT INTO finance.property
(p_id , p_c_id ,p_i_id , p_status , p_amount ,p_get_time ,p_income ,p_type ) VALUES
(1 ,1 ,1 ,’可用’ ,100 ,’2021-11-20’ ,2000 ,1 ),
(2 ,1 ,2 ,’可用’ ,100 ,’2021-11-21’ ,3020 ,1 ),
(3 ,1 ,1 ,’冻结’ ,100 ,’2021-11-20’ ,2000 ,2 ),
(4 ,1 ,4 ,’可用’ ,101 ,’2021-11-10’ ,3000 ,3 ),
(5 ,2 ,5 ,’可用’ ,100 ,’2021-11-20’ ,2000 ,2 )
对表添加外键约束,在银行信息表和资产信息表中,都存在每个银行卡必须有一个持卡者、每份资产必须都有一个资产拥有者这样的对应关系。因此针对这种对应关系,创建外键约束。(4分)
添加外键约束
- 信用卡的外键
ALTER TABLE finance.bank_card ADD CONSTRAINT fk_c_id FOREIGN KEY (b_c_id) REFERENCES finance.client(c_id) ON DELETE CASCADE; - 资产的外键
ALTER TABLE finance.property ADD CONSTRAINT fk_pro_c_id FOREIGN KEY (p_c_id) REFERENCES finance.client(c_id) ON DELETE CASCADE;
- 信用卡的外键
在理财产品表、保险信息表和基金信息表中,都存在金额这个属性,在现实生活中,金额不会存在负数。因此针对表中金额的属性,增加大于0的约束条件。(4分)
增加大于0的约束
f_products表
ALTER table finance.f_products ADD CONSTRAINT c_p_mount CHECK (fp_amount >=0);fund表
ALTER table finance.fund ADD CONSTRAINT c_f_mount CHECK (f_amount >=0);
insurance表
ALTER table finance.insurance ADD CONSTRAINT c_i_mount CHECK (i_amount >=0);
输出:
输出查询所有表的数据字典的查询语句和结果,可以截图或者复制黏贴代码。(基本都在之前有展示,这里对所有的表及schema进行了一次查询)
select * from pg_tables;输出查询所有表的数据的查询语句和结果,可以截图或者复制黏贴代码。
client表
select * from finance.client;bank_card表
select * from finance.bank_card;fund表
select * from finance.fund;f_products表
select * from finance.f_products;insurance表
select * from finance.insurance;property表
select * from finance.property;
输出所有创建约束的语句和创建结果,可以截图或者复制黏贴代码(这在之前已经放上了)
对表中的数据进行查询操作
l 至少完成1条单表查询和1条表查询。(4分)
单表查询–查询客户表中所有的客户idselect c_id from finance.client;
表查询
select c_name from finance.client;l 至少完成两条聚合查询,例如查询用户表中有多少个用户;查询保险信息表中,保险金额的平均值等。(4分)
查询property表的income平均值
select avg(p_income) from finance.property
查询用户表中用户总数
select count(*) from finance.client;l 至少完成3条连接查询,例如:半连接、反连接、多表查询和子查询。(6分)
- 等值连接查询,查询每个用户及其拥有的资产
select finance.client.*,finance.property. *
from finance.client,finance.property
where finance.client.c_id=finance.property.p_c_id; - 自身连接,在fund表中对每一个基金项,查询manager 和自身id相同的基金
select first.*,second.f_manager
from finance.fund first,finance.fund second
where first.f_id = second.f_manager - 子查询,查询和p_id=3的资产处于相同状态的资产(然后发现就它自己冻结着)
select * from finance.property
where p_status=
(select p_status from finance.property where p_id=3);
- 等值连接查询,查询每个用户及其拥有的资产
至少完成1条ORDER BY查询。(2分)
将资产按照收益逆序输出
select * from finance.property order by p_income desc;至少完成1条GROUP BY……HAVING查询。(4分)
查询收益在2000及以上的资产及其平均收益,将结果按p_id分组显示,且只显示平均收益大于2500的组
select *,avg(p_income) from finance.property
where p_income>=2000group by p_id
having avg(p_income)>=2500;查询基金,将其按f_id分组,且只显示平均数量大于等于2000的组
select *,avg(f_amount) from finance.fund
group by f_id
having avg(f_amount)>=2000;
4.5 创建视图和索引
创建一个视图
查看client中属性,除了c_mail属性
create view see_client
as
select c_id,c_name,c_id_card,c_phone,c_password
from finance.client修改视图
修改视图所属schema
alter view see_client set schema finance;使用视图进行查询
重命名视图
alter view finance.see_client
rename to oh_my_god;删除视图
创建索引
为客户电话号码创建索引
CREATE INDEX index_phone on finance.client(c_phone) ;重建索引
对整个客户表重建索引重命名索引
alter index finance.index_phone rename to ind_phone;删除索引
1.4.6 数据修改和删除(5分)
任务:
l 修改数据:至少修改2个不同的数据。(3分)
将client表中第1个元素的值c_name改为’年没怀念’
之前的表
修改语句:
update finance.client
set c_name=’年没怀念’
where c_id=1;修改后表情况
将property中p_get_time小于2021-11-11 00:00:00的值修改为2021-11-19 00:00:00
修改前状态
修改
update finance.property
set p_get_time=’2021-11-19’
where p_get_time<’2021-11-11’;
修改后状态
l 删除指定数据:至少删除2个不同数据表的数据。(2分)
将finance.property中p_amount=101的数据删除
删除前表情况
删除
delete from finance.property where p_amount=101;删除后表情况
删掉c_id>20的用户信息
删除前表情况
删除操作
delete from finance.client where c_id>20;
删除后表情况
1.4.7 创建新用户(6分)
任务:
新用户的创建和授权:创建用户dbuser,密码为Gauss#3demo;给用户dbuser授予finance数据库下银行卡信息表的查询和插入权限,并将finance模式的权限也授予dbuser用户。(3分)
创建用户
给用户dbuser授予finance数据库下银行卡信息表的查询和插入权限,并将finance模式的权限也授予dbuser用户
新用户连接数据库:使用新用户连接finance数据库;访问finance数据库的银行卡信息表。(2分)
新用户连接
新用户访问finance数据库的银行卡信息表删除finance模式。(1分)
1.4.8 使用jdbc连接openGauss数据库
创建测试数据库demo;
创建名为demo的schema,并设置demo为当前的schema
设置搜索路径为demoSET search_path TO demo;
创建测试表websites(id,name,url),数据为(’1’, ‘openGauss’, ‘https://opengauss.org/zh/'),(‘2’, ‘华为云’, ‘https://www.huaweicloud.com/'), (‘3’, ‘openEuler’, ‘https://openeuler.org/zh/'), (‘4’, ‘华为support中心’, ‘https://support.huaweicloud.com/')。(3分)
create table websites
(
id Integer primary key,
name varchar(200),
url varchar(200));
插入数据INSERT INTO websites (id,name,url) VALUES
(‘1’, ‘openGauss’, ‘https://opengauss.org/zh/'),
(‘2’, ‘华为云’, ‘https://www.huaweicloud.com/'),
(‘3’, ‘openEuler’, ‘https://openeuler.org/zh/'),
(‘4’, ‘华为support中心’, ‘https://support.huaweicloud.com/')
查看入站规则是否包括tcp26000端口
查看java版本是否为8
创建项目,引入postgres.jar包
写下测试程序如下
结果
本实验完成
21/12/6 Database exp 8