HNU分布式数据库华为云数据库TaurusDB实践

华为云数据库TaurusDB实践

实验一：华为云数据库TaurusDB用户登录验证

步骤1：使用IAM用户登录华为云官网

登录https://auth.huaweicloud.com/，进入登录页面

使用IAM用户登录，使用老师分发的个人账号

登录成功后：

点击左上角，打开服务列表：

然后点击数据库，选择云数据库TaurusDB

步骤2：用数据库用户访问对应数据库：

注意将控制台调整为北京四

点击实例管理：

使用分发的账号密码登录数据库

我的账户是DB_USRR04，所以只能访问USER04DB

步骤3：验证数据库用户权限：
创建新数据库：

尝试创建新数据库会报错，说明我们的用户只拥有当前数据库的操作权限，不能新建数据库。

创建表：

点击创建会生成SQL脚本预览：

点击执行脚本

可以发现我们的表创建成功了

实验二：华为云数据库TaurusDB场景化综合应用实验：

本实验以金融行业为场景，设计数据库模型，并使用华为云TaurusDB构建金融场景下的数据库，通过对数据库中的对象（表、约束、视图、索引等）创建，掌握基础SQL语法，并通过对表中数据的增删改查，模拟金融场景下的业务实现。

本实验中的金融数据模型，主要是为了实现实验操作而构造的，若与现实场景中模型相似，纯属巧合。

实验环境：

本实验环境为华为云TaurusDB数据库。

实验环境配置：

实验总览：

1. 金融暑假模型设计
   1. 金融数据模型

假设A市C银行为了方便对银行数据的管理和操作，引入了华为TaurusDB数据库。针对C银行的业务，本实验主要将对象分为客户、银行卡、理财产品、保险、基金和资产。因此，针对这些数据库对象，本实验假设C银行的金融数据库存在着以下关系：客户可以办理银行卡，同时客户可以购买不同的银行产品，如资产，理财产品，基金和保险。那么，根据C银行的对象关系，本实验给出了相应的关系模式和ER图，并对其进行较为复杂的数据库操作。

1. 1. 1. ER图

1. 1. 1. 关系模式

对于C银行中的5个对象，分别建立属于每个对象的属性集合，具体属性描述如下：

 ·客户（客户编号、客户名称、客户邮箱，客户身份证，客户手机号，客户登录密码）

 ·银行卡（银行卡号，银行卡类型）

 ·理财产品（产品名称，产品编号，产品描述，购买金额，理财年限）

 ·保险（保险名称，保险编号，保险金额，适用人群，保险年限，保障项目）

 ·基金（基金名称，基金编号，基金类型，基金金额，风险等级，基金管理者）

对象之间的关系：

 ·一个客户可以办理多张银行卡

 ·一个客户可以购买多个理财产品，同一类理财产品可由多个客户购买

 ·一个客户可以购买多个基金，同一类基金可由多个客户购买

 ·一个客户可以购买多个保险，同一类保险可由多个客户购买

根据关系分析，设计关系模型如下：

说明：

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

1. 1. 1. 物理模型

对象及属性对应的编号为：

• Client(c_id，c_name，c_mail，c_id_card，c_phone，c_password)
• bank_card(b_number，b_type，b_c_id)
• finances_product(p_name，p_id，p_description，p_amount，p_year)
• insurance(i_name，i_id，i_amount，i_person，i_year，i_project)
• fund(f_name，f_id，f_type，f_amount，risk_level，f_manager)
• property(pro_id,pro_c_id，pro_pif_id，pro_type，pro_status，pro_quantity，pro_income，pro_purchase_time)

说明：

• 资产表（property）中由于商品编号(pro_pif_id)字段引用自理财产品表、保险和基金表的id字段，为了防止三商品的id字段相互冲突，添加商品类型(pro_type)字段区分三种商品。并且资产表添加资产编号(pro_id)字段作为主键。

Client（客户）表

bank_card（银行卡）表

finances_product（理财产品）信息表

insurance（保险）表

fund（基金）表

property（资本）表

接下来进行openGauss数据模型表操作

1. 1. TaurusDB数据模型表操作
      1. 登录华为云及数据库

我们已经参照实验一，成功用IAM登录华为云，且成功用所分配的数据库访问对应的数据库。

1. 1. 1. 表的创建

根据C银行的场景描述，本实验分别针对客户(client)，银行卡(bank_card)，理财产品(finances_product)，保险(insurance)，基金(fund)和资产(property)创建相应的表。具体的实验步骤如下所示：

步骤1：客户信息表的创建

步骤2：银行卡信息表的创建

步骤3：理财产品信息表的创建

步骤4：保险信息表的创建

步骤5：基金信息表的创建

步骤6：资产信息表的创建

通过执行上面的代码，我们可以观察我们现有的表：

1. 1. 1. 数据表的插入

为了实现对表数据的相关操作，本实验需要以执行SQL的方式对金融数据库的相关表插入部分数据

步骤1：初始化client表

步骤2：初始化bank_card表

步骤3：初始化finances_product表

步骤4：初始化insurance表

步骤5：初始化fund表

步骤6：初始化property表

1. 1. 1. 手工插入一条数据

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

步骤1：在金融数据库的客户信息表中添加一个客户的信息。（属性冲突的场景）

由于我们定义了c_id_card和c_phone唯一且非空，当表中存在数据时，插入会失败。

步骤2：在金融数据库的客户信息表添加一个客户的信息（插入成功）

1. 1. 1. 添加约束

步骤1：对表添加外键约束，在银行信息表和资产信息表中，都存在每个银行卡必须有一个持卡者、每份资产必须都有一个资产拥有者这样的对应关系。因此针对这种对应关系，创建外键约束。

分别给bank_card和property添加约束（外键）

备注：

• 银行卡信息表中的b_c_id与客户信息表中的c_id一致，且每个银行卡都必须有一个持卡者。
• 在进行表删除时，需要先删除bank_card表，再删除client表，因为两个表存在约束。
• 资产信息表中的pro_c_id与客户信息表中的c_id一致，且每一份资产都必须有一个资产拥有者。
• 在进行表删除时，需要先删除property表，再删除client表，因为两个表存在约束。

步骤2：在理财产品表、保险信息表和基金信息表中，都存在金额这个属性，在现实生活中，金额不会存在负数。因此针对表中金额的属性，增加大于0的约束条件。

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

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

发现插入失败（我们设置的约束成功）

步骤4：向fund表添加约束

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

步骤5：向insurance表中添加约束

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

1. 1. 1. 查询数据

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

步骤1：单表查询

步骤2：条件查询

• 查询资产信息中“可用”的资产数据

步骤3：聚合查询

• 查询用户表有多少个用户

我们最开始加入了30个，后续又添加了1个，现在查询到31个，符合。

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

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

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

步骤4：连接查询

1. 半连接

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

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

1. 反连接

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

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

步骤5：子查询

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

步骤6：ORDER BY和GROUP BY

1. ORDER BY子句

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

1. GROUP BY子句

查询各保险信息总数，按照p_year分组

步骤7：HAVING和WITH AS

1. HAVING子句

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

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

1. WITH AS子句

该子句可以创建一个临时表名，可以被整个SQL语句用到

使用WITH AS查询基金信息表

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

1. 1. 1. 视图

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

步骤1：创建视图

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

步骤2：修改视图

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

步骤3：删除视图

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

之后我们再次进行SELECT操作，失败，视图已经被删除

但是原表不受影响

1. 1. 1. 索引

步骤1：创建索引

在普通表property上创建索引

这个语句创建了一个复合索引（从左边开始匹配）

步骤2：重命名索引

在普通表property上重建及重命名索引

重建索引

重命名索引

在普通表property上重建及重命名索引

步骤3：删除索引

删除索引idx_property_temp

1. 1. 1. 数据的修改和删除

步骤1：修改数据

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

先查看表数据

进行更新：

之后重新查询数据情况

可以发现更新成功，c_id小于10的都变成了借记卡（对比之前查询结果）

步骤2：删除指定数据

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

删除前查询结果

开始删除：

查询删除结果：

可以发现编号小于3的行都被删除了

1. 1. 使用JDBC连接数据库
      1. 准备连接环境

步骤1：Java连接TaurusDB可以使用MySQL8.0的驱动包，下载Java连接MySQL的驱动包，并将其导入对应的使用工具。

通过下面的链接，下载驱动包（原文档链接失效了）

https://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-8.0.16.zip

步骤2：创建测试表websites

步骤3：插入数据

步骤4：绑定弹性公网IP（已经绑定）

1. 1. 1. 下载并按照JDK

用下面的链接

https://gitcode.com/open-source-toolkit/3468b

安装完成

查看安装目录

1. 1. 1. 配置JDK环境变量

步骤1：右击“此电脑”选择“属性”，点击“高级系统设置”

步骤2：步骤 2 点击“环境变量”，新建系统变量“JAVA_HOME”，输入JDK安装目录。

步骤3：编辑系统变量"path"

点击PATH环境变量，新建这两个值

步骤4：新建系统变量"classpath"变量，输入"."

步骤5：系统变量配置完毕，查询检验是否配置成功，运行cmd 输入java -version（java和 -version之间有空格）。

安装和配置成功

1. 1. 1. 连接TaurusDB并执行Java代码

步骤1：使用Java程序连接数据库并进行查询

先创建GaussDBMySQLDemo.java文件

注意需要根据实际情况，修改jdbc:postgresql://公网IP:3306/demo中公网IP信息，USER = "root"连接数据库的用户及密码PASS = "12345"中红色的部分

import java.sql.*; public class GaussDBMySQLDemo { // MySQL 8.0 以上版本 - JDBC 驱动名及数据库 URL static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver"; static final String DB_URL = "jdbc:mysql://公网IP:3306/demo?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC"; // 数据库的用户名与密码，需要根据自己的设置 static final String USER = "root"; static final String PASS = "123456"; public static void main(String[] args) { Connection conn = null; Statement stmt = null; try{ // 注册 JDBC 驱动 Class.forName(JDBC_DRIVER); // 打开链接 System.out.println("连接数据库..."); conn = DriverManager.getConnection(DB_URL,USER,PASS); // 执行查询 System.out.println(" 实例化Statement对象..."); stmt = conn.createStatement(); String sql; sql = "SELECT id, name, url FROM websites"; ResultSet rs = stmt.executeQuery(sql); // 展开结果集数据库 while(rs.next()){ // 通过字段检索 int id = rs.getInt("id"); String name = rs.getString("name"); String url = rs.getString("url"); // 输出数据 System.out.print("ID: " + id); System.out.print(", 站点名称: " + name); System.out.print(", 站点 URL: " + url); System.out.print("\n"); } // 完成后关闭 rs.close(); stmt.close(); conn.close(); }catch(SQLException se){ // 处理 JDBC 错误 se.printStackTrace(); }catch(Exception e){ // 处理 Class.forName 错误 e.printStackTrace(); }finally{ // 关闭资源 try{ if(stmt!=null) stmt.close(); }catch(SQLException se2){ }// 什么都不做 try{ if(conn!=null) conn.close(); }catch(SQLException se){ se.printStackTrace(); } } System.out.println("Goodbye!"); } }

步骤2：打开cmd对Java程序编译后执行。

会出现.class文件：

然后再执行：

1. 1. 实验小结

本实验通过ER模型加深对数据库的理解和数据库设计知识的掌握，通过SQL语句的练习，使得能熟练掌握SQL语法。